[MySQL][表的增删查改][二][Retrieve][SELECT][WHERE]详细讲解

目录

1.Retrieve

1.基本语法

SELECT [DISTINCT] * | {column [, column] ...} [FROM table_name]
[WHERE ...] [ORDER BY column [ASC | DESC], ...] LIMIT ...

• WHERE后面没有办法使用别名 - 此时数据还没有被筛选出来，别名还没有生效
• ORDER BY后面可以使用别名 - 先要有合适的数据，才能排序
• LIMIT只有数据准备好了，才能显示，LIMIT的本质功能是"显示"
• 示例数据：// 创建表结构CREATE TABLE exam_result (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20) NOT NULL COMMENT '同学姓名',chinese float DEFAULT 0.0 COMMENT '语文成绩',math float DEFAULT 0.0 COMMENT '数学成绩',english float DEFAULT 0.0 COMMENT '英语成绩');// 插入测试数据INSERT INTO exam_result (name, chinese, math, english) VALUES('唐三藏', 67, 98, 56),('孙悟空', 87, 78, 77),('猪悟能', 88, 98, 90),('曹孟德', 82, 84, 67),('刘玄德', 55, 85, 45),('孙权', 70, 73, 78),('宋公明', 75, 65, 30);Query OK, 7 rows affected (0.00 sec)Records: 7 Duplicates: 0 Warnings: 0

2.SELECT列

1.全列查询

• 通常情况下不建议使用 * 进行全列查询- 查询的列越多，意味着需要传输的数据量越大- 可能会影响到索引的使用SELECT * FROM exam_result;+----+-----------+---------+------+---------+| id | name | chinese | math | english |+----+-----------+---------+------+---------+| 1 | 唐三藏 | 67 | 98 | 56 || 2 | 孙悟空 | 87 | 78 | 77 || 3 | 猪悟能 | 88 | 98 | 90 || 4 | 曹孟德 | 82 | 84 | 67 || 5 | 刘玄德 | 55 | 85 | 45 || 6 | 孙权 | 70 | 73 | 78 || 7 | 宋公明 | 75 | 65 | 30 |+----+-----------+---------+------+---------+7 rows in set (0.00 sec)

2.查询字段为表达式

• 表达式不包含字段SELECT id, name, 10 FROM exam_result;+----+-----------+----+| id | name | 10 |+----+-----------+----+| 1 | 唐三藏 | 10 || 2 | 孙悟空 | 10 || 3 | 猪悟能 | 10 || 4 | 曹孟德 | 10 || 5 | 刘玄德 | 10 || 6 | 孙权 | 10 || 7 | 宋公明 | 10 |+----+-----------+----+7 rows in set (0.00 sec)
• 表达式包含一个字段SELECT id, name, english + 10 FROM exam_result;+----+-----------+--------------+| id | name | english + 10 |+----+-----------+--------------+| 1 | 唐三藏 | 66 || 2 | 孙悟空 | 87 || 3 | 猪悟能 | 100 || 4 | 曹孟德 | 77 || 5 | 刘玄德 | 55 || 6 | 孙权 | 88 || 7 | 宋公明 | 40 |+----+-----------+--------------+
• 表达式包含多个字段SELECT id, name, chinese + math + english FROM exam_result;+----+--------- -+--------------------------+| id | name | chinese + math + english |+----+-----------+--------------------------+| 1 | 唐三藏 | 221 || 2 | 孙悟空 | 242 || 3 | 猪悟能 | 276 || 4 | 曹孟德 | 233 || 5 | 刘玄德 | 185 || 6 | 孙权 | 221 || 7 | 宋公明 | 170 |+----+-----------+--------------------------+7 rows in set (0.00 sec)

3.为查询结果指定别名

