1.关系型数据库

1.1.关系型数据结构模型

数据库按照数据结构来组织、存储和管理数据，实际上，数据库一共有三种模型：

层次模型
网状模型
关系模型

层次模型就是以“上下级”的层次关系来组织数据的一种方式，层次模型的数据结构看起来就像一颗树：

网状模型把每个数据节点和其他很多节点都连接起来，它的数据结构看起来就像很多城市之间的路网：

关系模型把数据看作是一个二维表格，任何数据都可以通过行号+列号来唯一确定，它的数据模型看起来就是一个Excel表：

随着时间的推移和市场竞争，最终，基于关系模型的关系数据库获得了绝对市场份额。

为什么关系数据库获得了最广泛的应用？

因为相比层次模型和网状模型，关系模型理解和使用起来最简单。

一个班级的学生就可以用一个表格存起来，并且定义如下：

ID	姓名	班级ID	性别	年龄
1	小明	201	M	9
2	小红	202	F	8
3	小军	202	M	8
4	小白	201	F	9

其中，班级ID对应着另一个班级表：

ID	名称	班主任
201	二年级一班	王老师
202	二年级二班	李老师

通过给定一个班级名称，可以查到一条班级记录，根据班级ID，又可以查到多条学生记录，这样，二维表之间就通过ID映射建立了“一对多”关系。

1.2.数据类型

对于一个关系表，除了定义每一列的名称外，还需要定义每一列的数据类型。关系数据库支持的标准数据类型包括数值、字符串、时间等：

名称	类型	说明
INT	整型	4字节整数类型，范围约+/-21亿
BIGINT	长整型	8字节整数类型，范围约+/-922亿亿
REAL	浮点型	4字节浮点数，范围约+/-1038
DOUBLE	浮点型	8字节浮点数，范围约+/-10308
DECIMAL(M,N)	高精度小数	由用户指定精度的小数，例如，DECIMAL(20,10)表示一共20位，其中小数10位，通常用于财务计算
CHAR(N)	定长字符串	存储指定长度的字符串，例如，CHAR(100)总是存储100个字符的字符串
VARCHAR(N)	变长字符串	存储可变长度的字符串，例如，VARCHAR(100)可以存储0~100个字符的字符串
BOOLEAN	布尔类型	存储True或者False
DATE	日期类型	存储日期，例如，2018-06-22
TIME	时间类型	存储时间，例如，12:20:59
DATETIME	日期和时间类型	存储日期+时间，例如，2018-06-22 12:20:59

上面的表中列举了最常用的数据类型。很多数据类型还有别名，例如，REAL又可以写成FLOAT(24)。还有一些不常用的数据类型，例如，TINYINT（范围在0~255）。各数据库厂商还会支持特定的数据类型，例如JSON。

选择数据类型的时候，要根据业务规则选择合适的类型。通常来说，BIGINT能满足整数存储的需求，VARCHAR(N)能满足字符串存储的需求，这两种类型是使用最广泛的。

1.3.SQL语句

什么是SQL？SQL是结构化查询语言的缩写，用来访问和操作数据库系统。SQL语句既可以查询数据库中的数据，也可以添加、更新和删除数据库中的数据，还可以对数据库进行管理和维护操作。不同的数据库，都支持SQL，这样，我们通过学习SQL这一种语言，就可以操作各种不同的数据库。

虽然SQL已经被ANSI组织定义为标准，不幸地是，各个不同的数据库对标准的SQL支持不太一致。并且，大部分数据库都在标准的SQL上做了扩展。也就是说，如果只使用标准SQL，理论上所有数据库都可以支持，但如果使用某个特定数据库的扩展SQL，换一个数据库就不能执行了。例如，Oracle把自己扩展的SQL称为PL/SQL，Microsoft把自己扩展的SQL称为T-SQL。

现实情况是，如果我们只使用标准SQL的核心功能，那么所有数据库通常都可以执行。不常用的SQL功能，不同的数据库支持的程度都不一样。而各个数据库支持的各自扩展的功能，通常我们把它们称之为“方言”。

总的来说，SQL语言定义了这么几种操作数据库的能力：

DDL：Data Definition Language

DDL允许用户定义数据，也就是创建表、删除表、修改表结构这些操作。通常，DDL由数据库管理员执行。

DML：Data Manipulation Language

DML为用户提供添加、删除、更新数据的能力，这些是应用程序对数据库的日常操作。

DQL：Data Query Language

DQL允许用户查询数据，这也是通常最频繁的数据库日常操作。

DCL：Data Control Language

DCL用来管理数据库用户、控制数据库的访问权限。

1.4.主流关系行数据库

目前，主流的关系数据库主要分为以下几类：

商用数据库，例如：Oracle，SQL Server，DB2等；
开源数据库，例如：MySQL，PostgreSQL等；
桌面数据库，以微软Access为代表，适合桌面应用程序使用；
嵌入式数据库，以Sqlite为代表，适合手机应用和桌面程序。

本文使用的MySQL关系型数据库进行讲解，

MySQL数据库连接指令：mysql -h 127.0.0.1 -u root -p

2.DDL定义（定义表结构）

DDL：Data Definition Language（数据定义语言）

允许用户定义数据，也就是创建表、删除表、修改表结构这些操作。通常，DDL由数据库管理员执行。

一个操作语句结尾以分号结束

2.1.数据库操作

查看所有已经创建的数据库

show databases;

查看当前正在操作的数据库

SELECT DATABASE();

使用某个数据库

use 数据库名字;

创建数据库

[...] 表明该操作是可以省略不写，[IF NOT EXISTS] 表示如果不存在则创建，[DEFAULT CHARSET字符集] 用于设置字符集，[COLLATE 排序规则] 用于设置排序规则，查看支持的字符集和排序规则可以通过 show charset;命令查看，

CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET字符集] [COLLATE 排序规则];

删除数据库。

[...] 表明该操作是可以省略不写，[IF EXISTS] 表示如果存在则删除

DROP DATABASE [IF EXISTS] 数据库名;

2.2.表操作

2.2.3.查看表信息

查询当前数据库所有表

SHOW TABLES;

查询表结构

DESC 表名;

查询指定表的建表语句

SHOW CREATE TABLE 表名;

2.3.4.创建表

[...] 表明该操作是可以省略不写

CREATE TABLE 表名 (
    字段1	字段1类型[COMMENT '字段1注释'] [字段1约束列表],
    字段2	字段2类型[COMMENT '字段2注释'] [字段2约束列表],
    字段3 字段3类型[COMMENT '字段3注释'] [字段3约束列表],
    字段n 字段n类型[COMMENT '字段n注释'] [字段n约束列表]
)[COMMENT '表注释'];

