文章目录
单表查询语句
语法
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[SQL_CACHE | SQL_NO_CACHE]
select_expr [, select_expr ...]
[FROM table_references
[PARTITION partition_list]
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[FOR UPDATE | LOCK IN SHARE MODE]]
说明
字段显示可以使用别名
col1 AS alias1,col2 AS alias2,…
WHERE子句: 指明过滤条件以实现“选择”功能
过滤条件:
布尔型表达式
算术操作符:+,-,*,/,%
比较操作符:
=,<=>(相等或都为空字符串)：相等
<>,!=(非标准SQL)：不等
>,>=,<,<=
BETWEEN min_num AND max_num：在两个取值之间的
in(element1,element2,......)：在指定的范围内取值
IS NULL：是否为空
IS NOT NULL：是否不为空
DISTINCT去除重复值
范例:SELECT DISTINCT gender FROM students;
LIKE
%: 任意长度的任意字符
_: 任意单个字符
RLIKE: 正则表达式，索引失效，不建议使用
REGECXP: 匹配字符串可用正则表达式书写模式，同上
逻辑操作符:NOT,AND,OR,XOR
GROUP子句: 根据指定的条件把查询结果进行“分组”以用于做“聚合”运算;常见聚合函数:
avg()平均数,max()最大值,min()最小值,count()计数,sum()总和
HAVING：HAVING字句是过滤分组之后的各种数据，一般跟在group by之后
PS: 一旦做group by分组，那么select语句后只查询分组的字段或者聚合函数；添加其他的没有意义
范例：select gender,avg(age),max(age),min(age) from students group by gender;
ORDER BY: 根据指定的字段对查询结果进行排序
ASC：升序
DESC：降序
[LIMIT {[offset,] row_count]: 对查询的结果进行输出行数数量限制
offset：表示开始的索引值，默认为0
row_count：表示取值数量
对查询结果中的数据请求施加“锁”
FOR UPDATE: 写锁，独占或排它锁，只有一个读和写操作
LOCK IN SHARE MODE:读锁，共享锁，同时多个读操作
实例:简单查询
-- 在MySQL5.7中password()函数取消
07:37:05 (root@localhost) [hellodb]> select password('hooper');
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
07:37:24 (root@localhost) [hellodb]> select md5('hooper');
+----------------------------------+
| md5('hooper') |
+----------------------------------+
| c7180702fdeaa1dcc8cc5a91ab26c6f6 |
+----------------------------------+
1 row in set (0.00 sec)
-- 查询所有学生表的信息
12:50:43 (root@(none)) [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | <null> |
| 7 | Xi Ren | 19 | F | 3 | <null> |
| 8 | Lin Daiyu | 17 | F | 7 | <null> |
| 9 | Ren Yingying | 20 | F | 6 | <null> |
| 10 | Yue Lingshan | 19 | F | 3 | <null> |
| 11 | Yuan Chengzhi | 23 | M | 6 | <null> |
| 12 | Wen Qingqing | 19 | F | 1 | <null> |
| 13 | Tian Boguang | 33 | M | 2 | <null> |
| 14 | Lu Wushuang | 17 | F | 3 | <null> |
| 15 | Duan Yu | 19 | M | 4 | <null> |
| 16 | Xu Zhu | 21 | M | 1 | <null> |
| 17 | Lin Chong | 25 | M | 4 | <null> |
| 18 | Hua Rong | 23 | M | 7 | <null> |
| 19 | Xue Baochai | 18 | F | 6 | <null> |
| 20 | Diao Chan | 19 | F | 7 | <null> |
| 21 | Huang Yueying | 22 | F | 6 | <null> |
| 22 | Xiao Qiao | 20 | F | 1 | <null> |
| 23 | Ma Chao | 23 | M | 4 | <null> |
| 24 | Xu Xian | 27 | M | <null> | <null> |
| 25 | Sun Dasheng | 100 | M | <null> | <null> |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set
Time: 0.010s
-- 查询stuid，name，gender字段，并自定义别名为学员ID，姓名，性别
11:30:46 (root@(none)) [hellodb]> select stuid 学员ID,name as 姓名,gender 性别 from students;
+--------+---------------+------+
| 学员ID | 姓名 | 性别 |
+--------+---------------+------+
| 1 | Shi Zhongyu | M |
| 2 | Shi Potian | M |
| 3 | Xie Yanke | M |
| 4 | Ding Dian | M |
| 5 | Yu Yutong | M |
| 6 | Shi Qing | M |
| 7 | Xi Ren | F |
| 8 | Lin Daiyu | F |
| 9 | Ren Yingying | F |
| 10 | Yue Lingshan | F |
| 11 | Yuan Chengzhi | M |
| 12 | Wen Qingqing | F |
| 13 | Tian Boguang | M |
| 14 | Lu Wushuang | F |
| 15 | Duan Yu | M |
| 16 | Xu Zhu | M |
| 17 | Lin Chong | M |
| 18 | Hua Rong | M |
| 19 | Xue Baochai | F |
| 20 | Diao Chan | F |
| 21 | Huang Yueying | F |
| 22 | Xiao Qiao | F |
| 23 | Ma Chao | M |
| 24 | Xu Xian | M |
| 25 | Sun Dasheng | M |
+--------+---------------+------+
25 rows in set
Time: 0.010s
-- 查询所有女生的名字
12:49:38 (root@(none)) [hellodb]> select name from students where gender = 'F';
+---------------+
| name |
+---------------+
| Xi Ren |
| Lin Daiyu |
| Ren Yingying |
| Yue Lingshan |
| Wen Qingqing |
| Lu Wushuang |
| Xue Baochai |
| Diao Chan |
| Huang Yueying |
| Xiao Qiao |
+---------------+
10 rows in set
Time: 0.009s
-- 查询年龄小于18岁的学生
13:03:47 (root@(none)) [hellodb]> select stuid,name,age from students where age <=18;
+-------+-------------+-----+
| stuid | name | age |
+-------+-------------+-----+
| 8 | Lin Daiyu | 17 |
| 14 | Lu Wushuang | 17 |
| 19 | Xue Baochai | 18 |
+-------+-------------+-----+
3 rows in set
Time: 0.009s
-- 查询年龄在18到28之间的学生
13:08:25 (root@(none)) [hellodb]> select stuid,name,age from students where age >=18 and age <=28;
+-------+---------------+-----+
| stuid | name | age |
+-------+---------------+-----+
| 1 | Shi Zhongyu | 22 |
| 2 | Shi Potian | 22 |
| 5 | Yu Yutong | 26 |
| 7 | Xi Ren | 19 |
| 9 | Ren Yingying | 20 |
| 10 | Yue Lingshan | 19 |
| 11 | Yuan Chengzhi | 23 |
| 12 | Wen Qingqing | 19 |
| 15 | Duan Yu | 19 |
| 16 | Xu Zhu | 21 |
| 17 | Lin Chong | 25 |
| 18 | Hua Rong | 23 |
| 19 | Xue Baochai | 18 |
| 20 | Diao Chan | 19 |
| 21 | Huang Yueying | 22 |
| 22 | Xiao Qiao | 20 |
| 23 | Ma Chao | 23 |
| 24 | Xu Xian | 27 |
+-------+---------------+-----+
18 rows in set
Time: 0.010s
13:09:11 (root@(none)) [hellodb]> select stuid,name,age from students where age between 18 and 28;
+-------+---------------+-----+
| stuid | name | age |
+-------+---------------+-----+
| 1 | Shi Zhongyu | 22 |
| 2 | Shi Potian | 22 |
| 5 | Yu Yutong | 26 |
| 7 | Xi Ren | 19 |
| 9 | Ren Yingying | 20 |
| 10 | Yue Lingshan | 19 |
| 11 | Yuan Chengzhi | 23 |
| 12 | Wen Qingqing | 19 |
| 15 | Duan Yu | 19 |
| 16 | Xu Zhu | 21 |
| 17 | Lin Chong | 25 |
| 18 | Hua Rong | 23 |
| 19 | Xue Baochai | 18 |
| 20 | Diao Chan | 19 |
| 21 | Huang Yueying | 22 |
| 22 | Xiao Qiao | 20 |
| 23 | Ma Chao | 23 |
| 24 | Xu Xian | 27 |
+-------+---------------+-----+
18 rows in set
Time: 0.009s
-- 查询年龄小于18年的学生，年龄上增加10
13:15:19 (root@(none)) [hellodb]> select stuid,name,age+10 from students where age < 18;
+-------+-------------+--------+
| stuid | name | age+10 |
+-------+-------------+--------+
| 8 | Lin Daiyu | 27 |
| 14 | Lu Wushuang | 27 |
+-------+-------------+--------+
2 rows in set
Time: 0.009s
-- 直接当成计算器使用
13:17:35 (root@(none)) [hellodb]> select 10*2 as result;
+--------+
| result |
+--------+
| 20 |
+--------+
1 row in set
Time: 0.009s
-- 查询年龄在22，46，100的学生
13:19:45 (root@(none)) [hellodb]> select stuid,name,age from students where age in (22,46,100);
+-------+---------------+-----+
| stuid | name | age |
+-------+---------------+-----+
| 1 | Shi Zhongyu | 22 |
| 2 | Shi Potian | 22 |
| 6 | Shi Qing | 46 |
| 21 | Huang Yueying | 22 |
| 25 | Sun Dasheng | 100 |
+-------+---------------+-----+
5 rows in set
Time: 0.009s
-- 查询classid为空值的学生
13:23:50 (root@(none)) [hellodb]> select * from students where classid is null;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 24 | Xu Xian | 27 | M | <null> | <null> |
| 25 | Sun Dasheng | 100 | M | <null> | <null> |
+-------+-------------+-----+--------+---------+-----------+
2 rows in set
Time: 0.009s
13:25:40 (root@(none)) [hellodb]> select * from students where classid <=> null;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 24 | Xu Xian | 27 | M | <null> | <null> |
| 25 | Sun Dasheng | 100 | M | <null> | <null> |
+-------+-------------+-----+--------+---------+-----------+
2 rows in set
Time: 0.009s
-- 查询ClassID为空或者等于6的学生
13:26:52 (root@(none)) [hellodb]> select * from students where classid <=> null or classid = 6;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 9 | Ren Yingying | 20 | F | 6 | <null> |
| 11 | Yuan Chengzhi | 23 | M | 6 | <null> |
| 19 | Xue Baochai | 18 | F | 6 | <null> |
| 21 | Huang Yueying | 22 | F | 6 | <null> |
| 24 | Xu Xian | 27 | M | <null> | <null> |
| 25 | Sun Dasheng | 100 | M | <null> | <null> |
+-------+---------------+-----+--------+---------+-----------+
6 rows in set
Time: 0.010s
-- 查询teacherid不为空的学生
14:40:04 (root@(none)) [hellodb]> select * from students where teacherid is not null;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
+-------+-------------+-----+--------+---------+-----------+
5 rows in set
Time: 0.016s
-- 查询gender都有哪些选项（去重功能）
14:41:05 (root@(none)) [hellodb]> select distinct gender from students;
+--------+
| gender |
+--------+
| M |
| F |
+--------+
2 rows in set
Time: 0.009s
-- 查询所有包含“xu ”的学生
14:44:11 (root@(none)) [hellodb]> select * from students where name like 'xu %';
+-------+---------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------+-----+--------+---------+-----------+
| 16 | Xu Zhu | 21 | M | 1 | <null> |
| 24 | Xu Xian | 27 | M | <null> | <null> |
+-------+---------+-----+--------+---------+-----------+
2 rows in set
Time: 0.009s
-- 查询以shi开头的学生，使用正则表达式
15:23:50 (root@(none)) [hellodb]> select * from students where name rlike '^shi';
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 6 | Shi Qing | 46 | M | 5 | <null> |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set
Time: 0.009s
实例:有关分组练习
-- 查询男、女生的平均年龄，最大年龄及最小年龄
15:28:24 (root@(none)) [hellodb]> select gender,avg(age),max(age),min(age) from students group by gender;
+--------+----------+----------+----------+
| gender | avg(age) | max(age) | min(age) |
+--------+----------+----------+----------+
| F | 19.0000 | 22 | 17 |
| M | 33.0000 | 100 | 19 |
+--------+----------+----------+----------+
2 rows in set
Time: 0.009s
15:29:48 (root@(none)) [hellodb]> select gender 年龄,avg(age) 平均年龄,max(age) 最大年龄,min(age) 最小年龄 from students group by gender;
+------+----------+----------+----------+
| 年龄 | 平均年龄 | 最大年龄 | 最小年龄 |
+------+----------+----------+----------+
| F | 19.0000 | 22 | 17 |
| M | 33.0000 | 100 | 19 |
+------+----------+----------+----------+
2 rows in set
Time: 0.009s
-- 查询每个班级男、女生平均年龄
-- 注意⚠️：嵌套分组的时候，分组是有顺序的；
-- group by classid,gender：先按照classid分组，然后再按照gender分组取平均值
15:45:10 (root@(none)) [hellodb]> select classid,gender,avg(age) from students group by classid,gender;
+---------+--------+----------+
| classid | gender | avg(age) |
+---------+--------+----------+
| <null> | M | 63.5000 |
| 1 | F | 19.5000 |
| 1 | M | 21.5000 |
| 2 | M | 36.0000 |
| 3 | F | 18.3333 |
| 3 | M | 26.0000 |
| 4 | M | 24.7500 |
| 5 | M | 46.0000 |
| 6 | F | 20.0000 |
| 6 | M | 23.0000 |
| 7 | F | 18.0000 |
| 7 | M | 23.0000 |
+---------+--------+----------+
12 rows in set
Time: 0.009s
-- group by gender,classid：先按照gender分组，然后再按照classid分组取平均值
15:47:00 (root@(none)) [hellodb]> select classid,gender,avg(age) from students group by gender,classid;
+---------+--------+----------+
| classid | gender | avg(age) |
+---------+--------+----------+
| 1 | F | 19.5000 |
| 3 | F | 18.3333 |
| 6 | F | 20.0000 |
| 7 | F | 18.0000 |
| <null> | M | 63.5000 |
| 1 | M | 21.5000 |
| 2 | M | 36.0000 |
| 3 | M | 26.0000 |
| 4 | M | 24.7500 |
| 5 | M | 46.0000 |
| 6 | M | 23.0000 |
| 7 | M | 23.0000 |
+---------+--------+----------+
12 rows in set
Time: 0.009s
-- 查询每个班级的平均年龄，排除没有班级的学生
-- 当对分组查询产生的结果后，再进行过滤查询时，不能使用where子句，而是需要使用having
15:54:50 (root@(none)) [hellodb]> select classid,avg(age) from students group by classid having classid is not null;
+---------+----------+
| classid | avg(age) |
+---------+----------+
| 1 | 20.5000 |
| 2 | 36.0000 |
| 3 | 20.2500 |
| 4 | 24.7500 |
| 5 | 46.0000 |
| 6 | 20.7500 |
| 7 | 19.6667 |
+---------+----------+
7 rows in set
Time: 0.009s
16:04:12 (root@(none)) [hellodb]> select classid,avg(age) from students where classid is not null group by classid;
+---------+----------+
| classid | avg(age) |
+---------+----------+
| 1 | 20.5000 |
| 2 | 36.0000 |
| 3 | 20.2500 |
| 4 | 24.7500 |
| 5 | 46.0000 |
| 6 | 20.7500 |
| 7 | 19.6667 |
+---------+----------+
7 rows in set
Time: 0.009s
-- 查询班级年纪最小的学生
16:11:23 (root@(none)) [hellodb]> select * from students where (age in (select min(age) from students));
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 8 | Lin Daiyu | 17 | F | 7 | <null> |
| 14 | Lu Wushuang | 17 | F | 3 | <null> |
+-------+-------------+-----+--------+---------+-----------+
2 rows in set
Time: 0.010s
实例:有关排序的练习
-- 根据年龄正序跟倒序查询学生信息
16:16:30 (root@(none)) [hellodb]> select * from students order by age;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 14 | Lu Wushuang | 17 | F | 3 | <null> |
| 8 | Lin Daiyu | 17 | F | 7 | <null> |
| 19 | Xue Baochai | 18 | F | 6 | <null> |
| 20 | Diao Chan | 19 | F | 7 | <null> |
| 15 | Duan Yu | 19 | M | 4 | <null> |
| 12 | Wen Qingqing | 19 | F | 1 | <null> |
| 7 | Xi Ren | 19 | F | 3 | <null> |
| 10 | Yue Lingshan | 19 | F | 3 | <null> |
| 22 | Xiao Qiao | 20 | F | 1 | <null> |
| 9 | Ren Yingying | 20 | F | 6 | <null> |
| 16 | Xu Zhu | 21 | M | 1 | <null> |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 21 | Huang Yueying | 22 | F | 6 | <null> |
| 11 | Yuan Chengzhi | 23 | M | 6 | <null> |
| 23 | Ma Chao | 23 | M | 4 | <null> |
| 18 | Hua Rong | 23 | M | 7 | <null> |
| 17 | Lin Chong | 25 | M | 4 | <null> |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 24 | Xu Xian | 27 | M | <null> | <null> |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 13 | Tian Boguang | 33 | M | 2 | <null> |
| 6 | Shi Qing | 46 | M | 5 | <null> |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 25 | Sun Dasheng | 100 | M | <null> | <null> |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set
Time: 0.010s
16:16:41 (root@(none)) [hellodb]> select * from students order by age desc;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 25 | Sun Dasheng | 100 | M | <null> | <null> |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 6 | Shi Qing | 46 | M | 5 | <null> |
| 13 | Tian Boguang | 33 | M | 2 | <null> |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 24 | Xu Xian | 27 | M | <null> | <null> |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 17 | Lin Chong | 25 | M | 4 | <null> |
| 23 | Ma Chao | 23 | M | 4 | <null> |
| 11 | Yuan Chengzhi | 23 | M | 6 | <null> |
| 18 | Hua Rong | 23 | M | 7 | <null> |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 21 | Huang Yueying | 22 | F | 6 | <null> |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 16 | Xu Zhu | 21 | M | 1 | <null> |
| 22 | Xiao Qiao | 20 | F | 1 | <null> |
| 9 | Ren Yingying | 20 | F | 6 | <null> |
| 15 | Duan Yu | 19 | M | 4 | <null> |
| 12 | Wen Qingqing | 19 | F | 1 | <null> |
| 20 | Diao Chan | 19 | F | 7 | <null> |
| 10 | Yue Lingshan | 19 | F | 3 | <null> |
| 7 | Xi Ren | 19 | F | 3 | <null> |
| 19 | Xue Baochai | 18 | F | 6 | <null> |
| 14 | Lu Wushuang | 17 | F | 3 | <null> |
| 8 | Lin Daiyu | 17 | F | 7 | <null> |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set
Time: 0.010s
-- 根据班级进行正序排序，但将空值排到最后
16:21:08 (root@(none)) [hellodb]> select * from students order by -classid desc;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 22 | Xiao Qiao | 20 | F | 1 | <null> |
| 16 | Xu Zhu | 21 | M | 1 | <null> |
| 12 | Wen Qingqing | 19 | F | 1 | <null> |
| 13 | Tian Boguang | 33 | M | 2 | <null> |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 7 | Xi Ren | 19 | F | 3 | <null> |
| 10 | Yue Lingshan | 19 | F | 3 | <null> |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 14 | Lu Wushuang | 17 | F | 3 | <null> |
| 15 | Duan Yu | 19 | M | 4 | <null> |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 17 | Lin Chong | 25 | M | 4 | <null> |
| 23 | Ma Chao | 23 | M | 4 | <null> |
| 6 | Shi Qing | 46 | M | 5 | <null> |
| 9 | Ren Yingying | 20 | F | 6 | <null> |
| 11 | Yuan Chengzhi | 23 | M | 6 | <null> |
| 19 | Xue Baochai | 18 | F | 6 | <null> |
| 21 | Huang Yueying | 22 | F | 6 | <null> |
| 8 | Lin Daiyu | 17 | F | 7 | <null> |
| 20 | Diao Chan | 19 | F | 7 | <null> |
| 18 | Hua Rong | 23 | M | 7 | <null> |
| 24 | Xu Xian | 27 | M | <null> | <null> |
| 25 | Sun Dasheng | 100 | M | <null> | <null> |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set
Time: 0.010s
-- 多列排序：按照性别及年龄的倒序查询
16:24:52 (root@(none)) [hellodb]> select * from students order by gender,age desc;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 21 | Huang Yueying | 22 | F | 6 | <null> |
| 22 | Xiao Qiao | 20 | F | 1 | <null> |
| 9 | Ren Yingying | 20 | F | 6 | <null> |
| 7 | Xi Ren | 19 | F | 3 | <null> |
| 10 | Yue Lingshan | 19 | F | 3 | <null> |
| 20 | Diao Chan | 19 | F | 7 | <null> |
| 12 | Wen Qingqing | 19 | F | 1 | <null> |
| 19 | Xue Baochai | 18 | F | 6 | <null> |
| 8 | Lin Daiyu | 17 | F | 7 | <null> |
| 14 | Lu Wushuang | 17 | F | 3 | <null> |
| 25 | Sun Dasheng | 100 | M | <null> | <null> |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 6 | Shi Qing | 46 | M | 5 | <null> |
| 13 | Tian Boguang | 33 | M | 2 | <null> |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 24 | Xu Xian | 27 | M | <null> | <null> |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 17 | Lin Chong | 25 | M | 4 | <null> |
| 18 | Hua Rong | 23 | M | 7 | <null> |
| 11 | Yuan Chengzhi | 23 | M | 6 | <null> |
| 23 | Ma Chao | 23 | M | 4 | <null> |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 16 | Xu Zhu | 21 | M | 1 | <null> |
| 15 | Duan Yu | 19 | M | 4 | <null> |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set
Time: 0.010s
-- 查询年龄最小的前10名
16:35:09 (root@(none)) [hellodb]> select * from students order by age limit 10;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 14 | Lu Wushuang | 17 | F | 3 | <null> |
| 8 | Lin Daiyu | 17 | F | 7 | <null> |
| 19 | Xue Baochai | 18 | F | 6 | <null> |
| 20 | Diao Chan | 19 | F | 7 | <null> |
| 12 | Wen Qingqing | 19 | F | 1 | <null> |
| 15 | Duan Yu | 19 | M | 4 | <null> |
| 7 | Xi Ren | 19 | F | 3 | <null> |
| 10 | Yue Lingshan | 19 | F | 3 | <null> |
| 22 | Xiao Qiao | 20 | F | 1 | <null> |
| 9 | Ren Yingying | 20 | F | 6 | <null> |
+-------+--------------+-----+--------+---------+-----------+
10 rows in set
Time: 0.009s
-- 查询年龄最小的前3到5名，由于有多个19岁的学生，所以年龄是19的学生是随机的
16:37:35 (root@(none)) [hellodb]> select * from students order by age limit 2,3;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 19 | Xue Baochai | 18 | F | 6 | <null> |
| 12 | Wen Qingqing | 19 | F | 1 | <null> |
| 7 | Xi Ren | 19 | F | 3 | <null> |
+-------+--------------+-----+--------+---------+-----------+
3 rows in set
Time: 0.009s
-- 像这样的结果，并非是我们想要的，所以还需要再加上限制排序的语句做严格限制
17:13:00 (root@(none)) [hellodb]> select * from students order by age,stuid limit 2,3;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 19 | Xue Baochai | 18 | F | 6 | <null> |
| 7 | Xi Ren | 19 | F | 3 | <null> |
| 10 | Yue Lingshan | 19 | F | 3 | <null> |
+-------+--------------+-----+--------+---------+-----------+
3 rows in set
Time: 0.009s
实例:SQL注入攻击
-- 实例准备
14:48:10 (root@(none)) [hellodb]> create table user (id int primary key auto_increment,username varchar(30) not null,password varchar(50));
Query OK, 0 rows affected
Time: 0.177s
14:49:55 (root@(none)) [hellodb]> select md5("hooper");
+----------------------------------+
| md5("hooper") |
+----------------------------------+
| c7180702fdeaa1dcc8cc5a91ab26c6f6 |
+----------------------------------+
1 row in set
Time: 0.009s
14:50:53 (root@(none)) [hellodb]> insert into user (username,password) values('hooper','c7180702fdeaa1dcc8cc5a91ab26c6f6');
Query OK, 1 row affected
Time: 0.050s
14:51:02 (root@(none)) [hellodb]> select md5("admin");
+----------------------------------+
| md5("admin") |
+----------------------------------+
| 21232f297a57a5a743894a0e4a801fc3 |
+----------------------------------+
1 row in set
Time: 0.008s
14:51:25 (root@(none)) [hellodb]> insert into user (username,password) values('admin','21232f297a57a5a743894a0e4a801fc3');
Query OK, 1 row affected
Time: 0.031s
14:51:44 (root@(none)) [hellodb]> select md5("ann");
+----------------------------------+
| md5("ann") |
+----------------------------------+
| 7e0d7f8a5d96c24ffcc840f31bce72b2 |
+----------------------------------+
1 row in set
Time: 0.008s
14:52:01 (root@(none)) [hellodb]> insert into user (username,password) values('ann','7e0d7f8a5d96c24ffcc840f31bce72b2');
Query OK, 1 row affected
Time: 0.037s
14:52:13 (root@(none)) [hellodb]> select * from `user`;
+----+----------+----------------------------------+
| id | username | password |
+----+----------+----------------------------------+
| 1 | hooper | c7180702fdeaa1dcc8cc5a91ab26c6f6 |
| 2 | admin | 21232f297a57a5a743894a0e4a801fc3 |
| 3 | ann | 7e0d7f8a5d96c24ffcc840f31bce72b2 |
+----+----------+----------------------------------+
3 rows in set
Time: 0.009s
-- 用户通过在登录界面上输入用户名及密码后，判断是否登录成功
-- 查询结果有记录，则表示用户名及密码正确，登录成功
15:02:37 (root@(none)) [hellodb]> select * from user where username = 'admin' and password = '21232f297a57a5a743894a0e4a801fc3';
+----+----------+----------------------------------+
| id | username | password |
+----+----------+----------------------------------+
| 2 | admin | 21232f297a57a5a743894a0e4a801fc3 |
+----+----------+----------------------------------+
1 row in set
Time: 0.009s
-- 查询无结果，则表示用户名及密码错误，登录失败
15:04:42 (root@(none)) [hellodb]> select * from user where username = 'admin' and password = '212801fc3';
+----+----------+----------+
| id | username | password |
+----+----------+----------+
0 rows in set
Time: 0.009s
-- SQL注入攻击
-- 黑客构建一个特殊的密码：' or '1'='1
15:07:42 (root@(none)) [hellodb]> select * from user where username = 'admin' and password = '' or '1'='1';
+----+----------+----------------------------------+
| id | username | password |
+----+----------+----------------------------------+
| 1 | hooper | c7180702fdeaa1dcc8cc5a91ab26c6f6 |
| 2 | admin | 21232f297a57a5a743894a0e4a801fc3 |
| 3 | ann | 7e0d7f8a5d96c24ffcc840f31bce72b2 |
+----+----------+----------------------------------+
3 rows in set
Time: 0.009s
-- ' or 1=1
15:08:09 (root@(none)) [hellodb]> select * from user where username = 'admin' and password = '' or 1=1;
+----+----------+----------------------------------+
| id | username | password |
+----+----------+----------------------------------+
| 1 | hooper | c7180702fdeaa1dcc8cc5a91ab26c6f6 |
| 2 | admin | 21232f297a57a5a743894a0e4a801fc3 |
| 3 | ann | 7e0d7f8a5d96c24ffcc840f31bce72b2 |
+----+----------+----------------------------------+
3 rows in set
Time: 0.009s
-- 黑客构建一个特殊的用户名：admin'; --
15:10:47 (root@(none)) [hellodb]> select * from user where username = 'admin'; -- ' and password = 'abc';
+----+----------+----------------------------------+
| id | username | password |
+----+----------+----------------------------------+
| 2 | admin | 21232f297a57a5a743894a0e4a801fc3 |
+----+----------+----------------------------------+
1 row in set
Time: 0.009s
-- 这样黑客就以管理员的权限登录到网站上了
多表查询语句
多表查询，即查询结果来自于多张表
- 子查询：在SQL语句嵌套着查询语句，性能差，基于某语句的查询结果再次进行查询
- 联合查询：
UNION- 交叉连接：笛卡尔乘积，cross join
- 内连接：
等值连接：让表之间的字段以"等值"建立连接关系
不等值连接：
自然连接：去掉重复列的等值连接- 外连接：
左外连接：FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
右外连接：FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col- 自连接：本表与本表进行连接查询
子查询
常用在WHERE子句中的子查询
- 用于比较表达式中的子查询：子查询仅能返回单个值
实例：
-- 查询大于学生平均年龄的学生
10:53:47 (root@(none)) [hellodb]> select name,age from students where age>(select avg(age) from students);
+--------------+-----+
| name | age |
+--------------+-----+
| Xie Yanke | 53 |
| Ding Dian | 32 |
| Shi Qing | 46 |
| Tian Boguang | 33 |
| Sun Dasheng | 100 |
+--------------+-----+
5 rows in set
Time: 0.009s
10:53:53 (root@(none)) [hellodb]> select avg(age) from students;
+----------+
| avg(age) |
+----------+
| 27.4000 |
+----------+
1 row in set
Time: 0.009s
-- 查询大于老师平均年龄的学生
11:09:25 (root@(none)) [hellodb]> select name,age from students where age>(select avg(age) from teachers);
+-------------+-----+
| name | age |
+-------------+-----+
| Sun Dasheng | 100 |
+-------------+-----+
1 row in set
Time: 0.009s
11:09:35 (root@(none)) [hellodb]> select avg(age) from teachers;
+----------+
| avg(age) |
+----------+
| 77.2500 |
+----------+
1 row in set
Time: 0.008s
注意⚠️：
子查询的查询结果，必须是单一值
查询跟子查询可以是同一张表，也可以是不同表
- 用于in中的子查询：子查询应用单独查询并返回一个或多个值重新构成列表
实例：
-- 查询学生跟老师是同岁的学生
11:14:06 (root@(none)) [hellodb]> select name,age from students where age in (select age from teachers);
+------+-----+
| name | age |
+------+-----+
0 rows in set
Time: 0.009s
11:15:43 (root@(none)) [hellodb]> select age from teachers;
+-----+
| age |
+-----+
| 45 |
| 94 |
| 77 |
| 93 |
+-----+
4 rows in set
Time: 0.009s
11:15:49 (root@(none)) [hellodb]> select age from students;
+-----+
| age |
+-----+
| 22 |
| 22 |
| 53 |
| 32 |
| 26 |
| 46 |
| 19 |
| 17 |
| 20 |
| 19 |
| 23 |
| 19 |
| 33 |
| 17 |
| 19 |
| 21 |
| 25 |
| 23 |
| 18 |
| 19 |
| 22 |
| 20 |
| 23 |
| 27 |
| 100 |
+-----+
25 rows in set
Time: 0.009s
- 用于
EXISTS和NOT EXISTS
EXISTS（包括）NOT EXISTS子句的返回值是一个bool值；
EXISTS内部有一个子查询语句（SELECT...FROM...），将其称为EXISTS的内查询语句。其内查询语句返回一个结果集。EXISTS子句根据其内查询语句的结果集空或者非空，返回一个bool值。将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果为非空值，则EXISTS子句返回TRUE，外查询的这一行数据便可作为外查询的结果行返回，否则不能作为结果
select * from tableA a where exists (select * from tableB b where a.id=b.id and a.name=b.name);
-- tableA a ｜tableB b 分别是给表起了一个别名，后续用到tableA 就可以直接使用a来代替
注意⚠️：
EXISTS（NOT EXISTS） 用在where之后，且后面紧跟子查询语句带括号
EXISTS（NOT EXISTS） 只关心子查询有没有结果，并不关心子查询的结果是什么
把Table A的记录逐条代入到子查询，如果子查询结果集为空，说明不存在；那么这条Table A的记录出现在最终结果集，否则被排除
一般不太使用这样的查询语句，效率低
- 用于
FROM子句中的子查询
使用格式：
select tb_alias.col1,... from (select clause) as tb_alias where clause;
范例：
-- 查询女学生的信息
select name,age,gender from (select * from students where gender = 'F') girl_students;
+---------------+-----+--------+
| Name | Age | Gender |
+---------------+-----+--------+
| Xi Ren | 19 | F |
| Lin Daiyu | 17 | F |
| Ren Yingying | 20 | F |
| Yue Lingshan | 19 | F |
| Wen Qingqing | 19 | F |
| Lu Wushuang | 17 | F |
| Xue Baochai | 18 | F |
| Diao Chan | 19 | F |
| Huang Yueying | 22 | F |
| Xiao Qiao | 20 | F |
+---------------+-----+--------+
10 rows in set
Time: 0.009s
-- 给select * from students where gender = 'F'查出来的结果做为一个新表并给新表起了一个别名为girl_students
-- 查询每个班的平均年龄大于30的班级
17:36:44 (root@(none)) [hellodb]> select s.aage,s.classid from (select avg(age) as aage,classid from students where classid is not null group by classid) as s where s.aage>30;
+---------+---------+
| aage | classid |
+---------+---------+
| 36.0000 | 2 |
| 46.0000 | 5 |
+---------+---------+
2 rows in set
Time: 0.010s
17:37:17 (root@(none)) [hellodb]> select avg(age) as aage,classid from students where classid is not null group by classid;
+---------+---------+
| aage | classid |
+---------+---------+
| 20.5000 | 1 |
| 36.0000 | 2 |
| 20.2500 | 3 |
| 24.7500 | 4 |
| 46.0000 | 5 |
| 20.7500 | 6 |
| 19.6667 | 7 |
+---------+---------+
7 rows in set
Time: 0.009s
-- 子查询用户更新表，将子查询的结果做为更新表的条件
17:41:45 (root@(none)) [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set
Time: 0.009s
-- 将tid为4老师的年龄修改为学生表的平均年龄
17:43:23 (root@(none)) [hellodb]> update teachers set age=(select avg(age) from students) where tid=4;
Query OK, 1 row affected
Time: 0.054s
17:43:26 (root@(none)) [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 27 | F |
+-----+---------------+-----+--------+
4 rows in set
Time: 0.008s
联合查询
多表纵向合并，多表的字段（字段名、属性等）必须一致，使用UNION实现
17:54:57 (root@(none)) [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 27 | F |
+-----+---------------+-----+--------+
4 rows in set
Time: 0.009s
17:55:07 (root@(none)) [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | <null> |
| 7 | Xi Ren | 19 | F | 3 | <null> |
| 8 | Lin Daiyu | 17 | F | 7 | <null> |
| 9 | Ren Yingying | 20 | F | 6 | <null> |
| 10 | Yue Lingshan | 19 | F | 3 | <null> |
| 11 | Yuan Chengzhi | 23 | M | 6 | <null> |
| 12 | Wen Qingqing | 19 | F | 1 | <null> |
| 13 | Tian Boguang | 33 | M | 2 | <null> |
| 14 | Lu Wushuang | 17 | F | 3 | <null> |
| 15 | Duan Yu | 19 | M | 4 | <null> |
| 16 | Xu Zhu | 21 | M | 1 | <null> |
| 17 | Lin Chong | 25 | M | 4 | <null> |
| 18 | Hua Rong | 23 | M | 7 | <null> |
| 19 | Xue Baochai | 18 | F | 6 | <null> |
| 20 | Diao Chan | 19 | F | 7 | <null> |
| 21 | Huang Yueying | 22 | F | 6 | <null> |
| 22 | Xiao Qiao | 20 | F | 1 | <null> |
| 23 | Ma Chao | 23 | M | 4 | <null> |
| 24 | Xu Xian | 27 | M | <null> | <null> |
| 25 | Sun Dasheng | 100 | M | <null> | <null> |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set
Time: 0.010s
17:55:47 (root@(none)) [hellodb]> select * from teachers union select stuid,name,age,gender from students;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 27 | F |
| 1 | Shi Zhongyu | 22 | M |
| 2 | Shi Potian | 22 | M |
| 3 | Xie Yanke | 53 | M |
| 4 | Ding Dian | 32 | M |
| 5 | Yu Yutong | 26 | M |
| 6 | Shi Qing | 46 | M |
| 7 | Xi Ren | 19 | F |
| 8 | Lin Daiyu | 17 | F |
| 9 | Ren Yingying | 20 | F |
| 10 | Yue Lingshan | 19 | F |
| 11 | Yuan Chengzhi | 23 | M |
| 12 | Wen Qingqing | 19 | F |
| 13 | Tian Boguang | 33 | M |
| 14 | Lu Wushuang | 17 | F |
| 15 | Duan Yu | 19 | M |
| 16 | Xu Zhu | 21 | M |
| 17 | Lin Chong | 25 | M |
| 18 | Hua Rong | 23 | M |
| 19 | Xue Baochai | 18 | F |
| 20 | Diao Chan | 19 | F |
| 21 | Huang Yueying | 22 | F |
| 22 | Xiao Qiao | 20 | F |
| 23 | Ma Chao | 23 | M |
| 24 | Xu Xian | 27 | M |
| 25 | Sun Dasheng | 100 | M |
+-----+---------------+-----+--------+
29 rows in set
Time: 0.011s
-- union默认带有去重的功能，去重的必须是整行的数据都是一样的
18:00:12 (root@(none)) [hellodb]> select * from teachers union select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 27 | F |
+-----+---------------+-----+--------+
4 rows in set
Time: 0.009s
-- union all 不去重
18:03:15 (root@(none)) [hellodb]> select * from teachers union all select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 27 | F |
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 27 | F |
+-----+---------------+-----+--------+
8 rows in set
Time: 0.009s
-- 当多个union｜union all 合并查询的话，是按照从左向右的顺序查询的，左边的查询结果出来后，再与右边的合并
18:15:39 (root@(none)) [hellodb]> select * from teachers union all select * from teachers union select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 27 | F |
+-----+---------------+-----+--------+
4 rows in set
Time: 0.009s
18:15:51 (root@(none)) [hellodb]> select * from teachers union all select * from teachers union all select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 27 | F |
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 27 | F |
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 27 | F |
+-----+---------------+-----+--------+
12 rows in set
Time: 0.009s
18:16:55 (root@(none)) [hellodb]> select * from teachers union select * from teachers union all select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 27 | F |
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 27 | F |
+-----+---------------+-----+--------+
8 rows in set
Time: 0.009s
交叉连接
将两个表做笛卡尔乘积，“雨露均沾”，利用cross join实现
多表横向合并，将多张表的所有字段合并
-- 横向合并，交叉连接（横向笛卡尔）
18:46:07 (root@(none)) [hellodb]> select * from teachers cross join students;
19:17:04 (root@(none)) [hellodb]> select * from teachers , students;
+-----+---------------+-----+--------+-------+---------------+-----+--------+---------+-----------+
| TID | Name | Age | Gender | StuID | Name | Age | Gender | ClassID | TeacherID |
+-----+---------------+-----+--------+-------+---------------+-----+--------+---------+-----------+
| 1 | Song Jiang | 45 | M | 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Zhang Sanfeng | 94 | M | 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 3 | Miejue Shitai | 77 | F | 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 4 | Lin Chaoying | 27 | F | 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 1 | Song Jiang | 45 | M | 2 | Shi Potian | 22 | M | 1 | 7 |
| 2 | Zhang Sanfeng | 94 | M | 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Miejue Shitai | 77 | F | 2 | Shi Potian | 22 | M | 1 | 7 |
| 4 | Lin Chaoying | 27 | F | 2 | Shi Potian | 22 | M | 1 | 7 |
| 1 | Song Jiang | 45 | M | 3 | Xie Yanke | 53 | M | 2 | 16 |
| 2 | Zhang Sanfeng | 94 | M | 3 | Xie Yanke | 53 | M | 2 | 16 |
| 3 | Miejue Shitai | 77 | F | 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Lin Chaoying | 27 | F | 3 | Xie Yanke | 53 | M | 2 | 16 |
| 1 | Song Jiang | 45 | M | 4 | Ding Dian | 32 | M | 4 | 4 |
| 2 | Zhang Sanfeng | 94 | M | 4 | Ding Dian | 32 | M | 4 | 4 |
| 3 | Miejue Shitai | 77 | F | 4 | Ding Dian | 32 | M | 4 | 4 |
| 4 | Lin Chaoying | 27 | F | 4 | Ding Dian | 32 | M | 4 | 4 |
| 1 | Song Jiang | 45 | M | 5 | Yu Yutong | 26 | M | 3 | 1 |
| 2 | Zhang Sanfeng | 94 | M | 5 | Yu Yutong | 26 | M | 3 | 1 |
| 3 | Miejue Shitai | 77 | F | 5 | Yu Yutong | 26 | M | 3 | 1 |
| 4 | Lin Chaoying | 27 | F | 5 | Yu Yutong | 26 | M | 3 | 1 |
| 1 | Song Jiang | 45 | M | 6 | Shi Qing | 46 | M | 5 | <null> |
| 2 | Zhang Sanfeng | 94 | M | 6 | Shi Qing | 46 | M | 5 | <null> |
| 3 | Miejue Shitai | 77 | F | 6 | Shi Qing | 46 | M | 5 | <null> |
| 4 | Lin Chaoying | 27 | F | 6 | Shi Qing | 46 | M | 5 | <null> |
| 1 | Song Jiang | 45 | M | 7 | Xi Ren | 19 | F | 3 | <null> |
| 2 | Zhang Sanfeng | 94 | M | 7 | Xi Ren | 19 | F | 3 | <null> |
| 3 | Miejue Shitai | 77 | F | 7 | Xi Ren | 19 | F | 3 | <null> |
| 4 | Lin Chaoying | 27 | F | 7 | Xi Ren | 19 | F | 3 | <null> |
| 1 | Song Jiang | 45 | M | 8 | Lin Daiyu | 17 | F | 7 | <null> |
| 2 | Zhang Sanfeng | 94 | M | 8 | Lin Daiyu | 17 | F | 7 | <null> |
| 3 | Miejue Shitai | 77 | F | 8 | Lin Daiyu | 17 | F | 7 | <null> |
| 4 | Lin Chaoying | 27 | F | 8 | Lin Daiyu | 17 | F | 7 | <null> |
| 1 | Song Jiang | 45 | M | 9 | Ren Yingying | 20 | F | 6 | <null> |
| 2 | Zhang Sanfeng | 94 | M | 9 | Ren Yingying | 20 | F | 6 | <null> |
| 3 | Miejue Shitai | 77 | F | 9 | Ren Yingying | 20 | F | 6 | <null> |
| 4 | Lin Chaoying | 27 | F | 9 | Ren Yingying | 20 | F | 6 | <null> |
| 1 | Song Jiang | 45 | M | 10 | Yue Lingshan | 19 | F | 3 | <null> |
| 2 | Zhang Sanfeng | 94 | M | 10 | Yue Lingshan | 19 | F | 3 | <null> |
| 3 | Miejue Shitai | 77 | F | 10 | Yue Lingshan | 19 | F | 3 | <null> |
| 4 | Lin Chaoying | 27 | F | 10 | Yue Lingshan | 19 | F | 3 | <null> |
| 1 | Song Jiang | 45 | M | 11 | Yuan Chengzhi | 23 | M | 6 | <null> |
| 2 | Zhang Sanfeng | 94 | M | 11 | Yuan Chengzhi | 23 | M | 6 | <null> |
| 3 | Miejue Shitai | 77 | F | 11 | Yuan Chengzhi | 23 | M | 6 | <null> |
| 4 | Lin Chaoying | 27 | F | 11 | Yuan Chengzhi | 23 | M | 6 | <null> |
| 1 | Song Jiang | 45 | M | 12 | Wen Qingqing | 19 | F | 1 | <null> |
| 2 | Zhang Sanfeng | 94 | M | 12 | Wen Qingqing | 19 | F | 1 | <null> |
| 3 | Miejue Shitai | 77 | F | 12 | Wen Qingqing | 19 | F | 1 | <null> |
| 4 | Lin Chaoying | 27 | F | 12 | Wen Qingqing | 19 | F | 1 | <null> |
| 1 | Song Jiang | 45 | M | 13 | Tian Boguang | 33 | M | 2 | <null> |
| 2 | Zhang Sanfeng | 94 | M | 13 | Tian Boguang | 33 | M | 2 | <null> |
| 3 | Miejue Shitai | 77 | F | 13 | Tian Boguang | 33 | M | 2 | <null> |
| 4 | Lin Chaoying | 27 | F | 13 | Tian Boguang | 33 | M | 2 | <null> |
| 1 | Song Jiang | 45 | M | 14 | Lu Wushuang | 17 | F | 3 | <null> |
| 2 | Zhang Sanfeng | 94 | M | 14 | Lu Wushuang | 17 | F | 3 | <null> |
| 3 | Miejue Shitai | 77 | F | 14 | Lu Wushuang | 17 | F | 3 | <null> |
| 4 | Lin Chaoying | 27 | F | 14 | Lu Wushuang | 17 | F | 3 | <null> |
| 1 | Song Jiang | 45 | M | 15 | Duan Yu | 19 | M | 4 | <null> |
| 2 | Zhang Sanfeng | 94 | M | 15 | Duan Yu | 19 | M | 4 | <null> |
| 3 | Miejue Shitai | 77 | F | 15 | Duan Yu | 19 | M | 4 | <null> |
| 4 | Lin Chaoying | 27 | F | 15 | Duan Yu | 19 | M | 4 | <null> |
| 1 | Song Jiang | 45 | M | 16 | Xu Zhu | 21 | M | 1 | <null> |
| 2 | Zhang Sanfeng | 94 | M | 16 | Xu Zhu | 21 | M | 1 | <null> |
| 3 | Miejue Shitai | 77 | F | 16 | Xu Zhu | 21 | M | 1 | <null> |
| 4 | Lin Chaoying | 27 | F | 16 | Xu Zhu | 21 | M | 1 | <null> |
| 1 | Song Jiang | 45 | M | 17 | Lin Chong | 25 | M | 4 | <null> |
| 2 | Zhang Sanfeng | 94 | M | 17 | Lin Chong | 25 | M | 4 | <null> |
| 3 | Miejue Shitai | 77 | F | 17 | Lin Chong | 25 | M | 4 | <null> |
| 4 | Lin Chaoying | 27 | F | 17 | Lin Chong | 25 | M | 4 | <null> |
| 1 | Song Jiang | 45 | M | 18 | Hua Rong | 23 | M | 7 | <null> |
| 2 | Zhang Sanfeng | 94 | M | 18 | Hua Rong | 23 | M | 7 | <null> |
| 3 | Miejue Shitai | 77 | F | 18 | Hua Rong | 23 | M | 7 | <null> |
| 4 | Lin Chaoying | 27 | F | 18 | Hua Rong | 23 | M | 7 | <null> |
| 1 | Song Jiang | 45 | M | 19 | Xue Baochai | 18 | F | 6 | <null> |
| 2 | Zhang Sanfeng | 94 | M | 19 | Xue Baochai | 18 | F | 6 | <null> |
| 3 | Miejue Shitai | 77 | F | 19 | Xue Baochai | 18 | F | 6 | <null> |
| 4 | Lin Chaoying | 27 | F | 19 | Xue Baochai | 18 | F | 6 | <null> |
| 1 | Song Jiang | 45 | M | 20 | Diao Chan | 19 | F | 7 | <null> |
| 2 | Zhang Sanfeng | 94 | M | 20 | Diao Chan | 19 | F | 7 | <null> |
| 3 | Miejue Shitai | 77 | F | 20 | Diao Chan | 19 | F | 7 | <null> |
| 4 | Lin Chaoying | 27 | F | 20 | Diao Chan | 19 | F | 7 | <null> |
| 1 | Song Jiang | 45 | M | 21 | Huang Yueying | 22 | F | 6 | <null> |
| 2 | Zhang Sanfeng | 94 | M | 21 | Huang Yueying | 22 | F | 6 | <null> |
| 3 | Miejue Shitai | 77 | F | 21 | Huang Yueying | 22 | F | 6 | <null> |
| 4 | Lin Chaoying | 27 | F | 21 | Huang Yueying | 22 | F | 6 | <null> |
| 1 | Song Jiang | 45 | M | 22 | Xiao Qiao | 20 | F | 1 | <null> |
| 2 | Zhang Sanfeng | 94 | M | 22 | Xiao Qiao | 20 | F | 1 | <null> |
| 3 | Miejue Shitai | 77 | F | 22 | Xiao Qiao | 20 | F | 1 | <null> |
| 4 | Lin Chaoying | 27 | F | 22 | Xiao Qiao | 20 | F | 1 | <null> |
| 1 | Song Jiang | 45 | M | 23 | Ma Chao | 23 | M | 4 | <null> |
| 2 | Zhang Sanfeng | 94 | M | 23 | Ma Chao | 23 | M | 4 | <null> |
| 3 | Miejue Shitai | 77 | F | 23 | Ma Chao | 23 | M | 4 | <null> |
| 4 | Lin Chaoying | 27 | F | 23 | Ma Chao | 23 | M | 4 | <null> |
| 1 | Song Jiang | 45 | M | 24 | Xu Xian | 27 | M | <null> | <null> |
| 2 | Zhang Sanfeng | 94 | M | 24 | Xu Xian | 27 | M | <null> | <null> |
| 3 | Miejue Shitai | 77 | F | 24 | Xu Xian | 27 | M | <null> | <null> |
| 4 | Lin Chaoying | 27 | F | 24 | Xu Xian | 27 | M | <null> | <null> |
| 1 | Song Jiang | 45 | M | 25 | Sun Dasheng | 100 | M | <null> | <null> |
| 2 | Zhang Sanfeng | 94 | M | 25 | Sun Dasheng | 100 | M | <null> | <null> |
| 3 | Miejue Shitai | 77 | F | 25 | Sun Dasheng | 100 | M | <null> | <null> |
| 4 | Lin Chaoying | 27 | F | 25 | Sun Dasheng | 100 | M | <null> | <null> |
+-----+---------------+-----+--------+-------+---------------+-----+--------+---------+-----------+
100 rows in set
Time: 0.014s
-- 查询指定的字段，如果多表字段相同的话，需要将注名哪个表的那个字段
18:55:28 (root@(none)) [hellodb]> select teachers.name t_name,teachers.age t_age,teachers.gender t_gender,students.name,students.age,students.gender from teachers cross join students;
18:59:26 (root@(none)) [hellodb]> select t.name t_name,t.age t_age,t.gender t_gender,s.name,s.age,s.gender from teachers t cross join students s;
+---------------+-------+----------+---------------+-----+--------+
| t_name | t_age | t_gender | name | age | gender |
+---------------+-------+----------+---------------+-----+--------+
| Song Jiang | 45 | M | Shi Zhongyu | 22 | M |
| Zhang Sanfeng | 94 | M | Shi Zhongyu | 22 | M |
| Miejue Shitai | 77 | F | Shi Zhongyu | 22 | M |
| Lin Chaoying | 27 | F | Shi Zhongyu | 22 | M |
| Song Jiang | 45 | M | Shi Potian | 22 | M |
| Zhang Sanfeng | 94 | M | Shi Potian | 22 | M |
| Miejue Shitai | 77 | F | Shi Potian | 22 | M |
| Lin Chaoying | 27 | F | Shi Potian | 22 | M |
| Song Jiang | 45 | M | Xie Yanke | 53 | M |
| Zhang Sanfeng | 94 | M | Xie Yanke | 53 | M |
| Miejue Shitai | 77 | F | Xie Yanke | 53 | M |
| Lin Chaoying | 27 | F | Xie Yanke | 53 | M |
| Song Jiang | 45 | M | Ding Dian | 32 | M |
| Zhang Sanfeng | 94 | M | Ding Dian | 32 | M |
| Miejue Shitai | 77 | F | Ding Dian | 32 | M |
| Lin Chaoying | 27 | F | Ding Dian | 32 | M |
| Song Jiang | 45 | M | Yu Yutong | 26 | M |
| Zhang Sanfeng | 94 | M | Yu Yutong | 26 | M |
| Miejue Shitai | 77 | F | Yu Yutong | 26 | M |
| Lin Chaoying | 27 | F | Yu Yutong | 26 | M |
| Song Jiang | 45 | M | Shi Qing | 46 | M |
| Zhang Sanfeng | 94 | M | Shi Qing | 46 | M |
| Miejue Shitai | 77 | F | Shi Qing | 46 | M |
| Lin Chaoying | 27 | F | Shi Qing | 46 | M |
| Song Jiang | 45 | M | Xi Ren | 19 | F |
| Zhang Sanfeng | 94 | M | Xi Ren | 19 | F |
| Miejue Shitai | 77 | F | Xi Ren | 19 | F |
| Lin Chaoying | 27 | F | Xi Ren | 19 | F |
| Song Jiang | 45 | M | Lin Daiyu | 17 | F |
| Zhang Sanfeng | 94 | M | Lin Daiyu | 17 | F |
| Miejue Shitai | 77 | F | Lin Daiyu | 17 | F |
| Lin Chaoying | 27 | F | Lin Daiyu | 17 | F |
| Song Jiang | 45 | M | Ren Yingying | 20 | F |
| Zhang Sanfeng | 94 | M | Ren Yingying | 20 | F |
| Miejue Shitai | 77 | F | Ren Yingying | 20 | F |
| Lin Chaoying | 27 | F | Ren Yingying | 20 | F |
| Song Jiang | 45 | M | Yue Lingshan | 19 | F |
| Zhang Sanfeng | 94 | M | Yue Lingshan | 19 | F |
| Miejue Shitai | 77 | F | Yue Lingshan | 19 | F |
| Lin Chaoying | 27 | F | Yue Lingshan | 19 | F |
| Song Jiang | 45 | M | Yuan Chengzhi | 23 | M |
| Zhang Sanfeng | 94 | M | Yuan Chengzhi | 23 | M |
| Miejue Shitai | 77 | F | Yuan Chengzhi | 23 | M |
| Lin Chaoying | 27 | F | Yuan Chengzhi | 23 | M |
| Song Jiang | 45 | M | Wen Qingqing | 19 | F |
| Zhang Sanfeng | 94 | M | Wen Qingqing | 19 | F |
| Miejue Shitai | 77 | F | Wen Qingqing | 19 | F |
| Lin Chaoying | 27 | F | Wen Qingqing | 19 | F |
| Song Jiang | 45 | M | Tian Boguang | 33 | M |
| Zhang Sanfeng | 94 | M | Tian Boguang | 33 | M |
| Miejue Shitai | 77 | F | Tian Boguang | 33 | M |
| Lin Chaoying | 27 | F | Tian Boguang | 33 | M |
| Song Jiang | 45 | M | Lu Wushuang | 17 | F |
| Zhang Sanfeng | 94 | M | Lu Wushuang | 17 | F |
| Miejue Shitai | 77 | F | Lu Wushuang | 17 | F |
| Lin Chaoying | 27 | F | Lu Wushuang | 17 | F |
| Song Jiang | 45 | M | Duan Yu | 19 | M |
| Zhang Sanfeng | 94 | M | Duan Yu | 19 | M |
| Miejue Shitai | 77 | F | Duan Yu | 19 | M |
| Lin Chaoying | 27 | F | Duan Yu | 19 | M |
| Song Jiang | 45 | M | Xu Zhu | 21 | M |
| Zhang Sanfeng | 94 | M | Xu Zhu | 21 | M |
| Miejue Shitai | 77 | F | Xu Zhu | 21 | M |
| Lin Chaoying | 27 | F | Xu Zhu | 21 | M |
| Song Jiang | 45 | M | Lin Chong | 25 | M |
| Zhang Sanfeng | 94 | M | Lin Chong | 25 | M |
| Miejue Shitai | 77 | F | Lin Chong | 25 | M |
| Lin Chaoying | 27 | F | Lin Chong | 25 | M |
| Song Jiang | 45 | M | Hua Rong | 23 | M |
| Zhang Sanfeng | 94 | M | Hua Rong | 23 | M |
| Miejue Shitai | 77 | F | Hua Rong | 23 | M |
| Lin Chaoying | 27 | F | Hua Rong | 23 | M |
| Song Jiang | 45 | M | Xue Baochai | 18 | F |
| Zhang Sanfeng | 94 | M | Xue Baochai | 18 | F |
| Miejue Shitai | 77 | F | Xue Baochai | 18 | F |
| Lin Chaoying | 27 | F | Xue Baochai | 18 | F |
| Song Jiang | 45 | M | Diao Chan | 19 | F |
| Zhang Sanfeng | 94 | M | Diao Chan | 19 | F |
| Miejue Shitai | 77 | F | Diao Chan | 19 | F |
| Lin Chaoying | 27 | F | Diao Chan | 19 | F |
| Song Jiang | 45 | M | Huang Yueying | 22 | F |
| Zhang Sanfeng | 94 | M | Huang Yueying | 22 | F |
| Miejue Shitai | 77 | F | Huang Yueying | 22 | F |
| Lin Chaoying | 27 | F | Huang Yueying | 22 | F |
| Song Jiang | 45 | M | Xiao Qiao | 20 | F |
| Zhang Sanfeng | 94 | M | Xiao Qiao | 20 | F |
| Miejue Shitai | 77 | F | Xiao Qiao | 20 | F |
| Lin Chaoying | 27 | F | Xiao Qiao | 20 | F |
| Song Jiang | 45 | M | Ma Chao | 23 | M |
| Zhang Sanfeng | 94 | M | Ma Chao | 23 | M |
| Miejue Shitai | 77 | F | Ma Chao | 23 | M |
| Lin Chaoying | 27 | F | Ma Chao | 23 | M |
| Song Jiang | 45 | M | Xu Xian | 27 | M |
| Zhang Sanfeng | 94 | M | Xu Xian | 27 | M |
| Miejue Shitai | 77 | F | Xu Xian | 27 | M |
| Lin Chaoying | 27 | F | Xu Xian | 27 | M |
| Song Jiang | 45 | M | Sun Dasheng | 100 | M |
| Zhang Sanfeng | 94 | M | Sun Dasheng | 100 | M |
| Miejue Shitai | 77 | F | Sun Dasheng | 100 | M |
| Lin Chaoying | 27 | F | Sun Dasheng | 100 | M |
+---------------+-------+----------+---------------+-----+--------+
100 rows in set
Time: 0.013s
-- 当给表起了别名后，就只能使用别名来指定表了，否则会报错
19:02:41 (root@(none)) [hellodb]> select t.name t_name,t.age t_age,t.gender t_gender,s.name,s.age,students.gender from teachers t cross join students s;
(1054, "Unknown column 'students.gender' in 'field list'")
内连接
-- 内连接 inner join
-- 语法：FROM tbA INNER JOIN tbB ON tbA.col=tbB.col
-- 取符合tbA.col=tbB.col部分的信息
-- 查询学生表中的teacherid跟老师表中的tid相同的信息
19:26:43 (root@(none)) [hellodb]> select s.stuid,s.name,s.age,s.gender,t.name t_name,t.tid t_id,t.age t_age from students s inner join teachers t on s.teacherid=t.tid;
+-------+-------------+-----+--------+---------------+------+-------+
| stuid | name | age | gender | t_name | t_id | t_age |
+-------+-------------+-----+--------+---------------+------+-------+
| 1 | Shi Zhongyu | 22 | M | Miejue Shitai | 3 | 77 |
| 4 | Ding Dian | 32 | M | Lin Chaoying | 4 | 27 |
| 5 | Yu Yutong | 26 | M | Song Jiang | 1 | 45 |
+-------+-------------+-----+--------+---------------+------+-------+
3 rows in set
Time: 0.010s
-- 查询学生的性别跟老师性别不一样的信息
19:36:13 (root@(none)) [hellodb]> select s.stuid,s.name,s.age,s.gender,t.name t_name,t.tid t_id,t.age t_age,t.gender t_gender from students s inner join teachers t on s.gender<>t.gender;
+-------+---------------+-----+--------+---------------+------+-------+----------+
| stuid | name | age | gender | t_name | t_id | t_age | t_gender |
+-------+---------------+-----+--------+---------------+------+-------+----------+
| 1 | Shi Zhongyu | 22 | M | Miejue Shitai | 3 | 77 | F |
| 1 | Shi Zhongyu | 22 | M | Lin Chaoying | 4 | 27 | F |
| 2 | Shi Potian | 22 | M | Miejue Shitai | 3 | 77 | F |
| 2 | Shi Potian | 22 | M | Lin Chaoying | 4 | 27 | F |
| 3 | Xie Yanke | 53 | M | Miejue Shitai | 3 | 77 | F |
| 3 | Xie Yanke | 53 | M | Lin Chaoying | 4 | 27 | F |
| 4 | Ding Dian | 32 | M | Miejue Shitai | 3 | 77 | F |
| 4 | Ding Dian | 32 | M | Lin Chaoying | 4 | 27 | F |
| 5 | Yu Yutong | 26 | M | Miejue Shitai | 3 | 77 | F |
| 5 | Yu Yutong | 26 | M | Lin Chaoying | 4 | 27 | F |
| 6 | Shi Qing | 46 | M | Miejue Shitai | 3 | 77 | F |
| 6 | Shi Qing | 46 | M | Lin Chaoying | 4 | 27 | F |
| 7 | Xi Ren | 19 | F | Song Jiang | 1 | 45 | M |
| 7 | Xi Ren | 19 | F | Zhang Sanfeng | 2 | 94 | M |
| 8 | Lin Daiyu | 17 | F | Song Jiang | 1 | 45 | M |
| 8 | Lin Daiyu | 17 | F | Zhang Sanfeng | 2 | 94 | M |
| 9 | Ren Yingying | 20 | F | Song Jiang | 1 | 45 | M |
| 9 | Ren Yingying | 20 | F | Zhang Sanfeng | 2 | 94 | M |
| 10 | Yue Lingshan | 19 | F | Song Jiang | 1 | 45 | M |
| 10 | Yue Lingshan | 19 | F | Zhang Sanfeng | 2 | 94 | M |
| 11 | Yuan Chengzhi | 23 | M | Miejue Shitai | 3 | 77 | F |
| 11 | Yuan Chengzhi | 23 | M | Lin Chaoying | 4 | 27 | F |
| 12 | Wen Qingqing | 19 | F | Song Jiang | 1 | 45 | M |
| 12 | Wen Qingqing | 19 | F | Zhang Sanfeng | 2 | 94 | M |
| 13 | Tian Boguang | 33 | M | Miejue Shitai | 3 | 77 | F |
| 13 | Tian Boguang | 33 | M | Lin Chaoying | 4 | 27 | F |
| 14 | Lu Wushuang | 17 | F | Song Jiang | 1 | 45 | M |
| 14 | Lu Wushuang | 17 | F | Zhang Sanfeng | 2 | 94 | M |
| 15 | Duan Yu | 19 | M | Miejue Shitai | 3 | 77 | F |
| 15 | Duan Yu | 19 | M | Lin Chaoying | 4 | 27 | F |
| 16 | Xu Zhu | 21 | M | Miejue Shitai | 3 | 77 | F |
| 16 | Xu Zhu | 21 | M | Lin Chaoying | 4 | 27 | F |
| 17 | Lin Chong | 25 | M | Miejue Shitai | 3 | 77 | F |
| 17 | Lin Chong | 25 | M | Lin Chaoying | 4 | 27 | F |
| 18 | Hua Rong | 23 | M | Miejue Shitai | 3 | 77 | F |
| 18 | Hua Rong | 23 | M | Lin Chaoying | 4 | 27 | F |
| 19 | Xue Baochai | 18 | F | Song Jiang | 1 | 45 | M |
| 19 | Xue Baochai | 18 | F | Zhang Sanfeng | 2 | 94 | M |
| 20 | Diao Chan | 19 | F | Song Jiang | 1 | 45 | M |
| 20 | Diao Chan | 19 | F | Zhang Sanfeng | 2 | 94 | M |
| 21 | Huang Yueying | 22 | F | Song Jiang | 1 | 45 | M |
| 21 | Huang Yueying | 22 | F | Zhang Sanfeng | 2 | 94 | M |
| 22 | Xiao Qiao | 20 | F | Song Jiang | 1 | 45 | M |
| 22 | Xiao Qiao | 20 | F | Zhang Sanfeng | 2 | 94 | M |
| 23 | Ma Chao | 23 | M | Miejue Shitai | 3 | 77 | F |
| 23 | Ma Chao | 23 | M | Lin Chaoying | 4 | 27 | F |
| 24 | Xu Xian | 27 | M | Miejue Shitai | 3 | 77 | F |
| 24 | Xu Xian | 27 | M | Lin Chaoying | 4 | 27 | F |
| 25 | Sun Dasheng | 100 | M | Miejue Shitai | 3 | 77 | F |
| 25 | Sun Dasheng | 100 | M | Lin Chaoying | 4 | 27 | F |
+-------+---------------+-----+--------+---------------+------+-------+----------+
50 rows in set
Time: 0.012s
-- 查询学生的性别跟老师性别不一样且老师年龄小于50岁的信息
19:49:16 (root@(none)) [hellodb]> select s.stuid,s.name,s.age,s.gender,t.name t_name,t.tid t_id,t.age t_age,t.gender t_gender from students s inner join teachers t on s.gender<>t.gender and t.age <= 50;
19:50:08 (root@(none)) [hellodb]> select s.stuid,s.name,s.age,s.gender,t.name t_name,t.tid t_id,t.age t_age,t.gender t_gender from students s inner join teachers t on s.gender<>t.gender where t.age <= 50;
+-------+---------------+-----+--------+--------------+------+-------+----------+
| stuid | name | age | gender | t_name | t_id | t_age | t_gender |
+-------+---------------+-----+--------+--------------+------+-------+----------+
| 1 | Shi Zhongyu | 22 | M | Lin Chaoying | 4 | 27 | F |
| 2 | Shi Potian | 22 | M | Lin Chaoying | 4 | 27 | F |
| 3 | Xie Yanke | 53 | M | Lin Chaoying | 4 | 27 | F |
| 4 | Ding Dian | 32 | M | Lin Chaoying | 4 | 27 | F |
| 5 | Yu Yutong | 26 | M | Lin Chaoying | 4 | 27 | F |
| 6 | Shi Qing | 46 | M | Lin Chaoying | 4 | 27 | F |
| 7 | Xi Ren | 19 | F | Song Jiang | 1 | 45 | M |
| 8 | Lin Daiyu | 17 | F | Song Jiang | 1 | 45 | M |
| 9 | Ren Yingying | 20 | F | Song Jiang | 1 | 45 | M |
| 10 | Yue Lingshan | 19 | F | Song Jiang | 1 | 45 | M |
| 11 | Yuan Chengzhi | 23 | M | Lin Chaoying | 4 | 27 | F |
| 12 | Wen Qingqing | 19 | F | Song Jiang | 1 | 45 | M |
| 13 | Tian Boguang | 33 | M | Lin Chaoying | 4 | 27 | F |
| 14 | Lu Wushuang | 17 | F | Song Jiang | 1 | 45 | M |
| 15 | Duan Yu | 19 | M | Lin Chaoying | 4 | 27 | F |
| 16 | Xu Zhu | 21 | M | Lin Chaoying | 4 | 27 | F |
| 17 | Lin Chong | 25 | M | Lin Chaoying | 4 | 27 | F |
| 18 | Hua Rong | 23 | M | Lin Chaoying | 4 | 27 | F |
| 19 | Xue Baochai | 18 | F | Song Jiang | 1 | 45 | M |
| 20 | Diao Chan | 19 | F | Song Jiang | 1 | 45 | M |
| 21 | Huang Yueying | 22 | F | Song Jiang | 1 | 45 | M |
| 22 | Xiao Qiao | 20 | F | Song Jiang | 1 | 45 | M |
| 23 | Ma Chao | 23 | M | Lin Chaoying | 4 | 27 | F |
| 24 | Xu Xian | 27 | M | Lin Chaoying | 4 | 27 | F |
| 25 | Sun Dasheng | 100 | M | Lin Chaoying | 4 | 27 | F |
+-------+---------------+-----+--------+--------------+------+-------+----------+
25 rows in set
Time: 0.011s
左、右外连接
左外连接
-- 左外连接 left outer join
-- 语法：FROM tbA LEFT JOIN tbB ON tbA.col=tbB.col
-- 取tbA表的所有加上符合tbA.col=tbB.col部分的tbB表的信息，等于有一部分取的交集
-- 查询学生表中的teacherid跟老师表中的tid相同的信息并加上所有学生的信息
17:08:32 (root@(none)) [hellodb]> select s.stuid,s.name,s.age,s.gender,t.name t_name,t.tid t_id,t.age t_age from students s left outer join teachers t on s.teacherid=t.tid;
+-------+---------------+-----+--------+---------------+--------+--------+
| stuid | name | age | gender | t_name | t_id | t_age |
+-------+---------------+-----+--------+---------------+--------+--------+
| 5 | Yu Yutong | 26 | M | Song Jiang | 1 | 45 |
| 1 | Shi Zhongyu | 22 | M | Miejue Shitai | 3 | 77 |
| 4 | Ding Dian | 32 | M | Lin Chaoying | 4 | 27 |
| 2 | Shi Potian | 22 | M | <null> | <null> | <null> |
| 3 | Xie Yanke | 53 | M | <null> | <null> | <null> |
| 6 | Shi Qing | 46 | M | <null> | <null> | <null> |
| 7 | Xi Ren | 19 | F | <null> | <null> | <null> |
| 8 | Lin Daiyu | 17 | F | <null> | <null> | <null> |
| 9 | Ren Yingying | 20 | F | <null> | <null> | <null> |
| 10 | Yue Lingshan | 19 | F | <null> | <null> | <null> |
| 11 | Yuan Chengzhi | 23 | M | <null> | <null> | <null> |
| 12 | Wen Qingqing | 19 | F | <null> | <null> | <null> |
| 13 | Tian Boguang | 33 | M | <null> | <null> | <null> |
| 14 | Lu Wushuang | 17 | F | <null> | <null> | <null> |
| 15 | Duan Yu | 19 | M | <null> | <null> | <null> |
| 16 | Xu Zhu | 21 | M | <null> | <null> | <null> |
| 17 | Lin Chong | 25 | M | <null> | <null> | <null> |
| 18 | Hua Rong | 23 | M | <null> | <null> | <null> |
| 19 | Xue Baochai | 18 | F | <null> | <null> | <null> |
| 20 | Diao Chan | 19 | F | <null> | <null> | <null> |
| 21 | Huang Yueying | 22 | F | <null> | <null> | <null> |
| 22 | Xiao Qiao | 20 | F | <null> | <null> | <null> |
| 23 | Ma Chao | 23 | M | <null> | <null> | <null> |
| 24 | Xu Xian | 27 | M | <null> | <null> | <null> |
| 25 | Sun Dasheng | 100 | M | <null> | <null> | <null> |
+-------+---------------+-----+--------+---------------+--------+--------+
25 rows in set
Time: 0.011s
-- 查询学生表中的teacherid跟老师表中的tid相同的信息并加上所有老师的信息
17:16:58 (root@(none)) [hellodb]> select s.stuid,s.name,s.age,s.gender,t.name t_name,t.tid t_id,t.age t_age from teachers t left outer join students s on s.teacherid=t.tid;
+--------+-------------+--------+--------+---------------+------+-------+
| stuid | name | age | gender | t_name | t_id | t_age |
+--------+-------------+--------+--------+---------------+------+-------+
| 1 | Shi Zhongyu | 22 | M | Miejue Shitai | 3 | 77 |
| 4 | Ding Dian | 32 | M | Lin Chaoying | 4 | 27 |
| 5 | Yu Yutong | 26 | M | Song Jiang | 1 | 45 |
| <null> | <null> | <null> | <null> | Zhang Sanfeng | 2 | 94 |
+--------+-------------+--------+--------+---------------+------+-------+
4 rows in set
Time: 0.017s
-- 扩展：FROM tbA LEFT JOIN tbB ON tbA.col=tbB.col WHERE tbB.col IS NULL
-- 取tbA表的所有加上不符合tbA.col=tbB.col部分的tbB表的信息，非交集
--
17:52:32 (root@(none)) [hellodb]> select s.stuid,s.name,s.age,s.gender,t.name t_name,t.tid t_id,t.age t_age
-> from teachers t
-> left outer join students s on s.teacherid=t.tid
-> where s.teacherid IS NULL;
+--------+--------+--------+--------+---------------+------+-------+
| stuid | name | age | gender | t_name | t_id | t_age |
+--------+--------+--------+--------+---------------+------+-------+
| <null> | <null> | <null> | <null> | Zhang Sanfeng | 2 | 94 |
+--------+--------+--------+--------+---------------+------+-------+
1 row in set
Time: 0.010s
右外连接
-- 右外连接 right outer join
-- 语法：FROM tbA RIGHT JOIN tbB ON tbA.col=tbB.col
-- 取tbB表的所有加上符合tbA.col=tbB.col部分的tbA表的信息，等于有一部分取的交集
-- 查询学生表中的teacherid跟老师表中的tid相同的信息并加上所有老师的信息
17:21:57 (root@(none)) [hellodb]> select s.stuid,s.name,s.age,s.gender,t.name t_name,t.tid t_id,t.age t_age from students s right outer join teachers t on s.teacherid=t.tid;
+--------+-------------+--------+--------+---------------+------+-------+
| stuid | name | age | gender | t_name | t_id | t_age |
+--------+-------------+--------+--------+---------------+------+-------+
| 1 | Shi Zhongyu | 22 | M | Miejue Shitai | 3 | 77 |
| 4 | Ding Dian | 32 | M | Lin Chaoying | 4 | 27 |
| 5 | Yu Yutong | 26 | M | Song Jiang | 1 | 45 |
| <null> | <null> | <null> | <null> | Zhang Sanfeng | 2 | 94 |
+--------+-------------+--------+--------+---------------+------+-------+
4 rows in set
Time: 0.010s
-- 扩展：FROM tbA RIGHT JOIN tbB ON tbA.col=tbB.col WHERE tbA.col IS NULL
-- 取tbB表的所有加上不符合tbA.col=tbB.col部分的tbA表的信息，非交集
--
17:37:49 (root@(none)) [hellodb]> select s.stuid,s.name,s.age,s.gender,t.name t_name,t.tid t_id,t.age t_age
-> from students s
-> right join teachers t on s.teacherid=t.tid
-> where s.teacherid IS NULL;
+--------+--------+--------+--------+---------------+------+-------+
| stuid | name | age | gender | t_name | t_id | t_age |
+--------+--------+--------+--------+---------------+------+-------+
| <null> | <null> | <null> | <null> | Zhang Sanfeng | 2 | 94 |
+--------+--------+--------+--------+---------------+------+-------+
1 row in set
Time: 0.010s
完全外连接
-- 完全链接 full outer join
-- 语法：FROM tbA FULL JOIN tbB ON tbA.col=tbB.col
-- 取tbA及tbB所有表的交集，但MySQL不支持这个语法，需要通过左右外连接加联合查询共同创建
-- MySQL 5.7.24
18:00:49 (root@(none)) [hellodb]> select s.stuid,s.name,s.age,s.gender,t.name t_name,t.tid t_id,t.age t_age
-> from teachers t
-> full outer join students s on s.teacherid=t.tid;
(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'full outer join students s on s.teacherid=t.tid' at line 3")
18:12:17 (root@(none)) [hellodb]> select s.stuid,s.name,s.age,s.gender,t.name t_name,t.tid t_id,t.age t_age
-> from teachers t
-> left join students s on s.teacherid=t.tid
-> union
-> select s.stuid,s.name,s.age,s.gender,t.name t_name,t.tid t_id,t.age t_age
-> from teachers t
-> right join students s on s.teacherid=t.tid;
+--------+---------------+--------+--------+---------------+--------+--------+
| stuid | name | age | gender | t_name | t_id | t_age |
+--------+---------------+--------+--------+---------------+--------+--------+
| 1 | Shi Zhongyu | 22 | M | Miejue Shitai | 3 | 77 |
| 4 | Ding Dian | 32 | M | Lin Chaoying | 4 | 27 |
| 5 | Yu Yutong | 26 | M | Song Jiang | 1 | 45 |
| <null> | <null> | <null> | <null> | Zhang Sanfeng | 2 | 94 |
| 2 | Shi Potian | 22 | M | <null> | <null> | <null> |
| 3 | Xie Yanke | 53 | M | <null> | <null> | <null> |
| 6 | Shi Qing | 46 | M | <null> | <null> | <null> |
| 7 | Xi Ren | 19 | F | <null> | <null> | <null> |
| 8 | Lin Daiyu | 17 | F | <null> | <null> | <null> |
| 9 | Ren Yingying | 20 | F | <null> | <null> | <null> |
| 10 | Yue Lingshan | 19 | F | <null> | <null> | <null> |
| 11 | Yuan Chengzhi | 23 | M | <null> | <null> | <null> |
| 12 | Wen Qingqing | 19 | F | <null> | <null> | <null> |
| 13 | Tian Boguang | 33 | M | <null> | <null> | <null> |
| 14 | Lu Wushuang | 17 | F | <null> | <null> | <null> |
| 15 | Duan Yu | 19 | M | <null> | <null> | <null> |
| 16 | Xu Zhu | 21 | M | <null> | <null> | <null> |
| 17 | Lin Chong | 25 | M | <null> | <null> | <null> |
| 18 | Hua Rong | 23 | M | <null> | <null> | <null> |
| 19 | Xue Baochai | 18 | F | <null> | <null> | <null> |
| 20 | Diao Chan | 19 | F | <null> | <null> | <null> |
| 21 | Huang Yueying | 22 | F | <null> | <null> | <null> |
| 22 | Xiao Qiao | 20 | F | <null> | <null> | <null> |
| 23 | Ma Chao | 23 | M | <null> | <null> | <null> |
| 24 | Xu Xian | 27 | M | <null> | <null> | <null> |
| 25 | Sun Dasheng | 100 | M | <null> | <null> | <null> |
+--------+---------------+--------+--------+---------------+--------+--------+
26 rows in set
Time: 0.012s
-- 扩展：FROM tbA FULL JOIN tbB ON tbA.col=tbB.col WHERE tbA.col IS NULL OR tbB.col IS NULL
-- 取tbA及tbB所有表的非交集
--
18:19:29 (root@(none)) [hellodb]> select s.stuid,s.name,s.age,s.gender,t.name t_name,t.tid t_id,t.age t_age
-> from teachers t
-> left join students s on s.teacherid=t.tid where s.teacherid IS NULL
-> union
-> select s.stuid,s.name,s.age,s.gender,t.name t_name,t.tid t_id,t.age t_age
-> from teachers t
-> right join students s on s.teacherid=t.tid where t.tid IS NULL;
+--------+---------------+--------+--------+---------------+--------+--------+
| stuid | name | age | gender | t_name | t_id | t_age |
+--------+---------------+--------+--------+---------------+--------+--------+
| <null> | <null> | <null> | <null> | Zhang Sanfeng | 2 | 94 |
| 2 | Shi Potian | 22 | M | <null> | <null> | <null> |
| 3 | Xie Yanke | 53 | M | <null> | <null> | <null> |
| 6 | Shi Qing | 46 | M | <null> | <null> | <null> |
| 7 | Xi Ren | 19 | F | <null> | <null> | <null> |
| 8 | Lin Daiyu | 17 | F | <null> | <null> | <null> |
| 9 | Ren Yingying | 20 | F | <null> | <null> | <null> |
| 10 | Yue Lingshan | 19 | F | <null> | <null> | <null> |
| 11 | Yuan Chengzhi | 23 | M | <null> | <null> | <null> |
| 12 | Wen Qingqing | 19 | F | <null> | <null> | <null> |
| 13 | Tian Boguang | 33 | M | <null> | <null> | <null> |
| 14 | Lu Wushuang | 17 | F | <null> | <null> | <null> |
| 15 | Duan Yu | 19 | M | <null> | <null> | <null> |
| 16 | Xu Zhu | 21 | M | <null> | <null> | <null> |
| 17 | Lin Chong | 25 | M | <null> | <null> | <null> |
| 18 | Hua Rong | 23 | M | <null> | <null> | <null> |
| 19 | Xue Baochai | 18 | F | <null> | <null> | <null> |
| 20 | Diao Chan | 19 | F | <null> | <null> | <null> |
| 21 | Huang Yueying | 22 | F | <null> | <null> | <null> |
| 22 | Xiao Qiao | 20 | F | <null> | <null> | <null> |
| 23 | Ma Chao | 23 | M | <null> | <null> | <null> |
| 24 | Xu Xian | 27 | M | <null> | <null> | <null> |
| 25 | Sun Dasheng | 100 | M | <null> | <null> | <null> |
+--------+---------------+--------+--------+---------------+--------+--------+
23 rows in set
Time: 0.013s
自连接
-- 表自身连接自身
-- 可以将一个表使用别名的方式当成两张表使用
-- 实例：
18:46:23 (root@(none)) [hellodb]> create table emp (id int(11) auto_increment primary key,emp_name varchar(30),leaderid int);
Query OK, 0 rows affected
Time: 0.208s
18:48:14 (root@(none)) [hellodb]> insert into emp (emp_name,leaderid)
-> values('mage',null),('zhangsir',1),('wang',2),
-> ('hooper',3),('ann',3),('snake',4);
Query OK, 6 rows affected
Time: 0.029s
18:48:25 (root@(none)) [hellodb]> select * from emp;
+----+----------+----------+
| id | emp_name | leaderid |
+----+----------+----------+
| 1 | mage | <null> |
| 2 | zhangsir | 1 |
| 3 | wang | 2 |
| 4 | hooper | 3 |
| 5 | ann | 3 |
| 6 | snake | 4 |
+----+----------+----------+
6 rows in set
Time: 0.009s
18:58:14 (root@(none)) [hellodb]> select e.emp_name,l.emp_name leader_name
-> from emp as e
-> left join emp as l on e.leaderid = l.id;
+----------+-------------+
| emp_name | leader_name |
+----------+-------------+
| mage | <null> |
| zhangsir | mage |
| wang | zhangsir |
| hooper | wang |
| ann | wang |
| snake | hooper |
+----------+-------------+
6 rows in set
Time: 0.009s
-- 扩展：
19:04:59 (root@(none)) [hellodb]> select e.emp_name,ifnull(l.emp_name,'无上级领导') leader_name
-> from emp as e
-> left join emp as l on e.leaderid = l.id;
+----------+-------------+
| emp_name | leader_name |
+----------+-------------+
| mage | 无上级领导 |
| zhangsir | mage |
| wang | zhangsir |
| hooper | wang |
| ann | wang |
| snake | hooper |
+----------+-------------+
6 rows in set
Time: 0.009s
-- 三表连接查询
-- 这里有三张表，分别是学生表、课程表、成绩表
19:25:58 (root@(none)) [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | <null> |
| 7 | Xi Ren | 19 | F | 3 | <null> |
| 8 | Lin Daiyu | 17 | F | 7 | <null> |
| 9 | Ren Yingying | 20 | F | 6 | <null> |
| 10 | Yue Lingshan | 19 | F | 3 | <null> |
| 11 | Yuan Chengzhi | 23 | M | 6 | <null> |
| 12 | Wen Qingqing | 19 | F | 1 | <null> |
| 13 | Tian Boguang | 33 | M | 2 | <null> |
| 14 | Lu Wushuang | 17 | F | 3 | <null> |
| 15 | Duan Yu | 19 | M | 4 | <null> |
| 16 | Xu Zhu | 21 | M | 1 | <null> |
| 17 | Lin Chong | 25 | M | 4 | <null> |
| 18 | Hua Rong | 23 | M | 7 | <null> |
| 19 | Xue Baochai | 18 | F | 6 | <null> |
| 20 | Diao Chan | 19 | F | 7 | <null> |
| 21 | Huang Yueying | 22 | F | 6 | <null> |
| 22 | Xiao Qiao | 20 | F | 1 | <null> |
| 23 | Ma Chao | 23 | M | 4 | <null> |
| 24 | Xu Xian | 27 | M | <null> | <null> |
| 25 | Sun Dasheng | 100 | M | <null> | <null> |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set
Time: 0.010s
19:26:09 (root@(none)) [hellodb]> select * from scores;
+----+-------+----------+-------+
| ID | StuID | CourseID | Score |
+----+-------+----------+-------+
| 1 | 1 | 2 | 77 |
| 2 | 1 | 6 | 93 |
| 3 | 2 | 2 | 47 |
| 4 | 2 | 5 | 97 |
| 5 | 3 | 2 | 88 |
| 6 | 3 | 6 | 75 |
| 7 | 4 | 5 | 71 |
| 8 | 4 | 2 | 89 |
| 9 | 5 | 1 | 39 |
| 10 | 5 | 7 | 63 |
| 11 | 6 | 1 | 96 |
| 12 | 7 | 1 | 86 |
| 13 | 7 | 7 | 83 |
| 14 | 8 | 4 | 57 |
| 15 | 8 | 3 | 93 |
+----+-------+----------+-------+
15 rows in set
Time: 0.009s
19:26:19 (root@(none)) [hellodb]> select * from courses;
+----------+----------------+
| CourseID | Course |
+----------+----------------+
| 1 | Hamo Gong |
| 2 | Kuihua Baodian |
| 3 | Jinshe Jianfa |
| 4 | Taiji Quan |
| 5 | Daiyu Zanghua |
| 6 | Weituo Zhang |
| 7 | Dagou Bangfa |
+----------+----------------+
7 rows in set
Time: 0.009s
-- 两表查询
19:21:30 (root@(none)) [hellodb]> select st.name,sc.courseid,sc.score
-> from students st
-> inner join scores sc on st.stuid = sc.stuid;
+-------------+----------+-------+
| name | courseid | score |
+-------------+----------+-------+
| Shi Zhongyu | 2 | 77 |
| Shi Zhongyu | 6 | 93 |
| Shi Potian | 2 | 47 |
| Shi Potian | 5 | 97 |
| Xie Yanke | 2 | 88 |
| Xie Yanke | 6 | 75 |
| Ding Dian | 5 | 71 |
| Ding Dian | 2 | 89 |
| Yu Yutong | 1 | 39 |
| Yu Yutong | 7 | 63 |
| Shi Qing | 1 | 96 |
| Xi Ren | 1 | 86 |
| Xi Ren | 7 | 83 |
| Lin Daiyu | 4 | 57 |
| Lin Daiyu | 3 | 93 |
+-------------+----------+-------+
15 rows in set
Time: 0.010s
-- 三表查询
19:24:36 (root@(none)) [hellodb]> select st.name,co.course,sc.score
-> from students st
-> inner join scores sc on st.stuid = sc.stuid
-> inner join courses co on sc.courseid = co.courseid;
+-------------+----------------+-------+
| name | course | score |
+-------------+----------------+-------+
| Shi Zhongyu | Kuihua Baodian | 77 |
| Shi Zhongyu | Weituo Zhang | 93 |
| Shi Potian | Kuihua Baodian | 47 |
| Shi Potian | Daiyu Zanghua | 97 |
| Xie Yanke | Kuihua Baodian | 88 |
| Xie Yanke | Weituo Zhang | 75 |
| Ding Dian | Daiyu Zanghua | 71 |
| Ding Dian | Kuihua Baodian | 89 |
| Yu Yutong | Hamo Gong | 39 |
| Yu Yutong | Dagou Bangfa | 63 |
| Shi Qing | Hamo Gong | 96 |
| Xi Ren | Hamo Gong | 86 |
| Xi Ren | Dagou Bangfa | 83 |
| Lin Daiyu | Taiji Quan | 57 |
| Lin Daiyu | Jinshe Jianfa | 93 |
+-------------+----------------+-------+
15 rows in set
Time: 0.010s
select语句处理的顺序
查询执行路径中的组件：查询缓存，解析器，预处理器，优化器，查询执行引擎，存储引擎
select语句执行的流程：
FROM CLause ---> WHERE CLause ---> GROUP BY ---> HAVING CLause ---> SELECT ---> ORDER BY ---> LIMIT
视图
视图：虚拟表，保存有实表的查询结果，相当于别名
创建视图
CREATE
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
查看视图定义
-- 只能查看视图定义
SHOW CREATE VIEW view_name
-- 可以查看视图和表
SHOW CREATE TABLE tbl_name
删除视图
DROP VIEW [IF EXISTS]
view_name [, view_name] ...
[RESTRICT | CASCADE]
注意⚠️：视图中的数据事实上存储于“基表”中，因此修改视图的数据，就是对“基表”数据的修改，而且不建议直接修改视图中的数据，会影响“基表”数据的准确性
实例：
-- 之前使用两个内连接查询了一个带有学生姓名的成绩表
09:39:17 (root@(none)) [hellodb]> select st.name,co.course,sc.score
-> from students st
-> inner join scores sc on st.stuid = sc.stuid
-> inner join courses co on sc.courseid = co.courseid;
+-------------+----------------+-------+
| name | course | score |
+-------------+----------------+-------+
| Shi Zhongyu | Kuihua Baodian | 77 |
| Shi Zhongyu | Weituo Zhang | 93 |
| Shi Potian | Kuihua Baodian | 47 |
| Shi Potian | Daiyu Zanghua | 97 |
| Xie Yanke | Kuihua Baodian | 88 |
| Xie Yanke | Weituo Zhang | 75 |
| Ding Dian | Daiyu Zanghua | 71 |
| Ding Dian | Kuihua Baodian | 89 |
| Yu Yutong | Hamo Gong | 39 |
| Yu Yutong | Dagou Bangfa | 63 |
| Shi Qing | Hamo Gong | 96 |
| Xi Ren | Hamo Gong | 86 |
| Xi Ren | Dagou Bangfa | 83 |
| Lin Daiyu | Taiji Quan | 57 |
| Lin Daiyu | Jinshe Jianfa | 93 |
+-------------+----------------+-------+
15 rows in set
Time: 0.010s
-- 我们可以将这个查询创建一个视图，具体如下：
-- 创建视图
09:42:05 (root@(none)) [hellodb]> create view v_st_co_sc as
-> select st.name,co.course,sc.score
-> from students st
-> inner join scores sc on st.stuid = sc.stuid
-> inner join courses co on sc.courseid = co.courseid;
Query OK, 0 rows affected
Time: 0.076s
-- 查看这个视图
09:42:57 (root@(none)) [hellodb]> select * from v_st_co_sc;
+-------------+----------------+-------+
| name | course | score |
+-------------+----------------+-------+
| Shi Zhongyu | Kuihua Baodian | 77 |
| Shi Zhongyu | Weituo Zhang | 93 |
| Shi Potian | Kuihua Baodian | 47 |
| Shi Potian | Daiyu Zanghua | 97 |
| Xie Yanke | Kuihua Baodian | 88 |
| Xie Yanke | Weituo Zhang | 75 |
| Ding Dian | Daiyu Zanghua | 71 |
| Ding Dian | Kuihua Baodian | 89 |
| Yu Yutong | Hamo Gong | 39 |
| Yu Yutong | Dagou Bangfa | 63 |
| Shi Qing | Hamo Gong | 96 |
| Xi Ren | Hamo Gong | 86 |
| Xi Ren | Dagou Bangfa | 83 |
| Lin Daiyu | Taiji Quan | 57 |
| Lin Daiyu | Jinshe Jianfa | 93 |
+-------------+----------------+-------+
15 rows in set
Time: 0.009s
-- 查看hellodb库中的所有的表
09:43:41 (root@(none)) [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| emp |
| scores |
| students |
| teachers |
| toc |
| user |
| v_st_co_sc |
+-------------------+
10 rows in set
Time: 0.008s
-- 查看v_st_co_sc表的信息
09:44:23 (root@(none)) [hellodb]> show table status like 'v_st_co_sc'\G
***************************[ 1. row ]***************************
Name | v_st_co_sc
Engine | <null>
Version | <null>
Row_format | <null>
Rows | <null>
Avg_row_length | <null>
Data_length | <null>
Max_data_length | <null>
Index_length | <null>
Data_free | <null>
Auto_increment | <null>
Create_time | <null>
Update_time | <null>
Check_time | <null>
Collation | <null>
Checksum | <null>
Create_options | <null>
Comment | VIEW
1 row in set
Time: 0.002s
-- 当我们创建视图后，因为他是一个虚拟表，所以在数据目录下不会有相关的数据文件存在的
pwd
/data/mysql/data/hellodb
ls
classes.frm coc.frm courses.frm db.opt scores.frm students.frm teachers.frm toc.frm user.frm
classes.MYD coc.MYD courses.MYD emp.frm scores.MYD students.MYD teachers.MYD toc.MYD user.ibd
classes.MYI coc.MYI courses.MYI emp.ibd scores.MYI students.MYI teachers.MYI toc.MYI v_st_co_sc.frm
123
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
