DDL语言——库和表的创建、修改、删除

DDL语言

DDL：Data Definition Language

DDL允许用户定义数据，也就是创建表、删除表、修改表结构这些操作。通常，DDL由数据库管理员执行。

下面的中括号【】内的内容是可选的。

1. 库的管理

• 库的创建

  create database 【if not exists】 库名;
  

• 库的修改

  #更改库的字符集
  alter database 库名 character set 新的字符集;
  

• 库的删除

  drop database 【if exists】 库名;
  

2. 表的管理

2.1. 表的创建

• 语法

  create table 【if not exists】 表名(
  	列名 列的类型【(长度) 约束】,
      列名 列的类型【(长度) 约束】,
      ...
      列名 列的类型【(长度) 约束】
  );
  

  示例：

  CREATE TABLE book(
  	id INT, #编号
      bNane VARCHAR(20), #图书名
      price DOUBLE, #价格
      authorid INT, #作者编号
      publishdate DATETIME #出版日期
  );
  

  create table author(
  	id int,
  	au_name varchar(20),
  	nation varchar(10)
  );
  

2.2. 表的修改

• 语法

  alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;
  

  • 修改列名

    #修改表book中的列publishdate，将列名改为pubDate
    alter table book change column publishdate pubDate datetime;
    

  • 修改列的类型或约束

    #修改表book中的列pubDate的类型，改为timestamp类型
    alter table book modify column pubDate timestamp;
    

  • 添加新列

    #给表author添加新列annual，并设置类型为double，放在列au_name后面
    alter table author add column annual double after au_name;
    

  • 删除列

    #删除表author中的列annual
    alter table author drop column annual;
    

  • 修改表名

    #将表author的表名改为book_author
    alter table author rename to book_author;
    

2.3. 表的删除

• 语法

  drop table 【if exists】 表名;
  

2.4. 表的复制

• 仅复制表的结构

  create table 新表名 like 要复制的表名;
  

• 复制表的结构和数据

  #复制表的全部数据
  create table 新表名
  select * from 要复制的表名;
  

  #复制部分数据
  create table 新表名
  select 查询列表 from 要复制的表名 where 筛选条件;
  

  #复制表的部分结构，不复制数据
  create table 新表名
  select 查询列表 from 要复制的表名 where 0;
  

  可以跨库复制表，只需要把要复制的表名改为库名.表名

2.5. 数据类型

• 数值型
  • 整型
    • tinyint：字节数1，有符号是-128_{127，无符号是0}255
    • smallint：字节数2
    • mediumint：字节数3
    • int / integer：字节数4
    • bigint：字节数8
  • 整型的特点
    • 默认是有符号，若要设置无符号，可以在后面加unsigned
    • 如果插入的数值超出了整型的范围，会报out of range异常，并且插入临界值
    • 如果不设置长度，会有默认长度，长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用
  • 小数
    • 定点数
      • dec(M,D)或decimal(M,D)：字节数是M+2，最大取值范围与double一样，但精度更高
    • 浮点数
      • float(M,D)：字节数是4
      • double(M,D)：字节数是8
  • 小数的特点
    • M表示整数位数+小数位数，D表示保留的小数位数，如果超出范围，则插入临界值。
    • (M,D)可以省略，如果是decimal，则M默认为10，D默认为0；如果是float和double，则会根据插入的数值的精度来决定精度
    • 定点型数据的精度较高，如果要求插入数值的精度较高如货币运算可以考虑
• 字符型
  • 较短的文本：
    • char(M)：固定长度的字符；M是最多字符数，可以省略，默认为1；效率较高
    • varchar(M)：可变长度的字符；M是最多字符数，不可省略；效率较低
    • binary和varbinary：用于保存较短的二进制
    • mnum(枚举列表)：保存枚举
    • set(列表)：保存集合
  • 较长的文本：
    • text
    • blob（较长的二进制数据）
• 日期型
  • date：字节数为4，只保存日期
  • datetime：字节数为8，保存日期+时间，时间不受时区影响，时间范围为1000~9999年
  • timestamp：字节数为4，保存日期+时间，时间戳，时间会受时区影响，时间范围为1970~2038年
  • time：字节数为3，只保存时间
  • year：字节数为1，只保存年份

