SQL

SQL 语句的分类

DQL：

数据查询语言（凡是带有 select 关键字的都是查询语句）

​ select...

DML：

​ 数据操作语言（凡是对表中的数据进行增删改的都是 DML）

​ insert 增 delete 删 update 改

DDL：

​ 数据定义语言（凡是带有 create、drop、alter 的都是 DDL）

​ 主要操作的是表的结构，不是表的数据

TCL：

​ 事务控制语言（包括：事务提交 commit、事务回滚 rollback）

DCL：

​ 数据控制语言（授权 grant、撤销权限 revoke）

MySQL 常用命令

desc 表名 查看表的结构select 命令select * from 表名; 查看表的信息select version(); 查看当前使用的版本号select database(); 查看当前使用的数据库\c 用来终止一条命令的输入source 地址\xxx.sqlxxx.sql 这种文件被称为 sql 脚本文件批量的执行 sql 语句，可以使用 sql 脚本文件

PRI UNI MUL

# 如果键是PRI，则列是主键或多列主键中的列之一。# 如果键是UNI，则该列是唯一索引的第一列。(唯一索引允许多个空值，但可以通过检查Null字段来判断该列是否允许空。)# 如果键为MUL，则该列是非唯一索引的第一列，其中允许在列中多次出现给定值。

DQL

3. 查询

3.1 简单查询

select 字段名 from 表名​ 若查询多个字段，用逗号隔开即可查询所有字段​ 不建议使用 *，因为它会先将 * 转为所有字段，效率低，可读性差，建议写上所有字段。select 字段名 as 别名 from 表名只是将 指定字段 显示的查询结果字段名显式为别名（select 语句永远不会进行修改操作）as 可以省略，用空格代替若别名含有空格，可以用单引号（或双引号，但在oracle数据库中使用不了）引起来，字符串标准的使用单引号括起来字段可以使用数字表达式

3.2 条件查询

​ select 字段 from 表名 where 条件;

= 等于<> 或 != 不等于< 小于 <= 小于等于> 大于 >= 大于等于between ... and ... 表示两个值之间（闭区间） 等同于 >= and <=is null 为 null （is not null 不为空，数据库中 null 代表什么都没有）and 并且 or 或者 （若两者同时出现，and 的优先级 比 or的优先级高，可加括号避免）in 包含，相当于多个 or（not in 不在这几个值当中的数据）

​ in(具体值，具体值，......) 不是区间

not 取非，主要用在 is 或 in 中like 模糊查询，支持 % 或 下划线匹配% 匹配任意个字符下划线，一个下划线只能匹配任意一个字符（若想匹配下划线，可以加上\转义字符）select ename from emp where ename like %K 找出以 K 结尾的名字

4. 排序

select 字段名 from 表名 order by 字段名 默认是升序select 字段名 from 表名 order by 字段名 **desc ** 指定降序（Descending order）select 字段名 from 表名 order by 字段名 asc 指定升序（Ascending order）多个字段排序select 字段名 from 表名 order by 字段名 控制字符，字段名 控制字符，...只有前者相同的情况下，才会轮到后者排序根据字段的位置进行排序select 字段名 from 表名 order by 字段名的列位置（不建议在开发中写，不健壮，列的顺序容易修改）select ... from ... where ... order by ...执行顺序 from -> where -> select -> order by（排序总是在最后）在处理使用Mysql时，数据表采用utf8字符集，使用中发现中文不能直接按照拼音排序select * from musician_ordered order by convert(name using gbk) collate gbk_chinese_ci;对name字段进行gbk编码，然后，对编码后的内容根据gbk_chinese_ci进行整理排序这样得到的结果，英文是排在中文前面的，而且是根据拼音排序的gbk_chinese_ci：按照普通的字母顺序排，不区分大小写gbk_bin：按照二进制顺序排，区分大小写

5. 数据处理函数（单行处理函数）

5.1特点

一个输入对应一个输出，和其对应的是多行处理函数（多个输入，对应一个输出）

5.2 常见的函数

