2020-10-17sql练习2（很详细）

最新推荐文章于 2022-09-28 12:48:57 发布

吾名为王

最新推荐文章于 2022-09-28 12:48:57 发布

阅读量2k

点赞数 10

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_46029176/article/details/109134315

版权

数据库创建代码在（1）

基础查询

1.  显示表 departments 的结构，并查询其中的全部数据
DESC departments;
SELECT * FROM departments;

2.  显示出表 employees 中的全部 job_id（不能重复）
SELECT DISTINCT job_id FROM employees;

3.  显示出表 employees 的全部列，各个列之间用逗号连接，列头显示成 OUT_PUT
SELECT * FROM employees;
SELECT CONCAT(employee_id,",",first_name,",",last_name,",",email,",
",phone_number,",",job_id,",",salary,",",IFNULL(commission_pct,0),",",
IFNULL(manager_id,0),",",department_id,",",hiredate) out_put FROM employees;

4.  查询工资大于 12000 的员工姓名和工资
SELECT last_name,salary FROM employees WHERE salary>12000;

5.  查询员工号为 176 的员工的姓名和部门号和年薪
SELECT last_name,department_id,salary*12 年薪 FROM employees WHERE employee_id=176;

6.  选择工资不在 5000 到 12000 的员工的姓名和工资
SELECT last_name ,salary FROM employees WHERE salary NOT BETWEEN 5000 AND 12000;

7.  选择在 20 或 50 号部门工作的员工姓名和部门号
SELECT last_name,department_id FROM employees WHERE department_id IN(20,50);

8.  选择公司中没有管理者的员工姓名及 job_id
SELECT last_name,job_id FROM employees WHERE manager_id IS NULL;

9.  选择公司中有奖金的员工姓名，工资和奖金级别
SELECT last_name,salary,
CASE 
WHEN salary>18000 THEN 'A'
WHEN salary>9000 THEN 'B'
WHEN salary>4800 THEN 'C'
ELSE 'D'
END AS '工资级别'
FROM employees
WHERE commission_pct IS NOT NULL;

10.  选择员工姓名的第三个字母是 a 的员工姓名
SELECT last_name FROM employees WHERE last_name LIKE '__a%';

11.  选择姓名中有字母 a 和 e 的员工姓名
SELECT last_name FROM employees WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';

12.  显示出表 employees 中 first_name 以 'e'结尾的员工信息
SELECT * FROM employees WHERE first_name LIKE '%e';

13. 显示出表 employees 部门编号在 80-100 之间 的姓名、职位
SELECT last_name,department_id FROM employees WHERE department_id BETWEEN 80 AND 100;

14. #显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、职位
SELECT e.last_name,d.department_name,e.department_id FROM employees e JOIN departments d ON e.department_id=d.department_id WHERE e.manager_id IN(100,101,110);==#sql99==
SELECT e.manager_id, e.last_name,e.job_id,d.department_name FROM employees e,departments d WHERE e.manager_id IN (100,101,110) AND e.department_id=d.department_id;==#sql92==

15.  查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序
SELECT last_name,department_id,salary*12 年薪 FROM employees ORDER BY 年薪 DESC,last_name ASC; 

16.  选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序
SELECT last_name,salary FROM employees WHERE salary NOT BETWEEN 8000 AND 17000 ORDER BY salary DESC;

17.  查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT * FROM employees WHERE email LIKE '%e%' ORDER BY LENGTH(email) DESC,department_id ASC;

18.  显示系统时间(注：日期+时间)
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日%H时%i分%s秒') AS 当前时间;
SELECT NOW();

19.  查询员工号，姓名，工资，以及工资提高百分之 20%后的结果（NEW salary）
SELECT employee_id,last_name,salary,salary*1.2 'new salary' FROM employees;

20.  将员工的姓名按 首字母排序，并写出姓名的长度（LENGTH）
SELECT LENGTH(last_name) 长度,SUBSTR(last_name,1,1) 首字母,last_name FROM employees ORDER BY SUBSTR(last_name,1,1) ASC; 

