MySql基础篇学习笔记

1、Mysql

1.1、MySql的启动与停止

MySQL安装完成之后，在系统启动时，会自动启动MySQL服务，我们无需手动启动了。

也可以使用指令启动、停止MySql

net start mysql80
net stop mysql80

其中mysql80为数据库默认名
1
2
3
4

1.2、MySql的连接

1. 使用MySQL提供的客户端命令行工具
2. 使用系统自带的命令行工具执行指令，如下

mysql [-h 127.0.0.1] [-P 3306] -u 用户名 -p
参数：
-h : MySQL服务所在的主机IP
-P : MySQL服务端口号， 默认3306
-u : MySQL数据库用户名
-p ： MySQL数据库用户名对应的密码

示例：
mysql -u root -p
1
2
3
4
5
6
7
8
9

注意：这里是指某个用户连接MySql数据库，所以上面的代码中要给的是用户名。连接成功后，该用户才能按照其权限操作MySql中的对应数据库。

2、SQL

2.1、DDL(Data Definition Language)

数据定义语言，用来定义数据库对象(数据库，表，字段)

2.1.1、数据库操作

show database; #查询所有数据库

select database(); #查询当前数据库

create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则]; #创建数据库

drop database [if exists] 数据库名; #删除数据库

use 数据库名; #切换数据库
1
2
3
4
5
6
7
8
9

2.1.2、表操作

show tables; #查询当前数据库中所有的 表

desc 表名; #查看指定 表 的结构

show create table 表名; #查询指定 表 的建表语句

create table 表名(
    字段1 字段1类型 [约束列表1] [comment 字段1注释],
    字段2 字段2类型 [约束列表2] [comment 字段2注释],
    字段3 字段3类型 [约束列表3] [comment 字段3注释],
    ......
    字段n 字段n类型 [约束列表n] [comment 字段n注释]
) [comment 表注释]; #创建 表 结构
#约束列表中可以放置多个约束，各约束间用空格分隔

alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];
#向表中添加 字段

alter table 表名 modify 字段名 新类型(长度);
#修改表中某个 字段 的数据类型

alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] ][约束];
#修改表中某个 字段 的字段名和字段类型

alter table 表名 drop 字段名;
#删除表中的某个 字段

alter table 表名 rename to 新表名;
#修改 表 的名称

drop table [if exists] 表名;
#删除指定 表

truncate table 表名;
#清空 表(删除指定表，然后重新创建该表)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

2.2、DML( Data Manipulation Language)

数据操作语言，用来对数据库表中的数据进行增删改。

insert into 表名 (字段名1,  字段名2, ...) values (值1, 值2, ...);
#给指定字段添加数据

insert into 表名 values (值1, 值2, ...);
#给全部字段添加数据

insert into 表名 (字段名1,  字段名2, ...) values (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);
#给指定字段 批量 添加数据

insert into 表名 values (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);
#给全部字段 批量 添加数据

update 表名 set 字段名1 = 值1, 字段名2 = 值2, ... [where 条件];
#根据 条件 修改表中一些 字段的值

delete from 表名 [where 条件];
#根据 条件 删除表中一些 字段的值，如果不给条件则为清空表中所有字段的值
#DELETE 语句不能删除某一个字段的值(可以使用UPDATE，将该字段值置为NULL即可)。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

2.3、DQL(Data Query Language)

数据查询语言，用来查询数据库中表的记录。查询关键字：select

2.3.1、查询语句

select 字段列表 [as 别名] from 表名列表 [where 条件列表] [group by 分组字段列表] [having 分组后条件列表] [order by 排序字段列表] [limit 分页参数];
#其中字段列表中的字段以逗号分隔
1
2

2.3.2、普通查询

查询多个字段（取别名）

select 字段1 [[as] 别名1], 字段2 [[as] 别名2], ... from 表名;
#从表中查询一些 指定字段的值，[并给这些字段起别名]
1
2

去除重复记录：

select distinct 字段1 [[as] 别名1], 字段2 [[as] 别名2], ... from 表名;
#distinct关键字表示 对返回的值进行 去重
1
2

2.3.3、条件查询

2.3.3.1、条件

2.3.3.2、常用的逻辑运算符

2.3.3.3、一些功能相同的条件语句

