自制笔记 | JavaWeb——MySQL（持续更新...）

MySQL连接：

mysql -u用户名 -p密码 [-h数据库服务器IP地址 -p端口号]

SQL：一门操作关系型数据库的编程语言，定义操作所有关系型数据库的统一标准

通用语法：

① SQL语句可以单行或多行书写，以分号结尾
② SQL语句可以使用空格 / 缩进来增强语句的可读性
③ MySQL数据库的SQL语句不区分大小写
④ 注释：
单行注释：-- 注释内容或#注释内容
多行注释：/*注释内容*/

SQL分类：

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

一个项目的开发流程：

DDL

数据库操作：

1.查询

查询所有数据库：

show databases;

查询当前数据库：

select database();

2.使用

使用数据库：

use 数据库名;

3.创建

创建数据库：

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

4.删除

删除数据库：

drop database [if exists] 数据库名;

表操作：

1.创建

创建表：

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

约束：

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

设计表结构的基本流程：

注意：create_time记录的是当前这条数据插入的时间；update_time记录的是当前这条数据最后更新的时间

2.查询

查询当前数据库所有表：

show tables;

查询表结构：

desc 表名;

查询建表语句：

show create table 表名;

3.修改

添加字段：

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

修改字段类型：

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

修改字段名和字段类型：

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

删除字段：

alter table 表名 drop column 字段名;

修改表名：

rename table 表名 to 新表名;

4.删除

删除表：

drop table [if exists] 表名;

注意：在删除表时，表中的全部数据也会被删除

DML

1.添加数据（INSERT）

指定字段添加数据：

insert into 表名(字段名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, ...);

2.修改数据（UPDATE）

修改数据：

update 表名 set 字段名1 = 值1, 字段名2 = 值2, ... [where 条件];

注意：如果没有条件，则会修改整张表的数据

3.删除数据（DELETE）

删除数据：

delete from 表名 [where 条件];

注意：
① 如果没有条件，则会删除整张表的数据
② DELETE语句不能删除某一个字段的值，如果要操作，可以用UPDATE，将该字段的值置为NULL

DQL

语法：

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

1.基本查询

查询多个字段：

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

查询所有字段（通配符）：

select * from 表名

设置别名：

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

去除重复记录：

select distinct 字段列表 from 表名;

注意： *号代表查询所有字段，在实际开发中尽量少用（不直观、影响效率）

2.条件查询

条件查询：

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

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

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

3.分组查询

聚合函数：

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

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

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

注意：

① null值不参与所有聚合函数运算
② 统计数量可以使用：count(*)、count(字段)、count(常量)，推荐使用count( * )

分组查询：

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

where与having区别：

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

注意：

① 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义
② 执行顺序：where > 分组条件 + 聚合函数 > having

4.排序查询

排序查询：

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

排序方式：

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

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

5.分页查询

分页查询：

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

注意：

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

使用的两个函数：

① if(表达式, tvalue, fvalue)：当表达式为true时，取值tvalue；当表达式为false时，取值fvalue
② case expr when value1 then result1 [when value2 then value2...] [else result] end

多表设计

1.一对多

在数据库表中多的一方，添加字段，来关联一的一方的主键

外键语法：

-- 创建表时指定
create table 表名(
    字段名 数据类型,
    ...
    [constraint] [外键名称] foreign key(外键字段名) references 主表(字段名)
);

-- 建完表后，添加字段
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(字段名);

物理外键：

使用foreign key定义外键关联另外一张表

缺点：

① 影响增、删、改的效率（需要检查外键关系）
② 仅用于单节点数据库，不适用于分布式、集群场景
③ 容易引发数据库的死锁问题，消耗性能

逻辑外键（推荐）：在业务层逻辑中，解决外键关联。通过逻辑外键，就可以很方便的解决多表关联中的问题

2.一对一

一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率

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

3.多对多

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

多表查询

分类：

① 内连接：相当于查询A、B交集部分数据
② 外连接
左外连接：查询左表所有数据（包括两张表交集部分数据）
右外连接：查询右表所有数据（包括两张表交集部分数据）
③ 子查询

1.内连接

语法：

隐式内连接：

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

显式内连接：

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

2.外连接

语法：

左外连接：

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

右外连接：

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

3.子查询

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

形式：

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

子查询外部的语句可以是insert / update / delete /select的任何一个，最常见的是select

分类：

① 标量子查询：子查询返回的结果为单个值
② 列子查询：子查询返回的结果为一列
③ 行子查询：子查询返回的结果为一行
④ 表子查询：子查询返回的结果为多行多列

标量子查询：

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式

常用的操作符：= <> > >= < <=

列子查询：

子查询返回的结果是一列（可以是多行）

常用的操作符：in、not in

行子查询：

子查询返回的结果是一行（可以是多列）

常用的操作符：=、<>、in、not in

示例：

-- 查询与“韦一笑”的入职日期及职位都相同的员工信息
select * from tb_emp where (entrydate, job) = (select entrydate, job from tb_emp where name = '韦一笑')

表子查询：

子查询返回的结果是多行多列，常作为临时表

常用的操作符：in

事务

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

注意：默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式地提交事务

事务控制：

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

四大特性（ACID）：

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

索引

索引（index）是帮助数据库高效获取数据的数据结构

优点：

① 提高数据查询的效率，降低数据库的IO成本
② 通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗

缺点：

① 索引会占用存储空间
② 索引大大提高了查询效率，同时却也降低了insert、update、delete的效率（因为需要维护索引的结构）

结构：

MySQL数据库支持的索引结构有很多，如：Hash索引、B+Tree索引、Full-Text索引等。我们平常所说的索引，如果没有特别指明，都是指默认的B+Tree结构组织的索引

在B+Tree中：

① 每一个结点，可以存储多个key（有n个key，就有n个指针）
② 所有的数据都存储在叶子结点，非叶子结点仅用于索引数据
③ 叶子结点形成了一个双向链表，便于数据的排序及区间范围查询

语法：

创建索引：

create [unique] index 索引名 on 表名(字段名, ...);

查看索引：

show index from 表名;

删除索引：

drop index 索引名 on 表名;

注意：

① 提高数据查询的效率，降低数据库的IO成本
② 通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