学习目标

能够完成数据的备份和恢复
能够使用SQL语句添加主键、外键、唯一、非空约束
能够说出多表之间的关系及其建表原则
能够理解三大范式
能够使用内连接进行多表查询
能够使用左外连接和右外连接进行多表查询
能够使用子查询进行多表查询
能够理解多表查询的规律
第一章基础SQL-DCL语句(了解)
我们现在默认使用的都是root用户，超级管理员，拥有全部的权限。但是，一个公司里面的数据库服务器上面可能同时运行着很多个项目的数据库。所以，我们应该可以根据不同的项目建立不同的用户，分配不同的权限来管理和维护数据库。

2.1 创建用户
CREATE USER ‘用户名’@’主机名’ IDENTIFIED BY ‘密码’;
关键字说明：

用户名：将创建的用户名
2. 主机名：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
3. 密码：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器

具体操作:

-- user1用户只能在localhost这个IP登录mysql服务器
CREATE USER 'user1'@'localhost' IDENTIFIED BY '123';
-- user2用户可以在任何电脑上登录mysql服务器
CREATE USER 'user2'@'%' IDENTIFIED BY '123';

2.2 授权用户

用户创建之后，基本没什么权限！需要给用户授权
day02-mysql - 图2
授权格式：
GRANT 权限1, 权限2… ON 数据库名.表名 TO ‘用户名’@’主机名’;
关键字说明：

GRANT 授权关键字
2. 授予用户的权限，如SELECT，INSERT，UPDATE等。如果要授予所的权限则使用ALL
数据库名.表名：该用户可以操作哪个数据库的哪些表。如果要授予该用户对所有数据库和表的相应操作权限则可用表示，如`.*`
'用户名'@'主机名': 给哪个用户授权

具体操作:

给user1用户分配对test这个数据库操作的权限

GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON test.* TO 'user1'@'localhost';

给user2用户分配对所有数据库操作的权限
```
GRANT ALL ON *.* TO 'user2'@'%';
```
2.3 撤销授权(了解)
REVOKE 权限1, 权限2… ON 数据库.表名 FROM ‘用户名’@’主机名’;
具体操作:

撤销user1用户对test操作的权限

REVOKE ALL ON test.* FROM 'user1'@'localhost';

2.4 查看权限(了解)
SHOW GRANTS FOR ‘用户名’@’主机名’;
具体操作:
查看user1用户的权限
```
SHOW GRANTS FOR 'user1'@'localhost';
```
2.5 删除用户(了解)
DROP USER ‘用户名’@’主机名’;
具体操作:
删除user2
```
DROP USER 'user2'@'%';
```
2.6 修改用户密码(了解)

2.6.1 修改管理员密码
mysqladmin -uroot -p password 新密码 — 新密码不需要加上引号

注意：需要在未登陆MySQL的情况下操作。

具体操作:

mysqladmin -uroot -p password 123456
输入老密码

day02-mysql - 图8

2.6.2 修改普通用户密码

set password for ‘用户名’@’主机名’ = password(‘新密码’);

注意：需要在登陆MySQL的情况下操作。

具体操作:

set password for 'user1'@'localhost' = password('666666');

day02-mysql - 图9

第二章数据库备份与还原

3.1 备份的应用场景

在服务器进行数据传输、数据存储和数据交换，就有可能产生数据故障。比如发生意外停机或存储介质损坏。这时，如果没有采取数据备份和数据恢复手段与措施，就会导致数据的丢失，造成的损失是无法弥补与估量的。

3.2 source命令备份与还原

备份格式：
mysqldump -u用户名 -p密码数据库 > 文件的路径
还原格式：
SOURCE 导入文件的路径;

注意：还原的时候需要先登录MySQL,并选中对应的数据库

备份bd1数据库中的数据

具体操作:

mysqldump -uroot -p db1 > G:\db1.sql
输入密码
-------------
注意:生成的sql文件中,备份有表的创建语句和数据的sql语句,没有创建数据库的语句

day02-mysql - 图10

还原db1数据库中的数据
- 删除db1数据库中所有的内容
- 创建一个新的db1数据库
- 选中数据库
- 使用SOURCE命令还原数据
```
source G:/sql/db1.sql
```
  3.3 图形化界面备份与还原
