python 41 数据类型与表的完整性约束，, 目录

目录

数据类型与完整性约束 1. 创建表 2. sql模式：要用严格模式 3. 常用数据类型：用严格模式 3.1 数值类型 3.2 日期类型 3.3 字符串类型 3.4 枚举和集合类型 4. 完整性约束 4.1 not null 4.2 default xx 4.3 unique 4.4 primary key 主键 4.5 auto_increment 4.6 foreign key 外键

数据类型与完整性约束

1. 创建表

# 完整创建表语法create table 表名(字段名1 类型[(宽度) 约束条件],字段名2 类型[(宽度) 约束条件],字段名3 类型[(宽度) 约束条件]);# 注意：1. 在同一张表中，字段名是不能相同2. 宽度和约束条件可选、非必须，宽度指的就是字段长度约束，例如：char(10)里面的103. 字段名和类型是必须的4. 最后一个字段后面不能加逗号

2. sql模式：要用严格模式

select @@sql_mode;  # 查看当前会话模式宽松模式，在插入数据的时候，即便是给了一个错误的数据，也可能会被接受，并且不报错。例如，创建表时，字段 name char(5);当插入数据时，如果输入"abcdefgh",这个数据就会保存为"abcde",后面的"fgh"就被截断，mysql自行处理这个数据。mysql> select @@sql_mode;+------------------------+| @@sql_mode             |+------------------------+| NO_ENGINE_SUBSTITUTION |  ————>默认空值，表示没有什么模式设置+------------------------+严格模式设置方式：1. 设置当前会话的严格模式，超出会报错，不会保存任何数据。   set session sql_mode = ‘STRICT_TRANS_TABLSE’;    2. 设置全局会话严格模式， 但是重启mysql服务后，严格模式会失效。   set global sql_mode = ‘STRICT_TRANS_TABLES’;   设置后，重新打开cmd   mysql> select @@sql_mode;    +---------------------+    | @@sql_mode          |    +---------------------+    | STRICT_TRANS_TABLES |    +---------------------+   3. 在安装目录下 my.ini 文件中,新增 sql_mode=STRICT_TRANS_TABLES   再重启mysql服务。

3. 常用数据类型：用严格模式

3.1 数值类型

不指定符号，默认都是有符号的；加unsigned 可以创建无符号数据类型。

create table t1(id int unsigned);mysql> desc t1;+-------+------------------+------+-----+---------+-------+| Field | Type             | Null | Key | Default | Extra |+-------+------------------+------+-----+---------+-------+| id    | int(10) unsigned | YES  |     | NULL    |       |+-------+------------------+------+-----+---------+-------+int(10):长度最大为10， 如果插入数据超出范围(0—2**64-1)，会报错。  mysql> insert into t1 values(-1);ERROR 1264 (22003): Out of range value for column 'id' at row 1

3.1.1 整型：

整数类型：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT

显示宽度，表示数据的长度，int(11):11位，负号（-）。

类型      大小     范围(有符号)       范围(无符号)      用途tinyint  1字节    (-128—127)         (0—255)       小整数值int      4字节   (-2**32—2**32-1)   (0—2**64-1)    大整数值

# tinyint测试：mysql> create table t2(id tinyint);Query OK, 0 rows affected (0.01 sec)mysql> desc t2;+-------+------------+------+-----+---------+-------+| Field | Type       | Null | Key | Default | Extra |+-------+------------+------+-----+---------+-------+| id    | tinyint(4) | YES  |     | NULL    |       |+-------+------------+------+-----+---------+-------+1 row in set (0.02 sec)mysql> insert into t2(id) values(200);ERROR 1264 (22003): Out of range value for column 'id' at row 1 mysql> insert into t2(id) values(-129);ERROR 1264 (22003): Out of range value for column 'id' at row 1mysql> insert into t2(id) values(0);Query OK, 1 row affected (0.01 sec)

宽松模式下，如果超出范围，以范围边界数据保存数据。

3.1.2 浮点型

作用：存储薪资、身高、温度、体重、体质参数等

