Oracle's basic operations-DML,DDL,DCL

Second, the SQL language

SQL structure Query Language structured queries language

SQL statement: DDL statement DML statement, DCL statement

Ddl--data define language--create, alter, drop-data definition language

Dml--data Management language--insert, UPDATE, delete--Data manipulation language

Dcl--data Control Language--grant, revoke--Data Control language


Introduction to 1.DDL Statements

1.1 Creating a Table
Sql> Conn Scott/scott;
Connected.

Sql> CREATE TABLE T (
2 T1 VARCHAR2 (20),
3 T2 Date,
4 T3 number,
5 T4 char (7)
6);

Table created.

Sql> desc t;
Name Null? Type
----------------------------------------- -------- ----------------------------
T1 VARCHAR2 (20)
T2 DATE
T3 number
T4 CHAR (7)

VARCHAR2 (20): variable-length string type

CHAR (7): fixed-length string type, less than 7 bits to complement 7 bits with spaces

Number (length, precision) default number (12,2)

Date: Time Type

The above four types are the most common data types, as well as unstructured data types

Long text

Large Object Blob CLOB LOB (forum post, Weibo, Weibo, blog)--Big data platform

To view the structure of a table:

Sql> desc t;
Name Null? Type
----------------------------------------- -------- ----------------------------
T1 VARCHAR2 (20)
T2 DATE
T3 number
T4 CHAR (7)

1.2 Adding a column to a table

Sql> ALTER TABLE T add T5 varchar2 (5);

Table altered.

Sql> desc t;
Name Null? Type
----------------------------------------- -------- ----------------------------
T1 VARCHAR2 (20)
T2 DATE
T3 number
T4 CHAR (7)
T5 VARCHAR2 (5)

1.3 Modifying the data type of a column

Sql> ALTER TABLE T modify T5 number;

Table altered.

Attention:
If the data is stored on the T5 column, the command is unsuccessful and will report a mistake: ORA-01439 's fault


1.4 Modify the name of the table and the name of the column

Sql> ALTER TABLE t rename to test; --Modify Table name

Table altered.

Sql> desc t;
ERROR:
Ora-04043:object T does not exist


sql> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
T1 VARCHAR2 (20)
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 VARCHAR2 (20)
T2 DATE
T3 number
T4 CHAR (7)
T51 number

1.5 Deleting columns

sql> ALTER TABLE test drop column T51; --Delete a column

Table altered.

sql> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
T1 VARCHAR2 (20)
T2 DATE
T3 number
T4 CHAR (7)

sql> ALTER TABLE Test drop (T3,T4); --Delete multiple columns

Table altered.

sql> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
T1 VARCHAR2 (20)
T2 DATE

1.6 Adding comments to tables and columns

sql> Comment on table test is ' Test table '; --Add notes to the table

How to query the comments of a table:
SELECT * from user_tab_comments where table_name= ' TEST ';

sql> comment on column test.t1 is ' Test column '; --Add comments to columns

Comment created.

How to query the comments for a column:
SELECT * from user_col_comments where table_name= ' TEST ';

1.7 Deleting a table

NOTE: Deleting a table will erase the definition of the table and all the data.

sql> drop table test;

Table dropped.

The principle and creation of the reference relation of the primary foreign KEY constraint of the table

(1) Entity integrity
Primary key

(2) Reference integrity
FOREIGN key

(3) User-defined integrity
Generally not implemented in the database

2.1 Adding a primary key to a table

Sql> ALTER TABLE t add constraint pk_t primary key (T1);

Table altered.

Query the user's own created primary key:
SELECT * from User_constraints;

2.2 Deleting a primary key from a table

sql> ALTER TABLE test drop constraint sys_c006822;

Table altered.

2.3 Adding foreign keys to a table
A. Clone table emp_t, add primary key to this table
Sql> CREATE TABLE emp_t as SELECT * from EMP;

Table created.

Sql> ALTER TABLE emp_t add constraint pk_emp_t primary key (EMPNO);

Table altered.

B. Clone table dept_t, add primary key to this table


Sql> CREATE TABLE dept_t as SELECT * from dept;

Table created.

Sql> ALTER TABLE dept_t add constraint pk_dept_t primary key (DEPTNO);

Table altered.


C. Do a foreign key reference on emp_t dept_t

Sql> ALTER TABLE emp_t add constraint fk_emp_t foreign key (DEPTNO) references dept_t (DEPTNO);

Table altered.

2.4 Delete foreign key analysis

(1) Try to delete the reference source

sql> drop table dept_t;
drop table dept_t
*
ERROR at line 1:
ora-02449:unique/primary keys in table referenced by foreign keys
Analysis: The referenced source is not able to be deleted, if it must be deleted, you can use the following two methods:
Method 1: Delete the secondary table first, delete the main table
Method 2: Delete the foreign key first, then delete the main table

sql> ALTER TABLE emp_t drop constraint fk_emp_t;

Table altered.

sql> drop table dept_t;

Table dropped.

