数据库之Oracle-第3篇-查询
程序员文章站
2022-06-01 19:46:51
...
a.查询关键字
SELECT
DISTINCT
AS
聚合函数(COUNT() MAX() MIN() SUM() AVG())
FROM
WHERE
GROUP BY
规则:除聚合函数外 显示列必须是分组列
意义:目的为了聚合统计
HAVING
对聚合后的数据进一步筛选
ORDER BY
b.模糊查询
LIKE
通配符 % _
c.多表查询
"外键" 关系明确
分清主次 首选外联接
d.子查询
理论依据->前一次查询结果可以作为下次查询条件(等量代换)
伪列:
分页:
RowNum
特点:当数据查询产生后进行动态编号 从1开始
排序顺序
先查询完 [RowNum产生] 在排序
/*
scott 用例
dept 部门表
deptno 部门编号
dname 部门名称
loc 部门所在地
emp 员工表
empno 员工工号
ename 姓名
job 职位
mgr 直接上司工号
hiredate 入职时间
sal 底薪
comm 奖金
deptno 所在部门编号（外键）
*/
--基础sql准备
--常规用户登录
--请输入用户名：
username/password
system/admin
--SQL>
conn username/password
conn system/admin
--查看当前用户
show user;
--创建表空间
create tablespace tablespace_name datafile 'path' size [32k~128G] autoextend on/off;
create tablespace test datafile 'c:/test.dbf' size 1m autoextend on;
--创建用户
create user username identified by password default tablespace tablespace_name ;
create user lichee identified by admin default tablespace test;
--授登录和资源权限
grant connect,resource to username;
grant connect,resource to lichee;
--断开连接
disconnect
disconn
--常规用户登录
--请输入用户名：
username/password
lichee/admin
--SQL>
conn username/password
conn lichee/admin
--查看当前用户
show user;
--删除约束
--删除外键约束
alter table emp drop constraint fk_emp;
--删除主键约束
alter table emp drop constraint pk_emp;
alter table dept drop constraint pk_dept;
--删除表
drop table bonus;
drop table salgrade;
drop table dept;
drop table emp;
--创建bonus表
create table bonus (
ename varchar2(10 byte) null ,
job varchar2(9 byte) null ,
sal number null ,
comm number null
);
--创建dept表
create table dept (
deptno number(2) not null ,
dname varchar2(14 byte) null ,
loc varchar2(13 byte) null
);
--创建emp表
create table emp (
empno number(4) not null ,
ename varchar2(10 byte) null ,
job varchar2(9 byte) null ,
mgr number(4) null ,
hiredate date null ,
sal number(7,2) null ,
comm number(7,2) null ,
deptno number(2) null
);
--创建salgrade表
create table salgrade (
grade number null ,
losal number null ,
hisal number null
);
--插入数据dept表
insert into dept values ('10', 'accounting', 'new york');
insert into dept values ('20', 'research', 'dallas');
insert into dept values ('30', 'sales', 'chicago');
insert into dept values ('40', 'operations', 'boston');
--插入数据emp表
insert into emp values ('7369', 'smith', 'clerk', '7902', to_date('1980-12-17 00:00:00', 'yyyy-mm-dd hh24:mi:ss'), '800', null, '20');
insert into emp values ('7499', 'allen', 'salesman', '7698', to_date('1981-02-20 00:00:00', 'yyyy-mm-dd hh24:mi:ss'), '1600', '300', '30');
insert into emp values ('7521', 'ward', 'salesman', '7698', to_date('1981-02-22 00:00:00', 'yyyy-mm-dd hh24:mi:ss'), '1250', '500', '30');
insert into emp values ('7566', 'jones', 'manager', '7839', to_date('1981-04-02 00:00:00', 'yyyy-mm-dd hh24:mi:ss'), '2975', null, '20');
insert into emp values ('7654', 'martin', 'salesman', '7698', to_date('1981-09-28 00:00:00', 'yyyy-mm-dd hh24:mi:ss'), '1250', '1400', '30');
insert into emp values ('7698', 'blake', 'manager', '7839', to_date('1981-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'), '2850', null, '30');
insert into emp values ('7782', 'clark', 'manager', '7839', to_date('1981-06-09 00:00:00', 'yyyy-mm-dd hh24:mi:ss'), '2450', null, '10');
insert into emp values ('7788', 'scott', 'analyst', '7566', to_date('1987-04-19 00:00:00', 'yyyy-mm-dd hh24:mi:ss'), '3000', null, '20');
insert into emp values ('7839', 'king', 'president', null, to_date('1981-11-17 00:00:00', 'yyyy-mm-dd hh24:mi:ss'), '5000', null, '10');
insert into emp values ('7844', 'turner', 'salesman', '7698', to_date('1981-09-08 00:00:00', 'yyyy-mm-dd hh24:mi:ss'), '1500', '0', '30');
insert into emp values ('7876', 'adams', 'clerk', '7788', to_date('1987-05-23 00:00:00', 'yyyy-mm-dd hh24:mi:ss'), '1100', null, '20');
insert into emp values ('7900', 'james', 'clerk', '7698', to_date('1981-12-03 00:00:00', 'yyyy-mm-dd hh24:mi:ss'), '950', null, '30');
insert into emp values ('7902', 'ford', 'analyst', '7566', to_date('1981-12-03 00:00:00', 'yyyy-mm-dd hh24:mi:ss'), '3000', null, '20');
insert into emp values ('7934', 'miller', 'clerk', '7782', to_date('1982-01-23 00:00:00', 'yyyy-mm-dd hh24:mi:ss'), '1300', null, '10');
--插入数据salgrade表
insert into salgrade values ('1', '700', '1200');
insert into salgrade values ('2', '1201', '1400');
insert into salgrade values ('3', '1401', '2000');
insert into salgrade values ('4', '2001', '3000');
insert into salgrade values ('5', '3001', '9999');
--添加emp主键empno
alter table emp add constraint pk_emp primary key (empno);
--添加dept主键deptno
alter table dept add constraint pk_dept primary key (deptno);
--添加emp.deptno外键dept.deptno
alter table emp add constraint fk_emp foreign key (deptno) references dept (deptno);
---------------------------------------------------------
--设置显示长度
set linesize 200;
--设置显示行数
set pagesize 100;
--查询关键字
--请列举所有职位
select distinct job as 职位 from emp;
--请列举底薪在1000~2000之间的员工信息
select * from emp where sal between 1000 and 2000;
select * from emp where sal >= 1000 and sal <= 2000;
--请列举每个员工的薪水
--注意:任何数据与null运算结果为null
select 1+null from dual;
--函数 nvl(exp,value)
--如果exp为null 返回value 否则返回exp
select ename,sal+nvl(comm,0) as salary from emp;
--请列举没有奖金的员工信息
select * from emp where comm is null;
select * from emp where comm is not null;
--注意:误区"分组"
select * from emp order by job;
--数据库分组的规则
--除聚合函数外，显示列必须是分组列
--数据库分组的意义
--为了统计聚合
--请统计每个职位有多少人
select job,count(empno) from emp group by job;
--以下两种结果是不一样的
select job,deptno,count(empno) from emp group by job,deptno;
select deptno,job,count(empno) from emp group by deptno,job;
--请列举超过两人以上的职位
--where 对原始数据进行条件筛选
--having 对聚合后的数据进一步筛选
select job,count(empno) from emp group by job having count(empno)>2;
----------------------------------------------------------------
--模糊查询
--请列举工号第二位是3的所有员工
-- % 匹配任意长度字符
-- _ 匹配任意单个字符
select * from emp where empno like '_3%';
--请列举名字第二位是“l”或者“o”的所有员工
select * from emp where ename like '_l%' or ename like '_o%';
--请列举名字第二位不是“l”的所有员工
select * from emp where ename not like '_l%'
----------------------------------------------------------------
--多表查询 原则:首选外联接
--请列举所有员工姓名及其所属的部门名称
select ename,dname from emp,dept where emp.deptno = dept.deptno;
--员工必定所属部门
select ename,dname from emp left join dept on emp.deptno = dept.deptno;
--请列举所有部门名称及其对应的员工姓名
select dname,ename from emp,dept where emp.deptno = dept.deptno;
--部门未必有员工
--外联接特点：主表数据必须显示，如果没有字表数据，使用null值填充
select dname,ename from emp right join dept on emp.deptno = dept.deptno;
--请列举员工和其直属上司的名字
select e1.ename,e2.ename from emp e1 left join emp e2 on e1.mgr=e2.empno;
--请列举没有员工的部门
select dname,ename from emp right join dept on emp.deptno = dept.deptno where ename is null;
--分析员工表 deptno
--在员工表出现过的部门编号
select distinct deptno from emp;
--分析部门表
select * from dept where deptno not in (select distinct deptno from emp);
--关于分页
--每页5条记录 显示第二页
--知识点:伪列
--rowid 记录添加到表空间 系统唯一物理地址编码 不会因为数据变化而修改 通过该信息查找效率最快
--rownum 序号 根据查询结果而依次进行的编号 该编号与查询出现的记录顺序相关
select rowid,rownum,emp.* from emp;
select rowid,rownum,emp.* from emp where sal >1500;
--通过子查询固定rownum
select * from
(select rownum rn,emp.* from emp) where rn between 6 and 10;
--推荐 (自然排名分页)
select * from
(select rownum rn,emp.* from emp where rownum<=10) where rn >5;
--按sal降序排列 获取第二页
--排序规则 先按自然排名查询后在进行规则排序
select rownum,emp.* from emp order by sal desc;
select * from
(select rownum as rn,t.* from (select * from emp order by sal desc) t where rownum<=10)
where rn >5;
select * from
(select rownum as rn,t.* from (select * from emp order by sal desc) t)
where rn >5 and rn<=10;
select * from dept;
select * from emp;
--1) 查询一共有多少员工
select count(empno) from emp;
--2) 查询每个员工的工龄
select empno,ename,hiredate,floor(months_between(sysdate,hiredate)/12) from emp;
--3) 查询每个部门有多少人
select dept.deptno,dname,nvl(ct,0) from dept left join
(select deptno,count(empno) ct from emp group by deptno) e
on dept.deptno=e.deptno;
--4) 查询工号第三位为8的所有员工
select * from emp where empno like '__8%';
--5) 查询每个员工和直接上司
select e1.ename,e2.ename from emp e1 left join emp e2
on e1.mgr = e2.empno;
--6) 查询员工所对应的部门
select ename,dname from emp left join dept on emp.deptno = dept.deptno;
--7) 查询部门所对应的员工
select dname,ename from emp right join dept on emp.deptno = dept.deptno;
--8) 查询哪个部门没有员工
select * from dept where deptno not in
(select distinct deptno from emp);
--9) 统计哪个部门的平均工资超过2200
select * from dept where deptno in
(select deptno from emp group by deptno having avg(sal+nvl(comm,0))>=2200);
--10)统计每个职位有多少人并降序排列
select job,count(empno) ct from emp group by job order by ct desc;
------------------
--分页
--RowNum
--自然排序
select * from
(select rownum rn,emp.* from emp where rownum<=10)
where rn>5;
--自定义排序(通用)
--排序顺序
select * from
(select rownum rn,e.* from
(select emp.* from emp order by sal desc) e where rownum<=10)
where rn>5;
-------------------
--排序函数
select empno,ename,sal,
row_number() over(order by sal desc) rn1,
rank() over(order by sal desc) rn2,
dense_rank() over(order by sal desc) rn3
from emp;
--分组排序函数
select empno,ename,deptno,sal,
rank() over(partition by deptno order by sal desc) rn2
from emp;
--------------------
--笔试1
--学生考试成绩表 获取成绩排名第三的学生信息
--类似 底薪排名第三的员工信息
select * from
(select empno,ename,sal,
rank() over(order by sal desc) rn2
from emp) where rn2=3;
--笔试2
--现存一张表 (没有主键 有很多重复数据) 要求删除重复数据
/*
名称 名称
张三 张三
李四 -> 李四
王五 王五
张三
李四
张三
*/
create table tb_student
(
sname VARCHAR2(50)
);
INSERT INTO tb_student VALUES('张三');
INSERT INTO tb_student VALUES('李四');
INSERT INTO tb_student VALUES('王五');
INSERT INTO tb_student VALUES('张三');
INSERT INTO tb_student VALUES('李四');
INSERT INTO tb_student VALUES('张三');
commit;
delete from tb_student where rowid in
(select rowid from
(select sname,row_number() over(partition by sname order by sname) rn from tb_student)
where rn>1);
select * from tb_student;