一、分组查询[group by]
count() //统计计数
sum() //求和
avg() //平均值
min() //最小值
max() //最大值
group_concat() //拼接函数1.查询每个国家人口总数
select countrycode,sum(population) from city group by countrycode; //给国家分组2.查询中国每个省城市的个数
MariaDB [world]> select district,count(name) from city where countrycode='CHN' group by district;
+----------------+-------------+
| district | count(name) |
+----------------+-------------+
| Anhui | 16 |
| Chongqing | 1 |
| Fujian | 12 |
| Gansu | 7 |
| Guangdong | 20 |
| Guangxi | 9 |
| Guizhou | 6 |
| Hainan | 2 |
| Hebei | 12 |
| Heilongjiang | 21 |
| Henan | 18 |
| Hubei | 22 |
| Hunan | 18 |
| Inner Mongolia | 13 |
| Jiangsu | 25 |
| Jiangxi | 11 |
| Jilin | 20 |
| Liaoning | 21 |
| Ningxia | 2 |
| Peking | 2 |
| Qinghai | 1 |
| Shaanxi | 8 |
| Shandong | 32 |
| Shanghai | 1 |
| Shanxi | 9 |
| Sichuan | 21 |
| Tianjin | 1 |
| Tibet | 1 |
| Xinxiang | 10 |
| Yunnan | 5 |
| Zhejiang | 16 |
+----------------+-------------+
31 rows in set (0.00 sec)
3.查看北京城市（拼接）
MariaDB [world]> select district,group_concat(name) from city where district='peking' group by district;
+----------+--------------------+
| district | group_concat(name) |
+----------+--------------------+
| Peking | Peking,Tong Xian |
+----------+--------------------+
1 row in set (0.00 sec)
再加一个字段，统计下城市总数
MariaDB [world]> select district,group_concat(name),count(name) from city where district='peking' group by district;
+----------+--------------------+-------------+
| district | group_concat(name) | count(name) |
+----------+--------------------+-------------+
| Peking | Peking,Tong Xian | 2 |
+----------+--------------------+-------------+
1 row in set (0.00 sec)
4.查询每个国家的人口总数，只显示大于1个亿的国家
分析
以国家做分组，统计人口总数
过滤>1亿，having
MariaDB [world]> select countrycode,sum(population) from city group by countrycode having sum(population)>100000000;
+-------------+-----------------+
| countrycode | sum(population) |
+-------------+-----------------+
| CHN | 175953614 |
| IND | 123298526 |
+-------------+-----------------+
2 rows in set (0.01 sec)
----------------------------------------------------------------------
分组后只能使用【having】不能使用【where】，【having】要在【group by】的后面
----------------------------------------------------------------------二、排序【order by】
统计超过5000w人口的国家，进行排序
order by 默认是升序
MariaDB [world]> select countrycode,sum(population) from city group by countrycode having sum(population) > 50000000 order by sum(population);
+-------------+-----------------+
| countrycode | sum(population) |
+-------------+-----------------+
| MEX | 59752521 |
| RUS | 69150700 |
| JPN | 77965107 |
| USA | 78625774 |
| BRA | 85876862 |
| IND | 123298526 |
| CHN | 175953614 |
+-------------+-----------------+
7 rows in set (0.00 sec)
降序desc
MariaDB [world]> select countrycode,sum(population) from city group by countrycode having sum(population) > 50000000 order by sum(population) desc;
+-------------+-----------------+
| countrycode | sum(population) |
+-------------+-----------------+
| CHN | 175953614 |
| IND | 123298526 |
| BRA | 85876862 |
| USA | 78625774 |
| JPN | 77965107 |
| RUS | 69150700 |
| MEX | 59752521 |
+-------------+-----------------+
7 rows in set (0.01 sec)
limit 显示前3行
MariaDB [world]> select countrycode,sum(population) from city group by countrycode having sum(population) > 50000000 order by sum(population) desc limit 3;
+-------------+-----------------+
| countrycode | sum(population) |
+-------------+-----------------+
| CHN | 175953614 |
| IND | 123298526 |
| BRA | 85876862 |
+-------------+-----------------+
3 rows in set (0.00 sec)
------------------
拓展
limit 2,5 //从第三行开始（012），显示5行
limit 2 offset 2 //显示2行，从第一个向后偏移2行三、联表查询
1.准备基础环境
1.创建学校数据库
MariaDB [world]> create database school;
Query OK, 1 row affected (0.00 sec)
MariaDB [world]> show create database school;
+----------+--------------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------------+
| school | CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)
2.切换数据库
MariaDB [world]> use school
Database changed2.创建教师表teacher
MariaDB [school]> create table teacher(
-> tno int(10) not null primary key auto_increment comment '教师编号',
-> tname varchar(20) not null comment '教师姓名'
-> );
MariaDB [school]> desc teacher;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| tno | int(10) | NO | PRI | NULL | auto_increment |
| tname | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
插入数据
MariaDB [school]> insert into teacher values(101,'张老师'),
-> (102,'李老师'),
-> (103,'王老师'),
-> (104,'赵老师');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
MariaDB [school]> select * from teacher;
+-----+-----------+
| tno | tname |
+-----+-----------+
| 101 | 张老师 |
| 102 | 李老师 |
| 103 | 王老师 |
| 104 | 赵老师 |
+-----+-----------+
4 rows in set (0.00 sec)
3.创建学生表student
创建表
MariaDB [school]> create table student(
-> sno int(10) not null primary key auto_increment comment '学号',
-> sname varchar(20) not null comment '学生姓名',
-> sage tinyint not null comment '学生年龄',
-> ssex enum('男','女','保密') not null default '保密' comment '学生性别'
-> ) engine=innodb;
Query OK, 0 rows affected (0.00 sec)
MariaDB [school]> desc student;
+-------+----------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------------+------+-----+---------+----------------+
| sno | int(10) | NO | PRI | NULL | auto_increment |
| sname | varchar(20) | NO | | NULL | |
| sage | tinyint(4) | NO | | NULL | |
| ssex | enum('男','女','保密') //指定内容 | NO | | 保密 | |
+-------+----------------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
2.插入学生数据
MariaDB [school]> insert into student values (1,'张三',22,'男'), (2,'李四',33,'女'), (3,'王五',23,'男'), (4,'赵六',32,'女'), (5,'孙七',19,'男'), (6,'钱八',20,'女'), (7,'杨九',30,'男'), (8,'周实',33,'女'), (9,'吴铁蛋',45,'男'), (10,'郑钱',60,'女');
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0
MariaDB [school]> select * from student;
+-----+-----------+------+------+
| sno | sname | sage | ssex |
+-----+-----------+------+------+
| 1 | 张三 | 22 | 男 |
| 2 | 李四 | 33 | 女 |
| 3 | 王五 | 23 | 男 |
| 4 | 赵六 | 32 | 女 |
| 5 | 孙七 | 19 | 男 |
| 6 | 钱八 | 20 | 女 |
| 7 | 杨九 | 30 | 男 |
| 8 | 周实 | 33 | 女 |
| 9 | 吴铁蛋 | 45 | 男 |
| 10 | 郑钱 | 60 | 女 |
+-----+-----------+------+------+
10 rows in set (0.00 sec)
MariaDB [school]>
4.创建课程表course
1.创建表
MariaDB [school]> create table course(
-> cno int not null primary key auto_increment comment '课程编号',
-> cname varchar(20) not null comment '课程名称',
-> tno int not null comment '课程教师'
-> );
Query OK, 0 rows affected (0.00 sec)
MariaDB [school]> desc course
-> ;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| cno | int(11) | NO | PRI | NULL | auto_increment |
| cname | varchar(20) | NO | | NULL | |
| tno | int(11) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
2.插入数据
MariaDB [school]> insert into course values (1001,'linux',101), (1002,'python',102), (1003,'golang',103), (1004,'java',104);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
MariaDB [school]> select * from course;
+------+--------+-----+
| cno | cname | tno |
+------+--------+-----+
| 1001 | linux | 101 |
| 1002 | python | 102 |
| 1003 | golang | 103 |
| 1004 | java | 104 |
+------+--------+-----+
4 rows in set (0.00 sec)
5.创建成绩表sc
1.创建表
MariaDB [school]> create table sc(
-> sno int not null comment '学生编号',
-> cno int not null comment '课程编号',
-> score int not null default 0 comment '成绩'
-> );
Query OK, 0 rows affected (0.00 sec)
MariaDB [school]> desc sc;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| sno | int(11) | NO | | NULL | |
| cno | int(11) | NO | | NULL | |
| score | int(11) | NO | | 0 | |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)
2.插入数据
MariaDB [school]> insert into sc
-> values
-> (1,1001,80),
-> (1,1003,56),
-> (2,1001,90),
-> (2,1004,100),
-> (3,1002,32),
-> (4,1003,99),
-> (5,1004,80),
-> (6,1004,81),
-> (6,1002,99),
-> (7,1001,77),
-> (7,1002,45),
-> (8,1001,70),
-> (8,1002,22),
-> (8,1003,90),
-> (8,1004,5),
-> (9,1003,76),
-> (10,1004,100);
Query OK, 17 rows affected (0.00 sec)
Records: 17 Duplicates: 0 Warnings: 0
MariaDB [school]> select * from sc;
+-----+------+-------+
| sno | cno | score |
+-----+------+-------+
| 1 | 1001 | 80 |
| 1 | 1003 | 56 |
| 2 | 1001 | 90 |
| 2 | 1004 | 100 |
| 3 | 1002 | 32 |
| 4 | 1003 | 99 |
| 5 | 1004 | 80 |
| 6 | 1004 | 81 |
| 6 | 1002 | 99 |
| 7 | 1001 | 77 |
| 7 | 1002 | 45 |
| 8 | 1001 | 70 |
| 8 | 1002 | 22 |
| 8 | 1003 | 90 |
| 8 | 1004 | 5 |
| 9 | 1003 | 76 |
| 10 | 1004 | 100 |
+-----+------+-------+
17 rows in set (0.00 sec)
6.拼接表查询
笛卡尔乘积查询
拼接后，得到的结果是不合理的，只是强硬的乱拼接
语法：【select * from 表1,表2;】
MariaDB [school]> select * from teacher,course;
+-----+-----------+------+--------+-----+
| tno | tname | cno | cname | tno |
+-----+-----------+------+--------+-----+
| 101 | 张老师 | 1001 | linux | 101 |
| 102 | 李老师 | 1001 | linux | 101 |
| 103 | 王老师 | 1001 | linux | 101 |
| 104 | 赵老师 | 1001 | linux | 101 |
| 101 | 张老师 | 1002 | python | 102 |
| 102 | 李老师 | 1002 | python | 102 |
| 103 | 王老师 | 1002 | python | 102 |
| 104 | 赵老师 | 1002 | python | 102 |
| 101 | 张老师 | 1003 | golang | 103 |
| 102 | 李老师 | 1003 | golang | 103 |
| 103 | 王老师 | 1003 | golang | 103 |
| 104 | 赵老师 | 1003 | golang | 103 |
| 101 | 张老师 | 1004 | java | 104 |
| 102 | 李老师 | 1004 | java | 104 |
| 103 | 王老师 | 1004 | java | 104 |
| 104 | 赵老师 | 1004 | java | 104 |
+-----+-----------+------+--------+-----+
16 rows in set (0.00 sec)
内连拼接
语法：
第一种：【select * from 表1,表2 where 表1.字段=表2.字段】
MariaDB [school]> select * from teacher,course where teacher.tno=course.tno;
+-----+-----------+------+--------+-----+
| tno | tname | cno | cname | tno |
+-----+-----------+------+--------+-----+
| 101 | 张老师 | 1001 | linux | 101 |
| 102 | 李老师 | 1002 | python | 102 |
| 103 | 王老师 | 1003 | golang | 103 |
| 104 | 赵老师 | 1004 | java | 104 |
+-----+-----------+------+--------+-----+
4 rows in set (0.00 sec)
第二种：【select * from 表1 join 表2 on 表1.字段=表2.字段】
MariaDB [school]> select * from teacher join course on teacher.tno=course.tno;
+-----+-----------+------+--------+-----+
| tno | tname | cno | cname | tno |
+-----+-----------+------+--------+-----+
| 101 | 张老师 | 1001 | linux | 101 |
| 102 | 李老师 | 1002 | python | 102 |
| 103 | 王老师 | 1003 | golang | 103 |
| 104 | 赵老师 | 1004 | java | 104 |
+-----+-----------+------+--------+-----+
4 rows in set (0.00 sec)
案例：统计周实学习了几门课
MariaDB [school]> select sname,count(cno) from student join sc on student.sno=sc.sno and sname='周实';
+--------+------------+
| sname | count(cno) |
+--------+------------+
| 周实 | 4 |
+--------+------------+
1 row in set (0.00 sec)
MariaDB [school]> select sname,count(cno) from student join sc on student.sno=sc.sno and sname='周实' group by sname;
+--------+------------+
| sname | count(cno) |
+--------+------------+
| 周实 | 4 |
+--------+------------+
1 row in set (0.00 sec)
案例：统计张三学习的课程名称有哪些
MariaDB [school]> select sname,group_concat(cname) from student,course,sc where student.sno=sc.sno and course.cno=sc.cno and sname='周实' group by sname;
+--------+--------------------------+
| sname | group_concat(cname) |
+--------+--------------------------+
| 周实 | linux,python,golang,java |
+--------+--------------------------+
1 row in set (0.00 sec)
MariaDB [school]> select sname,group_concat(cname) from student join sc on student.sno=sc.sno join course on sc.cno=course.cno and sname='张三' group by sname;
+--------+---------------------+
| sname | group_concat(cname) |
+--------+---------------------+
| 张三 | linux,golang |
+--------+---------------------+
1 row in set (0.00 sec)
案例：统计王老师教的学生有哪些
MariaDB [school]> select tname,group_concat(sname) from teacher,course,sc,student where teacher.tno=course.tno and course.cno=sc.cno and sc.sno=student.sno and tname='王老师' group by tname;
+-----------+--------------------------------+
| tname | group_concat(sname) |
+-----------+--------------------------------+
| 王老师 | 张三,赵六,周实,吴铁蛋 |
+-----------+--------------------------------+
1 row in set (0.00 sec)
MariaDB [school]> select tname,group_concat(sname) from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno join student on sc.sno=student.sno where tname='王老师' group by tname;
+-----------+--------------------------------+
| tname | group_concat(sname) |
+-----------+--------------------------------+
| 王老师 | 张三,赵六,周实,吴铁蛋 |
+-----------+--------------------------------+
1 row in set (0.00 sec)
案例：统计每个老师教的课程的平均分数，并按照降序排序
MariaDB [school]> select tname,avg(score) from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno group by tname order by avg(score) desc;
+-----------+------------+
| tname | avg(score) |
+-----------+------------+
| 王老师 | 80.2500 |
| 张老师 | 79.2500 |
| 赵老师 | 73.2000 |
| 李老师 | 49.5000 |
+-----------+------------+
4 rows in set (0.00 sec)左右外连接查询
课程表插入1条数据
MariaDB [school]> insert into course value(1005,'c++',105);
Query OK, 1 row affected (0.00 sec)
MariaDB [school]> select * from course;
+------+--------+-----+
| cno | cname | tno |
+------+--------+-----+
| 1001 | linux | 101 |
| 1002 | python | 102 |
| 1003 | golang | 103 |
| 1004 | java | 104 |
| 1005 | c++ | 105 |
+------+--------+-----+
5 rows in set (0.00 sec)
此时用内连接查询一下：你会发现，只显示有关联的表内容，没关联的不显示；
MariaDB [school]> select * from teacher join course on teacher.tno=course.tno;
+-----+-----------+------+--------+-----+
| tno | tname | cno | cname | tno |
+-----+-----------+------+--------+-----+
| 101 | 张老师 | 1001 | linux | 101 |
| 102 | 李老师 | 1002 | python | 102 |
| 103 | 王老师 | 1003 | golang | 103 |
| 104 | 赵老师 | 1004 | java | 104 |
+-----+-----------+------+--------+-----+
4 rows in set (0.00 sec)
左外连接 以左边的表为基准表，做拼接
MariaDB [school]> select * from teacher left join course on teacher.tno=course.tno;
+-----+-----------+------+--------+------+
| tno | tname | cno | cname | tno |
+-----+-----------+------+--------+------+
| 101 | 张老师 | 1001 | linux | 101 |
| 102 | 李老师 | 1002 | python | 102 |
| 103 | 王老师 | 1003 | golang | 103 |
| 104 | 赵老师 | 1004 | java | 104 |
+-----+-----------+------+--------+------+
4 rows in set (0.00 sec)
右外连接：以右边的表为基准表，做拼接
MariaDB [school]> select * from teacher right join course on teacher.tno=course.tno;
+------+-----------+------+--------+-----+
| tno | tname | cno | cname | tno |
+------+-----------+------+--------+-----+
| 101 | 张老师 | 1001 | linux | 101 |
| 102 | 李老师 | 1002 | python | 102 |
| 103 | 王老师 | 1003 | golang | 103 |
| 104 | 赵老师 | 1004 | java | 104 |
| NULL | NULL | 1005 | c++ | 105 |
+------+-----------+------+--------+-----+
5 rows in set (0.00 sec)
联合查询union
join是水平拼接两个表
union是垂直拼接两个表
MariaDB [school]> select * from sc where sno=9 union select * from sc where sno=1;
+-----+------+-------+
| sno | cno | score |
+-----+------+-------+
| 9 | 1003 | 76 |
| 1 | 1001 | 80 |
| 1 | 1003 | 56 |
+-----+------+-------+
3 rows in set (0.00 sec)
子查询
就是将一个语句的查询结果，当做另一个语句的查询对象
MariaDB [world]> select population from city where countrycode='CHN' and district='hebei' and population=530000;
+------------+
| population |
+------------+
| 530000 |
+------------+
1 row in set (0.00 sec)
MariaDB [world]> select district,name,population from city where countrycode='CHN' and population>(select population from city where countrycode='CHN' and district='hebei' and population=530000);
+----------------+---------------------+------------+
| district | name | population |
+----------------+---------------------+------------+
| Shanghai | Shanghai | 9696300 |
| Peking | Peking | 7472000 |
| Chongqing | Chongqing | 6351600 |
| Tianjin | Tianjin | 5286800 |
| Hubei | Wuhan | 4344600 |
| Heilongjiang | Harbin | 4289800 |
| Liaoning | Shenyang | 4265200 |
| Guangdong | Kanton [Guangzhou] | 4256300 |
| Sichuan | Chengdu | 3361500 |
| Jiangsu | Nanking [Nanjing] | 2870300 |
| Jilin | Changchun | 2812000 |
| Shaanxi | Xi´an | 2761400 |
| Liaoning | Dalian | 2697000 |
.......四、数据库服务备份
1.查看数据库数据目录
[root@c7-100 ~]# ll /var/lib/mysql
总用量 28700
-rw-rw---- 1 mysql mysql 16384 8月 12 20:27 aria_log.00000001
-rw-rw---- 1 mysql mysql 52 8月 12 20:27 aria_log_control
-rw-rw---- 1 mysql mysql 18874368 8月 13 15:25 ibdata1
-rw-rw---- 1 mysql mysql 5242880 8月 13 15:25 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 8月 9 10:16 ib_logfile1
drwx------ 2 mysql mysql 4096 8月 9 10:16 mysql
srwxrwxrwx 1 mysql mysql 0 8月 13 08:31 mysql.sock
drwx------ 2 mysql mysql 54 8月 12 15:09 oldboy
drwx------ 2 mysql mysql 90 8月 12 17:22 oldboy01_test
drwx------ 2 mysql mysql 4096 8月 9 10:16 performance_schema
drwx------ 2 mysql mysql 90 8月 13 11:13 school
drwx------ 2 mysql mysql 82 8月 12 15:48 world
2.备份数据库
物理备份
需要先关闭数据库进行操作
[root@c7-100 ~]# tar zcvf sql-`date +%F`.tar.gz /var/lib/mysql逻辑备份
语法：【mysqldump -u用户 -p密码 [备份参数] > /路径/文件名.sql】
参数：
-A 备份所有数据库的数据信息
-B 备份指定数据库的数据信息
-F 备份启动之前，自动刷新日志文件（落盘）
【-A】全备
[root@c7-100 ~]# mysqldump -uroot -p1 -A > ./qb.sql
[root@c7-100 ~]# ll -d qb.sql
-rw-r--r-- 1 root root 765733 8月 13 16:21 qb.sql
【-B】指定库备份
[root@c7-100 ~]# mysqldump -uroot -p1 -B school world > ./kb.sql
[root@c7-100 ~]# ll -d kb.sql
-rw-r--r-- 1 root root 247769 8月 13 16:23 kb.sql
指定数据库中的部分数据表进行备份
[root@c7-100 ~]# mysqldump -uroot -p1 school sc teacher > ./bb.sql
[root@c7-100 ~]# ll -d bb.sql
-rw-r--r-- 1 root root 2864 8月 13 16:24 bb.sql3.删除库恢复数据
删表
MariaDB [school]> show tables;
+------------------+
| Tables_in_school |
+------------------+
| course |
| sc |
| student |
| teacher |
+------------------+
4 rows in set (0.00 sec)
MariaDB [school]> drop table sc;
Query OK, 0 rows affected (0.00 sec)
MariaDB [school]> drop table teacher;
Query OK, 0 rows affected (0.00 sec)
MariaDB [school]> show tables;
+------------------+
| Tables_in_school |
+------------------+
| course |
| student |
+------------------+
2 rows in set (0.00 sec)
使用备份恢复数据
MariaDB [school]> source ~/bb.sql
MariaDB [school]> show tables;
+------------------+
| Tables_in_school |
+------------------+
| course |
| sc |
| student |
| teacher |
+------------------+
4 rows in set (0.00 sec)
登录前导入
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oldboy |
| oldboy01_test |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> quit
Bye
[root@c7-100 ~]# mysql -uroot -p1 < ~/qb.sql
[root@c7-100 ~]# mysql -uroot -p1 -e "show databases"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oldboy |
| oldboy01_test |
| performance_schema |
| school |
| world |
+--------------------+
拓展
统计不及格学生的老师有几个
MariaDB [school]> set @row_number = 0; //初始化变量
Query OK, 0 rows affected (0.00 sec)
MariaDB [school]> select tname AS '教师名',count(sname) AS '不及格的学生数',group_concat(sname,score) AS '不及格学生名称及成绩',CASE WHEN(@row_number := @row_number + 1)=1 THEN (select count(DISTINCT tname) from teacher,student,course,sc where teacher.tno=course.tno and course.cno = sc.cno and sc.sno=student.sno and sc.score<60) else null END AS '有不及格学生教师的人数' from teacher,student,course,sc where teacher.tno=course.tno and course.cno=sc.cno and sc.sno=student.sno and sc.score<60 group by tname;
+-----------+----------------------+----------------------------+-----------------------------------+
| 教师名 | 不及格的学生数 | 不及格学生名称及成绩 | 有不及格学生教师的人数 |
+-----------+----------------------+----------------------------+-----------------------------------+
| 李老师 | 3 | 王五32,杨九45,周实22 | 3 |
| 王老师 | 1 | 张三56 | NULL |
| 赵老师 | 1 | 周实5 | NULL |
+-----------+----------------------+----------------------------+-----------------------------------+
3 rows in set (0.00 sec)
CASE：条件语句，根据是否为第一行来决定是否显示教师人数。
语法【CASE WHEN条件 TEHN sql语句（结果1） ELSE sql语句（结果2）END 】
AS :别名
DISTINCT ：去重，就是去掉重复的值
例：
假设在没有 DISTINCT 的情况下，查询结果中可能出现以下情况：
李老师
李老师
王老师
赵老师
如果直接使用 COUNT(tname)，则结果为 4，因为 tname 出现了 4 次。而使用 COUNT(DISTINCT tname) 后，结果为 3，因为 李老师虽然出现了两次，但只算作一个唯一值。
set @row_number = 0; 声明变量