一、数据库操作

1.查看数据库

show databases;

2.创建数据库

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

推荐写charset UTF8mb4指定字符集
以使用反引号包裹关键字作为数据库/列/表的名字
MySQL的UTF8不是完整的UTF8，UTF8mb4是完整的UTF8

3.选中数据库

对于一个数据库服务器，有很多数据库。
需要先选中，再操作。

use 数据库名;

4.删除数据库

drop database 数据库名;

二、数据类型

1.数字数据类型

类型	说明
BIT(M)	Mbit(M省略则为1)
BOOL	1byte
TINYINT	1byte
SMALLINT	2byte
INT	4byte
BIGINT	8byte
FLOAT	4byte
DOUBLE	8byte
DECIMAL(M,D)	动态

BIT(M)中(M)填写数字。例如BIT(2)代表占用2bit。
用DECIMAL可以表示精确数字，但存储空间更大，运算速度更慢。
INT可存储的最大数字为21亿。
DECIMAL不存在精度损失。M是总位数，D是小数点后位数。M最大为65，D最大为30。M默认为10，D默认为0。如果D为零，则没有小数点。

2.字符串类型

类型	说明
CHAR(M)	0~255个字符(M省略则为1)
VARCHAR(M)	M为最大字符长度，具体长度为实际长度
TINYTEXT	最大长度为255个字节
TEXT	最大长度为65535个字节
MEDIUMTEXT	最大长度为2^24-1个字节
LONGTEXT	最大长度为2^32-1个字节

一个字节对应几个字符取决于字符集
ACSII、GBK：一个英文字符占1个字节，一个中文字符占2个字节
UTF-8：一个英文字符占1个字节，一个中文字符占3个字节
Unicode：一个英文字符占2个字节，一个中文字符占2个字节

3.二进制数据

二进制数据指的是图片、视频、音频。

在实际开发中，这类二进制数据不会存储在数据库中
一般之间按照文件的方式存储在硬盘中，将文件路径存储在数据库中

4.枚举和集合

无

5.日期类型

类型	说明
DATETIME	8Bytes，表示为 YYYY-MM-DD hh:mm:ss

三、数据表操作

1.查看数据库的所有表

show tables;

先使用USE选中数据库，这是进行所有表操作的前提条件。

2.创建数据表

create [temporary] table [if not exists] 表名(列名类型 [comment "描述"], 列名类型 [comment "描述"]) [engine 存储引擎] [character set 字符集] [collate 排序规则];

SQL一般使用蛇形命名法，例如student_id。
使用temporary关键字代表此表不会存储在硬盘中。

3.查看表结构

desc 表名;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

Null值为YES代表可以不填。
Key指索引类型。
Default指该列的默认值。
Extra指扩展信息。

4.修改表

alter table 表名动作列名类型;

4.1 添加列

alter table meat add age int after name;
# 在name列后添加一个age列，类型为int
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

4.2 修改列类型

alter table student modify age bigint;
# 把age类型改为BIGNIT
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | bigint      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

4.3 修改列名

alter table student rename column age to AGE;
# 把age名字改为AGE
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| AGE   | bigint      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

4.4 删除列

alter table student drop AGE;

5.删除表

drop table 表名;

四、增删查改

insert into 表名
select from 表名
update 表名
delete from 表名

增

1.普通插入

insert into 表名 values (列元素, 列元素);

2.指定列插入

insert into 表名 (指定列名, 指定列名) values (列元素, 列元素);

3.指定多列插入

insert into 表名 (指定列名, 指定列名) values (列元素, 列元素), (列元素, 列元素);

查

1.全列查询

select * from 表名;

全列查询是危险操作，会涉及到大量的硬盘和网络访问。

2.指定列查询

select 列名, 列名 from 表名;

指定列查询的结果是临时表。

3.查询时带表达式

select name, chinese + 10 from exam;

select name, chinese + math + english from exam;

4.查询时带别名

select name, chinese + math + english as "总成绩" from exam;

5.针对查询结果去重

select distinct 列名 from 表名;

6.条件查询

条件成立，这一行作为查询结果。

