数据库系统学习
关系型数据库
关系型数据库建立在关系模型基础上的数据库，关系型数据库是由多张能相互相连的二维表组成的数据库
优点：
- 都是使用表结构，格式一致，易于维护
- 使用通用的sql语言操作，使用方便，可用于复杂查询
- 数据存储在磁盘中，安全
结构化查询语言，一门操作关系型数据库的编程语言
定义操作所有关系型数据库的统一标准
sql分类
- DDL(Data Definition Language）数据库定义语言，用来定义数据库对象：数据库，表、列等
- DML(Data Manipulation Language)数据库操作语言，用来对数据库中的表数据进行增删改查
- DQL(Data Query Language)数据查询语言，用来查询数据库中表的记录（数据）
- DCL(Data Control Language)数据库控制语言，用来定义数据库的访问权限和安全级别，及创建用户
DDL-操作数据库
- 查看Mysql中 都有哪些数据库
SHOW DATABASES;
使用数据库
USE 数据库名称;
查看当前正在使用的数据库
select database();
查看一个数据库的定义信息
show create database 数据库名；
- 创建数据库
CREATE DATABASES 数据库名称;
创建数据库（判断，如果不存在则创建）
CREATE DATABASE IF NOT EXISTS 数据库名称;
创建指定名称的数据库，并且指定字符集（一般都指定
utf-8）
create database 数据库名 character set 字符
集；
- 删除数据库
DROP DATABASE 数据库名称
删除数据库（判断，如果存在则删除）
DROP DATABASE IF EXISTS 数据库名称
- 修改数据库
数据库的字符集修改操作
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 条件表达式
比较运算符
| 运算符 | 说明 |
|---|---|
| > < <= >= = <> != | 大于、小于、大于(小于)等于、不等于 |
| BETWEEN …AND… | 显示在某一区间的值例如: 2000-10000之间： Between 2000 and 10000 |
| IN(集合) | 集合表示多个值,使用逗号分隔,例如: name in (悟空，八戒) in中的每个数据都会作为一次条件,只要满足条件就会显示 |
| ILIKE ‘%张%’ | 模糊查询 |
| IS NULL | 查询某一列为NULL的值, 注: 不能写 = NULL |
逻辑运算符
| 运算符 | 说明 |
|---|---|
| And && | 多个条件同时成立 |
| Or | 多个条件任一成立 |
| Not | 不成立 |
模糊查询 通配符
| 通配符 | 说明 |
|---|---|
| % | 表示匹配任意多个字符串 |
| _ | 表示匹配 一个字符 |
DQL操作单表
排序
SELECT 字段名 FROM 表名 [WHERE 字段 = 值] ORDER BY 字段名 [ASC / DESC]
-- 组合排序
SELECT * FROM emp ORDER BY salary DESC, eid DESC;
聚合函数
| 聚合函数 | 作用 |
|---|---|
| count(字段) | 统计指定列不为NULL的记录行数 |
| sum(字段) | 计算指定列的数值和 |
| max(字段) | 计算指定列的最大值 |
| min(字段) | 计算指定列的最小值 |
| avg(字段) | 计算指定列的平均值 |
分组
分组查询指的是使用 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使用：
- 需要在分组后,对数据进行过滤,使用 关键字 having
- 分组操作中的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的区别
| 过滤方式 | 特点 |
|---|---|
| where | where 进行分组前的过滤 where 后面不能写聚合函数 |
| having | having 是分组后的过滤 having 后面可以写聚合函数 |
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约束
- 约束的作用: 对表中的数据进行进一步的限制，从而保证数据的正确性、有效性、完整性. 违反约束的不正确数据,将无法插入到表中
- 常见的约束
| 约束名 | 约束关键字 |
|---|---|
| 主键 | primary key |
| 唯一 | unique |
| 非空 | not null |
| 外键 | foreign key |
主键约束
不可重复、唯一、非空
添加主键约束
字段名 字段类型 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对自增长的影响
| 清空表数据的方式 | 特点 |
|---|---|
| delete | 只是删除表中数据，对自增没有影响 |
| truncate | 是将整个表删除掉，然后创建一个新的表自增的主键，重新从1开始 |
非空约束
字段名 字段类型 not null
唯一约束
字段名 字段值 unique
主键约束和唯一约束的区别：
- 主键约束唯一且不能为空
- 唯一约束，唯一可以为空
- 一个表中只能有一个主键约束，但是可以有多个唯一约束
默认值
默认值约束 用来指定某列的默认值
字段名 字段类型 DEFAULT 默认值
数据库事务
事务是一个整体,由一条或者多条SQL 语句组成,这些SQL语句要么都执行成功,要么都执行失败, 只要有一条SQL出现异常,整个操作就会回滚,整个业务执行失败
回滚
即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，滚回到事务开始时的状态。（在提交之前执行）
mysql事务操作
两种方式操作事务
- 手动提交事务
- 自动提交事务
手动提交事务
| 功能 | 语句 |
|---|---|
| 开启事务 | start transaction;或者begin |
| 提交事务 | commit |
| 回滚事务 | rollback; |
start transaction：这个语句显式地标记一个事务的起始点。
commit：表示提交事务，即提交事务的所有操作，具体地说，就是将事务中所有对数据库的更新都写到磁盘上的物理数据库中，事务正常结束。
rollback：表示撤销事务，即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，回滚到事务开始时的状态
流程：
- 执行成功的情况： 开启事务 -> 执行多条 SQL 语句 -> 成功提交事务
- 执行失败的情况： 开启事务 -> 执行多条 SQL 语句 -> 事务的回滚
自动提交事务
MySQL 默认每一条 DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，语句执行完毕 自动提交事务，MySQL 默认开始自动提交事务
取消自动提交
登录mysql，查看autocommit状态
SHOW VARIABLES LIKE 'autocommit';
on：自动提交
off：手动提交
把 autocommit 改成 off;
SET @@autocommit=off;
事务的四大特性ACID
| 特性 | 含义 |
|---|---|
| 原子性 | 每个事务都是一个整体，不可再拆分，事务中所有的 SQL 语句要么都执行成功， 要么都失败。 |
| 一致性 | 事务在执行前数据库的状态与执行后数据库的状态保持一致。如：转账前2个人的总金额是2000，转账后 2 个人总金额也是 2000 |
| 隔离性 | 事务在执行前数据库的状态与执行后数据库的状态保持一致。如：转账前2个人的 总金额是2000，转账后 2 个人总金额也是 2000 |
| 持久性 | 一旦事务执行成功，对数据库的修改是持久的。就算关机，数据也是要保存下来的. |
mysql事务隔离级别
并发访问会产生的问题
| 产生的问题 | 说明 |
|---|---|
| 脏读 | 一个事务读取到了另一个事务中尚未提交的数据 |
| 不可重复读 | 一个事务中两次读取的数据内容不一致, 要求的是在一个事务中多次读取时数据是一致的这是进行 update 操作时引发的问题 |
| 幻读 | 一个事务中,某一次的 select 操作得到的结果所表征的数据状态, 无法支撑后续的业务操作. 查询得到的数据状态不准确,导致幻读. |
设置隔离级别：
- 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 ('家电','鞋服'));
子查询总结
- 子查询如果查出的是一个字段(单列), 那就在where后面作为条件使用.
- 子查询如果查询出的是多个字段(多列), 就当做一张表使用(要起别名).
数据库设计
三范式
第一范式
概念:
原子性, 做到列不可拆分
第一范式是最基本的范式。数据库表里面字段都是单一属性的，不可再分, 如果数据表中每个字段都是不可再分的最小数据单元，则满足第一范式。
第二范式
概念:
在第一范式的基础上更进一步，目标是确保表中的每列都和主键相关。
一张表只能描述一件事.
第三范式
概念:
消除传递依赖
表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放
数据库反三范式
反范式化指的是通过增加冗余或重复的数据来提高数据库的读性能
浪费存储空间,节省查询时间 (以空间换时间)
什么是冗余字段？
设计数据库时，某一个字段属于一张表，但它同时出现在另一个或多个表，且完全等同于它在其本来所属表的意义表示，那么这个字段就是一个冗余字段
总结：
创建一个关系型数据库设计，我们有两种选择
1，尽量遵循范式理论的规约，尽可能少的冗余字段，让数据库设计看起来精致、优雅、让人心醉。
2，合理的加入冗余字段这个润滑剂，减少join，让数据库执行性能更高更快。