首页 > 代码库 > Oracle闲扯<二>
Oracle闲扯<二>
数据冗余和数据不一致性是数据库的源头！一个万能的答案。
———老牛
SQL简介
SQL Statement (SQL 语句)
1)DDL Data Defination Language数据库定义语言
用于定义数据库的三级结构，包括外模式、概念模式、内模式及其相互之间的映像，定义数据的完整性、安全控制等约束，不需要commit
CREATE
Ps:例如创建用户 create user Super_YY identified by 123；又如create table创建一张表
ALTER
Ps:例如修改用户密码 alter user Super_YY identified by 1234；又如alter table修改数据库表格
DROP
Ps:例如删除用户 drop user Super_YY；又如drop table删除表格与truncate不同drop是删除整张表
TRUNCATE
Ps：truncate table table_name用来删除所有表的内容与delete不同，truncate无法使用rollback回滚，而delete可以使用rollback撤销删除
COMMENT
Ps：comment on table 用来给表添加注释，用来提示这个表是干嘛的
RENAME
Ps：用来重命名，例如重命名表名 rename table_name1 to table_name2;重命名列名alter table table_name rename column column_name to new_column_name;
2)DML Data Management Language数据库操纵语言
数据操纵分成数据查询和数据更新两类。数据更新又分成插入、删除、和修改三种操作。
INSERT
Ps:插入数据 insert into table_name(value1,value2,.......)
UPDATE
Ps:修改数据 update table_name set column_name1=YY where column_name2=XY;
DELETE
Ps:删除数据，delete table_name
SELECT
Ps：从数据库表中检索数据行和列，例如select * from table_name
3)DCL Data Control Language数据库控制语言
GRANT
Ps: 授权，例如grant update on table_name to YY;授权修改table_name的权利给YY
REVOKE
Ps:收回权限，例如revoke update on table_name to YY;从YY那里收回修改table_name的权限，谁授权，谁收回权限
4)TCL Transaction Control Language)数据库事务控制语言
COMMIT
Ps：提交
ROLLBACK
Ps：回滚
SAVEPOINT
Ps：设置保存点
创建数据库、表
1）基本语句
创建数据库：create database database_name
Ps:看到许多视屏以及文档资料里面都没有提到create database语句而直接进行create table后来看了些说法一方面是由于权限级别，如果你是DBA当然不是问题，另外一方面当然是在创建数据库是需要考虑的很多问题，例如指定数据库的默认大小以及结构等等，由于暂无经验在此就不多做陈述-_-!
创建表：
create table table_name(column_name1 datatype[NOT NULL],
column_name2 datatype[NOT NULL],
...........);
例如：
create table student(
Id char(8) not null primary key,
Name varchar220) not null,
Sex char(4) not null,
Address varchar2(120) not null,
Phone Number(8),
Email varchar2(60));
我们来一句一句的解释下，该语句创建了一个名为student的表，表里的字段名分别为Id（编号）、Name（姓名）、Sex（性别）、Address（地址）、Phone（电话）、Email（电子邮箱）。
Id char(8) not null primary key,
编号，数据内容为长度为8的非空字符型，primary key（PK）主键
Name varchar(20) not null,
姓名，数据内容为可变长度为20的非空字符型
Sex char(4) not null,
性别，数据内容为长度为4的非空字符型
Address varchar(120) not null,
地址，数据内容为可变长度为120的非空字符串
Phone Number(8),
电话，数据内容为数字类型长度为8，可以为空
Email varchar(60)
2）基本数据类型
字符型
char 定长 最大2000字符
例子：char（10）‘瑶瑶’前四个字符放‘瑶瑶’，后添加6个空格补全
效率高，查询速度快
char为整体比较，查询效率高，特别适合类似身份证号、学号类为已知长度
并且经常需要作为查询条件的数据
varchar2 （20）变长 最大4000个字符
例子：varchar2（10）‘瑶瑶’oracle分配四个字符。这样可以节省空间
vrchar2为逐个匹配，第一位匹配相同再匹配第二位等等，占用空间小，查询效率较低，比较推荐使用！
Ps:varchar与varchar2，varchar2是将任何字符都用2字节进行存储相比varchar具有更好的兼容性，较少使用varchar
大数据
BLOB (Binary Large Object)
Ps:用来存储大量二进制数据
CLOB (Character Large Object)
Ps:用来存储大量文本数据
NCLOB可以存储无结构的二进制数据
Ps:三种大型对象(LOB)，用来保存较大的图形文件或带格式的文本文件，如Miceosoft Word文档，以及音频、视频等非文本文件，最大长度是4GB
BFILE 当大型二进制对象的大小大与4G字节时，BFILE数据类型用于将其存储在数据库外的操作系统文件中；当其大小不足4G字节时，则将其存储在数据库内部的操作系统文件中，BFILE列存储文件定位程序，此定位程序指向服务器上的大型二进制文件。
日期
DATE
Ps:从公元前4712年1月1日到公元4712年12月31日的所有合法日期（有的也说日期范围到公元9999年12月31日）,缺省格式为DD-MON-YY，如14-05月-05 表示2014年5月5日。
数字型
Ps:number 范围 -10的38次方至10的38次方，可以表示整数，也可以表示小数
number（5,2）
Ps:表示一个小数有5位有效数字、2位小数,范围-999.99至999.99
number（5）
Ps:表示一个五位整数,范围-99999至99999
3）约束
constraint 保证数据库的完整性、一致性
1. 主键约束 PK primary key
1） 定义：
PK = UK + NN 唯一且非空，保证实体完整性 每一行是唯一的
UK unique 唯一键，NN not null 非空
2） 列级约束 约束直接定义在字段之后
create table emp(
id int constraint emp_id_pk primary key,
name varchar(30) not null,
salary float);
3） 表级约束 所有字段之后，追加约束
create table emp1(
id int,
name varchar(30) not null,
salary float,
constraint emp1_id_pk primary key(id) );
注：一个表中无法同时定义多个PK
4） 可以使用表级约束,定义联合主键
联合主键：联合起来是唯一非空
create table stu(
id1 int,
id2 int,
name varchar(30),
constraint stu_id1_id2_pk primary key(id1, id2));
注：实现联合主键，必须表级约束
5） 通过后期追加的方式定义约束
create table emp2(
id int not null,
name varchar(30) not null,
salary float );
--给emp2表的id字段追加PK约束，使用DDL alter语句改变表结构
alter table emp2 add constraint emp2_id_pk primary key(id);
--如何删除约束 根据约束的名称，所有约束的删除方式统一
alter table emp2 drop constraint emp2_id_pk;
2. 唯一约束 UK unique
1） 可以追加UK 让name唯一
alter table stu add constraint stu_name_uk unique(name);
2） 联合唯一约束
drop table stu1;
create table stu1(
id int primary key,
name1 varchar(30),
name2 varchar(30));
--给stu1表追加一个联合唯一约束
--要求name1和name2两个字段联合唯一
alter table stu1 add constraint stu1_name1_name2_uk unique(name1, name2);
3） 删除约束
alter table stu1 drop constraint stu1_name1_name2_uk;
3. 外键约束 FK foreign key
1）constraint emp_dept_deptid_fk foreign key(dept_id) references dept(id)
a) 参照的值id必须是唯一的 uk
b) dept的id是PK 一定是唯一的
c) references 关键字： 参照
Ps:外键约束可以后期追加
在某些项目中，有时即便两个表有关系，但不加外键约束
目的：提升系统性能,数据库维护约束会增加系统开销，可以通过其它层面技术来弥补具体问题具体分析不可一刀切（by:老牛）
2） 如何删除外键约束
alter table emp drop constraint emp_dept_deptid_fk;
4 CHECK 限制
作用：根据用户自己的需求来进行限制
特点：可以实现多样化
添加方式：建表时添加，和建表后添加
注意：添加的时候不要忘了使用括号。
5 NOT NULL 不能为空
作用：使制定的列必须插入值
特点：不能为空
添加方式：建表时添加，和建表后添加
注意：用的使modify 来进行修改
6 DEFAULT 默认值
作用：当不插入值的时候给默认效果
特点：省事
添加方式：建表时添加，和建表后添加
注意：用的使modify 来进行修改
7 删除约束
alter table table_name drop constraint con_name;
alter table table_naem drop primary key
alter table table_name drop primary key cascade;
8 删除表
drop table table_name;
drop table table_name cascade constraint;
9 启用约束
alter table table_name enable constraint con_name;
alter table table_name enable primary key;
10 禁用约束
alter table table_name disable constraint con_name;
alter table table_name disable primary key;
例子：在此我创建了三张表，对一张表做出分析，其余可以自己对照前面想想
表一 s_dept
CREATE TABLE s_dept
(id NUMBER(7) CONSTRAINT s_dept_id_nn NOT NULL,
id数字类型，约束为非空，就是强制要求输入7位数字
name VARCHAR2(25) CONSTRAINT s_dept_name_nn NOT NULL,
Name字符类型，约束为非空，必须强制输入名字
region_id NUMBER(7),
Region_id地区编号，数字类型 可以为空
CONSTRAINT s_dept_id_pk PRIMARY KEY (id),
约束id为主键
CONSTRAINT s_dept_name_region_id_uk UNIQUE (name, region_id));
约束name字段和region_id字段联合唯一
表二：s_emp
CREATE TABLE s_emp
(id NUMBER(7)
CONSTRAINT s_emp_id_nn NOT NULL,
last_name VARCHAR2(25)
CONSTRAINT s_emp_last_name_nn NOT NULL,
first_name VARCHAR2(25),
userid VARCHAR2(8),
start_date DATE,
comments VARCHAR2(255),
manager_id NUMBER(7),
title VARCHAR2(25),
dept_id NUMBER(7),
salary NUMBER(11, 2),
commission_pct NUMBER(4, 2),
CONSTRAINT s_emp_id_pk PRIMARY KEY (id),
CONSTRAINT s_emp_userid_uk UNIQUE (userid),
CONSTRAINT s_emp_commission_pct_ck
CHECK (commission_pct IN (10, 12.5, 15, 17.5, 20)));
表三 s_region
CREATE TABLE s_region
(id NUMBER(7)
CONSTRAINT s_region_id_nn NOT NULL,
name VARCHAR2(50)
CONSTRAINT s_region_name_nn NOT NULL,
CONSTRAINT s_region_id_pk PRIMARY KEY (id),
CONSTRAINT s_region_name_uk UNIQUE (name));
另外附上E-R图供大家参考
by:cs