2.6. 约束

一种限制，用来限制表中的数据，为了保证表中数据的准确和可靠性

• 约束分类

  • not null：用于保证该字段的值不能为空
  • default：用于保证该字段有默认值
  • primary key：用于保证该字段的值具有唯一性，并且非空
  • unique：用于保证该字段的值具有唯一性，可以为空
  • check：检查约束【mysql中不支持】
  • foreign key：用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值

• 创建表时添加约束的语法

  create table 表名(
  	字段名 字段类型 列级约束,
      ...
      字段名 字段类型 列级约束,
      表级约束
  );
  

  • 列级约束：六大约束在语法上都支持，但外键约束没有效果

  • 列级约束示例

    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,#默认约束
        majorid int foreign key references major(id) #外键，引用主表major中的列id的值
    );
    #查看stuinfo表中的所有索引，包括主键、外键、唯一
    show index from stuinfo;
    

  • 表级约束：除了非空、默认，其他约束都支持

    #在各个字段的最下面加下面的内容：
    【constraint 约束名】 约束类型(字段名)
    

  • 表级约束示例

    create table stuinfo(
    	id int,
        stuname varchar(20),
        gender char(1),
        seat int,
        age int,
        majorid int,
        【constraint pk】 primary key(id),#为id加主键约束
        【constraint uq】 unique(seat),#为seat加唯一键约束
        【constraint ck】 cheak(gender='男' or gender='女'),#检查gender
        【constraint fk_stuinfo_major】 foreign key(majorid) references major(id)#外键
    );
    

  • 通用写法

    create table stuinfo(
    	id int primary key,#主键
        stuname varchar(20) not null,#非空
        gender char(1),
        seat int unique,#唯一
        age int default 18,#默认约束
        majorid int,
        constraint fk_stuinfo_major foreign key(majorid) references major(id)
    );
    

• 一个表中最多只能有一个主键，可以有多个唯一键。允许多个列组合成一个主键，也允许多个列组合成一个唯一键，但不推荐这么使用。

• 外键

  • 要求在从表中设置外键关系
  • 从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
  • 主表的关联列必须是一个key，一般是主键或唯一
  • 插入数据时应先插入主表，再插入从表；删除数据时应先删除从表，再删除主表

• 修改表时添加约束

  • 语法

    #添加列级约束
    alter table 表名 modify column 字段名 字段类型 新约束;
    #添加表级约束
    alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;
    

  • 示例

    alter table stuinfo modify column stuname varchar(20) not null;
    alter table stuinfo modify column age int default 18;
    alter table stuinfo modify column id int primary key;
    alter table stuinfo add primary key(id);
    alter table stuinfo modify column seat int unique;
    alter table stuinfo add unique(seat);
    alter table stuinfo add constraint fk_stuinfo_major foreign key(majorid) references major(id);
    

• 修改表时删除约束

  • 删除非空约束

    alter table stuinfo modify column stuname varchar(20);
    

  • 删除默认约束

    alter table stuinfo modify column age int;
    

  • 删除主键

    alter table stuinfo drop primary key;#由于只有一个主键，可以不写列名
    

  • 删除唯一键

    alter table stuinfo drop index seat;
    

  • 删除外键约束

    alter table stuinfo drop foreign key fk_stuinfo_major;
    

2.7. 标识列

• 创建表时通过auto_increment设置标识列

  create table tab(
  	id int primary key auto_increment,
      name varchar(20)
  );
  

• 标识列默认起始值是1，步长是1，插入数据时可以不用赋值，自动递增

• 特点

  • 一个表中最多只能有一个标识列
  • 标识列必须和一个key搭配，不一定是和主键搭配
  • 标识列的类型只能是数值型
  • 标识列可以通过set auto_increment_increment=3设置步长，可以通过手动插入值设置起始值

• 修改表时设置标识列

  alter table tab modify column id int primary key auto_increment;
  

• 修改表时删除标识列

  alter table tab modify column id int;#使用修改列约束的形式不能删除主键，id列的主键仍在，但是自增长被删除