10.表和数据的基本操作

-----------------------表和数据的基本操作－－－－－－－－－－－－－－－－－－－－－－


一、表和列的基本操作


1、创建表
SQL> conn scott/scott
Connected.
SQL> create table bttest(t1 varchar2(3),t2 date,t3 number,t4 char(7));


Table created.


SQL> desc bttest;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 T1                                                 VARCHAR2(3)
 T2                                                 DATE
 T3                                                 NUMBER
 T4                                                 CHAR(7)


VARCHAR2(3)变长字符串——当存储不足指定长度的时候，就存储这些字符，不填充。
CHAR(7)定长字符串——当存储的内容不足7个字节的时候，右边用空格补足。


2、给表添加一列
SQL> alter table bttest add t5 varchar2(6);


Table altered.


SQL> desc bttest;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 T1                                                 VARCHAR2(3)
 T2                                                 DATE
 T3                                                 NUMBER
 T4                                                 CHAR(7)
 T5                                                 VARCHAR2(6)


3、给表添加一个主键——特殊的索引，单独占存储空间的。


不能为空且唯一的标识这个行记录的字段作主键，一般不用有业务含义的字段作主键，
用序列生成一个ID作主键的。
SQL> alter table bttest add constraint PK_BTTEST primary key(t1);


Table altered.




4、修改列的数据类型


SQL> alter table bttest modify t5 number;


Table altered.


SQL> desc bttest;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 T1                                        NOT NULL VARCHAR2(3)
 T2                                                 DATE
 T3                                                 NUMBER
 T4                                                 CHAR(7)
 T5                                                 NUMBER


注意：如果这个T5列中存储的是字符非1234567890，比如abc。。，这样的转化是报错的。


5、修改表、列的名字
SQL> alter table bttest rename to test;


Table altered.


SQL> desc bttest;
ERROR:
ORA-04043: object bttest does not exist


SQL> desc test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 T1                                        NOT NULL VARCHAR2(3)
 T2                                                 DATE
 T3                                                 NUMBER
 T4                                                 CHAR(7)
 T5                                                 NUMBER


SQL> alter table test rename column t5 to t51; （修改列的名字） 


Table altered.


SQL> desc test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 T1                                        NOT NULL VARCHAR2(3)
 T2                                                 DATE
 T3                                                 NUMBER
 T4                                                 CHAR(7)
 T51                                                NUMBER


6、删除列
SQL> alter table test drop column t51;


Table altered.


SQL> desc test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 T1                                        NOT NULL VARCHAR2(3)
 T2                                                 DATE
 T3                                                 NUMBER
 T4                                                 CHAR(7)


删除多列
SQL> alter table test drop (t3,t4);


Table altered.


SQL> desc test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 T1                                        NOT NULL VARCHAR2(3)
 T2                                                 DATE


7、给表和列添加注释


SQL> comment on table test is 'xxxxxx';


Comment created.


SQL> comment on column test.t1 is 'yyyyyyy';


Comment created.


8、表的删除
SQL> drop table test;


Table dropped.


SQL> desc test;
ERROR:
ORA-04043: object test does not exist


二、DML语句——对数据的操作


SQL语句分类
A 查询语句：select
B DML（data managed language）——insert update delete
C DDL（data defined language）——create alter drop
D DCL（data control language）——grant revoke


1、insert操作


SQL> create table bttest(t1 varchar2(3),t2 date,t3 number,t4 char(7));


Table created.


SQL> insert into bttest values('x11',sysdate,23,'dd');


1 row created.


SQL> commit;


Commit complete.


SQL> select * from bttest;


T1  T2                          T3 T4
--- ------------------- ---------- -------
x11 2013-07-15 11:41:24         23 dd


SQL> insert into bttest values('x12',sysdate,23,'dd');


1 row created.


SQL> rollback;  －－回滚操作 
Rollback complete.


SQL> insert into bttest values('x12',sysdate,23,'dd');


1 row created.


SQL> rollback;


Rollback complete.


SQL> select * from bttest;


T1  T2                          T3 T4
--- ------------------- ---------- -------
x11 2013-07-15 11:41:24         23 dd


===在oracle中，一个commit或者rollback操作表示一个事务的结束。===
例如：下面是一个事务
SQL> insert into bttest values('x12',sysdate,23,'dd');


1 row created.


SQL> insert into bttest values('x12',sysdate,23,'dd');


1 row created.


SQL> insert into bttest values('x12',sysdate,23,'dd');


1 row created.


SQL> insert into bttest values('x12',sysdate,23,'dd');


1 row created.


SQL> commit;


Commit complete.




2、特殊的insert——表的克隆CAST方式创建表并插入数据
SQL> desc emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)


SQL> create table k_emp as select * from emp;  表的结构和其中的数据全部克隆


Table created.


SQL> desc k_emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)


只克隆结构不克隆数据：
SQL> create table p_emp as select * from emp where 1=2;


Table created.


SQL> select count(1) from p_emp;


  COUNT(1)
----------
         0


注意：CAST方式创建表，原表中的索引、主外键不会被克隆的。


3、特殊INSERT——从一个表将数据导入另外一个相同的表


SQL> insert into p_emp select * from emp;


14 rows created.


SQL> commit;


Commit complete.


4、update操作
SQL> update p_emp set job='xxxx' where empno=7788;


1 row updated.


SQL> commit;


Commit complete.


SQL> update p_emp set job='yyy',sal=5000 where empno=7369;


1 row updated.


SQL> commit;


Commit complete.




5、delete操作
SQL> delete from p_emp where empno=7788;


1 row deleted.


SQL> commit;


Commit complete.
------------------------------------------------


DDL——truncate操作


SQL> truncate table p_emp;   －－只能整表将数据删除，而不能删除一部分数据（where）。


不写日志，操作快，数据恢复的话，比较麻烦。


Table truncated.


SQL> select count(1) from p_emp;


  COUNT(1)
----------
         0


SQL> insert into p_emp select * from emp;


14 rows created.


SQL> commit;


Commit complete.


SQL> delete from p_emp;  －－这个操作写日志的，操作的过程很慢，数据删除了，也能恢复回来。DML操作。


14 rows deleted.


SQL> commit;


Commit complete.


SQL> select count(1) from p_emp;


  COUNT(1)
----------
         0