1.float[(m,d)] [unsigned] [zerofill]    定义：       单精度浮点数（非准确小数值），m是整数部分+小数部分的总个数，d是小数点后个数。m最大值为255，d最大值为30，例如：float(255,30)    有符号：          -3.402823466E+38 to -1.175494351E-38,          1.175494351E-38 to 3.402823466E+38    无符号：          1.175494351E-38 to 3.402823466E+38    精确度：           随着小数的增多，精度变得不准确2.double[(m,d)] [unsigned] [zerofill]    定义：       双精度浮点数（非准确小数值），m是整数部分+小数部分的总个数，d是小数点后个数。m最大值也为255，d最大值也为30    有符号：    -1.7976931348623157E+308 to -2.2250738585072014E-308    2.2250738585072014E-308 to 1.7976931348623157E+308    无符号：    2.2250738585072014E-308 to 1.7976931348623157E+308          精确度：          随着小数的增多，精度比float要高，但也会变得不准确3.decimal[(m[,d])] [unsigned] [zerofill]    定义：        准确的小数值，m是整数部分+小数部分的总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。比float和double的整数个数少，但是小数位数都是30位。     精确度：         随着小数的增多，精度始终准确         对于精确数值计算时需要用此类型         decimal能够存储精确值的原因在于其内部按照字符串存储。        精度：decimal>double>float

测试：

mysql> create table t3(num1 float(255,30),num2 double(255,30),num3 decimal(65,30));Query OK, 0 rows affected (0.02 sec)mysql> insert into t3(num1,num2,num3) values(    -> 1.111111111111111111111111,    -> 1.111111111111111111111111,    -> 1.111111111111111111111111    -> );Query OK, 1 row affected (0.01 sec)mysql> select * from t3;# 结果看下图 发现float和double的精度都发生改变# 结论：浮点数的精度：decimal>double>float

3.2 日期类型

类型：DATE，TIME，DATETIME ，IMESTAMP，YEAR

作用：存储用户注册时间，文章发布时间，员工入职时间，出生时间，过期时间等

常用：data、datatime

YEAR    YYYY（范围：1901/2155）2018    DATE    YYYY-MM-DD（范围：1000-01-01/9999-12-31）例：2018-01-01 TIME    HH:MM:SS（范围：'-838:59:59'/'838:59:59'）例：12:09:32DATETIME    YYYY-MM-DD HH:MM:SS（范围：1000-01-01 00:00:00/9999-12-31 23:59:59）例： 2018-01-01 12:09:32TIMESTAMP    YYYYMMDD HHMMSS（范围：1970-01-01 00:00:00/2037 年某时）

测试：

mysql> create table t4(d1 year,d2 date,d3 datetime);Query OK, 0 rows affected (0.02 sec)mysql> insert into t4 values(now(),now(),now());Query OK, 1 row affected, 1 warning (0.01 sec)mysql> select * from t4;+------+------------+---------------------+| d1   | d2         | d3                  |+------+------------+---------------------+| 2019 | 2019-08-30 | 2019-08-30 17:18:02 |+------+------------+---------------------+1 row in set (0.00 sec)

3.3 字符串类型

类型：char、varchar

char和varchar括号内的参数指的都是字符的长度。

char(10):最大字符长度10，超出会只存储10个字符（截断）；在严格模式下，超出会报错。

3.3.1 char、varchar类型

#char类型：定长，简单粗暴，浪费空间，存取速度快    字符长度范围：0-255字节（一个中文是一个字符，是utf8编码的3个字节）    存储：        存储char类型的值时，会往右填充空格来满足长度        例如：指定长度为10，存>10个字符则报错(严格模式下)，存<10个字符则用空格填充直到凑够10个字符存储    检索：        在检索或者说查询时，查出的结果会自动删除尾部的空格，如果你想看到它补全空格之后的内容，除非我们打开        pad_char_to_full_length SQL模式（SET sql_mode = 'strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH';）#varchar类型：变长，精准，节省空间，存取速度慢（有计算长度、读取长度时间消耗）    字符长度范围：0-65535字节（如果大于21845会提示用其他类型 。mysql行最大限制为65535字节，字符编码为utf-8：https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html）    存储：        varchar类型存储数据的真实内容，不会用空格填充，如果'ab  ',尾部的空格也会被存起来        强调：varchar类型会在真实数据前加1-2Bytes的前缀，该前缀用来表示真实数据的bytes字节数（1-2Bytes最大表示65535个数字，正好符合mysql对row的最大字节限制，即已经足够使用）        如果真实的数据<255bytes则需要1Bytes的前缀（1Bytes=8bit 2**8最大表示的数字为255）        如果真实的数据>255bytes则需要2Bytes的前缀（2Bytes=16bit 2**16最大表示的数字为65535）    检索：        尾部有空格会保存下来，在检索或者说查询时，也会正常显示包含空格在内的内容

3.3.2 char和varchar性能对比：

