【Javaweb】MySQL多表查询与事务④

内连接查询

内连接查询包括：

• 隐式内连接
• 显式内连接

他俩实质上是一样的，只是写法不同

-- 隐式内连接

select * from tb_user, tb_user_desc;
-- 这样会产生笛卡尔积：有A，B两个集合，取A，B的所有组合情况，出现3*3=9种组合

select * from tb_user, tb_user_desc where tb_user.id = tb_user_desc.id;
-- 通过添加一个where条件来限定出现的组合数，使其不出现重复值

-- 查询tb_user的nickname，age，tb_user_desc的edu，income
SELECT
tb_user.nickname, tb_user.age, tb_user_desc.edu, tb_user_desc.income
FROM
tb_user, tb_user_desc
WHERE
tb_user.id = tb_user_desc.id;

-- 由于tb_user_desc太长了，可以使用as给表起别名，as可以省略
-- (注意，用了别名之后就不能用本名了，否则会报错)
SELECT
t1.nickname, t1.age, t2.edu, t2.income
FROM
tb_user AS t1, tb_user_desc AS t2
WHERE
t1.id = t2.id;

查询结果

-- 显式内连接

-- 显式内连接，其中inner可以省略，效果和隐式内连接相同
SELECT
tb_user.nickname, tb_user.age, tb_user_desc.edu, tb_user_desc.income
FROM
tb_user
INNER JOIN tb_user_desc ON tb_user.id = tb_user_desc.id;

查询结果同上一样

外连接

-- 左外连接
select * from tb_user LEFT JOIN tb_user_desc on tb_user.id = tb_user_desc.id;

-- 右外连接
select * from tb_user RIGHT JOIN tb_user_desc on tb_user.id = tb_user_desc.id;

-- 左外连接代替右外连接
select * from tb_user_desc LEFT JOIN tb_user on tb_user.id = tb_user_desc.id;

子查询

查询中嵌套查询，括号里的内容（被嵌套的查询）就叫子查询

单行单列	子查询中（第一次查询）查出来的结果是单行单列的
多行单列	子查询中（第一次查询）查出来的结果是多行单列的
多行多列	子查询中（第一次查询）查出来的结果是多行多列的

 

-- 单行单列子查询

-- 查询所有年龄大于'顶针'的人

select age from tb_user where nickname='丁真';

select * from tb_user where age > 22;

select nickname from tb_user where age > (select age from tb_user where nickname='丁真');

-- 多行单列子查询

-- 查询销售部的员工信息

select * from emp2 where dep_id = (select id from dept where dep_name='销售部');

-- 查询研发部和外贸部的员工信息

select * from emp2 where dep_id in (select id from dept where dep_name='研发部' or dep_name='外贸部');

-- 这里注意，因为（）里的内容是研发部和外贸部的id 2、3。所以这里不能用等号，应该用in，如果emp2 中dep_id的值为2或3，则显示员工信息

-- 多行多列子查询
-- 查询年龄大于25的员工信息和部门信息

-- 1.先查询年龄大于25的员工信息
select * from emp2 where age>25;

-- 2.用内联表把上面查到的员工信息和dept部门信息连接
select * from (select * from emp2 where age>25) as t1 , dept as t2 where t1.dep_id = t2.id;

事务

一组事务包含一组命令，他们要么同时生效，要么同时失效

其实你在MySQL中的每一个操作，都被当做一个独立的事物，比如：

-- begin;

select * from stu;

-- commit;

他们自动被begin和commit包围起来

本节要学的事物就是手动用begin开启一段事物，用来保证数据的安全性（比如下面的银行存取案例）

事务四大特征

• 原子性(Atomicity)：事务是不可分割的最小操作单位，要么同时成功，要么同时失败
• 一致性(Consistency)：事务完成时，必须使所有的数据都保持一致状态
• 隔离性(Isolation)：多个事务之间，操作的可见性
• 持久性(Durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

-- 创建账户表

create table account(
    id int primary key auto_increment,
    name varchar(50),
    money double(10,2)
);

insert into account(name, money) values
("张三",1000),
("李四",1000);

select * from account;

update account SET money = 1000;

-- 转账操作：李四给张三转账500元
-- 开启事务 事务在开启后，做出的操作都是临时性的，直到你commit提交事务，才能永久更改数据

begin;

-- 1.查询李四的余额
-- 2.李四-500
update account set money = money - 500 where name = '李四';

-- 3.张三+500
update account set money = money + 500 where name = '张三';

-- 提交事务 提交事物后才能永久更改数据
commit;

-- 回滚事务 就是恢复到未开启的状况
rollback;

-- 查询事务的默认提交方式
select @@autocommit;

-- 设置默认提交方式为自动/手动提交 1/0
set @@autocommit = 1;

部分图源：黑马程序员