MySQL高级

子查询

订单存储在两个表中。对于包含订单号、客户ID、订单⽇期的每个订单，orders表存储⼀⾏。 各订单的 物品存储在相关的orderitems表中。orders表不存储客户信息。它只存储客户的ID。 实际的客户信息存储在customers表中。

现在，假如需要列出订购物品TNT2的所有客户，应该怎样检索？

--(1) 检索包含物品TNT2的所有订单的编号。
SELECT orderitems.order_num FROM orderitems WHERE orderitems.prod_id = 'TNT2';

+-----------+
| order_num |
+-----------+
|     20005 |
|     20007 |
+-----------+

--(2) 检索具有前⼀步骤列出的订单编号的所有客户的ID
SELECT orders.cust_id FROM orders WHERE orders.order_num in (20005,20007);

+---------+
| cust_id |
+---------+
|   10001 |
|   10004 |
+---------+

--(3) 检索前⼀步骤返回的所有客户ID的客户信息。
SELECT customers.cust_name FROM customers WHERE customers.cust_id in (10001,10004);

+----------------+
| cust_name      |
+----------------+
| Coyote Inc.    |
| Yosemite Place |
+----------------+

可以把其中的WHERE⼦句转换为⼦查询⽽不是硬编码这些SQL 返回的数据：

 

SELECT customers.cust_name 
FROM customers 
WHERE customers.cust_id in (
	SELECT orders.cust_id 
	FROM orders 
	WHERE orders.order_num 
	in (
		SELECT orderitems.order_num 
		FROM orderitems 
		WHERE orderitems.prod_id = 'TNT2'
	)
);

 

什么是子查询(嵌套查询)

就是在一个sql当中，它的where条件来源于另外一个sql

或者反过来理解，一个sql语句的结果，作为外层sql语句的条件。
 

作为计算字段使用子查询

查询customers表中，每个用户的订单

-- (1) 从customers表中检索客户列表。

SELECT cust_id,cust_name FROM customers;

+---------+----------------+
| cust_id | cust_name      |
+---------+----------------+
|   10001 | Coyote Inc.    |
|   10002 | Mouse House    |
|   10003 | Wascals        |
|   10004 | Yosemite Place |
|   10005 | E Fudd         |
+---------+----------------+

-- (2) 对于检索出的每个客户，统计其在orders表中的订单数⽬。这里先统计一个人的

select count(*) as orders FROM orders WHERE cust_id = 10001;

+--------+
| orders |
+--------+
|      2 |
+--------+

--  为了对每个客户执⾏COUNT()计算，应该将COUNT()作为⼀个⼦查询。
-- 把查询的数量放到一个(里面)

SELECT cust_name,
	(SELECT COUNT(*) FROM ordersWHERE customers.cust_id = orders.cust_id)as orders
FROM customers;

+----------------+--------+
| cust_name      | orders |
+----------------+--------+
| Coyote Inc.    |      2 |
| Mouse House    |      0 |
| Wascals        |      1 |
| Yosemite Place |      1 |
| E Fudd         |      1 |
+----------------+--------+

表关系

相同数据出现多次决不是⼀件好事，此因素是关系数据库设计的基础。 关系表的设计就是要保证把信息分解成多个表，⼀类数据⼀个表。 各表通过某些常⽤的值（即关系设计中的关系（relational））互相关联。

 

外键:

在一个表中，定义一个字段，这个字段中存储的数据是另外一张表中的主键就是在一个表中的字段，代表着这个数据属于谁。
 

一对一关系：

就是在一个表中的数据，对应着另外一张表中的一个数据，只能有一个。

员工表:

id，姓名、性别、年龄、籍贯、联系方式、学历、工龄.

由上面的一个表，拆分成两个表

 

员工表: id,  姓名，联系方式，工龄

详情表：yid，性别，籍贯，学历

yid就是外键

上面的表关系就是一对一的表关系，通过详情表中的yid这个字段来标记员工表中的主键。一个员工有着一个对应的详情信息，存储在详情表中，
在详情表中的数据，也只属于某一个员工。

一对多关系：（最常见）

在一个表中的一条数据对应着另外一个表中的多条数据

在一个表中的多条数据，对应着外一张表中一个数据

