Mysql 4 —— select 进阶
起别名
- 便于理解
- 如果要查询的字段有重名的情况，使用别名可以区分开
方式一：使用as
mysql> SELECT 100%98 As 结果;
mysql> SELECT last_name As 姓, first_name As 名 FROM employees;
方式二：使用空格
mysql> SELECT last_name 姓 , first_name 名 FROM employees;
案例：查询salary，显示结果为 out put
mysql> SELECT salary As "out put" FROM employees; ///注意这边的双引号
去重
案例：查询员工表中涉及|到的所有的部门编号
mysql> SELECT DISTINCT department_id FROM employees;
+号的作用
回顾java中的+号：
- 运算符，两个操作数都为数值型
- 连接符，只要有一个操作数为字符串
mysql中的+号，仅仅只有一个功能：运算符
mysql>select 100+90;///两个操作数都为数值型，则做加法运算
mysql>select '123'+90;///其中一方为字符型，试图将字符型数值转换成数值型
///如果转换成功,则继续做加法运算
mysql>select 'john'+90;1 ///如果转换失败，则将字符型数值转换成0特别的，只要一方为NULL，结果肯定为null
在mysql中的拼接有一个函数加concat
mysql> concat('a','b','c') as '字符集' from XXX；
但如果其中有一个是null，那么结果就是null。
条件筛选
一、简单条件运算符
> < <> != = <= >= <=>
二、按逻辑表达式筛选
案例1：查询工资z在10000到20000之间的员工名、工资以及奖金
mysql>SELECT last_name,salary, commission_pct FROM employees WHERE salary>=10000 AND salary<=20000;
案例2：查询部门编号不是在90到110之间，或者工资高于15000的员工信息
mysql>SELECT * FROM employees where NOT (department id>=90 ANDdepartment id<=110) OR salary>15000;
三、模糊查询
1、like特点：
—般和通配符搭配使用
通配符：
%任意多个字符，包含0个字符任意单个字符_任意单个字符。
案例1：查询员工名中包含字符a的员工信息
mysql>SELECT * from employees where last name like '%a%';
案例2：查询员工名中第三个字符为e，第五个字符为a的员工名和工资
mysql>SELECT * last_name,salary from employees where last_name like '__e_a%';
案例3：查询员工名中第二个字符为_的员工名
mysql>SELECT last_name from employees where last_name like '-\-%';
mysql>SELECT last_name from employees where last_name like '-&-%' ESCAPE '&';
2、between and
可以提高语句的简洁度 包含临界值 两个临界值不要调换
案例1：查询员工编号在100到120之间的员工信息
mysql>SELECT * FROM employees WHERE employee_id >=100 AND employee_id<=120;
mysql>SELECT * FROM employees WHERE employee_id between 100 AND 120;
3、in
含义：判断某字段的值是否属于in列表中的某一项
特点：1、使用in提高语句简洁度 2、in列表的值类型必须统一或兼容3、不支持like语法中的通配符
案例：查询员工的工种编号IT_PROG、AD_VP、AD_PRES中的一个
mysql>SELECT last_name , job_id FROM employees WHERE job_id = 'IT_PROG' OR job_id = 'AD_VP' OR job_id = 'AD_PRES';
mysql>SELECT last_name , job_id FROM employees WHERE job_id IN ('IT_PROG' , 'AD_VP' , 'AD_PRES');
4、is nulll is not null
=或<>不能用于判断null值
is null 或 is not null 可以判断null值
案例1：查询没有奖金的员工名和奖金率
mysql>SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL;
mysql>SELECT last_name,commission_pct FROM employees WHERE commission_pct <=> NULL;
补充 安全等于 <=>
排序查询
语法 SELECT 查询列表 FROM 表 【WHERE 筛选条件】order by 排序序列 【ascldesc】
特点：
- asc代表升序，desc代表的是降序，如果不写，默认是升序。
- order by 子句中国可以支持单个字段、多个字段、表达式、函数、别名
- order by 子句一般是放在查询语句的最后面，但limit 子句除外。
案例1：查询员工信息，要求工资从高到低排序 SELECT * FROM employees ORDER BY DESC;
SELECT * FROM employees ORDER BY ASC;
案例2：查询部门编号>=90的员工信息，按入职时间先后进行排序
SELECT * employees WHERE department_id>=90 ORDER BY hiredate ASC;
案例3：按年薪的高低显示员工的信息和年薪【按表达式排序】
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪 FROM employees OREDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
案例4：按年薪的高低显示员工的信息和年薪【按别名排序】
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪 FROM employees 年薪 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;
常见函数
概念：类似与java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1、隐藏了实现细节 2、提高代码的重用性
调用 select 函数名(实参列表)【from 表】
特点：1、叫什么 2、干什么
分类：1、单行函数 如 concat、length、ifnull 2、分组函数 功能：做统计使用，又称为统计函数、聚合函数、组函数
一、字符函数
1、length 获取参数值的字节个数
SELECT LENGTH('john');///4
SELECT LENGTH('张三丰hahaha');///15
SHOW VARIABLES LIKE '%char%'
2、concat 拼接字符串
SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;
3、upper、lower
SELECT UPPER('jhon')；
SELECT lower('jOHn')；
4、substr、substring
截取从指定索引处后面所有字符
SELECT SUBSTR ('李莫愁爱上了陆展元'，7) out_put;///陆展元
截取从指定索引处指定字符长度的字符
SELECT SUBSTR ('李莫愁爱上了陆展元',1, 3) out_put;///李莫愁
5、instr 返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR ('李莫愁爱上了陆展元','陆元') AS output;
6、trim
SELECT length(TRIM(' 张翠山 ‘）AS output ；//////3
SELECT TRIM('a' FROM 'aaaaaaaaaaaaa张aaaaaa翠山aaaaaaaaaaaa‘）AS output ；//////张aaaaaa翠山
7、lpad 用指定的字符实现左填充
SELECT LPAD('殷素素',10,'*') output;
8、Rpad 用指定的字符实现左填充
9、replace 替换
SELECT REPLACE ('周芷若周芷若周芷若周芷若周芷若张无忌'，'周芷若'，'赵敏')；///赵敏赵敏赵敏赵敏赵敏赵敏张无忌
二、数学函数
round（） 四舍五入
ceil（）向上取整，返回>=该参数的最小整数
floor() 向下取整，返回<=该参数的最大整数
truncate 截断
mod取余 a-a/b*b
三、日期函数
now() 返回当前系统的日期+时间
curdate 返回当前系统的日期，不包含时间
curtime 返回当前系统的时间，不包含日期
year() day() month() hour() minute() second()
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 phone, date_format(time, '%Y%m%d%H%i%s') from user where phone='xxxxxxxx' #20160513160750
select phone, date_format(time, '%Y%m%d') from user where phone='xxxxxxxx' #20160513
四、其他函数
version 当前数据库服务器的版本
database当前打开的数据库
五、流程控制函数
1、if函数：if else 的效果
select if (10<5,'大','小')；
2、case 函数的使用一：switch case 的效果
switch
java中
switch(变量或表达式){
case 常量1:语句1; break;...
default:语句n; break;
}
mysql中
case要判断的字段或表达式
when常量1 then要显示的值1或语句1; when常量2 then要显示的值2或语句2;I...
else要显示的值n或语句n;
end
SELECT 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;
3、case函数的使用二（类似于多重if）：
java中:if(条件1){
语句1;
}else if(条件2){
语句2;
}
else{
语句n;
SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;
分组函数
1、sum 求和、avg平均值、max最大值﹒min最小值、count计算个数
2、以上分组函数都忽略null值
3、可以和distinct搭配是实现去重的运算
4、count 函数
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT count(*) FROM employees;
SELECT count(1) FROM employees;
分页查询
应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
语法：
SELECT 查询列表
FROM 表
【join type join 表2】
ON 连接条件
WHERE 筛选条件
GROUP BY 分组字段
HAVING 分组后筛选
ORDER BY 排序后的字段
LIMIT OFFSER SIZE;
offset要显示条目的起始索引（起始索引从0开始）
size 要显示的条目个数
特点：
- limit语句放在查询语句的最后
- 公式
- 要显示的页数page，每页的条目数size
- select 查询列表 from 表 (page-1)*size,size;
