MySQL笔记（基础篇）

一、数据库概述

---

1、数据库（DataBase，简称DB）：按照数据结构来组织、存储和管理数据的仓库（简单来讲就是存数据的，比如 用户信息、商品信息 等等）。

2、数据库管理系统（DataBase Management System ，简称DBMS）：操纵和管理数据库的大型软件。（通常我们把数据库管理系统称为数据库，比如常见DBMS有 MySQL、SQLServer、Oracle、PostgreSQL等等）

3、SQL（全称：Structured Query Language，结构化查询语言）：操作关系型数据库的编程语言，定义了一套操作关系型数据库的统一标准。

4、主流的关系型数据库管理系统。

• Oracle：大型的收费数据库，Oracle公司产品，价格昂贵。
• MySQL：开源免费的中小型数据库，后来Sun公司收购了MySQL，而Oracle又收购了Sun公司。(目前Oracle推出了收费版本的MySQL，也提供了免费的社区版本)
• SQL Server：Microsoft 公司推出的收费的中型数据库，C#、.net等语言常用。
• PostgreSQL：开源免费的中小型数据库。
• DB2：IBM公司的大型收费数据库产品。
• SQLLite：嵌入式的微型数据库，Android内置的数据库采用的就是该数据库。
• MariaDB：开源免费的中小型数据库。是MySQL数据库的另外一个分支、另外一个衍生产品，与 MySQL数据库有很好的兼容性。

SQL为操作关系型数据库提供了统一的标准，所以我们掌握一门RDBMS就八九不离十啦，剩下的就是个别语法差异（sql方言）。

关系型数据库(RDBMS)：就是建立在关系模型基础上，由多张相互连接的二维表组成的数据库。(通过表结构来存储数据)

除此之外还有非关系型数据库(NOSQL)，键值型Redis、文档型MongoDB、列式存储HBase、图形数据库Neo4J等等

MySQL数据模型：

二、MySQL环境搭建

---

版本介绍

MySQL官方提供了两种不同的版本：

• 社区版（ MySQL Community Server)，免费，不提供任何技术支持。
• 商业版（MySQL Enterprise Edition)，收费，可以试用30天，官方提供技术支持。

1. 下载MySQL软件包

下载地址：https://downloads.mysql.com/archives/community/

然后将软件解压到我们的环境目录中。

2. 配置MySQL

2.1 配置环境变量

变量名: MYSQL_HOME    
变量值: D:\Program Files\Environment\mysql-5.7.24

然后在 path变量中引用MySQL的环境变量：

%MYSQL_HOME%\bin

2.2 创建MySQL配置文件(my.ini)

[mysql]
default-character-set=utf8

[mysqld]
character-set-server=utf8
default-storage-engine=INNODB
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

2.3 初始化MySQL

在管理员权限的 “命令提示符” 窗口中执行如下命令后，就会自动在MySQL目录下生成一个data目录(存放数据库的)。

mysqld --initialize-insecure

2.4 注册MySQL服务

在管理员权限的 “命令提示符” 窗口中执行如下命令,就会在我们的电脑上注册MySQL服务。

mysqld -install

执行命令后提示：Service successfully installed 表示安装成功 !

MySQL服务器：就是在计算机上安装了MySQL服务。

2.5 启动MySQL服务

执行如下命令，启动或者停止MySQL服务。

net start mysql # 启动mysql服务

net stop mysql # 停止mysql服务

2.6 修改默认账户密码

在命令提示符中执行如下命令，这里的123456就是设置管理员(即root账户)的密码，可以自行修改成你喜欢的密码。

mysqladmin -u root password 123456

3. 登录MySQL

在命令提示符中输入如下命令就会连接MySQL服务器，并进入管理界面：

mysql -uroot -p123456

退出登录：

eixt 或  quit

登录参数：

mysq1 -u用户名 -p密码 -h要连接的mysq1服务器的ip地址(默认127.0.0.1) -P端口号(默认3306)

4. 卸载MySQL

1、打开管理员权限的命令提示符窗口。

2、停止MySQL服务。

net stop mysql

3、删除MySQL服务。

mysqld -remove mysql

4、删除MySQL工作目录及相关的环境变量。

5. MySQL图形化工具

• SQLyog

• Navicat

• DataGrip

• dbeaver

6. MySQL远程登录

mysql -h[ip地址] -P[端口号] -u[账户] -p[密码]

三、SQL

---

1. SQL通用语法

1、SQL语句可以单行或多行书写，以分号结尾。

2、SQL语句可以使用空格或者缩进来增强语句的可读性。

3、MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。

4、注释：

单行注释:  -- 单行注释 
		  # 单行注释  
		 
多行注释:  /*多行注释*/				 

2. SQL分类

分类	全称	说明
DDL	Data Definition Language	数据定义语言，用来定义数据库对象(数据库、表、字段)
DML	Data Manipulation Language	数据操作语言，用来对数据库表中的数据进行增删改
DQL	Data Query Language	数据查询语言，用来查询数据库中表的记录
DCL	Data Control Language	数据控制语言，用来创建数据库用户、控制数据库的访问权限

3. DDL

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

3.1 数据库操作

1、查询数据库

# 查询所有数据库
SHOW DATABASES;	

# 查询当前数据库
SELECT DATABASE();

2、创建数据库

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

• utf8字符集，存储长度是3个字节。（utf8中，一个中文字符占于三个字节）
• utf8mb4字符集，支持4个字节。（但是有的中文字符占4个字节）

3、删除数据库

DROP DATABASE [IF EXISTS] 数据库名;

4、使用数据库

USE 数据库名;

3.2 表操作

