当前位置：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‘ doesn‘t 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.     
  38.     </span><span style="color: #008000">#</span><span style="color: #008000">基于会话级别</span>
  39.     set session auth_increment_increment=2 <span style="color: #008000">#</span><span style="color: #008000">修改会话级别的步长</span>
  40.     <span style="color: #008000">#</span><span style="color: #008000">基于全局级别的</span>
  41.     set <span style="color: #0000ff">global</span> auth_increment_increment=2 <span style="color: #008000">#</span><span style="color: #008000">修改全局级别的步长（所有会话都生效）</span>
  42. <span style="color: #008000">#</span><span style="color: #008000">！！！注意了注意了注意了！！！</span>
  43. 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. 
  44. 翻译：如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset的值会被忽略 
  45. 比如：设置auto_increment_offset</span>=3，auto_increment_increment=2<span style="color: #000000">
  46. mysql</span>> set <span style="color: #0000ff">global</span> auto_increment_increment=5<span style="color: #000000">;
  47. Query OK, 0 rows affected (</span>0.00<span style="color: #000000"> sec)
  48. mysql</span>> set <span style="color: #0000ff">global</span> auto_increment_offset=3<span style="color: #000000">;
  49. Query OK, 0 rows affected (</span>0.00<span style="color: #000000"> sec)
  50. 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>
  51. +--------------------------+-------+
  52. | Variable_name            | Value |
  53. +--------------------------+-------+
  54. | auto_increment_increment | 1     |
  55. | auto_increment_offset    | 1     |
  56. +--------------------------+-------+<span style="color: #000000">
  57. create table student(
  58. id int primary key auto_increment,
  59. name varchar(</span>20<span style="color: #000000">),
  60. 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">
  61. );
  62. 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">);
  63. mysql</span>> select * <span style="color: #0000ff">from</span><span style="color: #000000"> student;
  64. </span>+----+-------+------+
  65. | id | name  | sex  |
  66. +----+-------+------+
  67. |  3 | egon1 | male |
  68. |  8 | egon2 | male |
  69. | 13 | egon3 | male |
  70. +----+-------+------+
步长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   表示   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！