MySQL-Part3：DML & DDL & 数据类型 & 约束 & 事务 & 视图

1. DML数据操纵语言

   • 1.1 表数据---插入 关键字 insert into

     • 语法1

       insert into tab_name [(字段1，字段2，...)]
       values (v1,v2,...)[,(v1,v2,...),... ];
       
       /**
       (字段1，字段2，...)必须与(v1,v2,...)在数量，类型上一一对应。
       (字段1,字段2，...)可以省略，当然此时后面的values(v1,v2,...)必须与原表各字段对应，即使为空也要标识出来：Null
       支持多行插入，使用逗号来进行间隔；支持子查询语句。
       **/
       
       

     • 语法2

       insert into tab_name
       set 字段1 = v1,字段2 = v2 , ....  ;  #不支持多行插入。
       

     • 语法3

       insert into tab_name [(字段1，字段2，...)]
       select v1,v2,... union
       select v1,v2,... union
       select ...  ;     #union 联合多个子句执行
       

   • 1.2表数据修改/更新 关键字 **update set **

     • ①单表

       update tab_name 
       set 字段1 = v1,字段2 = v2,...
       where 条件;
       

     • ② 多表

       update table_1_name 别名
       [left/right/full ]join table_2_name 别名 on 连接条件
       set 字段1 = v1 , 字段2 = v2 , ...
       where 筛选条件;
       

   • 1.3表数据删除 关键字 **delete from **

     • ①单表

       delete from tab_name 
       [where 筛选条件];
       

     • ② 多表

       #删除张无忌的女朋友的信息
       delete tab_1_alias [, tab_2_alias]
       from tab_1_name  as tab_1_alias 
       [inner/left/right/full] join tab_2_name as tab_2_alias on 连接条件
       [where 筛选条件];
       

     • ③ 直接删除整张表的数据值 关键字 truncate

       Truncate [table ]tab_name ;
       #严格来讲 truncate同drop一样属于DDL，不属于DML
       #truncate 不支持回滚，只能作用于表，效果相当于drop+create table 的执行结果。
       #truncate 操作执行后，具有自增值属性的列的值会从1重新开始自增，而delete是接断点续增。
       #truncate后,表和索引所占用的空间会恢复到初始大小；delete操作不会减少表或索引所占用的空间，drop语句将表所占用的空间全释放掉。
       #truncate 执行后无返回值：‘共0行受到影响’
       

       详细细节可参考博客园MySQL技术的文章，较全面。

   • 1.4 查询表中数据 关键字 select

     条件查询，分组查询，连接查询，联合查询，子查询，排序，分页查询等。
     

2. DDL数据定义语言

   • 2.1数据库

     • ①创建数据库 create database [if not exists] database_name ;

     • ②修改数据库

       • 重命名-------->一般不建议/不可以

         • 替代方式一： 通过移动数据库中所有table到新数据库的方法来实现

           #!/bin/bash
           # 假设将sakila数据库名改为new_sakila
           # MyISAM直接更改数据库目录下的文件即可
           
           mysql -uroot -p123456 -e 'create database if not exists new_sakila'
           #将db中所有的table_name 存入数组list_table中
           list_table=$(mysql -uroot -p123456 -Nse "select table_name from information_schema.TABLES where TABLE_SCHEMA='sakila'")
           
           for table in $list_table
           do
               mysql -uroot -p123456 -e "rename table sakila.$table to new_sakila.$table"
           done
           

         • 替代方式二：整体导出数据库，修改sql语句中的建库语句的db_name之后再导入

           #备份
           mysqldump -u root -p db_name[.table_name] > path/db_name.sql
           
           #vi path/db_name.sql 
           create database new_db_name;
           
           #recovery [op in linux]
           mysql -u root -p db_name <path/db_name.sql;
           #recovery [op in mysql]
           source path/db_name;
           
           

       • 修改数据库字符集 &排序规则

         alter database database_name [default] character set char_set_name
         default collation 与字符集对应的匹配的collation_name;
         

     • ③ 删除数据库 drop database [if exists] database_name;

   • 2.2表

     • 创建表

       create table [if not exists]tab_name(
       	字段1 字段类型 其他限制,
       	字段2 字段类型 其他限制,
           ...
       );
       #限制主要有：是否可为null值，是否为主键，默认值为多少...
       desc tab_name ; # 查看表的结构
       

     • 表结构的修改

       • 修改列名 关键字 change

         alter table table_name 
         change [column] col_name new_col_name col_type [其他限制]
         
         #此操作可以修改列的每个属性，但是，修改列的某一属性时，其他限制条件也不能省略，省略即意味着会自动恢复为默认值，而不是保持当前设置不变。
         

       • 修改列的数据类型/其他限制 关键字 modify

         alter table tab_name 
         modify [column] colname new_coltype [其他限制]
         
         #modify 可以修改除col_name 外的所有列属性，同样，需要设置全面，不能缺省，否则缺省属性会恢复默认值。
         
         #为自增列设置每次自增的值
         alter auto_increment= val_auto_incre;
         

       • 添加/删除 列 关键字 add / drop

         alter table tab_name add [column]  col_name col_type [其他限制] 
         [frist/after col_name]； #指定插入列的具体位置
         
         alter table tab_name drop [column] [if exists] col_name;
         

       • 修改表名(重命名)

         • 方式一：

           #关键语法 ALTER RENAME
           alter table old_table rename to new_table;
           

         • 方式二：

           RENAME TABLE, unlike ALTER TABLE, can rename multiple tables within a single statement:

           #关键字：RENAME TO
           #可同时重命名多个table
           rename table old_table1 to new_table1,
           `````````````old_table2 to new_table2,
           `````````````old_table3 to new_table3;
           

     • 删除表 关键字 drop
       drop table [if exists] tab_name ;

     • 复制表

       #复制整个表的结构
       create table new_cop_tab_name like org_tab_name;
       
       #复制表的部分结构
       create table new_cop_tab_name from
       select 字段1，字段2 
       from org_tab_name
       where 1=2 或者 where 0; 
       
       #将表的结构与数据值同时复制。
       create table new_cop_tab_name
       select */字段1，字段2，... 
       from org_tab_name
       [where clause]; 
       

