MySQL基础之DML语言、DDL语言、TCL语言

DML语言，即数据操作语言

插入 insert

语法：

1. insert into 表名(列名,…) values (value1,…)[,(value1,…),…];
2. insert into 表名1 select * from 表名2;
3. insert into 表名 set 列名=值, 列名=值;

修改 update

1. 修改单表中的记录

   语法：

   1. update 表名 set 列 = 新值，列=新值，… where 筛选条件;

2. 修改多表中的记录

   update 表1 别名
   inner | left | right join 表2 别名
   on 连接条件
   set 列=值,…
   where 筛选条件;

   update boys bo
   right join beauty b on bo.id = b.boyfriend_id
   set b.boyfriend_id = 2
   where bo.id is null
   

删除 delete

1. 单表的删除

   语法
   delete from 表 where 筛选条件;
   truncate table 表;

2. 多表的删除

   delete 表1的别名，表二的别名
   from 表1 别名1
   innner | left | right join 表2 别名2
   on 连接条件
   set 列=值,…
   where 筛选条件;

3. delete 与 truncate的区别

   1. delete 可以加 where 条件，truncate 不能加
   2. truncate 删除，效率高一些
   3. 假如要删除表中数有自增长的列。如果用delete删除后，再插入数据，自增长列从断点开始，而truncate清空表中数据后，再插入数据，自增长列从1开始。
   4. truncate 删除没有返回值，而delete删除有返回值。
   5. truncate 删除不能回滚，delete 删除可以回滚。

DDL 语言，即数据定义语言

库的管理

1. 库的创建

   create database 库名;
   create database if not exists 库名;
   

2. 库的修改

   alter database books character set 字符集; # 修改数据库的字符集
   

3. 删除库

   drop database if exists 库名;
   

表的管理

1. 表的创建

   create table if not exists 表名(
   	列名 列的类型[(长度) 约束]
   	[,列名 列的类型[(长度) 约束]]
   );
   

2. 表的修改
   1. 修改列名

      alter table book change [column] publishdate pubdate datetime;
      

   2. 修改列的类型或约束

      alter table book modify column pubdate timestamp;
      

   3. 添加新列

      alter table book add column annual_salary double;
      

   4. 删除列

      alter table book drop column annual_salary;
      

   5. 修改表名

      alter table book rename to english_book;
      

3. 表的删除

   drop table if exists book;
   

4. 表的复制
   1. 只复制表结构

      create table book_copy like book;
      create table book_copy select * from book where 1=0;
      

   2. 复制表结构+数据

      create table book_copy select * from book;
      

表的常见数据类型

1. 数值型
   1. 整型：tinyint、smallint、Mediumint、int、integer、bigint

      整型数据区分无符号数据和有符号数据
      如果整型数据不设置有符号还是无符号，默认是有符号的。如果想设置无符号，需要用关键字unsigned。
      如果插入数据超出范围，会插入临界值。
      如果不设置长度，会自己设置默认的长度。
      数据类型的长度，不代表访问。而是代表显示结果的宽度，如果不够位数，左边会用0来填充。但是，需要搭配zerofill关键字一起使用才行。使用zerofill之后，数据类型自动变为无符号类型。

      create table tb_int(
      	t1 int(7) zerofill
      );
      

   2. 浮点型 float、double
   3. 定点型 DEC(M,D)，DECIMAL(M,D)

      M 代表整数位+小数位
      D 代表的是小数位
      decimal 默认的M是10，D是0

2. 字符型
   1. 保存较短的文本：char、varchar

      char数据类型，适合保存 长度固定的数据。如性别数据
      varchar适合长度变化的数据。如员工建议，意见等。
      char(M) M范围为0~255，默认可以省略，M默认值是1
      varchar(M) M范围0~65535，M不可用省略

   2. 保存较长的文本：text、blob（较长的二进制数据）
3. Enum类型
   枚举类型，要求插入的值必须是属于列表中指定的值之一。如果列表成员为1-255，则需要1个字节存储；如果列表成员为255~65535，则需要两个字节存储。

   create table tb_enum(
   	name varchar(20),
   	sex enum('男','女')
   )
   

