当前位置:Gxlcms > 数据库问题 > MySQL四-2:完整性约束

MySQL四-2:完整性约束

时间:2021-07-01 10:21:17 帮助过:12人阅读

null==================== mysql> create table t1(id int); #id字段默认可以插入空 mysql> desc t1; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ mysql> insert into t1 values(); #可以插入空 mysql> create table t2(id int not null); #设置字段id不为空 mysql> desc t2; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | +-------+---------+------+-----+---------+-------+ mysql> insert into t2 values(); #不能插入空 ERROR 1364 (HY000): Field id doesnt have a default value ==================default==================== #设置id字段有默认值后,则无论id字段是null还是not null,都可以插入空,插入空默认填入default指定的默认值 mysql> create table t3(id int default 1); mysql> alter table t3 modify id int not null default 1; ==================综合练习==================== mysql> create table student( -> name varchar(20) not null, -> age int(3) unsigned not null default 18, -> sex enum(male,female) default male, -> hobby set(play,study,read,music) default play,music -> ); mysql> desc student; +-------+------------------------------------+------+-----+------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------------------------+------+-----+------------+-------+ | name | varchar(20) | NO | | NULL | | | age | int(3) unsigned | NO | | 18 | | | sex | enum(male,female) | YES | | male | | | hobby | set(play,study,read,music) | YES | | play,music | | +-------+------------------------------------+------+-----+------------+-------+ mysql> insert into student(name) values(egon); mysql> select * from student; +------+-----+------+------------+ | name | age | sex | hobby | +------+-----+------+------------+ | egon | 18 | male | play,music | +------+-----+------+------------+ 验证