21.  做一个查询，产生下面的结果<last_name> earns <salary> monthly but wants <salary*3>
SELECT CONCAT(last_name,' earns ',salary,' monthly but wants ',salary*3) 结果 FROM employees;

22.  员工工种级别 
SELECT last_name,job_id,
CASE 
WHEN job_id='AD_PRES' THEN 'A'
WHEN job_id='ST_MAN'  THEN 'B'
WHEN job_id='IT_PROG' THEN 'C'
WHEN job_id='SA_REP'  THEN 'D'
WHEN job_id='ST_CLERK' THEN 'E'
ELSE 'F'
END AS '工种级别'
FROM employees;

23.  查询各 job_id 的员工工资的最大值，最小值，平均值，总和，并按 job_id 升序
SELECT job_id,MAX(salary) 最大值,MIN(salary) 最小值,AVG(salary) 平均值,SUM(salary) 总和 FROM employees GROUP BY job_id ORDER BY job_id ASC;

24.  查询员工最高工资和最低工资的差距（DIFFERENCE）
SELECT MAX(salary)-MIN(salary) DIFFERENCE FROM employees;

25.  查询各个管理者手下员工的最低工资，其中最低工资不能低于 6000，没有管理者的员工不计算在内
SELECT MIN(salary) 最底工资 FROM employees WHERE manager_id IS NOT NULL GROUP BY manager_id HAVING MIN(salary)>6000;
#注意select查询列的别名不能用

26.  查询所有部门的编号，员工数量和工资平均值,并按平均工资降序
SELECT department_id,COUNT(*),AVG(salary) FROM employees GROUP BY department_id ORDER BY AVG(salary) DESC;

27.  选择具有各个 job_id 的员工人数
SELECT COUNT(*) FROM employees GROUP BY job_id;

28.  查询编号>3 的女神的男朋友信息，如果有则列出详细，如果没有，用 NULL 填充
SELECT * FROM boys b RIGHT JOIN beauty g ON b.`id`=g.boyfriend_id WHERE g.id>3;

29.  查询哪个城市没有部门
SELECT * FROM locations l LEFT OUTER JOIN departments d ON l.location_id=d.location_id WHERE d.department_id IS NULL;

注：

SELECT * FROM locations l JOIN  departments d  ON  d.location_id=l.location_id ;#没有left或right，查询出来的数据是两表连接条件成立的部分。
如果加了left或者right则查询出来的数据是left左边表的全部数据，left右边的表如果没有数据则全显示null。right同理。

比如：
不加left或者right这里查询出来的就是两表连接条件相同的在这里插入图片描述加了right显示的是right右边表的所有信息：

30.  查询部门名为 SAL 或 IT 的员工信息
SELECT e.*,d.department_name FROM employees e INNER JOIN departments d ON e.department_id=d.department_id WHERE d.department_name IN('SAL','IT');#==注意in(),括号里面的需要加引号==

31.  显示所有员工的姓名，部门号和部门名称。
SELECT e.last_name 姓名,e.department_id 部门号,d.department_name 
FROM employees e 
INNER JOIN departments d
ON e.department_id=d.department_id;

32.  查询 90 号部门员工的 job_id 和 90 号部门的 location_id
SELECT e.job_id,l.location_id
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
INNER JOIN locations l
ON d.location_id=l.location_id
WHERE e.department_id=90;

33. 选择所有有奖金的员工的 last_name , department_name , location_id , city。
SELECT e.last_name,d.department_name,l.location_id,l.city
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
INNER JOIN locations l
ON d.location_id=l.location_id
WHERE e.commission_pct IS NOT NULL;

34.  查询city在Toronto工作的员工的 last_name , job_id , department_id , department_name。
SELECT e.last_name,e.job_id,e.department_id,d.department_name,l.city
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
INNER JOIN locations l
ON d.location_id=l.location_id
WHERE l.city='Toronto';

35.  查询每个工种的工种名和最低工资
SELECT e.job_id,MIN(salary) FROM employees e GROUP BY e.job_id; 

