表结构：

• 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增

1. 创建表

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';