备份db1数据库中的数据
选中数据库，右键 ”备份/导出”，指定导出路径，保存成.sql文件即可。
还原db1数据库中的数据
- 删除db1数据库
- 数据库列表区域右键“执行SQL脚本”，指定要执行的SQL文件，执行即可

day02-mysql - 图20

第三章数据库约束

约束概述
约束其实就是一种限制,用于修饰表中的列. 通过这种限制来保证表中数据的正确性、有效性和完整性。
约束的类型

约束名	约束关键字
主键	primary key
非空	not null
唯一	unique
外键	foreign key … references
默认	default

4.1主键约束(重点)

4.1.1 主键的作用

用来标注一条记录的唯一性，每个表都应该有一个主键，并且每个表只能有一个主键。
有些记录的 name,age,score 字段的值都一样时,那么就没法区分这些数据,造成数据库的记录不唯一,这样就不方便管理数据

哪个字段应该作为表的主键？
通常不用业务字段作为主键，单独给每张表设计一个id的字段，把id作为主键。主键是给数据库和程序使用的，不是给最终的客户使用的。所以主键有没有含义没有关系，只要不重复，非空就行。

4.1.2 创建主键

主键：PRIMARY KEY
主键的特点：

主键字段值唯一(唯一不可重复)
主键列不能包含NULL值
一个表中只能有一个主键,但主键可以是多个字段 (联合主键)

创建主键方式：

方式1:创建表时,添加主键约束 (字段约束区)
    格式:
        create table 表名 ( 
            字段名称1 字段类型 字段约束区,     
            字段名称2 字段类型 字段约束区
        );
方式2:创建表时,在约束区域添加约束(扩展)
    格式1:单一主键
        create table 表名 (
            字段名称1 字段类型,     
            字段名称2 字段类型,     
            primary key(字段名称1)  
        );
    格式2:联合主键
        create table 表名 (
            字段名称1 字段类型,     
            字段名称2 字段类型,     
            primary key(字段名称,字段名称1)  
        );
方式3:给已有表中添加主键(扩展)
    alter table 表名 add primary key(字段名称);

具体操作:
创建表用户表, 包含字段(id, name)将id做为主键

-- 主键约束
-- 方式1: 建表时在字段的约束区添加主键约束
CREATE TABLE user1(
    id INT PRIMARY KEY, # 在字段的约束区添加主键约束
    `name` VARCHAR(20)
);
-- 添加数据信息
INSERT INTO user1 VALUES(1,'tom'); -- 正常插入
INSERT INTO user1 VALUES(NULL,'tom'); -- 插入异常(主键不可为空): Column 'id' cannot be null
INSERT INTO user1 VALUES(1,'tom'); -- 插入异常(主键不可重复): Duplicate entry '1' for key 'PRIMARY'
-- 方式2: 建表时在约束区添加主键约束
CREATE TABLE user2(
    id INT, 
    `name` VARCHAR(20),
    PRIMARY KEY (id)
);
-- 方式3: 创建完表后,通过修改表结构添加主键约束
CREATE TABLE user3(
    id INT, 
    `name` VARCHAR(20)
);
-- 修改表添加主键
ALTER TABLE user3 ADD PRIMARY KEY (id);

day02-mysql - 图22
day02-mysql - 图23
创建表用户表, 包含字段(id, name) 将id和name做为联合主键

-- 联合主键
CREATE TABLE user4(
    id INT, 
    `name` VARCHAR(20),
    PRIMARY KEY (id,`name`)
);
-- 保证id和name字段不为空,然后联合判断唯一性
INSERT INTO user4 VALUES(1,'tom');    -- 正常插入
INSERT INTO user4 VALUES(2,"tom");    -- 正常插入
INSERT INTO user4 VALUES(1,"rose");    -- 正常插入
INSERT INTO user4 VALUES(2,"rose");    -- 正常插入
INSERT INTO user4 VALUES(1,"tom");    -- 插入异常:Duplicate entry '1-tom' for key 'PRIMARY'
INSERT INTO user4 VALUES(NULL,"jack"); -- 插入异常:Column 'id' cannot be null
INSERT INTO user4 VALUES(3,NULL); -- 插入异常:Column 'name' cannot be null

