Oracle 常用SQL 语句

-- 3-1

create tablespace tablespace_test

 datafile 'F:\oracle\testdatafile_1.dbf' size 10m

 AUTOEXTEND OFF;

-- 3-2

CREATE TEMPORARY TABLESPACE tablespace_temp

 TEMPFILE 'F:\oracle\testtemp_1.dbf' SIZE 10m

 AUTOEXTEND OFF;

-- 3-3

ALTER USER system DEFAULT TABLESPACE tablespace_test;

ALTER USER system TEMPORARY TABLESPACE tablespace_temp;

-- 3-4

ALTER TABLESPACE tablespace_test

 ADD DATAFILE 'F:\oracle\test_datafile1.dbf' size 1m;

-- 3-5

ALTER TABLESPACE tablespace_test

 DROP DATAFILE 'F:\oracle\test_datafile1.dbf';

ALTER TABLESPACE tablespace_test OFFLINE;

ALTER TABLESPACE tablespace_test ONLINE;

-- 3-6

ALTER TABLESPACE tablespace_test

 RENAME TO new_tablespace;

-- 3-7

DROP TABLESPACE new_tablespace

 INCLUDING CONTENTS AND DATAFILES;

-- 1.创建一个名为TS_TESTBASE 的表空间，数据文件大小为5m,并且设置数据文件的最大值为10m

CREATE TABLESPACE ts_testbase

 DATAFILE 'F:\oracle\ts_datafile.dbf' SIZE 5m

 AUTOEXTEND ON MAXSIZE 10m;

-- 2.为ts_testbase 表空间添加一个数据文件ts_datafile1.

ALTER TABLESPACE ts_testbase

 ADD DATAFILE 'f:\oracle\ts_datafile1.dbf' SIZE 2m;

-- 3.将表空间的状态设置成脱机状态

ALTER TABLESPACE ts_testbase OFFLINE;

-- 4.将数据文件ts_datafile1 移除

ALTER TABLESPACE ts_testbase ONLINE;

ALTER TABLESPACE ts_testbase

 DROP DATAFILE 'f:\oracle\ts_datafile1.dbf';

-- 5.将表空间重命名为new_testbase.

ALTER TABLESPACE ts_testbase

 RENAME TO new_testbase;

-- 6.删除表空间并将数据文件一并删除

DROP TABLESPACE new_testbase

 INCLUDING CONTENTS AND DATAFILES;

// 4-2

CREATE TABLE productrating

(

    id VARCHAR(20),

    proname VARCHAR(20),

    rating number,

    contents VARCHAR(800),

    ratingtime date,

    username VARCHAR(20)

);

CREATE TABLE productrating1

(

    id VARCHAR(20),

    proname VARCHAR(20),

    rating number,

    contents VARCHAR(800),

    ratingtime date,

    username VARCHAR(20)

) tablespace users;

select tablespace_name from dba_tables where table_name='productrating1';

//4-3复制表

create table new_productrating as select * from productrating;

//4-4 修改表

alter table productrating modify contents varchar2(100);

//4-5

alter table productrating modify ratingtime default sysdate;

//4-6 添加列

alter table productrating add times number;

//4-7 添加列

alter table productrating add (col1 number, col2 varchar2(10));

//4-8 删除列

alter table productrating drop column times;

//4-9

alter table productrating drop (col1, col2);

//4-10 标记

alter table productrating set unused(col1, col2);

desc productrating;

alter table productrating drop unused columns;

// 4-11重命名

alter table productrating rename column username TO userid;

// 4-12 重命名表

rename productrating to new_productrating1;

alter new_productrating1 rename to productrating;

//4-13 删除表

drop table productrating cascade constraints;

// 4-14 表截断操作

truncate table productrating; 

//4-15 主键约束

drop table productrating cascade constraints;

CREATE TABLE productrating

(

    id VARCHAR(20) primary key,

    proname VARCHAR(20),

    rating number,

    contents VARCHAR(800),

    ratingtime date,

    username VARCHAR(20)

);

select constraint_type, constraint_name from dba_constraints where table_name='productrating';

// 4-16

CREATE TABLE productrating

(

    id VARCHAR(20),

    proname VARCHAR(20),

    rating number,

    contents VARCHAR(800),

    ratingtime date,

    username VARCHAR(20),

    constraint pk_id primary key(id)

);

// 4-17

alter table productrating add constraint pk_id primary key(id);

//4-18

alter table productrating drop constraint pk_id;

// 4-19 非空约束

drop table productrating cascade constraints;

CREATE TABLE productrating

(

    id VARCHAR(20),

    proname VARCHAR(20) not null,

    rating number,

    contents VARCHAR(800),

    ratingtime date,

    username VARCHAR(20) not null

);

// 4-20修改非空约束

alter table productrating modify rating not null;

// 4-21

alter table productrating modify rating  null;

// 4-22 唯一约束

drop table productrating cascade constraints;

CREATE TABLE productrating

(

    id VARCHAR(20),

    proname VARCHAR(20),

    rating number,

    contents VARCHAR(800) unique,

    ratingtime date,

    username VARCHAR(20)

);

select constraint_type, constraint_name from dba_constraints where table_name='productrating' and constraint_type='U';

//4-23 设置唯一约束

alter table productrating add constraint uq_contents unique(contents);

// 4-24 检查约束

drop table productrating cascade constraints;

CREATE TABLE productrating

(

    id VARCHAR(20),

    proname VARCHAR(20),

    rating number check(rating>0),

    contents VARCHAR(800),

    ratingtime date,

    username VARCHAR(20)

);

CREATE TABLE productrating

(

    id VARCHAR(20),

    proname VARCHAR(20),

    rating number,

    contents VARCHAR(800),

    ratingtime date,

    username VARCHAR(20),

    constraint chk_rating check(rating>0)

);

//4-25 添加检查约束

alter table productrating add constraint chk_rating check(rating>0);

// 4-26 删除检查约束

alter table productrating drop constraint chk_rating;

// 4-27 创建users 表

create table users

(

    username varchar2(20) primary key,

    userpwd varchar2(20)

)

drop table productrating cascade constraints;

CREATE TABLE productrating

(

    id VARCHAR(20),

    proname VARCHAR(20),

    rating number,

    contents VARCHAR(800),

    ratingtime date,

    username VARCHAR(20) references users(username)

);

CREATE TABLE productrating

(

    id VARCHAR(20),

    proname VARCHAR(20),

    rating number,

    contents VARCHAR(800),

    ratingtime date,

    username VARCHAR(20),

    constraint fk_username foreign key(username) references users(username)

);

// 4-28 表级添加外键约束

