—rowid并未存储在表中，所以不支持增删改操作，只能用于查询
select rowid,ename,sal,deptno from emp;
—员工表中工资最高的三位
select from
(
select from emp order by sal desc
)
where rownum<4;
—员工表中工资最高的4-6位
select from
(
select rownum as rn,a. from
(
select from emp order by sal desc
) a
)
where rn>=4 and rn<=6;
—约束
create table emp_bak(
empno number(4),
ename varchar2(10),
deptno number(7,2) not null,
constraint emp_empno_pk
primary key(empno));
create table huaminidept(
deptno number(2),
dname varchar2(14),
loc varchar2(13),
constraint dept_dname_uk unique(dname));
insert into huaminidept values(20,’教学部’,’南京’);
select from huaminidept;
—创建一个stu表（stuno(PR),stuname,deptno(FR)）
create table stu(
stuno number(4),
stuname varchar2(10),
deptno number(5),
constraint stu_stuno_pk primary key(stuno),
constraint stu_deptno_fk foreign key(deptno)
referenceS studept(deptno));

insert into stu values(2,’huna’,10,’男’);
insert into stu values(3,’huache’,20,’女’);
insert into stu values(4,’hun’,30,’男’);
select from stu;

—创建一个stusc表（stuno(PR),deptno(FR),score(NOT NULL)）
create table stusc(
stuno number(2),
deptno number(5),
score number(3) not null,
constraint stusc_stuno_pk primary key(stuno),
constraint stusc_deptno_fk foreign key(deptno)
referenceS studept(deptno));
insert into stusc values(1,null,70);
insert into stusc values(2,10,80);
insert into stusc values(3,20,60);
insert into stusc values(4,30,90);
select from stusc;
—创建一个系名表studept（deptno(PR),deptname(unique)）
create table studept(
deptno number(5),
deptname varchar2(13),
constraint studept_deptno_pk primary key(deptno),
constraint studept_deptname_uk unique(deptname));

insert into studept values(10,’教学部’);
insert into studept values(20,’销售部’);
insert into studept values(30,’综合部’);
select from studept;

—在stu表中增加性别gender列
alter table stu add(gender varchar(10),
constraint stu_gender_ck
check(gender in(‘男’,’女’)));
—将stu表中的stuname设为唯一的
alter table stu add(constraint stu_stuname_uk unique(stuname));
—查看表的约束(表名大写)类型
select constraint_name,constraint_type,search_condition
from user_constraints
where table_name=’STU’

—视图View
create or replace view emp_bak1
as select empno,ename,job,deptno
from emp
where deptno=10;

select from emp_bak1;
—视图的列名使用别名
create or replace view salvu30
as select empno employee_name,ename name,sal salary,deptno “部门编号”
from emp
where deptno=10;

select from salvu30;
—在USER_VIEWS中查看用户所有的视图
select from USER_VIEWS;
—创建视图包含了复杂条件
CREATE VIEW dept_sum_vu
(name, minsal, maxsal, avgsal)
AS SELECT d.dname, MIN(e.sal), MAX(e.sal),
AVG(e.sal)
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname;

select from dept_sum_vu;
create or replace view empvu20
as select
from emp
where deptno=20
with check option constraint empvu20_ck;

select from empvu20;
update empvu20 set ename=’SMITH’ where ename=’SMITH’;
—drop view 视图名称; 删除视图
drop view empvu20;
—队列
create sequence dept_deptno
increment by 1
start with 91
maxvalue 100
nocache
nocycle;
select from dept_deptno;
insert into dept values(41,’develop’,’NEW YORK’);
—创建表Book1表
create table book1(
bookId varchar2(4),
bookname varchar2(20),
CONSTRAINT book1_bookId_pk PRIMARY KEY(bookId));
—创建序列
create sequence book1_sequence start with 1 increment by 1;
SELECT book1_sequence.currval FROM DUAL;
—创建触发器
Create or replace trigger book1_trigger
before insert on book1
for each row
begin
select book1_sequence.nextval into :new.bookId from dual;
end ;
—添加数据
insert into book1(bookname) values (‘cc’);
insert into book1(bookname) values (‘dd’);
commit;
insert into book1(bookname) values (‘ee’);
insert into book1(bookname) values (‘ff’);
commit;
insert into book1(bookname) values (‘hello41’);
insert into book1(bookname) values (‘hello42’);
commit;
SELECT from book1;
select from stu;
—创建索引
create index stuind on stu(stuname);
—对emp表中的ename列创建索引
create index empinx on emp(ename);
select from user_indexes;
—查询某表哪些列有索引
SELECT ic.index_name, ic.column_name,
ic.column_position col_pos,ix.uniqueness
FROM user_indexes ix, user_ind_columns ic
WHERE ic.index_name = ix.index_name
AND ic.table_name = ‘EMP’;

