第四阶段:day20_070219【单表查询】
文章目录
单表查询:
查询:用户根据不同的方式从数据库表中获取自己所需要的数据
是数据库操作中非常重要且非常频繁的
-
创建新数据库test3
-
在数据库test3库中创建emp(雇员表)
emp(雇员编号empno
雇员名称ename
雇员的职位job
雇员领导mgr
雇员入职日期hiredate
雇员月薪sal
雇员的津贴comm
雇员部门编号deptno) -
给表插入数据
-- 创建雇员表
create table emp(
empno int(4) primary key,
ename varchar(10),
job varchar(9),
mgr int(4),
hiredate date,
sal decimal(7,2),
comm decimal(7,2),
deptno int(2)
)
-- 插入数据
insert into emp values
(7369, 'Smith', 'clerk', 7902, '1980-12-17', 800, null, 20),
(7499, 'Allen', 'salesman', 7698, '1981-02-20', 1600, 300, 30),
(7521, 'Ward', 'salesman', 7698, '1981-02-22', 1250, 500, 30),
(7566, 'Jones', 'manager', 7839, '1981-04-02', 2975, null, 20),
(7654, 'Maritn', 'salesman', 7698, '1981-09-28', 1250, 1400, 30),
(7698, 'Blake', 'manager', 7839, '1981-05-01', 2850, null, 30),
(7782, 'Clark', 'manager', 7839, '1981-06-09', 2450, null, 10),
(7788, 'Scott', 'analyst', 7566, '1987-04-19', 3000, null, 20),
(7839, 'King', 'president', null, '1981-11-17', 5000, null, 10),
(7844, 'Turner', 'salesman', 7698, '1981-09-08', 1500, 0, 30),
(7876, 'Adams', 'clerk', 7788, '1987-05-23', 1100, null, 20),
(7900, 'James', 'clerk', 7698, '1981-12-03', 950, null, 30),
(7902, 'Ford', 'analyst', 7566, '1981-12-03', 3000, null, 20),
(7934, 'Miller', 'clerk', 7782, '1982-01-23', 1300, null, 10);
-- 查询记录
select * from emp
一.单表查询
- 查询所有字段
语法:select 字段名1,字段名2… from 表名
- 例如:查询雇员表中雇员姓名、编号、职位、领导号、入职时间、薪资、奖金、部门编号
select ename,empno,job,mgr,hiredate,sal,comm,deptno from emp;
- 使用通配符*查询所有字段
语法:select * from 表名
- 例如:查询雇员表中所有信息
select * from emp
- 查询指定字段
语法:select 字段名1,字段名2… from 表名
- 例如:查询雇员表中雇员姓名、职位、部门号、薪资
select ename,job,deptno,sal from emp
- 去重 distinct 的使用
语法:select distinct 字段名1,字段名2 …from 表名
- 例如:查询雇员的部门号;查询雇员在哪几个部门号中
-- 查询雇员的部门号
select deptno from emp;
-- 查询雇员在哪几个部门号中
select distinct deptno from emp
- distinct使用到多个字段上
- 例如:查询雇员部门号及职位
-- 查询雇员部门号及职位
select deptno, job from emp;
-- distinct使用到多个字段上
select distinct deptno, job from emp;
- 注意:distinct使用到多个字段上,只有多个字段的值都相同时才去重
- 查询中使用算术运算符:+;-;*;/ (div);% (mod)
- 例如:查询雇员的年薪
select sal*12 from emp;
-
给字段起别名
语法:select 字段名1 [as] 别名1,字段名2 [as] 别名2 …from 表名
- 例如:查询雇员的年薪,别名为yearsal
select sal*12 as yearsal from emp;
-
注意:如果别名中有空格特殊符号 需要使用单引号括起来
-
例如:查询雇员的年薪,别名为"yearsal&年薪 ",或者 “yearsal 年薪”
select sal*12 as 'year&年薪' from emp
select sal*12 as 'year 年薪' from emp
二.对查询结果排序
-
升序默认的排序方式 asc
-
降序 desc
1.在单个字段中排序
语法:select 查询内容 from 表名 order by 字段 asc|desc -
例如:查询雇员信息按雇员薪资升序排序
select * from emp order by sal asc;
-- 默认的排序方式是升序
select * from emp order by sal;
- 在多个字段中使用排序
语法:select 查询内容 from 表名 order by 字段名1 asc|desc,字段名2 asc|desc…
- 例如:查询雇员信息,先按照雇员薪资升序排序,如果薪资相同则按入职日期降序排序
-- 查询雇员信息,先按照雇员薪资升序排序,如果薪资相同则按入职日期降序排序
select * from emp order by sal asc, hierdate desc;
- 注意:排序可以使用别名
- 例如:查询雇员的年薪,给年薪起别名yearsal 按照年薪降序排序
select sal*12 as yearsal from emp order by yearsal desc;
三.条件查询
- 条件查询:是用户按照一定条件查询,查询满足条件的部分记录
语法:select 查询内容 from 表 where 条件
- 比较运算符的使用:
= >; <; >= ;<=; != ;<>
- 例如:查询部门号为20的雇员信息;查询薪资大于等于3000的雇员信息;查询雇员的姓名为smith的雇员信息
-- 查询部门号为20的雇员信息
select * from emp where deptno=20;
-- 查询薪资大于等于3000的
select * from emp where sal>=3000;
-- 查詢薪資小於3000的僱員的民資,薪資,職位
select ename,sal,job from emp where sal<3000;
-- 查询雇员的姓名为smith的雇员信息
select * from emp where name='smith';
-- 或者:
select * from emp where name='SMITH'
** 注意HAVING 和 WHERE的区别:
WHERE 后面不能接多行函数(操作一组数据,即多行记录,返回一个结果)中使用(例如:COUNT(),SUM() )!!!而 HAVING 可以!!!
- 例如:
- HAVING可以这么理解:在一个大学,找出系里女生大于10个的系的名称,先按照不同的系分组,然后选择系里女生大于10的系。
- 再看这四种写法的区别:
- 注意:MySQL默认的是不区分大小写的,如果需要区分大小写,则可以使用binary关键字
-- binary的使用:來幫助我們區分大小寫
select * from emp where binary ename="smith"
select * from emp where binary ename="Smith"
[not]between and
的使用:可以查询某区间范围的记录
- 例如:查询薪资在1200-3000之间的雇员信息;查询薪资不在1200-3000之间的雇员姓名,薪资
-- 查询薪资在1200-3000之间的雇员信息
select * from emp where sal between 1200 and 3000;
-- 查询薪资不在1200-3000之间的雇员姓名 薪资
select ename,sal from where sal not between 1200 and 3000;
-
[not] in
:表示在指定集合中语法:select 查询内容 from 表名 where 字段名 in(值1,值2,…)
- 例如:查询雇员姓名是smith 、scott、king的雇员信息
select * from emp where ename in ('smith','scott','king')
- 例如:查询雇员姓名不是smith 、scott、king的雇员信息
select * from emp where ename not in ('smith','scott','king')
- is null或者 is not null:判断某个字段是否为空
- 例如:查询雇员中,有津贴的雇员信息;查询雇员中,没有津贴的雇员信息
select * from emp;
-- 查询雇员中,有津贴的雇员信息
select * from emp where comm is not null
-- 查询雇员中,没有津贴的雇员信息
select * from emp where comm is null
-
模糊查询:可以查询以XX开头 以XX结尾 包含XX 第几个是X
语法:select 查询内容 from 表名 where 字段名 like-
%:指任意长度的字符(可以有也可以没有)
-
_:匹配一个字符
-
- 例如:查询雇员中雇员姓名以S开头的雇员信息;查询雇员中雇员姓名以S结尾的雇员信息;查询雇员中雇员姓名包含S的雇员信息;查询雇员姓名第二个字符是L的雇员信息;查詢僱員姓名長度是5的僱員信息
-- 查询雇员中雇员姓名以S开头的雇员信息
select * from emp where ename like 'S%';
-- 查询雇员中雇员姓名以S结尾的雇员信息
select * from emp where ename like '%s'
-- 查询雇员中雇员姓名包含S的雇员信息
select * from where ename like '%s%'
-- 查询雇员姓名第二个字符是L的雇员信息
select * from emp where ename like '_L%'
-- 詢僱員姓名長度是5的僱員信息
select * from emp where ename like '_____'
-
条件中的逻辑运算符
- and:指并且 ,在查询时候只有满足所有查询条件的记录才会被查询出来
语法:select 查询内容 from 表名 where 条件1 and 条件2 and…
- 例如:查询雇员表中部门号为20 ,职位为clerk的雇员姓名,部门编号,职位
- and:指并且 ,在查询时候只有满足所有查询条件的记录才会被查询出来
-- 查询雇员表中部门号为20 ,职位为clerk的雇员姓名,部门编号,职位
select ename,job,deptno from emp where deptno=20 and job='clerk'
-- 查詢部門號為30,薪資在1500-3000之間的僱員信息
select * from emp where deptno=30 and sal between 1500 and 3000
-
or :指或者,在查询时候只要满足任意一个条件的记录就能被查询出来
语法select 查询内容 from 表名 where 条件1 or 条件2 or…
- 例如:查询雇员表中部门号为20 ,或者职位为clerk的雇员姓名 部门编号 职位
select ename,deptno,job from emp where deptno=20 or job='clerk'
四. 分页查询
- 语法:
select 查询内容
from 表名
where 条件
order by 字段名 asc|desc
limit a,b
select * from emp limit 1,3
-- 顯示第一頁的僱員信息,假如每頁顯示4條信息
select * from emp limit 0,4
-- 顯示第二頁僱員信息,假如每頁顯示4條信息
select * from emp limit 4,4
-- 顯示第三頁僱員信息,假如每頁顯示4條信息
select * from emp limit 8,4
-- 顯示第N頁僱員信息,假如每頁顯示4條信息
select * from emp limit (n-1)*4,4
-
注意:
1. limit 从哪条记录开始, 每页显示的条数,且默认第一条的值0- 如果limit与order by连用时候,先排序再分页
五. 函数的使用
1. 单行函数
a.字符函数
concat()
:拼接字符串
- 例如:查询雇员的信息,以指定格式输出;
雇员姓名:XX,薪资:XX,职位:XX,入职日期:XX,年薪:XX
select concat('僱員姓名:',ename,'薪資:',sal,'職位',job,'入職日期:'hiredate,'年薪:'sal*12) from emp;
length()
:计算字符串的长度
- 例如:查询雇员中雇员姓名长度是5的雇员信息
select * from emp where length(ename)=5
- lower() / upper():转换大小写
- 例如:查询雇员姓名,大写的雇员姓名 ,小写的雇员姓名
select ename, upper(ename),loewer(ename) from emp;
replace()
:在指定的字符串中,将某子串替换为新的字符串
replace(目标字符串, 查找的子串, 新字符串)
- 例如:将helloword字符串中的hello替换为hi
select replace('hellow','hello','hi') from dual
substring()
:截取子串
substring(目标字符串, 开始位置, 长度)
- 注意:开始索引是从1开始,不是0
- 例如:获取雇员姓名的前四个字符
select substring(ename,1,4)
b.数值函数
abs()
:取绝对值
- 例如:select abs(1),abs(-1)
select abs(1), abs(-1)
pi()
:获取圆周率
- 例如:select pi()
mod()
:取模
- 例如:select mod(3,2)
pow()
:求一个数的n次方
- 例如:select pow(3,2)
ceil()
:向上取
floor()
:向下取整
- 例如:select ceil(5.43),floor(5.7)
-
round(num)
:返回四舍五入的整数round(num,n)
:返回四舍五入n位小数
- 例如:select round(5.4),round(6.8),round(5.34,1),round(6.789,2)
truncate()
:取多少位
truncate(num,n)
其中n的取值可以是0,1,2…如果n取值为0代表截取整数
- 例如:select truncate(5.3,0),truncate(5.67,1),truncate(5.789,2)
rand()
:获取浮点类型的随机数,范围0-1.0 其中包括0但不包括1
- 例如:select rand(),rand(),rand()
c.日期时间函数
now()
获取当前日期和时间,包括年月日 时分秒
- 例如:select now()
curdate()
获取当前日期 只包括年月日
- 例如:select curdate()
curtime()
获取当前时间 只包括时分秒
- 例如:select curtime();
sysdate()
获取函数执行时的日期和时间
now()获取SQL执行时的日期和时间
- 例如:select sysdate(),now()
select sysdate(),now(),sleep(2),sysdate(),now()
dayofyear()
:获取某个日期是所在年份的第几天
week()获取某个日期是所在年份的第几周
- 例如:select dayofyear(now()),week(now())
datediff()
:计算两个日期之间的时间间隔
- 例如:计算2019-1-1距离现在时间间隔
select datediff(‘2019-1-1’,now())
date_add() / date_sub()
: 实现日期的加减运算
date_add(日期,时间间隔类型关键字interval 时间间隔类型对应的表达式 时间间隔类型)
- 例如:day_hour 1_12 代表 1天12小时
year_month 2_1 代表 2年1个月
select date_add(now(),interval ‘2_1’ year_month)
select date_sub(now(),interval 10 day)
d.流程控制函数
if(条件,t,f)
:如果条件成立返回t,否则返回f
- 例如:select if(1>2, ‘1大于2’, ‘1小于2’)
查询雇员的薪资,如果薪资>=3000 输入’高薪’ 否则’低薪’
select sal from emp;
select sal,if(sal>=3000,‘高薪’,‘低薪’) ‘薪资水平’ from emp;
select if (sal>=3000,'高薪','底薪') as '薪資水平' from emp;
ifnull(值1,值2)
:如果值1不为空则返回值1,否则返回值2
- 例如:询雇员的年薪
select sal*12 from emp;
-- 下面寫法會出錯,因為comm有可能為空
select (sal+comm)*12 from emp;
-- 有的僱員沒有津貼(comm),所以用ifnull
select (sal + ifnull(comm,0))*12 from emp
nullif(值1,值2)
:如果值1等于值2返回null,否则返回值1
select nullif(1,2), nullif(1,1)
- case 值:
when 值1 then 结果1
when 值2 then 结果2
…
else 其他结果
end
- 例如: select case 1
when 1 then ‘结果是1’
when 2 then ‘结果是2’
else ‘其他结果’
end
select case 1
when 1 then '结果是1'
when 2 then '结果是2'
else '其他结果'
end
- case:
when 条件 then 结果1
when 条件 then 结果2
…
else 其他结果
end
- 例如:查询雇员薪资 如果薪资>=3000返回 ‘高薪’ 否则’低薪’
select sal,case
when sal>=3000 then '高薪'
else '低薪'
end '薪资水平' # 起的別名’薪資水平‘
from emp;
2.多行函数
- 多行函数:操作一组数据(多行记录),返回一个结果。多行函數也叫分组函数。
- 多行函数大多使用于统计
- 例如:统计各部门中雇员的人数;统计各部门中最高最低薪资是多少
-
多行函数主要有:
count()
:统计表中记录的数目
a. 统计表中记录的总数目count(*)
-- 例如查询雇员表中有多少条记录
select count(*) from emp;
b. count(exp)统计exp值非空的记录数目
-- 例如:查询雇员表中有多少位雇员有津贴
select count(comm) from emp;
c. count(distinct(exp)) 返回表达式exp的值不重复且非空的总记录数目
-- 例如:统计雇员表中有多少位雇员是领导
select count(distinct(mgr)) from emp;
-- 统计的是除董事长外的领导人数,统计雇员表中所有领导
-- ifnull(值1,值2) 如果值1不为null返回值1 ,否则返回值2
select count(distinct(ifnull(mgr,1))) from emp;
sum()
:
a. sum(exp):返回表达式值得总和
-- 所有人的薪資
select sum(sal) from emp;
b. sum(distinct(exp)):返回不重复的表达式exp的总和]
select sum(sal),sum(distinct(sal)) from emp;
avg()
:
a.avg(exp):返回表达式值得平均值
select avg(sal) from emp;
b.avg(distinct(exp)):返回不重复的表达式exp的平均值
select avg(distinct(sal)) from emp;
max() / min()
max(exp):返回表达式值得最大值
min(exp):返回表达式值得最小值
select min(sal),max(sal) from emp;
3. 分组统计
-
语法:
select 查询内容
from 表名
[where 条件][group by 分组字段名1,分组字段名2…]
[order by 字段名 asc|desc] [limit]
-
使用举例
a. 求每个部门的人数
-- 按照部門號分組deptno select deptno,count(*) from emp group by deptno;
b. 求每个部门的平均工资
select deptno,avg(sal) from emp group by deptno;
c. 求每个部门中最高工资和人数
select deptno,max(sal),count(*) from emp group by deptno;
d. 求每个岗位的人数
select job,count(*) from emp group by job
e. 显示每个部门不同岗位的人数
select deptno,job,count(*) from emp group by deptno,job
3. 注意
a. 查询字段,如果没有在多行函数中包含,则必须是分组字段
select ename,job,sum(sal) from emp group by job;
-- 不同崗位的人一定很多個,按照崗位分組的話,到底顯示那一個人?計算機不知道
b. 如果没有group by,查询字段不能与多行函数一起查询
-- 合法:
select sal,empno from emp;
-- 不合法:
select sum(sal),empno from emp;
c. 不允许在where条件中使用多行函数
4. having子句
- 语法:
select 查询内容
from 表名
[where 条件][group by 分组字段]
[having 条件][order by]
- 執行順序:select from --where过滤—group by–having过滤;where 不能先过滤,因此where不能放在group by前面
-
使用举例
a. 每个部门不同岗位的人数,且人数大于2
-- 會出現錯誤:不允許where條件中使用多行函數!!!
select count(*) from emp
where count(*)>2 group by deptno,job
-- 決絕方法:Having(having在最後面)
select count(*) from emp
group by deptno,job
having count(*)>2
b. 在emp表中列出工资最小值小于2000的职位
-- 查詢每個職位的最低薪資:
select job,min(sal) from emp group by job;
-- 在emp表中列出工资最小值小于2000的职位
select job,min(sal) from emp
group by job
having min(sal)<2000;
c. 列出平均工资大于1200的部门和职位搭配组合
-- 求每个部门不同职位的平均工资
select avg(sal) from emp group by deptno,job
-- 列出平均工资大于1200的部门和职位搭配组合
select avg(sal) from emp
group by deptno,job
having avg(sal)>1200