MYSQL
![SB07IR3ML5]PS@6BRJ35VM1](https://img2022.cnblogs.com/blog/1763268/202204/1763268-20220429164730142-1298937621.png "SB07IR3ML5]PS@6BRJ35VM1")
MySQL数据库
基础
概念
数据库：按一定数据结构组织，存储，管理数据的仓库
分类
关系型数据库
非关系型数据库
表结构数据
由固定列和任意行构成的表格数据集
列为字段，行为记录
与表格的区别
以字段为基本存储和计算单位
每个字段必有字段名
同一表中字段不能重复
每个字段数据类型一致
DBMS数据库管理系统：管理数据库的软件
SQL：结构化查询语言，和DBMS通信
企业数据存储问题
存储大量数据
大量数据检索访问
数据信息一致性，完整性
数据共享和安全
MySQL服务
启动和停止
cmd管理员身份运行--net start/stop mysql80
登录和退出
mysql -h主机名 -P端口号 -u用户名 -p，输入密码0000
mysql -hlocalhost -P3306 -uroot -p
退出 exit
SQL语言
分类
数据定义语言DDL
创建，修改，删除数据库中各种对象（数据库，表，索引等）如CREATE,ALTER,DROP
数据操作语言DML
操作数据表的记录，如INSERT,UPDATE,DELETE
数据库查询语言DQL
查询数据表中记录，基本结构，如SELECT
数据控制语言DCL
定义数据库访问权限和安全级别，GRANT,REVOKE
书写要求
单行或多行，以分号结尾
大小写不敏感
#或--单行注释。/**/多行
select语句书写顺序
SELECT-FROM-WHERE-GROUP BY-HAVING-ORDER BY-LIMIT
select语句执行顺序
FROM-WHERE-GROUP BY-HAVING-SELECT-ORDER BY-LIMIT
DDL数据定义语言
数据库管理
查看
show databases;
show create database 数据库名称
创建
create database 库名
选择使用数据库
use 库名
修改数据库编码
alter database 数据库名称 character set 编码格式
删除数据库
drop database 库名
数据表管理
查看
当前数据库所有表：SHOW TABLES
查看创建好的表：show create table 表名
查看表结构：DESC 表名
创建
建表前先use数据库
CREATE TABLE 表名（字段名，字段类型，[约束条件]）
字段类型
int（n）:n位整数
float（n,m）：n位数字，其中m位小数，默认float（10,2）
decimal（n,m）：n位数字，其中m位小数，适合精度较高数据存储，默认decimal（10,0）
char（n）：固定长度字符串，默认长度为1
varchar（n）：可变长度字符串，必须指定长度
text：长文本字符串，不可指定长度
date：日期型，yyyy-mm-dd
time：时间型，hh:mm:ss
datetime：yyyy-mm-dd hh:mm:ss
约束条件
定义
在表上强制执行的数据检验规则，用于保证数据完整和准确，主要对空值和重复值进行约束
常用约束条件
PRIMARY KEY
主键约束
每个表只有1个主键
主键值必须非空不重复
可设置单字段主键&多字段联合主键
添加主键约束
CREATE TABLE 表名（字段名1，字段类型1，PRIMARY KEY，...，字段名n，字段类型n）
表级：CREATE TABLE 表名（字段名1，字段类型1，......字段名n，字段类型n）constraint [主键约束名 PRIMARY KEY]（字段名1[，字段名2，...字段名n]
NOT NULL
非空约束，不可为空
CREATE TABLE 表名（字段名1，字段类型1，NOT NULL，...，字段名n，字段类型n）
UNIQUE
唯一约束，不可重复
指定字段取值不能重复，可以为空，但只可出现一个空值
添加唯一约束
列级：CREATE TABLE 表名（字段名1，字段类型1，UNIQUE，...，字段名n，字段类型n）
表级：CREATE TABLE 表名（字段名1，字段类型1，......字段名n，字段类型n）constraint [唯一约束名 UNIQUE]（字段名1[，字段名2，...字段名n]）
DEFAULT
默认约束，默认值
CREATE TABLE 表名（字段名1，字段类型1，DEFAULT value，...，字段名n，字段类型n）
FOREIGN KEY
外键约束
在1张表中执行数据插入，更新，删除时，DBMS会跟另一张表进行对照
某表某字段依赖于另一表某字段
每个外键值和另一表主键值对应，主键所在表为主表，外键所在表为从表
CREATE TABLE 表名（字段名1，字段类型1，......字段名n，字段类型n constraint [外键约束名] FOREIGN KEY（字段名）reference 主表 （主键字段））
标识列AUTO_INCREMENT
自增
指定字段值自动生成，默认从1开始，每增加一条记录，该字段取值增长1
仅适用于整数，配合键一起使用，每个表至多一个
CREATE TABLE 表名（字段名1，字段类型1，PRIMARY KEY AUTO_INCREMENT，...，字段名n，字段类型n）
主键和唯一的区别
主键不允许空值，唯一可以有1个空值
一个表中只能有1个主键，唯一可以有多个
复制
仅复制表结构
CREATE TABLE 表名 LIKE 旧表名
复制数据+结构
CREATE TABLE 表名 select*from 旧表名
修改
修改表名
ALTER TABLE 表名1 rename 表名2
修改字段名
ALTER TABLE 表名 change 字段名1 字段名2 字段类型 [约束条件]
修改字段类型
ALTER TABLE 表名 modify 字段名 字段类型
修改字段排列位置
ALTER TABLE 表名 modify 字段名 字段类型 after 某字段
添加字段
ALTER TABLE 表名 add 字段名 字段类型 [位置（first/after 某字段），没有位置默认最后一行]
删除字段
ALTER TABLE 表名 drop 字段名
删除数据表
drop table 表名 if exists
DML数据操作语言
插入数据
字段名与字段值数据类型，个数，顺序必须一一对应
指定字段名插入
INSERT INTO 表名（字段名1[，字段名2，...字段名n]）values （字段值1[，字段值2，...字段值n]）
不指定字段名插入
INSERT INTO 表名 values （字段值1[，字段值2，...字段值n]）
批量导入
LOAD DATA INFILE “文件路径.csv” into table 表名 fields terminated by "," ignore 1 lines
文件路径获取：选中文件后，shift+右键，选择复制为路径
路径不可为中文
路径分隔符\要转换为/或\\，Ctrl+H进行替换
更新
UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2,....]where 更新条件
若无where，则为批量更新，需设置权限：set sql_safe_updates=0
删除
数据表：DROP TABLE 表名
数据，保留表结构
DELETE FROM 表名[where 条件]
TRUNCATE 表名
delete from和truncate区别
delete可以添加where条件删除部分数据，truncate删除表中全部数据
truncate直接把表删除（drop）然后创建一张新表，比delete快
delete支持回滚，truncate不支持
TCL事务定义语言
事务定义：1个或1组SQL语句组成1个执行单元，要么全部执行，要么全部不执行
事务ACID属性
原子性Atomicity
事务是不可分割的工作单位，事务中的操作要么都发生要么都不发生
一致性Consistency
事务使数据库从1个一致性状态变换到另一个一致性状态
隔离性Isolation
一个事务的执行不能被另一个事务干扰
持久性Durability
一个事务一旦被提交，对数据库中的数据改变是永久的，接下来其他操作对其无影响
创建事务
隐式事务
无开启和结束标志，如insert,update,delete
显式事务
设置自动提交功能为禁用
set autocommit=0;
start transaction;可选
语句1，语句n...
commit;提交事务
rollback;回滚
并发问题：同时运行的多个事务访问数据库中相同的数据
脏读：T2更新了字段但没提交，同时T1读取数据，如果T2回滚，那么T1读取了无效数据
read uncommitted
不可重复读：T1读取了一个字段，然后T2更新字段并成功，T1再读此字段值不同
read uncommitted,read committed
幻读：T1读取了一个字段，然后T2插入记录并成功，T1再读此表，会多出几行
read uncommitted,read committed,repeatable read
DQL数据查询语言(先use库)
单表查询
全表查询
select*from表名
查询指定字段
select 字段1，字段2...from表名
设置别名
select 字段名 as 字段别名 from 表名 as 表别名
查询不重复记录
select distinct 字段名 from 表名
条件查询
select 字段1，字段2...from表名 where 条件
运算符
算术运算符+-*/
加号+
两个操作数都为数值，做加法运算
其中一个为字符，试着转化为数值，转化成功继续加法运算，转化失败，字符型数值为0
其中一个为null，结果为null
逻辑运算符not and or
比较运算符= > < >= <= !=/<>,between...and, in,is[not]null
between...and包含数值左右，字符串不包括
in列表值类型必须一致，且列表值不可以使用通配符
=或<>不可判断null，is不可判断数值
模糊查询
select 字段1，字段2...from表名 where 字段[not] like 通配符
%匹配多个字符，_匹配1个字符
查询结果排序
select 字段1，字段2...from表名 order by 字段1 排序方向[,字段2 排序方向]
多字段排序，先按第一个字段排序，第一个字段值相同时才按第二个字段排序
asc升序，从小到大,desc降序，从大到小
限制查询结果数量
select 字段1，字段2...from表名 limit [偏移量,]行数
聚合运算
将多行数据聚集计算为1行
对null以外的数据进行聚合运算
sum()
avg()
count(*)
max() min()
数值
任意
可与distinct搭配实现去重后字段的运算
select 分组字段，聚合字段；注意：先分组再聚合，不聚合，用where
分组聚合
分组后查询
select 分组字段1，[分组字段2...]，分组函数from表名 [where 条件] group by 分组字段1，[分组字段2...]
将查询结果按照一个或多个字段分组，字段值相同为1组，对每个组聚合运算
分组筛选
select 分组函数，字段 from表名 [where 条件] group by 分组字段1，[分组字段2...] having筛选条件
where和having的区别
where针对于表中记录的筛选，having作用于分组结果的过滤
where在分组和聚合之前筛选行，having在分组和聚合后筛选分组的行，where字句不可包含聚合函数
多表查询
通过不同表中具有相同意义的字段，将多表进行连接，查询不同表中字段信息
笛卡尔乘积现象：由于缺少连接条件，m行n行表相连，结果为mn行表
联合查询
把多条select结果合并为一个结果集
被合并表的列数，顺序，数据类型必须一致
union去重
select 字段1，字段2...from表1 union select 字段1，字段2...from表2
union all 不去重
连接方式
SQL92标准仅支持内连接，SQL99标准支持除全连接的其他连接
内连接
等值连接
select 字段1，字段2...from表1 inner join 表2 on 表1.字段=表2.字段
非等值连接
select 字段1，字段2...from表1 inner join 表2 on 表1.字段between表2.字段1 and 表2.字段2
自连接
select 字段1，字段2...from表1 inner join 表1 on 表1.字段1=表1.字段2
连接满足条件的行
外连接
左连接
查询结果为主表中所有记录
若从表中有连接条件成立的，显示匹配值，若连接条件不成立，显示null
右连接
全连接
连接结果确定
方向性：写在前面的是左表，写在后面的是右表
主附关系：主表要出所有数据范围，附表与主表无匹配时标记为null，内连接无主附关系
对应关系：关键字段有重复值为多表，无重复值为一表
子查询
1个select语句中包含1个或多个select语句
位置
where/having 后：作为主查询条件
from后：作为主查询的一个表
exists后：表子查询
分类
标量子查询：返回单行单列
行子查询：返回1行多列
列子查询
表子查询
操作符
[not]in：字段in数据表（子查询）
any
字段 比较 any 数据表（子查询）
all
常用函数
select 函数名（实参）from 表
字符串函数
CONCAT(str1,str2...)
分组合并GROUP_CONCAT([distinct]str[orderby str][separator])
INSTR(str,substr):返回子字符串在文本字符串中第一次出现的位置（从1开始）
LEFT(str,len):返回左边len个字符
RIGHT(str,len):返回右边len个字符
SUBSTR(exp,start,len)
exp:字符串，二进制字符串，文本，列，包含列的表达式
start：整数或可转为int的表达式，指定子字符串开始位置（从1开始）
len：整数或可转为int的表达式，指定子字符串长度
TRIM(STR):删除两遍空格
REPLACE(str,from,to)
REPEAT(str,count)
REVERSE(str)
UPPER(str) LOWER(str)
数学函数
ABS(n)
FLOOR(n) CEILING(n)
ROUND(n,d)
RAND(n)：返回0-1的浮点数，若指定n，则生成随机数不变化
日期与时间函数
DATE(date):返回指定日期、时间表达式的日期部分
WEEK(date):返回指定日期是一年中的第几周
MONTH(date)
QUARTER(date)
YEAR(date)
ADDDATE(date,INTERVAL expr,type)
SUBDATE(date,INTERVAL expr,type)
expr:对date进行加减的表达式字符串，如 2
type：指明expr如何被解释，如 day
DATE_FORMAT(date,format)
根据format字符串格式化date值
%Y-%m-%d %h-%i-%s
CURDATE()：日期 CURTIME()：时间 NOW()：日期时间
DATEDIFF(expr1,expr2)：返回两者之间的天数
逻辑函数
IFNULL(expr,alt_val)
if expr==null,return alt_val;else return expr_val
IF(expr1,expr2,expr3)
if expr==true,return expr2;else return expr3
CASE
WHEN expr1 THEN expr2
[WHEN expr3 THEN expr4]
ELSE expr
END
if expr1==true,return expr2;if expr3==false, return expr4
开窗函数
在满足某种条件的记录集合上执行的特殊函数。本质是聚合运算，只不过更具灵活性，对数据的每一行都使用以该行相关的行并返回计算结果
静态窗口：对于每条记录都要在此窗口执行函数，窗口大小固定
动态窗口：窗口大小不固定
语法
开窗函数名（[字段名]over（[partition by 分组字段][order by 排序字段][细分窗口]））
当前行属于某个窗口，窗口由over关键字指定函数执行的窗口范围。
若为空，开窗函数基于where字句的所有行计算
不为空，有3个参数设置窗口
partition by：按指定字段分区，2个分区由边界分隔，开窗函数在不同分区分别执行，跨越边界时重新初始化
order by：按指定字段排序，可单独使用
frame：定义分区子集规则，通常作为滑动窗口
滑动窗口范围指定：between frame-start and frame-end
current row:边界是当前行
unbounded preceding：边界是分区第一行
unbounded following：边界是分区最后一行
expr preceding：边界是当前行减去expr
expr following：边界是当前行加上expr
序号函数
ROW_NUMBER()：显示分区中不重复不间断的序号
DENSE_RANK()：显示分区中重复不间断的序号
RANK()：显示分区中重复间断的序号
开窗函数与聚合函数区别
普通聚合函数是将多条记录聚合为一条；
开窗函数是每条记录都会执行函数，有几条记录执行完还是几条。
![C@H9ND3D)4XKTSV7QXS_]FF](https://img2022.cnblogs.com/blog/1763268/202204/1763268-20220429164735209-610408762.png "C@H9ND3D)4XKTSV7QXS_]FF")
