# 数据库概述

# 数据库相关概念

名称	全程	简称
数据库	存储数据的仓库，数据是有组织的进行存储	DataBase（DB）
数据库管理系统	操纵和管理数据库的大型软件	DataBase Management System（DBMS）
SQL	操作关系型数据库的编程语言，定义了一套操作关系数据库统一标准	Structured Query Language（SQL）

# MySQL 安装及启动

# 服务启动与停止

net start mysql180

net stop mysql180

# 连接服务客户端

mysql -uroot -proot

# MySQL 数据库

# 关系数据库（RDBMS）

概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库
特点：
1. 使用表存储数据，格式统一，便于维护
2. 使用 SQL 语言操作，标准统一，使用方便

# 数据模型

# SQL

# SQL 通用语法

SQL 语句可以单行或者多行书写，以分号结尾
SQL 语句可以使用空格 / 缩进来增强语句可读性
MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写
注释：
1. 单行注释：-- 注释内容或 # 注释内容（MySQL 特有）
2. 多行注释：/* 注释内容 */

# SQL 语句分类

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

# DDL

# 数据库操作

# 查询

查询所有数据库

show databases;

查询当前数据库

select database();

# 创建

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

# 删除

drop database [if exists] 数据库名;

# 使用

use 数据库名;

# 表操作 —— 查询

# 查询当前数据库所有表

show tables;

# 查询表结构

desc 表名;

# 查询指定表的创建语句

show create table 表名;

# 表操作 —— 创建

	create table 表名(
	字段1 字段1类型 [comment 字段1注释],
	字段1 字段1类型 [comment 字段1注释],
	字段1 字段1类型 [comment 字段1注释],
	......
	字段1 字段1类型 [comment 字段1注释]
	) [comment 表注释];

# 表操作 —— 数据类型

# 数值类型

类型	大小	有符号（SIGNED）范围	无符号（UNSIGNED）范围	描述
TINYINT	1 byte	(-128，127)	(0，255)	小整数值
SMALLINT	2 bytes	(-32768，32767)	(0，65535)	大整数值
MEDIUMINT	3 bytes	(-8388608，8388607)	(0，16777215)	大整数值
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			双精度浮点数
DECIMAL		依赖于 M（精度）和 D（标度) 的值	依赖于 M（精度）和 D（标度）的值	小数值

# 字符串类型

类型	大小	描述
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOG	0-255 bytes	不超过 255 个字符的二进制数据
TINYTEXT	0-255 bytes	短文本字符串
BLOG	0-65535 bytes	二进制形式的长文本数据
TEXT	0-65535 bytes	长文本数据
MEDIUMBLOG	0-16777215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16777215 bytes	中等长度文本数据
LONGBLOG	0-4294967295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4294967295 bytes	极大文本数据

# 日期类型

类型	大小	范围	格式	描述
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:00 至 2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	混合日期和时间值

# 表操作 —— 修改表

# 添加字段

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

# 修改数据类型

alter table 表名 modify 字段名 新增数据类型(长度);

# 修改字段名和字段类型

alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束];

# 删除字段

alter table 表名 drop 字段名;

# 修改表名

alter table tb_user rename to 新表名;

# 表操作 —— 删除

# 删除表

drop table [if exists] 表名;

# 删除指定表，并重新创建该表

truncate table 表名;

# DML

# DML 介绍

DML 英文全称是 Data Manipulation Language（数据操作语言），用来对数据库表中的数据记录进行增删改操作

添加数据 (INSERT)
修改数据 (UPDATE)
删除数据 (DELETE)

# 添加数据

# 给指定字段添加数据

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 条件];

# DQL

# DQL 介绍

DQL 英文全程是 Data Query Language（数据库查询语言），数据查询语言，用来查询数据库中表的记录，查询关键字：select

# DQL 语法

select 字段列表 from 表名列表 where 条件列表 group by 分组字段列表 having 分组后条件列表 order by 排序字段列表 limit 分页参数;

# 基本查询

# 查询多个字段

select 字段1,字段2,... from 表名;

select * from 表名;

# 设置别名

select 字段1 [as 别名1],字段2 [as 别名2] ... from 表名;

# 去除重复记录

select distinct 字段列表 from 表名;

# 条件查询

# 语法

select 字段列表 from 表名 where 条件列表;

# 条件

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<> 或！=	不等于
BETWEEN ... AND ...	在某个范围之内
IN(...)	在 in 之后的列表中的值，多选一
LIKE 占位符	模糊匹配（_匹配单个字符，% 匹配多个字符）
IS NULL	是 NULL
逻辑运算符	功能
----------	----------------------------
AND 或 &&	并且（多个条件同时成立）
OR 或 \|\|	或者（多个条件任意一个成立）
NOT 或者！	非，不是

# 聚合函数

# 介绍

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

# 常见聚合函数

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

# 语法

select 集合函数(字段列表) from 表名;

# 分组查询

# 语法

select 字段名 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];

# where 与 having 区别

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

# 注意

执行顺序：where > 聚合函数 > haviing
分组之后，查询的字段一般为聚合函数和分组字段，查询其它字段无任何意义

