第二天MySQL

sql条件查询、排序查询、一些常见函数

2020/5/5

一、 DQL条件查询

语法：（执行顺序已标出）

SELECT

　　　　　查询列表                      3

FORM

　　　　　表名                             1

WHERE

                 筛选条件; 　　　　　   2

分类：

按条件表达式筛选

条件运算符：>  <  =  !=  <>  >=  <=

#查找员工月薪>12000的员工信息

SELECT

               *

FROM

               employees

WHERE

               salary>12000;

按逻辑表达式筛选

逻辑运算符：and  or  not

#查找员工编号在90至120的员工名、薪水以及员工编号

SELECT

       last_name,

       salary,

       employee_id

FROM

       employees

WHERE

               employee_id >= 90 AND employee_id <= 120;

 

模糊查询

关键字：

1   like

特点：一般和通配符搭配使用，可以判断字符型或数值型。

通配符

%  可带表任意多个字符（包括空字符）

_  只能代表任意一个字符

案例一：查找员工名中含有字符a的员工名和部门编号

 

 

 

 运行结果:

 

 

案例二：查找员工名中第二个字符为"a"，第五个字符为"o"的员工名、部门编号和薪水

 

 

 

运行结果;

 

注意：若想查找的字符本身就为特殊的字符如_或%，则需要通过对该字符转义来实现。共有俩种方式

方式一：使用’’来转义

方式二: 使用ESCAPE关键字配合

如：#查找员工名第二个字符为"_"的员工名、月薪以及奖金率

SELECT

       last_name,

       salary,

       commission_pct

FROM

       employees

WHERE

       last_name LIKE "_\_%";

-----------------------------------------------------

SELECT

       last_name,salary,

       commission_pct

FROM

       employees

WHERE

        last_name

 LIKE

　　"_$_%" ESCAPE  "$";  (相当于把‘$‘附上转义的功能)

 

2   between and

优点：

能够提高语句的简洁度

包含临界值

两个临界值不要调换顺序

案例： 查找员工部门编号在90到120之间的员工名、部门编号、工作编号

     

运行结果：

 

以上两种方法返回的查询结果一样，两种方式的表达意义完全等价！

 

3   in

特点：

1. 可以提高语句简洁度
2. in 列表的值类型必须一致或兼容（可以隐式的转换）
3. 不支持使用通配符表示的的模糊字符

#查找员工部门编号是"AD_VP"、"FI_MGR"、"FI_ACCOUNT"的员工名、薪水、部门编号以及部门编号

SELECT

       last_name,

       salary,

       job_id,

       department_id

FROM

       employees

WHERE

       job_id = "AD_VP" OR job_id = "FI_MGR" OR job_id = "FI_ACCOUNT";

---------------------------------------------------------------------

SELECT

       last_name,

       salary,

       job_id,

       department_id

FROM

       employees

WHERE

       job_id IN ("AD_VP","FI_MGR","FI_ACCOUNT");

 

上面这两种方式完全等价

 

4   is null 、is not null

特点：=或<>不能用于判断null值，而is null 或is not null可以判断null值（is只能用于判断null，与null搭配，不可以判断数值如12000）

注意：安全等于<=>即可以判断NULL值，也可以判断其他普通的数值，可读性较差。

#查找奖金率为null的员工名、月薪以及奖金率

SELECT

       last_name,

       salary,

       commission_pct

FROM

       employees

WHERE

       commission_pct IS NULL;（可读性很好！）

--------------------------------------------------------

SELECT

       last_name,

       salary,

       commission_pct

FROM

       employees

WHERE

       commission_pct <=> NULL;（可读性较差）

以上两种方式等价，返回查询结果一致！！！

#查找奖金率不为null的员工名、月薪、奖金率以及年薪

SELECT

       last_name,

       salary,

       commission_pct,

       salary * (1 + IFNULL(commission_pct, 0)) * 12 AS 年薪

