MySQL 多表查询与事务的操作

一,多表联查

有些数据我们已经拆分成多个表,他们之间通过外键进行连接.当我们要查询两个表的数据,各取其中的一列或者多列.
这时候就需要使用多表联查.
数据准备:

# 创建部门表
create table dept(id int primary key auto_increment,name varchar(20)
)
insert into dept (name) values ('开发部'),('市场部'),('财务部'); 
# 创建员工表
create table emp (id int primary key auto_increment,name varchar(10),gender char(1), -- 性别salary double, -- 工资join_date date, -- 入职日期dept_id int,foreign key (dept_id) references dept(id) -- 外键，关联部门表(部门表的主键)
) 
insert into emp(name,gender,salary,join_date,dept_id) values('孙悟空','男
',7200,'2013-02-24',1);
insert into emp(name,gender,salary,join_date,dept_id) values('猪八戒','男
',3600,'2010-12-02',2);
insert into emp(name,gender,salary,join_date,dept_id) values('唐僧','男',9000,'2008-
08-08',2);
insert into emp(name,gender,salary,join_date,dept_id) values('白骨精','女
',5000,'2015-10-07',3);
insert into emp(name,gender,salary,join_date,dept_id) values('蜘蛛精','女
',4500,'2011-03-14',1);

如果现在我们想知道对应员工所在的部门,就需要联查,但是如果仅仅select * from emp,dept;则会把所有的情况罗列出来,但是有效的数据,其实仅仅是emp.dept_id= dept.id,也就是外键等于主键的情况.

--所有的情况罗列出来,员工表4*部门表3=12列
select * from emp,dept;
--满足条件的情况,只有四条
select * from emp,dept where emp.`dept_id` = dept.`id`;
--只展示员工名字和部门名
select emp.`name`, dept.`name` from emp,dept where emp.`dept_id` = dept.`id`;

二,子查询

有的时候,我们需要先查一个表,得到结果后才能查询另一个表.比如说,我们现在要查询:开发部中有哪些员工.
如果我们一步步来,就需要走两步:

select id from dept where name='开发部' ;
select * from emp where dept_id = 1;

如果采用子查询,就是把查询的结果再次作为查询的条件进行查询:

1) 一个查询的结果做为另一个查询的条件
2) 有查询的嵌套，内部的查询称为子查询
3) 子查询要使用括号

当第一次查询的结果是多行一列时,还可以用in关键字,以下两种写法是等价的:

select name from dept where id =(select dept_id from emp where salary > 5000);
select name from dept where id in (select dept_id from emp where salary > 5000);

当第一次查询是多行多列时,则需要将第一次的查询结果起个别名,例如:查询出 2011 年以后入职的员工信息，包括部门名称.

select * from dept d, (select * from emp where join_date >='2011-1-1') e where
d.`id`= e.dept_id ;
--d是dept表的别名
--e是select * from emp where join_date >='2011-1-1'的结果表别名

三,事务

事务执行是一个整体，所有的 SQL 语句都必须执行成功。如果其中有 1 条 SQL 语句出现异常，则所有的
SQL 语句都要回滚，整个业务执行失败。
现在我们模拟张三转给李四500块钱,就需要张三减少500,李四增加500来

-- 创建数据表
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
balance DOUBLE
);
-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('张三', 1000), ('李四', 1000);
-- 张三账号-500
update account set balance = balance - 500 where name='张三';
-- 李四账号+500
update account set balance = balance + 500 where name='李四';

但是如果在执行到张三账户-500的时候,服务出问题了,李四的账号并没有+500 元，数据就出现问题了。我们需要保证其中
一条 SQL 语句出现问题，整个转账就算失败。只有两条 SQL 都成功了转账才算成功。这个时候就需要用到事务。
对于事物,分为手动提交事务和自动提交事务.

3.1,手动提交事务

主要的流程如下:

1,start transaction;开启事务
2,若全部sql语句执行正常,则提交事务:commit;
3,如有sql语句执行出问题,则回滚所有事务中的sql语句:rollback;

例如:

--开启事务
start transaction;
-- 张三账号-500
update account set balance = balance - 500 where name='张三';
-- 李四账号+500
update account set balance = balance + 500 where name='李四';
rollback;

就会发现这两条修改账户余额的sql都回滚了.

3.2,自动提交事务

MySQL 默认每一条 DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，语句执行完毕
自动提交事务，MySQL 默认开始自动提交事务

--查看mysql是否开启自动提交事务,1:开启,0:未开启
select @@autocommit;
--设置为不自动提交事务
set @@autocommit=1

这样之后,每次写的sql语句不会更新,而是需要执行commit后才会更新.

3.3,回滚点

每次回滚总不能都全部回滚,前面操作都已经成功，可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置，而不是返回所有操作，这个点称之为回滚点。

--开启事务
start transaction;
savepoint 回滚点名字a;
savepoint 回滚点名字b;
rollback to 回滚点名字a;

3.4,事务的隔离级别

事务在操作时的理想状态：所有的事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问同一个数据。可能引发并发访问的问题：
请添加图片描述
MySQL 数据库有四种隔离级别:

需要注意的事情:

--查看隔离级别
select @@tx_isolation;
--设置隔离级别
set global transaction isolation level 级别字符串;
--示例设置隔离级别为read committed
set global transaction isolation level read committed;

隔离级别越高，性能越差，安全性越高。

四,mysql用户角色的创建

我们现在默认使用的都是 root 用户，超级管理员，拥有全部的权限。但是，一个公司里面的数据库服务器上面可能同时运行着很多个项目的数据库。所以，我们应该可以根据不同的项目建立不同的用户，分配不同的权限来管理和维护数据库。

4.1,创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

请添加图片描述
示例,创建用户1,让他能在任何电脑登录,密码为123:

create user 'user1'@'%' identified by '123';

4.2,给用户授权

新创建的用户是没有权限的,需要另外给他授权.

--权限有: CREATE、ALTER、SELECT、INSERT、UPDATE 等。如果要授予所有的权限则使用 ALL
GRANT 权限 1, 权限 2... ON 数据库名.表名 TO '用户名'@'主机名';
--数据库名.表名:该用户可以操作哪个数据库的哪些表。如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*
--示例:
grant CREATE,ALTER,SELECT,INSERT,UPDATE on db2.account to 'user1'@'%';
--查看用户权限
SHOW GRANTS FOR '用户名'@'主机名';
--撤销用户授权
revoke all on 数据库名.表名 from '用户名'@'主机名';
--删除用户
DROP USER '用户名'@'主机名';
--修改管理员登录密码(未登录情况)
mysqladmin -uroot -p password 新密码
--修改普通用户密码
set password for '用户名'@'主机名' = password('新密码');