第二天MySQL

2020/5/5

一、 dql条件查询

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

select

　　　　　查询列表                      3

form

　　　　　表名                             1

where

                 筛选条件; 　　　　　   2

分类：

1. 按条件表达式筛选

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

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

select

               *

from

               employees

where

               salary>12000;

2. 按逻辑表达式筛选

逻辑运算符：and  or  not

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

select

       last_name,

       salary,

       employee_id

from

       employees

where

               employee_id >= 90 and employee_id <= 120;

 

3. 模糊查询

关键字：

3.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  '$';  (相当于把‘$‘附上转义的功能)

 

3.2   between and

优点：

1. 能够提高语句的简洁度

2. 包含临界值

3. 两个临界值不要调换顺序

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

     

运行结果：

 

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

 

3.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');

 

上面这两种方式完全等价

 

3.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排序查询

特点：

1. asc代表升序，desc代表降序（若不写，则默认为升序）

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

3. 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 表名];

分类：

1.单行函数

如：concat、length、ifnull、isnull等

2.分组函数

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

       单行函数分类：

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;

 注意！！！索引是从1开始的

#substr,substring(截断字符串)

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

select substr('abcefghijk',2) as output;->'cefghijk'

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

select substr('abcdefghijk',2,3) as output;->'cde'

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;