NOT NULL PRIMARY KEY 表示该字段不为空，为主键约束

CREATE TABLE student(
  `s_id` int(11) COMMENT '学生主键id' NOT NULL PRIMARY KEY,
  `s_name` varchar(8),
  `s_birth` date,
  `s_sex` varchar(4),
) COMMENT '学生表';

表字段类型

名称	类型	说明
INT	整型	4字节整数类型，范围约+/-21亿
BIGINT	长整型	8字节整数类型，范围约+/-922亿亿
REAL	浮点型	4字节浮点数，范围约+/-1038
DOUBLE	浮点型	8字节浮点数，范围约+/-10308
DECIMAL(M,N)	高精度小数	由用户指定精度的小数，例如，DECIMAL(20,10)表示一共20位，其中小数10位，通常用于财务计算
CHAR(N)	定长字符串	存储指定长度的字符串，例如，CHAR(100)总是存储100个字符的字符串
VARCHAR(N)	变长字符串	存储可变长度的字符串，例如，VARCHAR(100)可以存储0~100个字符的字符串
BOOLEAN	布尔类型	存储True或者False
DATE	日期类型	存储日期，例如，2018-06-22
TIME	时间类型	存储时间，例如，12:20:59
DATETIME	日期和时间类型	存储日期+时间，例如，2018-06-22 12:20:59
TEXT	长文本类型	存储0到65535个字节

约束列表

约束	描述	关键字
非空约束	限制该字段的数据不能为null	NOT NULL
唯一约束	保证该字段的所有数据都是唯一、不重复的	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	DEFAULT
检查约束(8.0.16版本之后)	保证字段值满足某一个条件	CHECK
外键约束	用来让两张表的数据之间建立连接，保证数据的一致性和完整性	FOREIGN KEY

2.3.5.修改表

添加新字段

ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT注释] [约束];

修改原字段类的名称和类型

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT注释] [约束];

删除已创建的字段

ALTER TABLE 表名 DROP 字段名;

修改原表名

ALTER TABLE 表名 RENAME TO 新表名;

删除已存在的表

DROP TABLE [IF EXISTS] 表名;

3.DML操作（操作表数据）

DML：Data Manipulation Language（数据操作语句）

DML为用户提供添加、删除、更新数据的能力，这些是应用程序对数据库的日常操作。

3.1.添加数据

给指定字段添加数据

INSERT INTO 表名 (字段名1,字段名2, ..) VALUES (字段名1的值1, 字段名2的值2, ..);

给全部字段添加数据

INSERT INTO 表名 VALUES (字段名1的值1, 字段名2的值2, ..);

INSERT INTO `student` VALUES (1, '赵雷', '1990-01-01', '男');
INSERT INTO `student` VALUES (2, '钱电', '1990-12-21', '男');
INSERT INTO `student` VALUES (3, '孙风', '1990-05-20', '男');
INSERT INTO `student` VALUES (4, '李云', '1990-08-06', '男');
INSERT INTO `student` VALUES (5, '周梅', '1991-12-01', '女');
INSERT INTO `student` VALUES (6, '吴兰', '1992-03-01', '女');
INSERT INTO `student` VALUES (7, '郑竹', '1989-07-01', '女');
INSERT INTO `student` VALUES (8, '王菊', '1990-01-20', '女');

批量添加数据

每行数据值后面以逗号分隔，结尾用分号结束，日期类型需要写在引号当中。

INSERT INTO 表名 (字段名1,字段名2, ..) VALUES 
(字段名1的值1, 字段名2的值2, ..),
(字段名1的值1, 字段名2的值2, ..)
........ ;

INSERT INTO student VALUES 
(1, '赵雷', '1990-01-01', '男'),
(2, '钱电', '1990-12-21', '男'),
(3, '孙风', '1990-05-20', '男'),
(4, '李云', '1990-08-06', '男');

3.2.修改更新数据

[...] 表示可以省略不写，当不写条件语句时会更改表中所有数据

UPDATE 表名 SET 字段名1=值1, 字段名2=值 ... [WHERE 条件];

UPDATE student SET s_name='赵康' WHERE s_id=1

3.2.删除数据

如果不写条件语句将删除该表中所有数据，删除只能删除一整条行数据，不能删除某个字段的值。

DELETE FROM 表名 [WHERE 条件]

4.DQL（查询表数据）

DQL：Data Query Language（数据查询语句）

DQL允许用户查询数据，这也是通常最频繁的数据库日常操作。

4.1.基础查询

查询全部字段的全部记录，*通配符表示所有，也就是查询所有字段

SELECT * FROM 表名;

SELECT * FROM student

查询指定字段并设置别名的全部记录

SELECT 字段1 [AS 别名1], 字段2 [AS 别名2]  ....  FROM 表名;

SELECT s_id AS '主键', s_name AS '名字' FROM student WHERE s_id=1

去除重复记录的查询，关键字 DISTINCT

SELECT DISTINCT 字段1 [AS 别名1], 字段2 [AS 别名2]  ....  FROM 表名;

SELECT DISTINCT s_id AS '主键', s_name AS '名字' FROM student

4.2.条件查询

SELECT 字段列表 FROM 表名 WHERE 条件列表;

select * from student where year(s_birth) = '1990' AND s_sex='男' AND s_id>1

条件列表如下

比较运算符	功能
=	等于
>	大于
>=	大于等于
<	小于
<=	小于等于
<>或者!=	不等于u
BETWEEN ... AND ...	在某个范围之内(含最小、最大值)
IN(..)	在in之后的列表中的值，多选一
LIKE 占位符	模糊匹配( _匹配单个字符，%匹配任意个字符 )
IS NULL	等于NULL
IS NOT NULL	不等于NULL

逻辑运算符	功能
AND或&&	并且(多个条件同时成立)
OR或II	或者(多个条件任意一个成立)
NOT或!	非，不是

4.3.聚合函数

聚合函数用于对整个表的某一列或者分组中的某一列数据进行聚合统计，作用整列（字段），注意聚合函数不统计null值。

SELECT 聚合函数(字段列表) FROM 表名;

SELECT COUNT(s_id) FROM student

聚合函数

函数名	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

4.4.分组查询

SELECT 字段列表 FROM 表名 [WHERE条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];

where与having区别
执行时机不同: where是分组之前进行过滤，不满足where条件,不参与分组;而having是分组之后对结果进行过滤。
判断条件不同: where不能对聚合函数进行判断，而having可以。