注意 : 一张表中只有一个主键 , 主键可以为多个字段 , 不过我们一般增加一个字段 id 来作为主键.

4.1.3 删除主键

ALTER TABLE 表名 DROP PRIMARY KEY;
具体操作:

删除user1表的主键
```
ALTER TABLE user1 DROP PRIMARY KEY;
```
4.1.4 主键自增
主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值
AUTO_INCREMENT 表示自动增长(字段类型是整型数字)
具体操作:

创建学生表user5, 包含字段(id, name)将id做为主键并自动增长

CREATE TABLE user5 (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20)
);

插入数据

-- 主键默认从1开始自动增长
INSERT INTO user5 (name) VALUES ('唐僧');
INSERT INTO user5 (name) VALUES ('孙悟空');
INSERT INTO user5 VALUES (null,'猪八戒');
INSERT INTO user5 VALUES (null,'沙僧');

day02-mysql - 图25

DELETE和TRUNCATE的区别

DELETE删除表中的数据，不重置AUTO_INCREMENT的值
TRUNCATE摧毁表，重建表，AUTO_INCREMENT重置为1
如果想自定义id的值,可以使用下面的sql进行设置,一般不需要
ALTER TABLE 表名 AUTO_INCREMENT=起始值;

4.2 唯一约束

在这张表中这个字段的值不能重复

4.2.1 唯一约束的基本格式

字段名字段类型 UNIQUE

4.2.2 实现唯一约束

具体操作:

创建学生表st7, 包含字段(id, name),name这一列设置唯一约束,不能出现同名的学生
```
CREATE TABLE st7 (
  id INT,
  NAME VARCHAR(20) UNIQUE
);
```

添加一个学生

INSERT INTO st7 VALUES (1, '貂蝉');
INSERT INTO st7 VALUES (2, '西施');
INSERT INTO st7 VALUES (3, '王昭君');
INSERT INTO st7 VALUES (4, '杨玉环');
-- 插入相同的名字出现name重复: Duplicate entry '貂蝉' for key 'name'
INSERT INTO st7 VALUES (5, '貂蝉');
-- 出现多个null的时候会怎样？因为null是没有值，所以不存在重复的问题
INSERT INTO st3 VALUES (5, NULL);
INSERT INTO st3 VALUES (6, NULL);

4.3 非空约束

被修饰的这个字段必须设置值,不能是NULL
字段名字段类型 NOT NULL
具体操作:

创建表学生表st8, 包含字段(id,name,gender)其中name不能为NULL

CREATE TABLE st8 (
  id INT,
  NAME VARCHAR(20) NOT NULL,
  gender CHAR(2)
);

添加一条完整的记录

INSERT INTO st8 VALUES (1, '郭富城', '男');
INSERT INTO st8 VALUES (2, '黎明', '男');
INSERT INTO st8 VALUES (3, '张学友', '男');
INSERT INTO st8 VALUES (4, '刘德华', '男');
-- 姓名不赋值出现姓名不能为null: Column 'name' cannot be null
INSERT INTO st8 VALUES (5, NULL, '男');

4.4 默认值

往表中添加数据时,如果不指定这个字段的数据,就使用默认值
默认值格式
字段名字段类型 DEFAULT 默认值
具体操作:

创建一个学生表 st9，包含字段(id,name,address)，地址默认值是广州

CREATE TABLE st9 (
  id INT,
  NAME VARCHAR(20),
  address VARCHAR(50) DEFAULT '广州'
);

添加一条记录,使用默认地址

INSERT INTO st9 (id, NAME) VALUES (1, '刘德华');

添加一条记录,不使用默认地址
```
INSERT INTO st9 VALUES (2, '张学友', '香港');
```
第四章多表间的关系

5.1 表关系概述
现实生活中，实体与实体之间肯定是有关系的，比如：老公和老婆，部门和员工，用户和订单、订单和商品、学生和课程等等。那么我们在设计表的时候，就应该体现出表与表之间的这种关系！表和表之间的关系分成三种：

一对一 (老公和老婆)
一对多 (部门和员工, 用户和订单)
多对多 (学生和课程)

