MySQL数据库DQL语言（二）

最新推荐文章于 2024-02-11 22:09:20 发布
最新推荐文章于 2024-02-11 22:09:20 发布
阅读量315 收藏 3
点赞数
分类专栏： Mysql 文章标签： 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/licmi/article/details/106817193
版权

MySQL

分组查询

概念

分组函数作用于一组数据，并对一组数据返回一个值。
在这里插入图片描述

组函数类型

功能：用作统计使用，又称为聚合函数或统计函数或组函数，sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数
特点：
1、sum、avg一般用于处理数值型
max、min、count可以处理任何类型
2、以上分组函数都忽略null值
3、可以和distinct搭配实现去重的运算
4、count函数的单独介绍
一般使用count(*)用作统计行数
5、和分组函数一同查询的字段要求是group by后的字段

简单的使用方式

根据薪资查，总和或平均值、最大、最小等等

SELECT SUM(salary) FROM t_mysql_employees;
SELECT AVG(salary) FROM t_mysql_employees;
SELECT MIN(salary) FROM t_mysql_employees;
SELECT MAX(salary) FROM t_mysql_employees;
SELECT COUNT(salary) FROM t_mysql_employees;

参数支持哪些类型

SELECT SUM(last_name) ,AVG(last_name) FROM t_mysql_employees;
SELECT SUM(hiredate) ,AVG(hiredate) FROM t_mysql_employees;

SELECT MAX(last_name),MIN(last_name) FROM t_mysql_employees;

SELECT MAX(hiredate),MIN(hiredate) FROM t_mysql_employees;

SELECT COUNT(commission_pct) FROM t_mysql_employees;
SELECT COUNT(last_name) FROM t_mysql_employees;

是否忽略null

SELECT SUM(commission_pct) ,AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107 FROM t_mysql_employees;

SELECT MAX(commission_pct) ,MIN(commission_pct) FROM t_mysql_employees;

SELECT COUNT(commission_pct) FROM t_mysql_employees;
SELECT commission_pct FROM t_mysql_employees;

和distinct搭配

SELECT SUM(DISTINCT salary),SUM(salary) FROM t_mysql_employees;

SELECT COUNT(DISTINCT salary),COUNT(salary) FROM t_mysql_employees;

效率：
MYISAM存储引擎下 ，COUNT()的效率高
INNODB存储引擎下，COUNT()和COUNT(1)的效率差不多，比COUNT(字段)要高一些

count函数的详细介绍

SELECT COUNT(salary) FROM t_mysql_employees;

SELECT COUNT(*) FROM t_mysql_employees;

SELECT COUNT(1) FROM t_mysql_employees;
分组函数练习

语法：

select 查询列表
from 表
【where 筛选条件】
group by 分组的字段
【order by 排序的字段】;
特点：
1、和分组函数一同查询的字段必须是group by后出现的字段
2、筛选分为两类：分组前筛选和分组后筛选
针对的表 位置 连接的关键字
分组前筛选 原始表 group by前 where

分组后筛选 group by后的结果集 group by后 having

问题1：分组函数做筛选能不能放在where后面
答：不能

问题2：where——group by——having

一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率

3、分组可以按单个字段也可以按多个字段
4、可以搭配着排序使用

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

SELECT MAX(salary) 最大值,MIN(salary) 最小值,AVG(salary) 平均值,SUM(salary) 和
FROM t_mysql_employees;
#2.查询员工表中的最大入职时间和最小入职时间的相差天数 （DIFFRENCE）

SELECT MAX(hiredate) 最大,MIN(hiredate) 最小,(MAX(hiredate)-MIN(hiredate))/1000/3600/24 DIFFRENCE
FROM t_mysql_employees;

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

SELECT DATEDIFF('1995-2-7','1995-2-6');


#3.查询部门编号为90的员工个数

SELECT COUNT(*) FROM t_mysql_employees WHERE department_id = 90;

#案例1：查询每个工种的员工平均工资
SELECT AVG(salary),job_id
FROM t_mysql_employees
GROUP BY job_id;

#案例2：查询每个位置的部门个数

SELECT COUNT(*),location_id
FROM t_mysql_departments
GROUP BY location_id;


