day02(排序函数，常见函数，分组查询，连接查询)

最新推荐文章于 2021-10-10 19:25:41 发布

yan_xiao_liu

最新推荐文章于 2021-10-10 19:25:41 发布

阅读量152

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/qq_44132400/article/details/105074335

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

排序函数

语法：

select 查询列表 from 表名 where 筛选条件 order by 排序列表 ;

特点：

①排序列表可以是单个字段、多个字段、函数、表达式、别名、列的索引，以及以上的组合

②升序，通过asc

降序，通过desc

#1.查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序
select last_name,department_id,salary from employees order by salary desc,last_name asc ;
#2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序
select last_name,salary
from employees where salary not between 8000 and 17000 order by salary desc ;
#3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序
select * from employees where email like "%e%" order by department_id asc ;

常见函数

数学函数

ABS(X)	返回x的绝对值
CEIL(X)	返回大于x的最小整数值
FLOOR(X)	返回大于x的最大整数值
MOD(X,Y)	返回x/y的模
RAND(X)	返回0~1的随机值
ROUND(X,Y)	返回参数x四舍五入的有y位的小数的值
TRUNCATE(X,Y)	返回数字x截断位y位小数的结果
SQRT(X)	返回x的平方根
POW(X,Y)	返回x的y次方

-- 返回绝对值
select abs(-33),abs(-22);#33   22
-- 返回大于x的最小整数
select ceil(1.2),ceil(1.0);#2  1
-- 返回小于x的最大整数
select floor(1.0),floor(1.2);#1    1
-- 取模
select mod(98,10),mod(98,-10),mod(-98,10),mod(-98,-10);#8  8  -8 -8  结果正负跟随被除数
-- 返回0-1之间的随机数
select rand();
-- 四舍五入
select round(2.4),round(2.5);#2    3
-- 截断
select truncate(1.222,2),truncate(1.666,2);#1.22   1.66
-- 开平方
select sqrt(9),sqrt(8);##3 2.8284271247461903
-- x的y次方 pow(x,y)
select pow(3,2);#9

字符串函数

CONCAT(S1,S2,…,Sn)	连接S1,S2,…,Sn为一个字符串

CHAR_LENGTH(s)	返回字符串s的字符数
LENGTH(s)	返回字符串s的字节数，和字符集有关
INSERT(str, index , len, instr)	将字符串str从第index位置开始，len个字符长的子串替换为字符串instr
UPPER(s) 或 UCASE(s)	将字符串s的所有字母转成大写字母
LOWER(s) 或LCASE(s)	将字符串s的所有字母转成小写字母
LEFT(s,n)	返回字符串s最左边的n个字符
RIGHT(s,n)	返回字符串s最右边的n个字符
LPAD(str, len, pad)	用字符串pad对str最左边进行填充，直到str的长度为len个字符
RPAD(str ,len, pad)	用字符串pad对str最右边进行填充，直到str的长度为len个字符
LTRIM(s)	去掉字符串s左侧的空格
RTRIM(s)	去掉字符串s右侧的空格
TRIM(s)	去掉字符串s开始与结尾的空格
TRIM(【BOTH 】s1 FROM s)	去掉字符串s开始与结尾的s1
TRIM(【LEADING】s1 FROM s)	去掉字符串s开始处的s1
TRIM(【TRAILING】s1 FROM s)	去掉字符串s结尾处的s1
REPEAT(str, n)	返回str重复n次的结果
REPLACE（str, a, b）	用字符串b替换字符串str中所有出现的字符串a
STRCMP(s1,s2)	比较字符串s1,s2
SUBSTRING(s,index,len)	返回从字符串s的index位置其len个字符

日期和时间函数