FROM

       employees

WHERE

       commission_pct IS NOT NULL;

注意，有个IFNULL()函数，原型为IFNULL(expression, alt_value)，如果第一个参数的值expression为null，则替换它并返回第二个参数alt_value。

二、 DOL排序查询

特点：

ASC代表升序，DESC代表降序（若不写，则默认为升序）

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

order by 字句一般放在查询语句的最后面，LIMIT子句除外

语法：（执行顺序已标出）

SELECT

              查询列表        　　　　　3

FROM

              表名         　　　　　　  1

[WHERE

              筛选条件]   　　　　　　2

ORDER BY

              排序列表 ASC|DESC;     4

案例一: #查询员工信息，要求工资从高到低排序

SELECT * FROM employees ORDER BY salary DESC;

 

案例二：#查询部门编号>=90的员工信息，按入职时间的先后顺序排序

SELECT

       *

FROM

       employees

WHERE

       department_id >= 90

ORDER BY

       hiredate DESC;

 

案例三：#按年薪的高低显示员工的信息和年薪【按表达式排序】

SELECT

       *,salary * 12 * (1 + IFNULL(commission_pct, 0)) AS 年薪

FROM

       employees

ORDER BY

              salary * 12 * (1 + IFNULL(commission_pct, 0))  DESC;

 

案例四：#按年薪的高低显示员工的信息和年薪【按别名排序】

SELECT

       *, salary * 12 * (1 + IFNULL(commission_pct, 0)) AS 年薪

FROM

       employees

ORDER BY

              年薪 DESC;

 

案例五：#按姓名的长度显示员工的姓名和工资【按函数排序】

SELECT

       last_name,

       salary,

       LENGTH(last_name) 姓名长度

FROM

       employees

ORDER BY

              LENGTH(last_name) ASC;  （LENGTH（str）函数->返回str的字节长度）

 

案例六：#查询员工信息，要求先按工资排序，再按员工编号排序【按多个字段排序】

SELECT

       *

FROM

       employees

ORDER BY

       salary DESC, employee_id DESC;

 

三、 常见函数介绍

调用方法：SELECT 函数名（实参列表）[from 表名];

分类：

单行函数

如：concat、length、ifnull、isnull等

分组函数

功能：做统计使用，又称为统计函数、聚合函数、组函数

       单行函数分类：

1．  字符函数

# length函数->返回当前参数的字节数(若字符集使用的是gbk则一个汉字占2个字节，若是utf8则为3个字节)

SELECT LENGTH(‘jane’); ->4

SELECT LENGTH(last_name) AS 姓名长度 FROM employees;

 

#concat函数->拼接字符

SELECT  CONCAT(last_name,’_’,first_name)  AS 姓名 FROM employees;

 

#upper、lower函数（类似于python中的str.lower()与str.upper()函数,UPPER(str)->将str转换为大写字母返回，LOWER(str)-> 将str转换为小写字母返回）

SELECT UPPER(last_name) FROM employees;

SELECT CONCAT(UPPER(last_name),’_’,LOWER(first_name)) AS 姓名 FROM employees;

 

#substr,substring(截断字符串)

截取从指定索引处后面的所有字符

SELECT SUBSTR("abcefghijk",2) AS output;

截取从指定索引处指定”字符“长度的字符

SELECT SUBSTR("abcdefghijk",2,3) AS output;

SELECT SUBSTR("呀呀呀",2,1) AS output;

 

#instr(返回子串第一次出现的索引，如果找不到则返回0)

SELECT INSTR(‘我去上学啦’,’上学啦’);->3

 

#trim(去”前后”的制定字符，若不指定则默认为去掉空格。注意，字符串中间的内容不会去除)

SELECT TRIM(‘      lalalla      ’)  AS output;  ->’lalalla’

SELECT TRIM( ‘a’ FROM ‘aaaaaaaabbbbbabbbaaabbbbaaaaa’);-> bbbbbabbbaaabbbb

 

