### **多表查询**:
##### *内连接
-- 隐式内连接
select 表1.查询字段 , 表2.查询字段 from 表1 , 表2 where 表1.外键列 = 表2.主键;
-- 显式内连接
select * from 表1 [inner] join 表2 on 条件;
作用:查询两张有交集的数据.
##### *外连接
-- 左连接
SELECT 字段名 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件 (OUTER可以省略)
作用:完全显示左表数据和与右边有交集的数据.
-- 右连接
SELECT 字段名 FROM 表1 RIGHT [OUTER ]JOIN 表2 ON 条件
作用:完全显示右表数据和与左边有交集的数据.
##### *子查询
**单行单列:** SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;
**多行单列 :** SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;
**多行多列 :** SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件;
注意:1.子查询结果只要是单列，则在WHERE 后面作为条件
2.子查询结果只要是多列，则在FROM后面作为表进行二次查询
### 事务:
概念:在执行多个SQL语句时,要么全部成功,要么全部失败.
##### 事务操作步骤:
开启事务: start transaction;
回滚事务: rollback;
提交事务: commit;
##### 事务的四大特征:
**1.原子性:**是不可分割的最小操作单位,要么同时成功,要么同时失败
**2.持久性:**当事务提交或者回滚后,数据库会持久化的保存数据.
**3.隔离性:**多个事务之间,相互独立
**4.一致性:**事务操作前后,数据总量不变
##### 事务的隔离级别引发的问题:
**脏读 :**一个事务读取到了另一个事务中尚未提交的数据
**不可重复读 :**一个事务中两次读取的数据内容不一致，要求的是一个事务中多次读取时数据是一致的，这是事务 update时引发的问题
**幻读 :**一个事务中两次读取的数据的数量不一致，要求在一个事务多次读取的数据的数量是一致的，这是 insert 或 delete 时引发的问题
##### 事务的隔离级别:
**read uncommitted :读未提交**
产生的问题:脏读 ,不可重复读, 幻读
**read committed :读已提交**(Oracle默认)
产生的问题:不可重复读,幻读
**repeatable read:可重复读**(MySQL默认)
产生的问题:幻读
**serializable:串行化**
可以解决所有的问题
注意:隔离级别越高，性能越差，安全性越高.
数据库设置隔离级别:
set global transaction isolation level 级别字符串;
数据库查询隔离级别:
select @@tx_isolation;
### DCl数据库语句:
DDL： create / alter / drop 数据库操作
DML：insert /update/delete 数据操作
DQL ：select /show 数据查询
DCL ：grant /revoke 用户授权
##### 创建用户:
**语法:CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';**
-- 创建user1 用户，只能在 localhost这个服务器登录mysql 服务器，密码为123
create user 'user1'@'localhost' identified by '123';
-- 创建user2 用户可以在任何电脑上登录mysql 服务器，密码为123
create user 'user2'@'%' identified by '123';
##### 给用户授权:
**语法:GRANT 权限 1, 权限 2... ON 数据库名.表名 TO '用户名'@'主机名';**
1. 给user1 用户分配对test这个数据库操作的权限：创建表，修改表，插入记录，更新记录，查询
grant create,alter,insert,update,select on test.* to 'user1'@'localhost';
2. 给user2 用户分配所有权限，对所有数据库的所有表
grant all on *.* to 'user2'@'%';
##### 撤销授权:
**语法:REVOKE 权限 1, 权限 2... ON 数据库.表名 from '用户名'@'主机名';**
-- 撤销user1 用户对test数据库所有表的操作的权限
revoke all on test.* from 'user1'@'localhost';
##### 查看权限:
**语法:SHOW GRANTS FOR '用户名'@'主机名';**
##### 删除用户:
**语法:DROP USER '用户名'@'主机名';**
##### 修改管理员密码:
**语法:mysqladmin -uroot -p password 新密码** -- 未登录数据库前操作
##### 修改普通用户密码:
**语法:set password for '用户名'@'主机名' = password('新密码'); -- 登录数据库后操作**
##### =====================================================================================
### 案例语句:
```
-- 创建部门表
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
)
-- 插入数据
2INSERT 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) -- 外键，关联部门表(部门表的主键)
)
-- 插入数据
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-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);
```
##### *多表查询
```
SELECT * FROM emp e ,dept d WHERE e.dept_id = d.id;
```
-- 查询员工和部门的名字
```
SELECT e.name, d.name FROM emp e,dept d WHERE e.dept_id = d.id;
```
##### *隐式内连接查询
-- 隐式内连接查询两张表数据
```
SELECT * FROM emp,dept WHERE emp.dept_id = dept.id;
```
##### *显式内连接查询
-- 显式内连接查询两张表数据
```
SELECT e.id, e.name, d.name FROM emp e JOIN dept d ON e.dept_id=d.id;
```
-- 查询唐僧的信息
```
SELECT * FROM emp INNER JOIN dept ON emp.dept_id = dept.id WHERE emp.name='唐僧';
```
-- 查询唐僧的信息，显示员工id，姓名，性别，工资和所在的部门名称
```
SELECT e.id,e.name,e.gender,e.salary,d.name FROM emp e INNER JOIN dept d ON e.dept_id = d.id WHERE e.name='唐僧';
```
-- 表名有点长，可以给表取别名，显示的字段名也使用别名
```
SELECT e.id 编号,e.name 姓名,e.gender 性别,e.salary 工资,d.name 部门名字 FROM emp e INNER JOIN dept d ON e.dept_id = d.id WHERE e.name='唐僧';
```
##### *左外连接查询
-- 在部门表中增加一个销售部
```
INSERT INTO dept (NAME) VALUES ('销售部');
SELECT * FROM dept;
```
-- 使用左外连接查询,左匹配右,有数据则显示,无数据则为null
``SELECT * FROM dept d LEFT JOIN emp e ON d.id = e.dept_id;``
##### *右外连接查询
-- 在员工表中增加一个员工
`INSERT INTO emp VALUES (NULL, '沙僧','男',6666,'2013-12-05',NULL);`
-- 使用右外连接查询 ,右匹配左,有则显示,无则为空
`SELECT * FROM dept RIGHT JOIN emp ON dept.id = emp.dept_id;`
##### *使用子查询
`SELECT * FROM emp WHERE dept_id = (SELECT id FROM dept WHERE NAME='市场部');`
-- 1) 查询最高工资是多少
`SELECT MAX(salary) FROM emp;`
-- 2) 根据最高工资到员工表查询到对应的员工信息
`SELECT * FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);`
-- 3) 到员工表查询小于平均的员工信息
`SELECT * FROM emp WHERE salary < (SELECT AVG(salary) FROM emp);`
-- 查询工资大于5000 的员工，来自于哪些部门的名字
`SELECT NAME FROM dept WHERE id IN (SELECT dept_id FROM emp WHERE salary > 5000);`
-- 查询开发部与财务部所有的员工信息
`select * from emp where dept_id in (select id from dept where name in('开发部','财务部'));`
-- 查询出2011 年以后入职的员工信息，包括部门名称
`SELECT * FROM dept d, (SELECT * FROM emp WHERE join_date >='2011-1-1') e WHERE d.id= e.dept_id ;`
### 多表查询经典案例:
```
-- 部门表
CREATE TABLE dept (
id INT PRIMARY KEY PRIMARY KEY, -- 部门id
dname VARCHAR(50), -- 部门名称
loc VARCHAR(50) -- 部门所在地
);
-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');
-- 职务表，职务名称，职务描述
CREATE TABLE job (
id INT PRIMARY KEY,
jname VARCHAR(20),
description VARCHAR(50)
);
-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');
-- 员工表
CREATE TABLE emp (
id INT PRIMARY KEY, -- 员工id
ename VARCHAR(50), -- 员工姓名
job_id INT, -- 职务id
mgr INT , -- 上级领导
joindate DATE, -- 入职日期
salary DECIMAL(7,2), -- 工资
bonus DECIMAL(7,2), -- 奖金
dept_id INT, -- 所在部门编号
CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);
-- 工资等级表
CREATE TABLE salarygrade (
grade INT PRIMARY KEY, -- 级别
losalary INT, -- 最低工资
hisalary INT -- 最高工资
);
-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);
-- 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
SELECT
e.`id`, -- 员工编号
e.`ename`, -- 员工姓名
e.`salary`, -- 员工工资
j.`jname`, -- 职务名称
j.`description` -- 职务描述
FROM
emp e, job j
WHERE
e.`job_id`=j.`id`;
-- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
SELECT
e.`id`, -- 员工编号
e.`ename`, -- 员工姓名
e.`salary`, -- 员工工资
j.`jname`, -- 职务名称
j.`description`, -- 职务描述
d.`dname`, -- 部门名称
d.`loc` -- 部门位置
FROM
emp e, job j, dept d
WHERE
e.`job_id`=j.`id` AND e.`dept_id`=d.`id`;
-- 3.查询员工姓名，工资，工资等级
SELECT
e.`ename`,
e.`salary`,
s.*
FROM
emp e, salarygrade s
WHERE
e.`salary` BETWEEN s.`losalary` AND s.`hisalary`;
-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
SELECT
e.`ename`, -- 员工姓名
e.`salary`, -- 工资
j.`jname`, -- 职务名称
j.`description`, -- 职务描述
d.`dname`, -- 部门名称
d.`loc`, -- 部门位置
s.`grade` -- 工资等级
FROM
emp e, job j, dept d, salarygrade s
WHERE
e.`job_id`=j.`id` AND
e.`dept_id`=d.`id` AND
e.`salary` BETWEEN s.`losalary` AND s.`hisalary`;
-- 5.查询出部门编号、部门名称、部门位置、部门人数(此处用到表映射)
SELECT
d.`id`, -- 部门编号
d.`dname`, -- 部门名称
d.`loc`, -- 部门位置
t2.人数 -- 部门人数
FROM
dept d,
(SELECT dept_id,COUNT(id) 人数 FROM emp GROUP BY dept_id) t2
WHERE
d.`id`=t2.dept_id;
-- 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询
SELECT
e.`ename` 员工姓名,-- 员工姓名
e.`mgr`, -- 上级领导
e2.`id`, -- 领导id
e2.`ename` 上级领导 -- 领导
FROM emp e
LEFT JOIN emp e2
ON e.`mgr`=e2.`id`;
SELECT * FROM emp;
```
### 事务案例:
```
-- 创建数据表
CREATE TABLE account (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
balance DOUBLE
);
-- 添加数据
INSERT INTO account (NAME, balance) VALUES ('张三', 1000), ('李四', 1000);
-- 查询表
SELECT *FROM account;
-- 开启事务
START TRANSACTION;
-- 张三账号-500
UPDATE account SET balance = balance -500 WHERE NAME='张三';
-- 一旦此处发生错误,数据就会回滚
-- 李四账号+500
UPDATE account SET balance = balance +500 WHERE NAME='李四';
-- 回滚事务
ROLLBACK;
-- 提交事务
COMMIT;
-- 查看MySQL是否开启自动提交事务,1表示开启,0表示关闭
SELECT @@autocommit;
-- 取消自动提交事务
SET @@autocommit=0;
```
### 作业经典案例:
```
CREATE TABLE emp (
`empno` BIGINT(4) NOT NULL COMMENT '员工编号',
`ename` VARCHAR(20) NOT NULL COMMENT '姓名',
`job` VARCHAR(20) NOT NULL COMMENT '职位',
`mgr` BIGINT(4) DEFAULT NULL COMMENT '上级领导编号',
`hiredate` DATETIME DEFAULT NULL COMMENT '入职时间',
`sal` DECIMAL(10,2) NOT NULL COMMENT '工资',
`comm` DECIMAL(10,2) DEFAULT NULL COMMENT '奖金',
`deptno` VARCHAR(2) DEFAULT NULL COMMENT '部门编号'
) ENGINE=INNODB DEFAULT CHARSET=utf8;
-- alter table emp modify comm decimal(10,2)
INSERT INTO emp VALUES
(1001,'甘宁','文员',1013,'2000-12-07',8000.00,NULL,20),
(1002,'黛绮丝','销售员',1006,'2001-02-20',16000.00,3000,30),
(1003,'殷天正','销售员',1006,'2001-02-21',12500.00,5000,30),
(1004,'刘备','经理',1009,'2001-04-02',29750.00,NULL,20),
(1005,'谢逊','销售员',1006,'2001-09-28',12500.00,14000,30),
(1006,'关羽','经理',1009,'2001-02-20',28500.00,NULL,30),
(1007,'张飞','经理',1009,'2001-09-01',24500.00,NULL,10),
(1008,'诸葛亮','分析师',1004,'2007-04-19',30000.00,NULL,20),
(1009,'曾阿牛','董事长',NULL,'2001-11-17',50000.00,NULL,10),
(1010,'韦一笑','销售员',1006,'2001-09-08',15000.00,0,30),
(1011,'周泰','文员',1008,'2007-05-23',11000.00,NULL,20),
(1012,'程普','文员',1006, '2001-12-03',9500.00,NULL,30),
(1013,'庞统','分析师',1004, '2001-12-03',30000.00,NULL,20),
(1014,'黄盖','文员',1007, '2002-01-23',13000.00,NULL,10),
(1015,'张三','保洁员',1001,'2013-05-01',80000.00,50000,50);
CREATE TABLE dept (
`deptno` VARCHAR(2) DEFAULT NULL COMMENT '部门编号',
`dname` VARCHAR(20) NOT NULL COMMENT '部门名称',
`loc` VARCHAR(20) NOT NULL COMMENT '地址'
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO dept VALUES('10','教研部','北京');
INSERT INTO dept VALUES('20','学工部','上海');
INSERT INTO dept VALUES('30','销售部','广州');
INSERT INTO dept VALUES('40','财务部','武汉');
CREATE TABLE salgrade(
`grade` BIGINT(4) NOT NULL COMMENT '级别',
`losal` DECIMAL(10,2) COMMENT'最低工资',
`hisal` DECIMAL(10,2) COMMENT'最高工资'
)
INSERT INTO salgrade VALUES(1,7000,12000);
INSERT INTO salgrade VALUES(2,12010,14000);
INSERT INTO salgrade VALUES(3,14010,20000);
INSERT INTO salgrade VALUES(4,20010,30000);
INSERT INTO salgrade VALUES(5,30010,99990);
CREATE TABLE sallevel(
`job` VARCHAR(20) NOT NULL COMMENT '职位',
`grade` BIGINT(4) NOT NULL COMMENT '级别'
)DEFAULT CHARSET=utf8;
INSERT INTO sallevel VALUES('文员',1 );
INSERT INTO sallevel VALUES('销售员',2);
INSERT INTO sallevel VALUES('经理',3);
INSERT INTO sallevel VALUES('分析师',4);
INSERT INTO sallevel VALUES('董事长',5);
INSERT INTO sallevel VALUES('保洁员',5);
CREATE TABLE profit (
`year` BIGINT(4) COMMENT'年份',
`zz` BIGINT(4) COMMENT'利润率'
)
INSERT INTO profit VALUES(2010,100);
INSERT INTO profit VALUES(2011,150);
INSERT INTO profit VALUES(2012,250);
INSERT INTO profit VALUES(2013,800);
INSERT INTO profit VALUES(2014,1000);
-- 1. 查出至少有一个员工的部门。显示部门编号deptno、部门名称dname、部门位置loc、部门人数。
/*
查询的表: emp,dept
条件(关联关系)emp.deptno = dept.deptno
查询的字段:部门编号deptno、部门名称dname、部门位置loc、部门人数。
部门人数:需要根据部门进行分组group by ,聚合统计count(*)
注意:在emp表中有个部门编号是存在的,但是没有部门名称,需要用到左外连接展示emp表中的所有数据
*/
SELECT
dept.deptno 部门编号,
dept.dname 部门名称,
dept.loc 部门位置,
COUNT(*) 部门人数
FROM emp,dept
WHERE emp.deptno = dept.deptno
GROUP BY dept.deptno
-- 修改后(左外连接)
SELECT
emp.deptno 部门编号,
dept.dname 部门名称,
dept.loc 部门位置,
COUNT(*) 部门人数
FROM emp
LEFT JOIN dept
ON emp.deptno = dept.deptno
GROUP BY dept.deptno
-- 2. 列出所有员工的姓名及其直接上级的姓名
/*
查的表:emp,需要注意emp表中mgr就是上级的编号
条件:自关联 emp e1(员工表) emp e2(上级表) e1.mgr = e2.empno
查询的字段:员工的姓名e1.ename 上级的姓名e2.ename
*/
SELECT
e1.ename 员工的姓名,
e2.ename 上级的姓名
FROM emp e1,emp e2
WHERE e1.mgr = e2.empno
-- 修改后(左外连接)
SELECT
e1.ename 员工的姓名,
e2.ename 上级的姓名
FROM emp e1
LEFT JOIN emp e2
ON e1.mgr = e2.empno
-- 3. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称
/*
查的表:emp,需要注意emp表中mgr就是上级的编号 , 部门表dept
条件:自关联 emp e1(员工表) emp e2(上级表) e1.mgr = e2.empno
查询的字段:员工的姓名e1.ename 上级的姓名e2.ename 部门名称dept.dname
*/
-- 单表
SELECT
e1.empno 员工的编号,
e1.ename 员工的姓名,
e1.deptno
FROM emp e1
LEFT JOIN emp e2
ON e1.mgr = e2.empno
WHERE e1.hiredate < e2.hiredate
-- 优化(将上面查询的结果作为一张表继续跟部门表关联)
SELECT
dept.dname 部门名称,
e.*
FROM dept,
(SELECT
e1.empno 员工的编号,
e1.ename 员工的姓名,
e1.deptno
FROM emp e1
LEFT JOIN emp e2
ON e1.mgr = e2.empno
WHERE e1.hiredate < e2.hiredate) e
WHERE dept.deptno = e.deptno
-- 4. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
/*
查询的表:dept ,emp
条件: dept.deptno = emp .deptno 注意:需要查询部门表中的所有数据(把部门作为左表)
字段: dept.dname emp.*
*/
SELECT
dept.dname 部门名称,
emp.*
FROM dept
LEFT JOIN emp
ON dept.deptno = emp .deptno
-- 5. 列出最低薪金大于15000的各种工作及从事此工作的员工人数
/*
查表:emp
条件:薪金大于15000 注意:需要根据工种统计有多少人(分组group by job 统计count(*))
字段:工作emp.job
*/
SELECT job 工作, COUNT(*) 人数 FROM emp WHERE sal > 15000 GROUP BY job ;
SELECT job 工作, COUNT(*) 人数 FROM emp WHERE (sal+ IFNULL(comm,0)) > 15000 GROUP BY job ;
SELECT job 工作, COUNT(*) 人数 FROM emp GROUP BY job HAVING MIN(sal) > 15000;
SELECT job, COUNT(*) 总数 FROM emp GROUP BY job
HAVING job IN (SELECT s2.`job` FROM salgrade s1, sallevel s2 WHERE s1.`losal`>15000 AND s1.`grade`=s2.`grade`);
-- 优化
salgrade:工资等级表(最低工资和最高工资)
sallevel:工种等级表(工种,等级)
emp:统计人数
-- 第一步:salgrade 和 sallevel 关联关系salgrade.grade = sallevel.grade 可以查出每个工种的最低工资
SELECT * FROM salgrade, sallevel WHERE salgrade.grade = sallevel.grade
-- 第二步:查询最低工资大于15000的 salgrade.losal >15000
SELECT * FROM salgrade, sallevel WHERE salgrade.grade = sallevel.grade AND salgrade.losal >15000
-- 第三步:将上面查询到的数据作为一张表s,然后跟emp表关联(emp.job = s.job),再根据工种进行分组,统计有多少人
-- 注意:表中有重复字段的时候一定要排除
SELECT
emp.job 工作,
COUNT(*) 人数
FROM emp,
(SELECT sallevel.job FROM salgrade, sallevel WHERE salgrade.grade = sallevel.grade AND salgrade.losal >15000) s
WHERE emp.job = s.job
GROUP BY emp.job
-- 6. 列出在销售部工作的员工的姓名, 假定不知道销售部的部门编号
/*
查表:emp,dept
条件:emp.deptno = dept.deptno dept.dname ='销售部'
字段:员工的姓名emp.ename
*/
SELECT
emp.ename 员工姓名
FROM emp,dept
WHERE emp.deptno = dept.deptno AND dept.dname ='销售部';
-- 7. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级
-- 第一步:公司平均薪金
SELECT AVG(sal) FROM emp
-- 第二步:薪金高于平均薪金的员工信息
SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp);
-- 第三步:将上面查到的数据作为一张表s ,关联dept表(dept.deptno = s.deptno),注意需要所有员工(左表为s表)
SELECT
s.*,
dept.dname
FROM
(SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp)) s
LEFT JOIN dept
ON s.deptno =dept.deptno
-- 第四步:求上级领导姓名,将第三步的结果作为一张表(员工表)t ,关联emp(上级表)(t.mgr = emp.empno)
SELECT
t.*,
emp.ename 上级领导
FROM
(SELECT s.*,dept.dname 部门名称 FROM (SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp)) s
LEFT JOIN dept ON s.deptno =dept.deptno ) t
LEFT JOIN emp
ON t.mgr = emp.empno
-- 第五步:求工资等级,将第四步的结果作为一张表 r ,关联sallevel表(r.job = sallevel.job)
SELECT
r.*,
sallevel.grade 工资等级
FROM
(SELECT t.*,emp.ename 上级领导 FROM
(SELECT s.*,dept.dname 部门名称 FROM (SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp)) s
LEFT JOIN dept ON s.deptno =dept.deptno ) t
LEFT JOIN emp
ON t.mgr = emp.empno) r, sallevel
WHERE r.job = sallevel.job
-- 8. 列出与庞统从事相同工作的所有员工及部门名称
-- 第一步:查询庞统的工作 --结果是分析师
SELECT job FROM emp WHERE emp.ename = '庞统'
-- 第二步:查询emp中工作是分析师的所有员工
SELECT * FROM emp WHERE emp.job =(SELECT job FROM emp WHERE emp.ename = '庞统')
-- 第三步:将第二步的结果作为一张表 e 关联部门表dept 关联条件(e.deptno = dept.deptno)
SELECT
e.*,
dept.dname
FROM
(SELECT * FROM emp WHERE emp.job =(SELECT job FROM emp WHERE emp.ename = '庞统') )e, dept
WHERE e.deptno = dept.deptno
-- 9. 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称
-- 第一步:求出在部门30工作的最高薪金 查询结果是28500
SELECT MAX(sal) FROM emp WHERE emp.deptno = 30
-- 第二步:查询emp表中薪金大于28500的所有员工
SELECT emp.ename 员工姓名, emp.sal 薪金, emp.deptno FROM emp WHERE sal >(SELECT MAX(sal) FROM emp WHERE emp.deptno = 30)
-- 第三步: 将第二步的结果作为一张表 e 关联部门表dept 关联条件(e.deptno = dept.deptno)
SELECT
e.*,
dept.dname 部门名称
FROM (SELECT emp.ename 员工姓名, emp.sal 薪金, emp.deptno FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE emp.deptno = 30)) e
LEFT JOIN dept
ON e.deptno = dept.deptno
-- 10. 查出年份、利润、年度增长比( 年度增长比 = (今年-去年)/去年 *100% )
-- 内连接
SELECT *,(p1.zz-p2.zz)/p2.zz 年度增长比 FROM profit p1, profit p2 WHERE p1.year = p2.year + 1
-- 左外连接
SELECT *,(p1.zz-p2.zz)/p2.zz 年度增长比 FROM profit p1 LEFT JOIN profit p2 ON p1.year = p2.year + 1
-- 百分比效果的内连接
SELECT *,CONCAT((p1.zz-p2.zz)/p2.zz*100,'%') FROM profit p1, profit p2 WHERE p1.year = p2.year + 1
-- 百分比效果的左外连接
SELECT p1.year,CONCAT(
IFNULL(
ROUND((p1.zz-p2.zz)/p2.zz*100,2)
,0),
'%') 年度增长比
FROM profit p1 LEFT JOIN profit p2 ON p1.year = p2.year + 1
```
### DCL语句案例:
-- 创建超级用户
CREATE USER 'user1'@'localhost' IDENTIFIED BY '123';
-- 创建普通用户
CREATE USER 'user2'@'%' IDENTIFIED BY '123'
-- 给用户1授权对day03数据库的权限
GRANT CREATE,ALTER,INSERT,UPDATE,SELECT ON day03.* TO 'user1'@'localhost';
-- 给用户2赋予超级权限
GRANT ALL ON *.* TO 'user2'@'%';
-- 撤销user1对day03数据库的表的权限
REVOKE ALL ON day03.* FROM 'user1'@'localhost';
-- 查看权限
SHOW GRANTS FOR 'user1'@'localhost';
-- 删除用户
DROP USER 'user2'@'%';
-- 删除管理员密码
mysqladmin -urood -p PASSWORD 123456 -- cmd界面,此处无分号结尾
-- 修改普通用户密码
SET PASSWORD FOR 'user1'@'localhost'=123456; -- SQLyong界面,此处有分号结尾
扫一扫
4237
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
