【数据库】多表查询，事务处理，数据库索引

数据库笔记 day03

2019/8/22 周四
学习内容：多表查询（子查询，内链接，外连接），事务处理，数据库索引
关键字：in; not in; any; all; inner join … on; as; left/right join … on; in; set autocommit; start transaction; rollback; commit; unique; index; key; alter; fulltext;

---

多表查询

	   -----子查询      
多表查询---|     	    -----内链接 
	   -----连接查询----|      	   -----左连接
   			    -----外连接----|
    					   -----右连接

1. 子查询

（1）单行单列

# 查询 刘德华买了什么商品
select c_name from commodity where c_id in (
	select o_cid from `order` where o_cuid=(
		select cu_id from customer where cu_name='刘德华'
	)
);

子查询中用 > < = 要求括号里的返回值为单行单列

（2）如果要查询单行多列（同时查询一条数据的多个字段，用得比较少）

select * from table_a where (sal,name) = (
	 select sal,name from table_b where name = 'TomCat' 
); 

这里中间行的返回值与括号里查询的列名的顺序必须一致

（3）如果要查询单列多行（查询多行数据的同一个字段），可以用in; not in; any; all关键字，如

select * from table_a where sal >= ANY (select sal from table_b;); 

sal需要大于table_b里的任意一个sal的值，即只要大于最小的那个就符合

select * from table_a where sal >= ALL (select sal from table_b;);

sal需要大于table_b里的所有sal的值，即必须大于最大的那个才符合

2. 内连接 inner join … on

select * from commodity inner join commoditytype on c_type=ct_id;

inner join 前后是要连接的两个表，on后面是连接条件，也就是外键约束中对应的两个列名。建议以小表连大表，如下行，效果是一样的，但在数据量较大时，可以提高查询效率。

select * from commoditytype inner join commodity on c_type=ct_id;

如果忘记加on的连接条件，commodity表中的每一条数据都会与commoditytype中的每一条数据连接一次，即如果commodity有60条数据，commoditytype有4条数据，一共就会连接出来60*4=240条，而加上c_type=ct_id条件后，就只会连接符合此约束的数据，得到刚好60条。

• 需要连接多个表时

# 查询 刘德华买了什么商品
select c_name from commodity as c inner join (
	select * from `order` inner join customer where  cu_name='刘德华'
) as t on c.c_id=t.o_cid;

因为join后需要跟的是表的名字，所以可以用 as 给中间 用order和customer两张表 连接起来的表来命名

• 用内连接来连接多个表的另外一种语法

上面这种方法当需要连接的表比较多的时候逻辑容易混，所以可以使用下面这种较简单的连接方法，注意，只适用于内连接

# 查询 刘德华买了什么商品
select c_name
from commodity as c, `order` as o, customer as cu
where c.c_id=o.o_cid and o.o_cuid=cu.cu_id
and cu.cu_name='刘德华';

from后面写要连接的所有的表，用","逗号隔开
where为连接条件，用and连接
如果连接的有3张表，则需要有2个连接条件

3. 外连接 left/right join … on

左连接

select * from commodity left join commoditytype on c_type=ct_id;

右连接

select * from commodity right join commoditytype on c_type=ct_id;

左连接和右连接在用法上是一样的，查询逻辑上互为相反。
它们的区别是，以执行语句中的哪个表为主表。以主表为准，主表中有的数据才显示，主表中没有的数据即使附表中有也不显示在结果中。

---

事务处理

• 在银行的转账过程中，转账方余额扣除和收款方余额增加两条语句，可能发生执行了前一条，而后一条由于服务器原因没有执行成功的情况；或者是转账方同时向两个收款方转账，第一条语句同时执行两次，第二条语句也能分别执行成功的情况。为了避免意外而造成不必要的损失，银行使用事务处理的方式进行处理
• 事务就是将一组SQL语句放在同一批次内去执行。如果一个SQL语句出错，则该批次内的所有SQL都将被取消执行。相当于可以实现撤销。
  （MySQL事务处理只支持InnoDB和BDB数据表类型）
• 事务的ACID原则：原子性（Atomic），一致性（Consist），隔离性（Isolated），持久性（Durable）
• 注意：只对 对表内数据的操作 有效，即insert into; delete; update等有效，对creat、drop等操作无效。
• 处理步骤：
  

