一、多表查询的概述
多表查询 //☆☆☆☆☆
概念:一次查询多张数据库表
分类:内连接查询,外链接查询,子查询
1、多表查询语法
查询语法:
select:列名列表
from:表名列表
where…
2、准备表
# 创建部门
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) -- 外键。关联部门:外键(dept_id)引用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-28',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',4);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',5);
1.笛卡尔积
两个集合A和B,求他们的所有组成
举例:{a,b,c} {1,2} = {a1,a2,b1,b2,c1,c2}
2.为什么要使用笛卡尔积?
是为了在一条记录中找到所有的信息 ☆☆☆☆☆
比如:查询孙悟空所在部门名称
3.笛卡尔积的弊端?
产生了大量的冗余数据
4.怎么解决产生的大量冗余数据?
一般情况下根据主外键的关系来去除
去除笛卡尔积 (根据主外键)
5、多表查询分类:
1、内连接查询
2、外连接查询
3、子查询
二、内连接查询
1.分类
(1)隐式内连接
实现:
使用where条件来消除无用的笛卡尔积 (使用的是where)
举例:
1、 查询所有员工信息对应的部门信息
SELECT * FROM emp , dept WHERE emp.dept_id = dept.id;
2、 查询员工表的姓名、性别。部门的名称
SELECT emp.name,dept.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
ti.dept_id = t2.id;
(2)显示内连接 //至少能看懂
实现:
select 字段列表 from 表名1 [inner] join 表名2 on 条件 //使用的是 join on。 " [ ] “意思是可省略的
举例:
查询所有员工信息对应的部门信息
SELECT * FROM emp INNER JOIN dept ON emp.dept_id = dept.id ;
SELECT * FROM emp JOIN dept ON emp.dept_id = dept.id;
(3)内连接查询:
1、从哪些表中查询数据
2、条件是什么
3、查询哪些字段
(4)对比两种内连接查询
隐式内连接和显示内连接效率差不多,推荐隐式内连接
三、外连接
1. 左外连接:
语法:select 字段列表 from 表1 left [outer] join 表2 on 条件;// [ ] 中内容可不写
查询的是左表所有数据以及其交集部分。
例子:
查询所有员工信息,如果员工有部门,则查询部门名称,没有部门,则不显示部门名称
SELECT t1.* , t2. name FROM emp t1 LEFT JOIN dept t2 ON t1.dept_id = t2.id`;
2. 右外连接:
语法: select 字段列表 from 表1 right [outer] join 表2 on 条件;
查询的是右表所有数据以及其交集部分。
; 例子:
SELECT * FROM dept t2 RIGHT JOIN emp t1 ON t1.dept_id = t2.id`;
3.相关问题
(1)为什么要使用外连接?
为了解决交集以外的数据问题
(2)内连接、左外连接、右外连接它们之间的区别?
内连接:A表和B表的交集
左外连接:A表的所有数据 + A表和B表的交集
右外连接:B表的所有数据 + A表和B表的交集
(3)左表和右表到底怎么区分呢?
以join为分隔点,左边的表就是左表,右边的表就是右表
连接查询小总结:
(1)凡是使用了连接查询,就必须要去除笛卡尔积
(2)凡是看到 ‘所有’、'每一个’的这些字眼一定要使用外连接
四、子查询
1、概念:查询中嵌套查询,称嵌套查询为子查询。 //一般也叫嵌套查询
– 查询工资最高的员工信息
例:1 查询最高的工资是多少 9000
SELECT MAX(salary) FROM emp;
– 2 查询员工信息,并且工资等于9000的
SELECT * FROM emp WHERE emp.salary
= 9000;
– 一条sql就完成这个操作:子查询
SELECT * FROM emp WHERE emp.salary
= (SELECT MAX(salary) FROM emp);
2、以后我们怎么去写子查询?
分步骤去写
第一步:先写里面的那个查询
SELECT MAX(salary) FROM emp
第二步:再写外面的查询
SELECT * FROM emp WHERE emp.salary
= (SELECT MAX(salary) FROM emp);
3、子查询的不同情况
1. 子查询的结果是单行单列的: 单行单列
子查询可以作为条件,使用运算符去判断。 运算符: > >= < <= = //使用场景:在where后 > >= < <= =
例如:查询员工工资小于平均工资的人
SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
2. 子查询的结果是多行单列的: 单行多列
1、子查询可以作为条件,使用运算符in来判断 //使用场景: 在where后,跟in连用
例如:查询’财务部’和’市场部’所有的员工信息
方法一:一般方法
SELECT id FROM dept WHERE NAME = ‘财务部’ OR NAME = ‘市场部’;
SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;
方法二:子查询方法
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = ‘财务部’ OR NAME = ‘市场部’);
3. 子查询的结果是多行多列的: 多行多列(一张表)
1、子查询可以作为一张虚拟表参与查询 //使用场景: 在from后作为一张虚拟表
例如:查询员工入职日期是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. 概念: 如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败。
如果同时成功------提交
如果有一个失败-----回滚(回滚到你开启事务的那一刻)
//三层结构: 控制层(controller)、业务层(service)、持久层(dao)
//事务也叫作业务,多条 “操作语句”(增删改–dml) 结合在一起形成的操作
-
操作:
1. 开启事务:start transaction;
2. 回滚:rollback;
3. 提交:commit; -
例子:
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
balance DOUBLE
);
-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);
-- 张三给李四转账 500 元
SELECT * FROM account;
UPDATE account SET balance = 1000;
-- 0. 开启事务
START TRANSACTION;
-- 1. 张三账户 -500
UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';
-- 2. 李四账户 +500
UPDATE account SET balance = balance + 500 WHERE NAME = 'zhangsan';
-- 出错了...
UPDATE account SET balance = balance - 500 WHERE NAME = 'lisi';
-- True 发现执行没有问题,提交事务
COMMIT;
-- False 发现出问题了,回滚事务
ROLLBACK;
2、事务的四大特征:常见的面试题或者笔试题
(1). 原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败。
(2). 持久性:当事务提交或回滚后,数据库会持久化的保存数据。
(3). 隔离性:多个事务之间。相互独立。
(4). 一致性:事务操作前后,数据总量不变
"原持一隔"
3、 事务的隔离级别(了解)
概念:多个事务之间隔离的,相互独立的。但是如果多个事务操作同一批数据,则会引发一些问题, 设置不同的隔离级别就可以解决这些问题。
存在问题:
1. 脏读: 一个事务,读取到另一个事务中没有提交的数据
2. 不可重复读(虚读): 在同一个事务中,两次读取到的数据不一样。
3. 幻读: 一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改。(MySQL数据库演示不出来)
隔离级别:
1. read uncommitted:读未提交
* 产生的问题:脏读、不可重复读、幻读
2. read committed:读已提交 (Oracle)
* 产生的问题:不可重复读、幻读
3. repeatable read:可重复读 (MySQL默认)
* 产生的问题:幻读
4. serializable:串行化
* 可以解决所有的问题
* 注意:隔离级别从小到大安全性越来越高,但是效率越来越低
数据库查询隔离级别:
select @@tx_isolation;
数据库设置隔离级别:
set global transaction isolation level 隔离级别字符串;
演示:
set global transaction isolation level read uncommitted;
start transaction;
– 转账操作
update account set balance = balance - 500 where id = 1;
update account set balance = balance + 500 where id = 2;
//在以后的mysql操作中,我们不需要去修改mysql的隔离级别,直接使用默认的就好了
4、 MySQL数据库中事务默认自动提交
* 事务提交的两种方式:
* 自动提交:
* mysql就是自动提交的
* 一条DML(增删改)语句会自动提交一次事务。
* 手动提交:
* Oracle 数据库默认是手动提交事务
* 需要先开启事务,再提交
* 修改事务的默认提交方式:
* 查看事务的默认提交方式:SELECT @@autocommit; -- 1 代表自动提交 0 代表手动提交
* 修改默认提交方式: set @@autocommit = 0;
5、相关问题
之前我们在数据库中执行操作,为什么没有写事务?
数据库默认的帮我们开启了事务,自动帮我们提交
如果我们手动的开启了一个事务,那么mysql就不会再自动开启事务了、自动提交,必须自己手动提交
六、DCL //了解
- SQL分类:
- DDL:操作数据库和表
- DML:增删改表中数据
- DQL:查询表中数据
- DCL:管理用户,授权
-
DBA:数据库管理员
-
DCL:管理用户,授权
1、管理用户
1、添加用户语法:CREATE USER ‘用户名’@‘主机名’ IDENTIFIED BY ‘密码’; @‘主机名’ - - > @‘%’ 表示:任意主机
2、删除用户
语法:DROP USER ‘用户名’@‘主机名’;
3、修改用户密码
UPDATE USER SET PASSWORD = PASSWORD(‘新密码’) WHERE USER = ‘用户名’;
UPDATE USER SET PASSWORD = PASSWORD(‘abc’) WHERE USER = ‘lisi’;
SET PASSWORD FOR ‘用户名’@‘主机名’ = PASSWORD(‘新密码’);
SET PASSWORD FOR ‘root’@‘localhost’ = PASSWORD(‘123’);
3.1mysql中忘记了root用户的密码? //也可选择卸载重装
1、需要以管理员身份运行该cmd - - > net stop 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、使用新密码登录。
4、查询用户
查询用户:
– 1. 切换到mysql数据库
USE myql;
– 2. 查询user表
SELECT * FROM USER;
通配符: % 表示可以在任意主机使用用户登录数据库
如果以后遇到真的要我们去操作权限,我们只要可以根据百度、笔记、资料能模仿出来就行
2、权限管理:
1、查询权限:
SHOW GRANTS FOR ‘用户名’@‘主机名’;
SHOW GRANTS FOR ‘lisi’@’%’;
2、授予权限:
grant 权限列表 on 数据库名.表名 to ‘用户名’@‘主机名’;
例子1: 给张三用户授予查询、删除、改权限,在任意数据库任意表上
GRANT SELECT,DELETE,UPDATE ON db3.account TO ‘zhangsan’@‘localhost’;
例子2: 给张三用户授予所有权限,在任意数据库任意表上
GRANT ALL ON *.* db3.account TO ‘zhangsan’@‘localhost’;
3、撤销权限:
revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’;
例子:撤销李四的 update 权限
REVOKE UPDATE ON db3.account FROM ‘lisi’@’%’;
多表查询练习:
1、准备表:
A:对应着每种水果;B:对应着每种水果的价格
create table A(
A_ID int primary key auto_increment,
A_NAME varchar(20) not null
);
insert into A values(1,‘苹果’);
insert into A values(2,‘橘子’);
insert into A values(3,‘香蕉’);
create table B(
A_ID int primary key auto_increment,
B_PRICE double
);
insert into B values(1,2.30);
insert into B values(2,3.50);
insert into B values(4,null);
需求:
1、查询价格最贵的水果名称
2、显示最贵的水果 以及 最贵水果的价格
答案:
1、 SELECT a_name FROM a WHERE a_id =(SELECT a_id FROM b WHERE b_price=(SELECT MAX(B_PRICE) FROM b))
2、 SELECT a.A_NAME,b.B_PRICE FROM a,b WHERE a.A_ID=b.A_ID AND a.A_ID=(SELECT a_id FROM b WHERE b_price=(SELECT MAX(B_PRICE) FROM b))
部门员工查询练习
– 部门表
create table dept(
deptno int primary key auto_increment, – 部门编号
dname varchar(14) , – 部门名字
loc varchar(13) – 地址
) ;
– 员工表
create table emp(
empno int primary key auto_increment,-- 员工编号
ename varchar(10), – 员工姓名 -
job varchar(9), – 岗位
mgr int, – 直接领导编号
hiredate date, – 雇佣日期,入职日期
sal int, – 薪水
comm int, – 提成
deptno int not null, – 部门编号
foreign key (deptno) references dept(deptno)
);
INSERT INTO dept02 VALUES(10,‘财务部’,‘北京’);
INSERT INTO dept02 VALUES(20,‘研发部’,‘上海’);
INSERT INTO dept02 VALUES(30,‘销售部’,‘广州’);
INSERT INTO dept02 VALUES(40,‘行政部’,‘深圳’);
INSERT INTO emp02 VALUES(7369,‘刘一’,‘职员’,7902,‘1980-12-17’,800,NULL,20);
INSERT INTO emp02 VALUES(7499,‘陈二’,‘推销员’,7698,‘1981-02-20’,1600,300,30);
INSERT INTO emp02 VALUES(7521,‘张三’,‘推销员’,7698,‘1981-02-22’,1250,500,30);
INSERT INTO emp02 VALUES(7566,‘李四’,‘经理’,7839,‘1981-04-02’,2975,NULL,20);
INSERT INTO emp02 VALUES(7654,‘王五’,‘推销员’,7698,‘1981-09-28’,1250,1400,30);
INSERT INTO emp02 VALUES(7698,‘赵六’,‘经理’,7839,‘1981-05-01’,2850,NULL,30);
INSERT INTO emp02 VALUES(7782,‘孙七’,‘经理’,7839,‘1981-06-09’,2450,NULL,10);
INSERT INTO emp02 VALUES(7788,‘周八’,‘分析师’,7566,‘1987-06-13’,3000,NULL,20);
INSERT INTO emp02 VALUES(7839,‘吴九’,‘总裁’,NULL,‘1981-11-17’,5000,NULL,10);
INSERT INTO emp02 VALUES(7844,‘郑十’,‘推销员’,7698,‘1981-09-08’,1500,0,30);
INSERT INTO emp02 VALUES(7876,‘郭靖’,‘职员’,7788,‘1987-06-13’,1100,NULL,20);
INSERT INTO emp02 VALUES(7900,‘令狐冲’,‘职员’,7698,‘1981-12-03’,950,NULL,30);
INSERT INTO emp02 VALUES(7902,‘张无忌’,‘分析师’,7566,‘1981-12-03’,3000,NULL,20);
INSERT INTO emp02 VALUES(7934,‘杨过’,‘职员’,7782,‘1983-01-23’,1300,NULL,10);
练习:
– 1.列出至少有一个员工的所有部门。
– 2.列出薪金比"刘一"多的所有员工。
– 3. 列出所有员工的姓名及其直接上级的姓名。
– 4.列出受雇日期早于其直接上级的所有员工。
– 5.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门。
– 6.列出所有job为“职员”的姓名及其部门名称。
– 7.列出最低薪金大于1500的各种工作。
– 8.列出在部门 “销售部” 工作的员工的姓名,假定不知道销售部的部门编号。
– 9.列出薪金高于公司平均薪金的所有员工。
– 10.列出与"周八"从事相同工作的所有员工。
– 11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。(只要和部门30中任意一个员工的薪资相等即可)
– 12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
– 13.列出在每个部门工作的员工数量、平均工资。
– 14.列出所有员工的姓名、部门名称和工资。
– 15.列出所有部门的详细信息和部门人数。
– 16.列出各种工作的最低工资。
– 17.列出各个部门的 经理 的最低薪金。
– 18.列出所有员工的年工资,按年薪从低到高排序。
– 19.查出emp表中薪水在3000以上(包括3000)的所有员工的员工号、姓名、薪水。
– 20.查询出所有薪水在’陈二’之上的所有人员信息。
– 21.查询出emp表中部门编号为20,薪水在2000以上(不包括2000)的所有员工,显示他们的员工号,姓名以及薪水,以如下列名显示:员工编号 员工名字 薪水
– 22.查询出emp表中所有的工作种类(无重复)
– 23.查询出所有奖金(comm)字段不为空的人员的所有信息。
– 24.查询出薪水在800到2500之间(闭区间)所有员工的信息。
– 25.查询出员工号为7521,7900,7782的所有员工的信息。
– 26.查询出名字中有“张”字符,并且薪水在1000以上(不包括1000)的所有员工信息。
– 27.查询出名字第三个汉字是“忌”的所有员工信息。
答案:
– 1.列出至少有一个员工的所有部门。
SELECT t1.dname FROM dept t1, emp t2 WHERE t1.deptno = t2.deptno GROUP BY t1.dname ;
– 2.列出薪金比"刘一"多的所有员工。
SELECT emp.ename FROM emp WHERE sal > (SELECT sal FROM emp WHERE emp.ename = “刘一”);
– 3.列出所有员工的姓名及其直接上级的姓名。
SELECT t1.ename , t2.ename FROM emp t1 LEFT JOIN emp t2 ON t1.mgr = t2.empno;
– 4.列出受雇日期早于其直接上级的所有员工。
SELECT t1.ename FROM emp t1 , emp t2 WHERE t1.mgr = t2.empno AND t1.hiredate< t2.hiredate;
– 5.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门。
SELECT t1.dname , t2.* FROM dept t1 LEFT JOIN emp t2 ON t1.deptno = t2.deptno;
– 6.列出所有job为“职员”的姓名及其部门名称。
SELECT t1.ename,t2.dname FROM emp t1 , dept t2 WHERE t1.deptno = t2.deptno AND t1.job=“职员”;
– 7.列出最低薪金大于1500的各种工作。
SELECT emp.job FROM emp WHERE emp.sal>1500 GROUP BY job;
– 8.列出在部门 “销售部” 工作的员工的姓名,假定不知道销售部的部门编号。
SELECT emp.ename FROM emp WHERE emp.deptno=(SELECT dept.deptno FROM dept WHERE
dept.dname = “销售部”);
– 9.列出薪金高于公司平均薪金的所有员工。
SELECT emp.enameFROM emp WHERE emp.sal>(SELECT AVG(sal) FROM emp);
– 10.列出与"周八"从事相同工作的所有员工。
SELECT ename FROM emp WHERE job =(SELECT job FROM emp WHERE ename = “周八”);
– 11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。(只要和部门30中任意一个员工的薪资相等即可)
SELECT emp.ename,emp.sal FROM emp WHERE sal IN (SELECT sal FROM emp WHERE emp.deptno = 30);
– 12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
SELECT emp.ename,emp.sal FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE emp.deptno = 30);
– 13.列出在每个部门工作的员工数量、平均工资。
SELECT COUNT(emp.empno) ,AVG(sal) FROM emp GROUP BY emp.deptno;
– 14.列出所有员工的姓名、部门名称和工资。
SELECT t1.ename,t2.dname,t1.sal FROM emp t1, dept t2 WHERE t1.deptno = t2.deptno;
– 15.列出所有部门的详细信息和部门人数。
SELECT t1.* ,COUNT(t2.empno) FROM dept t1 LEFT JOIN emp t2 ON t1.deptno = t2.deptnoGROUP BY t1.dname;
– 16.列出各种工作的最低工资。
SELECT job,MIN(sal) FROM emp GROUP BY job;
– 17.列出各个部门的 经理 的最低薪金。
SELECT MIN(sal) FROM emp WHERE job = “经理”;
– 18.列出所有员工的年工资,按年薪从低到高排序。
SELECT emp.ename, emp.sal12 nx FROM emp ORDER BY nx ASC;
– 19.查出emp表中薪水在3000以上(包括3000)的所有员工的员工号、姓名、薪水。
SELECT empno , ename ,sal FROM emp WHERE sal >= 3000;
– 20.查询出所有薪水在’陈二’之上的所有人员信息。
SELECT * FROM emp WHERE sal >(SELECT sal FROM emp WHERE ename = ‘陈二’);
– 21.查询出emp表中部门编号为20,薪水在2000以上(不包括2000)的所有员工,显示他们的员工号,姓名以及薪水,以如下列名显示:员工编号 员工名字 薪水
SELECT empno , ename , sal FROM emp WHERE deptno = 20 AND sal > 2000;
– 22.查询出emp表中所有的工作种类(无重复)
SELECT job FROM emp GROUP BY job;
– 23.查询出所有奖金(comm)字段不为空的人员的所有信息。
SELECT t1. , t2.dname FROM emp t1 , dept t2 WHERE t1.deptno = t2.deptno AND t1.comm IS NOT NULL;
– 24.查询出薪水在800到2500之间(闭区间)所有员工的信息。
SELECT * FROM emp WHERE sal BETWEEN 800 AND 2500;
– 25.查询出员工号为7521,7900,7782的所有员工的信息。
SELECT * FROM emp WHERE emp.empno IN(7521,7900,7782);
– 26.查询出名字中有“张”字符,并且薪水在1000以上(不包括1000)的所有员工信息。
SELECT * FROM emp WHERE ename LIKE ‘%张%’ AND sal >1000;
– 27.查询出名字第三个汉字是“忌”的所有员工信息。
SELECT * FROM emp WHERE ename LIKE ‘__忌%’;