Oracle的一些例子代码

╰+哭是因爲堅強的太久メ 2022-07-12 03:06 174阅读 0赞

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 LOCAL

2.创建用户并授权访问

-- USER SQL
    CREATE USER yyf IDENTIFIED BY yyf 
    DEFAULT TABLESPACE "YYFTABLESPACE"
    TEMPORARY TABLESPACE "TEMP"
    ACCOUNT LOCK
    
    -- QUOTAS
    
    -- ROLES
    GRANT "RESOURCE" TO yyf ;
    GRANT "CONNECT" TO yyf ;
    ALTER USER yyf DEFAULT ROLE "RESOURCE","CONNECT";
    
    -- SYSTEM PRIVILEGES
    GRANT UNLIMITED TABLESPACE TO yyf ;

3.锁定用户

ALTER USER "yyf" ACCOUNT LOCK

4.解锁用户

ALTER USER "yyf" ACCOUNT UNLOCK

5.修改用户密码

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 c
    on u.user_id='20130101001' and u.class_id= c.class_id
    inner join professional_info p
    on 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_ID
    and 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 = 1
    where 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 = 1
    where 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_info
    where 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_ID
    order 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 r
    inner join USER_INFO u on u.CLASS_ID = '20130101' and r.USER_ID = u.USER_ID
    where r.COURSE_ID = -1 and r.TERM_ID = 8 order by r.RESULT_VALUE desc ;
    --);

存储过程

create or replace procedure yyf is
    begin
      insert into role_info(role_id,role_info) values(3,'老师1');
      dbms_output.putline('执行成功！');
      commit;
    end yyf;

曾经课后一些代码例子。（好像已经很久没碰Oracle了）