单多表查询-CSDN博客

本文链接：https://blog.csdn.net/weixin_57611136/article/details/131645923

DQL练习1-学生表
创建如下学生表

create table student(
id int,
name varchar(20),
gender varchar(20),
chinese int,
math int,
english int
);		
 
insert into student values 
(1,'张明','男',89,78,90),
(2,'李进','男',67,53,95),
(3,'王五','女',87,78,77),
(4,'李一','女',88,98,92),
(5,'李财','男',82,84,67),
(6,'张宝','男',55,85,45),
(7,'黄蓉','女',75,65,30),	
(7,'黄蓉','女',75,65,30);	
 完成下列要求

-- 查询表中所有学生的信息
SELECT * FROM student;
-- 查询表中所有学生的姓名和英语成绩

SELECT name, english FROM student;
– 过滤表中的重复数据
SELECT DISTINCT * FROM student;
– 统计每个学生的总分
SELECT id, name, chinese + math + english AS total_score FROM student;
– 所有学生总分上加上10
SELECT id, name, chinese + math + english + 10 AS total_score FROM student;
– 使用别名表示学生分数
SELECT id, name, (chinese + math + english) AS total_score FROM student;
– 查询英语成绩大于90分的所有同学
SELECT * FROM student WHERE english > 90;
– 查询总分大于200分的所有同学
SELECT * FROM student WHERE chinese + math + english > 200;
– 查询英语成绩在80到90之间的所有同学
SELECT * FROM student WHERE english BETWEEN 80 AND 90;
– 查询英语成绩不在80到90之间的所有同学
SELECT * FROM student WHERE english NOT BETWEEN 80 AND 90;
– 查询数学分数为89，90，91的同学
SELECT * FROM student WHERE math IN (89, 90, 91);
– 查询数学分数不为89,90,91的同学
SELECT * FROM student WHERE math NOT IN (89, 90, 91);
– 查询所有姓李的学生英语成绩
SELECT name, english FROM student WHERE name LIKE ‘李%’;
– 查询数学分80且语文80分的同学
SELECT * FROM student WHERE math = 80 AND chinese = 80;
– 查询英语80或者总分200的同学
SELECT * FROM student WHERE english = 80 OR chinese + math + english = 200;
– 对学生数学成绩进行降序排序后输出
SELECT * FROM student ORDER BY math DESC;
– 对总分从高到低的顺序输出
SELECT * FROM student ORDER BY chinese + math + english DESC;
– 对姓李的学生按总成绩升序排序输出
SELECT * FROM student WHERE name LIKE ‘李%’ ORDER BY chinese + math + english ASC;
– 查询男生和女生分别有多少人，并将人数降序输出
SELECT gender, COUNT() AS count FROM student GROUP BY gender ORDER BY count DESC;
– 查询男生和女生分别有多少人，并将人数降序输出，并查询出大于9的性别人数信息
SELECT gender, COUNT() AS count FROM student GROUP BY gender HAVING count > 9 ORDER BY count DESC;

DQL练习2-员工表
创建如下员工表

create table emp(
empno int,  -- 员工编号
ename varchar(20),  -- 员工名字
job varchar(20),  -- 工作名字
mgr int,  -- 上级领导编号
hiredate date,  -- 入职日期
sal int,  -- 薪资
comm int,  -- 奖金
deptno int  -- 部门编号
);
 
insert into emp values
(7369,'SMITH','CLERK',7902,'1980-12-17',800,null,20),
(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30),
(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30),
(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20),
(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30),
(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30),
(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,20),
(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20),
(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10),
(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30),
(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20),
(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30),
(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20),
(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
完成下列要求

-- 按员工编号升序排列不在10号部门工作的员工信息

SELECT * FROM emp WHERE deptno <> 10 ORDER BY empno ASC;

-- 查询姓名第二个字母不是'A'且薪水大于1000元的员工信息，按薪水降序排列
SELECT * FROM emp WHERE ename LIKE '_%[^A]%_' AND sal > 1000 ORDER BY sal DESC;

-- 按年薪+奖金降序排序
SELECT *, sal + IFNULL(comm, 0) AS annual_income FROM emp ORDER BY annual_income DESC;

-- 求每个部门的平均薪水
SELECT deptno, AVG(sal) AS avg_salary FROM emp GROUP BY deptno;

-- 求每个部门的最高薪水
SELECT deptno, MAX(sal) AS max_salary FROM emp GROUP BY deptno;

-- 求每个部门每个岗位的最高薪水并美观
SELECT deptno, job, MAX(sal) AS max_salary FROM emp GROUP BY deptno, job;、

-- 求平均薪水大于2000的部门编号	
SELECT deptno FROM emp GROUP BY deptno HAVING AVG(sal) > 2000;
-- 将部门平均薪水大于1500的部门编号列出来，按部门平均薪水降序排列
SELECT deptno, AVG(sal) AS avg_salary FROM emp GROUP BY deptno HAVING avg_salary > 1500 ORDER BY avg_salary DESC;

-- 选择公司中有奖金和员工姓名和工资
SELECT ename, sal FROM emp WHERE comm IS NOT NULL;

-- 查询员工最高工资和最低工资的差距

SELECT MAX(sal) - MIN(sal) AS salary_difference FROM emp;

– 单表查询练习
/* 素材
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 avg_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’)

1、查询每个部门的所属员工
SELECT dept3.name AS department, emp3.ename AS employee
FROM dept3
JOIN emp3 ON dept3.deptno = emp3.dept_id;
2、查询研发部门的所属员工
SELECT dept3.name AS department, emp3.ename AS employee
FROM dept3
JOIN emp3 ON dept3.deptno = emp3.dept_id
WHERE dept3.name = ‘研发部’;

3、查询研发部和销售部的所属员工
SELECT dept3.name AS department, emp3.ename AS employee
FROM dept3
JOIN emp3 ON dept3.deptno = emp3.dept_id
WHERE dept3.name IN (‘研发部’, ‘销售部’);

4、查询每个部门的员工数,并升序排序
SELECT dept3.name AS department, COUNT(emp3.eid) AS employee_count
FROM dept3
LEFT JOIN emp3 ON dept3.deptno = emp3.dept_id
GROUP BY dept3.name
ORDER BY employee_count ASC;

5、查询人数大于等于3的部门，并按照人数降序排
SELECT dept3.name AS department, COUNT(emp3.eid) AS employee_count
FROM dept3
LEFT JOIN emp3 ON dept3.deptno = emp3.dept_id
GROUP BY dept3.name
HAVING employee_count >= 3
ORDER BY employee_count DESC;