转载自:MySQL杂记(ORDER BY、DISTINCT、ALTER 、LIKE/NOT LIKE、REGEXP/NOT REGEXP、COUNT、MAX)_Lockey23的博客-CSDN博客
排序:
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);
————————————————
版权声明:本文为CSDN博主「「已注销」」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/Lockey23/article/details/79533486