MySQL（二）——其他基础功能

  iwehdio的博客园：https://www.cnblogs.com/iwehdio/

3、DDL

• 库和表的管理。

• 库的管理：

  • 创建库：
    • create database 库名。
    • create database if not exist 库名：不存在这个库再创建。
  • 库的修改：
    • alter database 库名 character set 字符集：修改库的字符集。
  • 库的删除：
    • drop database 库名。
    • drop database if exist 库名：存在再删除。

• 表的管理：

  • 表的创建：

    create table 表名(
    	列名 类型 【约束】,
        列名 类型 【约束】
    );
    
    -- 例：
    create table book(
    	id int(7),
        bName varchar(20),
        price double(5,1),
        date datetime
    );
    

  • 表的修改：

    • alter table 表名 change column 旧列名 新列名 类型：修改列名。
    • alter table 表名 modify column 列名 新类型 约束：修改类型或约束。
    • alter table 表名 add column 列名 类型：添加新列。
      • 可以用first指定新列在第一个，after 列名指定插入在那一列后。
    • alter table 表名 drop colnum 列名：删除列。
    • alter table 旧表名 rename to 新表名 ：修改表名。

  • 表的删除：

    • drop table if exist 表名。

  • 表的复制：

    • create table 新表名 like 旧表名：只复制表的结构。
    • create table 新表名 select * from 旧表名：复制表的结构和内容。也可以修改子查询，只复制一部分。

• 数据类型：

  • 整型：
    • Tinyint：1个字节。
    • Smallint：2个字节。
    • Mediumint：3个字节。
    • Int、integer：4个字节。
    • Bigint：8个字节。
    • int(7)表示显示7位数字。
    • 默认是有符号数，设置无符号数需要创建或修改时使用unsigned。
    • 如果存入的数据超出所能存储的最大范围，则会报异常，并且存入较近的临界值。
    • zerofill表示按创建时传入的指定长度显示位数，不够则零填充，同时也设置为无符号数。
  • 小数：
    • 浮点型：
      • float(M,D)：4个字节。
      • double(M,D)：8个字节。
    • 定点型：
      • dec(M,D)/decimal(M,D)：M+2个字节。
      • 最大取值范围与double相同，有效取值范围由M和D决定。
    • M表示总位数，D表示小数位数。
    • float(5,2)表示显示5位数字，其中2位为小数。
    • 如果存入的数据超出所能存储的最大范围，则会报异常，并且存入较近的临界值。
    • 对于dec/decimal，M默认10，D默认0。
    • 对于float/double，默认根据插入数值的精度决定。
  • 字符型：
    • 短字符：
      • char(M)：M个字符。
      • varchar(M)：M个字符。
      • char是固定长度字符，varchar是可变长度字符。
      • char中M默认为1，varchar中M不可省略。
      • binary/varbinary：M位二进制。
      • enum：枚举类型。
        • 例：列名 enum('a','b')。
      • set：集合类型。与枚举类型，不同的是可以一次插入后边集合中的多个值。
  • 日期型：
    • date：日期，4个字节。
    • datetime：日期和时间，8个字节。
    • timestamp：时间戳，4个字节（会根据时区变化，但是范围较小）。
    • time：时间，4个字节。

• 约束：

  • 用于限制表中的数据，为了保证数据的可靠性。

  • 非空约束：

    • not null：保证这个字段的值不能为空。

  • 默认约束：

    • default：为该字段设置默认值。

  • 主键约束：

    • primary：保证该字段中的值非空且唯一。

  • 唯一约束：

    • unique：保证字段中的值唯一，但可以为空。

  • 检查约束：

    • check：为字段中的值设置取值范围，MySQL中不支持。

  • 外键约束：

    • foreign key：用于限制两个表的关系（在从表中设置），用于保证该字段的值必须来自主表的关联列的值。

  • 列级约束：

    • 约束是针对表中的某一列/字段的。

    • 字段名 类型 约束。

    • 列级约束除了外键约束外均可设置。

    • 例：

      create table stuinfo(
      	id int primary key,
          stuName varchar(20) not null,
          gender char(1) check(gender='男' or gender=‘女’),
          seat int unique,
          age int default 18
      );
      

  • 表级约束：

    • 不在每个字段后声明，而是单独声明。

    • 除了非空和默认约束都支持。

    • constraint 约束名 约束类型(字段)。

    • 例：

      create table stuinfo(
      	id int,
          majorid int,
          
          constraint stu_key primary(id),
          constraint fk_stu_major foreign key(majorid) references major(id)
      ),
      

    • constraint 外键名 foreign key(从表中的外键列) references 主表名(主表关联列)：外键约束。

  • 主键和唯一约束：

    • 主键至多只有一个，唯一可以有多个。
    • 主键不能为空，唯一可以为空。但是唯一约束可以插入多个null。
    • 主键和唯一都可以进行多个字段的组合。

  • 外键约束：

    • 外键约束是在从表中设置的。
    • 从表中外键列的类型要与主表中的关联列兼容。
    • 要求主表中的关联列被主键或者唯一约束（外键约束也可以）。
    • 插入数据时，先插入主表，再插入从表。删除数据时，先删除从表，再删除主表。

  • 修改约束：

    • alter table 表名 modify column 列名 类型 约束：添加列级约束。
    • alter table 表名 modify column 列名 类型：删除列级约束（只对非空和默认约束有效）。
    • alter table 表名 add 约束(字段)：添加表级约束。
    • alter table 表名 drop 约束 约束名：删除表级约束。
      • 删除主键不需要写约束名，而且删除后非空约束还在。
      • 主键删除后，mysql会自动匹配非空唯一约束的列作为名义上的主键。
      • 删除唯一，约束需要写index。
    • 对于主键和唯一约束，可以通过列级或表级的方式进行添加，但是删除只能通过表级约束。