select * from emp where age != 88;
select * from emp where age <> 88;
#查询年龄不等于 88 的员工信息


select * from emp where age >= 15 && age <= 20;
select * from emp where age >= 15 and age <= 20;
select * from emp where age between 15 and 20;#如果between后面的数大于and后面的，则查询不到任何结果(但是不报错)
#查询年龄在15岁(包含) 到 20岁(包含)之间的员工信息


select * from emp where age = 18 or age = 20 or age =40;
select * from emp where age in(18,20,40);
#查询年龄等于18 或 20 或 40 的员工信息

select * from emp where idcard like '%X';
select * from emp where idcard like '_________________X';
#查询身份证号最后一位是X的员工信息(前一个语句不限定idcard值的长度，后一个限定长度和_的个数加1相等)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

2.3.4、聚合函数

将一列数据作为一个整体，进行纵向计算 。

SELECT 聚合函数(字段列表) FROM 表名 ;
#注意：聚合函数不计算null值

#示例
select sum(age) from emp where workaddress = '西安';
# 统计西安地区员工的年龄之和

select count(idcard) from emp; 
#统计的是idcard字段不为null的个数
1
2
3
4
5
6
7
8
9

2.3.5、分组查询

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


select workaddress, count(*) address_count from emp where age < 45 group by workaddress having address_count >= 3;
#查询年龄小于45的员工 , 并根据工作地址分组 , 获取员工数量大于等于3的工作地址

select workaddress, gender, count(*) from emp group by gender, workaddress;
# 统计各个工作地址上班的男性及女性员工的数量
1
2
3
4
5
6
7
8

注意：分组之后，查询的字段(即select后面的字段) 一般为聚合函数 和 分组字段(即group by后面的字段)，查询其他字段无任何意义。

如上面的代码中的第7行，workaddress, gender, count(*)表示为要查询的字段，而workaddress, gender为分组字段，count(*)为聚合函数。

另外注意，[HAVING 分组后过滤条件]是使用了[GROUP BY 分组字段名]才能正确使用的。

2.3.6、排序查询

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;
#排序方式关键字分别为asc (代表升序，默认值，可省略)，desc (代表降序)
#排序时会先按 字段1排序方式1 进行排序，如果字段1中的值相等，再以字段2的值按排序方式2进行排序。

#示例
select * from emp order by age asc , entrydate desc;
#根据年龄对公司的员工进行升序排序, 年龄相同, 再 按照入职时间进行降序排序
1
2
3
4
5
6
7

2.3.7、分页查询

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

#示例
select * from emp limit 0,10;
select * from emp limit 10;
#查询第1页员工数据, 每页展示10条记录

select * from emp limit 10,10;
#查询第2页员工数据, 每页展示10条记录

select * from emp where gender = '男' and age between 20 and 40 order by age asc ,entrydate asc limit 5;
#查询性别为男，且年龄在20-40 岁(含)以内的前5个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序。
1
2
3
4
5
6
7
8
9
10
11
12

注意事项:

• 起始索引从 0 开始，起始索引 = （查询页码 - 1） 每页显示记录数*。
• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
• 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 查询记录数。
• limit可以用来限制查询到的记录数(见上面的代码第11行)

2.3.8、DQL执行顺序

2.4、DCL(Data Control Language)

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

注意：连接到mysql的用户，可能会因为权限问题无法管理用户，如查询用户、创建新用户、授予用户权限、删除用户等。可以使用root用户连接mysql，来对用户进行管理。

2.4.1、管理用户

select * from mysql.user; #查询用户

create user '用户名'@'主机名' identified by '密码'; #创建用户

alter user '用户名'@'主机名' identified with mysql_native_password by '新密码'; #修改用户密码

drop user '用户名'@'主机名' ; #删除用户

#----------------------------------
#示例
create user 'itcast'@'localhost' identified by '123456';
#创建用户itcast, 只能够在当前主机localhost访问, 密码123456;

create user 'heima'@'%' identified by '123456';
#创建用户heima, 可以在任意主机访问该数据库, 密码123456;

alter user 'heima'@'%' identified with mysql_native_password by '1234';
#修改用户heima的访问密码为1234;

drop user 'itcast'@'localhost';
#删除 itcast@localhost 用户
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

注意事项:

• 在MySQL中需要通过用户名@主机名的方式，来唯一标识一个用户。
• 主机名可以使用 ==%==通配。
• 这类SQL开发人员操作的比较少，主要是DBA（ Database Administrator 数据库 管理员）使用。

2.4.2、权限控制

MySQL中定义了很多种权限，但是常用的就以下几种：

show grants for '用户名'@'主机名'; #查询权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名'; #授予权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名'; #撤销权限

#示例
show grants for 'heima'@'%'; 
#查询 'heima'@'%' 用户的权限

grant all on itcast.* to 'heima'@'%';
#授予 'heima'@'%' 用户itcast数据库所有表的所有操作权限

revoke all on itcast.* from 'heima'@'%';
#撤销 'heima'@'%' 用户的itcast数据库的所有权限
1
2
3
4
5
6
7
8
9
10
11
12
13

注意事项：

• 多个权限之间，使用逗号分隔
• 授权时， 数据库名 和 表名 可以使用 * 进行通配，代表所有。

3、函数

3.1、字符串函数

MySQL中内置了很多字符串函数，常用的几个如下：

select 函数(参数);

select concat('Hello' , ' MySQL');
#输出为：HelloMySQL
select lpad('01', 5, '-');
#输出为：---01
select substring('Hello MySQL',1,5);
#输出为：Hello
#substring函数中要截取的字符串是从1开始的

update emp set workno = lpad(workno, 5, '0');
#将表emp中字段workno的所有值使用0进行左填充更改，填充后的长度为5

select rpad(workno,5,0) as new_workno from emp;
#将表emp中字段workno的所有值进行查询，然后将返回结果，使用0进行右填充更改，填充后每个值的长度为5。在操作中，给rpad(workno,5,0)起别名为newworkno
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

3.2、数值函数

常见的数值函数

select lpad(round(rand()*1000000 , 0), 6, '0');
#使用rand()函数乘以1000000获得0~1000000之间的随机小数，然后使用round()函数对该小数进行四舍五入，并去除小数位。最后将这个结果使用左填充函数lpad()，用0将其填充至6位。
#目的：通过数据库的函数，生成一个六位数的随机验证码。
1
2
3

3.3、日期函数

select curdate(); #获取当前日期(返回数据格式为yyyy-mm-dd)
select curtime(); #获取当前时间(返回数据格式为hh:mm:ss)
select now(); #获取当前日期和时间(返回数据格式为yyyy-mm-dd hh:mm:ss)
select YEAR(now()); #获取当前对应的年份(返回数据格式为yyyy)
select month(now()); #获取当前对应的月份(返回数据格式为m)
select day(now()); #获取当前对应的日期(返回数据格式为d)

select name, datediff(curdate(), entrydate) as 'entrydays' from emp order by entrydays desc;
#查询所有员工姓名和对应的入职天数，并将结果按降序排列
1
2
3
4
5
6
7
8
9

3.4、流程函数

注意，上面的case语句中，如果遇到下面这样的代码，

select name, workaddress,
       case
           when workaddress='北京' then '一线城市'
           when workaddress='北京' then curdate()
           else '二线城市' end as '工作地址'
from emp;
1
2
3
4
5
6

只有前面的when workaddress='北京' then '一线城市'生效。

另外case前面有逗号。

#查询emp表的员工姓名和工作地址 (北京/上海 -> 一线城市 , 其他 -> 二线城市)

#方法1
select name, workaddress,
       case(workaddress)
           when '北京' then '一线城市'
           when '北京' then curdate()
           when '上海' then '一线城市'
           else '二线城市' end as '工作地址'
from emp;

#方法2
select name, workaddress,
       case
           when workaddress='北京' then '一线城市'
           when workaddress='北京' then curdate()
           when workaddress='上海' then '一线城市'
           else '二线城市' end as '工作地址'
from emp;

#方法3
select name, workaddress,
       if(workaddress='北京'or workaddress='上海', '一线城市', '二线城市') as '工作地址'
from emp;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

案例：统计班级各个学员的成绩，展示的规则为：>=85时，展示优秀；>=60时展示及格；否则展示不及格。

#首先创建要查询的表
create table score(
    id int comment 'ID',
    name varchar(20) comment '姓名',
    math tinyint unsigned comment '数学成绩',
    english tinyint unsigned comment '英语成绩',
    chinese tinyint unsigned comment '语文成绩'
)comment '学员成绩表';