lower 转换小写select lower(字段名) from 表名upper 转换大写select upper(字段名) from 表名substr 取子串select substr(字段名，起始下标（从1开始），截取的长度)length 长度select length(字段名) from 表名concat 进行拼接select concat(字段名，字段名) from 表名trim 去空格select 字段名 from 表名 where 字段名 = trim( % )str_to_date 将字符串转换成日期date_format 格式化日期format 设置千分位round 四舍五入若 select 后面接字面量/字面值，则生成一个表，其中的数据全部变为这个字面量，字段名为 这个字面量。select round(数值，保留几个小数) from 表名若为 -1 例如：1236.567 -> 1240 保留到十位若为 -2 例如：1236.567 -> 1200 保留到百位rand 生成随机数select rand() from 表名 生成 [0-1）的随机数例如：select round(rand()100, 0) from 表名 生成100以内的随机数ifnull 可以将 null 转换成一个具体值数据库中只要 NULL 参与运算，结果就为 NULLifnull（数据，被当作哪个值） 如果数据为 NULL，则将其当作后面指定的值case ... when ... then ... when ... then ... else ... end 类似于 if-else 语句

6. 分组函数（聚合函数/多行处理函数）

6.1 特点

​ 输入多行，最终输出一行

6.2 常用的函数

count 计数sum 求和avg 平均值max 最大值min 最小值

6.3 注意

分组函数在使用的时候必须先进行分组，然后才能用；

​ 如果你没有对数据进行分组，整张表默认为一组。

分组函数自动忽略 NULL， 不需要提前对 NULL 进行处理count（*） 统计表当中的总行数。（不可能有一行记录所有的列都为 NULL）分组函数不能直接使用在 where 子句中因为 where 子句执行的时候，还没有进行分组所有的分组函数可以组合起来一起用 （select min(),max(),... from 表名）

7. 分组查询

​ 在实际的应用中，可能需要先进行分组，然后对每一组的数据进行操作

select ... from ... group by ...select ... from ... where ... group by ... order by ...执行顺序：from -> where -> group by -> select -> order by在一条 select 语句当中，如果有 group by 语句的话，select 后面只能跟：参加分组的字段以及分组函数，其他的一律不能跟多个字段联合分组select xxx from xxx group by 字段1，字段2，...使用 having 可以对分完组之后的数据进一步过滤，having 不能单独使用（单独使用没有意义），必须和 group by 联合使用(很耗资源，尽量少用)优化策略：where 和 having，优先选择 where，where 实在完成不了了，再选择 havingselect Colle, max(Credit) MC from course group by Colle having MC >= 4 order by MC desc;执行顺序 from -> group by -> having -> select -> order by (为啥 having 后面可以使用别名？（MYSQl可以这么做是因为MYSQL用的是临时表，在having前已经产生了数据，所以你可以用别名，但SQL Sever不可以，SQL是在having后才Select）因为mysql对此作了扩展。在mysql 5.7.5之前的版本，ONLY_FULL_GROUP_BY sql mode默认不开启。在5.7.5或之后的版本默认开启。)

8. distinct

select distinct 字段名，... from 表名 把查询结果去除重复记录需要放在所有字段的最前面若 distinct 后接多个字段，表示多个字段联合起来去重

9. 连接查询

9.1定义：多张表联合起来查询数据，被称为连接查询

9.2 分类（按表连接的方式）

内连接等值连接非等值连接自连接外连接左外连接（左连接）右外连接（右连接）全连接

9.3 笛卡尔积现象

若将两张表进行连接查询，且没有任何条件的限制，会发生笛卡尔现象最终查询结果的条数，是两张表条数的乘积（匹配次数也是）如何避免？连接时加上条件，满足这个条件的记录被筛选出来但匹配次数不会减少，加上条件只是为了避免笛卡尔积现象，并为了筛选出有效的记录例：select e.ename,d.dname from emp e, dept d where e.deptno = d.deptno（SQL92)这种写法可以提高效率，不会导致字段名在两个表中都找通过笛卡尔积现象可以得出，表的连接次数越多效率越低，需要尽量减少表的连接次数

9.4 内连接（把完全能够匹配这个条件的数据查询出来，表之间没有主次关系）

9.4.1 等值连接

案例：查询每个员工所在部门的名称，显示员工名和部门名？

​ emp e 和 dept d 表进行连接。条件是：e.deptno = d.deptno

SQL92语法：（结构不够清晰，表的连接条件和后期进一步筛选的条件，都放到了 where 子句中）

select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno;

SQL99语法：（表连接的条件是独立的，连接之后，如果还需要进一步筛选，再往后继续添加 where 子句）

select e.ename, d.dname from emp e (inner) join # 连接 dept d on e.deptno = d.deptno; #（连接条件） where ... # 筛选条件

9.4.2 非等值连接

select ... from ... inner join ... on ... #（非等值条件）

9.4.3 自连接

技巧：把一张表看成两张表

9.5 外连接（表之间有主次关系）

9.5.1 右连接

select ... from ... right (outer) join # 表示把 join 右边的这张表看成主表（主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表 ... on ... # 筛选条件

9.5.2 左连接

select ... from ... left (outer) join ... on ... # 筛选条件

思考：外连接的查询结果条数 >= 内连接的查询结果条数

9.6 多张表连接

select ...(a) from ... join ...(b) on ...(a 和 b 的连接条件) join ...(c) on ...(a 和 c 的连接条件 或 b 和 c 连接条件) ....... （一条 SQL 中内连接和外连接可以混合） SELECT stu.*,sc.c_id,sc.score,co.c_name,te.t_name FROM stu JOIN sc on stu.s_id=sc.s_id JOIN co on sc.c_id=co.c_id JOIN te on co.t_id=te.t_id;

10. 子查询

10.1 定义

​ select 语句中 嵌套 select 语句，被嵌套的 select 语句称为 子查询。

10.2 出现地方

select ...(select) # 此处子查询的结果只能返回一条结果，若多余一条则报错 from ...(select) # 可以将其查询结果当作一张临时表（调用派生表的时候需要定义别名） where ...(select)

