数据类型
(详细数据类型请参考:http://www.runoob.com/mysql/mysql-data-types.html)
数字
整型 tinyint int bigint
小数: float 在小数点后 位数比较短的情况下不精准;
double 在小数点后 位数比较长的情况下不精准
字符串
char (10):简单粗暴,浪费空间,存取速度快
varchar:精准;节省空间,存取速度较慢
时间类型: 常用 datetime (年月日时分秒)
枚举类型与集合类型 enum(多选一)和set(多选多)
数值类型
整数数据类型:tinyint smallint mediumint int bigint
作用:存储年龄,等级,id,各种号码等
范围都顾头顾尾
========================================
tinyint[(m)] [unsigned] [zerofill] 小整数，数据类型用于保存一些范围的整数数值范围：
有符号(默认)：
范围:-128 ～ 127(顾头顾尾) mysql> create table t1(x tinyint);
mysql> insert into t1 values(-128);
无符号：
创建表时定义记录的字符为无符号类型,要使用unsigned
范围: 0 ～ 255(顾头顾尾) mysql> create table t2(x tinyint unsigned);
mysql> insert into t2 values(255);
注意:若插入的数字范围超过规定则会报错.
PS： MySQL中无布尔值，使用tinyint(1)构造。 ========================================
int[(m)][unsigned][zerofill] 整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-2147483648 ～ 2147483647
无符号：
0 ～ 4294967295 ========================================
bigint[(m)][unsigned][zerofill]
大整数，数据类型用于保存一些范围的整数数值范围：
有符号：
-9223372036854775808 ～ 9223372036854775807
无符号：
0 ～ 18446744073709551615
int类型后面的存储是显示宽度,而不是存储宽度
若未达到显示的宽度,空出的位置则会被占用,超出显示宽度的部分正常显示,
该宽度只是指定查询结果的显示宽度,与存储范围和类型包含的值的范围无关.
有符号和无符号的最大数字需要的显示宽度均为10，而针对有符号的最小值
则需要11位才能显示完全，所以int类型默认的显示宽度为11是非常合理的.
mysql> create table t3(id int(1) unsigned);
#插入255555记录也是可以的
mysql> insert into t3 values(255555);
mysql> select * from t3;
+--------+
| id |
+--------+
| 255555 |
+--------+
以上操作还不能够验证，再来一张表验证用zerofill 用0填充
# zerofill 用0填充
mysql> create table t4(id int(5) unsigned zerofill);
mysql> insert into t4 value(1);
Query OK, 1 row affected (0.00 sec)
#插入的记录是1，但是显示的宽度是00001
mysql> select * from t4;
+-------+
| id |
+-------+
| 00001 |
+-------+
row in set (0.00 sec)
浮点型:
定点浮点型:DEC等同于DECIMAL
浮点类型: FLOAT DOUBLE
作用:储存薪资,身高体重,体质参数等;
语法:
-------------------------FLOAT-------------------
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
参数解释：单精度浮点数（非准确小数值），M是全长,即显示的宽度，D是小数点后个数。M最大值为255，D最大值为30
unsigned定义为无符号型,常与zerofill搭配,若实际显示宽度不满足给定的宽度,则用0补充.
有符号：
-3.402823466E+38 to -1.175494351E-38,
1.175494351E-38 to 3.402823466E+38 无符号：
1.175494351E-38 to 3.402823466E+38
精确度： 随着小数的增多，精度变得不准确 ****
mysql> create table t3(x float(255,30));
Query OK,0 rows affected (0.03 sec)
mysql> create table t4(y float(255,30) unsigned zerofill);
Query OK, 0 rows affected (0.27 sec)
insert into t4 values(1.0111123111);
Query OK, 0 rows affected (0.14 sec);
mysql> desc t4;
mysql> select *from t4;
-------------------------DOUBLE-----------------------
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要高，但也会变得不准确
mysql> create table t5(x double(255,30));
Query OK, 0 row affected (0.03 sec)
--------------------DECIMAL------------------------
decimal[(m[,d])] [unsigned] [zerofill] 参数解释：准确的小数值，M是整数部分总个数（负号不算），D是小数点后个数。 M最大值为65，D最大值为30。 精确度：随着小数的增多，精度始终准确
对于精确数值计算时需要用此类型
decaimal能够存储精确值的原因在于其内部按照字符串存储。 mysql> create table t6(x decimal(65,30));
Query OK, 0 rows affected (0.00 sec)
日期类型
date time datetime timestamp year
作用:存储用户注册时间,文章发布时间,员工入职时间,出生时间,过期时间等.语法：
YEAR
YYYY（1901~2155）
mysql> create table t6(year_t year);#无论year制定何种宽度,最后默认是year(4).
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t8 values
-> (1905),
-> (2018);
Query OK, 2 rows affected (0.00 sec) #插入记录成功
Records: 2 Duplicates: 0 Warnings: 0
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）
mysql> create table t7(d date,t time,dt datetime);#d为文件或id名,date为类型.
Query OK, 0 rows affected (0.25 sec)
注意:now()为mysql自带函数,获取当前类型指定的时间.
TIMESTAMP(了解)
YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）
mysql> create table t10(time timestamp);
Query OK, 0 rows affected (0.06 sec)
mysql> insert into t10 values();
Query OK, 1 row affected (0.00 sec)
mysql> insert into t10 values(null);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t10;
+------+
| time |
+------+
| NULL |
| NULL |
+------+
mysql> insert into t10 values(now());
Query OK, 1 row affected (0.01 sec)
mysql> select * from t10;
+---------------------+
| time |
+---------------------+
| 2018-06-09 09:44:48 |
+---------------------+
row in set (0.01 sec)
datetime与timestamp的区别
在实际应用的很多场景中，MySQL的这两种日期类型都能够满足我们的需要，存储精度都为秒，但在某些情况下，会展现出他们各自的优劣。
下面就来总结一下两种日期类型的区别。 1.DATETIME的日期范围是1001——9999年，TIMESTAMP的时间范围是1970——2038年。 2.DATETIME存储时间与时区无关，TIMESTAMP存储时间与时区有关，显示的值也依赖于时区。在mysql服务器，
操作系统以及客户端连接都有时区的设置。 3.DATETIME使用8字节的存储空间，TIMESTAMP的存储空间为4字节。因此，TIMESTAMP比DATETIME的空间利用率更高。 4.DATETIME的默认值为null；TIMESTAMP的字段默认不为空（not null）,默认值为当前时间（CURRENT_TIMESTAMP），
如果不做特殊处理，并且update语句中没有指定该列的更新值，则默认更新为当前时间。
注意事项:
============注意啦，注意啦，注意啦===========
#1. 单独插入时间时，需要以字符串的形式，按照对应的格式插入
#2. 插入年份时，尽量使用4位值
#3. 插入两位年份时，<=69，以20开头，比如50, 结果2050
>=70，以19开头，比如71，结果1971
create table t12(y year);
insert into t12 values (50),(71);
select * from t12;
+------+
| y |
+------+
| 2050 |
| 1971 |
+------+
字符类型
详情参考https://dev.mysql.com/doc/refman/5.7/en/char.html
注意:char和varchar括号内的参数指的都是字符的长度
length():查看字节数 char_length():查看字符数
char类型: 定长 ,简单粗暴 浪费空间 存取速度快
字符长度范围:0-255(一个中文是一个字符,utf8三个字节)
存储：
存储char类型的值时，会往右填充空格来满足宽度
例如：指定宽度为10，存>10个字符则报错，存<10个字符则用空格填充直到凑够10个字符存储
检索：
在检索或者说查询时，查出的结果会自动删除尾部的空格，除非我们打开
pad_char_to_full_length SQL模式
（设置SQL模式：SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';查询sql的默认模式：select @@sql_mode;）
vachar类型:变长,精准 节省空间,存取速度较慢
字符长度范围：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） 检索：
尾部有空格会保存下来，在检索或者说查询时，也会正常显示包含空格在内的内容
char填充空格来达到规定的显示宽度,但在查询时不会显示填充的部分,需要修
改sql_mode让其现出原形.
# 创建t1表,分别指明字段x为char类型，字段y为varchar类型
mysql> create table t1(x char(5),y varchar(4));
#此时规定char类型的显示宽度为5,varchar显示宽度为4.
Query OK, 0 rows affected (0.16 sec) # char存放的是5个字符，而varchar存4个字符
mysql> insert into t1 values('你谁啊 ','你谁呀 ');
Query OK, 1 row affected (0.01 sec) # 在检索时char却不显示填充的宽度部分，而varchar存了多少，就显示多少
mysql> select x,char_length(x),y,char_length(y) from t1;
+-----------+----------------+------------+----------------+
| x | char_length(x) | y | char_length(y) |
+-----------+----------------+------------+----------------+
| 你瞅啥 | 3 | 你瞅啥 | 4 |
+-----------+----------------+------------+----------------+
row in set (0.02 sec) #略施小计，让char现原形
mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
Query OK, 0 rows affected (0.00 sec) #查看当前mysql的mode模式
mysql> select @@sql_mode;
+-------------------------+
| @@sql_mode |
+-------------------------+
| PAD_CHAR_TO_FULL_LENGTH |
+-------------------------+
row in set (0.00 sec) 显示出本有的宽度
mysql> select x,char_length(x) y,char_length(y) from t1;
+-------------+------+----------------+
| x | y | char_length(y) |
+-------------+------+----------------+
| 你瞅啥 | 5 | 4 |
+-------------+------+----------------+
row in set (0.00 sec) 查看字节数
char类型：3个中文字符+2个空格=11Bytes
#三个中文字符为9个字节,之前规定的显示宽度为5,所以要填充俩空格.
varchar类型:3个中文字符+1个空格=10Bytes
mysql> select x,length(x),y,length(y) from t1;
+-------------+-----------+------------+-----------+
| x | length(x) | y | length(y) |
+-------------+-----------+------------+-----------+
| 你谁啊 | 11 | 你谁呀 | 10 |
+-------------+-----------+------------+-----------+
row in set (0.02 sec)
补充:
#常用字符串系列：char与varchar
注：虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡 #其他字符串系列（效率：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 单选 只能在给定的范围内选一个值,如性别 sex 男 male/女 female
set 多选 在给定的范围内可以选择一个或一个以上的值(爱好1,爱好2...)
mysql> create table consumer(
-> id int,
-> name varchar(50),
-> sex enum('male','female','other'),
-> level enum('vip1','vip2','vip3','vip4'),#在指定范围内，多选一
-> fav set('play','music','read','study') #在指定范围内，多选多
-> );
Query OK, 0 rows affected (0.03 sec) mysql> insert into consumer values
-> (1,'赵云','male','vip2','read,study'),
-> (2,'赵云2','other','vip4','play');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from consumer;
+------+---------+-------+-------+------------+
| id | name | sex | level | fav |
+------+---------+-------+-------+------------+
| 1 | 赵云 | male | vip2 | read,study |
| 2 | 赵云2 | other | vip4 | play |
+------+---------+-------+-------+------------+
rows in set (0.00 sec)
完整性约束
简述
约束条件与数据类型的宽度一样,都是可选参数
作用: 用于保证数据完整性和一致性
主要分为:
PRIMARY KEY (PK) #标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK) #标识该字段为该表的外键
NOT NULL #标识该字段不能为空
UNIQUE KEY (UK) #标识该字段的值是唯一的
AUTO_INCREMENT #标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT #为该字段设置默认值 UNSIGNED #无符号
ZEROFILL #使用0填充
说明:
#1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
#2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
sex enum('male','female') not null default 'male' #必须为正值（无符号） 不允许为空 默认是20
age int unsigned NOT NULL default 20
# 3. 是否是key
主键 primary key
外键 foreign key
索引 (index,unique...)
not null与default
是否可空 ,null表示空 , 非字符串
not null 不可空 null 可空(一般不写出来)
默认值,创建列时可以指定默认值,当插入数据时如果未主动设置,则自动添
加默认值.
默认为空示例 mysql> create table t11(id int);# id字段默认可以为空
Query OK, 0 rows affected (0.05 sec) mysql> desc t11;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
row in set (0.03 sec)
mysql> insert into t11 values(); #给t11表插一个空的值
Query OK, 1 row affected (0.00 sec) #查询结果如下
mysql> select * from t11;
+------+
| id |
+------+
| NULL |
+------+
row in set (0.00 sec) 不为空示例 mysql> create table t12(id int not null);#设置字段id不为空
Query OK, 0 rows affected (0.03 sec) mysql> desc t12;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
+-------+---------+------+-----+---------+-------+
row in set (0.01 sec) mysql> insert into t12 values();#不能插入空
ERROR 1364 (HY000): Field 'id' doesn't have a default value 若为空,则设置默认值
第一种情况
mysql> create table t13(id int default 1);#若为空字段,则设置id字段默认值为1.
Query OK, 0 rows affected (0.03 sec) mysql> desc t13;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | 1 | |
+-------+---------+------+-----+---------+-------+
row in set (0.01 sec) mysql> insert into t13 values();#为空字段,默认插入default指定值 1.
Query OK, 1 row affected (0.00 sec) mysql> select * from t13;
+------+
| id |
+------+
| 1 |
+------+
row in set (0.00 sec) # 第二种情况
mysql> create table t14(id int not null default 2);#同上一种情况,只是写上了not null.
Query OK, 0 rows affected (0.02 sec) mysql> desc t14;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | | 2 | |
+-------+---------+------+-----+---------+-------+
row in set (0.01 sec) mysql> select * from t14;
+----+
| id |
+----+
| 2 |
+----+
row in set (0.00 sec) 设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值
unique 单列唯一
假如:创建一个公司部门表,每个部门只能创建一个,但是在操作中却能创建
多个,这是不符合逻辑的.那么就可以使用unique,来对公司部门的字段进行
约束设置.
#第一种创建unique的方式
create table department(
id int unique,
name char(10) unique #若后面插入的名字重复,则会报错.
);
insert into department values(1,'it'),(2,'sale'); #第二种创建unique的方式
create table department(
id int,
name char(10) ,
unique(id),
unique(name)
);
insert into department values(1,'it'),(2,'sale');
联合唯一:
只要被联合的字段有一列不同,则符合联合唯一的约束.
primary key (主键)
一个表中可以单列做主键 或多列做主键(符合主键)
约束:等价于 not null unique,字段的值不为空且唯一
存储引擎默认是 innodb:对于innodb存储来说,一张表必须有一个主键.
单列主键
# 创建t14表，为id字段设置主键，唯一的不同的记录
create table t14(
id int primary key,
name char(16)
); insert into t14 values
(1,'xiaoma'),
(2,'xiaohong'); mysql> insert into t14 values(2,'wxxx');
ERROR 1062 (23000): Duplicate entry '' for key 'PRIMARY' # not null + unique，相当于给id设置primary key
create table t15(
id int not null unique,
name char(16)
);
mysql> create table t15(
-> id int not null unique,
-> name char(16)
-> );
Query OK, 0 rows affected (0.01 sec) mysql> desc t15;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | char(16) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
rows in set (0.02 sec)
复合主键
create table t16(
ip char(15),
port int,
primary key(ip,port)
); insert into t16 values
('1.1.1.2',80),
('1.1.1.2',81);
auto_increment
约束的字段为自动增长,约束的字段必须被key约束
若约束字段为id 在不指定id时,自动增长: # 创建student
create table student(
id int primary key auto_increment, #约束id为自动增长.
name varchar(20),
sex enum('male','female') default 'male'
); mysql> desc student;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | YES | | male | |
+-------+-----------------------+------+-----+---------+----------------+
rows in set (0.17 sec) mysql> insert into student(name) values ('老白'),('小白');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from student;
+----+--------+------+
| id | name | sex |
+----+--------+------+
| 1 | 老白 | male |
| 2 | 小白 | male |
+----+--------+------+
rows in set (0.00 sec)
指定id时,优先在原有的最后一条记录后面依次显示指定的id mysql> insert into student values(4,'asb','female');
Query OK, 1 row affected (0.00 sec) mysql> insert into student values(7,'wsb','female');
Query OK, 1 row affected (0.01 sec) mysql> select * from student;
+----+--------+--------+
| id | name | sex |
+----+--------+--------+
| 1 | 老白 | male |
| 2 | 小白 | male |
| 4 | asb | female |
| 7 | wsb | female |
+----+--------+--------+
rows in set (0.00 sec) # 再次插入一条不指定id的记录，会在之前的最后一条记录继续增长
mysql> insert into student(name) values ('大白');
Query OK, 1 row affected (0.00 sec) mysql> select * from student;
+----+--------+--------+
| id | name | sex |
+----+--------+--------+
| 1 | 老白 | male |
| 2 | 小白 | male |
| 4 | asb | female |
| 7 | wsb | female |
| 8 | 大白 | male |
+----+--------+--------+
rows in set (0.00 sec)
注意:
补充:
查看可用的 开头auto_inc的词
mysql> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
rows in set (0.02 sec)
# 步长auto_increment_increment,默认为1
# 起始的偏移量auto_increment_offset, 默认是1 # 设置步长 为会话设置，只在本次连接中有效
set session auto_increment_increment=5; #全局设置步长 都有效。
set global auto_increment_increment=5; # 设置起始偏移量
set global auto_increment_offset=3; #强调：If the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored.
翻译：如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset的值会被忽略 # 设置完起始偏移量和步长之后，再次执行show variables like'auto_inc%';
发现跟之前一样，必须先exit,再登录才有效。 mysql> show variables like'auto_inc%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 5 |
| auto_increment_offset | 3 |
+--------------------------+-------+
rows in set (0.00 sec) #因为之前有一条记录id=1
mysql> select * from student;
+----+---------+------+
| id | name | sex |
+----+---------+------+
| 1 | xiaobai | male |
+----+---------+------+
row in set (0.00 sec)
# 下次插入的时候，从起始位置3开始，每次插入记录id+5
mysql> insert into student(name) values('ma1'),('ma2'),('ma3');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from student;
+----+---------+------+
| id | name | sex |
+----+---------+------+
| 1 | xiaobai | male |
| 3 | ma1 | male |
| 8 | ma2 | male |
| 13 | ma3 | male |
+----+---------+------+ auto_increment_increment和 auto_increment_offset
清空表区分delete和truncate的区别:
delete from t1:如果有自增id,新增的数据,仍然是以删除前的最后一样作为始.
truncate table t1: 数据量大,删除速度比上一个快,且直接从0开始.
mysql> delete from student;
Query OK, 5 rows affected (0.00 sec) mysql> select * from student;
Empty set (0.00 sec) mysql> select * from student;
Empty set (0.00 sec) mysql> insert into student(name) values('ysb');
Query OK, 1 row affected (0.01 sec) mysql> select * from student;
+----+------+------+
| id | name | sex |
+----+------+------+
| 9 | ysb | male |
+----+------+------+
row in set (0.00 sec) #应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它
mysql> truncate student;
Query OK, 0 rows affected (0.03 sec) mysql> insert into student(name) values('xiaobai');
Query OK, 1 row affected (0.00 sec) mysql> select * from student;
+----+---------+------+
| id | name | sex |
+----+---------+------+
| 1 | xiaobai | male |
+----+---------+------+
row in set (0.00 sec) mysql> 对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
对于自增的字段,在用delete删除后,再插入值,该字段仍按照删除前的位置继续增长
foreign key
之前创建表的时候都是在一张表中添加记录,表与表之间没有联系,若让表与表
之间有关联,可以使用foreign key.
#1.创建表时先创建被关联表即主表，再创建关联表即从表 # 先创建被关联表（dep表）
create table dep(
id int primary key,
name varchar(20) not null,
descripe varchar(20) not null
); #再创建关联表（emp表）
create table emp(
id int primary key,
name varchar(20) not null,
age int not null,
dep_id int,
constraint fk_dep foreign key(dep_id) references dep(id)
# constraint 外键名字 foreign key(从表中关联列名字) references dep(主表中被关联列名字)
注意:constraint fk_dep 给这个PKEY约束功能增加一个名字而已
); #2.插入记录时，先往被关联表中插入记录，再往关联表中插入记录 insert into dep values
(1,'IT','IT技术有限部门'),
(2,'销售部','销售部门'),
(3,'财务部','花钱太多部门'); insert into emp values
(1,'zhangsan',18,1),
(2,'lisi',19,1),
(3,'egon',20,2),
(4,'yuanhao',40,3),
(5,'alex',18,2); 3.删除表
#按道理来说，删除了部门表中的某个部门，员工表的有关联的记录相继删除,其实不行。
mysql> delete from dep where id=3;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db5`.`emp`, CONSTRAINT `fk_name` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`)) #但是先删除从表中员工表的记录之后，再删除主表当前部门就没有任何问题 mysql> delete from emp where dep =3;
Query OK, 1 row affected (0.00 sec) mysql> select * from emp;
+----+----------+-----+--------+
| id | name | age | dep_id |
+----+----------+-----+--------+
| 1 | zhangsan | 18 | 1 |
| 2 | lisi | 18 | 1 |
| 3 | egon | 20 | 2 |
| 5 | alex | 18 | 2 |
+----+----------+-----+--------+
rows in set (0.00 sec) mysql> delete from dep where id=3;
Query OK, 1 row affected (0.00 sec) mysql> select * from dep;
+----+-----------+----------------------+
| id | name | descripe |
+----+-----------+----------------------+
| 1 | IT | IT技术有限部门 |
| 2 | 销售部 | 销售部门 |
+----+-----------+----------------------+
rows in set (0.00 sec)
同步删除,同步更新
on delete cascade 同步删除 on update cascade 同步更新
create table emp(
id int primary key,
name varchar(20) not null,
age int not null,
dep_id int,
constraint fk_dep foreign key(dep_id) references dep(id)
on delete cascade #同步删除
on update cascade #同步更新
); #再去删被关联表(dep)的记录，关联表(emp)中的记录也跟着删除
mysql> delete from dep where id=3;
Query OK, 1 row affected (0.00 sec) mysql> select * from dep;
+----+-----------+----------------------+
| id | name | descripe |
+----+-----------+----------------------+
| 1 | IT | IT技术有限部门 |
| 2 | 销售部 | 销售部门 |
+----+-----------+----------------------+
rows in set (0.00 sec) mysql> select * from emp;
+----+----------+-----+--------+
| id | name | age | dep_id |
+----+----------+-----+--------+
| 1 | zhangsan | 18 | 1 |
| 2 | lisi | 19 | 1 |
| 3 | egon | 20 | 2 |
| 5 | alex | 18 | 2 |
+----+----------+-----+--------+
rows in set (0.00 sec) #再去更改被关联表(dep)的记录，关联表(emp)中的记录也跟着更改 mysql> update dep set id=222 where id=2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0 # 查看一下两张表是否都被删除了，是否都被更改了
mysql> select * from dep;
+-----+-----------+----------------------+
| id | name | descripe |
+-----+-----------+----------------------+
| 1 | IT | IT技术有限部门 |
| 222 | 销售部 | 销售部门 |
+-----+-----------+----------------------+
rows in set (0.00 sec) mysql> select * from emp;
+----+----------+-----+--------+
| id | name | age | dep_id |
+----+----------+-----+--------+
| 1 | zhangsan | 18 | 1 |
| 2 | lisi | 19 | 1 |
| 3 | egon | 20 | 222 |
| 5 | alex | 18 | 222 |
+----+----------+-----+--------+
rows in set (0.00 sec)
注意:无论是数据类型还是约束,都是为了保证数据的完整性和一致性.