alter table productrating add constraint fk_username foreign key (username)

references users(username);

// 4-29 删除外键约束

alter table productrating drop constraint fk_username;

// 4-30 修改约束

alter table productrating rename constraint fk_username to fk_uname;

// 4-31 修改约束状态

alter table productrating disable constraint fk_uname;

alter table productrating enable constraint fk_uname;

//4-32 完整代码

CREATE TABLE productrating

(

    id VARCHAR(20) primary key,

    proname VARCHAR(20) not null,

    rating number check(rating>0),

    contents VARCHAR(800) unique,

    ratingtime date,

    username VARCHAR(20) not null,

     constraint fk_username foreign key(username) references users(username)

);

// 实例演练

// 创建学生信息管理系统所需表

create table student

(

    id varchar2(10),

    name  varchar2(20),

    majorid varchar2(10),

    classid varchar2(10),

    sex varchar2(6),

    nation varchar2(10),

    entrancedate varchar2(20),

    idcard varchar2(20),

    tel varchar2(20),

    email varchar2(20),

    remarks varchar2(100)

);

create table majorinfo

(

    majorid varchar2(10),

    majorname varchar2(20)

);

create table classinfo

(

    classid varchar2(10),

    grade varchar2(10),

    classname varchar2(20)

);

create table course

(

    courseid varchar2(10),

    coursename varchar2(20),

    credit number(3,1),

    remarks varchar2(100)

);

create table gradeinfo

(

    studentid varchar2(10),

    courseid varchar2(10),

    grade number(4,1),

    semester varchar2(16),

    remarks varchar2(100)

);

// 专业班级表

alter table majorinfo add constraint pk_id primary  key(majorid);

alter table majorinfo modify majorname not null;

// 班级信息表

alter table classinfo add constraint pk_classid primary key(classid);

alter table classinfo add constraint uq_grade unique(grade);

alter table classinfo add constraint uq_classname unique(classname);

//课程信息表

alter table course add constraint pk_courseid primary key(courseid);

alter table course add constraint uq_coursename unique(coursename);

// 学生信息表

alter table student add constraint pk_stuid primary key(id);

alter table student add constraint fk_classid foreign key(classid) references classinfo(classid);

alter table student add constraint fk_majorid foreign key(majorid) references majorinfo(majorid);

alter table student modify name not null;

// 学生成绩表

alter table gradeinfo add constraint pk_gradeid primary key(studentid, courseid);

alter table gradeinfo add constraint chk_grade check(grade>0);

// 习题 3.操作题

create table foodinfo

(

    no varchar2(10),

    name varchar2(50),

    price number(7,2),

    typeid varchar2(10)

);

create table typeinfo

(

    typeid varchar2(10) primary key,

    typename varchar2(100)

);

alter table foodinfo add constraint fk_typeid foreign key(typeid) references typeinfo(typeid);

// 第五章 操作表中的数据

create table movieinfo

(

    id varchar2(20) primary key,

    name varchar2(50) not null,

    actors varchar2(20),

    contents varchar2(800) not null,

    typename varchar2(20),

    releasetime varchar2(20) not null,

    country varchar2(20)

);

//5-1 全部列添加数据

insert into movieinfo values('2015001','有一个地方只有我们知道','吴亦凡、王丽坤','小白领金天正在经历人生中最失败的时刻……','爱情','2015.2','中国');

insert into movieinfo(id,name,actors,contents,typename,releasetime,country) 

    values('2015002','饥饿游戏3','詹妮弗.劳伦斯','凯特尼斯.伊弗蒂恩，燃烧的女孩虽然她的家被毁了，可她却活了下来，……','科幻','2015.2','美国');

//5-2 向指定列添加值

insert into movieinfo(id,name,releasetime,contents) 

    values('2015003','星际穿越','2015.2','讲述一队探险家利用他们针对虫洞的新发现，超越了人类对于太空旅行的极限，从而开始在广袤的宇宙中进行星际航行的故事。');

insert into movieinfo(id,name,releasetime,contents) 

    values('2015004','超能陆战队','2015.2','改编自漫威于1998年出版的同名漫画');

//5-3 null值的使用

insert into movieinfo values('2015005','熊出没之雪岭熊风',null,'在狗熊岭百年不遇的大雪中，熊二偶遇了小时候曾有过一面之缘的神秘小伙伴，……','动画','2015.1','中国');

insert into movieinfo values('2015006','奔跑吧兄弟','王宝强','跑男团的几位兄弟姐妹散落在全国各地，他们有的人是厨师，有的人是富二代，有的人沉迷于网络游戏，有的人干起了电视购物……','喜剧','2015.1',null);

//5-4 & 号的插入

set define off;

insert into movieinfo values('2015007','天降雄狮','成龙','&无','动作','2015.2','中国');

set define on;

insert into movieinfo values('2015008','冲上云霄','古天乐',chr(38)||'无','剧情','2015.2','中国');

//5-5 单引号插入

insert into movieinfo values('2015009','澳门风云2','周润发','''无''','动作','2015.2','中国');

insert into movieinfo values('2015010','澳门风云','周润发',chr(39)||'无'||chr(39),'动作','2015.2','中国');

//5-6 复制表中的数据

create table test

(

    name varchar2(50) not null,

    country varchar2(20)

);

insert into test select name, country from movieinfo;

//5-7 修改表中的数据

update movieinfo set name='*'||name;

select * from movieinfo;

//5-8 按条件修改表中的数据

update movieinfo set name='*'||name where typename='动画';

//5-9 

update movieinfo set name='动作'||name, releasetime='2015.2' where typename='动作';

select name,releasetime from movieinfo;

//5-10 删除表中的数据

create table new_movieinfo

as 

select * from movieinfo;

select * from new_movieinfo;

delete from new_movieinfo;

//5-11 按条件删除数据

delete from movieinfo where releasetime='2015.1';

-- 向班级信息表添加的数据

insert into classinfo values('1501','2014级','计算机1班');

insert into classinfo values('1502','2013级','会计1班');

insert into classinfo values('1503','2015级','自动化1班');

--向专业信息表添加的数据

insert into majorinfo values('0001','计算机');

insert into majorinfo values('0002','会计');

insert into majorinfo values('0003','自动化');

--向课程信息表添加的数据

insert into course values('1001','计算机基础',0.5,'无');

insert into course values('1002','会计',1,'无');

insert into course values('1003','自动化',1,'无');

--向学生信息表添加的数据

insert into student values('15001','张小林','0001','1501','男','汉','2015.9','无','13112345678','无','无');

insert into student values('14001','王铭','0002','1502','男','回','2014.9','无','13212345678','无','无');

