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