MySQL学习笔记-3

DML数据操纵语言

• 1.1 向表中插入数据 关键字 insert

  • ① 写法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 [(字段1，字段2，...)]
    select v1,v2,... union
    select v1,v2,... union
    select ...  ;#union 联合多个子句执行
    

  • ③ 写法3

    insert into tab_name
    set 字段1 = v1,字段2 = v2 , ....  ;
    

• 1.2修改(更新)表中数据 关键字 update

  • ①单表

    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

  • ①单表

    delete from tab_name 
    [where 筛选条件];
    

  • ② 多表

    delete tab_1_rename [, tab_2_rename]
    from tab_1_name  as tab_1_rename 
    [inner/left/right/full] join tab_2_name as tab_2_rename on 连接条件
    [where 筛选条件];
    

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

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

    详细细节可参考的文章，较全面。

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

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

DDL数据定义语言

• 2.1数据库

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

  • ②修改数据库

    • 数据库一般不建议/不可以进行重命名操作

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

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

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

• 2.2表

  • ①创建表

    create table [if not exists]tab_name(
    字段1 字段类型 其他限制 ,
    字段2 字段类型 其他限制 ,
    ...);
    
    desc tab_name ; # 查看表的结构
    

  • ②修改表

    • 修改列名 关键字change

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

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

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

    • 添加/删除 列 关键字 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;
      

    • 修改表名(重命名) 关键字 rename to
      alter table tab_name rename to new_tab_name ;

  • ③删除表 关键字 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]; 
    

数据类型

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 可以省略，若赋值位数低于指定位数将自动用零填充。② 精确小数型。decimal(总有效位数m，小数点后的位数d) 。例如：decimal(5,2)可表示的数值范围为-999.99-999.99。 关键字decimal 等价于 numeric 。超出范围会收到警告。m,d皆可省略，默认分别值分别为10,0
  ③ 近似浮点型。 不建议使用，将来版本中将会移除该数据类型。
  ④ 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节。

常见约束

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

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

  • 表级约束

    #位置 :在各个字段明确完毕后
    # 表级约束不支持not null 与default
    [constraint cons_name ] cons_type(字段名[，字段名]) 
    

  • Primary Key 与 Unique 对比

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

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

  • 外键约束

    ① 8.0 MySQL的默认engine为InnoDB, 该引擎支持外键(MyISAM不支持，参见)。
    ② 外键约束是设置在从表中的字段中的；从表的外键字段的类型与主表的对应字段的类型必须一致或兼容，对于名称的一致性无要求；
    ③ 主表的对应字段必须是一个索引(一般为主键或唯一)
    ④ 插入数据时：先主表后从表；删除数据时：先从表后主表；若添加外键时注明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 col_name;
    #删除外键
    alter table tab_name drop foreign key col_name ;
    

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

• 4.3 标识列/自增长列

  • 关键字 auto_Increment
  • 相关系统变量
    ① auto_increment_offset(实际不生效，但可以通过实际执行插入操作时手动设置起始值)；
    ② auto_increment_increment(每次的增长步长)
  • 只适用于具有主键/外键/唯一等这些约束的列；一个表中最多只能有一个标识列(自增长列)；标识列的类型只能是数值型。

TCL事务控制语言

• 5.1 事务：一个或一组sql语句组成一个执行单元，这些语句之间相互依赖，这个执行单元要么全部执行，要么全部不执行(某条执行失败，整个单元将会回滚，回复执行之前的状态)。
• 5.2 show engines 查看当前mysql 支持的引擎类别具体信息。
• 5.3 事务的ACID属性：原子性(Atomicity) , 一致性(Consistency) , 隔离性(Isolation各事务之间的执行是相互隔离的不会相互干扰)，持久性(Durability，事务一旦被执行，它对数据库的改变就是永久性的。)
• 5.4 事务的分类
  ①. 隐式事务：没有明显的开启或结束标志，例如常见操作：insert ,delete ,update语句
  ②. 显示事务：事务具有明显的开启或结束标志。 必须将系统变量autocommit设置为off,禁用自动提交：set autocommit=0;
• 5.5 具体步骤
  ① 开启事务 set autocommit = 0 ;start transaction;
  ②具体语句：insert，update，delete clause;
  ③ 结束事务：commit; 或者rollback;
• 5.6 并发事务
  • 一个事务与其他事务隔离的程度称为隔离级别。数据库规定了多种事务隔离级别，不同隔离级别对应不同的干扰程度，级别越高，数据一致性就越好，但也意味着并发性越弱。
  • MySQL支持四种隔离级别：read uncommitted ; read committed ;repeatable read (一般为默认级别);serializable read.
  • 常见的并发问题：① 脏读(一个事务读取了另一个事务还没有提交的更新后的数据) ②不可重复读(一个事务多次读取某数据时结果不一致) ③ 幻读 (一个事务读取了另一个事务还没有提交的插入后的数据)
  • 可以通过命令select @@tx_isolation;来查看当前当前设置的隔离级别；
  • 设置当前会话的隔离级别set session transaction isolation level 隔离级别;
    设置数据库系统的全局隔离级别set global transaction isolation level 隔离级别
  • savepoint 节点名 ，搭配 'rollback to 节点名 ' 使用

视图

• 6.1 视图是一种虚拟存在的表，而且是在使用视图本身的过程中动态生成的。应用场景：多个地方用到同样的查询结果&该查询结果所使用的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 ; 或者  select create view view_name ;
  
  #可以对视图中的数据进行手动的的插入/删除,对视图进行 更新(修改) 存在很多限制，一般会报错。一旦成功，与视图相关的原始表中的数据也会随之修改。
  insert into view_name [字段] values (具体的值)；
  update view_name set 字段= 字段值 where 条件;
  delete view_name where 条件;