统计男女平均年龄并取平均年龄大于等于35岁的记录

SELECT s_sex, AVG(YEAR(CURDATE())-YEAR(s_birth)) AS avg_year FROM student GROUP BY s_sex HAVING avg_year>=35

3.5.排序查询

关键字：ORDER BY

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2, ....;

SELECT s_id AS '主键', s_name AS '名字' FROM student ORDER BY s_id DESC

排序方式，当多个字段进行排序时，当第一个字段相同时才会按照第二个字段排序

排序方式	功能
ASC	升序（默认）
DESC	降序

3.6.分页查询

关键字：LIMIT

起始索引从0开始，起始索引值( (需要的查询页码数-1) *每页显示记录数)
分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录条数;

SELECT s_id AS '主键', s_name AS '名字' FROM student ORDER BY s_id DESC LIMIT 0,5

3.7.执行顺序（重要）

3.8.多表查询

3.8.1.多表概述

项目开发中，在进行数据库表结构设计时,会根据业务需求及业务模块之间的关系,分析并设计表结构,由于业务之间相互关联,所
以各个表结构之间也存在着各种联系,基本上分为三种:

一对一
一对多(多对一)
多对多

一对一

用户与用户详情之间的关系，在任意一方加入外键,关联另外一方的主键，并设置外键为唯一约束（UNIQUE）

一对多(多对一)

部门与员工之间的关系，在多的一方建立外键指向一的一方的主键

多对多

学生与课程之间的关系，建立第三张中间表，中间表中包含至少两个外键，分别指向另外两张表的主键。

3.8.2.多表查询概述

笛卡尔积

笛卡尔积:笛卡尔乘积是指在数学中，两个集合A集合和B集合的所有组合情况。(在多表查询时，需要消除无效的笛
卡尔积)

多表查询分类

1）连接查询

内连接：A和B集合相交的部分
外连接：

左外连接：查询左表A所有数据，以及两张表交集的部分（连接条件）

右外连接：查询右表B所有数据，以及两张表交集的部分（连接条件）
自连接：当前表A与自身表B的连接查询，自连接必须使用表别名，两张表交集的部分（连接条件）

自连接-内连接：查询左表A所有数据，以及两张表交集的部分（连接条件）

自连接-外连接：查询右表B所有数据，以及两张表交集的部分（连接条件）

2）子查询：SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

5.3.3.连接查询

其实连接查询可以使用条件语句中的in来实现多张表的连接查询，这样可以提高查询效率。

1）内连接查询

查询A和B集合相交的部分，交集的部分

隐式内连接

SELECT 字段列表 
FROM 表1, 表2, ....表n 
WHERE 连接条件列表

SELECT st.s_id AS id,st.s_name AS name,ce.c_name,se.s_score 
FROM student st,score se, course ce
WHERE st.s_id=se.s_id AND se.c_id=ce.c_id;

显示内连接

SELECT 字段列表
FROM 表1 
[INNER] JOIN 表2 
ON 连接条件
[INNER] JOIN 表3 
ON 连接条件
...
[INNER] JOIN 表n
on 连接条件

SELECT st.s_id AS id,st.s_name AS name,ce.c_name,se.s_score 
FROM student st
INNER JOIN score se   
ON st.s_id=se.s_id 
INNER JOIN course ce 
ON se.c_id=ce.c_id;

这两个查询结果相同

+----+--------+--------+---------+
| id | name   | c_name | s_score |
+----+--------+--------+---------+
|  1 | 赵康   | 语文   |      80 |
|  1 | 赵康   | 数学   |      90 |
|  1 | 赵康   | 英语   |      99 |
|  2 | 钱电   | 语文   |      70 |
|  2 | 钱电   | 数学   |      60 |
|  2 | 钱电   | 英语   |      65 |
|  3 | 孙风   | 语文   |      80 |
|  3 | 孙风   | 数学   |      80 |
|  3 | 孙风   | 英语   |      80 |
|  4 | 李云   | 语文   |      50 |
|  4 | 李云   | 数学   |      30 |
|  4 | 李云   | 英语   |      40 |
|  5 | 周梅   | 语文   |      76 |
|  5 | 周梅   | 数学   |      87 |
|  6 | 吴兰   | 语文   |      31 |
|  6 | 吴兰   | 英语   |      34 |
|  7 | 郑竹   | 数学   |      89 |
|  7 | 郑竹   | 英语   |      98 |
+----+--------+--------+---------+

2)外连接

左外连接

查询左表A所有数据，以及两张表交集的部分

SELECT 字段列表
FROM 表1 
LEFT [OUTER] JOIN 表2 
ON 条件
LEFT [OUTER] JOIN 表3 
ON 条件
...
LEFT [OUTER] JOIN 表n
ON 条件

SELECT e.id,e.name e_name,d.name d_name
FROM emp e
LEFT OUTER JOIN dept d
ON e.dept_id=d.id;

从结果中可以看到，id为17的员工他所属的部门为空，但是还是被查出来了，所以说左外连接是左表所有数据都能查出来再取两表交集。

+----+-----------+-----------+
| id | e_name    | d_name    |
+----+-----------+-----------+
|  1 | 金庸      | 总经办    |
|  2 | 张无忌    | 研发部    |
|  3 | 杨逍      | 研发部    |
|  4 | 韦一笑    | 研发部    |
|  5 | 常遇春    | 研发部    |
|  6 | 小昭      | 研发部    |
|  7 | 灭绝      | 财务部    |
|  8 | 周芷若    | 财务部    |
|  9 | 丁敏君    | 财务部    |
| 10 | 赵敏      | 市场部    |
| 11 | 鹿杖客    | 市场部    |
| 12 | 鹤笔翁    | 市场部    |
| 13 | 方东白    | 市场部    |
| 14 | 张三丰    | 销售部    |
| 15 | 俞莲舟    | 销售部    |
| 16 | 宋远桥    | 销售部    |
| 17 | 陈友谅    | NULL      |
+----+-----------+-----------+

右外连接

查询右表B所有数据，以及两张表交集的部分

SELECT 字段列表
FROM 表1
RIGHT OUTER JOIN 表2
ON 连接条件
RIGHT OUTER JOIN 表2
ON 连接条件
....
RIGHT OUTER JOIN 表n
ON 连接条件

SELECT e.id,e.name e_name,d.name d_name
FROM emp e
RIGHT OUTER JOIN dept d
ON e.dept_id=d.id;

对比左外连接，右外连接则是。先查询出右边所有的表数据，再取两表交集。

