第三章---MySQL高级查询

先让我们回顾一下，上一章节的知识，看一个小练习：

        提问：下面的语句有没有错误？实现了什么功能

                 SELECT * FROM `student`
                 WHERE `studentNo` IN  (SELECT `studentNo` FROM result );

        结果：没有错误 ； 查询学生表中，与成绩表里面学号相同的学生的信息

一。下面就进入本章的知识---高级查询

1. exists子查询

大家可以想一想，怎么样就可以去检测一张表有没有建立？      其实这的语法跟英语非常相似，用if判断一下

         DROP TABLE IF EXISTS student ;

         CREATE TABLE student (..................) ;

我们可以做一些练习，来熟悉一下

#查询一下Logic Java 最后一次考试成绩大于80的记录

SELECT * FROM `result` 

WHERE  `subjectNo` = (

SELECT `subjectNo` FROM `subject`  WHERE  `subjectName` = 'Login Java'

) AND `examData` = (SELECT MAX(`examDate`) FROM `result` 

WHERE  `subjectNo` =(SELECT `subjectNo` FROM `subject`  WHERE  `subjectName` = 'Login Java')

) AND `studentResult`> 80 )

AND `subjectNo` = (SELECT `subjectNo` FROM `subject`  WHERE  `subjectName` = 'Login Java')

ORDER BY `studentResult`  DESC        #desc   是从高到低降序排序

LIMIT  5 ;

注：exists 和 in 是可以互换使用的，但in是逐个去查询，速度慢；而exists速度很快，它查询的结果只有两种：TRUE 或者FALSE，要么返回真，要么返回假   所以exists就分为了相关子查询、不相关子查询

我们做一个exists 和 in 的练习

       #查询参加考试的学生信息，用in     

             SELECT * FROM student WHERE studentNo IN(

SELECT r. studentNo FROM result  r )

       #相关子查询，exists

             SELECT * FROM student WHERE EXISTS(

SELECT  r.studentNo FROM result  r 

WHERE  r.studentNo = student.studentNo )

2.子查询的注意事项：

      a)任何允许使用表达式的地方都可以用子查询

      b)嵌套在父查询SELECT语句的子查询可以包括：

               （1）SELECT语句

               （2）FROM语句

               （3）WHERE语句

               （4）GROUP BY 语句

               （5）HAVING 语句

       c)只出现在子查询中而没有出现在父查询中的列，不可以包含在输出列中

3.下面做一些练习，巩固一下

（1）统计一下每个年级男女生人数

          SELECT  gradeID, sex ,COUNT(sex)  FROM student

          GROUP BY sex,gradeID

（2）查询每门课程的平均分，并且按照分数从大到小去排序

          SELECT subjectNo,AVG(studentResult) FROM result

          GROUP BY AVG(studentResult)  DESC

我们可以总结一下语句的作用：

      WHERE语句 ： 用来筛选FROM子句中指定的操作所产生的行

      GROUP BY 语句： 用来分组 WHERE子句的输出

      HAVING语句  ：   用来从分组的结果中筛选行

注意：如果先进行了分组排序GROUP BY，就不能在它后面使用WHERE语句；这个时候如果还想继续添加条件，只能使用HAVING 语句！！！

二。常用的多表连接查询

1.内连接（INNER JOIN）和外连接---左外连接（LEFT JOIN）跟右外连接（RIGHT JOIN）

         #查询学生的学号、学生姓名、学生成绩

                   SELECT studentNo,studentName,studentResult FROM student 

                   INNER JOIN  result  ON  student. studentNo = result.studentNo

          也可以用第二种方法写：

                   SELECT studentNo,studentName,studentResult  FROM student,result

                   WHERE ...............................

注意： 使用INNER JOIN 内连，连接的两张表，两者必须要有共同匹配的内容！！！

LEFT JOIN 左侧的为主表 ；RIGHT JOIN 右侧的为 主表

三。如何去创建表？需要满足什么条件？

三大范式：

       a)第一范式:确保每列的原子性.
    如果每列(或者每个属性)都是不可再分的最小数据单元(也称为最小的原子单元),则满足第一范式.
    例如:顾客表(姓名、编号、地址、……)其中"地址"列还可以细分为国家、省、市、区等。

      b)第二范式:在第一范式的基础上更进一层,目标是确保表中的每列都和主键相关.
    如果一个关系满足第一范式,并且除了主键以外的其它列,都依赖于该主键,则满足第二范式.
    例如:订单表(订单编号、产品编号、定购日期、价格、……)，"订单编号"为主键，"产品编号"和主键列没有直接的关系，即"产品编号"列不依赖于主键列，应删除该列。

     c)第三范式:在第二范式的基础上更进一层,目标是确保每列都和主键列直接相关,而不是间接相关.
    !!如果一个关系满足第二范式,并且除了主键以外的其它列都不依赖于主键列,则满足第三范式.
   为了理解第三范式，需要根据Armstrong公里之一定义传递依赖。假设A、B和C是关系R的三个属性，如果A-〉B且B-〉C，则从这些函数依赖中，可以得出A-〉C，如上所述，依赖A-〉C是传递依赖。

    例如:订单表(订单编号，定购日期，顾客编号，顾客姓名，……)，初看该表没有问题，满足第二范式，每列都和主键列"订单编号"相关，再细看你会发现"顾客姓名"和"顾客编号"相关，"顾客编号"和"订单编号"又相关，最后经过传递依赖，"顾客姓名"也和"订单编号"相关。为了满足第三范式，应去掉"顾客姓名"列，放入客户表中。

四。事务

1.首先我们了解一下它的定义：事务是作为单个逻辑工作单元执行的一系列操作

多个操作作为一个整体向系统提交，要么都执行，要么都不执行

事务是一个不可分割的工作逻辑单元

2.事务必备的4个属性，简称ACID属性 ：

        原子性（Atomiccity）:       事务是一个完整的操作，每一步都是不可以分的

        一致性（Consistency）:     当事务完成时，数据必须处于同一状态

        隔离性（Isolation）:          并发事务之间彼此隔离、独立，它不应该以任何方式依赖或影响了其他事务

        持久性（Durability）:        事务完成后，它对数据库的修改是会被永久保持的

3.我们可以去思考一下，怎么样就能够创建一个事务？（拓：MySQL中支持事务的引擎有 InnoDB 和 BDB）

先列一下语法：   

                     开始事务用  ----  BEGIN  或者  START TRANSACTION

                     提交事务     ----   COMMIT

                     回滚事务     ----   ROLLBACK

做一个练习，来感受一下它的用法：  

#转账---张三给李四转500元，求转账之后两人户头的金额

         CREATE DATABASE mybank;
         USE mybank;
         CREATE TABLE `bank`(
              `customerName` CHAR(10),      #用户名  
              `currentMoney` DECIMAL(10,2)  #当前余额    
          );

         INSERT INTO `bank`(`customerName`,`currentMoney`)
         VALUES('张三',1000);
         INSERT INTO `bank`(`customerName`,`currentMoney`) VALUES('李四',1);

         BEGIN;
         UPDATE `bank`  SET  `currentmoney` = `currentmoney` - 500

                  WHERE `customerName` = ' 张三' ;

         UPDATE `bank` SET  `currentmoney` = `currentmoney` +500

                  WHERE `customerName` = '李四' ;