oracle数据库基础（1）

1，数据完整性约束

1，数据完整性约束：可靠性+正确性

具体体现在：列值约束（域完整性约束），整行数据约束（实体完整性）
1）实体完整性方法：
a）主键约束 primary key （PK) 能唯一标识行的一列或一组列的集合（唯一性约束+非空约束）
特点：唯一性，不能为空
可以是单列作为主键，也可以是多列作为主键，称为组合主键或复合主键
例如：学号，员工编号，分数表中：学生编号+课程编号
b) 唯一性约束(unique) 确保列值的唯一性
特点：不能包含重复值，但允许为空
例如：身份证号，邮箱
c）标识列（oracle序列） 自动生成序号作为主键用
2）域完整性约束：
a）数据类型限制 通过数据类型可以限制此列值，防止非法输入
b）检查约束check 对于用户自定义的约束，强制列数据必须满足条件
例如：年龄，性别等 适用于：固定值或范围限制等
c）输入格式 如日期格式
d）默认值 default
在插入数据时，如果没有设置这一列的值，那么系统会自动设置这一列的默认值
e）非空约束 not null 约束列的值不能为空，避免过多空值，会影响数据库的性能
当插入数据时，必须为此列提供数
3）引用完整性约束（外键约束(foreign key:(Fk))：表与表之间的约束关系）
外键：这张表的这一列引用了另外一张表的主键，那么这张表的这一列称为外键
外键的数据范围来自于另外一张表中主键的数据，可以重复 可以为空
那么包含外键的表称为外表，包含主键的表称为主表
主表和外表是相对而言，由关联的列来确定的
4）自定义完整性约束（约束关系由自己来决定怎么样写规则，具体限制方法有：触发器等等）

2，oracle介绍
Oracle官网：www.oracle.com

常用的数据库：
1)关系型数据库：
大型数据库：DB2,oracle,sybase (DB2>ORACLE>Sybase)
中型数据库：mysql sqlserver,postgresql等
小型数据库：access
2)非关系型数据库：
NoSql、Cloudant

Oracle数据库主要特点：
1）支持多用户，大事务量的事务处理
2）在保持数据安全性和完整性方面性能优越
3）支持分布式数据处理
4）具有可移植性

sql:structured query language 标准语言，对数据库进行操作的一种语言
先讲DDL =>DML> DQL
sql分类：
1）DQL:数据查询语言 data query language
select
2）DDL：数据定义语言 data definition language（对象操作）
create
alter（次要些)
drop
truncate
rename

3）DML：数据操作语言data manipulation language
insert
update
delete

4）DCL：数据控制语言data control language（权限）
grant（赋权）
revoke（回收）
5）TCL：事务控制语言Transaction control language
commit提交
rollback回滚
savepoint保存点

2，DDL语言

1，create建表
–表示单行注释
/* 多行注释 */

create主要应用于创建对象：（主要讲创建表）
建表语句语法：

create table 表名(
列名1 数据类型和长度 ， 例如：sname varchar2(50)后面可以添加约束
列名2 数据类型和长度 ，
。。。。
列名n 数据类型和长度 (注意最后一列不可以加短号）
)

举例：
班级表 class：

班级编号(cno) 作为主键(PK)
班级名称(cname) 非空

学生表student：
学生编号 sno 作为主键(PK)
学生姓名 sname 非空
学生性别 ssex 默认值男 只能输入男或女（check)
学生年龄 sage 要求输入范围在1-100之间(check)
学生身份证号 sid 唯一性约束 unique
班级编号 classno (关联班级表的cno) FK

建表先后顺序规则：

在建表前，如果涉及到多张表，表与表之间存在关联关系时，那么首先根据关联的列来判断哪个表是主表，哪个表是外表，主表先建，外表后建
例如：

班级表：

create table class1(
–constraint 约束名，如果不加这个，系统会自动分配一个约束名
cno char(20) constraint pk_class primary key,–单列作为主键 主键约束
–如果不设置约束名 cno char(20) primary key,
cname varchar2(30) not null --非空约束，注意最后一列不能加逗号
);

学生表：

