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

II. SQL language

SQL structure query language structured query language

SQL statements: DDL statements, DML statements, DCL statements

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 of 1.DDL statement

1.1 create 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 with less than 7 bits filled with spaces

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

Date: time typ

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

Long text long

Blob clob lob (forum posts, Weibo, long Weibo, blog)-- big data platform

View the structure of the table:

SQL > desc t

Name Null? Type

-

T1 VARCHAR2 (20)

T2 DATE

T3 NUMBER

T4 CHAR (7)

1.2 add a column to the 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 modify the data type of a column

SQL > alter table t modify T5 number

Table altered.

Note:

If data is stored in the T5 column, an error will be reported if the command is unsuccessful: ORA-01439 's error.

1.4 modify the names of tables and columns

SQL > alter table t rename to test;-- modify the 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 Delete column

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 and T4);-- Delete multiple columns

Table altered.

SQL > desc test

Name Null? Type

-

T1 VARCHAR2 (20)

T2 DATE

1.6 add comments to tables and columns

SQL > comment on table test is' test table';-- add comments to the table

How to query the comments for a table:

Select * from user_tab_comments where table_name='TEST'

SQL > comment on column test.t1 is' test column';-- add comments to the column

Comment created.

How to query column comments:

Select * from user_col_comments where table_name='TEST'

1.7 Delete a table

Note: when deleting a table, the definition of the table and all the data will be deleted.

SQL > drop table test

Table dropped.

Second, the principle and creation of the primary foreign key constraint reference relation of the table.

(1) physical integrity

Primary key

(2) referential integrity

Foreign key

(3) user-defined integrity

It is generally not implemented in the database.

2.1 add a primary key to the table

SQL > alter table t add constraint PK_T primary key (T1)

Table altered.

Query the primary key created by the user:

Select * from user_constraints

2.2 remove the primary key from the table

SQL > alter table test drop constraint SYS_C006822

Table altered.

2.3 add foreign keys to the table

a. Clone the table emp_t and add a primary key to the 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 the table dept_t and add a primary key to the 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 foreign key reference dept_t on emp_t

SQL > alter table emp_t add constraint FK_EMP_T foreign key (deptno) references dept_t (deptno)

Table altered.

2.4 deletion of 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 cannot be deleted. If you must delete it, you can use the following two methods:

Method 1: delete the secondary table first, and then delete the primary table

Method 2: delete the foreign key first, and 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 and delete 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 main 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 is a record of deptno=10 in the emp_ t table, oracle forbids deleting this data for reference integrity

If you have to delete

Method 1: delete the foreign key and then delete the data

Method 2: delete the deptno=10 on the secondary table and then delete the data of the primary table

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

2.5 add constraints to the table (define user-defined integrity)

Add a check

SQL > alter table emp_t add constraint dept_no_ck check (deptno in (10, 20, 50, 30, 40))

Table altered.

3. Introduction of DML statement

3.1 insert statement

SQL > insert into t values ('x11 recording journal 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 recording recording todddate (' 20131212 recording recordingyyyymurymm copyright 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 with n columns and single rows

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

If the whole table is deleted

SQL > delete from t

1 row deleted.

SQL > commit

Commit complete.

3.4 truncation of table operation

SQL > truncate table t

Table truncated.

SQL > select * from t

No rows selected

The difference between delete full table and truncate full table:

1.delete operations write logs, while truncate operations do not write logs.

2.delete operation is slow, truncate is fast.

3.delete operations can have where conditions, while truncate must be full table deletion

Truncate is dangerous and the data may not be found.

3.5Clone of tables-- creating tables by CAST

(1) the data and structure of the 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

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

(2) only the structure of the table is cloned, but not the data of the table.

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

Insert data by logical operation

SQL > insert into dept_r select * from dept

4 rows created.

SQL > commit

Commit complete.

Summary:

A clone table can only clone the structure or data of the table, and constraints cannot be cloned.

4. Introduction of 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 are 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.

The two permissions are granted together:

SQL > grant connect,resource to xp

Grant succeeded.

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.