数据库笔记 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;
再说一次,在事务完成后,一定要记得重新开启自动提交,否则代码没有提交,相当于白写。
数据库索引
- 作用:提高查询速度
- 索引不是越多越好,小数据量的表建议不要加索引
- 分类 (创建索引):
- 主键索引(PRIMARY KEY)
最常见的索引类型
CREATE TABLE `表名` (
`GradeID` INT(11) AUTO_INCREMENT PRIMARY KEY,
# 或 PRIMARY KEY(`GradeID`)
)
- 唯一索引(UNIQUE)
主键索引只能有一个,而唯一索引可有多个
CREATE TABLE `Grade` (
`GradeID` INT(11) AUTO_INCREMENT PRIMARY KEY,
`GradeName` VARCHAR(32) NOT NULL UNIQUE
# 或 UNIQUE KEY `GradeID` (`GradeID`)
)
- 常规索引(INDEX)
index和key关键字都可设置常规索引,应加在查找条件的字段
(不宜添加太多常规索引,影响数据的插入、删除和修改操作)
# 创建表时添加
CREATE TABLE `result` (
INDEX/KEY `ind` (`studentNo`,`subjectNo`)
)
# 创建后追加
ALTER TABLE `result` ADD INDEX `ind` (`studentNo`, `subjectNo`);
ALTER关键字可以在表创建后,对表的字段信息进行更改,但一般不建议使用
- 全文索引(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 表名 - 注意:不要对经常变动的数据加索引;索引一般应加在查找条件的字段