MySQL练习整理（每日习题）

nppe6

已于 2022-04-17 14:20:30 修改

阅读量2k

点赞数 1

文章标签： mysql 数据库

于 2022-03-26 15:51:39 首次发布

本文链接：https://blog.csdn.net/nppe6/article/details/123757416

版权

2022年3月26日笔记

# 1.查询员工12个月的工资总和，并起别名为ANNUAL SALARY 
#第一种理解方式,只计算了基本工资
SELECT employee_id,last_name,salary * 12 "ANNUAL SALARY"
FROM employees; 
#第二种理解方式，基本工资加上了奖金  用到了IFNULL的语句
SELECT employee_id,last_name,salary * 12 * (1 + IFNULL (commission_pct,0))"ANNUAL SALARY"
FROM employees;

#2.查询employees表中去除重复的job_id以后的数据
SELECT DISTINCT job_id
FROM employees;

# 3.查询工资大于12000的员工姓名和工资 
SELECT last_name,salary
FROM employees
WHERE salary >= 12000;

# 4.查询员工号为176的员工的姓名和部门号
SELECT last_name,department_id
FROM employees
WHERE employee_id = 176;

# 5.显示表 departments 的结构，并查询其中的全部数据
DESC departments;
SELECT * FROM departments;

2022年3月27日笔记，第二天...

# 在SQL中，+没有连接的作用，就表示加法运算。此时，会将字符串转换为数值（隐式转换）
SELECT 100 + '1'  # 在Java语言中，结果是：1001。 
FROM DUAL;
SELECT 100 + 'a' #此时将'a'看做0处理
FROM DUAL;

SELECT 100 + NULL  # null值参与运算，结果为null
FROM DUAL;

SELECT 100, 100 * 1, 100 * 1.0, 100 / 1.0, 100 / 2,
100 + 2 * 5 / 2,100 / 3, 100 DIV 0  # 分母如果为0，则结果为null
FROM DUAL;
#练习：查询员工id为偶数的员工信息
SELECT employee_id,last_name,salary
FROM employees
WHERE employee_id % 2 = 0;

#练习：查询表中commission_pct为null的数据有哪些
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct <=> NULL;

SELECT 3 <> 2,'4' <> NULL, '' != NULL,NULL != NULL
FROM DUAL;

#① IS NULL \ IS NOT NULL \ ISNULL
#练习：查询表中commission_pct为null的数据有哪些
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NULL;

#练习：查询表中commission_pct不为null的数据有哪些
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

#查询工资在6000 到 8000的员工信息
SELECT employee_id,last_name,salary
FROM employees
#where salary between 6000 and 8000;
WHERE salary >= 6000 && salary <= 8000;

#交换6000 和 8000之后，查询不到数据
SELECT employee_id,last_name,salary
FROM employees
WHERE salary BETWEEN 8000 AND 6000;

#查询工资不在6000 到 8000的员工信息
SELECT employee_id,last_name,salary
FROM employees
WHERE salary NOT BETWEEN 6000 AND 8000;
#where salary < 6000 or salary > 8000;

⑤ LIKE :模糊查询
# % : 代表不确定个数的字符 （0个，1个，或多个）

#练习：查询last_name中包含字符'a'的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%';

#练习：查询last_name中以字符'a'开头的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE 'a%';

#练习：查询last_name中包含字符'a'且包含字符'e'的员工信息
#写法1：
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';
#写法2：
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';

# _ ：代表一个不确定的字符

#练习：查询第3个字符是'a'的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';

#练习：查询第2个字符是_且第3个字符是'a'的员工信息
#需要使用转义字符: \ 
SELECT last_name
FROM employees
WHERE last_name LIKE '_\_a%';

#或者  (了解)
SELECT last_name
FROM employees
WHERE last_name LIKE '_$_a%' ESCAPE '$';

#⑥ REGEXP \ RLIKE :正则表达式

SELECT 'shkstart' REGEXP '^shk', 'shkstart' REGEXP 't$', 'shkstart' REGEXP 'hk'
FROM DUAL;

