oracle数据库建表篇CREATE TABLE

__________________________________________________________
                         Table
__________________________________________________________

Creat Table//建表

1、数据类型
1>char
create table lhj_char(
  a char(10),
  b varchar(10),
  c varchar2(10)
);

char(10)//固定长度，不足10个用空格填补，如果字段经常
               更新建议使用char类型
varchar(10),//可变长的sql标准的类型
varchar2(10)//可变长的oracle自己的类型

insert into lhj_char values('aaa','aaa','aaa');
select length(a),length(b),length(c) from lhj_char;
===========================================================
2>num
create table lhj_num(
  a number,
  b number(3),
  c number(5,2)
);

b number(3),//相当于数据只会存放整型
c number(5,2)
5代表插入数的总的位数
2代表小数点后的小数位数1234.1不可以插入

insert into lhj_num(a) values(1234);
insert into lhj_num(a) values(123456789123);
insert into lhj_num(a) values(1.2);
以上三条记录都可以成功插入

insert into lhj_num(b) values(1234);
ERROR at line 1:
ORA-01438: value larger than specified precision
 allows for this column

insert into lhj_num(b) values(123);
insert into lhj_num(b) values(1.2);
只保留整数部分

insert into lhj_num(c) values(123);

create table lhj_num1(
  a number,
  b number(3),
  c number(5,2),
  d int
);
===========================================================
3>date
create table lhj_date(
  a date
);
insert into lhj_date values(sysdate);
insert into lhj_date values('25-DEC-87');
日-月-年的格式可以自动加入表中
insert into lhj_date values('DEC-25-87');
ERROR at line 1:
ORA-01858: a non-numeric character was found
 where a numeric was expected

SQL> select * from lhj_date;

A
---------
24-SEP-08
25-DEC-87
23-SEP-80
24-SEP-08
23-SEP-08


select * from lhj_date;
查询显示系统默认的日期格式

to_date

insert into lhj_date
values(to_date('SEP-23-80','MM-DD-YY'));
insert into lhj_date
values(to_date('SEP-23-2008','MM-DD-YY'));


insert into lhj_date
values(to_date('2008-9-24','YYYY-MM-dd'));


to_char
用指定的格式显示日期
select to_char(a,'yyyy-mm-dd') from lhj_date;

insert into lhj_date values(sysdate);
select to_char(sysdate,'DD-MM-YY HH:MI:SS am') from dual;

select to_char(a,'DD-MM-YY HH:MI:SS am') from lhj_date;

