*DQL:数据查询语言
--select:选择
1.查询:
1)当查询的内容与表无关时,select 查询内容;
2)当查询的内容与表有关时,select 查询内容 from 表名;
查询内容:查询表中所有的字段:*
查询指定字段:select 字段名,字段名 from 表名;
结果集:查询得到的结果
如果查询时有条件:
where 子句
用法:
select 字段名,字段名 from 表名 where 条件;
2、给查询显示的列,起别名。
格式:
select 字段名1 [as] 列别名,字段名2 from 表名 [where 条件]
###:列别名可以不加双引号或单引号,
但是如果列别名中带有空格的话,
我们就需要将列别名加上双引号或单引号
select ename 姓名, sal '工 资' from emp
3、where子句:
作用:起到条件限制约束等操作。有过滤的作用。
位置:放在 from子句后。
格式:
select colName from tableName where 条件。
条件:关系运算符:
>,>=,<,<=,=,!=,<>。
(1)null在条件中,不能使用 = 或 != 来判断,应该使用is 或者is not (2)范围查询:针对于某一个字段来说。
1)可以使用>,>=和<,<=来进行范围限制
2)还可以使用 between and来限制。
格式:
colName [not] between val1 and val2;
###: between val1 and val2 相当于
闭区间[val1,val2]
运算符 | 优先级(优先级小的优先) |
所有的比较运算符 | 1 |
not | 2 |
and | 3 |
or | 4 |
(3)集合作为条件写法:同一个字段有多个值的情况下使用。
使用: in / not in
格式: colName [not] in (元素1,元素2,....);
###: in : 相当于 or,比较的都是同一字段
not in:相当于 and,比较的都是同一字段
集合的其他用法:
【all与any】
格式:
>all(元素1,元素2,元素3......)
大于集合中最大的元素。
<all(元素1,元素2,元素3......)
小于集合中最小的元素。
>any(元素1,元素2,元素3......)
大于集合中最小的元素。
<any(元素1,元素2,元素3......)
小于集合中最大的元素。
###:在mysql中,集合函数(>all,>any,<all,<any)不适用与直接加数字
>any(10,20,30) -- 不允许
但是我们可以将(10,20,30)转换为子查询即可
(4)模糊查询:like关键字
格式: colName like value。
通常与通配符%连用
%: 0或0个以上的字符
_:匹配一个字符
*sql中运算符:
=:判断两个值是否相等
<>:判断不相等
:=:赋值运算符
\:转义运算符,
标准SQL语句并没有提供反斜线(\)的转义字符,
而是使用escape关键字显示进行转义
如:select * from emp where ename like '\_%' escape '\';
4、排序:
当在查询表中数据时,记录比较多,
有可能需要进行排序,
此时我们可以使用order by子句。
位置语法:
select 字段 from 表名 where 条件 order by 字段 排序规则
###:排序规则有两个:
升序:asc(默认)
降序:desc
###:当order by后面是一个字段时,
就按照该字段的排序规则进行排序; 当order by后面是多个排序规则, 那么会先按照第一个排序规则进行排序, 在第一个排序结果相同时,会按照第二个排序规则进行排序
desc:1) 查询表结构 -- desc 表名
2) 排序的降序规则 -- order by 字段 desc
如果设计表时,字段名叫desc,会怎么样?
【在使用该字段时,会出现错误,解决办法,将该字段进行转义
`字段名`】
5、去重 : 有的时候我们可以需要对重复的记录进行去重操作
比如,查询表中有哪些职位,此时,一种职位只需要
显示一条记录就够。
关键字:distinct
位置: 必须写在select关键字后。
###:distinct可以对一个字段进行去重,
也可以对多个字段进行去重(联合去重,就是多个字段组合起来唯一即可)
6、分组查询与分组函数(聚合函数)
有的时候,我们可能需要查询表中的记录总数,
或者查询表中每个部门的总工资,平均工资,总人数。
这种情况需要对表中的数据进行分组统计,
需要group by子句。
位置:
select..from tName [where 条件] [group by子句] [order by子句]
用法:
group by Field1[,Field2]。
###:在分组查询时,
select子句中的值如果含有组标识或组函数,
就尽量不要含有非组标识或组函数,
如果在select后边既有组函数,
又有非组函数(普通字段),
那么在oracle数据库中,该语句出现错误,
而在mysql数据库中,该语句没有错误,但是查询结果不正确,
所以我们要避免在select后面既有组函数,又有普通字段
聚合函数:
count(Filed):统计指定字段的记录数。
*:统计指定表中有多少条记录
字段名:统计指定表中该字段不为空的个数
sum(Filed):统计指定字段的和。
avg(Filed):统计指定字段的平均值
max(Filed):返回指定字段中的最大值。
min(Filed):返回指定字段中的最小值。
PS:聚合函数会忽略null值。
因此有时候需要使用函数:ifnull()
函数ifnull(field,value)
逻辑:如果field字段对应的值不是null, 就使用field的值,
如果是null,就使用value.
多字段分组时:
field1, field2
10 500
20 600
30 700
###:多字段分组时,最多分组的数目为Filed1*Field2[*Filed3....]
================================================================================
--Mysql语句:
1.查询emp表中工资大于1000的员工姓名以及工资
select ename,sal from emp where sal > 1000
2.查询emp表中职位为'MANAGER'的员工姓名和工资以及职位
select ename,sal,job from emp where job='MANAGER'
3.查询emp表中名字长度为5的员工姓名和工资以及职位
select ename '姓 名',sal "工 资",job "职 位"
from emp where length(ename)=5
4.查询emp表中没有奖金的员工信息
SELECT * FROM emp WHERE comm IS NULL
5.查询emp表中有奖金并且工资大于1000的员工姓名以
及工资和奖金
select ename,sal,comm
from emp where comm is not null and sal > 1000
6.查询工资在1000到2000之间的员工信息
select * from emp where sal > 1000 and sal < 2000
select * from emp where sal BETWEEN 1000 AND 2000
7.查询10部门和20部门的员工信息
select * from emp where deptno = 10 or deptno = 20
select * from emp where deptno in (10,20)
8.查询emp表中职位为'CLERK','MANAGER','ANALYST'的员工信息
select * from emp where job='CLERK' or job='MANAGER'
or job='ANALYST'
select * from emp where job in ('CLERK','MANAGER','ANALYST')
9.查询emp表中既不是10部门,又不是20部门的员工
select * from emp where deptno != 10 and deptno != 20
select * from emp where deptno not in (10,20)
10.查询emp表中职位不为'CLERK','MANAGER','ANALYST'的员工信息
select * from emp where job
not in ('CLERK','MANAGER','ANALYST')
11.查询emp表中工资大于smith,allen,ward中最大工资的员工
select * from emp where sal >all(
select sal from emp where ename in ('SMITH','ALLEN','WARD')
)
12.查询emp表中名字带有'S'的
select * from emp where ename like '%S%'
13.查询名字中第五个字符为'S'的员工
select * from emp where ename like '____S%'
14.查询emp表中职位的第二个字符为'a'的
select * from emp where LOWER(job) like '_a%'
15.查询emp表中部门号为20号部门的工资,工资降序排列,
工资相同时,按照姓名升序排列
select sal,ename from emp where deptno = 20
order by sal desc,ename asc
16.查询emp表中所有数据,要求先按照部门降序排,同部门的按照
工资升序排
select * from emp order by deptno desc,sal
17.查询product表中所有的字段
select pid,pname,`desc` from product
18.查询emp表中有哪些职位
select DISTINCT job from emp
19.查询emp表中的员工都来自于哪个部门
select DISTINCT deptno from emp
20.查询每个部门中都有哪些职位
select DISTINCT deptno,job from emp
21.统计emp表中有多少条记录
select count(*) from emp
22.最大值,最小值,平均值
select max(sal),min(sal),avg(sal) from emp
23.查询20部门中的最高工资,最低工资,平均工资
select max(sal),min(sal),avg(sal) from emp where deptno = 20
24.查询每个部门中的最高工资,最低工资,平均工资
select deptno,max(sal),min(sal),avg(sal)
from emp group by deptno
25.查询emp表中的总工资和总奖金
select SUM(sal),SUM(IFNULL(comm,0)) from emp
26.查询emp表中每个人的工资年薪,奖金年薪
select sal*12,IFNULL(comm,100)*12 from emp
27.查询不同部门不同职位的最高工资,最低工资,平均工资
select deptno,job,max(sal),min(sal),avg(sal)
from emp group by deptno,job
复习
1.DQL:select
1)select子句
a)查询数据与表无关时,select 查询内容
b)查询数据与表有关时,select 查询内容 from 表名
2)from子句
作用:指定查询数据来源的表
3)where子句
作用:进行条件过滤的(满足条件的记录会加入到结果集中)
4)order by子句
作用:按照某一字段进行排序
用法:order by 字段名 排序规则[,字段名 排序规则]
排序规则:升序(默认的排序规则) asc,降序 desc
5)group by子句
作用:用来进行分组的
用法:group by 字段名
书写顺序:select from where group by having order by
执行顺序:from where group by having select order by
6)distinct子句
作用:去除重复的记录
用法:必须跟在select后面
7)运算符:
> < >= <= = !=(<>)
>any(10,20,30) >all <any <all
between and : 在某个区间内
用法:select 字段 from 表名 where 字段 between 值 and 值
in
not in
8)分组函数(聚合函数)
count(*/字段名):统计函数
sum(字段名): 求和函数
max(字段名):求最大值函数
min(字段名):求最小值函数
avg(字段名):求平均值函数
题:求每个职位的最高工资和最低工资
select max(sal),min(sal),job from emp group by job
###:在select后面,
如果有分组函数出现,那么就尽量不要出现普通字段,
可以出现分组函数或组标识
=================================================
1、having子句:
在分组查询时,
有的时候可能需要再次使用条件进行过滤,
这个时候不能where子句,
应该使用having子句。
having子句后可以使用聚合函数。
位置:位于group by子句后
面试题:where和having的区别?
1)where后面放的是字段名,having后面可以放聚合函数
(where属于一次过滤,having是对分组结果二次过滤)
2)书写位置:where 位于 group by之前,having 位于 group by之后
3)执行顺序:where先执行,having后执行
2、子句总结:
一个基本的查询语句包含的子句有:
select子句
from子句
where子句
group by子句
having子句
order by子句
一个完整的查询语句:
select..from..[where..][group by..][having..][order by..]
执行顺序:
先执行from子句。
再执行where子句
然后group by子句
再次having子句
之后select子句
最后order by子句
==============================