MySQL习题整理-526互联

每日一练

=====================================================================================

创建管理表库

1.将表departments中的数据插入新表dept02中

CREATE TABLE dept02

AS

SELECT *

FROM atguigudb.departments

2.创建表

CREATE TABLE emp01

(id INT(7),

first_name VARCHAR(25),

last_name VARCHAR(25),

dept_id INT(7)

);

3.创建数据库test01_office,指明字符集为utf8。并在此数据库下执行下述操作

CREATE DATABASE test01_office CHARACTER SET 'utf8';

USE test01_office;

4.将表emp02重命名为emp01

RENAME TABLE emp02 TO emp01;

5.在表dept02和emp01中添加新列test_column，并检查所作的操作

ALTER TABLE dept02 ADD test_column VARCHAR(10);

6.向books表中插入记录

(1）不指定字段名称，插入第一条记录

INSERT INTO books

VALUES(1,'tal of AAA','Dickes',23,1995,'novel',11);

(2）指定所有字段名称，插入第二记录

INSERT INTO books(id,`name`,`authors`,price,pubdate,note,num)

VALUES(2,'Emmat','jane lura',35,1993,'joke',22);

(3）同时插入多条记录（剩下的所有记录）

INSERT INTO books(id,`name`,`authors`,price,pubdate,note,num) VALUES
(3,'story of jane','jane tim',40,2001,'novel',0),
(4,'lovey day','George BYron',20,2005,'novel',30),
(5,'Old land','Honore Blade',30,2010,'law',0),
(6,'the battle','upton sara',30,1999,'medicine',40),
(7,'Rose Hood','Richard haggard',28,2008,'cartoon',28);

=====================================================================================

mysql 中子查询的in any all的区别：

1.IN：在范围内的值只要是true就返回任意一个

2.ALL：与子查询返回所有值比较只要是true就返回所有

3.ANY：与子查询任何值进行比较只要是true就返回某一个

练习整理

1.查询员工12个月的工资总和

SELECT employee_id,last_name,salary * 12 "SUM SALARY"
FROM employees;

2.查询employees表中去除重复的job_id以后的数据

SELECT DISTINCT job_id FROM employees;

3.查询工资大于10000的员工姓名和工资

SELECT last_name,salary FROM employees where salary>10000;

4.查询员工号为100的员工的姓名和部门号

SELECT last_name,department_id FROM employees where employee_id = 100;

5.查询员工id为偶数的员工信息

SELECT employee_id,last_name,salary FROM employees WHERE employee_id % 2 = 0;

6.查询表中commission_pct为null的数据有哪些

SELECT last_name,salary,commission_pct FROM employees WHERE commission_pct is NULL;

7.查询工资在6000 到 8000 的员工信息

SELECT employee_id,last_name,salary FROM employees where salary between 6000 and 8000;

SELECT employee_id,last_name,salary FROM employees WHERE salary >= 6000 && salary <= 8000;

8.查询工资不在6000到8000的员工信息

SELECT employee_id,last_name,salary FROM employees WHERE salary NOT BETWEEN 6000 AND 8000;

SELECT employee_id,last_name,salary FROM employees where salary < 6000 or salary > 8000;

9.查询姓名以张开头的员工信息

SELECT last_name FROM employees WHERE last_name LIKE '张%';

10.查询last_name中包含字符'张'且包含字符'陈'的员工信息

SELECT last_name FROM employees WHERE last_name LIKE '%张%' and last_name like '%陈%';

11.选择部门在12,20工作的员工姓名和部门号

SELECT last_name FROM employees WHERE department_id in (12,20);

12.选择公司中没有管理者的员工姓名及job_id

SELECT last_name, job_id FROM employees WHERE manager_id IS NULL;

13.选择公司中有奖金的员工姓名，工资和奖金级别

SELECT last_name,salary,commission_pct FROM employees WHERE commission_pct IS NOT NULL;

14.显示所有员工的姓名，部门号和部门名称

SELECT e.last_name,e.department_id,d.department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;

15.查询90号部门员工的job_id和90号部门的location_id

SELECT e.job_id,l.location_id,e.department_id FROM employees e JOIN locations l ON e.department_id = e.department_id WHERE department_id = 90;

16.查询哪些部门没有经理

SELECT d.department_id,d.department_name FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id WHERE e.department_id IS NULL;

17.查询员工号，姓名，工资，以及工资提高百分之20%后的结果

SELECT employee_id,last_name,salary,salary * 1.2 "new salary FROM employees;

18.将员工的姓名按首字母排序，并写出姓名的长度

SELECT last_name,LENGTH(last_name) FROM employees ORDER BY last_name DESC;

19.查询员工id,last_name,salary，并作为一个列输出

SELECT CONCAT(employee_id,',',last_name,',',salary) "OUT_PUT" FROM employees;

20.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序

SELECT DATEDIFF(SYSDATE(), hire_date) / 365 worked_years, DATEDIFF(SYSDATE(), hire_date) worked_days FROM employees ORDER BY worked_years DESC;

21.查询员工姓名，hire_date , department_id，满足以下条件：雇用时间在1997年之后，department_id 为80 或 90 或110

SELECT last_name,hire_date,department_id,commission_pct
FROM employees
WHERE hire_date > '1997-01-01'
AND department_id IN(80,90,110)
AND commission_pct IS NOT NULL
ORDER BY hire_date DESC;

22.查询公司中入职超过10000天的员工姓名、入职时间

SELECT last_name,DATEDIFF(CURDATE(),hire_date)"date",hire_date FROM employees WHERE DATEDIFF(CURDATE(),hire_date) > 10000;

23.做一个查询，产生下面的结果 <last_name> earns monthly but wants <salary*3>

SELECT CONCAT(last_name,'earns',TRUNCATE(salary,0),'monthly but want',TRUNCATE(salary * 3,0))"dream salary" FROM employees;

24.使用case-when，按照下面的条件： job grade AD_PRES A ST_MAN B IT_PROG C SA_REP D ST_CLERK E

SELECT last_name,job_id, CASE job_id WHEN 'AD_PRES' THEN 'A'
                                     WHEN 'ST_MAN' THEN 'B'
                                     WHEN 'IT_PROG' THEN 'C'
                                     WHEN 'SA_REP' THEN 'D'
                                     WHEN 'ST_CLERK' THEN 'E'
                                     ELSE 'F'
                                     END "grade"
FROM employees;

25.查询公司员工工资的最大值，最小值，平均值，总和

SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary) FROM employees;

26.查询各job_id的员工工资的最大值，最小值，平均值，总和

SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary),job_id FROM employees GROUP BY job_id;

27.选择具有各个job_id的员工人数

SELECT job_id,COUNT(*) FROM employees GROUP BY job_id;

28.查询员工最高工资和最低工资的差距

SELECT MAX(salary),MIN(salary),TRUNCATE(MAX(salary) - MIN(salary),0)"DIFFERENCE" FROM employees;

29.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内

SELECT manager_id,salary FROM employees WHERE manager_id IS NOT NULL GROUP BY manager_id

HAVING MIN(salary) > 6000;

30.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序

SELECT de.department_name,de.location_id,COUNT(employee_id),AVG(salary)"avg_salary"
FROM departments de LEFT JOIN employees em
ON de.department_id = em.department_id
GROUP BY de.department_id,de.location_id
ORDER BY avg_salary DESC;

31.查询每个工种、每个部门的部门名、工种名和最低工资

SELECT department_name,job_id,MIN(salary)
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
GROUP BY e.job_id,d.department_name

32.查询和Zlotkey相同部门的员工姓名和工资

SELECT last_name,salary,department_id
FROM employees e1
WHERE department_id =(
                       SELECT department_id
                       FROM employees e2
                       WHERE last_name = 'Zlotkey'
                     );

33.查询工资比公司平均工资高的员工的员工号，姓名和工资。

SELECT department_id,last_name,salary
FROM employees
WHERE salary > (
                 SELECT AVG(salary)
                 FROM employees
               );

34.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary

SELECT last_name, job_id, salary
FROM employees
WHERE salary > ALL (
                     SELECT salary
                     FROM employees
                     WHERE JOB_ID = 'SA_MAN'
                   );

35.查询姓名中包含字母u的员工在相同部门的员工的员工号和姓名

SELECT employee_id,last_name,department_id
FROM employees
WHERE department_id = ANY(
                          SELECT DISTINCT department_id
                          FROM employees
                          WHERE last_name LIKE '%u%'
                         )

36.查询在部门的location_id为1700的部门工作的员工的员工号

SELECT last_name,employee_id,department_id
FROM employees
WHERE department_id in(
                       SELECT department_id
                       FROM departments
                       WHERE location_id = 1700
                      );

37.查询工资最低的员工信息: last_name, salar

SELECT last_name,salary FROM employees WHERE salary =(

SELECT MIN(salary)

FROM employees);

38.查询平均工资最低的部门信息(每个部门小于平均)

SELECT *
FROM departments
WHERE department_id =(
                      SELECT department_id
                      FROM employees
                      GROUP BY department_id
                      HAVING AVG(salary) <= ALL(
                                                SELECT AVG(salary)
                                                FROM employees
                                                GROUP BY department_id
                                                )
                      );

39.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）

SELECT d.*,(SELECT AVG(salary) FROM employees WHERE department_id = d.department_id)"avg_salary"
FROM departments d
WHERE department_id = (
                       SELECT department_id
                       FROM employees e
                       GROUP BY department_id
                       HAVING AVG(salary) <= ALL (
                                                  SELECT AVG(salary)
                                                  FROM employees
                                                  GROUP BY department_id
                                                  )
                       );

40.查询平均工资最高的 job 信息

SELECT *
FROM jobs
WHERE job_id =(
               SELECT job_id
               FROM employees
               GROUP BY job_id
               HAVING AVG(salary) >= ALL(
                                         SELECT AVG(salary)
                                         FROM employees
                                         GROUP BY job_id
                                         )
               );

41.查询平均工资高于公司平均工资的部门有哪些?

SELECT department_id,AVG(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING AVG(salary) >(
                      SELECT AVG(salary)
                      FROM employees
                     )

42.查询出公司中所有 manager 的详细信息

SELECT *
FROM employees
WHERE manager_id in(
                    SELECT DISTINCT manager_id
                    FROM employees
                   )

SELECT e1.last_name,e1.employee_id,e1.department_id,e1.manager_id

FROM employees e1 JOIN employees e2

ON e1.employee_id = e2.manager_id

43.各个部门中最高工资中最低的那个部门的最低工资是多少?

SELECT MIN(salary)
FROM employees
WHERE department_id =(
                      SELECT department_id
                      FROM employees
                      GROUP BY department_id
                      HAVING MAX(salary) <= ALL (
                                                SELECT MAX(salary)
                                                FROM employees
                                                GROUP BY department_id
                                                )

44.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

查询平均工资最高的部门

SELECT department_id
                                              FROM employees
                                              GROUP BY department_id
                                              HAVING AVG(salary) >= ALL(
                                                                        SELECT AVG(salary)
                                                                        FROM employees
                                                                        GROUP BY department_id

SELECT last_name, department_id, email, salary
FROM employees
WHERE employee_id IN (
                        SELECT manager_id
                        FROM employees
                        WHERE department_id =(
                                              SELECT department_id
                                              FROM employees
                                              GROUP BY department_id
                                              HAVING AVG(salary) >= ALL(
                                                                        SELECT AVG(salary)
                                                                        FROM employees
                                                                        GROUP BY department_id
                                                                       )
                                        )
                  );

45.查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号

SELECT department_id
FROM departments
WHERE department_id NOT IN(
                           SELECT DISTINCT department_id
                           FROM employees
                           WHERE job_id = 'ST_CLERK'
                          );

46.选择所有没有管理者的员工的last_name

SELECT last_name
FROM employees e1
WHERE NOT EXISTS(
                SELECT manager_id
                FROM employees e2
                WHERE e1.manager_id = e2.manager_id
                )

47.查询员工号、姓名、雇用时间、工资，其中员工的管理者为 'De Haan'

SELECT employee_id,last_name,hire_date,salary
FROM employees
WHERE manager_id =(
                  SELECT manager_id
                  FROM employees
                  WHERE last_name = 'De Haan'
                  )

48.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询）

SELECT employee_id,last_name,salary
FROM employees e1
WHERE salary >(
              SELECT AVG(salary)
              FROM employees e2
              WHERE e1.department_id = e2.department_id
              )

49.查询每个部门下的部门人数大于 5 的部门名称（相关子查询）

SELECT department_id,department_name
FROM departments d
WHERE 5 <(
           SELECT COUNT(*)
           FROM employees e
           WHERE d.department_id = e.department_id
         )

50.按照note分类统计书的库存量，显示库存量超过30本的

SELECT note,SUM(num)"sum_num"

FROM books 

GROUP BY note

HAVING sum_num>30

51.查询所有图书，每页显示5本，显示第二页

SELECT * FROM books LIMIT 5,5;

SELECT note,SUM(num) "sum_num"
FROM books
GROUP BY note
HAVING SUM(num) >=ALL(
                  SELECT SUM(num)
                  FROM books
                  GROUP BY note
                  );

SELECT note,SUM(num) sum_num 

FROM books 

GROUP BY note 

ORDER BY sum_num 

DESC LIMIT 0,1;

52.查询书名和类型，其中note值为novel显示小说，law显示法律，medicine显示医药，cartoon显示卡通， joke显示笑话

SELECT name AS "书名" ,note, 
CASE note WHEN 'novel' THEN '小说' 
          WHEN 'law' THEN '法律' 
          WHEN 'medicine' THEN '医药' 
          WHEN 'cartoon' THEN '卡通' 
          WHEN 'joke' THEN '笑话' 
          END AS "类型" 
          FROM books;

53.查询书名、库存，其中num值超过30本的，显示滞销，大于0并低于10的，显示畅销，为0的显示需要无货

SELECT `name`,num, 
CASE WHEN num>30 THEN '滞销' 
     WHEN num>0 AND num<10 THEN '畅销' 
     WHEN num=0 THEN '无货' 
     ELSE '正常' 
     END  "库存状态" 
     FROM books;

54.统计每一种note的库存量，并合计总量

SELECT IFNULL(note,'总量') "note",SUM(num)

FROM books

GROUP BY note WITH ROLLUP;

SELECT note,SUM(num)

FROM books

GROUP BY note WITH ROLLUP;

55.统计每一种note的数量，并合计总量

SELECT IFNULL(note,'总量')AS note,COUNT(*)

FROM books

GROUP BY note WITH ROLLUP;

56.统计库存量前三名的图书

SELECT *

FROM books

ORDER BY num DESC 

LIMIT 0,3;

57.找出最早出版的一本书

SELECT *

FROM books

WHERE pubdate = (

                SELECT MIN(pubdate)

                FROM books

                )

58.找出novel中价格最高的一本书

SELECT *
FROM books
WHERE note='novel'
GROUP BY note
HAVING MAX(price) in(
                     SELECT MAX(price)
                     FROM books
                     GROUP BY note
                     )

59.找出书名中字数最多的一本书，不含空格

SELECT * FROM books 

ORDER BY CHAR_LENGTH(REPLACE(name,' ','')) DESC 

LIMIT 0,1;