多表连接查询
内连接
INNER JOIN关键字
select field1,field2… from table_name INNER JOIN join_table ON join_condition;
ON后面跟着的是连接条件
外连接
LEFT | RIGHT JOIN关键字
外连接分为 左连接 和 右连接
左连接 LEFT JOIN
select field1,field2…from table_name LEFT JOIN join_table ON join_condition;
右连接 RIGHT JOIN
select field1,field2…from table_name RIGHT JOIN join_table ON join_condition;
#内连接
select * from commoditytype inner join commodity on ct_id = c_type;
#将commoditytype表与commodity表连接,ct_id是c_type的外键约束,也就是这里的连接条件
#外连接的左连接
select * from commoditytype left join commodity on ct_id = c_type;
#左连接时,将左边的表作为主表,去一 一匹配右边的表
select * from commoditytype right join commodity on ct_id = c_type;
#右连接时,将右边的表作为主表,去匹配左边的表
注意:使用外连接时,应使用小表匹配大表,这样查询效率更高
内外、左右连接查询的区别
两表的交集为内连接,交集部分加上黄色区域为左连接,交集部分加上淡红色区域为右连接。
子查询
将一张表的查询结果作为另一张表的查询条件
select * from tableA_name where field = (select field from tableB_name where condition);
将tableB的查询结果当做tableA的条件
当tableB查询的结果不止一个值时,可以使用IN关键字来筛选条件
select * from tableA_name where field IN (select field from tableB_name where condition);
多层子查询的嵌套查询
select * from tableA where field IN (select field from tableB where IN (select field from tableC where condition));
select * from commodity where c_id in (
select o_cid from `order` where o_cuid = (
select cu_id from customer where cu_name = '刘德华'
)
);
#其中 commodity 和 customer `order`为表名称
#该语句 筛选出了刘德华所买过的东西 结果如下所示
当然也可以使用连接查询
select * from `order` inner join customer on o_cuid = cu_id inner join commodity on o_cid = c_id where cu_name = '刘德华'; #当使用到多表内连接的时候,可以简写为如下 select * from `order`,commodity,customer #此处写所有要内连接的表 不同的表用逗号隔开 where o_cid= c_id and o_cuid = cu_id and cu_name = '刘德华';
注意;上述的简写只能使用内连接,外连接不可用
事务处理
将一系列的SQL语句放在同一个批次去执行,如果有一个SQL语句出错,那么整个事务都取消执行
ACID原则
原子性(Atomic) 事务不可被分割
一致性(Consist) 事务不管执行多少次,所得结果都是一致的
隔离性(Isolated) 类似于java的锁,在多个客户访问同一张表时,数据库为每一个用户开启的事务,不能被其他事务的操作所干扰,多个并发事务之间要相互隔离
持久性(Durable)事务一旦提交,那么对数据库的改变是永久的
事务实现方法
这边提一下SQL的执行顺序
SQL语句 —>检查语法,并排出顺序 ---->自动提交语句给数据库------>数据库中实现语句
set autocommit来设置SQL语句的自动提交
set autocommit = 0; 关闭自动提交
set autocommit = 1; 开启自动提交
start transaction; 表示事务开始的起始点
commit; 手动提交事务
rollback; 回滚,回到事务开始执行的初始状态
set autocommit = 1; 还原MySQL的自动提交
举个例子set autocommit = 0; start transaction; #接下来你可以进行一系列的表操作,注意是表操作,无论是删除,修改,插入, #假如你做了你不想的操作,那么可以使用rollback rollback; # 回滚到 start transaction时的数据库状态 #假如你完成了你的事务,那么可以手动提交事务 commit; #提交一个事务 set autocommit = 1; #事务结束之后,记得还原MySQL的自动提交
数据库索引
作用:提高查询速度唯一
注意:并不是越多越好
分类
主键索引 (PRIMARY KEY)
唯一索引(UNIQUE)
常规索引(INDEX)
全文索引(FULLTEXT) //被弃用
建表后追加索引:
ALERT TABLE_NAME ADD 索引类型 (数据列名)
删除索引:
DROP INDEX 索引名 ON 表名
ALTER TABLE 表名 DROP INDEX 索引名
ALTER TABLE 表名 DROP PRIMARY KEY
查看索引:
SHOW INDEX(或KEYS) FROM 表名
索引准则:
索引不是越多越好
小数据量的表建议不要加索引
索引一般加在经常用来查找的字段
不要对经常变动的数据加索引