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
特点：
#查找员工部门编号是'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;
如对本文有疑问， 点击进行留言回复！！
我要评论
网友评论