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;