【数据库】MySQL

概述

关系型数据库(RDBMS):多张相连的二维表组成的数据库

SQL语句：操作关系型数据库的编程语言，是统一标准的。

（1）不区分大小写
（2）单行注释：-- 注释内容 ； 多行注释：/* 注释内容 */

SQL语句分类：

• DDL：definition，定义对象
• DML：manipulation，操作数据
• DQL：query，查询表记录
• DCL：control，控制用户、数据库权限

DDL

数据库操作

查询

查询所有数据库：show databases;
查询当前数据库：select database();

使用

使用数据库：use 数据库名;

创建

创建数据库：create database [if not exists] 数据库名;

删除

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

表操作

创建

create table 表名(字段1 字段类型 [约束] [comment 字段1注释]，字段2 字段类型 [约束] [comment 字段2注释]，......字段n 字段类型 [约束] [comment 字段n注释]
)[comment 表注释];

约束

概念：作用于表中字段上的规则，用于限制存储在表中的数据
目的：保证数据库中的数据的正确性、有效性和完整性。

约束	描述	关键字
非空约束	限制该字段值不能为null	not null
唯一约束	保证字段的所有数据都是唯一、不重复的	unique
主键约束	主键是一行数据的唯一标识，要求非空且唯一	primary key(auto_increment自增)
默认约束	保存数据时，若为指定该字段值，则采用默认值	default
外键约束	让两个表的数据建立联系，保证数据的一致性和完整性	forign key

create table tb_user(id int primary key comment 'ID,唯一标识';username varchar(20) not null unique comment '用户名',name varchar(10) not null comment '姓名',age int comment '年龄',gender char(1) default '男' comment '性别'
) comment '用户表';

MySqL数据类型

数值类型

类型	大小(byte)	有符号(signed)范围	无符号(unsigned)范围	描述
tinyint	1	(-128,127)	(0,255)	小整数值
smallint	2	(-32768,32767)	(0,65535)	大整数值
mediumint	3	(-8388608,8389607)	(0,16777215)	大整数值
int	4	(-2147483648,2147483647)	(0,4294967295)	大整数值
bigint	8	(-263 ,263-1)	(0,264-1)	极大整数值
float	4	(-3.4028 E+38 ,3.4028 E+38)	0和(1.1754 E-38，3.4028 E+38)	单精度浮点数
double	8	(-1.7976 E+308 ,1.7976 E+308)	0和(2.2250 E-308，1.7976 E+308)	双精度浮点数
decimal				小数值(精度更高)

float(5,2),double(5,2),decimal(5,2):5表示整个数字长度，2表示小数位个数。

字符串类型

类型	大小	描述
char	0-255 bytes	定长字符串
verchar	0-65535 bytes	变长字符串

char(10)最多只能存10个字符，不足10个字符，占用10个字符空间。【性能高、浪费空间】
varchar(10)最多只能存10个字符，不足10个字符，按照实际长度存储。【性能低、节省空间】

日期类型

类型	大小（bytes）	范围	格式	描述
date	3	1000-01-01 至9999-12-31	YYYY-MM-DD	日期值
datetime	8	1000-01-01 00:00:00至于9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值

查询

查询当前数据库所有表：show tables;
查询表结构：desc 表名;
查询建表语句：show create table 表名;

修改

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

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

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

删除字段：alter table 表名 drop column 字段名;

修改表名：rename table 表名 to 新表名;

例题：在表tb_emp中完成如下操作：
(1)为表tb_emp 添加字段 qq varchar(11)
alter table tb_emp add qq varchar(11) comment 'QQ';
(2)修改 表tb_emp 字段类型 qq varchar(13)
alter table tb_emp modify qq varchar(13) comment 'QQ';
(3)修改 表tb_emp 字段名 qq 为qq_num varchar(13)
alter table tb_emp change qq qq_num varchar(13) comment 'QQ';
(4)删除 表tb_emp 的 qq_num 字段
alter table tb_emp drop column qq_num;
(5)将表tb_emp 表名修改为emp
rename table tb_emp to emp;

删除

删除表：drop table [if exits] 表名;

DML

insert

指定字段添加数据：insert 表名(字段名1，字段名2) values(值1，值2);
全部字段添加数据：insert into 表名 values (值1，值2...);
批量添加数据（指定字段）:insert into 表名(字段1，字段名2) values(值1,值2),(值1,值2);
批量添加数据（全部字段）:insert into 表名 values(值1,值2,...),(值1,值2,...);

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

