当前位置：Gxlcms > 数据库问题 > mysql3

mysql3

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

1.1 默认值 CREATE TABLE student( id INT, NAME VARCHAR(20), address VARCHAR(20) DEFAULT ‘广州天河‘ -- 默认值 ) DROP TABLE student; -- 当字段没有插入值的时候，mysql自动给该字段分配默认值 INSERT INTO student(id,NAME) VALUES(1,‘张三‘); -- 注意：默认值的字段允许为null INSERT INTO student(id,NAME,address) VALUE(2,‘李四‘,NULL); INSERT INTO student(id,NAME,address) VALUE(3,‘王五‘,‘广州番禺‘);

非空

  1. <span style="color: #008080">--</span><span style="color: #008080"> 1.2 非空</span><span style="color: #008080">
  2. --</span><span style="color: #008080"> 需求： gender字段必须有值（不为null）</span>
  3. <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> student(
  4.     id </span><span style="color: #0000ff">INT</span><span style="color: #000000">,
  5.     NAME </span><span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">20</span><span style="color: #000000">),
  6.     gender </span><span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">2</span>) <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span> <span style="color: #008080">--</span><span style="color: #008080"> 非空</span>
  7. <span style="color: #000000">)
  8. </span><span style="color: #008080">--</span><span style="color: #008080"> 非空字段必须赋值</span>
  9. <span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> student(id,NAME) <span style="color: #0000ff">VALUES</span>(<span style="color: #800000; font-weight: bold">1</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">李四</span><span style="color: #ff0000">‘</span><span style="color: #000000">);
  10. </span><span style="color: #008080">--</span><span style="color: #008080"> 非空字符不能插入null</span>
  11. <span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> student(id,NAME,gender) <span style="color: #0000ff">VALUES</span>(<span style="color: #800000; font-weight: bold">1</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">李四</span><span style="color: #ff0000">‘</span>,<span style="color: #0000ff">NULL</span>);

唯一

                   作用： 对字段的值不能重复

                   注意：

                                     1）唯一字段可以插入null                               

                                     2）唯一字段可以插入多个null

  1. <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> student(
  2.     id </span><span style="color: #0000ff">INT</span> <span style="color: #0000ff">UNIQUE</span>, <span style="color: #008080">--</span><span style="color: #008080"> 唯一</span>
  3.     NAME <span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">20</span><span style="color: #000000">)
  4. )
  5. </span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> student(id,NAME) <span style="color: #0000ff">VALUES</span>(<span style="color: #800000; font-weight: bold">1</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">zs</span><span style="color: #ff0000">‘</span><span style="color: #000000">);
  6. </span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> student(id,NAME) <span style="color: #0000ff">VALUES</span>(<span style="color: #800000; font-weight: bold">1</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">lisi</span><span style="color: #ff0000">‘</span>); <span style="color: #008080">--</span><span style="color: #008080"> ERROR 1062 (23000): Duplicate entry ‘1‘ for key ‘id‘</span>
  7. <span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> student(id,NAME) <span style="color: #0000ff">VALUES</span>(<span style="color: #800000; font-weight: bold">2</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">lisi</span><span style="color: #ff0000">‘</span>);

主键

                            作用： 非空+唯一

                            注意：                      

                                     1）通常情况下，每张表都会设置一个主键字段。用于标记表中的每条记录的唯一性。

                                     2）建议不要选择表的包含业务含义的字段作为主键，建议给每张表独立设计一个非业务含义的id字段。

  1. <span style="color: #008080">--</span><span style="color: #008080"> 1.4 主键（非空+唯一）</span>
  2. <span style="color: #0000ff">DROP</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> student;
  3. </span><span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> student(
  4.     id </span><span style="color: #0000ff">INT</span> <span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span>, <span style="color: #008080">--</span><span style="color: #008080"> 主键</span>
  5.     NAME <span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">20</span><span style="color: #000000">)
  6. )
  7. </span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> student(id,NAME) <span style="color: #0000ff">VALUES</span>(<span style="color: #800000; font-weight: bold">1</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">张三</span><span style="color: #ff0000">‘</span><span style="color: #000000">);
  8. </span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> student(id,NAME) <span style="color: #0000ff">VALUES</span>(<span style="color: #800000; font-weight: bold">2</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">张三</span><span style="color: #ff0000">‘</span><span style="color: #000000">);
  9. </span><span style="color: #008080">--</span><span style="color: #008080"> INSERT INTO student(id,NAME) VALUES(1,‘李四‘); -- 违反唯一约束： Duplicate entry ‘1‘ for key ‘PRIMARY‘</span>
  10. <span style="color: #008080">--</span><span style="color: #008080"> insert into student(name) value(‘李四‘); -- 违反非空约束： ERROR 1048 (23000): Column ‘id‘ cannot be null</span>

自增长

                   作用： 自动递增

  1. <span style="color: #008080">--</span><span style="color: #008080"> 1.5 自增长</span>
  2. <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> student(
  3.     id </span><span style="color: #0000ff">INT</span>(<span style="color: #800000; font-weight: bold">4</span>) ZEROFILL <span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span> AUTO_INCREMENT, <span style="color: #008080">--</span><span style="color: #008080"> 自增长，从0开始  ZEROFILL 零填充</span>
  4.     NAME <span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">20</span><span style="color: #000000">)
  5. )
  6. </span><span style="color: #008080">--</span><span style="color: #008080"> 自增长字段可以不赋值，自动递增</span>
  7. <span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> student(NAME) <span style="color: #0000ff">VALUES</span>(<span style="color: #ff0000">‘</span><span style="color: #ff0000">张三</span><span style="color: #ff0000">‘</span><span style="color: #000000">);
  8. </span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> student(NAME) <span style="color: #0000ff">VALUES</span>(<span style="color: #ff0000">‘</span><span style="color: #ff0000">李四</span><span style="color: #ff0000">‘</span><span style="color: #000000">);
  9. </span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> student(NAME) <span style="color: #0000ff">VALUES</span>(<span style="color: #ff0000">‘</span><span style="color: #ff0000">王五</span><span style="color: #ff0000">‘</span><span style="color: #000000">);
  10. </span><span style="color: #0000ff">SELECT</span> <span style="color: #808080">*</span> <span style="color: #0000ff">FROM</span><span style="color: #000000"> student;
  11. </span><span style="color: #008080">--</span><span style="color: #008080"> 不能影响自增长约束</span>
  12. <span style="color: #0000ff">DELETE</span> <span style="color: #0000ff">FROM</span><span style="color: #000000"> student;
  13. </span><span style="color: #008080">--</span><span style="color: #008080"> 可以影响自增长约束</span>
  14. <span style="color: #0000ff">TRUNCATE</span> <span style="color: #0000ff">TABLE</span> student;

外键

                   作用：约束两种表的数据                  

                   出现两种表的情况：

                            解决数据冗余高问题： 独立出一张表               

                                     例如： 员工表  和  部门表

                   问题出现：在插入员工表数据的时候，员工表的部门ID字段可以随便插入！！！！！   

                   使用外键约束：约束插入员工表的部门ID字段值 

                  解决办法： 在员工表的部门ID字段添加一个外键约束

  1. <span style="color: #008080">--</span><span style="color: #008080"> 部门表（主表）</span>
  2. <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> dept(
  3.     id </span><span style="color: #0000ff">INT</span> <span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span><span style="color: #000000">,
  4.     deptName </span><span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">20</span><span style="color: #000000">)
  5. )
  6. </span><span style="color: #008080">--</span><span style="color: #008080"> 修改员工表（副表/从表）</span>
  7. <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> employee(
  8.     id </span><span style="color: #0000ff">INT</span> <span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span><span style="color: #000000">,
  9.     empName </span><span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">20</span><span style="color: #000000">),
  10.     deptId </span><span style="color: #0000ff">INT</span>,<span style="color: #008080">--</span><span style="color: #008080"> 把部门名称改为部门ID</span>
  11.     <span style="color: #008080">--</span><span style="color: #008080"> 声明一个外键约束</span>
  12.     <span style="color: #0000ff">CONSTRAINT</span> emlyee_dept_fk <span style="color: #0000ff">FOREIGN</span> <span style="color: #0000ff">KEY</span>(deptId) <span style="color: #0000ff">REFERENCES</span><span style="color: #000000"> dept(id)
  13.     </span><span style="color: #008080">--</span><span style="color: #008080">           外键名称                  外键               参考表(参考字段)</span>
  14. )

注意：

1）被约束的表称为副表，约束别人的表称为主表，外键设置在副表上的！！！

2）主表的参考字段通用为主键！

3）添加数据： 先添加主表，再添加副表

4）修改数据： 先修改副表，再修改主表

5）删除数据： 先删除副表，再删除主表

 

  1. <span style="color: #008080">--</span><span style="color: #008080"> 1.6 外键约束</span><span style="color: #008080">
  2. --</span><span style="color: #008080"> 员工表</span>
  3. <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> employee(
  4.     id </span><span style="color: #0000ff">INT</span> <span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span><span style="color: #000000">,
  5.     empName </span><span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">20</span><span style="color: #000000">),
  6.     deptName </span><span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">20</span>) <span style="color: #008080">--</span><span style="color: #008080"> 部门名称</span>
  7. <span style="color: #000000">)
  8. </span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> employee <span style="color: #0000ff">VALUES</span>(<span style="color: #800000; font-weight: bold">1</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">张三</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">软件开发部</span><span style="color: #ff0000">‘</span><span style="color: #000000">);
  9. </span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> employee <span style="color: #0000ff">VALUES</span>(<span style="color: #800000; font-weight: bold">2</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">李四</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">软件开发部</span><span style="color: #ff0000">‘</span><span style="color: #000000">);
  10. </span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> employee <span style="color: #0000ff">VALUES</span>(<span style="color: #800000; font-weight: bold">3</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">王五</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">应用维护部</span><span style="color: #ff0000">‘</span><span style="color: #000000">);
  11. </span><span style="color: #0000ff">SELECT</span> <span style="color: #808080">*</span> <span style="color: #0000ff">FROM</span><span style="color: #000000"> employee;
  12. </span><span style="color: #008080">--</span><span style="color: #008080"> 添加员工，部门名称的数据冗余高</span>
  13. <span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> employee <span style="color: #0000ff">VALUES</span>(<span style="color: #800000; font-weight: bold">4</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">陈六</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">软件开发部</span><span style="color: #ff0000">‘</span><span style="color: #000000">);
  14. </span><span style="color: #008080">--</span><span style="color: #008080"> 解决数据冗余高的问题：给冗余的字段放到一张独立表中</span><span style="color: #008080">
  15. --</span><span style="color: #008080"> 独立设计一张部门表</span>
  16. <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> dept(
  17.     id </span><span style="color: #0000ff">INT</span> <span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span><span style="color: #000000">,
  18.     deptName </span><span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">20</span><span style="color: #000000">)
  19. )
  20. </span><span style="color: #0000ff">DROP</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> employee;
  21. </span><span style="color: #008080">--</span><span style="color: #008080"> 修改员工表</span>
  22. <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> employee(
  23.     id </span><span style="color: #0000ff">INT</span> <span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span><span style="color: #000000">,
  24.     empName </span><span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">20</span><span style="color: #000000">),
  25.     deptId </span><span style="color: #0000ff">INT</span>,<span style="color: #008080">--</span><span style="color: #008080"> 把部门名称改为部门ID</span>
  26.     <span style="color: #008080">--</span><span style="color: #008080"> 声明一个外键约束</span>
  27.     <span style="color: #0000ff">CONSTRAINT</span> emlyee_dept_fk <span style="color: #0000ff">FOREIGN</span> <span style="color: #0000ff">KEY</span>(deptId) <span style="color: #0000ff">REFERENCES</span> dept(id) <span style="color: #0000ff">ON</span> <span style="color: #0000ff">UPDATE</span> <span style="color: #0000ff">CASCADE</span> <span style="color: #0000ff">ON</span> <span style="color: #0000ff">DELETE</span> <span style="color: #0000ff">CASCADE</span>  <span style="color: #008080">--</span><span style="color: #008080"> ON CASCADE UPDATE ：级联修改</span>
  28.     <span style="color: #008080">--</span><span style="color: #008080">           外键名称                  外键               参考表(参考字段)</span>
  29. <span style="color: #000000">)
  30. </span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> dept(id,deptName) <span style="color: #0000ff">VALUES</span>(<span style="color: #800000; font-weight: bold">1</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">软件开发部</span><span style="color: #ff0000">‘</span><span style="color: #000000">);
  31. </span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> dept(id,deptName) <span style="color: #0000ff">VALUES</span>(<span style="color: #800000; font-weight: bold">2</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">应用维护部</span><span style="color: #ff0000">‘</span><span style="color: #000000">);
  32. </span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> dept(id,deptName) <span style="color: #0000ff">VALUES</span>(<span style="color: #800000; font-weight: bold">3</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">秘书部</span><span style="color: #ff0000">‘</span><span style="color: #000000">);
  33. </span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> employee <span style="color: #0000ff">VALUES</span>(<span style="color: #800000; font-weight: bold">1</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">张三</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">);
  34. </span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> employee <span style="color: #0000ff">VALUES</span>(<span style="color: #800000; font-weight: bold">2</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">李四</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">);
  35. </span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> employee <span style="color: #0000ff">VALUES</span>(<span style="color: #800000; font-weight: bold">3</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">王五</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">2</span><span style="color: #000000">);
  36. </span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> employee <span style="color: #0000ff">VALUES</span>(<span style="color: #800000; font-weight: bold">4</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">陈六</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">3</span><span style="color: #000000">);
  37. </span><span style="color: #008080">--</span><span style="color: #008080"> 问题: 该记录业务上不合法，员工插入了一个不存在的部门数据</span>
  38. <span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> employee <span style="color: #0000ff">VALUES</span>(<span style="color: #800000; font-weight: bold">5</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">陈六</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">4</span>);<br> <span style="color: #008080">--</span><span style="color: #008080"> 违反外键约束： Cannot add or update a child row: a foreign key constraint fails (`day16`.`employee`, CONSTRAINT `emlyee_dept_fk` FOREIGN KEY (`deptId`) REFERENCES `dept` (`id`))</span>
  39. <span style="color: #008080">--</span><span style="color: #008080"> 1）当有了外键约束，添加数据的顺序： 先添加主表，再添加副表数据</span><span style="color: #008080">
  40. --</span><span style="color: #008080"> 2）当有了外键约束，修改数据的顺序： 先修改副表，再修改主表数据</span><span style="color: #008080">
  41. --</span><span style="color: #008080"> 3）当有了外键约束，删除数据的顺序： 先删除副表，再删除主表数据</span><span style="color: #008080">
  42. --</span><span style="color: #008080"> 修改部门(不能直接修改主表)</span>
  43. <span style="color: #0000ff">UPDATE</span> dept <span style="color: #0000ff">SET</span> id<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">4</span> <span style="color: #0000ff">WHERE</span> id<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">3</span><span style="color: #000000">;
  44. </span><span style="color: #008080">--</span><span style="color: #008080"> 先修改员工表</span>
  45. <span style="color: #0000ff">UPDATE</span> employee <span style="color: #0000ff">SET</span> deptId<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">2</span> <span style="color: #0000ff">WHERE</span> id<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">4</span><span style="color: #000000">;
  46.  
  47. </span><span style="color: #008080">--</span><span style="color: #008080"> 删除部门</span>
  48. <span style="color: #0000ff">DELETE</span> <span style="color: #0000ff">FROM</span> dept <span style="color: #0000ff">WHERE</span> id<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">2</span><span style="color: #000000">;
  49. </span><span style="color: #008080">--</span><span style="color: #008080"> 先删除员工表</span>
  50. <span style="color: #0000ff">DELETE</span> <span style="color: #0000ff">FROM</span> employee <span style="color: #0000ff">WHERE</span> deptId<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">2</span><span style="color: #000000">;
  51. </span><span style="color: #0000ff">SELECT</span> <span style="color: #808080">*</span> <span style="color: #0000ff">FROM</span><span style="color: #000000"> dept;
  52. </span><span style="color: #0000ff">SELECT</span> <span style="color: #808080">*</span> <span style="color: #0000ff">FROM</span> employee;

级联操作

                   问题： 当有了外键约束的时候，必须先修改或删除副表中的所有关联数据，才能修改或删除主表！但是，我们希望直接修改或删除主表数据，从而影响副表数据。可以使用级联操作实现！！！

 

                   级联修改： ON UPDATE CASCADE

                   级联删除： ON DELETE CASCADE

  1. <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> employee(
  2.     id </span><span style="color: #0000ff">INT</span> <span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span><span style="color: #000000">,
  3.     empName </span><span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">20</span><span style="color: #000000">),
  4.     deptId </span><span style="color: #0000ff">INT</span>,<span style="color: #008080">--</span><span style="color: #008080"> 把部门名称改为部门ID</span>
  5.     <span style="color: #008080">--</span><span style="color: #008080"> 声明一个外键约束</span>
  6.     <span style="color: #0000ff">CONSTRAINT</span> emlyee_dept_fk <span style="color: #0000ff">FOREIGN</span> <span style="color: #0000ff">KEY</span>(deptId) <span style="color: #0000ff">REFERENCES</span> dept(id) <span style="color: #0000ff">ON</span> <span style="color: #0000ff">UPDATE</span> <span style="color: #0000ff">CASCADE</span> <span style="color: #0000ff">ON</span> <span style="color: #0000ff">DELETE</span> <span style="color: #0000ff">CASCADE</span>  <span style="color: #008080">--</span><span style="color: #008080"> ON CASCADE UPDATE ：级联修改</span>
  7.     <span style="color: #008080">--</span><span style="color: #008080">           外键名称                  外键               参考表(参考字段)  更新同级          删除同级</span>
  8. <span style="color: #000000">)
  9. </span><span style="color: #008080">--</span><span style="color: #008080"> 注意： 级联操作必须在外键基础上使用</span><span style="color: #008080">
  10. --</span><span style="color: #008080"> 级联修改（修改）</span><span style="color: #008080">
  11. --</span><span style="color: #008080"> 直接修改部门</span>
  12. <span style="color: #0000ff">UPDATE</span> dept <span style="color: #0000ff">SET</span> id<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">5</span> <span style="color: #0000ff">WHERE</span> id<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">4</span><span style="color: #000000">;
  13. </span><span style="color: #008080">--</span><span style="color: #008080"> 级联删除</span><span style="color: #008080">
  14. --</span><span style="color: #008080"> 直接删除部门 </span>
  15. <span style="color: #0000ff">DELETE</span> <span style="color: #0000ff">FROM</span> dept <span style="color: #0000ff">WHERE</span> id<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">1</span>;

 

mysql3

标签：外键约束   const   存储过程   赋值   var   table   通用   约束   级联删除   

人气教程排行

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