SELECT 'atguigu' REGEXP 'gu.gu','atguigu' REGEXP '[ab]'
FROM DUAL;

#####################

续：题目练习·

# 1.选择工资不在5000到12000的员工的姓名和工资
#第一种方式
SELECT last_name,salary
FROM employees
WHERE salary  NOT BETWEEN 5000 AND 12000;
#第二种方式
SELECT last_name,salary
FROM employees
WHERE salary < 5000 AND salary > 12000; 

# 2.选择在20或50号部门工作的员工姓名和部门号 
SELECT last_name,department_id
FROM employees
WHERE department_id in(20,50);
#或是
SELECT last_name,department_id
FROM employees
WHERE department_id = 20 or department_id = 50;

# 3.选择公司中没有管理者的员工姓名及job_id 
SELECT * FROM employees;
SELECT last_name,job_id,manager_id
FROM employees
WHERE manager_id IS NULL;

# 4.选择公司中有奖金的员工姓名，工资和奖金级别
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

# 5.选择员工姓名的第三个字母是a的员工姓名  LIKE表模糊大概的意思
SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';

# 6.选择姓名中有字母a和k的员工姓名 
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE '%k%';
#或
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%k%' or last_name LIKE '%k%a%';

# 7.显示出表 employees 表中 first_name 以 'e'结尾的员工信息
SELECT first_name
FROM employees
WHERE first_name LIKE '%e';

# 8.显示出表 employees 部门编号在 80-100 之间的姓名、工种
SELECT department_id,last_name,job_id
FROM employees
WHERE department_id BETWEEN 80 AND 100;


# 9.显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、工资、管理者id
SELECT manager_id,last_name,salary
FROM employees
WHERE manager_id IN(100,101,110);

2022年3月29日，星期三

######################################

#1. 查询员工的姓名和部门号和年薪，按年薪降序,按姓名升序显示 
SELECT last_name,employee_id,salary,salary * 12 "annual salary"
FROM employees
ORDER BY "annual salary" DESC,last_name ASC;
#紧跟列名，也可以在列名和别名之间加入关键字AS，别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写。
#错误实列：ORDER BY annual salary DESC,last_name ASC;

#2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序，显示第21到40位置的数据 
SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC
LIMIT 20,20;

#3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT employee_id,department_id,last_name
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,department_id ASC;

2022年3月30日，天气阴

今天学习多表查询，从中学习了SQL92和SQL99语法的区别与使用。

########################################################

# 外连接：合并具有同一列的两个以上的表的行, 结果集中除了包含一个表与另一个表匹配的行之外，
#         还查询到了左表 或 右表中不匹配的行。

# 外连接的分类：左外连接、右外连接、满外连接

# 左外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行，这种连接称为左外连接。
# 右外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的行，这种连接称为右外连接。



#SQL92语法实现内连接：见上，略
#SQL92语法实现外连接：使用 +  ----------MySQL不支持SQL92语法中外连接的写法！

#SQL99语法中使用 JOIN ...ON 的方式实现多表的查询。这种方式也能解决外连接的问题。MySQL是支持此种方式的。
#SQL99语法如何实现多表的查询。

#9. 7种JOIN的实现：

# 中图：内连接
SELECT employee_id,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;

# 左上图：左外连接
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

# 右上图：右外连接
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

# 左中图：
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL;

# 右中图：
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;


# 左下图：满外连接
# 方式1：左上图 UNION ALL 右中图

SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;


# 方式2：左中图 UNION ALL 右上图

SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

# 右下图：左中图  UNION ALL 右中图
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

续练习整理：

#########################################################

# 1.显示所有员工的姓名，部门号和部门名称。 
SELECT * FROM locations;
SELECT * FROM departments;
SELECT e.last_name,e.department_id,d.department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;

# 2.查询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;   #------->SQL99的表达习惯这种方式因为在外连接上不支持直接写SQL92的语法
#或
SELECT e.job_id,l.location_id,e.department_id
FROM employees e,locations l
WHERE e.department_id = e.department_id AND department_id = 90;#------>SQL92
# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city 
SELECT e.last_name,d.department_name,l.location_id,l.city,e.commission_pct
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
LEFT JOIN locations l
ON l.location_id = d.location_id
WHERE e.commission_pct IS NOT NULL