运算符	含义
>、>=、<、<=	大于、大于等于、小于、小于等于
=	NULL=NULL结果是NULL
<=>	NULL=NULL结果是TURE(1)
!=	不等于
value BETWEEN a0 AND a1	范围是[a0, a1]，NOT BETWEEN则取反
value IN	如果value在option列表中，则返回TRUE(1),NOT IN则取反
列名 IS NULL	是NULL
列名 IS NOT NULL	不是NULL
LIKE	模糊匹配，% 表示任意多个字符（包含0个）； _ 表示任意一个字符。NOT LIKE相反
AND	与
OR	或
NOT	非

NULL和任何比较都为FALSE!

6.1 基本查询

select name, english from exam where english > 60;

select name, chinese, english from exam where chinese > english;

select name FROM exam where (chinese + math + english) > 200;

带别名无法带入条件

6.2 条件查询

select name, english, chinese from exam WHERE english > 60 and chinese > 60;

select name, english, chinese from exam WHERE english > 60 or chinese > 60;

AND的优先级比OR高。

6.3 范围查询

select name, english from exam where english between 60 and 80;

select name, english from exam where english IN (77, 88, 99);

BETWEEN ... AND ...范围为闭区间

6.4 模糊查询(% _)

select name from exam where name like "孙%";
# 查询所有以孙开头的名字

select name from exam where name like "孙_";
# 查询所有以孙开头的名字，孙后面只有一个字

select name from exam where name like "孙__";
# 查询所有以孙开头的名字，孙后面只有两个字

模糊查询效率较低。

6.5 NULL查询

select * from exam where chinese <=> NULL;
# 查询语文成绩为空值

7.排序查询

select 列名 from 表名 order by 列名 [desc], 列名 [desc];

select * from exam order by chinese desc, math;

select * from exam order by chinese + math + english

select name, chinese + math + english as total from exam order by total desc;

select name, chinese from exam where chinese is not null ORDER BY chinese desc;

默认升序排列
NULL默认是最小的
使用desc降序排列
默认asc升序排列
输出结果是临时表
列名在前优先级更高
排序查询可用as带别名查询

8.分页操作

通过limit N offset M;或者limit M, N;子句获取从第M条数据开始（从0开始记录），往后N条数据。

不存在越界，最多返回空数据

改

update 表名 set 列名 = 值 where... order by... limit N;

不写where条件默认更改所有行。

删

delete from 表名 where... order by... 列名 limit N;

如果没有条件，会删除所有数据

截断表

无

插入查询结果

把查询的结果插入到另一个表内。

insert into 表名 [(列名)] select 列名 from 表名;

查询出来的列数和表结构(类型)相匹配
列的名字可以不一样

聚合查询

聚合查询，就是在进行行和行之间的运算。需要借助聚合函数运算，一般是Sql库函数。

函数	说明
COUNT(列名)	返回查询到的数据的数量
SUM(列名)	返回查询到的数据的总和
AVG(列名)	返回查询到的数据的平均值
MAX(列名)	返回查询到的数据的最大值
MIN(列名)	返回查询到的数据的最小值

1.COUNT

统计exam表中有多少个记录

select count(*) from 表名;

指定查询某个列的行数时，会忽略含NULL
count()内只支持一个参数，其他一样

2.SUM

select SUM(*) from 表名;

3.AVG

select AVG(*) from 表名;

平均值可能和原列类型不一致

4.MIN

select AVG(*) from 表名;

分组查询

把表的若干行分组，需要进行“分组依据”。
指定某一个列，这个列值相同的行就会分到同一个组里。

select 表达式 from 表名 group by 列名;

select role, AVG(salary) from emp GROUP BY role;
# 统计相同职位的薪资平均数，并返回职位和薪资临时表

SELECT role, AVG(salary) from emp where name != '博哥' GROUP BY role;
# 统计除"博哥"之外相同职位的薪资平均数，并返回职位和薪资临时表（查询前分组）

SELECT role, AVG(salary) from emp GROUP BY role having AVG(salary) < 15000;
# 统计相同职位的薪资平均数，并返回职位和薪资<15000的临时表（查询后分组）

