DQL语言实例

-- 1. 查询员工号为176的员工的姓名和部门号和年薪

SELECT first_name,last_name,department_id,salary12(1+IFNULL(commission_pct,0)) AS 年薪 FROM employees WHERE employee_id in(176);

-- 2. 选择工资不在5000到12000的员工的姓名和工资 -- 方式一： SELECT first_name,last_name, salary FROM employees WHERE salary < 5000 OR salary > 12000;

-- 方式二： SELECT first_name,last_name, salary FROM employees WHERE salary NOT BETWEEN 5000 and 12000;

-- 方式三： SELECT first_name,last_name, salary FROM employees WHERE NOT (salary BETWEEN 5000 AND 12000);

-- 3. 选择在20或50号部门工作的员工姓名和部门号

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

-- 4. 选择姓名中有字母a和e的员工姓名

-- 方法一： SELECT first_name,last_name FROM employees WHERE first_name LIKE '%a%' AND first_name LIKE'%e%';

-- 方法二： SELECT first_name, last_name FROM employees WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';

-- 5. 显示出表employees部门编号在80-100之间的姓名、职位

SELECT first_name, last_name, job_id, department_id FROM employees

WHERE department_id BETWEEN 80 AND 100;

 -- 1. 将姓名中包含e字符的年薪和姓名显示出来，并且按年薪进行降序
SELECT first_name, last_name, salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
FROM employees 
ORDER BY 年薪 DESC;

-- 2. 显示所有有奖金的员工姓名、奖金、姓名的长度，按姓名的长度从低到高排序
SELECT first_name, last_name, salary*IFNULL(commission_pct,0) AS 奖金,LENGTH(last_name) AS 姓名的长度
FROM employees
ORDER BY 姓名的长度 ASC;

SELECT first_name, last_name, salary*IFNULL(commission_pct,0) 奖金,LENGTH(last_name) 姓名的长度
FROM employees
ORDER BY 姓名的长度 ASC;

 -- 3. 部门编号>50的按工资从高到低排序，如果一样，再按frist_name升序
SELECT * FROM employees
WHERE department_id >50
ORDER BY salary DESC, LENGTH(first_name) ASC;

-- 1.显示系统时间(注：日期+时间) SELECT NOW(); #2022-09-13 09:12:09

-- 2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary） SELECT employee_id,last_name,salary,salary*1.2 'new salary' FROM employees;

-- 3.将员工的姓名按首字母排序，并写出姓名的长度（length） SELECT LENGTH(last_name) 长度 ,SUBSTR(last_name,1,1) 首字符, last_name FROM employees ORDER BY 首字符;

#二、分组函数

/*
功能:用作统计使用，又称为聚合函数或统计函数或组函数分类:
sum求和、avg平均值、max最大值、min最小值、count计算个数

特点:
1-sum、avg—般用于处理数值型
max、min、 count可以处理任何类型

2、以上分组函数都忽略null值
3、都可以搭配distinct使用，用于统计去重后的结果 
4、count的参数可以支持：
        * 字段、*、常量值，一般放1
                效率:
MYISAM存储引擎下, COUNT(*)的效率高
INNODB存储引擎下， COUNT(*)和 COUNT(1)的效率差不多，比 COUNT(字段)要高一些
5、和分组函数一同查询的字段要求是group by后的字段
注意：聚合函数的计算，排除null值。
    解决方案：
        * 选择不包含非空的列进行计算
        * IFNULL函数

*/
#1、简单使用

SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;
SELECT SUM(salary)和,AVG(salary)平均,MAX(salary)最高,MIN(salary)最低, COUNT(salary)个数
FROM employees;

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

DATEDIFF

#2.查询员工表中的最大入职时间和最小入职时间的相差天数(DIFFRENCE)
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) DIFFRENCE
FROM employees;  #8735 

-- 3. 查询部门编号为90的员工个数
SELECT COUNT(*) 员工个数
FROM employees
WHERE department_id = 90; #3

#进阶5:分组查询

/*
语法:
select分组函数,列（要求出现在group by的后面)
from表
【where筛选条件】
group by分组的列表
【order by子句】

注意:
            查询列表必须特殊，要求是分组函数和group by后出现的字段
            */
            
#引入:查询每个都门的平均工资
SELECT AVG (salary) FROM employees;

#案例1 :查询每个工种的最高工资
SELECT MAX(salary), job_id
FROM employees
GROUP BY job_id;

#案例1:查询邮箱中包含a字符的,每个部门的平均工资
SELECT AVG(salary), department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

 
#案例2:查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary), manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

 #添加分组后复杂的筛选条件
-- 案例1：查询哪个部门员工个数>2
#①查询每个部门的员工个数
SELECT COUNT(*), department_id
FROM employees
GROUP BY department_id;
#②根据①的结果进行筛选，查询哪个部门的员工个数>2
SELECT COUNT(*) , department_id
FROM employees
GROUP BY department_id 
HAVING COUNT(*) >2;

练习二
-- 排序&分组查询作业
-- 1.    查询员工的姓名和部门号和年薪，按年薪降序按姓名升序
SELECT last_name, department_id, salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 ASC,last_name DESC;

-- 2.    选择工资不在8000到17000的员工的姓名和工资，按工资降序
SELECT last_name, salary
FROM employees 
WHERE salary NOT BETWEEN 8000 AND 17000
ORDER BY salary DESC;

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

