当前位置：Gxlcms > 数据库问题 > Python学习_day43_mysql表操作2

Python学习_day43_mysql表操作2

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

　　约束条件与数据类型宽度相似，都是可选参数，主要用于保证数据的完整性和一致性。

1、not null与default

　　null 为可为空，表默认即为null，可以插入空内容：

技术分享

　　not null为非空，不可以插入空内容，输入空会报错：

技术分享

　　设置default默认值后，无论是是null还是not null，都可以输入空，输空以后取默认设置的那个值：

设置为空，输入空的情况：

技术分享

设置不为空，输入为空的情况：

技术分享

2、unique

　　设置唯一性约束，如下例，若第二次此字段输入的数据与上一次相同则会报错。当然下例也可以通过命令：create table t22(id int,name char(10),sex char(10),constraint uk_name unique(name));实现。

技术分享

　　设置联合唯一情况如下：

技术分享

3、primary key

　　primary key 字段不为空且唯一，一个表中只能有一个主键primary key，not null +unique也有同样的功效，但是primary key 只能有一个，not null unique可以有多个。

  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>

 4、auto_increment

　　用于约束的字段可以自动增长，但是被约束的字段必须也被key约束，否则报错。

技术分享

　　设置为自增字段后，该字段不输入数据时会按照默认进行自增。

技术分享

　　对于自增的字段用delete删除后，再插入数值后，该字段仍旧按照删除前的位置进行增长：

技术分享

　　为解决这个问题必须使用truncate进行清空表操作，此命令会将表全部内容清空：

技术分享

　　自增字段起始值和自增步长默认均为1，在创建表的时候可以在括号外指定auto_increment的起始值：

技术分享

　　此外可以通过如下指令设置自增字段的的起始值（auto_increment_offset）和自增步长(auto_increment_increment)，需要注意的是设置的起始值一定要小于等于自增步长，否则设置不成功。

  1. <span style="color: #008000">#</span><span style="color: #008000">基于会话级的设置：此会话内创建的表有效，关闭会话后设置无效</span>
  2. set session auto_increment_increment=3;<span style="color: #000000">
  3. set session auto_increment_offset</span>=2;
  4. <span style="color: #008000">#</span><span style="color: #008000">基于全局级的设置：所有会话内都有效，但是设置完后需要重新登录</span>
  5.  set <span style="color: #0000ff">global</span> auto_increment_increment=3;<span style="color: #000000">
  6.  set </span><span style="color: #0000ff">global</span> auto_increment_offset=2;

　　以全局级设置为例如下：

技术分享技术分享

技术分享

5、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. create table employee(
  7. id int primary key,
  8. name varchar(</span>20) <span style="color: #0000ff">not</span><span style="color: #000000"> null,
  9. dpt_id int,
  10. foreign key(dpt_id) references department(id)  </span><span style="color: #008000">#</span><span style="color: #008000">关联语句</span>
  11. on delete cascade                              <span style="color: #008000">#</span><span style="color: #008000">保证同步删除</span>
  12. on update cascade                              <span style="color: #008000">#</span><span style="color: #008000">保证同步更新</span>
  13. )engine=<span style="color: #000000">innodb;
  14. </span><span style="color: #008000">#</span><span style="color: #008000">先往父表department中插入记录</span>
  15. <span style="color: #000000">insert into department values
  16. (</span>1,<span style="color: #800000">‘</span><span style="color: #800000">欧德博爱技术有限事业部</span><span style="color: #800000">‘</span><span style="color: #000000">),
  17. (</span>2,<span style="color: #800000">‘</span><span style="color: #800000">艾利克斯人力资源部</span><span style="color: #800000">‘</span><span style="color: #000000">),
  18. (</span>3,<span style="color: #800000">‘</span><span style="color: #800000">销售部</span><span style="color: #800000">‘</span><span style="color: #000000">);
  19. </span><span style="color: #008000">#</span><span style="color: #008000">再往子表employee中插入记录</span>
  20. <span style="color: #000000">insert into employee values
  21. (</span>1,<span style="color: #800000">‘</span><span style="color: #800000">egon</span><span style="color: #800000">‘</span>,1<span style="color: #000000">),
  22. (</span>2,<span style="color: #800000">‘</span><span style="color: #800000">alex1</span><span style="color: #800000">‘</span>,2<span style="color: #000000">),
  23. (</span>3,<span style="color: #800000">‘</span><span style="color: #800000">alex2</span><span style="color: #800000">‘</span>,2<span style="color: #000000">),
  24. (</span>4,<span style="color: #800000">‘</span><span style="color: #800000">alex3</span><span style="color: #800000">‘</span>,2<span style="color: #000000">),
  25. (</span>5,<span style="color: #800000">‘</span><span style="color: #800000">李坦克</span><span style="color: #800000">‘</span>,3<span style="color: #000000">),
  26. (</span>6,<span style="color: #800000">‘</span><span style="color: #800000">刘飞机</span><span style="color: #800000">‘</span>,3<span style="color: #000000">),
  27. (</span>7,<span style="color: #800000">‘</span><span style="color: #800000">张火箭</span><span style="color: #800000">‘</span>,3<span style="color: #000000">),
  28. (</span>8,<span style="color: #800000">‘</span><span style="color: #800000">林子弹</span><span style="color: #800000">‘</span>,3<span style="color: #000000">),
  29. (</span>9,<span style="color: #800000">‘</span><span style="color: #800000">加特林</span><span style="color: #800000">‘</span>,3<span style="color: #000000">)
  30. ;
  31. </span><span style="color: #008000">#</span><span style="color: #008000">删父表department，子表employee中对应的记录跟着删</span>
  32. mysql> delete <span style="color: #0000ff">from</span> department where id=3<span style="color: #000000">;
  33. mysql</span>> select * <span style="color: #0000ff">from</span><span style="color: #000000"> employee;
  34. </span>+----+-------+--------+
  35. | id | name  | dpt_id |
  36. +----+-------+--------+
  37. |  1 | egon  |      1 |
  38. |  2 | alex1 |      2 |
  39. |  3 | alex2 |      2 |
  40. |  4 | alex3 |      2 |
  41. +----+-------+--------+
  42. <span style="color: #008000">#</span><span style="color: #008000">更新父表department，子表employee中对应的记录跟着改</span>
  43. mysql> update department set id=22222 where id=2<span style="color: #000000">;
  44. mysql</span>> select * <span style="color: #0000ff">from</span><span style="color: #000000"> employee;
  45. </span>+----+-------+--------+
  46. | id | name  | dpt_id |
  47. +----+-------+--------+
  48. |  1 | egon  |      1 |
  49. |  3 | alex2 |  22222 |
  50. |  4 | alex3 |  22222 |
  51. |  5 | alex1 |  22222 |
  52. +----+-------+--------+

　　表与表之间的三种关系：

  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）一对多

　　如一个部门对应多个员工，员工信息一个表，部门信息一个表，则员工信息表中的部门信息就可以通过foreign key进行关联。

　　首先建立部门表：

技术分享

　　创建员工信息表，其中部门信息通过id关联：

技术分享

 

（2）多对多

（3）一对一

 

Python学习_day43_mysql表操作2

标签：prim   delete   idt   完整性   pre   表之间   varchar   rds   公司   

人气教程排行

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