MySQL的题1答案

-- 已知公司的员工表EMP(EID, ENAME, BDATE, SEX, CITY)，
-- 部门表DEPT（DID, DNAME, DCITY），
-- 工作表WORK（EID,DID,STARTDATE,SALARY）。

-- 各个字段说明如下：
-- EID——员工编号，最多6个字符。例如A00001（主键）
-- ENAME——员工姓名，最多10个字符。例如SMITH
-- BDATE——出生日期，日期型  '1990-01-01'
-- SEX——员工性别，单个字符。F或者M
-- CITY——员工居住的城市，最多20个字符。例如：上海

-- DID——部门编号，最多3个字符。例如 A01 （主键）
-- DNAME——部门名称，最多20个字符。例如：研发部门
-- DCITY——部门所在的城市，最多20个字符。例如：上海

-- STARTDATE——员工到部门上班的日期，日期型
-- SALARY——员工的工资。整型。

 
-- 1．创建表EMP，DEPT，WORK。
-- 2．向每个表中插入适当的数据。例如：插入三条部门的数据，分别为每个部门插入两条员工数据
-- 3．查询“研发”部门的所有员工的基本信息
-- 4．查询拥有最多的员工的部门的基本信息（要求只取出一个部门的信息），如果有多个部门人数一样，那么取出部门编号最小的那个部门的基本信息。
-- 5．显示部门人数大于5的每个部门的编号，名称，人数
-- 6．查询出工资比其所在部门平均工资高的所有职工信息。
-- 7．显示部门人数大于5的每个部门的最高工资，最低工资
-- 8．列出员工编号以字母P至S开头的所有员工的基本信息
-- 9．删除年龄超过60岁的员工
-- 10．为工龄超过10年的职工增加10%的工资

-- 创建数据库
CREATE DATABASE db1201;
SHOW DATABASES;
USE db1201;

-- 1.创建表
CREATE TABLE emp(
eid VARCHAR(12) PRIMARY KEY,
ename VARCHAR(20),
bdate DATE,
sex CHAR(2),
city VARCHAR(40)
)
SELECT * FROM emp;

CREATE TABLE dept(
did VARCHAR(12) PRIMARY KEY,
dname VARCHAR(40),
dcity VARCHAR(40)
)
SELECT * FROM dept;

CREATE TABLE WORK(
eid VARCHAR(12),
did VARCHAR(12),
startdate DATE,
salary INT

)
SELECT * FROM WORK;

-- 2.插入数据
INSERT INTO dept(did,dname,dcity)
VALUES
('001','人事部','北京'),
('002','研发部','北京'),
('003','测试部','北京');
SELECT * FROM dept;

INSERT INTO emp(eid,ename,bdate,sex,city)
VALUES
('0001','张三','1990-01-01','m','北京'),
('0002','李四','1989-09-02','m','上海'),
('0003','王五','1992-06-03','f','西安'),
('0004','小王','1991-01-01','m','北京'),
('0005','小李','1987-09-02','m','上海'),
('0006','小吴','1993-06-03','f','西安');
SELECT * FROM emp;
DROP TABLE emp;

INSERT INTO WORK(eid,did,startdate,salary)
VALUES
('0001','001','2000-12-11','3000'),
('0002','001','2015-09-06','4000'),
('0003','002','2016-07-03','7000'),
('0004','002','2002-12-11','9000'),
('0005','003','2011-09-06','4000'),
('0006','003','2012-07-03','5000');
SELECT * FROM WORK;
DROP TABLE WORK;

-- 3.查询“研发”部门的所有员工的基本信息
SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM WORK;
SELECT e.*,d.*, w.* FROM emp e,dept d,WORK w WHERE
 e.eid = w.eid AND d.did=w.did AND
((SELECT did FROM dept WHERE dname='研发部') = w.did);

SELECT e.*,d.*, w.* FROM emp e,dept d,WORK w WHERE
 e.eid = w.eid AND d.did=w.did
