Oracle基本数据类型及表的创建3
一、几个命令行操作
检查监听器状态： lsnrctl status
尝试登录本地数据库：sqlplus /nolog； sqlplus "/as sysdba"
启动监听器 lsnrctl start
打开本地数据库对应的服务 net start OracleServiceOradb
关闭oradb数据库服务 net stop oracleserviceoradb
二、表空间
表空间是数据库的逻辑划分，一个表空间只能属于一个数据库。所有的数据库对象都存放在指定的表空间中。但主要存放的是表， 所以称作表空间。
Oracle数据库中至少存在一个表空间，即SYSTEM的表空间。
SQL Server与Oracle之间最大的区别要属表空间设计。Oracle开创性地提出了表空间的设计理念，这为Oracle的高性能做出了不可磨灭的贡献。可以说，Oracle中很多优化都是基于表空间的设计理念而实现的。
表空间与数据文件相对应，一个表空间由一个或多个数据文件组成，一个数据文件只属于一个表空间。
Oracle数据的存储空间在逻辑上表现为表空间，而在物理上表现为数据文件。
表空间的大小等于所有从属于它的数据文件大小的总和。
若表空间只包含一个数据文件，则该表空间中的所有对象都存储在这个数据文件中；
若表空间只包含多个数据文件，则可将数据对象存储在该表空间的任意一个数据文件中，也可将同一个数据对象中的数据分布在表空间的多个数据文件中。
表空间中的数据文件个数不是固定不变的，可根据需要向其中追加数据文件。
Oracle 11g默认创建的默认表空间有：
SYSTEM表空间(系统表空间)，用于存放系统内部表和数据字典的数据。
SYSAUX表空间 充当系统表空间的辅助表空间，降低系统表空间的负荷，主要用于存储数据字典以外的其他数据对象。由系统内部自动维护。
UNDO表空间（撤销表空间） 用于存储撤销信息。
USERS表空间（用户表空间） 用户使用，创建各种数据对象。
表空间的典型应用
应用一：控制用户所占用的表空间配额。
应用二：控制数据库所占用的磁盘空间。
应用三：灵活放置表空间，提高数据库的输入输出性能。
应用四：大表的排序操作。
应用五：日志文件与数据文件分开放，提高数据库安全性。
create tablespace paul datafile '/ora10/product/oradata/ora10/paul01.dbf' size 20m;
创建临时表空间
create temporary tablespace user_temp
tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf'
size 50m autoextend on next 50m maxsize 20480m;
创建数据表空间
create tablespace user_data
datafile 'D:\oracle\oradata\Oracle9i\user_data.dbf'
size 50m autoextend on next 50m maxsize 20480m;
创建用户并指定表空间
create user username identified by password
default tablespace user_data
temporary tablespace user_temp;
三、SQL概述
关系型数据库采用结构化查询语言（Structured Query Language，SQL）作为客户端程序与数据库服务器间沟通的标准接口—客户端发送SQL指令到服务器端，服务器端执行相关的指令并返回其查询的结果。
在数据库服务器端执行SQL指令可以实现各种数据库操作和管理功能，比如：数据的查询和更新操作；创建、修改和删除各种数据库对象（如数据表、视图、索引等）；数据库用户账户管理、权限管理等。
SQL语句分类
数据操作语言（Data manipulation language，DML）
SELECT、 INSERT、DELETE、UPDATE、MERGE
数据定义语言语句（Data definition language，DDL）
CREATE、ALTER、DROP、RENAME、TRUNCATE
数据控制语言（Data Control Language，DCL）
GRANT、REVOKE
事务控制语句（Transaction Control Statement）
COMMIT、ROLLBACK、SAVEPOINT
会话控制语句（Session Control Statement）
ALTER SESSION、SET ROLE
SQL命名规则
以字符（A-Z,a-z,0-9,_,$,#）开头，后可跟字符和数字。
长度不超过30个字符（数据库名长度为不超过8个字符）。
同一用户/方案下的对象、或同一数据表中的字段不允许重名
不能使用Oracle服务器的保留字。
SQL语句结构
每条SQL语句均由一个保留字开头，以指定要实现的操作，如SELECT、CREATE、INSERT、UPDATE等。后跟若干子句以指定要操作的数据、操作的限定条件或其它更详细设置。
SQL语句执行方式
由客户端程序发送SQL指令到数据库服务器、并在服务器端执行，也可以调用存储于服务器端的程序单元以减少交互次数、提高运行效率。
SQL语句支持对数据的批量处理。
SQL语句既可以采用交互方式运行，也可以嵌入到其它高级语言编写的应用程序中运行，以实现在应用程序中对数据库的连接操作。
SQL编码惯例
应用开发中，遵从统一的命名和编码规范可使代码风格标准化，以便于代码的阅读、理解和维护升级。
SQL语句中的关键字（如SELECT、FROM等）使用大写字母；
表中字段名称则均小写、表名及字段名等应体现其内容含义。
编码时适当使用空格和缩进，以增强其可读性。
四、数据库设计
步骤：
1）按设计需求，搜集信息
2）分析出实体
3）找出实体间的关系 （E_R图） 逻辑设计 1:1 1:n m:n
4）做成表
实体对应于表，属性对应于字段 确定合适的数据类型
确定主键（唯一标识一条记录） 外键（参照主键，实现表间关联）
ER图如何转化成数据表
A、1:1关系
班级（班号，班名，人数）
班长（学号，姓名，性别，联系方式）
可以做成一张表或两张表，如果是两张表，要通过外键建立联系。
班级（班号，班名，人数, 班长学号，班长姓名，班长性别，班长联系方式）
班级（班号，班名，人数，班长学号）
班长（学号，姓名，性别，联系方式）
B、1:n关系
班级（班号，班名，人数）
学生（学号，姓名，性别，联系方式）
做成两张表，将其中表一（1）的主键作为表二（n)的外键，将1所在实体表中的主键放在n那个实体中，做外键。
班级（班号，班名，人数）
学生（学号，姓名，性别，联系方式，所在班号）
C、m:n关系
学生（学号，姓名，性别，联系方式）
课程表（课程号，课程名，课时，学分，教材）
再设计一张选课表，将前两张表中的主键放在其中，做外键，这两个字段组合起来做主键。
选课表（学号，课程号，备注）
五、Oracle的数据类型
数据类型 说 明
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
char 字符型，最大长度2000个字节（Byte)，缺省长度为1B
nchar 基于NLS国家字符集的字符型，最大长度2000个字符，缺省为1字符
varchar2 变长字符型，最大长度4000B
nvarchar2 基于NLS国家字符集的字符型，其余同varchar2
varchar 同varchar2
number(m,n) 数值型。m为总位数，n为小数位数。总长度最大为38位。
date 日期型。有效表数范围：公元前4712年1月1日到公元后4712年12月31日
long 变长字符型，最大长度2GB，不支持对字符串内容进行搜索
raw 变长二进制数据类型，最大长度2000B
long raw 变长二进制数据类型，最大长度2GB
blob 二进制大对象类型，最大长度4GB
clob 字符大对象类型，最大长度4GB
nclob 基于NLS国家字符集的字符字符大对象类型，最大长度4GB
bfile 在数据库外部保存的大型二进制文件大对象类型，最大长度4GB
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
字符型
char 定长 最大2000字符
如：char(10) '学习' 后面填6个空字符补全 虽然浪费空间，但查询效率高。
varchar2(20) 变长 最大4000字符
如：varchar2(20) '学习' 分配4个字符，节省空间。
clob 字符型大对象，最大4G character large object
数字型
number 绝对值范围 10-38—1038，可表示整数或小数
number(5,2) 表示小数，5位有效数字，2位小数 范围 -999.99—999.99
number(5) 表示整数，5位数 范围 -99999—99999
日期类型
date 包含年月日和时分秒
timestamp 是对date类型的扩展
图片类型
blob 二进制数据，可以存放图片、声音 4G
修改日期/时间的缺省显式格式
ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd';
六、创建与修改数据表
命名规则和注意事项
1)表名和字段名的命名规则：必须以字母开头，可以含符号A-Z,a-z,0-9,_,$,#
2)大小写不区分
3)不用SQL里的保留字, 一定要用时可用双引号把字符串括起来．
4)用和实体或属性相关的英文符号长度有一定的限制
5)建表时可以用中文的字段名, 但最好还是用英文的字段名
示例：
CREATE TABLE example(
ID Number(4),
NAME VARCHAR(25),
PHONE VARCHAR(10),
ADDRESS VARCHAR(50)
);
create table student (
id number,
name varchar2(20),
gender char(2),
age number,
addr varchar2(80)
);
create table 表名 (
字段1 类型1,
字段2 类型2,
字段3 类型3,
...
字段n 类型n
);
对表结构的修改：
alter table student add dept varchar2(20); --增加字段
alter table student modify dept number; --增加字段
alter table student drop column dept; --删除字段
alter table student rename column dept to deptid; --字段改名
表是Oracle数据库最基本的数据对象，用于存储用户数据，对应于现实世界中的对象。
设计数据库时，需要构造E-R图，在将E-R图转变为数据库对象时，实体要最终转换为数据表。
表由行和列两部分组成，列用于描述实体的属性，行则用于表面实现的数据。
设计表时注意：
1、应该使用有意义的名称。表名和列名只能使用英文字母、数字、下划线、$和#，必须以英文字母开头，长度不超过30个字符。
2、规划表名和列名时，使用一致的缩写格式、单数或复数格式。
3、必要时使用COMMENT命令描述表、列的作用。
4、使用1NF、2NF、3NF规范化每一张表。
5、定义列时，应选择合适的数据类型和长度。
6、定义列时，为节省存储空间，应将NULL列放在后面。
常用数据类型
char(n)、char(n byte) 用于定义固定长度的字符串(以字节为单位)，最大长度为2000字节。
char(n char) 用于定义固定长度的字符串(以字符个数为单位)。
vchar2(n)、varchar2(n byte) 用于定义变长字符串(以字节为单位)，最大长度为4000字节。
vchar2(n char) 用于定义变长字符串(以字符个数为单位)，最大长度为4000字节。
number(p, s) 用于定义数字类型的数据，p -数字总位数，s-小数点后面的。当定义整数类型时，可直接使用它的子类型int。
date 用于定义日期时间数据，长度为7个字节。
timestamp 是date类型的扩展，在其上执行DML操作时，于date类型完全一致。
raw(n) 用于定义二进制数据，n的上限的2000。
clob 用于存储大批量字符。
blob 用于存储大批量二进制数据。
建表
普通表是存放用户数据最常用的方式，其数据是以无序方式存放的。每张表最多可以定义1000个列。
create table [schema.]table_name(
column_name datatype [default expr][,...]
);
1、在当前方案中建表(省略方案名)
create table dept1(
dno number(2),
name varchar(10),
loc varchar(20)
);
2、在其他方案中建表
create table enterprise.dept2(
dno number(2),
name varchar(10),
loc varchar(20)
);
3、在建表时为表指定默认值
create table dept3(
dno number(2),
name varchar(10),
loc varchar(20) default 'Hohhot'
);
insert into dept3(dno, name) values(10, '技术处');
4、使用子查询建表
create table table_name[(column1, column2, ...)] as subquery;
create table emp1(name, salary, job, dno) as
select ename, sal, job, deptno
from emp where deptno = 30;
5、建立临时表
临时表用于存放会话或事务的私有数据，其中事务临时表是指数据只在当前事务内有效的临时表，会话临时表是指数据只在当前会话内有效的临时表。
create global temporary table temp(cola int)
on commit delete|preserve rows;
--ON COMMIT DELETE ROWS 说明临时表是事务指定，每次提交后ORACLE将截断表（删除全部行）
--ON COMMIT PRESERVE ROWS 说明临时表是会话指定，当中断会话时ORACLE将截断表。
insert into temp values(1);
select * from temp;
commit;
select * from temp;
修改表
1、增加列
alter table table_name add(
column_name datatype [default expr][,...]
);
alter table dept1 add (telephone varchar2(11));
2、修改列定义
alter table table_name modify(
column_name datatype [default expr][,...]
);
alter table dept1 modify (job varchar2(15) default 'CLERK');
修改了列的默认值后，默认值只对新插入的数据起作用。
3、删除列
alter table table_name drop(column);
4、修改列名
alter table table_name rename column old_name to new_name;
5、修改表名(也可以修改视图、序列或同义词的名字)
rename object_name to new_object_name;
6、增加注释
comment on table table_name is 'text';
comment on column table_name.column is 'text';
comment on table emp is '存储员工信息';
comment on column emp.ename is '描述员工姓名';
7、设置只读表
alter table emp read only;
alter table emp read write;
截断和删除表
1、截断表(保留表结构，数据不再有，释放占用空间，不能回退)
truncate table table_name;
2、删除表(删除表结构及数据)
drop table table_name [cascade constraints] [purge];
cascade constraints 用于指定级联从表的外部键约束。
purge 用于指定彻底删除表。
3、恢复被删除表
flashback table table_name to before drop;
显示表信息
1、user_tables
显示当前用户的所有表信息。
select table_name from user_tables;
2、user_objects
显示所有的数据库对象。
select object_name from user_objects where object_type = 'TABLE';
3、user_tab_comments
显示当前用户所有表的注释。
select comments from user_tab_comments where table_name = 'emp';
4、user_col_comments
显示当前用户的所有表的列注释。
select comments from user_col_comments where table_name = 'emp' and column_name = 'NAME';
约束
约束用于确保数据库满足特定的商业逻辑或者企业规则。有了约束后，不符合约束规则的数据操作将不会成功。
五种类型：not null、unique、primary key、foreign key、check
1、not null 非空约束，用于确保列不能为空。如emp表中的deptno不能为空，而comm可以为空。
2、unique 唯一约束，用于唯一地标识列的数据。唯一约束的列其值不能重复，但可以为空。如dept表中的dname不能重复，但可以为空。
3、primary key 主键约束，用于唯一地标识行的数据。主键约束列的值不能重复，不能为空。如dept表中的deptno不能重复，不能为空。
定义主键约束时，Oracle会自动基于主键约束建立唯一索引，索引名与约束名一致。一张表最多只能有一个主键约束。
4、foreigny key 外键约束，用于定义主从表之间的关系。外键约束要定义在从表上，但主表必有具有主键约束或唯一约束。
定义外键约束之后，要求外键列的数据必有在主表的主键列或唯一列中存在，或者为空。
5、check 检查约束，用于强制表行数据必须满足的条件，如要求sal列的值必须在1000-5000之间。
定义约束
建表的同时定义约束
create table [schema.]table_name(
column_name datatype [default expr][column_constraint],
...
[table_constraint]
);
列约束: column [constraint constraint_name] constraint_type
表约束: column, ... ,
[constraint constraint_name] constraint_type
查询约束信息：select constraint_name from user_constraints;
查询约束列信息：select constraint_name,column_name from user_cons_columns where table_name=’EMP’;
1)not null约束， 只能在列级定义
create table emp1(
eno int not null,
name varchar2(10) constraint nn_name not null,
sal number(6, 2)
);
insert into emp1 values(1, null, 1000);
2)unique约束， 可在列级或表级定义
create table emp2(
eno int,
name varchar2(10) constraint nn_name not null,
sal number(6, 2),
constraint u_name unique(name)
);
create table emp2(
eno int,
name varchar2(10) unique,
sal number(6, 2)
);
insert into emp2 values(1, 'SCOTT', 1000);
insert into emp2 values(2, 'SCOTT', 1000);
3)primary key约束， 可在列级或表级定义
create table dept4(
dno int primary key,
dname varchar2(10),
loc varchar2(20)
);
insert into dept4 values(1, 'SALES', 'HOHHOT');
insert into dept4 values(1, 'ADMIN', 'BAOTOU');
insert into dept4 values(null, 'ADMIN', 'BAOTOU');
create table dept4(
dno int constraint pk_dept_01 primary key,
dname varchar2(10),
loc varchar2(20)
);
create table dept4(
dno int,
dname varchar2(10),
loc varchar2(20),
constraint pk_dept_01 primary key(dno)
);
create table dept4(
dno int,
dname varchar2(10),
loc varchar2(20),
constraint pk_dept_01 primary key(dno, dname) --复合主键约束
);
5)check约束， 可在列级或表级定义, check约束允许列为空。
create table emp5(
eno int,
ename varchar2(10),
salary number(6, 2),
constraint ck_emp5_01 check(salary between 1000 and 5000)
);
create table emp5(
eno int,
ename varchar2(10),
salary number(6, 2) check(salary between 1000 and 5000)
);
insert into emp5 values(1111, 'SCOTT', 800);
