文章目录
一、数据库的简介
定义：
- 数据库是按照数据结构来组织、存储和管理的专业性仓库；
- 它是一套软件，用于对数据进行创建、访问、管理、搜索等操作，从而更加方便、快速和安全地管理数据。
作用：
- 提供持久化保存，确保数据安全。
- 快速进行数据的增删改查操作。
- 提供安全的权限管理访问机制，处理并发访问。
分类：
关系型数据库：
- 定义：基于关系模型的数据库，使用表格来存储数据，并通过SQL语言进行操作。
- 常见数据库：MySQL、Oracle、PostgreSQL、SQL Server。
非关系型数据库：
- 定义：不基于传统的关系模型，通常具有更灵活的数据结构，适用于大规模、高并发的场景。
- 常见数据库：Redis（内存数据库）、MongoDB（文档数据库）。
二、数据库设计阶段
| 行动阶段 | 产出物 |
|---|---|
需求分析 | 数据字典、数据流图 |
概要设计 | 概念模型、E-R图 |
逻辑设计 | 某个DBMS所支持的数据模型 |
物理设计 | 为逻辑数据模型确定合适的应用环境结构 |
实施阶段 | 数据库建立、组织数据入库 |
运维阶段 | 调整与优化 |
三、数据库规范化
| 范式 | 说明 |
|---|---|
第一范式（原子性） | 无再拆分项，表中的所有字段值都是不可分解的原子值 |
第二范式（唯一性） | 无部份依赖，表中只能保存一种数据，不可以把多种数据保存在同一张表中 |
第三范式（独立性） | 无传递依赖，表中的每一列数据都和主键直接相关，而不能间接相关。 |
四、MySQL数据类型
数据类型是定义列中可以存储什么类型的数据以及该数据实际怎样存储的基本规则；
- 数据类型限制存储在数据列列中的数据。
- 例如：数值类型列只能接收数值类型的数据
在设计表时，应该特别重视所用的数据类型；
使用错误的数据类型可能会严重地影响应用程序的功能和性能；MySQL中的数据类型大体分为三大类：
- 数值类型
- 时间日期类型
- 字符串类型
1、字符串类型
MySQL包括了CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和SET 等多种数据类型；
其中最为常用的是：
- char
- varchar
- text
最常用的数据类型是字符串数据类型，如名字、地址、电话号码、身份证号码、邮政编码等都可以用字符串存储；
不管使用任何形式的串数据类型，串都必须括在引号内
- 定长串：char
CHAR长度是固定的，存储字符串’abc’，对于CHAR(10)，存储的字符将占10个字节(包括7个空字符)
- 变长串：varchar
VARCHAR长度是可变的，存储字符串’abc’，对于VARCHAR(10)，存储的字符只会占用3个字节
MySQL中定长串要比变长串性能高，因为不需要计算char所需要的存储空间，直接全部取出来即可
2、数值类型
数值数据类型仅可存储数值数据，MySQL中支持多组数值数据类型
每种存储的数值具有不同的取值范围，支持的取值范围越大，所需要的存储空间越多
# 浮点类型推荐使用decimal，在精度上来说时Double的两倍、Float的四倍
decimal(5,2) # 数值总共5位，小数占2位
# 整数类型推荐使用int，在大数据计算的规模下整数长度过高也是常有的
int # 4个字节，-21亿 ~ + 21亿，0~42亿
所有数值数据类型都可以有符号或无符号
有符号数值列可以存储正或负的数值，无符号则仅可存储正数
如明确不需要存储负数，可以使用UNSIGNED关键字
3、日期和时间类型
MySQL中提供了五种类型的时间类型，分别为DATE，DATETIME，TIMESTAMP，TIME和YEAR；
如果要用来表示年月日，通常用DATE 来表示。
如果要用来表示年月日时分秒，通常用DATETIME 表示。
如果只用来表示时分秒，通常用TIME 来表示。
如果需要经常插入或者更新日期为当前系统时间，则通常使用TIMESTAMP 来表示。
TIMESTAMP 值返回后显示为“YYYY-MM-DD HH:MM:SS”格式的字符串，显示宽度固定为19 个字符。
- 如果想要获得数字值，应在TIMESTAMP 列添加+0。
- 如果只是表示年份，可以用YEAR 来表示，它比DATE 占用更少的空间。
YEAR 有2 位或4 位格式的年。默认是4 位格式。
在4 位格式中，允许的值是1901～2155 和0000。
在2 位格式中，允许的值是70～69，表示从1970～2069 年.
4、运算符类型
五、MySQL常用函数
1、数值型函数
ABS(x) # 返回x的绝对值
PI() # 返回圆周率π，默认显示6位小数
SQRT(x) # 返回非负数的x的二次方根
MOD(x,y) # 返回x被y除后的余数
CEIL(x)和CEILING(x) # 返回不小于x的最小整数
FLOOR(x) # 返回不大于x的最大整数
ROUND(x) # 返回最接近于x的整数，即对x进行四舍五入；
ROUND(x,y) # 返回最接近x的数，其值保留到小数点后面y位，若y为负值，则将保留到x到小数点左边y位
SIGN(x) # 返回参数x的符号，-1表示负数，0表示0，1表示正数
POW(x,y)和POWER(x,y) # 返回x的y次乘方的值
EXP(x) # 返回e的x乘方后的值
LOG(x) # 返回x的自然对数，x相对于基数e的对数
LOG10(x) # 返回x的基数为10的对数
RADIANS(x) # 返回x由角度转化为弧度的值
DEGREES(x) # 返回x由弧度转化为角度的值
SIN(x) # 返回x的正弦，其中x为给定的弧度值；
ASIN(x) # 返回x的反正弦值，x为正弦
COS(x) # 返回x的余弦，其中x为给定的弧度值；
ACOS(x) # 后者返回x的反余弦值，x为余弦
TAN(x) # 返回x的正切，其中x为给定的弧度值；
ATAN(x) # 返回x的反正切值，x为正切
COT(x) # 返回给定弧度值x的余切
2、字符串函数
CHAR_LENGTH(str) # 计算字符串字符个数
CONCAT(s1,s2，...) # 返回连接参数产生的字符串，一个或多个待拼接的内容，任意一个为NULL则返回值为NULL
CONCAT_WS(x,s1,s2,...) # 返回多个字符串拼接之后的字符串，每个字符串之间有一个x
INSERT(s1,x,len,s2) # 返回字符串s1，其子字符串起始于位置x，被字符串s2取代len个字符
LOWER(str)和LCASE(str) # 将str中的字母全部转换成小写
UPPER(str)和UCASE(str) # 将字符串中的字母全部转换成大写
LEFT(s,n) # 返回字符串s从最左边开始的n个字符
RIGHT(s,n) # 返回字符串s从最右边开始的n个字符
LPAD(S1, length, S2) # 将字符串S2填补到S1左边，直到S1长度达到length(如果S1长度大于length则从左边开始数截除超出字符)
RPAD(S1, length, S2) # 将字符串S2填补到S1右边，直到S1长度达到length(如果S1长度大于length则从左边开始数截除超出字符)
LTRIM(s) # 返回字符串s，其左边所有空格被删除；
RTRIM(s) # 返回字符串s，其右边所有空格被删除
TRIM(s) # 返回字符串s删除了两边空格之后的字符串
TRIM(s1 FROM s) # 删除字符串s两端所有子字符串s1，未指定s1的情况下则默认删除空格
REPEAT(s,n) # 返回一个由重复字符串s组成的字符串，字符串s的数目等于n
SPACE(n) # 返回一个由n个空格组成的字符串
REPLACE(s,s1,s2) # 返回一个字符串，用字符串s2替代字符串s中所有的字符串s1
STRCMP(s1,s2) # 若s1和s2中所有的字符串都相同，则返回0；根据当前分类次序，第一个参数小于第二个则返回-1，其他情况返回1
SUBSTRING(s,n,len) # 从字符串s中返回一个第n个字符开始、长度为len的字符串
MID(s,n,len) # 从字符串s中返回一个第n个字符开始、长度为len的字符串
LOCATE(str1,str) # 返回子字符串str1在字符串str中的开始位置(从第几个字符开始)
POSITION(str1 IN str) # 返回子字符串str1在字符串str中的开始位置(从第几个字符开始)
INSTR(str,str1) # 返回子字符串str1在字符串str中的开始位置(从第几个字符开始)
REVERSE(s) # 将字符串s反转
ELT(N,str1,str2,str3,...)# 返回第N个字符串
LENGTH # 计算字符串长度函数，返回字符串的字节长度
3、时间日期函数
CURDATE()、CURRENT_DATE() # 将当前日期按照"YYYY-MM-DD"或者"YYYYMMDD"格式的值返回
CURRENT_TIMESTAMP()、NOW() # 返回当前日期和时间值
SYSDATE()、LOCALTIME() # 返回当前日期和时间值
# 例如：格式为"YYYY_MM-DD HH:MM:SS"或"YYYYMMDDHHMMSS"，具体格式根据函数用在字符串或数字语境中而定
UNIX_TIMESTAMP() # 返回一个格林尼治标准时间1970-01-01 00:00:00到现在的秒数
UNIX_TIMESTAMP(date) # 返回一个格林尼治标准时间1970-01-01 00:00:00到指定时间的秒数
FROM_UNIXTIME(date)和UNIX_TIMESTAMP互为反函数，把UNIX时间戳转换为普通格式的时间
UTC_DATE() # 返回当前UTC（世界标准时间）日期值，其格式为"YYYY-MM-DD"或"YYYYMMDD"
UTC_TIME() # 返回当前UTC时间值，其格式为"YYYY-MM-DD"或"YYYYMMDD"。
MONTH(date) # 返回指定日期中的月份
MONTHNAME(date) # 返回指定日期中的月份的名称
DAYNAME(d) # 返回d对应的工作日的英文名称，如Sunday、Monday等
DAYOFWEEK(d) # 返回的对应一周中的索引，1表示周日、2表示周一
WEEKDAY(d) # 表示d对应的工作日索引，0表示周一，1表示周二
WEEK(d) # 计算日期d是一年中的第几周
WEEKOFYEAD(d) # 计算某一天位于一年中的第几周
DAYOFYEAR(d) # 返回d是一年中的第几天
DAYOFMONTH(d) # 返回d是一月中的第几天
YEAR(date) # 返回指定日期对应的年份，范围是1970~2069
QUARTER(date) # 返回date对应一年中的季度，范围是1~4
MINUTE(time) # 返回time对应的分钟数，范围是0~59
SECOND(time) # 返回制定时间的秒值
ADDTIME(date,expr) # 进行date的时间加操作，
SUBTIME(date,expr) # 进行date的时间减操作
EXTRACE(type FROM date) # 从日期中提取一部分，type可以是YEAR、YEAR_MONTH、DAY_HOUR、DAY_MICROSECOND、DAY_MINUTE、DAY_SECOND
TIME_TO_SEC(time) # 返回以转换为秒的time参数，转换公式为"3600*小时 + 60*分钟 + 秒"
SEC_TO_TIME()和TIME_TO_SEC(time)互为反函数，将秒值转换为时间格式
DATE_SUB(date,INTERVAL expr type) # 返回将起始时间减去expr type之后的时间
SUBDATE(date,INTERVAL expr type) # 返回将起始时间减去expr type之后的时间
DATE_ADD(date,INTERVAL expr type) # 返回将起始时间加上expr type之后的时间
ADD_DATE(date,INTERVAL expr type) # 返回将起始时间加上expr type之后的时间
# 例如：DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 SECOND)表示的就是把第一个时间加1秒
4、聚合函数
MAX # 查询指定列的最大值
MIN # 查询指定列的最小值
COUNT # 统计查询结果的行数
SUM # 求和，返回指定列的总和
AVG # 求平均值，返回指定列数据的平均值
5、条件判断函数
IF(expr,v1,v2) # 如果expr是TRUE则返回v1，否则返回v2
IFNULL(v1,v2) # 如果v1不为NULL，则返回v1，否则返回v2
# 如果expr等于某个vn，则返回对应位置THEN后面的结果，如果与所有值都不想等，则返回ELSE后面的rn
CASE expr WHEN v1 THEN r1 [WHEN v2 THEN v2] [ELSE rn] END
6、系统信息函数
VERSION() # 查看MySQL版本号
CONNECTION_ID() # 查看当前用户的连接数
# 查看当前被MySQL服务器验证的用户名和主机的组合，一般这几个函数的返回值是相同的
USER()、CURRENT_USER()、SYSTEM_USER()、SESSION_USER()
CHARSET(str) # 查看字符串str使用的字符集
COLLATION() # 查看字符串排列方式
7、加密函数
PASSWORD(str) # 从原明文密码str计算并返回加密后的字符串密码
注意这个函数的加密是单向的（不可逆）
因此不应将它应用在个人的应用程序中而应该只在MySQL服务器的鉴定系统中使用
MD5(str) # 为字符串算出一个MD5 128比特校验和，改值以32位十六进制数字的二进制字符串形式返回
ENCODE(str, pswd_str) # 使用pswd_str作为密码，加密str
DECODE(crypt_str,pswd_str) # 使用pswd_str作为密码，解密加密字符串crypt_str，crypt_str是由ENCODE函数返回的字符串
8、其他函数
FORMAT(x,n) # 将数字x格式化，并以四舍五入的方式保留小数点后n位，结果以字符串形式返回
CONV(N,from_base,to_base) # 不同进制数之间的转换，返回值为数值N的字符串表示，由from_base进制转换为to_base进制
INET_ATON(expr) # 给出一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数，地址可以使4或8比特
INET_NTOA(expr) # 给定一个数字网络地址（4或8比特），返回作为字符串的该地址的点地址表示
BENCHMARK(count,expr) # 重复执行count次表达式expr，它可以用于计算MySQL处理表达式的速度，结果值通常是0（0只是表示很快，并不是没有速度）
# 另一个作用是用它在MySQL客户端内部报告语句执行的时间
CONVERT(str USING charset) # 使用字符集charset表示字符串str
六、数据定义语言—DDL
DDL：是指对于库、表的创建create、修改alter、删除drop；
1、库操作
显示数据库：show databases;
创建数据库：careta database 数据库名称;
查看数据库：show create database 数据库名称;
选择数据库：use 数据库名称;
删除数据库：drop database 数据库名称;
设置字符集：alter database 数据库名 character set utf8;
# 显示数据库
show databases;
# 创建数据库
create database school;
# 查看数据库
show create database school;
# 选择数据库
use school;
# 删除数据库
drop database school;
# 设置字符集
alter database school character set utf8;
2、表操作
显示库中表：show tables;
显示详细表结构：show create table 表名称;
查看表数据结构：desc 表名称;
创建表：create table 表名称(列名 数据类型 约束);
修改表名称：alter table 旧表名 rename to 新表名;
删除表：drop table 表名称;
复制表结构（不含数据）：create table 新表名 like 参照表;
（插入参照表的数据：insert into 新表名 select * from 参照表）
复制表结构（同步数据）：create table 新表名 select * from 参照表;
（该语句仅是把select语句的结果建一个表，所以新表没有主键、约束、索引，所以使用上面两句代码）
复制表结构（部分数据）：create table 新表名 select * from 参照表 WHERE 条件;
复制表结构（部分字段）：create table 新表名 select * from city where 0;
# 显示数据库中所有的表
show tables;
# 显示建表语句结构
show create table student;
# 查看表数据结构
desc student;
# 创建表(不存在就创建)
create table if not exists test_student(
-- 创建学生ID字段，为正整数，不允许为空，主键约束，自增属性
stu_id int unsigned not null primary key auto_increment,
-- 创建学生姓名字段，为字符串类型，最大长度20个字符，不允许为空
stu_name varchar(20) not null,
-- 创建学生性别字段，为字符串类型，固定长度1个字符，默认值为男
stu_sex char(1) default '男',
-- 创建学生年龄字段，为整数类型，最大127，默认20岁
stu_age tinyint not null default 20
)engine=innodb default charset=utf8; -- 指定存储引擎、编码
# 修改表名称
alter table test_student rename to student;
# 删除表：
drop table student;
# 复制表（不含数据）
create table test_school like student;
# 想要插入参照表的数据时：
insert into test_school select * from student;
# 复制表（同步数据）
create table test_school select * from student;
# 复制表（部分字段，不含数据）
create table test_school select * from student where 0;
3、表字段操作
添加表字段：alter table 表名 add 新列名 类型 约束;
插入表字段：alter table 表名 add 新列名 类型 约束 after 目标列;(或用first关键字插入首列)
删除表字段：alter table 表名 drop 列名;
修改表字段名称：alter table 表名 change 旧列名 新列名 类型;
修改表字段数据类型：alter table 表名 modify 列名 新类型 约束;
修改表字段默认值：alter table 表名 alter 列名 set default 默认值;
删除表字段默认值：alter table 表名 alter 列名 drop default;
# 添加表字段 根据需要添加约束
alter table student add stu_age datetime;
# 插入表字段在name列后并添加默认约束
alter table student add stu_sex varchar(10) default '男' after stu_name;
# 删除表字段
alter table student drop stu_age;
# 修改表字段名称
alter table student change stu_sex stu_gender varchar(10);
# 修改表字段数据类型
alter table student modify stu_gender char(2) default '男';
# 修改表字段默认值
alter table student alter stu_gender set default '女';
# 删除表字段默认值
alter table student alter stu_gender drop default;
4、数据库引擎
# 推荐在定义表时，表引擎定义为 innodb
# 通过查看建表语句查看当前的表引擎
mysql>use school;
mysql> show create table student\G;
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`stu_id` varchar(20) NOT NULL,
`stu_name` varchar(20) NOT NULL,
`stu_gender` varchar(20) NOT NULL,
`stu_birthday` varchar(20) DEFAULT NULL,
`stu_Class` varchar(20) DEFAULT NULL,
PRIMARY KEY (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
# 直接查看当前表的状态信息
mysql> show table status from school where name = 'student'\G;
*************************** 1. row ***************************
Name: student
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 7
Avg_row_length: 2340
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2021-06-05 18:01:39
Update_time: 2022-03-09 17:12:48
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)
# 修改表引擎语句 一般不会使用
alert table student engine = ‘myisam’
七、数据操作语言—DML
# 以此表示例：
create table student(
stu_id int(30) primary key,
stu_name varchar(30),
stu_gender char(2),
stu_birthday datetime,
stu_classNo int(30)
);
1、insert 插入
# 向所有列插入数据：insert into 表名 values(列值1,列值2……);
insert into student values(101,'张三','男','1990-01-01',1001);
# 同时插入多条数据：insert into 表名 values(列值1,列值2…),(列值1,列值2…)……;
insert into student values
(2,'李四','女','1989-05-05',1001),
(3,'王五','女','1991-12-12',1002),
(4,'赵六','男','1991-08-02',1002);
# 向指定列插入数据：insert into 表名(列名1,列名2……) values(列值1,列值2……);
insert into student(stu_id,stu_name) values(1,'张三',null,null,null);
2、update 更新
# 修改指定数据：update 表名 set 列名1=该列新值, 列名2=该列新值…… where 条件;
update student set stu_name = '张三三' where stu_id=1;
# 修改所有数据：update 表名 set 列名1=该列新值, 列名2=该列新值…… ;
update student set stu_name = '张三李四';
3、delete 删除
# 删除指定数据：delete from 表名 where 条件;
delete from student where stu_id=1;
# 删除所有数据：delete from 表名;
delete from student;
# truncate 表名;
# delete：每次删除一行数据同时产生一条日志，删除数据效率慢，不会重置表结构定义也不会释放空间
# truncate：删除整张表然后再重建，不会产生日志，初始化自增序列、空间占用
八、数据查询语言—DQL
查询语言可以分为select、where、group by、order by等……
1、基础查询
基础查询：基本查询、查询条件、合并、排序、聚合
基础语法：1、查询所有：select * from 表名;
2、查询部分：select 列名1,列名2…… from 表名;
3、指定别名：select 列名1 as 别名，列名2 as 别名 …… from 表名;
4、消除冗余：select distinct 列名 from 表名;
5、限制查询：select * from 表名 limit 起始位置,行数;
2、比较查询
1、比较运算符：=、>、>=、<、<=、<>、!=
2、关系运算符：and、or、not
3、区间查询
between 最小值 and 最大值
4、成员查询
# 包含某个或者某些规定的值
in(……)
# 不包含某个或者某些规定的值
not in(……)
5、非空判断
# 列值为空
is null
# 列值不为空
is not null
6、模糊查询
# 包含“你好”的表达式
like '%你好%';
# 包含两个字且第二个字是好的表达式
like '_好';
7、合并查询
select 列名 from 表1
union all
select 列名 from 表2;
8、结果排序
order by 字段 asc 根据指定的列对结果集进行升序排序
order by 字段 desc 根据指定的列对结果集进行倒序排序
9、聚合函数
count() 计数：count(*)返回表中的记录数量 count(列)返回指定列的不同值的数量
sum() 求和：返回数值列的总和，null值不包括在计算中
avg() 平均：返回数值列的平均值，null值不包括在计算中
min() 最小：返回数值列的最小值，null值不包括在计算中
max() 最大：返回数值列的最大值，null值不包括在计算中
10、分组查询
# 用于聚合函数，根据一个或多个列对结果集进行分组
group by
# 过滤分组后的数据（因为where不能用于聚合函数）
having
# 示例代码：
# 以grades(成绩表)为例分组查询分数大于60的参考情况：
select
grades_cid as 课程编号,
count(*) as 参考数目,
max(grades_score) as 最高分数,
min(grades_score) as 最低分数,
sum(grades_score) as 分数总和,
avg(grades_score) as 平均分数
from grades
where grades_score > 60
group by grades_cid
order by count(*) desc;
11、表连接
# 内连接：
# 查询两个表交集的数据
inner join (select * from a inner join b on a.id = b.id;)
# 左连接：
# 查询两个表交集以及左表全部数据
left join (select * from a left join b on a.id = b.id;)
# 右链接：
# 查询两个表交集以及右表全部数据
right join (select * from a right join b on a.id = b.id;)
# 全连接：
# 查询两个表的数据
full join(union all)
# 交叉连接：
# 查询两个表的笛卡尔积数据
cross join (select * from a cross join b on a.id = b.id;)
12、高级查询（子查询）
高级查询： 子查询(嵌套查询)将select语句嵌套进其他sql语句中
概念1：将子查询当作一个值放到条件中过滤
概念2：将子查询当作一张表从中提取需要的列
位置1：from后
位置2：where后
位置3：select后
分类1：标量子查询(返回一个值的标量)（如：返回存款类型有多少种，可以依据做条件判断）
分类2：列子查询(返回N行一列)（如：返回所有存款类型的编号）
分类3：行子查询(返回一行N列)（如：返回一个存款类型编号和名称等）
分类4：表子查询(返回N行N列)（如：返回所有存款类型所有名称等）
标量子查询
# 示例代码：(标量子查询)查询最小学号的学生信息
# 主查询
select * from student where stu_sid = 1;
# 子查询
select stu_sid from student order by stu_id limit 1;
# 合并嵌套查询 标量子查询
# 把内层查询结果当作外层查询的比较条件
select * from student
where stu_sid =(select stu_sid from grades order by grades_sid limit 1);
列子查询
# 示例代码：(列子查询)查询95033教室的学生考试情况
# 主查询
select * from score where score_stu_id in ('101','107','108');
# 子查询
select stu_id from student where stu_classNo='95033';
# 合并嵌套查询 列子查询
# 把内层查询结果当作外层查询的比较条件
# 此时子查询返回的是一列结果
select * from score where score_stu_id in
(select stu_id from student where stu_class='95033');
行子查询
# 示例代码：(行子查询)查询张三参考的课程
# 主查询
select * from course where stu_id = '101';
# 子查询
select stu_id from student where stu_name='张三';
# 合并嵌套查询 行子查询
# 把内层查询结果当作外层查询的比较条件
# 此时就会出现两个子查询返回的值进行比较
select * from course where stu_id=
(select stu_id from student where stu_name='张三');
表子查询
# 示例代码：(表子查询)查询每个学生的最高分数课程情况
# 主查询
select sc1.*,sc2.* from score sc1 ,score sc2
where sc1.score_stu_id = sc2.score_stu_id and sc1.score_score<sc2.score_score;
# 子查询
select * from score;
# 合并嵌套查询 表子查询
# 把内层的查询结果当成临时表，供外层sql再次查询。
# 查询结果集可以当成表看待。临时表要使用一个别名。
select * from score sc1,(select * from score) as sc2
where sc1.score_stu_id=sc2.score_stu_id and sc1.score_score < sc2.score_score;
九、数据控制语言：DCL
1、change 切换
# 切换数据库
# database_name 数据库名称
use database_name;
2、create 创建用户名
# 创建用户名：
# %代表任何地方可以使用
# 如果换成localhost的话navicat等远程登录数据库软件就无权限了
create user '用户名'@'%' identified by '密码';
3、grant 赋权
# 赋权超级管理员：
# *.* 赋予所有数据库的所有权限
# with grant option 可以将自身用于的权限赋予给其他用户
grant all on *.* to '用户名'@'主机名' identified by '密码' with grant option;
# 赋权普通用户名：
grant select,insert,update,on 数据库名.* to '用户名'@'%' identified by '密码';
# 查看权限：
show grants for '用户名'@'主机名';
4、revoke 移除
# 删除用户名
drop user '用户名'@'主机名';
# 撤销权限
revoke create,alter,drop on 数据库名.* from 用户名@主机名;
# 权限刷新
flush privileges;
# 删除用户名：
drop user '用户名'@'主机名';
十、事务控制语言：DTL
| 关键词 | 说明 |
|---|---|
begin 或 start transaction | 显示的开启一个事务 |
commit | 提交事务 |
rollback | 回滚事务 |
savepoint identifier | 设置一个事务回滚点 |
release savepoint identifier | 删除回滚点 |
rollback to identifier | 回滚到设置的回滚点 |
| 四大特性（ACID） | 说明 |
|---|---|
原子性（Atomicity） | 整个事务不可拆分，所包含的操作要么全部成功，要么全部失败，失败回滚的操作不能产生任何影响 |
一致性（Consistency） | 事务的执行结构要与业务逻辑一直，即让数据保持逻辑上的合理性（A转账给B五百元，A账户减五百，B账户加五百） |
隔离性（Isolation） | 一个事务内部的操作及使用的数据对并发的其他的事务是隔离的，并发执行的事务之间互不干扰，同时间且只有一个事务执行 |
持久性（Durability） | 事务完成后，事务所做出的改变会存储到数据库中，不会被回滚 |
| 事务的隔离级别 | 说明 |
|---|---|
读未提交（read uncommitted） | 脏读：一个事务读取到了另一个事务中尚未提交的数据 |
读已提交（read committed） | 不可重复读：同事务中进行查询操作但是每次读取的数据内容都不一样（数据被另一个事务修改并提交） |
可重复读（repeatable read） | 幻读：同事务中前后查询同范围时后一次查询看到了前一次查询没有看到的行（另一个事务新增了数据并提交） |
串行化（serializable） | 最高限制：在该事务没有被提交之前，其他的线程必须等该操作完成之后才能进行操作 |
1、脏读
脏读：读未提交（read uncommitted）
假定当前有两个事务在并发执行，分别是：事务A、事务B
事务A: 张三向李四转账500元
update account set money = money - 500 where name = '张三';
update account set money = money + 500 where name = '李四';
事务B：李四查询账户余额
SELECT money FROM account where name = '李四';
当张三给李四转账后（事务A执行，但是事务未提交）
李四此时正好去查询账户（事务B执行），发现账户增加了500（此时即发生了脏读）
突然张三突然反悔了，于是迅速回滚了事务。
现在李四的账户变成了初始的状态，但是李四读取出来的金额，却多了500。
李四啊，别人都还没提交呢，你就去读取了，别人万一回滚咋办，所以你读的是脏数据，这叫脏读，是有问题的！
如何解决脏读问题?
脏读非常危险的，比如张三向李四购买商品，张三开启事务，向李四账号转入 500 块，然后打电话给李四说钱 已经转了；李四一查询钱到账了，发货给张三。张三收到货后回滚事务，李四的再查看钱没了。
解决方案：将全局的隔离级别进行提升为: 读已提交（read committed）
-- 设置事务隔离级别为 read committed
set global transaction isolation level read committed;
-- 重新开启DOS窗口, 查看设置是否成功
-- 查看事务隔离级别
select @@tx_isolation;
设置完成后，再次重新执行转账操作（事务A），不提交，然后李四执行查询操作（事务B），
内心毫无波澜，因为账户金额还是最初的样子，说明现在没有脏读问题了。
2、不可重复读
不可重复读：读已提交（read uncommitted）
假定当前有两个事务在并发执行，分别是：事务A、事务B
事务A: 张三向李四转账500元
update account set money = money - 500 where name = '张三';
update account set money = money + 500 where name = '李四';
事务B：李四查询账户余额
SELECT money FROM account where name = '李四';
李四着急忙慌的，先去查询一次自己的账户余额（事务B），嗯，有1000，不错。
当张三给李四转账成功后（事务A执行并提交），
李四老婆叫他再次确认下自己的余额，于是李四又去查询一次自己的账户余额（事务B），我嚓，怎么和之前查询不一样哦，多了500。
我这还处于同一个事务中呢，这读取结果却不一样，到底哪次是对的？
这就是所谓的不可重复读问题。
下面这张图很好的诠释了这个执行过程：
如何解决不可重复读问题?
我们可以考虑这样一种实际情况：
比如银行程序需要将查询结果分别输出到电脑屏幕和发短信给客户，
结果在一个事务中针对不同的输出目的地进行的两次查询不一致，
导致文件和屏幕中的结果不一致，银行工作人员就不知道以哪个为准了
解决方案：将全局的隔离级别进行提升为：可重复读（repeatable read）
-- 设置事务隔离级别为 repeatable read
set global transaction isolation level repeatable read;
-- 重新开启DOS窗口, 查看设置是否成功：
-- 查看事务隔离级别
select @@tx_isolation;
设置完成后，恢复数据，再次重复上述操作，然后可以发现，李四两次查询的金额是相同的，说明已经没有不可重复读问题了。
3、幻读
幻读：可重复读（repeatable read）
假定当前有两个事务在并发执行，分别是：事务A、事务B
事务A：先查询有没有id为3的记录，如果没有，则进行插入
select * from account where id = 3;
INSERT INTO account VALUES(3,'王五',1000);
事务B：插入一条新记录
INSERT INTO account VALUES(3,'王五',1000);
执行顺序为：
- 顺序1：事务A执行第一条SQL, 查询是否有id为3的记录，结果发现没有，心想那我可以插入了。
- 顺序2：事务B新增一条记录，并提交
- 顺序3：事务A执行第二条SQL，新增id为3的记录，发现报错——主键重复
见鬼了，我刚才读到的结果应该可以支持我这样操作才对啊，为什么现在不可以
查某记录是否存在，否就准备插入此记录，但执行插入时发现此记录已存在，无法插入此时就是幻读。
如何解决幻读问题?
我们可以考虑这样一种实际情况:
比如银行程序需要将查询结果分别输出到电脑屏幕和发短信给客户,
结果在一个事务中针对不同的输出目的地进行的两次查询不一致，导致文件和屏幕中的结果不一致，银行工作人员就不知道以哪个为准了
解决方案：将事务隔离级别设置到最高：串行化（serializable）
-- 设置事务隔离级别为 serializable
set global transaction isolation level serializable;
-- 重新开启DOS窗口, 查看设置是否成功：
-- 查看事务隔离级别
select @@tx_isolation;
设置完成后，恢复数据，再次重复上述操作
提示：如果一个事务，使用了SERIALIZABLE——可串行化隔离级别时，在这个事务没有被提交之前 ,
其他的线程，只能等到当前操作完成之后，才能进行操作，这样会非常耗时，
而且，影响数据库的性能，数据库不会使用这种隔离级别
而此时执行顺序为：
- 顺序1：事务A执行第一条SQL, 查询是否有id为3的记录，结果发现，没有，那我可以插入了。
- 顺序2：事务B插入一条记录，这个操作无法完成，光标一直闪烁
- 顺序3：事务A执行第二条SQL,新增id为3的记录, 提交事务 数据插入成功.
- 顺序4：事务B在事务C提交之后, 再执行,但是主键冲突出现错误
提示：serializable 串行化可以彻底解决幻读
但是事务只能排队执行，严重影响效率，数据库不会使用这种隔离级别
设置完成后，恢复数据，再次重复上述操作
总结:
脏读：
- 说的是事务知道了自己本不应该知道的东西，强调的动作是查询，我看到了自己不该看的东西；
不可重复读：
- 强调的是一个人查的时候，其他人却可以增删改, 等我再查的时候，和之前查询的不一致，也就是我两次看到了不一样的东西；
幻读：
- 说的是我看到了数据是这么多，其他人又插了一条新的，
- 等我拿刚才的查询结果再执行操作的时候，冷不丁发现又多了一条数据
注意：不适用串行化如何解决幻读？
next-key lock的确是解决了幻读问题，
但是next-key lock在并发情况下也经常会造成死锁。
死锁检测和处理也会花费时间，一定程度上影响到并发量.
显示的开启一个事务：begin; 或 start transction;
提交事务：commit;
回滚事务：rollback;
设置回滚点：savepoint identifier;
删除回滚点：release savepoint identifier;
回滚标记点：rollback to identifier;
代码示例：
# 设置自动提交功能为禁用
set autocommit = 0;
# 表结构 学号,姓名,年龄 此时表无数据
select * from student;
# 步骤1开始事务
begin;
# 插入数据
insert into student values(1,'张三',18);
insert into student values(2,'李四',19);
# 此时表中存在两条数据
select * from student;
# 步骤2设置回滚点
savepoint test_save_1;
# 再插入两条数据
insert into student values(3,'王五',18);
insert into student values(4,'赵六',18);
# 此时表中存在4条数据
select * from student;
# 步骤3回滚到保存点
rollback to savepoint test_save_1;
# 此时表中只有2条数据了，显而易见的是回滚点之后的操作并没有持久化
select * from student;
代码示例：
# 创建一个表 测试事务
create table card(
id int primary key,
name varchar(20),
balance decimal(10,2)
);
# 插入数据
insert into card values(1,'张三',1000),(2,'李四',1000);
# 查询数据
select * from card;
# 开启事务
start transaction;
# 事务中执行语句
update card set balance = balance-500 where name = '张三';
update card set balance = balance+500 where name = '李四';
# 提交事务
commit;
# 查询提交事务后的数据 发现数据成功被修改
select * from card;
# 开启事务
start transaction;
# 事务中执行语句
update card set balance = balance-500 where name = '张三';
update card set balance = balance+500 where name = '李四';
# 回滚事务
rollback;
# 检索表数据 发现并未发生变化 因为事务回滚 回到开始事务之前的状态
select * from card;
713
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
