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 name REGEXP '^.{5}$';

分组计数：
You can use COUNT() if you want to find out how many pets each owner has:

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;

ONLY_FULL_GROUP_BY参数开启与未开启区别

If the ONLY_FULL_GROUP_BY SQL mode is enabled, an error occurs:

mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY';
Retrieving Information from a Table
265
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT owner, COUNT(*) FROM pet;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression
#1 of SELECT list contains nonaggregated column 'menagerie.pet.owner';
this is incompatible with sql_mode=only_full_group_by

If ONLY_FULL_GROUP_BY is not enabled, the query is processed by treating all rows as a single
group, but the value selected for each named column is indeterminate. The server is free to select
the value from any row:

mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT owner, COUNT(*) FROM pet;
+--------+----------+
| owner | COUNT(*) |
+--------+----------+
| Harold | 8 |
+--------+----------+
1 row in set (0.00 sec)

假如有一个商品的数据表，请查找出价格最贵的一件商品的名称以及价格

SELECT name,MAX(price) AS price FROM shop;

also:

SELECT article, dealer, price
FROM shop
WHERE price=(SELECT MAX(price) FROM shop);