insert into student values('13001','吴琪','0001','1503','女','汉','2013.9','无','13312345678','无','无');

--向成绩表信息表中添加的数据

insert into gradeinfo values('150001','1001',86,'2015第1学期','无');

insert into gradeinfo values('140001','1002',90,'2014第2学期','无');

insert into gradeinfo values('130001','1001',92,'2014第1学期','无');

--复制表

create table test1

as 

select * from course;

create table test1_new

(

    courseid varchar2(10),

    coursename varchar2(20),

    credit number(3,1),

    remarks varchar2(100)

);

insert into test1_new select * from course;

--修改学分

update course set credit =credit+0.5 where coursename='计算机基础';

--修改学生电话

update student set tel='13812345678' where id='15001';

--按条件删除

delete from classinfo where classid='1501';

select * from student;

--第5章 习题 操作题

select * from movieinfo;

--1插入两条数据

insert into movieinfo values('2015011','无双','郭富城','以代号“画家”（周润发饰）为首的犯罪团伙，掌握了制造伪钞技术，难辨真伪……','爱情','2018.10','中国');

insert into movieinfo values('2015012','霸王别姬','张国荣','京剧表现形式……','剧情','1990.10','中国');

--2复制表

create table movieinfo_test

as 

select * from movieinfo;

--3修改上演日期

update movieinfo_test set releasetime='2015.2';

--4修改类型

update movieinfo_test set name=name||'动画',contents='无' where typename='动画'; 

--5删除

delete from movieinfo_test where  typename='动作';

// 第六章

// 数值函数

--6-1绝对值函数

select abs(123),abs(-123),abs(0) from dual;

--6-2取余函数

select mod(10,3),mod(-10,3),mod(10,0) from dual;

--6-3取整函数

select ceil(56.56),ceil(85.28),floor(85.28),ceil(78),floor(78) from dual;

--6-4四舍五入函数

select round(45.789,2),round(789.5),round(1245,-1) from dual;

--6-5幂函数

select power(-3,3),power(10.2,2),power(5,2.5) from dual;

--6-6求平方根函数

select sqrt('125'),sqrt('25'),sqrt(0),sqrt(100.5) from dual;

--6.7三角函数

select sin(0.5),cos(0.83),tan(-0.67) from dual;

// 字符函数

--6-8求子符长度

select length('abc d'),length(123.45),length('aa c d') from dual;

--6-9截取字符串

select substr('123abcABC',1,3),substr('123abcABC',4,3),substr('123abcABC',4) FROM DUAL;

--6-10字符大小写切换

select upper('abcABC'),upper('aaa123'),lower('ABDEcc') from dual;

--6-11首字母大写函数

select initcap('anny'),initcap('BANK') from dual;

--6-12字符串连接函数

select concat('abc','ABC'),'abc'||'ABC',concat('1234','abc'),'1234'||'abc' from dual;

--6-13字符串查找函数

select instr('Have a good time','good'),instr('very well','good') from dual;

--6-14替换函数

select replace('[email protected]','@','#'),replace('abc11aabc123ab','ab','AA') from dual;

--6-15获取系统时间

select sysdate from dual;

--6-16为日期加上指定的月份

select add_months(sysdate,6), add_months(sysdate,-3) from dual;

--6-17返回指定日期所在月的最后一天

select last_day(sysdate),last_day('01-2月2015') from dual;

--6-18返回指定日期后的周几

select next_day(sysdate,'星期五'),next_day('19-2月-2015','星期二') from dual;

--6-19计算月份差的函数

select months_between(sysdate,'01-9月-2014') from dual;

--6-20从日期中提取指定的数据

select extract(year from sysdate),extract(month from sysdate),extract(day from sysdate) from dual;

//转换函数

--6-21数值型转换成字符串

select to_char(14.53,'$99.99'),to_char(155.53,'9,999.9s') from dual;

select to_char(14.53,'$99.99'),to_char(155.51,'S9,999.9') from dual;

select to_char(-155.57,'9,999.9mi') from dual;

select to_char(-155.53,'9,999.9s'),to_char(-155.53,'s9,999.9'),to_char(155.53,'s9,999.9'),to_char(155.53,'9,999.9s') from dual;

--6.22将日期型转化成字符串

select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS DAY') from dual;

--6-23将字符型数据转换成日期型

select to_date('2014-06-01','YYYY-MM-DD') from dual;

SELECT to_char(to_date('2017-05-23','YYYY-MM-DD'),'YYYY-MM-DD') FROM DUAL;

SELECT to_char(to_date('2017-05-23','YYYY-MM-DD'),'YYYY"年"MM"月"DD"日"') FROM DUAL;

--6-24将字符型转换成数值型

select to_number('$14.53','$99.99'),to_number('1,555.5+','9,999.9s') from dual;

--6-25cast转换函数

select cast('12.35' as number(4,2)), cast(123 as varchar2(3)),cast(sysdate as varchar2(20)),cast('16-5月-2015' as date) from dual;

// 聚合函数

--6.26求平均值函数

select avg(grade) from gradeinfo;

--6-27求最小值、最大值

select max(grade),min(grade) from gradeinfo;

--6-28求记录数与求和

select count(*),sum(grade) from gradeinfo;

--6-29其他函数

select user from dual;

select USERENV('LANGUAGE') "Language" FROM DUAL

select nullif('aaa','bbb') from dual;

select coalesce('aa','','cc',null) from dual;

select vsize('aaa') from dual;

//自定义函数

--6-29创建函数

create function fun

(price number)

return number

is

begin

return price*0.6;

end;

/

select fun(60) from dual;

--6-30删除函数

drop function fun;

select to_date('17-10月-2018')-to_date('01-1月-2015') from dual;

select * from user_procedures;

//第六章 操作题

--1

select substr('abcdefg123456',7,4) FROM DUAL;

--2

select to_char('123456.6','999,999.9s') from dual;

--3

//自定义函数

--6-29创建函数

create function fun_test

(str1 varchar2,str2 varchar2)

return varchar2

is

begin

return str1||str2;

end;

/

select fun_test('abc','123') from dual;

// 第七章

drop table courses cascade constraints;

create table courses

(

    courseid varchar2(10) primary key,

    coursename varchar2(20),

    price number(5,1),

    teacher varchar2(20),

    contents varchar2(200),

    typeid varchar2(10),

    remarks varchar2(200)

);

drop table stuinfo cascade constraints;

create table stuinfo

(

    stuid varchar2(10) primary key,

    name varchar2(20),

    password varchar2(10),

    email varchar2(20),

    tel varchar2(20)

);

create table teachers

(

    teacherid varchar2(10) primary key,

    teachername varchar2(20),

    contents varchar2(200),

    remarks varchar2(200)

);