4. set 类型
   和enum类型类似，但是最大区别是enum类型一次可以选取多个成员，而enum只能选取一个成员。

   create table tb_set(
     s set('a','b','c','d')
   );
   insert into tb_set values ('a,b'),('a'),('a,c,b');
   

5. 日期型

   日期和时间类型	字节	最小值	最大值
   date	4	1000-01-01	9999-12-31
   datetime	8	1000-01-01 00:00:00	9999-12-31 23:59:59
   timestamp	4	19700101080001	2038某个时刻
   time	3	-838:59:59	838:59:59
   year	1	1901	2155

   timestamp 和实际时区有关，更能反映实际的日期，而datetime 则只能反映出插入时的当地时区。

   create table tb_date(
   	ti datetime,
   	t2 timestamp
   );
   
   insert into tb_date values(now(), now());
   
   select * from tb_date;
   
   show variables like 'time_zone';
   
   set time_zone='+9:00';
   
   select * from tb_date;
   

常见的约束

1. 非空约束：保证该字段不能为空。
2. DEFAULT：保证字段有默认值。
3. 主键约束：用于保证该字段的值具有唯一性，并且非空
4. UNIQUE：唯一约束，保证该字段具有唯一性，可以为空。
5. CHECK：检查约束，mysql中不支持。比如年龄字段，性别字段
6. FOREIGN KEY：外键，用于限制两个表的关系。

主键约束和唯一约束的区别

1. 都能保证唯一性
2. 主键约束不允许字段为空，唯一约束允许字段为空
3. 主键约束一个表中只能有一个，唯一约束一个表中可以有多个。
4. 主键可以多个字段组合，唯一约束也可以使用多个字段。

标识列

又称为自增长列，可以不用手动插入值，系统提供默认的序列值。

show variables like '%auto_increment%'; # 查看自增长列的偏移量和起始值

标识列必须和主键搭配吗？不是必须，只要该字段是一个key（主键、唯一、外键）
一个表中至多有一个标识列。
标识列的类型只能是数值型。
标识列可以通过 set auto_increment_increment=数字;设置步长，也可以通过手动插入值，设置起始值。

TCL 语言

事务

一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。

查看MySQL支持的存储引擎

show engines;

在mysql中用的最多的存储引擎是：innodb，myisam，memory等。其中innodb支持事务，而myisam、memory等不支持事务。

事务的ACID属性

1. 原子性（Atomicity）
   原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
2. 一致性（Consistency）
   事务必须使数据库从一个一致性状态变换到另一个一致性状态。
3. 隔离性（Isolation）
   事务的隔离性是指一个事务的执行不能倍其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务隔离的，并发执行的各个事务之间不相互影响。
4. 持久性（Durability）
   持久性是指一个事务一旦被提交，它对数据库中的数据的改变就是永久性的，接下来的其他操作和数据故障不应该对其有任何影响。

隐式的事务

show variables like '%autocommit%';

隐式的事务没有明显的开启和结束的标记，比如insert、update、delete语句。

显式的事务

具有明显的事务开启和结束标志。
开启显式事务，必须要设置自动提交功能为禁用。

set autocommit=0;
# 开启事务
start transaction;
# 语句1
commit; # 提交事务
rollback; # 回滚事务

数据的隔离级别

1. 对于同事运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种问题：
   • 脏读：对于两个事务T1、T2，T1读取了已经被T2更新但还没有被提交的字段之后，若T2回滚，T1读取的内容就是临时且无效的。（数据更新）
   • 不可重复读：对于两个事务T1、T2，T1读取了一个字段，然后T2更新了该字段之后，T1再次读取同一个字段，值就不同了。
   • 幻读：对于两个事务T1、T2，T1从一个表中读取一个字段，然后T2在该表中插入了一些新的行之后，如果T1再次读取同一个表，就会多出几行。（数据的插入和删除）
