表完整性约束
-
创建表的完整语法
create table 表名( 字段名1 类型[(宽度) 约束条件], 字段名2 类型[(宽度) 约束条件], 字段名3 类型[(宽度) 约束条件] );注意事项：
- 在同一表中，字段名不能相同。
- 字段名和类型必须，宽度约束条件可选。
- 最后一个字段不能加逗号。
表中的数据类型
介绍
存储引擎决定了表的类型，表里面的数据也得有类型，数据还得有宽度，宽度是可选的。别问，问就是记。
建表的时候，字段都有对应的数据类型：
整型、浮点型、字符类型（char 与 varchar）、日期类型、枚举与集合
数值类型
强调：对于整型来说，数据类型后的宽度并不是存储限制。
-
整型
-
TINYINT：小整数（默认有符号）
-
有符号：-128 ~ 127
-
无符号：0 ~ 255
-
有符号和无符号TINYINT
=================有符号：默认有符号，即数字前有正负号=========== create table t1(id tinyint); insert into t1 values(-128); #插入成功 insert into t1 values(-129); #插入失败,5.7版本报错：ERROR 1264 (22003): Out of range value for column 'id' at row 1。之前版本不会报错，会将 -129 存成 -128 insert into t1 values(127); #插入成功 insert into t1 values(128); #插入失败，5.7版本报错。之前版本不会报错，会将128存成127 ===================无符号：范围在 0~255====================== create table t2(id tinyint unsigned); insert into t2 values(-1); 插入失败，5.7之前版本会将 -1 存成 0。 其余结果就不一一演示了。 ===================zerofill测试整数类型的显示宽度=============================== mysql> insert into zerofilltable values(1),(11),(111),(1111) mysql> select * from zerofilltable; +------+ | id | +------+ | 001 | | 011 | | 111 | | 1111 | +------+ 4 rows in set (0.00 sec)注意：MySQL中没有布尔值，用 tinyint(1) 构造。
-
-
SMALLINT
-
MEDIUMINT
-
INT：整数
- 有符号：-2147483648 ～ 2147483647
- 无符号：0 ～ 4294967295
-
BIGINT：大整数
- 有符号：-9223372036854775808 ～ 9223372036854775807
- 无符号： 0 ～ 18446744073709551615
-
-
浮点型
- float
- 有符号：-3.402823466E+38 to -1.175494351E-38； 1.175494351E-38 to 3.402823466E+38
- 无符号：1.175494351E-38 to 3.402823466E+38
- double
- 有符号： -1.7976931348623157E+308 to -2.2250738585072014E-308；2.2250738585072014E-308 to 1.7976931348623157E+308
- 无符号：2.2250738585072014E-308 to 1.7976931348623157E+308
======================float [(M,D)] [UNSIGNED] [ZEROFILL]====================== 定义：单精度浮点数(非准确小数值)，m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30 精确度：随着小数的增多，精度变得不准确。 ======================double [(M,D)] [UNSIGNED] [ZEROFILL]====================== 定义：双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30 精确度：随着小数的增多，精度比float要高，但也会变得不准确 ======================decimal [(M,D)] [UNSIGNED] [ZEROFILL]====================== 定义：准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。 精确度：随着小数的增多，精度始终准确。 对于精确数值计算时需要用此类型，decaimal能够存储精确值的原因在于其内部按照字符串存储。 create table t12(id FLOAT(255,30)); create table t13(id DOUBLE(255,30)); create table t14(id DECIMAL(65,30)); insert into t12 values(1.111111111111111111111111111111); insert into t13 values(1.111111111111111111111111111111); insert into t14 values(1.111111111111111111111111111111); mysql> select * from t1; +----------------------------------+ | id | +----------------------------------+ | 1.111111164093017600000000000000 | +----------------------------------+ 1 row in set (0.00 sec) mysql> select * from t2; +----------------------------------+ | id | +----------------------------------+ | 1.111111111111111200000000000000 | +----------------------------------+ 1 row in set (0.00 sec) mysql> select * from t3; +----------------------------------+ | id | +----------------------------------+ | 1.111111111111111111111111111111 | +----------------------------------+ 1 row in set (0.00 sec) 精确度: float < double < decimal - float
时间类型
YEAR
YYYY（1901/2155）
DATE
YYYY-MM-DD（1000-01-01/9999-12-31）
TIME
HH:MM:SS（'-838:59:59'/'838:59:59'）
DATETIME
YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59 Y）
TIMESTAMP
YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）
===================================date,time,datetime===================================
mysql> create table t2(d date, t time, dt datetime);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t2 values(now(), now(), now());
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from t2;
+------------+----------+---------------------+
| d | t | dt |
+------------+----------+---------------------+
| 2019-08-19 | 23:32:13 | 2019-08-19 23:32:13 |
+------------+----------+---------------------+
1 row in set (0.00 sec)
==============================================timestamp==================================
mysql> create table t3(timestamp_ timestamp);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t3 values(now());
Query OK, 1 row affected (0.01 sec)
mysql> select * from t3;
+---------------------+
| timestamp_ |
+---------------------+
| 2019-08-19 23:35:12 |
+---------------------+
1 row in set (0.00 sec)
==============================================year=======================================
mysql> insert into t1 values(now());
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+-----------+
| born_year |
+-----------+
| 2001 |
| 1990 |
| 2017 |
| 2019 |
+-----------+
4 rows in set (0.00 sec)
1. 单独插入时间时，需要以字符串的形式，按照对应的格式插入
2. 插入年份时，尽量使用4位值
3. 插入两位年份时，<=69，以20开头，比如50, 结果2050
>=70，以19开头，比如71，结果1971
MariaDB [db1]> create table t12(y year);
MariaDB [db1]> insert into t12 values
-> (50),
-> (71);
MariaDB [db1]> select * from t12;
+------+
| y |
+------+
| 2050 |
| 1971 |
+------+
datetime 与 timestamp 的区别：
datetime没有默认值时，插入空值会是个null。必须指定默认值：not null default now() 才会自动填入当前时间。
timestamp，不需要任何设置，在传空值的情况下自动传入当前时间。
mysql> create table t1(t datetime);
Query OK, 0 rows affected (0.02 sec)
mysql> create table t2(t timestamp);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t1 values();
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+
| t |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
mysql> insert into t2 values();
Query OK, 1 row affected (0.01 sec)
mysql> select * from t2;
+---------------------+
| t |
+---------------------+
| 2019-08-19 23:49:16 |
+---------------------+
1 row in set (0.00 sec)
mysql> create table t3(t datetime not null default now());
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t3 values();
Query OK, 1 row affected (0.00 sec)
mysql> select * from t3;
+---------------------+
| t |
+---------------------+
| 2019-08-19 23:50:00 |
+---------------------+
1 row in set (0.00 sec)
例题
创建一个学生表，这个学生表字段为：id, 姓名，出生日期，出生年份，注册时间。
create table student(
id int,
name varchar(100),
born_year year,
born_time datetime,
register_time timestamp
);
mysql> desc student;
+---------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+-------------------+-----------------------------+
| id | int(11) | YES | | NULL | |
| name | varchar(100) | YES | | NULL | |
| born_year | year(4) | YES | | NULL | |
| born_time | datetime | YES | | NULL | |
| register_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+---------------+--------------+------+-----+-------------------+-----------------------------+
5 rows in set (0.00 sec)
mysql> insert into student values(1, '游哥', 2001, '2001-9-10');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into student values(1, '游哥', 2001, '2001-9-10', null);
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+------+--------+-----------+---------------------+---------------------+
| id | name | born_year | born_time | register_time |
+------+--------+-----------+---------------------+---------------------+
| 1 | 游哥 | 2001 | 2001-09-10 00:00:00 | 2019-08-19 23:58:51 |
+------+--------+-----------+---------------------+---------------------+
1 row in set (0.00 sec)
字符串类型( var 和 varchar )
create table t15(name char(4));
create table t16(name varchar(4));
-
char：定长，简单粗暴，浪费空间，存取速度快
字符长度范围：0-255（一个中文是一个字符，是utf8编码的3个字节） 存储： 存储char类型的值时，会往右填充空格来满足长度 例如：指定长度为10，存>10个字符则报错，存<10个字符则用空格填充直到凑够10个字符存储 检索： 在检索或者说查询时，查出的结果会自动删除尾部的空格，除非我们打开pad_char_to_full_length SQL模式（SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';） -
varchar：变长，精准，节省空间，存取速度慢（较于char比较慢）
字符长度范围：0-65535（如果大于21845会提示用其他类型 。mysql行最大限制为65535字节 存储： 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） 检索： 尾部有空格会保存下来，在检索或者说查询时，也会正常显示包含空格在内的内容存的时候 需要给数据一个记录长度的报头
取的时候 需要先读取报头才能读取真实数据
-
char 与 varchar :
char 取的时候方便，直接按固定长度取即可。
varchar 取的时候繁琐，无法知道数据多长。
mysql> create table t1(name char(4)); Query OK, 0 rows affected (0.02 sec) mysql> create table t2(name varchar(4)); Query OK, 0 rows affected (0.02 sec) mysql> insert into t1 values('hello'); #超出四个字符报错，不够四个字符空格补全 ERROR 1406 (22001): Data too long for column 'name' at row 1 mysql> insert into t1 values('hell'); Query OK, 1 row affected (0.00 sec) mysql> insert into t2 values('11111111'); #超出四个字符报错，不够四个有几个就存几个 ERROR 1406 (22001): Data too long for column 'name' at row 1 mysql> insert into t2 values('hell'); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; +------+ | name | +------+ | hell | +------+ 1 row in set (0.00 sec) mysql> select * from t2; +------+ | name | +------+ | hell | +------+ 1 row in set (0.00 sec) 了解两个函数 length：查看字节数 char_length:查看字符数 mysql> select char_length(name) from t1; +-------------------+ | char_length(name) | +-------------------+ | 4 | +-------------------+ 1 row in set (0.00 sec) mysql> select char_length(name) from t2; +-------------------+ | char_length(name) | +-------------------+ | 4 | +-------------------+ 1 row in set (0.00 sec) """ 首先应该肯定的是在硬盘上存的绝对是真正的数据，但显示的时候mysql会自动将末尾的空格取掉,如果不想让mysql帮你做自动去除末尾空格的操作，需要再添加一个模式 """ set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH"; ''' 针对char类型，mysql在存储时会将数据用空格补全存放到硬盘中。但是会在读出结果的时候自动取掉末尾的空格 ''' name char(5) # 缺点：浪费空间 # 优点：存取速度都快 egon alex lxx jxx txx name varchar(5) # 缺点：存取速度慢 # 优点：节省空间 1bytes+egon 1bytes+alex 1bytes+lxx 1bytes+jxx 1bytes+txx-
concat
-
mysql> select concat('数据: ',x,'长度: ',char_length(x)),concat(y,char_length(y) ) from t1; +------------------------------------------------+--------------------------+ | concat('数据: ',x,'长度: ',char_length(x)) | concat(y,char_length(y)) | +------------------------------------------------+--------------------------+ | 数据: 你瞅啥 长度: 5 | 你瞅啥 4 | +------------------------------------------------+--------------------------+ 1 row in set (0.00 sec) -
总结
#InnoDB存储引擎：建议使用VARCHAR类型 单从数据类型的实现机制去考虑，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。 但对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的CHAR列不一定比使用可变长度VARCHAR列性能要好。因而，主要的性能因素是数据行使用的存储总量。由于CHAR平均占用的空间多于VARCHAR，因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的。 #其他字符串系列（效率：char>varchar>text） TEXT系列 TINYTEXT TEXT MEDIUMTEXT LONGTEXT BLOB 系列 TINYBLOB BLOB MEDIUMBLOB LONGBLOB BINARY系列 BINARY VARBINARY text：text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符。 mediumtext：A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters. longtext：A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1) characters. -
枚举类型和集合类型
字段的值只能在给定范围内选择，如单选框，多选框。
枚举: enum 多选一
mysql> create table shirts(
-> name varchar(40),
-> size enum('s', 'l', 'x', 'xxl'));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into shirts values('dress', 's'), ('youyiku', 'x');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from shirts;
+---------+------+
| name | size |
+---------+------+
| dress | s |
| youyiku | x |
+---------+------+
2 rows in set (0.00 sec)
=====================================只插入一个值====================================
mysql> insert into shirts(name) values('haha');
Query OK, 1 row affected (0.00 sec)
mysql> select * from shirts;
+---------+------+
| name | size |
+---------+------+
| dress | s |
| youyiku | x |
| haha | NULL |
+---------+------+
3 rows in set (0.00 sec)
===========================枚举类型不一致会报错=========================================
mysql> insert into shirts values('der', 'asd');
ERROR 1265 (01000): Data truncated for column 'size' at row 1
集合：set 多选多
==========================建表==========================
mysql> create table teacher(
-> id int,
-> name char(16),
-> gender enum('male', 'female', 'others'),
-> hobby set('pingpang', 'basketball', 'soccer'));
Query OK, 0 rows affected (0.02 sec)
=========================插入=========================
mysql> insert into teacher values(1, 'egon', 'male', 'soccer,pingpang');
Query OK, 1 row affected (0.00 sec)
注意：插入的时候，set多选的值之间不能有其他的东西，如多了个空格之类的神奇玩意；
========================查询====================
mysql> select * from teacher;
+------+------+--------+-----------------+
| id | name | gender | hobby |
+------+------+--------+-----------------+
| 1 | NULL | female | soccer |
| 1 | egon | male | soccer |
| 1 | egon | male | pingpang,soccer |
+------+------+--------+-----------------+
3 rows in set (0.00 sec)