Oracle 11g 数据库 笔记（一）

SQLPLUS语句

登陆：sqlpus 用户名/密码
以dba权限登陆：sqlplus / as sysdba
切换用户：conn 用户名
切换到dba：conn / as sysdba
查看当前用户：show user
退出登录：exit
解锁用户：(dba权限下)alter user 用户名(scott) account unlock;
修改密码：(dba权限下)alter user 用户名(scott) identified by 新密码;
查看当前实例：select name from v$database;
切换到其他实例：sqlplus /@orcl as sysdba;
创建视图权限：(dba权限下)grant create any view to scott

常用表结构

系表：dep(dno char(3) 系代号, dname varchar2(20) 系名, tel number(8) 电话)

学生表：student(sno number(10) 学号, sname varchar2(8) 姓名, sex char(2) 性别, birth date 出生日期,
dno char(3) 系代号)

教师表：teacher(tno char(6) 工作证号, tname varchar2(8) 姓名, title varchar2(10) 职称, hiredate date 参加工作时间, sal number(8,2) 工资, bonus number(8,2) 奖金, mgr char(6) 上级领导, dno char(3) 系代号)

课程表：course(cno char(3) 课序号, cname varchar2(20) 课程名, credit number(1) 学分, tno char(6) 任课老师工作证号)

成绩表：sc(sno number(10) 学号, cno char(3) 课序号, grade number(3) 成绩)

职工表：emp(eno char(6) 工作证号, ename varchar2(8) 姓名, title varchar2(10) 职称, age number(2) 年龄, hiredate date 参加工作时间, sal number(8,2) 工资, bonus number(8,2) 奖金, mgr char(6) 上级领导,
deptno char(3) 部门代号)

部门表：dept(deptno char(3) 部门代号, dname varchar2(20) 部门名称, tel number(8) 电话)
产品表：it_products(it_no number(5) 产品编号, it_name varchar2(40) 产品名称, it_origin varchar2(20) 产地, price number(4) 价格, inventory number(4) 库存)

常用函数

dual：伪表，本身不存在，为了方便操作。例：select查询需要有特定的对象，select sysdate from dual;

abs(x)：返回x的绝对值

sqrt(x)：返回x的平方根

power(x,y)：返回x的y次幂 --select power(4,2) from dual;(16)

round(n,m)：将n四舍五入,保留小数点后m位 --select round(1234.5678,2) from dual;(1234.57)

trun(x[,y])：返回x按精度y截取后的值，如果y不为整数则截取y整数部分， 如果y>0则截取到y位小数， 如果y小于0则截取到小数点向左第y位，小数前其它数据用0表示。 --select trunc(3.1415926,3.2),trunc(2554, -2.6) from dual;(3.141 2500)

floor(x)：返回小于等于x的最大整数值 --select floor(3.1),floor(3.1+5.2),floor(0) from dual;(3 8 0)

ceil(x) ：返回大于等于x的最小整数值 --select ceil(3.1),ceil(3.1+5.2),ceil(0) from dual;(4 9 0)

upper('字符串'|列名称)：返回字符串或者列名,并将所有的字符或列名大写 --lower() 与之相反

to_char(x[[,c2],C3])：将日期或者数据转换成char指定格式 --select to_char(sysdate,'yyyy-mm-dd') from dual;(当前年-月-日) --select to_char(123456789,'999,999,999,999') from dual;(123,456,789)

months_between(d1,d2)：返回日期d1到日期d2之间的月数 --select months_between('19-12月-1999','19-3月-1999') mon_between from dual;

extract(c1 from d1)：返回日期特定部分 --select extract(hour from sysdate) from dual; C1:(YEAR、MONTH、DAY、HOUR、MINUTE、SECOND)

DDL语句

DDL（Data Definition Language，数据定义语言）： 用于定义数据的结构，比如 创建、修改或者删除数据库对象（表，视图等等）。
DDL包括：DDL语句可以用于创建用户和重建数据库对象。