36.  查询每个国家下的部门个数大于 2 的国家编号
SELECT l.country_id,COUNT(d.department_id) 部门数
FROM locations l 
INNER JOIN departments d
ON l.location_id=d.location_id 
GROUP BY l.country_id
HAVING COUNT(d.department_id)>2;

关于使用group by查询出来的数据：
相当于去重group by后面的字段，其余数据显示第一条

在这里插入图片描述

37.  查询和 Zlotkey 相同部门的员工姓名和工资
SELECT last_name,salary FROM employees WHERE department_id=(SELECT department_id FROM employees WHERE last_name='Zlotkey')

38.  查询工资比公司平均工资高的员工的员工号，姓名和工资。
SELECT employee_id,last_name,salary FROM employees WHERE salary>(SELECT AVG(salary) FROM employees) ORDER BY salary ASC;

39.  查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
SELECT department_id,AVG(salary) FROM employees GROUP BY department_id;
SELECT e1.employee_id,e1.last_name,e1.salary,e1.department_id
FROM employees e1,(SELECT AVG(e2.salary) ag,e2.department_id dep_id FROM employees e2 GROUP BY e2.department_id) e3 
WHERE e1.salary>e3.ag
AND e1.department_id=e3.dep_id;

注：

我在39题栽了跟头，所以来做个总结。--关于在一张表中需要通过分组求平均值，再根据分组与平均值比较，就需要原始表跟==“新表”==（你查询的数据需要作为一张表并且把分组条件加进去）联合查询。这样想就比较好理解。

妈的表述能力太差了，直接上图：
在这里插入图片描述

40.  查询和姓名中包含字母 u 的员工在相同部门的员工的员工号和姓名
SELECT DISTINCT department_id FROM employees WHERE last_name LIKE '%u%';
SELECT employee_id,last_name FROM employees WHERE department_id IN (SELECT DISTINCT department_id FROM employees WHERE last_name LIKE '%u%');

今天mysql就先练习这么多，明天继续！

2020-10-18今天继续

库的管理

#1、库的创建
/*
语法：
create database  [if not exists]库名;
*/

#2、库的修改
RENAME DATABASE books TO 新库名;

#更改库的字符集
ALTER DATABASE books CHARACTER SET gbk;

#3、库的删除
DROP DATABASE IF EXISTS books;

表的管理

/*
语法：
create table 表名(
	列名 列的类型【(长度) 约束】,
	列名 列的类型【(长度) 约束】,
	列名 列的类型【(长度) 约束】,
	...
	列名 列的类型【(长度) 约束】
)
*/

#2.表的修改
/*
语法
alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;
*/

#①修改列名
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;

#②修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;

#③添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE; 

#④删除列
ALTER TABLE book_author DROP COLUMN  annual;

#⑤修改表名
ALTER TABLE author RENAME TO book_author;

DESC book;

#3.表的删除
DROP TABLE IF EXISTS book_author;

#4.表的复制
#1.仅仅复制表的结构
CREATE TABLE （新表名）LIKE author;

#2.复制表的结构+数据
CREATE TABLE （新表名） SELECT * FROM author;

约束

含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性


分类：六大约束
	NOT NULL：非空，用于保证该字段的值不能为空
	比如姓名、学号等
	DEFAULT:默认，用于保证该字段有默认值
	比如性别
	PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空
	比如学号、员工编号等
	UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空
	比如座位号
	CHECK:检查约束【mysql中不支持】
	比如年龄、性别
	FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值
		在从表添加外键约束，用于引用主表中某列的值
	比如学生表的专业编号，员工表的部门编号，员工表的工种编号

主键和唯一的大对比：

		保证唯一性  是否允许为空    一个表中可以有多少个      是否允许组合
主键	       √		  ×		        至多有1个            √，但不推荐
唯一	       √		  √		        可以有多个           √，但不推荐

#一.添加约束

#通用的写法：★
CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,
	stuname VARCHAR(20),
	sex CHAR(1),
	age INT DEFAULT 18,
	seat INT UNIQUE,
	majorid INT,
	CONSTRAINT fk_stuinfo_major(给外键起的别名) FOREIGN KEY(majorid) REFERENCES major(id)   #注：majorid：本表的字段。major(id) ：指的是要关联的表的字段
);

