简单了解数据库--笔记03

清爽

已于 2024-09-01 21:02:04 修改

阅读量835

点赞数 23

文章标签：笔记学习数据库

于 2024-09-01 20:58:56 首次发布

本文链接：https://blog.csdn.net/weixin_44550167/article/details/141789374

版权

一、分组查询[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 changed

2.创建教师表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.sql

3.删除库恢复数据

删表
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; 声明变量