下面是DDL命令：
CREATE TABLE：创建表
ALTER TABLE：修改表
DROP TABLE：删除表
CREATE INDEX
DROP INDEX

*实例-创建/删除表结构*

1.创建一张系表dep，其架构包括系代号dno（定长字符串3个字节）、系名dname（变长字符串20个字节）和电话tel（12位整数）
create table dep
(dno char(3),
dname varchar2(20),
tel number(12));

2.创建一张课程表course，其架构包括课序号cno（定长字符串3个字节）、课程名cname（变长字符串20个字节）、学分credit（1位整数）、上课时间ctime（日期时间型）和上课人数quota（3位整数）
create table course
(cno char(3),
cname varchar2(20),
credit number(1),
ctime date,
quota number(3));

3.创建一张选课表sc，其架构包括学号sno（10位整数）、课序号cno（定长字符串3个字节）和成绩grade（3位整数1位小数）
create table sc
(sno number(10),
cno char(3),
grade number(4,1));

4.删除课程表course
drop table course;

--快速复制一张表：create table student_tmp as select * from student where 1 = 2;(如果为True[1=1]则同时复制表结构和数据，否则只复制表结构)

*实例-动态修改表结构*

1.给职工表emp添加新列出生日期birth（日期时间型）
alter table emp add birth date;

2.将系表dep中系名dname的数据类型修改为变长字符串16个字节
alter table dep modify dname varchar2(16);

3.修改学生表student中birth的数据类型
alter table student modify birth timestamp;

4.修改课程表course中cname的列名
alter table course rename column cname to c_name;

5.修改学生表student的表名
rename student to s_student;

6.删除职工表emp中年龄age这一列
alter table emp drop column age;

*实例-创建表结构并定义约束*

约束用于确保数据库满足特定的规则，数据库不仅仅是存储数据，还必须保证所保存的数据的正确性，通过表中的列定义约束，就可以防止非法数据的操作问题。

1.创建一张教师表teacher，其架构包括工号tno（定长字符串10个字节）、姓名tname（变长字符串8个字节）、职称title（变长字符串10个字节）、入职时间hiredate（日期时间型）、工资sal（6位整数2位小数）、津贴bonus（6位整数2位小数）、上级领导工号mgr（定长字符串10个字节）、所在部门代号deptno（定长字符串3个字节），其中定义工号主码完整性约束，姓名和入职时间非空完整性约束，职称check完整性约束（范围是教授、副教授、讲师和助教），所在部门代号引用完整性约束，引用自系表dep的系代号dno列。
create table teacher
(tno char(10) constraint t1 primary key,
tname varchar2(8) constraint t2 not null,
title varchar2(10) constraint t3 check(title in ('教授','副教授','讲师','助教')),
hiredate date constraint t4 not null,
sal number(8,2),
bonus number(8,2),
mgr char(10),
deptno char(3) constraint t5 references dep(dno) on delete cascade);

2.在表最后添加约束，有两个主码时，不能同时在sno，cno写主码完整性约束
create table sc
(sno number(10),
cno char(3),
grade number(4,1),
constraint sc1 primary key(sno,cno));

3.引用完整性约束放在后面，需要加foreign key()
create table sc
(sno number(10),
cno char(3),
grade number(4,1),
constraint sc1 foreign key(sno) references student(sno));

*实例-修改约束*

1.修改student表，给sname添加非空完整性约束。
alter table student modify sname constraint s1 not null;
或者
alter table student add constraint s1 check(sname is not null);

2.修改dep表，给dno添加主码完整性约束，给dname添加非空完整性约束。
alter table dep add constraint d1 primary key(dno);
alter table dep modify dname constraint d2 not null;

3.修改emp表，给age添加基于属性完整性约束，要求age的值在18到60之间。
alter table emp modify age constraint e1 check(age between 18 and 60);
或者
alter table emp add constraint e1 check(age between 18 and 60);

