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;