MySQL基础
1、数据库相关概念
数据库（DataBase，DB）：存储数据的仓库，数据是有组织地进行存储。
数据库管理系统（DataBase Management System，DBMS）：管理数据库的大型软件。
常见的关系型数据库管理系统：
| 常见的关系型数据库管理系统 | 描述 |
|---|---|
| Oracle | 收费的大型数据库，Oracle公司的产品 |
| MySQL | 开源免费的中小型数据库，后来Sun公司收购了MySQL，而Sun公司又被Oracle收购 |
| SQL Server | MicroSoft公司收费的中型的数据库，C#、.net等语言常使用 |
| PostgreSQL | 开源免费的中小型数据库 |
| DB2 | IBM公司的大型收费数据库产品 |
| SQLite | 嵌入式的微型数据库，如：作为Android内置数据库 |
| MariaDB | 开源免费的中小型数据库 |
SQL（Structured Query Language，结构化查询语言）：操作关系型数据库的编程语言，定义操作所有关系型数据库的统一标准。
- 数据库：文件夹；
- 表：文件；
- 数据：文件中存储的数据。
2、MySQL数据库
2.1、MySQL的下载和安装
下载：点击链接 https://downloads.mysql.com/archives/community/
2.2、MySQL的配置
1、添加环境变量
环境变量里面有很多选项，这里我们只用到Path这个参数。为什么在初始化的开始要添加环境变量呢？在CMD中输入一个可执行程序的名字，Windows会现在环境变量中的Path所指的路径中寻找以便，如果找到了就直接执行，没找到就在当前工作目录找，如果还找不到，就报错。我们添加环境变量的目的就是能够在任意情况下直接调用MySQL中的相关程序而不用总是修改工作目录，大大简化了操作。
右键此电脑 -> 属性 -> 高级系统设置 -> 高级 -> 环境变量 -> 在系统变量下面选择新建 -> 变量名为 MYSQL_HOME，变量值为 D:\mysql-5.7.24-winx64（即MySQL安装路径）-> 在系统变量中找到Path，选中后点击编辑 -> 新建 ->输入 %MYSQL_HOME%\bin -> 依次点击确定完成环境变量的配置。
2、新建配置文件
新建一个文本文件，内容如下：
[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上面的代码意思是配置数据库的默认编码集为utf-8和默认存储引擎为INNODB。
把上面的文本文件另存为，在保存类型里选所有文件(*.*)，文件名叫my.ini，存放的路径为MySQL的根目录的bin文件夹下。
3、初始化MySQL
在CMD中输入 mysqld --initialize-insecure ，回车，稍微等待一会，如果没有出现报错信息，则证明data目录初始化没问题，此时再查看MySQL目录下已经有data目录生成。
如果出现错误：(Errcode: 13 - Permission denied) ，是由于权限不足导致的，去C:\Windows\System32 下以管理员方式运行cmd.exe 。
4、注册MySQL服务
在CMD中输入mysqld -install ，回车。
安装完MySQL服务后，你的计算机可以称为MySQL服务器了，可以提供数据存储管理的服务。
5、启动MySQL服务
在CMD中输入 net start mysql ，启动MySQL服务。
停止mysql服务：net stop mysql
或者Win+R，输入services.msc，手动开启MySQL服务。
6、修改默认账户密码
在CMD中输入 mysqladmin -u root password 1234 ，这里的1234就是指默认管理员（即root账户）的密码，可自行修改成你喜欢的。
至此，MySQL 5.7 解压版安装完毕！
2.3、登录MySQL
在CMD中输入 mysql -uroot -p1234 ，回车，出现下图且左下角为mysql>，则说明登录成功。
退出mysql：输入 exit 或 quit
登录参数：mysql -u用户名 -p密码 -h要连接的mysql服务器的ip地址(默认127.0.0.1) -P端口号（默认3306）。
2.4、卸载MySQL
如果你想卸载MySQL，也很简单。打开CMD：
1、输入net stop mysql ，回车；
2、再输入 mysqld -remove mysql ，回车；
3、最后删除MySQL目录及相关的环境变量。
2.5、MySQL数据模型
关系型数据库：关系型数据库是建立在关系模型基础上的数据库，简单说，关系型数据库是由多张能互相连接的二维表组成的数据库。
优点：
- 都是使用表结构，格式一致，易于维护；
- 使用通用的SQL语言操作，使用方便，可用于复杂查询；
- 数据存储在磁盘中，安全。
3、SQL
什么是SQL？其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方，称为“方言”。
SQL通用语法：
- SQL语句可以单行或多行书写，以分号结尾。
- 可通过空格和缩进来增强语句的可读性。
- MySQL数据库的SQL语句的SQL语句不区分大小写，关键字建议使用大写。
- 注释：① 单行注释： -- 注释内容 或 # 注释内容；② 多行注释： /* 注释*/
SQL的分类：
- DDL（Data Definition Language）数据定义语言：用来定义数据库对象：数据库，表，列等。关键字：create，drop，alter等；
- DML（Data Manipulation Language）数据操作语言：用来对数据库中表的数据进行增删改。关键字：insert，delete，update等；
- DQL（Data Query Language）数据查询语言：用来查询数据库中表的记录（数据）。关键字：select，where等；
- DCL（Data Control Language）数据控制语言（了解）：用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT，REVOKE等。
客户端图形化工具：SQLYog
姓名（Name）：cr173
序列号（Code）：8d8120df-a5c3-4989-8f47-5afc79c56e7c
姓名（Name）：cr173
序列号（Code）：59adfdfe-bcb0-4762-8267-d7fccf16beda
姓名（Name）：cr173
序列号（Code）：ec38d297-0543-4679-b098-4baadf91f983
备注：最好关闭软件的自动更新功能3.1、DDL（Data Definition Language，数据定义语言）
3.1.1、操作数据库：
1、C（Create）：创建数据库；
- 创建数据库：create database 数据库名称;
- 创建数据库，判断是否存在，如果不存在就创建：create database if not exists 数据库名称;
- 创建数据库，判断是否存在，并指定字符集以及排序规则：create database [if not exist] 数据库名称 [character set 字符集名] [collate 排序规则];
2、R（Retrieve）：查询数据库；
- 查询所有数据库的名称：show databases;
- 查看某个数据库的字符集：查询某个数据库的创建语句：show create database 数据库名称;
3、U（Update）：修改数据库；
- 修改数据库的字符集：alter database 数据库名称 character 字符集名;
4、D（Delete）：删除数据库；
- 删除数据库：drop database 数据库名称;
- 判断数据库存在，存在再删除：drop database if exists 数据库名;
5、使用数据库。
- 查询当前正在使用的数据库名称：select database();
- 使用数据库：use 数据库名称;
3.1.2、操作表
1、C（Create）：创建表；
- 创建表：
创建表的语法： create table 表名( 列名1 数据类型1, 列名2 数据类型2, ... 列名n 数据类型n ); 范例： create table student( id int, name varchar(32), score double(4,1), birthday date, insert_time timestamp ); 注意：最后一列不需要加逗号 数据类型： int：整数类型 如 age int, double：小数类型 如 score double(5,2) #小数最多有5位，小数点后有2位 date：日期 只包含年月日，yyyy-MM-dd datetime：日期 包含年月日时分秒，yyyy-MM-dd HH:mm:ss timestamp：时间戳 包含年月日时分秒，yyyy-MM-dd HH:mm:ss。如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值 varchar：字符串 如：name varchar(20) #姓名最大20个字符，zhangsan有8个字符，张三有2个字符 - 复制表：create table 表名 like 被复制的表名;
2、R（Retrieve）：查询表；
- 查询某个数据库中所有的表名称：show tables;
- 查询表结构：desc 表名;
3、U（Update）：修改表；
- 修改表名：alter table 表名 rename to 新的表名;
- 修改表的字符集：alter table 表名 character set 字符集名;
- 添加一列：alter table 表名 add 列名 数据类型(长度) [comment 注释] [约束];
- 修改列名称、类型：alter table 表名 change 列名 新列名 新数据类型(长度) [comment 注释] [约束];
- 修改列类型：alter table 表名 modify 列名 新数据类型(长度);
- 删除列：alter table 表名 drop 列名;
4、D（Delete）：删除表；
- 删除某个表：drop table [if exists] 表名;
- 如果删除的表存在，则删除：drop table if exists 表名;
- 删除表，然后再创建一个一模一样的空表：truncate table 表名;
3.1.3、数据类型
MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型。
| 分类 | 类型 | 大小 | 有符号（SIGNED）范围 | 无符号（UNSIGNED）范围 | 描述 |
| 数值类型 | TINYINT | 1 byte | (-128, 127) | (0, 255) | 小整数值 |
| SMALLINT | 2 bytes | (-32768, 23767) | (0, 65535) | 大整数值 | |
| MEDIUMINT | 3 bytes | (-8388608, 8388607) | (0, 1677215) | 大整数值 | |
| INT或INTEGER | 4 bytes | (-2147483648, 2147483647) | (0, 4294967295) | 大整数值 | |
| BIGINT | 8 bytes | (-2^63, 2^63-1) | (0, 2^64-1) | 极大整数值 | |
| FLOAT | 4 bytes | (-3.402823466 E+38, 3.402823466351 E+38) | 0 和 (1.175494351 E-38, 3.402823466 E+38) | 单精度浮点数值 | |
| DOUBLE | 8 bytes | (-1.7976931348623157 E+308, 1.7976931348623157 E+308) | 0 和 (2.2250738585072014 E-308, 1.7976931348623157 E+308) | 双精度浮点数值 | |
| DECIMAL | 依赖于M（精度）和D（标度）的值 | 依赖于M（精度）和D（标度）的值 | 小数值（精确定点数） | ||
| 分类 | 类型 | 大小 | 描述 |
| 字符串类型 | CHAR | 0 - 255 bytes | 定长字符串 |
| VARCHAR | 0 - 65535 bytes | 变长字符串 | |
| TINYBLOB | 0 - 255 bytes | 不超过255个字符的二进制数据 | |
| TINYTEXT | 0 - 255 bytes | 短文本字符串 | |
| BLOB | 0 - 65535 bytes | 二进制形式的长文本数据 | |
| TEXT | 0 - 65535 bytes | 长文本数据 | |
| MEDIUMBLOB | 0 - 16777215 bytes | 二进制形式的中等长度文本数据 | |
| MEDIUMTEXT | 0 - 16777215 bytes | 中等长度文本数据 | |
| LONGBLOB | 0 - 4294967295 bytes | 二进制形式的极大文本数据 | |
| LONGTEXT | 0 - 4294967295 bytes | 极大文本数据 |
| 分类 | 类型 | 大小 | 范围 | 格式 | 描述 |
| 日期类型 | DATE | 3 bytes | 1000-01-01 至 9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 bytes | -838:59:59 至 838:59:59 | HH:MM:SS | 时间值 | |
| YEAR | 1 bytes | 1901 至 2155 | YYYY | 年份 | |
| DATETIME | 8 bytes | 1000-01-01 00:00:00 至 9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 | |
| TIMESTAMP | 4 bytes | 1970-01-01 00:00:01 至 2038-01-19 03:14:07 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值、，时间戳 |
设计一张员工信息表，要求如下：
- 编号（纯数字）；
- 员工工号（字符串类型，长度不超过10位）；
- 员工姓名（字符串类型，长度不超过10位）；
- 性别（男/女，存储一个汉字）；
- 年龄（正常人年龄，不可能存储负数）；
- 身份证号（二代身份证号均为18位，含有x这样的字符）；
- 入职时间（取值年月日即可）。
create table emp (
id int commit '编号',
workno varchar(10) comment '工号',
name varchar(10) comment '姓名',
gender char(1) comment '性别',
age tinyint unsigned comment '年龄',
idcard char(18) comment '身份证号',
entrydate date comment '入职时间'
) comment '员工表';3.2、DML（Data Manipulation Language，数据操作语言）
DML用来增删改表中数据。
1、添加数据
语法：insert into 表名(列名1,列名2,...,列名n) values(值1,值2,...,值n);
注意：① 列名和值要一一对应；② 如果表名后不定义列名，则默认给所有列添加值（insert into 表名 values(值1,值2,...,值n);）；③ 除了数字类型，其它类型需要使用引号引起来（单引号、双引号都可以）。
2、删除数据
语法：delete from 表名 [where 条件];
注意：① 如果不加条件，则删除表中所有记录；② 如果要删除所有记录：delete from 表名（不推荐使用，有多少条记录就会执行多少次删除操作）; truncate table 表名;（推荐使用，先删除表，再创建一张一样的表，效率更高）
3、修改数据
语法：update 表名 set 列名1=值1,列名2=值2,...,列名n=值n [where 条件];
注意：如果不加任何条件，则会将表中所有记录全部修改。
3.3、DQL（Data Query Language，数据查询语言）
3.3.1、查询的语法：
- 语句的编写顺序（括号内为执行顺序）
（4）select
字段列表
（1）from
表名列表
（2）where
条件列表
（3）group by
分组字段
（ ）having
分组之后的条件
（5）order by
排序
（6）limit
分页限定;3.3.2、基础查询
1、多个字段的查询：语法：select 字段名1,字段名2,...,字段名n from 表名;
注意：如果查询所有字段，则可以使用 * 来替代字段列表。
2、去除重复：语法：select distict 字段名1,字段名2,...,字段名n from 表名;
3、计算列：语法：select 字段名1 as 别名1,字段名2 as 别名2,字段名3,字段名1+字段名3 as 结果别名 from 表名;
注意：一般可以使用四则运算计算一些列的值（一般只会进行数值型的计算）；② ifnull(表达式1,表达式2)：表达式1：哪个字段需要判断是否为null；表达式2：如果该字段为null后的替换值。
4、起别名：语法：select 字段名1 as 别名 from 表名; （as也可以省略）
代码演示：
SELECT * FROM student; #查询所有列表
SELECT NAME,age FROM student; #查询姓名和年龄
SELECT address FROM student; #查询地址
SELECT DISTINCT address FROM student; #去除重复的结果集
SELECT DISTINCT NAME,address FROM student;
SELECT NAME,math,english,math+english FROM student; #计算math和english的分数之和
#如果有null参与的运算，计算结果都为null
SELECT NAME,math,english,math+IFNULL(english,0) FROM student;
#起别名
SELECT NAME,math 数学,english 英语,math+IFNULL(english,0) AS 总分 FROM student;3.3.3、条件查询
1、语法：select * from 表名 where 条件；
2、运算符
> 、 < 、 <= 、 >=、 =、 <>
BETWEEN ... AND ... IN(集合)
LIKE模糊查询：占位符可以是：
_：单个任意字符
%：多个任意字符
IS NULL and 或 && or 或 || not 或 !
代码演示：
#查询年龄大于20岁的人
SELECT * FROM student WHERE age > 20;
#查询年龄等于20岁的人
SELECT * FROM student WHERE age = 20;
#查询年龄不等于20岁的人
SELECT * FROM student WHERE age <> 20;
#查询年龄大于等于20，小于等于30的人
SELECT * FROM student WHERE age >= 20 AND age <= 30;
SELECT * FROM student WHERE age BETWEEN 20 AND 30; #包含20和30
#查询年龄等于22，18，25岁的人
SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25;
SELECT * FROM student WHERE age IN (22,18,25);
#查询英语成绩为null的人
SELECT * FROM student WHERE english = NULL; #不对，因为null值不能使用= 和 !=判断
SELECT * FROM student WHERE english IS NULL;
#查询英语成绩不为null的人
SELECT * FROM student WHERE english IS NOT NULL;
-- 模糊查询 ----------------------------------
#查询姓马的有哪些 like
SELECT * FROM student WHERE NAME LIKE '马%';
#查询姓名中第二个字是化的人
SELECT * FROM student WHERE NAME LIKE '_化%';
#查询姓名是三个字的人
SELECT * FROM student WHERE NAME LIKE '___';
#查询姓名中包含德的人
SELECT * FROM student WHERE NAME LIKE '%德%';3.3.4、排序查询
1、语法：order by 排序字段1 排序方式1, 排序字段2 排序方式2,...;
2、排序方式：① ASC：升序（默认）；② DESC：降序。
3、注意：如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。
代码演示：
SELECT * FROM student; #查询所有列表
-- 排序 ----------------------------------------
#按照数学成绩降序排列
SELECT * FROM student ORDER BY math DESC;
#按照数学成绩升序排列
SELECT * FROM student ORDER BY math ASC;
#按照数学成绩排名，如果数学成绩一样，则按照英语成绩排名
SELECT * FROM student ORDER BY math ASC, english ASC;3.3.5、聚合函数
聚合函数：将一列数据作为一个整体，进行纵向的计算。
常见的聚合函数：
- count：计算个数。一般选择非空的列（主键）。使用count(*)表示只要这行数据所有列都不为NULL就不会排除。
- max：计算最大值
- min：计算最小值
- sum：计算总和
- avg：计算平均值
注意：聚合函数的计算，会排除null值。解决方案：① 选择不包含非空列进行计算；② 利用IFNULL()函数。
代码演示：
SELECT * FROM student; #查询所有列表
-- 聚合函数 ----------------------------------------
# 查询学生个数
SELECT COUNT(NAME) FROM student;
SELECT COUNT(english) FROM student; #排除了none的值
SELECT COUNT(IFNULL(IFNULL(english,0))) FROM student; #解决方案
# 查询数学最高分
SELECT MAX(math) FROM student;
# 查询数学最低分
SELECT MIN(math) FROM student;
# 查询数学平均分
SELECT AVG(math) FROM student;
# 查询英语成绩总和
SELECT SUM(english) FROM student;3.3.6、分组查询
1、语法：group by 分组字段 [having 分组后过滤条件];
2、注意：
- 执行顺序：where > 聚合函数 > having 。
- 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
- where和having 的区别：where在分组之前进行过滤，如果不满足条件，则不参与分组；having在分组之后进行过滤，如果不满足结果则不会被查询出来。where不能对聚合函数进行判断，而having可以。
代码演示：
SELECT * FROM student; #查询所有列表
-- 分组查询 ----------------------------------------
# 按照性别分组，分别查询男、女同学平均分
SELECT sex,AVG(math) FROM student GROUP BY sex;
# 按照性别分组，分别查询男、女同学平均分、人数
SELECT sex,AVG(math),COUNT(id) FROM student GROUP BY sex;
# 按照性别分组，分别查询男、女同学平均分、人数
# 要求分数低于70分的人不参与分组
SELECT sex,AVG(math),COUNT(id) FROM student WHERE math>70 GROUP BY sex;
# 按照性别分组，分别查询男、女同学平均分、人数
# 要求分数低于70分的人不参与分组
# 要求分组之后，人数要大于2个人
SELECT sex,AVG(math),COUNT(id) FROM student WHERE math>70 GROUP BY sex HAVING COUNT(id) > 2;
SELECT sex,AVG(math),COUNT(id) AS 人数 FROM student WHERE math>70 GROUP BY sex HAVING 人数 > 2;3.3.7、分页查询
语法：limit 起始索引, 每页查询的记录数;
注意：
- 起始索引从0开始，起始索引=（查询页码 -1）* 每页的记录数。
- 分页操作是一个“方言”，不同的数据库分页操作不一样。上面的分页操作limit是一个MySQL“方言”。
- 如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10。
代码演示：
SELECT * FROM student; #查询所有列表
-- 分页查询 ----------------------------------------
# 每页显示三条记录
SELECT * FROM student LIMIT 0, 3; #第一页
SELECT * FROM student LIMIT 3, 3; #第二页
# 公式：开始的索引=（当前的页码-1）*每页显示的条数
SELECT * FROM student LIMIT (i-1)*3, 3; # 第i页3.4、DCL（Data Control Language，数据控制语言）
DCL用来管理数据库用户、控制数据库的访问权限。
3.4.1、管理用户
-- 1、查询用户
# 切换到mysql数据库
USE mysql;
# 查询user表
SELECT * FROM USER;
-- 2、创建用户
# 通配符： % 标识可以在任意主机使用用户登录数据库
CREATE USER '用户名（如 zhangsan）'@'主机名(如 localhost，或者填一个 %)'
IDENTIFIED BY '密码';
-- 3、修改用户密码
# 修改 zhangsan 用户的密码为 abc
UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
# 另一种方式
SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
-- 4、删除用户
DROP USER '用户名'@'主机名';MySQL中忘记了root用户和密码怎么办？解决方式如下：
-- MySQL中忘记了root用户密码怎么办？
#1、打开CMD，并在CMD中执行：
net STOP mysql #停止mysql服务
mysqld --skip-grand-TABLES #使用无验证方式启动mysql服务 ：
#2、打开新的CMD，并在新的CMD中执行：
mysql #无需账号密码，登录mysql
mysql> USE mysql; #进入mysql数据库
mysql> UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名'; #修改密码
#3、关闭原CMD和新的CMD
#4、打开任务管理器，找到mysqld.exe服务，结束该进程
#5、以管理员方式打开CMD，执行：
net START mysql
mysql -uroot -p新密码
#6、登陆成功3.4.2、权限管理
MySQL中定义了很多种权限，但是常用的就以下几种：
| 权限 | 说明 |
| ALL, ALL PRIVILEGES | 所有权限 |
| SELECT | 查询数据 |
| INSERT | 插入数据 |
| UPDATE | 修改数据 |
| DELETE | 删除数据 |
| ALTER | 修改表 |
| DROP | 删除数据库/表/视图 |
| CREATE | 创建数据库/表 |
-- 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
-- 授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
GRANT ALL ON * TO '用户名'@'主机名'; #给该用户授予使用所有数据库的所有表权限
-- 撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';4、函数
函数：是指一段可以直接被另一段程序调用的程序或代码。
4.1、字符串函数
MySQL中内置了很多字符串函数，常用的几个如下：
| 函数 | 功能 |
| 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, start, len) | 返回从字符串str从start位置起的len个长度的字符串 |
4.2、数值函数
| 函数 | 功能 |
| CEIL(x) | 向上取整 |
| FLOOR(x) | 向下取整 |
| MOD(x,y) | 返回x/y的模 |
| RAND() | 返回0~1内的随机数 |
| ROUND(x,y) | 求参数x的四舍五入的值，保留y位小数 |
4.3、日期函数
常见的日期函数如下：
| 函数 | 功能 |
| CURDATE() | 返回当前日期 |
| CURTIME() | 返回当前时间 |
| NOW() | 返回当前日期和时间 |
| YEAR(date) | 获取指定date的年份。如 select YEAR(now()); |
| MONTH(date) | 获取指定date的月份。如 select MONTH(now()); |
| DAY(date) | 获取指定date的日期。如 select DAY(now()); |
| DATE_ADD(date, INTERVAL expr type) | 返回上一个日期/时间值加上一个时间间隔expr后的时间值，type为时间单位类型。如 select date_add(now(), INTERVAL 70 DAY); |
| DATEDIFF(date1, date2) | 返回起始时间date1和结束时间date2之间的天数（返回date1-date2结果）。如： select datediff('2021-10-01', '2022-12-01') |
4.4、流程控制函数
流程函数也是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率。
| 函数 | 功能 |
| IF(value, t, f) | 如果value为true，则返回t，否则返回f。如 select if(true, 'ok', 'error'); |
| IFNULL(value1, value2) | 如果value1不为空，返回value1，否则返回value2。如 select ifnull('ok', 'error'); |
| CASE [field] WHEN [val1] THEN [res1] ... ELSE [default] END | 如果val1为true，返回res1，...，否则返回default默认值。如 select case address when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end from emp; |
| CASE [expr] WHEN [val1] THEN [res1] ... ELSE [default] END | 如果expr的值等于val1，返回res1， ...，否则返回default默认值。如 select case when math >= 85 then '优秀' when math >= 60 then '及格' else '不及格' end from score; |
5、约束
概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。约束用来保证数据的正确性、有效性和完整性。
5.1、默认约束
default，保存数据时，如果未指定该字段的值，则采用默认值。
5.2、主键约束
primary key，表示该列非空且唯一。注意：主键就是表中记录的唯一标识，一张表只能有一个字段为主键。
-- 创建表时添加主键约束
CREATE TABLE stu(
id INT PRIMARY KEY, #给id添加主键约束
NAME VARCHAR(20) #姓名
);
-- 删除主键约束 ------------------------
ALTER TABLE stu DROP PRIMARY KEY #正确方式
# alter table stu modify id int; #错误方式
-- 创建表后添加主键约束 ----------------
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
SELECT * FROM stu;自动增长：如果某一列是数值类型的，使用 auto_increment 可以来完成值的自动增长。
-- 创建表时添加主键约束
CREATE TABLE stu(
id INT PRIMARY KEY AUTO_INCREMENT, #给id添加主键约束，可自动增长
NAME VARCHAR(20) #姓名
);
-- 删除自动增长 -----------------------
ALTER TABLE stu MODIFY id INT;
-- 创建表后增加自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
INSERT INTO stu VALUES(NULL,'张三'); #自动得到id（增加1）
INSERT INTO stu VALUES(10,'李四'); #手动设置id值
INSERT INTO stu VALUES(NULL,'王五'); #再次使用自动增长则id值为11
SELECT * FROM stu;5.3、非空约束
not null，限制该字段的数据不能为null。
-- 创建表时添加非空约束
CREATE TABLE stu(
id INT,
NAME VARCHAR(20) NOT NULL #name为非空
);
-- 删除name的非空约束 --------------
ALTER TABLE stu MODIFY NAME VARCHAR(20);
-- 创建表完成后，添加非空约束 ------
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
SELECT * FROM stu;5.4、唯一约束
unique，保证该字段的所有数据都是唯一、不重复的。注意： 唯一约束可以有null值，但是只能有一条记录为null。
-- 创建表时添加唯一约束
CREATE TABLE stu(
id INT,
phone_number VARCHAR(20) UNIQUE #手机号唯一（不重复）
);
-- 删除唯一约束
ALTER TABLE stu DROP INDEX phone_number; #正确方式
# alter table stu modify phone_number varchar(20); #错误方式
-- 创建表后添加唯一约束
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
SELECT * FROM stu;5.5、检查约束
check，保证字段值满足某一个条件。如： create table user ( age int check (age > 0 && age <= 120) );
5.6、外键约束
foreign key，让两张表的数据之间建立连接，从而保证数据的一致性、完整性。
5.6.1、外键的添加/修改/删除
-- 1、在创建表时，可以添加外键。
# 语法：
create table 表名(
...
外键列
constraint 外键名称 foreign key 外键列名称 references 主表名称（主表列名称）
);
-- 2、删除外键
alter table 表名 drop foreign key 外键名称;
-- 3、创建表之后，添加外键
alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称（主表列名称）注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。
5.6.2、外键约束的删除/更新行为
| 行为 | 说明 |
| NO ACTION | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。（与RESTRICT一致） |
| RESTRICT | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。（与NO ACTION一致） |
| CASCADE | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。 |
| SET NULL | 当在父表删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（这就要求该外键允许取null）。 |
| SET DEFAULT | 父表有变更时，子表将外键列设置成一个默认的值（Innodb不支持）。 |
-- 1、cascade级联操作
# 1、添加级联操作
语法：alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称)on update cascade on delete cascade;
# 2、分类：
级联更新：on update cascade
级联删除：on delete cascade
-- 2、set null操作
语法：alter table 表明 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称) on update set null on delete set null;
-------------------------------------------------------------------------------
-- 代码演示：
USE DATABASE mydatabase;
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
age INT,
dep_name VARCHAR(30),
dep_location VARCHAR(30)
);
INSERT INTO emp (id,NAME,age,dep_name,dep_location) VALUES
(1,'张三',20,'研发部','广州'),
(2,'李四',21,'研发部','广州'),
(3,'王五',20,'研发部','广州'),
(4,'老王',20,'销售部','深圳'),
(5,'大王',22,'销售部','深圳'),
(6,'小王',18,'销售部','深圳');
DELETE FROM emp;
SELECT * FROM emp;
-- 数据有冗余，解决方案：表的拆分
-- 创建部门表（id,dep_name,dep_location）
-- 一方，主表
CREATE TABLE department(
id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(20),
dep_location VARCHAR(20)
);
-- 创建员工表（id,name,age,dep_id）
-- 多方，从表
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
dep_id INT, #外键对应主表的主键
CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id)
);
-- 添加两个部门
INSERT INTO department VALUES
(NULL,'研发部','广州'),
(NULL,'销售部','深圳');
-- 添加员工
INSERT INTO employee (NAME, age, dep_id) VALUES
('张三',20,1),
('李四',21,1),
('王五',20,1),
('老王',20,2),
('大王',22,2),
('小王',18,2);
-- 查询部门信息
SELECT * FROM department;
-- 查询员工信息
SELECT * FROM employee;
-- 删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;
-- 添加外键，设置级联更新
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk
FOREIGN KEY (dep_id) REFERENCES department(id)
ON UPDATE CASCADE ON DELETE CASCADE;6、多表查询
6.1、多表关系
项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：
- 一对多（多对一）；
- 多对多；
- 一对一。
6.1.1、一对一
如人和身份证，一个人只有一个身份证，一个身份证只能对应一个人。
实现方式：一对一关系的实现，可以在任意一方添加外键（外键唯一）指向另一方的主键。6.1.2、一对多（多对一）
如部门和员工，一个部门有多个员工，一个员工只能对应一个部门。
实现方式：在多的一方建立外键，指向一的一方的主键6.1.3、多对多
如学生和课程，一个学生可以选很多门课程，一个课程也可以被很多学生选择。
实现方式：多对多关系的实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为中间表的外键，分别指向两张表的主键。
联合主键：
create table table1 (
字段名1 类型1,
字段名2 类型2,
primary key (字段名1,字段名2) 字段1和字段2不能同时相同。
);6.2、查询语法
语法： select 列名列表 from 表名列表 where ...笛卡尔积：有两个集合A，B，取这两个集合的所有组成情况。在多表查询时，需要消除无效的笛卡尔积。
6.3、多表查询的分类
6.3.1、内连接查询
① 隐式内连接：使用where条件来消除无用数据。
SELECT * FROM employee,department; # 笛卡尔积
-- 隐式内连接 -- 查询所有员工信息和对应的部门信息
SELECT * FROM employee,department WHERE employee.`dep_id`=department.`id`;
-- 查询员工表的名称，年龄，部门表的名称
SELECT t1.name,t1.age,t2.dep_name FROM employee t1,department t2 WHERE t1.`dep_id`=t2.`id`;② 显式内连接：语法：select 字段列表 from 表名 [inner] join 表名2 on 条件;
-- 显式内连接 #inner可省略
SELECT * FROM employee INNER JOIN department ON employee.`dep_id`=department.`id`;内连接查询注意事项：① 从那些表中查询数据；② 条件是什么；③ 查询哪些字段。
6.3.2、外连接查询
① 左外连接：语法：select 字段列表 from 表1 left [outer] join 表2 on 条件;
左外连接查询的是左表所有数据以及其交集部分。
-- 左外连接
#查询所有员工信息，如果员工有部门，则查询部门的名称，没有部门，则不显示部门名称
SELECT t1.name,t1.age,t2.dep_name FROM employee t1 LEFT JOIN department t2 ON t1.`dep_id`=t2.`id`;② 右外连接：语法：select 字段列表 from 表1 right [outer] join 表2 on 条件;
右外连接查询的是右表所有数据以及其交集部分。
-- 右外连接
#查询所有员工信息，如果员工有部门，则查询部门的名称，没有部门，则不显示部门名称
SELECT t1.name,t1.age,t2.dep_name FROM employee t1 RIGHT JOIN department t2 ON t1.`dep_id`=t2.`id`;6.3.3、自连接查询
自连接：语法：select 字段列表 from 表A 表A别名 join 表B 表B别名 on 条件 ...;
自连接查询可以是内连接查询，也可以是外连接查询。
6.3.4、联合查询
对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。
select 字段列表 from 表A ...
union [all] # 去掉all，会去除重复记录
select 字段列表 from 表B ...对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。union all会将全部的数据直接合并在一起，union会对合并后的数据去重。
6.3.5、子查询
子查询概念：查询中嵌套查询，称嵌套查询为子查询。
1、标量子查询
子查询的结果是单行单列的（数字、字符串、日期等）：子查询可以作为条件，使用运算符去判断。
常用的操作符：=、 <>、 >=、 <、 <= 。
-- 子查询
#查询年龄最大的员工信息
SELECT * FROM employee WHERE age=( SELECT MAX(age) FROM employee );2、列子查询
子查询的结果是多行单列的：子查询可以作为条件，使用运算符in来判断。
常用的操作符：IN、NOT IN、ANY、SOME、ALL 。
| 操作符 | 描述 |
| IN | 在指定的集合范围之内，多选一 |
| NOT IN | 不在指定的集合范围之内 |
| ANY | 子查询返回列表中，有任意一个满足即可 |
| SOME | 与ANY等同，使用SOME的地方都可以使用ANY |
| ALL | 子查询返回列表的所有值都必须满足 |
-- 子查询
#查询研发部和销售部所有员工信息
SELECT * FROM employee e WHERE e.`dep_id` IN ( SELECT d.id FROM department d WHERE dep_name='研发部' OR dep_name='销售部' );3、行子查询
子查询的结果是一行多列的。
常用的操作符：=、<>、IN、NOT IN。
-- 查询张无忌的薪资及其直属领导
select salary, manager_id from emp where name = '张无忌';
-- 查询与张无忌的薪资及直属领导相同的员工信息
select * from emp where (salary, manager_id) = (select salary, manager_id from emp where name = '张无忌');4、表子查询
子查询的结果是多行多列的：子查询可以作为一张虚拟表参与查询。
-- 子查询
#查询员工的年龄为20岁的员工的姓名和部门信息
SELECT t1.`dep_name`,t2.name FROM department t1, ( SELECT * FROM employee WHERE employee.`age`=20 ) t2 WHERE t1.`id`= t2.dep_id;
#普通方式
SELECT d.`dep_name`,e.`name` FROM department d, employee e WHERE d.`id`=e.`dep_id` AND e.`age`=20;7、数据库的设计
7.1、数据库设计的范式
范式：设计数据库时，需要遵循的一些规范。设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式成递次规范，越高的范式数据库冗余越小。
几个概念：
1、函数依赖： A --> B，如果通过A属性（属性组）的值，可以确定唯一B属性的值，则称B依赖于A。
例如：学号 --> 姓名；（学号，课程名称） --> 分数
2、完全函数依赖 A --> B，如果A是一个属性组，则B属性值的确定需要依赖于A属性组中所有的属性值。
例如：（学号，课程名称）--> 分数
3、部分函数依赖 A --> B，如果A是一个属性组，则B属性值的确定只需要依赖于A属性组中的某一些值即可。
例如：（学号，课程名称）--> 姓名
4、传递函数依赖 A --> B，B --> C，如果通过A属性（属性组）的值，可以确定唯一B属性的值，再通过B属性（属性组）的值可以确定唯一的C属性的值，则称C传递函数依赖于A。
例如：学号 --> 系名，系名 --> 系主任。
5、码如果在一张表中，一个属性或属性组，被其它所有属性所完全依赖，则称这个属性（属性组）为该表的码。
例如：该表中码为：（学号，课程名称）主属性：码属性组中的所有属性。非主属性：除去码属性组的其它属性。目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式（4NF）和第五范式（SNF，又称完美范式）。要遵循后边的范式要求，必须先遵循前边的所有范式要求。
第一范式（1NF）：每一列都是不可分割的原子数据项。
第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于码（在1NF基础上消除非主属性对主码的部分函数依赖）。
第三范式（3NF）：在2NF 的基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）。
8、数据库的备份和还原
1、方式1：命令行方式
备份的语法： mysqldump -u用户名 -p密码 数据库名 > 保存的路径\xxx.sql;
还原：① 登录数据库；② 创建数据库；③ 使用数据库；④ 执行文件。source 文件路径;
2、方式2：图形化工具
备份：右键数据库名 -> 备份/导出 -> 备份数据库，转储到SQL... -> 选择路径 -> 导出。
还原：右键root@LocalHost -> 执行SQL脚本 -> 选择文件 -> 执行。
9、事务
9.1、事务的基本介绍
事务的概念：事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
操作：
① 开启事务：start transaction;
② 回滚：rollback;
③ 提交：commit;
SELECT * FROM account;
-- 开启事务
START TRANSACTION;
#接下来的数据只会临时发生变化
-- 张三给李四转账500元
#1、张三账户-500
UPDATE account SET balance = balance - 500 WHERE NAME = '张三';
#2、李四账户+500 UPDATE account SET balance = balance + 500 WHERE NAME = '李四';
-- 发现执行没有问题，提交事务
COMMIT;
-- 发现出问题了，回滚事务
ROLLBACK;事务提交有两种方式：① 自动提交（MySQL的默认提交方式）：MySQL就是自动提交的，一条DML（增删改）语句会自动提交一次事务；② 手动提交（Oracle的默认提交方式）：需要先开启事务，再提交。
-- 查看MySQL中修改事务的默认提交方式：
SELECT @@autocommit;
#1代表自动提交，0代表手动提交
-- 修改默认提交方式
SET @@autocommit = 0;9.2、事务的四大特征
- 原子性（Atomicity）：事务是不可分割的最小操作单位，要么同时成功，要么同时失败。
- 持久性（Durability）：当事务提交或回滚后，它对数据库中的数据的改变就是永久的。
- 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
- 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
9.3、并发事务问题
| 问题 | 描述 |
| 脏读 | 一个事务读取到另一个事务中没有提交的数据。 |
| 不可重复读（虚读） | 在同一个事务中，两次读取到的数据不一样。 |
| 幻读 | 一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻影”。 |
9.4、事务的隔离级别
概念：多个事务之间是隔离的，相互独立的，但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。
隔离级别：
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
| read uncommitted | √ | √ | √ |
| read committed | × | √ | √ |
| repeatable read | × | × | √ |
| serializable | × | × | × |
① read uncommitted：读未提交。产生的问题：脏读、不可重复读、幻读
② read committed：读已提交（Oracle默认）。产生的问题：不可重复读、幻读
③ repeatable read：可重复读（MySQL默认）。产生的问题：幻读
④ serializable：串行化。可以解决所有的问题。
注意：隔离级别从小到大安全性越来越高，但是效率越来越低。
-- 查看事务隔离级别
select @@tx_isolation;
-- 设置事务隔离级别：
set [session | global] transaction level {read uncommitted | read committed | repeatable read | serializable}