例子：表tb_emp中有姓名name和性别gender
(1)为tb_emp表的name,gender字段插入值
insert into tb_emp(name,gender) values('张无忌',1);
(2)为tb_emp表的所有字段插入值。
insert into tb_emp values('周芷若',2);
(3)批量为tb_emp表的username,gender字段插入数据。
inser into tb_emp (username,gender) values ('韦一笑',2),('谢逊',1);

update

修改数据：update 表名 set 字段名1=值1，字段名2=值2，... [where 条件];

修改语句的条件可以有。若没有条件，则修改整张表的所有数据。

delete

删除数据：delete from 表名 [where 条件];

1.删除语句的条件可以有。若没有条件，则删除整张表的所有数据。
2.删除语句不能删除某一个字段的值（若要操作，可以使用update，将该字段的值置为NULL）

DQL

语法：

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

基本查询、条件查询(where)、分组查询(group by)、排序查询(order by)、分页查询(limit)

基本查询

查询多个字段:select 字段1,字段2,字段3 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

例子：表tb_emp中有姓名name,性别gender(1为男2为女),职位job,入职时间entrydate.

(1)查询入职时间在’2000-01-01’(包含)到’2010-01-01’(包含)之间 且 性别为女 的员工信息。
select * from tb_emp where entrydate between '2000-01-01' and '2010-01-01' and gender=2;

(2)查询 职位为2(讲师)，3(学工主管)，4(教研主管)的员工信息。
select * from tb_emp where job = 2 or job = 3 or job = 4;
或
select * from tb_emp where job in (2,3,4);

(3)查询 姓名 为两个字的员工信息。
select * fron tb_emp where name like '__';

(4)查询 姓‘张’ 的员工信息
select * from tb_emp where name like '张%';

分组查询

将一列数据作为一个整体，进行纵向计算。
select 聚合函数(字段列表) from 表名;

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

NULL值不参与所有聚合函数运算。
统计数量可以用count(*) count(字段)

例子：表tb_emp中有入职时间entrydate.

(1)统计该企业员工数量
select count(*) from tb_emp;
(2)统计该企业最晚入职的员工
select max(entrydate) from tb_emp;

分组查询

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

面试题：
where与having 的区别？
1.执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
2.判断条件不同：where不能对聚合函数进行判断，而having可以。

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

例子：表tb_emp中有姓名name,性别gender(1为男2为女),职位job,入职时间entrydate.

(1)根据性别分组，统计男性和女性员工的数量
select gender,count(*) from tb_emp group by gender;

(2)先查询入职时间在’2015-01-01’(包含)以前的员工，并对结果根据职位分组，获取员工数量大于等于2的职位。
select job,count(*) from tb_emp where entrydate <= '2015-01-01' group by job having count(*) >=2;

排序查询

select 字段列表 from 表名 [where 条件列表] [group by 分组字段] order by 字段1 排序方式1,字段2 排序方式2...;

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

若是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

例子：表tb_emp中入职时间entrydate，更新时间update_time

(1)根据 入职时间 对公司的员工进行 升序排序， 入职时间相同的，再按照 更新时间 进行降序排序。
select * from tb_emp order by entrydate, update_time desc;

分页查询

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

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

例子：在表tb_emp中操作：

(1)从 起始索引0 开始查询员工数据，每页展示5条记录
select * from tb_emp limit 0,5;

(2)查询 第2页 员工数据，每页展示5条记录
select * from tb_emp limit 5,5;

多表设计

有一对一，一对多，多对多

一对多

目前上述的两个表，在数据库层面，并未建立关联，所以是无法保证数据的一致性和完整性的。

外键约束

--创建表时指定
create table 表名(字段名 数据类型，...[constraint] [外键名称] foreign key [外键字段名] references 主表(字段名)
);--建完表后，添加外键
alter table 表名 add constraint 外键名 foreign key (外键字段名) references 主表(字段名);

物理外键：使用foreign key定义外键关联另一张表
缺点：影响增删改的效率（需要检查外键关系）；仅用于单节点数据库，不适用于分布式、集群场景；容易引发数据库的死锁问题，消耗性能。

逻辑外键：在业务层中，解决外键关联
(推荐！！！)

一对一

多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提高效率.
实现：在任意一方加入外键，关联另一方的逐渐，并且设置外键为唯一的(unique)
案例：用户 与 身份证信息 的关系