TO_CHAR(A,'DD-MM-YYH
--------------------
24-09-08 09:16:59 pm
25-12-87 12:00:00 am
23-09-80 12:00:00 am
24-09-08 12:00:00 am
23-09-08 12:00:00 am
25-09-08 08:07:58 am

select to_char(a,'yyyy-mm-dd hh24:mi:ss am') from lhj_date;

TO_CHAR(A,'YYYY-MM-DDH
----------------------
2008-09-24 21:16:59 pm
1987-12-25 00:00:00 am
2080-09-23 00:00:00 am
2008-09-24 00:00:00 am
2008-09-23 00:00:00 am
2008-09-25 08:07:58 am
//自己加的记录没有指定时分秒的系统默认设置为12:00:00或
(hh24) 00:00:00
如果是用sysdate插入的则会自动取系统的时分秒

练习
查出08-8-8之后的记录
select * from lhj_date where a>'8-AUG-08';
日期的比较可以用>,<,=
==============================================================
5>clob
6>blob 二进制
create tabte lhj_clob(
   a clob
);
insert into lhj_clob values('abkfiddiodoir');
create table lhj_blob(
  a blob
);
insert into lhj_blob values('lhj123');
文件，影片存到clob或blob时要写java程序将数据写入
一般不使用sql语句写入
=============================================================
2、约束
constraint
_____________________
1>primary key
2>foreign key
3>unique
4>not null
5>check
_____________________

(1)primary key

create table lhj_pk(
  id number(4) primary key,
  name char(10)
);
//列级约束

create table lhj_pk1(
  id number(4),
  name char(10),
  constraint id_pk1 primary key(id)
);
//表级约束

insert into lhj_pk values(1,'aa');
insert into lhj_pk values(2,'bb');
insert into lhj_pk values(1,'cc');  --error唯一
-------------------------------------------------------------
insert into lhj_pk values(1,'cc');
*
ERROR at line 1:
ORA-00001: unique constraint (OPENLAB.SYS_C002736) violated
-----------------------------------------------------------

insert into lhj_pk(name) values('dd');--error非空
-------------------------------------------------------------
insert into lhj_pk(name) values('dd')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("OPENLAB"."LHJ_PK"."ID")
-------------------------------------------------------------

alter
对已建好的表加主键约束
前提：表中不能有重复的主键记录（两个id相同）
      如果有重复的，删除其中一条即
alter table studentlhj add constraint stulhj_pk primary key(id);

select * from studentlhj;

        ID SNAME             AGE        CID
---------- ---------- ---------- ----------
       101 zhang              23          1
       102 liu                33          2
       103 sun                25          2
       104 wu                 23          2
       106 ccc                28
       105 wang               18          1
       107 wu                 33          2

SQL> insert into studentlhj values(101,'lhj',20,2);--error
insert into studentlhj values(101,'lhj',20,2)
*
ERROR at line 1:
ORA-00001: unique constraint (OPENLAB.STULHJ_PK) violated

=================================================================
(2)not null

create table lhj_null(
   id number(3) primary key,
   name varchar(10) not null
);
insert into lhj_null(id) values(1);-error not null
alter table lhj_null modify name null;//把name 改成非空
alter table lhj_null modify name not null;
modify 只能修改非空约束
//再改成非空的时候会出错，原因是表中存在了一条空的记录，
解决方法，把空的记录删除再改成非空。

(3)unique

create table lhj_unique(
   id number primary key,
   name char(10) unique
);
insert into lhj_unique values(1,'aa');
insert into lhj_unique values(2,'aa');--error name unique允许是空

(4)check
create table lhj_check(
  sex char(3),
  constraint check_sex check (sex in ('male','female'))
);
alter table lhj_check modify sex char(10);//sex的length太短char(3)
insert into lhj_check values('male');
insert into lhj_check values('man');--error check

==================================================================
_____________________________________
user_tables                         
select table_name from user_tables
where table_name like '%student%';
--no rows selected
//原因，表名存成大写
_____________________________________

(5)foreign key

create table c_lhj(
   id number(3) primary key,
   cname varchar2(10) unique
);
//主表
create table stu_lhj(
   sid number(3) primary key,
   sname varchar2(10) not null,
   cid number(3),
   constraint fk_lhj foreign key(cid)
   references c_lhj(id)
);
//从表，即存在外键的表
___________________________________________________________________
建表：要先建主表，再建从表。
删表：先删从表，再删主表(对表中的数据进行删除）
delete from stu_lhj;
delete from c_lhj;
级联删除：删父表，子表也跟着删除on delete cascade
先drop掉表中的外键约束，在重新建约束

alter table stu_lhj drop constraint fk_lhj;

alter table stu_lhj add constraint fk_lhj
 foreign key(cid) references c_lhj(id) on delete cascade;

alter table stu_lhj add constraint fk_lhj
 foreign key(cid) references c_lhj(id) on delete set null;
//当删除主表时，自表相关的字段设置为null
例：班级不存在，但学生还可以存在可能不在原来的班了

user_constraints//存放每一张表的约束，忘了约束名时用它查找

select  CONSTRAINT_NAME,TABLE_NAME,CONSTRAINT_TYPE
     from user_constraints  where table_name='STU_LHJ';

CONSTRAINT_NAME                TABLE_NAME                     C
------------------------------ ------------------------------ -
SYS_C002874                    STU_LHJ                        C
SYS_C002875                    STU_LHJ                        P
FK_LHJ                         STU_LHJ                        R
__________________________________________________________________

向表中插入数据
insert into c_lhj values(1,'java');
insert into c_lhj values(2,'oracle');

insert into stu_lhj values(101,'zhang',1);
insert into stu_lhj values(102,'wang',2);
insert into stu_lhj values(103,'li',2);
insert into stu_lhj values(104,'zhao',1);
insert into stu_lhj values(105,'sun',2);

error example
insert into stu_lhj values(102,'wang',3); --error foreign key(cid)
*
ERROR at line 1:
ORA-02291: integrity constraint (OPENLAB.FK_LHJ) violated - parent key not
found
__________________________________________________________________
SQL
1>DDL-->create drop  alter
2>DML-->insert delete update
3>DCL

DML
=====================================================================
建表方式：
通过子查询建表
create table student_lhj as select id,sname,age from
student_lhj where age>23;
只能创建结构，插入符合条件的数据，只能把原表中的非空约束也跟着创建。
其他的约束需要自己添加。


通过子查询向已存在的表中插入数据。
insert into student_lhj(id,sname,age)
select id,sname,age from studentlhj;

update

update student_lhj set age=25 where id=1003;
update student_lhj set age=25,sname='lhj' where id=1003;
update student_lhj set age=age+10 ;//把所有人的年龄+10
例:
create table lhj_update(
  a number(3),
  b number(3)
);
insert into lhj_update values(2,3);
把lhj_update中a的值该成某个表中最大的id,b的值改为表中最小的age
1>
update lhj_update set a=(select max(id) from studentlhj),
b=(select min(age) from studentlhj);
2>*****
update lhj_update set(a,b)=(select min(id),max(age) from studentlhj);
//更新不能违反约束

delete
delete from student_lhj;//删除表中所有记录
delete from student_lhj where id>104;//删除符合条件的记录
====================================================================
DCL

Transaction
事务管理
事务的四个特性：acid
1、原子性 atomic
2、一致性 consistence
3、隔离性 isolation 没有commit的数据，自己的终端可以看到，                                         
4、持久性 durability                      别人的终端不可以查看到

隔离级别
set transaction isolation;
四种读：
dirty-read 脏读(一个事务能够读到另一个事务没有提交的事务)
committed read (一个事务能够读到另一个事务提交的事务)default
unrepeatable read 重复读
serializable|phantom read 幻影读

(1)user1(table1)             (2)user2(table1)
id name age                  id  name age
1   a   23                    1   a   23
2   b   34                    2   b   34
user1作查询操作               user2 update table1 set age=24;commit;
user1看见a的age仍然是23(重复读)

幻影读
user1设置set transaction isolation level serializable;后
user2 执行dml语句commit;后user1也不能看到.但当user1执行commit后就可以
看到更改后的记录，因为commit之后事务结束，
set transaction isolation level serializable;失效

select * from studentlhj;
set transaction isolation level serializable;

事务的两种级别：
1>局部事务
(数据库的操作都是针对一个数据库的同一个用户)
2>全局事务(也称分布式事务)
(数据库操作是对多个数据库或者一个数据库的不同用户下的表)

在sqlplus中执行第一个语句就会开一个事务,commit;时结束。
dml语句需要用commit;提交或用rollback回滚.
ddl语句自动提交.


例1
delete from student_lhj where id>104;
update student_lhj set age=50 where id=1001;
select * from student_lhj;
rollback;
select * from student_lhj;

例2
加锁
user1 用户1update一条记录
SQL> update student_lhj set age=80 where id=105;

1 row updated.

user2用户2update一条不同的记录--直接完成
SQL> update student_lhj set age=80 where id=103;

1 row updated.

user2用户2 update同一条记录则一直处于等待状态,
直到user1 commit or rolback

SQL>  update student_lhj set age=80 where id=105;

user1
SQL> rollback;

Rollback complete.

user2
1 row updated.

例3
设置回滚点
delete from student_lhj where id=105;
savepoint first_delete;
update student_lhj set sname='aaa' where id=106;
rollback to first_delete;