# 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name 
SELECT e.last_name,e.job_id,e.department_id,d.department_name,l.city
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
LEFT JOIN locations l
ON l.location_id = d.location_id
WHERE l.city IN('Toronto')

# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’
SELECT d.department_name,d.department_id,e.job_id,e.salary
FROM  employees e  JOIN departments d
ON e.department_id = d.department_id
WHERE department_name IN('Executive')

# 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式 
#employees Emp# manager Mgr# kochhar 101 king 100 
SELECT emp.employee_id "#Emp",emp.last_name employees,mar.employee_id "#Mar",mar.last_name manager
FROM employees Emp
LEFT JOIN employees mar
ON emp.employee_id = mar.employee_id
 
# 7.查询哪些部门没有经理 
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;
# 8. 查询哪个城市没有部门
SELECT l.city,d.department_id,d.department_name
FROM locations l LEFT JOIN departments d
ON l.location_id = d.location_id
WHERE department_id IS NULL;
 
# 9. 查询部门名为 Sales 或 IT 的员工信息
SELECT e.last_name,e.employee_id,l.city,d.department_id,d.department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
WHERE department_name IN('Sales','It');

续...

#############################################3

#1.所有有门派的人员信息 （ A、B两表共有）
SELECT * FROM t_dept;
SELECT * FROM t_emp;
SELECT d.deptName,e.`name`,d.id 
FROM t_dept d RIGHT JOIN t_emp e
ON d.id = e.id
WHERE deptName IS NOT NULL;
 
#2.列出所有用户，并显示其机构信息 （A的全集）
SELECT *
FROM t_dept d RIGHT JOIN t_emp e
ON d.id = e.deptId

select *
from t_emp a left join t_dept b 
on a.deptId = b.id;
#3.列出所有门派 （B的全集）
select *
from t_dept b;

#4.所有不入门派的人员 （A的独有）
SELECT *
FROM t_emp e LEFT JOIN t_dept d
ON e.deptId = d.id
WHERE e.deptId IS NULL;

select *
from t_emp a left join t_dept b 
on a.deptId = b.id 
where b.id is null;

#5.所有没人入的门派 （B的独有）
SELECT *
FROM t_emp e RIGHT JOIN t_dept d 
ON e.deptId = d.id
WHERE e.deptId IS NULL;

select *
from t_dept b left join t_emp a 
on a.deptId = b.id where a.deptId is null;
 
#6.列出所有人员和机构的对照关系 (AB全有) #MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法 #left join + union(可去除重复数据)+ right join
SELECT *
FROM t_emp e LEFT JOIN t_dept d
ON e.deptId = d.id
UNION ALL
SELECT *
FROM t_emp e RIGHT JOIN t_dept d
ON e.deptId = d.id
WHERE e.deptId IS NULL;
 
#7.列出所有没入派的人员和没人入的门派 （A的独有+B的独有）
SELECT *
FROM t_dept d RIGHT JOIN t_emp e
ON d.id = e.deptId
WHERE e.deptId IS NULL
UNION ALL
SELECT *
FROM t_emp e RIGHT JOIN t_dept d
ON e.deptId = d.id
WHERE e.deptId IS NULL

2022年4月4日天气晴

单行函数的练习

############################################################

# 1.显示系统时间(注：日期+时间) 
SELECT CURDATE(),CURTIME(),NOW(),SYSDATE()
FROM DUAL;

SELECT * FROM employees;
# 2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
SELECT employee_id,last_name,salary,salary * 1.2 "new salary"
FROM employees
 
# 3.将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT last_name,LENGTH(last_name)
FROM employees
ORDER BY last_name DESC;
 
# 4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT
SELECT CONCAT(employee_id,',',last_name,',',salary) "OUT_PUT"
FROM employees;
 
