#进阶4:常见函数/*
概念:类似于java的方法,将一组逻辑语句封装在方法体中,对外暴露方法名
好处:1、隐藏了实现细节 2、提高代码的重用性
调用:select 函数名(实参列表) 【from 表】;
特点:
①叫什么(函数名)
②干什么(函数功能)
分类:
1、单行函数
如 concat、length、ifnull等
2、分组函数
功能:做统计使用,又称为统计函数、聚合函数、组函数
常见函数:
一、单行函数
字符函数:
length:获取字节个数(utf-8一个汉字代表3个字节,gbk为2个字节)
concat
substr
instr
trim
upper
lower
lpad
rpad
replace
数学函数:
round
ceil
floor
truncate
mod
日期函数:
now
curdate
curtime
year
month
monthname
day
hour
minute
second
str_to_date
date_format
其他函数:
version
database
user
控制函数
if
case
*/#一、字符函数#1.length 获取参数值的字节个数SELECT LENGTH('john');SELECT LENGTH('张三丰hahaha');SHOW VARIABLES LIKE'%char%'#2.concat 拼接字符串SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;#3.upper、lowerSELECT UPPER('john');SELECT LOWER('joHn');#示例:将姓变大写,名变小写,然后拼接SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM 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 employees;#5.instr 返回子串第一次出现的索引,如果找不到返回0SELECT INSTR('杨不殷六侠悔爱上了殷六侠','殷八侠')AS out_put;#6.trimSELECT 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 替换SELECTREPLACE('周芷若周芷若周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏')AS out_put;#二、数学函数#round 四舍五入SELECTROUND(-1.55);SELECTROUND(1.567,2);#ceil 向上取整,返回>=该参数的最小整数SELECT CEIL(-1.02);#floor 向下取整,返回<=该参数的最大整数SELECT FLOOR(-9.99);#truncate 截断SELECTTRUNCATE(1.69999,1);#mod取余/*
mod(a,b) : a-a/b*b
mod(-10,-3):-10- (-10)/(-3)*(-3)=-1
*/SELECTMOD(10,-3);SELECT10%3;#三、日期函数#now 返回当前系统日期+时间SELECTNOW();#curdate 返回当前系统日期,不包含时间SELECT CURDATE();#curtime 返回当前时间,不包含日期SELECT CURTIME();#可以获取指定的部分,年、月、日、小时、分钟、秒SELECTYEAR(NOW()) 年;SELECTYEAR('1998-1-1') 年;SELECTYEAR(hiredate) 年 FROM employees;SELECTMONTH(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 employees
WHERE commission_pct ISNOTNULL;#四、其他函数SELECT VERSION();SELECTDATABASE();SELECTUSER();#五、流程控制函数#1.if函数: if else 的效果SELECTIF(10<5,'大','小');SELECT last_name,commission_pct,IF(commission_pct ISNULL,'没奖金,呵呵','有奖金,嘻嘻') 备注
FROM employees;#2.case函数的使用一: switch case 的效果/*
java中
switch(变量或表达式){
case 常量1:语句1;break;
...
default:语句n;break;
}
mysql中
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值n或语句n;
end
*//*案例:查询员工的工资,要求
部门号=30,显示的工资为1.1倍
部门号=40,显示的工资为1.2倍
部门号=50,显示的工资为1.3倍
其他部门,显示的工资为原工资
*/SELECT salary 原始工资,department_id,CASE department_id
WHEN30THEN salary*1.1WHEN40THEN salary*1.2WHEN50THEN salary*1.3ELSE salary
ENDAS 新工资
FROM employees;#3.case 函数的使用二:类似于 多重if/*
java中:
if(条件1){
语句1;
}else if(条件2){
语句2;
}
...
else{
语句n;
}
mysql中:
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
。。。
else 要显示的值n或语句n
end
*/#案例:查询员工的工资的情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000,显示C级别
否则,显示D级别
SELECT salary,CASEWHEN salary>20000THEN'A'WHEN salary>15000THEN'B'WHEN salary>10000THEN'C'ELSE'D'ENDAS 工资级别
FROM employees;
分组查询
#进阶5:分组查询/*
语法:
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、可以搭配着排序使用
*/#引入:查询每个部门的员工个数SELECTCOUNT(*)FROM employees WHERE department_id=90;#1.简单的分组#案例1:查询每个工种的员工平均工资SELECTAVG(salary),job_id
FROM employees
GROUPBY job_id;#案例2:查询每个位置的部门个数SELECTCOUNT(*),location_id
FROM departments
GROUPBY location_id;#2、可以实现分组前的筛选#案例1:查询邮箱中包含a字符的 每个部门的最高工资SELECTMAX(salary),department_id
FROM employees
WHERE email LIKE'%a%'GROUPBY department_id;#案例2:查询有奖金的每个领导手下员工的平均工资SELECTAVG(salary),manager_id
FROM employees
WHERE commission_pct ISNOTNULLGROUPBY manager_id;#3、分组后筛选#案例:查询哪个部门的员工个数>5#①查询每个部门的员工个数SELECTCOUNT(*),department_id
FROM employees
GROUPBY department_id;#② 筛选刚才①结果SELECTCOUNT(*),department_id
FROM employees
GROUPBY department_id
HAVINGCOUNT(*)>5;#案例2:每个工种有奖金的员工的最高工资>12000的工种编号和最高工资SELECT job_id,MAX(salary)FROM employees
WHERE commission_pct ISNOTNULLGROUPBY job_id
HAVINGMAX(salary)>12000;#案例3:领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资
manager_id>102SELECT manager_id,MIN(salary)FROM employees
GROUPBY manager_id
HAVINGMIN(salary)>5000;#4.添加排序#案例:每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序SELECT job_id,MAX(salary) m
FROM employees
WHERE commission_pct ISNOTNULLGROUPBY job_id
HAVING m>6000ORDERBY m ;#5.按多个字段分组#案例:查询每个工种每个部门的最低工资,并按最低工资降序SELECTMIN(salary),job_id,department_id
FROM employees
GROUPBY department_id,job_id
ORDERBYMIN(salary)DESC;
#进阶6:连接查询/*
含义:又称多表查询,当查询的字段来自于多个表时,就会用到连接查询
笛卡尔乘积现象:表1 有m行,表2有n行,结果=m*n行
发生原因:没有有效的连接条件
如何避免:添加有效的连接条件
分类:
按年代分类:
sql92标准:仅仅支持内连接
sql99标准【推荐】:支持内连接+外连接(左外和右外)+交叉连接
按功能分类:
内连接:
等值连接
非等值连接
自连接
外连接:
左外连接
右外连接
全外连接
交叉连接
*/SELECT*FROM beauty;SELECT*FROM boys;SELECT NAME,boyName FROM boys,beauty
WHERE beauty.boyfriend_id= boys.id;#一、sql92标准#1、等值连接/*
① 多表等值连接的结果为多表的交集部分
②n表连接,至少需要n-1个连接条件
③ 多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用,比如排序、分组、筛选
*/#案例1:查询女神名和对应的男神名SELECT NAME,boyName
FROM boys,beauty
WHERE beauty.boyfriend_id= boys.id;#案例2:查询员工名和对应的部门名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`;#3、两个表的顺序是否可以调换#查询员工名、工种号、工种名SELECT e.last_name,e.job_id,j.job_title
FROM jobs j,employees e
WHERE e.`job_id`=j.`job_id`;#4、可以加筛选#案例:查询有奖金的员工名、部门名SELECT last_name,department_name,commission_pct
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`AND e.`commission_pct`ISNOTNULL;#案例2:查询城市名中第二个字符为o的部门名和城市名SELECT department_name,city
FROM departments d,locations l
WHERE d.`location_id`= l.`location_id`AND city LIKE'_o%';#5、可以加分组#案例1:查询每个城市的部门个数SELECTCOUNT(*) 个数,city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`GROUPBY city;#案例2:查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资SELECT department_name,d.`manager_id`,MIN(salary)FROM departments d,employees e
WHERE d.`department_id`=e.`department_id`AND commission_pct ISNOTNULLGROUPBY department_name,d.`manager_id`;#6、可以加排序#案例:查询每个工种的工种名和员工的个数,并且按员工个数降序SELECT job_title,COUNT(*)FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`GROUPBY job_title
ORDERBYCOUNT(*)DESC;#7、可以实现三表连接?#案例:查询员工名、部门名和所在的城市SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`AND d.`location_id`=l.`location_id`AND city LIKE's%'ORDERBY department_name DESC;#2、非等值连接#案例1:查询员工的工资和工资级别SELECT salary,grade_level
FROM employees e,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 employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;
排序查询
#进阶3:排序查询/*
语法:
select 查询列表
from 表名
【where 筛选条件】
order by 排序的字段或表达式;
特点:
1、asc代表的是升序,可以省略
desc代表的是降序
2、order by子句可以支持 单个字段、别名、表达式、函数、多个字段
3、order by子句在查询语句的最后面,除了limit子句
*/#1、按单个字段排序SELECT*FROM employees ORDERBY salary DESC;#2、添加筛选条件再排序#案例:查询部门编号>=90的员工信息,并按员工编号降序SELECT*FROM employees
WHERE department_id>=90ORDERBY employee_id DESC;#3、按表达式排序#案例:查询员工信息 按年薪降序SELECT*,salary*12*(1+IFNULL(commission_pct,0))FROM employees
ORDERBY salary*12*(1+IFNULL(commission_pct,0))DESC;#4、按别名排序#案例:查询员工信息 按年薪升序SELECT*,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDERBY 年薪 ASC;#5、按函数排序#案例:查询员工名,并且按名字的长度降序SELECT LENGTH(last_name),last_name
FROM employees
ORDERBY LENGTH(last_name)DESC;#6、按多个字段排序#案例:查询员工信息,要求先按工资降序,再按employee_id升序SELECT*FROM employees
ORDERBY salary DESC,employee_id ASC;
单行函数(案例)
#1. 显示系统时间(注:日期+时间)SELECTNOW();#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
ORDERBY 首字符;#4. 做一个查询,产生下面的结果<last_name> earns <salary> monthly but wants <salary*3>
Dream Salary
King earns 24000 monthly but wants 72000SELECT 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
产生下面的结果
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'ENDAS Grade
FROM employees
WHERE job_id ='AD_PRES';
分组查询(案例)
#1.查询各job_id的员工工资的最大值,最小值,平均值,总和,并按job_id升序SELECTMAX(salary),MIN(salary),AVG(salary),SUM(salary),job_id
FROM employees
GROUPBY job_id
ORDERBY job_id;#2.查询员工最高工资和最低工资的差距(DIFFERENCE)SELECTMAX(salary)-MIN(salary) DIFFRENCE
FROM employees;#3.查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内SELECTMIN(salary),manager_id
FROM employees
WHERE manager_id ISNOTNULLGROUPBY manager_id
HAVINGMIN(salary)>=6000;#4.查询所有部门的编号,员工数量和工资平均值,并按平均工资降序SELECT department_id,COUNT(*),AVG(salary) a
FROM employees
GROUPBY department_id
ORDERBY a DESC;#5.选择具有各个job_id的员工人数SELECTCOUNT(*) 个数,job_id
FROM employees
GROUPBY job_id;
排序查询(案例)
#1.查询员工的姓名和部门号和年薪,按年薪降序 按姓名升序SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDERBY 年薪 DESC,last_name ASC;#2.选择工资不在8000到17000的员工的姓名和工资,按工资降序SELECT last_name,salary
FROM employees
WHERE salary NOTBETWEEN8000AND17000ORDERBY salary DESC;#3.查询邮箱中包含e的员工信息,并先按邮箱的字节数降序,再按部门号升序SELECT*,LENGTH(email)FROM employees
WHERE email LIKE'%e%'ORDERBY LENGTH(email)DESC,department_id ASC;
分组函数(案例)
#1.查询公司员工工资的最大值,最小值,平均值,总和SELECTMAX(salary) 最大值,MIN(salary) 最小值,AVG(salary) 平均值,SUM(salary) 和
FROM employees;#2.查询员工表中的最大入职时间和最小入职时间的相差天数 (DIFFRENCE)SELECTMAX(hiredate) 最大,MIN(hiredate) 最小,(MAX(hiredate)-MIN(hiredate))/1000/3600/24 DIFFRENCE
FROM employees;SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) DIFFRENCE
FROM employees;SELECT DATEDIFF('1995-2-7','1995-2-6');#3.查询部门编号为90的员工个数SELECTCOUNT(*)FROM employees WHERE department_id =90;