select,排序,常见函数,分组函数

排序查询

select 查询列表
from 表名
【where 筛选条件】
order by 排序的字段或表达式;


select 字段 from 表 order by 字段 asc/desc; 默认asc升序,desc降序

  • order by 和desc 和asc 搭配进行降序和升序, desc是降序,默认asc升序

  • 特点

1、asc代表的是升序,可以省略
desc代表的是降序


2、order by子句可以支持 单个字段、别名、表达式、函数、多个字段


3: order 一般都是放在句子最后面,如果有limit就要放在limit前,也就是order by是放在除了limit之外的所有语句后面

order by 排序和其它条件一起使用的时候 不可以有and

– 查询部门编号>= 90的员工信息,按入职时间的先后顺序进行排序
select * from employees where department_id >= 90 order by hiredate desc;


– 按照年薪的高低显示员工的信息和年薪按照表达式排序
SELECT * from employees ORDER BY salary12(1+ifnull(commission_pct,0));

函数排序

– 按姓名的长度显示员工的姓名和工资[按函数排序]
select length(last_name) 姓名长度,last_name
   from employees
order by length(last_name);

多个字段排序

– 查询员工信息,先按照工资排序,再按照员工编号排序[按多个字段排序]
select * from employees order by salary,employee_id ;


– 查询员工信息,先按照工资升序,再按照员工编号降序【多个字段】
SELECT
    *
FROM
    employees
ORDER BY
    salary ASC,
    employee_id DESC;

order by 排序 Practices

– 1. 查询员工的姓名和部门号和年薪,按年薪降序 按姓名升序
select last_name 名字, department_id,salary12(1+ifnull(commission_pct,0)) from employees order by last_name;


– orde by 排序不可以和and一起使用多个筛选条件是不需要使用and的
– 2:选择工资不在 8000 到 17000 的员工的姓名和工资,按工资降序
select last_name as 名字, salary from employees where salary not in (8000-17000) order by salary desc ;


– 3: 查询邮箱中包含 e 的员工信息,并先按邮箱的字节数降序,再按部门号升序
select * from employees where email like “%e%” order by LENGTH(email) DESC,department_id ;


常见的函数
  • 概念 : 类似于java的方法,将一组逻辑语句封装在方法体中,对外暴露方法名
  • 好处: 1、隐藏了实现细节 2、提高代码的重用性
  • 调用:select 函数名(实参列表) 【from 表】;
  • 特点:
    ①叫什么(函数名)
    ②干什么(函数功能)
  • 分类:
    1、单行函数
    如 concat、length、ifnull等
    2、分组函数
    功能:做统计使用,又称为统计函数、聚合函数、组函数
常见函数 :

一、单行函数

函数名作用使用
length( )获取字节的个数length(name, 获取name的字符)
concat( )字符拼接concat(first_name,last_name), 将first_name与last_name拼接在一起
upper(),lower()大写,小写upper(name),对name的值大写,lower(name)对name的值小写
substr()截取从指定索引处后面所有字符,注意:索引从1开始sunstr(‘xxxxxxx’,3); 获取字符从第3位到最后,
subString()截取字符,既可以截取指定索引到最后的所有字符,又可以选择截取某一段subString(“xxxxxx”,3), 截取字符从第3位到最后
subString(‘xxxxxx’,1,3);截取第一位到第三位的字符
instr()返回字符串第一次出现的索引,如果找不到返回0instr(“字符”,“要的到的字符”); instr(“老王在你家隔壁”,”隔壁“);查找字符内是否有隔壁返回隔壁所在的索引
trim()去除字符串两边的空格trim(“字符串”); trim(" 老王 "; 为字符串老王去除两边的空格)
lpad()用指定的字符实现左填充指定长度select lpad(“要填充字符”, 字符总个数,“填充内容”);
SELECT LPAD(‘殷素素’,12,’*’) AS out_put; 判断殷素素的长度是否是12 不满足12的时候在最左边填充 *号
rpad()用指定的字符实现右填充指定长度select rpad(“字符”,字符总长度,”填充内容“),
select rpad(“老王”, 5, ""); 当老王不满足5字符的时候在后边填充
replace()替换select replace(“总字符串”, “要替换的内容”,“替换的内容”);
select replace(“老王在你家的隔壁”,“隔壁”,“床上”); 将字符串隔壁替换为床上
  • subStr(字符,要截取的位数)

