《高级查询二》

《高级查询二》
一，高级查询
如何用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语句的子查询可包括

SELECT子句
FROM子句
WHERE子句:用来筛选FROM子句中指定的操作所产生的行
GROUP BY子句 :用来分组 WHERE子句的输出
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` = '李四' ;

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

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

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

视图的用途

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