#然后在表中插入数据
insert into score value 
    (1, 'Tom', 67, 88, 95),
    (2, 'Rose', 23, 66, 90),
    (3, 'Jack', 56, 98, 76);

#方法1
select name,
       case when math>=85 then '优秀'
           when math>=60 then '及格'
           else '不及格' end as '数学',
       case when english>=85 then '优秀'
           when english>=60 then '及格'
           else '不及格' end as '英语',
       case when chinese>=85 then '优秀'
           when chinese>=60 then '及格'
           else '不及格' end as '语文'
from score;

#方法2
select name,
       if(math>=85,'优秀',if(math>=65,'及格','不及格')) as '数学',
       if(english>=85,'优秀',if(english>=65,'及格','不及格')) as '英语',
       if(chinese>=85,'优秀',if(chinese>=65,'及格','不及格')) as '语文'
from score;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

4、约束

4.1、概述

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。

目的：保证数据库中数据的正确、有效性和完整性。

注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。约束可以设置多个，多个约束间使用空格进行分隔。

4.2、案例

案例需求：根据下表中的约束在数据库中创建表tb_user

create table tb_user(
    id int primary key auto_increment comment 'ID',
    name varchar(10) not null unique comment '姓名',
    age tinyint unsigned check ( age>0 and age<=120) comment '年龄',
    status char(1) default '1' comment '状态',
    gender char(1)
)comment '用户表';
1
2
3
4
5
6
7

4.3、外建约束

外键：用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

4.3.1、例程

左侧的emp表是员工表，里面存储员工的基本信息，包含员工的ID、姓名、年龄、职位、薪资、入职日 期、上级主管ID、部门ID，在员工的信息中存储的是部门的ID dept_id，而这个部门的ID是关联的 部门表dept的主键id，那emp表的dept_id就是外键,关联的是另一张表的主键。

注意：目前上述两张表，只是在逻辑上存在这样一层关系；在数据库层面，并未建立外键关联， 所以是无法保证数据的一致性和完整性的，也即是可以对父表中的数据进行修改或者删除，这样就出现了数据的不完整性。 而要想解决这个问题就得通过数据库的 外键约束。

#创建父表
create table dept(
	id int auto_increment comment 'ID' primary key,
	name varchar(50) not null comment '部门名称'
)comment '部门表';
#插入数据
INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4,'销售部'), (5, '总经办');

#创建子表
create table emp(
	id int auto_increment comment 'ID' primary key,
	name varchar(50) not null comment '姓名',
	age int comment '年龄',
	job varchar(20) comment '职位',
	salary int comment '薪资',
	entrydate date comment '入职时间',
	managerid int comment '直属领导ID',
	dept_id int comment '部门ID'
)comment '员工表';
#插入数据
INSERT INTO emp VALUES
(1, '金庸', 66, '总裁',20000, '2000-01-01', null,5),(2, '张无忌', 20, '项目经理',12500, '2005-12-05', 1,1),
(3, '杨逍', 33, '开发', 8400,'2000-11-03', 2,1),(4, '韦一笑', 48, '开发',11000, '2002-02-05', 2,1),
(5, '常遇春', 43, '开发',10500, '2004-09-07', 3,1),(6, '小昭', 19, '程序员鼓励师',6600, '2004-10-12', 2,1);

#为emp表的dept_id字段 添加 外键 约束，外键名称为fk_emp_dept_id，关联dept表的主键id。
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

#删除 emp表中名为fk_emp_dept_id的外键
alter table emp drop foreign key fk_emp_dept_id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

4.3.2、删除/更新行为

添加了外键之后，再删除父表数据时产生的约束行为，我们就称为删除/更新行为。具体的删除/更新行为有以下几种:

上图中，前两个为删除添加外键约束后的父表中数据时，产生的默认约束行为。

#设置删除/更新行为为cascade
alter table 表名 add constraint 外键名称 foreign key (外键字段) references 主表名 (主表字段名) on update cascade on delete cascade ;

#设置删除/更新行为为set null
alter table 表名 add constraint 外键名称 foreign key (外键字段) references 主表名 (主表字段名) on update set null on delete set null ;

#示例
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade ;
1
2
3
4
5
6
7
8