4.给sc表的sno添加引用完整性约束，引用自表student的学号sno列。
alter table sc add constraint sc1 foreign key(sno) references student(sno) on delete set null;
--当删除学生表的对应的sno学生时，sc的sno将自动设置为null

alter table sc add constraint sc1 foreign key(sno) references student(sno) on delete cascade;
--当删除学生表的对应的sno学生时，sc的数据将整条删除

5.修改student表sname的约束名
alter table student rename constraint s1 to s2

6.修改teacher表，删除约束名为t2的约束。
alter table teacher drop constraint t2;

DML语句

DML（Data Manipulation Language，数据操作语言）：用于检索或者修改数据。
DML包括： SELECT（用于检索数据）；INSERT（用于增加数据到数据库）；UPDATE（用于从数据库中修改现存的数据 ）；DELETE（用于从数据库中删除数据）。

DML需要提交事务：commit; 回滚：rollback;

*实例-增加数据*

insert into 表名(列名，...) values(值，...)

1.给学生表student插入数据2018122101,李宏,男，01-1月-1980，d01。
insert into student values(2018122101,'李宏','男','01-1月-1980','d01');

2.给成绩表sc插入数据2018122101，c01。
insert into sc(sno,cno) values(2018122101,'c01');

insert into sc values(2018122101,'c01',null);

--批量添加数据：insert into student_tmp select * from student;(将旧表所有数据添加到新表)

*实例-修改数据*

update 表名 set 列名 = 值 where 条件表达式

1.给成绩表sc中学号为2018122101，课序号为c01这条记录输入成绩98分。
update sc set grade = 98 where sno = 2018122101 and cno = 'c01';

2.将课程表course中数据库技术这门课的学分改为4学分。
update coure set credit = 4 where cname = '数据库技术';

3.修改teacher表，给王莹教师的职称添加临时班主任。
update teacher set title = title || '/临时班主任' where tname = '王莹';

3.将教师表teacher中系代号为d02、职称为副教授的教师的工资增加500元，奖金增加100元。
update teacher set sal = sal+500,bonus = bonus+100 where dno = 'd02' and title = '副教授';
(修改多列用逗号隔开不是and)

*实例-删除数据*

delete from 表名 where 条件表达式

1.删除职工表emp中姓名为田中华这位职工的信息。
delete from emp where ename = '田中华';

2.将教师表teacher中系代号为d01的记录的系代号删除。
update teacher set dno = null where dno = 'd01';

*实例-简单查询数据*

select * from 表名 where 条件表达式

1.查询出所有学生的学号、姓名，所在系的系号
select sno,sname,dno from student;

2.查询出所有男学生的学号、姓名，所在系的系号
select sno,sname,dno from student where sex = '男';

3.查询教授和副教授的年收入，修改输出的列标题为工号、姓名、职称和年收入
select tno 工号, tname 姓名, title 职称, sal*12+nvl(bonus,0) 年收入 from teacher where title = '教授' or title = '副教授';

4.查询现有教师职称的种类
select distinct title from teacher;

5.查询课序号为c01的课程成绩为良好（80分到89分，包含80分和89分）的同学的学号
select sno from sc where cno = 'c01' and grade between 80 and 89;

6.查询有科目不及格的学生的学号
select distinct sno from sc where grade<60;

7.查询正在学习课序号为c01、c02、c03和c04这几门课的学生的学号
select distinct sno from sc where cno in('c01','c02','c03','c04') and grade is null;

8.查询课序号为c01、c02、c03和c04这几门课之外的课程成绩及格的学生的学号
select distinct sno from sc where cno not in('c01','c02','c03','c04') and grade> = 60;

9.查询姓名中有个田字的同学的信息
select * from student where sname like '%田%';

10.查询名字中有个田字的同学的信息
select * from student where sname like '_%田%';

11.根据用户输入的员工姓名查询该员工信息
select * from emp where ename = '&员工姓名';

