当前位置：Gxlcms > 数据库问题 > Mysql-完整性约束

Mysql-完整性约束

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

  • 二 not null与default
  • 三 unique
  • 四 primary key
  • 五 auto_increment
  • 六 foreign key
  • 七 作业

    • 一 介绍

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

    作用：用于保证数据的完整性和一致性
    主要分为：

    1. <span style="color: #000000">PRIMARY KEY (PK)    标识该字段为该表的主键，可以唯一的标识记录
    2. FOREIGN KEY (FK)    标识该字段为该表的外键
    3. NOT NULL    标识该字段不能为空
    4. UNIQUE KEY (UK)    标识该字段的值是唯一的
    5. AUTO_INCREMENT    标识该字段的值自动增长（整数类型，而且为主键）
    6. DEFAULT    为该字段设置默认值
    7. UNSIGNED 无符号
    8. ZEROFILL 使用0填充</span>

     

    说明：

    1. 1<span style="color: #000000">. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
    2. </span>2<span style="color: #000000">. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
    3. 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>) <span style="color: #0000ff">not</span> null default <span style="color: #800000">‘</span><span style="color: #800000">male</span><span style="color: #800000">‘</span><span style="color: #000000">
    4. age int unsigned NOT NULL default </span>20<span style="color: #000000"> 必须为正值（无符号） 不允许为空 默认是20
    5. </span>3<span style="color: #000000">. 是否是key
    6. 主键 primary key
    7. 外键 foreign key
    8. 索引 (index,unique...)</span>

    二 not null与default

    是否可空，null表示空，非字符串
    not null - 不可空
    null - 可空


    默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
    create table tb1(
    nid int not null defalut 2,
    num int not null
    )

    技术分享 
    1. ==================<span style="color: #0000ff">not</span> null====================<span style="color: #000000">
    2. mysql</span>> create table t1(id int); <span style="color: #008000">#</span><span style="color: #008000">id字段默认可以插入空</span>
    3. mysql><span style="color: #000000"> desc t1;
    4. </span>+-------+---------+------+-----+---------+-------+
    5. | Field | Type    | Null | Key | Default | Extra |
    6. +-------+---------+------+-----+---------+-------+
    7. | id    | int(11) | YES  |     | NULL    |       |
    8. +-------+---------+------+-----+---------+-------+<span style="color: #000000">
    9. mysql</span>> insert into t1 values(); <span style="color: #008000">#</span><span style="color: #008000">可以插入空</span>
    10. <span style="color: #000000">
    11. mysql</span>> create table t2(id int <span style="color: #0000ff">not</span> null); <span style="color: #008000">#</span><span style="color: #008000">设置字段id不为空</span>
    12. mysql><span style="color: #000000"> desc t2;
    13. </span>+-------+---------+------+-----+---------+-------+
    14. | Field | Type    | Null | Key | Default | Extra |
    15. +-------+---------+------+-----+---------+-------+
    16. | id    | int(11) | NO   |     | NULL    |       |
    17. +-------+---------+------+-----+---------+-------+<span style="color: #000000">
    18. mysql</span>> insert into t2 values(); <span style="color: #008000">#</span><span style="color: #008000">不能插入空</span>
    19. ERROR 1364 (HY000): Field <span style="color: #800000">‘</span><span style="color: #800000">id</span><span style="color: #800000">‘</span> doesn<span style="color: #800000">‘</span><span style="color: #800000">t have a default value</span>
    20. ==================default====================
    21. <span style="color: #008000">#</span><span style="color: #008000">设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值</span>
    22. mysql> create table t3(id int default 1<span style="color: #000000">);
    23. mysql</span>> alter table t3 modify id int <span style="color: #0000ff">not</span> null default 1<span style="color: #000000">;
    24. </span>==================综合练习====================<span style="color: #000000">
    25. mysql</span>><span style="color: #000000"> create table student(
    26.     </span>-> name varchar(20) <span style="color: #0000ff">not</span><span style="color: #000000"> null,
    27.     </span>-> age int(3) unsigned <span style="color: #0000ff">not</span> null default 18<span style="color: #000000">,
    28.     </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><span style="color: #000000">,
    29.     </span>-> hobby set(<span style="color: #800000">‘</span><span style="color: #800000">play</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">study</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">read</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">music</span><span style="color: #800000">‘</span>) default <span style="color: #800000">‘</span><span style="color: #800000">play,music</span><span style="color: #800000">‘</span>
    30.     -><span style="color: #000000"> );
    31. mysql</span>><span style="color: #000000"> desc student;
    32. </span>+-------+------------------------------------+------+-----+------------+-------+
    33. | Field | Type                               | Null | Key | Default    | Extra |
    34. +-------+------------------------------------+------+-----+------------+-------+
    35. | name  | varchar(20)                        | NO   |     | NULL       |       |
    36. | age   | int(3) unsigned                    | NO   |     | 18         |       |
    37. | 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       |       |
    38. | hobby | set(<span style="color: #800000">‘</span><span style="color: #800000">play</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">study</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">read</span><span style="color: #800000">‘</span>,<span style="color: #800000">‘</span><span style="color: #800000">music</span><span style="color: #800000">‘</span>) | YES  |     | play,music |       |
    39. +-------+------------------------------------+------+-----+------------+-------+<span style="color: #000000">
    40. 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">);
    41. mysql</span>> select * <span style="color: #0000ff">from</span><span style="color: #000000"> student;
    42. </span>+------+-----+------+------------+
    43. | name | age | sex  | hobby      |
    44. +------+-----+------+------------+
    45. | egon |  18 | male | play,music |
    46. +------+-----+------+------------+
    验证

    三 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>
    View Code 技术分享 
    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. +-------+---------+------+-----+---------+-------+
    9. 1 row <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. +--------------+-------------+------+-----+---------+-------+
    16. 3 rows <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. +----+------+------+
    63. 1 row <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

    一 快速理解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">分析步骤：
    2. </span><span style="color: #008000">#</span><span style="color: #008000">1、先站在左表的角度去找</span>
    3. <span style="color: #000000">是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id）
    4. </span><span style="color: #008000">#</span><span style="color: #008000">2、再站在右表的角度去找</span>
    5. <span style="color: #000000">是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id）
    6. </span><span style="color: #008000">#</span><span style="color: #008000">3、总结：</span><span style="color: #008000">
    7. #</span><span style="color: #008000">多对一：</span>
    8. <span style="color: #000000">如果只有步骤1成立，则是左表多对一右表
    9. 如果只有步骤2成立，则是右表多对一左表
    10. </span><span style="color: #008000">#</span><span style="color: #008000">多对多</span>
    11. <span style="color: #000000">如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系
    12. </span><span style="color: #008000">#</span><span style="color: #008000">一对一:</span>
    13. 如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可

    三 建立表之间的关系

    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<span style="color: #000000">)
    26. ;</span>
    View Code 技术分享 
    1. <span style="color: #000000">一夫多妻制
    2. </span><span style="color: #008000">#</span><span style="color: #008000">妻子表的丈夫id外键到丈夫表的id</span>
    其他例子

      

    1. <span style="color: #008000">#</span><span style="color: #008000">多对多</span>
    2. <span style="color: #000000">三张表：出版社，作者信息，书
    3. 多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多
    4. 　　
    5. 关联方式：foreign key</span>+一张新的表
    技术分享 
    1. =====================多对多=====================<span style="color: #000000">
    2. create table author(
    3. id int primary key auto_increment,
    4. name varchar(</span>20<span style="color: #000000">)
    5. );
    6. </span><span style="color: #008000">#</span><span style="color: #008000">这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了</span>
    7. <span style="color: #000000">create table author2book(
    8. id int </span><span style="color: #0000ff">not</span><span style="color: #000000"> null unique auto_increment,
    9. author_id int </span><span style="color: #0000ff">not</span><span style="color: #000000"> null,
    10. book_id int </span><span style="color: #0000ff">not</span><span style="color: #000000"> null,
    11. constraint fk_author foreign key(author_id) references author(id)
    12. on delete cascade
    13. on update cascade,
    14. constraint fk_book foreign key(book_id) references book(id)
    15. on delete cascade
    16. on update cascade,
    17. primary key(author_id,book_id)
    18. );
    19. </span><span style="color: #008000">#</span><span style="color: #008000">插入四个作者，id依次排开</span>
    20. insert into author(name) values(<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">alex</span><span style="color: #800000">‘</span>),(<span style="color: #800000">‘</span><span style="color: #800000">yuanhao</span><span style="color: #800000">‘</span>),(<span style="color: #800000">‘</span><span style="color: #800000">wpq</span><span style="color: #800000">‘</span><span style="color: #000000">);
    21. </span><span style="color: #008000">#</span><span style="color: #008000">每个作者与自己的代表作如下</span>
    22. 1<span style="color: #000000"> egon: 
    23.       </span>1<span style="color: #000000"> 九阳神功
    24.       </span>2<span style="color: #000000"> 九阴真经
    25.       </span>3<span style="color: #000000"> 九阴白骨爪
    26.       </span>4<span style="color: #000000"> 独孤九剑
    27.       </span>5<span style="color: #000000"> 降龙十巴掌
    28.       </span>6<span style="color: #000000"> 葵花宝典
    29. </span>2<span style="color: #000000"> alex: 
    30.       </span>1<span style="color: #000000"> 九阳神功
    31.       </span>6<span style="color: #000000"> 葵花宝典
    32. </span>3<span style="color: #000000"> yuanhao:
    33.       </span>4<span style="color: #000000"> 独孤九剑
    34.       </span>5<span style="color: #000000"> 降龙十巴掌
    35.       </span>6<span style="color: #000000"> 葵花宝典
    36. </span>4<span style="color: #000000"> wpq:
    37.       </span>1<span style="color: #000000"> 九阳神功
    38. insert into author2book(author_id,book_id) values
    39. (</span>1,1<span style="color: #000000">),
    40. (</span>1,2<span style="color: #000000">),
    41. (</span>1,3<span style="color: #000000">),
    42. (</span>1,4<span style="color: #000000">),
    43. (</span>1,5<span style="color: #000000">),
    44. (</span>1,6<span style="color: #000000">),
    45. (</span>2,1<span style="color: #000000">),
    46. (</span>2,6<span style="color: #000000">),
    47. (</span>3,4<span style="color: #000000">),
    48. (</span>3,5<span style="color: #000000">),
    49. (</span>3,6<span style="color: #000000">),
    50. (</span>4,1<span style="color: #000000">)
    51. ;</span>
    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: #008000">#</span><span style="color: #008000">一定是student来foreign key表customer，这样就保证了：</span><span style="color: #008000">
    2. #</span><span style="color: #008000">1 学生一定是一个客户，</span><span style="color: #008000">
    3. #</span><span style="color: #008000">2 客户不一定是学生，但有可能成为一个学生</span>
    4. <span style="color: #000000">
    5. create table customer(
    6. id int primary key auto_increment,
    7. name varchar(</span>20) <span style="color: #0000ff">not</span><span style="color: #000000"> null
    8. );
    9. create table student(
    10. id int primary key auto_increment,
    11. name varchar(</span>20) <span style="color: #0000ff">not</span><span style="color: #000000"> null,
    12. class_name varchar(</span>20) <span style="color: #0000ff">not</span> null default <span style="color: #800000">‘</span><span style="color: #800000">python自动化</span><span style="color: #800000">‘</span><span style="color: #000000">,
    13. level int default </span>1<span style="color: #000000">,
    14. customer_id int unique, </span><span style="color: #008000">#</span><span style="color: #008000">该字段一定要是唯一的</span>
    15. foreign key(customer_id) references customer(id) <span style="color: #008000">#</span><span style="color: #008000">外键的字段一定要保证unique</span>
    16. <span style="color: #000000">on delete cascade
    17. on update cascade
    18. );
    19. </span><span style="color: #008000">#</span><span style="color: #008000">增加客户</span>
    20. <span style="color: #000000">insert into customer(name) values
    21. (</span><span style="color: #800000">‘</span><span style="color: #800000">李飞机</span><span style="color: #800000">‘</span><span style="color: #000000">),
    22. (</span><span style="color: #800000">‘</span><span style="color: #800000">王大炮</span><span style="color: #800000">‘</span><span style="color: #000000">),
    23. (</span><span style="color: #800000">‘</span><span style="color: #800000">守榴弹</span><span style="color: #800000">‘</span><span style="color: #000000">),
    24. (</span><span style="color: #800000">‘</span><span style="color: #800000">吴坦克</span><span style="color: #800000">‘</span><span style="color: #000000">),
    25. (</span><span style="color: #800000">‘</span><span style="color: #800000">赢火箭</span><span style="color: #800000">‘</span><span style="color: #000000">),
    26. (</span><span style="color: #800000">‘</span><span style="color: #800000">战地雷</span><span style="color: #800000">‘</span><span style="color: #000000">)
    27. ;
    28. </span><span style="color: #008000">#</span><span style="color: #008000">增加学生</span>
    29. <span style="color: #000000">insert into student(name,customer_id) values
    30. (</span><span style="color: #800000">‘</span><span style="color: #800000">李飞机</span><span style="color: #800000">‘</span>,1<span style="color: #000000">),
    31. (</span><span style="color: #800000">‘</span><span style="color: #800000">王大炮</span><span style="color: #800000">‘</span>,2<span style="color: #000000">)
    32. ;</span>
    View Code 技术分享 
    1. <span style="color: #000000">例一：一个用户只有一个博客
    2.     用户表：
    3.     id  name
    4.     </span>1<span style="color: #000000">    egon
    5.     </span>2<span style="color: #000000">    alex
    6.     </span>3<span style="color: #000000">    wupeiqi
    7.     博客表   
    8.            fk</span>+<span style="color: #000000">unique
    9.     id url name_id
    10.     </span>1  xxxx   1
    11.     2  yyyy   3
    12.     3  zzz    2<span style="color: #000000">
    13. 例二：一个管理员唯一对应一个用户
    14.     用户表：
    15.     id user  password
    16.     </span>1<span style="color: #000000">  egon    xxxx
    17.     </span>2<span style="color: #000000">  alex    yyyy
    18.     管理员表：
    19.        fk</span>+<span style="color: #000000">unique
    20.     id user_id password
    21.     </span>1   1<span style="color: #000000">      xxxxx
    22.     </span>2   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<span style="color: #000000">)
    129. ;</span>
    View Code

     

    作业：

    技术分享

     

    Mysql-完整性约束

    标签：练习   mysq   ref   insert   插入   log   作者   主机   资源   

    人气教程排行

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