mysql多事务查询_MYSQL多表查询与事务

多表查询

避免笛卡尔积

select * from emp,dept where emp.&＃96;dept_id&＃96; &＃61; dept.&＃96;id&＃96;;#设置过滤条件(隐式内连接)

select emp.&＃96;name&＃96;, dept.&＃96;name&＃96; from emp,dept where emp.&＃96;dept_id&＃96; &＃61; dept.&＃96;id&＃96;;#查询姓名和部门

select * from emp e inner join dept d on e.&＃96;dept_id&＃96; &＃61; d.&＃96;id&＃96;;#显式内连接

select * from emp e inner join dept d on e.&＃96;dept_id&＃96; &＃61; d.&＃96;id&＃96; where e.&＃96;name&＃96;&＃61;&＃39;唐僧&＃39;;#添加过滤条件

select e.&＃96;id&＃96;,e.&＃96;name&＃96;,e.&＃96;gender&＃96;,e.&＃96;salary&＃96;,d.&＃96;name&＃96; from emp e inner join dept d on e.&＃96;dept_id&＃96; &＃61; d.&＃96;id&＃96; where e.&＃96;name&＃96;&＃61;&＃39;唐僧&＃39;;显式内连接并显示指定列

select e.&＃96;id&＃96; 编号,e.&＃96;name&＃96; 姓名,e.&＃96;gender&＃96; 性别,e.&＃96;salary&＃96; 工资,d.&＃96;name&＃96; 部门名字 from emp e inner join dept d on e.&＃96;dept_id&＃96; &＃61; d.&＃96;id&＃96; where e.&＃96;name&＃96;&＃61;&＃39;唐僧&＃39;;#修正表头

select * from dept d left join emp e on d.&＃96;id&＃96; &＃61; e.&＃96;dept_id&＃96;;#左外连接查询。

select * from dept right join emp on dept.&＃96;id&＃96; &＃61; emp.&＃96;dept_id&＃96;;#右外连接查询

嵌套查询

select * from emp where dept_id &＃61; (select id from dept where name&＃61;&＃39;市场部&＃39;);#使用子查询

子查询只有一个值的时候

select * from emp where salary &＃61; (select max(salary) from emp);#查询最高薪水对应的姓名

select * from emp where salary <(select avg(salary) from emp);#小于平均工资的员工

子查询有多个值的时候使用in

select name from dept where id in (select dept_id from emp where salary > 5000);#薪水大于5k的部门

select * from emp where dept_id in (select id from dept where name in(&＃39;开发部&＃39;,&＃39;财务部&＃39;));#查询部门人员

子查询是多行多列的时候使用FROM后面作为表进行二次查询

select * from dept d, (select * from emp where join_date >&＃61;&＃39;2011-1-1&＃39;) e where d.&＃96;id&＃96;&＃61; e.dept_id ;#子查询作为表需要取别名&＃xff0c;否则这张表没有名称则无法访问表中的字段

或&＃xff1a;

select * from emp inner join dept on emp.&＃96;dept_id&＃96; &＃61; dept.&＃96;id&＃96; where join_date >&＃61;&＃39;2011-1-1&＃39;;

select * from emp inner join dept on emp.&＃96;dept_id&＃96; &＃61; dept.&＃96;id&＃96; and join_date >&＃61;&＃39;2011-1-1&＃39;;

事务

手动提交事务

start transaction;

update account set balance &＃61; balance - 500 where name&＃61;&＃39;张三&＃39;;

update account set balance &＃61; balance &＃43; 500 where name&＃61;&＃39;李四&＃39;;

commit;#执行完commit数据才真正发生改变

start transaction;

update account set balance &＃61; balance - 500 where name&＃61;&＃39;张三&＃39;;

rollback;#执行过程中使用rollback表示执行错误并回滚到原始状态

自动提交事务

查看是否自动提交&＃xff1a;

select &＃64;&＃64;autocommit;# &＃64;&＃64;表示全局变量&＃xff0c;结果为1表示开启&＃xff0c;0表示关闭。

set &＃64;&＃64;autocommit&＃61;0;#关闭自动提交

update account set balance&＃61;balance&＃43;500 where id&＃61;2;

commit;#手动提交

回滚点

在某些成功的操作完成之后&＃xff0c;后续的操作有可能成功有可能失败&＃xff0c;但是不管成功还是失败&＃xff0c;前面操作都已经成功&＃xff0c;可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置&＃xff0c;而不是返回所有操作&＃xff0c;这个点称之为回滚点。

事务与隔离

set global transaction isolation level read uncommitted;#设置事务隔离级别为读未提交

读未提交时&＃xff0c;当A对数据进行操作但未提交时&＃xff0c;B读取的信息是未提交信息&＃xff0c;可能不一致。

读已提交时&＃xff0c;当A对数据进行操作但未提交时&＃xff0c;B读取的信息是原始信息&＃xff0c;不出现脏读。

读已提交时&＃xff0c;当A对数据进行操作已提交时&＃xff0c;B读取的信息在A提交前后两次读取不一致&＃xff0c;出现不可重复读。

使用serializable隔离级别&＃xff0c;一个事务没有执行完&＃xff0c;其他事务的SQL执行不了&＃xff0c;可以挡住幻读

DCL语句

mysqld是MySQL的主程序&＃xff0c;服务器端。mysql是MySQL的命令行工具&＃xff0c;客户端。

创建用户user1密码123只能在本机使用&＃xff1a;

create user &＃39;user1&＃39;&＃64;&＃39;localhost&＃39; identified by &＃39;123&＃39;;

创建用户user1密码123可以在任何计算机使用&＃xff1a;

create user &＃39;user2&＃39;&＃64;&＃39;%&＃39; identified by &＃39;123&＃39;;

grant create,alter,insert,update,select on test.* to &＃39;user1&＃39;&＃64;&＃39;localhost&＃39;;#给user1分配到test数据库的部分权限

grant all on *.* to &＃39;user2&＃39;&＃64;&＃39;%&＃39;;#给user2分配到所有数据库的所有表权限

revoke all on test.* from &＃39;user1&＃39;&＃64;&＃39;localhost&＃39;;#撤销user1用户对test数据库所有表的操作权限

show grants for &＃39;user1&＃39;&＃64;&＃39;localhost&＃39;;#查询用户权限&＃xff0c;usage是指登录权限

drop user &＃39;user1&＃39;&＃64;&＃39;localhost&＃39;;#删除用户user1

mysqladmin -uroot -p password 1#修改管理员的用户密码为1(登出状态使用)

set password for &＃39;user2&＃39;&＃64;&＃39;%&＃39;&＃61;password(&＃39;1&＃39;);#修改普通用户user1的密码为1