1. SET AUTOCOMMIT 改变自动提交模式

set autocommit =0;

SET AUTOCOMMIT = 0; # 0为关闭自动提交模式
SET AUTOCOMMIT = 1; # 1为开启自动提交模式

在MySQL中默认是自动提交
使用事务时应先关闭自动提交，才能实现撤销的效果。在事务完成后，一定要记得重新开启自动提交，否则代码没有提交，相当于白写。

2. START TRANSACTION 开始一个事务,标记事务的起始点

start transaction;

3. 对数据进行操作

delete from `order`; 
delete from customer;

删除了两张表中的数据，此时

select * from `order`;
select * from customer;

结果都为Empty set

按理说mysql中的操作是不可逆的，但在事务中，可以有将事务滚回的方法

4. ROLLBACK 将事务回滚，数据回到本次事务的初始状态

rollback;

滚回后，再执行上面的两条查询语句（省略了结果中具体的表格数据）

select * from `order`;
3 rows in set (0.00 sec)
select * from customer;
5 rows in set (0.00 sec)

删除的表数据又还原了，相当于回到了事务刚开始的时候

5. COMMIT 提交一个事务给数据库

如果在事务中就已经使用commit关键字，将事务提交给了数据库，那么再用rollback是没法返回到之前的，只有commit之后再写的没有被提交的操作，可以滚回，且是滚回到上一个commit的位置

# 前面已经改变了自动提交模式，并标记了事务的起始点
select * from `order`;
6 rows in set (0.01 sec)

delete from `order` where o_id=6; # 删除一条数据
select * from `order`;
5 rows in set (0.00 sec)

commit; # 提交事务

rollback; # 使用滚回

select * from `order`;
5 rows in set (0.00 sec)  # 数据并没有恢复到最初的6条

delete from `order` where o_id=5; # 再删除一条数据
select * from `order`;
4 rows in set (0.00 sec)  

rollback; # 不提交事务，直接使用滚回
select * from `order`;
5 rows in set (0.00 sec)  # 数据恢复到5条了

6. 还原MySQL数据库的自动提交

set autocommit =1;

再说一次，在事务完成后，一定要记得重新开启自动提交，否则代码没有提交，相当于白写。

---

数据库索引

• 作用：提高查询速度
• 索引不是越多越好，小数据量的表建议不要加索引
• 分类 （创建索引）：

1. 主键索引（PRIMARY KEY）
   最常见的索引类型

CREATE TABLE `表名` (
	`GradeID` INT(11) AUTO_INCREMENT PRIMARY KEY, 
	# 或 PRIMARY KEY(`GradeID`)
)

2. 唯一索引（UNIQUE）
   主键索引只能有一个，而唯一索引可有多个

CREATE TABLE `Grade` (
	`GradeID` INT(11) AUTO_INCREMENT PRIMARY KEY,
	`GradeName` VARCHAR(32) NOT NULL UNIQUE 
	# 或 UNIQUE KEY `GradeID` (`GradeID`)
)

3. 常规索引（INDEX）
   index和key关键字都可设置常规索引，应加在查找条件的字段
   （不宜添加太多常规索引，影响数据的插入、删除和修改操作）

# 创建表时添加
CREATE TABLE `result` ( 
	INDEX/KEY `ind` (`studentNo`,`subjectNo`)
)
# 创建后追加
ALTER TABLE `result` ADD INDEX `ind` (`studentNo`, `subjectNo`);

ALTER关键字可以在表创建后，对表的字段信息进行更改，但一般不建议使用

4. 全文索引（FULLTEXT）
   只能用于MyISAM类型的数据表，只能用于 CHAR 、 VARCHAR、TEXT数据列类型，适合大型数据集
   现在一般已经不用了

# 创建表时添加
CREATE TABLE `student` ( 
	FULLTEXT (`StudentName`)
)ENGINE=MYISAM;
# 创建后追加
ALTER TABLE employeeADD FULLTEXT (`first_name`);

• 删除索引
  DROP INDEX 索引名 ON 表名
  ALTER TABLE 表名 DROP INDEX 索引名
  ALTER TABLE 表名 DROP PRIMARY KEY
• 查看索引
  SHOW INDEX/KEYS FROM 表名
• 注意：不要对经常变动的数据加索引；索引一般应加在查找条件的字段