select empno 员工编号, ename "员工姓名",
job as 工作, mgr as "领导编号" from emp
as可以省略,双引号在有特殊字符时不能省略。
2、查询连接符
select empno || '的姓名是' || ename as "信息" from emp;
--单字段去重
select distinct job from emp
--多字段去重,两个字段的值都相同才去除
select distinct job mgr from emp
--单字段排序
select * from emp order by empno asc
--多字段排序
--先按照之前的字段排序,之前的字段值相同的时候再按照之后的排序
select * from emp order by ename, job desc
在select与from之间的字段可以进行逻辑运算
--查询出来的工资加100, 工资加奖金两个字段值相加
select ename, sal+100,sal+comm from emp
select * from emp where ename like '%S%' --包含S的
select * from emp where ename like 'S%' --以开头是S的
select * from emp where ename like '_S%' --第二个字符是S的
select * from emp where ename like '%/_%' escape '/'
--包含_的, /变为转义字符
--查询平均工资
select avg(sal) from emp;
--查询公司有多少工作种类
select count(distinct job) from emp;
字符转数字:
--to_number(数值类型的字符) 把字符转换为数值
select to_number('123')+2 from emp
数字转字符:
字符转日期:
日期转字符:
在java程序中,一个字符串类型日期往数据库保存时使用to_date()函数,从数据库中取使用to_char()函数来获取字符串类型的日期。
--查询不同部门的最高工资
select deptno, max(sal) from emp group by deptno;
分组之后select与from之间只能出现分组字段和多行函数
多分组查询:
--查询不同部门不同工作的人数
select deptno, job, count(*) from emp
group by deptno,job
多分组查询先按照第一个字段分组查询,然后按照第二个字段在已分完组的 基础上再分组
having子句:
--查询不同部门不同工作的人数并且人数大于1的信息
select deptno, job, count(*) from emp
group by deptno,job having count(*) > 1;
having必须结合group by一起使用,针对的是分完组之后查询出来的结果集 再进行删选,可以使用多行函数。
10、92联合查询
笛卡尔集:
select * from emp, dept;
select * from emp e,dept d where e.depno = d.depno
不等值连接:
--查询员工工资等级
select * from emp e, salgrade s
where e.sal >= s.losad and e.sal <= s.hisal
--查询上级领导姓名,查询不能为*
select e1.name from emp e1, emp e2
where e1.mgr = e2.empno
11、99联合查询
笛卡尔集:
select * from emp cross join dept;
自然连接:
--先做笛卡尔积,然后按照所有同名同值字段进行等值筛选。
select * from emp natural join dept;
如果有多个同名同值字段想要按照一个字段进行筛选使用using关键字:
select * from emp inner join dept using(deptno);
如果字段名不同,但是值相同进行等值筛选:
select * from emp inner(可以省略) join dept
on emp.deptno = dept.deptno;
--on关键字仅仅来设置等值条件,尽量不要添加其他的筛选条件
外连接:
--左外连接
--查询员工姓名,工作,部门名称以及没有部门的员工信息
select * from emp e left join
dept d on e.deptno = d.deptno
--右外连接
--查询员工姓名,工作,部门名称以及没有员工的部门信息
select * from emp e right join
dept d on e.deptno = d.deptno
--查询员工姓名,工作,部门名称以及
--没有员工的部门信息和没有部门的员工信息
select * from emp e full outer join
dept d on e.deptno = d.deptno
12、三表联合查询
题目:查询员工信息及部门名称及所在城市名称并且员工工资大于2000或者有奖金
92方式:
select * from emp e,dept d,city c where
(e.deptno = d.deptno and d.loc =
c.loc and sal > 2000)
or (e.deptno = d.deptno and
d.loc = c.loc and e.comm is not null)
--方便书写,阅读困难
99方式:
select * from emp e
inner join dept p on e.deptno = p.deptno
inner join city c on d.loc = c.loc
where e.sal > 2000 or e.comm is not nuill
--书写麻烦,关键词多,阅读方便
在多表联合查询中,99方式要好于92方式。
13、子查询
单行子查询:
什么时候使用:查询条件不明确,使用单行子查询, 子查询出来的数据只能是一个字段。
--查询所有比雇员“clack”工资高的员工信息
select * from emp where sal >
(select sal from emp where name = "clack")
--查询工资高于平均工资的员工信息
select * from emp where sal >
(select avg(sal) from emp)
--查询和soctt属于同一个部门并且工资低于他的员工
select * from emp where deptno =
(select deptno from emp where name = "soctt")
and sal < (select sal from emp where name = "soctt")
--查询工资最高的员工
select * from emp where sal =
(select max(sal) from emp)
多行子查询:
子查询的结果只有一个字段但是字段有n个值 考虑使用多行子查询。
--查询工资高于任意一个clerr的所有员工
select * from emp where sal >
any (select sal from emp where job = "clerr")
--相当于 select * from emp where sal >
(select min(sal) from emp where job = "clerr")
--查询所有工资高于saleman的员工信息
select * from emp where sal >
all(select sal from emp where name = "saleman")
--相当于 select * from emp where sal >
(select max(sal) from emp where name = "saleman")
14、练习题
--列出所有部门的详细信息和部门人数
select * from dept d,
(select deptno, count(e.empno) from emp e, dept d
where e.deptno(+) = d.deptno group by deptno) s
where d.deptno = s.deptno
--平均工资大于2000的部门的详细信息
select * from dept d,
(select deptno, avg(sal) from emp group by deptno) s
where d.deptno = s.deprno and sal > 2000
15、分页查询
--查询员工信息的6-10条数据,第二页
select rownum, t.* from
(select rownum r, e.* from emp e
where rownum <= 10) t where r > 5
--每页显示m条数据,查询第n页
select * from (select rownum r, e.* from 表名 e
where rownum <= m*n) t where r > m*n-m
--分页查询员工信息按照工排序,显示第二页数据
select * from (select rownum r, t.* from
(select * from emp order by sal) t where
rownum <= 10) where r > 5
15、约束
create table students(
sno number(10) primary key, --主键约束
sname varchar2(100) not null, --非空约束
sage number(3) check(sage <150 and sage > 0), --检查约束
ssex char(4) check(ssex='男' or ssex='女')
sqq varchar2(30) unique, --唯一约束
cno number(10) references clazz(cno) --外键约束,防止添加不存在班级编号
--constraints pk_students_sno primary key(sno)
--constraints ck_students_sname check(sname is not null)
--constraints ck_students_sage check(sage<150 and sage>0)
--constraints un_students_sqq unique(sqq)
--constraints fk_students_cid foreign key(cno) reference clazz(cno)
)
外键一般选取父表的主键作为字表的外键,无法直接删除父表数据,除非级联删除。
级联删除:
添加外键时添加 on delete cascde,当删除父表数据时,会把关联的字表数据删除,所以最好使用 on delete set null,删除父表数据时,将子表的依赖字段的值设置为null。
16、尾巴
再过一遍oracle数据库相关知识,把之前薄弱的和没有学习到的记录下来,方便以后再复习。