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('anny@163.com','@','#'),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','aa@126.com','12345678');
insert into stuinfo values(2,'李明','123456','bb@126.com','12345678');
insert into stuinfo values(3,'刘想','123456','cc@126.com','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/123456@localhost: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;