例如:
双11当天,马哥和东哥两个用户分别在淘宝上下了一些订单,已知马哥下了1个订单,订单总金额为999元.东哥下了2个订单,订单金额分别为1314元和10元.
思考: 数据库该如何存放这些数据呢?
没有建立关系前: 通过表数据不能得知数据间的联系,这样存放数据是没有意义的
day02-mysql - 图27
建立关系后:
通过对该业务的分析,可得知一个用户可以有多个订单,一个订单只属于一个用户.
我们管1的一方,叫主表或1表. 我们管多个一方,叫从表或多表.
通常要在多的一方添加一个字段,用于存放主表主键的值,我们管这个字段叫外键字段.
外键字段的值必须为主表主键的值,若为其他值,则没有意义.
day02-mysql - 图28
用于限制外键字段取值必须为主表主键的值的约束叫做-外键约束.

5.2 一对多

一对多（1:n）
例如：班级和学生，部门和员工，客户和订单，分类和商品
一对多建表原则: 在从表(多方)创建一个字段,指向主表(一方)的主键.我们把这个字段称之为外键.

5.3 多对多

多对多（m:n）
例如：老师和学生，学生和课程，用户和角色
多对多关系建表原则: 需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。

5.4 一对一 (了解)

一对一（1:1）
在实际的开发中应用不多.因为一对一可以创建成一张表。
两种建表原则：

外键唯一：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一UNIQUE
外键是主键：主表的主键和从表的主键，形成主外键关系

5.5 外键约束
5.5.1 什么是外键约束
一张表中的某个字段引用另一个表的主键
主表：约束别人
副表/从表：使用别人的数据，被别人约束

5.5.2 创建外键

新建表时增加外键：
[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
关键字解释：
CONSTRAINT — 约束关键字
FOREIGN KEY(外键字段名) –- 某个字段作为外键
REFERENCES — 主表名(主键字段名) 表示参照主表中的某个字段
已有表增加外键：
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);

具体操作:
以”新建表时添加外键”演示

-- 先创建部门表
CREATE TABLE department (
    id INT PRIMARY KEY AUTO_INCREMENT,
    dep_name VARCHAR(20),
    dep_location VARCHAR(20)
);
-- 添加2个部门
INSERT INTO department (dep_name, dep_location) VALUES ('研发部', '广州'), ('销售部', '深圳');
-- 然后创建员工表,添加外键约束
CREATE TABLE employee (
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20),
    age INT,
    dep_id INT,
    -- 添加一个外键
    -- 外键取名公司要求,一般fk结尾
    CONSTRAINT emp_depid_ref_dep_id_fk FOREIGN KEY(dep_id) REFERENCES department(id)
);

正常添加数据

INSERT INTO employee (NAME, age, dep_id) VALUES
('张三', 20, 1),
('李四', 21, 1),
('王五', 20, 1),
('老王', 20, 2),
('大王', 22, 2),
('小王', 18, 2);

部门错误的数据添加失败
```
INSERT INTO employee (NAME, age, dep_id) VALUES ('二王', 20, 5);
```
5.5.3 删除外键(了解)
ALTER TABLE 从表 drop foreign key 外键名称;
具体操作:

删除employee表的emp_depid_ref_dep_id_fk外键

ALTER TABLE employee DROP FOREIGN KEY emp_depid_ref_dep_id_fk;

在employee表情存在况下添加外键
```
ALTER TABLE employee ADD CONSTRAINT emp_depid_ref_dep_id_fk FOREIGN KEY(dep_id) REFERENCES department(id);
```
5.5.4 数据操作注意事项
```
添加数据时:
  先添加主表中的数据,再添加从表中的数据
删除数据时:
  先删从表中的数据,再删主表中的数据
修改数据时:
  如果主表中的主键被从表引用了,不能修改此主键的值
```
第五章数据库范式(了解)

1.1 什么是范式
范式是指:设计数据库表的规则(Normal Form)
好的数据库设计对数据的存储性能和后期的程序开发，都会产生重要的影响。建立科学的，规范的数据库就需要满足一些规则来优化数据的设计和存储