+------+-----------+-----------+
| id   | e_name    | d_name    |
+------+-----------+-----------+
|    2 | 张无忌    | 研发部    |
|    3 | 杨逍      | 研发部    |
|    4 | 韦一笑    | 研发部    |
|    5 | 常遇春    | 研发部    |
|    6 | 小昭      | 研发部    |
|   10 | 赵敏      | 市场部    |
|   11 | 鹿杖客    | 市场部    |
|   12 | 鹤笔翁    | 市场部    |
|   13 | 方东白    | 市场部    |
|    7 | 灭绝      | 财务部    |
|    8 | 周芷若    | 财务部    |
|    9 | 丁敏君    | 财务部    |
|   14 | 张三丰    | 销售部    |
|   15 | 俞莲舟    | 销售部    |
|   16 | 宋远桥    | 销售部    |
|    1 | 金庸      | 总经办    |
| NULL | NULL      | 人事部    |
+------+-----------+-----------+

3）自连接

当前表A与自身表B的连接查询，自连接必须使用表别名，自连接采用内连接，也可以采用外连接（左外，右外）

自连接-内连接

注意，虽说都是自身表关联自身表进行查询，但是这两个表是不同的表（相互独立的表），可以想成其中一个表的某一个字段是另外一个表中没有的。

SELECT 字段列表 
FROM 表1,表2
WHERE 连接条件

注意，虽说都是自身表关联自身表进行查询，但是这两个表是不同的表（相互独立的表），可以想成其中一个表的某一个字段是另外一个表中没有的，如何理解上面说的呢，举个例子来说，员工是有上级领导的（员工表有领导id的字段），领导是没有上级领导的（领导表没有领导id字段），所以自连接查询时，员工表中可以用上级领导id这个字段，而领导表中就不应该（不能）出现上级领导id这个字段。

SELECT yg.id '员工id',yg.name AS '员工名字',ld.name AS '上级领导名字' 
FROM emp yg,emp ld
WHERE yg.managerid=ld.id;

SELECT yg.id '员工id',yg.name AS '员工名字',ld.name AS '上级领导名字'
FROM emp yg
INNER JOIN emp ld
ON yg.managerid=ld.id;

+----------+--------------+--------------------+
| 员工id   | 员工名字     | 上级领导名字       |
+----------+--------------+--------------------+
|        2 | 张无忌       | 金庸               |
|        3 | 杨逍         | 张无忌             |
|        4 | 韦一笑       | 张无忌             |
|        5 | 常遇春       | 杨逍               |
|        6 | 小昭         | 张无忌             |
|        7 | 灭绝         | 金庸               |
|        8 | 周芷若       | 灭绝               |
|        9 | 丁敏君       | 灭绝               |
|       10 | 赵敏         | 金庸               |
|       11 | 鹿杖客       | 赵敏               |
|       12 | 鹤笔翁       | 赵敏               |
|       13 | 方东白       | 赵敏               |
|       14 | 张三丰       | 金庸               |
|       15 | 俞莲舟       | 张三丰             |
|       16 | 宋远桥       | 张三丰             |
|       17 | 陈友谅       | 金庸               |
+----------+--------------+--------------------+

自连接-左外连接

SELECT yg.id '员工id',yg.name AS '员工名字',ld.name AS '上级领导名字'
FROM emp yg
LEFT OUTER JOIN emp ld
ON yg.managerid=ld.id;

+----------+--------------+--------------------+
| 员工id   | 员工名字     | 上级领导名字       |
+----------+--------------+--------------------+
|        1 | 金庸         | NULL               |
|        2 | 张无忌       | 金庸               |
|        3 | 杨逍         | 张无忌             |
|        4 | 韦一笑       | 张无忌             |
|        5 | 常遇春       | 杨逍               |
|        6 | 小昭         | 张无忌             |
|        7 | 灭绝         | 金庸               |
|        8 | 周芷若       | 灭绝               |
|        9 | 丁敏君       | 灭绝               |
|       10 | 赵敏         | 金庸               |
|       11 | 鹿杖客       | 赵敏               |
|       12 | 鹤笔翁       | 赵敏               |
|       13 | 方东白       | 赵敏               |
|       14 | 张三丰       | 金庸               |
|       15 | 俞莲舟       | 张三丰             |
|       16 | 宋远桥       | 张三丰             |
|       17 | 陈友谅       | 金庸               |
+----------+--------------+--------------------+

自连接-右外连接

SELECT yg.id '员工id',yg.name AS '员工名字',ld.name AS '上级领导名字'
FROM emp yg
RIGHT OUTER JOIN emp ld
ON yg.managerid=ld.id

+----------+--------------+--------------------+
| 员工id   | 员工名字     | 上级领导名字       |
+----------+--------------+--------------------+
|        2 | 张无忌       | 金庸               |
|        3 | 杨逍         | 张无忌             |
|        4 | 韦一笑       | 张无忌             |
|        5 | 常遇春       | 杨逍               |
|        6 | 小昭         | 张无忌             |
|        7 | 灭绝         | 金庸               |
|        8 | 周芷若       | 灭绝               |
|        9 | 丁敏君       | 灭绝               |
|       10 | 赵敏         | 金庸               |
|       11 | 鹿杖客       | 赵敏               |
|       12 | 鹤笔翁       | 赵敏               |
|       13 | 方东白       | 赵敏               |
|       14 | 张三丰       | 金庸               |
|       15 | 俞莲舟       | 张三丰             |
|       16 | 宋远桥       | 张三丰             |
|       17 | 陈友谅       | 金庸               |
|     NULL | NULL         | 韦一笑             |
|     NULL | NULL         | 常遇春             |
|     NULL | NULL         | 小昭               |
|     NULL | NULL         | 周芷若             |
|     NULL | NULL         | 丁敏君             |
|     NULL | NULL         | 鹿杖客             |
|     NULL | NULL         | 鹤笔翁             |
|     NULL | NULL         | 方东白             |
|     NULL | NULL         | 俞莲舟             |
|     NULL | NULL         | 宋远桥             |
|     NULL | NULL         | 陈友谅             |
+----------+--------------+--------------------+

5.3.4.子查询

SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询，子查询的本质就是将一个复杂的查询步步拆解成最小查询，然后组合查询最后得到结果。

SELECT * FROM 表1 WHERE 字段 = ( SELECT 字段 FROM 表2 );

子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT的任何一个。

根据子查询结果的不同，子查询又分为

标量子查询（子查询结果只有一个值，单行单列）
列子查询（子查询结果只有一列，多行单列）
行子查询（子查询结果只有一行，单行多列）
表子查询（子查询结果为多行多列）