1、创建表

CREATE TABLE 表名(
    字段1  数据类型  [COMMENT 字段1的注释],
    字段2  数据类型  [COMMENT 字段2的注释],
    ...
    字段n  数据类型  [COMMENT 字段n的注释]
)[COMMENT 表注释];

• 可以使用数据类型(长度)方式指定数据大小。

• []中的内容是可选参数。

• 最后一个字段后面不用加逗号。

2、表查询操作

# 查询当前数据库有哪些表
SHOW TABLES;

# 查询表结构
DESC 表名; 

# 查询指定表的建表语句
SHOW CREATE TABLE 表名;

3、修改表

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

# 1、修改字段的类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);

# 2、修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];

# 3、删除字段
ALTER TABLE 表名 DROP 字段名;

# 4、修改表名
ALTER TABLE 表名 RENAME TO 新表名;

4、删除表

# 1、删除表
DROP TABLE [IF EXISTS] 表名;

# 2、删除指定表,并重新创建该表
TRUNCATE TABLE 表名;

注意：删除表的时候数据也会被删除，请做好备份操作。

3.3 数据类型

MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型。

1、数值类型

类型	大小(byte)	有符号(SIGNED)范围	无符号(UNSIGNED)范围	描述
tinyint（类似Java的byte）	1	(-128，127)	(0，255)	小整数值
smallint（类似Java的short）	2	(-32768，32767)	(0，65535)	大整数值
mediumint	3	(-8388608，8388607)	(0，16777215)	大整数值
int或integer	4	(-2147483648，2147483647)	(0，4294967295)	大整数值
bigint （类似Java的long）	8	(-263, 263-1)	(0，264-1)	极大整数值
float	4	(-3.402823466 E+38，3.402823466351 E+38)	0 和 (1.175494351 E-38，3.402823466 E+38)	单精度浮点数值
double	8	(-1.7976931348623157 E+308，1.7976931348623157 E+308)	0 和 (2.2250738585072014 E-308，1.7976931348623157 E+308)	双精度浮点数值
decimal	-	依赖于M(精度)和D(标度)的值	依赖于M(精度)和D(标度)的值	小数值(精确定点数)

示例：设置年龄字段(不会出现负数, 而且人的年龄不会太大)

age tinyint unsigned  -- unsigned 无符号数

示例：设置成绩字段(保留一位小数)

score double(4,1)  -- 整体长度4,保留一位小数

---

2、字符串类型

类型	大小(byte)	描述
char	0-255	定长字符串
varchar	0-65535	变长字符串
tinyblob	0-255	不超过255个字符的二进制数据
tinytext	0-255	短文本字符串
blob	0-65535	二进制形式的长文本数据
text	0-65535	长文本数据
mediumblog	0-1677215	二进制形式的中等长度文本数据
mediumtext	0-1677215	中等长度文本数据
longblob	0-4 294967295	二进制形式的极大文本数据
longtext	0-4 294967295	极大文本数据

• 带blog是描述二进制数据的。例如：音频、视频等等
• 带text是描述文本数据的。

char(10):定长字符串，比如你存储一个字符它也会占用十个字符的空间，因为它用空格补位

varchar(10): 变长字符串，它会根据内容去计算所占用的空间

char--->性能好       
varchar--->性能较差


# 用户名 username varchar(50)  -- 变化的
# 性别   gender char(1)        -- 定长

---

3、日期时间类型

类型	大小（byte）	范围	格式	描述
date	3	1000-01-01 至 9999-12-31	YYYY-MM-DD	日期值
time	3	-838:59:59 至 838:59:59	HH:MM:SS	时间值或持续时间
year	1	1901 至 2155	YYYY	年份值
datetime	8	1000-01-01 00:00:00 至 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
timestamp	4	1970-01-01 00:00:01 至 2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	混合日期和时间值，时间戳

示例：描述生日字段

birthday date

设计一张员工信息表，描述信息如下：

• 编号（纯数字）
• 员工工号（字符串类型，长度不超过10位）
• 员工姓名（字符串类型，长度不超过10位)
• 性别（男/女，存储一个汉字)
• 年龄（正常人年龄，不可能存储负数)
• 身份证号（二代身份证号均为18位，身份证中有X这样的字符)
• 入职时间（取值年月日即可)

create table emp(
	
	id int comment '编号',
	workno varchar(10) comment '工号',
	name varchar(10) comment '姓名',
	gender char(1) comment '性别',
	age tinyint unsigned comment '年龄',
	idcard char(18) comment '身份证号',
	entrydate date comment '入职时间'
	
) comment '员工表';

总结：

4. DML

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

• 添加数据（INSERT）
• 修改数据（UPDATE）
• 删除数据（DELETE）

4.1 添加数据

# 1、给指定字段添加数据
INSERT INTO 表名(字段1,字段2,...) VALUES(值1,值2,...);

# 2、给全部字段添加数据
INSERT INTO 表名 VALUES(字段1,字段2,...);


# 3、批量给指定字段添加数据
INSERT INTO 表名(字段1,字段2,...) VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...);

# 4、批量给全部字段添加数据
INSERT INTO 表名 VALUES(字段1,字段2,...),(字段1,字段2,...);

注意事项：

• 添加数据时，指定的字段顺序需要与值的顺序是一一对应的。
• 字符串和日期型数据应该包含在引号中。
• 插入的数据大小，应该在字段的规定范围内。

4.2 修改数据

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

注意：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

示例：

# 将王强的性别改成女,年龄改成20
update employee set gender='女',age=20 where name='王强';

4.3 删除数据

DELETE FROM 表名 [WHERE 条件]

注意事项：

• DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
• DELETE语句不能删除某一个字段的值(可以使用UPDATE)。
• 清空一张表用 truncate方式，它不会影响事务，然后表的结果和索引不会变，并重新设置AUTO_INCREMENT计数器。

总结：

5. DQL

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

DQL语法的编写顺序如下：

SELECT 
	   字段列表
FROM
	   表名列表
WHERE
	   条件列表
GROUP BY
	   分组字段列表
HAVING
       分组后条件列表
ORDER BY
		排序字段列表
LIMIT
		分页参数

• 条件查询（where）
• 聚合函数（count、max、min、avg、sum）
• 分组查询（group by）
• 排序查询（order by）
• 分页查询（limit）

插入sql如下：

-- 如果存在emp员工表,先将它删除然后再创建
drop table if exists emp;

create table emp(
    id int comment '编号',
    workno varchar(10) comment '工号',
    name varchar(10) comment '姓名',
    gender char(1) comment '性别',
    age tinyint unsigned comment '年龄',
    idcard char(18) comment '身份证号',
    workaddress varchar(50) comment '工作地址',
    entrydate date comment '入职时间'
)comment '员工表';

-- 插入一些员工信息
INSERT INTO emp(id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES(1, '00001', '柳岩666', '女', 20, '123456789012345678', '北京', '2000-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (2, '00002', '张无忌', '男', 18, '123456789012345670', '北京', '2005-09-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (3, '00003', '韦一笑', '男', 38, '123456789712345670', '上海', '2005-08-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (4, '00004', '赵敏', '女', 18, '123456757123845670', '北京', '2009-12-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (5, '00005', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (6, '00006', '杨逍', '男', 28, '12345678931234567X', '北京', '2006-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (7, '00007', '范瑶', '男', 40, '123456789212345670', '北京', '2005-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (8, '00008', '黛绮丝', '女', 38, '123456157123645670', '天津', '2015-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (9, '00009', '范凉凉', '女', 45, '123156789012345678', '北京', '2010-04-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (10, '00010', '陈友谅', '男', 53, '123456789012345670', '上海', '2011-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (11, '00011', '张士诚', '男', 55, '123567897123465670', '江苏', '2015-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (12, '00012', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (13, '00013', '张三丰', '男', 88,'123656789012345678', '江苏', '2020-11-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (14, '00014', '灭绝', '女', 65, '123456719012345670', '西安', '2019-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (15, '00015', '胡青牛', '男', 70,'12345674971234567X', '西安', '2018-04-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (16, '00016', '周芷若', '女', 18, null, '北京', '2012-06-01');

5.1 基本查询

1、查询多个字段

SELECT 字段1,字段2... FROM 表名;
SELECT * FROM 表名; -- 查询所有字段数据

示例：查询员工的姓名、工号、年龄

select name,workno,age from emp;

示例：查询员工的所有信息

select * from emp;

2、设置别名(起名字)

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

• AS:新名字 (AS也可以省略)

示例：示例：查询员工的工作地址，并其别名

select workaddress as '工作地址' from emp;
-- as可以省略 
select workaddress '工作地址' from emp; 

3、去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

示例：员工的上班地址有哪些(不要重复)

select distinct workaddress as '工作地址' from emp;

5.2 条件查询

1、条件查询语法

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

2、WHERE条件

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<> 或 !=	不等于
between … and …	在某个范围之内(含最小、最大值)
in(…)	在某个范围内的数据，多选一
like 占位符	模糊匹配(_匹配单个字符, %匹配任意个字符)
is null	是null

---

逻辑运算符	功能
and 或 &&	并且（多个条件同时成立）
or 或 ||	或者（多个条件任意一个成立）
not 或 ！	非，不是

示例：

# 查询年龄等于 88 的员工
select * from emp where age=88;

# 查询年龄小于 20 的员工信息
select * from emp where age<20;

# 查询年龄小于等于 20 的员工信息
select * from emp where age<=20;

# 查询没有身份证号的员工信息
select * from emp where idcard is null;

# 查询有身份证号的员工信息
select * from emp where idcard is not null;

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

# 查询年龄在15岁(包含) 到 20岁(包含)之间的员工信息
select * from emp where age between 15 and 20;
select * from emp where age >= 15 and age <= 20;

# 查询性别为 女 且年龄小于 25岁的员工信息
select * from emp where gender='女' and age <= 25;

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

# 查询姓名为两个字的员工信息 _ %
--两个下划线_
select * from emp where name like '__'; 

# 查询身份证号最后一位是X的员工信息
select * from emp where name like '%X';

# 查询姓张的员工信息
select * from emp where name like '张%'; 

5.3 聚合函数

1、聚合函数：将一列数据作为一个整体，进行纵向计算。

2、常见的聚合函数：

函数	功能
count	统计数量（一般选用不为null的列）
max	最大值
min	最小值
avg	平均值
sum	求和

3、语法：

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

注：null 值不参与所有聚合函数运算。

示例：

# 统计员工数量
select count(*) from emp; --统计的是总记录数
select count(idcard) from emp; --统计idcard字段不为null的记录数

select count(ifnull(idcard,0)) from emp; --操作数据的时候有时候判断是否存在,如果idcard值为null，把它赋初值0，然后统计idcard数量

# 统计员工的平均年龄
select avg(age) from emp;

# 统计员工的最大年龄
select max(age) from emp;

# 统计员工的最小年龄
select min(age) from emp;

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

5.4 分组查询

分组查询：就是将查询结果按照指定字段进行分组，字段中数据相等的分为一组。

1、分组查询语法：

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

2、where与having区别：

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

注意事项：

• 分组之后，查询的字段一般为 ‘’ 聚合函数 ‘’ 和 “ 分组字段 ‘’，查询其他字段无任何意义。
• 执行顺序：where > 聚合函数 > having 。
• 支持多字段分组, 具体语法为 ：group by columnA,columnB。

示例：

# 根据性别分组 , 统计男性员工 和 女性员工的数量
select gender,count(*) from emp group by gender;
---------------------------
|	gender  |	count(*)  |


# 根据性别分组 , 统计男性员工 和 女性员工的平均年龄
select gender,avg(age) from emp group by gender;


# 查询年龄小于45的员工 , 并根据工作地址分组 , 获取员工数量大于等于3的工作地址
select workaddress,count(*) address_count  from emp 
where age < 45  --分组之前过滤，年龄小于45岁
group by workaddress  -- 根据工作地址进行分组
having address_count>=3;  -- having 过滤员工数量大于等于3的工作地址
-- 	address_count是别名,as可以省略不写					
-----------------------------------
|	workaddress  |	address_count |

#  统计各个工作地址上班的男性及女性员工的数量
select workaddress,gender,count(*) from emp group by gender,workaddress; -- 按照性别和工作地址进行分组

5.5 排序查询

排序查询：就是按照某个列将数据进行升序或降序显示，关键字order by

排序查询的语法：

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

• 排序方式：asc 升序（默认）、desc 降序。
• 注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

示例：

# 1、根据年龄对公司的员工进行升序排序
select * from emp order by age asc;
select * from emp order by age;

# 2、根据入职时间,对员工进行降序排序
select * from emp order by entrydate desc;

# 3、根据年龄对公司的员工进行升序排序，年龄相同，再按照入职时间进行降序排序
select * from emp order by age asc,entrydate desc;
select * from emp order by age,entrydate desc;

5.6 分页查询

分页查询可以控制每页显示多少条数据，提升用户可读性。

分页查询的语法：

SELECT 字段列表 FROM 表名 LIMIT 起始索引,每页展示的记录数;

• 起始索引从0开始，运算规则：起始索引 =（页码-1）*每页显示的记录数。
• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
• 如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10。

示例：

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

# 查询第2页员工数据，每页显示10条数据
select * from emp limit 10,10;  -- limit (2-1)*10,10;

5.7 习题

# 查询年龄为20,21,22,23岁的员工信息。
select * from emp  where gender='女' and age in(20,21,22,23)


# 查询性别为男，并且年龄在20-40岁(含)以内的姓名为三个字的员工。
select * from emp  where gender='男' and age between 20 and 40
and name like '___';
 
 
# 统计员工表中,年龄小于60岁的，男性员工和女性员工的人数。
select gender,count(*) from emp  where age<60 
group by gender;


# 查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序。
select name,age from emp  where age<=35
order by age asc,entrydate desc;
 


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

DQL执行顺序：

# 查询年龄大于15的员工姓名、年龄，并根据年龄进行升序排列
select e.name ename,e.age eage from emp e where e.age > 15
order by eage asc;

执行顺序：
frm...
where...
select...
order by...
limit...

总结：

6. DCL

DCL，数据控制语言，用来创建数据库用户、控制数据库的访问权限。

6.1 用户管理

1、查询用户

USE mysql;
SELECT * FROM user;

• Host 代表当前用户访问的主机，如果为localhost表示只能够在当前本机访问，它不可以 远程访问。
• User 代表的是访问该数据库的用户名。
• 在MySQL中需要通过Host和User来唯一标识一个用户。

2、创建用户

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

3、修改用户密码

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

4、删除用户

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

注意：

• 主机名可以使用通配符 %，表示本地和远程都能访问。
• DBA(DataBase Administrator 数据库管理员)对这类SQL接触的甚多。

示例：

# 1、创建用户zhangsan, 只能够在当前主机localhost访问, 密码123456
create user 'zhangsan'@'localhost' identified by '123456';

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

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

# 4、删除 zhangsan@localhost 用户
drop user 'zhangsan'@'localhost';

6.2 权限控制

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

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

官方文档：https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html

1、查询权限

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

2、授予权限

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

3、撤销权限

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

示例：

# 1、查询 'lisi'@'%' 用户的权限
show grants for 'lisi'@'%';

# 2、授予 'lisi'@'%'用户对test数据库所有表的所有操作权限
grant all on test.* to 'lisi'@'%';
grant all on *.* to 'lisi'@'%'; -- 授予远程用户李四对所有数据库操作
flush privileges;   -- 刷新权限

# 3、撤销 'lisi'@'%'用户对test数据库的所有权限
revoke all on test.* from 'lisi'@'%';
flush privileges;   -- 刷新权限

注意：

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

四、函数

---

① 什么是函数？

• 就是一段可以直接被调用的程序或代码。

② MySQL常见的内置函数：

• 字符串函数
• 数值函数
• 日期函数
• 流程函数

1. 字符串函数

MySQL中常见的字符串函数如下：

SELECT 函数(参数);

# concat 字符串拼接
select concat('Hello','World');  # HelloWorld

# lower 全部转小写
select lower("MYSQLYYDS"); # mysqlyyds

# upper 全部转大写
select upper("mysqlyyds"); # MYSQLYYDS

# lpad 左填充
select lpad('01',5,'-'); # ---01

# rpad 右填充
select rpad('01',5,'-'); # 01---

# trim 去除空格
select trim('   人人都是产品经理 ');  # 人人都是产品经理

# substring 截取子字符串
select substring('Hello Java',1,5); # Hello ,索引从1开始

示例： 由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0。（比如： 1号员 工的工号应该为00001。）

update emp set workno = lpad(workno,5,'0'); -- 不加条件就是修改所有

处理之后的数据：

2. 数值函数

MySQL中常见的数值函数如下：

-- ceil 向上取整
select  ceil(1.1);  # 2
select  ceil(1.5);  # 2

-- floor 向下取整
select  ceil(1.1);  # 1

-- mod 取模
select  mod(3,4); # 3,小%大=小

-- rand 随机数
select  rand(); #0.5695846719384051,0-1之间的随机数

-- round 四舍五入
select  round(2.345,2); #对2.345进行四舍五入，然后保留两位小数->2.35
select  round(2.344,2); #对2.344进行四舍五入，然后保留两位小数->2.34

示例：通过数据库的函数，生成一个六位数的随机验证码。

select rand()*1000000; #生成随机数 679249.404630856

select round(rand()*1000000,0); # 去除小数，679249

select lpad(round(rand()*1000000,0),6,'0'); # 不满6位前面补0

3. 日期函数

MySQL中常见的日期函数如下：

-- curdate 获取当前日期
select curdate(); -- 2022-07-13

-- curtime 获取当前时间
select curtime(); -- 13:39:25

-- now 获取当前日期和时间
select now(); -- 2022-07-13 13:39:51

-- month 获取指定的年份
select year(now()); -- 2022

-- year 获取指定的月份
select month(now()); -- 7

-- day 获取指定的日期
select day(now()); -- 13

-- 返回70年后的日期时间
select date_add(now(),interval 70 year); -- 2092-07-13 13:46:21

-- 返回两个日期之间的天数
select datediff('2022-02-13','2022-04-27'); -- 73，date1-date2

示例：查询所有员工的入职天数，并根据入职天数倒序排序。

select name,datediff(curdate(),entrydate) as 'entrydays' from emp order by entrydays desc;

4. 流程函数

流程函数可以在SQL语句中实现条件筛选，从而提高语句的效率。

示例：

# if
select if(false, 'Ok', 'Error') # Error

# ifnull
select ifnull('Ok', 'Error') # OK
select ifnull('','Default'); # 空
select ifnull(null,'Default'); # Default

# case when then else end
# 查询emp表的员工姓名和工作地址 (北京/上海 ----> 一线城市 , 其他 ----> 二线城市
select
	name,
    ( case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else
    '二线城市' end ) as '工作地址'
from emp;                    

五、约束

---

• 约束是一种规范，定义在表中字段上的规则，用于限制存储在表中的数据，可以在创建表/修改表的时候添加约束。（简述：约束是作用表的列上一些规则，用来保证数据的完整性，一致性和有效性）
• 目的：保证数据库中数据的正确、有效性和完整性。

约束的分类：

• 非空约束 not null
• 唯一约束 unique
• 主键约束 primary key
• 默认约束 default
• 检查约束 check
• 外键约束 foreign key （多表）

1. 约束的使用

根据如下需求，创建一张表：

字段名	字段含义	字段类型	约束条件	约束关键字
id	ID唯一标识	int	主键，并且自动增长	primary key，auto_increment
name	姓名	varchar(10)	不为空，并且唯一	not null，unique
age	年龄	int	大于0，并且小于等于120	check
status	状态	char(1)	如果没有指的该值，默认为1	default
gender	性别	char(1)	无

建表的SQL语句如下：（在MySQL8.0环境下）

create table user(
    
	-- 设置字段id为主键,并且自动增长
	id int primary key auto_increment comment '编号', 
	
	-- 设置字段name不为空,并且数据唯一
	name varchar(10) not null unique comment '姓名', 
	
	-- 设置字段age,大于0并且小于等于120
	age int check (age>0 && age<=120) comment '年龄', 
	
	-- 设置字段status，如果没有指的数据,默认为1
	status char(1) default '1' comment '状态',
	
	gender char(1) comment '性别'
	 
) comment '用户表';

1、验证 id 字段的自增策略

insert into user(name,age,status,gender) 
values('Tom',19,'1','男'),('Jeny',25,'0','女');

-- 发现每条数据的id会自动生成,默认步长为1
-- 也就是说表中插入第一条数据的id为1,插入第二条数据的id为2,以此类推

2、验证 name 字段的约束(非空、唯一)

-- 验证非空
insert into user(name,age,status,gender) values(null,19,'1','男');

-- 验证唯一
insert into user(name,age,status,gender) values(Jeny,19,'1','男');

3、验证 age 字段的约束(check)

insert into user(name,age,status,gender) values('jack',-19,'1','男');

4、验证 status 字段的约束(default)

insert into user(name,age,gender) values('kangkang',20,'男');

可视化界面创建约束：

2. 外键约束

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

• 外键约束用法：用该表的字段指向另一个表的主键字段。（建立表之间的关系）

添加外键

# 方式一:在创建表的时候添加外键约束
CREATE TABLE 表名(
  ...
  [CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);

# 方式二:在建表之后被字段添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);

示例：使用外键，将两张表建立主从关系。

基本SQL如下：

create table dept(
	id int primary key auto_increment comment '部门ID',
	name varchar(50) not null comment '部门名称'
)comment '部门表';
-- 向部门表中插入5条记录
insert into dept(id,name) values(1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'总经办');

create table emp(
	id int primary key auto_increment comment '员工ID',
	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 '员工表';

-- 向员工表插入6条数据
insert into emp (id, name, age, job,salary, entrydate, managerid, dept_id)
values
(1, '张三', 66, '总裁',20000, '2000-01-01', null,5),
(2, '李四', 20,'项目经理',12500, '2005-12-05', 1,1),
(3, '王五', 33, '开发', 8400,'2000-11-11', 2,1),
(4, '赵六', 46, '开发',11000, '2002-02-05', 2,1),
(5, '陈七', 55, '开发',10500, '2004-09-07', 3,1),
(6, '小昭', 19, '摸鱼师',6600, '2004-10-12', 2,1);

没添加外键之前，两张表的数据都是独立的；添加外键之后两张表根据某个字段就建立了对应关系。

为emp表的dept_id字段添加外键约束，关联dept表的主键id。

-- constraint  约束
-- fk_emp_dept_id 外键名称
-- foregin key 外键
-- references  参照哪张父表
alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id); 

注：添加外键约束之后，我们再删除dept表(父表)id为1的记录时，会报错（不能删除或更新父表记录），因为存在外键约束，默认行为是RESTRICT。

删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

示例： 删除emp表的外键fk_emp_dept_id。

alter table emp drop foreign key fk_emp_dept_id;

删除/更新行为

添加了外键之后，再删除父表数据时产生的约束行为，我们就称为 ”删除/更新行为“。

具体的 删除/更新行为 有以下几种：

行为	说明
NO ACTION	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有 则不允许删除/更新。 (与 RESTRICT 一致) 默认行为
RESTRICT	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有 则不允许删除/更新。 (与 NO ACTION 一致) 默认行为
CASCADE	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。（级联操作）
SET NULL	当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（这就要求该外键允许取null）
SET DEFAULT	父表有变更时，子表将外键列设置成一个默认的值 (Innodb不支持)

语法格式如下：

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES
主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;

-- 在添加外键的基础上设置了更新和删除的行为
-- ON UPDATE CASCADE ON DELETE CASCADE;

操作如下：

由于NO ACTION和RESTRICT都是默认行为，当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有 则不允许删除/更新。

所以接下来演示下 CASCADE、SET NULL。

1）CASCADE (级联操作)

alter table add constraint fk_emp_dept_id foreign key(dept_id) references emp(id) on update cascade on delete cascade;

A. 修改父表(dept)id为1的记录，将id修改为6。 (级联更新)

update dept set id=6 where id=1; 

当我们修改父表的id时，子表中对应的dept_id值也会修改，这就是cascade级联的效果。(在一般的业务系统中，不会修改一张表的主键值)

B. 删除父表id为6的记录。（级联删除）

delete from dept where id=6;

我们发现，当父表的数据删除成功后，子表中关联的记录也会被级联删除掉。

2) SET NULL

在进行测试之前，我们先需要删除上面建立的外键 fk_emp_dept_id。然后再通过数据脚本，将 emp、dept表的数据恢复了。

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
dept(id) on update set null on delete set null ;

接下来，我们删除id为1的数据，看看会发生什么样的现象。

我们发现父表的记录是可以正常的删除的，父表的数据删除之后，再打开子表 emp，我们发现子表emp 的dept_id字段，原来dept_id为1的数据，现在都被置为NULL了。

这就是SET NULL这种删除/更新行为的效果。

六、数据库设计

---

软件的研发步骤

数据库设计的概念

• 根据业务系统的具体需求，结合我们所选的DBMS，为这个业务构造出最优的数据存储模型；
• 然后析系统需要有哪些表，表中有哪些字段，表与表之间关系；

数据库设计的步骤

1、需求分析（数据是什么，数据具有哪些属性，数据与属性的特点是什么）
2、逻辑分析（通过ER图对数据库进行逻辑建模）
3、物理设计（根据数据库自身的特点把逻辑设计转换成物理设计）
4、维护设计（对新的需求进行建表、表优化）

表关系

在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系。

表关系有：一对多（多对一）、多对多、一对一。

• 一对一的实现方式：用于表拆分，将实体常用的字段放一张表，不经常用的字段放另一张表，然后在任意一方建立外键，关联对方主键，并设置外键唯一（unique）。（例如 用户和用户详情）

• 一对多的实现方式：在多的一个方建立外键(foreign key)，指向一的那方主键。 （例如 部门和员工）

• 多对多的实现方式：建立第三张中间表， 中间表至少包含 “两个外键”，分别 “关联双方主键”； （例如 商品和订单）

示例：多对多关系

-- 商品表
CREATE TABLE `goods`(
	`id` int PRIMARY KEY AUTO_INCREMENT COMMENT '编号',
	`title` VARCHAR(100) COMMENT '标题',
	`price` DOUBLE(10,2) COMMENT '价格'
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 订单表
CREATE TABLE `order`(
	`id` int PRIMARY KEY AUTO_INCREMENT COMMENT '编号',
	`payment` DOUBLE(10,2) COMMENT '付款金额',
	`payment_type` TINYINT COMMENT '付款类型',
  `status` TINYINT COMMENT '状态'
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 订单商品中间表
CREATE TABLE `order_goods`(
	`id` int PRIMARY KEY AUTO_INCREMENT COMMENT '编号',
	`order_id` int COMMENT '外键order_id',
	`goods_id` int COMMENT '外键goods_id',
	`count` int COMMENT '数量'
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 添加外键
ALTER TABLE `order_goods` ADD CONSTRAINT `fk_order_id` FOREIGN KEY(`order_id`) REFERENCES `order`(`id`);
ALTER TABLE `order_goods` ADD CONSTRAINT `fk_goods_id` FOREIGN KEY(`goods_id`) REFERENCES `goods`(`id`);

七、多表查询

---

• 多表查询：就是从多张表中查询数据。

导入SQL脚本：

-- 创建dept表，并插入数据
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, '人事部');

-- 创建emp表，并插入数据
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 (id, name, age, job,salary, entrydate, managerid, dept_id) 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：传统单表查询

select * from emp;

示例2：简单的多表查询

select * from emp,dept;

可以发现多表查询的这些数据并不是我们想要的结果，这种现象叫做笛卡尔积。

笛卡尔积：

• 有两个集合A，B ；取这两个集合的所有组成情况。（两张表数据的成绩）
• 要完成多表查询，需要消除无用的数据。

示例：多表查询，消除无效的笛卡尔

select * from emp,dept where emp.dept_id=dept.id;

多表查询分类：

1. 连接查询

内连接

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

1、隐式内连接

SELECT 字段列表 FROM 表1,表2 WHERE 条件... ;

2、显示内连接

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

示例1： 查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)

-- 表结构:emp、dept
-- 连接条件: emp.dept_id=dept.id
select emp.name,dept.name from emp,dept where emp.dept_id=dept.id;

-- 为每一张表起别名,简化SQL编写
select e.name,d.namefrom emp e,dept d where e.dept_id=d.id;

示例2：查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现)

select emp.name,dept.name from emp inner join dept on emp.dept_id=dept.id;

-- 为每一张表起别名,简化SQL编写 
select e.name,d.name from emp e inner join dept d on e.dept_id=d.id;

表的别名有两种写法：

• tablea as 别名1 , tableb as 别名2 ;

• tablea 别名1 , tableb 别名2 ;

注意：一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。

外连接

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

1、左外连接

左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。

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

2、右外连接

右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件... ;

示例1：查询emp表的所有数据, 和对应的部门信息（左外连接）

select * from emp e left outer join dept d on e.dept_id=d.id;

-- 简化写法
select * from emp e left join dept d on e.dept_id=d.id;

示例2：查询dept表的所有数据, 和对应的员工信息(右外连接)

select * from emp e right outer join dept d on e.dept_id=d.id;

-- 简化写法
select * from emp e right join dept d on e.dept_id=d.id;

注意： 左外连接和右外连接是可以相互替换的，只需要调整在连接查询SQL中，表结构的先后顺序 就可以了。而我们在日常开发使用时，更偏向于左外连接。

自连接

自连接查询

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

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;

而对于自连接查询，可以是内连接查询，也可以是外连接查询。

示例1：查询员工 及其 所属领导的名字（内连接）

select a.name '员工',b.name '领导' from emp a,emp b where a.managerid=b.id;

示例2：查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来(左外连接)

select a.name '员工',b.name '领导' from emp a left join emp b on a.managerid=b.id;

注意：在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底是哪一张表的字段。

联合查询 union、union all

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

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

• 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
• union all 会将全部的数据直接合并在一起（不去重）。
• union 会对合并之后的数据去重。

示例：将薪资低于5000的员工和年龄大于50岁的员工全部查询出来

对于当前这个需求，我们可以直接使用多条件查询，使用逻辑运算符 or 连接即可。当然我们也可以通过union/union all来联合查询。

select * from emp where salary<5000
union 
select * from emp where age>50;

union all查询出来的结果，仅仅进行简单的合并，并未去重。

union 联合查询，会对查询出来的结果进行去重处理。

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

2. 子查询

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

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

-- 外部 SELECT * FROM t1 WHERE column1 = xxx 
-- 内部 SELECT column1 FROM t2

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

子查询的分类

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

• 标量子查询（子查询结果为单个值） - - - 单行单列
• 列子查询(子查询结果为一列) - - - 多行单列
• 行子查询(子查询结果为一行) - - - 单行多列
• 表子查询(子查询结果为多行多列) - - - 多行多列（作为虚拟表）

根据子查询位置，分为： WHERE之后、 FROM之后、SELECT之后。

标量子查询（ 单行单列 ）

• 子查询返回的结果是单个值（可以是数字、字符串、日期等等），这种子查询称为标量子查询。 （单行单列）

• 常用的操作符：= 等于、<>不等于 、 >大于、 >=大于等于、 <小于、 <= 小于等于。

示例1：查询“销售部”所有员工信息

-- 分析:先查询出销售部门的id,然后根据销售部门的id查询员工信息
select * from emp where dept_id = (select id from dept where name='销售部');

示例2：查询在“方东白”入职之后的员工信息

select * from emp where entrydate > (select entrydate from emp where name='方东白');

列子查询 （ 多行单列 ）

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

• 常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL。

操作符	描述
IN	在指定的集合范围之内，多选一
NOT IN	不在指定的集合范围之内
ANY	子查询返回列表中，有任意一个满足即可
SOME	与ANY等同，使用SOME的地方都可以使用ANY
ALL	子查询返回列表的所有值都必须满足

示例1：查询 “销售部” 和 “市场部” 的所有员工信息

-- 1、查询 "销售部" 和 "市场部" 的ID
-- 2、根据 "销售部" 和 "市场部" 的ID 查询员工信息

select * from emp where dept_id in (select id from dept where name='销售部' or name='市场部');

示例2：查询比 ”财务部“ 所有人工资都高的员工信息

-- 1、查询财务部的员工薪资
-- 2、查询比 ”财务部“ 所有人工资都高的员工信息

select salary from emp where salary > 
all(select salary from emp where dept_id = (select id from dept where name='财务部'));

行子查询（单行多列 ）

• 子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。 (单行多列)
• 常用的操作符：= 、<> 、IN 、NOT IN。

示例：查询与 “张无忌” 的薪资及直属领导相同的员工信息 ;

-- 1、查询与 "张无忌" 的薪资及直属领导的id
select salary,managerid from emp where name = '张无忌';

-- 2、查询与 "张无忌" 的薪资及直属领导相同的员工信息
select * from emp where salary=12500 or managerid=1; 
select * from emp where (salary,managerid)=(12500,1);

select * from emp where (salary,managerid)=(select salary,managerid from emp where name = '张无忌');

表子查询（多行多列）

• 子查询返回的结果是多行多列，这种子查询称为表子查询。 （可以作为临时表/虚拟表）

• 常用的操作符：IN。

示例1： 查询与 “鹿杖客” , “宋远桥” 的职位和薪资相同的员工信息

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

示例2： 查询入职日期是 “2006-01-01” 之后的员工信息 , 及其部门信息

--   1、查询入职日期是 "2006-01-01" 之后的员工信
--   2、(查询的结果做虚拟表)然后与部门表进行左外连接
select e.*,d.* from (select * from emp where entrydate >'2006-01-01') e left join dept d on e.dept_id=d.id;

3. 多表查询案例

数据环境准备:

创建3张表：emp员工表、dept部门表、salgrade薪资登记表。

-- 创建dept表，并插入数据
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, '人事部');


-- 创建emp表，并插入数据
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 (id, name, age, job,salary, entrydate, managerid, dept_id) 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);

-- 创建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：查询员工的姓名、年龄、职位、部门信息 （隐式内连接）

-- 表：emp、dept
-- 连接条件:emp.dept_id=dept.id
select e.name,e.age,e.job,d.name from emp e,dept d where e.dept_id=d.id;

示例2： 查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）

-- 表：emp、dept
-- select 字段列表 from 表1 join 表二 on 条件;

select e.name,e.age,e.job,d.name from emp e join dept d on e.dept_id=d.id where age<30;

示例3：查询拥有员工的部门ID、部门名称（右外连接）

select distinct d.* from emp e , dept d where e.dept_id = d.id;

示例4： 查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来(外连接)

select e.*,d.name from emp e left join dept d on e.dept_id=d.id where age > 40;

示例5： 查询所有员工的工资等级

-- 表：emp、salgrade
-- 连接条件:emp.salary >= salgrade.losal and emp.salary <= salgrade.hisal
-- 方式一
select e.* , s.grade , s.losal, s.hisal from emp e , salgrade s where e.salary >= s.losal and e.salary <= s.hisal;
-- 方式二
select e.* , s.grade , s.losal, s.hisal from emp e , salgrade s where e.salary between s.losal and s.hisal;

示例6：查询 “研发部” 所有员工的信息及 工资等级

-- 表: emp , salgrade , dept
--连接条件 : emp.salary between salgrade.losal and salgrade.hisal ,emp.dept_id = dept.id
--查询条件 : dept.name = '研发部'

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： 查询 “研发部” 员工的平均工资

-- 表: emp , dept 
-- 连接条件 : emp.dept_id = dept.id
select avg(e.salary) from emp e, dept d where e.dept_id = d.id and d.name = '研发部';

示例8： 查询工资比 “灭绝” 高的员工信息。

1、查询 "灭绝" 的工资
select salary from emp where name = '灭绝';
2、查询比他工资高的员工信息
select * from emp where salary > ( select salary from emp where name = '灭绝' );

示例9：查询比平均薪资高的员工信息

-- 1、查询比平均薪资
-- 2、查询比平均薪资高的员工信息
select * from emp where salary > (select avg(salary) from emp);

示例10：查询低于本部门平均工资的员工信息

-- 1、查询指定部门平均工资
select avg(e1.salary) from emp e1 where e1.dept_id=1;
select avg(e1.salary) from emp e1 where e1.dept_id=2;

-- 2、查询低于本部门平均工资的员工信息
select * from emp e2 where e2.salary < (select avg(e1.salary) from emp e1 where e1.dept_id=e2.dept_id);

示例11：查询所有的部门信息, 并统计部门的员工人数

select d.*,(select count(*) from emp e where e.dept_id=d.id) '人数' from dept d;

八、事务

---

8.1 事务简述

数据库事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作的命令。

事务会把 所有的操作 作为一个整体一起向系统提交或撤销操作请求，即这些操作要么一起成功，要么一起失败。

例如: 张三给李四转账1000块钱，张三银行账户的钱减少1000，而李四银行账户的钱要增加 1000。 这一组操作就必须在一个事务的范围内，要么都成功，要么都失败。

我们只需要在业务逻辑执行之前开启事务，执行完毕后提交事务。如果执行过程中报错，则回滚事务，把数据恢复到事务开始之前的状态。

MySQL默认是自动提交事务的，也就是说，当执行完一条DML的SQL语句（insert/update/delete）时，MySQL都会帮我们隐式的提交(commit)事务。（而Oracle中的事务不自动提交）

8.2 手动操作事务

---

1、MySQL中开启事务命令如下：

begin;
或者  
start transaction;

2、MySQL中提交事务命令如下：

commit;  #操作成功就修改数据库

3、MySQL中回滚事务命令如下：

rollback;  #操作失败就回滚事务

8.3 事务的四大特征（ACID）

---

• 原子性(Atomicity）: 事务是不可分割的最小操作单位，要么一起成功要么一起失败。

• 一致性(Consistency) : 事务操作的前后，必须使所有的数据都保持一致的状态。

• 隔离性(Isolation）: 事务在执行的过程，尽量做到隔离，互不打扰。

• 持久性(Durability) : 事务一旦执行完毕，数据就持久化到数据库了。

1、MySQL事务默认是自动提交的，查看事务的默认提交方式，命令如下：

select @@autocommit;

2、修改事务提交方式，命令如下：

set @@autocommit = 0;   #设置手动提交事务方式