– 数据库查询语言(DQL)
select 关键字
–标准SELECT查询由SELECT子句、FROM子句、WHERE子句、ORDER BY子句组成。
–查询(DQL)是数据库中最为常用和复杂的一种SQL结构,学会编写查询SQL是程序员必须具备的能力。
基本语法结构: select [distinct] {* | 列名 [别名], …} from 表名 [where 过滤条件] [order by 列1,列2…]
– 投影操作 – 指定查询结果中能显示哪些列
–列名的几种书写方法:
–如果我们选择某个表中的多个列,那么列名之间用逗号分隔开;
– 查询雇员信息,显示雇员编号、姓名、工资
select empno,ename,sal from emp;
–如果是单个列,只需要列出该列的列名即可; 如果有别名、显示是别名,但是不会修改原有的表结构。
select ename name from emp;
–如果选择所有的列,可以简单地用“*”号带代替列名列表。
select empno,ename,job… from emp; --效率高
select * from emp; --*表示所有列
--表前缀
select emp.ename from emp;
--取别名 as
--列别名:使用别名,可以使SQL语句和返回的数据更容易读懂、更容易理解。在多个表有同样的列名的时候,使用别名更容易区分。
select ename as 雇员姓名 from emp;
--表别名:
select e.ename from emp as e;
--计算列 -- 不会改变原有的表数据,仅仅是查询显示。
--需求:将每个雇员的薪资+1000员显示
select sal+1000 from emp;
--排除重复distinct
--我们需要确保返回唯一的数据行。这时我们需要使用DISTINCT关键字来排除重复的行数据。
--需求:查看雇员信息表中工作岗位
select distinct job from emp;
--返回限定行数的查询 limit -- MySQL分页查询
--基本语法格式:
select 列1,列2 from 表名 limit 开始序号,返回的行数;
select * from emp limit 0,5;
--需求: 查询雇员信息表显示第1页数据,每页显示10条。
select * from emp limit 0,10;
开始序号 = (当前页码数 - 1) * 每页显示的行数
返回行数 = 每页显示的行数
--选择操作 选定那些行出现在结果中
-- where 条件过滤
select * from 表名 where 条件;
--需求:查询雇员姓名为smith的雇员的姓名和工资信息
思考?
1.显示什么? 雇员信息 * 雇员姓名,工资
2.过滤条件是什么?姓名为smith
select * from emp where ename = 'smith';
--需求:查询工资大于1600的雇员信息
思考?
1.显示什么? 雇员信息 *
2.过滤条件是什么? 工资大于1600
select * from emp where sal > 1600;
运算符 含义
= 用于检测值是否于另一个值相等
!= 用于检测值是否不等于另一个值
^= 与!=类似
<> 与!=类似
> 如果第一个操作数大于第二个操作数则为真
>= 用于检测第一个操作数是否大于或者等于第二个操作数
< 如果第一个操作数小于第二个操作数则为真
<= 用于检测第一个操作数是否小于或者等于第二个操作数
--需求:查询奖金不等于null的雇员信息
思考?
1.显示什么? 雇员信息 *
2.过滤条件是什么?奖金不等于null
select * from emp where comm is null;
--多条件选择操作
and or
--需求:查询工资大于1000并且奖金不为null的雇员信息
select * from emp where sal > 1000 and comm is not null;
思考?
1.显示什么? *
2.过滤条件是什么?工资 > 100 并且奖金不为null
sal > 100 and comm is not null
--需求:查询工资大于1000或者奖金不为null的雇员信息
思考?
1.显示什么? *
2.过滤条件是什么?工资 > 100 并且奖金不为null
sal > 100 or comm is not null
**-- 执行范围测试(between and)**
--查询工资在800~3000之间的雇员信息
-- select * from emp where sal >= 800 and sal <= 3000;
select * from emp where sal between 800 and 3000; -- 取返回,更直观。
**--定义集合关系(in和not in)**
基本语法: select 列A, 列B from 表 where 列 in (值集合)
--需求:查询雇员编号为 7369、7499、7521、8888的雇员信息
select * from emp where empno = 7369 or empno = 7499 or empno = 7521 or empno = 8888; -- 繁琐
select * from emp where empno in(7369,7499,7521,8888); -- 直观
--需求:查询雇员编号不为 7369、7499、7521、8888的雇员信息
select * from emp where empno not in(7369,7499,7521,8888);
**-- 模糊查询(like)**
模糊查询是一种比较实用的过滤方式,利用通配符来实现模糊查询。
基本语法: select 列a, 列b from 表 where 列c like 模式
-- 通配符
_ 表示任何单个字符
% 表示包含零个或多个任意字符
--需求:查询雇员姓名是以s开头的雇员信息
--条件:以s开头
select * from emp where ename like 's%';
--需求:查询雇员姓名中包含a字母的雇员信息
select * from emp where ename like '%a%';
--需求:查询雇员姓名中第二个字符为a字母的雇员信息
select * from emp where ename like '_a%';
--需求:查询雇员姓名中包含_的雇员信息
注意:当遇到通配符作为模糊查询内容时,需要用转义符(\)
select * from emp where ename like '%\_%';
--处理空值数据
在数据库中判断某列是否为空不能用=NULL,而应该用IS NULL或IS NOT NULL。
--需求:查询工资大于1000并且奖金不为null的雇员信息
select * from emp where sal > 1000 and comm is not null;
-- 排序操作 指定查询的结果以什么样的顺序显示 order by排序 asc(升序,默认) desc(降序)
单列排序: select 列a, 列b, 列c from 表 order by 列a
--需求:查询雇员信息按照工资从高到低的顺序显示
select * from emp order by sal asc;
多列排序:select 列a, 列b, 列c from 表 order by 列a, 列b, 列c...
--需求:查询雇员信息按照工资从低到高,奖金从高到低的顺序显示。
select * from emp order by sal asc,comm desc;
– SQL操作顺序
第一步:执行from
第二步:where条件过滤
第三步:执行select投影列
第四步:执行order by 排序
select ename,job,sal from emp where sal > 1000 order by sal asc;
– DQL聚合函数
聚合函数的分类
– count:统计行数量
– 需求:统计雇员信息表的人数
select count() from emp;
select count(empno) as 统计人数 from emp;
select count(0) as 统计人数 from emp; – 少
–需求:统计工作为salesman的雇员人数
select count() from emp where job = ‘salesman’;
select count(all comm) from emp; – 为null不统计了
–统计奖金为null人数
select count(*) from emp where comm is null;
– 统计有多少种工作岗位
select count(distinct job) from emp;
-- sum:获取单个列的合计值
-- 需求:统计雇员薪资总和
select sum(sal) from emp;
-- avg:计算某个列的平均值
-- 需求:统计雇员信息表中雇员的平均薪资
select avg(sal) from emp;
-- max:计算列的最大值
-- 需求:雇员信息表中薪资最高的值
select max(sal) from emp;
思考?查询雇员信息表中薪资最高的雇员
select * from emp where sal = max(sal); -- 错的 --子查询(可以,但是我们还没学)
select * from emp order by sal desc limit 0,1; -- 取第一条
--聚合函数不能直接放置where语句后使用
min:计算列的最小值
-- 需求:雇员信息表中薪资最低的值
select min(sal) from emp;
**-- 数据分组 group by**
-- 需求:统计雇员信息表中每个工作岗位的人数
select count(job) from emp where job = 'clerk'; -- 只能得单个工作岗位的人数
select job,count(job) as 人数 from emp group by job;
clerk 4
salesman 4
manager 3
analyst 2
president 1
-- 需求:统计雇员信息表中工资大于2000每个工作岗位的人数
select count(job) from emp where sal > 2000 group by job;
**-- 聚合函数筛选子句(having)**
-- 需求:查询工作岗位平均工资大于2000的工作岗位
salesman 4 1600.00 1250.00 1250.00 1500.00
clerk 4 800.00 950.00 1300.00 1100.00
manager 3 2975.00 2850.00 2450.00
analyst 2 3000.00 3000.00
president 1 5000
--select job from emp where avg(sal) > 200 group by job; -- 两个错:1.先判断平均薪资(整个表) > 2000 2.聚合函数不能放在where后边
--先分组,再判断
select job from emp group by job having avg(sal) > 2000;
--having与where区别
相同点:
都是进行条件筛选
不同点:
1. having是分组后进行条件筛选,而where是分组之前进行条件筛选。
2. where放置在group by 之前执行
3. where中不能使用聚合函数,having是可以使用聚合函数。
SQL语句执行顺序
select avg(mark) -- 4
from score -- 1
where mark>60 -- 2
group by id -- 3
having avg(mark)>70 -- 5
order by avg(mar) desc -- 6
limit 1, 5; -- 7
-- 需求:查询雇员信息表中没有奖金,每个工作岗位的平均薪资大于2000,按照平均薪资进行降序的排序,最后显示出第1页的数据(每页显示2条)。
select job,avg(sal) from emp where comm is null or comm = 0 group by job having avg(sal) > 2000 order by avg(sal) desc limit 0,2;