#2、可以实现分组前的筛选

#案例1：查询邮箱中包含a字符的 每个部门的最高工资

SELECT MAX(salary),department_id
FROM t_mysql_employees
WHERE email LIKE '%a%'
GROUP BY department_id;


#案例2：查询有奖金的每个领导手下员工的平均工资

SELECT AVG(salary),manager_id
FROM t_mysql_employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;



#3、分组后筛选

#案例：查询哪个部门的员工个数>5

#①查询每个部门的员工个数
SELECT COUNT(*),department_id
FROM t_mysql_employees
GROUP BY department_id;

#② 筛选刚才①结果

SELECT COUNT(*),department_id
FROM t_mysql_employees

GROUP BY department_id

HAVING COUNT(*)>5;


#案例2：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

SELECT job_id,MAX(salary)
FROM t_mysql_employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;


#案例3：领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资

manager_id>102

SELECT manager_id,MIN(salary)
FROM t_mysql_employees
GROUP BY manager_id
HAVING MIN(salary)>5000;


#4.添加排序

#案例：每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序

SELECT job_id,MAX(salary) m
FROM t_mysql_employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING m>6000
ORDER BY m ;


#5.按多个字段分组

#案例：查询每个工种每个部门的最低工资,并按最低工资降序

SELECT MIN(salary),job_id,department_id
FROM t_mysql_employees
GROUP BY department_id,job_id
ORDER BY MIN(salary) DESC;


#1.查询各job_id的员工工资的最大值，最小值，平均值，总和，并按job_id升序

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


#2.查询员工最高工资和最低工资的差距（DIFFERENCE）
SELECT MAX(salary)-MIN(salary) DIFFRENCE
FROM t_mysql_employees;
#3.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
SELECT MIN(salary),manager_id
FROM t_mysql_employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary)>=6000;



#4.查询所有部门的编号，员工数量和工资平均值,并按平均工资降序
SELECT department_id,COUNT(*),AVG(salary) a
FROM t_mysql_employees
GROUP BY department_id
ORDER BY a DESC;
#5.选择具有各个job_id的员工人数
SELECT COUNT(*) 个数,job_id
FROM t_mysql_employees
GROUP BY job_id;

多表查询

称为笛卡尔集
含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行

发生原因：没有有效的连接条件
如何避免：添加有效的连接条件
分类：

按年代分类：
sql92标准:仅仅支持内连接
sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接
按功能分类：
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接
右外连接
全外连接
交叉连接

笛卡尔集会在下面条件下产生
– 省略连接条件
– 连接条件无效
– 所有表中的所有行互相连接
• 为了避免笛卡尔集， 可以在 WHERE 加入有 效的连接条件
在这里插入图片描述

等值/连接连接

使用连接在多个表中查询数据
• 在 WHERE 子句中写入连接条件。
• 在表中有相同列时，在列名之前加上表名前缀

区分重复的列名
• 在不同表中具有相同列名的列可以用表的别名
加以区分。
• 如果使用了表别名，则在select语句中需要使
用表别名代替表名
• 表别名最多支持32个字符长度，但建议越少越
好
表的别名
• 使用别名可以简化查询。
• 使用表名前缀可以提高执行效率。
连接多个表
• 连接 n个表,至少需要 n-1个连接条件。 例如：连接
三个表，至少需要两个连接条件。

连接查询案例
#案例1：查询女神名和对应的男神名
SELECT NAME,boyName 
FROM boys,beauty
WHERE beauty.boyfriend_id= boys.id;

#案例2：查询员工名和对应的部门名

SELECT last_name,department_name
FROM t_mysql_employees,departments
WHERE t_mysql_employees.`department_id`=t_mysql_departments.`department_id`;


#2、为表起别名
/*
①提高语句的简洁度
②区分多个重名的字段

注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

*/
#查询员工名、工种号、工种名

SELECT e.last_name,e.job_id,j.job_title
FROM t_mysql_employees  e,jobs j
WHERE e.`job_id`=j.`job_id`;


#3、两个表的顺序是否可以调换

#查询员工名、工种号、工种名

SELECT e.last_name,e.job_id,j.job_title
FROM t_mysql_jobs j,t_mysql_employees e
WHERE e.`job_id`=j.`job_id`;