• 语法：SELECT column [AS] alias_name [...] FROM table_name;
• 示例：SELECT id, name, chinese + math + english 总分 FROM exam_result;+----+-----------+--------+| id | name | 总分 |+----+-----------+--------+| 1 | 唐三藏 | 221 || 2 | 孙悟空 | 242 || 3 | 猪悟能 | 276 || 4 | 曹孟德 | 233 || 5 | 刘玄德 | 185 || 6 | 孙权 | 221 || 7 | 宋公明 | 170 |+----+-----------+--------+7 rows in set (0.00 sec)

4.结果去重

// 98分重复了
SELECT math FROM exam_result;
+--------+
| math   |
+--------+
| 98     |
| 78     |
| 98     |
| 84     |
| 85     |
| 73     |
| 65     |
+--------+
7 rows in set (0.00 sec)

// 去重结果
SELECT DISTINCT math FROM exam_result;
+--------+
| math   |
+--------+
| 98     |
| 78     |
| 84     |
| 85     |
| 73     |
| 65     |
+--------+
6 rows in set (0.00 sec)

3.WHERE条件

1.比较运算符

2.逻辑运算符

3.示例

• 英语不及格的同学及英语成绩 ( < 60 )SELECT name, english FROM exam_result WHERE english < 60;+-----------+---------+| name | english |+-----------+---------+| 唐三藏 | 56 || 刘玄德 | 45 || 宋公明 | 30 |+-----------+---------+3 rows in set (0.01 sec)
• 语文成绩在 [80, 90] 分的同学及语文成绩# 使用 AND 进行条件连接SELECT name, chinese FROM exam_result WHERE chinese >= 80 AND chinese <= 90;+-----------+---------+| name | chinese |+-----------+---------+| 孙悟空 | 87 || 猪悟能 | 88 || 曹孟德 | 82 |+-----------+---------+3 rows in set (0.00 sec)# 使用 BETWEEN ... AND ... 条件SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 80 AND 90;+-----------+---------+| name | chinese |+-----------+---------+| 孙悟空 | 87 || 猪悟能 | 88 || 曹孟德 | 82 |+-----------+---------+3 rows in set (0.00 sec)
• 数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩# 使用 OR 进行条件连接SELECT name, math FROM exam_result WHERE math = 58 OR math = 59 OR math = 98 OR math = 99;+-----------+--------+| name | math |+-----------+--------+| 唐三藏 | 98 || 猪悟能 | 98 |+-----------+--------+2 rows in set (0.01 sec)# 使用 IN 条件SELECT name, math FROM exam_result WHERE math IN (58, 59, 98, 99);+-----------+--------+| name | math |+-----------+--------+| 唐三藏 | 98 || 猪悟能 | 98 |+-----------+--------+2 rows in set (0.00 sec)
• 姓孙的同学及孙某同学# % 匹配任意多个（包括 0 个）任意字符SELECT name FROM exam_result WHERE name LIKE '孙%';+-----------+| name |+-----------+| 孙悟空 || 孙权 |+-----------+2 rows in set (0.00 sec)# _ 匹配严格的一个任意字符SELECT name FROM exam_result WHERE name LIKE '孙_';+--------+| name |+--------+| 孙权 |+--------+1 row in set (0.00 sec)
• 语文成绩好于英语成绩的同学# WHERE 条件中比较运算符两侧都是字段SELECT name, chinese, english FROM exam_result WHERE chinese > english;+-----------+---------+---------+| name | chinese | english |+-----------+---------+---------+| 唐三藏 | 67 | 56 || 孙悟空 | 87 | 77 || 曹孟德 | 82 | 67 || 刘玄德 | 55 | 45 || 宋公明 | 75 | 30 |+-----------+---------+---------+5 rows in set (0.00 sec)
• 总分在 200 分以下的同学# WHERE 条件中使用表达式# 别名不能用在 WHERE 条件中SELECT name, chinese + math + english 总分 FROM exam_result WHERE chinese + math + english < 200;+-----------+--------+| name | 总分 |+-----------+--------+| 刘玄德 | 185 || 宋公明 | 170 |+-----------+--------+2 rows in set (0.00 sec)
• 语文成绩 > 80 并且不姓孙的同学# AND 与 NOT 的使用SELECT name, chinese FROM exam_result WHERE chinese > 80 AND name NOT LIKE '孙%';+----+-----------+---------+--------+---------+| id | name | chinese | math | english |+----+-----------+---------+--------+---------+| 3 | 猪悟能 | 88 | 98 | 90 || 4 | 曹孟德 | 82 | 84 | 67 |+----+-----------+---------+--------+---------+2 rows in set (0.00 sec)
• 孙某同学，否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80# 综合性查询，括号()内的可以看成一整个条件SELECT name, chinese, math, english, chinese + math + english 总分 FROM exam_resultWHERE name LIKE '孙_' OR (chinese + math + english > 200 AND chinese < math AND english >80);+-----------+---------+--------+---------+--- ---+| name | chinese | math | english | 总分 |+-----------+---------+--------+---------+-------+| 猪悟能 | 88 | 98 | 90 | 276 || 孙权 | 70 | 73 | 78 | 221 |+-----------+---------+--------+---------+-------+2 rows in set (0.00 sec)
• NULL 的查询# 查询 students 表+-----+-------+-----------+-------+| id | sn | name | qq |+-----+-------+-----------+-------+| 100 | 10010 | 唐大师 | NULL || 101 | 10001 | 孙悟空 | 11111 || 103 | 20002 | 孙仲谋 | NULL || 104 | 20001 | 曹阿瞒 | NULL |+-----+-------+-----------+-------+4 rows in set (0.00 sec)# 查询 qq 号已知的同学姓名SELECT name, qq FROM students WHERE qq IS NOT NULL;+-----------+-------+| name | qq |+-----------+-------+| 孙悟空 | 11111 |+-----------+-------+1 row in set (0.00 sec)# NULL 和 NULL 的比较，= 和 <=> 的区别SELECT NULL = NULL, NULL = 1, NULL = 0;+-------------+----------+----------+| NULL = NULL | NULL = 1 | NULL = 0 |+-------------+----------+----------+| NULL | NULL | NULL |+-------------+----------+----------+1 row in set (0.00 sec)SELECT NULL <=> NULL, NULL <=> 1, NULL <=> 0;+---------------+------------+------------+| NULL <=> NULL | NULL <=> 1 | NULL <=> 0 |+---------------+------------+------------+| 1 | 0 | 0 |+---------------+------------+------------+1 row in set (0.00 sec)

