|
1.数据库操纵语言
DML语句实在下面的时候执行 向表添加新行
更新表现有的行
从表删除现有行
一个事务处理是由一系列的DML语句逻辑组成
A.insert 每次插入一行数据 字符和日期的需要单引号引起来，日期的插入需要to_date()处理
SQL> insert into dept values(54,'',null); //插入空值
1 row created.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
54
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
一次插入多条数据使用子查询，即insert into es select * from emp;
DML事务结束需要commit/DDL/DCL/exit/conn
取消事务 rollback/quit
SQL> alter table dept modify(loc default 'TX');
Table altered.
SQL> insert into dept values (56,default,default);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from dept where deptno=56;
DEPTNO DNAME LOC
---------- -------------- -------------
56 TX
SQL> desc user_tab_columns
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME NOT NULL VARCHAR2(30)
COLUMN_NAME NOT NULL VARCHAR2(30)
DATA_TYPE VARCHAR2(106)
DATA_TYPE_MOD VARCHAR2(3)
DATA_TYPE_OWNER VARCHAR2(30)
DATA_LENGTH NOT NULL NUMBER
DATA_PRECISION NUMBER
DATA_SCALE NUMBER
NULLABLE VARCHAR2(1)
COLUMN_ID NUMBER
DEFAULT_LENGTH NUMBER
DATA_DEFAULT LONG
NUM_DISTINCT NUMBER
LOW_VALUE RAW(32)
HIGH_VALUE RAW(32)
DENSITY NUMBER
NUM_NULLS NUMBER
NUM_BUCKETS NUMBER
LAST_ANALYZED DATE
SAMPLE_SIZE NUMBER
CHARACTER_SET_NAME VARCHAR2(44)
CHAR_COL_DECL_LENGTH NUMBER
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
AVG_COL_LEN NUMBER
CHAR_LENGTH NUMBER
CHAR_USED VARCHAR2(1)
V80_FMT_IMAGE VARCHAR2(3)
DATA_UPGRADED VARCHAR2(3)
HISTOGRAM VARCHAR2(15)
SQL> select column_name,data_default from user_tab_columns where table_name='DEPT';
COLUMN_NAME DATA_DEFAULT
-------------- --------------
DEPTNO
DNAME
LOC 'TX'
B.update 根据需要可以更新一条或者多条记录
C.delete 不释放表空间，truncate 删除释放空间
SQL> analyze table e3 compute statistics;
Table analyzed.
SQL> select num_rows,blocks from user_tables where table_name='E3';
NUM_ROWS BLOCKS
---------- ----------
112 8
SQL> delete e3;
112 rows deleted.
SQL> analyze table e3 compute statistics;
Table analyzed.
SQL> select num_rows,blocks from user_tables where table_name='E3';
NUM_ROWS BLOCKS
---------- ----------
0 8
SQL> truncate table e3;
Table truncated.
SQL> analyze table e3 compute statistics;
Table analyzed.
SQL> select num_rows,blocks from user_tables where table_name='E3';
NUM_ROWS BLOCKS
---------- ----------
0 0
表经常delete的话会造成空间的浪费，就是说删除后空间没有释放
解决方法：alter table e3 mvoe;或者导出表
SQL> c/emp/e3
1* select count(*) from e3
SQL> /
COUNT(*)
----------
196
SQL> analyze table e3 compute statistics;
Table analyzed.
SQL> select num_rows,blocks from user_tables where table_name='E3';
NUM_ROWS BLOCKS
---------- ----------
196 5
SQL> delete e3 where rownum<180;
179 rows deleted.
SQL> select count(*) from e3;
COUNT(*)
----------
17
SQL> analyze table e3 compute statistics;
Table analyzed.
SQL> select num_rows,blocks from user_tables where table_name='E3';
NUM_ROWS BLOCKS
---------- ----------
17 5
SQL> alter table e3 move;
Table altered.
SQL> analyze table e3 compute statistics;
Table analyzed.
SQL> select num_rows,blocks from user_tables where table_name='E3';
NUM_ROWS BLOCKS
---------- ----------
17 4
2.事务
DDL/DCL语句自动提交
事务结束与开始
save point 保存点 rollback to 保存点
3.数据类型
vrachar2 4000
char 2000
number(p,s)
interval 间隔
SQL> create table t1(id numb |