Oracle总复盘（1）——SQL基础
表结构：
- stu表（学号，姓名，年龄，性别）
create table stu( sno varchar2(10) primary key, sname varchar2(20), sage number(2), ssex varchar2(5) ); insert into stu values ('s001','张三',23,'男'); insert into stu values ('s002','李四',23,'男'); insert into stu values ('s003','吴鹏',25,'男'); insert into stu values ('s004','琴沁',20,'女'); insert into stu values ('s005','王丽',20,'女'); insert into stu values ('s006','李波',21,'男'); insert into stu values ('s007','刘玉',21,'男'); insert into stu values ('s008','萧蓉',21,'女'); insert into stu values ('s009','陈萧晓',23,'女'); insert into stu values ('s010','陈美',22,'女'); commit;
- teacher表（教师编号，教师姓名）
create table teacher( tno varchar2(10) primary key, tname varchar2(20) ); insert into teacher values ('t001', '刘阳'); insert into teacher values ('t002', '谌燕'); insert into teacher values ('t003', '胡明星'); commit;
- course表（课程号，课程名，教师编号）
create table course( cno varchar2(10), cname varchar2(20), tno varchar2(20), constraint pk_course primary key (cno,tno) ); insert into course values ('c001','J2SE','t002'); insert into course values ('c002',' Java Web','t002'); insert into course values ('c003','SSH','t001'); insert into course values ('c004',' Oracle ','t001'); insert into course values ('c005','SQL SERVER 2005','t003'); insert into course values ('c006','C#','t003'); insert into course values ('c007',' JavaScript ','t002'); insert into course values ('c008','DIV+ CSS ','t001'); insert into course values ('c009',' PHP ','t003'); insert into course values ('c010','EJB3.0','t002'); commit;
- sc表（学生编号，课程编号，成绩）
create table sc( sno varchar2(10), cno varchar2(10), score number(4,2), constraint pk_sc primary key (sno,cno) ); insert into sc values ('s001','c001',78.9); insert into sc values ('s002','c001',80.9); insert into sc values ('s003','c001',81.9); insert into sc values ('s004','c001',60.9); insert into sc values ('s001','c002',82.9); insert into sc values ('s002','c002',72.9); insert into sc values ('s003','c002',81.9); insert into sc values ('s001','c003','59'); commit;
一、简单查询
1、查询平均成绩大于60 分的同学的学号和平均成绩；
-- 涉及字段：sc表（成绩、学号） -- 思路：需要结合avg+group by+having select sno,avg(score) from sc group by(sno) having avg(score) >60;
2、查询所有同学的学号、姓名、选课数、总成绩；
-- 涉及字段：sno、sname、cno、score -- 涉及表：stu、sc -- 思路：连接查询+count(cno) sum(score)+group by(sno) select stu.sno,sname,count(cno),sum(score) from stu,sc where stu.sno=sc.sno group by(stu.sno,sname);
3、查询姓“刘”的老师的个数；
select count(*) from teacher where tname like '刘%';
4、查询男生、女生人数
#方法一： select count(*) from stu where ssex='男' union all select count(*) from stu where ssex='女'; #方法二： select sum(case when ssex = '男' then 1 else 0 end) 男生人数 , sum(case when ssex = '女' then 1 else 0 end) 女生人数 from stu;
5、查询姓“张”的学生名单
select * from stu where sname like '张%';
6、1981 年出生的学生名单
select * from stu where to_char(sysdate,'yyyy')-sage='1998';
7、查询每门课程的平均成绩，结果按平均成绩升序排列
select cno,avg(score) from sc group by(cno) order by avg(score) ;
8、查询课程名称为“Oracle”，且分数低于60 的学生姓名和分数
--涉及表 course（cno，cname），sc（sno，cno，score），stu（sname） --思路：子查询，从课程表中找到cno，从sc表中根据cno，score找到sno，根据sno找到学生表中的sname； select sname from stu where sno=( select sno from sc where cno=( select cno from course where cname=' Oracle ') and score<60;)
9、查询课程编号为c001 且课程成绩在80 分以上的学生的学号和姓名；
select sc.sno,sname from stu join sc on stu.sno=sc.sno where cno='c001' and score>80;
10、查询每门课程被多少学生选修，输出课程名和选修人数
select cname,nvl(b.total,0) from course a left join (select cno,COUNT(sno) total from sc group by cno) b on a.cno=b.cno;
11、查询每门课程的最高分
select cno,max(score) from sc group by CNO;
二、复杂查询（连接查询、子查询）
1、查询“c001”课程比“c002”课程成绩高的所有学生的学号；
-- 涉及字段：课程号cno、成绩score、学号sno -- 需要的表：sc -- 思路1：先查出c001的各学生成绩 as a，再查出c002各学生的成绩 as b，通过学号相连接后，过滤出a.score>b.score select a.sno from (select * from sc where cno='c001') a, (select * from sc where cno='c002') b where a.sno=b.sno and a.score>b.score; -- 思路2：自连接，生成表a和表b，a表中显示c001的成绩，b表中显示c002的成绩，然后条件过滤。 select * from sc a join sc b on a.sno=b.sno where a.cno='c001' and b.cno='c002' and a.score>b.score;
2、查询没学过“谌燕”老师课的同学的学号、姓名；
-- 涉及字段：学生表（sno、sname），教师表（tname）、sc（选课情况），课程表(cno) -- 思路：嵌套子查询 -- 1.查询"谌燕"所教的课程号cno (c001 , c002 , c007 , c010) -- select cno from course a,teacher b where a.tno=b.tno and tname='谌燕'; ->将作为第二步的子查询 --2.查询sc表中已经选修过"谌燕"老师课程的学生学号 -- select distinct(sno) from sc where cno in (select cno from course a,teacher b where a.tno=b.tno and tname='谌燕'); -- 核心select sno from sc where cno in （c001 , c002 , c007 , c010），结果为(s001,s002,s003,s004) --3.上一步的结果说明该四个学号的学生选修过"谌燕"老师课程，在stu表中查找sno not in (s001,s002,s003,s004)，即为没学过的 select sno,sname from stu where sno not in ( select distinct(sno) from sc where cno in ( select cno from course a,teacher b where a.tno=b.tno and tname='谌燕' ));
3、查询选修“谌燕”老师所授课程的学生中，成绩最高的学生姓名及其成绩
-- 涉及表：teacher表（tname），sc（选课信息），stu表（sname） select * from ( select sno,score from teacher a,course b,sc c where a.tno=b.tno and b.cno=c.cno and tname='谌燕' order by score desc) where rownum=1;
4、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；
-- 涉及表：学生表(sno,sname) ，选课表（sno,cno） -- 思路：sc表自连接，得到有两个cno，条件过滤同时满足c001和c002的学生学号； 再连接stu表，找到学号对应的姓名。 select a.sname,b.sno from stu a, (select a.sno,a.cno,b.cno from sc a,sc b where a.sno=b.sno and a.cno='c001' and b.cno='c002') b where a.sno=b.sno;
三、单行函数
| 分类 | 函数 | 含义 |
|---|---|---|
| 大小写 | UPPER | 转大写 |
| LOWER | 转小写 | |
| INITCAP | 首字母大写 | |
| 字符串函数 | CONCAT | 字符串拼接 |
| SUBSTR('String',1,3) | 字符串截取 | |
| LENGTH | 字符串长度 | |
| INSTA('String','r') | 某字符在字符串中的位置 | |
| TRIM('s' from 'smisth') | 从字符串中抽离/删除指定字符 | |
| 数字函数 | ROUND(3.195,2) | 四舍五入保留n位小数 |
| TRUNC(3.195,2) | 去尾法保留n位小数 | |
| MOD（a,b） | 求模 | |
| 日期函数 | MONTHS_BETWEEN | 两个日期之间的月数 |
| ADD_MONTHS | 为一个日期增加月份 | |
| NEXT_DAY ('01-SEP-95','FRIDAY') | 以xxx日期为基准，查询下个星期五是几月几号 | |
| LAST_DAY(SYSDATE） | 本月最后一天 | |
| NVL函数 | NVL(expr1, expr2) | 如果expr1为null,则返回expr2,否则返回expr1 |
四、基本DDL
4.1 create增
- 创建表
create table t1(id int,name varchar2()); -- 默认存放在users表空间 create table t1(id int,name varchar2()) tablespace testtbs; -- 指定表空间 create table t1 as select * from t2; -- 查询创建表
2. 创建索引
create index idx_name on test(name); -- 为test表中的name字段创建索引
3. 创建视图
create view test_view as select * from test;
4. 创建同义词
-- 私有同义词syn1=scott.dept create synonym syn1 for scott.dept;
-- 共有同义词syn2=scott.emp create public synonym syn2 for scott.emp;
5. 创建序列
-- 起始100，递增序列 create sequence seq_empno start with 100 increment by 1 cache 50;
4.2 drop删
drop table test; -- 删除表 drop index idx_name; -- 删除索引 drop view test_view; -- 删除视图 drop synonym syn1; -- 删除同义词 drop sequence seq_empno; -- 删除序列
4.3 alter table
-- 1.修改某一列的名称 alter table DEPT **rename column** deptno to deptnum; -- 3.修改表名 alter table DEPT** rename to** department; -- 2.修改某一列的数据类型 alter table department **modify **deptnum int; -- 4.增加一列 alter table department **add** abc varchar2(20); -- 5.删除一列 alter table department **drop column** abc; -- 6.添加主键约束 alter table stu add constraint PK_ID primary key(id); -- 7.添加外键约束 alter table course add constraint FK_tno foreign key(tno) references teacher(tno); -- 8.非空约束 alter table stu modify sno not null; -- 9.唯一约束 alter table stu add constraint UNK_SNAME unique(sname); -- 10.检查约束 alter table stu add constraint CK_SEX check(sex in ('男','女')) -- 查看所有约束 select * from user_constraints;
五、基本DML
insert、update、delete....
七、索引管理
1. Oracle中索引有哪些类型？
B树索引：唯一索引、组合索引、反向索引、基于函数的索引
位图索引
2. 如何查看索引信息？
select * from user_indexes; -- 只能查看到有哪些索引，是什么类型，但无法知道是在哪张表的哪些列上建立的索引 select * from user_ind_columns; -- 具体到列
3.重建索引
当删除表中数据的时候，数据是删除了，但是索引还在；如图，理想状态是数据删了，对应的父节点索引也不需要了。频繁的删除会产生大量的碎片，执行 alter index IDX_NAME rebulid; 重建索引。
4.唯一索引
建立在unique列之上的索引；Oracle自动在表的主键列上创建唯一索引； create unique index IDX_UNK_SNAME on stu(sname);
5. 组合索引
建立在多列之上的索引；适合建立在where语句中常常使用的多个条件列上。 create index IDX_COMP_CLASSNO_SNAME on stu (classno,sname);
6. 什么是反向索引？
应用场景：工号1001，1002，1003，1004，1005... 建立索引后，全部都集中在一个叶子节点上； 使用反向索引得到，1001，2001，3001，4001，5001... 可以分散在各个叶子节点上；
create index IDX_REV_ID on dept( id ) REVERSE;
八、用户与权限管理
1. 创建用户
create user loto identified by '123456';
2. 什么是概要文件？
概要文件用于口令限制、资源限制；包含口令的有效期、锁定时间、会话时间限制、失败次数限制等；如果创建用户的时候没有指明概要文件，则默认使用Oracle 的default概要文件；也可以单独为用户指定概要文件；
创建概要文件
create profile PROC limit failed_login_attempts 2 password_lock_time 2;
改变用户的概要文件
alter user loto profile PROC
查看用户所使用的概要文件是哪些
select uesrname,profile from dba_users;
3. 锁定/解锁用户
alter user scott account lock alter user scott seaccount unlock
4. 删除用户
drop user loto cascade; --加了cascade就可以把用户连带的数据全部删掉。
5. 系统权限与对象权限
系统权限：create session、create table、create any table等等
对象权限举例：
grant select,update on scott.emp to loto; --允许loto查看、修改scott用户的emp表 grant all on scott.emp to loto; -- 授予全部权限 grant update(empname,sal) on scott.emp to loto; -- 授予权限精确到列
6. 权限的级联授予与撤回
系统权限：with admin option 不会级联回收 对象权限：with grant option 会级联回收
7. 角色
create role develop; grant xxx,xxx,xxx to develop; grant develop to loto,nancy,mike;
8. 查看当前用户具备哪些权限
select * from session_privs;
9. 查看xxx用户的权限
select * from dba_sys_privs where grantee='LOTO';