create table student1(
sno char(30) primary key,–单列主键约束，多列不能这样写
sname varchar2(50) not null,–非空约束
–默认值约束 default 默认值
–check约束，只能输入男或女：check(ssex=‘男’ or ssex=‘女’)
–check约束，年龄在1-100间：check(sage>=1 and sage<=100) 或者 check(sage between 1 and 100)
ssex char(2) default ‘男’ check(ssex in (‘男’,‘女’)),
sage number(3) check(sage>=1 and sage<=100),
sid char(18) unique,–unique唯一性约束
–关联列可以跟主表的列名不一样，也可以一样
–外键的列长度和类型跟主表中的关联的列类型长度要一致
–外键关联： 列名 类型长度 references 关联表(关联的主键列名)
classno char(20) references class1(cno)
);
–如果有联合主键则写到建表语句最后面： primary key(主键列1，主键列2）

这里是引用
–拓展一下：
–建一张表跟某一张表表结构一样或者部份一样
–例如创建一张跟员工表一样的表（表结构，数据一样）
–注意：这样创建的表跟原来的表区别是没有约束，原来表拥有的约束在此表中全部丢失
create table emp_temp as select * from emp;

2，修改表结构
alter对象修改 （主要讲修改表结构）
1）添加列
2）修改列的类型或长度
3）删除列
4）更改列名

–添加列
– alter table 表名 add 列名 类型(长度) 约束
–例如：学生表中添加列:地址
alter table student add address varchar2(300);

–修改列 类型长度
– alter table 表名 modify 列名 类型(长度)
–例如：学生表中修改列：地址长度改为200
alter table student modify address varchar2(200);

–修改列名
– alter table 表名 rename column 原列名 to 新列名
–例如：学生表中修改列名address为myaddress
alter table student rename column address to myaddress;

–删除列
– alter table 表名 drop column 列名
–例如：学生表中myaddress删除
alter table student drop column myaddress;

3，rename
rename更改表名
语法：
rename 原表名 to 新表名;

rename class1 to class2;

4，truncate删除数据
truncate删除（整张表数据删除，但不删除表结构）
语法：
truncate table 表名;

例如：删除emp_temp表中所有数据
truncate table emp_temp;

5，drop删除表
–删除表（表结构和数据全部删除)
–语法:drop table 表名;

drop table emp_temp;
6，补充oracle的数据类型
标准命名规则要满足如下要求：（数据库命名为1-8个字符）
以字符开头
30个字符以内
只能包含A-Z,a-z,0-9,_,$,#
不能和同一个用户下的其他对象重名
不能是oracle服务器的保留字

oracle常用数据类型:
char 固定长度字符串 速度快 浪费存储空间 最大2000bytes 默认为1
varchar2 可变长度字符串 实际多少就是多少 节省存储空间 查询速度相对慢 最大4000bytes
NUMBER (p,s):数字类型，p为总的整数位（不包括小数点），S为小数位，可以负数
日期类型:
date （精度：年月日小时分钟秒，没有小数秒，没有时区）
timestamp (精度高：年月日小时分钟秒，有小数秒，没有时区)

3，DML语言

DML：数据操作语言data manipulation language（数据操作）
insert
update
delete
1，update更新
–update更新数据：
语法：
update 表名 set 列名=值【，列名2=值2，。。。。】 where 条件过滤（指定行)

–备份员工表emp_temp
create table emp_temp as select * from emp;
–update更新数据：
–语法：
–update 表名 set 列名=值【，列名2=值2，。。。。】 where 条件过滤（指定行)

update emp_temp set sal=1000 where ename=‘SMITH’;–单列更新

–把部门是20的员工薪水改为4000，奖金改为800
update emp_temp set sal=4000,comm=800 where deptno=20;–多列更新
update emp_temp set comm=null;–把所有员工奖金取消
2，insert插入
如何查看表结构：
desc 表名（describe 表名)

插入数据的顺序：主表先插入，外表再插入

–insert语句(插入单条记录)
–格式 第一种方式：insert into 表名 (列名列表) values(值列表)
–列名列表跟值列表：个数，类型一定要一致
–注意非空的列必须加入列名列表中，列名列表可以是部份列名
insert into class(cno,cname) values(‘c001’,‘208班’);
–格式 第二种方式：insert into 表名 values(值列表)
–注意这种写法的值列表个数一个也不能少，没有值的设为null
insert into class values(‘c002’,‘209班’);
insert into student values(‘s001’,‘张三’,default,null,‘111111111111’,‘c001’);
insert into student values(‘s002’,‘李四’,‘女’,null,‘222222222222’,‘c002’);

–注意插入外表数据时特别是外键列的值必须要主表中有的数据才能插入

create table ttest(
sno char(30) primary key,
sname varchar2(100) not null,
sbirthday date
);
–插入日期类型数据时，一定要用to_date转换函数
insert into ttest values(‘10001’,‘zhangsan’,to_date(‘1985-05-20’,‘yyyy-mm-dd’));

3，delete删除
–delete删除数据，但不删除表结构
语法：
delete from 表名 【 where 条件 】

–不加where条件语句表示整张表数据删除 ======》truncate table 表名；

–删除部门20的所有员工
delete from emp_temp where deptno=20;
delete from emp_temp;–整张表的删除
4，truncate和delete的区别

–truncate与delete区别
–1） truncate 与delete语法不同
–2） truncate是ddl语句，delete是dml语句里
–3） Truncate只能删除整张表数据，delete既可以删除整个表数据，也可以删除部份数据
–4) truncate属于隐式递交，所以不能恢复，delete属于显式递交，可以撤消和恢复的
–5） truncate不记录日志,delete记录日志,所以删除速度比delete快
DML都是可以撤消的，ddl是隐式递交，不能撤消操作