4.结果排序

1.基本语法

-- ASC 为升序（从小到大）
-- DESC 为降序（从大到小）
-- 默认为 ASC
SELECT ... FROM table_name [WHERE ...]
ORDER BY column [ASC|DESC], [...];

• Tips - 升序：Ascending- 降序：Descending
• 注意：没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序

2.示例

• 同学及数学成绩，按数学成绩升序显示SELECT name, math FROM exam_result ORDER BY math;+-----------+--------+| name | math |+-----------+--------+| 宋公明 | 65 || 孙权 | 73 || 孙悟空 | 78 || 曹孟德 | 84 || 刘玄德 | 85 || 唐三藏 | 98 || 猪悟能 | 98 |+-----------+--------+7 rows in set (0.00 sec)
• 同学及 qq 号，按 qq 号排序显示# NULL 视为比任何值都小，升序出现在最上面SELECT name, qq FROM students ORDER BY qq;+-----------+-------+| name | qq |+-----------+-------+| 唐大师 | NULL || 孙仲谋 | NULL || 曹阿瞒 | NULL || 孙悟空 | 11111 |+-----------+-------+4 rows in set (0.00 sec)# NULL 视为比任何值都小，降序出现在最下面SELECT name, qq FROM students ORDER BY qq DESC;+-----------+-------+| name | qq |+-----------+-------+| 孙悟空 | 11111 || 唐大师 | NULL || 孙仲谋 | NULL || 曹阿瞒 | NULL |+-----------+-------+4 rows in set (0.00 sec)
• 查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示# 多字段排序，排序优先级随书写顺序SELECT name, math, english, chinese FROM exam_result ORDER BY math DESC, english, chinese;+-----------+--------+---------+---------+| name | math | english | chinese |+-----------+--------+---------+---------+| 唐三藏 | 98 | 56 | 67 || 猪悟能 | 98 | 90 | 88 || 刘玄德 | 85 | 45 | 55 || 曹孟德 | 84 | 67 | 82 || 孙悟空 | 78 | 77 | 87 || 孙权 | 73 | 78 | 70 || 宋公明 | 65 | 30 | 75 |+-----------+--------+---------+---------+7 rows in set (0.00 sec)
• 查询同学及总分，由高到低# ORDER BY 中可以使用表达式SELECT name, chinese + english + math FROM exam_result ORDER BY chinese + english + math DESC;+-----------+--------------------------+| name | chinese + english + math |+-----------+--------------------------+| 猪悟能 | 276 || 孙悟空 | 242 || 曹孟德 | 233 || 唐三藏 | 221 || 孙权 | 221 || 刘玄德 | 185 || 宋公明 | 170 |+-----------+--------------------------+7 rows in set (0.00 sec)# ORDER BY 子句中可以使用列别名SELECT name, chinese + english + math 总分 FROM exam_result ORDER BY 总分 DESC;+-----------+--------+| name | 总分 |+-----------+--------+| 猪悟能 | 276 || 孙悟空 | 242 || 曹孟德 | 233 || 唐三藏 | 221 || 孙权 | 221 || 刘玄德 | 185 || 宋公明 | 170 |+-----------+--------+7 rows in set (0.00 sec)
• 查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示# 结合 WHERE 子句 和 ORDER BY 子句SELECT name, math FROM exam_result WHERE name LIKE '孙%' OR name LIKE '曹%' ORDER BY math DESC;+-----------+--------+| name | math |+-----------+--------+| 曹孟德 | 84 || 孙悟空 | 78 || 孙权 | 73 |+-----------+--------+3 rows in set (0.00 sec)

