设为首页 加入收藏

TOP

MySQL中关于ORDERBY、DISTINCT、ALTER、LIKE/NOTLIKE、REGEXP/NOTREGEXP、COUNT、MAX的使用介绍(一)
2018-03-14 09:00:37 】 浏览:126
Tags:MySQL 关于 ORDERBY DISTINCT ALTER LIKE/NOTLIKE REGEXP/NOTREGEXP COUNT MAX 使用 介绍

排序:

SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]

你可以使用任何字段来作为排序的条件,从而返回排序后的查询结果。
你可以设定多个字段来排序。
你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下,它是按升序排列。
你可以添加 WHERE...LIKE 子句来设置条件。

对查询的结果去重(DISTINCT):

SELECT DISTINCT species FROM pet

更新表结构:

删除列:

ALTER TABLE pet DROP COLUMN death;

添加列:

ALTER TABLE pet ADD COLUMN id VARCHAR(20) NOT NULL;

修改列描述:

ALTER TABLE pet MODIFY COLUMN id VARCHAR(20) BIGINY NOT NULL;

添加/删除主键:

ALTER TABLE pet ADD/DROP PRIMARY KEY(id);

从txt文件导入数据

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
 -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

如 pets.txt:

Chirpy1 Gwen    bird    m   1998-09-11  1995-07-29
Chirpy  Gwen    bird    f   1998-09-11  1995-07-29

mysql> LOAD DATA LOCAL INFILE '/path/pets.txt' INTO TABLE pet;

If you created the file on Windows with an editor that uses \r\n as a line terminator, you should use this statement instead:

    mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
     -> LINES TERMINATED BY '\r\n';

(On an Apple machine running OS X, you would likely want to use LINES TERMINATED BY '\r'.)

计算针对某个日期字段相对于某个时间点经过的时间(可以是year,month,day,hour,minute,second)

mysql> SELECT name, birth, CURDATE(),
 -> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
 -> FROM pet ORDER BY age ;

假设用户表中存储着用户的生日信息(DATE类型),如何过滤出本月或者今天过生日的用户:

SELECT name,owner,MONTH(birth) FROM pet WHERE MONTH(birth)>3;
SELECT name,owner,DAY(birth) FROM pet WHERE DAY(birth)>3;

动态获取

mysql> SELECT name, birth FROM pet
 -> WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 0 MONTH));

mysql> SELECT name, birth FROM pet
 -> WHERE DAY(birth) = DAT(DATE_ADD(CURDATE(),INTERVAL 0 DAY));

你知道以下SQL语句的输出是什么吗?

mysql> SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;

是下面这样:
+———–+—————+————+—————-+
| 0 IS NULL | 0 IS NOT NULL | ” IS NULL | ” IS NOT NULL |
+———–+—————+————+—————-+
| 0 | 1 | 0 | 1 |
+———–+—————+————+—————-+

模式匹配(LIKE/NOT LIKE)

SQL pattern matching enables you to use _ to match any single character and % to match an arbitrary
number of characters (including zero characters).

To find names beginning with b:

mysql> SELECT * FROM pet WHERE name LIKE 'b%';

To find names ending with fy:

mysql> SELECT * FROM pet WHERE name LIKE '%fy';

To find names containing a w:

mysql> SELECT * FROM pet WHERE name LIKE '%w%';

To find names containing exactly five characters, use five instances of the _ pattern character:

mysql> SELECT * FROM pet WHERE name LIKE '_____';

如果想在模式匹配中使用其它的正则,那么请使用REGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE, which are synonyms).

. [...] *  use ^ at thebeginning or $ at the end of the pattern

To find names beginning with b, use ^ to match the beginning of the name:

mysql> SELECT * FROM pet WHERE name REGEXP '^b';
mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^b';#大小写敏感的(BINARY)

To find names ending with fy, use $ to match the end of the name:

mysql> SELECT * FROM pet WHERE name REGEXP 'fy$';

To find names containing a w, use this query:

mysql> SELECT * FROM pet WHERE name REGEXP 'w';

To find names containing exactly five characters, use ^ and $ to match the beginning and end of the name, and five instances of . in between:

mysql> SELECT * FROM pet WHERE name REGEXP '^.....$';
also:mysql> SELECT * FROM pet WHERE n
编程开发网
首页 上一页 1 2 下一页 尾页 1/2/2
】【打印繁体】【投稿】【收藏】 【推荐】【举报】【评论】 【关闭】 【返回顶部
上一篇MySQL事务的使用详解 下一篇SQL Server2005实例讲解数据库的..

评论

帐  号: 密码: (新用户注册)
验 证 码:
表  情:
内  容:

最新文章

热门文章

C 语言

C++基础

windows编程基础

linux编程基础

C/C++面试题目