mysql 高级查询总结

一，高级查询
如何用SQL语句检测temp表是否已经创建？
DROP TABLE IF EXISTS temp;
CREATE TABLE temp (
    … … #省略建表语句
) ;
加粗斜体（是否还有其他用法？）
EXISTS子查询的语法
SELECT......FROM表名WHERE EXISTS(子查询)；
子查询有返回结果： EXISTS子查询结果为TRUE
子查询无返回结果： EXISTS子查询结果为FALSE,
                                 外层查询不执行
举一个例子：题目为检查“Logic Java”课程最近一次考试成绩
SELECT AVG(studentresult)+5 AS 平均分 FROM result
WHERE NOT EXISTS (
SELECT * FROM `result`  WHERE `subjectNo` = (
   SELECT `subjectNo` FROM `subject` WHERE `subjectName` = 'Logic Java'
  )  AND `examDate` = (
     SELECT MAX(`examDate`) FROM `result` WHERE `subjectNo` = (     
     SELECT `subjectNo` FROM `subject` 
     WHERE `subjectName` = 'Logic Java') 
  ) AND `studentResult` > 60)
AND `subjectNo` = ( SELECT `subjectNo` FROM `subject` 
WHERE `subjectName` = 'Logic Java') 
AND `examDate` = (
  SELECT MAX(`examDate`) FROM `result` WHERE `subjectNo` = (     
  SELECT `subjectNo` FROM `subject` 
  WHERE `subjectName` = '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')
(exists 和 in 是可以互换使用)
#exists：速度快，查询的结果只有两种TRUE  or FALSE

#in :逐个去查询

二。常用的多表连接查询

1,      子查询注意事项：

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

只出现在子查询中而没有出现在父查询中的列不能包含在输出列中
嵌套在父查询SELECT语句的子查询可包括

1. SELECT子句
2. FROM子句
3. WHERE子句:用来筛选FROM子句中指定的操作所产生的行
4. GROUP BY子句 :用来分组 WHERE子句的输出
5. HAVING子句 :用来从分组的结果中筛选

a.分组查询：

SELECT …… FROM  <表名>  
WHERE  ……
GROUP BY ……

b.分组筛选：

示例：

SELECT `subjectNo`,AVG(`studentResult`) AS 课程平均成绩
FROM `result`
GROUP BY `subjectNo`
HAVING AVG(`studentResult`) >=60;

c.where与having的对比：

WHERE子句：用来筛选 FROM 子句中指定的操作所产生的行 
GROUP BY子句：用来分组 WHERE 子句的输出 
HAVING子句：用来从分组的结果中筛选行 

内连接（INNER JOIN）和外连接  左外连接（LEFT JOIN）右外连接（RIGHT JOIN）

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

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

三，事务

事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作。多个操作作为一个整体向系统提交，要么都执行、要么都不执行 。
事务是一个不可分割的工作逻辑单元 

MySQL中支持事务的储存引擎有innoDB和BDB

开始事务 begin /start transaction；

提交事务 commit；

回滚或者撤销事务 rollback；

自动关闭好人开启事务：

默认情况下，每天单独的sql语句视为一个事务

关闭默认提交状态后，可手动开启、关闭事务

关闭/开启自动提交状态：

set auto commit=0|1；

关闭自动提交后，从下一条SQL语句开始则开启新事务，需使用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` = '李四' ;

视图需求原因：不同的人关注不同发数据，保证数据的安全性；

视图是一张虚拟表：表示一张表的部分数据或多张表的综合数据。其结构和数据是建立在对表的查询基础上。视图中不存放数据

数据存放在视图所引用的原始表中。一个原始表，根据不同用户的不同需求，可以创建不同的视图

视图的用途

1.   筛选表中的行
2.   防止未经许可的用户访问敏感数据
3.   降低数据库的复杂程度
4.   将多个物理数据库抽象为一个逻辑数据库