# 排序查询

# 语法

select 字段列表 from 表名 order by 字段1 排序方式1, 字段2 排序字段2...;

# 排序方式

ASC：升序（默认值）
DESC：降序

注意：如果是多字段排序，当第一个字段值相同时，才会按照第二个字段进行排序

# 分页查询

# 语法

select 字段列表 from 表名 limit 起始索引,查询记录数;

# 注意

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

# DQL 语句执行顺序

# DCL

# DCL 介绍

DCL 英文全称是 Data Control Language（数据库控制语言），用来管理数据库用户、控制数据库的访问权限

# 管理用户

# 查询用户

	use mysql;
	select * from user;

# 创建用户

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

# 修改用户密码

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

# 删除用户

drop user '用户名'@'主机名';

# 权限控制

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

# 查询权限

show grants for '用户名'@'主机名';

# 授予权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

# 撤销权限

revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

# 函数

# 字符串函数

函数	功能
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 个长度的字符串

# 数值函数

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

# 日期函数

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

# 流程函数

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

# 约束

# 概述

# 概念

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

# 目的

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

# 分类

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

# 约束演示

	create table student(
	id int primary key auto_increment comment '主键',
	name varchar(10) not null unique comment '姓名',
	age int check ( age > 0 && age <= 120 ) comment '年龄',
	status char(1) default '1' comment '状态',
	gender char(1) comment '性别'
	) comment '用户表';

# 外键约束

# 概念

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

# 语法

# 添加外键

	create table 表名(
	字段名数据类型,
	...
	[constraint] [外键名称] foreign key(外键字段名) references 主表(主表列名);
	)

alter atble 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名);

# 删除外键

alter table 表名 drop foreign key 外键名称;

# 外键删除更新行为

# 删除和更新行为

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

alter table 表名 add constraint 外键名称 foreign key (外键字段) references 主表名(主表字段名) on update cascade on delete cascade;

# 多表查询

# 多表关系

# 概述

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

一对多（多对一）
多对多
一对一

# 一对多

实现：在多的一方建立外键，指向一的一方的主键

# 多对多

实现：建立中间表，中间至少包含两个外键，分别关联两方主键

# 一对一

实现：在任意一方加入外键，关联另一方主键，并且设置外键为唯一的 (UNIQUE)

# 多表查询概述

概述：指从多张表中查询数据
笛卡尔积：笛卡尔乘积是指在数学中，两个集合 A 集合和 B 集合所有组合情况
多表查询分类
1. 连接查询
  1. 内连接：相当于查询 A、B 交集部分数据
  2. 外连接：
    1. 左外连接：查询左表所有数据，以及两张表交集部分数据
    2. 右外连接：查询右表所有数据，以及两张表交集部分数据
  3. 自连接：当前表与自身的连接查询，自连接必须使用表别名
2. 子查询

# 内连接

# 隐式内连接

select 字段列表 from 表1,表2 where 条件 ...;

# 显示内连接

select 字段列表 from 表1 [inner] join 表2 on 连接条件 ...;

# 外连接

# 左外连接

select 字段列表 from 表1 left [outer] join 表2 on 条件 ...;

# 右外连接

select 字段列表 from 表1 right [outer] join 表2 on 条件 ...;

# 自连接

# 子连接语法

select 字段列表 from 表A 别名A join 表A 别名B on 条件 ...;

可以是内连接，也可以是外连接

# 联合查询 - union,-union all

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

	select 字段列表 from 表A ...
	union [ALL]
	select 字段列表 from 表B ...

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

# 子查询

# 概念

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

select * from t1 where column1 = (select column1 from t2);

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

# 标量子查询

# 介绍

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

# sql

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

# 列子查询

# 介绍

子查询返回的结果是一列，这种子查询称为列子查询，常用操作符：IN、NOT IN、ANY、SOME、ALL

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

# sql

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

# 行子查询

# 介绍

子查询返回的结果是一行，这种子查询称为行子查询，常用操作符：IN、NOT IN、ANY、SOME、ALL

# sql

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

# 表子查询

# 介绍

子查询返回的结果是多行多列，这种子查询称为表子查询

# sql

select * from emp where (job,salary) in (select job,salary from emp where name = '腾讯' or name = '阿里');

# 事务

# 简介

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

# 操作

# 查看 / 设置事务提交方式

	select @@aotocommit;
	set @@autocommit = 0;

# 开启事务

start transaction 或 begin;

# 提交事务

commit;

# 回滚事务

rollback;

# 事务四大特性

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

# 并发事务问题

问题	描述
脏读	一个事务读取到另外一个事务还没有提交的数据
不可重复读	一个事务先后读取同一条记录，但两次读取数据不同，称之为不可重复读
幻读	一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据好像已经存在，好像出现了幻影

# 事务的隔离级别

# 分类

隔离级别	脏读	不可重复读	幻读
Read uncommitted	1	1	1
Read committed	0	1	1
Repeatable Read（默认）	0	0	1
Serializable	0	0	0

# 查看事务隔离级别

select @@transaction_isolation;

# 设置事务隔离级别

set [ SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

MySQL