oracle数据库基础（2）

DQL数据库查询语言

1，select语句最简单的语法：
语法：
select * from 表名

*是统配符，表示所有的列 t表示表的别名
select * from emp t

查看部份列：

select 列名列表 from 表名;

–查看员工姓名，工作，薪水
select ename,job,sal from emp;
2，dual伪表：
当在查看的结果中不涉及到表时，可以用dual这张表
select 1+1 from dual;
select sysdate from dual;
select user from dual;
3，–算术运算符 ±*/

–查看员工姓名及年薪
select ename,sal12 from emp;
4，–列的别名
select ename,sal12 as 年薪 from emp;-- as可加可不加
select ename,sal12 年薪 from emp;
select ename,sal12 “年 薪” from emp;–当列的别名中有空格时，必须要加双引号 不能用单引号
5，连接符||:

select ename||‘的年薪是:’||sal*12 “员工年薪信息” from emp;

6，order by排序：（order by 写在select的最后面）
语法:
select 列名列表 from 表名 order by 排序列名（可以有多个，逗号隔开)

–1)升序(默认) order by 列名 【asc】
–2)降序： order by 列名 desc

–查看员工表薪水从低到高进行排序
select * from emp order by sal;

–查看员工表薪水从高到低进行排序
select * from emp order by sal desc;

–查询薪水从高到低,入职时间从后往前进行排序
–注意：当涉及多列排序时，先按第一列进行排序，当第一列有相同时，再按第二列进行排序
select * from emp order by sal desc,hiredate desc;
7，where条件语句：
作用：选择数据、过滤数据作用
语法：
select 列名列表 from 表名 where 条件 order by 排序列名
比较运算符和逻辑运算符:
–比较运算符 = > < >= <= != <>(不等于)
–逻辑运算符 and or not非

–查询员工表中姓名是SMITH的员工信息
select * from emp where ename=‘SMITH’;
–查询员工表中工资大于 2000的员工信息
select * from emp where sal>2000;
–查询员工表中工资2000到5000之间的员工信息
select * from emp where sal>=2000 and sal<=5000;
–查询员工表中不是部门20的员工信息
select * from emp where deptno<>20;
select * from emp where deptno!=20;
–查询员工入职时间在1981年之后（包括1981年）的员工信息
select * from emp where hiredate>=to_date(‘1981-01-01’,‘yyyy-MM-dd’)
–查询没有奖金的员工信息
select * from emp where comm is null;
–查询有奖金的员工信息
select * from emp where comm is not null;
select * from emp where not comm is null;
8，模糊查询 ：
1）between … and … --表示一个范围 包含两个边界值
2) in 条件为等于多个值时
3) like ( 模糊查询代表关键字）

1）between … and …–表示一个范围 包含两个边界值 ==> >= and <=
–查询员工表中工资2000到5000之间的员工信息
select * from emp where sal>=2000 and sal<=5000;–等价于下面写法
–注意一定要包含这两个边界值才可以使用between … and …
select * from emp where sal between 2000 and 5000;
–查询员工表中入职时间是1981年到1985年入职的员工信息
select * from emp where hiredate between to_date(‘1981-0101’,‘yyyy-mm-dd’)
and to_date(‘1985’)
select * from emp where hiredate between to_date(‘1981-01-01’,‘yyyy-mm-dd’)
and to_date(‘1985-12-31’,‘yyyy-mm-dd’);

2. in 条件为等于多个值时
   –查询员工姓名是SMITH SCOTT JONES的员工信息
   select * from emp where ename=‘SMITH’ or ename=‘SCOTT’ or ename=‘JONES’;–等价于下面写法
   select * from emp where ename in (‘SMITH’,‘SCOTT’,‘JONES’);

3)like 模糊查询关键字
统配符：
% 表示匹配0到多个字符 ============= linux 中的 *
_ 表示匹配1个字符 ============= linux 中的 ？

