创建表空间和表
ORACLE物理上是由磁盘上的以下几种文件:数据文件和控制文件和LOGFILE构成的
oracle中的表就是一张存储数据的表。表空间是逻辑上的划分。方便管理的。
数据表空间 (Tablespace)
存放数据总是需要空间, Oracle把一个数据库按功能划分若干空间来保存数据。当然数据存放在磁盘最终是以文件形式,所以一盘一个数据表空间包含一个以上的物理文件
数据表
在仓库,我们可能有多间房子,每个房子又有多个货架,每架又有多层。 我们在数据库中存放数据,最终是数据表的单元来存储与管理的。
数据文件
以上几个概念都是逻辑上的, 而数据文件则是物理上的。就是说,数据文件是真正“看得着的东西”,它在磁盘上以一个真实的文件体现
创建表空间
格式:
create tablespace 表间名 datafile '数据文件名' size 表空间大小
create tablespace data_test datafile 'e:\oracle\oradata\test\data_1.dbf' size 2000M;
create tablespace idx_test datafile 'e:\oracle\oradata\test\idx_1.dbf' size 2000M;
-- (*数据文件名 包含全路径, 表空间大小 2000M 表是 2000兆)
建好tablespace, 就可以建用户了
格式:
create user 用户名 identified by 密码 default tablespace 表空间表;
create user study identified by study default tablespace data_test;
-- 我们创建一个用户名为 study,密码为 study, 缺少表空间为 data_test -这是在第二步建好的.
-- 缺省表空间表示 用户study今后的数据如果没有专门指出,其数据就保存在 data_test中, 也就是保存在对应的物理文件 e:\oracle\oradata\test\data_1.dbf中
创建用户并指定表空间
CREATE USER cici IDENTIFIED BY cici PROFILE DEFAULT DEFAULT TABLESPACE CICI ACCOUNT UNLOCK;
create user jykl identified by jykl default tablespace jykl_data temporary tablespace jykl_temp;
授权给新用户
GRANT connect, resource TO cici;
grant create session to cici;
grant connect,resource to study;
--表示把 connect,resource权限授予study用户
grant dba to study;
--表示把 dba权限授予给 study
创建数据表
在上面,我们已建好了用户 study 我们现在进入该用户
sqlplusw study/study@test
-- 然后就可以在用户study中创建数据表了
格式:
create table 数据表名
oracle命令建立主键外键
1、创建一张学生表
create table t_stu(
stuid number(10) primary key,
stuname varchar2(20) not null,
stusex varchar2(2) default '男' check(stusex in('男','女'))
);
2、创建一张课程表
create table t_couse(
couseid number(10) primary key,
cousename varchar2(20) not null,
cousetype varchar2(4)
);
3、创建一张学生课程成绩表(包括主外键)
create table t_score(
scoreid number(10) primary key,
stuid number(10) references t_stu(stuid),
couseid number(10),
constraint fk_couseid foreign key(couseid)
references t_couse(couseid)
on delete cascade
);
orale表管理:
Oracle创建表同SQL Server一样,使用CREATE TABLE命令来完成。创建约束则使用如下命令:
语法格式:alter table命令
alter table 表名 add constraint 约束名 约束内容。
不论创建表还是约束,与SQL Server基本相同,注:在Oracle中default是一个值,而SQL Server中default是一个约束,因此Oracle的default设置可以在建表的时候创建。
案例1:创建一个学生信息(INFOS)表和约束
Oracle创建表和约束
create table INFOS(
STUID varchar2(7) not null, --学号 学号=‘S’+班号+2位序号
STUNAME varchar2(10) not null, --姓名
GENDER varchar2(2) not null, --性别
AGE number(2) not null, --年龄
SEAT number(2) not null, --座号
ENROLLDATE date, --入学时间
STUADDRESS varchar2(50) default '地址不详', --住址
CLASSNO varchar2(4) not null, --班号 班号=学期序号+班级序号
);
-- 创建主键约束
alter table INFOS add constraint pk_INFOS primary key(STUID) ;
-- 创建check约束
alter table INFOS add constraint ck_INFOS_gender check(GENDER = '男' or GENDER = '女');
alter table INFOS add constraint ck_INFOS_SEAT check(SEAT >=0 and SEAT <=50);
alter table INFOS add constraint ck_INFOS_AGE check(AGE >=0 and AGE<=100);
alter table INFOS add constraint ck_INFOS_CLASSNO check((CLASSNO >='1001' and CLASSNO<='1999') or (CLASSNO >='2001' and CLASSNO<='2999')) ;
-- 唯一约束
alter table INFOS add constraint un_STUNAME unique(STUNAME) ;
案例2:创建一个成绩表(SCORES)表和约束
Oracle创建表和约束
create table scores(
ID number, --ID ①
TERM varchar2(2), --学期 S1或S2
STUID varchar2(7) not null, --学号
EXAMNO varchar2(7) not null, --考号 E+班号+序号
WRITTENSCORE number(4,1) not null, --笔试成绩
LABSCORE number(4,1) not null, --机试成绩
);
ALTER TABLE SCORES ADD CONSTRAINT CK_SCORES_TERM CHECK(TERM = 'S1' OR TERM ='S2');
-- 创建外键约束
ALTER TABLE SCORES ADD CONSTRAINT FK_SCORES_INFOS_STUID FOREIGN KEY(STUID) REFERENCES INFOS(STUID) ;
oracle建表、建主键、外键基本语法
创建表格语法:
create table 表名(
字段名1 字段类型(长度) 是否为空,
字段名2 字段类型 是否为空 );
增加主键
alter table 表名 add constraint 主键名 primary key (字段名1);
增加外键
alter table 表名 add constraint 外键名 foreign key (字段名1) references 关联表 (字段名2);
在建立表格时就指定主键和外键
create table T_STU(
STU_ID char(5) not null,
STU_NAME VARCHAR2(8) not null,
constraint PK_T_STU primary key (STU_ID)
);
主键和外键一起建立:
create table T_SCORE(
EXAM_SCORE number(5,2),
EXAM_DATE date,
AUTOID number(10) not null,
STU_ID char(5),
SUB_ID char(3),
constraint PK_T_SCORE primary key (AUTOID),
constraint FK_T_SCORE_REFE foreign key (STU_ID) references T_STU (STU_ID)
);
orale数据类型:
类型 | 含义 |
---|---|
CHAR(length) | 存储固定长度的字符串。参数length指定了长度,如果存储的字符串长度小于length,用空格填充。默认长度是1,最长不超过2000字节。 |
VARCHAR2(length) | 存储可变长度的字符串。length指定了该字符串的最大长度。默认长度是1,最长不超过4000字符。 |
NUMBER(p,s) | 既可以存储浮点数,也可以存储整数,p表示数字的最大位数(如果是小数包括整数部分和小数部分和小数点,p默认是38为),s是指小数位数。可存负数 |
DATE | 存储日期和时间,存储纪元、4位年、月、日、时、分、秒,存储时间从公元前4712年1月1日到公元后4712年12月31日。 |
TIMESTAMP | 不但存储日期的年月日,时分秒,以及秒后6位,同时包含时区。 |
CLOB | 存储大的文本,比如存储非结构化的XML文档 |
BLOB | 存储二进制对象,如图形、视频、声音等。 |
- 创建表时给字段夹默认值和约束条件
创建表时可以给字段加上默认值 例如 : 日期字段 DEFAULT SYSDATE 这样每次插入和修改时, 不用程序操作这个字段都能得到动作的时间
例如:IS_SEND NUMBER(1) default 1 —是否已发
创建表时可以给字段加上约束条件 例如: 非空 NOT NULL ,不允许重复 UNIQUE ,关键字 PRIMARY KEY ,按条件检查 CHECK (条件), 外键 REFERENCES 表名(字段名)
- 创建表的例子
```sql
create table DEPT(
DNAME varchar2(14),
LOC varchar2(6),
EPTNO number(2) constraint PK_DEPT primary KEY,
);
create table region(
ID number(2) not null primary KEY,
postcode number(6) default ‘0’ not null,
areaname varchar2(30) default ‘’ not null,
);
<a name="qYNMB"></a>
## 创建表时的命名规则和注意事项
1)表名和字段名的命名规则:必须以字母开头,可以含符号A-Z,a-z,0-9,_,$,#<br />2)大小写不区分<br />3)不用SQL里的保留字, 一定要用时可用双引号把字符串括起来<br />4)用和实体或属性相关的英文符号长度有一定的限制<br />5)约束名的命名规则和语法 约束名的命名规则约束名如果在建表的时候没有指明,系统命名规则是SYS_Cn(n是数字) 约束名字符串的命名规则同于表和字段名的命名规则<br />6)使用约束时的注意事项 约束里不能用系统函数,如SYSDATE和别的表的字段比较 可以用本表内字段的比较<br />注意事项:<br />1)建表时可以用中文的字段名, 但最好还是用英文的字段名<br />2)创建表时要把较小的不为空的字段放在前面, 可能为空的字段放在后面<br />3)建表时如果有唯一关键字或者唯一的约束条件,建表时自动建了索引<br />4)一个表的最多字段个数也是有限制的,254个.
想在事务处理后, 做约束的<br />检查 SQL> alter session set constraints deferred.
7. 由实体关系图到创建表的例子 s_dept 前提条件:已有region表且含唯一关键字的字段id
```sql
CREATE TABLE s_dept (
id NUMBER(7) CONSTRAINT s_dept_id_pk PRIMARY KEY,
name VARCHAR2(25) CONSTRAINT s_dept_name_nn NOT NULL,
region_id NUMBER(7) CONSTRAINT s_dept_region_id_fk REFERENCES region (id),
CONSTRAINT s_dept_name_region_id_uk UNIQUE(name, region_id)
);
较复杂的创建表例子
CREATE TABLE s_emp (
id NUMBER(7) CONSTRAINT s_emp_id_pk PRIMARY KEY,
last_name VARCHAR2(25) CONSTRAINT s_emp_last_name_nn NOT NULL,
first_name VARCHAR2(25),
userid VARCHAR2(8) CONSTRAINT s_emp_userid_nn NOT NULL CONSTRAINT s_emp_userid_uk UNIQUE,
start_date DATE DEFAULT SYSDATE,
comments VARCHAR2(25),
manager_id NUMBER(7),
title VARCHAR2(25),
dept_id NUMBER(7) CONSTRAINT s_emp_dept_id_fk REFERENCES s_dept(id), salary NUMBER(11,2),
commission_pct NUMBER(4,2) CONSTRAINT s_emp_commission_pct_ck CHECK (commission_pct IN(10,12.5,15,17.5,20))
);
通过子查询建表 通过子查询建表的例子 ```sql CREATE TABLE emp_41 AS SELECT id, last_name, userid, start_date FROM s_emp WHERE dept_id = 41;
— 只要表结构 CREATE TABLE A as select * from B where 1=2;
10. 用子查询建表的注意事项 :
1. 可以关连多个表及用集合函数生成新表,注意选择出来的字段必须有合法的字段名称,且不能重复。
2. 用子查询方式建立的表,只有非空NOT NULL的约束条件能继承过来, 其它的约束条件和默认值都没有继承过来.
3. 根据需要,可以用alter table add constraint ……再建立其它的约束条件,如primary key等.
11. Foreign Key的可选参数ON DELETE CASCADE 在创建Foreign Key时可以加可选参数: ON DELETE CASCADE它的含义是如果删除外键主表里的内容,子表里相关的内容将一起被删除. 如果没有ON DELETE CASCADE参数,子表里有内容,父表里的主关键字记录不能被删除掉.
12. 如果数据库表里有不满足的记录存在,建立约束条件将不会成功.
13. 给表创建和删除同义词的例子
```sql
CREATE SYNONYM d_sum 2 FOR dept_sum_vu;
CREATE PUBLIC SYNONYM s_dept 2 FOR alice.s_dept;
DROP SYNONYM s_dept;
语法:
-- 创建新表
create table 表名
( 字段名1(列名) 数据类型 列的特征,
字段名2(列名) 数据类型 列的特征(NOT NULL),
...... );
-- 创建带有主键约束的表语法
create table 表名 (
字段名1(列名) 数据类型 列的特征,
字段名2 数据类型 列的特征(NOT NULL),
...... primary key(主键列字段));
-- 利用现有的表创建表 仅复制Oracle数据表结构
create table 新表 as select * from 旧的表 where 1=2;
create table 新表 select 字段1,字段2... from 旧的表 where 条件(旧的表字段满足的条件);
-- 利用现有的表的结构创建新表
create table 新表 as select * from 旧的表 where 1=1;
create table 新表 as select 字段1,字段2... from 旧的表 where 条件;
-- 将查询结果插入另一张表
insert into 另一张表 select * from 要查询的表 where 条件