# 5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序
SELECT DATEDIFF(CURDATE(),hire_date) / 365 "worked_years",DATEDIFF(CURDATE(),hire_date)"worked_date"
FROM employees
ORDER BY worked_years DESC;

SELECT DATEDIFF(SYSDATE(), hire_date) / 365 worked_years, DATEDIFF(SYSDATE(), hire_date) worked_days 
FROM employees 
ORDER BY worked_years DESC;
# 6.查询员工姓名，hire_date , department_id，满足以下条件：雇用时间在1997年之后，department_id 为80 或 90 或110, commission_pct不为空
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;
 
# 7.查询公司中入职超过10000天的员工姓名、入职时间 
SELECT last_name,DATEDIFF(CURDATE(),hire_date)"date",hire_date
FROM employees
WHERE DATEDIFF(CURDATE(),hire_date) > 10000;#WHERE 是不支持识别别名的！！！

# 8.做一个查询，产生下面的结果 <last_name> earns <salary> monthly but wants <salary*3> 
SELECT CONCAT(last_name,'earns',TRUNCATE(salary,0),'monthly but want',
TRUNCATE(salary * 3,0))"dream salary"
FROM employees;

# 9.使用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;

2022年4月7号天气晴朗

聚合函数练习

#######################################################3

#1.where子句可否使用组函数进行过滤? 
NO

#2.查询公司员工工资的最大值，最小值，平均值，总和 
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees;

#3.查询各job_id的员工工资的最大值，最小值，平均值，总和
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary),job_id
FROM employees
GROUP BY job_id;

#4.选择具有各个job_id的员工人数
SELECT job_id,COUNT(*)
FROM employees
GROUP BY job_id;

SELECT * FROM departments;
# 5.查询员工最高工资和最低工资的差距（DIFFERENCE）
SELECT MAX(salary),MIN(salary),TRUNCATE(MAX(salary) - MIN(salary),0)"DIFFERENCE"
FROM employees;

# 6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
SELECT manager_id,salary
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) > 6000;
 
# 7.查询所有部门的名字，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;
 
# 8.查询每个工种、每个部门的部门名、工种名和最低工资
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

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%5

2022年4月10日子查询！666

111111111111111111111111111111111111111111111111111111111111111111111

#空值问题的注意事项！！！
#错误示例
SELECT last_name 
FROM employees 
WHERE employee_id NOT IN 
                         ( 
                            SELECT manager_id 
                            FROM employees
                            #WHERE manager_id IS NOT NULL 
                         );

#正确的查询方式！
SELECT last_name 
FROM employees 
WHERE employee_id NOT IN 
                         ( 
                            SELECT manager_id 
                            FROM employees
                            WHERE manager_id IS NOT NULL 
                         );
#NULL本身就为空值参与判断为受影响

2022年4月15 阴天

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

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

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

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

##############################################3

#1.查询和Zlotkey相同部门的员工姓名和工资
SELECT last_name,salary,department_id
FROM employees e1
WHERE department_id =(
                       SELECT department_id
                       FROM employees e2
                       WHERE last_name = 'Zlotkey'
                     );
#2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
SELECT department_id,last_name,salary
FROM employees
WHERE salary > (
                 SELECT AVG(salary)
                 FROM employees
               );
 
#3.选择工资大于所有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'
                   );
 
#4.查询姓名中包含字母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%'
                         )

#5.查询在部门的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
                      );

#6.查询管理者是King的员工姓名和工资 
SELECT last_name,salary,employee_id
FROM employees
WHERE manager_id in(
                    SELECT employee_id
                    FROM employees
                    WHERE last_name= 'king'
                   );


#7.查询工资最低的员工信息: last_name, salar
SELECT last_name,salary
FROM employees
WHERE salary =(
               SELECT MIN(salary)
               FROM employees
              );
#8.查询平均工资最低的部门信息
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
                                                )
                      );

#9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）
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
                                                  )
                       );
 
#10.查询平均工资最高的 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
                                         )
               );
#11.查询平均工资高于公司平均工资的部门有哪些?
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
                     )
 
#12.查询出公司中所有 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