查询前分组用WHERE,查询后分组用HAVING，可以同时使用

内置函数

函数一般都是通过select语句调用的，把函数放到列的位置

1.时间函数

函数	说明
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
DATE(date)	提取datetime表达式的日期部分
ADDDATE(date, INTERVAL 单位)	向日期值添加时间
SUBDATE(date, INTERVAL 单位)	向日期值减去时间
DATEDIFF(expr1, expr2)	两个日期的差,expr1 - expr2

2.字符串函数

函数	说明
CHAR_LENGTH(str)	返回字符长度
LENGTH(str)	返回字节长度
CONCAT(str1, str2, ...)	返回拼接后字符串
CONCAT_WS(分隔符, str1, str2, ...)	返回拼接后带分隔符字符串
LCASE(str)	字符串转换成小写,LOWER()
UCASE(str)	字符串转换成大写,UPPER()
HEX(str), HEX(N)	将字符串、数字转换成16进制
INSTR(str, substr)	返回substring第一次出现的索引
INSERT(str, pos, len, newstr)	在指定位置插入字符串，最多不超过指定的字符数
SUBSTR(str FROM pos FOR len)	返回指定的子字符串
REPLACE(str, from_str, to_str)	把字符串str中所有的from_str替换为to_str，区分大小写
STRCMP(expr1, expr2)	逐个比较两个字符串，返回-1, 0, 1
LEFT(str, len), RIFHT(str, len)	返回字符串str中最左/右边的len个字符
LTRIM(str), RTRIM(str), TRIM(str)	删除给定字符串的前导、末尾、前导和末尾的空白符
TRIM [{LEADING / TRAILING / BOTH}] [remstr] FROM	删除给定字符串的前导、末尾、前导和末尾的指定字符

3.数学函数

函数	说明
ABS(X)	返回X的绝对值
CEIL(X)	返回不小于X的最小整数值
FLOOR(X)	返回不大于X的最大整数X
CONV(N, from_base, to_base)	不同进制之间的转换
FORMAT(X, D)	将数字X化为“#, ###, ###”的格式，四舍五入到小数点后D位，以字符串形式返回
RAND([N])	返回一个随机浮点值
ROUND(X), ROUND(X, D)	将参数X舍入到小数点后D位
CRC32(expr)	计算指定字符串的循环冗余校验值并返回一个32位无符号整数

4.其他函数

函数	说明
version()	显示当前数据库版本
database()	显示当前正在使用的数据库
user()	显示当前用户
md5(str)	对一个字符串进行md5摘要，摘要后得到一个32位字符串
ifnull(val1, val2)	如果val1为NULL，返回val2，否则返回val1

五、数据库约束

数据库约束会针对表的数据内容做出限制。在创建表的时候，跟随表的语句，一起被设置到数据库服务器中。

MySQL约束类型	说明
NOT NULL 非空约束	指定非空约束的列不能存储NULL值
DEFALUT 默认约束	当没有给列赋值时候使用默认值
UNIQUE 唯一约束	指定唯一约束的列每行数据必须有唯一值
PRIMARY KEY 主键约束	NOT NULL 和 UNIQUE 的结合，可以指定一个列或多个列，有助于防止数据重复和提高数据的查询性能
FOREIGN KEY 外键约束	用于定义两个表之间的关联关系，可以确保数据的完整性和一致性
CHECK 约束	用于限制列或数据在数据库中的值，确保数据的准确性和可靠性

# 非空约束
create table student(id int NOT NULL, student varchar(20));
insert into student values(null, null); # 报错

# 默认约束
create table student(id int, student varchar(20) DEFAULT "无名氏");
insert into student values(1); # 默认名字为“无名氏”

# 唯一约束
create table student(id int, student varchar(20) UNIQUE);
insert into student values(1, "张三"); # NULL和NULL不算重复

# 联合主键
create table student(name varchar(20), class varchar(20), primary key(name, class));

# 自增主键
create table student(id int primary key auto_increment, class varchar(20)); 
insert into student values(1, "张三");
insert into student values(2, "张三");
insert into student values(null, "张三"); # 自增为3

