MySQL 连接方式和子查询

MySQL 连接方式和子查询

标签（空格分隔）： MySQLday03 连接方式 子查询

---

多表连接查询

数据表的连接查询比较常用的有内连接和外连接查询两种

内连接

SELECT field1,field2,...fieldn 
from table_name 
INNER JOIN join_table 
ON join_condition;

• 方法一

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

• 方法二

select * from commodity, commoditytype wherer c_type=ct_id;

通过INNER JOIN … ON
关键字on后面其实跟的就是外键约束中相对应的两个列
当不添加约束条件时两张表会进行笛卡尔积运算

---

外连接

外连接分2种，左连接（left join）和右连接（right join）
两种的方法类似以左连接为例

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

表名代表着连接顺序而left right表示着主表对象，父表为标准，当没有对应的值用null代替。
如下 commoditytype left join commodity的连接方式

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

多表连接查询

用一张表的数据作为条件去查另一张表，我们需要用到子查询

• 单行单列

select c_name 
from commodity 
where c_type=(select ct_id from commoditytype where ct_name='玩具');

• 单行多列

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

这里的返回值和查询的列名顺便必须一致

• 单列多行

select c_name 
from commodity 
where c_type in 
(select ct_id from commoditytype where ct_name='玩具' or ct_name='文具');

---

事务的ACID原则

ACID是Atomic（原子性）
Consistency（一致性）
Isolation（隔离性）
Durability（持久性）

• SET AUTOCOMMIT
  SET AUTOCOMMIT = 0; # 关闭自动提交模式
  SET AUTOCOMMIT = 1; # 开启自动提交模式
• MySQL中默认是自动提交
  使用事务时应先关闭自动提交
• START TRANSACTION
  开始一个事务,标记事务的起始点
• COMMIT
  提交一个事务给数据库
• ROLLBACK
  将事务回滚，数据回到本次事务的初始状态
• SET AUTOCOMMIT = 1;
  还原MySQL数据库的自动提
  

---

索引

索引并不是越多越好索引也是占用硬盘空间 用空间换时间

作用：

• 提高查询速度
• 确保数据的唯一性
• 可以加速表和表之间的连接，实现表与表之间的参照 完整性
• 使用分组和排序子句进行数据检索时，可以显著减少 分组和排序的时间
• 全文检索字段进行搜索优

分类

• 主键索引（PRIMARY KEY）
  某一个属性组能唯一标识一条记录
• 唯一索引（UNIQUE）
  避免同一个表中某数据列中的值重复
• 常规索引（INDEX）
  快速定位特定数据
• 全文索引（FULLTEXT）
  快速定位特定数据
  已经被数据参控淘汰现在数据库中添加的是路径

子查询实例

//买过刘德华商品某个的人
select distinct o_cuid from `order` 
where o_cid 
in (
select o_cid
from `order` 
where o_cuid=(
select cu_id 
from customer  
where cu_name='刘德华' ));

//买的和刘德华全部的人
select o_cuid
from `order`
	where o_cid in(
		select o_cid
		from `order`
		where o_cuid=(
			select cu_id
			from customer
			where cu_name='刘德华'
		)
	)
group by o_cuid
having count(*) =(
		select count(*)
		from `order`
		where o_cuid=(
			select cu_id
			from customer
			where cu_name='刘德华'
		));