select substr(“老王是你家隔壁的小姐姐的最好的朋友”, 5) as 截取的字符; 截取字符串第5位到最后的信息
sunStr(字符,截取的位数)

  • subString(字符,截取的位数)
  • subString(字符,截取的开端位数,截取的终止位置);
select subString("老王是你家隔壁的小姐姐的最好的朋友",5) as 截取的字符;

在这里插入图片描述

select subString("老王是你家的隔壁的小姐姐的最好的朋友", 1,3) as 截取的字符;

在这里插入图片描述

  • instr()查找字符串出现的索引
  • 在这里插入图片描述

数学函数

函数名作用使用
round()四舍五入round(数值); round(-1.15); 查出-1.15的四舍的值
ceil向上取整,返回>=该参数的最小整数SELECT CEIL(-1.02);
floor向下取整,返回<=该参数的最大整数SELECT FLOOR(-9.99);
truncate截断select TRUNCATE(数值,截断的下标);
select TRUNCATE(1.256,2);截取1.256到下标2 也就是1.25
mod取余SELECT MOD(数值,余数);
SELECT MOD(10,-3);

日期函数

函数作用使用
now返回当前系统日期+时间SELECT NOW();
curdate返回当前系统日期,不包含时间SELECT CURTIME();
YEAR()可以获取指定的部分,年、月、日、小时、分钟、秒SELECT YEAR(NOW()) 年;
SELECT YEAR(‘1998-1-1’) 年;
SELECT YEAR(hiredate) 年 FROM employees;
str_to_date将字符通过指定的格式转换成日期SELECT STR_TO_DATE(‘1998-3-2’,’%Y-%c-%d’) AS out_put;
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 employees WHERE commission_pct IS NOT NULL;
流程控制
  • if 函数

  • case 函数

  • if 函数: if else的效果

SELECT IF(10<5,'大','小'); 差选10 是否小于5,如果满足就执行第一个不满足执行后面的
"还可以") 
select last_name, commission_pct, if(commission_pct is null , "没奖金, 呵呵", "还可以") from employees where employee_id between 100 and 120;
查看employees表内的第100-120 之间的人是否有奖金 没有的话就返回呵呵有的话就返回还可以

case函数的使用

  • case函数的使用一: switch case 的效果
  • case 函数的使用二:类似于 多重if
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或语句n;
end
  • eg

案例1:查询员工的工资,要求
部门号=30,显示的工资为1.1倍
部门号=40,显示的工资为1.2倍
部门号=50,显示的工资为1.3倍
其他部门,显示的工资为原工资

SELECT salary 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees;

*case 函数的使用二:类似于 多重if

  • 语法
case 
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
。。。
else 要显示的值n或语句n
end

#案例2:查询员工的工资的情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000,显示C级别
否则,显示D级别

select salary, 
CASE
	WHEn salary > 20000	THEN "A"
	WHEN salary > 15000 THEN "B"
	WHEN salary > 10000 THEN "C"
	ELSE  "D"
END  as 工资级别 from employees;
  • 如果用case的 第二种方法查看案例1
select department_id,
case
WHEN department_id = 30 THEN salary*1.1
WHEN department_id = 40 THEN salary*1.2
WHEN department_id = 50 THEN salary*1.3
else salary end as 新薪资,
salary as 原薪资 from employees;
  • 切忌 case的方式一是java的switch()case 第二种类似于Java的if…else if

Practices


-- 1. 显示系统时间(注:日期+时间)

