关于Oracle和pl/sql的学习记录2

------操作数据表结构------
1.在ANSI/ISO SQL标准中，SQL语句可以分为DDL(数据定义)、DML(数据操作)和DCL(数据控制)三类

2.基本表是数据库的重要组成部分，是许多诸如试图、索引等数据对象的数据来源。一个数据库中可以有多张基本表，在使用基本表前需要先创建该表；创建基本表主要是指对一个基本表的结构定义，也即是对创建一个基本表所需要的元素作定义
创建学生表：
create table stu(
  sno varchar2(10) not null,
  sname varchar2(10),
  sgender varchar2(2),
  sage number(2),
  sbirth date,
  sdept varchar2(20)
)
Oracle支持的主要数据类型：
char(length) 存储固定长度的字符串，最长不超过2000字节
varchar2(length) 存储可变长度的字符串，最长不超过4000字符
number(p,s) 既可以存储浮点数，也可以存储整数，p表示数字的最大位数(默认38位)，s指小数位数
date 存储日期时间
timestamp 不但存储日期的年月日，时分秒，以及秒后6位，同时包含时区
clob 存储大的文本，比如存储非结构化的XML文档
blob 存储二进制对象，如图形、视频、声音等

3.修改表结构，增加新字段：
alter table stu
add smajor varchar2(20) default '计算机科学与技术' not null

修改已有字段：
alter table stu
modify sname varchar2(15) default '无' not null
在指定字段数据类型为字符型的字段的默认值时，需要使用一对单引号括起来，数值型数据类型则不用

删除已有字段：
alter table stu
drop column sbirth

4.重命名基本表：
rename stu to stu1
或者
alter table stu rename to stu1

5.当实际应用中需要使用两个相同结构的数据表时，可以直接通过表结构复制功能实现
通过create table命令复制一个与stu表结构相同的基本表stu1:
a.通过desc stu1命令查看stu1是否存在
desc stu1;
b.复制表结构
create table stu1
as 
select * from stu where 1=2;
其中，where子句表示只复制表结构，不复制表数据；写法不唯一，例如可改成where rownum<1
c.执行后可以再次使用desc stu1查看stu1的表结构

6.删除表
drop table stu1;
执行后，目标表即从数据库中被移除到回收站中，基于该表的索引/触发器等数据对象也同时被删除
使用drop table命令被删除的表并没有完全消失，而是重命名为一个由系统定义的名称，它存在于同一个表空间中，具有与原始表相同的机构，还可以被恢复

7.基本表通常具有包含唯一标识表中每一行的值的一列或一组列，这样的一列或多列称为表的主键(PK)，用于强制表的实体完整性；一个表只能有一个PRIMARY KEY约束，并且PRIMARY KEY约束中的列不能为空
给stu表加主键约束sno:
desc table stu;//如果存在则删除表
drop table stu;
create table stu(
  sno varchar2(10) primary key,
  sname varchar2(10),
  sgender varchar2(2),
  sage number(2),
  sbirth date,
  sdept varchar2(20)
)
如果一个数据表已经存在，则使用alter table来实现添加主键约束：
alter table stu
add constraint pk_stu primary key(sno);
其中add constraint表示追加约束，pk_stu市主键约束的名称

8.唯一性约束即unique约束，用于确保在非主键列中不输入重复的值(包括空值)
unique约束允许null值
可在创建表时加unique约束，也可使用alter table来添加：
alter table stu
add constraint un_stu unique(sname);

9.check约束用于限制输入到一列或者多列的值的范围，如果想输入的数据值如果不满足check约束中的条件将无法正常输入
创建表时添加：
create table stu(
  sno varchar2(10),
  sname varchar2(10),
  sgender varchar2(2) check(sgender='男' or sgender='女'),
  sage number(2) check(sage>=10 and sage<=90)
)
修改表添加：
alter table stu
add constraint ck_stu check(sno>='10000' and sno<='10099');

10.可以使用select查询显示约束：
select table_name,constraint_name,constraint_type from user_constraints where table_name = 'stu';
约束有如下几种类型：
C：表明约束属于check类型，在这种情况下，RDBMS对非空值进行检查
P：表明约束与主键结合
U：表明约束与唯一性约束结合
R：表明约束与外键结合

11.外键约束(foreign key)，其作用是为表中的一列或者多列数据提供数据完整性参照，通常是与primary key和unique约束同时使用的
外键是用于建立和加强两个表数据之间的链接的一列或多列
创建：
create table stu1(
  sno varchar2(10),foreign key(sno) references stu(sno),
  sname varchar2(10),
  sgender varchar2(2),
  sage number(2),
  sbirth date,
  sdept varchar2(20)
)
其中，要引用stu表的sno字段作为stu1表的外键，首先必须保证sno字段是stu表的主键
修改表：
alter table stu1
add constraint fk_stu foreign key(sno) references stu(sno);

外键约束可以包含空值，的那hi，如果任何组合外键约束的列包含控制，则将跳过组合外键约束的所有值的验证

12.删除约束：
alter table stu
drop constraint ck_stu;
删除的约束只能是与其他表无关联的约束，如果该约束同时作为其他表的外键，则不能删除

13.字段设置默认值
用default关键字指定某个字段的默认值
create table stu(
  sno varchar2(10) primary key,
  sname varchar2(10) not null,
  sgender varchar2(2) default '男',
  sage number(2),
  sbirth date,
  sdept varchar2(20) default '暂未分配'
)
并不是所有数据类型的数据值都可以作为默认值；在SQL中，default关键字后的值只能为下列三种值中的一种：
常量值(如字符串)
系统函数(如system_user)
null
同时，oracle中default不是约束；使用default关键字为字段设置默认值是表结构操作，其只能在创建表的同时执行，即在create table命令中被执行