#13.各个部门中 最高工资中最低的那个部门的 最低工资是多少?
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
                                                )   
)
#14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
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
                                                                       )
                                        )
                  );

#15. 查询部门的部门号，其中不包括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'
                          );

#16. 选择所有没有管理者的员工的last_name
SELECT last_name
FROM employees e1
WHERE NOT EXISTS(
                SELECT manager_id
                FROM employees e2
                WHERE e1.manager_id = e2.manager_id
                )
#17．查询员工号、姓名、雇用时间、工资，其中员工的管理者为 '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'
                  )
#18.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（相关子查询）
SELECT employee_id,last_name,salary
FROM employees e1
WHERE salary >(
              SELECT AVG(salary)
              FROM employees e2
              WHERE e1.department_id = e2.department_id
              )

#19.查询每个部门下的部门人数大于 5 的部门名称（相关子查询）
SELECT department_id,department_name
FROM departments d
WHERE 5 <(
           SELECT COUNT(*)
           FROM employees e
           WHERE d.department_id = e.department_id
         )

#20.查询每个国家下的部门个数大于 2 的国家编号（相关子查询）
SELECT country_id
FROM locations l1
WHERE 2 <(
          SELECT COUNT(*)
          FROM departments d
          WHERE l1.location_id = d.location_id
          )

2022年4月16日阴雨天

######################################################

创建管理表库（上）

#1. 创建数据库test01_office,指明字符集为utf8。并在此数据库下执行下述操作 
CREATE DATABASE test01_office CHARACTER SET 'utf8';
USE test01_office;

#2. 创建表dept01 
/*字段 类型 id INT(7) NAME VARCHAR(25) */
CREATE TABLE dept01
(id INT(7),
`name` VARCHAR(25)
);
DESC dept01

#3. 将表departments中的数据插入新表dept02中
CREATE TABLE dept02
AS
SELECT *
FROM atguigudb.departments

#4. 创建表emp01 
/*字段 类型 id INT(7)first_name VARCHAR (25) last_name VARCHAR(25) dept_id INT(7) */
CREATE TABLE emp01
(id INT(7),
first_name VARCHAR(25),
last_name VARCHAR(25),
dept_id INT(7)
);

#5. 将列last_name的长度增加到50
ALTER TABLE emp01
MODIFY last_name VARCHAR(50);
DESC emp01
 
#6. 根据表employees创建emp02 
CREATE TABLE emp02
AS
SELECT *
FROM atguigudb.employees;
SELECT *
FROM emp02;

#7. 删除表emp01
DROP TABLE emp01;
 
#8. 将表emp02重命名为emp01
RENAME TABLE emp02 TO emp01;
 
#9.在表dept02和emp01中添加新列test_column，并检查所作的操作
ALTER TABLE dept02 ADD test_column VARCHAR(10);
DESC dept02

ALTER TABLE emp01 ADD test_column VARCHAR(10);
DESC emp01
 
#10.直接删除表emp01中的列 department_id
ALTER TABLE emp01
DROP COLUMN department_id;

（下）

############################################################


# 1、创建数据库 test02_market 
CREATE DATABASE test02_market CHARACTER SET 'utf8';
USE test02_market;

# 2、创建数据表 customers 
CREATE TABLE customers
(c_num INT,
c_name VARCHAR(50),
c_contact VARCHAR(50),
c_city VARCHAR(50),
c_birth date
);
DESC customers;
# 3、将 c_contact 字段移动到 c_birth 字段后面 
ALTER TABLE customers
MODIFY c_contact VARCHAR(50) AFTER c_birth;

# 4、将 c_name 字段数据类型改为 varchar(70)
ALTER TABLE customers
MODIFY c_name varchar(70)
# 5、将c_contact字段改名为c_phone
ALTER TABLE customers
CHANGE c_contact c_phone VARCHAR(50);
 
# 6、增加c_gender字段到c_name后面，数据类型为char(1)
ALTER TABLE customers
ADD c_gender char(1) AFTER c_name;
 
# 7、将表名改为customers_info
RENAME TABLE customers TO customers_info;

