1.1高级查询

1.1.1 EXISTS子查询

在实际开发中，我们常常会遇到表是否存在，及创建表等等的过程，Mysql中如何对已存在的表进行提示并且进行创建呢？可以

使用exists子句对表创建进行判断生成。

语法：

DROP TABLE IF EXISTS temp;
CREATE TABLE temp (
    … … #省略建表语句
) ;

   

注意 ： IF　EXISTS子查询语句在上面的作用为判断表是否存在，如果表temp存在，那么就进行创建temp表，同样和我们的判断相似，存在返回true，不存在当前表返回false,那么除了判断表以外，还可以做什么呢？它还可以进行以下判断：

语法：

 

SELECT …… FROM 表名 WHERE EXISTS(子查询);

   

当我们在查询时加上EXISTS子句的时候，作用是用来判断EXISTS后面的子查询中是否有返回结果的数据，如果有则返回true，执行外围查询，如果没则返回false，不执行外围查询。这也是他的一个常用方法。当然了，还有他的“对头” NOT EXISTS 子句：

语法：

SELECT …… FROM 表名 WHERE  NOT EXISTS(子查询);

NOT EXISTS子句和EXISTS子句含义相反。

子查询注意事项：

在我们使用子查询时，需要注意以下几点：

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

2 ) 嵌套在父查询SELECT语句的子查询可包括

SELECT子句

FROM子句

WHERE子句

GROUP BY子句

HAVING子句

3 ) 只出现在子查询中而没有出现在父查询中的列不能包含在输出列中

1.1.2 GROUP BY子句

平时大家在课堂中，每个班级由若干个组组成，每个组对应的组员及组名也会不相同，那么在mysql中是否可以进行分组呢，答案是肯定的，但是mysql中的分组和现实生活中的分组是有差异的，接下来我们来看一下mysql中的分组。

语法：

SELECT …… FROM  <表名>  
WHERE  ……
GROUP BY ……
例如：
SELECT `subjectNo`,AVG(`studentResult`) AS 课程平均成绩
FROM `result`
GROUP BY `subjectNo`;

 

上述的例子表示着查询每个学号以及对应着当前学生的平均成绩，这条SQL语句是根据SQL语句进行分组的，所以结果为：

subjectNo	课程平均成绩
1	74.6
2	67
3	89

但是在写分组语句时，要注意以下几点：

SELECT列表中只能包含：

1.被分组的列

2.为每个分组返回一个值的表达式，如聚合函数

由于分组只能返回一个值，所以大家在实际应用中需要进行注意书写方式。

另外，也可以指定多列进行分组统计:

SELECT `gradeId` AS 年级编号,`sex` AS 性别,COUNT(*) AS 人数
 FROM `student`
GROUP BY `gradeId`,`sex`
ORDER BY `gradeId`;

   

结果为：

年级	性别	人数
1	女	4
1	男	4
2	女	1
2	男	1
3	女	2

以上结果涉及了年级表,性别表进行分类处理,进行统计多个列的人数情况.大家会发现我们在获取结果的时候,都是以两个表进行数据获取分配,进行分类.相当于统计了两个表中的数据信息.

1.1.3 HAVING子句

HAVING 子句在SQL语言中的作用是为分组或集合指定搜索条件,通常与GROUNP BY 子句一起使用，如果不使用GROUNP BY 子句,HAVING的行为与WHERE 子句一样.

语法:

SELECT …… FROM  <表名>
WHERE ……
GROUP BY ……
HAVING……
例如:
SELECT `subjectNo`,AVG(`studentResult`) AS 课程平均成绩
FROM `result`
GROUP BY `subjectNo`
HAVING AVG(`studentResult`) >=60;   //获得课程平均分及格的课程编号

 

以上用法中HAVING用于对查询后的结果进行筛选处理.

注意事项 ： 1、where 后不能跟聚合函数，因为where执行顺序大于聚合函数。

                    2、where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函 数，使用where条件显示特定的行。

                    3、having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也 可以使用多个分组标准进行分组。

1.1.4 WHERE与HAVING对比

使用HAVING 和 其他子句时,需要注意各自的作用.

1) WHERE子句

数据库中常用的是where关键字，用于在初始表中筛选查询。它是一个约束声明，用于约束数据，在返回结果集之前起作用，一般用于指定查询的条件

2) GROUP BY子句

对select查询出来的结果集按照某个字段或者表达式进行分组，获得一组组的集合，然后从每组中取出一个指定字段或者表达式的值。

