MySql专项练习题①

MySql专项练习题①

CREATE DATABASE db10;

use db10;

– 员工信息表
CREATE TABLE emp(
empno INT,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
comm DECIMAL(7,2),
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,‘1987-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);
INSERT INTO emp VALUES(7981,‘MILLER’,‘CLERK’,7788,‘1992-01-23’,2600,500,20);

– 部门信息表
CREATE TABLE dept(
deptno INT,
dname VARCHAR(14),
loc VARCHAR(13)
);

INSERT INTO dept VALUES(10, ‘ACCOUNTING’, ‘NEW YORK’);
INSERT INTO dept VALUES(20, ‘RESEARCH’, ‘DALLAS’);
INSERT INTO dept VALUES(30, ‘SALES’, ‘CHICAGO’);
INSERT INTO dept VALUES(40, ‘OPERATIONS’, ‘BOSTON’);

– 基本查询--------------------------------------------------------
– 所有员工的信息
SELECT * FROM emp;
– 薪资大于等于1000并且小于等于2000的员工信息
SELECT * FROM emp WHERE sal>2000
– 从员工表中查询出所有的部门编号
SELECT DISTINCT deptno FROM emp;
– 查询出名字以A开头的员工的信息
SELECT * FROM emp WHERE ename LIKE ‘A%’;
– 查询出名字第二个字母是L的员工信息
SELECT * FROM emp WHERE ename LIKE ‘_L%’;
– 查询出没有奖金的员工信息
SELECT * FROM emp WHERE comm=0 OR comm IS NULL -----------------------------------------------------------
– 所有员工的平均工资
SELECT AVG(sal) FROM emp;
– 所 员工的工资总和
SELECT SUM(sal) FROM emp
– 所有员工的数量
SELECT COUNT(*) FROM emp
– 最高工资
SELECT MAX(sal) FROM emp
– 最少工资
SELECT MIN(sal) FROM emp
– 最高工资的员工信息
SELECT * FROM emp WHERE sal=(SELECT MAX(sal) FROM emp)
– 最低工资的员工信息
SELECT * FROM emp WHERE sal=(SELECT MIN(sal) FROM emp)

– 分组查询------------------------------------------
– 每个部门的平均工资
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno

– 子查询--------------------------------------------------
– 单行子查询(> < >= <= = <>)
– 查询出高于10号部门的平均工资的员工信息
SELECT AVG(sal) FROM emp WHERE deptno=10
SELECT * FROM emp WHERE sal>( SELECT AVG(sal) FROM emp WHERE deptno=10)

– 多行子查询(in not in any all) >any >all
– 查询出比10号部门任何员工薪资高的员工信息
SELECT * FROM emp WHERE sal>(SELECT MAX(sal) FROM emp WHERE deptno=10)???
– 多列子查询(实际使用较少) in
– 和10号部门同名同工作的员工信息
SELECT * FROM emp WHERE deptno!=10 AND ename IN(SELECT ename FROM emp WHERE deptno=10) AND job IN(SELECT job FROM emp WHERE deptno=10)
– Select接子查询
– 获取员工的名字和部门的名字
SELECT
e.ename,
d.dname
FROM
emp e,
dept d
WHERE
e.deptno=d.deptno

– from后面接子查询
– 查询emp表中经理信息
SELECT * FROM emp WHERE job LIKE ‘manager’
– where 接子查询
– 薪资高于10号部门平均工资的所有员工信息
SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp WHERE deptno=10)
– having后面接子查询
– 有哪些部门的平均工资高于30号部门的平均工资
SELECT AVG(sal) FROM emp WHERE deptno=30
SELECT AVG(sal) FROM emp GROUP BY deptno
SELECT deptno,AVG(sal) a FROM emp GROUP BY deptno HAVING a>(SELECT AVG(sal) FROM emp WHERE deptno=30)
– 工资>JONES工资
– 查询与SCOTT同一个部门的员工
SELECT * FROM emp WHERE deptno=(SELECT deptno FROM emp WHERE ename LIKE ‘SCOTT’)
– 工资高于30号部门所有人的员工信息
SELECT * FROM emp WHERE sal>(SELECT MAX(sal) FROM emp WHERE deptno=30)
– 查询工作和工资与MARTIN完全相同的员工信息
SELECT * FROM emp WHERE job LIKE (SELECT job FROM emp WHERE ename LIKE ‘MARTIN’) AND sal=(SELECT sal FROM emp WHERE ename LIKE ‘MARTIN’)
– 有两个以上直接下属的员工信息
SELECT mgr,COUNT(*) a FROM emp GROUP BY mgr HAVING a >=2

– 查询员工编号为7788的员工名称,员工工资,部门名称,部门地址
SELECT * FROM emp WHERE empno=7788
SELECT
a.ename,
a.sal,
d.dname,
d.loc
FROM
(SELECT * FROM emp WHERE empno=7788) a,
dept d

WHERE
d.deptno=a.deptno

– SQL查询的综合案例--------------------------------------
– 查询出高于本部门平均工资的员工信息
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno
SELECT *

FROM emp a1,
(SELECT deptno,AVG(sal) c FROM emp GROUP BY deptno) a2
WHERE
a1.deptno=a2.deptno
AND a1.sal>a2.c

– 列出达拉斯加工作的人中,比纽约平均工资高的人
SELECT AVG(sal) FROM emp WHERE deptno=10
SELECT * FROM emp WHERE deptno=20 AND sal>(SELECT AVG(sal) FROM emp WHERE deptno=10)
– 查询7369员工编号,姓名,经理编号和经理姓名
SELECT
t1.empno,
t1.ename,
t1.mgr,
t2.ename

FROM
emp t1,
emp t2
WHERE
t1.mgr=t2.empno
AND t1.empno=7369
– 查询出各个部门薪水最高的员工所有信息
SELECT MAX(sal) FROM emp GROUP BY deptno
SELECT * FROM emp WHERE sal IN(SELECT MAX(sal) FROM emp GROUP BY deptno)