# 8、删除字段c_city
ALTER TABLE customers_info
DROP COLUMN c_city;
DESC customers_info;

续...

——————>

# 1、创建数据库test03_company
CREATE DATABASE test03_company CHARACTER SET 'utf8';
USE test03_company;

# 2、创建表offices
CREATE TABLE offices
(officeCode INT,
city VARCHAR(30),
address VARCHAR(50),
country VARCHAR(50),
postalCode VARCHAR(25)
);
DESC offices;

# 3、创建表employees
CREATE TABLE employees
(empNum INT,
last_name VARCHAR(50),
first_name VARCHAR(50),
mobile VARCHAR(25),
`code` INT,
job_title VARCHAR(500),
birth date,
note VARCHAR(255),
sex VARCHAR(5)
);
DESC employees;
# 4、将表employees的mobile字段修改到code字段后面
ALTER TABLE employees
MODIFY mobile VARCHAR(25) AFTER `code`;
 
# 5、将表employees的birth字段改名为birthday
ALTER TABLE employees
CHANGE birth birthdate date;
 
# 6、修改sex字段，数据类型为char(1)
ALTER TABLE employees
MODIFY sex CHAR(1);
 
# 7、删除字段note
ALTER TABLE employees
DROP COLUMN note;
 
# 8、增加字段名favoriate_activity，数据类型为varchar(100)
ALTER TABLE employees
ADD favoriate_activity varchar(100);
 
# 9、将表employees的名称修改为 employees_info
RENAME TABLE employees TO employees_info;

DESC employees_info;

2022年4月17

综合练习############################

###################################

# 1、创建数据库test01_library 
CREATE DATABASE test01_library CHARACTER SET 'utf8';
USE test01_library;

# 2、创建表 books，表结构如下：
CREATE TABLE books
(id INT,
`name` VARCHAR(50),
`authors` VARCHAR(1000),
price FLOAT,
pubdate YEAR,
note VARCHAR(100),
num INT
);

DESC books;
# 3、向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);

SELECT * FROM books;

# 4、将小说类型(novel)的书的价格都增加5。
UPDATE books
SET price = price + 5
WHERE note = 'novel';
 
# 5、将名称为EmmaT的书的价格改为40，并将说明改为drama。
UPDATE books
SET price = 40
WHERE `name`= 'Emmat';
 
# 6、删除库存为0的记录。
DELETE FROM books
WHERE num = 0;
 
# 7、统计书名中包含a字母的书
SELECT *
FROM books
WHERE `name` LIKE '%a%';
 
# 8、统计书名中包含a字母的书的数量和库存总量 
SELECT count(*),SUM(num)
FROM books
WHERE `name` LIKE '%a%'

# 9、找出“novel”类型的书，按照价格降序排列
SELECT *
FROM books
WHERE note = 'novel'
ORDER BY price DESC;
 
# 10、查询图书信息，按照库存量降序排列，如果库存量相同的按照note升序排列 
SELECT *
FROM books
ORDER BY num DESC,note ASC;

# 11、按照note分类统计书的数量  聚合分组查询
SELECT count(*)
FROM books
GROUP BY note
 
# 12、按照note分类统计书的库存量，显示库存量超过30本的
SELECT note,SUM(num)"sum_num"
FROM books 
GROUP BY note
HAVING sum_num>30

# 13、查询所有图书，每页显示5本，显示第二页
SELECT *
FROM books
LIMIT 5,5;
# 14、按照note分类统计书的库存量，显示库存量最多的
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; 
# 15、查询书名达到10个字符的书，不包括里面的空格
SELECT *
FROM books

 
# 16、查询书名和类型，其中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;

# 17、查询书名、库存，其中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;


# 18、统计每一种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;

# 19、统计每一种note的数量，并合计总量 


SELECT IFNULL(note,'总量')AS note,COUNT(*)
FROM books
GROUP BY note WITH ROLLUP;



# 20、统计库存量前三名的图书
SELECT *
FROM books
ORDER BY num DESC 
LIMIT 0,3;
 
# 21、找出最早出版的一本书 
SELECT *
FROM books
WHERE pubdate = (
                SELECT MIN(pubdate)
                FROM books
                )


