文章目录
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. 为字段设置默认值
- 默认值不属于约束,无法在约束中查看
- 只能使用下列三种:
- 常量值(字符串)
- 系统函数(SYSTEM_USER())
- 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> /
表已创建。