• 级联：

  • 在被外键约束的表之间，删除主表的记录必须先删除从表中对应的记录。
  • 删除主表记录，自动删除从表相关记录，就是级联删除。
    • alter table 从表 add constraint 外键名 foreign key(从表外键列) references 主表(主表关联列) on delete cascade。
  • 删除主表记录，将从表相关记录置为null，就是级联删除。
    • alter table 从表 add constraint 外键名 foreign key(从表外键列) references 主表(主表关联列) on delete set null。

• 标识列（自增长列）：

  • 不用手动插入值，系统提供默认的序列值。起始为1，每次增长1。
  • 在字段后添加auto_increament。
  • 增长步长可以设置：set auto_increament_increament=步长。起始值可以通过对第一个值显式设置。
  • 标识列必须与一个key列（主键、唯一、外键）搭配。
  • 一张表中只能有一个自增长列。
  • 标识列的类型需要是数值类型。
  • 修改设置表示列类型于修改约束。

4、TCL

• 事务控制语言。

• 事务：

  • 一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。
  • 事务的属性：
    • 原子性（Atomicity）：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
    • 一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
    • 隔离性（lsolation）：事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
    • 持久性（Durability）：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。
  • 隐式事务：没有明显的事务开启和结束的标记。比如insert、update、delete语句。
  • 显式事务：事务具有明显的开启和结束的标记。但是需要先禁用事务自动提交（也就是对单条语句的事务）。
  • 在事务中不能使用create、alter、drop等DDL语句，实际上只对DML语句有效。

• 事务流程：

  -- 1、开启事务
  set autocommit=0;	-- 禁用自动提交
  start transaction;	-- 可以不写
  -- 2、编写事务内容
  -- sql语句：select、insert、update、delete
  -- 3、结束事务
  commit;		-- 提交事务
  rollback;	-- 回滚事务
  

• 事务的并发问题：

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

  隔离级别	描述
  读未提交	在事务中，可以读取到未提交的修改，可能出现脏读、不可重复读和幻读
  读已提交	在事务中，可以读取到已提交的修改，可能出现不可重复读和幻读
  可重复读	在事务中，不能读取到其他事务新提交的修改，可能出现幻读
  串行化	在事务中，其他事务要进行插入会阻塞其自身，确保从一个事务中读取相同的行

  • 设置隔离级别：
    • set session transaction isolation level 隔离级别。
    • read uncommitted、read committed、repeatable read、serializable。
    • MySQL默认是可重复读。
    • 查看隔离级别：select @@tx_isolation。
  • 回滚点：
    • 设置回滚点：savepoint 节点名。
    • 回滚到回滚点：rollback 节点名。

5、视图

• 视图：一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存了sql逻辑，不保存查询结果。

  -- 创建
  create view 视图名
  as
  查询语句;
  
  -- 使用
  select * from 视图名;
  
  -- 修改
  create or replace view 视图名
  as
  查询语句;
  
  alter view 视图名
  as 查询语句;
  

• 应用场景：

  • 多个地方用到同样的查询结果
  • 该查询结果使用的sql语句较复杂

• 视图的好处：

  • 重用sql语句
  • 简化复杂的sql操作，不必知道它的查询细节
  • 保护数据，提高安全性

• 创建：

  • create view 视图名 as 查询语句。

• 使用：

  • select * from 视图名。

• 修改：

  • create or replace view 视图名 as 查询语句。
  • alter view 视图名 as 查询语句。

• 删除：

  • drop view 视图名。

• 查看：

  • show create view 视图名。

