创建表 SQL> create table customers ( customer_id NUMBER(12) , cust_first_name VARCHAR2(40) , cust_last_name VARCHAR2(40) , nls_language VARCHAR2(3) , nls_territory VARCHAR2(30) , credit_limit NUMBER(9,2) , cust_email VARCHAR2(100) , account_mgr_id NUMBER(12), customer_since DATE, customer_class VARCHAR(40), suggestions VARCHAR(40), dob DATE, mailshot VARCHAR(1), partner_mailshot VARCHAR(1), preferred_address NUMBER(12), preferred_card NUMBER(12) ); Table created. 导入数据 SQL> exec dbms_stats.gather_schema_stats('SOE'); PL/SQL procedure successfully completed. SQL> set line 200 SQL> select table_name,num_rows,blocks,status,tablespace_name from user_tables; TABLE_NAME NUM_ROWS BLOCKS STATUS TABLESPACE_NAME ------------------------------ ---------- ---------- -------- ------------------------------ CUSTOMERS 1000000 16087 VALID SOE SQL> col data_type for a15 SQL> col nullable for a10 SQL> select table_name,column_name,data_type,data_length,nullable from user_tab_columns where table_name = 'CUSTOMERS'; TABLE_NAME COLUMN_NAME DATA_TYPE DATA_LENGTH NULLABLE ------------------------------ ------------------------------ --------------- ----------- ---------- CUSTOMERS CUSTOMER_ID NUMBER 22 N CUSTOMERS CUST_FIRST_NAME VARCHAR2 40 Y CUSTOMERS CUST_LAST_NAME VARCHAR2 40 Y CUSTOMERS NLS_LANGUAGE VARCHAR2 3 Y CUSTOMERS NLS_TERRITORY VARCHAR2 30 Y CUSTOMERS CREDIT_LIMIT NUMBER 22 Y CUSTOMERS CUST_EMAIL VARCHAR2 100 Y CUSTOMERS ACCOUNT_MGR_ID NUMBER 22 Y CUSTOMERS CUSTOMER_SINCE DATE 7 Y CUSTOMERS CUSTOMER_CLASS VARCHAR2 40 Y CUSTOMERS SUGGESTIONS VARCHAR2 40 Y TABLE_NAME COLUMN_NAME DATA_TYPE DATA_LENGTH NULLABLE ------------------------------ ------------------------------ --------------- ----------- ---------- CUSTOMERS DOB DATE 7 Y CUSTOMERS MAILSHOT VARCHAR2 1 Y CUSTOMERS PARTNER_MAILSHOT VARCHAR2 1 Y CUSTOMERS PREFERRED_ADDRESS NUMBER 22 Y CUSTOMERS PREFERRED_CARD NUMBER 22 Y 创建索引 SQL> create unique index customers_pk on customers (customer_id); Index created. SQL> col index_name for a30 SQL> col index_type for a15 SQL> col table_name for a20 SQL> col tablespace_name for a20 SQL> select index_name,index_type,table_name,num_rows,distinct_keys,blevel,status,tablespace_name from user_indexes; INDEX_NAME INDEX_TYPE TABLE_NAME NUM_ROWS DISTINCT_KEYS BLEVEL STATUS TABLESPACE_NAME ------------------------------ --------------- -------------------- ---------- ------------- ---------- -------- -------------------- CUSTOMERS_PK NORMAL CUSTOMERS 1000000 1000000 2 VALID SOE SQL> col column_name for a30 SQL> select index_name,table_name,column_name,column_position from user_ind_columns; INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION ------------------------------ -------------------- ------------------------------ --------------- CUSTOMERS_PK CUSTOMERS CUSTOMER_ID 1 创建主键约束 SQL> alter table customers add constraint customers_pk primary key (customer_id); Table altered. SQL> col constraint_name for a30 SQL> col constraint_type for a15 SQL> col table_name for a30 SQL> col index_name for a30 SQL> select constraint_name,constraint_type,table_name,index_name,status from user_constraints where constraint_type = 'P'; CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME INDEX_NAME STATUS ------------------------------ --------------- ------------------------------ ------------------------------ -------- CUSTOMERS_PK P CUSTOMERS CUSTOMERS_PK ENABLED SQL> col constraint_name for a30 SQL> col table_name for a30 SQL> col column_name for a30 SQL> select constraint_name,table_name,column_name,position from user_cons_columns; CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION ------------------------------ ------------------------------ ------------------------------ ---------- CUSTOMERS_PK CUSTOMERS CUSTOMER_ID 1