oracle数据库

又是在华清远见培训快乐学习知识的一天！今天我总结了关于oracle数据库的相关知识点及练习题。

• 目录

  一、plsql登录

  二、命令行（cmd）登录

  二、命令行（cmd）登录

一、plsql登录

create user cui identified by cxy;                    //system

grant connect to cui;                                       //system,链接权限

grant resource to cui;                                      //system,资源权限，可以增删改查操作

create table table_test (id int primary key);     //cui

insert into table_test(1);                                   //cui

select * from table_test;                                   //cui

二、命令行（cmd）登录

conn fang/fjy;   //成功

alter user Fang identified by hqyj;   //更改账号密码

conn Fang/hqyj;   //成功

conn system/root;   drop user Fang cascade; //级联查询（如果有表要用级联查询）--成功

alter user fang account lock;   //上锁

conn fang/hqyj;   //失败

alter user fang account unlock;      //解锁

conn fang/hqyj;   //成功

conn system/root;  select * from dba_users;  //查询

三、命令行（cmd）登录

1、题目：创建三个表，并插入数据

表：

student

id                  int 主键

name            varchar

gender          varchar

birthday         date

course

id                 int 主键

name           varchar

grade

id                 int 主键

sid               int

cid                int

score            int

插入以下数据：

student

1 Tony male 2001-3-5

2 Tom male 2001-5-21

3 Jerry male 2002-6-14

4 Peter male 2001-10-21

5 Frank male 2001-12-23

6 Lily female 2002-7-21

7 Lucy female 2002-1-24

8 Rose female 2001-10-5

9 Lisa female 2003-5-18

course

1 java

2 c

3 c++

4 python

grade

1 1 1 95

2 1 2 87

3 1 4 96

4 2 2 85

5 2 4 96

6 3 1 89

7 3 3 99

8 3 4 97

9 4 1 97

10 4 2 66

11 4 3 97

12 4 4 88

答案：

create table student(id int primary key,name varchar(20),gender varchar(10),birthday date);

create table course(id int primary key,name varchar(20));

create table grade(id int primary key,sid int,cid int,score int);

insert into student values(1,'Tony','male','5-3月-2001');

insert into student values(2,'Tom','male','21-5月-2001');

insert into student values(3,'Jerry','male','14-6月-2002');

insert into student values(4,'Peter','male','21-10月-2001');

insert into student values(5,'Frank','male','23-12月-2001');

insert into student values(6,'Lily','female','21-7月-2002');

insert into student values(7,'Lucy','female','24-1月-2002');

insert into student values(8,'Rose','female','5-10月-2001');

insert into student values(9,'Lisa','female','18-5月-2003');

insert into course values(1,'java');

insert into course values(2,'c');

insert into course values(3,'c++');

insert into course values(4,'python');

 insert into grade values(1,1,1,95);

 insert into grade values(2,1,2,87);

 insert into grade values(3,1,4,96);

 insert into grade values(4,2,2,85);

 insert into grade values(5,2,4,96);

 insert into grade values(6,3,1,89);

 insert into grade values(7,3,3,99);

 insert into grade values(8,3,4,97);

 insert into grade values(9,4,1,97);

 insert into grade values(10,4,2,66);

 insert into grade values(11,4,3,97);

 insert into grade values(12,4,4,88);

1.查询Peter的总分

select sum(score) from grade where sid=(select id from student where  name='Peter');

select name,sum(score) from student,grade where grade.sid = student.id group by name having name = 'Peter';

select sum(grade.score) from student,grade where student.id=grade.sid and name='Peter';

2.查询Jerry的平均分

select avg(grade.score) from student,grade where student.id=grade.sid and name='Jerry';

3.查询选择java课程的人数

select count(*) from grade,course where course.id=grade.cid and course.name='java';

4.查出所有学生的课程分数，没选的课程成绩显示为空

select student.name,course.name,score from student left join grade on student.id=grade.sid left join course on course.id=grade.cid;

select student.name,course.name,score from course,grade,student where course.id = grade.cid(+) and student.id(+) = grade.sid;

5.分别统计各科的优秀（85以上），及格（60以上）与不及格的人数

select course.name,

sum(case when score>=85 then 1 else 0 end) as "优秀",

sum(case when score>=60 and score<85 then 1 else 0 end) as "及格",

sum(case when score<60 then 1 else 0 end) as "不及格"

from course,grade

where course.id = grade.cid

group by name;

6.查询各科平均分

select course.name,avg(score) from grade,course,student where student.id=grade.sid and grade.cid=course.id group by course.name;

7.将各科按照成绩进行倒序排序

select course.name,grade.score from course,grade where  grade.cid=course.id order by score desc;

select cid,score from course,grade where course.id=grade.cid order by cid,score desc ;

8.统计各科的学生姓名

select course.name,student.name from student,grade,course where grade.sid = student.id and grade.cid = course.id;

9.分别找出男生中的平均分第一名和女生中的平均分第一名

方法一：

select * from

(select a.*,rownum rn from(select name,avg(score) from student,grade where student.id = grade.sid and gender= 'male'

group by name order by avg(score) desc) a) male,

(select a.*,rownum rn from(select name,avg(score) from student,grade where student.id = grade.sid and gender= 'female'

group by name order by avg(score) desc) a) female

where male.rn=1 and female.rn=1;

方法二：

select name from (select student.name,avg(grade.score) a from student,grade where student.id = grade.sid and student.gender = 'male' group by student.name) where a=

(select max(a) from (select student.name,avg(grade.score) a from student,grade where student.id = grade.sid and student.gender = 'male' group by student.name))

union

select name from (select student.name,avg(grade.score) a from student,grade where student.id = grade.sid and student.gender = 'female' group by student.name) where a=

(select max(a) from (select student.name,avg(grade.score) a from student,grade where student.id = grade.sid and student.gender = 'female' group by student.name));

10.找出平均分前三名的同学

select * from (select name,avg(score) from student,grade where student.id = grade.sid group by name order by avg(score) desc) where rownum <=3;