初识数据库3

多表连接查询

1.内连接

关键字：INNER JOIN…ON
具体语法：
SELECT field1,field2,…fieldn from table_name
INNER JOIN join_table ON join_condition;
注意：on后面其实跟的就是外键约束中相对应的两个列名！
代码：

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

2.外连接

关键字：LEFT|RIGHT JOIN …ON
具体语法：
SELECT field1,field2,…fieldn from table_name
LEFT|RIGHT JOIN join_table ON join_condition;
图形表示：

• 左连接

mysql> #左外连接
mysql> select * from commodity left join commoditytype on ct_id=c_type;

• 右连接

mysql> 右外连接：
mysql> select * from commodity right join commoditytype on ct_id=c_type;

注意：输出结果以主表为准，主表有的数据就显示。主表没有即使副表有也不显示用null表示

3.子查询

释义：用一张表的数据作为条件去查另一张表.

• 单行单列

具体语法：
select * from table_a where sal > (
select sal from table_b where name = ‘TomCat’);
解释：先从b表中查询出tomcat的售价，那括号内的查询结果一定是单行单例的一个值了，那么再通过这个值去查询出售价大于这个值的所有商品；
注意：这里的返回值必须是单行单例的值！
代码：

mysql> select c_name from commodity where c_type=(select ct_id=1 from commoditytype where ct_name='玩具');
+-----------------------+
| c_name                |
+-----------------------+
| 变形金刚-擎天柱       |
| 变形金刚-霸天虎       |
| 变形金刚-威震天       |
| 魔仙玩偶1             |
| 超人玩偶              |
| 小霸王游戏机          |
| X-BOX游戏机          |
| 任天堂游戏机          |
| 乐高玩具-散装         |
| 乐高玩具-蝙蝠侠纪念版  |
| 夏日有人手办          |
| EVA模型玩具           |
| 平板电脑模型          |
| 手机模型玩具          |
| 手机模型玩具          |
| hellokitty手机链      |
| 水枪-小               |
| 水枪-大               |
| 水枪-小               |
+-----------------------+
19 rows in set (0.05 sec)

• 单行多列

具体语法：
select * from table_a where (sal,name) = (
select sal,name from table_b where name = ‘TomCat’);
解释 ：先从b表中查询出tomcat的售价,输出的结果有2个列属于单行多
列，分别是售价和名字，那么再通过这两个值去查询和这两个值
相等的所有商品；
**注意：这里的返回值和查询的列名顺便必须一致！

4.习题

1.刘德华买过的商品名称
子查询

mysql> 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='刘德华') );
+-------------+
| c_name      |
+-------------+
| 中华铅笔HB  |
| 礼盒丝带-红 |
+-------------+
2 rows in set (0.00 sec)

连接查询

select c_name
from customer as cu,`order`as o,commodity as c
where c.c_id=o.o_cid and cu.cu_id=o.o_cuid and cu.cu_name='刘德华';
+-------------+
| c_name      |
+-------------+
| 中华铅笔HB  |
| 礼盒丝带-红 |
+-------------+
2 rows in set (0.00 sec)

2.查询买过刘德华买过所有同样商品的客户

#买过和刘德华一样东西的人
select o_cuid from `order` where o_cuid not in(
#买过和刘德华不一样东西的人
 select o_id from commodity where c_id not in(
  select o_cid from `order` where o_cuid=(
   select cu_id from customer where cu_name='刘德华'
   )
 )
)
group by o_cuid
having count(o_cid)=(
select count(o_cid) from `order` where o_cuid=(
select cu_id from customer where cu_name='刘德华'
)group by o_cuid 
);
+--------+
| o_cuid |
+--------+
|      3 |
+--------+
1 row in set (0.00 sec)

MySQL的事务处理

1.事务的ACID原则

• 原子性
• 一致性
• 隔离性
• 持久性

2.事务的实现方法

• set autocommit=0;关闭自动提交模式
• start transaction;开始一个事务，标记事务的起始点
• commit;提交一个事务给数据库
• rollback;将事务回滚，数据回到本次事务的起始状态
• set autocommit=1; 还原MySQL数据库的自动提交

代码：

mysql> set autocommit=1;
Query OK, 0 rows affected (0.07 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from `order` where o_id=1;
Query OK, 1 row affected (0.00 sec)

mysql> delete from `order` where o_id=2;
Query OK, 1 row affected (0.00 sec)

mysql> select * from `order`;
+------+--------+-------+-------+
| o_id | o_cuid | o_cid | o_num |
+------+--------+-------+-------+
|    3 |      1 |    32 |   100 |
|    4 |      2 |    32 |   100 |
|    5 |      3 |    32 |   100 |
|    6 |      3 |    32 |   100 |
+------+--------+-------+-------+
4 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.07 sec)

mysql> select * from `order`;
+------+--------+-------+-------+
| o_id | o_cuid | o_cid | o_num |
+------+--------+-------+-------+
|    1 |      1 |    32 |   100 |
|    2 |      1 |    23 |   100 |
|    3 |      1 |    32 |   100 |
|    4 |      2 |    32 |   100 |
|    5 |      3 |    32 |   100 |
|    6 |      3 |    32 |   100 |
+------+--------+-------+-------+
6 rows in set (0.00 sec)

mysql> set autocommit=1;
Query OK, 0 rows affected (0.00 sec)

注意：事务仅对表级别的修改有效(drop 无效)(仅对delete、insert、update有效）