数据库系统学习

关系型数据库

关系型数据库建立在关系模型基础上的数据库，关系型数据库是由多张能相互相连的二维表组成的数据库
优点：

1. 都是使用表结构，格式一致，易于维护
2. 使用通用的sql语言操作，使用方便，可用于复杂查询
3. 数据存储在磁盘中，安全

结构化查询语言，一门操作关系型数据库的编程语言
定义操作所有关系型数据库的统一标准

sql分类

1. DDL(Data Definition Language）数据库定义语言，用来定义数据库对象：数据库，表、列等
2. DML(Data Manipulation Language)数据库操作语言，用来对数据库中的表数据进行增删改查
3. DQL(Data Query Language)数据查询语言，用来查询数据库中表的记录（数据）
4. DCL(Data Control Language)数据库控制语言，用来定义数据库的访问权限和安全级别，及创建用户

DDL-操作数据库

1. 查看Mysql中 都有哪些数据库

SHOW DATABASES;

使用数据库

USE 数据库名称;

查看当前正在使用的数据库

select database();

查看一个数据库的定义信息

show create database 数据库名；

2. 创建数据库

CREATE DATABASES 数据库名称;

创建数据库（判断，如果不存在则创建）

CREATE DATABASE IF NOT EXISTS 数据库名称;

创建指定名称的数据库，并且指定字符集（一般都指定
utf-8）

create database 数据库名 character set 字符
集；

3. 删除数据库

DROP DATABASE 数据库名称

删除数据库（判断，如果存在则删除）

DROP DATABASE IF EXISTS 数据库名称

4. 修改数据库
   数据库的字符集修改操作

alter database 数据库名 character set 字符集；

DDL-操作表

• 创建create
• 查询retrieve
• 修改update
• 删除delete

查询表

• 查询当前数据库下所有的表名称

show tables；

• 查询表结构

desc 表名称；

创建表

• 创建表

create table 表名(
		字段名1 数据类型1，
		字段名2 数据类型2
）；

查看表

• 查看当前数据库中的所有表名

show tables;

• 查看数据表的结构

desc 表名；

删除表

• 删除表

drop table 表名；

• 删除表同时判断表是否存在

drop table if exists 表名；

修改表

• 修改表名

alter table 表名 rename to 新的表名；

• 添加一列

alter table 表名 add 列名 数据类型； 

• 修改数据类型

alter table 表名 modify 列名 新数据类型；

• 修改列名和数据类型

alter table 表名 change 列名 新列名 新数据类型；

• 修改表的字符集

alter table 表名 character set 字符集

• 删除一列

alter table 表名 drop 列名;

数据类型

mysql支持多种类型，可以分为三类：

• 数值
• 日期
• 字符串

DML操作表

插入数据

insert into 表名 （字段名1，字段名2...） values(字段值1，字段值2...);

插入指定字段

INSERT INTO category (cname) VALUES('白骨精');

更改数据

不带条件的修改

update 表名 set 列名 = 值

带条件的修改

update 表名 set 列名 = 值 [where 条件表达式：字段名 = 值 ]

删除数据

删除所有数据

delete from 表名

指定条件 删除数据

delete from 表名 [where 字段名 = 值]

DQL 查询表中数据

简单查询

select 列名 from 表名

条件查询

select 列名 from 表名 where 条件表达式

比较运算符

逻辑运算符

模糊查询 通配符

DQL操作单表

排序

SELECT 字段名 FROM 表名 [WHERE 字段 = 值] ORDER BY 字段名 [ASC / DESC]
-- 组合排序
SELECT * FROM emp ORDER BY salary DESC, eid DESC;

聚合函数

分组

分组查询指的是使用 GROUP BY 语句,对查询的信息进行分组,相同数据作为一组

分组时可以查询要分组的字段, 或者使用聚合函数进行统计操作.

SELECT 分组字段/聚合函数 FROM 表名 GROUP BY 分组字段 [HAVING 条件];

#1. 查询有几个部门
SELECT dept_name AS '部门名称' FROM emp GROUP BY dept_name;
#2.查询每个部门的平均薪资
SELECT
dept_name AS '部门名称',
AVG(salary) AS '平均薪资'
FROM emp GROUP BY dept_name;

#3.查询每个部门的平均薪资, 部门名称不能为null
SELECT dept_name AS '部门名称',AVG(salary) AS '平均薪资'
FROM emp WHERE dept_name IS NOT NULL GROUP BY dept_name;

having使用：

1. 需要在分组后,对数据进行过滤,使用 关键字 having
2. 分组操作中的having子语句，是用于在分组后对数据进行过滤的，作用类似于where条件。

# 查询平均薪资大于6000的部门
-- 需要在分组后再次进行过滤,使用 having
SELECT
dept_name ,
AVG(salary)
FROM emp WHERE dept_name IS NOT NULL GROUP BY dept_name HAVING AVG(salary) > 6000 ;

having与where的区别

limit关键字

limit 关键字的作用

• limit是限制的意思,用于限制返回的查询结果的行数 (可以通过limit指定查询多少行数据)
• limit 语法是 MySql的方言,用来完成分页

SELECT 字段1,字段2... FROM 表名 LIMIT offset , length;

limit offset , length; 关键字可以接受一个 或者两个 为0 或者正整数的参数
offset 起始行数, 从0开始记数, 如果省略 则默认为 0
length 返回的行数

# 查询emp表中的前5条数据
-- 参数1 起始值,默认是0 , 参数2 要查询的条数
SELECT * FROM emp LIMIT 5;
SELECT * FROM emp LIMIT 0 , 5;
# 查询emp表中 从第4条开始,查询6条
-- 起始值默认是从0开始的.
SELECT * FROM emp LIMIT 3 , 6;

分页操作

-- 分页操作 每页显示3条数据
SELECT * FROM emp LIMIT 0,3; -- 第1页
SELECT * FROM emp LIMIT 3,3; -- 第2页 2-1=1 1*3=3
SELECT * FROM emp LIMIT 6,3; -- 第三页
-- 分页公式 起始索引 = (当前页 - 1) * 每页条数
-- limit是MySql中的方言

sql约束

1. 约束的作用: 对表中的数据进行进一步的限制，从而保证数据的正确性、有效性、完整性. 违反约束的不正确数据,将无法插入到表中
2. 常见的约束

主键约束

不可重复、唯一、非空

添加主键约束

字段名 字段类型 primary key

删除主键约束

ALTER TABLE 表名 DROP PRIMARY KEY;

主键的自增

关键字:
AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)

修改主键自增

-- 创建主键自增的表,自定义自增其实值
CREATE TABLE emp2(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
sex CHAR(1)
)AUTO_INCREMENT=100;

delete和truncat对自增长的影响

非空约束

字段名 字段类型 not null

唯一约束

字段名 字段值 unique

主键约束和唯一约束的区别：

• 主键约束唯一且不能为空
• 唯一约束，唯一可以为空
• 一个表中只能有一个主键约束，但是可以有多个唯一约束

默认值

默认值约束 用来指定某列的默认值

字段名 字段类型 DEFAULT 默认值

数据库事务

事务是一个整体,由一条或者多条SQL 语句组成,这些SQL语句要么都执行成功,要么都执行失败, 只要有一条SQL出现异常,整个操作就会回滚,整个业务执行失败

回滚
即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，滚回到事务开始时的状态。（在提交之前执行）

mysql事务操作

两种方式操作事务

• 手动提交事务
• 自动提交事务

手动提交事务

start transaction：这个语句显式地标记一个事务的起始点。
commit：表示提交事务，即提交事务的所有操作，具体地说，就是将事务中所有对数据库的更新都写到磁盘上的物理数据库中，事务正常结束。
rollback：表示撤销事务，即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，回滚到事务开始时的状态

流程：

• 执行成功的情况： 开启事务 -> 执行多条 SQL 语句 -> 成功提交事务
• 执行失败的情况： 开启事务 -> 执行多条 SQL 语句 -> 事务的回滚

自动提交事务

MySQL 默认每一条 DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，语句执行完毕 自动提交事务，MySQL 默认开始自动提交事务

取消自动提交
登录mysql，查看autocommit状态

SHOW VARIABLES LIKE 'autocommit';

on：自动提交
off：手动提交

把 autocommit 改成 off;

SET @@autocommit=off;

事务的四大特性ACID

mysql事务隔离级别

并发访问会产生的问题

设置隔离级别：

• read uncommitted 读未提交：不能防止以上三种任何一种情况
• read committed 读已提交：可以防止脏读（Oracle和SQLServer默认隔离级别）
• repeatable read 可重复读：可以防止脏读和不可重复读（MySql默认隔离级别）
• serializable串读：可以防止以上三种情况

隔离级别越高，效率越低

隔离级别相关命令

查看隔离级别

select @@tx_isolation;

设置事务隔离级别，需要退出 MySQL 再重新登录才能看到隔离级别的变化

set global transaction isolation level 级别名称;
read uncommitted 读未提交
read committed 读已提交
repeatable read 可重复读
serializable 串行化

脏读

脏读非常危险的，比如张三向李四购买商品，张三开启事务，向李四账号转入 500 块，然后打电话给李四说钱已经转了。李四一查询钱到账了，发货给张三。张三收到货后回滚事务，李四的再看钱没了

解决方法：
将全局的隔离级别进行提升为: read committed

不可重复读

不可重复读: 同一个事务中,进行查询操作,但是每次读取的数据内容是不一样的

会遇到的问题：
比如银行程序需要将查询结果分别输出到电脑屏幕和发短信给客户，结果在一个事务中针对不同的输出目的地进行的两次查询不一致，导致文件和屏幕中的结果不一致，银行工作人员就不知道以哪个为准了

解决方法：
将全局的隔离级别进行提升为: repeatable read

幻读

幻读: select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，
此时就发生了幻读

将事务隔离级别设置到最高 SERIALIZABLE ，以挡住幻读的发生
如果一个事务，使用了SERIALIZABLE——可串行化隔离级别时，在这个事务没有被提交之前 , 其他的线程，只能等到当前操作完成之后，才能进行操作，这样会非常耗时，而且，影响数据库的性能，数据库不会使用这种隔离级别

外键约束

• 外键指的是在 从表中与主表的主键对应的那个字段
• 使用外键约束可以让两张表之间产生一个对应关系,从而保证主从表的引用的完整性

创建外键约束

新建表时添加外键

[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)

已有表添加外键

ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名)