5、多表查询

5.1、多表关系

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

• 一对多(多对一) 。实现：在多的一方建立外键，指向一的一方的主键
• 多对多 。实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
• 一对一。实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

5.2、多表查询概述

5.2.1、概述

多表查询就是指从多张表中查询数据。原来查询单表emp中的数据，执行的SQL形式为：select * from emp; 那么我们要执行多表查询，就只需要使用逗号分隔多张表即可，如： select * from emp , dept ; 此时的查询结果中包含了大量的结果集，其为两个表emp和dept的笛卡尔积。如何来去除无效的笛卡尔积呢？ 我们可以给多表查询加上连接查询的条件即可，如代码：select * from emp , dept where emp.dept_id = dept.id;

• 连接查询
  • 内连接：相当于查询A、B交集部分数据
  • 外连接：
  • 左外连接：查询左表所有数据，以及两张表交集部分数据
  • 右外连接：查询右表所有数据，以及两张表交集部分数据
  • 自连接：当前表与自身的连接查询，自连接必须使用表别名
• 子查询

5.2.2、数据准备

create table dept(
    id int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '部门名称'
)comment '部门表';
INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'),(3, '财务部'), (4,'销售部'), (5, '总经办'), (6, '人事部');


create table emp(
    id int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '姓名',
    age int comment '年龄',
    job varchar(20) comment '职位',
    salary int comment '薪资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导ID',
    dept_id int comment '部门ID'
)comment '员工表';
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

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

5.3、内连接

内连接查询的是两张表交集部分的数据。

#隐式内连接
select 字段列表 from 表1, 表2 where 条件;

#显式内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件;
#在显式内连接中，inner可以省略。

select e.name '姓名',d.name '部门' from emp e, dept d where e.dept_id=d.id;

select e.name '姓名', d.name '部门' from emp e inner join dept d on e.dept_id=d.id;
#上面的代码中，由于先执行的是from语言是，所以在from中给表起别名后，后面执行的语句，只能使用表的别名。
1
2
3
4
5
6
7
8
9
10
11

5.4、外连接

外连接分为两种，分别是：左外连接 和 右外连接。

#左外连接
select 字段列表 from 表1 left [ outer ] join 表2 on 条件;
#左外连接相当于查询 表1(左表)的 所有 数据，当然也包含表1和表2交集部分的数据。

#右外连接
select 字段列表 from 表1 right [ outer ] join 表2 on 条件;
#右外连接相当于查询 表2(右表)的 所有 数据，当然也包含表1和表2交集部分的数据。


select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;
#查询emp表的所有数据, 和对应的部门信息（由于emp中有的数据没有部门信息，所以这个数据在使用内连接时是不会被查询到的，只有使用外连接才会）

select d.*, e.* from emp e right outer join dept d on e.dept_id = d.id;
#查询dept表的所有数据, 和对应的员工信息(右外连接)
#同样的dept中的有些部分，没有对应的员工信息，所以也只能使用外连接，才能将这部分数据也显示出来
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

5.5、自连接

5.5.1、自连接查询

自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。

查询语法及案例：

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;
#其中别名不能省略

#查询表emp中员工 及其 所属领导的名字
select a.name '员工', b.name '领导' from emp a, emp b where a.managerid=b.id;

#查询表emp中所有员工 及其领导的名字, 如果员工没有领导, 也需要查询出来
select a.name '员工',b.name '领导' from emp a left join emp b on a.managerid=b.id;
1
2
3
4
5
6
7
8

5.5.2、联合查询

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

#语法
SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;


#将薪资低于 5000 的员工 , 和 年龄大于 50 岁的员工全部查询出来.
#方法1
select * from emp where salary < 5000
union all
select * from emp where age > 50;
#上面的这个代码查询的结果是将第一个select语句查询的结果和第二个select语句查询的结果上下拼接在一起，但是由于all关键字的出现，不对这两个查询结果进行去重

#方法2
select * from emp where salary < 5000
union
select * from emp where age > 50;
#由于不使用all关键字，所以会对两个查询结果进行去重

#方法3
select * from emp e where e.salary<5000 or e.age>50;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

注意： 如果多条查询语句查询出来的结果，字段数量不一致，在进行union/union all联合查询时，将会报错。

5.6、子查询