Method 3: Disable the foreign key while deleting the primary table
sql> ALTER TABLE emp_t disable constraint fk_emp_t;

Table altered.

sql> drop table dept_t;
drop table dept_t
*
ERROR at line 1:
ora-02449:unique/primary keys in table referenced by foreign keys

(2) Delete the data on the primary table

Sql> Delete from dept_t where deptno=10;
Delete from dept_t where deptno=10
*
ERROR at line 1:
Ora-02292:integrity constraint (SCOTT. fk_emp_t) Violated-child Record found

Analysis: Because there are records about deptno=10 in the emp_t table, Oracle prohibits the deletion of this data for referential integrity

If you must remove
Method 1: Delete the foreign key and then delete the data
Method 2: Delete the deptno=10 on the secondary table and remove the data from the primary table

Delete the data on the primary table, the primary key data on the primary table does not correspond to the secondary table, such as deleting deptno=40 data


2.5 Adding a constraint to a table (defining user-defined integrity)

Add a check

Sql> ALTER TABLE emp_t add constraint dept_no_ck check (deptno in (10,20,30,40));

Table altered.

3. Introduction to DML statements

3.1 INSERT statement
Sql> INSERT into t values (' x11 ', sysdate);

1 row created.

Sql> commit;

Commit complete.

Sql> select * from T;

NAME nowdate
-------------------- -------------------
X11 2014-06-09 15:23:42

Sql> INSERT into t values (' x12 ', to_date (' 20131212 ', ' yyyy-mm-dd '));

1 row created.

Sql> commit;

Commit complete.

Sql> select * from T;

NAME nowdate
-------------------- -------------------
X11 2014-06-09 15:23:42
X12 2013-12-12 00:00:00

Sql> select Sysdate from dual;

Sysdate
-------------------
2014-06-09 15:25:42

Dual: A table of dummy table n column lines

3.2 UPDATE statement

Sql> Update T set nowdate=sysdate+1 where name= ' x12 ';

1 row updated.

Sql> commit;

Commit complete.

Sql> select * from T;

NAME nowdate
-------------------- -------------------
X11 2014-06-09 15:23:42
X12 2014-06-10 15:28:35

3.3 DELETE statement

Sql> Delete from t where name= ' x12 ';

1 row deleted.

Sql> commit;

Commit complete.

Sql> select * from T;

NAME nowdate
-------------------- -------------------
X11 2014-06-09 15:23:42

The whole table is deleted.

Sql> Delete from T;

1 row deleted.

Sql> commit;

Commit complete.


3.4 Truncation of table operations

sql> truncate TABLE t;

Table truncated.

Sql> select * from T;

No rows selected

Delete Full table and truncate full table differences:
1.delete operation will write log, truncate operation does not write log
2.delete operation is slow, truncate fast
3.delete operations can have a where condition, while truncate must be a full table delete
Truncate more dangerous, data may not be found.


3.5 Table Clone--cast way to create table

(1) Data and structure of clone table
Sql> CREATE TABLE Dept_r as SELECT * from dept;

Table created.

sql> desc Dept_r;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO Number (2)
Dname VARCHAR2 (14)
LOC VARCHAR2 (13)

Sql> select * from Dept_r;

DEPTNO dname LOC
---------- -------------- -------------
Ten ACCOUNTING NEW YORK
DALLAS
SALES CHICAGO
OPERATIONS BOSTON

(2) clone only the structure of the table without cloning the table's data

sql> drop table Dept_r;

Table dropped.

Sql> CREATE TABLE Dept_r as SELECT * from dept where 1=2;

Table created.

sql> desc Dept_r;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO Number (2)
Dname VARCHAR2 (14)
LOC VARCHAR2 (13)

Sql> select * from Dept_r;

No rows selected

Inserting data by logical manipulation

sql> INSERT INTO Dept_r select * from dept;

4 rows created.

Sql> commit;

Commit complete.

Summarize:
A cloned table can only clone a table's structure or data, and constraints cannot be cloned.


4. Introduction to the DCL statement

GRANT, REVOKE

Sql> Conn Xp/xp;
Connected.
Sql> Conn/as SYSDBA
Connected.
Sql> revoke connect from XP;

Revoke succeeded.

Sql> Conn Xp/xp;
ERROR:
Ora-01045:user XP lacks CREATE SESSION privilege; Logon denied


Warning:you is no longer connected to ORACLE.

Sql> Conn Xp/xp;
Connected.
Sql> CREATE TABLE T (ID number);
CREATE TABLE T (ID number)
*
ERROR at line 1:
Ora-01031:insufficient Privileges

Sql> Conn/as SYSDBA
Connected.
Sql> grant resource to XP;

Grant succeeded.

Sql> Conn Xp/xp;
Connected.
Sql> CREATE TABLE T (ID number);

Table created.

Two permissions are granted together:
Sql> Grant Connect,resource to XP;

Grant succeeded.

This article is from the "Feng Songlin blog" blog, make sure to keep this source http://fengsonglin.blog.51cto.com/9860507/1615229

Oracle's basic operations-DML,DDL,DCL