欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mysql的数据类型与表约束

程序员文章站 2023-11-29 20:49:22
数据类型 (详细数据类型请参考:http://www.runoob.com/mysql/mysql-data-types.html) 数字 整型 tinyint int bigint 小数: float 在小数点后 位数比较短的情况下不精准; double 在小数点后 位数比较长的情况下不精准 字符 ......

数据类型

(详细数据类型请参考:)

 数字

  整型   tinyint  int  bigint

  小数: float  在小数点后 位数比较短的情况下不精准;

      double 在小数点后 位数比较长的情况下不精准

mysql的数据类型与表约束

 

 字符串

  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;
mysql的数据类型与表约束
 -------------------------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)
mysql的数据类型与表约束

 

  日期类型

  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
mysql的数据类型与表约束


        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)
    
    mysql的数据类型与表约束

    注意: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 |
+------+

 

 字符类型

  详情参考

    注意: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)

 

 补充:

mysql的数据类型与表约束
#常用字符串系列: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.
view code

 

 枚举类型和集合类型

  字段的值只能在给定范围中选择,如单选框,多选框

  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)

mysql的数据类型与表约束

 

 

完整性约束

  简述

  约束条件与数据类型的宽度一样,都是可选参数

  作用: 用于保证数据完整性和一致性

  主要分为:

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的数据类型与表约束
默认为空示例

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指定的默认值
view code

 

  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');

 

  联合唯一: 

  只要被联合的字段有一列不同,则符合联合唯一的约束.

mysql的数据类型与表约束

 

   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 '6' 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)

  注意:

mysql的数据类型与表约束

 

   补充:

查看可用的 开头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的数据类型与表约束
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) 
);

#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)