#二、修改表时添加约束
/*
1、添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;

2、添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;
*/

例：
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT
)
DESC stuinfo;
#1.添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20)  NOT NULL;

#2.添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;

#3.添加主键
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
#②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);

#4.添加唯一
#①列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#②表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);

#5.添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id); 

#三、修改表时删除约束

#1.删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

#2.删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT ;

#3.删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;

#4.删除唯一
ALTER TABLE stuinfo DROP INDEX seat;

#5.删除外键
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

练习

42. 查询管理者是 King 的员工姓名和工资
SELECT employee_id FROM employees WHERE last_name='K_ing';
SELECT last_name,salary,manager_id FROM employees WHERE manager_id IN(SELECT employee_id FROM employees WHERE last_name='K_ing');
SELECT * FROM employees e JOIN employees e2 ON e.`employee_id`=e2.`manager_id` WHERE e.`last_name`='K_ing';

43. 查询工资最高的员工的姓名，要求 first_name 和 last_name 显示为一列，列名为 姓.名
SELECT CONCAT(first_name,last_name) '姓名' FROM employees WHERE salary=(SELECT MAX(salary) FROM employees);

44. 创建员工表
CREATE TABLE my_employees(
Id INT(10),
First_name VARCHAR(10),
Last_name VARCHAR(10),
Userid VARCHAR(10),
Salary DOUBLE(10,2)
);

45. 创建用户表
CREATE TABLE users(
id INT,
userid VARCHAR(10),
department_id INT
)

46. 向 my_employees 表中插入下列数据
INSERT INTO my_employees
VALUES(1,'patel','Ralph','Rpatel',895),
(2,'Dancs','Betty','Bdancs',860),
(3,'Biri','Ben','Bbiri',1100),
(4,'Newman','Chad','Cnewman',750),
(5,'Ropeburn','Audrey','Aropebur',1550);

47. 向 users 表中插入数据
INSERT INTO users
VALUES(1,'Rpatel',10),
(2,'Bdancs',10),
(3,'Bbiri',20),
(4,'Cnewman',30),
(5,'Aropebur',40);

48. 将 3 号员工的 last_name 修改为“drelxer”
UPDATE my_employees SET last_name='drelxer' WHERE Id=3;#update不加from

49. 将所有工资少于 900 的员工的工资修改为 1000
UPDATE my_employees SET Salary=1000 WHERE Salary<900;

50. 将 userid 为 Bbiri 的 USER 表和 my_employees 表的记录全部删除
DELETE FROM my_employees WHERE Userid='Bbiri';
DELETE FROM users WHERE Userid='Bbiri';#delete加from

51. 删除所有数据
方式一：
DELETE FROM my_employees;
DELETE FROM users;
方式二：
TRUNCATE my_employees;
TRUNCATE users;

52. 创建dept1
CREATE TABLE dept1(
   NAME VARCHAR(25) NOT NULL,
   id INT(7)
);

INSERT INTO dept1 (id,NAME) SELECT d.department_id,d.department_name FROM departments d;

CREATE TABLE emp5(
   id INT(7),
    First_name VARCHAR(25) NOT NULL,
    Last_name VARCHAR(25),
    Dept_id INT(7)
);

53.  将列 Last_name 的长度增加到 50
ALTER TABLE emp5 MODIFY Last_name VARCHAR(50); 

54.  根据表 employees 创建 employees2
CREATE TABLE employees2 LIKE myemployees.employees;

55.  删除表 emp5
DROP TABLE emp5;

56.  将表 employees2 重命名为 emp5
方式一：
ALTER TABLE employees2 RENAME TO emp5;
方式二：
ALTER TABLE emp5 RENAME emp2;

57.  在表 dept1 和 emp5 中添加新列 test_column，并检查所作的操作
ALTER TABLE dept1 ADD COLUMN test_column VARCHAR(20);
ALTER TABLE emp5 ADD COLUMN test_column VARCHAR(20);