# 22、找出novel中价格最高的一本书 \
SELECT *
FROM books
WHERE note='novel'
GROUP BY note
HAVING MAX(price) in(
                     SELECT MAX(price)
                     FROM books
                     GROUP BY note
                     )

# 23、找出书名中字数最多的一本书，不含空格
SELECT * FROM books 
ORDER BY CHAR_LENGTH(REPLACE(name,' ','')) DESC 
LIMIT 0,1;

回顾------》

limit用法

分页
客户端通过传递start(页码)，pageSize(每页显示的条数)两个参数去分页查询数据库表中的数据，那我们知道MySql数据库提供了分页的函数limit m,n，但是该函数的用法和我们的需求不一样，所以就需要我们根据实际情况去改写适合我们自己的分页语句，具体的分析如下：

比如：

查询第1条到第10条的数据的sql是：select * from table limit 0,10; ->对应我们的需求就是查询第一页的数据：select * from table limit (1-1)*10,10;

查询第10条到第20条的数据的sql是：select * from table limit 10,10; ->对应我们的需求就是查询第二页的数据：select * from table limit (2-1)*10,10;

查询第20条到第30条的数据的sql是：select * from table limit 20,10; ->对应我们的需求就是查询第三页的数据：select * from table limit (3-1)*10,10;

练习————

###########################3

#1. 创建数据库dbtest11 
CREATE DATABASE IF NOT EXISTS dbtest11 CHARACTER SET 'utf8'; 
#2. 运行以下脚本创建表my_employees 
USE dbtest11; 
CREATE TABLE my_employees
( id INT(10),
first_name VARCHAR(10), 
last_name VARCHAR(10), 
userid VARCHAR(10), 
salary DOUBLE(10,2) );
CREATE TABLE users
( id INT, userid VARCHAR(10), 
department_id INT );
#3. 显示表my_employees的结构
DESC my_employees;
 
#4. 向my_employees表中插入下列数据
INSERT INTO my_employees
VALUES(1,'patel','Ralph','Ratpel',895), 
(2,'Dancs','Betty','Bdancs',860), 
(3,'Biri','Ben','Bbiri',1100), 
(4,'Newman','Chad','Cnewman',750), 
(5,'Ropeburn','Audrey','Aropebur',1550); 

SELECT *
FROM my_employees;
#5. 向users表中插入数据
INSERT INTO users
VALUES(1,'Rpatel',10), 
(2,'Bdancs',10), 
(3,'Bbiri',20), 
(4,'Cnewman',30), 
(5,'Aropebur',40);
 
SELECT *
FROM users;
#6. 将3号员工的last_name修改为“drelxer”
UPDATE my_employees
SET last_name = 'drelxer'
WHERE id =  3;
 
#7. 将所有工资少于900的员工的工资修改为1000
UPDATE my_employees
SET salary = 1000
WHERE salary < 900;
 
#8. 将userid为Bbiri的user表和my_employees表的记录全部删除
DELETE FROM my_employees
WHERE userid = 'Bbiri';

DELETE FROM users
WHERE userid = 'Bbiri';
 
#9. 删除my_employees、users表所有数据
DELETE FROM my_employees;
DELETE FROM users; 

#10. 检查所作的修正
SELECT *
FROM my_employees;

SELECT *
FROM users;

#11. 清空表my_employees
TRUNCATE TABLE my_employees;

DROP DATABASE dbtest11;

nppe6

关注

1
点赞
踩
11

收藏

觉得还不错? 一键收藏
0
评论
MySQL练习整理（每日习题）

# 1.查询员工12个月的工资总和，并起别名为ANNUAL SALARY #第一种理解方式,只计算了基本工资SELECT employee_id,last_name,salary * 12 "ANNUAL SALARY"FROM employees; #第二种理解方式，基本工资加上了奖金用到了IFNULL的语句SELECT employee_id,last_name,salary * 12 * (1 + IFNULL (commission_pct,0))"ANNUAL SALARY"FR.
复制链接

扫一扫