子查询位置可以为：where之后、from之后、select之后

1）标量子查询

标量子查询就是子查询结果只有一个值数字、字符串、日期等），单行单列。

标量子查询操作符

操作符	共功能
=	等于
<>或者!=	不等于
>	大于
>=	大于等于
<	小于
<=	小于等于

例子：

查询数学成绩低于90分的同学id

-- 1.查询数学成绩低于90分的同学id
-- 1）查询数学科目id，
SELECT c_id FROM course ce
WHERE ce.c_name='数学'
-- 得到数学科目id为2，单行单列
-- 2）查询数学成绩小于90的学生id
SELECT s_id FROM score se
WHERE se.s_score<90 AND se.c_id=2
-- 得到学成绩小于90的所有学生id 最终结果
-- 3）将前两步合并
SELECT s_id FROM score se
WHERE se.s_score<90 AND se.c_id = (SELECT c_id FROM course ce
WHERE ce.c_name='数学');

+------+
| s_id |
+------+
|    2 |
|    3 |
|    4 |
|    5 |
|    7 |
+------+

2）列子查询

子查询结果只有一列，多行单列

常用操作符

操作符	功能
IN	在这个集合中
NOT IN	不在这个集合中
ANY	配合标量子查询操作符，满足集合中任意一个
SOME	配合标量子查询操作符，满足集合中任意一个
ALL	配合标量子查询操作符，集合所有元素都满足

例子

1.查询数学成绩低于90分的同学id

-- 1.查询数学成绩低于90分的同学信息
-- 1）查询数学科目id，
SELECT c_id FROM course ce
WHERE ce.c_name='数学'
-- 得到数学科目id为2，单行单列
-- 2）查询数学成绩小于90的学生id
SELECT s_id FROM score se
WHERE se.s_score<90 AND se.c_id=2
-- 得到所有数学成绩小于90的学生id (2,3,4,5,7)
-- 3）根据学生id查询学生信息
SELECT * FROM student WHERE s_id IN (2,3,4,5,7)
-- 得到(2,3,4,5,7)id对应的学生信息
-- 4）将前面三个步骤合并
SELECT * FROM student WHERE
s_id IN (SELECT s_id FROM score se
WHERE se.s_score<90 AND se.c_id = (SELECT c_id FROM course ce
WHERE ce.c_name='数学'))
-- 得到最终结果

+------+--------+------------+-------+
| s_id | s_name | s_birth    | s_sex |
+------+--------+------------+-------+
|    2 | 钱电   | 1990-12-21 | 男    |
|    3 | 孙风   | 1990-05-20 | 男    |
|    4 | 李云   | 1990-08-06 | 男    |
|    5 | 周梅   | 1991-12-01 | 女    |
|    7 | 郑竹   | 1989-07-01 | 女    |
+------+--------+------------+-------+

2.查询比财务部所有人工资都高的员工信息

-- 2.查询比财务部所有人工资都高的员工信息
-- 1）查询出财务部对应的id
SELECT id FROM dept WHERE name='财务部'
-- 得到财务部对应id为3
-- 2）查询出财务部所有人的工资
SELECT salary FROM emp WHERE dept_id=3
-- 得到财务部所有人的工资为(8500,4800,5250)
-- 3）查询出比财务部所有人工资都高的员工信息,合并
SELECT * FROM emp 
WHERE salary > ALL (SELECT salary FROM emp 
                    WHERE dept_id=(SELECT id FROM dept 
                                   WHERE name='财务部'));

+----+-----------+------+-----------------+--------+------------+-----------+---------+
| id | name      | age  | job             | salary | entrydate  | managerid | dept_id |
+----+-----------+------+-----------------+--------+------------+-----------+---------+
|  1 | 金庸      |   66 | 总裁            |  20000 | 2000-01-01 |      NULL |       5 |
|  2 | 张无忌    |   20 | 项目经理        |  12500 | 2005-12-05 |         1 |       1 |
|  4 | 韦一笑    |   48 | 开发            |  11000 | 2002-02-05 |         2 |       1 |
|  5 | 常遇春    |   43 | 开发            |  10500 | 2004-09-07 |         3 |       1 |
| 10 | 赵敏      |   20 | 市场部总监      |  12500 | 2004-10-12 |         1 |       2 |
| 14 | 张三丰    |   88 | 销售总监        |  14000 | 2004-10-12 |         1 |       4 |
+----+-----------+------+-----------------+--------+------------+-----------+---------+

3）行子查询

子查询结果只有一行，单行多列，通过(字段1,字段2)=(字段1的值,字段2的值)进行关联

常用操作符

操作符	功能
=	相等
<>或者!=	不相等
IN	在集合中
NOT IN	不在集合中

例子

查询与张无忌的薪资相同及具有相同上级领导的员工信息

-- 1.查询与张无忌的薪资相同及具有相同上级领导的员工信息
-- 1）查询张无忌的薪资及上级领导id
SELECT salary,managerid FROM emp WHERE name='张无忌'
-- 得到张无忌的薪资和上级领导id (12500,1)
-- 2）查询与张无忌的工资和上级领导id相同的员工信息
SELECT * FROM emp WHERE (salary,managerid)=(12500,1)
-- 3）组合
SELECT * FROM emp WHERE (salary,managerid)=(SELECT salary,managerid FROM emp WHERE name='张无忌')

4）表子查询

子查询结果为多行多列，相当于一个表

常用操作符

操作符	功能
IN	在集合中

1.查询与鹿杖客和宋远桥的职位和薪资相同的员工

-- 1.查询与鹿杖客和宋远桥的职位和薪资相同的员工
-- 1）查询出鹿杖客和宋远桥的职位和薪资
SELECT salary,job FROM emp WHERE name='鹿杖客' or name='宋远桥'
-- 得到的是((3750,职员),(4800,销售))
-- 2）查询与鹿杖客和宋远桥的职位和薪资相同的员工
SELECT * FROM emp WHERE (salary,job) IN (SELECT salary,job FROM emp WHERE name='鹿杖客' or name='宋远桥');

+----+-----------+------+--------+--------+------------+-----------+---------+
| id | name      | age  | job    | salary | entrydate  | managerid | dept_id |
+----+-----------+------+--------+--------+------------+-----------+---------+
| 11 | 鹿杖客    |   56 | 职员   |   3750 | 2006-10-03 |        10 |       2 |
| 12 | 鹤笔翁    |   19 | 职员   |   3750 | 2007-05-09 |        10 |       2 |
| 15 | 俞莲舟    |   38 | 销售   |   4600 | 2004-10-12 |        14 |       4 |
| 16 | 宋远桥    |   40 | 销售   |   4600 | 2004-10-12 |        14 |       4 |
+----+-----------+------+--------+--------+------------+-----------+---------+