'like'：模糊查询。 like：包含，not like：不包含。
% 表示0或者多个字符，_ 表示一个字符

*实例-排序查询*

order by (asc) 升序 order by desc 降序

1.从学生表student中查询学生信息，按年龄从高到低排序输出（这里的年龄不止要比较出生年份，而且要比较到出生的月份和日子）
select * from student order by birth;

2.从学生表student中查询男学生信息，按年龄从低到高排序输出（这里的年龄只比较出生年份，不比较到出生的月份和日子），年龄相同的按学号从高到低排序
select * from student where sex = '男' order by to_char(birth,'yyyy') desc,sno desc;

3.从教师表teacher中查询所有教授的工龄（工作了多少年），输出工号、姓名和工龄，按工龄从高到低排序输出，工龄相同按工号从低到高排序
select tno,tname,to_char(sysdate,'yyyy')-to_char(hiredate,'yyyy') from teacher where title = '教授' order by 3 desc,1;

*实例-多表连接查询*

1.查询物理系和化学系教师的信息，输出工号、姓名、职称和系名。
select tno,tname,title,dname from teacher,dep where teacher.dno = dep.dno and dname in('物理系','化学系');

2.查询陈洁有哪些科目不及格，输出课程名和成绩。
select cname,grade from course,sc,student where course.cno = sc.cno and sc.sno = student.sno and sname = '陈洁' and grade<60;

3.查询正在上大学英语的学生的信息，输出学号、姓名和系名。
select sc.sno,sname,dname from student,sc,course,dep where student.sno = sc.sno and student.dno = dep.dno and sc.cno = course.cno and cname = '大学英语' and grade is null;

4.查询物理系大学英语不及格学生的信息，输出学号、姓名和成绩。
select sc.sno,sname,grade from student,sc,dep,course where student.sno = sc.sno and student.dno = dep.dno and sc.cno = course.cno and dname = '物理系' and cname = '大学英语' and grade<60;

5.查询这学期有上课的物理系老师的工号和姓名。
select distinct teacher.tno,tname from teacher,dep,course,sc
where course.cno = sc.cno and teacher.tno = course.tno and teacher.dno = dep.dno and dname = '物理系' and grade is null;

6.查询正在给物理系学生上课的教师信息，输出教师的工号和姓名。
select distinct teacher.tno,tname from teacher,course,sc,student,dep where teacher.tno = course.tno and course.cno = sc.cno and sc.sno = student.sno and student.dno = dep.dno and dname = '物理系' and grade is null;

*实例-层次查询*

1.查询教师王莹直接下属的信息，输出工号和姓名。
select t1.tno,t1.tname
from teacher t1,teacher t2
where t1.mgr = t2.tno and t2.tname = '王莹';

2.查询教师王莹及其所有下属的信息，输出工号和姓名。
select tno,tname
from teacher
start with tname = '王莹'
connect by prior tno = mgr;

输出列层次关系：level

*实例-集合运算查询*

1.查询同时选修了英语和日语两门课的学生，输出学号和姓名。
select sc.sno,sname
from student,sc,course
where student.sno = sc.sno and sc.cno = course.cno and cname = '英语'
intersect --交
select sc.sno,sname
from student,sc,course
where student.sno = sc.sno and sc.cno = course.cno and cname = '日语';

2.查询正在选修英语、但从未选修日语的学生，输出学号和姓名。
select sc.sno,sname
from student,sc,course
where student.sno = sc.sno and sc.cno = course.cno and cname = '英语' and grade is null
minus --差
select sc.sno,sname
from student,sc,course
where student.sno = sc.sno and sc.cno = course.cno and cname = '日语';

3.假如外语只开设了英语和日语。查询当前至少上着一门外语课的学生，输出学号和姓名。
select sc.sno,sname
from student,sc,course
where student.sno = sc.sno and sc.cno = course.cno and cname = '英语' and grade is null
union --并
select sc.sno,sname
from student,sc,course
where student.sno = sc.sno and sc.cno = course.cno and cname = '日语' and grade is null;