SELECT now();
-- 2. 查询员工号,姓名,工资,以及工资提高百分之 20%后的结果(new salary)
select employee_id,last_name,salary 原薪资,salary*1.2 as "new salary" from employees;

-- 3. 将员工的姓名按首字母排序,并写出姓名的长度(length)

--  按照姓名排序select employee_id, last_name, length(last_name) 姓名长度 from employees order by last_name;


 select employee_id,last_name, LENGTH(last_name) 姓名长度, subString(last_name,1,1) as 首字母 from employees ORDER BY 首字母;
 
 select employee_id, last_name,LENGTH(last_name) 姓名长度, subStr(last_name,1,1) as 首字母 from employees order by 首字母 ;
 
--  4. 做一个查询,产生下面的结果
--  <last_name> earns <salary> monthly but wants <salary*3>

select last_name, concat(last_name,"earns"), concat(salary,"monthly but wants"), salary*3 from employees where last_name = "King";
 
 
 select CONCAT(last_name,"earns",salary,"monthly but wants",salary*3) as "Dream Salary" from employees where salary = 24000;
 
 /*
 5:使用 case-when,按照下面的条件:
job grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E

 */
 
 select last_name,job_id job,
 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"

END as "Grade" from employees;



分组函数

max 最大值
min 最小值
sum 求和
avg 求平均值
count 统计个数

语法

   select  函数(字段) from 表名;
   eg: select sum(salary) from employee; 对employee表的薪资求平均值

支持的类型

sum 和avg 一般用于处理数值型
max min count 可以处理任意数据类型

注意点

以上函数都忽略null
都可以搭配distinct 使用,实现去重的统计
     eg:
         select sum(distinct 字段) from 表名
count (字段) 统计该字段非空值的个数
count(*) 统计结果集的行数
和分组函数一同查询的字段要求是group by后的字段

select sum(salary) from employees;
select avg(salary) from employees;

select last_name, 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;

select sum(salary), ROUND(AVG(salary),2) 平均,	MAX(salary) 最大值,min(salary)最低,COUNT(salary) 个数 from employees; 


-- 2、参数支持哪些类型

select sum(last_name),avg(last_name)from employees;
select sum(hiredate), avg(hiredate) from employees;
select MAX(last_name), min(last_name) from employees;
select MAX(hiredate),MIN(hiredate) from employees;
select count(commission_pct) from employees;
select count(last_name) from employees

-- 3、是否忽略null

select sum(commission_pct),avg(commission_pct) from employees;

select max(commission_pct),min(commission_pct) from employees;

-- 4、和distinct搭配

select sum(distinct(salary)), sum(salary) from employees;

select count(DISTINCT salary), count(salary) from employees; 
 
--  #5、count函数的详细介绍

select count(salary) from employees;
select count(*) from employees;
select count(1) from employees;


和分组函数一同查询的字段有限制
eg:

select avg(salary), employee_id from employees;

  • 我们可以看到结果是显示不合理的 ,employee_id是有很多行的 而平均值只有一个所有这两个放在一起是不合适的,所以要分组group by后的才有意义
    在这里插入图片描述
分组后的内容
 	select avg(salary), employee_id from employees GROUP BY employee_id;

在这里插入图片描述

  • 上面分组后的就有意义了
    Practices

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

select MAX(salary) 最大值薪资, MIN(salary) 最小薪资, avg(salary) 平均薪资, sum(salary) 总薪资 from employees;


-- #2.查询员工表中的最大入职时间和最小入职时间的相差天数 (DIFFRENCE)

select MAX(hiredate) 最大入职时间, MIN(hiredate) 最小入职时间, (MAX(hiredate)-MIN(hiredate))/1000/3600/24 DIFFRENCE  from employees;


-- 日期时间差:DATEDIFF函数


select DATEDIFF('2020-22-23', now())
select DATEDIFF(now(),'1995-10-30')   -- 查询一个人活了多久

-- #3.查询部门编号为90的员工个数
select count(*) from employees where department_id = 90;
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Lovely_red_scarf

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值