2.查询入职日期是"2006-01-01” 之后的员工信息，及其部门信息

-- 2.查询入职日期是"2006-01-01” 之后的员工信息，及其部门信息
-- 1）查询大于"2006-01-01” 之后入职的员工信息
-- 表子查询
SELECT * FROM emp WHERE entrydate > '2006-01-01';
-- 2）查询大于"2006-01-01” 之后入职的员工信息及其部门
SELECT * FROM (SELECT * FROM emp WHERE entrydate > '2006-01-01') e 
left OUTER JOIN dept 
ON e.dept_id=dept.id;
-- 左外连接后再筛选，结果是一样的
SELECT * FROM emp
left OUTER JOIN dept 
ON dept_id=dept.id 
WHERE entrydate > '2006-01-01';

+----+-----------+------+--------+--------+------------+-----------+---------+------+-----------+
| id | name      | age  | job    | salary | entrydate  | managerid | dept_id | id   | name      |
+----+-----------+------+--------+--------+------------+-----------+---------+------+-----------+
|  8 | 周芷若    |   19 | 会计   |   4800 | 2006-06-02 |         7 |       3 |    3 | 财务部    |
|  9 | 丁敏君    |   23 | 出纳   |   5250 | 2009-05-13 |         7 |       3 |    3 | 财务部    |
| 11 | 鹿杖客    |   56 | 职员   |   3750 | 2006-10-03 |        10 |       2 |    2 | 市场部    |
| 12 | 鹤笔翁    |   19 | 职员   |   3750 | 2007-05-09 |        10 |       2 |    2 | 市场部    |
| 13 | 方东白    |   19 | 职员   |   5500 | 2009-02-12 |        10 |       2 |    2 | 市场部    |
| 17 | 陈友谅    |   42 | NULL   |   2000 | 2011-10-12 |         1 |    NULL | NULL | NULL      |
+----+-----------+------+--------+--------+------------+-----------+---------+------+-----------+

3.9.多表查询练习题（重要）

数据库脚本(结构和数据)，上面章节所讲例子的数据均来自该脚本

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `c_id` int(11) NOT NULL,
  `c_name` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `t_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`c_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (1, '语文', 2);
INSERT INTO `course` VALUES (2, '数学', 1);
INSERT INTO `course` VALUES (3, '英语', 3);

-- ----------------------------
-- Table structure for dept
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '部门名称',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '部门表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of dept
-- ----------------------------
INSERT INTO `dept` VALUES (1, '研发部');
INSERT INTO `dept` VALUES (2, '市场部');
INSERT INTO `dept` VALUES (3, '财务部');
INSERT INTO `dept` VALUES (4, '销售部');
INSERT INTO `dept` VALUES (5, '总经办');
INSERT INTO `dept` VALUES (6, '人事部');

-- ----------------------------
-- Table structure for emp
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '姓名',
  `age` int(11) NULL DEFAULT NULL COMMENT '年龄',
  `job` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '职位',
  `salary` int(11) NULL DEFAULT NULL COMMENT '薪资',
  `entrydate` date NULL DEFAULT NULL COMMENT '入职时间',
  `managerid` int(11) NULL DEFAULT NULL COMMENT '直属领导ID',
  `dept_id` int(11) NULL DEFAULT NULL COMMENT '部门ID',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `fk_emp_dept_id`(`dept_id`) USING BTREE,
  CONSTRAINT `fk_emp_dept_id` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 19 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '员工表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of emp
