mysql作业

一、单表查询练习

/* 素材

CREATE TABLE emp (

empno int(4) NOT NULL,

ename varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

job varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

mgr int(4) NULL DEFAULT NULL,

hiredate date NOT NULL,

sai int(255) NOT NULL,

comm int(255) NULL DEFAULT NULL,

deptno int(2) NOT NULL,

PRIMARY KEY ( empno ) USING BTREE

) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO emp VALUES (1001, ' 甘宁 ', ' 文员 ', 1013, '2000-12-17', 8000, NULL, 20);

INSERT INTO emp VALUES (1002, ' 黛绮丝 ', ' 销售员 ', 1006, '2001-02-20', 16000, 3000, 30);

INSERT INTO emp VALUES (1003, ' 殷天正 ', ' 销售员 ', 1006, '2001-02-22', 12500, 5000, 30);

INSERT INTO emp VALUES (1004, ' 刘备 ', ' 经理 ', 1009, '2001-04-02', 29750, NULL, 20);

INSERT INTO emp VALUES (1005, ' 谢逊 ', ' 销售员 ', 1006, '2001-09-28', 12500, 14000, 30);

INSERT INTO emp VALUES (1006, ' 关羽 ', ' 经理 ', 1009, '2001-05-01', 28500, NULL, 30);

INSERT INTO emp VALUES (1007, ' 张飞 ', ' 经理 ', 1009, '2001-09-01', 24500, NULL, 10);

INSERT INTO emp VALUES (1008, ' 诸葛亮 ', ' 分析师 ', 1004, '2007-04-19', 30000, NULL, 20);

INSERT INTO emp VALUES (1009, ' 曾阿牛 ', ' 董事长 ', NULL, '2001-11-17', 50000, NULL, 10);

INSERT INTO emp VALUES (1010, ' 韦一笑 ', ' 销售员 ', 1006, '2001-09-08', 15000, 0, 30);

INSERT INTO emp VALUES (1011, ' 周泰 ', ' 文员 ', 1006, '2007-05-23', 11000, NULL, 20);

INSERT INTO emp VALUES (1012, ' 程普 ', ' 文员 ', 1006, '2001-12-03', 9500, NULL, 30);

INSERT INTO emp VALUES (1013, ' 庞统 ', ' 分析师 ', 1004, '2001-12-03', 30000, NULL, 20);

INSERT INTO emp VALUES (1014, ' 黄盖 ', ' 文员 ', 1007, '2002-01-23', 13000, NULL, 10);

INSERT INTO emp VALUES (1015, ' 张三 ', ' 保洁员 ', 1001, '2013-05-01', 80000, 50000, 50);

-- 1. 查询出部门编号为 30 的所有员工

SELECT * FROM emp WHERE deptno = 30;

-- 2. 所有销售员的姓名、编号和部门编号。

SELECT ename, empno, deptno FROM emp WHERE job = '销售员';

-- 3. 找出奖金高于工资的员工。

SELECT * FROM emp WHERE comm > sai;

-- 4. 找出奖金高于工资 60% 的员工。

SELECT * FROM emp WHERE comm > sai * 0.6;

-- 5. 找出部门编号为 10 中所有经理，和部门编号为 20 中所有销售员的详细资料。

SELECT * FROM emp WHERE (deptno = 10 AND job = '经理') OR (deptno = 20 AND job = '销售员');

-- 6. 找出部门编号为 10 中所有经理，部门编号为 20 中所有销售员，还有即不是经理又不是销售员但其工 资大或等于20000 的所有员工详细资料。

SELECT * FROM emp WHERE (deptno = 10 AND job = '经理') OR (deptno = 20 AND job = '销售员') OR (job NOT IN ('经理', '销售员') AND sai >= 20000);

-- 7. 无奖金或奖金低于 1000 的员工。

SELECT * FROM emp WHERE comm IS NULL OR comm < 1000;

-- 8. 查询名字由三个字组成的员工。

SELECT * FROM emp WHERE LENGTH(ename) = 9; -- 假设一个汉字占三个字节

-- 注意：一个汉字占三个字节

-- 9. 查询 2000 年入职的员工。

SELECT * FROM emp WHERE YEAR(hiredate) = 2000;

-- 10. 查询所有员工详细信息，用编号升序排序

SELECT * FROM emp ORDER BY empno ASC;

-- 11. 查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序 --

SELECT * FROM emp ORDER BY sai DESC, hiredate ASC;

12. 查询每个部门的平均工资

SELECT deptno, AVG(sai) AS average_salary FROM emp GROUP BY deptno;

-- 13. 查询每个部门的雇员数量

SELECT deptno, COUNT(*) AS employee_count FROM emp GROUP BY deptno;

-- 14. 查询每种工作的最高工资、最低工资、人数

SELECT job, MAX(sai) AS max_salary, MIN(sai) AS min_salary, COUNT(*) AS employee_count FROM emp GROUP BY job;

二、多表连接查询

use mydb3;

-- 创建部门表

create table if not exists dept3(

deptno varchar(20) primary key , -- 部门号

name varchar(20) -- 部门名字

);

-- 创建员工表

create table if not exists emp3(

eid varchar(20) primary key , -- 员工编号

ename varchar(20), -- 员工名字

age int, -- 员工年龄

dept_id varchar(20) -- 员工所属部门

);

-- 给 dept3 表添加数据

insert into dept3 values('1001',' 研发部 ');

insert into dept3 values('1002',' 销售部 ');

insert into dept3 values('1003',' 财务部 ');

insert into dept3 values('1004',' 人事部 ');

-- 给 emp3 表添加数据

insert into emp3 values('1',' 乔峰 ',20, '1001');

insert into emp3 values('2',' 段誉 ',21, '1001');

insert into emp3 values('3',' 虚竹 ',23, '1001');

insert into emp3 values('4',' 阿紫 ',18, '1001');

insert into emp3 values('5',' 扫地僧 ',85, '1002');

insert into emp3 values('6',' 李秋水 ',33, '1002');

insert into emp3 values('7',' 鸠摩智 ',50, '1002');

insert into emp3 values('8',' 天山童姥 ',60, '1003');

insert into emp3 values('9',' 慕容博 ',58, '1003');

insert into emp3 values('10',' 丁春秋 ',71, '1005');

-- 创建部门表
create table if not exists dept3(
deptno varchar(20) primary key , -- 部门号
name varchar(20) -- 部门名字
);

-- 创建员工表
create table if not exists emp3(
eid varchar(20) primary key , -- 员工编号
ename varchar(20), -- 员工名字
age int, -- 员工年龄
dept_id varchar(20) -- 员工所属部门
);

-- 给dept3表添加数据
insert into dept3 values('1001','研发部');
insert into dept3 values('1002','销售部');
insert into dept3 values('1003','财务部');
insert into dept3 values('1004','人事部');

-- 给emp3表添加数据
insert into emp3 values('1','乔峰',20, '1001');
insert into emp3 values('2','段誉',21, '1001');
insert into emp3 values('3','虚竹',23, '1001');
insert into emp3 values('4','阿紫',18, '1001');
insert into emp3 values('5','扫地僧',85, '1002');
insert into emp3 values('6','李秋水',33, '1002');
insert into emp3 values('7','鸠摩智',50, '1002');
insert into emp3 values('8','天山童姥',60, '1003');
insert into emp3 values('9','慕容博',58, '1003');
insert into emp3 values('10','丁春秋',71, '1005');

1 、查询每个部门的所属员工

SELECT d.name AS department, e.ename AS employee
FROM dept3 d
JOIN emp3 e ON d.deptno = e.dept_id;

2 、查询研发部门的所属员工

SELECT e.ename AS employee
FROM dept3 d
JOIN emp3 e ON d.deptno = e.dept_id
WHERE d.name = '研发部';

3 、查询研发部和销售部的所属员工

SELECT d.name AS department, e.ename AS employee
FROM dept3 d
JOIN emp3 e ON d.deptno = e.dept_id
WHERE d.name IN ('研发部', '销售部');

4 、查询每个部门的员工数 , 并升序排序

SELECT d.name AS department, COUNT(e.eid) AS employee_count
FROM dept3 d
LEFT JOIN emp3 e ON d.deptno = e.dept_id
GROUP BY d.name
ORDER BY employee_count ASC;

5 、查询人数大于等于 3 的部门，并按照人数降序排序

SELECT d.name AS department, COUNT(e.eid) AS employee_count
FROM dept3 d
LEFT JOIN emp3 e ON d.deptno = e.dept_id
GROUP BY d.name
HAVING employee_count >= 3
ORDER BY employee_count DESC;