• 更新内容：

  • insert into 视图名 values(值，...)。
  • update 视图名 set 列名=值。
  • delete from 视图名。
  • 在对视图更新的同时，对原表也进行了更新。
  • 一般对视图中不进行更新，而且许多类型的视图不支持更新：
    • 创建视图的sql语句中包含分组函数、distinct、group by、having、union。
    • 常量视图。
    • 创建视图的sql语句中select中包含子查询。
    • 创建视图的sql语句中用到了join或其他连接（可以修改，不能插入删除）。
    • 创建视图的sql语句中from了一个不能更新的视图。
    • 创建视图的sql语句中where子句的子查询引用了from子句中的表。

• 视图与表：

  • 创建语句不同。
  • 视图没有实际占用数据存储的物理空间。而表实际占有。
  • 视图一般不进行增删改，只进行查询。而表都可以进行。

6、存储过程和函数

• 变量：

  • 系统变量：由系统提供，不是用户定义，属于服务器层面。
    • 全局变量：针对整个服务器，但是不能跨重启。
    • 会话变量：针对客户端的一次连接。
    • 查看所有系统（全局/会话）变量：show global/session variables。
    • 查看部分系统变量：show global/session variables like 匹配值。
    • 查看指定的系统变量：select @@global/session.系统变量名。
    • 系统变量赋值：
      • set global/session 系统变量名=值。
      • set @@global/session.系统变量名=值。
  • 自定义变量：变量是用户自定义的。
    • 用户变量：针对与当前连接有效，与会话变量作用域相同。
      • 声明并初始化：
        • set @用户变量名=值。
        • set @用户变量名：=值。
        • select @用户变量名：=值。
      • 赋值：
        • 使用初始化语句。
        • select 字段 into @变量名 from 表。查出的是一个值。
      • 查看：
        • select @变量名。
    • 局部变量：作用域仅仅在局部（begin end中，且在第一句声明）有效。
      • 声明：declare 变量名 类型 default 值。
      • 赋值：与用户变量类似。
      • 使用：与用户变量类型。
      • 一般不需要加@符号。

• 存储过程：

  • 类似于Java中的方法。提高复用性，简化操作，减少了编译和连接次数，提高效率。

  • 是一组预先编译好的SQL语句的集合。一般用于批量插入和更新。

  • 创建：

    delimiter 结束标记
    create procedure 存储过程名(参数列表)
    begin
    	存储过程体;
    end 结束标记
    

    • 参数列表包含三部分：参数模式、参数名、参数类型。
    • 参数模式分为in、out、inout。
      • in：该参数可以作为输入。
      • out：该参数可以作为输出，即返回值。
      • inout：该参数可以同时作为输入输出。
    • 存储过程体中每条sql语句用分号结束。在存储过程结束使用delimiter指定的结束标记，比如$。

  • 调用：call 存储过程名(实参列表)结束标记。

    • 调用时，如果参数类型是in，可以是常量也可以是变量。
    • 如果是out必须是变量，但可以不提前声明而直接写在实参列表中。
    • 如果是inout，必须是提前声明并赋值的变量。

  • 删除：drop porcedure 存储过程名。

  • 查看：show create porcedure 存储过程名。

• 函数：

  • 与存储过程类似，但是有且仅有一个返回值。

  • 一般用于处理数据后返回一个结果。

  • 创建：

    delimiter 结束标记
    create function 函数名(参数列表) returns 返回类型
    begin
    	函数体;
    	return 值;
    end 结束标记
    

    • 参数列表包括参数名和参数类型，只能是输入参数。
    • return语句不放在函数体的最后也不会报错，但是其后的语句不会被执行。

  • 调用：select 函数名(实参列表)。

  • 查看：show create function 函数名。

  • 删除：drop function 函数名。

7、流程控制结构

• 分支结构：

  • if函数：

    • if(条件表达式，表达式1，表达式2)。
    • 条件表达式为真则返回表达式1，如果为假则返回表达式2。

  • case语句实现switch或多重if。

    • case语句中，then后既可以跟一个值，也可以跟一个语句。
    • 如果then后跟的是一个语句，case语句必须在begin/end存储过程中，且case语句结束需要写case end。

  • if结构：

    if 条件1 then 语句1;
    elseif 条件2 then 语句2;
    
    end if；
    

    • 只能在begin/end中使用。

• 循环结构：

  • while循环：

    循环名: while 循环条件 do
    	循环体;
    end while 循环名;
    

  • loop循环：

    循环名: loop
    	循环体;
    end loop 循环名;
    

    • 如果没有循环控制语句，就成为了死循环。

  • repeat循环：

    循环名: repeat
    	循环体;
    until 结束循环条件
    end repeat 循环名;
    

  • iterate 循环名：类似于continuou。

  • leave 循环名：类似于break。

  • 以上的循环结构只能在begin/end中使用。

iwehdio的博客园：https://www.cnblogs.com/iwehdio/