基础查询
select [字段] from 表名
1,select * from emp;
# 查询所有数据 在数据量很庞大的情况下, 效率低, 不推荐使用
2,select empno,ename.sal from emp;
# 指定查询字段 (使用逗号隔开)
as 子句
1,select empno as 编号,ename as 姓名 ,sal as 工资 from emp;
# 更改表头
2,select e.empno,e.ename,e.sal from emp as e;
# 给表起别名
3,select empno 编号,ename 姓名 ,sal 工资 from emp;
as 可以省略不写
distinct 关键字
1,select job 工作 from emp;
# 查看 emp 表中的工作种类
2,select distinct job 工作 from emp;
# 去除工作中重复性的数据,每一个字段都相同,才可以去除重复
在返回结果中使用表达式(函数)
1,select ename 姓名,sal 工资 from emp;
2,select ename 姓名,sal*1.1 工资 from emp;
# 所有员工的工资涨薪 10%
条件查询
语法 select [字段] from 表名 [where 条件]
比较运算符
> < >= <= = != 判断是否为空
is null / is not null between and 在什么与什么之间 相当于 >= && <= in 在集合范围内
like 像 模糊查询
基本比较
1,select * from emp where sal > 2000
#查询工资大于 2000 的所有员工
2,select * from emp where job = 'clerk'
#查询工作为办事员的 所有员工
3,select ename "姓名" , hiredate "入职时间" from emp where hiredate > '1981-05-31'
#查询入职时间 在1981年5月份之后入职的所有员工的姓名以及 入职时间
is null
1,select empno "编号" , job "职位" from emp where mgr is null;
#查询 没有上级的 员工的编号和职位
2,select * from emp where comm is not null;
#查询 奖金不为null 的 所有员工
between and
1,select ename,sal from emp where sal between 1000 and 2000;
#查询工资在1000 到2000 之间的员工的姓名和工资,
- 左右边界都包含在内
- 小值在前, 大值在后
in
1,select * from emp where job = 'CLERK' or job = 'SALESMAN';
2,select * from emp where job in ('CLERK','SALESMAN');
#查询工作为 办事员或者是 销售员的员工
3,select * from emp where deptno in (20,30);
#查询 20号和30号部门中的所有员工
like 模糊查询(占位符)
(_) 一个任意字符
% 任意字符 0个或多个
1,select * from emp where ename like '%A%';
#查询姓名中带有 A 字符的 所有员工
2,select * from emp where ename like 'A%';
#查询姓名中首字母为 A 字符的所有员工
3,select * from emp where ename like '_A%';
#查询姓名中 第二个字母为A的所有员工
注意:
# 如果说 select * from emp where ename like 'A';
相当于
select * from emp where ename = 'A';
逻辑运算符
and &&
or ||
not !
1,select * from emp WHERE hiredate < '1981-05-01' and job = 'MANAGER'
#查询入职时间早于1981 年5月, 并且职位为经理的员工
排序查询
语法: order by 需要排序的字段 指定规则(默认升序), 第二个需要排序的字段
- 可以根据字段名 排序
- 可以根据 字段的别名排序
- 根据字段的位置编号排序
1,select * from emp order by sal ;
2,select * from emp order by sal desc;
#查询所有员工信息, 按照工资升序排列
3,select * from emp order by sal , hiredate desc;
#查询所有员工信息, 按照工资升序排列,如果工资相同, 再按照入职时间降序排序
4,select * from emp order by 1 desc;
5,select empno 编号 , enamel from emp order by 编号 desc;
#按照员工编号降序 查询所有员工信息
分页查询(限定查询)
语法 语句的最后使用 分页
limit [m ,] n 从第m条记录开始 查询, 一共返回 n条数据
如果m 项不指定, 默认为 0
1,select * from emp order by sal desc limit 5;
#查询工资最高的5 名员工
2,select * from emp order by sal desc limit 6,4;
#查询工资从高到低排名第7 - 10 位的的4 名员工
3,select * from emp where job = 'SALESMAN' and (sal + comm)> 1500 order by sal desc limit 3
#查询职位为 销售员的 并且总工资高于 1500 的前三名员工, 按照工资降序排列
Mysql函数
统计函数/聚合函数
函数名:count() sum() avg() max() min()
说明:统计, 计数 求和 平均数 最大值 最小值
统计emp 表总数据条数
忽略 值为null 的字段
一般情况下, count() 中 统计那些非空字段 (主键)
1,select count(*) from emp; # 返回值为一个数字
select count(1) from emp;
select count(empno) from emp;
2,select sum(sal) from emp;
#统计该公司所有员工的总工资
3,select sum(sal)/count(empno) from emp;
select avg(sal) from emp;
#统计该公司的平均工资
4,select max(sal), min(sal) from emp;
#统计最高工资和 最低工资
数学函数
abs(n)
返回n的绝对值
mod(n,m)
取模运算,返回n被m除的余数(同%操作符)
floor(n)
返回不大于n的最大整数值
ceiling(n)
返回不小于n的最小整数值
round(n,d)
返回n的四舍五入值,保留d位小数(d的默认值为0)
truncate(n,d)
保留数字n的d位小数并返回
pow(x,y)
power(x,y)
返回值x的y次幂
sqrt(n)
返回非负数n的平方根
pi()
返回圆周率
rand()
rand(n)
返回在范围0到1.0内的随机浮点值(可以使用数字n作为初始值)
计算 2的31次幂
1,select pow(2,31); # 不依赖于任何表 , 不需要写from
# 但是oracle 数据库需要添加虚表
2,select rand();
# 获取随机数 0-1
3,select round(rand()*5+5);
# 获取 5-10 之间的随机数
字符串函数
length(str)
ascii(str)
返回字符串str的第一个字符的ascii值(str是空串时返回0)
concat(str1,str2,...)
把参数连成一个长字符串并返回(任何参数是null时返回null)
substring(str,pos,len)
substring(str from pos for len)
replace(str,from_str,to_str)
用字符串to_str替换字符串str中的子串from_str并返回
trim([[both | leading | trailing] [remstr] from] str)
返回前缀或后缀remstr被删除了的字符串str(位置参数默认both,remstr默认值为空格)
1,select * from emp where length(ename) = 5
#查询员工中 名字字符长度为 5 的所有员工
2,select ename,sal from emp;
3,select concat(ename,'的工资是: ',sal) from emp;
#查询员工的姓名和工资, 以如下格式显示 (XXX的工资是: 0.00)
4,select replace(ename,'A','') from emp;
#把所有员工姓名中 的A字符 全部隐藏
日期函数
now()
sysdate()
current_timestamp()
以'yyyy-mm-dd hh:mm:ss'或yyyymmddhhmmss格式返回当前日期
时间(根据返回值所处上下文是字符串或数字)
curtime()
current_time()
以'hh:mm:ss'或hhmmss格式返回当前时间值(根据返回值所处上
下文是字符串或数字)
curdate()
current_date()
以'yyyy-mm-dd'或yyyymmdd格式返回当前日期值(根据返回值所
处上下文是字符串或数字)
month(date)
返回date中的月份数值
datediff(now(),hiredate) 判断两个日期之间相隔天数 (大日期在前, 小日期在后)
timestampdiff(日月年等参数, 两个日期参数小日期在前, 大日期在后)
判断两个日期之间相隔的 年月日
adddate(时间参数 interval 3 month) 日期累加
last_day() 当前月的最后一天
1,select now();
2 select sysdate() ;
#获取系统当前时间
1,insert into emp (empno,ename,hiredate,sal) values (9527,'孙继斌',now(),6000);
2, delete from emp where empno = 9527;
#插入数据
3,select * from emp where month(hiredate) = 5;
#统计五月份入职的员工
4,select datediff(now(),hiredate) from emp;
#查询所有员工入职的天数
5,select * from emp where datediff(now(),hiredate) > 13000
#查询入职的天数大于 13000 的所有员工
查看100 天之后的日期
select adddate(now(), interval 100 day);
# 加 100 天
select adddate(now(), interval 3 month);
# 加 三个 月
select adddate(now(),100);
# 加 100 天 简写
转换函数
字符串转日期 str_to_date(字符串, 日期格式)
日期转字符串 date_format(date,format)
根据format字符串格式化date值
查询当前系统时间
1,select now();
# 格式化 日期
2,select date_format(now(),'%Y年%m月%d号 %H:%i:%s');
把字符串转换为日期
3,select str_to_date('2008年08月08号','%Y年%m月%d号')
隐式转换
1,SELECT '5'+3 ; # 结果为 8
2,select * from emp where hiredate > '1981-05-01'
# 如果 日期的书写格式 为 '2019-07-25' 能够自动转换为 日期格式
系统函数
ifnull(字段名,如果为null 需要替换的值)
UUID() 随机字符串数 绝对不可能重复的随机字符串
MD5() 加密函数
select uuid();给淘宝网订单表添加 随机的字符串订单号
复杂查询
子查询
在查询语句中的WHERE条件子句中,又嵌套了另外一个查询语句
一个语句 查询条件 依赖于另外一条语句的 结果
单行单列
#职位和 SMITH 先生 职位相同的所有员工
分步查询
1- 查询 SMITH 职位
select job from emp where ename = 'SMITH';
2- 查询 职位为 该职位的所有员工
select * from emp
where job = (select job from emp where ename = 'SMITH')
and ename != 'SMITH'
#查询入职时间 早于 JAMES 的 所有员工
3,select * from emp
where hiredate < (select hiredate from emp where ename = 'JAMES')
#查询 和SMITH 部门相同的员工
4,select * from emp
where deptno = (select deptno from emp where ename = 'SMITH')
单列多行
#查询和SMITH 工作相同 部门也相同的员工
1- 查询SMITH 工作, 部门
select job , deptno from emp where ename = 'SMITH'
2-
select * from emp
where (job,deptno) = (select job , deptno from emp where ename = 'SMITH')
多行多列
#查询人数最多的部门, 有多少人
1,分组查询出每个部门的人数
select deptno,count(empno)
from emp
group by deptno
2,把以上表的查询结果 当成一张新表来查询
select max(ct)
from (SELECT deptno,COUNT(empno) ct FROM emp GROUP BY deptno ) newtable
作为一个字段来存在
#(基于多表查询) 列出 员工姓名以及其 上司的姓名
1,select e.ename 员工,(select m.ename from emp m where e.mgr = m.empno) 上司 from emp e
分组查询
按照某些字段分组, 按照各个组, 分别统计查询
#查询10 号部门的员工人数
1,select count(empno) from emp where deptno = 10
#查询各个部门的员工人数
#先按照部门分组 每个部门多少人
select deptno,count(empno) from emp group by deptno
注意:
使用group by 分组, 不允许在select 中 随意添加字段
- 在group by 中出现的字段可以添加
- 统计函数 可以添加
#统计各种工作种类的员工人数
1,
select count(empno) from emp group by job
#统计每个部门的平均工资, 最高/最低工资
2,
select deptno, avg(sal),max(sal),min(sal) from emp group by deptno
select deptno
from emp
where sal > 1000
group by deptno
order by hiredate
limit 5
查询步骤
1- from
2- where 筛选
3- group by 按照某字段分组
4- select 列出要查看的字段
5- having 在分组之后执行的 筛选条件
6- order by
7- limit
验证语句的执行顺序
#按照总工资排序所有员工
1,select ename 员工姓名,sal+comm 总工资 from emp order by 总工资
# null 不能和 其他值做运算
having
#查询平均工资 大于2000 的部门编号
# 1- 先获取所有部门的平均工资
# 2- 查询平均工资 > 2000
1,select deptno , avg(sal) from emp
group by deptno having avg(sal) > 2000
having 和 where的对比
- 两者都是筛选条件
- where 在分组之前执行 having 在分组之后执行
- where 中不能出现分组函数(统计函数) , having 可以使用该函数
#查询 平均工资 最高的部门编号
# 1- 先获取所有部门的平均工资
select deptno , avg(sal) from emp group by deptno
# 2- 查询出最高的平均工资
select max(asl) from (select deptno , avg(sal) asl from emp group by deptno) nt
# 3- 该平均工资的部门
select deptno from emp group by deptno
having avg(sal) = (select max(asl) from (select deptno , avg(sal) asl from emp group by deptno) nt )
第二种方式
SELECT deptno ,MAX(nt.asl) FROM (SELECT deptno , AVG(sal) asl FROM emp GROUP BY deptno) nt
多表查询
员工表
部门表
工资等级表
笛卡尔积
假设集合A={a, b},集合B={0, 1, 2},
则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。
查询两个数据表数据, 查询结果是把所有的可能
通过连接条件避免出现笛卡尔积
多表查询
- 给数据库表起别名, 便于引用
- 如果要查询的字段没有重复, 可以不指定表名
#查询员工所有信息,以及该员工工作的部门信息
1,select emp.*, dept.* from emp , dept
#第一种语法格式
select emp.*, dept.* from emp , dept where emp.deptno = dept.deptno;
# 给数据库表起别名, 便于引用
# 如果要查询的字段没有重复, 可以不指定表名
select e.*, d.* from emp e, dept d where e.deptno = d.deptno;#第二种语法格式
select e.*, d.* from emp e join dept d on e.deptno = d.deptno
# 相当于
select e.*, d.* from emp e inner join dept d on e.deptno = d.deptno
分类
内连接
在表中至少一个匹配时,则返回记录
#给emp 添加一条没有部门信息的员工
1,
insert into emp (empno,ename,hiredate,sal) values (10086,'支音',now(),10000);
#查询员工的所有信息以及 该员工的工资等级
2,
select e.*,s.grade
from emp e join salgrade s
on e.sal between s.losal and s.hisal
外链接
左外连接
#从左表中返回所有的记录,即便在右中没有匹配的行
1,select e.*, d.* from emp e left join dept d on e.deptno = d.deptno
右外连接
2,select e.*, d.* from emp e right join dept d on e.deptno = d.deptno
#全外连接(MySql 不支持, Oracle 支持)
3,select e.*, d.* from emp e full join dept d on e.deptno = d.deptno
三表查询案例
#查询员工的所有信息, 和所属部门的所有信息, 和工资等级
例1,
select e.*, d.* ,s.grade
from emp e
join dept d on e.deptno = d.deptno
join salgrade s on e.sal between s.losal and hisal
例2,
select e.*, d.* ,s.grade
from emp e, dept d, salgrade s
where e.deptno = d.deptno and e.sal between s.losal and hisal
自连接查询
#获取emp表中 所有 员工的姓名 以及该员工上司的姓名
1,select e.ename,m.ename
from emp e, emp m
where e.mgr = m.empno2,select concat(e.ename,'的上司是: ',m.ename)
from emp e, emp m
where e.mgr = m.empno
联合查询
# 两次的查询结果 取并集
#查询20 号部门和30 号部门的所有员工
# 以前的写法
select * from emp where deptno = 20 or deptno = 30;
select * from emp where deptno in (20,30);
#使用并集select * from emp where deptno = 20
union
select * from emp where deptno = 30;
#使用并集 模拟 全外连接select e.*, d.* from emp e left join dept d on e.deptno = d.deptno
union
select e.*, d.* from emp e right join dept d on e.deptno = d.deptno;