–查询姓名是S开头的员工信息
select * from emp where ename like ‘S%’;
select * from emp where ename=‘S%’;–不能使用等号 错误写法
–查询姓名是包含M的员工信息
select * from emp where ename like ‘%M%’; --%S S结尾
–查询姓名是第二个字符是M的员工信息
select * from emp where ename like 'M%’; --%M 倒数第二个是M
–查询姓名是以5个字符组成的员工信息
select * from emp where ename like ‘_____’;
9，–distinct去重
–查看员工的所有工作
select distinct job from emp;–单列去重
–查看员工的所有工作以及领导编号
select distinct job,mgr from emp;–多列去重
10，–rownum伪列 表示行号
–表的别名：
select * from emp;

select ,sal12 “年薪” from emp;–错误写法
–当select后面的列表项除了星号之外还有其他的列的时候，
–那么星号前面必须要加表名或表的别名点，否则会报错
select emp.,sal12 from emp;–正确写法
select t.,sal12 from emp t;–正确写法
select rownum,t.* from emp t;–正确写法

–取部份行，分四种情况：
–1)查询某表中前n条记录
–2)查询某表中第n条到m条记录 （m>n n!=1)
–3）查询某表中按照某列排序后的前n条记录
–4）查询某表中按照某列排序后的第n条到m条记录 （m>n n!=1)

–1）查询某表中前n条记录 （最简单）
–举例：查询员工表中前5条记录
select * from emp where rownum<=5;
–2）查询某表中第n条到m条记录 （m>n n!=1)
–举例：查询员工表中第5条到第10条记录
select * from emp where rownum>=5 and rownum<=10;–错误写法
—查询过程中使用rownum作为where条件时要求：
–只能从第1条开始并且连续的行是可以查的，
—跟第1条不连续或者跳过某一行的条件是查询有问题的
select * from emp where rownum=1 or rownum>=3;
select * from emp where rownum=1 or rownum=2 or rownum=4;
–举例：查询员工表中第5条到第10条记录
–1) 先编号 设置rownum别名 写成（select rownum 别名，t.* from 表名 t)
–2） 把前面的select 语句括号括起来当作一张表 (嵌套一层查询)
再根据别名作为条件取其中的部份行

select * from (select rownum nums,t.* from emp t) where nums>=5 and nums<=10;
–3)查询某表中按照某列排序的前n条记录
–举例：查询员工表中薪水从高到低的前10条记录
–假如涉及到排序的，一定要先排序，当作一张表，嵌套一层select语句
–再根据条件查询前10条记录，通过rownum作为条件进行查询
select * from (select * from emp order by sal desc) where rownum<=10;
–4）查询某表中按照某列排序的第n条到m条记录 （m>n n!=1)
–举例：查询员工表中薪水从高到低的第5名到第10名记录
–1）假如涉及到排序的，一定要先排序，当作一张表，嵌套一层select语句
–2）再编号设置rownum别名 写成（select rownum 别名，t.* from （select排序语句） t）
–3）再根据别名作为条件取其中的部份行 嵌套两层select语句
select * from (select rownum nums,t.* from (select * from emp order by sal desc) t)
where nums>=5 and nums<=10;
11，–常用函数
1）转换函数 to_date to_char to_number
select to_date(‘1998-10-11’,‘yyyy-mm-dd’) from dual;
–转化为字符串，常用用法：to_char获取日期当中的年份yyyy，月份mm，日dd
select * from emp;
–计算员工入职年份
select ename,to_char(sysdate,‘yyyy’)-to_char(hiredate,‘yyyy’) “入职年份” from emp;
select ename,(sysdate-hiredate)/365 “入职年份” from emp;
–计算年龄
select to_char(sysdate,‘yyyy’)-to_char(to_date(‘1981-10-01’,‘yyyy-mm-dd’),‘yyyy’) “年龄” from dual;
select to_number(‘9,999’,‘0,000’) from dual;–把字符串转换为数字类型