(002 = w.did);

SELECT * FROM emp,WORK WHERE emp.`eid`=work.`eid`;
SELECT * FROM dept,WORK WHERE dept.`did`=work.`did` AND work.did=002;

-- 4查询拥有最多的员工的部门的基本信息（要求只取出一个部门的信息），
-- 如果有多个部门人数一样，那么取出部门编号最小的那个部门的基本信息。
--A.查询每个部门的人数
SELECT did,COUNT(eid) FROM WORK GROUP BY did
--B.人数最大，如果人数一样，部门编号最小
SELECT did,COUNT(eid) FROM WORK GROUP BY did
ORDER BY COUNT(eid) DESC,did ASC LIMIT 1

SELECT * FROM dept WHERE did=
(SELECT did FROM WORK GROUP BY did
ORDER BY COUNT(eid) DESC,did ASC LIMIT 1);

-- 5.显示部门人数大于1的每个部门的编号，名称，人数
   -- 部门人数在1人以上的部门编号和人数
   SELECT did,COUNT(eid) FROM WORK GROUP BY did
       HAVING COUNT(eid)>1
   --
    SELECT n.*,dept.dname FROM
    (
      SELECT did,COUNT(eid)c FROM WORK GROUP BY did
       HAVING COUNT(eid)>1 
    )n LEFT JOIN dept ON n.did=dept.did
   
-- 6.查询出工资比其所在部门平均工资高的所有职工信息。
  -- 方法一 
    （1）部门编号为001的平均工资
    SELECT * FROM WORK;
     SELECT did,AVG(salary) FROM  WORK WHERE did='001'
     (2)工资比其所在部门平均工资高
     SELECT * FROM WORK s WHERE salary>
     (SELECT AVG(salary) FROM  WORK WHERE did=s.did)

  -- 方法二
     (1)每个部门的平均工资
     SELECT did,AVG(salary) FROM WORK GROUP BY did
      (2)查询所有的人工资和平均工资
     SELECT * FROM WORK w LEFT JOIN
    (SELECT did,AVG(salary) ag FROM WORK GROUP BY did
      )n ON w.did=n.did
     WHERE w.salary>n.ag
    
    
    
-- 7．显示部门人数大于1的每个部门的最高工资，最低工资
SELECT did,COUNT(eid) FROM WORK GROUP BY
did HAVING COUNT(eid)>1;

   SELECT did,COUNT(eid),MAX(salary),MIN(salary) 
   FROM WORK GROUP BY did HAVING COUNT(eid)>1;
--8．列出员工编号以字母P至S开头的所有员工的基本信息
  SELECT * FROM emp WHERE eid LIKE'P%' OR
                          eid LIKE'Q%' OR
                          eid LIKE'R%' OR 
                          eid LIKE'S%' ;
--方法2
SELECT LEFT(eid,1) FROM emp;

SELECT *,LEFT(eid,1) FROM emp
WHERE LEFT(eid,1) BETWEEN'P' AND 'S';
   

SELECT 'admin','hellow' 
SELECT CONCAT('admin','hellow')
SELECT LEFT('admin',2)

--9．删除年龄超过60岁的员工

SELECT "aa" FROM emp;
SELECT CURDATE() FROM emp;  -- Date类型数据：‘1990-01-01’

-- 查询当前时间对应的年份
SELECT YEAR(CURDATE()) FROM emp;

SELECT *,YEAR(CURDATE())-YEAR(bdate)
FROM emp WHERE YEAR(CURDATE())-YEAR(bdate)>60

DELETE FROM emp
WHERE YEAR(CURDATE())-YEAR(bdate)>60
--10．为工龄超过10年的职工增加10%的工资
-- 工龄>10的work表中的所有信息
SELECT * FROM WORK
WHERE YEAR(CURDATE())-YEAR(startdate)>10

UPDATE WORK SET salary=salary+salary*10%
WHERE YEAR(CURDATE())-YEAR(startdate)>10