Shulou(Shulou.com)06/01 Report--

Creating a simple table

1.1 Creating tables using column specifications

Grammar:

CREATETABLE [schema.] tablename [ORGANIZATION HEAP]

(columndatatype [default expression]

[,columndatatype [default expression]]

);

Note: The DEFAULT sentence may be useful, but it has limited functionality.

Analyze the following cases:

CREATETABLE SCOTT.EMP

(EMPNONUMBER(4),

ENAMEVARCHAR2(10),

HIREDATEDATE DEFAULT TRUNC(SYSDATE),

SAL NUMBER(7，2),

COMM NUMBER(7，2) DEFAULT 0.03

);

SET LONG9999

Select dbms_metadata.get_ddl('TABLE','EMP','SCOTT')FROM DUAL;

1.2 Creating tables using subqueries

Grammar:

CREATETABLE [schema.] table AS subquery;

SQL>create tabel emp_copy as select * from scott.emp;

SQL>createtable emp_dept as select

Last_nameename,department_name dname,round(sysdate -hiredate) service

From employees nutural join departments orderby dname,ename;

Create a table with no rows:

SQL>createtable no_emps as select * from scott.emp where 1=2;

1.3. Change table definition after table creation

(1) Add columns

SQL>altertable emp add (job_id number(8));

(2) Modify the column

SQL>alter table emp modify (commission_pct number(6,2) default 0.05;

(3) Delete columns

SQL>altertable emp drop column job_id;

(4) Marking unused columns

SQL>altertable emp set unused column job_id;

(5) Renaming columns

SQL>altertable emp rename column hire_date to hiredate;

(6) Mark the table as read-only

SQL>altertable emp read only;

(7) Delete unused columns at once

SQL>altertable emp drop unused columns;

1.4 delete table

Grammar:

Drop table [schema.] tablename;

SQL>droptable emp;

Note: No warnings are generated before deleting tables (any DDL command is the same, they include COMMIT), they are generally irreversible, and flashback techniques and other recovery techniques can be used in certain situations.

1.5 truncate table

Grammar:

Trancate table [schema.] tablename;

SQL>trancate table emp;

Note: the emp table is cleared instantly. The speed of one record in the table is the same as that of 100 million records.

1.6 Creating and using temporary tables

Temporary tables contain definitions that are accessible to all sessions, but whose rows are specific to the session into which they are inserted.

Grammar:

Create global temporary table temp_tab_name

(columndatatype [,column datatype])

On commit{delete |preserve} rows;

Column definitions are indistinguishable from regular tables, and optional sentences at the end determine the lifetime of any inserted rows. Default: Delete rows when the transaction that inserted them completes, but you can change this behavior to keep them until the end of the session that inserted them. The data for a temporary table is temporary, dedicated to the corresponding session, and all SQL commands executed against it are much faster than command blocks for a permanent table.

Reasons for speed:

(1) Temporary tables are not segments in permanent tablespaces.

(2) DML for temporary tables does not generate redo data.

Creating and Using Temporary Table Cases

(1) Log in as peenboo and create temporary table tmp_emp:

SQL>createglobal temporary table tmp_emp on commit preserve rows as select * from emp where 1=2;

2) Insert some data:

SQL>Insertinto tmp_emp select * from emp where department_id=50;

SQL>commit;

(3) Start the second SQLPLUS using peenboo identity

SQL>selectcount(*) from tmp_emp;

SQL>insertinto tmp_emp select * from emp where department_id =60;

SQL>commit;

(4) Truncate the table in the first session:

SQL>truncate table tmp_emp;

(5) Query in the second session, the session copy still contains rows:

SQL>selectcount(*) from tmp_emp;

(6) In the second session, demo session termination does not purge the data rows. You need to disconnect and reconnect.

SQL>disconnect;

SQL>connectpeenboo/dbbocom

SQL>selectcount(*) from tmp_emp;

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.