MySQL - 基础

DDL：数据定义语言

• 查看所有数据库:show databases;
• 查看当前数据库:select database();
• 创建数据库：create databases [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];
• 删除数据库：drop database [if exists] 数据库名;
• 使用指定数据库：use 数据库名;
• 查看当前数据库所有表：show tables;
• 查看表结构：desc 表名;
• 查看指定表的建表语句：show create table 表名;
• 建表：create table 表名 (字段1 字段类型 [comment 字段注释], 字段2 字段类型 [comment 字段注释]...) [comment 表注释];
• 添加字段：alter table 表名 add 字段名 类型 [comment 注释] [约束];
• 修改字段的类型：alter table 表名 modify 字段名 数据类型;
• 修改字段名以及类型：alter table 表名 change 旧字段名 新字段名 类型 [comment 注释] [约束];
• 删除字段：alter table 表名 drop 字段名;
• 修改表名：alter table 表名 rename to 新表名;
• 删除表：drop table [if exists] 表名;
• 清空表中的数据：truncate table 表名;

DML：数据操作语言

• 添加数据：insert into 表名 (字段1, 字段2...) values (值1, 值2...);
• 批量添加：insert into 表名 (字段1, 字段2...) values (值1, 值2...), (值1, 值2...);
insert into 表名 values (值1, 值2...);
• 更新数据：update 表名 set 字段名1 = 值1, 字段名2 = 值2,... [where 条件];
• 删除数据：delete from 表名 [where 表名];

DQL：数据查询语言

• 查询数据：select 字段名 from 表名 [where 条件] [group by 分组字段名] [having 分组后过滤条件] [order by 排序字段 desc/asc, ...] [limit 起始索引（offset 默认0开始）,查询记录数（rows）];
• like 模糊匹配中 _ 匹配单个字符，% 匹配多个字符
• 执行顺序：where > 聚合函数 > having
• where 不能对聚合函数进行判断，having 可以
• 分组之后，查询的字段一般为聚合函数、分组字段，查询其它字段无意义
• DQL执行顺序，from->where->group by->having->select->order by->limit

DCL：数据控制语言

主要是添加删除用户、已经控制对应的权限

• use mysql;
• select * from user;
• 添加用户：create user '用户名'@'主机名' identified by '密码'; 创建用户，create user 'shehuan'@'localhost' identified by '123456';
  localhost表示该用户只能在本机访问数据库，用%则表示可以在任意主机访问该数据库
• 修改密码：alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';
• 删除用户：drop user '用户名'@'主机名';
• 权限类型：
  ALL：所有权限，SELECT：查询数据，INSERT：插入数据，UPDATE：修改数据，DELETE：删除数据，ALTER：修改表，DROP：删除数据库、表，CREATE：创建数据库、表
• 查询用户权限：show grants for '用户名'@'主机名';
• 给用户授权：grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';， 多个权限之间用逗号分隔，数据库名、表名也可以使用*代替，表示所有的数据库以及其中的表
  撤销用户权限：revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

函数：

• 聚合函数：max()、min()、count()、avg()、sum()
• 字符串函数：
  concat(s1, s2,...,sn)，字符串拼接
  lower(str)，将字符串转小写
  upper(str)，将字符串转大写
  lpad(str, n, pad)，左填充，用字符串pad在str左边填充，直到str长度达到n，update student set no = lpad(no, 10, 0)，更新no字段，给no的值左边补0，直到达到10位
  rpad(str, n, pad)，右填充，用字符串pad在str右边填充，直到str长度达到n
  trim(str)，去掉字符串头尾的空格
  substring(str, start, len)，从str的start索引开始截取len长的子字符串，索引从1开始
• 数值函数：
  cell(n)，向上取整
  floor(n)，向下取整
  mod(x, y)，返回x/y的模（取余）
  rand()，返回0-1的随机数
  round(x, y)，求参数x四舍五入的值，保留y位小数
  select lpad(round(rand()*1000000, 0), 6, 0)，返回6位随机数
• 日期函数：
  curdate()，返回当前日期
  curtime()，返回当前时间
  now()，返回当前日期和时间
  year(date)、month(date)、day(date)，获取指定date的年份、月份、日期
  date_add(date, interval exp type)，在date上增加exp，type是exp的单位，select date_add(now(), interval 7 day);
  datediff(date1, date2)，返回date1和date2之间的天数（date1-date2）
• 流程控制函数：
  if(value, m, n)，value为true返回m,否则返回n
  ifnull(v1, v2)，v1不为null返回v1，否则返回v2
  case 字段 when v1 then w1 when v2 then w2 ... else w end，如果字段的值为v1返回w1，... 否则返回w，when then 组合可以出现多次
  case when exp1 then v1 when exp2 then v2 ... else v end，如果exp1的值为true返回v1，... 否则返回v，when then 组合可以出现多次