5.6.1、概述

1. 概念

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

   SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );
   1

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

2. 分类

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

   A. 标量子查询（子查询结果为单个值）

   B. 列子查询(子查询结果为一列)

   C. 行子查询(子查询结果为一行)

   D. 表子查询(子查询结果为多行多列)

   根据子查询位置，分为：

   A. WHERE之后

   B. FROM之后

   C. SELECT之后

5.6.2、标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。 常用的操作符：= <> > >= < <=

#查询emp表中 "销售部" 的所有员工信息
select * from emp where dept_id = (select id from dept where name = '销售部');
#代码中的小括号不能省略

#查询在 "方东白" 入职之后的员工信息
select * from emp where entrydate > (select entrydate from emp where name = '方东白');
1
2
3
4
5
6

5.6.3、列子查询

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。

常用的操作符：

#查询emp表中属于 "销售部" 和 "市场部" 的所有员工信息
select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');

#查询emp表中比 财务部 所有人工资都高的员工信息
update emp set salary = 4800 where name='周芷若';
#由于建表时'周芷若'姓名对应的salary为48000，是表里面最高的，现在将其改小一点。不修改的话下面的语句没输出
select * from emp where salary > all ( select salary from emp where dept_id =(select id from dept where name = '财务部') );

#查询emp表中比 研发部其中任意一人工资高 的员工信息(比最小值高即可)
select * from emp where salary > any ( select salary from emp where dept_id = (select id from dept where name = '研发部') );
1
2
3
4
5
6
7
8
9
10

5.6.4、行子查询

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。

常用的操作符：= 、<> 、IN 、NOT IN

#查询与 "张无忌" 的薪资及直属领导相同的员工信息 
select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '张无忌');


#也可以使用下面的语句
select * from emp where salary = (select salary from emp where name='张无忌') and managerid = (select managerid from emp where name='张无忌');
1
2
3
4
5
6

5.6.5、表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询。子查询返回的结果可以看作一个表，该表可以再进行连接操作。

常用的操作符：IN

#查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
select * from emp where (job,salary) in ( select job, salary from emp where name ='鹿杖客' or name = '宋远桥' );

#查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;
#这个语句把(select * from emp where entrydate > '2006-01-01')当作了一个新表，然后起了个别名e，再和dept表进行左连接操作。
1
2
3
4
5
6

5.7、多表查询案例

5.7.1、数据准备

以下案例中，除了使用了上面所创建的表emp和dept，还要使用下面要新建的表salgrade，该表的建立即数据插入如下：

create table salgrade(
    grade int,
    losal int,
    hisal int
) comment '薪资等级表';
insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,25001,30000);
1
2
3
4
5
6
7
8
9
10
11
12
13

5.7.2、案例题目

1. 查询员工的姓名、年龄、职位、部门信息 （隐式内连接）
2. 查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）
3. 查询拥有员工的部门ID、部门名称(重要)
4. 查询所有年龄大于40岁的员工所有信息, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出 来(外连接)
5. 查询所有员工的信息及对应的工资等级(重要)
6. 查询 “研发部” 所有员工的信息及 工资等级
7. 查询 “研发部” 员工的平均工资
8. 查询工资比 “灭绝” 高的员工信息。
9. 查询比平均薪资高的员工信息
10. 查询低于本部门平均工资的员工信息(重要)
11. 查询所有的部门信息, 并统计部门的员工人数(重要)

5.7.3、案例对应代码

#1
select e.name '姓名', e.age '年龄', e.job '职位', d.name '部门信息' from emp e,dept d where e.dept_id=d.id;

#2
select e.name '姓名', e.age '年龄', e.job '职位', d.name '部门信息'
from emp e join dept d on e.dept_id = d.id where e.age < 30;

#3
select distinct e.dept_id, d.name from emp e, dept d where d.id=e.dept_id;

#4
select e.*, d.name '部门名称' from emp e left join dept d on e.dept_id=d.id where e.age>40;

#5
select e.*, s.grade from emp e, salgrade s where e.salary between s.losal and s.hisal;
#理解：这里使用的是隐式内连接，如果不加where条件的话，结果为表emp和表salgrade中grade的笛卡尔积，该结果中，e.salary列和s.losal列、s.hisal列长度是一样的，所以where后的语句相当与从这三列抽取同一行的三个数进行比较，如果满足则返回，不满足则不返回

