Orcale数据库基础知识总结之orcale数据库的DDL和DML操作

orcale数据库的DDL和DML操作。
第一部分：数据表级别操作
DDL(database defination language)，主要包括创
建建表，删除表，表更名，表字段新增，删除，修改，更名等等
1、新建一张数据表(关键字：create table... )
 create table student(
        id number,
        name varchar2(14),
        gender varchar2(4),
        address varchar2(200)
);

2、删除数据表  (关键字：drop table)
drop table student;

3、重命名表 (关键字：rename ..to ..)
 rename student  to  mystudent;

第二部分：数据列级别操作,对于要修改的表都是以alert table table_name为开头的。
      操作的一般都是属性，属性数据类型。
4、添加一个表属性（列） (关键字：...add ...)
alter table student add age number(5);


6、删除一个表属性(关键字：...drop...)
alter table student drop column age;


7、表属性重命名(关键字：rename ...to...)  :注意有的地方不适用,建议先drop后add .
alter table student rename gender to sex;


8、修改表属性的数据类型(关键字：modify....)
alter table student modify  name varchar2(10);


第三部分、数据行级别的操作（DML）数据库管理语言,注意这里面的insert update delete后面都不用table关键字
9、向数据表中添加一条数据（关键字：insert into ...values）
insert into  student(id,name)  values(1,'zhangsan');向指定的列添加数据
insert into student values(.....)，向所有的列添加数据。


10、修改数据表中的数据（关键字：update ...set ....）
 update student set name='jack' where id = 2;  
 update student set name='jack'; (将所有的属性都修改了) 


11、删除数据表中的一条数据（关键字：delelet from ....）
delete  from student  where id = 5;

delete from student; （将所有的数据都删除了,需要谨慎操作）

第四部分：视图和索引的相关

12、创建视图。（视图是虚表，并没有创建表，只是在已经有的表上面创建出一个虚拟的表）
简单视图：
create view view_emp as  select empno,ename,job,sal from emp  where sal>1200;  
复杂视图创建：
create or replace view view_emp_dept as  
select d.deptno,d.dname,min(e.sal) min_sal,max(e.sal) max_sal  
from emp e,dept d  
where e.deptno=d.deptno(+)  
group by d.deptno,d.dname;  


13、删除视图。
drop view stu_view;


14、创建索引(数据库的索引有很多种，这里说明一般索引的创建)
 create index stu_index on student(id,name,gender);//将student表中的三个列设置为索引项
 
15、索引的删除
drop index stu_index;

知识补充：
一、orcale数据库的数据类型：（按照常用的程度排序）
（1）、VARCHAR2(size)   可变长度的字符串,其最大长度为size个字节;size的最大值是4000,而最小值是1;你必须指定一个VARCHAR2的size;  


（2）、NUMBER(p,s)   精度为p并且数值范围为s的数值;精度p的范围从1到38;数值范围s的范围是从-84到127; 
                    例如:NUMBER(5,2)   表示整数部分最大3位，小数部分为2位；
                         NUMBER(5,-2)   表示数的整数部分最大为7其中对整数的倒数2位为0,前面的取整。
       NUMBER   表示使用默认值,即等同于NUMBER(5);  
                              
（3）、DATE   有效日期范围从公元前4712年1月1日到公元后4712年12月31日  


（4）、LONG    可变长度的原始二进制数据，其最长可达2G字节;   


（5）、CHAR(size)   固定长度的字符数据,其长度为size个字节;size的最大值是2000字节,而最小值和默认值是1; 


（6）、CLOB   一个字符大型对象,可容纳单字节的字符;不支持宽度不等的字符集;最大为4G字节   


（7）、BLOB   一个二进制大型对象;最大4G字节 .


（8）、 NVARCHAR2(size)   可变长度的字符串,依据所选的国家字符集,其最大长度为size个字符或字节;size的最大值取决于储存每个字符所需
的字节数,其上限为4000;你必须指定一个NVARCHAR2的size; 
 
（9）、  NCHAR(size)   也是固定长度。根据Unicode标准定义   


（10）、NCLOB   一个字符大型对象,可容纳单字节的字符;不支持宽度不等的字符集;最大为4G字节;储存国家字符集  


（11）、RAW(size)   长度为size字节的原始二进制数据,size的最大值为2000字节；你必须为RAW指定一个size;