删除外键约束

alter table 从表 drop foreign key 外键约束名称

外键约束的注意事项

• 从表外键类型必须与主表主键类型一致 否则创建失败
• 添加数据时, 应该先添加主表中的数据
• 删除数据时,应该先删除从表中的数据

级联删除操作
如果想实现删除主表数据的同时,也删除掉从表数据,可以使用级联删除操作

级联删除
ON DELETE CASCADE


-- 重新创建添加级联操作
CREATE TABLE employee(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
age INT,
dept_id INT,
CONSTRAINT emp_dept_fk FOREIGN KEY(dept_id) REFERENCES department(id)
-- 添加级联删除
ON DELETE CASCADE
);

多表查询分类

内连接查询

内连接的特点:
通过指定的条件去匹配两张表中的数据, 匹配上就显示,匹配不上就不显示
比如通过: 从表的外键 = 主表的主键 方式去匹配

隐式内链接
from子句，后面直接写多个表名，使用where制定连接条件，这种连接方式是隐式内链接，使用where条件清除无用的数据

SELECT 字段名 FROM 左表, 右表 WHERE 连接条件;

显式内链接
使用inner、join…on这种方式

SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件
-- inner 可以省略

外连接查询

左外连接
左外连接 , 使用 LEFT OUTER JOIN , OUTER 可以省略