新闻分类表：
     id  分类名
     1    体育
     2    电影

新闻表：
     idtitle               分类id
     国足加油            1
     漫威                    2
     篮球                   1

多对多：

例如：

一个班级有多个老师来讲课(化学、物理、数学、、、)

一个老师要带多个班级(一班，二班，三班)
 

表联结

如果数据存储在多个表中，怎样用单条SELECT语句检索出数据?

联结是一种机制，用来在一条SELECT语句中关联表。

 

例如：

供应商的表

商品表

查询出所有商品以及对应的供应商信息

SELECT vend_name,prod_name,prod_price
from vendors,products
WHERE vendors.vend_id = products.vend_id;

这两个表用WHERE子句联结，WHERE子句匹配vendors表中的vend_id和products表中的vend_id
 

在联结两个表时，你实际上做的是将第一个表中的每一行与第二个表中的每一行配对。
WHERE子句作为过滤条件，它只包含那些匹配给定条件(这里是联结条件）的行。

如果没有WHERE语句，则会匹配出两个表的长度相乘的行数


除了使用where进行表的联结查询外，还可以使用另外一种联结方式，join

SELECT vend_name,prod_name,prod_price
from vendors
JOIN products ON vendors.vend_id = products.vend_id;

联结多个表

订单项表

查询出订单号为20005的订单中购买的商品及对应的产品供应商信息

SELECT prod_name,vend_name,prod_price,quantity
FROM products,vendors,orderitems
WHERE products.prod_id = orderitems.prod_id
	AND products.vend_id = vendors.vend_id
	AND orderitems.order_num = 20005;

改为join

SELECT prod_name,vend_name,prod_price,quantity
FROM products
JOIN orderitems ON products.prod_id = orderitems.prod_id
JOIN vendors ON products.vend_id = vendors.vend_id
WHERE order_num = 20005;

SQL 规范⾸选 JOIN 语法。

 

 

 

自联结

 

假如你发现某物品（其ID为DTNTR)存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。

此查询要求首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。
 

方式一：使用子查询的方式(效率偏低，尽量少用)

SELECT prod_id,prod_name
FROM products
WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 'DTNTR');

方式二：自联结（自己联自己）

SELECT p1.prod_id,p2.prod_name
FROM products as p1
JOIN products as p2
on p1.vend_id = p2.vend_id
WHERE p2.prod_id = 'DTNTR';

SELECT p1.prod_id,p2.prod_name
FROM products as p1, products as p2
WHERE p1.vend_id = p2.vend_id
AND p2.prod_id = 'DTNTR';

 

 

外部联结

对每个客户下了多少订单进行计数，包括那些至今尚未下订单的客户;

内部联结（之前的联结方式）

select customers.cust_id,orders.order_num
from customers
join orders
on customers.cust_id = orders.cust_id;

无法找到尚未下单的用户

那什么是外部联结呢?（以某个表为基准做查询）

left join:是以 left join 左侧表为基准，去关联右侧的表进行联结，如果有未关联的数据，那么结果为null

right join :是以 right join右侧表为基准，去关联左侧的表进行联结，如果有未关联的数据，那么结果为null
 

select customers.cust_id,orders.order_num
from customers LEFT JOIN orders
on customers.cust_id = orders.cust_id;

等价

select customers.cust_id,orders.order_num
from orders RIGHT JOIN customers
on customers.cust_id = orders.cust_id;

例题：

检索所有客户及每个客户所下的订单数

select customers.cust_id,COUNT(orders.order_num) as nums
from customers LEFT JOIN orders
on customers.cust_id = orders.cust_id
GROUP BY customers.cust_id

组合查询UNION
 

MySQL也允许执行多个查询(多条SELECT语句并列)，并将结果作为单个查询结果集返回。

这些组合查询通常称为并(union)或复合查询(compound query) 。

规则：

UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔(因此,如果组合4条SELECT语句，将要使用3个UNION关键字)。

UNION中的每个查询必须包含相同的列、表达式或聚集函数(不过各个列不需要以相同的次序列出)

列数据类型必须兼容:类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型)。

假如需要价格⼩于等于5的所有物品的⼀个列表，⽽且还想包括供应商1001和1002⽣产的所有物品

SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price <= 5
UNION
SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id in(1001,1002);

不是单纯的将两条select语句的结果加起来，相当于查询的时候使用了一个or

如果不想去重 可以使用UNION ALL

UNION

UNION ALL

对结果集进行排序

SELECT语句的输出用ORDER BY子句排序。在用UNION组合查询时，只能使用一条ORDERBY子句，它必须出现在最后一条SELECT语句之后。

对于结果集，不存在用一种方式排序一部分，而又用另一种方式排序另一部分的情况，因此不允许使用多条ORDER BY子句。

虽然ORDER BY子句似乎只是最后一条SELECT语句的组成部分，但实际上MysQL将用它来排序所有SELECT语句返回的所有结果。

 

 

 

MySQL事务

事务(Transaction)是由一系列对系统中数据进行访问与更新的操作所组成的一个程序执行逻辑单元。

一、事务的语法

1. start transaction;/ begin;

2. commit;使得当前的修改确认

3. rollback;使得当前的修改被放弃

 

二、事务的ACID特性

1、原子性

事务的原子性是指事务必须是一个原子的操作序列单元。事务中包含的各项操作在一次执行过程中，只允许出现两种状态之一。

要么全部执行成功，要么全部执行失败。

事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。

也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。

 

2、⼀致性

 

事务的一致性是指事务的执行不能破坏数据库数据的完整性和一致性，一个事务在执行之前和执行之后，数据库都必须处以一致性状态。

比如∶如果从A账户转账到B账户，不可能因为A账户扣了钱，而B账户没有加钱。

3、隔离性

事务的隔离性是指在并发环境中，并发的事务是互相隔离的。也就是说，不同的事务并发操作相同的数据时，每个事务都有各自完整的数据空间。

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

4、持久性

事务的持久性是指事务一旦提交后，数据库中的数据必须被永久的保存下来。即使服务器系统崩溃或服务器宕机等故障。只要数据库重新启动，那么一定能够将其恢复到事务成功结束后的状态。
 

三、事务的并发问题

脏读：读取到了没有提交的数据,事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。

不可重复读：一个事务范围内两个相同的查询却返回了不同数据。这是由于查询时系统中其他事务修改的提交而引起的。比如事务T1读取某一数据，事务T2读取并修改了该数据，T1为了对读取值进行检验而再次读取该数据，便得到了不同的结果。

幻读：幻读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，比如这种修改涉及到表中的“全部数据行”。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入“一行新数据”。那么，以后就会发生操作第一个事务的用户发现表中还存在没有修改的数据行，就好象发生了幻觉一样。+

 

四、事务隔离级别

4种事务隔离级别从上往下，级别越高，并发性越差，安全性就越来越高。一般数据默认级别是读以提交或可重复读。

查看当前会话的隔离级别：

设置当前会话中的事务隔离级别：

set session transaction isolation level read uncommitted;

1、读未提交

读未提交，该隔离级别允许脏读取，其隔离级别是最低的。换句话说，如果一个事务正在处理某一数据，并对其进行了更新，但同时尚未完成事务，因此还没有提交事务;而以此同时，允许另一个事务也能够访问该数据。

脏读示例：

余额应该为1500元才对。请看T5时间点，事务A此时查询的余额为0，这个数据就是脏数据，他是事务B造成的,很明显是事务没有进行隔离造成的。

2、读已提交

读已提交是不同的事务执行的时候只能获取到已经提交的数据。这样就不会出现上面的脏读的情况了。但是在同一个事务中执行同一个读取,结果不一致

示例：

事务A其实除了查询两次以外，其它什么事情都没做，结果钱就人从1000变成0了，这就是不可重复读的问题。应该在A提交后，才能看到更新后的结果

3、可重复读

可重复读就是保证在事务处理过程中，多次读取同一个数据时，该数据的值和事务开始时刻是一致的。因此该事务级别限制了不可重复读和脏读，但是有可能出现幻读的数据。

幻读：

幻读就是指同样的事务操作，在前后两个时间段内执行对同一个数据项的读取，可能出现不一致的结果。

4、顺序读

顺序读是最严格的事务隔离级别。它要求所有的事务排队顺序执行，即事务只能一个接一个地处理，不能并发。