*实例-分组查询*

select column,group_function from table [where condition] [group by group_by_expression] [having group_condition] [order by column];

1.查询教师年收入总额、平均年收入、最高年收入、最低年收入以及教师人数，将列标题用中文输出
select sum(sal12+nvl(bonus,0)) 年收入总额,avg(sal12+nvl(bonus,0)) 平均年收入,max(sal12+nvl(bonus,0)) 最高年收入,min(sal12+nvl(bonus,0)) 最低年收入,count(tno) 教师人数
from teacher;

2.查询这学期上课的学生人数
select count(distinct sno)
from sc
where grade is null;

3.查询物理系工龄最长的教师的工龄
select max(extract(year from sysdate)-extract(year from hiredate))
from teacher, dep
where teacher.dno = dep.dno and dname = '物理系';

4.查询每个系的教师人数，输出系名和人数
select dname,count(tno)
from dep,teacher
where dep.dno = teacher.dno
group by dname;

5.查询每个系这学期上课的学生人数，输出系代号、系名和人数
select dep.dno,dname,count(distinct sc.sno)
from dep,sc,student
where dep.dno = student.dno and student.sno = sc.sno and grade is null
group by dep.dno,dname;

6.统计每个系参加工作超过20年的教师的平均月工资，输出系名和平均月工资
select dname,avg(sal)
from teacher,dep
where teacher.dno = dep.dno and (to_char(sysdate,'yyyy')-to_char(hiredate,'yyyy'))>20
group by dname;
或者
select dname,avg(sal)
from teacher,dep
where teacher.dno = dep.dno and (extract(year from sysdate)-extract(year from hiredate))>20
group by dname;
或者
select dname,avg(sal)
from teacher,dep
where teacher.dno = dep.dno and (months_between(sysdate,hiredate)/12)>20
group by dname;

7.查询已获得学分大于15分的学生，输出学号，姓名和学分
select sc.sno,sname,sum(credit)
from sc,student,course
where sc.sno = student.sno and sc.cno = course.cno and grade> = 60
group by sc.sno,sname
having sum(credit)>15;

8.查询数据库技术最高分超过85分的系，男女生分开统计，输出系名、性别、最高分
select dname,sex,max(grade)
from student,sc,course,dep
where student.sno=sc.sno and sc.cno=course.cno and student.dno=dep.dno and cname='数据库技术'
group by dname,sex
having max(grade)>85;
或者
select dname,sex,max(grade)
from dep,student,sc,course
where dep.dno=student.dno and student.sno=sc.sno and sc.cno=course.cno and cname='数据库技术' and grade>85
group by dname,sex;

9.查询各个系的同学正在选修的课程数，输出课程数超过3的系名和课程数
select dname,count(distinct cno)
from dep,sc,student
where dep.dno=student.dno and student.sno=sc.sno and grade is null
group by dname
having count(distinct cno)>3;

*实例-子查询*

1.统计每个系的信息和学生人数
select d.*,(select count(*) from student where dno = d.dno) from dep d;

2.查询数据库技术成绩大于85分的女学生的名单，输出学号和姓名（要求七种写法）
第一种：
select sc.sno,sname
from student,sc,course
where student.sno = sc.sno and course.cno = sc.cno and cname = '数据库技术' and grade>85 and sex = '女';
第二种：
select sc.sno,sname
from student,sc,course
where student.sno = sc.sno and sc.cno = course.cno and sc.cno in
(select cno
from course
where cname = '数据库技术') and grade>85 and sex = '女';
第三种：
select sc.sno,sname
from student,sc,course
where student.sno = sc.sno and sc.cno = course.cno and sc.cno in
(select cno from course where cname = '数据库技术') and grade>85 and student.sno in
(select sno from student where sex = '女');
第四种：
select sno,sname
from student
where sex = '女' and sno in
(select sno from sc where grade>85 and cno in
(select cno from course where cname = '数据库技术'));
第五种：
select sno,sname
from student
where sex = '女'
intersect
select sc.sno,sname
from student,sc,course
where student.sno = sc.sno and course.cno = sc.cno and cname = '数据库技术' and grade>85;
第六种：
select sno,sname
from student
where sex = '女'
intersect
select sno,sname
from student
where sno in
(select sno
from sc,course
where course.cno = sc.cno and cname = '数据库技术' and grade>85);
第七种：
select sno,sname
from student
where sex = '女'
intersect
select sno,sname
from student
where sno in
(select sno
from sc
where cno in
(select cno
from course
where cname = '数据库技术') and grade>85);