多对多

案例：学生 与 课程 的关系
关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

设计步骤

（1）分析各个模块涉及到的表结构，及表结构之间的关系
（2）分析各个表结构中的具体字段及约束

多表查询

概述

多表查询：从多张表中查询数据
笛卡尔积：两个集合的所有组合情况(在多表查询时，需要消除无效的笛卡尔积)

分类：内连接 和 外连接（左外连接和右外连接）

内连接

相当于查询A、B交际部分数据

隐式内连接：select 字段列表 from 表1,表2 where 条件...;
显式内连接：select 字段列表 from 表 [inner] join 表2 on 连接条件;

例子：表tb_emp有员工姓名name，员工的部门编号dept_id ; 表tb_dept有部门名称name,部门编号id

(1)查询员工的姓名，及所属的部门名称（隐式内连接实现）

select tb_emp.name,tb_dept.name 
from tb_em,tb_dept
where tb_emp.dept_id=tb_dept.id;-- 起别名
select e.name, d.name
from tb_emp e, tb_dept d
where e.dept_id=d.id;

(2)查询员工的姓名，及所属的部门名称（隐式外连接实现）

select tb_emp.name,tb_dept.name
from tb_emp
inner join tb_dept on tb_emp.dept_id=tb_dept.id;

外连接

左外连接：select 字段列表 from 表1 [outer] join 表2 on 连接条件;
右外连接：select 字段列表 from 表1 [outer] join 表2 on 连接条件;

例子：表tb_emp有员工姓名name，员工的部门编号dept_id; 表tb_dept有部门名称name,部门编号id

(1)查询员工表 所有 员工的姓名，和对应的部门名称（左外连接）

select e.name,d.name
from tb_emp e 
left join tb_dept d on e.dept_id=d.id;

(2)查询部门表 所有 部门的名称，和对应的员工名称（右外连接）

select e.name,d.name
from tb_emp e 
right join tb_dept d on e.dept_id=d.id;
--等同于
select e.name,d.name
from tb_dept d
left join tb_emp e on e.dept_id=d.id;

子查询

又称嵌套查询，其外部的语句可以是insert/ update/ delete/ select的任何一个，最常见的是select。
select * from t1 where column1 = (select column from t2 ...);

分类：
(1)标量子查询：子查询返回的结果为单个值
(2)列子查询：子查询返回的结果为一列
(3)行子查询：子查询返回的结果为一行
(4)表子查询：子查询返回的结果为多行多列

标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式
常用的操作符:= <> > >= < <=

例子：表tb_dept中有部门名name，部门ID id; 表tb_emp中有入职信息entrydate

(1)查询“教研部”所有员工信息

-- a.查询 教研部 的部门ID - tb_dept
select id from tb_dept where name = '教研部';
-- b.再查询该部门ID下的员工信息 -tb_emp
select * from tb_emp where dept_id = (select id from tb_dept where name = '教研部';)

(2)查询在“方东白”入职之后的员工信息

-- a.查询 方东白 的入职时间
select entrydate from tb_emp where name = '方东白';
-- b.查询在“方东白”入职之后的员工信息
select * from tb_emp where entrydate > (select entrydate from tb_emp where name = '方东白');

列子查询

子查询返回的结果是一列（可以是多行）
常用的操作符:in、not in等

例子：表tb_dept中有部门名name，部门ID id; 表tb_emp中有入职信息entrydate

(1)查询“教研部”和“咨询部”的所有员工信息

-- a.查询“教研部”和“咨询部”的部门ID -tb_dept
select id
from tb_dept
where name = '教研部' or name = '咨询部';-- b.根据部门ID,查询该部门下的员工信息 -tb_emp
select * 
from tb_emp
where dept_id in (3,2);合并以上:
select * from tb_emp
where dept_id in (select id from tb_dept where name = '教研部' or name = '咨询部');

行子查询

子查询返回的结果是一行（可以是多列）
常见的操作符:= <> in not in

例子：表tb_dept中有部门名name，部门ID id; 表tb_emp中有员工姓名name，入职信息entrydate，职位编号job

(1)查询与“韦一笑”的入职日期 及 职位都相同的员工信息

