1. 目标
熟悉常用的DQL语言
熟练常用的多表查询
熟练多表聚合
2. 创建数据表
3. DQL**数据查询**
CREATE TABLE employee
(
id INT,
NAME VARCHAR(20),
salary FLOAT
);
INSERT INTO employee VALUES(1,'zhangsan','male',2000),(2,'lisi','male',1000),
(3,'xiaohong','female',4000);
CREATE TABLE student(
id INT,
NAME VARCHAR(20),
chinese INT,
english INT,
math INT
);
INSERT INTO student(id, NAME, chinese, english, math)
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);
# 部门表
CREATE TABLE DEPT(
DEPTNO INT PRIMARY KEY, -- 部门编号
DNAME VARCHAR(14) , -- 部门名称
LOC VARCHAR(13) -- 部门地址
) ;
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK')，(20,'RESEARCH','DALLAS')，
(30,'SALES','CHICAGO')，(40,'OPERATIONS','BOSTON');
-- 查询表中所有学生的信息。
SELECT * FROM student;
-- 查询表中所有学生的姓名和对应的英语成绩。select name,
-- 过滤表中重复数据。
-- 统计每个学生的总分。
-- 在所有学生总分数上加10分特长分。
-- 使用别名表示学生分数。
-- 查询英语成绩大于90分的同学
-- 查询总分大于200分的所有同学
-- 查询英语分数在 80－90之间的同学。
-- 查询英语分数不在 80－90之间的同学。
-- 查询数学分数为89,90,91的同学。
-- 查询所有姓李的学生英语成绩。
-- 查询数学分80并且语文分80的同学。
-- 查询英语80或者总分200的同学
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),
(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,10),
(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);
-- 1、按员工编号升序排列不在10号部门工作的员工信息
-- 2、查询姓名第二个字母不是”A”且薪水大于800元的员工信息，按年薪降序排列
-- 3、求每个部门的平均薪水
-- 4、求各个部门的最高薪水-- 5、求每个部门每个岗位的最高薪水
-- 6、求平均薪水大于2000的部门编号
-- 7、将部门平均薪水大于1500的部门编号列出来，按部门平均薪水降序排列
-- 工资等级表
CREATE TABLE SALGRADE
(
GRADE INT, -- 工资等级
LOSAL DOUBLE, -- 最低工资
HISAL DOUBLE ); -- 最高工资
INSERT INTO SALGRADE VALUES (1,700,1200),(2,1201,1400),(3,1401,2000),
(4,2001,3000),(5,3001,9999);
-- 练习：
-- 0、求最高薪水的员工信息
-- 求大于平均薪水的员工信息
-- 1、查询部门和所属的员工
-- 2、工资水平多于smith的员工信息。
-- 3、返回员工和所属上级的姓名。
-- 4、返回雇员的雇佣日期早于其领导雇佣日期的员工及其领导姓名。
-- 5、返回从事clerk工作的员工姓名和所在部门名称。
-- 6、返回部门号,部门名称及其本部门的最低工资
-- 7、返回销售部(sales)所有员工的姓名。
-- 8、返回与SCOTT从事相同工作的员工。
# 答案
use day07;
CREATE TABLE employee
(
id INT,
NAME VARCHAR(20),
sex VARCHAR(10),
salary FLOAT
);
INSERT INTO employee
VALUES (1, 'zhangsan', 'male', 2000),
(2, 'lisi', 'male', 1000),
(3, 'xiaohong', 'female', 4000);
CREATE TABLE student
(
id INT,
NAME VARCHAR(20),
chinese INT,english INT,
math INT
);
INSERT INTO student(id, NAME, chinese, english, math)
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);
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),
(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, 10),
(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);
CREATE TABLE SALGRADE
(
GRADE INT, -- 工资等级
LOSAL DOUBLE, -- 最低工资
HISAL DOUBLE
); -- 最高工资
INSERT INTO SALGRADE
VALUES (1, 700, 1200),
(2, 1201, 1400),
(3, 1401, 2000),
(4, 2001, 3000),
(5, 3001, 9999);
# 部门表
CREATE TABLE dept
(
DEPTNO INT PRIMARY KEY, -- 部门编号
DNAME VARCHAR(14), -- 部门名称LOC VARCHAR(13) -- 部门地址
);
INSERT INTO dept
VALUES (10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON');
-- 查询表中所有学生的信息。
SELECT *
FROM student;
-- 查询表中所有学生的姓名和对应的英语成绩。
SELECT NAME '名字', english '英语成绩'
FROM student;
-- 过滤表中重复数据。
SELECT DISTINCT *
FROM student;
-- 统计每个学生的总分。
SELECT NAME '名字', (chinese + english + math) '总成绩'
FROM student;
-- 在所有学生总分数上加10分特长分。
SELECT NAME '名字', (chinese + english + math + 10) '总成绩'
FROM student;
-- 使用别名表示学生分数。
-- 查询英语成绩大于90分的同学
SELECT *
FROM student
WHERE english > 90;
-- 查询总分大于200分的所有同学
SELECT *
FROM student
WHERE (chinese + english + math) > 200;
-- 查询英语分数在 80－90之间的同学。
SELECT *
FROM student
WHERE english BETWEEN 80 AND 90;
-- 查询英语分数不在 80－90之间的同学。
-- 方法1
SELECT *
FROM student
WHERE NOT (english BETWEEN 80 AND 90);
-- 方法2
SELECT *
FROM student
WHERE english >90 or english<80;
-- 查询数学分数为89,90,91的同学。
SELECT *
FROM student
WHERE math IN (90, 89, 91);
-- 查询所有姓李的学生英语成绩。
SELECT NAME, english
FROM studentWHERE NAME LIKE '李%';
-- 查询数学分80并且语文分80的同学。
SELECT *
FROM student
WHERE chinese = 80
AND math = 80;
-- 查询英语98或者总分大于200的同学
SELECT *
FROM student
WHERE english = 98
OR (chinese + english + math) > 200;
-- 1、按员工编号升序排列不在10号部门工作的员工信息
select *
from emp
where deptno <> 10
order by empno;
-- 2、查询姓名第二个字母不是”A”且薪水大于800元的员工信息，按年薪降序排列
select *
from emp
where sal > 800
and ename not like '_A%'
order by 12*sal + IFNULL(comm, 0) desc; 工资需要加上奖金
-- 3、求每个部门的平均薪水
SELECT deptno, avg(sal)
from emp
group by deptno
order by deptno;
-- 4、求各个部门的最高薪水
SELECT deptno, max(sal)
from emp
group by deptno
order by deptno;
-- 5、求每个部门每个岗位的最高薪水 ----------有问题 不能用两个条件分组
SELECT deptno, job, max(sal)
from emp
group by deptno, job
order by deptno, job;
-- 6、求平均薪水大于2000的部门编号
select deptno
from emp
group by deptno
having avg(sal) > 2000;
-- 7、将部门平均薪水大于1500的部门编号列出来，按部门平均薪水降序排列
select deptno
from emp
group by deptno
having avg(sal) > 1500
order by avg(sal) desc;
-- 0、求最高薪水的员工信息
select *
from emp
where sal = (
select max(sal) as maxsalfrom emp
);
-- 求大于平均薪水的员工信息
select *
from emp
where sal > (
select avg(sal) as avgsal
from emp
);
-- 1、查询部门和所属的员工
SELECT *
FROM dept d,
emp e
WHERE d.deptno = e.deptno;
-- 2、工资水平多于smith的员工信息。
SELECT *
FROM emp
WHERE sal > (SELECT sal FROM emp WHERE ename = 'SMITH');
-- 3、返回员工和所属上级的姓名。
select * from emp;
SELECT e.ename as '员工姓名', m.ename as '所属上级姓名'
FROM emp e,
emp m
WHERE e.mgr = m.empno;
-- 4、返回雇员的雇佣日期早于其领导雇佣日期的员工及其领导姓名。
SELECT e.ename, m.ename
FROM emp e,
emp m
WHERE e.mgr = m.empno
AND e.HIREDATE < m.HIREDATE;
-- 5、返回从事clerk工作的员工姓名和所在部门名称。
SELECT ename, dname
FROM dept d,
emp e
WHERE d.deptno = e.deptno
AND job = 'clerk';
-- 6、返回部门号,部门名称及其本部门的最低工资 第一遍做错了 不关联查询没法查询寻min(sal)
SELECT e.deptno, d.dname, MIN(sal) as '本部门最低工资'
FROM emp e,
dept d
WHERE d.deptno = e.deptno
GROUP BY e.deptno;
-- 7、返回销售部(sales)所有员工的姓名。
SELECT e.ename
FROM emp e
where e.job = 'SALESMAN';
-- 8、返回与SCOTT从事相同工作的员工。
SELECT e.ename
FROM emp e
where e.job = (
select t.job
from emp twhere t.ename = 'SCOTT'