drop table typeinfo cascade constraints;

create table typeinfo

(

    typeid varchar2(10) primary key,

    typename varchar2(20)

);

create table shopping

(

    id varchar2(10) primary key,

    courseid varchar2(10),

    stuid varchar2(10),

    shoppingtime date default sysdate

);

alter table courses add constraint fk_typeid foreign key(typeid) references typeinfo(typeid);

alter table shopping add constraint fk_courseid foreign key(courseid) references courses(courseid);

alter table shopping add constraint fk_stuid foreign key(stuid) references stuinfo(stuid);

-- 向课程表中添加数据

insert into courses values(1,'Oracle基础',100,'1001','基础语法的使用','1001','略');

insert into courses values(2,'Java开发',300,'1001','Java基础语法','1002','略');

insert into courses values(3,'Android开发',200,'1002','开发手机游戏','1003','略');

--向学员表中添加数据

insert into stuinfo values(1,'张小小','123456','[email protected]','12345678');

insert into stuinfo values(2,'李明','123456','[email protected]','12345678');

insert into stuinfo values(3,'刘想','123456','[email protected]','12345678');

commit;

select * from stuinfo where password='49CQeU';

--向讲师表中添加数据

insert into teachers values(1001,'张老师','某大学毕业','略');

insert into teachers values(1002,'李老师','某大学毕业','略');

insert into teachers values(1003,'王老师','某大学毕业','略');

select * from teachers;

--向课程类型表中添加数据

insert into typeinfo values(1001,'数据库');

insert into typeinfo values(1002,'编程语言');

insert into typeinfo values(1003,'办公自动化');

--向购买课程表中添加数据

insert into shopping values(1,2,1,default);

insert into shopping values(2,1,2,default);

insert into shopping values(3,3,1,default);

select * from typeinfo;

--7-1 查询所有数据

select * from typeinfo;

--7-2 查询表中指定的列

select coursename, price from courses;

--7-3给列设置别名

select coursename as 课程名称, price as 课程价格 from courses;

--7-4 去除表中重复记录

insert into courses values(4,'Oracle基础',200,'1001','基础语法的使用','1001','略');

select distinct coursename from courses;

--7-5 对查询结果进行排序

select coursename as 课程名称, price as 课程价格 from courses order by 课程价格 desc;

--7-6

select coursename as 课程名称, price as 课程价格 from courses order by 课程价格 desc, 课程名称 asc;

--7.7 在查询中使用表达式

select coursename as 课程名称, price*0.8 as 打折后课程价格 from courses;

--7-8

select coursename||':'||contents as 课程介绍, price*0.8 as 打折后课程价格 from courses;

--7-9 case when

select coursename as 课程名称, case

    when price>=200 then price*0.8

    when price>=100 then price*0.9

    end as 价格 from courses;

--7-10 

select coursename as 课程名称, case typeid

    when '1001' then '数据库'

    when '1002' then '编程语言'

    when '1003' then '办公自动化'

    end as 课程类型 from courses;

--7-11 查询带null 值的列

select coursename, price from courses where coursename is NUll;

select coursename, price from courses where coursename is not NUll;

--7-12 使用rownum 查询指定数目的行

select coursename, price from courses  where rownum<=2  order by price asc;

select coursename, (select price from courses order by price asc) as price from courses;

select coursename,price from 

(select coursename,price from courses order by price) 

where rownum<=2;

select coursename, price from courses where rownum>=3;

--7-13 范围查询

select coursename, price from courses where price between 100 and 200;

--7-14

select coursename, price from courses where coursename in('Oracle基础','Android开发');

--7-15 模糊查询

select coursename, price from courses where coursename like '%Oracle%';

--7-16

select coursename, price from courses where coursename like '%基础';

select coursename, price from courses where coursename like '_racle基础';

--7-17 在分组查询中使用聚合函数

select coursename, count(*) from courses group by coursename;

--7-18 统计每类课程的平均价格

select typeid, avg(price) from courses group by typeid;

--7-19 带条件的分组查询

select typeid, avg(price) from courses group by typeid having avg(price) >200;

--7-20 

select typeid, avg(price) from courses where price>=200 group by typeid;

--7-21 对分组查询的结果排序

select typeid,avg(price) from courses group by typeid order by avg(price) desc;

--7-22 笛卡尔积

select * from courses, typeinfo;

--7-23 内连接查询

select courses.coursename,typeinfo.typename from courses,typeinfo where courses.typeid=typeinfo.typeid;

select courses.coursename,typeinfo.typename from courses inner join typeinfo on courses.typeid=typeinfo.typeid;

--7-24 

select courses.coursename,typeinfo.typename from courses inner join typeinfo on courses.typeid in typeinfo.typeid;

--7-25 外链接查询

select courses.coursename,typeinfo.typename from courses right outer join typeinfo on courses.typeid=typeinfo.typeid;

--7-26 交叉连接查询

select * from courses cross join typeinfo;

--7-27 子查询

select coursename, price from courses where price>(select avg(price) from courses);

--7-28

select coursename, price from courses

    where teacher in (select teacherid from teachers where teachername='张老师' or teachername='王老师');

--7-29 From 子句中的子查询

select coursename, price from courses a, (select typeid, avg(price) as avgprice from courses group by typeid) b 

    where a.typeid=b.typeid and a.price>avgprice;

--7-30

select * from (select rownum rn, coursename, price from courses) a where a.rn in(2,3);

--7.7 实例演练

select student.name, majorinfo.majorname from student, majorinfo where student.majorid=majorinfo.majorid and student.name='张%';

select student.name, majorinfo.majorname, course.coursename, gradeinfo.grade from student, majorinfo, gradeinfo, course where

    student.majorid=majorinfo.majorid and course.courseid=gradeinfo.courseid and gradeinfo.studentid=student.id;

select course.coursename,avg(gradeinfo.grade) as 平均分, max(gradeinfo.grade) as 最高分 from gradeinfo,course

    where course.courseid=gradeinfo.courseid

    group by course.coursename;

select student.name, gradeinfo.grade from student, gradeinfo where student.id = gradeinfo.studentid 

    and gradeinfo.grade>(select avg(grade) from gradeinfo);

ALTER USER scott ACCOUNT UNLOCK;

alter user scott identified by 123456;

-- 9-8 习题

--3 操作题

select courses.coursename, teachers.teachername, typeinfo.typename, courses.price from courses, teachers, typeinfo where

    courses.typeid=typeinfo.typeid and courses.teacher=teachers.teacherid;