#lpad(在左边用指定字符进行填充并且返回对应长度的字符结果)

SELECT LPAD(‘aaabbb’,10,’c’);->’ccccaaabbb’

SELECT LPAD(‘aaabbb’,4,’c’);->’aaab’

 

#rpad(在右边用指定字符进行填充并且返回对应长度的字符结果)

SELECT RPAD(‘aaabbb’,10,’c’);->’aaabbbcccc’

SELECT RPAD(‘aaabbb’,4,’c’);->’aaab’

 

#replace(用指定字符值替换指定字符值)

REPLACE(str,from_str,to_str)（str->作用的字符串，from_str->要被替换的字符串，to_str->用来替换的字符串）

SELECT REPLACE(’哈哈哈，我不知道’,’我不知道’,’我知道’)；->’哈哈哈，我知道’

 

2．  数字函数

#round(对传入的参数四舍五入并返回)

SELECT ROUND(8.9);->9

SELECT ROUND(-8.9);->-9

SELECT ROUND(1.567,2);->1.57(2代表保留小数点后2位)

 

#ceil(向上取整，返回>=该参数的最小整数)

SELECT CEIL(1.002);->2

SELECT CEIL(1.00);->1

SELECT CEIL(-1.002);->-1

 

#floor(向下取整，返回<=该参数的最小整数)

SELECT CEIL(1.002);->1

SELECT CEIL(1.00);->1

SELECT CEIL(-1.002);->-2

 

#truncate 截断

TRUNCATE(X,D)->(X->要被处理的参数，D->截断后的小树点位数)

SELECT TRUNCATE(1.69999,1);->1.6

 

#mod（取余）

MOD(a,b): a-a/b*b(返回的取余结果的正负值取决于参数a)

SELECT MOD(10,3);->1

SELECT MOD(10,-3);->1

SELECT MOD(-10,-3);->-1

SELECT MOD(-10,3);->-1

 

3．  日期函数

#now (返回当前系统日期+时间)

SELECT NOW();->2020-05-05 20:20:15

 

#curdate(返回当前系统日期,不包含时间)

SELECT CURDATE();->2020-05-05

 

#curtime(返回当前系统时间,不包含日期)

SELECT CURTIME();->20:21:53

 

#year、month、day、hour、minute、second(用于获取指定的部分，年、月、日、时、分、秒)

SELECT YEAR(NOW());->2020

SELECT MONTH(NOW());->5

SELECT DAY(NOW());->5

SELECT HOUR(NOW());->20

SELECT MINUTE(NOW());->25

SELECT SECOND(NOW());->4

 

#str_to_date（将日期格式的字符转换成指定格式的日期）

相对应的格式符如下:

 

 

SELECT STR_TO_DATE(‘9-13 1999’,’%c-%d %Y’);-> 1999-09-13

#date_format（将日期转换成字符）

SELECT DATE_FORMAT(‘2018/6/6’,’%Y年%m月%d日’);-> 2018年06月06日

 

4．  其他函数

#version(返回当前Mysql的版本)

SELECT VERSION();->5.7.25-log

 

#database（返回当前打开的数据库）

SELECT DATABASE();->myemployees

 

#user（返回当前的用户）

SELECT USER();->root@localhost

 

5．  流程控制函数

#if函数

IF(expr1,expr2,expr3)->(expr1:条件或逻辑表达式，若真则返回expr2,假则返回expr3)

SELECT IF(5>2,’大’,’小’);->’大’

 

#case函数

使用一：（switch case的效果）

case 要判断的字段或表达式

when 常量1 then 要显示的值1或语句1;

when 常量2 then 要显示的值2或语句2;

…

else常量n then 要显示的值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;

 

 

  使用二：（类似于多重if）

case

when 条件1 then 要显示的值1语句1;

when 条件2 then 要显示的值2或语句2;

…

else条件n then 要显示的值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;