多表查询
多表查询的分类
1 连接查询
交叉连接:将一个表中的每一条数据和另一张表的每一条数据无条件连接。(了解)
语法1:select * from 表1 cross join 表2 [cross join 表3]...
语法2:select * from 表1,表2,...;
结果集:笛卡尔积:如果表1有3条数据,表2有5条数据,那么交叉连接的结果有15条数据。
内连接:
外连接:
2 子查询:
内连接
1.语法:
显示内连接:
select * from 表1 inner join 表2 on 关联条件 [inner join 表3 on 关联条件] [where 其他条件] -- inner可以省略
关联条件:一般是主外键关联。
隐式内连接:(重点)
select * from 表1,表2,表3,...where 关联条件和其他条件;
2.案例
-- 查询所有员工信息以及所在部门信息
SELECT e.*,d.`NAME` FROM emp e INNER JOIN dept d ON e.`dept_id`=d.`id`;
SELECT e.*,d.`NAME` FROM emp e JOIN dept d ON e.`dept_id`=d.`id`;
SELECT e.*,d.`NAME` FROM emp e,dept d WHERE e.`dept_id`=d.`id`;
-- 查询1号部门和2部门的所有员工信息以及所在部门信息
SELECT e.*,d.`NAME` FROM emp e INNER JOIN dept d ON e.`dept_id`=d.`id` WHERE d.`id` IN(1,2);
SELECT e.*,d.`NAME` FROM emp e JOIN dept d ON e.`dept_id`=d.`id` WHERE d.`id` IN(1,2);
SELECT e.*,d.`NAME` FROM emp e,dept d WHERE e.`dept_id`=d.`id` AND d.`id` IN(1,2);
3.内连接结果集特点:
只能获取到两个表的交集部分,也就是能够使用主外键连接的数据。
外连接
1.语法:
左外连接:select * from 表1 left outer join 表2 on 关联条件 [where 其他条件];
右外连接:select * from 表1 right outer join 表2 on 关联条件 [where 其他条件];
2.案例:
-- 外连接
-- 查询没有员工的部门信息
SELECT d.* FROM dept d LEFT OUTER JOIN emp e ON d.`id`=e.`dept_id` WHERE e.`id` IS NULL;
SELECT d.* FROM emp e RIGHT OUTER JOIN dept d ON d.`id`=e.`dept_id` WHERE e.`id` IS NULL;
-- 统计每个部门的员工数量,没有员工的部门显示0;
SELECT
d.`NAME`,COUNT(e.`dept_id`) 员工数量
FROM
dept d LEFT OUTER JOIN emp e ON d.`id`=e.`dept_id`
GROUP BY d.`NAME`;
3.外连接结果集特点:
左外连接:可以获取左表的全部数据以及和右表的交集部分。
左外连接:可以获取右表的全部数据以及和左表的交集部分。
子查询
子查询概念和分类
概念:一个查询中嵌套另一个查询,这种查询就叫做子查询。
子查询分类:
简单的标准的查询语句:select * from 表名 where 条件;
where子查询:有两种情况,将一个查询结果作为另一个查询的条件
from子查询:有一种情况,将一个查询结果作为表和其他表连接查询。
select子查询:(补充)将一个查询结果作为另一个查询的结果
where子查询
1.什么情况下使用where子查询
1 如果子查询结果是单行单列,那么可以考虑where子查询。因为比较运算符后面只能有一个值。
2 如果子查询结果是多行单列,那么可以考虑where子查询。因为in()中可以使用多个值。
总结:子查询结果一定是单列的情况。如果是多列的情况,那么只能使用from子查询。
2.子查询的结果是单行单列,因为比较运算符后面只能是一个值
-- 查询工资大于平均工资的员工信息
-- 查询员工的平均工资
SELECT AVG(salary) FROM emp;
-- 查询工资大于6000的员工信息
SELECT * FROM emp WHERE salary > 5860;
-- 综合一下
SELECT * FROM emp WHERE salary > (SELECT AVG(salary) FROM emp);
3.多行单列,也叫做带in的子查询
-- 查询"开发部"和"市场部"的员工信息
-- 连接查询
SELECT * FROM emp e,dept d WHERE e.`dept_id`=d.`id` AND d.`NAME` IN('开发部','市场部');
-- 子查询
-- 查询"开发部"和"市场部"的部门id
SELECT id FROM dept WHERE NAME IN('开发部','市场部');
-- 查询1部门和2号部门的员工信息
SELECT * FROM emp WHERE dept_id IN(1,2);
-- 综合一下
SELECT * FROM emp WHERE dept_id IN(SELECT id FROM dept WHERE NAME IN('开发部','市场部'));
from子查询
1.什么情况下使用from子查询
如果子查询是多列的情况,那么只能使用from子查询
-- 查询"开发部"和"市场部"的员工信息和部门信息
-- 连接查询
SELECT * FROM emp e,dept d WHERE e.`dept_id`=d.`id` AND d.`NAME` IN('开发部','市场部');
-- 子查询:
-- 查询查询"开发部"和"市场部"的部门信息
SELECT * FROM dept WHERE NAME IN('开发部','市场部');
-- 将上面的查询结果和emp表连接查询
SELECT
*
FROM
emp e,(SELECT * FROM dept WHERE NAME IN('开发部','市场部')) d
WHERE e.`dept_id`=d.id;
-- 查询最高工资和最低工资的员工信息
-- 查询最高工资和最低工资
SELECT MAX(salary),MIN(salary) FROM emp;
-- 查询工作等于9000或者3600的员工信息
SELECT * FROM emp WHERE salary IN(9000,3600);
-- 综合
SELECT
e.*
FROM emp e,(SELECT MAX(salary) max_sal,MIN(salary) min_sal FROM emp) sal
WHERE salary IN(sal.max_sal,sal.min_sal);
select子查询
使用条件:子查询结果只能是单行单列的
-- select 子查询
-- 连接查询
SELECT e.*,d.name FROM emp e,dept d WHERE e.`dept_id`=d.id;
-- 子查询 将查询到的每条员工信息的部门id从部门表中查询部门名称
SELECT *,(SELECT NAME FROM dept WHERE id=dept_id) 部门名称 FROM emp;
多表查询总结
对于一个需求,首先分析需要查询的结果来自于哪些表,如果这些表都是原始表,那么就可以考虑连接查询。如果需要查询一个表的全部数据,那么就使用外连接,否则可以考虑使用内连接。如果查询条件不是直接给出的,而是需要通过查询得到,那么就使用子查询。
事务以及事务隔离级别
事务的概念
如果一组sql操作要么同时成功要么同时失败,那么就需要统一管理,这就是事务管理。
如何使用事务(开启事务,提交事务,回滚事务)
开启事务:start trunsation;
提交事务:commit;
回滚事务:rollback;
说明:对于mysql数据库,每一条DML语句都是在一个单独的事务中执行,如果没有使用START TRANSACTION开启事务,那么事务默认是提交,如果使用了TART TRANSACTION开启事务,那么就必须手动提交或者回滚。对于oracle数据库,每一条DML语句都是在一个单独的事务中执行,但是都必须手动提交或者回滚。
事务的4大特征
原子性:一组sql操作要么同时成功要么同时失败。
持久性:事务一旦提交或者回滚,那么就将数据永久性的保存到数据库中。
隔离性:事务与事务之间本不应该相互影响。
一致性:事务前后,数据的总量不会发生变化。
其他错误说法:完整性、有效性...
事务的隔离级别
1. read uncommitted:读未提交
产生的问题:脏读、不可重复读、幻读
2. read committed:读已提交 (Oracle)
产生的问题:不可重复读、幻读
3. repeatable read:可重复读 (MySQL默认)
产生的问题:幻读
4. serializable:串行化
可以解决所有的问题
数据库查询隔离级别:
select @@tx_isolation;
数据库设置隔离级别:
set global transaction isolation level 级别字符串;
DCL-用户管理及授权
修改root用户密码
1. 需要管理员运行cmd -- > net stop mysql 停止mysql服务
2. 使用无验证方式启动mysql服务: mysqld --skip-grant-tables
3. 打开新的cmd窗口,直接输入mysql命令,敲回车。就可以登录成功
4. use mysql;
5. update user set password = password('你的新密码') where user = 'root';
6. 关闭两个窗口
7. 打开任务管理器,手动结束mysqld.exe 的进程
8. 启动mysql服务
9. 使用新密码登录
用户管理以及授权
1.查询用户
USE myql;
select * from user;
2.创建用户
语法:CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
例如:create user 'zhangsan'@'%' IDENTIFIED BY '123456';
3.删除用户
语法:DROP USER '用户名'@'主机名';
例如:drop user 'zhangsan'@'%';
4.查看权限
语法:SHOW GRANTS FOR '用户名'@'主机名';
例如:SHOW GRANTS FOR 'zhangsan'@'%';
5.授予权限
语法:grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
例如:GRANT ALL ON *.* TO 'zhangsan'@'%';
6.撤销权限
语法:revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
例如:REVOKE UPDATE ON db3.`account` FROM 'zhangsan'@'%';
总结
1 多表查询(重点)
连接查询:
交叉连接:将两个表中的数据无条件连接,得到的结果是笛卡尔积。(了解)
语法1:select * from 表1 cross join 表2;
语法2:select * from 表1,表2;
内连接:得到的结果是两个表的交集部分(能够使用主外键连接的数据)。(重点)
显示内连接:select * from 表1 [inner] join 表2 on 关联条件 [where 其他条件];
隐式内连接:select * from 表1,表2 where 条件; -- 常用
外连接:得到的结果是一个表的全部数据以及和另一张表的交集部分。
左外连接:select * from 表1 left [outer] join 表2 on 关联条件 [where 其他条件];
右外连接:select * from 表1 right [outer] join 表2 on 关联条件 [where 其他条件];
子查询:
where子查询:将一个查询结果作为另一个查询条件。
两种情况:子查询结果是单行单列或者多行单列。总之是单列的。如果是多行单列,那么条件使用in()
from子查询:将一个查询结果作为表和其他表连接查询。
一种情况:子查询结果一般是多列
select子查询:将一个查询结果作为另一个查询的结果,
一种情况:子查询结果是单行单列。
2 事务管理
概念:一组sql操作要么同时成功要么同时失败,那么就需要统一管理,这就是事务管理。
四大特征:原子性、持久性、隔离性、一致性。