数据库系统学习

关系型数据库

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

都是使用表结构，格式一致，易于维护
使用通用的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 ('家电','鞋服'));