58.  直接删除表 emp5 中的列 dept_id
ALTER TABLE emp5 DROP test_column;

59.  向表 emp2 的 id 列中添加 PRIMARY KEY 约束（my_emp_id_pk）
ALTER TABLE emp2 MODIFY COLUMN employee_id INT PRIMARY KEY;

60.  向表 dept2 的 id 列中添加 PRIMARY KEY 约束（my_dept_id_pk）
ALTER TABLE dept1 MODIFY COLUMN id INT PRIMARY KEY;

61.  向表 emp2 中添加列 dept_id，并在其中定义 FOREIGN KEY 约束，与之相关联的列是dept2 表中的 id 列。
ALTER TABLE emp2 ADD COLUMN dept_id INT;
ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_dept2 FOREIGN KEY(dept_id) REFERENCES dept1(id);

ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_dept2 FOREIGN KEY(dept_id) REFERENCES dept1(id);

62.  删除外键
ALTER TABLE emp2 DROP  FOREIGN KEY fk_emp2_dept2;

63.  创建一个表，里面有 id 为主键，stuname 唯一键，seat 座位号，要求将 id 设置成自增
CREATE TABLE stu(
   id INT PRIMARY KEY AUTO_INCREMENT,
   stuname VARCHAR(20) UNIQUE,
   seat INT
);

64.  要求用事务的方式插入 3 行数据
SHOW VARIABLES LIKE "%commit%"
SET autocommit=0;
INSERT INTO stu(stuname,seat) 
VALUES('jack',12),
('reos',13),
('make',14);
SELECT * FROM stu;
COMMIT;

65.  要求用事务的方式删除数据，并回滚
SET autommit=0; #开启事务
DELETE FROM stu;
SELECT * FROM stu;
ROLLBACK;

事务

#TCL
/*
Transaction Control Language 事务控制语言

事务：
一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。

案例：转账
张三丰  1000
郭襄	1000

update 表 set 张三丰的余额=500 where name='张三丰'
意外
update 表 set 郭襄的余额=1500 where name='郭襄'

事务的特性：
ACID
原子性：一个事务不可再分割，要么都执行要么都不执行
一致性：一个事务执行会使数据从一个一致状态切换到另外一个一致状态
隔离性：一个事务的执行不受其他事务的干扰
持久性：一个事务一旦提交，则会永久的改变数据库的数据.

事务的创建
隐式事务：事务没有明显的开启和结束的标记
比如insert、update、delete语句

delete from 表 where id =1;

显式事务：事务具有明显的开启和结束的标记
前提：必须先设置自动提交功能为禁用

set autocommit=0;

步骤1：开启事务
set autocommit=0;
start transaction;可选的
步骤2：编写事务中的sql语句(select insert update delete)
语句1;
语句2;
...

步骤3：结束事务
commit;提交事务
rollback;回滚事务
savepoint 节点名;设置保存点

事务的隔离级别：
		    					脏读		不可重复读		幻读
read uncommitted：				√			√			√
read committed：  				×			√			√
repeatable read： 				×			×			√
serializable	 				×           ×           ×

mysql中默认 第三个隔离级别 repeatable read
oracle中默认第二个隔离级别 read committed
查看隔离级别
select @@tx_isolation;
设置隔离级别
set session|global transaction isolation level 隔离级别;

开启事务的语句;
update 表 set 张三丰的余额=500 where name='张三丰'

update 表 set 郭襄的余额=1500 where name='郭襄' 
结束事务的语句;
*/

视图

#一、创建视图
/*
语法：
create view 视图名
as
查询语句;
*/
#二、视图的修改

#方式一：
/*
create or replace view  视图名
as
查询语句;
*/

#方式二：
/*
语法：
alter view 视图名
as 
查询语句;
*/

#三、删除视图

/*
语法：drop view 视图名,视图名,...;
*/

存储过程

#存储过程和函数
/*
存储过程和函数：类似于java中的方法
好处：
1、提高代码的重用性
2、简化操作

*/
#存储过程
/*
含义：一组预先编译好的SQL语句的集合，理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
*/