# 外键约束
create table class(class_id int primary key auto_increment, class_name varchar(10));
create table student(student_id int primary key auto_increment, student_name varchar(10),
    class_id int, foreign key(class_id) references class(class_id));
# 学生班级id必须是班级表存在的班级id
    
# 自定义约束
create table student(id int, name varchar(20), age int, gender varchar(1),
    check(age > 18), check(gender = "男" or gender = "女"));
# 学生年龄必须大于18，性别必须是男或女

一个表只能有一个主键，但是多个列可以作为主键
针对整数，可以自增主键PRIMARY KEY
自增主键只会在最大数自增，且即使数据删除，最大数也会保留
针对外键约束，指的是两个表之间的关联。假设一共只有三个班级，分别是class1, class2, class3。如果出现了有学生在class4这个班级的不合法数据，就应该由外键约束控制。换句话说，是班级表约束学生表，班级表是父表，学生表是子表。其次，父表约束子表的同时，子表也在约束父表。父表的删除/修改操作，也会收到子表外键的约束
针对主键列，自带索引。如果一个列没有主键，那么这个列不带有索引，就不能被外键约束。因为关联表必须带有索引！

六、数据库设计

三大范式

1.第一范式

数据表的每一列都是不可分割的原子数据项，而不是集合，数据，对象等非原子数据。

2.第二范式

在满足第一范式的基础上，不存在非关键字段对任意候选键的部分函数依赖，存在于表中定义了复合主键的情况。

复合主键：即联合主键，一个主键包含了多个列。
候选键：当一个主键只包含一个列时，一个列就是候选键；当一个主键包含多个列时，其中每一个被包含的列叫候选键。
非关键字段：除候选键之外的列。
完全函数依赖：通过整个主键，确定一个数据。
部分函数依赖：通过主键中的一部分，确定一个数据。

即：一张表中的非主键列，必须由整个主键确定

3.第三范式

再满足第二范式的基础上，不存在非关键字段，对任一候选键的传递依赖。

ID	学号	姓名	年龄	性别	学院	学院电话	学院地址
1	001	张三	18	男	经济管理学院	8888-8888	上海市浦东新区
2	002	李四	18	女	信息工程学院	6666-6666	上海市浦东新区

此表的学院电话和学院地址附属于学院，学院又附属于主键ID，则发生了传递依赖。通过分表来消灭传递依赖。

ID	学号	姓名	年龄	性别
1	001	张三	18	男
2	002	李四	18	女

ID	学院	学院电话	学院地址
1	经济管理学院	8888-8888	上海市浦东新区
2	信息工程学院	6666-6666	上海市浦东新区

设计过程

1.从业务逻辑中抽象的到抽象类/实体类

类对应的数据库中的表，类的属性对应数据表中的列。

2.确定实体和实体之间的关系，并画出E-R图

即表和表之间的关系：一对一，一对多，多对多。

3.根据E-R图完成Sql语句并创建数据库

表关系为一对一或一对多时，附表应该包含主表主键。
表关系为多对多时，引入一张关联表。

七、联合查询

联合查询/多表查询的前提，是多表之间存在一定关系。

笛卡尔积

笛卡尔积的结果是一张更大的表。列数是两列之和，笛卡尔积的行数是两行之积。

select * from student, class;
+------+-------+----------+----------+------------+
| id   | name  | class_id | class_id | class_name |
+------+-------+----------+----------+------------+
|  100 | meat1 |        1 |        2 | java101    | 
|  100 | meat1 |        1 |        1 | java100    |
|  101 | meat2 |        1 |        2 | java101    |
|  101 | meat2 |        1 |        1 | java100    |
|  102 | meat3 |        2 |        2 | java101    |
|  102 | meat3 |        2 |        1 | java100    |
+------+-------+----------+----------+------------+

通过排列组合后可以把两个表的所有情况都罗列出来，但其中这里包含了无效数据。此时发现其中对应列的值相同，此时这个数据就应该被筛选出来作为合理的数据-连接条件。