三 unique

  1. ============设置唯一约束 UNIQUE===============<span style="color: #000000">
  2. 方法一:
  3. create table department1(
  4. id int,
  5. name varchar(</span>20<span style="color: #000000">) unique,
  6. comment varchar(</span>100<span style="color: #000000">)
  7. );
  8. 方法二:
  9. create table department2(
  10. id int,
  11. name varchar(</span>20<span style="color: #000000">),
  12. comment varchar(</span>100<span style="color: #000000">),
  13. constraint uk_name unique(name)
  14. );
  15. mysql</span>> insert into department1 values(1,<span style="color: #800000">‘</span><span style="color: #800000">IT</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">技术</span><span style="color: #800000">‘</span><span style="color: #000000">);
  16. Query OK, </span>1 row affected (0.00<span style="color: #000000"> sec)
  17. mysql</span>> insert into department1 values(1,<span style="color: #800000">‘</span><span style="color: #800000">IT</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">技术</span><span style="color: #800000">‘</span><span style="color: #000000">);
  18. ERROR </span>1062 (23000): Duplicate entry <span style="color: #800000">‘</span><span style="color: #800000">IT</span><span style="color: #800000">‘</span> <span style="color: #0000ff">for</span> key <span style="color: #800000">‘</span><span style="color: #800000">name</span><span style="color: #800000">‘</span>
技术分享
  1. mysql> create table t1(id int <span style="color: #0000ff">not</span><span style="color: #000000"> null unique);
  2. Query OK, 0 rows affected (</span>0.02<span style="color: #000000"> sec)
  3. mysql</span>><span style="color: #000000"> desc t1;
  4. </span>+-------+---------+------+-----+---------+-------+
  5. | Field | Type | Null | Key | Default | Extra |
  6. +-------+---------+------+-----+---------+-------+
  7. | id | int(11) | NO | PRI | NULL | |
  8. +-------+---------+------+-----+---------+-------+<span style="color: #000000">
  9. row </span><span style="color: #0000ff">in</span> set (0.00 sec)
not null+unique的化学反应 技术分享
  1. <span style="color: #000000">create table service(
  2. id int primary key auto_increment,
  3. name varchar(</span>20<span style="color: #000000">),
  4. host varchar(</span>15) <span style="color: #0000ff">not</span><span style="color: #000000"> null,
  5. port int </span><span style="color: #0000ff">not</span><span style="color: #000000"> null,
  6. unique(host,port) </span><span style="color: #008000">#</span><span style="color: #008000">联合唯一</span>
  7. <span style="color: #000000">);
  8. mysql</span>><span style="color: #000000"> insert into service values
  9. </span>-> (1,<span style="color: #800000">‘</span><span style="color: #800000">nginx</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">192.168.0.10</span><span style="color: #800000">‘</span>,80<span style="color: #000000">),
  10. </span>-> (2,<span style="color: #800000">‘</span><span style="color: #800000">haproxy</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">192.168.0.20</span><span style="color: #800000">‘</span>,80<span style="color: #000000">),
  11. </span>-> (3,<span style="color: #800000">‘</span><span style="color: #800000">mysql</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">192.168.0.30</span><span style="color: #800000">‘</span>,3306<span style="color: #000000">)
  12. </span>-><span style="color: #000000"> ;
  13. Query OK, </span>3 rows affected (0.01<span style="color: #000000"> sec)
  14. Records: </span>3<span style="color: #000000"> Duplicates: 0 Warnings: 0
  15. mysql</span>> insert into service(name,host,port) values(<span style="color: #800000">‘</span><span style="color: #800000">nginx</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">192.168.0.10</span><span style="color: #800000">‘</span>,80<span style="color: #000000">);
  16. ERROR </span>1062 (23000): Duplicate entry <span style="color: #800000">‘</span><span style="color: #800000">192.168.0.10-80</span><span style="color: #800000">‘</span> <span style="color: #0000ff">for</span> key <span style="color: #800000">‘</span><span style="color: #800000">host</span><span style="color: #800000">‘</span>
联合唯一

四 primary key

primary key字段的值不为空且唯一

一个表中可以:

单列做主键
多列做主键(复合主键)

但一个表内只能有一个主键primary key

技术分享
  1. ============单列做主键===============
  2. <span style="color: #008000">#</span><span style="color: #008000">方法一:not null+unique</span>
  3. <span style="color: #000000">create table department1(
  4. id int </span><span style="color: #0000ff">not</span> null unique, <span style="color: #008000">#</span><span style="color: #008000">主键</span>
  5. name varchar(20) <span style="color: #0000ff">not</span><span style="color: #000000"> null unique,
  6. comment varchar(</span>100<span style="color: #000000">)
  7. );
  8. mysql</span>><span style="color: #000000"> desc department1;
  9. </span>+---------+--------------+------+-----+---------+-------+
  10. | Field | Type | Null | Key | Default | Extra |
  11. +---------+--------------+------+-----+---------+-------+
  12. | id | int(11) | NO | PRI | NULL | |
  13. | name | varchar(20) | NO | UNI | NULL | |
  14. | comment | varchar(100) | YES | | NULL | |
  15. +---------+--------------+------+-----+---------+-------+<span style="color: #000000">
  16. rows </span><span style="color: #0000ff">in</span> set (0.01<span style="color: #000000"> sec)
  17. </span><span style="color: #008000">#</span><span style="color: #008000">方法二:在某一个字段后用primary key</span>
  18. <span style="color: #000000">create table department2(
  19. id int primary key, </span><span style="color: #008000">#</span><span style="color: #008000">主键</span>
  20. name varchar(20<span style="color: #000000">),
  21. comment varchar(</span>100<span style="color: #000000">)
  22. );
  23. mysql</span>><span style="color: #000000"> desc department2;
  24. </span>+---------+--------------+------+-----+---------+-------+
  25. | Field | Type | Null | Key | Default | Extra |
  26. +---------+--------------+------+-----+---------+-------+
  27. | id | int(11) | NO | PRI | NULL | |
  28. | name | varchar(20) | YES | | NULL | |
  29. | comment | varchar(100) | YES | | NULL | |
  30. +---------+--------------+------+-----+---------+-------+<span style="color: #000000">
  31. rows </span><span style="color: #0000ff">in</span> set (0.00<span style="color: #000000"> sec)
  32. </span><span style="color: #008000">#</span><span style="color: #008000">方法三:在所有字段后单独定义primary key</span>
  33. <span style="color: #000000">create table department3(
  34. id int,
  35. name varchar(</span>20<span style="color: #000000">),
  36. comment varchar(</span>100<span style="color: #000000">),
  37. constraint pk_name primary key(id); </span><span style="color: #008000">#</span><span style="color: #008000">创建主键并为其命名pk_name</span>
  38. <span style="color: #000000">
  39. mysql</span>><span style="color: #000000"> desc department3;
  40. </span>+---------+--------------+------+-----+---------+-------+
  41. | Field | Type | Null | Key | Default | Extra |
  42. +---------+--------------+------+-----+---------+-------+
  43. | id | int(11) | NO | PRI | NULL | |
  44. | name | varchar(20) | YES | | NULL | |
  45. | comment | varchar(100) | YES | | NULL | |
  46. +---------+--------------+------+-----+---------+-------+<span style="color: #000000">
  47. rows </span><span style="color: #0000ff">in</span> set (0.01 sec)
单列主键 技术分享
  1. ==================多列做主键================<span style="color: #000000">
  2. create table service(
  3. ip varchar(</span>15<span style="color: #000000">),
  4. port char(</span>5<span style="color: #000000">),
  5. service_name varchar(</span>10) <span style="color: #0000ff">not</span><span style="color: #000000"> null,
  6. primary key(ip,port)
  7. );
  8. mysql</span>><span style="color: #000000"> desc service;
  9. </span>+--------------+-------------+------+-----+---------+-------+
  10. | Field | Type | Null | Key | Default | Extra |
  11. +--------------+-------------+------+-----+---------+-------+
  12. | ip | varchar(15) | NO | PRI | NULL | |
  13. | port | char(5) | NO | PRI | NULL | |
  14. | service_name | varchar(10) | NO | | NULL | |
  15. +--------------+-------------+------+-----+---------+-------+<span style="color: #000000">
  16. rows </span><span style="color: #0000ff">in</span> set (0.00<span style="color: #000000"> sec)
  17. mysql</span>><span style="color: #000000"> insert into service values
  18. </span>-> (<span style="color: #800000">‘</span><span style="color: #800000">172.16.45.10</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">3306</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">mysqld</span><span style="color: #800000">‘</span><span style="color: #000000">),
  19. </span>-> (<span style="color: #800000">‘</span><span style="color: #800000">172.16.45.11</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">3306</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">mariadb</span><span style="color: #800000">‘</span><span style="color: #000000">)
  20. </span>-><span style="color: #000000"> ;
  21. Query OK, </span>2 rows affected (0.00<span style="color: #000000"> sec)
  22. Records: </span>2<span style="color: #000000"> Duplicates: 0 Warnings: 0
  23. mysql</span>> insert into service values (<span style="color: #800000">‘</span><span style="color: #800000">172.16.45.10</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">3306</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">nginx</span><span style="color: #800000">‘</span><span style="color: #000000">);
  24. ERROR </span>1062 (23000): Duplicate entry <span style="color: #800000">‘</span><span style="color: #800000">172.16.45.10-3306</span><span style="color: #800000">‘</span> <span style="color: #0000ff">for</span> key <span style="color: #800000">‘</span><span style="color: #800000">PRIMARY</span><span style="color: #800000">‘</span>
多列主键

五 auto_increment

约束字段为自动增长,被约束的字段必须同时被key约束

技术分享
  1. <span style="color: #008000">#</span><span style="color: #008000">不指定id,则自动增长</span>
  2. <span style="color: #000000">create table student(
  3. id int primary key auto_increment,
  4. name varchar(</span>20<span style="color: #000000">),
  5. sex enum(</span><span style="color: #800000">‘</span><span style="color: #800000">male</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">female</span><span style="color: #800000">‘</span>) default <span style="color: #800000">‘</span><span style="color: #800000">male</span><span style="color: #800000">‘</span><span style="color: #000000">
  6. );
  7. mysql</span>><span style="color: #000000"> desc student;
  8. </span>+-------+-----------------------+------+-----+---------+----------------+
  9. | Field | Type | Null | Key | Default | Extra |
  10. +-------+-----------------------+------+-----+---------+----------------+
  11. | id | int(11) | NO | PRI | NULL | auto_increment |
  12. | name | varchar(20) | YES | | NULL | |
  13. | sex | enum(<span style="color: #800000">‘</span><span style="color: #800000">male</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">female</span><span style="color: #800000">‘</span>) | YES | | male | |
  14. +-------+-----------------------+------+-----+---------+----------------+<span style="color: #000000">
  15. mysql</span>><span style="color: #000000"> insert into student(name) values
  16. </span>-> (<span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span><span style="color: #000000">),
  17. </span>-> (<span style="color: #800000">‘</span><span style="color: #800000">alex</span><span style="color: #800000">‘</span><span style="color: #000000">)
  18. </span>-><span style="color: #000000"> ;
  19. mysql</span>> select * <span style="color: #0000ff">from</span><span style="color: #000000"> student;
  20. </span>+----+------+------+
  21. | id | name | sex |
  22. +----+------+------+
  23. | 1 | egon | male |
  24. | 2 | alex | male |
  25. +----+------+------+
  26. <span style="color: #008000">#</span><span style="color: #008000">也可以指定id</span>
  27. mysql> insert into student values(4,<span style="color: #800000">‘</span><span style="color: #800000">asb</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">female</span><span style="color: #800000">‘</span><span style="color: #000000">);
  28. Query OK, </span>1 row affected (0.00<span style="color: #000000"> sec)
  29. mysql</span>> insert into student values(7,<span style="color: #800000">‘</span><span style="color: #800000">wsb</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">female</span><span style="color: #800000">‘</span><span style="color: #000000">);
  30. Query OK, </span>1 row affected (0.00<span style="color: #000000"> sec)
  31. mysql</span>> select * <span style="color: #0000ff">from</span><span style="color: #000000"> student;
  32. </span>+----+------+--------+
  33. | id | name | sex |
  34. +----+------+--------+
  35. | 1 | egon | male |
  36. | 2 | alex | male |
  37. | 4 | asb | female |
  38. | 7 | wsb | female |
  39. +----+------+--------+
  40. <span style="color: #008000">#</span><span style="color: #008000">对于自增的字段,在用delete删除后,再插入值,该字段仍按照删除前的位置继续增长</span>
  41. mysql> delete <span style="color: #0000ff">from</span><span style="color: #000000"> student;
  42. Query OK, </span>4 rows affected (0.00<span style="color: #000000"> sec)
  43. mysql</span>> select * <span style="color: #0000ff">from</span><span style="color: #000000"> student;
  44. Empty set (</span>0.00<span style="color: #000000"> sec)
  45. mysql</span>> insert into student(name) values(<span style="color: #800000">‘</span><span style="color: #800000">ysb</span><span style="color: #800000">‘</span><span style="color: #000000">);
  46. mysql</span>> select * <span style="color: #0000ff">from</span><span style="color: #000000"> student;
  47. </span>+----+------+------+
  48. | id | name | sex |
  49. +----+------+------+
  50. | 8 | ysb | male |
  51. +----+------+------+
  52. <span style="color: #008000">#</span><span style="color: #008000">应该用truncate清空表,比起delete一条一条地删除记录,truncate是直接清空表,在删除大表时用它</span>
  53. mysql><span style="color: #000000"> truncate student;
  54. Query OK, 0 rows affected (</span>0.01<span style="color: #000000"> sec)
  55. mysql</span>> insert into student(name) values(<span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span><span style="color: #000000">);
  56. Query OK, </span>1 row affected (0.01<span style="color: #000000"> sec)
  57. mysql</span>> select * <span style="color: #0000ff">from</span><span style="color: #000000"> student;
  58. </span>+----+------+------+
  59. | id | name | sex |
  60. +----+------+------+
  61. | 1 | egon | male |
  62. +----+------+------+<span style="color: #000000">
  63. row </span><span style="color: #0000ff">in</span> set (0.00 sec)
View Code 技术分享
  1. <span style="color: #008000">#</span><span style="color: #008000">在创建完表后,修改自增字段的起始值</span>
  2. mysql><span style="color: #000000"> create table student(
  3. </span>-><span style="color: #000000"> id int primary key auto_increment,
  4. </span>-> name varchar(20<span style="color: #000000">),
  5. </span>-> sex enum(<span style="color: #800000">‘</span><span style="color: #800000">male</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">female</span><span style="color: #800000">‘</span>) default <span style="color: #800000">‘</span><span style="color: #800000">male</span><span style="color: #800000">‘</span>
  6. -><span style="color: #000000"> );
  7. mysql</span>> alter table student auto_increment=3<span style="color: #000000">;
  8. mysql</span>><span style="color: #000000"> show create table student;
  9. .......
  10. ENGINE</span>=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=<span style="color: #000000">utf8
  11. mysql</span>> insert into student(name) values(<span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span><span style="color: #000000">);
  12. Query OK, </span>1 row affected (0.01<span style="color: #000000"> sec)
  13. mysql</span>> select * <span style="color: #0000ff">from</span><span style="color: #000000"> student;
  14. </span>+----+------+------+
  15. | id | name | sex |
  16. +----+------+------+
  17. | 3 | egon | male |
  18. +----+------+------+<span style="color: #000000">
  19. row </span><span style="color: #0000ff">in</span> set (0.00<span style="color: #000000"> sec)
  20. mysql</span>><span style="color: #000000"> show create table student;
  21. .......
  22. ENGINE</span>=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=<span style="color: #000000">utf8
  23. </span><span style="color: #008000">#</span><span style="color: #008000">也可以创建表时指定auto_increment的初始值,注意初始值的设置为表选项,应该放到括号外</span>
  24. <span style="color: #000000">create table student(
  25. id int primary key auto_increment,
  26. name varchar(</span>20<span style="color: #000000">),
  27. sex enum(</span><span style="color: #800000">‘</span><span style="color: #800000">male</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">female</span><span style="color: #800000">‘</span>) default <span style="color: #800000">‘</span><span style="color: #800000">male</span><span style="color: #800000">‘</span><span style="color: #000000">
  28. )auto_increment</span>=3<span style="color: #000000">;
  29. </span><span style="color: #008000">#</span><span style="color: #008000">设置步长</span>
  30. <span style="color: #000000">sqlserver:自增步长
  31. 基于表级别
  32. create table t1(
  33. id int。。。
  34. )engine</span>=innodb,auto_increment=2 步长=2 default charset=<span style="color: #000000">utf8
  35. mysql自增的步长:
  36. show session variables like </span><span style="color: #800000">‘</span><span style="color: #800000">auto_inc%</span><span style="color: #800000">‘</span><span style="color: #000000">;
  37. </span><span style="color: #008000">#</span><span style="color: #008000">基于会话级别</span>
  38. set session auth_increment_increment=2 <span style="color: #008000">#</span><span style="color: #008000">修改会话级别的步长</span>
  39. <span style="color: #008000">#</span><span style="color: #008000">基于全局级别的</span>
  40. set <span style="color: #0000ff">global</span> auth_increment_increment=2 <span style="color: #008000">#</span><span style="color: #008000">修改全局级别的步长(所有会话都生效)</span>
  41. <span style="color: #008000">#</span><span style="color: #008000">!!!注意了注意了注意了!!!</span>
  42. If the value of auto_increment_offset <span style="color: #0000ff">is</span> greater than that of auto_increment_increment, the value of auto_increment_offset <span style="color: #0000ff">is</span><span style="color: #000000"> ignored.
  43. 翻译:如果auto_increment_offset的值大于auto_increment_increment的值,则auto_increment_offset的值会被忽略
  44. 比如:设置auto_increment_offset</span>=3,auto_increment_increment=2<span style="color: #000000">
  45. mysql</span>> set <span style="color: #0000ff">global</span> auto_increment_increment=5<span style="color: #000000">;
  46. Query OK, 0 rows affected (</span>0.00<span style="color: #000000"> sec)
  47. mysql</span>> set <span style="color: #0000ff">global</span> auto_increment_offset=3<span style="color: #000000">;
  48. Query OK, 0 rows affected (</span>0.00<span style="color: #000000"> sec)
  49. mysql</span>> show variables like <span style="color: #800000">‘</span><span style="color: #800000">auto_incre%</span><span style="color: #800000">‘</span>; <span style="color: #008000">#</span><span style="color: #008000">需要退出重新登录</span>
  50. +--------------------------+-------+
  51. | Variable_name | Value |
  52. +--------------------------+-------+
  53. | auto_increment_increment | 1 |
  54. | auto_increment_offset | 1 |
  55. +--------------------------+-------+<span style="color: #000000">
  56. create table student(
  57. id int primary key auto_increment,
  58. name varchar(</span>20<span style="color: #000000">),
  59. sex enum(</span><span style="color: #800000">‘</span><span style="color: #800000">male</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">female</span><span style="color: #800000">‘</span>) default <span style="color: #800000">‘</span><span style="color: #800000">male</span><span style="color: #800000">‘</span><span style="color: #000000">
  60. );
  61. mysql</span>> insert into student(name) values(<span style="color: #800000">‘</span><span style="color: #800000">egon1</span><span style="color: #800000">‘</span>),(<span style="color: #800000">‘</span><span style="color: #800000">egon2</span><span style="color: #800000">‘</span>),(<span style="color: #800000">‘</span><span style="color: #800000">egon3</span><span style="color: #800000">‘</span><span style="color: #000000">);
  62. mysql</span>> select * <span style="color: #0000ff">from</span><span style="color: #000000"> student;
  63. </span>+----+-------+------+
  64. | id | name | sex |
  65. +----+-------+------+
  66. | 3 | egon1 | male |
  67. | 8 | egon2 | male |
  68. | 13 | egon3 | male |
  69. +----+-------+------+
步长increment与起始偏移量offset:auto_increment_increment,auto_increment_offset

六 foreign key

员工信息表有三个字段:工号  姓名  部门

公司有3个部门,但是有1个亿的员工,那意味着部门这个字段需要重复存储,部门名字越长,越浪费

解决方法:

我们完全可以定义一个部门表

然后让员工信息表关联该表,如何关联,即foreign key

技术分享
  1. <span style="color: #008000">#</span><span style="color: #008000">表类型必须是innodb存储引擎,且被关联的字段,即references指定的另外一个表的字段,必须保证唯一</span>
  2. <span style="color: #000000">create table department(
  3. id int primary key,
  4. name varchar(</span>20) <span style="color: #0000ff">not</span><span style="color: #000000"> null
  5. )engine</span>=<span style="color: #000000">innodb;
  6. </span><span style="color: #008000">#</span><span style="color: #008000">dpt_id外键,关联父表(department主键id),同步更新,同步删除</span>
  7. <span style="color: #000000">create table employee(
  8. id int primary key,
  9. name varchar(</span>20) <span style="color: #0000ff">not</span><span style="color: #000000"> null,
  10. dpt_id int,
  11. constraint fk_name foreign key(dpt_id)
  12. references department(id)
  13. on delete cascade
  14. on update cascade
  15. )engine</span>=<span style="color: #000000">innodb;
  16. </span><span style="color: #008000">#</span><span style="color: #008000">先往父表department中插入记录</span>
  17. <span style="color: #000000">insert into department values
  18. (</span>1,<span style="color: #800000">‘</span><span style="color: #800000">欧德博爱技术有限事业部</span><span style="color: #800000">‘</span><span style="color: #000000">),
  19. (</span>2,<span style="color: #800000">‘</span><span style="color: #800000">艾利克斯人力资源部</span><span style="color: #800000">‘</span><span style="color: #000000">),
  20. (</span>3,<span style="color: #800000">‘</span><span style="color: #800000">销售部</span><span style="color: #800000">‘</span><span style="color: #000000">);
  21. </span><span style="color: #008000">#</span><span style="color: #008000">再往子表employee中插入记录</span>
  22. <span style="color: #000000">insert into employee values
  23. (</span>1,<span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span>,1<span style="color: #000000">),
  24. (</span>2,<span style="color: #800000">‘</span><span style="color: #800000">alex1</span><span style="color: #800000">‘</span>,2<span style="color: #000000">),
  25. (</span>3,<span style="color: #800000">‘</span><span style="color: #800000">alex2</span><span style="color: #800000">‘</span>,2<span style="color: #000000">),
  26. (</span>4,<span style="color: #800000">‘</span><span style="color: #800000">alex3</span><span style="color: #800000">‘</span>,2<span style="color: #000000">),
  27. (</span>5,<span style="color: #800000">‘</span><span style="color: #800000">李坦克</span><span style="color: #800000">‘</span>,3<span style="color: #000000">),
  28. (</span>6,<span style="color: #800000">‘</span><span style="color: #800000">刘飞机</span><span style="color: #800000">‘</span>,3<span style="color: #000000">),
  29. (</span>7,<span style="color: #800000">‘</span><span style="color: #800000">张火箭</span><span style="color: #800000">‘</span>,3<span style="color: #000000">),
  30. (</span>8,<span style="color: #800000">‘</span><span style="color: #800000">林子弹</span><span style="color: #800000">‘</span>,3<span style="color: #000000">),
  31. (</span>9,<span style="color: #800000">‘</span><span style="color: #800000">加特林</span><span style="color: #800000">‘</span>,3<span style="color: #000000">)
  32. ;
  33. </span><span style="color: #008000">#</span><span style="color: #008000">删父表department,子表employee中对应的记录跟着删</span>
  34. mysql> delete <span style="color: #0000ff">from</span> department where id=3<span style="color: #000000">;
  35. mysql</span>> select * <span style="color: #0000ff">from</span><span style="color: #000000"> employee;
  36. </span>+----+-------+--------+
  37. | id | name | dpt_id |
  38. +----+-------+--------+
  39. | 1 | egon | 1 |
  40. | 2 | alex1 | 2 |
  41. | 3 | alex2 | 2 |
  42. | 4 | alex3 | 2 |
  43. +----+-------+--------+
  44. <span style="color: #008000">#</span><span style="color: #008000">更新父表department,子表employee中对应的记录跟着改</span>
  45. mysql> update department set id=22222 where id=2<span style="color: #000000">;
  46. mysql</span>> select * <span style="color: #0000ff">from</span><span style="color: #000000"> employee;
  47. </span>+----+-------+--------+
  48. | id | name | dpt_id |
  49. +----+-------+--------+
  50. | 1 | egon | 1 |
  51. | 3 | alex2 | 22222 |
  52. | 4 | alex3 | 22222 |
  53. | 5 | alex1 | 22222 |
  54. +----+-------+--------+
示范 技术分享
  1. <span style="color: #000000">表1 foreign key 表2
  2. 则表1的多条记录对应表2的一条记录,即多对一
  3. 利用foreign key的原理我们可以制作两张表的多对多,一对一关系
  4. 多对多:
  5. 表1的多条记录可以对应表2的一条记录
  6. 表2的多条记录也可以对应表1的一条记录
  7. 一对一:
  8. 表1的一条记录唯一对应表2的一条记录,反之亦然
  9. 分析时,我们先从按照上面的基本原理去套,然后再翻译成真实的意义,就很好理解了</span>
辅助理解
  1. <span style="color: #008000">#</span><span style="color: #008000">一对多或称为多对一</span>
  2. <span style="color: #000000">三张表:出版社,作者信息,书
  3. 一对多(或多对一):一个出版社可以出版多本书
  4. 关联方式:foreign key</span>
技术分享
  1. =====================多对一=====================<span style="color: #000000">
  2. create table press(
  3. id int primary key auto_increment,
  4. name varchar(</span>20<span style="color: #000000">)
  5. );
  6. create table book(
  7. id int primary key auto_increment,
  8. name varchar(</span>20<span style="color: #000000">),
  9. press_id int </span><span style="color: #0000ff">not</span><span style="color: #000000"> null,
  10. foreign key(press_id) references press(id)
  11. on delete cascade
  12. on update cascade
  13. );
  14. insert into press(name) values
  15. (</span><span style="color: #800000">‘</span><span style="color: #800000">北京工业地雷出版社</span><span style="color: #800000">‘</span><span style="color: #000000">),
  16. (</span><span style="color: #800000">‘</span><span style="color: #800000">人民音乐不好听出版社</span><span style="color: #800000">‘</span><span style="color: #000000">),
  17. (</span><span style="color: #800000">‘</span><span style="color: #800000">知识产权没有用出版社</span><span style="color: #800000">‘</span><span style="color: #000000">)
  18. ;
  19. insert into book(name,press_id) values
  20. (</span><span style="color: #800000">‘</span><span style="color: #800000">九阳神功</span><span style="color: #800000">‘</span>,1<span style="color: #000000">),
  21. (</span><span style="color: #800000">‘</span><span style="color: #800000">九阴真经</span><span style="color: #800000">‘</span>,2<span style="color: #000000">),
  22. (</span><span style="color: #800000">‘</span><span style="color: #800000">九阴白骨爪</span><span style="color: #800000">‘</span>,2<span style="color: #000000">),
  23. (</span><span style="color: #800000">‘</span><span style="color: #800000">独孤九剑</span><span style="color: #800000">‘</span>,3<span style="color: #000000">),
  24. (</span><span style="color: #800000">‘</span><span style="color: #800000">降龙十巴掌</span><span style="color: #800000">‘</span>,2<span style="color: #000000">),
  25. (</span><span style="color: #800000">‘</span><span style="color: #800000">葵花宝典</span><span style="color: #800000">‘</span>,3);
View Code 技术分享
  1. 单张表:用户表+相亲关系表,相当于:用户表+相亲关系表+<span style="color: #000000">用户表
  2. 多张表:用户表</span>+用户与主机关系表+<span style="color: #000000">主机表
  3. 中间那一张存放关系的表,对外关联的字段可以联合唯一</span>
其它例子
  1. <span style="color: #008000">#</span><span style="color: #008000">一对一</span>
  2. <span style="color: #000000">两张表:学生表和客户表
  3. 一对一:一个学生是一个客户,一个客户有可能变成一个学校,即一对一的关系
  4. 关联方式:foreign key</span>+unique
技术分享
  1. <span style="color: #000000">例一:一个用户只有一个博客
  2. 用户表:
  3. id name
  4. egon
  5. alex
  6. wupeiqi
  7. 博客表
  8. fk</span>+<span style="color: #000000">unique
  9. id url name_id
  10. xxxx </span>1<span style="color: #000000">
  11. yyyy </span>3<span style="color: #000000">
  12. zzz </span>2<span style="color: #000000">
  13. 例二:一个管理员唯一对应一个用户
  14. 用户表:
  15. id user password
  16. egon xxxx
  17. alex yyyy
  18. 管理员表:
  19. fk</span>+<span style="color: #000000">unique
  20. id user_id password
  21. </span>1<span style="color: #000000"> xxxxx
  22. </span>2 yyyyy
其他例子

七 作业

练习:账号信息表,用户组,主机表,主机组

技术分享
  1. <span style="color: #008000">#</span><span style="color: #008000">用户表</span>
  2. <span style="color: #000000">create table user(
  3. id int </span><span style="color: #0000ff">not</span><span style="color: #000000"> null unique auto_increment,
  4. username varchar(</span>20) <span style="color: #0000ff">not</span><span style="color: #000000"> null,
  5. password varchar(</span>50) <span style="color: #0000ff">not</span><span style="color: #000000"> null,
  6. primary key(username,password)
  7. );
  8. insert into user(username,password) values
  9. (</span><span style="color: #800000">‘</span><span style="color: #800000">root</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">123</span><span style="color: #800000">‘</span><span style="color: #000000">),
  10. (</span><span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">456</span><span style="color: #800000">‘</span><span style="color: #000000">),
  11. (</span><span style="color: #800000">‘</span><span style="color: #800000">alex</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">alex3714</span><span style="color: #800000">‘</span><span style="color: #000000">)
  12. ;
  13. </span><span style="color: #008000">#</span><span style="color: #008000">用户组表</span>
  14. <span style="color: #000000">create table usergroup(
  15. id int primary key auto_increment,
  16. groupname varchar(</span>20) <span style="color: #0000ff">not</span><span style="color: #000000"> null unique
  17. );
  18. insert into usergroup(groupname) values
  19. (</span><span style="color: #800000">‘</span><span style="color: #800000">IT</span><span style="color: #800000">‘</span><span style="color: #000000">),
  20. (</span><span style="color: #800000">‘</span><span style="color: #800000">Sale</span><span style="color: #800000">‘</span><span style="color: #000000">),
  21. (</span><span style="color: #800000">‘</span><span style="color: #800000">Finance</span><span style="color: #800000">‘</span><span style="color: #000000">),
  22. (</span><span style="color: #800000">‘</span><span style="color: #800000">boss</span><span style="color: #800000">‘</span><span style="color: #000000">)
  23. ;
  24. </span><span style="color: #008000">#</span><span style="color: #008000">主机表</span>
  25. <span style="color: #000000">create table host(
  26. id int primary key auto_increment,
  27. ip char(</span>15) <span style="color: #0000ff">not</span> null unique default <span style="color: #800000">‘</span><span style="color: #800000">127.0.0.1</span><span style="color: #800000">‘</span><span style="color: #000000">
  28. );
  29. insert into host(ip) values
  30. (</span><span style="color: #800000">‘</span><span style="color: #800000">172.16.45.2</span><span style="color: #800000">‘</span><span style="color: #000000">),
  31. (</span><span style="color: #800000">‘</span><span style="color: #800000">172.16.31.10</span><span style="color: #800000">‘</span><span style="color: #000000">),
  32. (</span><span style="color: #800000">‘</span><span style="color: #800000">172.16.45.3</span><span style="color: #800000">‘</span><span style="color: #000000">),
  33. (</span><span style="color: #800000">‘</span><span style="color: #800000">172.16.31.11</span><span style="color: #800000">‘</span><span style="color: #000000">),
  34. (</span><span style="color: #800000">‘</span><span style="color: #800000">172.10.45.3</span><span style="color: #800000">‘</span><span style="color: #000000">),
  35. (</span><span style="color: #800000">‘</span><span style="color: #800000">172.10.45.4</span><span style="color: #800000">‘</span><span style="color: #000000">),
  36. (</span><span style="color: #800000">‘</span><span style="color: #800000">172.10.45.5</span><span style="color: #800000">‘</span><span style="color: #000000">),
  37. (</span><span style="color: #800000">‘</span><span style="color: #800000">192.168.1.20</span><span style="color: #800000">‘</span><span style="color: #000000">),
  38. (</span><span style="color: #800000">‘</span><span style="color: #800000">192.168.1.21</span><span style="color: #800000">‘</span><span style="color: #000000">),
  39. (</span><span style="color: #800000">‘</span><span style="color: #800000">192.168.1.22</span><span style="color: #800000">‘</span><span style="color: #000000">),
  40. (</span><span style="color: #800000">‘</span><span style="color: #800000">192.168.2.23</span><span style="color: #800000">‘</span><span style="color: #000000">),
  41. (</span><span style="color: #800000">‘</span><span style="color: #800000">192.168.2.223</span><span style="color: #800000">‘</span><span style="color: #000000">),
  42. (</span><span style="color: #800000">‘</span><span style="color: #800000">192.168.2.24</span><span style="color: #800000">‘</span><span style="color: #000000">),
  43. (</span><span style="color: #800000">‘</span><span style="color: #800000">192.168.3.22</span><span style="color: #800000">‘</span><span style="color: #000000">),
  44. (</span><span style="color: #800000">‘</span><span style="color: #800000">192.168.3.23</span><span style="color: #800000">‘</span><span style="color: #000000">),
  45. (</span><span style="color: #800000">‘</span><span style="color: #800000">192.168.3.24</span><span style="color: #800000">‘</span><span style="color: #000000">)
  46. ;
  47. </span><span style="color: #008000">#</span><span style="color: #008000">业务线表</span>
  48. <span style="color: #000000">create table business(
  49. id int primary key auto_increment,
  50. business varchar(</span>20) <span style="color: #0000ff">not</span><span style="color: #000000"> null unique
  51. );
  52. insert into business(business) values
  53. (</span><span style="color: #800000">‘</span><span style="color: #800000">轻松贷</span><span style="color: #800000">‘</span><span style="color: #000000">),
  54. (</span><span style="color: #800000">‘</span><span style="color: #800000">随便花</span><span style="color: #800000">‘</span><span style="color: #000000">),
  55. (</span><span style="color: #800000">‘</span><span style="color: #800000">大富翁</span><span style="color: #800000">‘</span><span style="color: #000000">),
  56. (</span><span style="color: #800000">‘</span><span style="color: #800000">穷一生</span><span style="color: #800000">‘</span><span style="color: #000000">)
  57. ;
  58. </span><span style="color: #008000">#</span><span style="color: #008000">建关系:user与usergroup</span>
  59. <span style="color: #000000">
  60. create table user2usergroup(
  61. id int </span><span style="color: #0000ff">not</span><span style="color: #000000"> null unique auto_increment,
  62. user_id int </span><span style="color: #0000ff">not</span><span style="color: #000000"> null,
  63. group_id int </span><span style="color: #0000ff">not</span><span style="color: #000000"> null,
  64. primary key(user_id,group_id),
  65. foreign key(user_id) references user(id),
  66. foreign key(group_id) references usergroup(id)
  67. );
  68. insert into user2usergroup(user_id,group_id) values
  69. (</span>1,1<span style="color: #000000">),
  70. (</span>1,2<span style="color: #000000">),
  71. (</span>1,3<span style="color: #000000">),
  72. (</span>1,4<span style="color: #000000">),
  73. (</span>2,3<span style="color: #000000">),
  74. (</span>2,4<span style="color: #000000">),
  75. (</span>3,4<span style="color: #000000">)
  76. ;
  77. </span><span style="color: #008000">#</span><span style="color: #008000">建关系:host与business</span>
  78. <span style="color: #000000">
  79. create table host2business(
  80. id int </span><span style="color: #0000ff">not</span><span style="color: #000000"> null unique auto_increment,
  81. host_id int </span><span style="color: #0000ff">not</span><span style="color: #000000"> null,
  82. business_id int </span><span style="color: #0000ff">not</span><span style="color: #000000"> null,
  83. primary key(host_id,business_id),
  84. foreign key(host_id) references host(id),
  85. foreign key(business_id) references business(id)
  86. );
  87. insert into host2business(host_id,business_id) values
  88. (</span>1,1<span style="color: #000000">),
  89. (</span>1,2<span style="color: #000000">),
  90. (</span>1,3<span style="color: #000000">),
  91. (</span>2,2<span style="color: #000000">),
  92. (</span>2,3<span style="color: #000000">),
  93. (</span>3,4<span style="color: #000000">)
  94. ;
  95. </span><span style="color: #008000">#</span><span style="color: #008000">建关系:user与host</span>
  96. <span style="color: #000000">
  97. create table user2host(
  98. id int </span><span style="color: #0000ff">not</span><span style="color: #000000"> null unique auto_increment,
  99. user_id int </span><span style="color: #0000ff">not</span><span style="color: #000000"> null,
  100. host_id int </span><span style="color: #0000ff">not</span><span style="color: #000000"> null,
  101. primary key(user_id,host_id),
  102. foreign key(user_id) references user(id),
  103. foreign key(host_id) references host(id)
  104. );
  105. insert into user2host(user_id,host_id) values
  106. (</span>1,1<span style="color: #000000">),
  107. (</span>1,2<span style="color: #000000">),
  108. (</span>1,3<span style="color: #000000">),
  109. (</span>1,4<span style="color: #000000">),
  110. (</span>1,5<span style="color: #000000">),
  111. (</span>1,6<span style="color: #000000">),
  112. (</span>1,7<span style="color: #000000">),
  113. (</span>1,8<span style="color: #000000">),
  114. (</span>1,9<span style="color: #000000">),
  115. (</span>1,10<span style="color: #000000">),
  116. (</span>1,11<span style="color: #000000">),
  117. (</span>1,12<span style="color: #000000">),
  118. (</span>1,13<span style="color: #000000">),
  119. (</span>1,14<span style="color: #000000">),
  120. (</span>1,15<span style="color: #000000">),
  121. (</span>1,16<span style="color: #000000">),
  122. (</span>2,2<span style="color: #000000">),
  123. (</span>2,3<span style="color: #000000">),
  124. (</span>2,4<span style="color: #000000">),
  125. (</span>2,5<span style="color: #000000">),
  126. (</span>3,10<span style="color: #000000">),
  127. (</span>3,11<span style="color: #000000">),
  128. (</span>3,12);
View Code

作业:

技术分享

 

MySQL四-2:完整性约束

标签:资源   charset   没有   not   des   ==   解决方法   mariadb   表示   

人气教程排行