多表查询
建表
#创建部门表
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
INSERT INTO dept(name) VALUE('武器部'),('执行部'),('纪律部');
INSERT INTO dept(name) VALUE ('后勤部');
#创建员工表
CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30),
gender VARCHAR(1),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES dept(id)
);
INSERT INTO emp(name,gender,dept_id) VALUE('猪八戒','男',4);
INSERT INTO emp(name,gender,dept_id) VALUE('白骨精','女',2);
INSERT INTO emp(name,gender,dept_id) VALUE('红孩儿','男',3);
INSERT INTO emp(name,gender,dept_id) VALUE('孙悟空','男',2);
INSERT INTO emp(name,gender,dept_id) VALUE('沙僧','男',4);
INSERT INTO emp(name,gender,dept_id) VALUE('蜘蛛精','女',2);
INSERT INTO emp(name,gender,dept_id) VALUE('铁扇公主','女',1);
笛卡尔积
- 有两个集合A、B，取这两个集合的所有组成情况
- 要完成多表查询，需要消除无用的数据
1 猪八戒 男 4 1 武器部
1 猪八戒 男 4 2 执行部
1 猪八戒 男 4 3 纪律部
1 猪八戒 男 4 4 后勤部
2 白骨精 女 2 1 武器部
2 白骨精 女 2 2 执行部
2 白骨精 女 2 3 纪律部
2 白骨精 女 2 4 后勤部
3 红孩儿 男 3 1 武器部
3 红孩儿 男 3 2 执行部
3 红孩儿 男 3 3 纪律部
3 红孩儿 男 3 4 后勤部
4 孙悟空 男 2 1 武器部
4 孙悟空 男 2 2 执行部
4 孙悟空 男 2 3 纪律部
4 孙悟空 男 2 4 后勤部
…
内连接查询
-
隐式内连接：使用where条件消除无用的数据
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 e.name, e.gender, d.name FROM emp e, dept d WHERE e.dept_id=d.id; -
显式内连接：
语法：select 字段列表 from 表1 [inner] join 表2 on 条件
#显式内连接 SELECT * FROM emp INNER JOIN dept ON emp.dept_id=dept.id; #inner省略 + 起别名 SELECT * FROM emp e JOIN dept d ON e.dept_id=d.id; -
注意事项：
- 从哪些表中查询数据
- 条件是什么
- 查询哪些字段
外连接查询
-
左外连接
语法：select 字段列表 from 表1 left [outer] join 表2 on 条件；
查询的是左表（表1）所有数据以及其交集部分
#查询所有员工信息，如果员工有部门则查询部门名称，没有部门则不显式部门名称 SELECT t1.*,t2.`name` FROM emp t1,dept t2 WHERE t1.dept_id=t2.id; #使用左外连接实现上述要求 SELECT t1.*,t2.name FROM emp t1 LEFT JOIN dept t2 ON t1.dept_id=t2.id; -
右外连接
语法：select 字段列表 from 表1 right [outer] join 表2 on 条件；
查询的是右表所有数据以及其交集部分
子查询
概念：查询中嵌套查询，称嵌套查询为子查询。
#两步找到数据中id最大的数据
SELECT MAX(id) FROM emp;
SELECT * FROM emp WHERE emp.id = 7;
#使用嵌套查询获取id最大的数据
SELECT * FROM emp WHERE emp.id = (SELECT MAX(id) FROM emp);
子查询不同情况
-
子查询的结果是单行单列的
子查询的结果可以作为条件，使用运算符去判断。运算符：> >= < <= =
#查询员工工资小于平均工资的人 子查询作为条件 SELECT * FROM emp WHERE emp.slalry <(SELECT AVG(slalry) FROM emp); -
子查询的结果是多行单列的
子查询可以作为条件，使用运算符in来判断
#查询财务部和纪律所有员工信息 SELECT id FROM dept WHERE name='财务部' OR name='纪律部'; SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE name='财务部' OR name='纪律部'); -
子查询的结果是多行多列的
如果子查询结果是多行多列的，子查询结果可以作为一张虚拟表
#查询员工入职日期是2015-11-11之后的员工信息和部门信息 SELECT * FROM dept d,(SELECT * FROM emp WHERE date>'2015-11-11') t WHERE d.id=t.dept_id; #使用内连接查询上述需求 SELECT * FROM emp t1,dept t2 WHERE t1.dept_id=t2.id AND t1.date>'2015-11-11';
练习
-- 部门表
CREATE TABLE dept(
id INT PRIMARY KEY PRIMARY KEY, -- 部门id
dname VARCHAR(50), -- 部门名称
loc VARCHAR(50) -- 部门所在地
);
-- 添加四个部门
INSERT INTO dept(id,dname,loc) VALUE
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');
-- 职务表，职务名称，职务描述
CREATE TABLE job(
id INT PRIMARY KEY,
jname VARCHAR(40),
description VARCHAR(40));
-- 员工表
CREATE TABLE emp(
id INT PRIMARY KEY,
ename VARCHAR(30),
job_id INT,
mgr INT,
joindate DATE,
salary INT,
bonus INT,
dept_id INT,
FOREIGN KEY (job_id) REFERENCES job(id),
FOREIGN KEY (dept_id) REFERENCES dept(id)
);
-- 薪资表
CREATE TABLE salarygrade(
grade INT PRIMARY KEY,
losalary INT,
hisalary INT);
#1查询所有员工信息 员工编号 员工姓名 工资 emp 职务名称 职务描述 job
SELECT
t1.id,t1.ename,t1.salary,t2.jname,t2.description
FROM
emp t1,
job t2
WHERE
t1.job_id = t2.id;
#2查询员工编号，姓名，工资，职务名，职务描述，部门名，部门位置
SELECT
t1.id,t1.ename,t1.salary,t2.jname,t2.description,t3.dname,t3.loc
FROM
emp t1,
job t2,
dept t3
WHERE
t1.dept_id = t3.id AND t1.job_id=t2.id;
#3查询员工姓名，工资，工资等级
SELECT
t1.ename,t1.salary,t2.grade
FROM
emp t1,
salarygrade t2
WHERE
t1.salary>=t2.losalary AND t1.salary<t2.hisalary;
#4查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
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.dept_id = t3.id AND t1.job_id=t2.id AND t1.salary>=t4.losalary AND t1.salary<t4.hisalary;
#5查询部门编号，部门名称，部门位置，部门人数
SELECT
dept_id,COUNT(id)
FROM
emp
GROUP BY dept_id;
#使用子查询查询最终结果
SELECT
t1.id,t1.dname,t1.loc,t2.total
FROM
dept t1,
(SELECT
dept_id,COUNT(id) total
FROM
emp
GROUP BY dept_id) t2
WHERE
t1.id=t2.dept_id;
emp
id ename job_id mgr joindate salary bonus dept_id
1001 孙悟空 4 1004 2000-12-17 8000 3000 20
1002 八戒 3 1006 2000-02-17 18000 3000 30
1003 沙僧 2 1005 2000-11-17 28000 3000 40
1004 唐僧 1 1004 2000-05-17 38000 3000 30
1005 小白龙 3 1004 2000-02-17 3001 3000 10
1006 蜘蛛精 2 1004 2000-03-17 84000 3000 20
1007 白骨精 2 1004 2000-04-17 4000 3000 30
1008 琵琶精 4 1004 2000-07-17 5000 3000 30
dept
id dname loc
10 教研部 北京
20 学工部 上海
30 销售部 广州
40 财务部 深圳
job
id jname description
1 董事长 大boss
2 经理 小boss
3 销售员 销售
4 文员 办公
salarygrade
grade losalary hisalary
1 3000 10000
2 10000 15000
3 15000 30000
4 30000 100000
事务
概念：如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败
操作
-
开启事务：start transaction，加入手动开启事务，不执行提交操作，最终关闭会话后，会自动回滚。
-
回滚：rollback
-
提交事务：commit
-
在mysql数据库中事务默认自动提交
事务提交的两种方式：
-
自动提交：mysql就是自动提交的
-
手动提交：需要先开启事务，再提交
修改事务默认提交方式：
- 查看事务的默认提交方式 SELECT @@autocommit; #1代表自动提交，0代表手动提交
- 修改默认提交方式 set @@autocommit=0;
- Oracle数据库默认是手动提交事务，需要先开启事务再提交
-
#0开启事务
START TRANSACTION;
#1张三给李四转500元
UPDATE account SET balance=balance-500 WHERE name='zs';
出错了...
ROLLBACK;
#2李四加500
UPDATE account SET balance=balance+500 WHERE name='ls';
#3提交事务
COMMIT;
#查看事务的默认提交方式
SELECT @@autocommit; #1代表自动提交，0代表手动提交
#修改默认提交方式
set @@autocommit=0;
事务的四大特征
- 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败
- 持久性：如果事务一旦提交或者回滚后，数据库会持久化的保存数据
- 隔离性：多个事务之间，相互独立。
- 一致性：事务操作前后，数据总量不变。
事务的隔离级别（了解）
概念：多个事务之间是隔离、相互独立的，如果多个事务操作同一批数据，会引发问题，通过设置不同的隔离级别就可以解决这些问题。
存在问题：
- 脏读：一个事务，读取到另一个事务中没有提交的数据
- 不可重复读（虚读）：在同一个事务中，两次读取到的数据不一样
- 幻读：一个事务操作数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改
隔离级别：
-
read uncommitted：读未提交
产生的问题：脏读、不可重复读、幻读
-
read commited：读已提交（oracle默认隔离级别）
产生的问题：不可重复读、幻读
-
repeatable read：可重复读（mysql默认隔离级别）
产生的问题：幻读
-
serializable：串行化
可以解决所有的问题
注意：隔离级别从小到大安全性越来越高，但是效率越来越低
数据库查询隔离级别：
- select @@tx_isolation;
数据库设置隔离级别：
- set global transaction isolation level 级别字符串;
811
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