select * from student, class where student.class_id = class.class_id;
# 通过表名.列名取消歧义
+------+-------+----------+----------+------------+
| id   | name  | class_id | class_id | class_name |
+------+-------+----------+----------+------------+
|  100 | meat1 |        1 |        1 | java100    |
|  101 | meat2 |        1 |        1 | java100    |
|  102 | meat3 |        2 |        2 | java101    |
+------+-------+----------+----------+------------+

进一步，基于这个结果，添加其他条件，满足其他的查询条件。

进行笛卡尔积
通过连接条件，进行筛选
根据实际需求场景，进一步添加其他条件
针对列进行筛选/计算/分组/聚合

内连接

上述代码编写的联合查询，也是在进行内连接，只是语法不同。

select * from 表1 inner [inner] join 表2;

select * from 表1 inner [inner] join 表2 on 条件;

select * from 表1 inner [inner] join 表2 on 条件 where 其他条件;

select 表名.列名 from 表1 [inner] join 表2 on 条件 where 其他条件;

外连接

对于MySQL来说，外连接分为左外连接'left join'、右外连接'right join'，但不支持全外连接。

现在有两张表，他们的数据是一一对应的。

student
+------------+------+
| student_id | name |
+------------+------+
|          1 | 张三 |
|          2 | 李四 |
|          3 | 王五 |
+------------+------+

score
+------------+-------+
| student_id | score |
+------------+-------+
|          1 |    90 |
|          2 |    80 |
|          3 |    70 |
+------------+-------+

此时外连接和内连接是完全等价的。

select * from student join score on student.student_id = score.student_id;
# 内连接
+------------+------+------------+-------+
| student_id | name | student_id | score |
+------------+------+------------+-------+
|          1 | 张三 |          1 |    90 |
|          2 | 李四 |          2 |    80 |
|          3 | 王五 |          3 |    70 |
+------------+------+------------+-------+

select * from student left(right) join score on student.student_id = score.student_id;
# 外连接
+------------+------+------------+-------+
| student_id | name | student_id | score |
+------------+------+------------+-------+
|          1 | 张三 |          1 |    90 |
|          2 | 李四 |          2 |    80 |
|          3 | 王五 |          3 |    70 |
+------------+------+------------+-------+

如果两张表的数据不是一一对应时，左右外连接结果不同。

select * from student left join score on student.student_id = score.student_id;
# 左外连接
+------------+------+------------+-------+
| student_id | name | student_id | score |
+------------+------+------------+-------+
|          1 | 张三 |          1 |    90 |
|          2 | 李四 |          2 |    80 |
|          3 | 王五 |       NULL |  NULL |
+------------+------+------------+-------+

select * from student right join score on student.student_id = score.student_id;
# 右外连接
+------------+------+------------+-------+
| student_id | name | student_id | score |
+------------+------+------------+-------+
|          1 | 张三 |          1 |    90 |
|          2 | 李四 |          2 |    80 |
|       NULL | NULL |          4 |    70 |
+------------+------+------------+-------+

外连接一般用于查询多余数据。

自连接

通过自连接，把行和行之间的关系，转成列和列之间的关系。查询时需要指定别名！

select * from score s1, score s2;

假设有两张表，一张学生表，一张成绩表。

student
+------+------+
| id   | name |
+------+------+
|    1 | 张三 |
|    2 | 李四 |
|    3 | 王五 |
+------+------+

score
+------+------------+---------+
| id   | java_score | c_score |
+------+------------+---------+
|    1 |         86 |      90 |
|    2 |         75 |      84 |
|    3 |         90 |      86 |
+------+------------+---------+

查询Java成绩比C成绩高的学生。先不筛选列来观察情况：

select * from student 
   join score s1 on student.id = s1.id 
   join score s2 on student.id = s2.id 
   where s1.java_score > s2.c_score;
+------+------+------+------------+---------+------+------------+---------+
| id   | name | id   | java_score | c_score | id   | java_score | c_score |
+------+------+------+------------+---------+------+------------+---------+
|    3 | 王五 |    3 |         90 |      86 |    3 |         90 |      86 |
+------+------+------+------------+---------+------+------------+---------+