—创建一个表为employee表，id（非空，PK），name（唯一），
—hiredate (默认系统时间),gender（check ,”男”,”女”，默认值为男）
create table employee(
id number(4) not null primary key,
name varchar2(14) unique,
hiredate date default sysdate,
gender varchar2(10) default(‘男’)
check(gender in(‘男’,’女’))
);

insert into employee(id,name) values(11,’seun’);
select from employee;

—创建用户
create user [username]
identified by [password];

—使用sysbda用登录，授权scott用户创建表，创建序列和视图的权限
GRANT create table, create sequence, create view TO scott;
—1.普通导出：exp scott/H123456@orcl file=’D:\back\orcl_back0313.dmp’
—2.导出所有数据库对象：exp sys/H123456@orcl file=’D:\back\orcl_back0313.dmp’ full=y （登录用户必须是DBA权限）
—3.导出特定用户下的表：exp scott/H123456@orcl file=’D:\back\orcl_back0313.dmp’ owne=
—4.导出特定的表：exp scott/H123456@orcl file=’D:\back\orcl_back0313.dmp’ tables=(emp_bak,dept_bak)

—作业

select from student;
select from teacher;
—1、查询Student表中的所有记录的Sname、Ssex和Class列。
select sname,ssex,class from student;
—2、查询教师所有的单位即不重复的Depart列。
select distinct depart from teacher;
—3、查询Student表的所有记录。
select from student;
—4、查询Score表中成绩在60到80之间的所有记录。
select from score where degree between 60 and 80;
—5、查询Score表中成绩为85，86或88的记录。
select from score where degree=85 or degree=86 or degree=88;
—6、查询Student表中“95031”班或性别为“女”的同学记录。
select from student where class=95031 or ssex like ‘女’;
—7、以Class降序查询Student表的所有记录。
select from student order by class desc;
—8、以Cno升序、Degree降序查询Score表的所有记录。
select from score order by cno,degree desc;
—9、查询“95031”班的学生人数。
select count() from student where class=95031;
—10、查询Score表中的最高分的学生学号和课程号。
select sno,cno from (
select from score order by degree desc
)
where rownum=1;
—11、查询‘3-105’号课程的平均分。
select avg(degree) from score where cno=’3-105’;
—12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
select cno,avg(degree) from score group by cno having cno like ‘3-%’ and count()>5;
—13、查询最低分大于70，最高分小于90的Sno列。
select sno from score group by sno having min(degree)>70 and max(degree)<90;
—14、查询所有学生的Sname、Cno和Degree列。
select a.sname,b.cno,b.degree from student a , score b where a.sno=b.sno;
—15、查询所有学生的Sno、Cname和Degree列。
select a.cname, b.sno,b.degree from course a , score b where a.cno=b.cno;
—16、查询所有学生的sname、cname和degree列。
select a.sname,b.cname,c.degree from student a ,course b,score c where a.sno=c.sno and b.cno =c.cno;
—17、查询“95033”班所选课程的平均分。
select avg(a.degree) from score a , student b where a.sno = b.sno and b.class=’95033’;
—18、假设使用如下命令建立了一个grade表：
—现查询所有同学的sno、cno和rank列。
create table grade1(low number(3,0),upp number(3),rank char(1));
insert into grade1 values(90,100,’A’);
insert into grade1 values(80,89,’B’);
insert into grade1 values(70,79,’C’);
insert into grade1 values(60,69,’D’);
insert into grade1 values(0,59,’E’);
commit;
select a.sno,a.cno,b.rank from score a,grade1 b where a.degree between b.low and b.upp order by rank;
—19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select a. from score a , score b where a.cno=’3-105’ and a.degree>b.degree and
b.sno=’109’ and b.cno=’3-105’;

—20、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录
select a.* from score a where a.cno=’3-105’ and a.degree>all(select degree from
score b where b.sno=’109’ and b.cno=’3-105’);