#一、创建语法

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
	存储过程体（一组合法的SQL语句）
END

#注意：
/*
1、参数列表包含三部分
参数模式  参数名  参数类型
举例：
in stuname varchar(20)

参数模式：
in：该参数可以作为输入，也就是该参数需要调用方传入值
out：该参数可以作为输出，也就是该参数可以作为返回值
inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

2、如果存储过程体仅仅只有一句话，begin end可以省略
存储过程体中的每条sql语句的结尾要求必须加分号。
存储过程的结尾可以使用 delimiter 重新设置
语法：
delimiter 结束标记
案例：
delimiter $
*/

#二、调用语法

CALL 存储过程名(实参列表);

#三、删除存储过程

#语法：drop procedure 存储过程名
DROP PROCEDURE p1;
DROP PROCEDURE p2,p3; #×错误写法

练习

66.  创建视图 emp_v1,要求查询电话号码以‘011’开头的员工姓名和工资、邮箱
CREATE VIEW emp_v1 
AS 
	SELECT last_name,salary,email FROM employees WHERE phone_number LIKE '011%';

SELECT * FROM emp_v1 WHERE last_name='Jack';

DROP VIEW emp_v1;

67.  要求将视图 emp_v1 修改为查询电话号码以‘011’开头的并且邮箱中包含 e 字符的员工姓名和邮箱、电话号码

67.  向 emp_v1 插入一条记录，是否可以？
INSERT INTO emp_v1 VALUES('张飞1133',7868,'Tencet');

68.  修改刚才记录中的姓名为‘yang’
UPDATE emp_v1 SET last_name='yang';

69.  删除刚才记录
DELETE FROM emp_v1;

70.  创建视图 emp_v2，要求查询部门的最高工资高于 12000 的部门信息
CREATE OR REPLACE VIEW emp_v2 AS
SELECT MAX(salary),department_id FROM employees GROUP BY department_id HAVING MAX(salary)>12000;

SELECT d.* FROM departments d INNER JOIN emp_v2 ev2 ON d.department_id=ev2.department_id;
SELECT d.* FROM departments d WHERE d.department_id IN (SELECT department_id FROM emp_v2);

71.  向 emp_v2 中插入一条记录，是否可以？
不可以，因为emp_v2中带有分组函数

72.  删除刚才的 emp_v2 和 emp_v1
DROP VIEW emp_v1,emp_v2;

73.  创建存储过程或函数实现传入用户名和密码，插入到 admin 表中
CREATE TABLE IF NOT EXISTS admin(
   username VARCHAR(20),
   pwd VARCHAR(20)
);
#修改sql结束符
DELIMITER $
#存储过程
CREATE PROCEDURE pro1(IN username VARCHAR(20),IN pwd VARCHAR(20))
BEGIN
    INSERT INTO admin VALUES(username,pwd);
END$
#调用pro1
CALL pro1('yang','123456')$

#函数
CREATE FUNCTION fun1(username VARCHAR(20),pwd VARCHAR(20)) RETURNS INT
BEGIN
    DECLARE result INT DEFAULT 0;#默认值
    INSERT INTO admin VALUES(username,pwd);
    result=1;
    RETURN result;
END$

#调用fun1
SELECT fun1('ming','123456');

74.  创建存储过程或函数实现传入女神编号，返回女神名称和女神电话
#存储过程
CREATE PROCEDURE pro2(IN id INT)
BEGIN
    SELECT NAME,phone FROM beauty b WHERE b.id=id; 
END$
#函数
CREATE FUNCTION fun2(id INT) RETURNS VARCHAR(50)
BEGIN
    DECLARE result VARCHAR(50) DEFAULT "";
    SELECT CONCAT(NAME,',',phone) INTO result FROM beauty b WHERE b.id=id;
    RETURN result;
END$

75.  创建存储存储过程或函数实现传入两个女神生日，返回大小
#存储过程
CREATE PROCEDURE pro3(IN n1 DATETIME,IN n2 DATETIME,OUT result INT)
BEGIN
    SELECT DATEDIFF(n1,n2) INTO result;