优化查询后就是想得到的结果：

select student.id, student.name, s1.java_score, s2.c_score from student 
   join score s1 on student.id = s1.id 
   join score s2 on student.id = s2.id 
   where s1.java_score > s2.c_score;
+------+------+------------+---------+
| id   | name | java_score | c_score |
+------+------+------------+---------+
|    3 | 王五 |         90 |      86 |
+------+------+------------+---------+

子查询

将查询出来的结果，作为下一个查询条件。

假设有一张学生表：

+------+------+----------+
| id   | name | class_id |
+------+------+----------+
|    1 | 张三 |        1 |
|    2 | 李四 |        1 |
|    3 | 王五 |        2 |
+------+------+----------+

查询和“张三”同一个班级的学生，可以用两个SQL语句。

select class_id from student where name = "张三";
+----------+
| class_id |
+----------+
|        1 |
+----------+

select name from student where class_id = 1 and name != "张三";
+------+
| name |
+------+
| 李四 |
+------+

也可使用子查询将两句合并为一句。虽然SQL语句更少了，但可读性变差了。

select name from student where class_id = (
    select class_id from student where name = "张三"
    ) and name != "张三";
+------+
| name |
+------+
| 李四 |
+------+

当子查询返回多个列时，用IN关键字。

select * from 表名 where 列名 in (
    select ...
    );

合并查询

把多个查询结果，合并成一个查询结果。使用UNION或UNION ALL关键字。

select * from student where id = 1
    union all
    select * from student;
+------+------+----------+
| id   | name | class_id |
+------+------+----------+
|    1 | 张三 |        1 |
|    2 | 李四 |        1 |
|    3 | 王五 |        2 |
|    1 | 张三 |        1 |
+------+------+----------+

使用UNION会剔除重复数据，UNION ALL则不会。但需确保两组合并的结果，列类型和个数需匹配。

八、索引

索引在数据库中是一个特殊的数据结构，可以加快查询速度，但会占据额外的存储空间。后续修改数据的时候，也会有额外的开销。

索引的数据结构

在日常开发中，数据库的查询操作占比最高。因此要选择一种查询效率最高的数据结构。二叉搜索树会由于不平衡的问题导致一边子树的长度过长。AVL树虽严格控制左右子树的长度，但会给增删改的操作制造更多的开销。而哈希表只能处理“相等”查询，不能处理“范围”查询。

因此，就出现了一种为数据库量身定制的数据结构：B+树。想了解B+树，就要知道B+树的前身：B树。

B树是一个N叉搜索树。每个结点保存N个值，每个结点有N + 1个子树。因此和红黑树相比，相同数目的元素B树比红黑树低很多。虽然此时在查询的时候要比较多次，但一次硬盘读取可以读多个值。

B+树是基于B树进行改进的，也是一个N叉搜索树。

每个结点保存N个值，每个结点有N个子树
父结点的值会在子结点中以最大值的身份出现。最后叶子结点的内容是整个数据集合的“全集”
由于当前的叶子结点是全集，因此只使用叶子结点存储涉及到的“数据行”，非叶子结点，只存储“索引列”

B+树相比于其他数据结构作为索引的优势：

作为一个N叉搜索树，高度更低。在查询的时候，访问硬盘的次数更少
所有的数据行都在叶子结点上，所有的查询操作最终都要落到叶子结点上。访问硬盘次数更稳定
叶子结点作为链表，适合进行范围查询
非叶子结点只存储ID，占据的空间小

页

B+树上的结点，在MySQL内部称为“页”。默认大小是16KB。

索引页（非叶子结点）只存储索引列
数据也（叶子结点）存储数据行
对于非主键列创建的索引，虽然也是B+树，但叶子节点就不是数据行了，而是主键id。
一个表可以有多个索引(B+树)，只需要有一个B+树保存数据就行

索引分类

一个表，可以有多个索引。

1.主键索引

创建表时根据主键创建的索引。

2.普通索引

通过SQL语句创建的索引。

3.唯一索引

创建的表带有UNIQUE关键字的。

