当前位置：Gxlcms > 数据库问题 > mysql数据库基本操作（六）

mysql数据库基本操作（六）

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

  1. <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> Classhost(
  2.        id </span><span style="color: #0000ff">TINYINT</span> <span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span><span style="color: #000000"> auto_increment,
  3.        name </span><span style="color: #0000ff">VARCHAR</span> (<span style="color: #800000; font-weight: bold">20</span><span style="color: #000000">),
  4.        age </span><span style="color: #0000ff">INT</span><span style="color: #000000"> ,
  5.        is_marriged boolean 
  6. );<br>
  7. </span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> Classhost (name,age,is_marriged) <span style="color: #0000ff">VALUES</span> ("丽丽",<span style="color: #800000; font-weight: bold">22</span>,<span style="color: #800000; font-weight: bold">0</span><span style="color: #000000">),
  8.                                                        ("莉莉",</span><span style="color: #800000; font-weight: bold">24</span>,<span style="color: #800000; font-weight: bold">0</span><span style="color: #000000">),
  9.                                                        ("李丽",</span><span style="color: #800000; font-weight: bold">22</span>,<span style="color: #800000; font-weight: bold">0</span><span style="color: #000000">),
  10.                                                        ("李莉",</span><span style="color: #800000; font-weight: bold">20</span>,<span style="color: #800000; font-weight: bold">0</span><span style="color: #000000">);
  11. </span><span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> student(
  12.         id </span><span style="color: #0000ff">INT</span> <span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span><span style="color: #000000"> auto_increment,
  13.        name </span><span style="color: #0000ff">VARCHAR</span> (<span style="color: #800000; font-weight: bold">20</span><span style="color: #000000">),
  14.        </span><span style="color: #ff00ff">host_id</span> <span style="color: #0000ff">TINYINT</span><span style="color: #000000">   
  15. )ENGINE</span><span style="color: #808080">=</span><span style="color: #000000">INNODB;
  16. </span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span> Student(name,<span style="color: #ff00ff">host_id</span>) <span style="color: #0000ff">VALUES</span> ("pengfy",<span style="color: #800000; font-weight: bold">2</span><span style="color: #000000">),
  17.                                             ("pyq",</span><span style="color: #800000; font-weight: bold">4</span><span style="color: #000000">),
  18.                                             ("xiaojiang",</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">),
  19.                                             ("pyq2",</span><span style="color: #800000; font-weight: bold">3</span><span style="color: #000000">),
  20.                                             ("pengfy2",</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">),
  21.                                             ("pyq3",</span><span style="color: #800000; font-weight: bold">3</span><span style="color: #000000">),
  22.                                             ("lily",</span><span style="color: #800000; font-weight: bold">2</span>);

     创建好了两张表如下:

 技术图片

技术图片

     现在假设丽丽辞职了，我们试一下删除名字叫丽丽的班主任：

  1. <span style="color: #0000ff">delete</span> <span style="color: #0000ff">from</span> classhost <span style="color: #0000ff">where</span> name<span style="color: #808080">=</span>"丽丽";

技术图片

     还真的删掉了，那现在看看学生表有没有什么变化：

技术图片

        发现xiaojaing和pengfy2的班主任id还是1，这显然是不合适的，一个班主任走了，学生信息也要变化，要么分到别的班，要么一起走（特别喜欢这个班主任），大家也可是试一下在删除班主任后，在学生表里面插入一条学生信息，host_id设为1，也是OK的。这两种情况显示不合理，那么这个时候就要引入外键这个概念了，现在给学生的表加上一个外键，首先要找到两张表关联的地方，就是学生表里面host_id和老师表里面的id是关联的，那我们新建一下学生表2：

  1. <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> student2(
  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"> auto_increment,
  3.        name </span><span style="color: #0000ff">VARCHAR</span> (<span style="color: #800000; font-weight: bold">20</span><span style="color: #000000">),
  4.        </span><span style="color: #ff00ff">host_id</span> <span style="color: #0000ff">TINYINT</span><span style="color: #000000">,    --切记:作为外键一定要和关联主键的数据类型保持一致       <br></span><span style="color: #0000ff">       FOREIGN</span> <span style="color: #0000ff">KEY</span>(<span style="color: #ff00ff">host_id</span>) <span style="color: #0000ff">REFERENCES</span><span style="color: #000000"> classhost(id)
  5. )ENGINE</span><span style="color: #808080">=</span>INNODB;<br><br><span data-mce-="">INSERT <span data-mce-="">INTO Student2(name,<span data-mce-="">host_id) <span data-mce-="">VALUES ("pengfy",<span data-mce-="">2<span data-mce-="">),<br>                                            ("pyq",<span data-mce-="">4<span data-mce-="">),<br>                                            ("xiaojiang",<span data-mce-="">4<span data-mce-="">),<br>                                            ("pyq2",<span data-mce-="">3<span data-mce-="">),<br>                                            ("pengfy2",<span data-mce-="">4<span data-mce-="">),<br>                                            ("pyq3",<span data-mce-="">3<span data-mce-="">),<br>                                            ("lily",<span data-mce-="">2);</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

       这里新的学生信息也要改一下，把id为1的要改成其他的，不然会报错。现在再试一下，删掉id为2的班主任：

  1. <span style="color: #0000ff">delete</span> <span style="color: #0000ff">from</span> classhost <span style="color: #0000ff">where</span> id<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">2</span>;

       报错了，不让删除了，因为外键约束了，报错信息如下：

  1.  Cannot <span style="color: #0000ff">delete</span> <span style="color: #808080">or</span> <span style="color: #0000ff">update</span> a parent row: a <span style="color: #0000ff">foreign</span> <span style="color: #0000ff">key</span> <span style="color: #0000ff">constraint</span> fails (`test`.`student2`, <span style="color: #0000ff">CONSTRAINT</span> `student2_ibfk_1` <span style="color: #0000ff">FOREIGN</span> <span style="color: #0000ff">KEY</span> (`<span style="color: #ff00ff">host_id</span>`) <span style="color: #0000ff">REFERENCES</span> `classhost` (`id`))

      再试一下给id=1的老师加一个学生，看是否能添加成功：

  1. <span style="color: #0000ff">insert</span> <span style="color: #0000ff">into</span> student2(name,<span style="color: #ff00ff">host_id</span>) <span style="color: #0000ff">values</span> (<span style="color: #ff0000">‘</span><span style="color: #ff0000">lucy</span><span style="color: #ff0000">‘</span>,<span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">);
  2. 报错信息：
  3. Cannot </span><span style="color: #0000ff">add</span> <span style="color: #808080">or</span> <span style="color: #0000ff">update</span> a child row: a <span style="color: #0000ff">foreign</span> <span style="color: #0000ff">key</span> <span style="color: #0000ff">constraint</span> fails (`test`.`student2`, <span style="color: #0000ff">CONSTRAINT</span> `student2_ibfk_1` <span style="color: #0000ff">FOREIGN</span> <span style="color: #0000ff">KEY</span> (`<span style="color: #ff00ff">host_id</span>`) <span style="color: #0000ff">REFERENCES</span> `classhost` (`id`))

     同样报错了 ，看一下报错信息里面有个东西，CONSTRAINT `student2_ibfk_1`这个东西我没有写过，后面的FOREIGN KEY (`host_id`) REFERENCES `classhost` (`id`))才是我写的，那这个是什么呢?这个是外键名称，你不自定义的时候，会默认给你加上一个，所以给一个表增加一个外键完整的写法是这样的：

  1. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> student  <span style="color: #0000ff">ADD</span> <span style="color: #0000ff">CONSTRAINT</span><span style="color: #000000"> anyname
  2.                      </span><span style="color: #0000ff">FOREIGN</span> <span style="color: #0000ff">KEY</span>(<span style="color: #ff00ff">host_id</span><span style="color: #000000">)
  3.                      </span><span style="color: #0000ff">REFERENCES</span>  classhost(id);

     试着给我们学生表1增加一个外键：

  1.  Cannot <span style="color: #0000ff">add</span> <span style="color: #808080">or</span> <span style="color: #0000ff">update</span> a child row: a <span style="color: #0000ff">foreign</span> <span style="color: #0000ff">key</span> <span style="color: #0000ff">constraint</span> fails (`test`.`#sql<span style="color: #808080">-</span>1110_<span style="color: #800000; font-weight: bold">2</span>`, <span style="color: #0000ff">CONSTRAINT</span> `anyname` <span style="color: #0000ff">FOREIGN</span> <span style="color: #0000ff">KEY</span> (`<span style="color: #ff00ff">host_id</span>`) <span style="color: #0000ff">REFERENCES</span> `classhost` (`id`))

     也报错了，这是为什么呢？因为老师里面已经没有id=1的了，而学生表还存在，看来要修改一下学生表才行：

技术图片

     第三个和第五个改成4好了，

  1. <span style="color: #0000ff">update</span> student <span style="color: #0000ff">set</span> <span style="color: #ff00ff">host_id</span><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: #808080">or</span> id <span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">5</span>;

     再试一下增加外键，看一下表结构：

技术图片

      添加成功，那么能增加就能删除嘛，试一下删除这个外键：

  1.  <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> student <span style="color: #0000ff">DROP</span> <span style="color: #0000ff">FOREIGN</span> <span style="color: #0000ff">KEY</span> anyname;

      查看是否删除成功：

技术图片

     已经找不到外键了，删除成功喽。那现在的问题就是主表怎么正常删除数据，并且把子表的数据也一并删除。这就涉及下面要讲的INNODB支持的ON语句。

INNODB支持的ON语句

    外键约束对子表的含义:   如果在父表中找不到候选键,则不允许在子表上进行insert/update
    外键约束对父表的含义: 在父表上进行update/delete以更新或删除在子表中有一条或多条对应匹配行的候选键时,父表的行为取决于：在定义子表的外键时指定的on update/on delete子句

innodb支持的四种方式

1.cascade方式

      在父表上update/delete记录时，同步update/delete掉子表的匹配记录，外键的级联删除：如果父表中的记录被删除，则子表中对应的记录自动被删除，比如刚才在删除班主任的同事，也把学生删除，就应该在主键中加上一段ON DELETE CASCADE：

  1. <span style="color: #0000ff">FOREIGN</span> <span style="color: #0000ff">KEY</span> (charger_id) <span style="color: #0000ff">REFERENCES</span><span style="color: #000000"> ClassCharger(id)
  2.                               </span><span style="color: #0000ff">ON</span> <span style="color: #0000ff">DELETE</span> <span style="color: #0000ff">CASCADE</span>

      我们再给student加上这么一个外键：

  1. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> student  <span style="color: #0000ff">ADD</span> <span style="color: #0000ff">CONSTRAINT</span><span style="color: #000000"> anyname
  2.                      </span><span style="color: #0000ff">FOREIGN</span> <span style="color: #0000ff">KEY</span>(<span style="color: #ff00ff">host_id</span><span style="color: #000000">)
  3.                      </span><span style="color: #0000ff">REFERENCES</span><span style="color: #000000">  classhost(id)
  4.                      </span><span style="color: #0000ff">ON</span> <span style="color: #0000ff">DELETE</span> <span style="color: #0000ff">CASCADE</span>;

     同时我们也要把student2这张表删除，因为主表也和这个表绑定了，外键没改到时候删除主表还是会报错的，删掉之后，试一下删除classhost表里面的一个内容：

技术图片

     成功了，没有报错啊，看看子表里面，原先我们的子表id=1和id=7都是host_id=2的：

技术图片

      现在子表里面绑定了host_id=2的信息也都一起删除了，除了这种，还有以下三种情况。

2.set null方式

      在父表上update/delete记录时，将子表上匹配记录的列设为null，要注意子表的外键列不能为not null

  1. <span style="color: #0000ff">FOREIGN</span> <span style="color: #0000ff">KEY</span> (charger_id) <span style="color: #0000ff">REFERENCES</span><span style="color: #000000"> ClassCharger(id)
  2.                               </span><span style="color: #0000ff">ON</span> <span style="color: #0000ff">DELETE</span> <span style="color: #0000ff">SET</span> <span style="color: #0000ff">NULL</span>

      有兴趣可以试一下这种方式，就不演示了。

3.Restrict方式

       拒绝对父表进行删除更新操作，这个很少用，相当于不让sql报错，了解一下就行了。

4.No action方式

      在mysql中同Restrict,如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作，这个也作一个了解就行。

 

mysql数据库基本操作（六）

标签：OLE   style   rri   png   基本操作   foreign   test   多个   其他   

人气教程排行

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