约束：

• not null，非空约束，保证字段值不能为null
• unique，唯一约束，保证字段值的唯一性
• primary key，主键约束，保证字段值唯一且不为空
• default，默认约束，保存数据时如果该字段没指定值，则使用默认值
• foreign key 外键约束，将两张表建立连接，保证数据的完整性、一致性，即当前表某个字段值必须是另外一张表某个字段的值
• check，检查约束，mysql8.0.16版本开始支持，保证字段满足某个条件
• 建表时给字段添加外键：create table user (id int primary key auto_increment, name varchar(10) not null unique, age int check(age > 0 and age <=120), status char(1) default '1' constraint 外键名称 foreign key (外键字段名) references 主表 (字段名));
• 给表的指定字段添加外键：alter table 子表 add constraint 外键名称 foreign key (外键字段名) references 主表 (字段名);
• 删除外键：alter table 表名 drop foreign key 外键名称;
• 外键约束的删除、更新行为：
  1、no action、restrict，默认行为，删除、更新主表对应记录时，先检查是否被其它子表作为外键引用，如果是则不允许删除、更新
  2、cascade，删除主表记录时如果子表存在外键关联则也会删除子表对应的记录，更新主表被引用的外键字段值时也会更新子表对应的值
  3、set null，会将子表对应字段值设置为null（前提是子表对应字段可以为null）
  4、alter table 子表 add constraint 外键名称 foreign key (外键字段名) references 主表 (字段名) [on update cascade on delete cascade];

多表连接查询：

• 隐式内连接：select 字段列表 表1, 表2 where 条件; 查询表1、表2的交集
• 显式内连接：select 字段列表 表1 [inner] join 表2 on 条件; 查询表1、表2的交集
• 左外连接：select 字段列表 from 表1 left [outer] join 表2 on 条件; 查询表1的数据，以及表1和表2交集的数据
• 右外连接：select 字段列表 from 表1 right [outer] join 表2 on 条件; 查询表2的数据，以及表1和表2交集的数据
• 自连接：表必须指定别名
• 联合查询：select 字段列表 from 表1 union [all] select 字段列表 from 表2; 返回两张表查询结果的并集，每张表查询的字段列表数量、类型必须一致，使用all不会对最终结果去重
• 子查询：
  1、in，not in
  2、any、some这两个作用一样，表示子查询结果中任意一个；all表示子查询结果中的全部；这三个需要配合比较操作符一起使用 比如'> any (子查询结果)'
  3、子查询结果是一行多列：select * from user where (age, salary) = (子查询); 子查询返回的结果对应 where 条件的两个字段，子查询前边的常用操作符： =、<>、in、not in
  4、子查询结果是多行多列：select * from user where (age, salary) in (子查询); 子查询前边的操作符可以使用 in、not in

事务：

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有操作作为一个整体一起向系统提交或撤销的操作请求，这些操作请求要么同时成功要么同时还失败。

事务基本操作：

• select @@autocomment; 查看事务的提交方式，1自定提交（mysql默认开启事务，并自动提交），0手动提交
• set @@autocomment = 0; 设置事务为手动提交，之后所有执行的sql语句都需要手动提交，类似于全局的
• start transaction、begin; 开启手动提交事务，可以在指定的sql前执行，sql执行后手动提交事务，仅对当前sql有效
• commit;手动提交事务
• rollback; 回滚事务

事务四大特性（ACID）：

• 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
• 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态
• 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作响应的独立环境下运行
• 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变是永久的

并发事务问题：

• 脏读：一个事务中读到另一个事务还没有提交的数据
• 不可重复读：一个事务中先后读取同一条记录，但两次读取的数据不同（两次读取之间另一个事务修改了对应的记录）
• 幻读：一个事务中按条件查询指定数据时，没有对应的数据，此时在另一个事务中插入对应数据后，再当前事务中插入数据时发现对应的记录已存在（但此时还是查不到），好像出现了幻影

事务的隔离级别解决的并发问题（√表示没有此问题）：

隔离级别	脏读	不可重复读	幻读
read uncommitted	×	×	×
read committed	√	×	×
repeatable read（可重复读，mysql 默认）	√	√	×
serializable（串行化）	√	√	√

• 查看事务的隔离级别：select @@transaction_isolation;
• 设置事务的隔离级别：set [session（当前会话的事务） | global（全局事务）] transaction isolation level [read uncommitted | read committed | repeatable read | serializable];