-- ----------------------------
INSERT INTO `emp` VALUES (1, '金庸', 66, '总裁', 20000, '2000-01-01', NULL, 5);
INSERT INTO `emp` VALUES (2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1);
INSERT INTO `emp` VALUES (3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1);
INSERT INTO `emp` VALUES (4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1);
INSERT INTO `emp` VALUES (5, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1);
INSERT INTO `emp` VALUES (6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1);
INSERT INTO `emp` VALUES (7, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3);
INSERT INTO `emp` VALUES (8, '周芷若', 19, '会计', 4800, '2006-06-02', 7, 3);
INSERT INTO `emp` VALUES (9, '丁敏君', 23, '出纳', 5250, '2009-05-13', 7, 3);
INSERT INTO `emp` VALUES (10, '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 2);
INSERT INTO `emp` VALUES (11, '鹿杖客', 56, '职员', 3750, '2006-10-03', 10, 2);
INSERT INTO `emp` VALUES (12, '鹤笔翁', 19, '职员', 3750, '2007-05-09', 10, 2);
INSERT INTO `emp` VALUES (13, '方东白', 19, '职员', 5500, '2009-02-12', 10, 2);
INSERT INTO `emp` VALUES (14, '张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4);
INSERT INTO `emp` VALUES (15, '俞莲舟', 38, '销售', 4600, '2004-10-12', 14, 4);
INSERT INTO `emp` VALUES (16, '宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4);
INSERT INTO `emp` VALUES (17, '陈友谅', 42, NULL, 2000, '2011-10-12', 1, NULL);

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`  (
  `s_id` int(11) NULL DEFAULT NULL,
  `c_id` int(11) NULL DEFAULT NULL,
  `s_score` int(11) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES (1, 1, 80);
INSERT INTO `score` VALUES (1, 2, 90);
INSERT INTO `score` VALUES (1, 3, 99);
INSERT INTO `score` VALUES (2, 1, 70);
INSERT INTO `score` VALUES (2, 2, 60);
INSERT INTO `score` VALUES (2, 3, 65);
INSERT INTO `score` VALUES (3, 1, 80);
INSERT INTO `score` VALUES (3, 2, 80);
INSERT INTO `score` VALUES (3, 3, 80);
INSERT INTO `score` VALUES (4, 1, 50);
INSERT INTO `score` VALUES (4, 2, 30);
INSERT INTO `score` VALUES (4, 3, 40);
INSERT INTO `score` VALUES (5, 1, 76);
INSERT INTO `score` VALUES (5, 2, 87);
INSERT INTO `score` VALUES (6, 1, 31);
INSERT INTO `score` VALUES (6, 3, 34);
INSERT INTO `score` VALUES (7, 2, 89);
INSERT INTO `score` VALUES (7, 3, 98);

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `s_id` int(11) NOT NULL,
  `s_name` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `s_birth` date NULL DEFAULT NULL,
  `s_sex` varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  PRIMARY KEY (`s_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '赵康', '1990-01-01', '男');
INSERT INTO `student` VALUES (2, '钱电', '1990-12-21', '男');
INSERT INTO `student` VALUES (3, '孙风', '1990-05-20', '男');
INSERT INTO `student` VALUES (4, '李云', '1990-08-06', '男');
INSERT INTO `student` VALUES (5, '周梅', '1991-12-01', '女');
INSERT INTO `student` VALUES (6, '吴兰', '1992-03-01', '女');
INSERT INTO `student` VALUES (7, '郑竹', '1989-07-01', '女');
INSERT INTO `student` VALUES (8, '王菊', '1990-01-20', '女');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `t_id` int(11) NULL DEFAULT NULL,
  `t_name` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (1, '张三');
INSERT INTO `teacher` VALUES (2, '李四');
INSERT INTO `teacher` VALUES (3, '王五');

SET FOREIGN_KEY_CHECKS = 1;

1.查询员工的姓名、年龄、职位、部门信息 (隐式内连接)

SELECT emp.name,age,job,dp.*
FROM emp,dept dp
WHERE emp.dept_id=dp.id;

2.查询年龄小于30岁的员工的姓名、年龄、职位、部门信息(显式内连接)

SELECT e.name,e.age,e.job,d.*
FROM emp e
INNER JOIN dept d
ON e.dept_id=d.id
WHERE e.age<30;

SELECT e.name,e.age,e.job,d.*
FROM (SELECT * FROM emp WHERE age<30) e
LEFT JOIN dept d
ON e.dept_id=d.id;

3.查询拥有员工的部门ID、部门名称

SELECT d.id,d.name
FROM dept d
WHERE d.id IN (SELECT dept_id FROM emp WHERE dept_id IS NOT NULL);

SELECT DISTINCT d.id,d.name
FROM dept d
INNER JOIN emp
ON d.id=emp.dept_id;

4.查询所有年龄大于40岁的员工，及其归属的部门名称;如果员工没有分配部门，也需要展示出来

SELECT * FROM emp e
LEFT OUTER JOIN dept d
ON e.dept_id=d.id
WHERE e.age>40

5.查询所有员工的工资等级（重点）

SELECT e.*,sa.grade 
FROM salgrade sa,emp e
WHERE e.salary BETWEEN sa.losal AND hisal
ORDER BY sa.grade DESC

6.查询”研发部”所有员工的信息及工资等级

SELECT e.*,sa.grade,dt.name
FROM salgrade sa,emp e,(SELECT * FROM dept WHERE name='研发部') dt
WHERE e.salary BETWEEN sa.losal AND hisal AND e.dept_id=dt.id
ORDER BY sa.grade DESC

7.查询"研发部”员工的平均工资

SELECT AVG(salary) FROM emp WHERE dept_id=(SELECT id FROM dept WHERE name='研发部')

SELECT AVG(salary) FROM emp,dept dt WHERE dt.name='研发部' AND dt.id=emp.dept_id

8.查询工资比"灭绝”高的员工信息。

SELECT * FROM emp WHERE emp.salary>(SELECT salary FROM emp WHERE name='灭绝')

9.查询比平均薪资高的员工信息

SELECT * FROM emp WHERE salary > (SELECT AVG(salary) FROM emp)

10.查询低于本部门平均工资的员工信息（易错，重点）

SELECT * FROM emp e 
LEFT OUTER JOIN (SELECT AVG(salary) a,dept_id FROM emp WHERE dept_id IS NOT NULL GROUP BY dept_id ) s
ON s.dept_id=e.dept_id
WHERE e.salary<s.a;

SELECT * FROM emp e WHERE e.salary<(SELECT AVG(salary) FROM emp e2 WHERE e2.dept_id=e.dept_id);

11.查询所有的部门信息，并统计部门的员工人数（易错，重点）

SELECT id,name,ct FROM dept
LEFT OUTER JOIN (SELECT COUNT(id) ct,dept_id FROM emp WHERE dept_id IS NOT NULL GROUP BY dept_id) d
ON dept.id=d.dept_id

SELECT id,name,(SELECT COUNT(*) FROM emp WHERE emp.dept_id= dt1.id) ct
FROM dept dt1

12.查询所有学生的选课情况，展示出学生名称，学号，课程名称

SELECT st.s_name,st.s_id,ce.c_name
FROM student st
LEFT JOIN score se
ON st.s_id=se.s_id
LEFT JOIN course ce
ON se.c_id=ce.c_id

CSDN 50道经典SQL题

地址：https://blog.csdn.net/GodSuzzZ/article/details/106930311

-- 1.查询"01"课程比"02"课程成绩高的学生的信息及课程分数

SELECT * 
FROM student st
RIGHT JOIN (SELECT * FROM score WHERE c_id=1) c1
ON st.s_id=c1.s_id
RIGHT JOIN  (SELECT * FROM score WHERE c_id=2) c2
ON st.s_id=c2.s_id
WHERE c1.s_score>c2.s_score

-- 3.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

SELECT 
 st.s_id,
 st.s_name,
 avg.a as '平均成绩'
FROM student st
INNER JOIN (SELECT AVG(s_score) a,se.s_id FROM score se GROUP BY se.s_id HAVING AVG(s_score) >60) avg
ON st.s_id=avg.s_id

-- 4.查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)

SELECT
	st.s_id,
	st.s_name,
	avg.a AS '平均成绩' 
FROM
	student st
	LEFT JOIN ( SELECT AVG( s_score ) a, se.s_id FROM score se GROUP BY se.s_id ) avg ON st.s_id = avg.s_id 
WHERE
	avg.a < 60 OR avg.a IS NULL

-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT 
	st.s_id,
	st.s_name,
  (SELECT COUNT(*) FROM score se1 WHERE se1.s_id=st.s_id) AS '选课总数',
  IFNULL((SELECT SUM(se.s_score) FROM score se WHERE se.s_id=st.s_id),0) AS '课程总成绩'
FROM student st

SELECT 
	st.s_id,
	st.s_name,
	IFNULL(ct1.c,0) AS '选课总数',
	IFNULL(ct2.s,0) AS '课程总成绩'
FROM student st LEFT JOIN
(SELECT COUNT(*) c,s_id FROM score se GROUP BY se.s_id) ct1
ON st.s_id=ct1.s_id
LEFT JOIN 
(SELECT SUM(se.s_score) s,s_id FROM score se GROUP BY se.s_id) ct2
ON st.s_id=ct2.s_id

select s.s_id,s.s_name,ifnull(cnt_course, 0) as cnt_course,ifnull(sum_score, 0) as sum_score
from student s
left join (
	select s_id,count(*) as cnt_course,sum(s_score) as sum_score
	from score
	group by s_id
) t1
on s.s_id = t1.s_id;

-- 6、查询"李"姓老师的数量
SELECT COUNT(*) FROM teacher WHERE t_name LIKE '李%'


-- 7、询学过"张三"老师授课的同学的信息
SELECT * FROM student st
INNER JOIN score se
ON st.s_id=se.s_id
INNER JOIN course ce
ON se.c_id=ce.c_id
WHERE ce.t_id=(SELECT t_id FROM teacher WHERE t_name='张三')

-- 8、查询没学过"张三"老师授课的同学的信息



-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT * FROM student st
INNER JOIN (SELECT * FROM score WHERE c_id=1) se1
ON st.s_id=se1.s_id
INNER JOIN (SELECT * FROM score WHERE c_id=2) se2
ON st.s_id=se2.s_id


-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

SELECT * FROM student st
INNER JOIN (SELECT * FROM score WHERE c_id=1) se1
ON st.s_id=se1.s_id
WHERE st.s_id NOT IN (SELECT s_id FROM score WHERE c_id =2)

 
-- 11、查询没有学全所有课程的同学的信息

select *
from student
where s_id not in (
	select s_id
	from score
	group by s_id
	having count(*) = (
		select count(*) from course
	)
);

-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
SELECT DISTINCT(st.s_id),st.s_name,s_birth,s_sex FROM student st
INNER JOIN score se
ON se.s_id=st.s_id
WHERE c_id IN (SELECT c_id FROM score se WHERE s_id=1)

-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息


-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名



-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩




-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息




-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩



-- 18、查询各科成绩最高分、最低分和平均分，以如下形式显示：


-- 19、按各科成绩进行排序，并显示排名



-- 20、查询学生的总成绩并进行排名

-- 21、查询不同老师所教不同课程平均分从高到低显示
22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
24、查询学生平均成绩及其名次
25、查询各科成绩前三名的记录
26、查询每门课程被选修的学生数
27、查询出只有两门课程的全部学生的学号和姓名
28、查询男生、女生人数
29、查询名字中含有"风"字的学生信息
30、查询同名同性学生名单，并统计同名人数
31、查询1990年出生的学生名单
32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
35、查询所有学生的课程及分数情况
36、查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数
37、查询课程不及格的学生
38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
39、求每门课程的学生人数
40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
42、查询每门课程成绩最好的前三名
43、统计每门课程的学生选修人数（超过5人的课程才统计）
44、检索至少选修两门课程的学生学号
45、查询选修了全部课程的学生信息
46、查询各学生的年龄(周岁)
47、查询本周过生日的学生
48、查询下周过生日的学生
49、查询本月过生日的学生
50、查询12月份过生日的学生

5.DCL（访问数据控制）

DCL：Data Control Language（数据控制语句）

DCL用来管理数据库用户、控制数据库的访问权限。

5.1.管理用户

MySQL数据库中的所有用户数据都是存放在mysql这张表中的，其中host（主机名）决定了只能通过该主机名访问的用户才能访问，主机名设置为%（百分号）时，任意主机都可以访问

查询用户

USE mysql;
SELECT * FROM user;

创建用户

CREATE USER '用户名’@'主机名' IDENTIFIED BY '密码' ;

修改用户密码

ALTER USER '用户名‘@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

删除用户

DROP USER '用户名’@'主机名';

5.2.权限管理

查询权限

SHOW GRANTS FOR '用户名'@'主机名';

授予权限

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

权限列表

权限	说明
ALL, ALL PRIVILEGES	所有权限
SELECT	查询数据
INSERT	INSERT
UPDATE	修改数据
DELETE	删除数据
ALTER	修改表
DROP	删除数据库/表/视图
CREATE	创建数据库/表

6.MySQL数据库中的函数

6.1.字符串函数

函数	功能
CONCAT(s1, s2...Sn)	字符串拼接，将S1, S2, ... Sn拼接成-一个字符串
LOWER(str)	将字符串str全部转为小写
UPPER(str)	将字符串str全部转为大写
LPAD(str,n,pad)	左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
RPAD(str,n,pad)	右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
TRIM(str)	去掉字符串头部和尾部的空格
SUBSTRING(str,tart,len)	返回从字符串str从start位置起的len个长度的字符串

SELECT CONCAT('aa','b')

6.2.数值函数

函数	功能
CEIL(x)	向上取整
FLOOR(x)	向下取整
MOD(x,y)	返回x/y的模
RAND()	返回0~1内的随机数
ROUND(x,y)	求参数x的四舍五入的值，保留y位小数

SELECT ROUND(3.5)  --返回4

6.3.日期函数

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
YEAR(date)	获取指定date的年份
MONTH(date)	获取指定date的月份
DAY(date)	获取指定date的日期
DATE_ ADD(date, INTERVAL expr type)	返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1 ,date2)	返回起始时间date1和结束时间date2之间的天数