-- 4.做一个查询，产生下面的结果：
-- earns monthly but wants
-- Dream Salary
-- King earns 24000 monthly but wants 72000
SELECT CONCAT(last_name,'earns',TRUNCATE(salary,0),'monthly but wants',TRUNCATE(salary * 3,0)) 'Dream Salary'
FROM employees;
-- TRUNCATE()函数介绍
-- TRUNCATE(X,D)是MySQL自带的一个系统函数。其中，X是数值，D是保留小数的位数。
-- 其作用就是按照小数位数，进行数值截取（此处的截取是按保留位数直接进行截取，没有四舍五入)

--     6. 查询员工表中的最大入职时间和最小入职时间的相差天数（DIFFRENCE）
SELECT MAX(hiredate) 最大,MIN(hiredate) 最小,(MAX(hiredate) - MIN(hiredate))/1000/3600/24 DIFFRENCE
FROM employees;

SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) DIFFRENCE
FROM employees;

-- DATEDIFF():返回两个日期之间的时间间隔

 -- 7. 查询员工最高工资和最低工资的差距（DIFFERENCE）
SELECT MAX(salary) max, MIN(salary) min, (MAX(salary) - MIN(salary)) DIFFERENCE
FROM employees;

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

03.多表连接查询

 -- 1. 查询90号部门员工的job_id和90号部门的location_id
SELECT job_id, location_id
FROM employees e,departments d
WHERE e.department_id = d.department_id
AND e.department_id = 90;

SELECT job_id,location_id
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND e.`department_id`=90;

-- 2. 选择所有有奖金的员工的last_name,department_name,location_id,city
SELECT last_name,department_name,d.location_id,city
FROM employees e, locations l, departments d
WHERE e.department_id = d.department_id
AND d.location_id=l.location_id
AND e.commission_pct IS NOT NULL;
   

 -- 3. 选择city在Toronto工作的员工的last_name , job_id , department_id , department_name 
SELECT last_name , job_id , d.department_id , department_name 
FROM employees e, locations l, departments d
WHERE d.location_id = l.location_id
AND e.department_id = d.department_id
AND city = 'Toronto';

 -- 4.    查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充    
SELECT bo.* , be.id
FROM boys bo
RIGHT JOIN beauty be
ON bo.id = be.boyfriend_id
WHERE be.id>3;

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

 SELECT employee_id,last_name,e.department_id
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND last_name LIKE '%u%';

-- 6.    查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为姓名                
SELECT CONCAT(first_name,last_name) 姓名
FROM employees
WHERE salary=(
        SELECT MAX(salary)
        FROM employees
);

-- DDL（data definition language）是数据定义语言-- 数据库操作
-- 创建db_demo数据库，设置数据库字符集为utf8;
CREATE DATABASE db_demo CHARACTER SET utf8;

-- 使用db_demo数据库
-- 1. 创建表dept1
-- | 字段名 | 是否为空 | 数据类型    |
-- | ------ | -------- | ----------- |
-- | id     |          | int(7)      |
-- | name   |          | varchar(25) |

CREATE TABLE dept1(
    id INT(7),
    NAME VARCHAR(25)
);

 -- 2. 创建表emp5
-- | 字段名     | 是否为空 | 数据类型    |
-- | ---------- | -------- | ----------- |
-- | id         |          | int(7)      |
-- | frist_name |          | varchar(25) |
-- | last_name  |          | varchar(25) |
-- | dept_id    |          | int(7)      |
CREATE TABLE emp5(
id INT(7),
frist_name VARCHAR(25),
last_name VARCHAR(25),
dept_id INT(7) 
);

homework3库综合查询作业

-- 1、查询销售量最高产品的前2名(使用in关键字)

SELECT *

FROM production

WHERE p_no IN (

SELECT p_no FROM (SELECT p_no FROM deal GROUP BY p_no ORDER BY SUM(volume) DESC LIMIT 2) p

);

-- 2、查询每种产品销售量最高的销售人员(使用group by关键字)

SELECT s.s_no,s.s_name,volume

FROM sales s, deal d

WHERE d.s_no = s.s_no

AND volume IN(

              SELECT MAX(volume) FROM deal GROUP BY p_no

);

-- 3、查询产品编号为2且销量量超过100的销售人员的姓名及所在公司

SELECT s.s_name 姓名, a.agent_name 公司 , d.volume 销量

FROM sales s, agent a, deal d

WHERE s.agent_no = a.agent_no

AND s.s_no = d.s_no

AND d.p_no=2

AND d.volume>100;

-- 4、查询所有代理商所有产品的销售量

SELECT a.`agent_name` 代理商,SUM(volume) 产品销售量

FROM agent a,sales s ,deal d

WHERE a.`agent_no`=s.`agent_no` AND s.`s_no`=d.`s_no`

GROUP BY a.`agent_name`;

-- 5、查询每个产品有多少个销售人员在销售

SELECT d.p_no 产品编号, COUNT(*) 销售人员数量

FROM deal d,sales s

WHERE d.`s_no`=s.`s_no`

GROUP BY d.`p_no`;

-- 6、查询名称包含BBB的代理商中的所有销售人员

SELECT a.agent_no 代理商编号, a.agent_name 代理商, s.s_name 销售人员

FROM agent a, sales s

WHERE a.agent_no = s.agent_no

AND a.agent_name LIKE '%BBB%';

-- 7、查询总销量最差的产品

SELECT p.p_no 产品编号, SUM(volume) 总销量

FROM production p, deal d

WHERE p.p_no = d.p_no

GROUP BY p.p_no

ORDER BY SUM(volume) ASC

LIMIT 1;

-- 8、查询2017年生产的产品的总销量

SELECT SUM(volume) '2017年总销售量'

FROM production p,deal d

WHERE d.p_no=p.p_no

AND p.p_date BETWEEN '2017-01-01' AND '2017-12-31';