CURDATE() 或 CURRENT_DATE()	返回当前日期
CURTIME() 或 CURRENT_TIME()	返回当前时间
NOW() SYSDATE() CURRENT_TIMESTAMP() LOCALTIME() LOCALTIMESTAMP()	返回当前系统日期时间
YEAR(date)MONTH(date)DAY(date)HOUR(time)MINUTE(time)SECOND(time)	返回具体的时间值
WEEK(date) WEEKOFYEAR(date)	返回一年中的第几周
DAYOFWEEK()	返回周几，注意：周日是1，周一是2，周六是7
WEEKDAY(date)	返回周几，注意，周1是0，周2是1，周日是6
DAYNAME(date)	返回星期：MONDAY,TUESDAY…SUNDAY
MONTHNAME(date)	返回月份：January，。。。。。
DATEDIFF(date1,date2)TIMEDIFF(time1, time2)	返回date1 - date2的日期间隔返回time1 - time2的时间间隔
DATE_ADD(datetime, INTERVALE expr type)	返回与给定日期时间相差INTERVAL时间段的日期时间
DATE_FORMAT(datetime ,fmt)	按照字符串fmt格式化日期datetime值
STR_TO_DATE(str, fmt)	按照字符串fmt对str进行解析，解析为一个日期

-- 返回当前日期
select curdate(),current_date();#2020-03-24    2020-03-24
-- 返回当前时间
select current_time,curtime();#13:39:24    13:39:24
-- 返回当前系统日期时间
select now(),sysdate(),current_timestamp(),localtime(),localtimestamp();#2020-03-24 13:40:51   2020-03-24 13:40:51    2020-03-24 13:40:51    2020-03-24 13:40:51    2020-03-24 13:40:51
-- 返回具体的时间值
select YEAR(now()),month(now()),day(now()),hour(now()),minute(now()),second(now());#2020   3  24 13 42 40
-- 返回一年中的第几周
select week(now()),weekofyear(now());#12   13
-- 返回周几
select dayofweek(now());#3
select weekday(now());#1
select dayname(now());#Tuesday
-- 返回月份
select monthname(now());#March
-- 返回date1 - date2的日期间隔返回time1 - time2的时间间隔
select datediff(now(),"1999-02-09");#7714
-- 返回与给定日期时间相差INTERVAL时间段的日期时间
select date_add(now(),interval 2 day);#2020-03-26 13:59:00

DATE_ADD(datetime,INTERVAL expr type)

-- 返回与给定日期时间相差INTERVAL时间段的日期时间
select date_add(now(),interval 2 day);#2020-03-26 13:59:00

SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);

SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR); #可以是负数

SELECT DATE_ADD(NOW(), INTERVAL ‘1_1’ YEAR_MONTH); #需要单引号

表达式类型	YEAR_MONTH
YEAR	DAY_HOUR
MONTH	DAY_MINUTE
DAY	DAY_SECOND
HOUR	HOUR_MINUTE
MINUTE	HOUR_SECOND
SECOND	MINUTE_SECOND

DATE_FORMAT(datetime ,fmt)和STR_TO_DATE(str, fmt)

-- 按照字符串fmt格式化日期datetime值
select date_format("1999-02-09","%Y年%M月%d日 %H小时%i分钟%s秒");#1999年February月09日 00小时00分钟00秒

格式符	说明	格式符	说明
%Y	4位数字表示年份	%y	表示两位数字表示年份
%M	月名表示月份（January,…）	%m	两位数字表示月份（01,02,03。。。）
%b	缩写的月名（Jan.，Feb.，…）	%c	数字表示月份（1,2,3,…）
%D	英文后缀表示月中的天数（1st,2nd,3rd,…）	%d	两位数字表示月中的天数(01,02…)
%e	数字形式表示月中的天数（1,2,3,4,5…）
%H	两位数字表示小数，24小时制（01,02…）	%h和%I	两位数字表示小时，12小时制（01,02…）
%k	数字形式的小时，24小时制(1,2,3)	%l	数字形式表示小时，12小时制（1,2,3,4…）
%i	两位数字表示分钟（00,01,02）	%S和%s	两位数字表示秒(00,01,02…)
%W	一周中的星期名称（Sunday…）	%a	一周中的星期缩写（Sun.，Mon.,Tues.，…）
%w	以数字表示周中的天数(0=Sunday,1=Monday…)
%j	以3位数字表示年中的天数(001,002…)	%U	以数字表示年中的第几周，（1,2,3。。）其中Sunday为周中第一天
%u	以数字表示年中的第几周，（1,2,3。。）其中Monday为周中第一天
%T	24小时制	%r	12小时制
%p	AM或PM	%%	表示%

