Oracle之创建表与修改表详解
Oracle中表有、标准表、索引表、簇表、分区表。现在主要介绍标准表的创建和修改内容。其他表的创建以及使用会放到其他章节中。
第一部分创建表：
一）创建表
Create table [schema.]table_name (
Column_name date_type [default expression]constraint;
Eg:
create table scott.mybook(
bookid number(10) not null quiue,
bookname varchar2(40),
author varchar2(40),
press varchar2(40),
bookprice number(4,2),
presstime date,
constraint book1_pk primary key(bookid)
)
二）查看表：
通过describe查看表的概述信息，如果查看表中每个列的信息可以通过查看user_tab_columns这个视图进行检索相关内容。
Eg：
SQL> describe user_tab_columns;
Name Type Nullable Default Comments
-------------------- ------------- -------- ---------------------------------------------------------------------------
TABLE_NAME VARCHAR2(30) Table, view or cluster name
COLUMN_NAME VARCHAR2(30) Column name
DATA_TYPE VARCHAR2(106)Y Datatype of thecolumn
DATA_TYPE_MOD VARCHAR2(3) Y Datatype modifier of thecolumn
DATA_TYPE_OWNER VARCHAR2(30) Y Owner of the datatype of thecolumn
DATA_LENGTH NUMBER Length of the column inbytes
DATA_PRECISION NUMBER Y Length: decimal digits (NUMBER)or binary digits (FLOAT)
DATA_SCALE NUMBER Y Digits to right of decimal point in anumber
NULLABLE VARCHAR2(1) Y Does column allow NULLvalues
COLUMN_ID NUMBER Y Sequence number of the columnas created
DEFAULT_LENGTH NUMBER Y Length of default value for thecolumn
DATA_DEFAULT LONG Y Default value for thecolumn
NUM_DISTINCT NUMBER Y The number of distinct valuesin the column
LOW_VALUE RAW(32) Y The low value in thecolumn
HIGH_VALUE RAW(32) Y The high value in thecolumn
DENSITY NUMBER Y The density of the column
NUM_NULLS NUMBER Y The number of nulls in thecolumn
NUM_BUCKETS NUMBER Y The number of buckets inhistogram for the column
LAST_ANALYZED DATE Y The date of the most recenttime this column was analyzed
SAMPLE_SIZE NUMBER Y The sample size used inanalyzing this column
CHARACTER_SET_NAME VARCHAR2(44) Y Character set name
CHAR_COL_DECL_LENGTH NUMBER Y Declarationlength of character type column
GLOBAL_STATS VARCHAR2(3) Y Are the statistics calculatedwithout merging underlying partitions
USER_STATS VARCHAR2(3) Y Were the statistics entereddirectly by the user
AVG_COL_LEN NUMBER Y The average l