mysql的两道实验题 涵盖sql语句基本操作方向

-- 作业部分
CREATE DATABASE dome_1

CREATE TABLE IF NOT EXISTS dept(
d_no INT(11) PRIMARY KEY,
d_name VARCHAR(50) NOT NULL,
d_location VARCHAR(100)
)
-- 部门表的数据
INSERT INTO dept
VALUES (10, 'ACCOUNTING', 'ShangHai'),
(20, 'RESEARCH ', 'BeiJing '),
(30, 'SALES ', 'ShenZhen '),
(40, 'OPERATIONS ', 'FuJian ');

CREATE TABLE IF NOT EXISTS employee(
e_no INT(11) PRIMARY KEY,
e_name VARCHAR(50) NOT NULL,
e_gender CHAR(2),
dept_no INT(11) NOT NULL,
e_job VARCHAR(50) NOT NULL,
e_salary INT(11) NOT NULL,
hireDate DATE NOT NULL
)

-- 员工表的数据
INSERT INTO employee
VALUES (1001, 'SMITH', 'm',20, 'CLERK',800,'2005-11-12'),
(1002, 'ALLEN', 'f',30, 'SALESMAN', 1600,'2003-05-12'),
(1003, 'WARD', 'f',30, 'SALESMAN', 1250,'2003-05-12'),
(1004, 'JONES', 'm',20, 'MANAGER', 2975,'1998-05-18'),
(1005, 'MARTIN', 'm',30, 'SALESMAN', 1250,'2001-06-12'),
(1006, 'BLAKE', 'f',30, 'MANAGER', 2850,'1997-02-15'),
(1007, 'CLARK', 'm',10, 'MANAGER', 2450,'2002-09-12'),
(1008, 'SCOTT', 'm',20, 'ANALYST', 3000,'2003-05-12'),
(1009, 'KING', 'f',10, 'PRESIDENT', 5000,'1995-01-01'),
(1010, 'TURNER', 'f',30, 'SALESMAN', 1500,'1997-10-12'),
(1011, 'ADAMS', 'm',20, 'CLERK', 1100,'1999-10-05'),
(1012, 'JAMES', 'm',30, 'CLERK', 950,'2008-06-15');

-- 1、在employee表中，查询所有记录的e_no、e_name和e_salary字段值
SELECT e_no,e_name,e_salary FROM employee

-- 2、在employee表中，查询dept_no等于10和20的所有记录。
SELECT * FROM employee AS em
WHERE em.dept_no=10 OR em.dept_no=20

-- 3、在employee表中，查询工资范围在800到2500之间的员工信息
SELECT * FROM employee AS em
WHERE e_salary BETWEEN 800 AND 2500

-- 4、在employee表中，查询部门编号为20的部门中的员工信息。
SELECT * FROM employee AS em
WHERE em.dept_no=20

-- 5、在employee表中，查询各个部门最高工资的员工信息
SELECT MAX(em.e_salary),em.* FROM employee AS em GROUP BY em.dept_no

-- 6、查询员工BLAKE所在部门和部门所在地
SELECT d_name,d_location FROM dept AS d
WHERE d.d_no=(SELECT dept_no FROM employee WHERE e_name='BLAKE')

-- 7、使用连接查询，查询所有员工的部门和部门信息
SELECT * FROM employee AS em , dept AS r
WHERE r.d_no=em.dept_no

-- 8、在employee表中，计算每个部门各有多少名员工
SELECT COUNT(e_name),dept_no FROM employee AS em GROUP BY em.dept_no

-- 9、在employee表中，计算不同类型职工的总工资数
SELECT SUM(e_salary),dept_no FROM employee AS em GROUP BY em.dept_no

-- 10、在employee表中，计算不同部门的平均工工资。
SELECT AVG(e_salary),dept_no FROM employee AS em GROUP BY em.dept_no

-- 11、在employee表中，查询工资低于1500的员工信息。
SELECT * FROM employee
WHERE e_salary<1500

-- 12、在employee表中，将查询记录先按部门编号由高到低排列，再按员工工资由高到低排列。
SELECT * FROM employee ORDER BY dept_no DESC,e_salary DESC

-- 13、在employee表中，查询员工姓名以字母A或S开头的员工的信息
SELECT * FROM employee
WHERE e_name LIKE 's%' OR e_name LIKE'a%';

-- 14、在employee表中，查询到目前为止，工龄大于等于10年的员工信息。
SELECT * FROM employee AS em
WHERE ((SELECT YEAR(NOW()))-(SELECT YEAR(em.hiredate)))>10

SELECT (SELECT YEAR(NOW()))-(SELECT YEAR(em.hiredate)) FROM employee AS em
-- 作业2
CREATE TABLE emp(
empno INT PRIMARY KEY,
ename VARCHAR(10) NOT NULL,
job VARCHAR(20) NOT NULL,
mgr INT,
hiredate DATE NOT NULL,
sal INT ,
comm INT ,
deptno INT
)

