ORACLE实例练习一

1. 建表

create table stu (
sno varchar2 (10) not null,
sname varchar2(10),
sgentle varchar2(2),
sbirth date,
sdept varchar2(20))

tips:
date 存储日期与时间
timestamp 年月日时分秒 秒后6位同时包含时区
clob 存储大文本，ex：非结构化的XML文档
blob 二进制对象，ex：图形、视频、声音等

2. 添加字段

SQL> l
  1  alter table stu
  2  add snajor varchar2(20)
  3   default '计算机科学与技术'
  4* not null
SQL> /
alter table stu
*
第 1 行出现错误:
ORA-00600: 内部错误代码, 参数: [kkdlReadOnDiskDefVal: bad defValLen], [24],
[20], [], [], [], [], [], [], [], [], []

这里有

报错ORA-00600

，参考https://stackoverflow.com/questions/18050871/ora-00600-internal-error-code-arguments-4194-65-51?r=SearchResults
重新打开一个命令窗口后不再报错。

3. 修改字段

SQL> l
  1  alter table stu
  2     modify  snajor varchar2(200)
  3*  default '计算机科学与技术'

4. 删除字段

SQL> alter table stu
  2  drop column sbirth
  3  /
alter table stu
*
第 1 行出现错误:
ORA-12988: 无法删除属于 SYS 的表中的列

sys用户不能删除表中字段，那就只好新建一个用户了

SQL> create user scott identified by tiger;
create user scott identified by tiger
            *
第 1 行出现错误:
ORA-65096: 公用用户名或角色名无效

新建用户由于版本不一样，12和11的命令似乎不一样，12在CDB模式下需要以C##开头新建用户

新建用户

SQL> create user c##a identified by password;
SQL> grant dba,connect to  c##a;

授权成功。

5. 重命名表

两种方法

SQL> rename stu to stu1;

表已重命名。
SQL> alter table stu1 rename to stu;

表已更改。

6. 复制相同结构的表

SQL> create table stu1 as select * from stu where 1=2;

表已创建。

SQL> desc stu1;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 SNO                                       NOT NULL VARCHAR2(10)
 SNAME                                              VARCHAR2(10)
 SGENTLE                                            VARCHAR2(2)
 SBIRTH                                             DATE
 SDEPT                                              VARCHAR2(20)

where 1 = 2

表示只复制表结构，不复制表数据
也可写成

where rownum<1

7. DROP删除表

SQL> drop table stu1;

表已删除。

drop执行后，目标表被移除到回收站中，基于该表的索引、触发器等对象也同时删除；
该表并未完全消失，而是被重命名为系统定义名称，存在于统一表空间中，具有与原始表相同结构，还可被恢复。

8. 创建主键约束

一个表只能有一个主键约束，可以为一列或一组列。
主键保证数据的唯一性和非空
创建表时

SQL> create table stu(
  2  sno varchar2(10) primary key,
  3  sname varchar2(10),
  4  sgentle varchar2(2),
  5  sage number(2),
  6  sbirth date,
  7  sdept varchar2(20))
  8  /

创建表后

SQL> alter table stu add constraint pk_stu primary key(sno)

9. 创建唯一性约束

unique约束保证了在非主键列不输入重复的值，每个字段只允许一个空值

SQL> create table stu(
  2  sno varchar2(10) unique,
  3  sname varchar2(10),
  4  sgentle varchar2(2),
  5  sage number(2),
  6  sbirth date,
  7  sdept varchar2(20))
  8  /

表已创建。

10. 创建CHECK约束

限制输入到一列或者多列的值的范围

• Oracle PL/SQL支持单独一列使用多个check约束，按约束创建的顺序对其取值
• 可在表一级创建check约束，再应用到多列

建表时创建：

SQL> create table stu(
  2  sno varchar2 (10),
  3  sname varchar2(10),
  4  sgentle varchar2(10),
  5  check(sgentle='男' or sgentle='女'),
  6  sage number(2)
  7  check(sage>=10 and sage<=90),
  8  sbirth date,
  9  sdept varchar2(20))
 10  /

表已创建。

建表后创建：

SQL> alter table stu
  2  add constraint ck_stu
  3  check(sno>='120001' and sno<='129999')
  4  /

表已更改。

查看所有约束

SQL> l
  1  select table_name,constraint_name,constraint_type
  2  from user_constraints
  3* where table_name='STU'
SQL> /

TABLE_NAME  CONSTRAINT_NAME        CO
----------- ---------------------- --
STU         SYS_C008453            C
STU         SYS_C008454            C
STU         CK_STU                 C

• C表示约束属于CHECK类型
• P表示PRIMARY KEY
• U表示UNIQUE KEY
• R表示外键FOREIGN KEY

11. 创建外键约束

• 即FOREIGN KEY约束，为表中的一列或者多列提供数据完整性参照；
• 通常与PRIMARY约束或者UNIQUE约束同时使用；
• 可包含空值，包含空值时将跳过所有值的验证

SQL> create table stu1 (
 2  sno varchar2(10),
 3  foreign key(sno）
 4  references stu(sno),
 5  sname varchar2(10),
 6  sgentle varchar2(2),
 7  sage number(2),
 8  sbirth date,
 9  sdept varchar2(20))
10  /
references stu(sno),
               *
第 4 行出现错误:
ORA-02270: 此列列表的唯一关键字或主键不匹配

引用stu表的SNO字段作为STU1表的外键，必须保证SNO是STU的主键

SQL> save d:1.sql
已创建 file d:\1.sql
SQL> alter table stu add constraint pk_stu primary key(sno);

表已更改。

SQL> @ d:1.sql

表已创建。

SQL> select table_name ,constraint_name,constraint_type from user_constraints where table_name in('STU','STU1');

TABLE_NAME  CONSTRAINT_NAME        CO
----------- ---------------------- --
STU         SYS_C008453            C
STU         SYS_C008454            C
STU         CK_STU                 C
STU         PK_STU                 P
STU1        SYS_C008457            R

追加外键约束

SQL> l
  1  alter table stu1
  2  add constraint fk_stu
  3*  foreign key(sno) references stu(sno

12. 删除约束

通过确认的约束名删除

SQL>  select table_name ,constraint_name,constraint_type from user_constraints where table_name in('STU','STU1');

TABLE_NAME  CONSTRAINT_NAME        CO
----------- ---------------------- --
STU         SYS_C008453            C
STU         SYS_C008454            C
STU         CK_STU                 C
STU         PK_STU                 P
STU1        SYS_C008457            R

SQL> alter table stu
  2  drop constraint SYS_C008453
  3  /

表已更改。

SQL>  select table_name ,constraint_name,constraint_type from user_constraints where table_name in('STU','STU1');

TABLE_NAME  CONSTRAINT_NAME        CO
----------- ---------------------- --
STU1        SYS_C008457            R
STU         SYS_C008454            C
STU         CK_STU                 C
STU         PK_STU                 P

13. 为字段设置默认值

• 默认值不属于约束，无法在约束中查看
• 只能使用下列三种：

1. 常量值（字符串）
2. 系统函数（SYSTEM_USER()）
3. NULL

• 只能在创建表时执行

SQL> L
  1  create table stu (
  2  sno varchar2 (10)
  3  primary key,
  4  sname varchar2(10)
  5  not null,
  6   sgentle varchar2(4)
  7  default '男',
  8  sage number(2),
  9  sbirth date,
 10  sdept varchar2(20)
 11* default '暂未分配')
SQL> /

表已创建。