数据库01 用户；建表

删除 创建 授权用户信息

SQL> drop user wcj  cascade;
用户已删除。
SQL> create user  wcj identified  by wcj;
用户已创建。
SQL> grant connect,resource to wcj;
授权成功。
SQL> connect wcj/wcj @orcl;

#更改 日期格式 
SQL> alter session set nls_date_format='rrrr-mm-dd';
会话已更改。

#查看当前用户下所有表信息
SQL> select table_name from user_tables ;
#查看 更改表列名 
desc table_name;
SQL> alter table TB_term rename column titleid to termid;

创建表

##四个基本表

SQL> create table TB_teachingyear (teachingyearid char(4) primary key, teachingyearname nchar(11) not null);
表已创建。

SQL> create table TB_term ( titleid char(2) primary key , titlename char(8) not null);
表已创建。

SQL>CREATE TABLE TB_Title                  
( TitleID CHAR(2) PRIMARY KEY,                                             
  TitleName CHAR(8) NOT NULL                         
)
SQL> create table TB_dept ( deptid char(2) primary key,deptname char(20) not null , deptsetdate date  not null ,deptscript nchar(50) not null ) ;
表已创建。

##6个对象信息表
SQL>CREATE TABLE TB_Spec                  
( SpecID CHAR(4) PRIMARY KEY,                                             
  SpecName CHAR(20) NOT NULL,                         
  DeptID CHAR(2) NOT NULL REFERENCES TB_Dept(DeptID),
  SpecScript TEXT NOT NULL
)  

#not null 和 default 冲突？？？ 
# date  和  smalldatetime   
SQL> create table TB_teacher ( teacherid char(6) primary key check (teacherid like 'T[0-9][0-9][0-9][0-9][0-9]' ),
  2  teachername char(8) not null ,
  3  deptid char(2) not null references TB_dept(deptid),
  4  sex char(1)  default('M') check (sex in ('M','F')),
  5  birthday date not null,
  6  tpassword varchar(32)  default('123456'),
  7  titleid char(2) not null references TB_title(titleid) ) ;



SQL> create table Tb_class (classid char(6) primary key, classname char(20) not null, deptid char(2) not null references tb_dept(deptid) , teacherid char(6) not null references tb_teacher(teacherid) );
表已创建。


SQL> CREATE TABLE TB_Student
  2  ( StuID CHAR(8) PRIMARY KEY CHECK (StuID LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),                         
  3    StuName CHAR(8) NOT NULL,
  4    EnrollYear CHAR(4) NOT NULL CHECK (EnrollYear LIKE '[0-9][0-9][0-9][0-9]'),
  5    GradYear CHAR(4) NOT NULL CHECK (GradYear LIKE '[0-9][0-9][0-9][0-9]'),
  6    DeptID CHAR(2) NOT NULL REFERENCES TB_Dept(DeptID),
  7    ClassID CHAR(6) NOT NULL REFERENCES TB_Class(ClassID),
  8    Sex CHAR(1)  DEFAULT('M') CHECK (Sex IN ('M','F')),
  9    Birthday date  NOT NULL,
 10    SPassword VARCHAR(32)  DEFAULT('123456'),
 11    StuAddress VARCHAR(64) NOT NULL,
 12    ZipCode CHAR(6) NOT NULL CHECK (ZipCode LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]')
 13  )    ;



# real 类型 ？？？？ 
SQL> create table TB_course(courseid char(6) primary key check(courseid like 'C[0-9][0-9][0-9][0-9][0-9]'),
  2  coursename  varchar(32) not null unique,
  3  deptid char(2)  references TB_dept(deptid),
  4  coursegrade real  default(0) check (coursegrade>=0),
  5  courseoutline nchar(50) ) ;
  
SQL> alter table tb_course add(lessontime int default(0) check (lessontime >=0));
表已更改。

##3个业务信息表

