MYSQL数据完整性约束

DDL

1. 库的管理
   • 创建数据库

   #如果已经存在则不创建 if exists
   create database if exists books;
   

   • 更改库的字符集

   alter database books character set gbk;
   

   • 库的删除

   #如果存在则删除
   drop database if no exists books;
   

2. 表的管理
   • 表的创建: 长度与约束可选填

   create table 表名(
       列名 列的类型(长度) 约束,
       列名 列的类型(长度) 约束,
       列名 列的类型(长度) 约束
   )
   

   • 表的修改: alter table 表名 add|drop|modify|change column 列名 列类型(可选) 约束(可选)
     • 修改列名
     • 类型或约束

     alter table 表名 modify column 列名 类型;
     

     • 添加列

     alter table 表名 add column 新列名 类型;
     

     • 删除列

     alter table 表名 drop column 列名;
     

     • 修改列名

     alter table 表名 change column 旧列名 新列名 类型;
     

     • 修改表名

     alter table 原表名 rename to 新表名;
     

   • 表的删除

     #如果存在的话就删除
     drop table if exists 表名;
     

   • 表的复制
     • 仅复制表的结构

     # 复制表的全部结构
     create table 新表名 like 旧表;
     # 复制表的部分结构
     create table 新表名  select 列1,列2 from 旧表 where 1 = 2;
     

     • 复制表的结构和数据

     # 复制表的全部数据
      create table 新表名 like 旧表 select * from 旧表;
     # 复制表的部分数据
     create table 新表名 like 旧表 select * from 旧表 where 筛选条件;
     

数据类型

1. 数值型

   • 整型

   create table tab_int(
       t1 int,# 有符号
       t2 int unsigned #无符号
   )
   

   • 特点
     • 如果不设置无符号还是有符号,默认是有符号,如果设置无符号,需要添加unsigned关键字
     • 如果插入的数据超出了整型的范围,会报out of range异常,并且插入临界值
     • 如果不设置长度,会有默认的长度
     • 长度代表了显示的最大宽度,如果不够会用0在左边填充,但必须搭配zerofill使用

   整数类型	字节	范围
   Tinyint	1	有符号:-128~127 无符号:0~255
   Smallint	2	有符号:-32768~32767 无符号:0~65535
   Mediumint	3	有符号:-8388608~8388607 无符号:0~1677215
   Int、Integer	4	有符号: -2147483648~2147483647 无符号:0~4294967295
   Bigint	8	有符号: -9223372036854775808~9223372036854775807 无符号:0~9223372036854775807*2+1

   • 小数

     • 定点数

     定点类型	字节	范围
     DEC(M,D) DECIMAL(M,D)	M+2	最大取值范围与double相同,给定decimal的有效取值返回有M和D决定

     • 浮点数

     浮点类型	字节	范围
     float(M,D)	4	±1.75494351E+38~±3.402823466E+38
     double(M,D)	8	±2.2250738585072014E-308~±1.7976931348623157E+308

     • 特点:
       • M: 整数部位+小数部位的总长度
       • D: 小数部位(保留几位小数)
       • M与D: 可以省略
         • float: 会根据数值的插入的精度来决定精度
         • double: 会根据数值的插入的精度来决定精度
         • DECIMAL: 默认M为10,D为0
       • 定典型的精确度较高,如果要求插入数值的精度较高则考虑使用

2. 字符型

   • 较短的文本: char、varchar
   • 特点:
     • char(M)
       • 固定长度的字符,比较耗费
       • 效率高
       • 默认为1
     • varchar(M)
       • 不能省略长度
       • 可变长度的字符,比较节省空间
       • 效率低

   字符串类型	最多字符类型	描述及需求
   char(M)	M	M为0~255之间的整数
   varchar(M)	M	M为0~65535之间的整数

   • 较长的文本: text、blob(较长的二进制数据)

3. 日期型

   日期和时间类型	字节	最小值	最大值
   date	4	1000-01-01	9999-12-31
   datetime	8	1000-01-01 00:00:00	9999-12-31 23:59:59
   timestamp	4	19700101080001	2038年的某个时刻
   time	3	-838:59:59	838:59:59
   year	1	1901	2155

常见约束

含义:一种限制,用于限制表中的数据,为了保证该表中的数据的准确和可靠性

1. NOT NULL: 非空,用于保证该字段的值不能为空

2. DEFAULT: 默认,用于保证该字段有默认值

3. PRIMARY KEY: 主键,用户保证该字段的值具有唯一性,并且非空

4. UNIQUE: 唯一,用于保证该字段的值具有唯一性,可以为空

5. check: 检查约束

6. FOREIGN KEY：外键,用于限制两个表的关系,用于保证该字段的值必须来自于主表的关联列的值

7. 分类:

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

     create database students;
     
     create table stuinfo(
         id int primary key,#主键
         stuName varchar(20) not null,#非空
         gender char(1) check(gender='男' or gender='女'),#检查 mysql当中不支持
         seat int unique,#唯一
         age int default 18,#默认约束
         majorId int  references major(id) #外键,列级约束中mysql不支持
     );
     
     create table major(
         id int primary key,
         majorName varchar(20)
     )
     

   • 表级约束:
     除了非空、默认,其他的都支持
     • 语法: constraint 约束名 约束类型(字段名),其中constraint 约束名可以省略

      create table stuinfo(
         id int,
         stuName varchar(20),
         gender char(1),
         seat int,
         age int,
         majorId int,
         constraint pk primary key(id),#主键
         constraint uq unique(seat),#唯一键
         constraint ck check(gender = '男' or gender = '女'), #检查
         constraint fk_stuinfo_major foreign key(majorId) references major(id) #外键
     );
     

标识列
又称:主键自增长
标示: auto_increment

create table tab_identity(
    id int primary key auto_increment;
)