SELECT CURDATE()

6.4.流程控制函数

函数	功能
IF(value,t,f)	如果value为true,则返回t,否则返回f
IFNULL(value1，value2)	如果value1不为空，返回value1, 否则返回value2
CASE WHEN [val1 ] THEN [res1] ... ELSE [ default] END	如果val1为true,返回res1, ... 否则返回default默认值
CASE [ expr] WHEN [val1 ] THEN [res1] .. ELSE [ default ] ENDD	如果expr的值等于val1,返回res1, ... 否则返回default默认值

SELECT IF(true,'a','b')

Menu

Share

MySQL学习笔记

1.关系型数据库

1.1.关系型数据结构模型

1.2.数据类型

1.3.SQL语句

1.4.主流关系行数据库

2.DDL定义（定义表结构）

2.1.数据库操作

2.2.表操作

2.2.3.查看表信息

2.3.4.创建表

2.3.5.修改表

3.DML操作（操作表数据）

3.1.添加数据

3.2.修改更新数据

3.2.删除数据

4.DQL（查询表数据）

4.1.基础查询

4.2.条件查询

4.3.聚合函数

4.4.分组查询

3.5.排序查询

3.6.分页查询

3.7.执行顺序（重要）

3.8.多表查询

3.8.1.多表概述

3.8.2.多表查询概述

5.3.3.连接查询

5.3.4.子查询

3.9.多表查询练习题（重要）

5.DCL（访问数据控制）

5.1.管理用户

5.2.权限管理

6.MySQL数据库中的函数

6.1.字符串函数

6.2.数值函数

6.3.日期函数

Comment