3.查询没有被物理系学生选修过的课程的课序号和课程名（要求两种写法）
第一种：
select cno,cname
from course
where cno not in
(select cno
from sc
where sno in
(select sno
from student
where dno =
(select dno
from dep
where dname = '物理系')));
第二种：
select cno,cname
from course
minus
select sc.cno,cname
from course,student,sc,dep
where student.sno = sc.sno and course.cno = sc.cno and dname = '物理系' and student.dno = dep.dno;

4.查询各系数据库技术课程成绩大于85分的年纪最小（精确到日）的学生名单，输出系名，学生姓名和出生日期（要求三种写法）
第一种：
select dname,sname,birth
from student,dep,course,sc
where student.dno = dep.dno and course.cno = sc.cno and sc.sno = student.sno and cname = '数据库技术' and grade>85 and(dep.dno,birth) in
(select dno,max(birth)
from student
where sno in
(select sno
from sc
where grade>85 and cno in
(select cno
from course
where cname = '数据库技术'))
group by dno);
第二种：
select dname,sname,birth
from student,dep,sc
where student.dno = dep.dno and sc.sno = student.sno and cno in
(select cno
from course
where cname = '数据库技术') and grade>85 and (dep.dno,birth) in
(select dno,max(birth)
from student
where sno in
(select sno
from sc
where grade>85 and cno in
(select cno
from course
where cname = '数据库技术'))
group by dno);
第三种：
select dname,sname,birth
from student,dep,course,sc
where student.dno = dep.dno and course.cno = sc.cno and sc.sno = student.sno and cname = '数据库技术' and grade>85 and(dep.dno,birth) in
(select dno,max(birth)
from student,sc,course
where student.sno = sc.sno and sc.cno = course.cno and grade>85 and cname = '数据库技术'
group by dno);

4.查询各系年纪最小（只考虑年）且其数据库技术课程成绩大于85分的学生名单，输出系名、学生姓名和年龄（要求两种写法）
第一种：
select dname,sname,extract(year from sysdate)-extract(year from birth)
from dep,student
where dep.dno = student.dno and (dep.dno,extract(year from sysdate)-extract(year from birth)) in
(select dno,min(extract(year from sysdate)-extract(year from birth))
from student
group by dno)
intersect
select dname,sname,extract(year from sysdate)-extract(year from birth)
from dep,student
where dep.dno = student.dno and sno in
(select sno
from sc
where grade>85 and cno in
(select cno
from course
where cname = '数据库技术'));
第二种：
select dname,sname,extract(year from sysdate)-extract(year from birth)
from student,dep,sc,course
where student.dno = dep.dno and student.sno = sc.sno and sc.cno = course.cno and (extract(year from sysdate)-extract(year from birth),dep.dno) in
(select min(extract(year from sysdate)-extract(year from birth)),dno
from student
group by dno) and grade>85 and cname = '数据库技术';

5.查询数据库技术成绩最高的学生的信息，输出学号、姓名和成绩
select sc.sno,sname,grade
from sc,student
where sc.sno=student.sno and grade=(select max(grade) from sc,course where sc.cno=course.cno and cname='数据库技术') and cno in (select cno from course where cname='数据库技术');