#4、可以加筛选


#案例：查询有奖金的员工名、部门名

SELECT last_name,department_name,commission_pct

FROM t_mysql_employees e,t_mysql_departments d
WHERE e.`department_id`=d.`department_id`
AND e.`commission_pct` IS NOT NULL;

#案例2：查询城市名中第二个字符为o的部门名和城市名

SELECT department_name,city
FROM t_mysql_departments t_mysql_d,locations l
WHERE d.`location_id` = l.`location_id`
AND city LIKE '_o%';

#5、可以加分组


#案例1：查询每个城市的部门个数

SELECT COUNT(*) 个数,city
FROM t_mysql_departments d,t_mysql_locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY city;


#案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT department_name,d.`manager_id`,MIN(salary)
FROM t_mysql_departments d,t_mysql_employees e
WHERE d.`department_id`=e.`department_id`
AND commission_pct IS NOT NULL
GROUP BY department_name,d.`manager_id`;
#6、可以加排序


#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序

SELECT job_title,COUNT(*)
FROM t_mysql_employees e,t_mysql_jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;

#7、可以实现三表连接？

#案例：查询员工名、部门名和所在的城市

SELECT last_name,department_name,city
FROM t_mysql_employees e,t_mysql_departments d,t_mysql_locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`
AND city LIKE 's%'

ORDER BY department_name DESC;



#2、非等值连接
#案例1：查询员工的工资和工资级别

SELECT salary,grade_level
FROM t_mysql_employees e,t_mysql_job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
AND g.`grade_level`='A';

/*
select salary,employee_id from employees;
select * from job_grades;
CREATE TABLE job_grades
(grade_level VARCHAR(3),
 lowest_sal  int,
 highest_sal int);

INSERT INTO job_grades
VALUES ('A', 1000, 2999);

INSERT INTO job_grades
VALUES ('B', 3000, 5999);

INSERT INTO job_grades
VALUES('C', 6000, 9999);

INSERT INTO job_grades
VALUES('D', 10000, 14999);

INSERT INTO job_grades
VALUES('E', 15000, 24999);

INSERT INTO job_grades
VALUES('F', 25000, 40000);

*/

#3、自连接
#案例：查询 员工名和上级的名称

SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM t_mysql_employees e,t_mysql_employees m
WHERE e.`manager_id`=m.`employee_id`;

join链接
语法：
select 查询列表
from 表1 别名 【连接类型】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】

– 内连接 [inner] join on
– 外连接
• 左外连接 left [outer] join on
• 右外连接 right [outer] join on
使用ON 子句创建连接
• 自然连接中是以具有相同名字的列为连接条件的。
• 可以使用 ON 子句指定额外的连接条件。
• 这个连接条件是与其它条件分开的。
• ON 子句使语句具有更高的易读性。
内连接
语法：

select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件;

分类：
等值
非等值
自连接

特点：
①添加排序、分组、筛选
②inner可以省略
③ 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集
join案例

#案例1.查询员工名、部门名

SELECT last_name,department_name
FROM t_mysql_departments d
 JOIN  t_mysql_employees e
ON e.`department_id` = d.`department_id`;



#案例2.查询名字中包含e的员工名和工种名（添加筛选）
SELECT last_name,job_title
FROM t_mysql_employees e
INNER JOIN t_mysql_jobs j
ON e.`job_id`=  j.`job_id`
WHERE e.`last_name` LIKE '%e%';



#3. 查询部门个数>3的城市名和部门个数，（添加分组+筛选）

#①查询每个城市的部门个数
#②在①结果上筛选满足条件的
SELECT city,COUNT(*) 部门个数
FROM t_mysql_departments d
INNER JOIN t_mysql_locations l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*)>3;




#案例4.查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）

#①查询每个部门的员工个数
SELECT COUNT(*),department_name
FROM t_mysql_employees e
INNER JOIN t_mysql_departments d
ON e.`department_id`=d.`department_id`
GROUP BY department_name

#② 在①结果上筛选员工个数>3的记录，并排序

SELECT COUNT(*) 个数,department_name
FROM t_mysql_employees e
INNER JOIN t_mysql_departments d
ON e.`department_id`=d.`department_id`
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;

#5.查询员工名、部门名、工种名，并按部门名降序（添加三表连接）

SELECT last_name,department_name,job_title
FROM t_mysql_employees e
INNER JOIN t_mysql_departments d ON e.`department_id`=d.`department_id`
INNER JOIN t_mysql_jobs j ON e.`job_id` = j.`job_id`

ORDER BY department_name DESC;

#二）非等值连接

#查询员工的工资级别

SELECT salary,grade_level
FROM t_mysql_employees e
 JOIN t_mysql_job_grades g
 ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
 
 
 #查询工资级别的个数>20的个数，并且按工资级别降序
 SELECT COUNT(*),grade_level
FROM t_mysql_employees e
 JOIN t_mysql_job_grades g
 ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
 GROUP BY grade_level
 HAVING COUNT(*)>20
 ORDER BY grade_level DESC;
 
 
 #三）自连接
 
 #查询员工的名字、上级的名字
 SELECT e.last_name,m.last_name
 FROM t_mysql_employees e
 JOIN t_mysql_employees m
 ON e.`manager_id`= m.`employee_id`;
 
  #查询姓名中包含字符k的员工的名字、上级的名字
 SELECT e.last_name,m.last_name
 FROM t_mysql_employees e
 JOIN t_mysql_employees m
 ON e.`manager_id`= m.`employee_id`
 WHERE e.`last_name` LIKE '%k%';

#二、外连接
应用场景：用于查询一个表中有，另一个表没有的记录

特点：
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接，left join左边的是主表
右外连接，right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的

#案例1：查询哪个部门没有员工
#左外
SELECT d.*,e.employee_id
FROM t_mysql_departments d
LEFT OUTER JOIN t_mysql_employees e
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;


#右外

 SELECT d.*,e.employee_id
FROM t_mysql_employees e
RIGHT OUTER JOIN t_mysql_departments d
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;


#全外


USE girls;
SELECT b.*,bo.*
FROM t_mysql_beauty b
FULL OUTER JOIN t_mysql_boys bo
ON b.`boyfriend_id` = bo.id;


#交叉连接

SELECT b.*,bo.*
FROM t_mysql_beauty b
CROSS JOIN boys bo;


#1.显示所有员工的姓名，部门号和部门名称。
#USE myemployees;

SELECT last_name,d.department_id,department_name
FROM t_mysql_employees e,t_mysql_departments d
WHERE e.`department_id` = d.`department_id`;


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

SELECT job_id,location_id
FROM t_mysql_employees e,t_mysql_departments d
WHERE e.`department_id`=d.`department_id`
AND e.`department_id`=90;



#3.	选择所有有奖金的员工的
last_name , department_name , location_id , city


SELECT last_name , department_name , l.location_id , city
FROM t_mysql_employees e,t_mysql_departments d,t_mysql_locations l
WHERE e.department_id = d.department_id
AND d.location_id=l.location_id
AND e.commission_pct IS NOT NULL;


#4.选择city在Toronto工作的员工的
last_name , job_id , department_id , department_name 

SELECT last_name , job_id , d.department_id , department_name 
FROM t_mysql_employees e,t_mysql_departments d ,t_mysql_locations l
WHERE e.department_id = d.department_id
AND d.location_id=l.location_id
AND city = 'Toronto';



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


SELECT department_name,job_title,MIN(salary) 最低工资
FROM t_mysql_employees e,t_mysql_departments d,t_mysql_jobs j
WHERE e.`department_id`=d.`department_id`
AND e.`job_id`=j.`job_id`
GROUP BY department_name,job_title;



#6.查询每个国家下的部门个数大于2的国家编号

SELECT country_id,COUNT(*) 部门个数
FROM t_mysql_departments d,t_mysql_locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY country_id
HAVING 部门个数>2;





#7、选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
employees	Emp#	manager	Mgr#
kochhar		101	king	100


SELECT e.last_name employees,e.employee_id "Emp#",m.last_name manager,m.employee_id "Mgr#"
FROM t_mysql_employees e,t_mysql_employees m
WHERE e.manager_id = m.employee_id
AND e.last_name='kochhar';

常见函数

概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1、隐藏了实现细节 2、提高代码的重用性
调用：select 函数名(实参列表) 【from 表】;
特点：
①叫什么（函数名）
②干什么（函数功能）

字符函数
作用函数结果
转小写LOWER(‘SQL Course’)sql course
转大写UPPER(‘SQL Course’)SQL COURSE
拼接CONCAT(‘Hello’, ‘World’)HelloWorld
截取SUBSTR(‘HelloWorld’,1,5)Hello
长度LENGTH(‘HelloWorld’)10
字符出现索引值INSTR(‘HelloWorld’, ‘W’)6
字符截取后半段TRIM(‘H’ FROM ‘HelloWorld’)elloWorld
字符替换REPLACE(‘abcd’,‘b’,‘m’)amcd
#一、字符函数

#1.length 获取参数值的字节个数
SELECT LENGTH('john');
SELECT LENGTH('张三丰hahaha');

SHOW VARIABLES LIKE '%char%'

#2.concat 拼接字符串

SELECT CONCAT(last_name,'_',first_name) 姓名 t_mysql_FROM employees;

#3.upper、lower
SELECT UPPER('john');
SELECT LOWER('joHn');
#示例：将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name))  姓名 FROM t_mysql_employees;

#4.substr、substring
注意：索引从1开始
#截取从指定索引处后面所有字符
SELECT SUBSTR('李莫愁爱上了陆展元',7)  out_put;

#截取从指定索引处指定字符长度的字符
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put;


#案例：姓名中首字符大写，其他字符小写然后用_拼接，显示出来

SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2)))  out_put
FROM t_mysql_employees;

#5.instr 返回子串第一次出现的索引，如果找不到返回0

SELECT INSTR('杨不殷六侠悔爱上了殷六侠','殷八侠') AS out_put;

#6.trim

SELECT LENGTH(TRIM('    张翠山    ')) AS out_put;

SELECT TRIM('aa' FROM 'aaaaaaaaa张aaaaaaaaaaaa翠山aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')  AS out_put;

#7.lpad 用指定的字符实现左填充指定长度

SELECT LPAD('殷素素',2,'*') AS out_put;

#8.rpad 用指定的字符实现右填充指定长度

SELECT RPAD('殷素素',12,'ab') AS out_put;


#9.replace 替换

SELECT REPLACE('周芷若周芷若周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put;
数学函数
作用函数结果
四舍五入ROUND(45.926, 2)45.93
截断TRUNC(45.926, 2)45.92
求余MOD(1600, 300)100
#二、数学函数

#round 四舍五入
SELECT ROUND(-1.55);
SELECT ROUND(1.567,2);


#ceil 向上取整,返回>=该参数的最小整数

SELECT CEIL(-1.02);

#floor 向下取整，返回<=该参数的最大整数
SELECT FLOOR(-9.99);

#truncate 截断

SELECT TRUNCATE(1.69999,1);

#mod取余
/*
mod(a,b) ：  a-a/b*b

mod(-10,-3):-10- (-10)/(-3)*（-3）=-1
*/
SELECT MOD(10,-3);
SELECT 10%3;
日期函数
作用函数结果
获取当前日期now()
将日期格式的字符转换成指定格式的日期STR_TO_DATE(‘9-13-1999’,’%m-%d-%Y’)1999-09-13
将日期转换成字符DATE_FORMAT(‘2018/6/6’,‘%Y年%m月%d日’)2018年06月06日
#now 返回当前系统日期+时间
SELECT NOW();

#curdate 返回当前系统日期，不包含时间
SELECT CURDATE();

#curtime 返回当前时间，不包含日期
SELECT CURTIME();


#可以获取指定的部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年;
SELECT YEAR('1998-1-1') 年;

SELECT  YEAR(hiredate) 年 FROM t_mysql_employees;

SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;


#str_to_date 将字符通过指定的格式转换成日期

SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;

#查询入职日期为1992--4-3的员工信息
SELECT * FROM employees WHERE hiredate = '1992-4-3';

SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');


#date_format 将日期转换成字符

SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;

#查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期
FROM t_mysql_employees
WHERE commission_pct IS NOT NULL;


#四、其他函数

SELECT VERSION();
SELECT DATABASE();
SELECT USER();


#五、流程控制函数
#1.if函数： if else 的效果

SELECT IF(10<5,'大','小');

SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，呵呵','有奖金，嘻嘻') 备注
FROM t_mysql_employees;

练习

#1.	显示系统时间(注：日期+时间)
SELECT NOW();

#2.	查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）

SELECT employee_id,last_name,salary,salary*1.2 "new salary"
FROM t_mysql_employees;
#3.	将员工的姓名按首字母排序，并写出姓名的长度（length）

SELECT LENGTH(last_name) 长度,SUBSTR(last_name,1,1) 首字符,last_name
FROM t_mysql_employees
ORDER BY 首字符;


#4.	做一个查询，产生下面的结果
<last_name> earns <salary> monthly but wants <salary*3>
Dream Salary
King earns 24000 monthly but wants 72000


SELECT CONCAT(last_name,' earns ',salary,' monthly but wants ',salary*3) AS "Dream Salary"
FROM t_mysql_employees
WHERE salary=24000;


#5.	使用case-when，按照下面的条件：
job                  grade
AD_PRES            A
ST_MAN             B
IT_PROG             C
SA_REP              D
ST_CLERK           E
产生下面的结果
Last_name	Job_id	Grade
king	AD_PRES	A


SELECT last_name,job_id AS  job,
CASE job_id
WHEN 'AD_PRES' THEN 'A' 
WHEN 'ST_MAN' THEN 'B' 
WHEN 'IT_PROG' THEN 'C' 
WHEN 'SA_PRE' THEN 'D'
WHEN 'ST_CLERK' THEN 'E'
END AS Grade
FROM t_mysql_employees
WHERE job_id = 'AD_PRES';

子查询

含义：
出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询

按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）
特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用
> < >= <= = <>
子查询练习

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

#①查询Zlotkey的部门
SELECT department_id
FROM t_mysql_employees
WHERE last_name = 'Zlotkey'

#②查询部门号=①的姓名和工资
SELECT last_name,salary
FROM t_mysql_employees
WHERE department_id = (
	SELECT department_id
	FROM t_mysql_employees
	WHERE last_name = 'Zlotkey'

)

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

#①查询平均工资
SELECT AVG(salary)
FROM t_mysql_employees

#②查询工资>①的员工号，姓名和工资。

SELECT last_name,employee_id,salary
FROM t_mysql_employees
WHERE salary>(

	SELECT AVG(salary)
	FROM t_mysql_employees
);



#3.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
#①查询各部门的平均工资
SELECT AVG(salary),department_id
FROM t_mysql_employees
GROUP BY department_id

#②连接①结果集和employees表，进行筛选
SELECT employee_id,last_name,salary,e.department_id
FROM t_mysql_employees e
INNER JOIN (
	SELECT AVG(salary) ag,department_id
	FROM t_mysql_employees
	GROUP BY department_id


) ag_dep
ON e.department_id = ag_dep.department_id
WHERE salary>ag_dep.ag ;



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

SELECT  DISTINCT department_id
FROM t_mysql_employees
WHERE last_name LIKE '%u%'

#②查询部门号=①中的任意一个的员工号和姓名
SELECT last_name,employee_id
FROM t_mysql_employees
WHERE department_id IN(
	SELECT  DISTINCT department_id
	FROM t_mysql_employees
	WHERE last_name LIKE '%u%'
);


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

#①查询location_id为1700的部门

SELECT DISTINCT department_id
FROM t_mysql_departments 
WHERE location_id  = 1700


#②查询部门号=①中的任意一个的员工号
SELECT employee_id
FROM t_mysql_employees
WHERE department_id =ANY(
	SELECT DISTINCT department_id
	FROM t_mysql_departments 
	WHERE location_id  = 1700

);
#6.查询管理者是King的员工姓名和工资

#①查询姓名为king的员工编号
SELECT employee_id
FROM t_mysql_employees
WHERE last_name  = 'K_ing'

#②查询哪个员工的manager_id = ①
SELECT last_name,salary
FROM t_mysql_employees
WHERE manager_id IN(
	SELECT employee_id
	FROM t_mysql_employees
	WHERE last_name  = 'K_ing'

);

#7.查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为 姓.名


#①查询最高工资
SELECT MAX(salary)
FROM t_mysql_employees

#②查询工资=①的姓.名

SELECT CONCAT(first_name,last_name) "姓.名"
FROM t_mysql_employees
WHERE salary=(
	SELECT MAX(salary)
	FROM t_mysql_employees

);

分页查询

应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
语法：
select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段】
limit 【offset,】size;
offset要显示条目的起始索引（起始索引从0开始）
size 要显示的条目个数
特点：
①limit语句放在查询语句的最后
②公式
要显示的页数 page，每页的条目数size

#案例1：查询前五条员工信息
SELECT * FROM  t_mysql_employees LIMIT 0,5;
SELECT * FROM  t_mysql_employees LIMIT 5;


#案例2：查询第11条——第25条
SELECT * FROM  t_mysql_employees LIMIT 10,15;


#案例3：有奖金的员工信息，并且工资较高的前10名显示出来
SELECT 
    * 
FROM
    t_mysql_employees 
WHERE commission_pct IS NOT NULL 
ORDER BY salary DESC 
LIMIT 10 ;

总结

今天就到这里了如果有什么不对的地方欢迎大家在评论区留言交流改进！！

licmi
关注
专栏目录
2024最新数据库MySQL-DQL-HM
03-20
### 数据库操作-DQL #### 1.1 DQL简介 DQL，即Data Query Language...以上就是关于MySQL数据库中DQL语句的基础介绍与使用示例。掌握了这些知识点后，你将能够在实际项目中灵活运用DQL来进行高效的数据检索与处理。
MySQL查询练习
Miziguo的博客
01-05 846
MySQL查询练习题
mysq查询员工姓名，入职时间，入职天数，原始工资，现工资cur_salary（如果入职天数大于2000的工资*1.5,入职天数大于1600的工资*1.4，入职天数大于1200的工资*1.3，入职
m0_73886490的博客
09-28 486
入职天数大于1600的工资*1.4，入职天数大于1200的工资*1.3，入职天数大于1000的工资*1.2，其他的显示原工资）查询员工姓名，入职时间，入职天数，原始工资，现工资cur_salary（如果入职天数大于2000的工资*1.5,查询员工工资的最大值，最小值，平均值，总和，四舍五入保留两位小数。查询部门编号为3的员工数，即部门3有多少名员工。查询入职时间最早和最晚的员工相差天数。
【MySQL】—— 学习日期函数计算员工入职时间并进行倒排
最新发布
“路漫漫其修远兮，吾将上下求索。”
02-11 2666
学习日期函数计算员工入职时间并进行倒排
【mysql入门笔记02 2022.10.03】
weixin_43360094的博客
10-03 896
mysql入门笔记02 2022.10.03
三、MySQL——函数类型
weixin_53330986的博客
03-29 993
目录 1、字符串函数 2、数值函数 ​​​​​​​3、日期函数 ​​​​​​​4、流程函数 窗口函数 是指一段可以直接被另一段程序调用的程序或代码。 也就意味着，这一段程序或代码在MySQL中已经给我们提供了，我们要做的就是在合适的业务场景调用对应的函数完成对应的业务需求即可。 那么，函数到底在哪儿使用呢？ 我们先来看两个场景： 在企业的OA或其他的人力系统中，经常...
MySQL数据库PDF文档
10-20
本文档主要介绍了MySQL数据库的基本概念、数据类型、SQL语言、约束条件、查询语言、聚合函数、子查询和多表查询等知识点。 数据库基础概念 在介绍MySQL数据库之前，首先需要了解数据库的基本概念。数据库是一个...
MySQL数据库相关课件PPT
02-22
MySQL数据库是全球最受欢迎的开源关系型数据库管理系统之一，它以其高效、可靠和易于维护的特点，在Web开发、企业级应用和数据分析等领域广泛应用。本课件PPT涵盖了MySQL的基础知识到高级特性的深入理解，旨在帮助...
jdbc操作mysql数据库实例
10-23
对于DDL（数据定义语言）或DML（数据操纵语言）语句，使用executeUpdate方法，而对于DQL（数据查询语言）语句，使用executeQuery方法。 实例中的insert()方法演示了如何通过循环向数据库中插入数据。对于插入语句，...
Mysql数据库基础知识
01-30
MySQL数据库是目前广泛应用的关系型数据库管理系统，其基础知识涵盖了多个方面，包括数据库的基本概念、MySQL的安装与使用、SQL语言的各个部分以及高级特性。对于数据库管理员、开发人员、数据分析师和学习者来说，...
Mysql(四)
weixin_63816398的博客
05-01 2015
Mysql（四）
MySQL基础篇
星航夜空的帆舟
09-20 733
1. SQL 语句分类 1、DDL 数据定义语言,    如：建库，建表; 2、DML 数据操纵语言，如：对表中的记录操作增删改; 3、DQL 数据查询语言，如：对表中的查询操作; 4、DCL 数据控制语言，如：对用户权限的设置。 2. MySQL 的语法 1、每条语句以分号结尾，如果在 SQLyog 中不是必须加的。 2、SQL 中不区分大小写，关键字中认为大写和小写是一样的。 3、3 种注释： 注释的语法 说明 –空格 单行注释 /* */ 多行注.
一些mysql自学笔记4(分组函数)
qq_36494882的博客
03-01 933
哔哩哔哩自学视频做的笔记
【MYSQL】MYSQL 主要函数
weixin_42694422的博客
03-25 1146
MYSQL【基础篇】MYSQL 主要函数。MySQL是 MySQL 数据库提供的内部函数，这些内部函数可以帮助用户更加方便地处理表中的数据。函数就像预定的公式一样存放在数据库里，每个用户都可以调用已经存在的函数来完成某些功能。 同时MySQL数据库还支持用户自己建立函数，以适应实际的业务操作。正确使用函数会让读者在编写 SQL 语句时起到事半功倍的效果
查询员工表中的最大入职时间和最小入职时间的天数差，查询部门编号为90的员工个数
x²的博客
03-09 4379
1.查询员工表中的最大入职时间和最小入职时间的天数差 SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) AS DIFFERENCE FROM employees; DATEDIFF函数: DATEDIFF(日期1，日期2)，返回的是日期1减去日期2得到的天数 2.查询部门编号为90的员工个数 SELECT COUNT(*) 个数 FROM e...
2020-10-17 mysql练习（1表的创建）
weixin_46029176的博客
10-17 250
myemployees数据库创建(mysql可直接复制粘贴) /* SQLyog Ultimate v10.00 Beta1 MySQL - 5.5.15 : Database - myemployees ********************************************************************* */ /*!40101 SET NAMES utf8 */; /*!40101 SET SQL_MODE=''*/; /*!40014 SET @OLD_UN
SQL---基本常用函数
qq_49810363的博客
09-26 1089
SQL—基本常用函数 提示：本人新手，仅供参考 案例成绩表结构 1、AVG 函数 AVG 函数返回数值列的平均值，NULL 值不包括在计算中 -- 查询整个年级的语文平均分 SELECT AVG(score) AS "语文平均分" FROM grade WHERE `subject` = '语文' -- 查询语文成绩高于平均分的同学及分数 SELECT st_name AS '姓名',score AS '分数' FROM grade WHERE `subject` = '语文' AND score &g
MySQL 时间函数（答学生问:关于员工考勤查询）
老焦沉淀淀
07-17 4643
今天有学生问个关于MySQL时间操作的问题，直接看图：  查询某日人员的出勤时间问题。 我们都知道，企业员工考勤使用打卡方式，每天会记录每个员工的多次打卡记录，最后取每个员工的每天： 上班时间：当日最早时间； 下班时间：当日最晚时间。 所以分析查询每个员工每日考勤记录，需要根据员工分组，当天时间、时间最小和最大，时间格式化等操作，下面举例模拟该问题： 学生表student  ...
MySQL数据库操作详解：DDL、DML与DQL
"MySQL数据库的学习笔记，涵盖了基础语法和数据库、表的操作。" 在MySQL数据库管理中，了解并熟练掌握各种SQL语句是至关重要的。本笔记主要涉及四大类SQL语句：DDL（数据定义语言）、DML（数据操作语言）、DQL...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值