4.聚集索引

对于InnoDB来说，就是主键索引。

5.非聚集索引

对于InnoDB来说，就是非主键索引。

6.索引覆盖

查询的内容已经包含在索引列中。

使用索引

1.建立索引

当一张表存在主键约束、外键约束或唯一约束时，MySQL会为其自动创建索引。如果不指定任何约束时，MySQL会自动为每一个表生成一个隐藏列并为其创建索引。

创建索引操作可能很危险，如果针对一张大表创建索引，会触发大量的硬盘IO。最好在创建表时指定索引。

1.1 主键索引

create table 表名 (列名 类型 primary key);

create table 表名 (列名 类型, primary key (列名));

create table 表名 (列名 类型 primary key);
alter table 表名 add primary key (列名);
alter table 表名 modify 列名 类型 auto_increment;

1.2 唯一索引

create table 表名 (列名 类型 unique);

create table 表名 (列名 类型, unique (列名));

create table 表名 (列名 类型);
alter table 表名 add unique (列名);

1.3 普通索引

create table 表名 (列名 类型, index (列名));

create table 表名 (列名 类型);
alter table 表名 add unique (列名);
       
create table 表名 (列名 类型);
create index 索引名 on 表名(列名);

2.建立复合索引

create table 表名 (列名1 类型, 列名2 类型, index (列名1, 列名2));

create table 表名 (列名1 类型, 列名2 类型);
alter table 表名 add unique (列名1, 列名2);
       
create table 表名 (列名1 类型, 列名2 类型);
create index 索引名 on 表名(列名1, 列名2);

3.查看索引

show keys from 表名;

show index from 表名;

desc 表名;

4.删除索引

4.1 主键索引

alter table 表名 drop primary key;

4.2 其他索引

alter table 表名 drop index 索引名;

5.索引失效问题

可以在SQL语句前使用EXPLAIN关键字查看是否包含索引。当TYPE列显示All为全表查询，const为索引。

查询操作时没有使用索引列
查询索引列时索引值较单一，例如"男"和"女"
查询时针对索引列进行表达式运算
查询条件中包含了OR，一边是索引列的条件，一边是非索引列的条件
字符串查询时包含了LIKE时，通配符%或_放在字符串开头
查询索引列时条件包含!=或NOT
多个表联合查询时，但表的字符集不一致
设定复合索引时（例如a,b,c），使用b或c或b,c查询

九、事务

开发中，某个功能需要多个SQL配合完成。事务可以把多个SQL打包成一个整体，要么全部执行完，要么全都不执行（回滚到执行前状态），避免出现执行一般的情况。

事务的四个核心特性：

原子性：多个SQL是一个整体
一致性：数据正确符合实际情况
持久性：事务对数据做出的修改能够持久生效（写入硬盘）
隔离性：讨论并发情况下可能出现的问题：脏读问题、不可重复读问题和幻读问题
- 脏读：第一个事务正在对数据进行修改时，被第二个事务读取，此时读取的数据可能是脏数据。此时应规定未被提交的修改，不能被其他事务读取到。
- 不可重复读：同一个事务多次读取同一个数据结果不同。此时应给“读取”操作加锁。
- 幻读：同一个事务针对某个查询，多次读取到的“结果集”(查询得到的临时表)不同。此时需要让服务器对事务进行彻底“串行化”。

事务操作

START TRANSACTION; # 开启事务

COMMIT; # 提交事务

ROLLBACK; # 回滚事务

SAVEPOINT 保存点名; # 设置保存点

ROLLBACK TO 保存点名; # 回滚到保存点

隔离级别

MySQL为程序员提供了多个“隔离级别”让程序员自主选择隔离性。可以通过SQL语句或MySQL的配置文件修改隔离级别。

READ UNCOMMITTED -> 隔离性性最弱，并发性最强，效率最高 -> 脏读、不可重复读、幻读
READ COMMITTED -> 隔离性提高，并发性降低，效率降低 -> 不可重复读、幻读
REPEATABLE READ（默认）-> 隔离性提高，并发性降低，效率降低 -> 一部分幻读
SERIALIZABLE -> 隔离性最高，并发性最低，效率最低

