数据库知识整理

数据库

---

SQL的语言分类

DQL（Data Query Language）：数据查询语言——select
DML(Data Manipulate Language):数据操作语言——insert 、update、delete
DDL（Data Define Languge）：数据定义语言——create、drop、alter
TCL（Transaction Control Language）：事务控制语言——commit、rollback

---

查询

语法：
select 查询列表    ⑦
from 表1 别名       ①
连接类型 join 表2   ②
on 连接条件         ③
where 筛选          ④
group by 分组列表   ⑤
having 筛选         ⑥
order by排序列表    ⑧
limit 起始条目索引，条目数;  ⑨

从from（表）
where（按条件取出数据）
goup by（再对取出的数据进行分组）
having（分组之后再过滤得到最新数据集）
select（按照设置列从数据集里面取出数据）
order by（对取出的数据进行排序）

执行顺序：from—where–group by—having—select—order by

limit 子句，限制结果数量子句
distinct 去除重复记录
默认为 all, 全部记录

---

多表连接查询

通过join关键字实现多表连接

	select 字段，...
	from 表1
	【inner|left outer|right outer|cross】join 表2 on  连接条件
	【inner|left outer|right outer|cross】join 表3 on  连接条件
	【where 筛选条件】
	【group by 分组字段】
	【having 分组后的筛选条件】
	【order by 排序的字段或表达式】

内连接、自连接、外连接（左、右、全）、交叉连接
内连接：只有两个元素表相匹配的才能在结果集中显示。

外连接：
左外连接:左边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。
右外连接:右边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。
全外连接：连接的表中不匹配的数据全部会显示出来。

交叉连接： 笛卡尔效应，显示的结果是链接表数的乘积。

---

子查询和联合查询

子查询或内查询： 嵌套在其他语句内部的select语句

select last_name
from employees
where employee_id in(
	select manager_id
	from employees
);

union：合并、联合，将多次查询结果合并成一个结果
UNION 和 UNION ALL 操作符

// 默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

//  UNION ALL 语法：  结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

---

DML数据操作语言——insert 、update、delete

插入：

insert into 表名(字段名,...) values(值,...);
insert into 表名 set 字段=值,字段=值,...;

修改

1，修改单表的记录 
语法：update 表名 set 字段=值,字段=值 【where 筛选条件】

2，修改多表的记录
语法：
update 表1 别名 
left|right|inner join 表2 别名 
on 连接条件  
set 字段=值,字段=值 
【where 筛选条件】;

删除

方式一：使用delete
一、删除单表的记录★
语法：delete from 表名 【where 筛选条件】【limit 条目数】

二、级联删除
语法：
delete 别名1,别名2 from 表1 别名 
inner|left|right join 表2 别名 
on 连接条件
 【where 筛选条件】

方式二：使用truncate
语法：truncate table 表名

delete 和 truncate 区别

1.truncate删除后，如果再插入，标识列从1开始
delete删除后，如果再插入，标识列从断点开始
2.delete可以添加筛选条件； truncate不可以添加筛选条件
3.truncate效率较高；delete逐行删除
4.truncate没有返回值；delete可以返回受影响的行数
5.truncate不可以回滚；delete可以回滚

不再需要一张表的时候，用drop
想删除部分数据行时候，用delete，并且带上where子句
保留表而删除所有数据的时候用truncate

---

DDL数据定义语言——create、drop、alter

库的管理

创建库
create database 【if not exists】 库名【 character set 字符集名】;

修改库
alter database 库名 character set 字符集名;

删除库
drop database 【if exists】 库名;

表的管理

一、创建表 ★
create table 【if not exists】 表名(
	字段名 字段类型 【约束】,
	字段名 字段类型 【约束】,
	。。。
	字段名 字段类型 【约束】 
)

二、修改表

1.添加列
alter table 表名 add column 列名 类型 【first|after 字段名】;
2.修改列的类型或约束
alter table 表名 modify column 列名 新类型 【新约束】;
3.修改列名
alter table 表名 change column 旧列名 新列名 类型;
4 .删除列
alter table 表名 drop column 列名;
5.修改表名
alter table 表名 rename 【to】 新表名;

三、删除表
drop table【if exists】 表名;

四、复制表
1、复制表的结构
create table 表名 like 旧表;
2、复制表的结构+数据
create table 表名 
select 查询列表 from 旧表【where 筛选】;

---

TCL 事务

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。

(1). 事务的特征
原子性(Atomicity)：事务所包含的一系列数据库操作要么全部成功执行，要么全部回滚；
一致性(Consistency)：事务的执行结果必须使数据库从一个一致性状态到另一个一致性状态；
隔离性(Isolation)：并发执行的事务之间不能相互影响；
持久性(Durability)：事务一旦提交，对数据库中数据的改变是永久性的。

(2). 事务并发带来的问题
脏读：一个事务读取了另一个事务未提交的数据；
不可重复读：不可重复读的重点是修改，同样条件下两次读取结果不同，也就是说，被读取的数据可以被其它事务修改；
幻读：幻读的重点在于新增或者删除，同样条件下两次读出来的记录数不一样

4、隔离级别

四种隔离级别
Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。
Repeatable read (可重复读)：可避免脏读、不可重复读的发生。
Read committed (读已提交)：可避免脏读的发生。
Read uncommitted (读未提交)：最低级别，任何情况都无法保证。

							脏读	 			不可重复读		幻读
read uncommitted:读未提交     ×                ×              ×        
read committed：读已提交      √                ×              ×
repeatable read：可重复读     √                √              ×
serializable：串行化          √                √              √

---

视图

视图：理解成一张虚拟的表，它的数据来自于表，通过执行时动态生成。

		关键字		是否占用物理空间				使用
视图		view		占用较小，只保存sql逻辑		一般用于查询
表		table		保存实际的数据				增删改查

二、创建

# 创建视图
CREATE VIEW v AS SELECT id, name FROM n;
CREATE VIEW v(id, name) AS SELECT id, name FROM n;
# 查看视图(与表操作类似)
SELECT * FROM v;
DESC v;
# 查看创建视图语句
SHOW CREATE VIEW v;
# 更改视图
CREATE OR REPLACE VIEW v AS SELECT name, age FROM n;
ALTER VIEW v AS SELECT name FROM n ;
# 删除视图
DROP VIEW IF EXISTS v;

---

范式

第一范式（1NF）：属性（字段）是最小单位不可再分。
第二范式（2NF）：满足 1NF，每个非主属性完全依赖于主键（消除 1NF 非主属性对码的部分函数依赖）。
第三范式（3NF）：满足 2NF，任何非主属性不依赖于其他非主属性（消除 2NF 非主属性对码的传递函数依赖）。就是数据只在一个地方存储，不重复出现在多张表中，可以认为就是消除传递依赖
鲍依斯-科得范式（BCNF）：满足 3NF，任何非主属性不能对主键子集依赖（消除 3NF 主属性对码的部分和传递函数依赖）。
第四范式（4NF）：满足 3NF，属性之间不能有非平凡且非函数依赖的多值依赖（消除 3NF 非平凡且非函数依赖的多值依赖）。

---

乐观锁和悲观锁

确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性，乐观锁和悲观锁是并发控制主要采用的技术手段。

• 悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作
  • 在查询完数据的时候就把事务锁起来，直到提交事务
  • 实现方式：使用数据库中的锁机制
    
• 乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。
  • 在修改数据的时候把事务锁起来，通过version的方式来进行锁定
  • 实现方式：使用version版本或者时间戳
    

---

---

---

---

---