Oracle笔记

Microsoft Windows [版本 5.2.3790]
(C) 版权所有 1985-2003 Microsoft Corp.

C:/Documents and Settings/Administrator>sqlplus "sys/12345 as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 12月 31 08:39:04 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> get E:/oracle实训/createtablespace.sql
  1  create  tablespace testhe
  2     datafile 'e:/testhe.dbf' size 4m autoextend on next 1m maxsize 20m
  3          permanent online logging;
  4  create user admin
  5     identified by admin
  6     default tablespace testhe;
  7* grant dba to admin;
  8
SQL> start E:/oracle实训/createtablespace.sql

授权成功。

SQL> get E:/oracle实训/createtables.sql
  1  create table Department_info
  2  (
  3   dep_no varchar(10) not null primary key,
  4   dep_name varchar(20) not null ,
  5   dep_director varchar(8) ,
  6   dep_address varchar(50),
  7   dep_count int
  8  );
  9  create table Student_info
 10  (
 11   dep_no varchar(9) not null,
 12   stu_no varchar(9) primary key not null,
 13   name   varchar(8) not null,
 14   sex    varchar(4) default '男',
 15   stu_type varchar(4)  default '普招',
 16   enter_date   date,
 17   nativeplace varchar(20) ,
 18   id     varchar(18),
 19   homeaddress varchar(50),
 20   birthday date,
 21   speciality varchar(16),
 22   nation varchar(4),
 23   phone  varchar(13),
 24   free_aggregate decimal(18,1),
 25   buildcredithour number(3,1),
 26   remark varchar(50)
 27  );
 28  create table Course_info
 29  (
 30   course_no varchar(4) primary key not null,
 31   course_name varchar(30) not null,
 32   setgrade varchar(20),
 33   periodcount int ,
 34   credithour number(2,1) default 0
 35  );
 36  create table Grade_info
 37  (
 38   stu_no varchar(9)  not null,
 39   course_no varchar(4) not null,
 40   term varchar(20),
 41   grade smallint default 0,
 42   grade_remark varchar(50)
 43  );
 44  create table Popedom
 45  (
 46   popedom_no int primary key not null,
 47   popedom_type varchar(10) not null
 48  );
 49  create table User_info
 50  (
 51   username varchar(10) primary key not null,
 52   password varchar(16) default 'admin',
 53   state    int default 0,
 54   popedom_no  int
 55* );
 56
SQL> start E:/oracle实训/createtables.sql
SQL> get E:/oracle实训/insertrecord.sql

SQL> set linesize 138
SQL> set pagesize 100
SQL> select * from department_info;
SQL> select * from course_info;
SQL> select * from grade_info;
SQL> select * from user_info;
SQL> select * from popedom;
SQL> set linesize 300
SQL> set pagesize 100
SQL> select * from department_info;
已选择10行。

SQL> desc user_info;
//创建外键
SQL> alter table student_info
  2   add(constraint fk_department_Student_dep_no foreign key(dep_no)
  3     references department_info(dep_no));

表已更改。

SQL> alter table Grade_info
  2   add(constraint fk_course_grade_course_no foreign key(course_no)
  3     references Course_info(course_no));

表已更改。

SQL> alter table grade_info
  2   add(constraint fk_student_grade_stu_no foreign key(stu_no)
  3     references student_info(stu_no));

表已更改。

SQL> alter table user_info
  2   add(constraint fk_popedom_user_popedom_no foreign key(popedom_no)
  3     references popedom(popedom_no));

表已更改。

SQL> create view stu_info
  2    as
  3    select stu_no,course_info.course_name,term,grade
  4    from course_info,grade_info
  5    where course_info.course_no=grade_info.course_no;

视图已创建。

SQL> select * from stu_info;

 

SQL> create view stu_grade_info
  2    as
  3    select student_info.stu_no as 学号,name as 姓名,course_info.course_name as 课程名,term as 学期,grade as成绩
  4    from student_info,course_info,grade_info
  5    where student_info.stu_no=grade_info.stu_no and course_info.course_no=grade_info.course_no;

视图已创建。

SQL> select * from stu_grade_info;

SQL> create view stu_base_info
  2    as
  3    select department_info.dep_name as所属院系 ,student_info.stu_no as 学号,name as 姓名,sex as 性别,enter_date as 入学日期,homeaddress as 家庭住址
  4    from student_info,department_info
  5    where student_info.dep_no=department_info.dep_no;

视图已创建。

SQL> select * from stu_base_info;