select teachers.teachername, courses.price from courses, teachers where 

    courses.teacher=teachers.teacherid and courses.typeid=(select typeid from typeinfo where typeinfo.typename='数据库');

select * from courses;

select teachers.teachername, count(teacher) from courses, teachers where 

    courses.teacher=teachers.teacherid group by teachers.teachername;

-- 第八章 视图与索引

create table types

(

    typeid varchar2(10) primary key,

    typename varchar2(50) not null

);

create table questions

(

    id varchar2(10) primary key,

    question varchar2(300) not null,

    typeid varchar2(10) references types(typeid),

    answer varchar2(800) not null,

    points number not null,

    remarks varchar2(100)

);

-- 题目类型表

insert into types values('1001', '自我认知');

insert into types values('1002', '组织管理');

insert into types values('1003', '综合分析');

insert into types values('1004', '解决问题');

insert into types values('1005', '联想题');

-- 面试试题表

insert into questions values('201401','请简单做一个自我介绍。','1001','略',15,'无');

insert into questions values('201402','有你负责组织一个学生春游活动，怎么组织？','1002','需要明确时间，地点，人物等等',15,'无');

insert into questions values('201403','对于目前大学生就业难的问题，你怎么看？','1003','需要明确具体的措施',15,'无');

commit;

select * from questions;

-- 8-1 创建单表试图

create view v_question1

as 

select question, answer from questions;

select * from v_question1;

-- 8-2 创建多表试图

create view v_question2

as 

select questions.question, types.typename 

from questions, types

where questions.typeid=types.typeid

with read only;

select * from v_question2;

-- 8-3 

create view v_question3

as

select question from v_question1;

select * from v_question3;

--8-4 删除试图

drop view v_question1;

select * from v_question3;

-- 8-5 使用dml 语句操作试图

create view v_question4

as 

select * from questions where typeid='1001';

-- 添加

insert into v_question4 values('201404','请说出你的三个缺点。','1001','略',15,'无');

select question,answer from questions;

-- 8-6更新

update v_question4 set answer='提出的缺点能否通过努力克服'

    where id='201404';

-- 8-7 删除

delete from v_question4 where id='201404';

-- 8-8 修改

update v_question2 set question='自我认知'|| question where typename='自我认知';

-- 管理索引

-- 8-9 创建索引

create index ix_question

on questions(question);

--8-10 唯一索引

create unique index ix_typename 

on types (typename);

alter index in_typename rename to ix_typename;

--8-11 

create bitmap index ix_typeid

on questions(typeid);

--8-12

create index ix_question_answer

on questions (question,answer);

-- 8-13 更名

alter index ix_question rename to ix_question_new;

-- 8-14 改成压缩索引

alter index ix_question_answer rebuild compress;

--8-15 重新生成索引

alter index ix_typename rebuild;

--8-16 是索引 ix_typename 不可见

alter index ix_typename invisible;

-- 8-17 删除索引

drop index ix_typename;

-- 实例演练

create view v_student

as

select student.name, majorinfo.majorname, classinfo.classname, student.entrancedate,student.tel

from student, classinfo, majorinfo

where student.majorid=majorinfo.majorid and student.classid=classinfo.classid;

select name,majorname,classname from v_student;

create index ix_stuname

on student(name);

create bitmap index ix_stumajor

on student(majorid);

create bitmap index ix_stuclass

on student(classid);

select index_name,index_type from dba_indexes where table_name='STUDENT';

-- 操作题

create view v_course

as 

select questions.question, types.typename,  questions.points

from questions, types

where questions.typeid=types.typeid;

select * from v_course;

create index ix_course

on questions (question,points);

-- 改成压缩索引

alter index ix_course rebuild compress;

-- 第九章 序列与同义词

-- 9-1 创建序列

create sequence seq_test

increment by 1

start with 1

maxvalue 100

minvalue 1;

-- 9-2 创建递减序列

create SEQUENCE seq_test1

  INCREMENT BY -1 

  start with 100

  MAXVALUE 100

  minvalue 1

  cycle;

-- 使用序列

select seq_test.currval from dual;

select seq_test.nextval from dual;

-- 9-4 创建表

create  table sales

(

    id varchar2(10) primary key,

    name varchar2(100),

    price number(6,1),

    area number(6,1),

    buildingno varchar2(5),

    remarks varchar2(100)

);

insert into sales values(seq_test.nextval, '2室1厅','8000','80','A1','无');

insert into sales values(seq_test.nextval, '2室1厅','8500','100','A2','无');

select id,name from sales;

--9-5 修改

update sales set remarks=seq_test.nextval where name='2室1厅';

select id,name, remarks from sales;

-- 管理序列

-- 9-6 修改

alter sequence seq_test

maxvalue 50;

alter sequence seq_test

maxvalue 3;

--9-7 选好产生序列

alter sequence seq_test

cycle

cache 10;

-- 9-8 删除序列

drop sequence seq_test;

-- 同义词

-- 9-9 创建同义词

create SYNONYM house for sales;

select table_name, synonym_name from dba_synonyms where owner='SYSTEM';

-- 9-10 

create public synonym house1 for system.sales;

-- 使用同义词

-- 9-11 添加数据

insert into house values('10', '1室1厅', '8500', '65', 'A3', '无');

conn scott/123456;

insert into house values('11', '1室2厅', '8500', '65', 'A3', '无');

grant insert on system.sales to scott;

insert into system.house values('11', '1室2厅', '8500', '65', 'A3', '无');

-- 9-12 

insert into house1 values('13', '1室2厅', '8500', '65', 'A3', '无');

-- 删除同义词

-- 9-13

drop synonym house;

--9-14 删除公用同义词

drop public synonym house1;

-- 实例演练

create sequence seq_majorid

increment by 1

start with 1

maxvalue 9999999999

minvalue 1;

select * from majorinfo;

insert into majorinfo values(seq_majorid.nextval,'计算机');

insert into majorinfo values(seq_majorid.nextval,'会计');

insert into majorinfo values(seq_majorid.nextval,'自动化');

select * from stuinfo;

select * from student;

create synonym stuinfo1

for system.student;

select id , name from stuinfo1;

-- 习题 3操作题

--1

create sequence seq_testadd

increment by 1

start with 1

maxvalue 100

minvalue 1;

-- 9-2 创建递减序列

create SEQUENCE seq_testdown

  INCREMENT BY -1 

  start with 100

  MAXVALUE 100

  minvalue 1

  cycle;

--2

select seq_testadd.nextval from dual;

select seq_testadd.currval from dual;

select seq_testdown.nextval from dual;

select seq_testdown.currval from dual;

--3

create public synonym sales

    for system.sales;

select * from sales;

drop public synonym sales; 

