MySQL数据库DQL练习题

练习题一用的表以及数据：

CREATE TABLE student(
id INT,
name VARCHAR(20),
gender VARCHAR(20),
chinese INT,
english INT,
math INT
);

INSERT INTO student(id, name, gender, chinese, english, math) VALUES (1, '张明', '男', 89, 78, 90);
INSERT INTO student(id, name, gender, chinese, english, math) VALUES  (2, '李进', '男', 67, 53, 95);
INSERT INTO student(id, name, gender, chinese, english, math) VALUES (3, '王五', '女', 87, 78, 77);
INSERT INTO student(id, name, gender, chinese, english, math) VALUES  (4, '李一', '女', 88, 98, 92);
INSERT INTO student(id, name, gender, chinese, english, math) VALUES (5, '李财', '男', 82, 84, 67);
INSERT INTO student(id, name, gender, chinese, english, math) VALUES (6, '张宝', '男', 55, 85, 45);
INSERT INTO student(id, name, gender, chinese, english, math) VALUES  (7, '黄蓉', '女', 75, 65, 30);
INSERT INTO student(id, name, gender, chinese, english, math) VALUES  (7, '黄蓉', '女', 75, 65, 30);

练习题一：

• 查询表中所有学生的信息
• 查询表中所有学生的姓名和对应的英语成绩
• 过滤表中重复数据
• 统计每个学生的总分
• 在所有学生总分数上加上10分特长分
• 使用别名表示学生分数
• 查询英语成绩大于90分的同学
• 查询总分大于200分的所有同学
• 查询英语分数在80-90之间的同学
• 查询英语分数不在80-90之间的同学
• 查询数学分数为89,90,91的同学
• 查询所有姓李的学生的英语成绩
• 查询数学分80并且语文分80的同学
• 查询英语80或者总分200的同学
• 对数学成绩降序排序后输出
• 对总分排序后输出，然后再按从高到低的顺序输出
• 对姓李的学生总成绩排序输出
• 查询男生和女生分别由多少人，并将人数降序排序输出

答案一：

--  查询表中所有学生的信息 
SELECT * FROM student;
--  查询表中所有学生的姓名和对应的英语成绩 
SELECT name,english FROM student;
--  过滤表中重复数据 
SELECT DISTINCT * FROM student;

-- 统计每个学生的总分 
SELECT name,chinese+english+math as total_score FROM student;

--  在所有学生总分数上加上10分特长分
SELECT name,chinese+english+math+10 as total_score FROM student;
--  使用别名表示学生分数
SELECT name,chinese '语文',english '英语',math '数学' FROM student;
--  查询英语成绩大于90分的同学 
SELECT * FROM student WHERE english > 90;
--  查询总分大于200分的所有同学 
SELECT * FROM student WHERE (chinese+english+math) > 200;
SELECT name,chinese+english+math+10 as total_score FROM student WHERE (chinese+english+math) > 200;


--  查询英语分数在80-90之间的同学
SELECT * FROM student WHERE english >=80 AND english<90;
--  查询英语分数不在80-90之间的同学
SELECT * FROM student WHERE  NOT (english >=80 AND english<90);
SELECT * FROM student WHERE  english not BETWEEN 80 and 90;
SELECT * FROM student WHERE  english>=80 and english<=90;

--  查询数学分数为89,90,91的同学
SELECT * FROM student WHERE math=89 or math=90 or math = 91;
SELECT * FROM student WHERE math in (89,90,91);
--  查询所有姓李的学生的英语成绩
SELECT * FROM student WHERE name  LIKE '李%';
--   查询数学分80并且语文分80的同学
SELECT name,math,chinese FROM student WHERE math = 80 AND chinese=80;
--  查询英语80或者总分200的同学
SELECT * FROM student WHERE english = 80 and (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) DESC;
--  查询男生和女生分别由多少人，并将人数降序排序输出
SELECT gender,COUNT(*) FROM student GROUP BY gender ORDER BY COUNT(*) DESC;

练习题二用的表以及数据：

CREATE TABLE emp(
empno INT,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate date,
sal INT,
comm INT,
deptno INT
);

INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20);
INSERT INTO emp VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
INSERT INTO emp VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
INSERT INTO emp VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20);
INSERT INTO emp VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
INSERT INTO emp VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30);
INSERT INTO emp VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10);
INSERT INTO emp VALUES (7788, 'SCOTT', 'ANALYST', 7566, '7987-04-19', 3000, NULL, 20);
INSERT INTO emp VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
INSERT INTO emp VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
INSERT INTO emp VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20);
INSERT INTO emp VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30);
INSERT INTO emp VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20);
INSERT INTO emp VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);

练习题二：

• 按员工编号升序排列不在10号部门工作的员工信息
• 查询姓名第二个字母不是“A”且薪水大于1000元的员工信息，按年薪降序排列
• 求每个部门的平均薪水
• 求各个部门的最高薪水
• 求每个部门每个岗位的最高薪水
• 求平均薪水大于2000的部门编号
• 将部门平均薪水大于1500的部门编号列出来，按部门平均薪水降序排列
• 选择公司中有奖金的员工姓名，工资
• 查询员工最高工资和最低工资的差距

答案二

--  按员工编号升序排列不在10号部门工作的员工信息
SELECT * FROM emp WHERE deptno !=10 ORDER BY empno ASC;
--  查询姓名第二个字母不是“A”且薪水大于1000元的员工信息，按年薪降序排列
SELECT * FROM emp WHERE ename NOT LIKE'_A%' AND sal >1000 ORDER BY (12*sal+IFNULL(comm,0)) DESC;
-- 注：ifnull（comm,0）如果comm的值为null，则当做0，否则还是原来的值。
--  求每个部门的平均薪水
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno;
--  求各个部门的最高薪水
SELECT deptno,MAX(sal) FROM emp GROUP BY deptno;
--  求每个部门每个岗位的最高薪水
SELECT deptno,job,MAX(sal) FROM emp GROUP BY deptno,job;
--  求平均薪水大于2000的部门编号
SELECT deptno,AVG(sal) FROM emp  GROUP BY deptno HAVING AVG(sal)>2000;
--  将部门平均薪水大于1500的部门编号列出来，按部门平均薪水降序排列
SELECT deptno,AVG(sal) FROM emp  GROUP BY deptno HAVING AVG(sal)>1500  ORDER BY AVG(sal) DESC;
--  选择公司中有奖金的员工姓名，工资
SELECT * FROM emp WHERE comm is NOT NULL;
--  查询员工最高工资和最低工资的差距
SELECT MAX(sal)-MIN(sal) FROM emp;