关系表创建始末

ora-00900

CREATE SMALLFILE TABLESPACE "LINUXSPACE" DATAFILE '/u/app/oracle/oradata/testorcl/LINUXDATA' SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO    ----创建永久表空间和数据文件

SQL> select * from v$tablespace;

       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
         0 SYSTEM                         YES NO  YES
         1 UNDOTBS1                       YES NO  YES
         2 SYSAUX                         YES NO  YES
         4 USERS                          YES NO  YES
         3 TEMP                           NO  NO  YES
         6 EXAMPLE                        YES NO  YES
         7 LINUXSPACE                     YES YES YES

7 rows selected.

SQL> create user oratest identified by oratest default tablespace linuxspace temporary tablespace temp;  －－－－－创建用户给予表空间

User created.

SQL> select username,default_tablespace,temporary_tablespace from user_users;

USERNAME   DEFAULT_TABLESPACE    TEMPORARY_TABLESPACE
-------------------- ------------------------------             ------------------------------
ORATEST                 TLM52                          TEMP

SQL> grant connect,resource,dba to oratest;  －－－－－预定义角色                                              

Grant succeeded.

SQL> connect oratest/oratest      ----login
Connected.
SQL> show user         --------show
USER is "ORATEST"
SQL>  create schema s-t authorization oratest;   ------给用户创建模式名

SQL> create table student(sno char(5) not null,   ---------创建student关系
  2  sname char(6),
  3  s*** char(2),
  4  sage varchar(4),
  5  sdept char(4),
  6  Constraint StudentPK Primary Key (Sno),
  7  Constraint SageCK Check (Sage > 0 AND Sage <150));

Table created.

SQL> create table course ( cno char(4) not null,  -----------创建课程关系
  2  cname varchar(20),
  3  cpno char(1),
  4  ccredit int default 0,
  5  constraint corusepk primary key (cno));

SQL> create table sc ( sno char(5) not null,        ------------选课关系
  2  cno char(4) not null,
  3  grade int default 0,
  4  constraint scpk primary key(sno,cno),
  5  constraint studentfk foreign key (sno) references student(sno),
  6  constraint coursefk foreign key (cno) references course(cno),
  7  Constraint GradeCK Check (Grade >= 0 AND Grade <= 100));

SQL> commit;

SQL> create table xxx as select * from student;  ------- 创建xxx
Table created.

SQL> Alter Table student Add age int constraint CheckAge check (age>0 and age<150); --------修改追加age 属性

SQL> drop table xxx;