-- 第十章 PL/SQL基本语法

--10-1 定义常量

declare

    class_num constant number(4):=50;

    class_name constant varchar2(20):='计算机一班';

begin

end;

-- 10-2 变量定义

age number(3):=20;

name varchar2(20):='张三';

-- 10-3 变量输出

set serverout on;

declare

    age number(3):=20;

    name varchar2(20):='张三';

begin

    DBMS_OUTPUT.PUT_LINE('年龄='||age);

    DBMS_OUTPUT.PUT_LINE('姓名='||name);

END;

/

-- 流程控制语句

-- 10-4 if

declare

    name varchar2(20):='张三';

begin

    if name='张三' then

        dbms_output.put_line('正确！');

    end if;

end;

/

--10-5 if else

declare

    name varchar2(20):='张三';

begin

    if name='张三' then

        dbms_output.put_line('正确！');

    else

        dbms_output.put_line('错误！');

    end if;

end;

/

-- if elsif else

declare

    score number(4,1):=76;

begin

    if score>=85 then

        dbms_output.put_line('优秀！');

    elsif score>=75 then

        dbms_output.put_line('良好！');

    elsif score>=60 then

        dbms_output.put_line('及格！');

    end if;

end;

/  

-- 10-7

declare

    proid varchar2(5):='001';

    result varchar2(10);

begin

    case proid

        when '001' then result:='图书类';

        when '002' then result:='电器类';

    else

        result:='其他类';

    end case;

    DBMS_OUTPUT.PUT_LINE(result);

end;

/

-- 循环语句

--  10-8 loop

declare

    i number(2):=0;

begin

loop

    i:=i+1;

    DBMS_OUTPUT.PUT_LINE(i);

    if i>=5 then

        exit;

    end if;

end loop;

end;

/

-- 10-9 loop-exit-when-end loop

declare

    i number(2):=0;

begin

loop

    i:=i+1;

    DBMS_OUTPUT.PUT_LINE(i);

    exit when i=5;

end loop;

end;

/

-- 10-10 while-loop-end loop

declare

    i number(2):=0;

begin

    while i<5

    loop

        i:=i+1;

        DBMS_OUTPUT.PUT_LINE(i);

    end loop;

end;

/

-- 10-11 for-in-loop-end loop

declare

    i number(2):=0;

begin

   for i in 1..5

   loop

     DBMS_OUTPUT.PUT_LINE(i);

    end loop;

end;

/

-- 异常处理

-- 10-13

declare 

    age number(2):=-1;

    exception_age exception;

begin

    if age<0 then

        raise exception_age;

    end if;

exception

    when exception_age then

    dbms_output.put_line('年龄小于0岁，请重新输入！');

end;

/

-- 事务

-- 10-14 

create table userinfo

(

    userid varchar2(10) primary key,

    username varchar2(20),

    userpwd varchar2(20)

);

insert into userinfo values('001','张三','abc');

select * from userinfo;

savepoint sp_1;

insert into userinfo values('002','李四','abc');

rollback to sp_1;

-- 游标

-- 10-15 

declare

cursor cursor_test is select username, userpwd from userinfo where userid='001';

v_username varchar2(20);

v_password varchar2(20);

begin

    open cursor_test;

    fetch cursor_test into v_username,v_password;

    dbms_output.put_line('用户名：'||v_username);

    dbms_output.put_line('密码：'||v_password);

close cursor_test;

end;

/

-- 10-16

declare

cursor cursor_test1 

is select * from userinfo where userid='001';

cur_record userinfo%rowtype;

begin

    open cursor_test1;

    fetch cursor_test1 into cur_record;

    dbms_output.put_line('用户名：'||cur_record.username);

    dbms_output.put_line('密码：'||cur_record.userpwd);

close cursor_test1;

end;

/

--10-17

declare

cursor cursor_test2 

is select * from userinfo;

cur_record userinfo%rowtype;

begin

    open cursor_test2;

    loop

        fetch cursor_test2 into cur_record;

        exit when cursor_test2%notfound;

        dbms_output.put_line('用户名：'||cur_record.username);

        dbms_output.put_line('密码：'||cur_record.userpwd);

    end loop;

close cursor_test2;

end;

/

-- 10-18 隐式游标

declare

v_username varchar2(20);

begin

    select username into v_username

    from userinfo

    where userid='001';

    dbms_output.put_line('用户名：'||v_username);

end;

/

-- 习题

-- 3.操作题

-- 1

set serverout on;

declare

    i number(2):=1;

    n number(4):=1;

begin

    while i<=5

    loop

        n:=n*i;

        i:=i+1;

    end loop;

    DBMS_OUTPUT.PUT_LINE('5!='||n);

end;

/

-- 2

create table bookinfo(

    bookid varchar2(10) primary key,

    bookname varchar2(20),

    bookprice number(6,2),

    bookpublish varchar2(20),

    bookauthor varchar2(20)

);

drop table bookinfo;

insert into bookinfo values('10010','Oracle基础',25,'清华出版社','张华');

insert into bookinfo values('10020','java基础',28,'清华出版社','夏江华');

insert into bookinfo values('10030','计算机基础',30,'清华出版社','刘琦');

select * from bookinfo;

declare

cursor cursor_t1 is select * from bookinfo;

c_bookrecord bookinfo%rowtype;

begin

open cursor_t1;

loop

fetch cursor_t1 into c_bookrecord;

exit when cursor_t1%notfound;

    dbms_output.put_line('图书名称：'||c_bookrecord.bookname||', 图书价格：'||c_bookrecord.bookprice);

end loop;

close cursor_t1;

end;

/

-- 3

declare

c_bookname varchar2(20);

c_bookprice number(6,2);

begin

    select bookname,bookprice into c_bookname, c_bookprice from bookinfo where bookid='10010';

    dbms_output.put_line('图书名称：'||c_bookname||', 图书价格：'||c_bookprice);

end;

/

-- 第十一章

-- 创建无参的存储过程

-- 11-1 

select * from typeinfo;

create procedure pro_test1

as

cursor cursor_test

is select * from userinfo;

cur_record userinfo%rowtype;

begin

open cursor_test;

loop

fetch cursor_test into cur_record;

exit when cursor_test%notfound;

    dbms_output.put_line('用户编号：'||cur_record.userid);

    dbms_output.put_line('用户名：'||cur_record.username);

    dbms_output.put_line('密码：'||cur_record.userpwd);

end loop;

close cursor_test;

end;

/

exec pro_test1;

-- 11-2 创建有参的存储过程

create procedure pro_test2(p_name in varchar2)

as

cursor cursor_test

is select * from userinfo where username=p_name;

cur_record userinfo%rowtype;