在说group by的时候，我们还需要了解聚合函数，聚合函数是SQL语言中一种特殊的函数。这些函数和其它函数的根本区别就是它们一般作用在多条记录上。

我们需要注意的是：在使用group by的SQL语句中，select中返回的字段，必须满足以下两个条件之一：

1. 包含在group by语句的后面，作为分组的依据；

1. 这些字段包含在聚合函数中。

3) HAVING子句

用来从分组的结果中筛选行

以及他们的优先顺序为: WHERE子句 > GROUP BY子句 > HAVING子句

1.2 常用的多表联查

1.2.1 什么是联查?一般用于哪些地方?

有时一个查询结果需要从两个或两个以上表中提取字段数据，此时需要使用的就是多表关联查询。

链接查询主要分为三种：内链接、外链接、交叉连接。

1) 内链接

使用比较运算符（包括=、>、<、<>、>=、<=、!> 和!<）进行表间的比较操作，查询与连接条件相匹配的数据。根据所使用的比较方式不同，内连接分为等值连接、自然连接和自连接三种。

关键字：INNER JOIN 1.等值连接/相等链接： 使用”=“关系将表连接起来的查询，其查询结果中列出被链接表中的所有列，包括其中的重复列

2.自然链接 等值连接中去掉重复的列，形成的链接。 

3.自链接 如果在一个连接查询中，涉及到的两个表是同一个表，这种查询称为自连接查询。

2) 外链接

内连接只返回满足连接条件的数据行，外连接不只列出与连接条件相匹配的行，而是列出左表（左外连接时）、右表（右外连接时）或两个表（全外连接时）中所有符合搜索条件的数据行。外连接分为左外连接、右外链接、全外连接三种。

1.左外连接 关键字：LEFT[OUTER]JOIN 返回左表中的所有行，如果左表中行在右表中没有匹配行，则在相关联的结果集中右表的所有字段均为NULL。

2.右外连接 关键字：RIGHT[OUTER]JOIN 返回右表中的所有行，如果右表中行在左表中没有匹配行，则在左表中相关字段返回NULL值。

3) 交叉连接/笛卡尔积

两个表做笛卡尔积，得到的结果集的行数是两个表中的行数的乘积。

1.2.2 内连接

语法:

inner join 或者join（等同于inner join）

select a.*, b.* from tablea a  
inner join tableb b  
on a.id = b.id 

   

或

select a.*, b.* from tablea a  
join tableb b  
on a.id = b.id 

   

这种情况下得到的是满足某一条件的A，B内部的数据；正因为得到的是内部共有数据，所以连接方式称为内连接。

当然了,不仅仅可以进行两表联查,也可以进行两表以上联查:

SELECT S.studentName AS 姓名,SU.subjectName AS 课程,R.studentResult AS 成绩
FROM student AS S 
INNER JOIN `result` AS R ON  (S.`studentNo` = R.`studentNo`)
INNER JOIN `subject` AS SU ON (SU.subjectNo=R.subjectNo);

   

以上用法可以获取字段相关联匹配数据的结果.

1.2.3 外连接

1) 左外链接

左边表数据行全部保留，右边表保留符合连接条件的行。

语法:

left join 或者 left outer join(等同于left join)

select a.*, b.* from tablea a  
left join tableb b  
on a.id = b.id  
或者
select a.*, b.* from tablea a  
left outer join tableb b  
on a.id = b.id 

这种情况下得到的是A的所有数据，和满足某一条件的B的数据;

2) 右外连接

右边表数据行全部保留，左边表保留符合连接条件的行。

语法:

right join 或者fight outer join(等同于right join)

select a.id aid,a.age,b.id bid,b.name from tablea a  
right join tableb b  
on a.id = b.id  
或
select a.id aid,a.age,b.id bid,b.name from tablea a  
right outer join tableb b  
on a.id = b.id  

这种情况得到的是B的所有数据，和满足某一条件的A的数据；

3) 交叉连接/笛卡尔积

笛卡尔（Descartes）乘积又叫直积。假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。可以扩展到多个集合的情况。类似的例子有，如果A表示某学校学生的集合，B表示该学校所有科目的集合，则A与B的笛卡尔积表示所有可能的选课情况.,将所有数据进行组合起来.

需要用到cross join：

语法:

select a.id aid,a.age,b.id bid,b.name from tablea a  
cross join tableb b  

   

这种情况下实际上实现了内连接的效果,但是唯一的缺点是效率慢,需要先进行组合再进行筛选.查询的效率较低.但是大家要注意的是:

多表查询遵循的算法就是以上提到的笛卡尔积，表与表之间的连接可以看成是在做乘法运算。在实际应用中，应避免使用笛卡尔积，因为笛卡尔积中容易存在大量的不合理数据，简单来说就是容易导致查询结果重复、混乱。

2.1 事务

2.1.1 什么是事务?

事务（Transaction）是一个操作序列，该序列中的多个操作要么都做，要么都不做 是MySQL5.5之后的存储引擎所支持

现在我们来举一个例子: 银行ATM取钱，扣款成功后突然大停电，吐钱的操作还没做，这时候ATM机就要恢复到没取钱时候的状态，否则钱扣了还没拿到手，那么这样就会造成数据的不一致,从而导致了一些糟糕的情况发生 。

事务是应用程序中一系列严密的操作，所有操作必须成功完成，否则在每个操作中所做的所有更改都会被撤消。也就是事务具有原子性，一个事务中的一系列的操作要么全部成功，要么一个都不做。 事务的结束有两种，当事务中的所有步骤全部成功执行时，事务提交。如果其中一个步骤失败，将发生回滚操作，撤消之前到事务开始时的所有操作。

2.1.2 事务的特点( 简称 ACID)

1) 原子性（atomicity）

原子是自然界中最小的颗粒，具有不可再分的特点，事务中的所有操作可以看作是一个原子(事务是数据库的逻辑工作单位)，要么全部执行，要么全不执行.

2) 一致性（consistency）

事务执行的结果必须要保证数据库中数据的一致性事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统 运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是 不一致的状态 。

3) 隔离性（isolation）

隔离性指各个事务的操作是互不干扰的，即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。

4) 持久性（durability）

持久性也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响.

6.1.3 事务的控制

那么，我们如何在程序中进行体现事务的作用呢？接下来，我们来看一个例子：

现在张三有1000元,李四有1000元,张三通过转账的方式向李四转了500元.我们可以使用以下SQL语句进行描述:

--创建数据库
create database bank;
--创建表account
create table account(
    id int(8) primary key auto_increment,
    username varchar(30),
    money double
);
--插入数据
insert into account(username,money) values("张三",1000),("李四",1000)
--查询数据
select * from account; 
--进行转账
update account set money=money-500 where username="张三";
update account set money=money+500 where username="李四";

 

按照如上的执行结果后张三的金额为: 500 元 ,李四为:1500元

但是在现实生活中什么都有可能发生,比如断电,系统瘫痪等等的一些不可控因素,会导致数据的不一致,那么我们可以向语句中进行添加事务控制,我们先来了解一下语法:

1) 开启事务

语法:

begin transaction;   //开启一个新的事务
或
start transaction;

 

作用： 标记着一个事务的开始，在其后面的代码都将会受到事务的控制。该事务不会自动提交，必须手动提交。

2 ) 提交事务

语法:

commit; //提交事务

   

作用： 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；

3 ) 回滚事务

语法:

rollback; //回滚事务

 

作用：也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

2.1.4 事务的使用

接下来我们在转账的过程中添加事务处理：

//......省略代码
--进行转账
begin transaction;//事务开始后,必须进行手动提交  
update account set money=money-500 where username="张三";
update account set money=money+500 where username="李四";
commit;//进行事务提交

使用了COMMIT就会将语句块化为了一个整体,执行完sql语句后,才能执行提交功能.

//......省略代码
--进行转账
begin transaction;//事务开始后,必须进行手动提交  
update account set money=money-500 where username="张三";
update account set money=money+500 where username="李四";
rollback;//进行事务回滚

 

如果使用了ROLLBACK,那么当前事务会将已经执行过的未提交的语句进行退回至未执行的状态,一般回滚会在程序中出现错误或者异常时进行使用.

平时大家在执行sql语句的时候也会发现,如果不进行开启事务或者提交事务,我们也可以进行正常的数据库操作,这是因为我们数据库默认的状态是开启了自动提交功能,如果大家需要进行设置提交方式,可以使用以下代码进行修改提交方式:

语法:

SET AUTOCOMMIT=0; 禁止自动提交
或
SET AUTOCOMMIT=1; 开启自动提交

   

但是大家要注意的是,上述SQL修改会话系统变量或全局系统变量，只对当前实例有效，如果MySQL服务重启的话，这些设置就会丢失，如果要永久生效，就必须在配置文件中修改系统变量。可以将MySQL配置文件中的my.ini文件中进行修改：

[mysqld]
autocommit=0