? 针对char类型，mysql在存储的时候会将不足规定长度的数据使用后面(右边补全)补充空格的形式进行补全，然后存放到硬盘中，但是在读取或者使用的时候会自动去掉它给你补全的空格内容，因为这些空格并不是我们自己存储的数据，所以对我们使用者来说是无用的。

char和varchar性能对比：
以char(5)和varchar(5)来比较，加入我要存三个人名：sb，ssb1，ssbb2.

char：
优点：简单粗暴，不管你是多长的数据，我就按照规定的长度来存，5个5个的存，三个人名就会类似这种存储：sb ssb1 ssbb2，中间是空格补全，取数据的时候5个5个的取，简单粗暴速度快。
缺点：貌似浪费空间，并且我们将来存储的数据的长度可能会参差不齐。

varchar：
varchar类型不定长存储数据，更为精简和节省空间.
例如存上面三个人名的时候类似于是这样的：sbssb1ssbb2，连着的，如果这样存，请问这三个人名你还怎么取出来，你知道取多长能取出第一个吗？
不知道从哪开始从哪结束，遇到这样的问题，你会想到怎么解决呢？还记的吗？socket？tcp？struct？把数据长度作为消息头。
所以，varchar在存数据的时候，会在每个数据前面加上一个头，这个头是1-2个bytes的数据，这个数据指的是后面跟着的这个数据的长度，1bytes能表示2**8=256，两个bytes表示2**16=65536，能表示0-65535的数字，所以varchar在存储的时候是这样的：1bytes+sb+1bytes+ssb1+1bytes+ssbb2;

? 所以存的时候会比较麻烦，导致效率比char慢，取的时候也慢，先拿长度，再取数据。
? 优点：节省了一些硬盘空间，一个acsii码的字符用一个bytes长度就能表示，但是也并不一定比char省，看一下官网给出的一个表格对比数据，当你存的数据正好是你规定的字段长度的时候，varchar反而占用的空间比char要多。

? 显示为存储在表的最后一行中的值仅在不使用严格模式时适用 ; 如果MySQL在严格模式下运行，则不会存储超过列长度的值，并且会产生错误。

缺点：存取速度慢。

3.3.3 其他部分类型：

longblob类型： 0—4294967295字节      二进制形式的极大文本数据longtext类型： 0—4294967295字节      极大文本数据区别：1._BLOB和_text存储方式不同，_TEXT以文本方式存储，英文存储区分大小写，而_Blob是以二进制方式存储，不分大小写。2._BLOB存储的数据只能整体读出。3._TEXT可以指定字符集，_BLO不用指定字符集。

3.4 枚举和集合类型

枚举类型（enum）:单选，只能在给定的范围内选一个值。# 测试：mysql> create table shirts(name varchar(30),    -> size enum('s','m','l','xl','xxl')    -> );Query OK, 0 rows affected (0.02 sec)mysql> insert into shirts(name,size) values('dress_shirt','l'),('t_shirt','m');Query OK, 2 rows affected (0.00 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> select * from shirts;+-------------+------+| name        | size |+-------------+------+| dress_shirt | l    || t_shirt     | m    |+-------------+------+2 rows in set (0.00 sec)mysql> insert into shirts(name,size) values('s_shirt','xxxl');ERROR 1265 (01000): Data truncated for column 'size' at row 1  # 报错集合类型（set）:多选，在给定的范围内可以选择一个或多个值mysql> create table myset(col set('a','b','c','d'));Query OK, 0 rows affected (0.02 sec)mysql> insert into myset(col) values('a,b'),('c,d'),('a,d,a');Query OK, 3 rows affected (0.01 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> select * from myset;+------+| col  |+------+| a,b  || c,d  || a,d  |+------+3 rows in set (0.00 sec)# 可以看出能够选择多个，而且第三行插入相同元素，也会去重。

4. 完整性约束

4.1 not null

约束字段，字段的值不能为空。

create table t1(id int not null);

测试：

mysql> create table t5(id int not null,num int);Query OK, 0 rows affected (0.02 sec)mysql> desc t5;+-------+---------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id    | int(11) | NO   |     | NULL    |       || num   | int(11) | YES  |     | NULL    |       |+-------+---------+------+-----+---------+-------+2 rows in set (0.01 sec)# 如果只给字段num插入值，会报错，因为字段id不能为空；mysql> insert into t5(num) values(1);ERROR 1364 (HY000): Field 'id' doesn't have a default valuemysql> insert into t5(id) values(1);Query OK, 1 row affected (0.01 sec)

4.2 default xx

设置字段值的默认值。

create table t2(id int not null，sex char(5) default 'male');

测试：