2)空值相关函数 nvl
–null值相关的函数
–查询员工姓名及年薪（包括奖金）
select ename,sal12+comm from emp;–结果会有空值
–注意：任何值跟null值进行计算，得到的结果肯定是null值
–nvl(参数1，参数2）表示当参数1这一列为空时用参数2代替
select ename,sal12+nvl(comm,0) “年薪” from emp;

3）round四舍五入函数 trunc截取
–round四舍五入函数 trunc截取
select ename,round((sysdate-hiredate)/365,2) “入职年份” from emp;
select ename,trunc((sysdate-hiredate)/365,2) “入职年份” from emp;
12，分组查询（重点）：关键字group by
–分组函数（聚合函数）： 主要应用于统计

–1）count 统计行数 count()常用
select count() “员工数” from emp;–select count(1) from emp
select count() “学生数” from student;
–2）sum 求和 (数字累计） sum(列名)
select sum(sal) “月付薪水” from emp;
select sum(grade) “学生考试总分” from score;
–3)avg 求平均值 avg(列名)
select avg(grade) “总平均分” from score;
select avg(sal) “员工平均薪水” from emp;
–4)max 求最大值 max(列名)
select max(sal) “最高薪水” from emp;
select max(sage) “最大年龄” from student;
–5)min 求最小值 min(列名)
select min(sal) “最少薪水” from emp;
select min(sage) “最小年龄” from student;
–分组查询中有where条件
–查询入职年限超过30年的员工数
select count() “员工数” from emp where (sysdate-hiredate)/365>30;
–查询年龄大于18岁的学生数
select count(*) “学生数” from student where sage>18;

–group by 分组关键字
–group by 语法规则：
–select后面列除了分组函数之外，只能写group by 后面的列,而且只能少不能多，
其他的列是不允许写在select后面
–查询部门人数超过5个人的部门编号

select deptno from emp group by deptno having count(*)>5;

–查询部门平均工资超过2000的部门编号
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
–查询部门MANAGER平均工资超过2500的部门编号
select deptno from emp where job=‘MANAGER’ group by deptno having avg(sal)>2500;
select * from score;
–查询每门课平均分超过85分的课程编号
select cid from score group by cid having avg(grade)>85;
–查询课程编号是以4结尾的且平均分超过85分的课程编号
select cid from score where trim(cid) like ‘%4’ group by cid having avg(grade)>85;
–查询每个学生最高分超过90分的学生编号
select sid from score group by sid having max(grade)>90
–查询学生表中男生人数超过5个人的班级编号
select classno from student1 where ssex=‘男’ group by classno having count(*)>5;
–查询学生表中各个班最小年龄小于17岁的班级编号
select classno from student1 group by classno having min(sage)<17;
13，–子查询：（嵌套查询）
–应用场景：
–1.一条查询语句的查询条件依赖另外一条查询语句的查询结果。
–2.一条查询语句的查询结果是作为另外一条查询语句的查询表(查询依据)。
–子查询查询结果有两种情况：1）单行 > < <= != 2）多行 in

–查询最高工资员工姓名
select ename from emp where sal=(select max(sal) from emp);
–查询比SMITH工资要高的员工信息
select * from emp where sal>(select sal from emp where ename=‘SMITH’)
–查询学生表中年龄最大的学生姓名
select sname from student where sage=(select max(sage) from student);
–查询比部门20人数要多的各个部门编号及人数
select deptno,count() “人数” from emp group by deptno having count()>
(select count(*) from emp where deptno=20);
–查询每个学生平均分超过所有学生平均分的学生编号
select sid from score group by sid having avg(grade)>(select avg(grade) from score);

14，多表关联：
1)内联接
2）外联接（左外联接，右外联接，全联接）

–什么情况下用到多表关联：
–1)查询结果涉及到多张表，需要用到多表关联
–2)查询条件涉及到多张表，一般也会用到多表关联

内联接
–语法（两张表的关联）
–特点：
–关联字段匹配到数据才会显示出来,没有匹配到的不会显示，例如：关联字段为空值的数据行是不会显示出来，关联后可能会有部份数据没有显示出来
第一种语法：
select 列名列表 from 表1 inner join 表2 on 表1.关联字段=表2.关联字段
select 列名列表 from 表1 t inner join 表2 k on t.关联字段=k.关联字段

