MySQL数据库学习（二）

1、 多表查询
需要消除无用的数据
查询语法：

多表查询的分类：

内连接查询：

隐式内连接：使用where条件消除无用的数据
Eg:
–查询所有员工信息和对应的部门信息
Select * from emp,dept where emp.’dept_id’ = dept.’id’;
–查询员工表的名称，性别，部门表的名称
Select emp.name,emp.gender,dept.name from emp,dept where emp.’dept_id’ = dept.’id’;

Select
	T1.name,
	T1.gender,
	T2.name,
From
	Emp t1,
	Dept t2
Where
	T1.‘dept_id’ = t2.‘id’;

显式内连接
语法：select 字段列表 from 表名1 inner join 表名2 on 条件
Eg:
Select * from emp inner join dept on emp.’dept_id’ = dept.’id’;(inner可以省略)

内连接查询注意事项：
从哪些表查询？ 条件是什么？ 查询哪些字段？

外连接查询：

左外连接
	语法：select 字段列表 from 表1 left [outer] join 表2 on 条件；
	查询的是左表所有的数据及其交集部分
右外连接

语法：select 字段列表 from 表1 right [outer] join 表2 on 条件；
查询的是右表所有的数据及其交集部分

子查询：
概念：查询中嵌套查询，称嵌套查询为子查询
Eg:查询工资最高的员工信息
Select * from emp where emp.salary = (select MAX(salary) from emp);

子查询不同情况：
1、子查询结果是单行单列的
子查询可以作为条件，使用运算符判断
Eg:小于平均薪资
Select * from emp where emp.salary < (select avg(salary) from emp);

2、子查询结果是多行单列的
使用运算符 in 来判断
Eg:查询财务部、市场部所有员工信息
Select * from emp where dept_id in (select id from dept where name = ‘财务部’or name = ‘市场部’)；

3、子查询结果是多行多列的
子查询可以作为一张虚拟表来进行一张表的查询
Eg: 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
Select * from dept t1,(select * from emp where emp.join_date > ‘2011-11-11’) t2 where t1.id = t2.dept_id;
可以用内链接来完成：
Select * from emp t1,dept t2 where t1.dept_id = t2.id and t1.join_date>”2011-11-11”;

例子：
– 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
/*
分析：1、员工编号，员工姓名，工资需要查询emp表 职务名称，职务描述需要查询job表
2、查询条件：emp.job_id = job.id
*/
SELECT
t1.id,t1.ename,t1.salary,t2.jname,t2.description
FROM
emp t1,job t2
WHERE
t1.job_id = t2.id;

– 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
/*
分析：
1.员工编号，员工姓名，工资 emp 职务描述，部门名称 job 部门位置 dept
2.emp.job_id = job.id and emp.dept_id = dept.id
*/
SELECT
t1.id,t1.ename,t1.salary,t2.jname,t2.description,t3.dname,t3.loc
FROM
emp t1,job t2,dept t3
WHERE
t1.job_id = t2.id AND t1.dept_id = t3.id;

– 3.查询员工姓名，工资，工资等级
/*
分析：
1.员工姓名，工资 emp 工资等级 salarygrade
2.条件：emp.salary>=salarygrade.losalary and emp.salary<=salarygrade.hisalary
(between)
*/
SELECT
t1.ename,t1.salary,t2.grade
FROM
emp t1,salarygrade t2
WHERE
t1.salary BETWEEN t2.losalary AND t2.hisalary;

– 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
/*
分析：
1.员工姓名，工资 emp 职务名称，职务描述 job 部门名称，部门位置 dept 工资等级 salarygrade
2.条件：emp.job_id = job.id and emp.dept_id = dept.id and emp.salary>=salarygrade.losalary and emp.salary<=salarygrade.hisalary
*/
SELECT
t1.ename,t1.salary,t2.jname,t2.description,t3.dname,t3.loc,t4.grade
FROM
emp t1,job t2,dept t3,salarygrade t4
WHERE
t1.job_id = t2.id
AND t1.dept_id = t3.id
AND t1.salary BETWEEN t4.losalary AND t4.hisalary;

– 5.查询出部门编号、部门名称、部门位置、部门人数
/*
分析：
1.部门编号、部门名称、部门位置 dept 部门人数 emp
2.使用分组查询。按照emp.dept_id完成分组,查询count(id)
3.使用子查询第二部的查询结果和dept表进行关联查询
*/
SELECT
t1.id,t1.dname,t1.loc,t2.total
FROM
dept t1,(SELECT COUNT(id) total FROM emp GROUP BY dept_id) t2
WHERE t1.id = t2.dept_id;

– 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询
/*
分析：
1.所有员工的姓名emp 直接上级的姓名 emp
*emp表的id和他的mgr是自关联的
2.条件：emp.id = emp.mgr
3.需要查询左表的所有数据和交集数据
/
/
select
t1.ename,t2.ename,t1.mgr,t2.id
from emp t1,emp t2
where t1.mgr = t2.id;
*/
SELECT
t1.ename,t2.ename,t1.mgr,t2.id
FROM emp t1
LEFT JOIN emp t2
ON t1.mgr = t2.id;
2、事务
1.事务的基本介绍
概念：一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。
操作：
开启事务：start transaction
回滚：rollback
提交：commit
MySQL数据库中事务默认自动提交：一条DML语句（增删改）会自动提交一次
查询事务的默认提交方式：select @@autocommit;（1—自动提交，0—手动）
修改事务的默认提交方式：set @@autocommit=0;
2.事务的四大特征
原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
持久性：如果事务一旦提交或者回滚，数据库会持久化的保存数据
隔离性：多个事务之间相互独立
一致性：事务操作前后数据总量不变。

3.事务的隔离级别
概念：多个事务之间隔离的，相互独立的。如果多个事务操作同一批数据，则会引发一些问题，设立隔离级别可以解决。

存在问题：
脏读：一个事务读取到另一个事务中没有提交的数据。
不可重复读：在同一个事务中，两次读取的数据不一样
幻读：一个事务操作（DML）数据表中所有的记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改

隔离级别：
Read uncommitted:读未提交
产生问题：脏读、不可重复读、幻读
Read committed：读已提交(oracle 默认)
产生问题：不可重复读、幻读
Repeatable read:可重复读（MySQL 默认）
产生问题：幻读
Serializable:串行化
可以解决所有问题
隔离级别从小到大安全性越来越高，效率越来越低。

3、DCL
管理用户、授权
管理用户：
添加用户：create user ‘用户名’@ ’主机名’identified by ‘密码’
删除用户：drop user ‘用户名’@ ‘主机名’；
修改用户密码：update user set password = password(‘新密码’) where user = ‘用户名’；
Set password for ‘用户名’@ ‘主机名’=password(‘新密码’)
在MySQL中忘记了root的密码：
Cmd --> net stop mysql (需要管理员运行权限)
使用无验证方式启动MySQL服务：
Mysqld - - skip-grant-tables
打开新的cmd窗口，直接输入mysql，敲回车，即成功
Use mysql
update user set password = password(‘新密码’) where user = ‘root’；
关闭两个窗口
打开任务管理器，关闭MySQL.exe
启动mysql服务
使用新密码登陆

查询用户：

权限管理：
查询权限：show grants for‘用户名’@ ’主机名’;
授予权限：grant 权限列表 on 数据库名.表名 to ‘用户名’@ ’主机名’;
授予所有权限：grant all on . to ‘用户名’@ ’主机名’;
撤销权限：revoke 权限列表 on 数据库名.表名 from 用户名’@ ’主机名’;