#6
##方法1--使用表子查询的方式，先将emp中所有属于研发部的人员的所有信息查询出来，做为一个子表，并起别名为new，然后将这个表new与salgrade进行左连接查询
select new.*, s.grade
from (select * from emp e where e.dept_id = (select id from dept where dept.name = '研发部')) new
         left join salgrade s on new.salary between s.losal and s.hisal;
         
##方法2--使用隐式内连接的方法，对三个表进行查询
select e.*, s.grade
from emp e,
     dept d,
     salgrade s
where e.dept_id = d.id
  and e.salary between s.losal and s.hisal
  and d.name = '研发部';
  
#7
##方法1--和题6的方法1类似，先将emp中属于研发部门的人员查询出来作为一个子表，其别名为new，然后查询这个新表new中的salary，然后使用聚合函数求平均
select avg(new.salary) '平均工资'
from (select * from emp e where e.dept_id = (select id from dept where dept.name = '研发部')) new;

##方法2--和题6中的方法2类似，使用隐式内连接的方式，使用where中的条件限制笛卡尔积的输出结果，最后使用聚合函数计算平均值
select avg(e.salary) '平均工资'
from emp e,
     dept d
where e.dept_id = d.id
  and d.name = '研发部';

#8--使用标量子查询
select *
from emp
where salary > (select e.salary from emp e where e.name = '灭绝');

#9--使用标量子查询
select * from emp where salary>(select avg(salary) from emp);

#10
##方法1--先将各部门的平均工资和部门id查询出来作为一个新表a，将表emp、a进行隐式连接，给出限定条件，即部门id相同时，再取小于a中平均工资的行，最后输出
select e.*
from emp e,
     (select avg(salary) a, dept_id d from emp group by dept_id) a
where e.salary < a.a
  and a.d = e.dept_id
order by e.id;

##方法2--只从emp表中进行查询，条件是，员工的薪资小于部门的平均工资。在计算部门的平均工资时，需要将对应的部门dept_id传入。
select *
from emp e1
where e1.salary < (select avg(e2.salary) from emp e2 where e2.dept_id = e1.dept_id)
order by e1.id;

#11
select d.id 'ID', d.name '部门名称', (select count(*) from emp e where e.dept_id = d.id) '员工人数'
from dept d;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

6、事务

6.1、概述

事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

事务的四大特性（简称ACID）

• 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
• 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立 环境下运行。
• 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

6.2、控制事务

查看/设置事务提交方式

SELECT @@autocommit ;#查看事务提交方式
SET @@autocommit = 0 ;#设置事务提交方式为手动，需要执行commit或者rollback
1
2

提交事务

COMMIT;
1

回滚事务

ROLLBACK;
1

开启事务

START TRANSACTION 或 BEGIN ;
1

案例

-- 开启事务
start transaction
-- 1. 查询张三余额
select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';
-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';
-- 如果正常执行完毕, 则提交事务
commit;
-- 如果执行过程中报错, 则回滚事务
-- rollback;
1
2
3
4
5
6
7
8
9
10
11
12

6.3、并发事务问题

1. 赃读：一个事务读到另外一个事务还没有提交的数据。例如，事务1中虽然执行了修改表中的数据的语句，但是还没执行commit提交，但是这时另一个并发的事务2读取表中事务1要修改的数据时，发现数据已经被修改。
2. 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。例如：事务1执行了修改表中数据的语句，但还没提交时，另一个事务2查询表中的数据得到结果1，然后事务1提交，此时再在事务2中继续查询表中的数据，发现结果和结果1不同。
3. 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据 已经存在，好像出现了 “幻影”。例如：事务1查询了某个主键值，发现其不存在，而此时事务2在表中插入了这个主键值，并提交了。这时候事务1执行插入该主键的语句时，发现提示错误，错误为该主键已存在（主键不能重复）。但是事务1再执行查询该主键值是否存在的语句时，发现该值依然为空。(注意该例中事务1未提交)

6.4、事务的隔离级别

注意：事务隔离级别越高，数据越安全，但是性能越低。

结束

说明：本文是在看黑马程序员提供的视频和资料后记录的，用于本人存档。
致谢：感谢黑马程序员提供的优质教程！