Mysql数据库-DQL数据查询语言
DQL数据查询语言
- 1. 单表查询
- 1.1 语法
- 1.2 where子句
- 1.2.1 条件关系运算符
- 1.2.2 条件逻辑运算符
- 1.3 like子句
- 1.4 对查询结果处理
- 1.4.1 设置查询列
- 1.4.2 计算列
- 1.4.3 as取字段别名
- 1.4.4 distinct去重
- 1.5 order by 排序
- 1.5.1 升序
- 1.5.2 降序
- 1.5.3 单字段排序
- 1.5.4 多字段排序
- 1.6 聚合函数
- 1.6.1 count()总数
- 1.6.2 min()最小值
- 1.6.3 max()最大值
- 1.6.4 sum()求和
- 1.6.5 avg()平均值
- 1.7 日期函数
- now()函数
- sysdate()函数
- 1.8 字符串函数
- 1.8.1 concat 拼接
- 1.8.2 upper 大写
- 1.8.3 lower 小写
- 1.8.4 substring 截取
- 1.9 分组查询 group by...having
- 1.10 分页查询limit
- 2. 多表联查
- 2.1 数据准备
- 2.2 内连接 inner join
- 2.3 左链接 left join
- 2.4 右链接 right join
- 2.5 子查询
1. 单表查询
1.1 语法
select * from
1.2 where子句
1.2.1 条件关系运算符
| 关系运算符 | 符号 |
|---|---|
| 等于 | = |
| 不等于 | != 或 <> |
| 大于 | > |
| 小于 | < |
| 大于等于 | >= |
| 小于等于 | <= |
| 介于v1和v2之间 | between v1 and v2 |
# 查询students表中，20-28岁之间的数据，并进行升序排列，包含20和28岁。
mysql> select * from students where stu_age not between 20 and 28 order by stu_age;
+----------+----------+------------+---------+-------------+---------+---------------------+
| stu_num | stu_name | stu_gender | stu_age | stu_tel | stu_qq | stu_datetime |
+----------+----------+------------+---------+-------------+---------+---------------------+
| 20211218 | 刘依 | 女 | 16 | 13000000001 | 0930291 | NULL |
| 20211215 | 周一 | 女 | 17 | 13511111111 | 223233 | NULL |
| 20211221 | 周小 | 女 | 18 | 13563764832 | 2112233 | 2021-12-17 14:31:53 |
| 20211222 | 李小小 | 女 | 18 | 13563764833 | 2112233 | 2021-12-17 14:34:59 |
| 20211217 | 刘备 | 女 | 30 | 13000000000 | 0930293 | NULL |
+----------+----------+------------+---------+-------------+---------+---------------------+
1.2.2 条件逻辑运算符
| 逻辑运算符 | 符号 |
|---|---|
| 并 | and |
| 或 | or |
| 非 | not |
1.3 like子句
模糊查询：select * from
在like关键字后的eg表达式中
% 表示任意多个字符 【%S% 包含字母S】
_ 表示任意一个字符 【_S% 第二个字母为S】1. 查询students表中，stu_name中含有“小”的数据
mysql> select * from students where stu_name like '%小%';
+----------+----------+------------+---------+-------------+---------+---------------------+
| stu_num | stu_name | stu_gender | stu_age | stu_tel | stu_qq | stu_datetime |
+----------+----------+------------+---------+-------------+---------+---------------------+
| 20211212 | 小黑 | NULL | NULL | 13999999998 | NULL | NULL |
| 20211213 | 小黑 | 男 | 25 | 13999999998 | 123456 | NULL |
| 20211214 | 小黑 | 男 | 25 | 13999999998 | 123456 | NULL |
| 20211221 | 周小 | 女 | 18 | 13563764832 | 2112233 | 2021-12-17 14:31:53 |
+----------+----------+------------+---------+-------------+---------+---------------------+2. 查询students表中，stu_name中第一个字是“小”的数据
mysql> select * from students where stu_name like '小%';
+----------+----------+------------+---------+-------------+--------+--------------+
| stu_num | stu_name | stu_gender | stu_age | stu_tel | stu_qq | stu_datetime |
+----------+----------+------------+---------+-------------+--------+--------------+
| 20211212 | 小黑 | NULL | NULL | 13999999998 | NULL | NULL |
| 20211213 | 小黑 | 男 | 25 | 13999999998 | 123456 | NULL |
| 20211214 | 小黑 | 男 | 25 | 13999999998 | 123456 | NULL |
+----------+----------+------------+---------+-------------+--------+--------------+3. 查询students表中，stu_name中第二个字是“小”的数据mysql> select * from students where stu_name like '_小%';
+----------+----------+------------+---------+-------------+---------+---------------------+
| stu_num | stu_name | stu_gender | stu_age | stu_tel | stu_qq | stu_datetime |
+----------+----------+------------+---------+-------------+---------+---------------------+
| 20211221 | 周小 | 女 | 18 | 13563764832 | 2112233 | 2021-12-17 14:31:53 |
| 20211222 | 李小小 | 女 | 18 | 13563764833 | 2112233 | 2021-12-17 14:34:59 |
+----------+----------+------------+---------+-------------+---------+---------------------+
1.4 对查询结果处理
1.4.1 设置查询列
mysql> select stu_num,stu_name,stu_age from students;
+----------+----------+---------+
| stu_num | stu_name | stu_age |
+----------+----------+---------+
| 20211212 | 小黑 | NULL |
| 20211213 | 小黑 | 25 |
| 20211214 | 小黑 | 25 |
| 20211215 | 周一 | 17 |
| 20211216 | 周二 | 28 |
| 20211217 | 刘备 | 30 |
| 20211218 | 刘依 | 16 |
| 20211219 | 周五 | 20 |
| 20211220 | 周六 | 20 |
| 20211221 | 周小 | 18 |
| 20211222 | 李小小 | 18 |
+----------+----------+---------+
1.4.2 计算列
## 计算students表中，stu_age列年龄总和
mysql> select sum(stu_age) from students;
+--------------+
| sum(stu_age) |
+--------------+
| 217 |
+--------------+
1 row in set (0.00 sec)
1.4.3 as取字段别名
## 计算students表中，stu_age列年龄总和，显示字段取名为“年龄总和”
mysql> select sum(stu_age) as 年龄总和 from students;
+----------+
| 年龄总和 |
+----------+
| 217 |
+----------+
1 row in set (0.00 sec)
1.4.4 distinct去重
## 去掉年龄重复值，并按照年龄大小进行升序
mysql> select distinct(stu_age) from students order by stu_age;
+---------+
| stu_age |
+---------+
| NULL |
| 16 |
| 17 |
| 18 |
| 20 |
| 25 |
| 28 |
| 30 |
+---------+
8 rows in set (0.00 sec)
1.5 order by 排序
数据表中按照指定的列进行排序，降序或升序，默认为升序。
语法：
select * from
- asc 按照指定的列升序 （默认）
- desc 按照指定的列降序
1.5.1 升序
#按照stu_gender进行升序排列
select * from students where stu_age > 10 order by stu_gender asc;
1.5.2 降序
# 按照stu_gender列进行降序排列
select * from students where stu_age > 10 order by stu_gender desc;
1.5.3 单字段排序
# 按照stu_gender列单字段进行降序排列
select * from students where stu_age > 10 order by stu_gender desc;
1.5.4 多字段排序
# 先按照stu_gender升序排列，在此基础上在按照stu_age降序排列。
select * from students where stu_age > 10 order by stu_gender asc,stu_age desc;
1.6 聚合函数
1.6.1 count()总数
# 统计students表中年龄大于20岁的人数select count(1) as 年龄大于20人数统计 from students where stu_age >20;
+--------------------+
| 年龄大于20人数统计 |
+--------------------+
| 4 |
+--------------------+
1.6.2 min()最小值
# 获取students表中，男生最小年龄
mysql> select min(stu_age) as 获取男生中最小年龄 from students where stu_gender = '男';
+--------------------+
| 获取男生中最小年龄 |
+--------------------+
| 25 |
+--------------------+
1 row in set (0.00 sec)
1.6.3 max()最大值
# 获取students表中，女生最大年龄
mysql> select max(stu_age) as 获取女生中最大年龄 from students where stu_gender = '女';
+--------------------+
| 获取女生中最大年龄 |
+--------------------+
| 30 |
+--------------------+
1 row in set (0.00 sec)
1.6.4 sum()求和
mysql> select sum(stu_age) as 男女年龄总和, min(stu_age) as 年龄最小值, max(stu_age) as 年龄最大值, avg(stu_age) as 平均年龄 from students;
+--------------+------------+------------+----------+
| 男女年龄总和 | 年龄最小值 | 年龄最大值 | 平均年龄 |
+--------------+------------+------------+----------+
| 141 | 16 | 30 | 23.5000 |
+--------------+------------+------------+----------+
1.6.5 avg()平均值
mysql> select sum(stu_age) as 男女年龄总和, min(stu_age) as 年龄最小值, max(stu_age) as 年龄最大值, avg(stu_age) as 平均年龄 from students;
+--------------+------------+------------+----------+
| 男女年龄总和 | 年龄最小值 | 年龄最大值 | 平均年龄 |
+--------------+------------+------------+----------+
| 141 | 16 | 30 | 23.5000 |
+--------------+------------+------------+----------+
1.7 日期函数
now()函数
mysql> insert into students values ('20211219','周五','女',20,'13562738283','222999333',now());
Query OK, 1 row affected (0.01 sec)mysql> select * from students;
+----------+----------+------------+---------+-------------+-----------+---------------------+
| stu_num | stu_name | stu_gender | stu_age | stu_tel | stu_qq | stu_datetime |
+----------+----------+------------+---------+-------------+-----------+---------------------+
| 20211212 | 小黑 | NULL | NULL | 13999999998 | NULL | NULL |
| 20211213 | 小黑 | 男 | 25 | 13999999998 | 123456 | NULL |
| 20211214 | 小黑 | 男 | 25 | 13999999998 | 123456 | NULL |
| 20211215 | 周一 | 女 | 17 | 13511111111 | 223233 | NULL |
| 20211216 | 周二 | 男 | 28 | 13511111112 | 293992 | NULL |
| 20211217 | 刘备 | 女 | 30 | 13000000000 | 0930293 | NULL |
| 20211218 | 刘依 | 女 | 16 | 13000000001 | 0930291 | NULL |
| 20211219 | 周五 | 女 | 20 | 13562738283 | 222999333 | 2021-12-17 11:01:51 |
+----------+----------+------------+---------+-------------+-----------+---------------------+
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2021-12-17 11:06:55 |
+---------------------+
1 row in set (0.00 sec)
sysdate()函数
mysql> insert into students values ('20211220','周六','女',20,'13562738284','222999334',sysdate());
Query OK, 1 row affected (0.05 sec)mysql> select * from students;
+----------+----------+------------+---------+-------------+-----------+---------------------+
| stu_num | stu_name | stu_gender | stu_age | stu_tel | stu_qq | stu_datetime |
+----------+----------+------------+---------+-------------+-----------+---------------------+
| 20211212 | 小黑 | NULL | NULL | 13999999998 | NULL | NULL |
| 20211213 | 小黑 | 男 | 25 | 13999999998 | 123456 | NULL |
| 20211214 | 小黑 | 男 | 25 | 13999999998 | 123456 | NULL |
| 20211215 | 周一 | 女 | 17 | 13511111111 | 223233 | NULL |
| 20211216 | 周二 | 男 | 28 | 13511111112 | 293992 | NULL |
| 20211217 | 刘备 | 女 | 30 | 13000000000 | 0930293 | NULL |
| 20211218 | 刘依 | 女 | 16 | 13000000001 | 0930291 | NULL |
| 20211219 | 周五 | 女 | 20 | 13562738283 | 222999333 | 2021-12-17 11:01:51 |
| 20211220 | 周六 | 女 | 20 | 13562738284 | 222999334 | 2021-12-17 11:05:42 |mysql> select sysdate();
+---------------------+
| sysdate() |
+---------------------+
| 2021-12-17 11:06:27 |
+---------------------+
1 row in set (0.00 sec)
1.8 字符串函数
通过sql指令对字符串进行处理
1.8.1 concat 拼接
concat(column1,colunm2...)
拼接students表中stu_name，stu_gender，stu_age
mysql> select concat(stu_name,'-',stu_gender,'-',stu_age,'岁') from students;
+--------------------------------------------------+
| concat(stu_name,'-',stu_gender,'-',stu_age,'岁') |
+--------------------------------------------------+
| NULL |
| 小黑-男-25岁 |
| 周二-男-28岁 |
| Tom-女-18岁 |
+--------------------------------------------------+
12 rows in set (0.00 sec)
1.8.2 upper 大写
### 将studens表中，stu_name列大写字母变成大写。
mysql> select upper(stu_name) from students ;
+-----------------+
| upper(stu_name) |
+-----------------+
| 小黑 |
| TOM |
+-----------------+
12 rows in set (0.04 sec)1.8.3 lower 小写
### 将studens表中，stu_name列大写字母变成小写。
mysql> select lower(stu_name) from students ;
+-----------------+
| lower(stu_name) |
+-----------------+
| 小黑 |
| tom |
+-----------------+
12 rows in set (0.01 sec)
1.8.4 substring 截取
substring(column,strat,len) 从start开始截取，截取len位，start从1开始计算
### 在students表中，截取显示stu_tel手机号的后四位。
mysql> select * from students;
+----------+----------+------------+---------+-------------+-----------+---------------------+
| stu_num | stu_name | stu_gender | stu_age | stu_tel | stu_qq | stu_datetime |
+----------+----------+------------+---------+-------------+-----------+---------------------+
| 20211212 | 小黑 | NULL | NULL | 13999999998 | NULL | NULL |
| 20211213 | 小黑 | 男 | 25 | 13999999998 | 123456 | NULL |
| 20211214 | 小黑 | 男 | 25 | 13999999998 | 123456 | NULL |
| 20211215 | 周一 | 女 | 17 | 13511111111 | 223233 | NULL |
| 20211216 | 周二 | 男 | 28 | 13511111112 | 293992 | NULL |
| 20211217 | 刘备 | 女 | 30 | 13000000000 | 0930293 | NULL |
| 20211218 | 刘依 | 女 | 16 | 13000000001 | 0930291 | NULL |
| 20211219 | 周五 | 女 | 20 | 13562738283 | 222999333 | 2021-12-17 11:01:51 |
| 20211220 | 周六 | 女 | 20 | 13562738284 | 222999334 | 2021-12-17 11:05:42 |
| 20211221 | 周小 | 女 | 18 | 13563764832 | 2112233 | 2021-12-17 14:31:53 |
| 20211222 | 李小小 | 女 | 18 | 13563764833 | 2112233 | 2021-12-17 14:34:59 |
| 20211223 | Tom | 女 | 18 | 13563764834 | 2112223 | 2021-12-17 15:47:45 |
+----------+----------+------------+---------+-------------+-----------+---------------------+
12 rows in set (0.00 sec)mysql> select stu_num,stu_name, substring(stu_tel,8,4) as 手机号后四位 from students;
+----------+----------+--------------+
| stu_num | stu_name | 手机号后四位 |
+----------+----------+--------------+
| 20211212 | 小黑 | 9998 |
| 20211213 | 小黑 | 9998 |
| 20211214 | 小黑 | 9998 |
| 20211215 | 周一 | 1111 |
| 20211216 | 周二 | 1112 |
| 20211217 | 刘备 | 0000 |
| 20211218 | 刘依 | 0001 |
| 20211219 | 周五 | 8283 |
| 20211220 | 周六 | 8284 |
| 20211221 | 周小 | 4832 |
| 20211222 | 李小小 | 4833 |
| 20211223 | Tom | 4834 |
+----------+----------+--------------+
12 rows in set (0.00 sec)
1.9 分组查询 group by…having
语法
select 分组字段/聚合函数 from 表名 [where 条件] group by 分组列名 [having 条件]
- select 后使用 * 显示对查询结果进行分组之后，显示每组的第一条数据（无意义）
- select 后通常显示分组字段和聚合函数
- 语句执行顺序：先where从数据表查询记录，在group by对查询记录进行分组，然后执行having对分组后的数据进行筛选。
在students表中，查询性别为“女”的学生，按年龄进行分组，然后分别统计每组的人数，在筛选当前组人数 > 1 的组，在按照年龄升序进行显示。
mysql> select * from students;
+----------+----------+------------+---------+-------------+-----------+---------------------+
| stu_num | stu_name | stu_gender | stu_age | stu_tel | stu_qq | stu_datetime |
+----------+----------+------------+---------+-------------+-----------+---------------------+
| 20211212 | 小黑 | NULL | NULL | 13999999998 | NULL | NULL |
| 20211213 | 小黑 | 男 | 25 | 13999999998 | 123456 | NULL |
| 20211214 | 小黑 | 男 | 25 | 13999999998 | 123456 | NULL |
| 20211215 | 周一 | 女 | 17 | 13511111111 | 223233 | NULL |
| 20211216 | 周二 | 男 | 28 | 13511111112 | 293992 | NULL |
| 20211217 | 刘备 | 女 | 30 | 13000000000 | 0930293 | NULL |
| 20211218 | 刘依 | 女 | 16 | 13000000001 | 0930291 | NULL |
| 20211219 | 周五 | 女 | 20 | 13562738283 | 222999333 | 2021-12-17 11:01:51 |
| 20211220 | 周六 | 女 | 20 | 13562738284 | 222999334 | 2021-12-17 11:05:42 |
| 20211221 | 周小 | 女 | 18 | 13563764832 | 2112233 | 2021-12-17 14:31:53 |
| 20211222 | 李小小 | 女 | 18 | 13563764833 | 2112233 | 2021-12-17 14:34:59 |mysql> select stu_age,count(stu_num)
from students
where stu_gender = '女'
group by stu_age
having count(stu_num) > 1
order by stu_age asc;
+---------+----------------+
| stu_age | count(stu_num) |
+---------+----------------+
| 18 | 2 |
| 20 | 2 |
+---------+----------------+
2 rows in set (0.00 sec)
1.10 分页查询limit
语法：select * from 表名 where条件 limit (m,n)
- limit(m,n)：从第m条数据显示查询，显示n条，m从0开始计算
### students共有12条数据，
mysql> select * from students;
+----------+----------+------------+---------+-------------+-----------+---------------------+
| stu_num | stu_name | stu_gender | stu_age | stu_tel | stu_qq | stu_datetime |
+----------+----------+------------+---------+-------------+-----------+---------------------+
| 20211212 | 小黑 | NULL | NULL | 13999999998 | NULL | NULL |
| 20211213 | 小黑 | 男 | 25 | 13999999998 | 123456 | NULL |
| 20211214 | 小黑 | 男 | 25 | 13999999998 | 123456 | NULL |
| 20211215 | 周一 | 女 | 17 | 13511111111 | 223233 | NULL |
| 20211216 | 周二 | 男 | 28 | 13511111112 | 293992 | NULL |
| 20211217 | 刘备 | 女 | 30 | 13000000000 | 0930293 | NULL |
| 20211218 | 刘依 | 女 | 16 | 13000000001 | 0930291 | NULL |
| 20211219 | 周五 | 女 | 20 | 13562738283 | 222999333 | 2021-12-17 11:01:51 |
| 20211220 | 周六 | 女 | 20 | 13562738284 | 222999334 | 2021-12-17 11:05:42 |
| 20211221 | 周小 | 女 | 18 | 13563764832 | 2112233 | 2021-12-17 14:31:53 |
| 20211222 | 李小小 | 女 | 18 | 13563764833 | 2112233 | 2021-12-17 14:34:59 |
| 20211223 | Tom | 女 | 18 | 13563764834 | 2112223 | 2021-12-17 15:47:45 |
+----------+----------+------------+---------+-------------+-----------+---------------------+
12 rows in set (0.00 sec)mysql> select * from students limit 3,4;
+----------+----------+------------+---------+-------------+---------+--------------+
| stu_num | stu_name | stu_gender | stu_age | stu_tel | stu_qq | stu_datetime |
+----------+----------+------------+---------+-------------+---------+--------------+
| 20211215 | 周一 | 女 | 17 | 13511111111 | 223233 | NULL |
| 20211216 | 周二 | 男 | 28 | 13511111112 | 293992 | NULL |
| 20211217 | 刘备 | 女 | 30 | 13000000000 | 0930293 | NULL |
| 20211218 | 刘依 | 女 | 16 | 13000000001 | 0930291 | NULL |
+----------+----------+------------+---------+-------------+---------+--------------+
4 rows in set (0.00 sec)mysql> select * from students limit 3;
+----------+----------+------------+---------+-------------+--------+--------------+
| stu_num | stu_name | stu_gender | stu_age | stu_tel | stu_qq | stu_datetime |
+----------+----------+------------+---------+-------------+--------+--------------+
| 20211212 | 小黑 | NULL | NULL | 13999999998 | NULL | NULL |
| 20211213 | 小黑 | 男 | 25 | 13999999998 | 123456 | NULL |
| 20211214 | 小黑 | 男 | 25 | 13999999998 | 123456 | NULL |
+----------+----------+------------+---------+-------------+--------+--------------+
3 rows in set (0.00 sec)mysql> select * from students limit 3,5;
+----------+----------+------------+---------+-------------+-----------+---------------------+
| stu_num | stu_name | stu_gender | stu_age | stu_tel | stu_qq | stu_datetime |
+----------+----------+------------+---------+-------------+-----------+---------------------+
| 20211215 | 周一 | 女 | 17 | 13511111111 | 223233 | NULL |
| 20211216 | 周二 | 男 | 28 | 13511111112 | 293992 | NULL |
| 20211217 | 刘备 | 女 | 30 | 13000000000 | 0930293 | NULL |
| 20211218 | 刘依 | 女 | 16 | 13000000001 | 0930291 | NULL |
| 20211219 | 周五 | 女 | 20 | 13562738283 | 222999333 | 2021-12-17 11:01:51 |
+----------+----------+------------+---------+-------------+-----------+---------------------+
5 rows in set (0.00 sec)mysql> select * from students limit 3,6;
+----------+----------+------------+---------+-------------+-----------+---------------------+
| stu_num | stu_name | stu_gender | stu_age | stu_tel | stu_qq | stu_datetime |
+----------+----------+------------+---------+-------------+-----------+---------------------+
| 20211215 | 周一 | 女 | 17 | 13511111111 | 223233 | NULL |
| 20211216 | 周二 | 男 | 28 | 13511111112 | 293992 | NULL |
| 20211217 | 刘备 | 女 | 30 | 13000000000 | 0930293 | NULL |
| 20211218 | 刘依 | 女 | 16 | 13000000001 | 0930291 | NULL |
| 20211219 | 周五 | 女 | 20 | 13562738283 | 222999333 | 2021-12-17 11:01:51 |
| 20211220 | 周六 | 女 | 20 | 13562738284 | 222999334 | 2021-12-17 11:05:42 |
+----------+----------+------------+---------+-------------+-----------+---------------------+
6 rows in set (0.00 sec)mysql> select * from students limit 3,1;
+----------+----------+------------+---------+-------------+--------+--------------+
| stu_num | stu_name | stu_gender | stu_age | stu_tel | stu_qq | stu_datetime |
+----------+----------+------------+---------+-------------+--------+--------------+
| 20211215 | 周一 | 女 | 17 | 13511111111 | 223233 | NULL |
+----------+----------+------------+---------+-------------+--------+--------------+
1 row in set (0.00 sec)
2. 多表联查
2.1 数据准备
创建学生表students，班级表classs
#学生表
create table students(stu_num char(8) primary key ,stu_name varchar(20) not null,stu_sex char(2) not null,stu_age int not null,stu_id int,CONSTRAINT FK_Students_Class foreign key(stu_id) references class(class_id)
);
# 班级表
create table class(
class_id int primary key auto_increment,
class_name varchar(30) unique not null,
class_remark varchar(200)
);
插入数据
#学生表插入数据
insert into students (stu_num,stu_name,stu_sex,stu_age,stu_id) values('20210001','张三','男',18,1);
insert into students (stu_num,stu_name,stu_sex,stu_age,stu_id) values('20210002','李四','女',26,1);
insert into students (stu_num,stu_name,stu_sex,stu_age,stu_id) values('20210003','王五','女',16,1);
insert into students (stu_num,stu_name,stu_sex,stu_age,stu_id) values('20210004','佳佳','女',18,2);
insert into students (stu_num,stu_name,stu_sex,stu_age,stu_id) values('20210005','依依','男',18,2);
insert into students (stu_num,stu_name,stu_sex,stu_age,stu_id) values('20210006','冰冰','女',22,2);
insert into students (stu_num,stu_name,stu_sex,stu_age,stu_id) values('20210007','钉钉','女',30,3);
insert into students (stu_num,stu_name,stu_sex,stu_age,stu_id) values('20210008','小黑','男',35,null);#班级表插入数据
insert into class (class_id,class_name,class_remark) values(1,'java','东风校区3号楼210');
insert into class (class_id,class_name,class_remark) values(2,'python','东风校区3号楼310');
insert into class (class_id,class_name,class_remark) values(3,'C语言','东风校区3号楼410');
insert into class (class_id,class_name,class_remark) values(4,'PHP','东风校区3号楼510');
select * from students;
+----------+----------+---------+---------+--------+
| stu_num | stu_name | stu_sex | stu_age | stu_id |
+----------+----------+---------+---------+--------+
| 20210001 | 张三 | 男 | 18 | 1 |
| 20210002 | 李四 | 女 | 26 | 1 |
| 20210003 | 王五 | 女 | 16 | 1 |
| 20210004 | 佳佳 | 女 | 18 | 2 |
| 20210005 | 依依 | 男 | 18 | 2 |
| 20210006 | 冰冰 | 女 | 22 | 2 |
| 20210007 | 钉钉 | 女 | 30 | 3 |
| 20210008 | 小黑 | 男 | 35 | NULL |
+----------+----------+---------+---------+--------+
select * from class;
+----------+------------+------------------+
| class_id | class_name | class_remark |
+----------+------------+------------------+
| 1 | java | 东风校区3号楼210 |
| 2 | python | 东风校区3号楼310 |
| 3 | C语言 | 东风校区3号楼410 |
| 4 | PHP | 东风校区3号楼510 |
+----------+------------+------------------+
2.2 内连接 inner join
SELECT * from students inner join class on stu_id = class_id;
+----------+----------+---------+---------+--------+----------+------------+------------------+
| stu_num | stu_name | stu_sex | stu_age | stu_id | class_id | class_name | class_remark |
+----------+----------+---------+---------+--------+----------+------------+------------------+
| 20210001 | 张三 | 男 | 18 | 1 | 1 | java | 东风校区3号楼210 |
| 20210002 | 李四 | 女 | 26 | 1 | 1 | java | 东风校区3号楼210 |
| 20210003 | 王五 | 女 | 16 | 1 | 1 | java | 东风校区3号楼210 |
| 20210004 | 佳佳 | 女 | 18 | 2 | 2 | python | 东风校区3号楼310 |
| 20210005 | 依依 | 男 | 18 | 2 | 2 | python | 东风校区3号楼310 |
| 20210006 | 冰冰 | 女 | 22 | 2 | 2 | python | 东风校区3号楼310 |
| 20210007 | 钉钉 | 女 | 30 | 3 | 3 | C语言 | 东风校区3号楼410 |
+----------+----------+---------+---------+--------+----------+------------+------------------+
2.3 左链接 left join
SELECT * from students left join class on stu_id = class_id;
+----------+----------+---------+---------+--------+----------+------------+------------------+
| stu_num | stu_name | stu_sex | stu_age | stu_id | class_id | class_name | class_remark |
+----------+----------+---------+---------+--------+----------+------------+------------------+
| 20210001 | 张三 | 男 | 18 | 1 | 1 | java | 东风校区3号楼210 |
| 20210002 | 李四 | 女 | 26 | 1 | 1 | java | 东风校区3号楼210 |
| 20210003 | 王五 | 女 | 16 | 1 | 1 | java | 东风校区3号楼210 |
| 20210004 | 佳佳 | 女 | 18 | 2 | 2 | python | 东风校区3号楼310 |
| 20210005 | 依依 | 男 | 18 | 2 | 2 | python | 东风校区3号楼310 |
| 20210006 | 冰冰 | 女 | 22 | 2 | 2 | python | 东风校区3号楼310 |
| 20210007 | 钉钉 | 女 | 30 | 3 | 3 | C语言 | 东风校区3号楼410 |
| 20210008 | 小黑 | 男 | 35 | NULL | NULL | NULL | NULL |
+----------+----------+---------+---------+--------+----------+------------+------------------+
2.4 右链接 right join
SELECT * from students right join class on stu_id = class_id;
+----------+----------+---------+---------+--------+----------+------------+------------------+
| stu_num | stu_name | stu_sex | stu_age | stu_id | class_id | class_name | class_remark |
+----------+----------+---------+---------+--------+----------+------------+------------------+
| 20210001 | 张三 | 男 | 18 | 1 | 1 | java | 东风校区3号楼210 |
| 20210002 | 李四 | 女 | 26 | 1 | 1 | java | 东风校区3号楼210 |
| 20210003 | 王五 | 女 | 16 | 1 | 1 | java | 东风校区3号楼210 |
| 20210004 | 佳佳 | 女 | 18 | 2 | 2 | python | 东风校区3号楼310 |
| 20210005 | 依依 | 男 | 18 | 2 | 2 | python | 东风校区3号楼310 |
| 20210006 | 冰冰 | 女 | 22 | 2 | 2 | python | 东风校区3号楼310 |
| 20210007 | 钉钉 | 女 | 30 | 3 | 3 | C语言 | 东风校区3号楼410 |
| NULL | NULL | NULL | NULL | NULL | 4 | PHP | 东风校区3号楼510 |
+----------+----------+---------+---------+--------+----------+------------+------------------+
2.5 子查询
select * from students where stu_id in (select class_id from class where class_name = 'java');
+----------+----------+---------+---------+--------+
| stu_num | stu_name | stu_sex | stu_age | stu_id |
+----------+----------+---------+---------+--------+
| 20210001 | 张三 | 男 | 18 | 1 |
| 20210002 | 李四 | 女 | 26 | 1 |
| 20210003 | 王五 | 女 | 16 | 1 |
+----------+----------+---------+---------+--------+
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！