INSERT INTO emp VALUES
(1001, 'SMITH','CLERK', 1004 , '2005-11-12' ,800,100,20),
(1002, 'ALLEN','SALESMAN', 1006,'2003-05-12',1600,200,20),
(1003, 'WARD','SALESMAN', 1006,'2003-05-12', 1250,200,30),
(1004, 'JONES','MANAGER',1008, '1998-05-18', 2975,500,20),
(1005, 'MARTIN', 'SALESMAN',1006,'2001-06-12', 1250,200,30),
(1006, 'BLAKE', 'MANAGER',1008,'1997-02-15', 2850,500,30),
(1007, 'CLARK', 'MANAGER',1008, '2002-09-12',2450,500,10),
(1008, 'SCOTT', 'ANALYST', 1009 ,'2003-05-12', 3000,1000,20),
(1009, 'KING', 'PRESIDENT', NULL,'1995-01-01', 5000,20000,40),
(1010, 'TURNER','SALESMAN', 1006, '1997-10-12', 1500,200,30),
(1011, 'ADAMS', 'CLERK', 1004 ,'1999-10-05', 1100,100,20),
(1012, 'JAMES', 'CLERK', 1004 ,'2008-06-15', 950,100,30);

CREATE TABLE dept_2(
deptno INT PRIMARY KEY,
dname VARCHAR(20),
loc VARCHAR(50)
);

INSERT INTO dept_2
VALUES (10, 'ACCOUNTING', 'ShangHai'),
(20, 'RESEARCH ', 'BeiJing '),
(30, 'SALES ', 'ShenZhen '),
(40, 'OPERATIONS ', 'FuJian ');

-- 1．列出至少有2个员工的所有部门。
SELECT * FROM dept_2 AS d
WHERE (SELECT COUNT(deptno) FROM emp AS e WHERE e.deptno=d.deptno)!=1

-- 2．列出薪金比“SMITH”多的所有员工。
SELECT * FROM emp AS e
WHERE e.sal>(SELECT sal FROM emp WHERE ename='SMITH')

-- 3．列出所有员工的姓名及其直接上级的姓名。
SELECT e.empno,e.ename,r.ename FROM emp AS e,emp AS r
WHERE e.empno=r.mgr

-- 4．列出受雇日期早于其直接上级的所有员工。
SELECT DISTINCT e.empno,e.ename FROM emp AS e,emp AS r
WHERE e.empno=r.mgr AND e.hiredate<r.hiredate

-- 5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
SELECT * FROM dept_2 AS d LEFT JOIN emp AS e
ON d.deptno=e.deptno

-- 6．列出所有“CLERK”（办事员）的姓名及其部门名称。
SELECT * FROM dept_2 AS d LEFT JOIN emp AS e
ON d.deptno=e.deptno
WHERE e.job='CLERK'

-- 7．列出最低薪金大于1500的各种工作。
SELECT DISTINCT job FROM emp AS e
WHERE e.sal>1500

SELECT DISTINCT job FROM emp AS e
WHERE e.sal>1500

-- 8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编
SELECT ename FROM emp AS em
WHERE em.deptno=(SELECT deptno FROM dept_2 AS e WHERE e.dname='SALES')

-- 9．列出薪金高于公司平均薪金的所有员工
SELECT ename FROM emp AS e
WHERE (SELECT AVG(e.sal) FROM emp AS e)<e.sal

-- 10．列出与“SCOTT”从事相同工作的所有员工
SELECT ename FROM emp AS e
WHERE (SELECT job FROM emp AS e WHERE e.ename='SCOTT')=e.job

-- 11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
-- select distinct sal from emp as e where e.deptno=30
SELECT ename,sal FROM emp AS em
WHERE em.sal IN(SELECT DISTINCT sal FROM emp AS e WHERE e.deptno=30)

-- 12．列出薪金高于在30部门工作的所有员工薪金的员工姓名和薪金。
-- SELECT sum(e.sal) FROM emp AS e WHERE e.deptno=30
SELECT ename,sal FROM emp AS em
WHERE em.sal>(SELECT SUM(e.sal) FROM emp AS e WHERE e.deptno=30)

-- 13．列出在每个部门工作的员工数量、平均工资和平均服务期限。
SELECT COUNT(deptno)AS '工作人数',AVG(sal)AS '平均工资' ,DATEDIFF(NOW(),hiredate) AS '平均服务期限'
FROM emp AS e GROUP BY e.deptno

-- 14．列出所有员工的姓名、部门名称和工资。
SELECT ename,sal,dname FROM emp AS e LEFT JOIN dept_2 AS d
ON e.deptno=d.deptno

-- 15．列出从事同一种工作但属于不同部门的员工的一种组合。
SELECT DISTINCT e.job,e.deptno FROM emp AS e,emp AS a
WHERE e.deptno!=a.deptno AND e.job=a.job
ORDER BY e.deptno DESC

-- 16．列出所有部门的详细信息和部门人数。
-- select * from emp as e group by deptno
SELECT a.*,COUNT(e.deptno)AS '人数' FROM emp AS e, dept_2 AS a
WHERE e.deptno=a.deptno
GROUP BY e.deptno

-- 17．列出各种工作的最低工资。
SELECT MIN(sal)FROM emp AS e
GROUP BY e.deptno

-- 18．列出各个部门的MANAGER（经理）的最低薪金。
SELECT ename,job,sal FROM emp WHERE job='MANAGER'

-- 19．列出所有员工的年工资,按年薪从低到高排序。
SELECT (sal+comm)AS '月薪' FROM emp
ORDER BY 月薪

-- 20. 列出所有部门的经理的详细信息
SELECT * FROM emp
WHERE job='MANAGER'

-- 21. 列出薪金高于佣金的员工信息
SELECT * FROM emp AS d
WHERE d.sal>d.comm

#22. 列出没有薪金或者薪金为0的员工信息
SELECT * FROM emp WHERE sal=0

#23. 列出薪金高于10000元王姓经理的员工信息
SELECT * FROM emp
WHERE ename LIKE '王%' AND sal>10000