3. 数据类型

   MySQL supports SQL data types in several categories: numeric types, date and time types, string (character and byte) types, spatial types, and the JSON data type.

   MySQL支持的数据类型有：数值型，时间型，字符串类型，地理空间类型，JSON类型（详细参见手册11章 Data Types）

   • 3.1数值型

     MySQL supports all standard SQL numeric data types. These types include the exact numeric data types (INTEGER, SMALLINT, DECIMAL, and NUMERIC), as well as the approximate numeric data types (FLOAT, REAL, and DOUBLE PRECISION). (详细参见手册11.1节)

     • 各种大小的 int 型 ,包括tinyint,smallint ,mediumint , int(interger),bigint .定义时默认为signed有符号数，若需定义无符号数(非负数)直接添加关键字unsigned.。例：col_name int unsigned; col_name int (8) zerofill unsigned 其中unsigned 可以省略，zerofill表示若赋值位数低于指定位数将自动用零填充。

     • 精确小数型。decimal(总有效位数m，小数点后的位数d) 。例如：decimal(5,2)可表示的数值范围为-999.99-999.99。 关键字decimal 等价于 numeric 。超出范围会收到警告。m,d皆可省略，默认值分别为10,0

     • 浮点型数值 。 ① float(p) ② double precision (m,d)
       

       As of MySQL 8.0.17, the nonstandard FLOAT(M,D) and DOUBLE(M,D) syntax is deprecated and support for it will be removed in a future MySQL version.

       For maximum portability, code requiring storage of approximate numeric data values should use FLOAT or DOUBLE PRECISION with no specification of precision or number of digits.

     • bit型 bit(m) 其中m指的是比特位数，支持1-64。为特定位数的bit型数据赋值时不足自动右面补零。

   • 3.2 字符串型

     The string data types are CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET. (详细参见手册11.3节)

        ① char(m) ,varchar(m) 分别表示固定/自适应长度的字符串类型。char(m) 中'(m)'可以缺      	        省，默认为1。varchar(m)中'm'表示支持的最大长度。
        ② binary(m),varbinary(m) 含义基本同上，只不过是表示二进制值。
        ③ text,blob 用于表示较长的二进制字符串文本。
        ④ enum，set 枚举型数值。有所相似，有所区别。
     

   • 3.3 时间类型

     The date and time data types for representing temporal values are DATE, TIME, DATETIME, TIMESTAMP, and YEAR. Each temporal type has a range of valid values, as well as a “zero” value that may be used when you specify an invalid value that MySQL cannot represent. 详细参加手册11.2节

     其中datetime ,timestamp 处理所占字节数不一样(分别为8,4)，表示范围不一样外，timestamp 还与特定的时区有关(系统变量time-zone) ，不同类型的now() 可以体现。

   • 3.4 地理空间类型（spatial data type ）, JSON数据类型 参见手册11.4 , 11.5节。

   • 3.5 其他有关细节请翻阅手册11章

