MySQL学习笔记4
where语句
指定查询条件使用where子句,可以查询符合条件的部分记录。where子句:将过滤条件放在where子句的后面,可以筛选/过滤出我们想要的符合条件的数据
-- where 子句 + 关系运算符
select * from emp where deptno = 10;
select * from emp where deptno > 10;
select * from emp where deptno >= 10;
select * from emp where deptno < 10;
select * from emp where deptno <= 10;
select * from emp where deptno <> 10;
select * from emp where deptno != 10;
select * from emp where job = 'CLERK';
select * from emp where job = 'clerk'; -- 默认情况下不区分大小写
select * from emp where binary job = 'clerk'; -- binary区分大小写
select * from emp where hiredate < '1981-12-25';
-- where 子句 + 逻辑运算符:and
select * from emp where sal > 1500 and sal < 3000; -- (1500,3000)
select * from emp where sal > 1500 && sal < 3000;
select * from emp where sal > 1500 and sal < 3000 order by sal;
select * from emp where sal between 1500 and 3000; -- [1500,3000]
-- where 子句 + 逻辑运算符:or
select * from emp where deptno = 10 or deptno = 20;
select * from emp where deptno = 10 || deptno = 20;
select * from emp where deptno in (10,20);
select * from emp where job in ('MANAGER','CLERK','ANALYST');
-- where子句 + 模糊查询:
-- 查询名字中带A的员工 -- %代表任意多个字符 0,1,2,.....
select * from emp where ename like '%A%' ;
-- -任意一个字符
select * from emp where ename like '__A%' ;
-- 关于null的判断:
select * from emp where comm is null;
select * from emp where comm is not null;
-- 小括号的使用 :因为不同的运算符的优先级别不同,加括号为了可读性
select * from emp where job = 'SALESMAN' or job = 'CLERK' and sal >=1500; -- 先and再or
--and优先级 > or的优先级
select * from emp where job = 'SALESMAN' or (job = 'CLERK' and sal >=1500);
select * from emp where (job = 'SALESMAN' or job = 'CLERK') and sal >=1500;
使用函数
MySQL中提供了大量函数来简化用户对数据库的操作,比如字符串的处理、日期的运算、数值的运算等等。使用函数可以大大提高SELECT语句操作数据库的能力,同时也给数据的转换和处理提供了方便。 (在sql中使用函数)
函数只是对查询结果中的数据进行处理,不会改变数据库中数据表的值。MySQL中的函数主要分为单行函数和多行函数两大类,下面我们将详细讲解这两大类函数。
单行函数
单行函数是指对每一条记录输入值进行计算,并得到相应的计算结果,然后返回给用户,也就是说,每条记录作为一个输入参数,经过函数计算得到每条记录的计算结果。
常用的单行函数主要包括字符串函数、数值函数、日期与时间函数、流程函数以及其他函数。
1.字符串函数 (String StringBuilder)
函数 | 描述 |
---|---|
CONCAT(str1, str2, ···, strn) | 将str1、str2···strn拼接成一个新的字符串 |
INSERT(str, index, n, newstr) | 将字符串str从第index位置开始的n个字符替换成字符串newstr |
LENGTH(str) | 获取字符串str的长度 |
LOWER(str) | 将字符串str中的每个字符转换为小写 |
UPPER(str) | 将字符串str中的每个字符转换为大写 |
LEFT(str, n) | 获取字符串str最左边的n个字符 |
RIGHT(str, n) | 获取字符串str最右边的n个字符 |
LPAD(str, n, pad) | 使用字符串pad在str的最左边进行填充,直到长度为n个字符为止 |
RPAD(str, n, pad) | 使用字符串pad在str的最右边进行填充,直到长度为n个字符为止 |
LTRIM(str) | 去除字符串str左侧的空格 |
RTRIM(str) | 去除字符串str右侧的空格 |
TRIM(str) | 去除字符串str左右两侧的空格 |
REPLACE(str,oldstr,newstr) | 用字符串newstr替换字符串str中所有的子字符串oldstr |
REVERSE(str) | 将字符串str中的字符逆序 |
STRCMP(str1, str2) | 比较字符串str1和str2的大小 |
SUBSTRING(str,index,n) | 获取从字符串str的index位置开始的n个字符 |
2、数值函数
函数 | 描述 |
---|---|
ABS(num) | 返回num的绝对值 |
CEIL(num) | 返回大于num的最小整数(向上取整) |
FLOOR(num) | 返回小于num的最大整数(向下取整) |
MOD(num1, num2) | 返回num1/num2的余数(取模) |
PI() | 返回圆周率的值 |
POW(num,n)/POWER(num, n) | 返回num的n次方 |
RAND(num) | 返回0~1之间的随机数 |
ROUND(num, n) | 返回x四舍五入后的值,该值保留到小数点后n位 |
TRUNCATE(num, n) | 返回num被舍去至小数点后n位的值 |
3、日期与时间函数
函数 | 描述 |
---|---|
CURDATE() | 返回当前日期 |
CURTIME() | 返回当前时间 |
NOW() | 返回当前日期和时间 |
SYSDATE() | 返回该函数执行时的日期和时间 |
DAYOFYEAR(date) | 返回日期date为一年中的第几天 |
WEEK(date)/WEEKOFYEAR(date) | 返回日期date为一年中的第几周 |
DATE_FORMAT(date, format) | 返回按字符串format格式化后的日期date |
DATE_ADD(date, INTERVAL expr unit) | ADDDATE(date, INTERVAL expr unit) |
返回date加上一个时间间隔后的新时间值 | DATE_SUB(date, INTERVAL expr unit) |
SUBDATE(date, INTERVAL expr unit) | 返回date减去一个时间间隔后的新时间值 |
DATEDIFF(date1, date2) | 返回起始日期date1与结束日期date2之间的间隔天数 |
4、 流程函数( IF SWITCH)
间隔类型 | 描述 |
---|---|
IF(condition, t, f) | 如果条件condition为真,则返回t,否则返回f |
IFNULL(value1, value2) | 如果value1不为null,则返回value1,否则返回value2 |
NULLIF(value1, value2) | 如果value1等于value2,则返回null,否则返回value1 |
CASE value WHEN [value1] THEN result1 [WHEN [value2] THEN result2 …] [ELSE result] END | 如果value等于value1,则返回result1,···,否则返回result |
CASE WHEN [condition1] THEN result1 [WHEN [condition2] THEN result2 …] [ELSE result] END | 如果条件condition1为真,则返回result1,···,否则返回result |
5、JSON函数
函数 | 描述 |
---|---|
JSON_APPEND() | 在JSON文档中追加数据 |
JSON_INSERT () | 在JSON文档中插入数据 |
JSON_REPLACE () | 替换JSON文档中的数据 |
JSON_REMOVE () | 从JSON文档的指定位置移除数据 |
JSON_CONTAINS() | 判断JSON文档中是否包含某个数据 |
JSON_SEARCH() | 查找JSON文档中给定字符串的路径 |
-- 单行函数包含:
-- 1.字符串函数
select ename,length(ename),substring(ename,2,3) from emp;
-- substring字符串截取,2:从字符下标为2开始,3:截取长度3 (下标从1开始)
-- 2.数值函数
select abs(-5),ceil(5.3),floor(5.9),round(3.14) from dual; -- dual实际就是一个伪表
select abs(-5) 绝对值,ceil(5.3) 向上取整,floor(5.9) 向下取整,round(3.14) 四舍五入; -- 如果没有where条件的话,from dual可以省略不写
select ceil(sal) from emp;
select 10/3,10%3,mod(10,3) ;
-- 3.日期与时间函数
select * from emp;
select curdate(),curtime() ; -- curdate()年月日 curtime()时分秒
select now(),sysdate(),sleep(3),now(),sysdate() from dual; -- now(),sysdate() 年月日时分秒
insert into emp values (9999,'lili','SALASMAN',7698,now(),1000,null,30);
-- now()可以表示年月日时分秒,但是插入数据的时候还是要参照表的结构的
desc emp;
6、其他函数
函数 | 描述 |
---|---|
DATABASE() | 返回当前数据库名 |
VERSION() | 返回当前MySQL的版本号 |
USER() | 返回当前登录的用户名 |
INET_ATON(IP) | 返回IP地址的数字表示 |
INET_NTOA | 返回数字代表的IP地址 |
PASSWORD(str) | 实现对字符串str的加密操作 |
FORMAT(num, n) | 实现对数字num的格式化操作,保留n位小数 |
CONVERT(data, type) | 实现将数据data转换成type类型的操作 |
-- 4.流程函数
-- if相关
select empno,ename,sal,if(sal>=2500,'高薪','底薪') as '薪资等级' from emp; -- if-else 双分支结构
select empno,ename,sal,comm,sal+ifnull(comm,0) from emp; -- 如果comm是null,那么取值为0 -- 单分支
select nullif(1,1),nullif(1,2) from dual; -- 如果value1等于value2,则返回null,否则返回value1
-- case相关:
-- case等值判断
select empno,ename,job,
case job
when 'CLERK' then '店员'
when 'SALESMAN' then '销售'
when 'MANAGER' then '经理'
else '其他'
end '岗位',
sal from emp;
-- case区间判断:
select empno,ename,sal,
case
when sal<=1000 then 'A'
when sal<=2000 then 'B'
when sal<=3000 then 'C'
else 'D'
end '工资等级',
deptno from emp;
from emp;
-- 5.JSON函数
-- 6.其他函数
select database(),user(),version() from dual;
多行函数
多行函数是指对一组数据进行运算,针对这一组数据(多行记录)只返回一个结果,也称为分组函数。
除了多行函数(max,min,count,sum,avg),都是单行函数。
多行函数包括:
函数 | 描述 |
---|---|
COUNT() | 统计表中记录的数目 |
SUM() | 计算指定字段值的总和 |
AVG() | 计算指定字段值的平均值 |
MAX() | 统计指定字段值的最大值 |
MIN() | 统计指定字段值的最小值 |
-- 多行函数:
select max(sal),min(sal),count(sal),sum(sal),sum(sal)/count(sal),avg(sal) from emp;
select * from emp;
-- 多行函数自动忽略null值
select max(comm),min(comm),count(comm),sum(comm),sum(comm)/count(comm),avg(comm) from emp;
-- max(),min(),count()针对所有类型 sum(),avg() 只针对数值型类型有效
select max(ename),min(ename),count(ename),sum(ename),avg(ename) from emp;
-- count --计数
-- 统计表的记录数:方式1:
select * from emp;
select count(ename) from emp;
select count(*) from emp;
-- 统计表的记录数:方式2
select 1 from dual;
select 1 from emp;
select count(1) from emp;
group by 分组
select * from emp;
-- 统计各个部门的平均工资
select deptno,avg(sal) from emp; -- 字段和多行函数不可以同时使用(Oracle会报错)
select deptno,avg(sal) from emp group by deptno; -- 字段和多行函数不可以同时使用,除非这个字段属于分组
select deptno,avg(sal) from emp group by deptno order by deptno desc;
-- 统计各个岗位的平均工资
select job,avg(sal) from emp group by job;
select job,lower(job),avg(sal) from emp group by job;
having分组后筛选
-- 统计各个部门的平均工资 ,只显示平均工资2000以上的 - 分组以后进行二次筛选 having
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
select deptno,avg(sal) 平均工资 from emp group by deptno having 平均工资 > 2000;
select deptno,avg(sal) 平均工资 from emp group by deptno having 平均工资 > 2000 order by deptno desc;
-- 统计各个岗位的平均工资,除了MANAGER
-- 方法1:
select job,avg(sal) from emp where job != 'MANAGER' group by job;
-- 方法2:
select job,avg(sal) from emp group by job having job != 'MANAGER' ;
-- where在分组前进行过滤的,having在分组后进行后滤。