条件判断函数：流程控制函数

IF(value,t ,f)	如果value是真，返回t，否则返回f
IFNULL(value1, value2)	如果value1不为空，返回value1，否则返回value2
CASE WHEN 条件1 THEN result1WHEN 条件2 THEN result2…[ELSE resultn]END	相当于Java的if…else if…
CASE expr WHEN 常量值1 THEN 值1WHEN 常量值1 THEN 值1…[ELSE 值n]END	相当于Java的switch
	SELECT ename ,CASE WHEN salary>=15000 THEN '高薪’WHEN salary>=10000 THEN '潜力股’WHEN salary>=8000 THEN '屌丝’ELSE '草根’END FROM t_employee;
	SELECT oid,`status`, CASE `status`WHEN 1 THEN '未付款’WHEN 2 THEN '已付款’WHEN 3 THEN '已发货’WHEN 4 THEN '确认收货’ELSE '无效订单’END FROM t_order;

-- if函数
select if(100>9,'好','坏');
#需求：如果有奖金，则显示最终奖金，如果没有，则显示0
select if(commission_pct is null,0,salary * 12 * commission_pct) 奖金额
from employees;
-- 2、CASE函数
/*
案例：
部门编号是30，工资显示为2倍
部门编号是50，工资显示为3倍
部门编号是60，工资显示为4倍
否则不变
 */
select department_id,salary,
       case department_id
           when 30 then salary*2
           when 50 then salary*3
           when 60 then salary*4
           else salary
           end newsalary
from employees;
/*
 案例：如果工资>20000,显示级别A
      工资>15000,显示级别B
      工资>10000,显示级别C
      否则，显示D
 */
select salary,
       case salary
            when salary>20000 then 'A'
            when salary>15000 then 'B'
            when salary>10000 then 'C'
            else 'D'
end newsalay from employees;

分组函数

COUNT()：统计指定列不为NULL的记录行数；

MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；

AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

/*

说明：分组函数往往用于实现将一组数据进行统计计算，最终得到一个值，又称为聚合函数或统计函数

分组函数清单：

sum(字段名)：求和
avg(字段名)：求平均数
max(字段名)：求最大值
min(字段名)：求最小值
count(字段名)：计算非空字段值的个数

*/
#案例1 ：查询员工信息表中，所有员工的工资和、工资平均值、最低工资、最高工资、有工资的个数
select sum(salary),avg(salary),min(salary),max(salary),count(salary)
from employees;
#1、统计结果集的行数，推荐使用count(*)
select count(*) from employees;
#2、搭配distinct实现去重的统计
#需求：查询有员工的部门个数
select count(distinct department_id) from employees;
-- 每个部门的总工资、平均工资？
select sum(salary),avg(salary),department_id
from employees
group by department_id;

分组查询