4. 常见约束

   • 4.1 可以区分为表级约束，列级约束，也可以具体分为六大约束

     • not null （保证该字段非空）, default （保证该字段设置为默认值）, primary key （保证该字段具有唯一性，且非空）, unique（用于保证该字段具有唯一性，可以为空值），check约束（用于检查字段值是否符合条件，MySQL中兼容check约束语法，但是实际无效。），foreign key（用于保证vice_table 的该字段的值必须来自于主表的关联列的值 ，语法：[foreign key(字段名)] reference 主表名（字段名） ) ;

     • 表级约束[constraint cons_name ] constraint_type(字段名[，字段名])

       #位置 :在各个字段定义完毕后
       # 表级约束不支持not null 与 default ， 列级约束外键定义无效，需要在表级约束中指定
       
       #1.添加主键
       [constraint pk_name ]primary key(col_1[,col_2]) ;  
       #此处的pk_name 实际上设置无效，查看时始终显示为‘PRIMARY’。每个table中只能设置一个主键，但多个col可以结合起来作为一个主键。
       
       #2.添加唯一约束
       [constraint uq_name] unique(col_1[,col_2]);
       
       #3.添加check约束
       [constraint ck_name] check(col_name between 20 and 200);
       #4.添加外键约束
       [constraint fk_name] foreign key(col_name) references major_table_name(col_name)
       
       

     • Primary Key 与 Unique 对比

       	是否保证列值唯一性	是否允许为空	一个表中允许有多少个该约束存在	是否允许组合
       primary key	√	×	最多一个	√但不建议
       unique	√	√	可以有多个	√但不建议

       是否允许组合是指，多个字段联合起来作为一个整体具有 主键/唯一约束

     • FOREIGN KEY 约束

       ① 8.0 MySQL的默认engine为InnoDB, 该引擎支持外键(MyISAM不支持，参见博客)。
       ② 外键约束是设置在从表中的字段中的；vice_table's 外键字段的类型与pri_table's对应字段的类型必须一致或兼容，对于名称的一致性无要求；
       ③ 主表的对应字段必须是一个索引(一般为主键或唯一)
       ④ 插入数据时：先主表后从表；删除数据时：先从表后主表；若添加外键时注明on delete cascade,则可以直接删除主表中的数据(级联删除);注明on delete set null可以在删除主表的同时将从表中的对应值置为指定值 .
       ⑤ 具体语法

       #列级语法
       col_name int references main_table_name(主表中对应的col_name)
       #表级语法
       [constraint def_cons_name] foreign key(需要设置外键从表col_name) references main_table_name(主表对应的col_name)
       

     • 修改/添加约束

       #通用：列级添加约束
       alter table tab_name modify column col_name col_type constr_name;
       
       #表级添加约束 (不可添加默认& 非空约束)
       alter table tab_name add [constraint def_cons_name] 
       primary key(col_name),
       unique(col_name),
       check(具体约束),
       foreign key(col_name) references tab_name(col_name) [on delete cascade];
       #删除非空/默认/自增长：  直接modify时省略即可
       
       #删除主键
       alter table tab_name drop primary key;#无需具体指定列名
       #删除唯一 
       alter table tab_name drop index index_name; (show index from table_name)
       #删除外键
       alter table tab_name drop foreign key fk_name ;
       

   • 4.2 查看表中索引：show index from table;

   • 4.3 标识列/自增长列

     • 关键字 auto_increment 。 详细参见手册 "15.6.1.6 AUTO_INCREMENT Handling in InnoDB"

     • 相关系统变量
       ① auto_increment_offset(实际不生效，但可以通过实际执行插入操作时插入指定值)；
       ② auto_increment_increment(每次的增长步长)

     • 只适用于具有主键/外键/唯一等这些约束的列；一个表中最多只能有一个标识列(自增长列)；标识列的类型只能是数值型。

     • In MySQL 8.0, a server restart does not cancel the effect of the AUTO_INCREMENT = N table option. If you initialize the auto-increment counter to a specific value, or if you alter the auto-increment counter value to a larger value, the new value is persisted across server restarts.

       After the auto-increment counter is initialized, if you do not explicitly specify an auto-increment value when inserting a row, InnoDB implicitly increments the counter and assigns the new value to the column. If you insert a row that explicitly specifies an auto-increment column value, and the value is greater than the current maximum counter value, the counter is set to the specified value.

       InnoDB uses the in-memory auto-increment counter as long as the server runs. When the server is stopped and restarted, InnoDB reinitializes the auto-increment counter, as described earlier.

       The auto_increment_offset configuration option determines the starting point for the
       AUTO_INCREMENT column value. The default setting is 1. (set auto_increment_offset val_ue) （该操作设置无效）

       The auto_increment_increment configuration option controls the interval between successive column values. The default setting is 1set auto_increment_increment=val_ue;

     #查看Innodb存储引擎的自增mode，处于模式0,1时每次分别生成自增值1,4(默认初始值为100)；当处于mode2时，默认初始值为1。
     
     select @@innodb_autoinc_lock_mode; (0,1,2) 
     
     #修改自增列下次自增起始值（处于mode2时，该值必须大于已有值中的最大值）
     ① alter table table_name auto_increment=next_val_init; 
     ② insert into table_name set auto_incr_col_name=next_val_init;
     #设置该值后，该列仍然是以offset为基准自增，col_vaule仍然满足：auto_increment_offset+auto_increment_increment*n ,该操作只不过是确保了下次自增的起始值大于next_val_init;
     
     
     
     #查看当前设置的自增初始值&自增间隔
     show variables like '%auto_increment%';
     
     