2. 数据库事务的隔离级别：数据库系统必须具有隔离并发运行各个事务的能力，使他们不会相互影响，避免各种并发问题。
3. 一个事务与其他事务隔离的程度称为隔离级别。数据库规定了多种事务的隔离级别，不同隔离级别对应不同的干扰程度。隔离级别越高，数据一致性就越好，但并发性就越弱。
4. 查看数据库中设置的事务的隔离级别

   select @@tx_isolation; # 查看事务的隔离级别
   set session transaction isolation level read uncommitted; #修改事务的隔离级别
   

5. MySQL数据的隔离级别

   离级别	脏读	不可重复读	幻读
   读未提交	有	有	有
   读已提交	没有	有	有
   可重复读	没有	没有	有
   串行化	没有	没有	没有

保存点

set autocommit=0;
start transaction;
delete from account where id = 25;
savepoint a; # 设置保存点
delete from account where id = 28;
rollback to a;

Delete和Truncate在事务中的区别

delete语句，支持回滚；Truncate语句，不支持回滚。

视图

创建视图

语法：
create view 视图名
as
查询语句;

视图的修改

语法：
create or replace view 视图名
as
查询语句;
alter view 视图名
as
查询语句;

删除视图

语法：
drop view myv1,myv2,myv3,…;

查看视图

语法
desc myv1;
show create view myv1;
show create view myv1\G;

视图的更新

1. 插入数据：通过视图插入数据，其实数据被插入到了原始表中。但是，并不是所有是视图都不能保证数据的插入成功。
2. 修改：同插入相似。
3. 删除：也可以通过视图，删除数据。

• 具备以下特定的视图，不允许更新：
  分组函数、distinct、group by、having、union、union all、常量、select中包含子查询、join、from一个不能更新的视图、where子句的子查询引用from子句中的表。

变量

系统变量

变量由系统提供，不是用户自己定义的，属于服务器层面的。
查看所有的系统变量

show global | [session] variables;
show global | [session] variables like ‘%b%’; #模糊查询变量
select @@global.系统变量名; # 查看全局变量
select @@系统变量; # 查看会话变量
set global | [session] 系统变量名 = 值;
set @@global|[session].系统变量名 = 值;

• 全局变量
  作用域：服务器每次启动将为所有的全局变量赋初值，针对于所有的会话（连接），但是不能跨重启。
• 会话变量
  作用域：仅仅针对当前会话有效（连接）。

自定义变量

用户自己定义的变量。

• 用户变量
  针对当前会话（连接）有效。

  #声明并初始化
  set @用户变量名=值;
  set @用户变量名:=值;
  select @用户变量名:=值;

• 局部变量
  仅仅在定义它的 begin end中有效。

  #申明变量
  declare 变量名 类型;
  declare 变量名 类型 default 值;

存储过程和函数

存储过程

1. 创建的语法

   create procedure 存储过程名(参数列表)
   begin
   	存储过程体(一组合法的SQL语句)
   end
   

   参数列表包含三部分：
   参数模式、参数名、参数类型。如：in stuname varchar(20)
   参数模式包括：IN，OUT，INOUT
   存储过程体只有一条语句，begin end可以省略。
   存储过程体中的每条SQL语句的结尾要求必须加分号。
   存储过程的结尾，可以使用DELIMITER设置结束标记。
2. 调用语法

   call 存储过程名(实参列表);
   

3. 删除存储过程

   drop procedure 存储过程名;
   

4. 查看存储过程

   show create procedure 存储过程名
   

函数

1. 创建语法

create function 函数名(参数列表) returns 返回类型
begin
	函数体
end

2. 调用函数

   select 函数名(参数列表)
   

3. 查看函数

   show create function myfun;
   

4. 删除函数

   drop function myfun;
   

函数与存储过程的区别

存储过程的返回值，可以由0个，也可以由多个返回值。
函数只能有一个返回值。

流程控制结构

分支结构

1. if 函数
2. case 结构
3. if 结构

循环结构

1. while
2. loop
3. repeat
4. 循环控制语句：iterate，同c语言中的continue功能一样；leave 同c语言中的break功能一样。

   create PROCEDURE pro_while(in count int)
   begin
     declare i int default 1;
     while i <= count do
       insert into admin(username,`password`) values('Rose'+i, '666');
       set i = i +1;
     end while;
   end