11. union（合并结果集）

union 的效率要高一些（相对多表连接，每连接一次新表，则匹配的次数满足笛卡尔积，成倍的翻...，但是 union 可以减少匹配的次数，还可以完成两个结果集的拼接（乘法变成了加法））注意：union 进行结果集合并的时候，要求两个结果集的列数相同两个结果集的数据类型也需要相同（oracle），但 MySQL 中可以不相同select ... from ... where ... union select ... from ... where ...

12. limit

12.1 作用

​ 将查询结果集的一部分取出来。（通常使用在分页查询当中）

12.2 怎么用？

limit startIndex, length （起始下标，长度） 起始下标从 0 开始缺省用法：limit 5 这是取前 5

12.3 注意

limit 在 order by 之后执行

12.4 分页

limit (pageNo - 1) * pageSize, pageSize // (页号 - 1)*记录条数 记录条数

13. DQL 总结

select (5) ... from (1) ... where (2) ... group by (3) ... having (4) ... order by (6) ... limit (7) ...

DML

14. 表的创建（DDL）

建表属于 DDL 语句，DDL 包括：create drop alter

14.1 建表的语法格式

create table 表名 { 字段名1 数据类型 default ..., # default 可以指定该字段名的数据类型 字段名2 数据类型, 字段名3 数据类型, ... };

表名建议以 t_ 或者 tbl_ 开始，可读性强，见名知意。

字段名：见名知意

表名和字段名都属于标识符

14.2 MySQL 中的常见数据类型

varchar（最长 255）可变长度的字符串，比较智能，节省空间，会根据实际的数据长度动态分配空间优缺点优点：节省空间缺点：需要动态分配空间，速度慢char（最长 255）定长字符串，不管实际的数据长度是多少，分配固定长度的空间取存储数据，使用不恰当的时候，可能会导致空间的浪费优缺点优点：不需要动态分配空间，速度块缺点：使用不当可能会导致空间的浪费

varchar 和 char 一般根据实际情况进行选择，若字段固定长度一般选择 char，长度不一则选择 varchar

int（最长 11）数字中的整数型（ java中的 int）bigint数字中的长整型（java 中的 long）float单精度浮点型数据double双精度浮点型数据double (M,D) M：表示有效数字的最大位数 D：表示小数点后的位数date（不包括具体时间）短日期类型datetime（包括具体时间）长日期类型clob（Character Large Object）字符大对象，最多可以存储 4G 的字符串例如：存储一篇文章，一个说明超过 255 个字符的都要采用 CLOB 字符大对象来存储blob（Binary Large Object）二进制大对象专门用来存储图片、声音、视频等流媒体数据需要用 IO 流，来往 BLOB 类型的字段上插入数据

14.3 删除表

drop table 表名,...; # 当这张表不存在的时候会报错 drop table if exists 表名,...; # 如果这张表存在的话，删除(若后面存在已经被删除的表，会有警告，但其他表也会被删除)

14.4 表的复制

create table 表名1 as select *(字段...) from 表名2;

将一个查询结果当作一张表新建，实现表的快速复制（数据也同样拷贝了）

14.5 alter（修改表结构（及约束）（待补充！！！）

15. 插入数据 （DML）

15.1 语法格式

insert into 表名(字段名 1，字段名 2，字段名 3，...) value(值1，值2，值3，...) # 字段名和值 要一一对应（数量要对应，数据类型要对应） # 若字段名都省略的话，相当于都写上了 # 没有给其他字段指定值的话，默认值为 NULL （除非 创建表的时候指定了默认值）

15.2 插入多条数据

# 插入多条数据 insert into 表名(字段名 1，字段名 2，字段名 3，...) values(...),(...),(...),...

15.3 将查询结果插入表中

# 很少用 要求查询结果的结构和表的结构相同 insert into 表名1 (select * from 表名0);

15.4 format（数字格式化）

format(数字,格式); # select ename, format(sal, $999,999) as sal from emp; 显示千分位

15.5 str_to_date

​ 将字符串 varchar 类型转换成 date 类型

%Y 年(4位) %y 年(2位) %m 月 %d 日 %H 时(00-23) %h 时(00-12) %i 分 %s 秒 # insert into t_user(id,name,birth) values(2,jack,str_to_date(01-12-1990,%d-%m-%Y); str_to_date(字符串日期，日期格式) 注意：若提供的字符串日期格式为 %Y-%m-%d 则不需要 str_to_date 转换 # insert into t_user(id,name,birth) values(2,jack,1990-10-05); 常用于 insert 语句，因为插入的时候需要一个日期类型的数据

15.4 date_format

​ 将日期转换成字符串

date_format(日期类型数据,日期格式); 通常使用在查询日期方面，设置展示的日期格式 # 若不使用该函数，而直接查询 date 类型的数据 # SQL 会将日期进行默认的格式化：%Y-%m-%d

15.6 datetime

date 是短日期，仅含年月日信息 （默认格式：**%Y-%m-%d**）datetime 是长日期，含有年月日 时分秒信息（默认格式：**%Y-%m-%d %H:%i:%s**）

15.7 now()

​ 可以获取当前系统的时间，并且获取的时间是 datetime 类型的

16. 修改数据

update 表名 set 字段名1 = 值1，字段名2 = 值2，字段名3 = 值3 ... where 条件;

注意：若没有条件限制将会导致所有数据全部更新。

17. 删除数据

17.1 语法格式

delete from 表名 where ...; # (DML) # 表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放 # 缺点：删除效率比较低 # 优点：支持回滚(start transaction -> ... -> rollback)，后悔了可以再恢复数据！！！

注意：若没有条件，会删除整张表的数据。

17.2 快速删除表中的数据

truncate table 表名; # (DDL) # 删除效率比较高，表被一次截断，物理删除 # 优点：不支持回滚 # 缺点：快速

17.3 对表结构的增删改（DDL 数据定义语言）

create drop alter什么是对表结构的修改？添加一个字段删除一个字段修改一个字段

约束

1. 定义

​ constraint

在创建表的时候，可以给表中的字段加上一些约束，来保证这个表中数据的完整性、有效性！作用：保证表中的数据有效！

2. 常见的约束

非空约束：not null唯一性约束：unique主键约束：primary key（简称 PK）外键约束：foreign key（简称 FK）检查约束：check （mysql 不支持，oracle 支持）

3. 非空约束

​ not null 约束的字段不能为 NULL （只有列级约束）

ERROR 1364 (HY000): Field name doesnt have a default value # 若让 not null 约束的字段为空，则会报该错误

4. 唯一性约束

​ unique 约束的字段不能重复，但是可以为 NULL

ERROR 1062 (23000): Duplicate entry lisa for key t_user2.name # 若让 unique 约束的字段重复，则会报该错误 # 但若有多个 NULL，不是表示重复，因为 NULL 表示什么都没有 create table 表名 ( 字段名1 类型1 unique, # 列级约束 字段名2 类型2 unique, # 列级约束 ); create table 表名( 字段名1 类型1, 字段名2 类型2, unique(字段名1,字段名2) # 表级约束 需要给多个字段联合起来添加某一个约束的时候使用 );

5. not null 和 unique 联合使用

create table 表名( 字段名1 类型1 not null unique, # mysql 当中，这样使用会使得该字段自动变成主键 PRIMARY KEY (oracle 不一样) 字段名2 类型2 );

6. 主键约束

​ primary key

6.1 相关术语

主键约束：一种约束主键字段：该字段上添加了主键约束，该字段就叫做主键字段主键值：主键字段中的每一个值都叫做主键值

6.2 作用

主键值是每一行记录的唯一标识（身份证号）任何一张表都应该有主键（否则无效）

6.3 特征

not null + unique （主键值不能为 NULL，同时也不能重复）可以使用表级约束create table 表名( id int, name varchar(255), # primary key(id) 单一主键 primary key(id,name) # 复合主键！！！（开发中不建议使用） # 表级约束 若包含两个字段，则表示两者联合判断主键（这样算一个主键） ); 一张表，主键约束只能添加 1 个主键值（一般都是数字，或者定长的）建议使用：int bigint char不建议使用：varchar

6.4 分类

自然主键主键值是一个自然数，和业务没关系业务主键主键值和业务紧密关联实际开发中使用自然主键多，还是业务主键多？自然主键使用比较多，因为主键只要做到不重复就行，不需要有意义，业务主键不好，因为主键一旦和业务挂钩，那么当业务发生变动的时候，可能会影响到主键值，所以尽量使用自然主键

6.5 自增

create table 表名( id int(11) primary key auto_increment, # 主键自增 从 1 开始 # 每次在上一个记录的基础上 加1 name varchar(255) );

7. 外键约束

​ foreign key

7.1 相关术语

外键约束：一种约束（foreign key）外键字段：该字段上添加了外键约束外键值：外键字段当中的每一个值

7.2 案例

业务背景​ 设计数据库表，来描述“班级和学生”的信息：# 方案一： t_student no(pk) name classno classname ----------------------------------------------------- 1 lisi 101 上海理工大学计科一班 2 zhangsan 101 上海理工大学计科一班 3 wangwu 102 上海理工大学计科二班 4 zhaoliu 102 上海理工大学计科二班 5 qing 103 上海理工大学计科三班 缺点：数据冗余，空间浪费 # 方案二： t_class（父表） classno(pk) classname ------------------------------------- 101 上海理工大学计科一班 102 上海理工大学计科二班 103 上海理工大学计科三班 t_student(子表) no(pk) name cno(fk) ---------------------------------- 1 lisi 101 2 zhangsan 101 3 wangwu 102 4 zhaoliu 102 5 qing 103 drop table if exists t_student; # 先删除子表 drop table if exists t_class; # 在删除父表 create table t_class( # 先创建父表 classno int(11) primary key, classname varchar(255) )engine = InnoDB default charset = gbk; create table t_student( # 再创建子表 no int(11) primary key, name varchar(255), cno int(11), foreign key(cno) references t_class(classno) # 添加了外键约束 表名：这个字段的值只能取自父表的对应字段的值（可以为NULL） )engine=InnoDB default charset=gbk; # 先插入父表数据 insert into t_class values(101, 上海理工大学计科一班); insert into t_class values(102, 上海理工大学计科二班); insert into t_class values(103, 上海理工大学计科三班); # 再插入子表数据 insert into t_student values(1, lisi, 101); insert into t_student values(2, zhangsan, 101); insert into t_student values(3, wangwu, 102); insert into t_student values(4, zhaoliu, 102); insert into t_student values(5, qing, 103); select classno,classname from t_class; select no,name,cno from t_student; 解决中文乱码**set names gbk; **相当于是告诉 MySQL 服务器软件，我们在当前命令行下输入的内容是GBK编码。当命令窗口关闭或退出 MySQL 服务后，它再输入中文就会出现问题注意外键引用的父表中字段不一定是主键，但要求是 unique 的外键可以为 NULL

存储引擎

1. 定义

存储引擎是 MySQL 中特有的一个术语，其他数据库中没有（Oracle中有，但不叫这个名字）实际上存储引擎是一个表存储/组织数据的方式不同的存储引擎，表存储数据的方式不同create table t_product( id int(11) primary key, name varchar(255) )engine = InnoDB default charset = utf8; # 指定存储引擎 和 默认字符编码方式（MySQL默认字符集为 utf8）

2. 查看 MySQL 支持的存储引擎

show engines \G mysql> show engines \G *************************** 1. row *************************** Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary tables Transactions: NO XA: NO Savepoints: NO *************************** 2. row *************************** Engine: MRG_MYISAM Support: YES Comment: Collection of identical MyISAM tables Transactions: NO XA: NO Savepoints: NO *************************** 3. row *************************** Engine: CSV Support: YES Comment: CSV storage engine Transactions: NO XA: NO Savepoints: NO *************************** 4. row *************************** Engine: FEDERATED Support: NO Comment: Federated MySQL storage engine Transactions: NULL XA: NULL Savepoints: NULL *************************** 5. row *************************** Engine: PERFORMANCE_SCHEMA Support: YES Comment: Performance Schema Transactions: NO XA: NO Savepoints: NO *************************** 6. row *************************** Engine: MyISAM Support: YES Comment: MyISAM storage engine Transactions: NO XA: NO Savepoints: NO *************************** 7. row *************************** Engine: InnoDB Support: DEFAULT Comment: Supports transactions, row-level locking, and foreign keys Transactions: YES XA: YES Savepoints: YES *************************** 8. row *************************** Engine: BLACKHOLE Support: YES Comment: /dev/null storage engine (anything you write to it disappears) Transactions: NO XA: NO Savepoints: NO *************************** 9. row *************************** Engine: ARCHIVE Support: YES Comment: Archive storage engine Transactions: NO XA: NO Savepoints: NO # 版本不同 支持情况不同 show engines \g # 表格形式

3. MySQL 常用的存储引擎

3.1 MyISAM

使用三个文件表示每个表格式文件 - 存储表结构的定义（mytable.frm)数据文件 - 存储表行的内容（mytable.MYD)索引文件 - 存储表上的索引（mytable.MYI）索引是一本书的目录，缩小扫描范围，提高查询效率的一种机制对于一张表来说，只要是主键，或者加有 unique 约束的字段上会自动创建索引MyISAM 存储引擎特点可被转换为压缩、只读表来节省空间但不支持事务机制，安全性低

3.2 InnoDB

MySQL 默认的存储引擎，同时也是一个重量级的存储引擎InnoDB 管理的表具有下列主要特征：每个 InnoDB 表在数据库目录中以 .frm 格式文件表示InnoDB 表空间 tablespace 被用于存储表的内容（表空间是一个逻辑名称，表空间用于存储数据+索引）提供一组用来记录事务性活动的日志文件用 COMMIT（提交）、SAVAPOINT 及 ROLLBACK（回滚）支持事务处理提供全 ACID 兼容在 MySQL 服务器崩溃后提供自动恢复多版本（MVCC）和行级锁定支持外键及引用的完整性，包括级联删除和更新InnoDB 存储引擎特点支持事务，以保证数据的安全效率不是很高，并且不能压缩，不能转换为只读表，不能很好的节省空间

3.3 MEMORY

使用 MEMORY 存储引擎的表，其数据存储在内存中，且行的长度固定MEMORY 存储引擎管理的表具有下列特征：在数据库目录内，每个表均以 .frm 格式的文件表示表数据及索引被存储在内存中（目的就是查询快！）表级锁机制不能包含 TEXT 或 BLOB 字段MEMORY 存储引擎以前被称为 HEAP 引擎优缺点：优点：查询效率是最高的，不需要和硬盘交互缺点：不安全，关机之后数据消失。因为数据和索引都是在内存当中内存中的数据是通过电容这种电子元件在承载，电容充放电都是需要时间的，所以可以大胆猜测，即使断电了，内存中的数据全部消失也是需要时间的

事务

transaction

1. 概述

一个事务就是一个完整的业务逻辑。是一个最小的工作单元，不可再分。

2. DML 语句与事务

只有 DML 语句才有事务这一说insertdeleteupdate只要你的操作一旦涉及到数据的增、删、改，那么就一定要考虑安全问题

3. 思考

假设所有的业务，只要一条 DML 语句就能完成，还有必要存在事务机制吗？正是因为做某件事的时候，需要多条 DML 语句共同联合起来才能完成，所以需要事务的存在。如果任何一件复杂的事情都能用一条 DML 语句搞定，那么事务则没有存在的价值了事务本质上就是批量的 DML 语句同时成功，或者同时失败！

4. 如何做

InnoDB 存储引擎：提供一组用来记录事务性活动的日志文件事务开启了： insert insert delete insert update update 事务结束了！ 在事务的执行过程中，每一条 DML 的操作都会记录到 “事务性活动的日志文件” 中。在事务的执行过程中，我们可以提交事务，也可以回滚事务提交事务清空事务性活动的日志文件，将数据全部彻底持久化到数据表中提交事务标志着事务的结束，并且是一种全部成功的结束回滚事务将之前所有的 DDL 操作全部撤销，并且清空事务性活动的日志文件回滚事务标志着事务的结束，并且是一种全部失败的结束

4. 具体实现

提交事务：commit; 语句回滚事务：rollback; 语句（回滚永远都是只能回滚到上一次的提交点！）注意：在 MySQL 当中默认的事务行为是怎样的？MySQL 默认情况下是支持自动提交事务的即每执行一条 DML 语句，则提交一次（这种自动提交的方式是不符合开发习惯的，因为一个业务通常是需要多条 DML 语句共同执行才能完成的，为了保证数据的安全，必须要求同时成功后再提交，而不能执行一条就提交一条）如何关闭？ start transaction;start transaction; delete ... insert ... update ... roll back; # 使得上述 DML 命令全部失败 (commit; # 使得上述 DML 命令全部成功) 均清空事务性活动的日志文件，标志着事务的结束

5. 四个特性

A ：原子性事务是最小的工作单元，不可再分C：一致性在同一个事务当中，所有操作必须同时成功，或者同时失败，以保证数据的一致性I：隔离性A 事务 和 B 事务之间具有一定的隔离D：持久性事务最终结束的一个保障：事务提交，就相当于将没有保存到硬盘上的数据保存到硬盘上

6. 四个隔离级别

6.1 隔离级别

读未提交 read uncommitted（最低的隔离级别）事务 A 可以读取到事务 B未提交的数据存在的问题：脏读现象（Dirty Read）—— 读到了脏数据这种隔离级别一般都是理论上的，大多数的数据库隔离级别都是二档起步读已提交 read committed事务 A只能读取到事务 B提交之后的数据解决了什么问题：解决了脏读的现象存在的问题：不可重复读取数据在事务开启之后，第一次读到的数据是 3 条，当前事务还没有结束，第二次再去读取，可能读到的数据变成 4 条，3 不等于 4 称为不可重复读取数据这种隔离级别是比较真是的数据，每一次读到的数据是绝对的真实Oracle 数据库默认的隔离级别是：read committed可重复读repeatable read提交之后也读不到，永远读取的都是刚开启事务时的数据可重复读取不管是多久，每一次在事务 A 中读取到的数据都是已知的，即使事务 B 将数据已经修改，并且提交了，事务 A 读取到的数据还是没有发生改变。解决了什么问题：解决了不可重复读取数据存在的问题：可能会出现幻影读每一次读取到的数据都是幻象，不真实MySQL 中默认的事务隔离级别就是这个！序列化/串行化 serializable（最高的隔离级别）效率最低，解决了所有的问题表示事务排队，不能并发！类似于 synchronized 线程同步（事务同步）每一次读取到的数据都是最真是的，并且效率是最低的

6.2 验证

查看事务隔离级别MySQL 8.0+ select @@transaction_isolation; # 查看当前会话隔离级别 select @@global.transaction_isolation; # 查看系统当前隔离级别 MySQL 5.0+ select @@tx_isolation; # 查看当前会话隔离级别 select @@global.tx_isolation; # 查看系统当前隔离级别 设置事务隔离级别# 设置全局事务隔离级别 set global transaction isolation level ...; # 设置当前会话事务隔离级别 set session transaction isolation level ...;

6.3 注意

MySQL 在单个事务中 select 的查询结果可以看到临时的变化 例如：start transaction 会取消自动提交 例如： start transaction insert into t_user2 values(1,2,a); select * from t_user2; # 此时 可以看到查询结果中有了数据（但这个数据只是临时的）， # 但实际上没有在没有 commit 或 rollback 的情况下话，实际表中数据未变化 commit; # 此时 实际表才有数据 事务结束（DML 语句全部成功），清空事务性活动的日志文件，并将数据彻底持久化在表中 若存在其他事务，使用 select 还需要根据 事务隔离级别 做具体变换 MySQL 中 若连续使用两次 start transaction，第一次 不 commit 也不 rollback 例如： start transaction; insert into t_user2 values(1,2,a); start transaction; insert into t_user2 values(2,3,b); select * from t_user2; # 此时 两个操作后的结果都能看到 commit; # 将 第一个操作后的结果 也更新到表中了

索引

1. 定义

索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制一张表的一个字段可以添加一个索引，也可以多个字段联合起来添加索引索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制MySQL 在查询方面主要是两种方式：全表扫描根据索引检索注意：在 MySQL 当中索引是需要排序的，底层是一个 B-Tree 数据结构遵循左小右大原则存放，采用中序遍历方式遍历取数据

2. 实现原理

在任何数据库当中主键上都会自动添加索引对象，另外在 MySQL 当中，一个字段上如果有 unique 约束的话，也会自动创建索引对象在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号在 MySQL 当中，索引是一个单独的对象，不同的存储引擎以不同的形式存在在 MyISAM 存储引擎中，索引存储在一个 .MYI 文件中在 InnoDB 存储引擎中，索引存储在一个逻辑名称叫做 tablespace 的表空间当中在 MEMORY 存储引擎中，索引存储在内存当中不管索引存储在哪里，索引在 MySQL 当中都是以一个树的形式存在（自平衡二叉树：B-Tree）select * from t_user where id = 101; # MySQL 发现 id 字段上有索引对象，所以会通过索引对象 idIndex 进行查找 # 通过 101 得到物理编号：0x6666，此时马上 SQL 语句转换 select * from t_user where 物理编号 = 0x6666;

实现原理：缩小扫描的范围（形成树），避免全表扫描

3. 使用条件

数据量庞大该字段经常出现在 where 后面，以条件的形式存在，即该字段总是被扫描该字段有很少的 DML 操作（因为 DML 之后，索引需要重新排序）

4. 建议

建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。建议通过主键查询 或者 通过 unique 约束的字段进行查询，效率比较高

5. 语法

# 创建索引 create index 索引名(自己起) on 表名(字段名); # 删除索引 drop index 索引名(已存在) on 表名; # 查询是否采用索引 explain select 字段 from 表名 where 字段名 ... ;

6. 失效

select * from emp where ename like %T

;

# ename 上即使添加了索引，也不会走索引？# 因为模糊匹配当中以 % 开头了，就会走模糊匹配的路线去查询（全表查询，需要知道第一个字母）# 尽量避免模糊查询的时候以 % 开头（优化策略）select * from emp where ename = KING or job = MANAGER

;

# 如果 or 两边有一个字段没有索引，那么另一个字段上的索引就会失效create index emp_job_sal_index on

emp(job,sal);

# 复合索引：两个字段或者更多的字段联合起来添加一个索引# 若使用复合索引的时候，没有使用左侧的列查找，索引就会失效create index emp_sal_index on

emp(sal);

select * from emp where sal+1 = 800

;

# 在 where 中，索引列若参加了运算，索引失效select * from emp where lower(ename) = smith

;

# 若在 where 当中索引列使用了函数，索引会失效# 若使用 类型转换 也会失效

7. 分类

单一索引一个字段上添加索引复合索引两个字段或者更多的字段上添加索引主键索引主键上添加索引唯一性索引具有 unique 约束的字段上添加索引注意：唯一性比较弱的字段上添加索引用处不大

视图

1. 定义

view：站在不同的角度去看待同一份数据

2. 语法

# 创建视图对象 create view 视图名(自己起) as select ...; # 删除视图对象 drop view 视图名(已存在) # 注意： 只有 DQL 语句才能以 view 的形式创建 create view 视图名 as DQL语句;

3. 用处

方便，简化开发，利于维护可以将一条复杂的 DQL 语句以视图对象的形式新建，可以大大地简化开发，并且利于后期的维护，因为修改的时候也只需要修改一个位置，只需要修改视图对象所映射的 SQL 语句使用视图可以像使用 table 一样，对视图的 CRUD 也会更新到原表当中视图不是在内存当中，视图对象也是以文件形式存储在硬盘上的，不会消失

4. 注意

还有一些特定的其他结构，这些结构会使得视图不可更新。更具体地讲，如果视图包含以下结构中的任何一种，它就是不可更新的： 1. 聚合函数 SUM()、MIN()、MAX()、COUNT() 等。 2. DISTINCT 关键字。 3. GROUP BY 子句。 4. HAVING 子句。 5. UNION 或 UNION ALL 运算符。 6. 位于选择列表中的子查询。 7. FROM 子句中的不可更新视图或包含多个表。 8. WHERE 子句中的子查询，引用 FROM 子句中的表。 9. ALGORITHM 选项为 TEMPTABLE（使用临时表总会使视图成为不可更新的）的时候。 ERROR 1471 (HY000): The target table t_user2_view of the INSERT is not insertable-into ERROR 1288 (HY000): The target table t_user2_view of the DELETE is not updatable

DBA

1. 定义

Database Administrator 数据库管理员

2. 数据的导出和导入

导出在 windows 的 dos 命令窗口中：# 导出数据库

mysqldump 数据库名>文件路径名 -uroot -p

# 导出数据库当中的指定表

mysqldump 表名>文件的路径名 -uroot -p

Enter password:xxxxxxx导入需要先登录到 MySQL 数据库服务器上然后创建数据库create database

数据库名;

使用数据库use

数据库名;

初始化数据source 文件路径名;

3. 其他常用 DBA 命令（待补充）

数据库设计三范式

1. 定义

​ 数据库表的设计依据。教你怎么进行数据库表的设计。

2. 数据库设计三范式

设计数据库表的时候，按照以上的范式进行，可以避免表中数据的冗余，空间的浪费

2.1 第一范式

要求任何一张表必须有主键，每一个字段原子性不可再分最核心，最重要的范式，所有表的设计都需要满足。

学生编号 学生姓名 联系方式

--------------------------------------------

1001 张三 zs@123.com,13599999999

1002 李四 ls@123.com,15999999999

1003 王五 ww@123.com,13588888888

以上是学生表，不满足第一范式

# 第一：没有主键# 第二：联系方式可以分为邮箱地址和电话号码

故更改为：

学生编号 学生姓名 邮箱地址 电话号码

--------------------------------------------

1001 张三 zs@123.com 13599999999

1002 李四 ls@123.com 15999999999

1003 王五 ww@123.com 13588888888

2.2 第二范式

建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖

学生编号 学生姓名 教师编号 教师姓名

----------------------------------------

1001 张三 001 王老师

1002 李四 002 赵老师

1003 王五 001 王老师

1001 张三 002 赵老师

# 多对多关系

这张表描述了学生和老师的关系（1个学生可能有多个老师，1个老师也可能有多个学生）

不满足第一范式？修改：

学生编号+教师编号（PK） 学生姓名 教师姓名

----------------------------------------

1001 001 张三 王老师

1002 002 李四 赵老师

1003 001 王五 王老师

1001 002 张三 赵老师

学生编号和教师编号，两个字段联合做主键，满足了第一范式，但是不满足第二范式！

# ‘张三’ 依赖 1001，‘王老师’ 依赖 001，产生了部分依赖：# 缺点：数据冗余，空间浪费。‘张三’重复，‘王老师’重复，‘赵老师’重复

修改：使用三张表来表示多对多的关系！！！

学生表：

学生编号（PK） 学生姓名

-------------------------

1001 张三

1002 李四

1003 王五

教师表：

教师编号（PK） 教师姓名

--------------------------

001 王老师

002 赵老师

学生教师关系表

id（PK） 学生编号（PK） 教师编号（PK）

--------------------------------------

1 1001 001

2 1002 002

3 1003 001

4 1001 002

口诀：多对多，三张表，关系表两个外键！！！

2.3 第三范式

建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖

学生编号（PK） 学生姓名 班级编号 班级名称

---------------------------------------------

1001 张三 01 一年一班

1002 李四 02 一年二班

1003 王五 03 一年三班

1004 赵六 03 一年三班

# 一对多关系

以上表的设计是描述：班级和学生的关系（一个班级可以有多个学生）

1. 满足第一范式：有主键

2. 满足第二范式：主键不是复合主键，没有产生部分依赖。主键是单一主键

3. 是否满足第三范式？

不满足！ ‘一年一班’ 依赖 ‘01’，‘01’ 依赖 ‘1001’，产生了传递依赖

修改：

班级表：

班级编号（PK） 班级名称

-------------------------

01 一年一班

02 一年二班

03 一年三班

学生表：

学生编号（PK） 学生姓名 班级编号（FK）

---------------------------------------

1001 张三 01

1002 李四 02

1003 王五 03

1004 赵六 03口诀：一对多，两张表，多的表加外键！

3. 总结表的设计

一对多一对多，两张表，多的表加外键！多对多多对多，三张表，关系表两个外键！一对一在实际的开发中，可能存在一张表字段太多，过于庞大，这时候就需要拆分表

t_user

id login_name login_pwd real_name email address ...

---------------------------------------------------------------------

1 zhangsan 123 张三 zs@123.com

2 lisi 567 李四 lisi@123.com

...

这样庞大的表建议拆分为两张：

t_login 登录信息表

id(PK) login_name login_pwd

-------------------------------

1 zhangsan 123

2 lisi 567

t_user 用户详细信息表

id(PK) real_name email address... login_id(FK+unique)

-----------------------------------------------------------------------

100 张三 zs@123.com 1

200 李四 lisi@123.com 2

口诀：一对一，外键唯一！

4. 注意

数据库设计三范式是理论上的实践和理论有的时候是有偏差的最终的目的都是为了满足客户的需求，有的时候会拿冗余换执行速度因为在 sql 中，表和表之间连接次数越多，效率越低（笛卡尔积）有的时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的，并且对于开发人员来说，sql 语句的编写难度也会降低

免费领取有关于java面试题材料和讲解！