mysql 数据库学习笔记 3

 

mysql 数据库学习笔记 3 外键 和 select 子查询

标签： 数据库mysql

2016-04-18 15:03 1785人阅读 评论(0) 收藏 举报

目录(?)[+]

1. 外键

1. 如果一个实体 student 的某个字段 classid 指向(引用) 另一个实体的主键 classid, 我们就称 student 实体的 classid 为外键。

2. 被指向的实体， 我们称为是主实体(父实体， 主表， 父表)；而负责指向的实体， 称为是从实体(子实体， 从表， 字表)。

3. 作用： 用来约束处于关系内的实体

   1. 增加子表记录时， 是否有与之对应的主表记录
   2. 在删除或者更新主表记录的时候， 从表应该如何处理

4. 定义一个外键， 使用关键字 foreign key 
   在从表上面添加一个外键字段， 用来指向主表的主键。

发生错误了， 查看原因：

<code class="hljs sql has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;">    <span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">SHOW</span> ENGINE INNODB STATUS;</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>

显示错误信息：（LATEST FOREIGN KEY ERROR） 
 
仔细分析一下代码发现是我们的tbl_name 表 没有建立！！！

修正后， 相应table 创建成功 

然后插入数据: 
 

1. 设置表之间的级联操作：

   1. 级联操作是指， 在操作一个表的时候会如何影响另一个表
   2. 通常是主表数据发生改变， 从表数据相应处理
   3. on update, on delete 针对主表数据变动而言
   4. 允许的级联操作： 
      
      1. cascade 从表相应处理
      2. set null 将从表设置为 null
      3. restrict 拒绝主表的相关操作

2. 修改外键

   1. 修改外键的流程一般是 先删除 再新建 
      删除的时候， 一般需要 有外键的信息： 
       
      然后根据外键信息进行删除 
       
      新建外键， 并更新主表数据 
      
   2. 另外需要特别注意的是， 外键是定义在从表上的， 修改时应该修改从表
   3. 外键允许为 null

2. 存储引擎

1. 关于这部分内容， 我们转载了一篇文章 介绍了一部分内容http://blog.csdn.net/zhyh1435589631/article/details/51183365
2. innodb 行级锁定（并发性能好）适用于删除更新较多的情况， 支持事务和外键
3. myisam 表级锁定， 使用于插入， 检索较多的情形

3. select 查询子句

3.1 order by

order by 主要是用来对指定字段进行排序 (asc | desc), 根据校对规则 
他支持多字段排序。 如果是分组排序的话， 使用 group by 

3.2 limit 限定获取记录的数量信息

一般都是放在SQL最后的位置

<code class="hljs css has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"><span class="hljs-tag" style="color: rgb(0, 0, 0); box-sizing: border-box;">limit</span> <span class="hljs-attr_selector" style="color: rgb(0, 136, 0); box-sizing: border-box;">[offset, ]</span> <span class="hljs-tag" style="color: rgb(0, 0, 0); box-sizing: border-box;">row_count</span>;</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li></ul>

3.3 distinct 去除重复记录

相应的 all 表示显示所有记录

3.4 union 联合查询

1. 将多条select 语句的结果合并到一起， 如果union 结果中存在重复记录， 默认是会消除重复记录信息的。 我们可以使用 union all 实现不消除重复信息的效果。 
   
2. 只有在子语句的排序操作 与 limit 放在一起的时候， 子语句的 order by 才是有效的， 否则 union 会自动将他忽略掉 
    
   
3. 要求子语句中检索的字段的类型应该保持一致， 结果中列名由第一个select 中的列名来决定

3.5 子查询

1. definition： 如果一个语句出现在另一个语句的内部， 那么我们将该查询语句称为是子查询。 ps ： 子查询需要使用括号进行包裹。 
   

3.5.1 子查询的分类

1. 按照返回值分类: 
   
   • 单一值： 支持运算操作： < > >= <= == !=
   • 一列： 支持运算操作： in, not in, any, all
   • 一行： 
     
     • 一般采用 limit 1 作为限定， 运算符： = ， in
   • 表格： 
     
     • 一般通过为表格起一个别名 as xxx 来实现简化操作
2. 按照子查询应用位置 
   
   • where
   • from
   • exist
3. != all ⇌ not in
4. = any ⇌ in
5. != any ⇌ not some 
   只要和集合中的某些元素不相等就可以了， ps： 当集合多于一个元素的时候， 一般返回全集

3.6 联结查询

1. 一个实体是一个表
2. 一个业务逻辑， 使用多个实体数据， 多张表一起使用将多个表的记录连接起来
3. 总体思路： 
   
   • 将所有的数据按照某种条件， 连接起来， 再做筛选

4. 分类：

   • 内连接 （inner join on） 
     
     • default， 要求连接的多个数据必须是真实存在的 , 连接的时候， 可以省略inner 条件
     • 另外， 当不写条件的是时候， 我们默认为 笛卡尔积连接 cross join 
       
     • 有条件的内连接 
       
       • on 
         
         • 连接的时候， 将无效的连接过滤掉
       • where 
         
         • 先做笛卡尔积， 连接成功之后， 在做过滤
       • using 
         
         • 要求， 他们连个表有相同的字段, 并且，* 他会自动合并这个相同字段， 并放在最前面* 
           
   • 外联结 （left / right outer join on） 
     
     • 他的流程基本上是这样的： 先拿一边表中的记录和另一边表中所有的记录做连接， 保留连接成功的记录
     • ps： 外联结只能使用 on， using 不能使用 where
     • 区分左外联结， 右外联结， 全外联结（mysql 暂不支持， 不过可以利用 union 查询运算得到， ie 左外连接 + 右外连接）
   • 自然连接 
     

5. ps 连接指的是数据之间的关系