（12）、BFILE   包含一个大型二进制文件的定位器,其储存在数据库的外面；使得可以以字符流I/O访问存在数据库服务器上的外部LOB;
最大大小为4G字节.


二、数据表设计中的字段约束 ：（以常用程度排序）
一段完整的建表语句：


create table tab_stu(
stu_id number primary key,
--学生姓名，不能为空，不能重复
stu_name varchar2(20) not null unique,
--学生姓名只能是male或female
stu_gender varchar2(6) not null check(stu_gender='male' or stu_gender='female'),
--学生年龄只能在18到60之间
stu_age number check(stu_age >18 and stu_age <60),
--邮箱可以不填写，填写的话不能相同
stu_email varchar2(30) unique,
--默认没有约束
stu_address varchar2(30),
    --联合主键约束
    primary key(tab_firstname,tab_lastname)
--外键约束
class_id number not null references tab_class(class_id) 
    
);
以下详细说明表约束。
（1）、主键约束，表示设置字段为主键。（关键字：primary key）
stu_id number primary key,



（2）、非空约束、表示该字段不能为空，表现为必填。(关键字：not null)
stu_name varchar2(20) not null unique,


（3）、唯一约束 表示填写的不能有相同的。(关键字：unique)
stu_email varchar2(30) unique,


（4）、设置默认值,表示添加时如果不填，就是用默认值。(关键字：default)
gender varchar2(20) default '男'，


（5）、自定义约束。(关键字：check)
stu_gender varchar2(6) not null check(stu_gender='male' or stu_gender='female'),
stu_age number check(stu_age >18 and stu_age <60),


（6）、外键约束(关键字：reference，这个不常用，因为用到外键更新删除都会出现关联，一般比较谨慎使用）。
class_id number not null references tab_class(class_id)。


三、维护已经创建好的字段约束：
（1）、为表增加主键约束
alter table tab_check
add constraints tab_check primary key(che_id);


（2）、添加唯一约束,tab_check_unique表示约束的名称
alter table tab_check
add constraints tab_check_unique unique(che_name);


（3）、添加检查约束
alter table tab_check
add constraints tab_check_age check(che_age>18 and che_age<60);


（4）、删除主键约束
alter table tab_check
drop constraints tab_check;


（5）、禁用约束
alter table tab_check disable constraints tab_check;


（6）、启用约束
alter table tab_check enable constraints tab_check;


（7）、为表添加外键约束：
alter table tab_stu

add constraints tab_stu foreign key(class_id) references tab_class(class_id);

四、关于视图的一些概念：（更详细的概念参照orcale数据库视图说明的博文）
（1）视图实际上是一个或多个表上的预定义查询，这些表称为基表。视图并不存储数据，只是在查询视图时才访问基表。  
（2）视图的优点：  
·限制用户只能通过视图检索数据，对用户屏蔽基表  
·可以将复杂的查询编写为视图，减少用户查询的复杂度  
·限制某个视图只能访问基表中的部分数据，提高了安全性 
（3）、视图创建的语法知识：
create [or replace] [{force|noforce}] view view_name  
[(alias_name[,alias_name...])]  
as subquery  
[with {check option|read only} constraint constraint_name]  
  说明：
or replace:如果视图存在就替换它。  
force:即使基表不存在也要建立该视图。  
noforce:若基表不存在就不建立此视图，默认值。  
view_name:视图名称。  
alias_name:为自查询中的表达式指定一个别名，别名个数与子查询中列数相同。  
subquery:指定一个子查询，它对基表进行查询，如果已经提供别名，可以在select子句之后的列表中使用别名  
with check option:表名只有子查询检索的行才能被插入、删除、更新，  
 默认情况下，在插入、删除、更新行之前并不会检查这些行是否被子查询检索。  
constraint_name:指定with check option或read only约束的名称。  
with read only:说明只能对基表中的行进行只读访问。  

五、关于索引的一些概念：（关于索引的详细，见orcale索引博文）
 （1）、索引特点：
第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
 （2）、索引不足：
第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

数据库平常使用小技巧：
1、 根据查询出来的内容创建一张表(关键字：create table.... as)
create table myStudent as select * from  student;


2、清空表中数据（这里面是彻底清空，不可以从垃圾箱中恢复）
 truncate table student;


3、删除整个表的数据
  delete from student ;（这里删除的数据集可以从垃圾箱中恢复）