mysql> create table t6(id int not null,sex char(10) default 'male');Query OK, 0 rows affected (0.02 sec)mysql> desc t6;+-------+---------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id    | int(11) | NO   |     | NULL    |       || sex   | char(10)| YES  |     | male    |       |+-------+---------+------+-----+---------+-------+2 rows in set (0.01 sec)# 字段sex不插入值，会默认传入设定的male。mysql> insert into t6(id) values(1);Query OK, 1 row affected (0.01 sec)mysql> select * from t6;+----+------+| id | sex  |+----+------+|  1 | male |+----+------+1 row in set (0.00 sec)# 不需默认值时，给sex字段插入新数据mysql> insert into t6(id,sex) values(2,'female');Query OK, 1 row affected (0.00 sec)mysql> select * from t6;+----+--------+| id |  sex   |+----+--------+|  1 |  male  ||  2 | female |+----+--------+2 rows in set (0.00 sec)

4.3 unique

约束字段的值唯一。身份证号，id等。

create table t3(id int,name char(10) unique);

测试：

mysql> create table t7(id int unique,name char(10));Query OK, 0 rows affected (0.03 sec)mysql> desc t7;+-------+----------+------+-----+---------+-------+| Field | Type     | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| id    | int(11)  | YES  | UNI | NULL    |       || name  | char(10) | YES  |     | NULL    |       |+-------+----------+------+-----+---------+-------+2 rows in set (0.01 sec)# 插入数据mysql> insert into t7 values(1,'aa');Query OK, 1 row affected (0.01 sec)# 如果再插入id字段为 1，会报错mysql> insert into t7 values(1,'aaaaaaa');ERROR 1062 (23000): Duplicate entry '1' for key 'id'

4.4 primary key 主键

约束字段的值不为空且唯一。

一张表有且只有一个主键PRI。

create table t4(id int primary key,name char(0));

测试：

mysql> create table t8(id int primary key,name char(10));Query OK, 0 rows affected (0.02 sec)mysql> desc t8;+-------+----------+------+-----+---------+-------+| Field | Type     | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| id    | int(11)  | NO   | PRI | NULL    |       || name  | char(10) | YES  |     | NULL    |       |+-------+----------+------+-----+---------+-------+2 rows in set (0.02 sec)

4.5 auto_increment

约束字段为自增(如果以前没有数据，默认从1开始；如果有，会在最后一行数据基础上加1（即使delete整个表）)，被约束的字段必须同时被主键primary key约束。

creat table t5(id int primary key uto_increment,    name varchar(10));

测试：

