SQL语法体系学习笔记
SQL语法之基础查询（进阶1）and条件查询（进阶2）
SQL语法之排序查询（进阶3）and常见函数（进阶4）
SQL语法之分组函数，分组查询（进阶5）and连接查询（sql92语法进阶6）
文章目录
SQL语法体系学习笔记SQL语法之基础查询（进阶1）and条件查询（进阶2）SQL语法之排序查询（进阶3）and常见函数（进阶4）SQL语法之分组函数，分组查询（进阶5）and连接查询（sql92语法进阶6）排序查询引入：语法：特点实例案例1案例2案例3案例4案例5案例6总结测试：第一题第二题第三题常见函数概念好处调用特点分类：字符函数1. length 获取参数值的字节个数2.`concat` 拼接字符串3.upeer,lower4.substring 或者substr注意截取从指定索引处指定字符长度的字符示例：姓名中首字符 大写，其它字符小写然后用_拼接，显示出来5.instr6.trim (默认去掉前后的空格,或者去掉前后的指定字符)7.lpad 用指定的字符实现左填充指定长度注意：8.rpad 用指定的字符实现右填充指定长度9.replace 替换数学函数1.round 四舍五入2.ceil 向上取整,返回大于等于该参数的最小整数3.floor,向下取整，返回小于等于该参数的最大整数4.truncate 截断5.mod 取余解释mod日期函数1.now 返回当前系统日期+时间2.curdate 返回当前系统日期，不包含时间3.curtime 返回当前时间，不包含日期4.可以获取指定的部分,年，月，日，小时，分钟，秒5.str_to_date（将日期格式的字符转换成指定格式的日期）例子1：查询入职日期为1992-4-3的员工信息6.date_format（将日期转换成字符）例题：查询有奖金的员工名和入职日期（XX月/XX日 XX年）其它函数流程控制函数if函数(if else 的效果)case函数使用1(类似switch case)java中：mysql中：案例：查询员工的工资，要求部门号=30，显示的工资为1.1倍；部门号=40，显示的工资为1.2倍；部门号=50，显示的工资为1.3倍；其它部分，显示的工资原工资case函数的使用2（类似于 多重if）:java中：mysql中：案例：查询员工的工资的情况：如果工资>20000，显示A级别；如果工资>15000，显示B级别；如果工资>10000，显示C级别；否则，显示D级别。常见函数总结：字符函数：数学函数：日期函数：其它函数：控制函数：习题总结：排序查询
引入：
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*bmod(-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_idwhen 30 then salary*1.1when 40 then salary*1.2when 50 then salary*1.3else salaryend as 新工资from employees;
case函数的使用2（类似于 多重if）:
java中：
if(条件1){语句1；}else if（条件2）{语句2;}…………else{}
mysql中：
casewhen 条件1 then 要显示的值1或 语句1when 条件2 then 要显示的值2或 语句2……else 要显示的值n或语句nend
案例：查询员工的工资的情况：如果工资>20000，显示A级别；如果工资>15000，显示B级别；如果工资>10000，显示C级别；否则，显示D级别。
select salary,casewhen 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
日期函数：
nowcurdatecurtimeyearmonthmonthnamedayhourminutesecondstr_to_datedate_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 SalaryKing 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 GradeAD_PRES AST_MANBIT_PROGCSA_REPDST_CLERK E
产生下面的结果：
Last_name Job_id Gradeking 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 gradeFROM employees WHERE job_id='AD_PRES';
