Oracle的一些例子代码

ALTER USER "yyf" ACCOUNT UNLOCK前提：Oracle成功安装
1.创建表空间
CREATE TABLESPACE YYFTABLESPACE     DATAFILE         'D:/DevRepository/oracle/YYFTABLESPACE' SIZE 5242880 REUSE AUTOEXTEND ON NEXT 134217728 MAXSIZE UNLIMITED     NOLOGGING     DEFAULT NOCOMPRESS     ONLINE     EXTENT MANAGEMENT LOCAL2.创建用户并授权访问
-- USER SQLCREATE USER yyf IDENTIFIED BY yyf DEFAULT TABLESPACE "YYFTABLESPACE"TEMPORARY TABLESPACE "TEMP"ACCOUNT LOCK-- QUOTAS-- ROLESGRANT "RESOURCE" TO yyf ;GRANT "CONNECT" TO yyf ;ALTER USER yyf DEFAULT ROLE "RESOURCE","CONNECT";-- SYSTEM PRIVILEGESGRANT UNLIMITED TABLESPACE TO yyf ;3.锁定用户
ALTER USER "yyf" ACCOUNT LOCK
4.解锁用户
ALTER USER "yyf" ACCOUNT UNLOCK5.修改用户密码
ALTER USER "yyf" IDENTIFIED BY 123 6.一些sql查询例子：
--01.查找学号为"20130101001"的学生信息select * from user_info where user_id='20130101001';--02.查找学号为"20130101001"的学生信息包含(专业名称,班级名称,学号,姓名,手机号码,家庭住址)select p.professional_name as 专业名称,c.class_name as 班级名称,u.user_id as 学号,u.user_name as 姓名,u.user_tel as 手机号码,u.user_address as 家庭住址 from user_info u  inner join class_info con u.user_id='20130101001' and u.class_id= c.class_idinner join professional_info pon c.professional_id = p. professional_id;--select class_id,class_name,professional_name from class_info c join professional_info p on c.professional_id = p.professional_id; --03.查找全校在读学生在1990年以后出生,并且家庭住址在湖南省的学生信息SELECT u.user_id,u.user_name,u.class_id,u.user_address,c.CLASS_START_YEAR,c.CLASS_END_YEAR from user_info u inner join CLASS_INFO c on u.class_id =  c.CLASS_IDand sysdate >= to_date(c.CLASS_START_YEAR,'yyyy') and sysdate <= to_date(c.CLASS_END_YEAR,'yyyy')where user_birthday >= to_date('1990-01-01','yyyy-MM-dd') and user_address like '湖南%';--04.查找本学期所有成绩都在80分以上的优先学生的信息select * from(SELECT DISTINCT(r.user_id) FROM RESULT_INFO r inner join TERM_INFO t on r.TERM_ID = t.TERM_ID  and t.TERM_YEAR = 2015 and t.TERM_FIRST = 1where r.user_id not in(  SELECT r.USER_ID FROM RESULT_INFO r inner join TERM_INFO t on  r.RESULT_VALUE < 80   and r.TERM_ID = t.TERM_ID  and t.TERM_YEAR = 2015 and t.TERM_FIRST = 1    ) ) q inner join USER_INFO u on u.USER_ID = q.USER_ID order by u.user_id;SELECT * from user_info where user_id in(  SELECT user_id from result_info  where     term_id in(      select term_id from term_info where term_year = 2015 and term_first = 1    ) and    user_id not in (      SELECT user_id from result_info where term_id in(        SELECT term_id from term_info where term_year = 2015 and term_first = 1      )and result_value < 80    ) ) order by user_id;--05.查找本学期所有成绩都在60分以下的需要补考的学生信息select * from(SELECT DISTINCT(r.user_id) FROM RESULT_INFO r inner join TERM_INFO t on r.TERM_ID = t.TERM_ID  and t.TERM_YEAR = to_number(to_char(sysdate,'yyyy')) and t.TERM_FIRST = 1where r.user_id not in(  SELECT r.USER_ID FROM RESULT_INFO r inner join TERM_INFO t on  r.RESULT_VALUE >=70   and r.TERM_ID = t.TERM_ID  and t.TERM_YEAR = to_number(to_char(sysdate,'yyyy')) and t.TERM_FIRST = 1    ) ) q inner join USER_INFO u on u.USER_ID = q.USER_ID;--06.查找"20130101"班在当前学期需要学习的课程信息select * from course_infowhere course_id in(  select course_id from result_info where user_id in(    select user_id from user_info where class_id = '20130101'  )and term_id in (select term_id from term_info t where t.TERM_YEAR = '2015' and t.TERM_FIRST = 1 ));--07.统计2013级的各班级的人数(班级编号,班级名称,专业名称,班级人数)select t.class_id as 班级编号,c.class_name as 班级名称,p.PROFESSIONAL_NAME as 专业名称,t.班级人数 from class_info c inner join (select count(u.CLASS_ID) as 班级人数,u.CLASS_ID from USER_INFO u group by u.CLASS_ID) t on c.CLASS_START_YEAR = '2013' and c.CLASS_ID = t.CLASS_ID inner join PROFESSIONAL_INFO p on p.PROFESSIONAL_ID = c.PROFESSIONAL_IDorder by t.CLASS_ID;--08.编写"20130101"班"大学英语"的成绩表的SQL语句--select max(成绩) from(SELECT u.user_id as 学号,u.USER_NAME as 姓名,r.RESULT_VALUE as 成绩 FROM RESULT_INFO rinner join USER_INFO u on u.CLASS_ID = '20130101' and r.USER_ID = u.USER_IDwhere r.COURSE_ID = -1 and r.TERM_ID = 8 order by r.RESULT_VALUE desc ;--);存储过程
create or replace procedure yyf isbegin  insert into role_info(role_id,role_info) values(3,'老师1');  dbms_output.putline('执行成功！');  commit;end yyf;曾经课后一些代码例子。（好像已经很久没碰Oracle了）