SQL语法体系学习笔记
SQL语法之基础查询(进阶1)and条件查询(进阶2)
SQL语法之排序查询(进阶3)and常见函数(进阶4)
SQL语法之分组函数,分组查询(进阶5)and连接查询(sql92语法进阶6)
排序查询
引入:
select * from employees;
语法:
select 查询列表 from 表 [where 筛选条件] order by
排序列表 [asc | desc]
特点
- asc代表的是升序,desc代表的是降序(如果不写,默认是升序)
- order by 子句中可以支持单个字段,多个字段,表达式,函数,别名
- order by 子句一般是放在查询语句的最后面,limit子句除外
实例
案例1
查询员工信息,要求工资从高到低排序:
select * from employees order by salary desc;
查询员工信息,要求工资从高到低排序(默认可以不写):
select * from employees order by salary asc;
select * from employees order by salary;
案例2
查询部分编号>=90的员工信息,按入职时间的先后进行排序[添加筛选条件]:
select * from employees where department_id>=90 order by hiredate ASC;
案例3
按年薪的高低显示员工的信息和年薪[按表达式排序]
SELECT * ,salary * 12 *(1+IFNULL(commission_pct,0)) 年薪 FROM employees ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
案例4
按年薪的高低显示员工的信息和年薪[按别名排序]
SELECT * ,salary * 12 *(1+IFNULL(commission_pct,0)) 年薪 FROM employees ORDER BY 年薪 DESC;
案例5
按姓名的长度显示员工的姓名和工资[按函数排序]
select length(last_name)字节长度,last_name,salary from employees order by length(last_name) DESC;
案例6
查询员工信息,要求先按工资排序,再按员工编号排序[按多个字段排序]
select * from employees order by salary asc,employee_id desc;
总结测试:
第一题
查询员工的姓名和部门号和年薪,按年薪降序,按姓名升序
select last_name ,department_id,salary*12*(1+ifnull(commission_pct,0)) 年薪 from employees order by 年薪 desc ,last_name ASC;
第二题
选择工资不在8000到17000的员工姓名和工资,按工资降序
select last_name,salary from employees where salary not between 8000 and 17000 order by salary desc;
SELECT last_name,salary FROM employees WHERE NOT(salary BETWEEN 8000 AND 17000)ORDER BY salary DESC;
第三题
查询邮箱中包含e的员工信息,并先按邮箱的字节数降序,再按部门号升序
Select *,length(email) from employees where email like '%e%' order by length(email) DESC,department_id ASC;
常见函数
概念
类似于java 的方法,将一组逻辑语句封装在方法体中,对外暴露方法名
好处
- 隐藏了实现细节
- 提高代码的重用性
调用
select 函数名(实参列表) [from 表]
(如果函数的参数用到了表中的字段,那就加入from 表,如果没有,那就不需要加)
特点
- 叫什么(函数名)
- 干什么(函数功能)
分类:
- 单行函数(做处理使用),如:
concat
,length
,ifnull
等 - 分组函数。 功能:做统计使用,又称为统计函数,聚合函数,组函数
字符函数
1. length 获取参数值的字节个数
select length('张三丰hahah');
这里为什么是14呢?首先我们来看看所使用的字符编码是什么
show variables like '%char%';
使用的是utf-8
,这里的中文字占了三个字节,9+5=14;如果使用的是gbk
编码的话,那么中文字将只占两个字节.
2.concat
拼接字符串
SELECT CONCAT(first_name,'_',last_name) FROM employees;
3.upeer,lower
select upper('john');
select lower('joHn');
示例:把姓变大写,名变小写
select concat(upper(last_name),lower(first_name)) 姓名 from employees;
4.substring 或者substr
注意
索引从1开始的哦
select substr('李莫愁爱上了陆展元',6) 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
返回子串第一次出现的索引,如果找不到返回0
SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') AS out_put;
6.trim (默认去掉前后的空格,或者去掉前后的指定字符)
select trim(' 张翠山 ') As out_put;
会不会有人好奇右边的空格去没去掉呢?我们来一起验证一下:
SELECT LENGTH(TRIM(' 张翠山 ')) AS out_put;
一个中文字符三个字节,然后3个中文字符也就是9个字节,那么右边就没有空格喽
select trim('a' from 'aaaaaaaa张aaaaa翠山aaaaaaa') As out_put;
7.lpad 用指定的字符实现左填充指定长度
SELECT LPAD('殷素素',10,'*') AS out_put;
注意:
(最后显示的字符长度一定是中间所输入的数字,如果前面是三个长度,中间数字是2,那么会被从右往左截取到符合中间数字的字符长度)
8.rpad 用指定的字符实现右填充指定长度
select rpad('殷素素',12,'ab') As out_put;
9.replace 替换
select replace('张无忌爱上了周芷若','周芷若','赵敏') As out_put;
数学函数
1.round 四舍五入
(先把绝对值四舍五入,然后再加符号即可)
select round(-1.45);
小数点后四舍五入保留两位小数:
select round(1.567,2);
2.ceil 向上取整,返回大于等于该参数的最小整数
select ceil(1.52);
select ceil(1.001);
select ceil(1.00);
3.floor,向下取整,返回小于等于该参数的最大整数
select floor(-9.99);
4.truncate 截断
select truncate(1.65,1);
5.mod 取余
select mod(10,3);
解释mod
(
mod(a,b): a-a/b*b
mod(-10,-3):-10-(-10)/(-3)*(-3)=-1
)
日期函数
1.now 返回当前系统日期+时间
select now();
2.curdate 返回当前系统日期,不包含时间
select curdate();
3.curtime 返回当前时间,不包含日期
select curtime();
4.可以获取指定的部分,年,月,日,小时,分钟,秒
SELECT YEAR(NOW()) 年;
select year('1998-1-5') 年;
select year(hiredate) 年 from employees;
SELECT MONTH(NOW()) 月;
英文的月份:
select monthname(now()) 月;
5.str_to_date(将日期格式的字符转换成指定格式的日期)
SELECT STR_TO_DATE('09-13-1999','%m-%d-%Y');
SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;
例子1:查询入职日期为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');
6.date_format(将日期转换成字符)
select date_format(now(),'%y年%m月%d日') As out_put;
例题:查询有奖金的员工名和入职日期(XX月/XX日 XX年)
select last_name, date_format(hiredate,'%m月/%d日 %年') 入职日期 from rmployees where commission_pct is not null;
其它函数
查询MySQL版本号
select version();
查询当前库
select database();
当前用户:
select user();
流程控制函数
if函数(if else 的效果)
SELECT IF(10>5,'大','小');
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金,呵呵','有奖金,嘻嘻') 备注 FROM employees;
case函数使用1(类似switch case)
java中:
switch(变量或表达式){
case 常量1:语句2;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
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函数的使用2(类似于 多重if):
java中:
if(条件1){
语句1;
}else if(条件2){
语句2;
}
…………
else{
}
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,
case
when salary>20000 then 'A'
when salary>15000 then 'B'
when salary>10000 then 'C'
else 'D'
end as 工资级别
from employees;
常见函数总结:
字符函数:
length,
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,
datebase,
user
控制函数:
if
case
习题总结:
- 显示系统时间(注:日期+时间)
select now();
- 查询员工号,姓名,工资,以及工资提高百分之20之后的结果(new salary)
select employee_id,last_name,salary,salary*1.2 "new salary" from employees;
- 将员工的姓名按首字符排序,并且写出姓名的长度(length)
SELECT LENGTH(last_name) 长度 ,SUBSTR(last_name,1,1) 首字符 ,last_name FROM employees ORDER BY 首字符;
(注意这个和按last_name
排序不一样,)
- 做一个查询,产生下面的结果
<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 employees where salary=24000;
- 使用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_id,
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
WHERE job_id='AD_PRES';