5. TCL事务控制语言

   • 5.1 事务：一个或一组sql语句组成一个执行单元，这些语句之间相互依赖，这个执行单元要么全部执行，要么全部不执行(某条执行失败，整个单元将会回滚，回复执行之前的状态)。

   • 5.2 show engines 查看当前mysql 支持的引擎类别具体信息。

   • 5.3 事务的ACID属性：

     • 原子性(Atomicity,事务中的操作要么都被成功执行，要么都不被执行) ；
     • 一致性(Consistency，事务必须使数据库从一个一致性状态变换到另一种一致性状态) ；
     • 隔离性(Isolation，各事务之间的执行是相互隔离的不会相互干扰)
     • 持久性(Durability，事务一旦被执行，它对数据库的改变就是永久性的。)

   • 5.4 事务的分类
     ①. 隐式事务：没有明显的开启或结束标志，例如常见操作：insert ,delete ,update语句
     ②. 显示事务：事务具有明显的开启或结束标志。 必须将系统变量autocommit设置为off,禁用自动提交：show variables like 'autocommit; set autocommit=0;

   • 5.5 具体步骤

     • 开启事务 set autocommit = 0 ;[start transaction;]
     • 具体语句：insert，update，delete ，clause;
     • 结束事务：commit; 或者 rollback;

   • 5.6 并发事务

     • 一个事务与其他事务隔离的程度称为隔离级别。数据库规定了多种事务隔离级别，不同隔离级别对应不同的干扰程度，级别越高，数据一致性就越好，但也意味着并发性越弱。

     • MySQL支持四种隔离级别：read uncommitted （A未提交，B就可读）; read committed（A提交后，B才可读） ;repeatable read (只有在A提交后才开启的C事务才可读取到修改，此期间一直开启的B事务读取到的数据保持在它自己开启前已有的状态，一般为默认级别);serializable read （对数据库的操作转变为了单程进行，A事务操作期间，其他事务的所有操作将被阻塞。不支持并发，效率低下。） 各种细节详见manual 15.7.2.1 Transaction Isolation Levels

     • 常见的并发问题：

       • ① 脏读(read uncommitted 级别下：B事务读取了A事务做了修改，但还没有commit的数据)
       • ②不可重复读(read committed级别下：在B事务两次读取数据间隔期间，A事务修改数据后进行了commit操作，彻底更新了数据）
       • ③ 幻读 (repeatable read级别下：由于B事务的插入操作，导致A事务select与update行数不一致)

       

     • 可以通过命令select @@transaction_isolation;来查看当前当前设置的隔离级别；

     • 设置当前会话的隔离级别set session transaction isolation level 隔离级别;
       设置数据库系统的全局隔离级别set global transaction isolation level 隔离级别

     • savepoint 节点名 ，搭配 'rollback to 节点名 ' 使用

6. 视图

   • 6.1

     • 视图是一种虚拟存在的表，是通过表动态生成的数据，只保存sql逻辑，不保存实际的查询结果。

     • 应用场景：多个地方用到同样的查询结果 & 该查询结果所使用的sql语句较为复杂。可以类似理解为暂时性的将通用的sql语句打包起来。

   • 6.2 语法

     #创建视图
     create view view_name as
     具体的通用的查询语句；
     
     
     #使用视图
     select 字段 from view_name where等子句(当前的特殊查询条件)
     
     #修改视图
     create or replace view view_name as 或者 alter view view_name as 
     具体的sql语句;
     
     #删除视图
     drop view view_name;
     
     #查看视图结构
     DESC view_name ; 或者  show create view view_name ;
     
     # 查看[当前数据库]已创建的视图的[所有]详细信息
     show tables;
     select * from information_schema.views [where table_schema=db_name][where table_name=view_name]
     
     #可以对视图中的数据进行手动的的插入/删除,对视图进行 更新(修改) 存在很多限制，一般会报错。一旦成功，与视图相关的原始表中的数据也会随之修改。
     insert into view_name [字段] values (具体的值)；
     update view_name set 字段= 字段值 where 条件;
     delete view_name where 条件;