Chapter 4 查询
4.1 基本查询
4.2 条件查询
4.3 分组查询
4.4 排序查询
4.5 子查询与多表查询
这里说明一下,oracle在my object中提供了一个数据库,里面有dept(部门表),emp(员工表),salgrade(工资等级表),bonus(奖金表),学习时,希望大家熟练的记得里面的字段,和表之间的对应关系,这对学历数据库语言有很大的帮助,下面的查询语句都是以此为基础,而且大家的练习也会围绕这几个表。
4.1 基本查询
section 1
--语法
select 字段 from 表 ;
--查询单个字段
查询员工姓名
select ename from emp ;
--查询多个字段
查询员工姓名和薪水
select ename, sal from emp ;
--查询全部字段
查询员工的全部信息
select * from emp ;
section 2
--列上做运算和连接
查询员工姓名和年薪
select ename,(sal+comm)*12 from emp;结果不准确,因为comm可能会有空值,造成数据错误
select ename,(sal+NVL(comm,0))*12 from emp; --正确 NVL的是函数,学会百度,是程序员的必备技能喔
--连接
查询员工的姓名(姓名前拼接公司名)和薪水
select ‘门道信息’ || ename,sal from emp ;(数据库的字符用单引号)
section 3
--列别名和表别名
--列别名
查询员工的姓名和年薪
select e.ename,(sal+NVL(comm,0))*12 as from emp e ;
--可以不写as,同时别名尽量不用中文
将as改为 year_sal这样意思比较明确。
--表别名
查询员工姓名和入职日期
select e.ename,hiredate from emp e ;
section 4 (规范)
a. SQL语言对大小写不敏感,但是对单引号所包含的字符串,SQL语言是区分大小写的。
b. SQL语言可以写在一行或者多行。
c.各子句一般要分行写。
d.使用缩进提高语句的可读性。
4.2 条件查询
4.2.1条件查询概述
--使用where子句查询将不满足条件的行为过滤掉,where子句紧跟form子句,语法格式如下
select 字段 from 表 where 条件
--字符大小写敏感
select * from emp where job='CLERK' ;--查询正确
select * from emp where job='clerk' ;--查询错误
--数字直接写,字符串必须包含在单引号中,日期需要日期函数处理。
--查询员工编号为7788的员工的所有信息
select * from emp where deptno='7788' ;
--查询职位为'CLERK'的员工的所有信息
select * from emp where deptno='CLERK' ;
--查询入职日期为1987-08-20的员工的所有信息
select * from empt where hiredate=to_date('1987-08-20','yyyy-mm-dd') ;
4.2.2关系运算符
操作符 含义
> 大于
< 小于
= 等于
>= 大于或者等于
<= 小于或者等于
<> 不等于
!= 不等于
4.2.3 条件查询(LIKE)
--查询名字中含有字符’O'的员工信息
select * from emp where ename LIKE '%O%';
--查询名字中第三个字符'O'的员工信息
select * from emp where ename like '__O%';
4.2.4 条件查询(BETWEEN. .AND)
--查询工资在3000-5000的员工信息
select * from emp where sal between 3000 and 5000 ; (between . . and 查询包含边界值)
4.2.5 条件查询(IN)
--查询empno在7369或者7499中的任意一个的员工信息
select * from emp where empno in (7369,7499) ; (IN 查询表示只要是多个值中的任意一个就满足条件。)
4.2.6 条件查询(NULL)
--查询没有奖金的员工信息
select * from emp where is NULL ;(NULL只能通过 is not 或者not(is)来判断,不能使用关系运算符来判断)
4.2.7 逻辑运算符
--逻辑运算符
--AND 并且(逻辑与):只有AND的两边都是成立才满足条件
--OR 或者(逻辑或):只要OR 的两边有一边成立就满足条件
--NOT 非(逻辑否):表示与原来的条件相反
4.2.8 逻辑运算符的用法(这里的表与前面不同)
--AND
select studentid,sname,classid
from student
where classid = 1 AND sname LIKE '%O%' ;
--OR
select studentid,sname,sex,classid
from student
where classid = 2 OR sname LIKE '%O%' ;
--NOT
select studentid,sname,sex,classid
from
where classid NOT IN (1,2) ;
4.3 分组查询
--分组函数
--GROUP BY 手动分组
--DISTINCT应用于分组函数
--NVL函数应用于分组函数
--分组后过滤 HAVING
4.3.1 分组函数
--什么是分组函数?
分组函数作用于一组数据,返回一个结果
4.3.2 分组函数的使用
--AVG--平均值、SUM--和、MAX--最大值、MIN--最小值、COUNT--计数
select avg(sal) from emp;
...
--AVG,SUM函数只作用于number类型的字段
--MAX和MIN函数可以作用于任何类型
--但字段的值为NULL时不会被COUNT函数统计
--分组函数单独使用的时候可以任意组合
select avg(sname) from emp ;
select count(comm) from emp ;
select avg(sal),sum(sal),max(sal),min(sal),count(comm) form emp ;
4.3.3 GROUP BY
--什么是GROUP BY手动分组
--分组函数作用于一组数据,返回0-N个结果。(本人现在也不太理解,这里希望有大佬指点)
4.3.4 GROUP BY 手动分组
--GROUP BY 单独使用
--查询公司有那些岗位
select job from emp group by job ;
--GROUP BY 和分组函数一起使用
--查询公司每个部门的平均工资
select deptno,avg(sal) from emp group by deptno;
--根据多个字段分组
--查询每个部门每个岗位的最高工资
select deptno,job,max(sal) from emp group by deptno,job ;
--注意
a.在SELECT列表中所有未包含在组函数中的列都应该包含在GROUP BY 子句中。
b.包含在GROUP BY 子句中的列不必包含在SELECT 列表中
4.3.5 DISTINCT 关键字
--DISTINCT 单独使用表示去重复
--查询公司有哪些岗位
select job from emp group by job;
--DISTINCT应用于分组函数
--查询公司的岗位个数
select count(distinct job) from emp;
4.3.6 HAVING分组后过滤
--HAVING分组以后过滤
--查询部门工资>2000的人数超过2个的部门人数
select deptno,count(*)
from emp
where sal>2000 --分组前过滤
group by deptno
having count(*)>=2 --分组后过滤
--查询学生中大于60分的中课程超过1门的学生信息
select sid,sname,count(cid),max(score)
from student
where score>=60 group by sid,sname
having count(cid)>=2
4.3.7 非法分组
where中不能使用分组函数,HAVING可以使用分组函数
4.4 排序查询
--逻辑数据都已写好,按照指定规则挪动数据就称为排序
select * from emp order by sal asc ;
select * from emp order by ename desc ;
select ename,sal*12 year_sal from emp order by year_sal asc;
select ename,sal*12 from emp order by 2 asc ;(这里的2是根据第二列排序)
select * from emp order by deptno desc,sal asc,comm,empno,ename desc (依据多个字段排列)
--注意
a.order by 子句永远在语句的最后。
b.asc是升序,desc是降序,默认升序,可以不写
c.多字段排序时,先按照第一个规则排,拍完以后如果还有相同,按照第二个规则排序,如果还有相同数据,继续按照第三个规则排序,以此类推。
4.5 子查询&多表查询
4.5.1 使用子查询解决问题
4.5.2 子查询的特点
4.5.3 单行子查询
4.5.4 单行子查询示例
4.5.5 多行子查询
4.5.6 多行子查询示例
4.5.7 多表查询(内连接)
4.5.8 多表查询(左/右连接)
4.5.9 多表查询(其他连接)
4.5.1使用子查询解决问题
--查询比JONES的工资还要高的员工信息
--主查询:谁的工资比JONES高?
--子查询:JONES的工资是多少?
--注意:
a.子查询(内查询)在主查询之前一次执行完成。
b.子查询的结果被主查询使用(外查询)
4.5.2 子查询的特点
--子查询要包含在括号内
--将子查询放在比较条件的右侧
--子查询分为单行查询和多行查询
--子查询一般情况下应用于where子句或者是from子句
4.5.3 单行子查询
--单行子查询
--返回一行数据:比如=、>、<、>=、<=、<>
--注意:1.返回单行;2.使用单行比较操作符。
4.5.4 单行子查询例子
4.5.5 多行子查询
--多行子查询
--返回多行数据:比如IN,NOT IN
--注意:1.返回多行;2.使用多行比较操作符
4.5.6 多行子查询例子
4.5.7 多表查询(内连接)
--内连接:select e.ename,e.sal,d.dname,d.deptno from emp e,dept d
where e.deptno = d.deptno;
--第二种:select e.ename,e.sal,d.dname,d.deptno from emp e
inner join dept d on e.deptno
--多表查询(左/右连接)
左连接:以左连接的表为准,连接后的结果左表里面的数据全部都有,如果关联的字段在游表有值,那么就会显示关联的右表的数据,如果关联的字段在右表中没有值,则显示null。
--右连接:与左连接刚好相反。
4.5.9 多表查询(其他连接)
--满连接 full join
满连接的结构就是左连接和右连接的结果去掉重复记录以后加在一起
--交叉连接cross join
交叉连接的结果就是笛卡尔积,注意没有条件
--三个或者三个表以上连接
--求班级名称,学生姓名,选修的课程表,课程的成绩
select bj.bname,st.sname,cs.kname,cs.score
from classes_student cs,classes bj,student st
where cs.bid = bj.bid and st.sid=cs.sid;