5.筛选分页结果

• 语法：# 起始下标为 0# 从 0 开始，筛选 n 条结果SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;# 从 s 开始，筛选 n 条结果SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;# 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;
• 建议：对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死
• 示例：按 id 进行分页，每页 3 条记录，分别显示 第 1、2、3 页// 第 1 页SELECT id, name, math, english, chinese FROM exam_resultORDER BY id LIMIT 3 OFFSET 0;+----+-----------+--------+---------+---------+| id | name | math | english | chinese |+----+-----------+--------+---------+---------+| 1 | 唐三藏 | 98 | 56 | 67 || 2 | 孙悟空 | 78 | 77 | 87 || 3 | 猪悟能 | 98 | 90 | 88 |+----+-----------+--------+---------+---------+3 rows in set (0.02 sec)// 第 2 页SELECT id, name, math, english, chinese FROM exam_resultORDER BY id LIMIT 3 OFFSET 3;+----+-----------+--------+---------+---------+| id | name | math | english | chinese |+----+-----------+--------+---------+---------+| 4 | 曹孟德 | 84 | 67 | 82 || 5 | 刘玄德 | 85 | 45 | 55 || 6 | 孙权 | 73 | 78 | 70 |+----+-----------+--------+---------+---------+3 rows in set (0.00 sec)// 第 3 页，如果结果不足 3 个，不会有影响SELECT id, name, math, english, chinese FROM exam_resultORDER BY id LIMIT 3 OFFSET 6;+----+-----------+--------+---------+---------+| id | name | math | english | chinese |+----+-----------+--------+---------+---------+| 7 | 宋公明 | 65 | 30 | 75 |+----+-----------+--------+---------+---------+1 row in set (0.00 sec)