mysql> create table t9(id int primary key auto_increment,    -> name varchar(10)    -> );Query OK, 0 rows affected (0.03 sec)mysql> insert into t9(name) values('aaa'),('bbb'),('ccc');Query OK, 3 rows affected (0.01 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> select * from t9;+----+------+| id | name |+----+------+|  1 | aaa  ||  2 | bbb  ||  3 | ccc  |+----+------+3 rows in set (0.00 sec)# 1.用delete清空t9的数据，再插入数据时，会在上次数据的最后一行基础上 +1mysql> delete from t9;Query OK, 5 rows affected (0.00 sec)mysql> insert into t9(name) values('fff');Query OK, 1 row affected (0.01 sec)mysql> select * from t9;+----+------+| id | name |+----+------+|  4 | fff  |+----+------+1 row in set (0.00 sec)# 2.用truncate清空表后，再插入数据，默认从1开始mysql> truncate table t9;Query OK, 0 rows affected (0.02 sec)mysql> insert into t9(name) values('fff');Query OK, 1 row affected (0.01 sec)mysql> select * from t9;+----+------+| id | name |+----+------+|  1 | fff  |+----+------+1 row in set (0.00 sec)

4.6 foreign key 外键

标明表与表之间的关系，表和表之间的关系：一对一，一对多，多对多

必须先建立被关联表（出版社），再创建关联表（书籍）。

都必须设置primary key 主键。

4.6.1 一对多：

将关系字段加到数据多的表上面。

create table 关联表(id int primary key,    name char(10),    关联字段 类型,    foreign key(关联字段) references 被关联表(被关联字段)    );    例如：出版社对应书籍是多对一的关系1.先创建出版社表——publish表2.创建书籍表——book表,外键写法:    create table book(        id int primary key,        name char(10),        pid int,        foreign key(pid) references publish(id)        );3.先给出版社插入数据

测试：

sql> create table publish(id int primary key,name char(16));Query OK, 0 rows affected (0.03 sec)mysql> create table book(id int primary key,name char(16),    -> pid int,    -> foreign key(pid) references publish(id)    -> );Query OK, 0 rows affected (0.02 sec)mysql> insert into publish values(1,'清华出版社'),(2,"新华出版社"),(3,"工业出版社");Query OK, 3 rows affected (0.01 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> insert into book values(1,'九阴真经',2),(2,'九阳真经',1),(3,'打狗棒',1),    -> (4,'降龙十八掌',3),(5,'六脉神剑',2);# 查找"降龙十八掌"的出版社mysql> select * from publish where id=(select pid from book where name="降龙十八掌");+----+-----------------+| id | name            |+----+-----------------+|  3 | 工业出版社       |+----+-----------------+1 row in set (0.00 sec)

4.6.2 一对一：

那个数据少，关联字段加到那个上面。

需 foreign key + unique 约束。

教育机构中，学生表(student)和客户表(customer)查看某个学生，还是客户时的信息。1.先创建customer表2.再创建学生表：create table student(        id int primary key,        name char(10),        cid int unique,        foreign key(cid) references customer(id)        );3.先给customer表插入数据

测试：

mysql> create table customer(id int primary key,    -> name char(10),    -> qq int    -> );Query OK, 0 rows affected (0.03 sec)mysql> create table student(id int primary key,    -> name char(10),    -> cid int unique,    -> foreign key(cid) references customer(id));Query OK, 0 rows affected (0.03 sec)mysql> insert into customer values(1,'aaa',123),    -> (2,'bbb',456),    -> (3,'ccc',789);Query OK, 3 rows affected (0.01 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> insert into student values(1,'aaaa',1),    -> (2,'cccc',3);Query OK, 2 rows affected (0.01 sec)Records: 2  Duplicates: 0  Warnings: 0# 通过学生表查找cccc学生再客户表中的qq（注：两边名字可不一样，假如客户端是网名）mysql> select qq from customer where     -> id=(select cid from student where name='cccc');+------+| qq   |+------+|  789 |+------+1 row in set (0.00 sec)

4.6.3 多对多：

需新建一个中间关系表。然后用foreign key去约束。

作者和书籍需要借助第三张表来完整两者的关系记录1.先创建作者表（author）与书籍表（books）2.第三张表后创建create table authortobook(        id int primary key,        author_id int,        book_id int,        foreign key(author_id) references author(id),        foreign key(book_id) references books(id)        );

测试：

# 武侠合集由三个人共同完成mysql> create table author(id int primary key,name char(10));Query OK, 0 rows affected (0.02 sec)mysql> create table books(id int primary key,name char(10));Query OK, 0 rows affected (0.02 sec)# 新建联系表mysql> create table authortobooks(id int primary key,author_id int,    -> book_id int,    -> foreign key(author_id) references author(id),    -> foreign key(book_id) references books(id)    -> );Query OK, 0 rows affected (0.02 sec)mysql> insert into author values(1,'金庸'),    -> (2,'古龙'),                        ,    -> (3,'梁羽生');Query OK, 3 rows affected (0.01 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> insert into books values(1,'神雕侠侣'),    -> (2,'鹿鼎记'),    -> (3,'小李飞刀'),    -> (4,'萧十一郎'),    -> (5,'白发魔女'),    -> (6,'七剑下天山'),    -> (7,'武侠合集');Query OK, 7 rows affected (0.01 sec)Records: 7  Duplicates: 0  Warnings: 0mysql> insert into authortobooks values(1,1,1),    -> (2,1,2),    -> (3,1,7),    -> (4,2,3),    -> (5,2,4),    -> (6,2,7),    -> (7,3,5),    -> (8,3,6),    -> (9,3,7);Query OK, 9 rows affected (0.00 sec)Records: 9  Duplicates: 0  Warnings: 0

三张表的关系分析：

# 打印出书籍与作者的对应（这是多张表关联查询的操作，后面会讲到，这里先看下效果）mysql> select books.name '书籍',author.name '作者' from author,books,authortobooks    -> where books.id = authortobooks.book_id and                           -> author.id = authortobooks.author_id;+----------------+-----------+| 书籍            | 作者      |+----------------+-----------+| 神雕侠侣        | 金庸      || 鹿鼎记          | 金庸      || 武侠合集        | 金庸      || 小李飞刀        | 古龙      || 萧十一郎        | 古龙      || 武侠合集        | 古龙      || 白发魔女        | 梁羽生    || 七剑下天山      | 梁羽生    || 武侠合集        | 梁羽生    |+-----------------+-----------+9 rows in set (0.00 sec)

python 41 数据类型与表的完整性约束