-- a.查询“韦一笑”的入职信息 及职位
select entrydate, job
from tb_emp
where name = '韦一笑'; 
-- b.查询与其入职信息 及 职位都相同的员工信息
select * 
from tb_emp
where entrydate = '2007-01-01' and job = 2;
或
select * 
from tb_emp
where (entrydate,job) = ('2007-01-01',2);合并:
select * from tb_emp
where entrydate = (select entrydate from tb_emp where name = '韦一笑') and job = (select job from tb_emp where name = '韦一笑' );
或
select * from tb_emp
where (entrydate,job) = (select entrydate, job from tb_emp where name = '韦一笑');

表子查询

子查询返回的结果是多行多列，常常作为临时表
常用的操作符:in

例子：表tb_dept中有部门名name，部门ID id; 表tb_emp中有员工姓名name，入职信息entrydate，职位job,部门ID dept_id

(1)查询入职时间是"2006-01-01"之后的员工信息 及其部门名称

-- a.查询入职时间是"2006-01-01"之后的员工信息 
select * 
from tb_emp
where entrydate > '2006-01-01';
-- b.查询这部分员工信息及其部门名称
select e.*,d.name
from tb_dept e,tb_dept d
where e.dept_id=d.id;-- 合并得：
select e.*,d.name
from (select * from tb_emp where entrydate > '2006-01-01');

案例

如下为四张表及其对应关系

(1)查询价格低于 10元 的菜品的名称、价格 及其 菜品的分类名称

select d.name,d.price,c.name
from dish d,category c
where d.category_id=c.id and d.price<10;

(2)查询所有价格在 10元(含)到50元(含)之间 且 状态为“起售”(为1)的菜品，展示出菜品的名称、价格 及其 菜品的分类名称（即使菜品没有分类，也需要将菜品查询出来）

select d.name, d.price c.name
from dish d
left join category c on c.id = d.category_id
where d.price between 10 and 50 
and d.status = 1

(3)查询每个分类下最贵的菜品，展示出分类的名称、最贵的菜品的价格

select c.name,max(d.price)
from dish d,category c
where c.id=d.category_id
group by c.name;

(4)查询各个分类下 菜品状态为“起售”(为1)，并且 该分类下菜品总数量大于等于3 的分类名称

select c.name,count(*)
from category c,dish d
where d.category_id = c.id and d.status = 1
group by c.name
having count(*)>=3

(5)查询出"商务套餐A"中包含了哪些菜品（展示出套餐名称、价格，包含的菜品名称、价格、份数）

select s.name, s.price, d.name, d.price, sd.copies
from setmeal s , dish d, setmeal_dish sd
where s.id=sd.setmeal_id and sd.dish_id = d.id and s.name='商务套餐A';

(6)查询出低于平均价格的菜品信息(展示出菜品名称、菜品价格)

-- a.查询出平均价格
select avg(price)
from dish;-- b.查询菜品信息
select * from dish 
where price < (select avg(price) from dish);

事务

场景：学工部 整个部门都解散了，该部门及其部下的员工都需要删除

操作：

删除学工部：delete from tb_dept where id=1;
删除学工部的员工：delete from tb_emp where dept_id =1;

问题：
若删除部门成功了，删除该部门员工时失败了，就造成了数据的不一致。

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

语法

开启事务：start transaction; / begin;
提交事务：commit
回滚事务：rollback

则场景的代码可改为：

start transaction;
delete from tb_dept where id=1;
delete from tb_emp where dept_id =1;
commit;
rollback;

commit类似crtl+s保存；rollback类似ctrl+z撤销

注意：MySQL的事务是自动提交的，即当执行一条DML语句，MySQL会立即隐式的提交事务。

四大特性(ACID)

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

索引

概念：帮助数据库高效获取数据 的 数据结构

优点：提高数据查询的效率，降低数据库的IO成本；通过索引对数据进行排序，降低数据排序的成本，降低CPU消耗
缺点：索引会占用存储空间；大大提高了查询效率，同时也降低了insert,update,delete的效率

空间换时间

结构

一般是指B+ Tree结构组织的索引。

1.每个节点，可以存储多个key(有n个key，就有n个指针)
2.所有的数据都存储在叶子节点，非叶子节点仅用于索引数据。
3.叶子节点形成了一颗双向链表，便于数据的排序及区间范围查询。

语法

创建索引：create [unique] index 索引名 on 表名(字段名...);
查看索引：show index from 表名;
删除索引：drop index 索引名 on 表名;

注意：
主键字段，在建表时，会自动创建主键索引
添加唯一约束时，数据库实际上会添加唯一索引

例子：
为tb_emp表的name字段建立一个索引
create index idx_emp_name on tb_name(name);