begin

open cursor_test;

loop

fetch cursor_test into cur_record;

exit when cursor_test%notfound;

    dbms_output.put_line('密码：'||cur_record.userpwd);

end loop;

end;

/

exec pro_test2('张三');

-- 11-3

create procedure pro_test3(p_name in varchar2, p_pwd out varchar2)

as

cursor cursor_test

is select * from userinfo where username=p_name;

cur_record userinfo%rowtype;

begin

open cursor_test;

loop

fetch cursor_test into cur_record;

exit when cursor_test%notfound;

p_pwd:=cur_record.userpwd;

end loop;

close cursor_test;

end;

/

declare 

p_pwd1 varchar2(20);

begin

pro_test3('张三',p_pwd1);

dbms_output.put_line('密码为：'||p_pwd1);

end;

/

exec pro_test3('张三','aa');

begin pro_test3('张三','aa') end;

-- 11-4

create procedure pro_test4(p_namepwd in out varchar2)

as 

cursor cursor_test

is select * from userinfo where username=p_namepwd;

cur_record userinfo%rowtype;

begin

open cursor_test;

loop

fetch cursor_test into cur_record;

exit when cursor_test%notfound;

p_namepwd:=cur_record.userpwd;

end loop;

close cursor_test;

end;

/

declare 

p_namepwd varchar2(20):='张三';

begin

pro_test4(p_namepwd);

dbms_output.put_line('密码为：'||p_namepwd);

end;

/

-- 11-5 重新编译存储过程

alter procedure pro_test1 compile;

-- 11-6

drop procedure pro_test1;

-- 触发器

select * from userinfo;

create table v_userinfo

as

select * from userinfo;

delete from userinfo1;

-- 11-7

create trigger tri_test1

after delete

on userinfo

for each row

begin

    insert into userinfo1 values(:old.userid,:old.username,:old.userpwd);

end;

/

delete from userinfo where userid='001';

-- 11-8 

create view v_userinfo1

as 

select * from userinfo;

create or replace trigger tri_test2

instead of delete

on v_userinfo1

begin

    dbms_output.put_line('不能删除该记录');

end;

/

delete from v_userinfo1;

select * from v_userinfo1;

-- 11-9

create trigger tri_test3

before drop on system.schema

begin

    dbms_output.put_line('删除的对象名为：'||ORA_DICT_OBJ_NAME);

end;

/

drop table userinfo1;

-- 管理触发器

-- 11-10 更改触发器状态

alter trigger tri_test3 disable;

-- 11-11 重新编译触发器

alter trigger tri_test3 compile;

-- 11-12 删除触发器

drop trigger tri_test3;

-- 实例演练

set serverout on;

select * from student;

create procedure pro_majorbyid(stuid in varchar2)

as

cursor cursor_test

is select maj.majorname from student stu, majorinfo maj

where stu.majorid = maj.majorid and stu.id = stuid;

v_majorname varchar2(20);

begin

open cursor_test;

loop

fetch cursor_test into v_majorname;

exit when cursor_test%notfound;

    dbms_output.put_line('学生专业是：'||v_majorname);

end loop;

close cursor_test;

end;

/

exec pro_majorbyid('15001');

-- 创建触发器

create table student_test

    (id varchar2(10),

    name varchar2(20),

    majorid varchar2(10),

    classid varchar2(10),

    sex varchar2(6),

    nation varchar2(10),

    entrancedate varchar2(20),

    idcard varchar2(20),

    tel varchar2(20),

    email varchar2(20),

    remarks varchar2(100)

);

select * from student_test;

drop table student_test;

create table student_test

as 

select * from student where 1>2;

create trigger tri_student

after delete

on student

for each row

begin

    insert into student_test values(:old.id, :old.name, :old.majorid,:old.classid, :old.sex, :old.nation, :old.entrancedate, :old.idcard,:old.tel, :old.email, :old.remarks);

end;

/

insert into student values('13001', '张三', '0002','1502', '男', '汉', '2015.9', '无','15112345678', '无', '无');

drop trigger tri_student;

select * from student;

delete from student where id='14001';

-- 习题

-- 操作题

create procedure procedure_test01(p_name in varchar2, p_pwd in varchar2)

as

begin

    if p_name='tom' and p_pwd='123' then

        dbms_output.put_line('用户名密码正确！');

    else

        dbms_output.put_line('用户名或密码不正确！');

    end if;

end;

/

drop procedure procedure_test01;

exec procedure_test01('tom', '123');

--2

select * from userinfo;

create table test_userinfo1

as 

select * from userinfo;

insert into userinfo values('001','张三','bcd');

insert into userinfo values('003','张丰','bcd');

insert into userinfo values('004','张华','bcd');

select * from test_userinfo;

drop trigger tri_userinfo1;

create trigger tri_userinfo1

after delete

on test_userinfo1

for each row

begin

    insert into test_userinfo values(:old.userid,:old.username,:old.userpwd);

end;

/

delete from test_userinfo1 where userid='004';

--3

create or replace trigger trigger_test2

instead of delete or update

on v_userinfo1

begin

    dbms_output.put_line('不能删除或更新该记录');

end;

/

delete from v_userinfo1;

select * from v_userinfo1;

update v_userinfo1 set username='*'||username;

-- 第十二章 用户与权限

-- 12-1 

create user user_test1 identified by 123456

default tablespace users;

-- 12-2 

create user user_test2 identified by 123456

quota 10m on users

account lock;

-- 12-3

alter user user_test1 identified by 654321;

-- 12-4

alter user user_test2 default tablespace system account unlock;

-- 12-5 删除用户

drop user user_test2 cascade;

-- 

select * from system_privilege_map;

-- 12-6 授予创建表权限

grant create table to user_test1;

grant create session to user_test1;

-- 12-7

grant create view, drop any view to user_test1

with admin option;

-- 12-8

select * from userinfo;

grant update on userinfo to user_test1

with grant option;

-- 12-9

grant select, update, delete, insert on userinfo to user_test1;

-- 12-10

revoke create table 

from user_test1;

-- 12-11

revoke create view from user_test1, user_test2;

-- 12-12

revoke update on userinfo

from user_test1;

-- 12-13 查看用户系统权限

select * from dba_sys_privs where grantee='user_test1';

-- 12-14 查看用户的对象权限

select grantee, table_name, privilege from dba_tab_privs where grantee='user_test1';

-- 角色

select * from dba_roles;

select privilege from dba_sys_privs where grantee='connect';

-- 12-15 创建角色

create role test_role1

not identified;

-- 12-16 创建角色并设置密码

create role test_role2

identified by 123456;

-- 12-17