左外连接的特点：

• 以左表为基准，匹配右边表中的数据；如果匹配上，就展示匹配到的数据
• 如果匹配不到，左表中的数据正常展示，右边展示null

SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件

右外连接
右外连接 , 使用 RIGHT OUTER JOIN , OUTER 可以省略

右外连接的特点：

• 以右表为基准，匹配左边表中的数据，如果能匹配到，展示匹配到的数据
• 如果匹配不到，右表中的数据正常展示，左边展示为null

SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件

各种连接方式的总结

内连接: inner join , 只获取两张表中 交集部分的数据.
左外连接: left join , 以左表为基准 ,查询左表的所有数据, 以及与右表有交集的部分
右外连接: right join , 以右表为基准,查询右表的所有的数据,以及与左表有交集的部分

子查询

子查询的概念：
一条select 查询语句的结果, 作为另一条 select 语句的一部分

子查询的特点

• 子查询必须在小括号中
• 子查询一般作为父查询的查询条件使用

子查询常见分类

• where型子查询：将子查询的结果，作为父查询的比较条件
• from型子查询：将子查询的结果，作为一张表提供给父层查询使用
• exists型子查询：子查询的结果是单列多行，类似一个数组，父层查询使用IN 函数 ,包含子查询的结果

子查询的结果作为查询条件

SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;

子查询的结果作为一张表

SELECT 查询字段 FROM （子查询）表别名 WHERE 条件;

-- 1. 先查询分类表的数据
SELECT * FROM category;
-- 2.将上面的查询语句 作为一张表使用
SELECT
p.`pname`,
p.`price`,
c.cname
FROM products p
-- 子查询作为一张表使用时 要起别名 才能访问表中字段
INNER JOIN (SELECT * FROM category) c
ON p.`category_id` = c.cid WHERE p.`price` > 500;

子查询结果是单列多行

SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;

# 查询家电类 与 鞋服类下面的全部商品信息
-- 先查询出家电与鞋服类的 分类ID
SELECT cid FROM category WHERE cname IN ('家电','鞋服');
-- 根据cid 查询分类下的商品信息
SELECT * FROM products
WHERE category_id IN (SELECT cid FROM category WHERE cname IN ('家电','鞋服'));

子查询总结

1. 子查询如果查出的是一个字段(单列), 那就在where后面作为条件使用.
2. 子查询如果查询出的是多个字段(多列), 就当做一张表使用(要起别名).

数据库设计

三范式

第一范式

概念:
原子性, 做到列不可拆分
第一范式是最基本的范式。数据库表里面字段都是单一属性的，不可再分, 如果数据表中每个字段都是不可再分的最小数据单元，则满足第一范式。

第二范式

概念:
在第一范式的基础上更进一步，目标是确保表中的每列都和主键相关。
一张表只能描述一件事.

第三范式

概念:
消除传递依赖
表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放

数据库反三范式

反范式化指的是通过增加冗余或重复的数据来提高数据库的读性能
浪费存储空间,节省查询时间 (以空间换时间)

什么是冗余字段？
设计数据库时，某一个字段属于一张表，但它同时出现在另一个或多个表，且完全等同于它在其本来所属表的意义表示，那么这个字段就是一个冗余字段

总结：
创建一个关系型数据库设计，我们有两种选择
1，尽量遵循范式理论的规约，尽可能少的冗余字段，让数据库设计看起来精致、优雅、让人心醉。
2，合理的加入冗余字段这个润滑剂，减少join，让数据库执行性能更高更快。