#constraint 添加约束信息
SQL> CREATE TABLE TB_CourseClass
  2  ( CourseClassID CHAR(10) PRIMARY KEY CHECK (CourseClassID LIKE 'T[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),  
  3    CourseID CHAR(6) NOT NULL REFERENCES TB_Course(CourseID),
  4    TeacherID CHAR(6) NOT NULL REFERENCES TB_Teacher(TeacherID),
  5    TeachingYearID CHAR(4) NOT NULL REFERENCES TB_TeachingYear(TeachingYearID),
  6    TermID CHAR(2) NOT NULL REFERENCES TB_Term(TermID),
  7    TeachingPlace VARCHAR(32) NOT NULL,
  8    TeachingTime VARCHAR(64) NOT NULL,
  9    CommonPart INT  DEFAULT(10) CHECK (CommonPart>=0),
 10    MiddlePart INT  DEFAULT(20) CHECK (MiddlePart>=0),
 11    LastPart INT  DEFAULT(70) CHECK (LastPart>=0),
 12    MaxNumber SMALLINT  DEFAULT(60) CHECK (MaxNumber>=0),
 13    SelectedNumber SMALLINT  DEFAULT(0),
 14    FullFlag CHAR(1)  DEFAULT('U') CHECK (FullFlag IN ('F','U')),
 15    CONSTRAINT CK_SumOfParts CHECK (CommonPart+MiddlePart+LastPart=100)
 16  )  ;


#on cascade on update cascade 

SQL> CREATE TABLE TB_SelectCourse
  2  ( StuID CHAR(8) NOT NULL REFERENCES TB_Student(StuID) ON DELETE CASCADE ,
  3    CourseClassID CHAR(10) NOT NULL REFERENCES TB_CourseClass(CourseClassID) ON DELETE CASCADE ,
  4    SelectDate DATE  ,
  5    CONSTRAINT PK_StuID_CourseClassID PRIMARY KEY (StuID, CourseClassID)
  6  )
  7  ;
表已创建。




SQL> CREATE TABLE TB_Grade
  2  ( GradeSeedID INT PRIMARY KEY,
  3    StuID CHAR(8) NOT NULL REFERENCES TB_Student(StuID),
  4    ClassID CHAR(6) NOT NULL REFERENCES TB_Class(ClassID),
  5    CourseClassID CHAR(10) NOT NULL REFERENCES TB_CourseClass(CourseClassID),
  6    CourseID CHAR(6) NOT NULL REFERENCES TB_Course(CourseID),
  7    CommonScore REAL DEFAULT(0) CHECK (CommonScore>=0 AND CommonScore<=100),
  8    MiddleScore REAL  DEFAULT(0) CHECK (MiddleScore>=0 AND MiddleScore<=100),
  9    LastScore REAL  DEFAULT(0) CHECK (LastScore>=0 AND LastScore<=100),
 10    TotalScore REAL DEFAULT(0) CHECK (TotalScore>=0 AND TotalScore<=100),
 11    RetestScore REAL DEFAULT(0) CHECK (RetestScore>=0 AND RetestScore<=100),
 12    LockFlag CHAR(1) DEFAULT('U') CHECK (LockFlag IN ('U','L'))
 13  )  ;
表已创建。

#创建序列
SQL> create sequence club_seq
  2  start with 1
  3  increment by 1
  4  nomaxvalue
  5  nocycle;
序列已创建。
#通过序列产生主键
insert into table_name values (club_seq.nextval,~~other_values);


#为上述表添加默认时间
SQL> alter  table TB_selectcourse modify ( selectdate date default sysdate );
表已更改。


SQL> alter table TB_term rename column titlename to termname;
表已更改。
SQL> alter table TB_title rename column termname to titlename;
表已更改。

#error
SQL> INSERT INTO TB_Teacher (TeacherID,TeacherName,DeptID,Sex,Birthday,TitleID)
  2  VALUES('T11111','陈','08','F','1998-12-2','T3');
INSERT INTO TB_Teacher (TeacherID,TeacherName,DeptID,Sex,Birthday,TitleID)
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (WCJ.SYS_C0012327)
#直接drop掉约束性条件 
SQL> alter table TB_teacher drop constraint sys_c0012327;
表已更改。