grant create table, create sequence to test_role1;

-- 12-18 授予connect 角色权限

grant connect to test_role2;

-- 12-19 撤销权限

revoke create table from test_role1;

commit;

select * from dba_sys_privs where grantee='test_role1';

-- 12-20 撤销

revoke connect from test_role2;

-- 12-21 使test_role2 角色失效

set role all except test_role2;

-- 12-22 

set role test_role2 identified by 123456;

set role test_role2;

-- 12-23 给用户授予角色

grant test_role1 to user_test1;

grant select_catalog_role to scott;

grant exp_full_database to scott;

-- 12-24  撤销角色

revoke test_role1 from user_test1;

-- 管理角色

-- 12-25

alter role test_role1 

identified by 654321;

-- 12-26

drop role test_role1;

-- 习题

-- 3 操作题

-- 1

conn sys/123456 as sysdba;

create user test_user3 identified by 123456;

-- 2

grant create table, drop any table to test_user3;

grant select, update on userinfo to test_user3;

-- 3

revoke update on userinfo from test_user3;

-- 4

select * from dba_sys_privs where grantee='test_user3';

select grantee, table_name, privilege from dba_tab_privs where grantee='test_user3';

-- 5 修改角色密码

create role user3_role 

identified by 123456;

-- 赋予权限

grant create table, drop any table to user3_role;

-- 6

grant user3_role to test_user3;

-- 7

drop role user3_role;

drop user test_user3;

--  第十三章 备份与恢复

-- 数据库备份

archive log list;

alter system set log_archive_start= true scope=spfile;

shutdown immediate;

startup mount;

alter database archivelog;

alter database noarchivelog; 

alter database open;

alter tablespace users begin backup;

alter tablespace users end backup;

exp system/123456

exp system/123456 file=d:\test1.dmp tables=userinfo

exp system/123456 file=d:\testspace.dmp tablespaces=users

exp scott/oracle file=d:\dept.dmp tables=dept;

-- 使用EXPDP 工具导出数据

create directory directory_test as 'd:\directory';

-- 赋予权限

grant read, write on directory directory_test to scott;

expdp scott/123456 dumpfile=userinfo.dmp directory=directory_test tables=userinfo;

alter system archive log current;

-- 关闭服务器

shutdown immediate;

-- 开启服务器

startup;

alter database datafile 4 offline drop;

alter database open;

recover datafile 4

alter database datafile 4 online;

imp system/123456

-- 导出userinfo 表

imp system/123456 file=d:\test.dmp tables=userinfo;

-- 使用IMPDP 导入数据

impdp scott/123456 directory =directory_test dumpfile =userinfo.dmp tables=userinfo;

-- 习题

-- 操作题

-- 1

exp scott/123456 file=d:\dept.dmp tables=dept;

exp scott/[email protected]:1522/oracle file=d:\dept.dmp tables=dept;

-- 2

imp scott/123456 file=d:\dept.dmp tables=dept;

-- 3

connect system/123456;

create tablespace test

 datafile 'F:\oracle\test.dbf' size 10m

 AUTOEXTEND OFF;

exp system/123456 file=d:\testspace.dmp tablespaces=test-- 导出表空间

imp system/123456 file=d:\testspace.dmp tablespaces=test; -- 导入表空间

-- 第十四章 使用java 语言开发学生选课系统

create table sys_students

(

    stuid varchar(10) primary key,

    stuname varchar2(12),

    stupwd varchar2(20),

    stusex varchar2(4),

    stuinstitute varchar2(20)

);

create table sys_admin

(

    admid int primary key,

    admname varchar2(12),

    admpwd varchar2(20)

);

drop table sys_showlog;

create table sys_showlog

(

    logid int primary key,

    stuid varchar(10),

    username varchar2(12),

    doing varchar2(20),

    msg varchar2(100),

    logdate varchar2(30)

);

insert into sys_showlog values('1','106','dfsdfs','登录系统','游齐登录系统成功！','2018-12-02')

insert into sys_showlog values('1','106','null','登录系统','游齐登录系统成功！','2018-12-02 16:18:40')

desc sys_students;

create table sys_courses

(

    couid varchar2(10) primary key,

    couname varchar2(20),

    teacher varchar2(12),

    credit number(3,1),

    couexp varchar2(50)

);

update sys_courses set couname='C语言',

    teacher='张无忌',

    credit=1,

    couexp='C语言'

    where couid='012';

commit;

insert into sys_courses values('012','C语言程序设计','张素',2，'C语言程序设计')

commit;

create table sys_stucou

(

    stuid varchar2(10),

    couid varchar2(10),

    primary key(stuid,couid)

);

create table cou

(

    id int primary key,

    cName varchar2(20),

    tName varchar2(12),

    descript varchar2(50),

    score number(5,2)

);

execute MGMT_USER.MAKE_EM_USER('scott');

select * from sys_students;

select * from stuinfo;

select stuId from sys_stucou where stuid='101' and couid='001'

select * from SYS_COURSES where COUID in(select couid from sys_stucou where stuid='101') and couname like '%基础%'

select couid from sys_stucou where stuid='101'

delete from sys_stucou where stuid='101' and couid='001'

commit;

update sys_students set stupwd='11111' where stuid='101';

insert into sys_showlog values('2','101','null','登录系统','游青洪登录系统成功！',null)

select l.logid,s.stuid,l.doing,l.msg,l.logdate from sys_showlog l,sys_students s 

    where s.stuid=l.stuid and l.stuid='101' order by l.logid DESC;

select l.logid,s.stuid,l.doing,l.msg,l.logdate  

from sys_showlog l,sys_students s  where s.stuid=l.stuid 

and l.stuid='102' order by l.logid DESC

select l.logid,s.stuname,l.doing,l.msg,l.logdate 

from sys_showlog l,sys_students s 

where s.stuid=l.stuid and s.stuid!=1 order by l.logid DESC

select l.logid,s.stuname,l.doing,l.msg,l.logdate  

from sys_showlog l,sys_students s  where s.stuid=l.stuid 

and s.stuid!='1' order by l.logid DESC

select l.logid,s.admname,l.doing,l.msg,l.logdate  

from sys_showlog l,sys_admin s 

where s.admid=l.stuid and l.stuid='1' order by l.logid DESC;

select s.stuid,s.stuname,c.couname,c.teacher,c.couexp from 

sys_stucou sc, sys_students s,sys_courses c where 

s.stuid = sc.stuid and c.couid=sc.couid;

select s.stuid,s.stuname, c.couname,c.teacher,c.couexp  

from sys_stucou sc, sys_students s,sys_courses c  

where s.stuid = sc.stuid and c.couid=sc.couid;