/*
语法：
select 查询列表
from 表名
where 筛选条件
group by 分组列表
having 分组后筛选
order by 排序列表;
执行顺序：
①from子句
②where子句
③group by 子句
④having子句
⑤select子句
⑥order by子句
特点：
①查询列表往往是  分组函数和被分组的字段 ★
②分组查询中的筛选分为两类
         筛选的基表  使用的关键词    位置
分组前筛选     原始表       where        group by 的前面
分组后筛选     分组后的结果集  having          group by的后面
where——group by ——having
问题：分组函数做条件只可能放在having后面！！！
*/
#案例1：查询每个工种的员工平均工资
select avg(salary),job_id
from employees group by job_id;
#案例2：查询每个领导的手下人数
select count(*) manager_id
from employees where manager_id is not null
group by manager_id;
#案例1：查询邮箱中包含a字符的 每个部门的最高工资
select max(salary) 最高工资,department_id
from employees where email like '%a%'
group by department_id;
#查询每个领导手下有奖金的员工的平均工资
select avg(salary),manager_id from employees where commission_pct is not null group by manager_id;
#案例1：查询哪个部门的员工个数>5
select count(*),department_id
from employees
group by department_id having count(*) > 5;
#案例2：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
select max(salary),job_id
from employees where commission_pct is not null group by job_id
having max(salary) > 12000;
#案例3：领导编号>102的    每个领导手下的最低工资大于5000的最低工资
select min(salary),manager_id
from employees where manager_id > 102
group by manager_id having min(salary)>5000;
#案例：查询没有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序
select max(salary),job_id from employees where commission_pct is not  null group by job_id having max(salary)>6000 order by max(salary) asc;
#案例：查询每个工种每个部门的最低工资,并按最低工资降序
select min(salary),job_id,department_id from employees group by job_id, department_id order by min(salary) desc;

连接查询

sql92标准

#一、内连接
/*
语法:
select 查询列表
from 表1 别名,表2 别名
where 连接条件
and 筛选条件
group by 分组列表
having 分组后筛选
order by 排序列表

执行顺序：

1、from子句
2、where子句
3、and子句
4、group by子句
5、having子句
6、select子句
7、order by子句
*/
#一）等值连接
/*

① 多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③ 多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选
*/
#查询女神名和对应的男神名
select name,boyName from beauty,boys where boyfriend_id = boys.id;
#案例2：查询员工名和对应的部门名
use myemployees;
select last_name,department_name from employees,departments where employees.department_id = departments.department_id;
#2、为表起别名
/*
①提高语句的简洁度
②区分多个重名的字段
注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
*/
#查询员工名、工种号、工种名
select e.last_name,e.job_id,j.job_title
from employees e,jobs j where e.job_id = j.job_id;
#案例：查询有奖金的员工名、部门名
select last_name,department_name,commission_pct from employees,departments where employees.department_id = departments.department_id and commission_pct is not null;
#案例2：查询城市名中第二个字符为o的部门名和城市名a
select department_name,city from departments,locations where departments.location_id = locations.location_id and city like '_o%';
#案例1：查询每个城市的部门个数
select count(*),city from locations,departments where departments.location_id = locations.location_id group by city;
#案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
select min(salary),employees.department_id,departments.department_name,departments.manager_id
from departments,employees
where employees.department_id = departments.department_id and commission_pct is not null
group by employees.department_id;
#案例：查询员工名、部门名和所在的城市
select last_name,department_name,city
from employees,departments,locations
where employees.department_id = departments.department_id and departments.location_id = locations.location_id;
#二）非等值连接
#案例1：查询员工的工资和工资级别
select salary,grade_level
from job_grades ,employees where salary between lowest_sal and highest_sal;
#三）自连接
#案例：查询 员工名和上级的名称
select e.last_name,l.last_name
from employees e,employees l where e.manager_id = l.employee_id;

yan_xiao_liu

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
day02(排序函数，常见函数，分组查询，连接查询)

排序函数语法：select 查询列表 from 表名 where 筛选条件 order by 排序列表 ;特点：①排序列表可以是单个字段、多个字段、函数、表达式、别名、列的索引，以及以上的组合②升序，通过asc 降序，通过desc#1.查询员工的姓名和部门号和年薪，按年薪降序按姓名升序select last_name,department_id,salary from...
复制链接

扫一扫

专栏目录