END$
CALL pro3('1993-02-03 00:00:00','1992-02-03 00:00:00',@result)$
SELECT @result$
#函数
CREATE FUNCTION fun3(n1 DATETIME,n2 DATETIME) RETURNS INT
BEGIN
    DECLARE result INT DEFAULT 0;
    SELECT DATEDIFF(n1,n2) INTO result;
    RETURN result;
END$
SELECT fun3('1993-02-03 00:00:00','1992-02-03 00:00:00')$

子查询练习

# 1. 查询工资最低的员工信息: last_name, salary

#①查询最低的工资
SELECT MIN(salary)
FROM employees

#②查询last_name,salary，要求salary=①
SELECT last_name,salary
FROM employees
WHERE salary=(
	SELECT MIN(salary)
	FROM employees
);

# 2. 查询平均工资最低的部门信息

#方式一：
#①各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#②查询①结果上的最低平均工资
SELECT MIN(ag)
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) ag_dep

#③查询哪个部门的平均工资=②
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
	SELECT MIN(ag)
	FROM (
		SELECT AVG(salary) ag,department_id
		FROM employees
		GROUP BY department_id
	) ag_dep

);

#④查询部门信息
SELECT d.*
FROM departments d
WHERE d.`department_id`=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	HAVING AVG(salary)=(
		SELECT MIN(ag)
		FROM (
			SELECT AVG(salary) ag,department_id
			FROM employees
			GROUP BY department_id
		) ag_dep

	)

);

#方式二：
#①各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

#②求出最低平均工资的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) 
LIMIT 1;

#③查询部门信息
SELECT *
FROM departments
WHERE department_id=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary) 
	LIMIT 1
);

# 3. 查询平均工资最低的部门信息和该部门的平均工资
#①各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#②求出最低平均工资的部门编号
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) 
LIMIT 1;
#③查询部门信息
SELECT d.*,ag
FROM departments d
JOIN (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary) 
	LIMIT 1
) ag_dep
ON d.`department_id`=ag_dep.department_id;

# 4. 查询平均工资最高的 job 信息
#①查询最高的job的平均工资
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY AVG(salary) DESC
LIMIT 1

#②查询job信息
SELECT * 
FROM jobs
WHERE job_id=(
	SELECT job_id
	FROM employees
	GROUP BY job_id
	ORDER BY AVG(salary) DESC
	LIMIT 1
);

# 5. 查询平均工资高于公司平均工资的部门有哪些?

#①查询平均工资
SELECT AVG(salary)
FROM employees

#②查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

#③筛选②结果集，满足平均工资>①

SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)>(
	SELECT AVG(salary)
	FROM employees
);

# 6. 查询出公司中所有 manager 的详细信息.
#①查询所有manager的员工编号
SELECT DISTINCT manager_id
FROM employees

#②查询详细信息，满足employee_id=①
SELECT *
FROM employees
WHERE employee_id =ANY(
	SELECT DISTINCT manager_id
	FROM employees
);

# 7. 各个部门中 最高工资中最低的那个部门的 最低工资是多少

#①查询各部门的最高工资中最低的部门编号
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary)
LIMIT 1


#②查询①结果的那个部门的最低工资

SELECT MIN(salary) ,department_id
FROM employees
WHERE department_id=(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY MAX(salary)
	LIMIT 1
);

# 8. 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
#①查询平均工资最高的部门编号
SELECT 
    department_id 
FROM
    employees 
GROUP BY department_id 
ORDER BY AVG(salary) DESC 
LIMIT 1 

#②将employees和departments连接查询，筛选条件是①
    SELECT 
        last_name, d.department_id, email, salary 
    FROM
        employees e 
        INNER JOIN departments d 
            ON d.manager_id = e.employee_id 
    WHERE d.department_id = 
        (SELECT 
            department_id 
        FROM
            employees 
        GROUP BY department_id 
        ORDER BY AVG(salary) DESC 
        LIMIT 1) ;

题目来自 https://blog.csdn.net/Duckdan/article/details/81783231