1.2 范式的基本分类
目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般说来，数据库只需满足第三范式(3NF）就行了。

1.3 第一范式
即数据库表的每一列都是不可分割的原子数据项，而不能是集合、数组、记录等非原子数据项。即实体中的某个属性有多个值时，必须拆分为不同的属性。在符合第一范式（1NF）表中每个列的值只能是表的一个属性或一个属性的一部分。简而言之，第一范式每一列不可再拆分，称为原子性。
第一范式：每一列不能再拆分

总结：如果不遵守第一范式，查询出数据还需要进一步处理（查询不方便）。遵守第一范式，需要什么字段的数据就查询什么数据（方便查询）。

1.4 第二范式

第二范式（2NF）要求数据库表中的每个实例或记录必须可以被唯一地区分。选取一个能区分每个实体的属性或属性组，作为实体的唯一标识。例如在员工表中的身份证号码即可实现每个员工的区分，该身份证号码即为候选键，任何一个候选键都可以被选作主键。在找不到候选键时，可额外增加属性以实现区分。<br />    第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。简而言之，第二范式就是在第一范式的基础上属性完全依赖于主键。<br />**第二范式**：

一张表只描述一件事情
表中的每一个字段都依赖于主键

总结：如果不准守第二范式，数据冗余，相同数据无法区分。遵守第二范式减少数据冗余，通过主键区分相同数据。

1.5 第三范式

在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）<br />第三范式（3NF）是第二范式（2NF）的一个子集，即满足第三范式（3NF）必须满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个关系中不包含已在其它关系已包含的非主关键字信息。<br />**第三范式**：从表的外键必须使用主表的主键<br />![范式04.png](https://cdn.nlark.com/yuque/0/2022/png/22810121/1651027703178-6b16b5ae-b594-4afd-aabe-2f5391b0ee9d.png#clientId=ua6e2e070-2360-4&crop=0&crop=0&crop=1&crop=1&from=drop&id=ua4303295&margin=%5Bobject%20Object%5D&name=%E8%8C%83%E5%BC%8F04.png&originHeight=379&originWidth=1038&originalType=binary&ratio=1&rotation=0&showTitle=false&size=26099&status=done&style=none&taskId=uc02d2f2f-92d7-43f9-b8e5-10eaffbf2d5&title=)

总结：如果不准守第三范式，可能会有相同数据无法区分，修改数据的时候多张表都需要修改（不方便修改）。遵守第三范式通过id可以区分相同数据，修改数据的时候只需要修改一张表（方便修改）。

1.6 反三范式

反三范式相对于三范式的，没有冗余的数据库未必是最好的数据库，有时为了提高数据库的运行效率，就必须降低范式标准，适当保留冗余数据。
具体做法是：在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加适当的字段，减少了查询时的关联，提高查询效率，因为在数据库的操作中查询的比例要远远大于DML的比例。但是反范式化一定要适度，并且在原本已满足三范式的基础上再做调整

第六章多表查询

同时查询多张表获取到需要的数据
比如：我们想查询到开发部有多少人，需要将部门表和员工表同时进行查询

多表查询的分类：

准备数据

-- 创建部门表
create table dept(
  id int primary key auto_increment,
  name varchar(20)
);
insert into dept (name) values ('开发部'),('市场部'),('财务部'); 
-- 创建员工表
create table emp (
  id int primary key auto_increment,
  name varchar(10),
  gender char(1),   -- 性别
  salary double,   -- 工资
  join_date date,  -- 入职日期
  dept_id int,
  foreign key (dept_id) references dept(id) -- 外键，关联部门表(部门表的主键)
);  
insert into emp(name,gender,salary,join_date,dept_id) values('孙悟空','男',7200,'2013-02-24',1);
insert into emp(name,gender,salary,join_date,dept_id) values('猪八戒','男',3600,'2010-12-02',2);
insert into emp(name,gender,salary,join_date,dept_id) values('唐僧','男',9000,'2008-08-08',2);
insert into emp(name,gender,salary,join_date,dept_id) values('白骨精','女',5000,'2015-10-07',3);
insert into emp(name,gender,salary,join_date,dept_id) values('蜘蛛精','女',4500,'2011-03-14',1);

查询某员工在哪个部门？

-- 只查询一张表不能查询出员工名字和部门名字，需要使用多表操作
select * from emp, dept;

完成多表操作的两种方式：

表连接
子查询
2.1 笛卡尔积
概念

左表的每条数据和右表的每条数据组合成新的数据
如：查询员工表和部门表，查询的数据如下，结果就是笛卡尔积的数据

select * from emp,dept;

day02-mysql - 图37
查询某员工所在的部门

部门是左表，员工是右表。
day02-mysql - 图38
我们发现不是所有的数据组合都是有用的，只有员工表.dept_id = 部门表.id 的数据才是有用的。所以需要通过条件过滤掉没用的数据

-- 1. 查询孙悟空在哪个部门名字
select * from emp;
select * from dept;
-- 2. 查询所有的员工和所有的部门
-- 查询2张表结果是2张表记录的乘积，称为笛卡尔积
select * from emp,dept;
- 如何消除笛卡尔积：条件是从表.外键=主表.主键
select * from emp,dept where emp.dept_id = dept.id;
-- 这就是隐式内连接，使用where，没有用到join...on
-- 给表起别名
select * from emp e ,dept d where e.dept_id = d.id;
-- 查询孙悟空在哪个部门名字
select * from emp e ,dept d where e.dept_id = d.id and e.id=1;
-- 只查询孙悟空的员工名字和部门名字，并指定别名：员工名、部门名
select e.name 员工名,d.name 部门名 from emp e ,dept d where e.dept_id = d.id and e.id=1;

2.2 内连接

内连接分类

隐式内连接
显示内连接

语法

-- 隐式内连接语法
select 列名 from 左表,右表 where 从表.外键=主表.主键
-- 显示内连接, on后面就是表连接的条件
select 列名 from 左表 inner join 右表 on 从表.外键=主表.主键

应用

查询唐僧的信息，显示员工id，姓名，性别，工资和所在的部门名称

确定查询哪些表
确定表连接条件，员工表.dept_id = 部门表.id 的数据才是有效的
确定查询条件，我们查询的是唐僧的信息，员工表.name=’唐僧’
确定查询字段，查询唐僧的信息，显示员工id，姓名，性别，工资和所在的部门名称

我们发现写表名有点长，可以给表取别名，显示的字段名也使用别名

-- 查询唐僧的信息，显示员工id，姓名，性别，工资和所在的部门名称
-- 1. 确定查询哪些表
select * from emp e inner join dept d;
-- 2. 确定表连接的条件
select * from emp e inner join dept d on e.dept_id = d.id;
-- 3. 如果有其它的查询条件，添加where语句
select * from emp e inner join dept d on e.dept_id = d.id where e.name='唐僧';
-- 4. 确定查询哪些列
select e.id 编号, e.name 姓名, e.gender 性别, e.salary 工资, d.name 部门名
from emp e inner join dept d on e.dept_id = d.id where e.name='唐僧';

2.3 外连接

外连接分类

左外连接
左表中所有的记录都出现在结果中，如果右表没有匹配的记录，使用NULL填充
右外连接
右表中所有的记录都出现在结果中，如果左表没有对应的记录，使用NULL填充

语法

-- 左外连接，左表中所有的记录都出现在结果，如果右表没有匹配的记录，使用NULL填充
select 列名 from 左表 left join 右表 on 从表.外键=主表.主键
-- 右外连接，保证右表中所有的数据都出现，如果左表没有对应的记录，使用NULL填充
select 列名 from 左表 right join 右表 on 从表.外键=主表.主键

应用

左外连接
需求：查询所有的部门，以及该部门下面的员工

-- 添加一个销售部，暂时还没有员工
insert into dept (name) values ('销售部');
-- 使用内连接查询，缺少销售部
select * from dept d inner join emp e on d.id = e.dept_id;
-- 使用左外连接查询
select * from dept d left join emp e on d.id = e.dept_id;

day02-mysql - 图42
右外连接
需求：查询所有员工，以及员工所属的部门

-- 在员工表中增加一个员工:'沙僧','男',6666,'2013-02-24',null
insert into emp values(null, '沙僧','男',6666,'2013-02-24',null);
select * from emp;
-- 使用内连接查询 
select * from dept d inner join emp e on d.id = e.dept_id;
-- 使用右外连接查询 
select * from dept d right join emp e on d.id = e.dept_id;

day02-mysql - 图43

2.4 子查询

什么是子查询

将一个查询的结果做为另一个查询的条件
这是一种查询语句的嵌套，嵌套的SQL查询称为子查询。
如果使用子查询必须要使用括号

子查询分类

单行单列
单行多列
多行多列
5.3.1 单行单例
效果图

语法

-- 如果子查询是单行单列，父查询使用比较运算符：> < =

应用

-- 1. 查询最高工资是多少
select max(salary) from emp;
-- 2. 查询最高工资的员工信息
select * from emp where salary=(select max(salary) from emp);
-- 查询工资大于"蜘蛛精"的员工
-- 1. 查询蜘蛛精的工资是多少
select salary from emp where name='蜘蛛精';
-- 2. 查询大于这个工资的员工
select * from emp where salary > (select salary from emp where name='蜘蛛精');

5.3.2 多行单列

效果图

day02-mysql - 图45
语法

多行单列认为是一个数组，父查询使用in、any 、all关键字

关键字	说明
in	查询包含在in条件中的所有数据
all	可以与>、<结合起来使用，分别表示大于、小于其中的所有数据时条件为真
any	可以与>、<结合起来使用，分别表示大于、小于其中的任何一个数据时条件为真

应用

-- 查询工资大于5000的员工，来自于哪些部门，得到部门的名字
-- 1. 先查询大于5000的员工所在的部门id
select dept_id from emp where salary > 5000;
-- 2. 再查询在这些部门id中部门的名字
select * from dept where id in (select dept_id from emp where salary > 5000);
-- 查询工资高于在1号部门工作的所有员工的员工信息
-- 1. 查询1号部门所有员工的工资，得到多行单列
select salary from emp where dept_id=1;
-- 2. 使用all关键字
select * from emp where salary > all (select salary from emp where dept_id=1);
-- 查询工资高于在1号部门工作的所有员工最低工资的员工信息
-- 1. 查询1号部门所有员工的工资，得到多行单列
select salary from emp where dept_id=1;
-- 2. 使用any关键字
select * from emp where salary > any (select salary from emp where dept_id=1);

5.3.3 多行多列

效果图

day02-mysql - 图46
特点

子查询可以认为它是一张虚拟表，可以使用表连接再次进行多表查询
如果访问子查询表的字段，需要为子查询表取别名，否则无法访问表中的字段
应用

-- 查询出2011年以后入职的员工信息，包括部门名称
-- 1. 在员工表中查询2011-1-1以后入职的员工
select * from emp where join_date > '2011-1-1';
-- 2. 查询所有的部门信息，与上面的虚拟表中的信息组合，找出所有部门id等于的dept_id
select * from dept d inner join (select * from emp where join_date > '2011-1-1') e on d.id = e.dept_id;
-- 3. 使用右连接，否则没有部门的员工信息查询不到
select * from dept d right join (select * from emp where join_date > '2011-1-1') e on d.id = e.dept_id;

day02-mysql

学习目标

第一章 基础SQL-DCL语句(了解)

2.1 创建用户

2.2 授权用户

2.3 撤销授权(了解)

2.4 查看权限(了解)

2.5 删除用户(了解)

2.6 修改用户密码(了解)

2.6.1 修改管理员密码

2.6.2 修改普通用户密码

第二章 数据库备份与还原

3.1 备份的应用场景

3.2 source命令备份与还原

3.3 图形化界面备份与还原

第三章 数据库约束

4.1主键约束(重点)

4.1.1 主键的作用

4.1.2 创建主键

4.1.3 删除主键

4.1.4 主键自增

4.2 唯一约束

4.2.1 唯一约束的基本格式

4.2.2 实现唯一约束

4.3 非空约束

4.4 默认值

第四章 多表间的关系

5.1 表关系概述

5.2 一对多

5.3 多对多

5.4 一对一 (了解)

5.5 外键约束

5.5.1 什么是外键约束

5.5.2 创建外键

5.5.3 删除外键(了解)

5.5.4 数据操作注意事项

第五章 数据库范式(了解)

1.1 什么是范式

1.2 范式的基本分类

1.3 第一范式

1.4 第二范式

1.5 第三范式

1.6 反三范式

第六章 多表查询

2.1 笛卡尔积

2.2 内连接

2.3 外连接

2.4 子查询

5.3.1 单行单例

5.3.2 多行单列

5.3.3 多行多列

第一章基础SQL-DCL语句(了解)

第二章数据库备份与还原

第三章数据库约束

第四章多表间的关系

第五章数据库范式(了解)

第六章多表查询