MySQL_Ch_2
1.特殊比较字符
-
特殊的比较运算符
– 1)between...and...
– 格式: 列名between
下限and
上限– 包含区间是含两个端点 等价大于等于 并且 小于等于
– 查询
emp
表中,20号部门月薪在1000-2000之间的员工信息select * from emp where deptno = 20 and sal >= 1000 and sal <= 2000;
– 不要按照数学公式方式写区间
0 <= sal <= 2000
;select * from emp where deptno = 20 and sal between 1000 and 2000;
– 下限和上限可以使用数值、文字(字符串)、日期
– 通常使用最多的是数值(表示数值范围)、日期(表示日期范围)
– 文字(使用很少,例如:英文名字可以按照字母顺序表示)
– 查询
emp
表中,在1982年入职的员工信息select * from emp where hiredate between '1982-1-1' and '1982-12-31'
– 英文字母顺序
select * from emp where ename between 'A' and 'B';-- 后者端点不包含
– 上限和下限可以反着写,但是你的语句虽然不报错,但是逻辑上是不行的
select * from emp where deptno = 20 and sal between 2000 and 1000;
-
in
– 格式:列名
in(值1,值2,...)
– 等价于多个
or
的关系组合– 查询所有30号部门,其上司的员工编号是7902、7698、7788的员工信息
select * from emp where (mgr = 7902 or mgr = 7698 or mgr = 7788) and deptno = 30;
select * from emp where mgr in(7902,7698,7788)and deptno = 30;
– 针对单个值,也可以使用
in
,它的执行效率要低,实际中有时需要写in
–
=
等号是无法与null
进行匹配 -
like
(重要)– 格式: 列名
like
包含统配字符的字符串– 模糊查询
统配字符 (
%
_
)–
%
:0-n个任意字符–
_
:1个任意的字符– 查询所有名字首字母是B的员工信息
select * from emp where ename like 'B%';
– 查询倒数第二个字符是T的员工信息
select * from emp where ename like '%T_';
关键字:
escape 转义
将通配字符转义为普通的字符含义ESCAPE '/'
表示'/'
为换码字符,在查询时使用可以方便查询到原本就含有'_'
、'%'
的值。– 如果要查询到名字在四个字符以上、倒数第三个字符为d、倒数第二个字符为_的教师的全部信息:
select * from prof where Pname like '%d/__' escape '/';
– 查询名字为
HaHa%Ha
的员工信息select * from emp where ename like '%/%%' escape '/';
–
escape
后面需要指定一个符号–
like
一般针对文字的模糊查询使用较多 -
is null
– 用于筛选空值的
– 查询没有奖金的员工信息
错误写法:select * from emp where comm = 0 or comm = null;-- 错误写法,null不能写成=等号
select * from emp where comm = 0 or comm is null;
– 查询公司的Boss是谁(查询其编号为空值的)
select * from emp where mgr is null;
-
not
– 逻辑非关系
– 可以与上述4个特殊比较运算符一起使用
–
not between ... and...
–
not in(...)
–
not like ...
–
is not null
(英文语法设置) -
ALL
(min, max)
小于
ALL
==> 小于min大于
ALL
==> 大于max -
ANY
(min, max)
小于
ANY
==> 小于max
大于
ANY
==> 大于ANY等于
ANY
==> 大于等于min && 小于等于max 类似于in
2.排序
-
排序
–
order by
排序–
group by
分组– 升序
asc
(默认) 和 降序desc
– 数值、日期、文字(字母日期)
– 查询所有10号部门的员工信息,按照月薪升序排序
select * from emp where deptno = 10 order by sal asc;-- 实际写法
– 按照多列排序
– 查询员工姓名,年收入,特定是20号部门,按照年收入的升序排序,按照入职日期的降序排序
– 第二次排序是要建立在第一次排序的基础上
select ename,(sal + ifnull(comm,0))*12 as 年收入 from emp where deptno = 20 order by 年收入 asc,hiredate desc; -- 使用别名可以进行排序
– 查询年收入高于15000的所有员工信息
# 错误语法,这里别名不能使用 select ename,(sal + ifnull(comm,0))*12 as 年收入 from emp where 年收入 > 15000; -- Unknown column '年收入' in 'where clause'
– SQL语句执行顺序
– 书写顺序:
select ... from... where ... order by
– 执行顺序:
from ... where ... select ...order by
– 因此别名在这里
where
不能使用进行判断select ename,(sal + ifnull(comm,0))*12 as 年收入 from emp where (sal + ifnull(comm,0))*12 > 15000;
3.练习
-
练习
-
查询员工信息,按照部门编号升序排列,按照月薪降序排列
select * from emp order by empno asc ,sal desc;
-
查询在1982期间入职的,其上司的员工编号是7782,7788的员工信息
select * from emp where (hiredate between '1982-1-1'and '1982-12-31') and mgr in(7782,7788);
-
查询名字中包含至少一个o,至少一个T的员工信息
select * from emp where ename like '%o%T%'or ename like'%T%o%';
-
查询员工姓名,月薪,奖金数,按照年收入升序排列
select ename as 姓名,sal as 月薪,comm as 奖金数 from emp order by (sal + ifnull(comm,0))*12;
-
查询在10号部门工作,月薪在2000-3000之间的员工信息
select * from emp where deptno = 10 and sal between 2000 and 3000;
-
4.MySQL的函数
– 函数:官方软件中已经给我们封装了一些固定的功能
– 我们只需要了解对应的函数名称,知道如何调用即可
– 分为两大类
– 1) 单行函数
– 2) 多行函数(分组函数、聚合函数)
1.单行函数
– 只对一行数据进行变换,每一行都会返回一个结果
-
字符函数
-
upper()
小写转大写,针对英文字母 -
lower()
大写转小写,针对英文字母 – 中文不会转换查询一个结果,将一个字符串从小写转为大写;使用
upper()
函数select upper('hGhE') from dual;
–
dual
在MySQL
中属于关键字;在oracle
中属于虚表;# 在oracle中可以使用: select * from dual;
-
字符截取函数
-
substring(str,n,len)
获取一个子字符串str
:被截取的字符串n
:开始截取的位置,mysql中索引位是以1开始len
: 截取字符个数select substring('0123456',3,3) from dual;
# 查询emp表中,员工姓名前三个字母 select substring(ename,1,3) from emp;
-
-
字符查找函数
–
instr(str1,str2)
– 在
str1
中寻找(不是遍历,及只找第一次出现的位置)str2
字符串,返回一个位置,str2
在str1
中的位置– 如果没有查到,返回0
select instr('I Like GiRL','K') from dual; #5 空格也是一个字符;不区分大小写
-
字符串拼接函数
–
concat(str1,str2)
– 多个字符串拼接
select concat('haha','hehe','xixi') from dual;
-
字符串替换函数
–
replace(str1,str2,str3)
– 在
str1
字符串中,使用str3
来替换全部的str2
select replace('ABCDEF','CD','HAHA') from dual;
-
字符串的长度函数
–
length(str)
– 返回的是一个字符串的长度
select length('abcde'),length('哈哈') from dual; #5 6 mysql中一个汉字占3个Unicode编码长度
-
字符串补齐函数
–
lpad(str1,n,str2)
左补齐函数– 将字符串
str1
使用str2
在其左侧补充n
个长度–
rpad(str1,n,str2)
右补齐函数– 将字符串
str1
使用str2
在其右侧补充n
个长度select lpad('abc',6,'*'),lpad('abc',2,'*')from dual; # ***abc ab
-
去除函数
trim()
– 去除前后字符函数,函数能去掉字符串前后的
空格
,或者去掉字符串首尾特定的字符
.select trim(' hell oworld') from dual; select trim('h' from 'helloworldhh') from dual; # elloworld select length(' hell oworld'),length(trim(' hell oworld'))from dual;
-
-
数值函数
-
round(n1,n2)
四舍五入– 将
n1
值四舍五入,精确到小数点后n2
位–
n2
表示负数时,将小数点看成0,小数点左边就是1,2…;小数点左边依次为-1,-2,-3…select round(3.1415,3),round(3.14,0),round(16.14,-1),round(16.14,-2)from dual; # 3.142 3 20 0
-
trunca(n1,n2)
舍去函数– 将
n1
数舍去,精确到小数点后n2
位select truncate(3.1415,3),truncate(3.14,0),truncate(6.14,-1)from dual;
-
mod(n1,n2)
取余函数– 求
n1
除以n2
后得到的余数select mod(5,3),mod(5,-3),mod(-5,3),mod(-5,-3) from dual; # 2 2 -2 -2
– 判断依据:余数是正还是负,取决于被除数
-
-
日期函数
-
now()
– 获取数据库所在服务器的当前系统时间
select now() from dual; #MySQL特有的 select sysdate() from dual; -- system date 系统时间 通用写法 select curdate()from dual; #2020-11-11 # cur_开头的函数都与时间有关
-
时间差值函数
–
timediff(date1,date2)
:返回的是两个日期的差值,是一个数值–
datediff(date1,date2)
:返回两个日期相差的天数– 假设
emp
表中所有的员工至今都未离职,计算他们的司龄select datediff(now(),hiredate)/365 from emp; #只能算大概 select timediff(now(),hiredate) from emp; #格式对应才能准确,小时分钟秒要有都有 select timediff(curdate(),hiredate) from emp;#大概 select year(curdate())-year(hiredate) from emp; -- MySQL中准确的
-
追加/追减函数
– 追加
date_add(date,interval expr type)
– 追减
date_sub(date,interval expr type)
– 设置公司的试用期是6个月,计算
emp
表中所有的员工的转正日期select date_add(hiredate,interval 6 month) from emp;
– 查询工龄大于或者等于35年的员工信息
select * from emp where (year(curdate())-year(hiredate)) >= 35; select * from emp where date_sub(curdate(),interval 35 year) >= hiredate;
last_day(date)
查询所在月份的最后一天的日期
select ename,hiredate,last_day(hiredate) from emp;
-
round()、trunc()、extract()函数
–
round
– 在MySQL中,
round()
函数是将日期变为字符串select round(now()) from dual;
– 在oracle中,
round()
函数用法:月份中15号含15号之前的舍去,16含16之后的进位– 查询1981年入职的员工姓名,入职日期
select ename,hiredate,round(hiredate,'month') from emp where substr(round(hiredate,'month'),3,2) = 81;
–
trunc
舍去–
extract
抽取日期select ename,hiredate,extract(month from hiredate) from emp;
-
日期的格式化函数
– 日期的格式化:
date_format(date,format)
– 时间的格式化:
time_format(time,format)
–
format
指定的日期格式或者是指定时间格式select date_format(now(),'%Y/%m/%d %H:%i:%s') from dual; select date_format(now(),'%Y年%m月%d日 %H:%i:%s') from dual; select now() from dual; #2020-11-10 16:43:03
-
-
转换函数
– MySQL中转换函数:
日期格式转换、大小写转换
– oracle中转换函数:
to_date、to_char、to_number
-
通用函数
– 控制语句 实际中一般不使用,描述程序的执行流程
– 基本上大多数的控制在Java语言中进行描述
select case when 1 > 2 then 'true' else 'false' end; #false
–
if(expr1,expr2,expr3)
– 如果
expr1
是true,则if语句返回的值是expr2
,否则返回的是expr3
select if(1 > 2, 2, 3); # mysql规则不是很规范 select if(1 > 2, 2, 3) from dual;
多行函数(分组函数)
- 有多条记录生成一组统计出的数据结果
- 数据库标准叫法:行(记录) 列(属性)
-
统计型的函数
-
求和
sum()
函数select sum(sal) from emp;
-
求平均值(使用最多)
avg()
函数select avg(sal) from emp;
-
最大值
max()
函数select max(sal) from emp;
-
最小值
min()
函数select min(sal) from emp;
-
统计个数(计数)(使用最多)
count()
函数查询emp中,有多少个员工
select count(ename) from emp;
查询emp中,有多少个部门
select count(distinct deptno)from emp;
-
分组
group by
依据…分组查询各个部门的工资平均值
select deptno,avg(sal) from emp group by deptno;
select empno,deptno,avg(sal) from emp group by deptno;#错误写法
– 注意:
– 只有使用了分组函数(
sum() avg() max() min() count()
等),才能使用group by
– 只有在
group by
中出现的列,才能写在select
中– 在MySQL中如果
select
中的列,没有出现group by
中,可以执行,但是逻辑是不对的.-
group by
多次分组– 第二次分组是建立在第一次分组的基础上
分组先后是有顺序区别顺序的,结果相同,列的输出顺序不同
– 查询每个部门不同岗位的平均工资
select deptno,job,avg(sal) from emp group by deptno, job; # 每个部门不同岗位 select deptno,job,avg(sal) from emp group by job, deptno; #顺序前后有区别,这个与上面那个结果是相同的
– 查询10号部门和20部门的平均工资
select deptno avg(sal) from emp where deptno in(10,20) group by deptno;
-
-
having
专门用于分组函数的筛选,代替了where
的查询– 只有在
where
中出现分组函数时,才用having
替换.–
group by ... having ...
– 查询平均工资高于2500的部门id和平均工资
select deptno,avg(sal) from emp where avg(sal) > 2500 -- 数据库是无法区分到底以什么分组 group by detpno #错误写法
select deptno, avg(sal) from emp group by deptno having avg(sal) > 2500;
-
SQL语句书写顺序和执行顺序
-- 只有在`where` 中出现分组函数时,才用`having`替换.
-- 书写顺序: `select ... from ... where ... group by ... having ... order by...`
-- 执行顺序: `from ... where ... group by ...having ... select ... order by ...`
练习(以employees表)
-
查询Nancy Greenberg 的员工信息
select * from employees where first_name = 'Nancy' and last_name = 'Greenberg';
-
查询职位是AD_VP,并且在1990年前入职的员工信息
select * from employees where job_id = 'AD_VP' and hire_date < '1990-1-1';
select * from employees where job_id = 'AD_VP' and substr(round(hire_date,'month'),1,4) < 1990;
-
查询月薪在10000-20000之间的且部门编号是90的员工信息
select * from employees where (salary between 10000 and 20000) and department_id = 90;
-
查询司龄高于20年的员工信息,按照月薪升序排列
select * from employees where (year(curdate())-year(hire_date)) > 20 order by salary asc; ## select * from employees where date_sub(curdate(),interval 20 year) > hire_date order by salary asc; # oracle 特有函数:months_between()函数可以计算两个量之间的相差月数
-
查询年收入高于25万的员工信息
select * from employees where ((1 + ifnull(commission_pct,0)) * salary*12) > 250000; select * from employees where ((salary + ifnull(commission_pct,0) * salary)*12) > 250000;
-
查询职位以SA开头的所有员工信息
select * from employees where job_id like('SA%'); select * from employees where substr(job_id,1,2) = 'SA';
-
查询上司是145、146、147、149的所有员工信息
select * from employees where manager_id in(145,146,147,149);
-
查询公司的老板信息
select * from employees where manager_id is null;
-
查询不属于任何部门的员工信息
select * from employees where department_id is null;
- 分组练习题
-
查询每个岗位的平均工资,最高工资和最低工资
select job_id,avg(salary), max(salary),min(salary) from employees group by job_id;
-
查询月薪高于8000的员工,按照岗位查询平均工资
select avg(salary) from employees where salary > 8000 group by job_id;
-
查询各部门,各个经理负责的员工的平均工资
select distinct department_id, manager_id ,avg(salary) from employees group by department_id,manager_id; ## select avg(salary) from employees group by department_id,manager_id;
-
查询平均工资高于8000的岗位id和平均工资
select job_id, avg(salary) from employees group by job_id having avg(salary) > 8000;
-
查询司龄高于20年的员工的平均工资
select avg(salary) from employees where (year(curdate()) - year(hire_date)) > 20;
-
查询1995年之前入职员工的最高工资和最低工资
select max(salary), min(salary) from employees where hire_date < '1995-1-1';
-
查询各个部门的部门id和部门人数
select department_id, count(*) from employees where department_id is not null group by department_id;
-
查询各个部门部门id和月薪的总和
select department_id, sum(salary) from employees where department_id is not null group by department_id;
-
查询在1992-1993年间入职员工平均工资
select avg(salary) from employees where hire_date < '1993-1-1' and hire_date >= '1992-1-1'; ## select avg(salary) from employees where hire_date between '1992-1-1' and '1993-12-31';
-
查询各部门平均工资在8000元以上的部门id及平均工资。
select department_id,avg(salary) from employees group by department_id having avg(salary) > 8000;
-
查询工作编号中不含有“SA_”字符串及平均工资在8000元以上的工作编号及平均工资,并按平均工资 降序排序。
select job_id,avg(salary) from employees where job_id not like 'SA/_%' escape '/' group by job_id having avg(salary) > 8000 order by avg(salary) desc; #### select job_id,avg(salary) from employees where instr(job_id,'SA_') = 0 ---可变的 group by job_id having avg(salary) > 8000 order by avg(salary) desc;
-
查询工作不为AD_PRES,工资的和大于等于25000的工作编号和每种工作工资的和。
select job_id,sum(salary) from employees where job_id not like 'AD/_PRES' escape '/' group by job_id having sum(salary) > 25000; #### select job_id,sum(salary) from employees where job_id <> 'AD_PRES' group by job_id having sum(salary) > 25000;
-
显示经理号码,这个经理所管理员工的最低工资,不包括经理号为空的,不包括最低工资小于3000的,按最低工资由高到低排序。
select manager_id,min(salary) from employees where manager_id is not null group by manager_id having min(salary) >=3000 order by min(salary) desc;
-
显示公司员工总工资及平均工资。
select sum(salary),avg(salary) from employees;
-
查询未分配部门的雇员信息。
select * from employees where department_id is null;
-
查找职位是FI_ACCOUNT或者工资超过16000的职位是AD_VP的员工信息。
select * from employees where job_id = 'FI_ACCOUNT' or (job_id = 'AD_VP'and salary >16000);
-
相同职位且经理相同的员工平均工资。
select distinct job_id,manager_id ,avg(salary) from employees group by job_id, manager_id;