文章目录
数据查询语言 DQL(Data Query Language)
1、DQL统一格式
SELECT [ALL|DISTINCT] <目标列表达式> [,<目标列表达式>] …
FROM <表名或视图名>[, <表名或视图名> ] …
[ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ];
2、DQL语句执行顺序
由上不难看出,语法顺序为
SELECT[DISTINCT]
FROM
WHERE
GROUP BY
HAVING
UNION
ORDER BY
但到了实际执行时,为:
#说明:在执行过程中,每个步骤的结果都是一个虚拟表(VT)(ORDER BY除外),这个虚拟表将作为下一个执行步骤的输入
FROM # 执行笛卡尔乘积,生成虚拟表 VT1
WHERE # 对 VT1 应用 WHERE 筛选器,只有满足WHERE子句的行才被插入 VT2
GROUP BY # 按指定的一列或多列值分组,VT2 中值相等的为一组。得到虚拟表 VT3
# 如果没有分组,则之后的聚合函数将作用于查询结果;否则将作用于每一组
HAVING # 对VT3应用 HAVING 筛选器,只有满足 HAVING 子句的行才被插入 VT4
SELECT # 处理 VT4(如选列,执行聚集函数等),产生 VT5
DISTINCT # 将重复的行从 VT5 中移除,得到 VT6
UNION # 合并多个 SELECT 语句的结果,产生新的虚拟表 VT7
ORDER BY # 对 VT7 进行排序,得到虚拟游标 VC1
SELECT 语句
SELECT <目标列表达式>
<目标列表达式>可以为:列名(或列别名)、算术表达式、字符串常量、函数
1、查询指定列
SELECT 列名1,列名2,...
FROM 表名字 WHERE 限制条件;
mysql> select name, age from employee;
+------+------+
| name | age |
+------+------+
| Tom | 26 |
| Jack | 24 |
| Rose | 22 |
| Jim | 35 |
| Mary | 21 |
| Alex | 26 |
| Ken | 27 |
| Rick | 24 |
| Joe | 31 |
| Mike | 23 |
| Jobs | NULL |
| Tony | NULL |
+------+------+
12 rows in set (0.00 sec)
2、查询全部列
SELECT *
FROM 表名字 WHERE 限制条件;
mysql> select * from employee;
+----+------+------+--------+--------+--------+
| id | name | age | salary | phone | in_dpt |
+----+------+------+--------+--------+--------+
| 1 | Tom | 26 | 2500 | 119119 | dpt4 |
| 2 | Jack | 24 | 2500 | 120120 | dpt2 |
| 3 | Rose | 22 | 2800 | 114114 | dpt3 |
| 4 | Jim | 35 | 3000 | 100861 | dpt1 |
| 5 | Mary | 21 | 3000 | 100101 | dpt2 |
| 6 | Alex | 26 | 3000 | 123456 | dpt1 |
| 7 | Ken | 27 | 3500 | 654321 | dpt1 |
| 8 | Rick | 24 | 3500 | 987654 | dpt3 |
| 9 | Joe | 31 | 3600 | 110129 | dpt2 |
| 10 | Mike | 23 | 3400 | 110110 | dpt4 |
| 11 | Jobs | NULL | 3600 | 19283 | dpt2 |
| 12 | Tony | NULL | 3400 | 102938 | dpt3 |
+----+------+------+--------+--------+--------+
12 rows in set (0.00 sec
WHERE子句
查询条件 | 谓词 |
---|---|
比较运算符 | =,>,<,>=,<=,!=,<>,!>,!< |
范围查询 | BETWEEN AND,NOT BETWEEN AND |
逻辑运算符 | AND,OR,NOT |
确定集合 | IN,NOT IN |
匹配查询 | LIKE,NOT LIKE,RLIKE,NOT RLIKE |
空值 | IS NULL,IS NOT NULL |
1、比较运算符
符号 | 说明 |
---|---|
> | |
< | |
= | |
!=或<> | |
>= | |
<= |
mysql> select name, age from employee where age>25;
+------+------+
| name | age |
+------+------+
| Tom | 26 |
| Jim | 35 |
| Alex | 26 |
| Ken | 27 |
| Joe | 31 |
+------+------+
5 rows in set (0.00 sec)
2、BETWEEN
SELECT Sname # 查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名
FROM Student
WHERE Sage BETWEEN 20 AND 23;
2、AND 和 OR
符号 | 说明 |
---|---|
AND | |
OR |
mysql> select name, age from employee where age<30 and age>25;
+------+------+
| name | age |
+------+------+
| Tom | 26 |
| Alex | 26 |
| Ken | 27 |
+------+------+
3 rows in set (0.00 sec)
3、IN 和 NOT IN
mysql> SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt IN ('dpt3','dpt4');
+------+------+--------+--------+
| name | age | phone | in_dpt |
+------+------+--------+--------+
| Rose | 22 | 114114 | dpt3 |
| Rick | 24 | 987654 | dpt3 |
| Tony | NULL | 102938 | dpt3 |
| Tom | 26 | 119119 | dpt4 |
| Mike | 23 | 110110 | dpt4 |
+------+------+--------+--------+
5 rows in set (0.00 sec)
4、LIKE与通配符
符号 | 说明 |
---|---|
_ | 匹配一个字符 |
% | 匹配任意数量的字符 |
mysql> SELECT name,age,phone FROM employee WHERE phone LIKE '1101__';
+------+------+--------+
| name | age | phone |
+------+------+--------+
| Joe | 31 | 110129 |
| Mike | 23 | 110110 |
+------+------+--------+
2 rows in set (0.00 sec)
mysql> SELECT name,age,phone FROM employee WHERE name LIKE 'J%';
+------+------+--------+
| name | age | phone |
+------+------+--------+
| Jack | 24 | 120120 |
| Jim | 35 | 100861 |
| Joe | 31 | 110129 |
| Jobs | NULL | 19283 |
+------+------+--------+
4 rows in set (0.01 sec)
5、匹配模式与REGEXP(RLIKE)
找出以 “b” 开头的名字
mysql> SELECT * FROM pet WHERE name REGEXP '^b';
------------------------------------------------
为了找出以 “fy” 结尾的名字,使用 “$” 匹配名字的结尾:
mysql> SELECT * FROM pet WHERE name REGEXP 'fy$';
------------------------------------------------
为了找出包含正好 5 个字符的名字,使用 “^” 和 “$” 匹配名字的开始和结尾,和 5 个 “.” 实例在两者之间:
mysql> SELECT * FROM pet WHERE name REGEXP '^.....$';
------------------------------------------------
也可以使用 “{n}” 重复 n 次操作符, 重写前面的查询:
mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$';
------------------------------------------------
找出包含一个 “w” 的名字,使用以下查询:
mysql> SELECT * FROM pet WHERE name REGEXP 'w';
分组 GROUP BY
格式
SELECT column_name, function(column_name)
FROM table_name
WHERE [clause]
GROUP BY column_name;
例1:分组可用作去重
mysql> select in_dpt from employee group by in_dpt;
+--------+
| in_dpt |
+--------+
| dpt1 |
| dpt2 |
| dpt3 |
| dpt4 |
+--------+
4 rows in set (0.00 sec)
例2
mysql> select in_dpt,count(*)
mysql> from employee
mysql> group by in_dpt;
+--------+----------+
| in_dpt | count(*) |
+--------+----------+
| dpt1 | 3 |
| dpt2 | 4 |
| dpt3 | 3 |
| dpt4 | 2 |
+--------+----------+
4 rows in set (0.00 sec)
我们来分析下上面的执行过程,首先FROM,再GROUP BY,最后SELECT。分组得到的是例1的表,而count(*)返回被选中列的行数。综上,最后结果返回各部门的频数。
HAVING子句
在 SQL 中增加 HAVING 子句原因是,WHERE 子句中无法使用聚合函数。具体原因点击这里~
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000
HAVING子句与WHERE子句的区别——作用对象不同
- WHERE子句作用于基表或视图,从中选取满足条件的元组
- HAVING子句作用于组,从中选取满足条件的组
对结果排序 ORDER BY
标识 | 说明 |
---|---|
ASC | 升序(默认值) |
DESC | 降序 |
SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]];
mysql> SELECT name,age,salary,phone FROM employee ORDER BY salary DESC;
+------+------+--------+--------+
| name | age | salary | phone |
+------+------+--------+--------+
| Joe | 31 | 3600 | 110129 |
| Jobs | NULL | 3600 | 19283 |
| Ken | 27 | 3500 | 654321 |
| Rick | 24 | 3500 | 987654 |
| Mike | 23 | 3400 | 110110 |
| Tony | NULL | 3400 | 102938 |
| Jim | 35 | 3000 | 100861 |
| Mary | 21 | 3000 | 100101 |
| Alex | 26 | 3000 | 123456 |
| Rose | 22 | 2800 | 114114 |
| Tom | 26 | 2500 | 119119 |
| Jack | 24 | 2500 | 120120 |
+------+------+--------+--------+
12 rows in set (0.00 sec)
连接查询 JOIN1
JOIN 按照功能大致分为如下三类:
INNER JOIN(内连接, 或等值连接):获取两个表中字段匹配关系的记录。
LEFT JOIN(左连接):获取左表所有记录,即使右表没有对应匹配的记录。
RIGHT JOIN(右连接): 与 LEFT JOIN 相。
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a
INNER JOIN tcount_tbl b
ON a.runoob_author = b.runoob_author
不用JOIN连表
mysql> SELECT id,name,people_num
-> FROM employee,department
-> WHERE employee.in_dpt = department.dpt_name
-> ORDER BY id;
+----+------+------------+
| id | name | people_num |
+----+------+------------+
| 1 | Tom | 15 |
| 2 | Jack | 12 |
| 3 | Rose | 10 |
| 4 | Jim | 11 |
| 5 | Mary | 12 |
| 6 | Alex | 11 |
| 7 | Ken | 11 |
| 8 | Rick | 10 |
| 9 | Joe | 12 |
| 10 | Mike | 15 |
| 11 | Jobs | 12 |
| 12 | Tony | 10 |
+----+------+------------+
12 rows in set (0.01 sec)
这条语句查询出的是,各员工所在部门的人数,其中员工的 id 和 name 来自 employee 表,people_num 来自 department 表
另一个连接语句格式是使用 JOIN ON 语法,刚才的语句等同于:
SELECT id,name,people_num
FROM employee JOIN department
ON employee.in_dpt = department.dpt_name
ORDER BY id;