1.通过SQL语句（临时）

如果MySQL服务器重启，当前命令失效。

SELECT @@GLOBAL.transaction_isolation; # 查询隔离级别

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL 隔离级别|访问模式;

2.通过MySQL的配置文件（长期）

在 C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" 文件中修改。

十、视图

视图是一个虚拟的表。它不占据存储空间，而是把其他的查询结果集临时保存下来，组织成表一样的结构。

创建视图

通过创建视图，把一系列查询语句返回的表临时保存起来。以后使用视图进行查询，相当于“子查询”。

CREATE VIEW 视图名 (列名1, 列名2...) AS 查询语句;

修改视图

一般来说，修改表时，视图会同时修改；修改视图，表也会同时修改。但不是所有视图都能修改，本质是因为修改视图不能与原始表一一对应。

当创建视图的时候，使用了聚合函数
视图关联的查询中，使用了子查询
视图关联的查询中，包含去重查询
视图关联的查询中，包含GROUP BY或HAVING
视图关联的查询中，包含UNION或UNION ALL
视图关联的查询中，使用了不可修改的视图

删除视图

DROP VIEW 视图名;

十一、JDBC编程

实际开发中，很少使用JDBC进行数据库操作。

增删改操作

import com.mysql.cj.jdbc.MysqlDataSource; // 导入的MySql驱动包

import javax.sql.DataSource; // JDBC规范的核心接口
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

public class Demo1 {
    public static void main(String[] args) throws SQLException {
    
        // 1. 创建“数据源”
        DataSource dataSource = new MysqlDataSource(); // JDBC定义接口，MySql驱动包实现接口
        ((MysqlDataSource) dataSource).setURL("jdbc:mysql://127.0.0.1:3306/meat" +
                "?characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=true");
        ((MysqlDataSource) dataSource).setUser("root");
        ((MysqlDataSource) dataSource).setPassword("1234");

        // 2. 和数据库进行网络连接
        Connection connection = dataSource.getConnection(); // 封装了拨号和接通的过程，失败会抛出异常

        // 3. 构造SQL语句
        //    字符串的SQL不符合JDBC要求，需要SQL字符串构造出“语句”对象
        //    JDBC提供了 Statement 和 PreparedStatement
        //    使用 PreparedStatement 就能让SQL语句的解析操作放在客户端上完成
        Scanner sc = new Scanner(System.in);
        System.out.print("学号:");
        int id = sc.nextInt();
        System.out.print("姓名:");
        String name = sc.next();
        System.out.print("班级:");
        int class_id = sc.nextInt();
        String sql = "insert into student values(?, ?, ?)"; // ? 占位符
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1, id); // JDBC 在替换中进行检查校验，验证是否存在安全隐患
        statement.setString(2, name);
        statement.setInt(3, class_id);

        // 4. 发送SQL语句到服务器上执行
        int n = statement.executeUpdate(); // n 表示这样的操作影响了多少行

        System.out.println("n = " + n);

        // 5. 释放上述资源，且释放顺序和创建顺序相反
        statement.close();
        connection.close();
    }
}

查询操作

import com.mysql.cj.jdbc.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Demo2 {
    public static void main(String[] args) throws SQLException {

        // 1. 创建数据源
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource) dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/meat?" +
                "characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=true");
        ((MysqlDataSource) dataSource).setUser("root");
        ((MysqlDataSource) dataSource).setPassword("1234");

        // 2. 连接数据库
        Connection connection = dataSource.getConnection();

        // 3. 构造SQL语句
        String sql = "select * from student";
        PreparedStatement statement = connection.prepareStatement(sql);

        // 4. 返回结果集
        ResultSet resultSet = statement.executeQuery();

        // 5. 遍历结果集
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String  name = resultSet.getString("name");
            int class_id = resultSet.getInt("class_id");
            System.out.println(id + " " + name + " " + class_id);
        }

        // 6. 释放资源
        resultSet.close();
        statement.close();
        connection.close();
    }
}

MySQL基础