文章目录
dql–数据查询语言
简单查询
语法:
SELECT * FROM 表名;
查询 所有 从 某个表
我们使用 scott —普通用户,初始密码 tiger 做例子
scott自带四张表
我们目前使用 emp表(员工信息表)dept表(部门信息表)
数据库的表是有归属的,表都属于某个用户,scott用户是Oracle自带的用户,自带表
此时我们输入查询语句
SELECT * FROM emp;//注意要带分号
共有八个字段
empno–员工编号
ename–员工姓名
JOB–岗位
MGR–领导编号
HIREDATE–入职日期
SAL–工资
COMM–奖金
DEPTNO–部门编号
共有14行数据
注意
1.Oracle中绿色的代表关键字,如
2.关键字 和 表名 列名 不区分大小写(工作中几乎都是大写)
3.关键字左右需要加空格
4.Oracle中所有的标点符号都是英文状态的。
查询一下dept表是什么样的呢?
SELECT * FROM dept
如图所示,他有三个字段四行数据、
DEPTNO–部门编号
DNAME–部门名称
LOC–部门地址
SELECT * FROM xx; // * 代表所有
如果想单独查询某个或者某些字段
SELECT 列名1,列名2,列名3,..... FROM 表名;
SELECT ename,sal FROM emp;//查询姓名和工资
如图所示
SELECT ename,ename,sal,sal,sal FROM emp;
查什么展示什么,你可以随便查。
简单练习一下
查询emp表所有字段。要求两种方式。
查询部门名称和部门地址。
SELECT * FROM emp;
SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO FROM emp;
SELECT DNAME,LOC FROM dept;
注意:*的效率很低,工作中很少使用。宁可把所有字段都列出来,也最好不要用星号
别名
列别名
语法 :
SELECT
列名1,
列名2,
列名3.....
FROM 表名;
为了简单方便可以给列取别名
SELECT 列名1 AS "别名" , //1. AS " " 双引号里面房别名
列名2 " 别名", //2.把AS省略,直接空格双引号别名
列名3 别名, //2.空格直接写别名。
.....
FROM 表名;
如图所示
表别名
SELECT * 列名 FROM 表名 别名; //表名后直接空格取别名
SELECT * FROM emp e; //给emp表取e这个别名
别名命名规范
不要乱取
1.尽量使用缩写
2.可以使用t1,t2,t3,t4不容易乱
注意
1.不论是表别名还是列别名,只适用于当前语句。
2.别名不建议使用中文
3.同样不建议使用数字和特殊符号作为别名,如果要使用的话,双引号不能省略
4.一旦取了表别名,就不能使用原来的名字
SELECT emp.* FROM emp; //emp.表名归属,运行正常
SELECT emp.* FROM emp e//取了表别名后,执行会报错,emp已经不能使用了
SELECT emp.*,ename from emp;
//*想要和其他字段一同展示的时候必须要加表名归属
//如果切换到system用户
SELECT scott.emp.* FROM scott.emp;
做个练习
将emp表所有字段取一个中文名
将dept表取一个表别名
SELECT EMPNO "员工编号",ENAME "员工姓名",JOB "工作",MGR "领导",HIREDATE "入职时间",SAL "工资",COMM "奖金",DEPTNO "所属部门" FROM emp;
SELECT DEPTNO,DNAME,LOC FROM dept t1;
结果如图所示
连接符
定义:将左右两侧的内容拼接到一起
SELECT ename,job,sal FROM emp;
SELECT ename||job,sal FROM emp;
如图
常量
常量:不变的量
SELECT ename,'受到收到' FROM emp;
将 收到收到 识别为常量作为一个字段
如何将常量使用拼接符将字段链接呢?
SELECT ename|| '的岗位是' ||job|| ''的薪水是||sal FROM emp;
如图
注意:字符上面是需要加单引号的,数字不需要加
写个练习
将emp表所有字段拼接到一起
SELECT
'职工编号是'
||EMPNO
||'的'
||ENAME
|| ',他的工作是'
|| JOB
||',他的领导是'
|| MGR
||',他的入职时间是'
|| HIREDATE
|| ',他的工资是'
|| SAL
||',他的奖金是'
|| COMM
||',他所属的部门是'
|| DEPTNO FROM emp;
如图
数值之间的计算
写一个例题
查询员工姓名工资,奖金以及年薪(年薪未给出,需要计算,年薪=(工资+奖金)*12)
SELECT ename,sal,comm,(sal+comm)*12 FROM emp;
存在空值(null值),空值是不参与加减乘除计算的,任何值和空值进行计算,得到的还是空值
条件查询
//条件查询
SELECT *|列名|计算|常量
FROM 表名
WHERE 过滤条件;
过滤条件
1.判断
包括 < > = <> != <= >=
常见的数据类型:
1.数值型
2.字符型
3.日期型
数值之间的比较
例题:
查询工资大于3000的员工信息
//按照 工资大于3000的 条件去过滤,展示 员工信息
SELECT * FROM emp WHERE sal>3000;//将符合条件的数据保留,不符合条件的过滤掉
练习
查询10号部门的员工姓名岗位薪资入职日期
查询奖金大于100的员工信息
SELECT ename,job,sal,hiredate FROM emp WHERE DEPTNO = 10;
SELECT * FROM emp WHERE COMM>100;
以下为练习结果
字符之间的比较
例题:查询姓名是SMITH的员工信息
SELECT * FROM emp WHERE ename = 'SMITH';
SELECT * FROM emp WHERE ename <> 'SMITH';//查询姓名不是史密斯的人
注意:
1.字符要加单引号
2.数据是区分大小写的
3.字符是可以比较大小的,但是没有意义(比较是使用ASCII码比较的)
练习:查询岗位是销售的员工姓名,岗位,薪资,入职日期。
查询部门地址是纽约的部门信息
SELECT ename,job,sal,hiredate FROM emp WHERE job = 'SALESMAN';
SELECT * FROM dept WHERE LOC = 'NEW YORK' ;
查询结果如图所示。
日期之间的比较
例题:
查询入职日期在1981/1/1之前入职的员工信息
SELECT * FROM emp WHERE hiredate<1981/1/1;
上面的代码会提示错误
应为DATE,实际为NUMBER
此时我们需要转换函数
to_date(‘目标字符’,‘日期格式’)
作用:将目标字符转换成日期类型
返回:日期;
SELECT * FROM emp WHERE hiredate < to_date('1981/1/1','yyyy/mm/dd');
练习
查询入职日志在1981/2/1之后的员工姓名岗位工资以及入职日期
SELECT ename,job,sal,hiredate FROM emp WHERE hiredate > to_date('1981/2/1','yyyy/mm/dd');
结果如图
注意:
1.比较符左右数据类型要一致。
2.空值(NULL值)是不参与比较的。
2.NULL值判断
IS NULL //是空值
IS NOT NULL //不是空值
例题:查询有奖金的员工信息
SELECT * FROM emp WHERE comm IS NOT NULL;
练习
查询emp表中最大的领导信息;
SELECT * FROM emp WHERE MGR IS NULL;
3.范围查询
关键词
BETWEEN 值1 AND 值2 —在值1和值2之间即满足条件。
NOT BETWEEN 值1 AND 值2 --不在值1和值2之间即满足条件。
例题:
查询工资在1000-3000之间的员工信息。
SELECT * FROM emp WHERE sal BETWEEN 1000 AND 3000;
BETWEEN AND 是包含边界的 即大于等于1000小于等于3000
1000与3000不可以换位置 一定要将小值写在前面
练习题
1.查询入职时间在1980年至1981年5月之间的员工信息
SELECT * FROM emp WHERE hiredate BETWEEN to_date('1980/1/1','yyyy/mm/dd') AND to_date('1981/5/31','yyyy/mm/dd');
4.包含查询
关键词
IN(集合) —在集合里面即满足条件
NOT IN(集合) —不在集合里面即满足条件
集合:必须是同一属性的数据
IN(1,2,3)
IN(100,200,300,800)
IN(‘a’,‘b’,‘c’) IN(to_date(‘1988/2/3’,‘yyyy/mm/dd’),to_date(‘1928/2/3’,‘yyyy/mm/dd’))
这些都是集合
而IN(‘a’,2,to_date(‘1988/2/3’,‘yyyy/mm/dd’))这种就不是集合
例题:查询工资是1000或者3000的员工信息
SELECT * FROM emp WHERE sal IN(1000,3000);
查询部门地址是纽约或者芝加哥的部门信息
SELECT * FROM dept WHERE loc in('NEW YORK','CHICAGO');
练习题
1.查询部门编号是10号或20号的员工信息
2.查询薪资是3000或5000的员工信息
3.查询岗位是SALESMAN或者MANAGER的员工信息
4.查询岗位既不是SALESMAN也不是MANAGER的员工信息
5.查询入职时间是1980年12月17号或者1981年2月20号的员工信息
SELECT * FROM emp WHERE DEPTNO IN(10,20);
SELECT * FROM emp WHERE sal IN(3000,5000);
SELECT * FROM emp WHERE job IN('SALESMAN','MANAGER');
SELECT * FROM emp WHERE job NOT IN('SALESMAN','MANAGER');
SELECT * FROM emp WHERE hiredate IN(to_date('1980/12/17','yyyy/mm/dd'),to_date('1981/2/20','yyyy/mm/dd'));
自己检查一下 太多了不截图了
5.模糊查询
LIKE ‘目标格式’ --像目标格式即满足条件
NOT LIKE ‘目标格式’ --不像目标格式即满足条件
目标格式
% ----占有一个或者多个字节
_ ----下划线只占一位
例题:查询员工姓名以S开头的员工信息
SELECT * FROM emp WHERE ename LIKE 'S%';
查询姓名倒数第二位是T的员工信息
SELECT * FROM emp WHERE ename LIKE '%T_%';
练习题
1.查找姓名以S开头的员工信息
2.查找姓名前边是SM、后边是TH、中间有一位不确定的员工信息
3.查找姓名总共有五位的员工信息
4.查找姓名前边是S、后边是H、中间有三位不确定的员工信息
5.查找姓名前边是SMIT、最后一位不确定的员工信息
6.查找姓名不以S开头的员工信息
7.查找名字中带有A字母的员工信息
8.查找姓名总共有5位且首字母是A的员工信息
9.查找姓名是以A开头且倒数第二位是M的员工信息
SELECT * FROM emp WHERE ename LIKE 'S%';
SELECT * FROM emp WHERE ename LIKE 'SM_TH';
SELECT * FROM emp WHERE ename LIKE '_____';
SELECT * FROM emp WHERE ename LIKE 'S___H';
SELECT * FROM emp WHERE ename LIKE 'SMIT_';
SELECT * FROM emp WHERE ename NOT LIKE 'S%';
SELECT * FROM emp WHERE ename LIKE '%A%';
SELECT * FROM emp WHERE ename LIKE 'A____';
SELECT * FROM emp WHERE ename LIKE 'A%M_';
图太多了不截了,你们自己对对答案。
6.ANY 和 ALL 的区别
ANY(集合) --满足集合中的任意一个值即满足条件
ALL(集合) --满足集合中所有的值即满足条件
如>ANY(集合) --大于集合中的任意一个值即满足条件
<ANY(集合) --小于集合中的任意一个值即满足条件
ALL同理
例题:查询工资大于1000或者大于2000的员工信息
SELECT * FROM emp WHERE sal >ANY(1000,2000);
7.条件连接
上述都是只有一个条件的情况下,当我们的查询需要多个条件时,可以通过条件连接进行查询
AND OR
AND–并且,既要满足A条件,也要满足B条件
OR – 或者,满足其中一个条件即可
例题:
查询十号部门的工资大于1000的员工信息
SELECT * FROM emp WHERE deptno = 10 AND sal>1000;
例题:
查询10号部门的经理或者20号部门的职员
SELECT * FROM emp WHERE deptno = 10 AND job = 'MANAGER'
OR deptno = 20 AND job = 'CLERK';
注意:当AND与OR都有的时候,优先执行AND,后执行 OR,有括号先执行括号
练习题:
1.查询薪资超过1000并且小于3000的员工信息(2种)
2.查询部门编号是10号或20号的员工信息(2种)
3.查询岗位是销售SALESMAN,并且奖金超过400的员工信息
4.查询20号部门的经理
5.查询所有20号部门的员工或岗位是MANAGER的员工信息
6.查询10号部门的部门经理或20号部门的分析师ANALYST
7.查询10号部门的员工、30号部门的经理及所有的分析师ANALYST
SELECT * FROM emp WHERE sal>1000 AND sal<3000;
SELECT * FROM emp WHERE sal BETWEEN 1000 AND 3000;
SELECT * FROM emp WHERE deptno = 10 OR deptno = 20;
SELECT * FROM emp WHERE deptno IN(10,20);
SELECT * FROM emp WHERE job = 'SALESMAN' AND comm >400;
SELECT * FROM emp WHERE job = 'MANAGER' AND deptno = 20;
SELECT * FROM emp WHERE deptno = 20 or job = 'MANAGER';
SELECT * FROM emp WHERE deptno = 10 AND job = 'MANAGER'
OR deptno = 20 AND job = 'ANALYST';
SELECT * FROM emp WHERE deptno = 10 AND job= 'CLERK'
OR deptno = 30 AND job = 'MANAGER'
OR job = 'ANALYST';
注意哈 between and 左右是闭合的,包括1000跟3000,所以结果会有不一样,具体用什么看使用的时候。不放截图了太多了。
排序查询
将查询结果按照一定规则排序
语法:
SELECT * | 列名 | 常量 | 计算 | 函数 ----展示
FROM 表 ----数据源
WHERE 过滤条件 ----过滤数据
ORDER BY 排序内容 ASC | DESC //ASC升序,DESC降序
例题:
查询员工信息,要求结果按照工资升序
SELECT * FROM emp ORDER BY sal ASC; //不写ASC默认是升序
查询员工信息,要求结果按照部门升序,同一部门再按照工资降序
SELECT * FROM emp ORDER BY deptno ASC , sal DESC;
order by 后面能放多个列,先按第一列排序,相同时再按下一列排序。
用逗号隔开,谁在前先按谁排,先排部门,再排工资、
例题:
查询工资大于两千的员工信息,要求结果按照部门升序,同一部门再按照工资降序
SELECT * FROM emp WHERE sal>2000 ORDER BY deptno ASC , sal DESC;
注意,如果存在空值,空值是无限大的,在排序的时候最大。如果不希望空值在前,可以在语句最后添加 NULLS LAST,使空值放到最后。如
SELECT * FROM emp ORDER BY comm DESC;
SELECT * FROM emp ORDER BY comm DESC NULLS LAST;
练习题:
1.查询员工编号、姓名、薪资、部门编号,按部门升序及薪资降序排列
2.查询除20号部门外,员工编号、姓名、薪资、部门编号,按部门升序、薪资升序、工号降序排列
3.查询员工姓名、薪资、佣金、薪资佣金合计,按薪资佣金合计值升序排列,
薪资佣金以“总计” 二字显示
4.统计员工姓名及员工资历,并按资历排序,将老员工排在前,新员工排在后
SELECT empno,ename,sal,deptno FROM emp ORDER BY deptno ASC, sal DESC;
SELECT empno,ename,sal,deptno FROM emp WHERE deptno !=20 ORDER BY deptno,sal,empno DESC;
SELECT ename,sal,comm,(sal+comm) as '总计' FROM emp ORDER BY (sal+comm) ;
SELECT ename,hiredate FROM emp ORDER BY hiredate;
(ORDER BY 后面能直接使用别名)
分组查询
分组函数(聚合函数)
sum(列名) --求和
AVG(列名) --求平均
MIN(列名) --求最小
MAX(列名) --求最大
COUNT(列名 | * |数字) --计数
例题:
查询emp表所有员工的平均薪资;
SELECT AVG(sal) FROM emp;
注意:空值不参与聚合函数的统计
SELECT COUNT(comm),COUNT(*),COUNT(1) FROM emp;
放具体列的时候,统计的是该列非空值的个数。如comm统计数只有4 因为有奖金的人只有四个。
分组查询
根据要求,按照规则分组,统一分析各组的情况,每一组返回一个值。
SELECT *|列名|常量|计算|函数
FROM 表名
WHERE 过滤条件
GROUP BY 分组内容
HAVING 过滤条件
ORDER BY 排序内容 ASC|DESC;
例题:
查询每个部门的平均工资
SELECT AVG(sal) FROM emp GROUP BY deptno;
当SELECT后面出现聚合函数的时候,只能展示GROUP BY后面有的字段。
例题:
查询每个部门的每个岗位平均工资
SELECT deptno,job,AVG(sal)
FROM emp
GROUP BY deptno,job;
GROUP BY 后面可以跟多个字段,先按第一个字段分组,每组下面再按第二列分组…
练习:
查询工资大于2000的员工中,每个部门的每个岗位平均工资
SELECT deptno,job,AVG(sal)
FROM emp
WHERE sal>2000
GROUP BY deptno,job;
练习:
查询工资大于2000的员工中,每个部门的每个岗位平均工资,要求平均薪资大于3000.结果按照平均工资降序。
SELECT deptno,job,AVG(sal)
FROM emp
WHERE sal > 2000
GROUP BY deptno,job
HAVING AVG(sal)>3000
ORDER BY AVG(sal) DESC;
HAVING是对分组后的数据进行过滤,WHERE是对分组前的数据进行过滤,WHERE后面不能使用聚合函数,如果使用WHERE AVG() …会报错。只要记住,但凡分组条件带聚合函数,就放 HAVING后。
整点练习题:
1.统计除10号部门的其他部门中,哪些部门的最高薪资达到三千
2.查询平均薪资超过1800的部门,显示部门编号及平均薪资
3.查询薪资合计超过5000的岗位及其平均薪资
4.查询部门人数超过4人的部门编号及薪资合计
5.查询各部门下,人数超过两人的岗位有哪些,显示部门编号、岗位名称
6.查询姓名重复的员工的姓名
SELECT deptno
FROM emp
WHERE deptno !=10
GROUP BY deptno
HAVING MAX(sal)>=3000;
SELECT deptno,AVG(sal)
FROM emp
GROUP BY deptno
HAVING AVG(sal)>1800;
SELECT job,AVG(sal)
FROM emp
GROUP BY job
HAVING SUM(sal)>5000;
SELECT deptno,SUM(sal)
FROM emp
GROUP BY deptno
HAVING COUNT(deptno)>4;
SELECT deptno,job
FROM emp
GROUP BY deptno,job
HAVING COUNT(job)>2;
SELECT ename
FROM emp
GROUP BY ename
HAVING COUNT(ename)>=2;
完整的查询语句
SELECT *|列名|常量|计算|函数
FROM 表名
WHERE 分组前的过滤条件(不能使用聚合函数的条件)
GROUP BY 分组内容
HAVING 过滤条件(分组后的过滤条件,能使用聚合函数的条件)
ORDER BY 排序内容 ASC|DESC;
上述语法是数据库的书写顺序
执行顺序!
1.FROM
2.WHERE
3.GROUP BY
4.HAVING
5.SELECT
6.ORDER BY
要记住!
拿别名来举例,ORDER BY后可以加别名 ,是因为他位于取别名的SELECT之后,而WHERE不能使用别名,是因为他执行在SELECT之前。
做个练习吧
1.查询 1982年以前入职的员工,按部门、岗位分组,查出
平均薪资超过800的部门和岗位类型及其平均薪资和最高薪资,按最高薪资降序排列
2.查询部门人数超过3的部门的部门编号、最高薪资、最低薪资、部门人数,按部门人数升序排列
3.查询岗位名称不以S开头、并且岗位平均薪资超过2000的岗位的岗位名称、岗位人数、岗位平均薪资,按岗位平均薪资升序排列
4.查询各部门部门编号、最高薪资、最低薪资,以及最高和最低薪资的差值,按部门编号降序排列
5.统计岗位类型、最高薪资、最低薪资、薪资合计、平均薪资,以及人数,
各字段以中文列名展示
6.统计薪资在1000到3000之间的人员中,有佣金的员工总数及无佣金的员工总数,
SELECT deptno,job,AVG(sal),MAX(sal)
FROM emp
WHERE hiredate < to_date('1982/1/1','yyyy/mm/dd')
GROUP BY deptno,job HAVING AVG(sal)>800
ORDER BY MAX(sal) DESC;
SELECT deptno,MAX(sal),MIN(sal),COUNT(1)
FROM emp
GROUP BY deptno
HAVING COUNT(1)>3;
SELECT job,COUNT(1),AVG(sal)
FROM emp
WHERE job NOT LIKE 'S%'
GROUP BY job
HAVING AVG(sal)>2000
ORDER BY AVG(sal) ASC;
SELECT deptno,MAX(sal),MIN(sal),(MAX(sal)-MIN(sal))
FROM emp
GROUP BY deptno
ORDER BY deptno DESC;
SELECT job "岗位类型",MAX(sal) "最高薪资",MIN(sal) "最低薪资",SUM(sal) "薪资合计",AVG(sal) "平均薪资",COUNT(1) "人数"
FROM emp
GROUP BY job;
SELECT COUNT(comm),COUNT(1)-COUNT(comm)
FROM emp
WHERE sal BETWEEN 1000 AND 3000;