DML和DQL语句(重点)

详细介绍了DML和DQL语句包括SQL的单表和多表查询对于工作中非DBA来说已经够了

2.5. DML 语句

2.5.1. INSERT 语句

功能:一次插入一行或多行数据

语法:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
  [INTO] tbl_name [(col_name,...)]
  {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
  [ ON DUPLICATE KEY UPDATE #如果重复更新之
  col_name=expr
    [, col_name=expr] ... ]
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
  [INTO] tbl_name
  SET col_name={expr | DEFAULT}, ...
  [ ON DUPLICATE KEY UPDATE
  col_name=expr
    [, col_name=expr] ... ]
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
  [INTO] tbl_name [(col_name,...)]
  SELECT ...
  [ ON DUPLICATE KEY UPDATE
  col_name=expr
    [, col_name=expr] ... ]

简化语法

INSERT tbl_name [(col1,...)] VALUES (val1,...), (val21,...)

举例

#向student表中添加三条数据  
insert student(name,age) values('haha',20),('lala',18); #因为id和gender一个是自动生成一个是有默认值,所以可以不用指定
insert student values(null,'heihei',22,'F');    #如果不加字段名,就需要填写每个字段的信息,因为id为自动填写我们可以填写null即可

2.5.2. UPDATE 语句

语法:

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
  SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
  [WHERE where_condition]
  [ORDER BY ...]
  [LIMIT row_count]
注意:一定要有限制条件,否则将修改所有行的指定字段
可利用mysql 选项避免此错误:
[root@centos8 ~]#vim /etc/my.cnf
[mysql]
safe-updates

举例

#修改表student中lala的gender值,修改为F
update student set gender='F' where id=11;

#注意:一定要有限制条件(where id =11),否则将修改所有行的指定字段

2.5.3. DELETE 语句

删除表中数据,但不会自动缩减数据文件的大小。

语法

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
  [WHERE where_condition]
  [ORDER BY ...]
  [LIMIT row_count]
#可先排序再指定删除的行数
注意:一定要有限制条件,否则将清空表中的所有数据

如果想清空表,保留表结构,也可以使用下面语句,此语句会自动缩减数据文件的大小。

TRUNCATE TABLE tbl_name;

缩减表大小

OPTIMIZE TABLE tb_name

举例

#删除id为11的信息
delete from student where id=11;

2.6. DQL 语句

2.6.1. 导入数据库

方法一

方法二

2.6.2. ★★单表操作★★

语法

SELECT
[ALL | DISTINCT | DISTINCTROW ]
[SQL_CACHE | SQL_NO_CACHE]
select_expr [, select_expr ...]
  [FROM table_references
  [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:正则表达式,索引失效,不建议使用
    REGEXP:匹配字符串可用正则表达式书写模式,同上
    逻辑操作符:NOT,AND,OR,XOR

#GROUP BY:根据指定的条件把查询结果进行"分组"以用于做"聚合"运算
    常见聚合函数: count(), sum(), max(), min(), avg(),注意:聚合函数不对null统计
    HAVING: 对分组聚合运算后的结果指定过滤条件
    一旦分组 group by ,select语句后只跟分组的字段,聚合函数

#ORDER BY: 根据指定的字段对查询结果进行排序
    升序:ASC
    降序:DESC

#LIMIT [[offset,]row_count]:对查询的结果进行输出行数数量限制,跳过offset,显示row_count行,offset默为值为0

#对查询结果中的数据请求施加"锁"
    FOR UPDATE: 写锁,独占或排它锁,只有一个读和写操作
    LOCK IN SHARE MODE: 读锁,共享锁,同时多个读操作

举例

#字段别名————指定列输出并且为输出字段重命名,不改变表中信息是指以指定方式显示
MariaDB [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 |

MariaDB [hellodb]> select stuid 学员ID ,name 姓名,gender as 性别 from students;
+----------+---------------+--------+
| 学员ID   | 姓名          | 性别   |
+----------+---------------+--------+
|        1 | Shi Zhongyu   | M      |
|        2 | Shi Potian    | M      |
|        3 | Xie Yanke     | M      |

#order by————指定字段排序输出
MariaDB [hellodb]> select *from students order by name desc limit 2;    #name字段降序输出前两个(0,1)
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
+-------+---------------+-----+--------+---------+-----------+
2 rows in set (0.000 sec)

MariaDB [hellodb]> select *from students order by name desc limit 1,2;  #name字段降序输出从1开始输出两位(1,2)
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
+-------+---------------+-----+--------+---------+-----------+
2 rows in set (0.000 sec)

MariaDB [hellodb]> select *from students order by name desc limit 25;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |


#where
MariaDB [hellodb]> select *from students where stuid>2 and stuid <4;
+-------+-----------+-----+--------+---------+-----------+
| StuID | Name      | Age | Gender | ClassID | TeacherID |
+-------+-----------+-----+--------+---------+-----------+
|     3 | Xie Yanke |  53 | M      |       2 |        16 |
+-------+-----------+-----+--------+---------+-----------+
1 row in set (0.001 sec)

MariaDB [hellodb]> select *from students where between 2 and 4;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'between 2 and 4' at line 1
MariaDB [hellodb]> select *from students where stuid between 2 and 4;
+-------+------------+-----+--------+---------+-----------+
| StuID | Name       | Age | Gender | ClassID | TeacherID |
+-------+------------+-----+--------+---------+-----------+
|     2 | Shi Potian |  22 | M      |       1 |         7 |
|     3 | Xie Yanke  |  53 | M      |       2 |        16 |
|     4 | Ding Dian  |  32 | M      |       4 |         4 |
+-------+------------+-----+--------+---------+-----------+
3 rows in set (0.001 sec)


MariaDB [hellodb]> select *from students where stuid <3;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu |  22 | M      |       2 |         3 |
|     2 | Shi Potian  |  22 | M      |       1 |         7 |
+-------+-------------+-----+--------+---------+-----------+
2 rows in set (0.002 sec)

MariaDB [hellodb]> select *from students where gender='m';
+-------+---------------+-----+--------+---------+-----------+
| 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 |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      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 |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
15 rows in set (0.000 sec)

MariaDB [hellodb]> select *from students where gender is null;
Empty set (0.000 sec)

MariaDB [hellodb]> select *from students where gender 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 |
|     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 (0.000 sec)


MariaDB [hellodb]> select *from students where name like 't%'
    -> ;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
|    13 | Tian Boguang |  33 | M      |       2 |      NULL |
+-------+--------------+-----+--------+---------+-----------+
1 row in set (0.000 sec)

MariaDB [hellodb]> select *from students where name like '%t';
Empty set (0.000 sec)

MariaDB [hellodb]> select *from students where name like '%t%';
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
|     2 | Shi Potian   |  22 | M      |       1 |         7 |
|     5 | Yu Yutong    |  26 | M      |       3 |         1 |
|    13 | Tian Boguang |  33 | M      |       2 |      NULL |
+-------+--------------+-----+--------+---------+-----------+
3 rows in set (0.001 sec)

MariaDB [hellodb]> select *from students where name rlike '.*[lo].*';
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu  |  22 | M      |       2 |         3 |
|     2 | Shi Potian   |  22 | M      |       1 |         7 |
|     5 | Yu Yutong    |  26 | M      |       3 |         1 |
|     8 | Lin Daiyu    |  17 | F      |       7 |      NULL |
|    10 | Yue Lingshan |  19 | F      |       3 |      NULL |
|    13 | Tian Boguang |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang  |  17 | F      |       3 |      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 |
|    22 | Xiao Qiao    |  20 | F      |       1 |      NULL |
|    23 | Ma Chao      |  23 | M      |       4 |      NULL |
+-------+--------------+-----+--------+---------+-----------+
13 rows in set (0.001 sec)

MariaDB [hellodb]> select *from students where classid in (2,4,7);
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu  |  22 | M      |       2 |         3 |
|     3 | Xie Yanke    |  53 | M      |       2 |        16 |
|     4 | Ding Dian    |  32 | M      |       4 |         4 |
|     8 | Lin Daiyu    |  17 | F      |       7 |      NULL |
|    13 | Tian Boguang |  33 | M      |       2 |      NULL |
|    15 | Duan Yu      |  19 | M      |       4 |      NULL |
|    17 | Lin Chong    |  25 | M      |       4 |      NULL |
|    18 | Hua Rong     |  23 | M      |       7 |      NULL |
|    20 | Diao Chan    |  19 | F      |       7 |      NULL |
|    23 | Ma Chao      |  23 | M      |       4 |      NULL |
+-------+--------------+-----+--------+---------+-----------+
10 rows in set (0.000 sec)

MariaDB [hellodb]> select *from students where classid not in (2,4,7);
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      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 |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
+-------+---------------+-----+--------+---------+-----------+
13 rows in set (0.000 sec)

#判断是否为nullMariaDB [hellodb]> select *from students where classid = null;       #直接等于不行,需要用<=>Empty set (0.000 sec)MariaDB [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 (0.000 sec)MariaDB [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 (0.000 sec)#ifnull函数判断指定的字段是否为空值,如果空值则使用指定默认值MariaDB [hellodb]> select stuid,name,ifnull(classid,'无班级') from students where classid is null;+-------+-------------+-----------------------------+| stuid | name        | ifnull(classid,'无班级')    |+-------+-------------+-----------------------------+|    24 | Xu Xian     | 无班级                      ||    25 | Sun Dasheng | 无班级                      |+-------+-------------+-----------------------------+2 rows in set (0.000 sec)
#记录去重MariaDB [hellodb]> select distinct gender from students;+--------+| gender |+--------+| M      || F      |+--------+2 rows in set (0.000 sec)MariaDB [hellodb]> select  gender from students;+--------+| gender |+--------+| M      || M      || M      || M      || M      || M      || F      || F      || F      || F      || M      || F      || M      || F      || M      || M      || M      || M      || F      || F      || F      || F      || M      || M      || M      |+--------+25 rows in set (0.000 sec)#将age和gender多个字段重复的记录去重MariaDB [hellodb]> select distinct age,gender from students;+-----+--------+| age | gender |+-----+--------+|  22 | M      ||  53 | M      ||  32 | M      ||  26 | M      ||  46 | M      ||  19 | F      ||  17 | F      ||  20 | F      ||  23 | M      ||  33 | M      ||  19 | M      ||  21 | M      ||  25 | M      ||  18 | F      ||  22 | F      ||  27 | M      || 100 | M      |+-----+--------+17 rows in set (0.000 sec)MariaDB [hellodb]> select age,gender from students order by age;+-----+--------+| age | gender |+-----+--------+|  17 | F      ||  17 | F      ||  18 | F      ||  19 | F      ||  19 | F      ||  19 | F      ||  19 | M      ||  19 | F      ||  20 | F      ||  20 | F      ||  21 | M      ||  22 | M      ||  22 | F      ||  22 | M      ||  23 | M      ||  23 | M      ||  23 | M      ||  25 | M      ||  26 | M      ||  27 | M      ||  32 | M      ||  33 | M      ||  46 | M      ||  53 | M      || 100 | M      |+-----+--------+25 rows in set (0.000 sec)
#sql注入攻击(如果感兴趣之后写一篇文章介绍一下)select * from user where name='admin' and password='' or '1'='1';select * from user where name='admin' and password='' or '1=1';select * from user where name='admin'; -- ' and password='asdasd3';select * from user where name='admin'; # ' and password='asdadjkdf3';
#分页查询#只取前3个mysql> select * from students limit 0,3;mysql> select * from students limit 3;# 查询第n页的数据,每页显示m条记录mysql> select * from students limit (n-1) * m,m;
#聚合函数MariaDB [hellodb]> select sum(age) from students where gender ='M';       #计算性别为M的年龄和+----------+| sum(age) |+----------+|      495 |+----------+1 row in set (0.000 sec)MariaDB [hellodb]> select count(*) from students where gender ='M';      #计算性别为M的人数+----------+| count(*) |+----------+|       15 |+----------+1 row in set (0.000 sec)MariaDB [hellodb]> select sum(age)/count(*) from students where gender ='M';  #计算性别为M的平均年龄+-------------------+| sum(age)/count(*) |+-------------------+|           33.0000 |+-------------------+1 row in set (0.001 sec)
#分组统计MariaDB [hellodb]> select classid,count(*) 数量 from students group by classid; #统计每个班有多少人+---------+--------+| classid | 数量   |+---------+--------+|    NULL |      2 ||       1 |      4 ||       2 |      3 ||       3 |      4 ||       4 |      4 ||       5 |      1 ||       6 |      4 ||       7 |      3 |+---------+--------+8 rows in set (0.000 sec)MariaDB [hellodb]> select classid,avg(age) 平均年龄 from students where classid >3 group by classid having 平均年龄 > 30;      #显示classid大于3,平均年龄大于30的classID和平均年龄+---------+--------------+| classid | 平均年龄     |+---------+--------------+|       5 |      46.0000 |+---------+--------------+1 row in set (0.001 sec)MariaDB [hellodb]> select gender,avg(age) 平均年龄 from students group by gender having gender="M";                #显示性别为男的平均年龄+--------+--------------+| gender | 平均年龄     |+--------+--------------+| M      |      33.0000 |+--------+--------------+1 row in set (0.000 sec)#下面两个例子都是显示以classid和gender分组统计,即显示每个班级的男生和女生的人数,哪个在前就以哪个为主进行排序MariaDB [hellodb]> select classid,gender,count(*) 数量 from students group by classid,gender;          #以classid进行排序显示+---------+--------+--------+| classid | gender | 数量   |+---------+--------+--------+|    NULL | M      |      2 ||       1 | F      |      2 ||       1 | M      |      2 ||       2 | M      |      3 ||       3 | F      |      3 ||       3 | M      |      1 ||       4 | M      |      4 ||       5 | M      |      1 ||       6 | F      |      3 ||       6 | M      |      1 ||       7 | F      |      2 ||       7 | M      |      1 |+---------+--------+--------+12 rows in set (0.000 sec)MariaDB [hellodb]> select classid,gender,count(*) 数量 from students group by gender,classid;          #以性别进行排序显示+---------+--------+--------+| classid | gender | 数量   |+---------+--------+--------+|       1 | F      |      2 ||       3 | F      |      3 ||       6 | F      |      3 ||       7 | F      |      2 ||    NULL | M      |      2 ||       1 | M      |      2 ||       2 | M      |      3 ||       3 | M      |      1 ||       4 | M      |      4 ||       5 | M      |      1 ||       6 | M      |      1 ||       7 | M      |      1 |+---------+--------+--------+12 rows in set (0.000 sec)#group_concat函数实现分组信息的集合MariaDB [hellodb]> select gender,group_concat(name) from students group by gender;+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------+| gender | group_concat(name)                                                                                                                                     |+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------+| F      | Lin Daiyu,Yue Lingshan,Ren Yingying,Wen Qingqing,Xi Ren,Diao Chan,Huang Yueying,Xiao Qiao,Lu Wushuang,Xue Baochai                                      || M      | Hua Rong,Shi Zhongyu,Ma Chao,Xu Xian,Lin Chong,Xu Zhu,Duan Yu,Tian Boguang,Yuan Chengzhi,Shi Qing,Yu Yutong,Ding Dian,Xie Yanke,Shi Potian,Sun Dasheng |+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------+2 rows in set (0.000 sec)# with rollup 分组后聚合函数统计后再做汇总MariaDB [hellodb]> select gender,count(*) from students group by gender with rollup;+--------+----------+| gender | count(*) |+--------+----------+| F      |       10 || M      |       15 || NULL   |       25 |+--------+----------+3 rows in set (0.000 sec)
注意:一旦使用分组group by,在select 后面的只能采用分组的列和聚合函数,其它的列不能放在select后面,否则根据系统变量SQL_MODE的值不同而不同的结果
MariaDB [hellodb]>  select classid,count(*) 数量 from students group by classid;+---------+--------+| classid | 数量   |+---------+--------+|    NULL |      2 ||       1 |      4 ||       2 |      3 ||       3 |      4 ||       4 |      4 ||       5 |      1 ||       6 |      4 ||       7 |      3 |+---------+--------+8 rows in set (0.000 sec)MariaDB [hellodb]> select classid,count(*),stuid 数量 from students group by classid;    #stuid重命名为数量显示,但是这个毫无意义,显示的其实是该classid对应的第一个学生的stuid而已。而且在现在较新的版本中执行此命令会直接报错+---------+----------+--------+| classid | count(*) | 数量   |+---------+----------+--------+|    NULL |        2 |     24 ||       1 |        4 |      2 ||       2 |        3 |      1 ||       3 |        4 |      5 ||       4 |        4 |      4 ||       5 |        1 |      6 ||       6 |        4 |      9 ||       7 |        3 |      8 |+---------+----------+--------+8 rows in set (0.000 sec)#以下为MySQL8.0.26 的执行结果mysql> \s;--------------mysql  Ver 8.0.26 for Linux on x86_64 (Source distribution)Connection id:       8Current database:  hellodbCurrent user:        root@localhostSSL:          Not in useCurrent pager:        stdoutUsing outfile:        ''Using delimiter:  ;Server version:        8.0.26 Source distributionmysql> select classid,count(*),stuid 数量 from students group by classid;ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'hellodb.students.StuID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
#排序MariaDB [hellodb]> select * from students where classid is not null order by gender desc, age asc ;         #注意必须先过滤在排序+-------+---------------+-----+--------+---------+-----------+| StuID | Name          | Age | Gender | ClassID | TeacherID |+-------+---------------+-----+--------+---------+-----------+|    15 | Duan Yu       |  19 | M      |       4 |      NULL ||    16 | Xu Zhu        |  21 | M      |       1 |      NULL ||     1 | Shi Zhongyu   |  22 | M      |       2 |         3 ||     2 | Shi Potian    |  22 | M      |       1 |         7 ||    18 | Hua Rong      |  23 | M      |       7 |      NULL ||    23 | Ma Chao       |  23 | M      |       4 |      NULL ||    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL ||    17 | Lin Chong     |  25 | M      |       4 |      NULL ||     5 | Yu Yutong     |  26 | M      |       3 |         1 ||     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 ||     8 | Lin Daiyu     |  17 | F      |       7 |      NULL ||    14 | Lu Wushuang   |  17 | F      |       3 |      NULL ||    19 | Xue Baochai   |  18 | 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 ||     9 | Ren Yingying  |  20 | F      |       6 |      NULL ||    22 | Xiao Qiao     |  20 | F      |       1 |      NULL ||    21 | Huang Yueying |  22 | F      |       6 |      NULL |+-------+---------------+-----+--------+---------+-----------+23 rows in set (0.000 sec)MariaDB [hellodb]> select * from students  order by gender desc, age asc where classid is not null ;     #先排序在过滤直接报错ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'where classid is not null' at line 1#多列排序MariaDB [hellodb]> select * from students order by gender desc, age asc;   #gender反向排序,age正向排序+-------+---------------+-----+--------+---------+-----------+| StuID | Name          | Age | Gender | ClassID | TeacherID |+-------+---------------+-----+--------+---------+-----------+|    15 | Duan Yu       |  19 | M      |       4 |      NULL ||    16 | Xu Zhu        |  21 | M      |       1 |      NULL ||     1 | Shi Zhongyu   |  22 | M      |       2 |         3 ||     2 | Shi Potian    |  22 | M      |       1 |         7 ||    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL ||    18 | Hua Rong      |  23 | M      |       7 |      NULL ||    23 | Ma Chao       |  23 | M      |       4 |      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 ||    14 | Lu Wushuang   |  17 | F      |       3 |      NULL ||     8 | Lin Daiyu     |  17 | F      |       7 |      NULL ||    19 | Xue Baochai   |  18 | F      |       6 |      NULL ||     7 | Xi Ren        |  19 | F      |       3 |      NULL ||    20 | Diao Chan     |  19 | F      |       7 |      NULL ||    10 | Yue Lingshan  |  19 | F      |       3 |      NULL ||    12 | Wen Qingqing  |  19 | F      |       1 |      NULL ||    22 | Xiao Qiao     |  20 | F      |       1 |      NULL ||     9 | Ren Yingying  |  20 | F      |       6 |      NULL ||    21 | Huang Yueying |  22 | F      |       6 |      NULL |+-------+---------------+-----+--------+---------+-----------+25 rows in set (0.000 sec)

2.6.3. 多表查询

多表查询,即查询结果来自于多张表

img

子查询:在SQL语句嵌套着查询语句,性能较差,基于某语句的查询结果再次进行的查询

联合查询:UNION

交叉连接:笛卡尔乘积 CROSS JOIN

内连接:

​ 等值连接:让表之间的字段以”等值”建立连接关系

​ 不等值连接

​ 自然连接:去掉重复列的等值连接 , 语法: FROM table1 NATURAL JOIN table2;

外连接:

左外连接:FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col

右外连接:FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col

​ 完全外连接: FROM tb1 FULL OUTER JOIN tb2 ON tb1.col=tb2.col 注意:MySQL 不支持此SQL语法

自连接:本表和本表进行连接查询

2.6.3.1★★子查询★★

子查询 subquery 即SQL语句调用另一个SELECT子句,可以是对同一张表,也可以是对不同表,主要有以下四种常见的用法.

  1. 用于比较表达式中的子查询;子查询仅能返回单个值
    SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age) FROM teachers);update students set Age=(SELECT avg(Age) FROM teachers) where stuid=25;
    
  2. 用于IN中的子查询:**子查询应该单独查询并返回一个或多个值重新构成列表****
    SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);
    
  3. 用于EXISTS 和 Not EXISTS
    参考链接:https://dev.mysql.com/doc/refman/8.0/en/exists-and-not-exists-subqueries.html
    EXISTS(包括 NOT EXISTS )子句的返回值是一个BOOL值。 EXISTS 内部有一个子查询语句(SELECT … FROM…), 将其称为EXIST的内查询语句。其内查询语句返回一个结果集。 EXISTS子句根据其内查询语句的结果集空或者非空,返回一个布尔值。将外查询表的每一行,代入内查询作为检验,如果内查询返回的结果为非空值,则EXISTS子句返回TRUE,外查询的这一行数据便可作为外查询的结果行返回,否则不能作为结果
    MariaDB [hellodb]> select *from students s where (select *from teachers t where s.teacherid=t.tid);ERROR 1241 (21000): Operand should contain 1 column(s)MariaDB [hellodb]> select *from students s where exists (select *from teachers t where s.teacherid=t.tid);+-------+-------------+-----+--------+---------+-----------+| StuID | Name        | Age | Gender | ClassID | TeacherID |+-------+-------------+-----+--------+---------+-----------+|     1 | Shi Zhongyu |  22 | M      |       2 |         3 ||     4 | Ding Dian   |  32 | M      |       4 |         4 ||     5 | Yu Yutong   |  26 | M      |       3 |         1 |+-------+-------------+-----+--------+---------+-----------+3 rows in set (0.001 sec)#说明:1、EXISTS (或 NOT EXISTS) 用在 where之后,且后面紧跟子查询语句(带括号)2、EXISTS (或 NOT EXISTS) 只关心子查询有没有结果,并不关心子查询的结果具体是什么3、上述语句把students的记录逐条代入到Exists后面的子查询中,如果子查询结果集不为空,即说明存在,那么这条students的记录出现在最终结果集,否则被排除
    
  4. 用于FROM子句中的子查询
    使用格式:
    SELECT tb_alias.col1,... FROM (SELECT clause) AS tb_alias WHERE Clause;
    
    MariaDB [hellodb]> SELECT s.ClassID,s.aage FROM (SELECT ClassID,avg(Age) AS aage FROM students WHERE ClassID IS NOT NULL GROUP BY ClassID) AS s WHERE s.aage>30;+---------+---------+| ClassID | aage    |+---------+---------+|       2 | 36.0000 ||       5 | 46.0000 |+---------+---------+2 rows in set (0.001 sec)
    
#子查询:select 的执行结果,被其它SQL调用MariaDB [hellodb]> select stuid,name,age from students where age> avg(age);ERROR 1111 (HY000): Invalid use of group functionMariaDB [hellodb]> select stuid,name,age from students where age> (select avg(age) from students);+-------+--------------+-----+| stuid | name         | age |+-------+--------------+-----+|     3 | Xie Yanke    |  53 ||     4 | Ding Dian    |  32 ||     6 | Shi Qing     |  46 ||    13 | Tian Boguang |  33 ||    25 | Sun Dasheng  | 100 |+-------+--------------+-----+#子查询用于更新表MariaDB [hellodb]> update teachers set age=(select avg(age) from students) where tid=4;Query OK, 1 row affected (0.001 sec)Rows matched: 1  Changed: 1  Warnings: 0MariaDB [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 (0.001 sec)MariaDB [hellodb]> select avg(age) from students;+----------+| avg(age) |+----------+|  27.4000 |+----------+1 row in set (0.000 sec)

2.6.3.2★★联合查询★★

联合查询 Union 实现的条件,多个表的字段数量相同,字段名和数据类型可以不同,但一般数据类型是相同的.

SELECT Name,Age FROM students UNION SELECT Name,Age FROM teachers;
MariaDB [hellodb]> select *from teachers union select *from students;       #直接联合两张表因为字段数不同无法显示ERROR 1222 (21000): The used SELECT statements have a different number of columnsMariaDB [hellodb]> select tid as id ,name,age,gender from teachers union select stuid,name,age,gender from students;        #指定相同的字段数量就可以正常显示了+----+---------------+-----+--------+| id | 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 (0.001 sec)MariaDB [hellodb]> select *from teachers union select *from teachers;  #默认去重等价于select distinct *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 (0.000 sec)MariaDB [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 (0.000 sec)

2.6.3.3交叉连接(基本不用)

cross join 即多表的记录之间做笛卡尔乘积组合,并且多个表的列横向合并相加, “雨露均沾”
比如: 第一个表3行4列,第二个表5行6列,cross join后的结果为3*5=15行,4+6=10列
交叉连接生成的记录可能会非常多,建议慎用

#横向合并,交叉连接(横向笛卡尔)
MariaDB [hellodb]> select * from students cross join teachers;
+-------+---------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name          | Age | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
+-------+---------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |   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      |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |   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.6.3.4★内连接★

inner join 内连接取多个表的交集

MariaDB [hellodb]> select *from students inner join teachers on students.teacherid=teachers.tid;        #取学生表的teacherid等于老师表的tid的部分显示
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name        | Age | Gender | ClassID | TeacherID | TID | Name          | Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
|     5 | Yu Yutong   |  26 | M      |       3 |         1 |   1 | Song Jiang    |  45 | M      |
|     1 | Shi Zhongyu |  22 | M      |       2 |         3 |   3 | Miejue Shitai |  77 | F      |
|     4 | Ding Dian   |  32 | M      |       4 |         4 |   4 | Lin Chaoying  |  27 | F      |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
3 rows in set (0.001 sec)

#如果表定义了别名,原表名将无法使用
MariaDB [hellodb]> select stuid, s.name studentname, tid, t.name teachername from students as s inner join teachers t on s.teacherid=t.tid;
+-------+-------------+-----+---------------+
| stuid | studentname | tid | teachername   |
+-------+-------------+-----+---------------+
|     5 | Yu Yutong   |   1 | Song Jiang    |
|     1 | Shi Zhongyu |   3 | Miejue Shitai |
|     4 | Ding Dian   |   4 | Lin Chaoying  |
+-------+-------------+-----+---------------+
3 rows in set (0.000 sec)


#内连接后过滤数据
MariaDB [hellodb]> select * from students s inner join teachers t on s.teacherid=t.tid and s.age > 30;
+-------+-----------+-----+--------+---------+-----------+-----+--------------+-----+--------+
| StuID | Name      | Age | Gender | ClassID | TeacherID | TID | Name         | Age | Gender |
+-------+-----------+-----+--------+---------+-----------+-----+--------------+-----+--------+
|     4 | Ding Dian |  32 | M      |       4 |         4 |   4 | Lin Chaoying |  27 | F      |
+-------+-----------+-----+--------+---------+-----------+-----+--------------+-----+--------+
1 row in set (0.000 sec)

自然连接

  • 当源表和目标表共享相同名称的列时,就可以在它们之间执行自然连接,而无需指定连接列。
  • 在使用纯自然连接时,如没有相同的列时,会产生交叉连接(笛卡尔乘积)
  • 语法:(SQL:1999)SELECT table1.column, table2.column FROM table1 NATURAL JOIN table2;
MariaDB [hellodb]> create table t1 ( id int,name char(20));Query OK, 0 rows affected (0.004 sec)MariaDB [hellodb]> create table t2 ( id int,title char(20));Query OK, 0 rows affected (0.005 sec)MariaDB [hellodb]> insert t1 values(1,'mage'),(2,'wang'),(3,'zhang');Query OK, 3 rows affected (0.001 sec)Records: 3  Duplicates: 0  Warnings: 0MariaDB [hellodb]> insert t2 values(1,'ceo'),(2,'cto');Query OK, 2 rows affected (0.001 sec)Records: 2  Duplicates: 0  Warnings: 0MariaDB [hellodb]> select * from t1;+------+-------+| id   | name  |+------+-------+|    1 | zhang ||    2 | li    ||    3 | wang  |+------+-------+3 rows in set (0.000 sec)MariaDB [hellodb]> select * from t2;+------+-------+| id   | title |+------+-------+|    1 | ceo   ||    2 | cto   |+------+-------+2 rows in set (0.000 sec)MariaDB [hellodb]> select *from t1 NATURAL JOIN t2;+------+-------+-------+| id   | name  | title |+------+-------+-------+|    1 | zhang | ceo   ||    2 | li    | cto   |+------+-------+-------+2 rows in set (0.000 sec)

2.6.3.5★★左和右外连接★★

左连接: 以左表为主根据条件查询右表数据﹐如果根据条件查询右表数据不存在使用null值填充
右连接: 以右表为主根据条件查询左表数据﹐如果根据条件查询左表数据不存在使用null值填充

MariaDB [hellodb]> select s.stuid,s.name,s.age,s.teacherid,t.tid,t.name,t.age from students as s left outer join teachers as t on s.teacherid=t.tid;+-------+---------------+-----+-----------+------+---------------+------+| stuid | name          | age | teacherid | tid  | name          | age  |+-------+---------------+-----+-----------+------+---------------+------+|     1 | Shi Zhongyu   |  22 |         3 |    3 | Miejue Shitai |   77 ||     2 | Shi Potian    |  22 |         7 | NULL | NULL          | NULL ||     3 | Xie Yanke     |  53 |        16 | NULL | NULL          | NULL ||     4 | Ding Dian     |  32 |         4 |    4 | Lin Chaoying  |   27 ||     5 | Yu Yutong     |  26 |         1 |    1 | Song Jiang    |   45 ||     6 | Shi Qing      |  46 |      NULL | NULL | NULL          | NULL ||     7 | Xi Ren        |  19 |      NULL | NULL | NULL          | NULL ||     8 | Lin Daiyu     |  17 |      NULL | NULL | NULL          | NULL ||     9 | Ren Yingying  |  20 |      NULL | NULL | NULL          | NULL ||    10 | Yue Lingshan  |  19 |      NULL | NULL | NULL          | NULL ||    11 | Yuan Chengzhi |  23 |      NULL | NULL | NULL          | NULL ||    12 | Wen Qingqing  |  19 |      NULL | NULL | NULL          | NULL ||    13 | Tian Boguang  |  33 |      NULL | NULL | NULL          | NULL ||    14 | Lu Wushuang   |  17 |      NULL | NULL | NULL          | NULL ||    15 | Duan Yu       |  19 |      NULL | NULL | NULL          | NULL ||    16 | Xu Zhu        |  21 |      NULL | NULL | NULL          | NULL ||    17 | Lin Chong     |  25 |      NULL | NULL | NULL          | NULL ||    18 | Hua Rong      |  23 |      NULL | NULL | NULL          | NULL ||    19 | Xue Baochai   |  18 |      NULL | NULL | NULL          | NULL ||    20 | Diao Chan     |  19 |      NULL | NULL | NULL          | NULL ||    21 | Huang Yueying |  22 |      NULL | NULL | NULL          | NULL ||    22 | Xiao Qiao     |  20 |      NULL | NULL | NULL          | NULL ||    23 | Ma Chao       |  23 |      NULL | NULL | NULL          | NULL ||    24 | Xu Xian       |  27 |      NULL | NULL | NULL          | NULL ||    25 | Sun Dasheng   | 100 |      NULL | NULL | NULL          | NULL |+-------+---------------+-----+-----------+------+---------------+------+25 rows in set (0.000 sec)MariaDB [hellodb]> select s.stuid,s.name,s.age,s.teacherid,t.tid,t.name,t.age from students as s right outer join teachers as t on s.teacherid=t.tid;+-------+-------------+------+-----------+-----+---------------+-----+| stuid | name        | age  | teacherid | tid | name          | age |+-------+-------------+------+-----------+-----+---------------+-----+|     1 | Shi Zhongyu |   22 |         3 |   3 | Miejue Shitai |  77 ||     4 | Ding Dian   |   32 |         4 |   4 | Lin Chaoying  |  27 ||     5 | Yu Yutong   |   26 |         1 |   1 | Song Jiang    |  45 ||  NULL | NULL        | NULL |      NULL |   2 | Zhang Sanfeng |  94 |+-------+-------------+------+-----------+-----+---------------+-----+4 rows in set (0.000 sec)

2.6.3.6 完全外连接

MySQL 不支持完全外连接full outer join语法

2.6.3.7自连接

自连接, 即表自身连接自身

MariaDB [hellodb]> select * from emp;+------+----------+----------+| id   | name     | leaderid |+------+----------+----------+|   1  | mage     |    NULL  ||   2  | zhangsir |     1    ||   3  | wang     |     2    ||   4  | zhang    |     3    |+------+----------+----------+4 rows in set (0.00 sec)MariaDB [hellodb]> select  e.name,l.name  from emp as e inner join emp as l one.leaderid=l.id;+----------+----------+| name     | name     |+----------+----------+| zhangsir | mage     || wang     | zhangsir || zhang    | wang     |+----------+----------+3 rows in set (0.00 sec)MariaDB [hellodb]> select  e.name,IFNULL(l.name,'无上级')  from emp as e leftjoin emp as l on e.leaderid=l.id;+----------+----------+| name     | name     | +----------+----------+| zhangsir | mage     || wang     | zhangsir || zhang    | wang     || mage     | NULL     |+----------+----------+4 rows in set (0.00 sec)MariaDB [hellodb]> select e.name emp,IFNULL(l.name,'无上级') leader from emp as eleft join emp as l on e.leaderid=l.id;+----------+----------+| emp      | leader   |+----------+----------+| zhangsir | mage     || wang     | zhangsir || zhang    | wang     || mage     | NULL     |+----------+----------+4 rows in set (0.000 sec)

2.6.4select语句处理的顺序


查询执行路径中的组件:查询缓存、解析器、预处理器、优化器、查询执行引擎、存储引擎

SELECT语句的执行流程:

FROM Clause --> WHERE Clause --> GROUP BY --> HAVING Clause -->SELECT --> ORDER BY --> LIMIT

练习

导入hellodb.sql生成数据库

  1. 在students表中,查询年龄大于25岁,且为男性的同学的名字和年龄
    select name,age from students where age >25;
    
  2. 以ClassID为分组依据,显示每组的平均年龄
    select classid,avg(age) from students group by classid
    
  3. 显示第2题中平均年龄大于30的分组及平均年龄
    select classid,avg(age) from students group by classid having avg(age) >30;
    
  4. 显示以L开头的名字的同学的信息
    select *from students where name like "l%";
    
  5. 显示TeacherID非空的同学的相关信息
    select *from students where teacherid is not null;
    
  6. 以年龄排序后,显示年龄最大的前10位同学的信息
    select *from students order by age desc limit 10;
    
  7. 查询年龄大于等于20岁,小于等于25岁的同学的信息
    select *from students where 25>=age and age>=20;
    
  8. 以ClassID分组,显示每班的同学的人数
    select classid,count(*) 人数 from students group by classid;
    
  9. 以Gender分组,显示其年龄之和
    select gender,sum(age) from students group by gender;
    
  10. 以ClassID分组,显示其平均年龄大于25的班级
    select classid ,avg(age) from students group by classid having avg(age) > 25;
    
  11. 以Gender分组,显示各组中年龄大于25的学员的年龄之和
    select gender,sum(age) from students where age>25 group by gender;
    
  12. 显示前5位同学的姓名、班级、老师信息
    select name,classid,teacherid from students limit 5;
    
  13. 显示年级大于20的同学的名称及班级
    select name,classid from students where age>20;
    
  14. 显示其年龄大于平均年龄的同学的名字
    select name,age from students where age> (select avg(age) from students);
    
  15. 显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学
    select * from students as a inner join (select classid,count(stuid),avg(age) as aage from students where classid >0 group by classid having count(stuid)>=3) as b on a.classid=b.classid where a.age>b.aage;