例如：查询员工姓名及部门名称
select t.ename,d.dname from emp t inner join dept d on t.deptno=d.deptno;
–准备数据
create table emp_temp as select * from emp;
create table dept_temp as select * from dept;
insert into emp_temp select 8000,‘zhangsan’,job,mgr,hiredate,sal,comm,null
from emp where empno=7369;
insert into dept_temp values(50,‘market’,‘shanghai’)

–分析以下数据
select t.ename,d.dname from emp_temp t inner join dept_temp d on t.deptno=d.deptno;
第二种语法：
select 列名列表 from 表1，表2 where 表1.关联字段=表2.关联字段

–外联接：
–左外联接
–特点：
以左表为主，左表数据全部显示，右表有匹配到的匹配显示，没有匹配到空值显示
–语法
select 列名列表 from 表1 left join 表2 on 表1.关联字段=表2.关联字段
select 列名列表 from 表1 t left join 表2 k on t.关联字段=k.关联字段
–例如：查询所有员工姓名及部门名称
select t.ename,d.dname from emp_temp t left join dept_temp d on t.deptno=d.deptno;

–右外联接
–特点：
以右表为主，右表数据全部显示，左表有匹配到的匹配显示，没有匹配到空值显示
–语法
select 列名列表 from 表1 right (outer) join 表2 on 表1.关联字段=表2.关联字段
select 列名列表 from 表1 t right (outer) join 表2 k on t.关联字段=k.关联字段
–例如：查询所有员工姓名及部门名称
select t.ename,d.dname from dept_temp d right join emp_temp t on t.deptno=d.deptno;

–全联接(特点：即是左关联又是右关联，左右两表都全部显示）

–语法
select 列名列表 from 表1 full (outer) join 表2 on 表1.关联字段=表2.关联字段
select 列名列表 from 表1 t full (outer) join 表2 k on t.关联字段=k.关联字段

–例如：查询所有员工以及所有部门信息
select t.,d. from dept_temp d full outer join emp_temp t on t.deptno=d.deptno;
查询学生姓名以及班级名称：
select s.sname,c.cname from student1 s inner join class1 c on s.classno=c.cno;

–查询学生姓名，课程编号及分数
select s.sname,sc.cid,sc.grade from student s inner join score sc on s.sid=sc.sid;

–查询所有学生姓名，课程编号及分数(注意有所有的文字一般用外链接）
select s.sname,sc.cid,sc.grade from student s left join score sc on s.sid=sc.sid;

–三表关联（两两关联）
–语法：
select 列名列表 from 表1 inner join 表2 on 表1.关联字段=表2.关联字段 inner join
表3 on 表3.关联字段=表1.关联字段 [ where …]
select 列名列表 from 表1 left join 表2 on 表1.关联字段=表2.关联字段 left join
表3 on 表3.关联字段=表1.关联字段
–查询学生姓名，课程名称，分数
select s.sname,c.cname,sc.grade from student s inner join score sc on s.sid=sc.sid inner join course c on c.cid=sc.cid;
–查询所有学生姓名，课程名称，分数
select s.sname,c.cname,sc.grade from student s left join score sc on s.sid=sc.sid left join course c on c.cid=sc.cid;
15，总结：
1） 理解表结构，主键 外键 各个列的意思（基本）
2）分析题目意思：
查询结果和查询条件分别是什么？
（是否涉及到多张表，如果是，基本上多表关联
有没有涉及分组查询的字眼：最大，最小，平均，和，人数）
如果有涉及分组查询的字眼，基本上需要分组查询，确定有没有分组函数的条件
如果有，确定分组的列是什么？

3）如果既有多表关联又有分组查询，那么一定要先写分组查询，因为分组查询语法是受限制的
分组查询先写好（一般一张表），括号括起来，当作一张表
再去关联另外表