MySQL笔记（2）-- CURD多表操作、库表操作、约束条件、字段类型

• insert语法：方式一支持插入多行，方式二不支持；方式一支持子查询，方式二不支持

  方式一：insert into tableName(column,column...) values(str,str...);
  方式二：insert into tableName set column=str,column=str...;
  子查询插入：insert into tableName(column,column...) 子查询
  (1)insert into tableName(column,column...) select str,str....;
  (2)insert into tableName(column,column...) select str,str.... from tableName where 条件;
  使用union进行多行插入：
  insert into tableName 
  select str,str... union
  select str,str... union
  ....

• update更新多表联合语法

  sql92语法:
    update tableName 别名1,tableName 别名2 ...
    set 别名1.column=value,别名2.column=value....
    where 连接条件【表1和表2的连接条件】
    and 筛选条件；
  sql99语法：
    update tableName 别名1
    inner|left|right join tableName 别名2
    on 连接条件
    set 别名1=value....
    where 连接条件　　

• delete多表删除语法：truncate删除全表，自增字段索引重置为0；单表delete from 表名 where 条件【 limit 条目】；truncate不支持事务，无法进行回滚

  sql92语法:
    delete 别名1,别名2 ...
    from tableName 别名1,tableName 别名2...
    where 连接条件【表1和表2的连接条件】
    and 筛选条件；
  sql99语法：
    delete 别名1,别名2 ...
    from tableName 别名1
    inner|left|right join tableName 别名2
    on 连接条件
    where 连接条件
  truncate语法：truncate table tableName;　　

• 库表操作：【】表示可省略
  • 库创建：create database [if not exists]库名
  • 库修改：rename database 旧名 to 新名
  • 更改库的字符集：alter database 库名 character set 编码格式(gbk)
  • 删除库：drop database if  exists 库名
  • 表创建： create table if not exists 表名（ 列名 列类型【（长度）约束】......）
  • 表修改：
    • alter table 表名 add|drop|modify|change column 列名 【列类型 约束】
    • alter table 旧表名  rename to 新表名
    • 修改列名：alter table 表名 change 【column】 旧列名 新列名 类型
    • 修改列的类型和约束：alter table 表名 modify column 列名 类型
    • 列添加： alter table 列名 add column 列名 类型 【first|after 列名】
    • 删除列：alter table 表名 drop column 列名
    • 表删除：drop table if exists 表名
    • 查看表信息：desc 表名； 查看当前库的所有表 show tables; 
    • 复制表的结构：create table 新表名 like 库中的表名  
      • 复制表的结构和数据：create table NewTableName select * from 库表名
      • 复制部分的结构和数据：create table NewTableName select column,column.. from 库表名 where 条件
      • 复制部分的结构：create table 新表名 select column,column.. from 库表名 where  0【条件是false】
• 约束条件：主键和唯一都可以保证数据的唯一；主键不能存null，唯一可以存null，但只能存一个null;主键最多只有1个，唯一可以多个。主键和唯一都可以组合，等插入的值判断是根据组合判断，比如唯一UNIQUE(id,number)，数据（1,1)和(1,2)、(1,2)和(2,2)没问题，（1,1)和(1,1)就报错不行。【不推荐进行组合，不稳定】；外键得先删除从表再删除主表，外键在从表中，该外键必须是主表的主键。
  • 六大约束：
    • NOT NULL：非空，用于保证该字段的值不能为空，比如姓名，学号等；
    • DEFAULT：默认，用于保证该字段有默认值，比如性别；
    • PRIMARY KEY：主键，用于保证该字段的值具有唯一性，并且非空，比如学号，编号等；
    • UNIQUE：唯一，用于保证该字段的值具有唯一性，可以为空，比如座位号；
    • CHECK：检查约束【MySQL中不支持】，比如年龄，性别；
    • FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自与主表的关联列的值，在从表中添加外键约束，用于引用主表中某列的值，比如学生表的专业编号等；
  • 约束的分类：
    • 列级约束：六大约束语法上都支持，但外键的约束没有效果
      • 添加列级约束：

        alter table tableName modify column 字段名 字段类型 新约束；

    • 表级约束：除了非空，默认，其他的都支持
      • 添加表级约束：在各个字段的最下面：【constraint 约束名】约束类似（字段名）

        alter table tableName add 【constraint 约束名】约束类型（字段名）【外键的引用】；

  • 查看表的所有索引，包括主键、外键、唯一：

    show index form tableName;

• 标识列设置自动增长，只能是数值，默认值是1，最多只能设置一列为自增长；
• 字段类型：
  • 整数：默认有符号，int unsigned 表示int无符号类型，即不允许存储负数，int(7) zerofill 表示插入的数据不够7位进行剩余位数左填充补上0【使用zerofill默认表示无符号，不支持负数】【为整型类型指定宽度，例如int(11)，对大多数应用是没有意义的，它不会限制值的合法范围，只是规定了MySQL的一些交互工具（比如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，int(1)和int(20)是相同的。】

• • 小数：定点型的精度比浮点型高，如果要求插入的数据精度较高比如货币运算，考虑使用定点型；
    • M表示整数部分+小数部分，D表示小数部分，如果超过位数，则使用临界值；字段设置定点型没有限定M和D，M默认是10，D默认是0，当插入数据的小数点后面有数据时，会发出警告；浮点型没有限定M和D，则字段的精度与插入数据的精度一致；【MySQL5.0及其以上版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字），例如DECIMAL(18,9)小数点两边将各存储9个数字，一个使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占用1个字节。DECIMAL只是一种存储格式，在计算中DECIMAL会转换为double类型。】
    • 节省空间可以使用float，使用字节少的类型；
  • 字符型：char【M默认为1】存储固定长度字符，字段存储在寄存器中，因为它的大小是已知的，这使得搜索和索引更快；varchar存储可变长度字符，空间耗费节省，存储位置不固定，必须使用引用表来查找内容，查找效率低。【固定长度值可以存储char，比如sex为1/0，或男女】
  • binary和varbinary保存二进制字符串
  • 枚举：要求插入的值必须属于列表中指定的值之一，就是说在创建字段为enum类型时，需指定列表值是什么，进行insert时，插入的值在指定列表值中，如果插入的不在指定范围内，默认插入为空字符串，插入字母，忽略大小写
    • 如果列表成员为1-255，则需要1个字节存储；
    • 如果列表成员为255-65535，则需要2个字节存储；
    • 最多可以有65535个不同的列表成员（实际限制小于3000）；
    • 每个成员被分配从1开始的索引值；
    • 空字符串作为错误值的索引值为0，可以使用select语句找出那些被指定无效枚举值的数据行；

      SELECT * FROM tableName WHERE enum_col=0;

    • NULL的索引为NULL；
    • 枚举值不能是0或空字符串；
    • enum在底层的存储方式是以整型进行存储的，比如这样的字段sex enum('male' , 'female' , 'both' , 'unknow')在查询时where sex='male'和where sex=1是等效的；
  • set不区分大小写，用于保存集合：和enum枚举类型类型，可以保存0-64个成员。【set类型一次可以选取多个成员作为插入值，enum枚举只能选一个】
    • 根据成员个数的不同，存储所占的字节也不同

      成员数           字节数
      1-8                 1
      9-16                2
      17-24               3
      25-32               4
      33-64               8
      create table tab_set(
          set1 set('a','b','c','d')
      )
      insert into tab_set values('a');
      insert into tab_set values('a,b');
      insert into tab_set values('a,c,d');

  • 日期型：查看当前时区show variables like 'time_zone'；设置为其他时区 set time_zone='+9:00'；datetime不受时区影响，timestamp受时区影响。