1.exists关键字 存在
与之对应的: no exists 不存在看如下案例
-- 和SMITH在同一个岗位,同一个部门的员工 select ENAME, DEPTNO, JOB from EMP where (DEPTNO, JOB) in (select DEPTNO, JOB from EMP where ENAME = 'SMITH'); -- 查询和smith在同一个岗位的员工 select m.ENAME, m.JOB from EMP m where exists( select 'X' from EMP c where c.ENAME = 'SMITH' and c.JOB = m.JOB ); `` exists和in的区别: in适合外表大而内表小的情况 exists适合外表小而内表大的情况2.普通函数
-- lower()小写 select lower('HelloWord') from dual; -- upper()大写 select upper('HelloWord') from dual; -- length() 字符长度个数 select length('你好啊?') from dual; -- substr('',startIndex,length) 从1开始算,从哪开始,要几个 select substr('你好啊?', 1, 2) from dual; -- 从1开始要,要2个 -- instr()获取字符串位置,找哪个字符,从第几个位置开始找 select instr('helloword', 'o', 6) from dual; -- replace()替换文本,将什么替换成什么,全部替换 select replace('helloword', 'o', '我是老虎') from dual; -- round()四舍五入,保留几位小数 select round(22.222, 2) from dual; -- trunc()截取数字或者日期(去掉时分秒),不改变数据类型 select trunc(sysdate), trunc(123.19344, 1) from dual; -- mod()取余数 select mod(12, 3) from dual; -- floor() 向下取整 select floor(12.9) from dual; -- ceil() 向上取整 select ceil(12.1) from dual; -- abs()取绝对值 select abs(-12.1) from dual; -- power() x的y次方 select power(2, 4) from dual; -- 获取当前日期:sysdate,systimestamp select sysdate, systimestamp from dual; --添加月份 SELECT add_months(SYSDATE, 2) FROM dual; --获取月的最后一天的日期 SELECT last_day(SYSDATE) FROM dual;练习案例:
-- 查找已经入职8个多月的员工 select * from EMP where sysdate >= add_months(HIREDATE, 8); -- 当前时间>入职时间+8个月 -- 显示满10年服务年限的员工的姓名和受雇日期 select ENAME, HIREDATE from EMP where sysdate >= add_months(HIREDATE, 12 * 10); -- 对于每个员工,显示其加入公司的天数 select floor(sysdate - HIREDATE) "入职天数", ENAME from EMP; -- 找出各月倒数第三天受雇的所有员工 select HIREDATE, ENAME from EMP -- last_day(hiredate)获取当前日期的最后一个月的最后一天 where last_day(HIREDATE) - 2 = HIREDATE; -- 查看language(oracle数据库的编码格式) select sys_context('USERENV', 'language') from dual;3.序列
序列sequence:就是一组数字的集合,有序数字,不重复,适合做表的主键的值
创建序列:
create sequence sq_dept
start with 50 --开始值50
increment by 10; – 每次递增10
nextval
– 第一次使用时必须sequence.nextval获取序列化初始值50
– 第二次==>第n次 上次值基础上+递增值返回
select sq_dept.nextval
from dual;
currval查询当前序列的值生成到几了
– sequence.currval 查询序列最后一次生成的值,它只能起查询作用,不会创建值
select sq_dept.currval
from dual;
补充:后续使用UUID 生成随机的32位字符,不重复,用来做主键列的值
4.视图
视图:就是一张虚表,降低sql查询的复杂度
细节:创建视图需要DBA权限
扩展如何让用户获取DBA权限
优点:
1.将复杂的查询保存视图,可以对最终用户屏蔽一定sql复杂度
2.可以通过视图查询数据的时候,可以对最终用户屏蔽底层表的结构和数据,安全性高
常问的问题:
视图会占用表空间?
不占用,视图不是直接存储数据,数据还是保存在原来的表中
视图可以加快查询吗?
不能,因为每次使用相当于重新执行一次
创建视图:
create view v_result as
select d.DEPTNO, d.DNAME, d.LOC, e.ENAME
from DEPT d,
EMP e
where d.DEPTNO = e.DEPTNO;
5.数据库的索引
<>, != ,is not null , or 都不走索引
oracle3类索引:
唯一索引:效率最高,应用在主键和唯一键上
位图索引:应用在有一定重复规则的数据字段上
b-tree:应用在不规则的数据的普通字段上
作用:
优点是提升查询效率
缺点是降低存储的效率
索引本身也占空间,需要控制数量,一般不超过3-5个人
索引:
–创建索引
create [BITMAP][UNIQUE] index 索引名称 on 表(字段);
–删除索引
drop index 索引名称;
6.行转列
有3种方法:现在讲的是静态行转列
通过:case when条件 then 结果1else 结果2 end
准备数据
-- 创建临时成绩表 create table temp_score ( id number primary key, -- 设置主键 sname varchar2(10), --学生名字 cname varchar2(10), -- 课程名 score number -- 成绩 ); select * from temp_score; insert into temp_score (id, sname, cname, score) VALUES (1, '赵云', '语文', 90); insert into temp_score (id, sname, cname, score) VALUES (2, '赵云', '数学', 80); insert into temp_score (id, sname, cname, score) VALUES (3, '赵云', '英语', 70); insert into temp_score (id, sname, cname, score) VALUES (4, '刘备', '语文', 90); insert into temp_score (id, sname, cname, score) VALUES (5, '刘备', '数学', 90); insert into temp_score (id, sname, cname, score) VALUES (6, '刘备', '英语', 90); insert into temp_score (id, sname, cname, score) VALUES (7, '诸葛亮', '语文', 100); insert into temp_score (id, sname, cname, score) VALUES (8, '诸葛亮', '数学', 100); insert into temp_score (id, sname, cname, score) VALUES (9, '诸葛亮', '英语', 100); insert into temp_score (id, sname, cname, score) VALUES (10, '张飞', '语文', 60); insert into temp_score (id, sname, cname, score) VALUES (11, '张飞', '数学', 50); insert into temp_score (id, sname, cname, score) VALUES (12, '张飞', '英语', 40); insert into temp_score (id, sname, cname, score) VALUES (13, '关羽', '语文', 70); insert into temp_score (id, sname, cname, score) VALUES (14, '关羽', '数学', 80); insert into temp_score (id, sname, cname, score) VALUES (15, '关羽', '英语', 50);
开始测试:
-- 1.查询出每个人的语文,数学,英语成绩 -- case when... then... else... end select sname, max(case when cname = '语文' then score else 0 end) 语文, max(case when cname = '数学' then score else 0 end) 数学, max(case when cname = '英语' then score else 0 end) 英语 from temp_score group by sname;
ps: 实现了行转列
案例扩展,找个单科成绩的最高的那个人
-- 思路1:找到了最高成绩,学科,再去成绩表找该条记录 select * from temp_score sco, ( -- 找到单科成绩最高,学科名 select cname, max(score) scoreMax from temp_score group by cname) sco_max -- 再去成绩表找该条记录 where sco.cname = sco_max.cname and sco.score = sco_max.scoreMax order by id asc; -- 思路2:先找到单科的最高分,再查询成绩表,找到最高分数的记录 select * from temp_score sco where score = ( -- 外部的成绩表的名字传入进来,然后内部的成绩表通过该名字找到该学科成绩的最大值 select max(score) from temp_score scoInner where scoInner.cname = sco.cname); -- 思路3:成绩表中,当学科一样的时候,不存在一条记录的 分数小于其他记录的分数 select * from temp_score sco where -- 当学科一样时,不存在外部的成绩表的成绩<内部表的成绩 == 也就是外部表的这个成绩最大(至多它等于内部表最大成绩) not exists(select * from temp_score scoInner where sco.cname = scoInner.cname and sco.score < scoInner.score) order by id asc;继续讲解case when…then… eles… end,可以传入多个when,也就是多个条件
-- 2.创建视图 create VIEW v_temp_score AS select sname, max(case when cname = '语文' then score else 0 end) 语文, max(case when cname = '数学' then score else 0 end) 数学, max(case when cname = '英语' then score else 0 end) 英语 from temp_score group by sname; -- 3.虚拟表测试 select case when 100 > 1 then 'pig1' when 'b' = 'b1' then 'pig2' when 'c3' = 'c' then 'pig3' else 'pig4' end case from dual; -- 3.2 select case 'a' when 'a' then 'pig1' when 'b' then 'pig2' when 'c' then 'pig3' else 'pig4' end case from dual;todo decode(条件1,值,返回值,…) 函数传入3的倍数个参数
-- todo decode(条件1,值,返回值,...) 函数传入3的倍数个参数 select decode('e', 'b', 'is a', 'd', 'is d', 'e', 'is e', 'not all') from dual; select decode('a', 'is a', 'not a') from dual;
