Oracle SQL statement 5 categories: DDL, DML, DQL, DCL, TCL

Tip: After the article is written, the table of contents can be automatically generated, how to generate can refer to the right side of thehelp file

Oracle The 5 major categories of SQL statements: DDL, DML, DQL, DCL, TCL

5 Categories of SQL Statements

1. DDL (Data Definition Language)

1. CREATE (creation order)

a. Conventional tabulation

b. Tabulation based on query result sets

2. ALTER (modification order)

1. Add columns

2. Modify the data type of the column

3. Modify the column name

4. Delete columns

5. Modify the table name

3. DROP (delete command)

4. TRUNCATE (clearing order)

2. DML (Data Manipulation Language)

1. INSERT Insertion

a. Query result insertion method

b. Conventional insertion

2. UPDATE Updates

3. DELETE Delete

4. MERGE condition modification

3. DQL (Data Query Language)

4. DCL (Data Control Language)

1. GRANT statement

2. REVOKE statement

5. TCL (Transaction Control Language)

5 Categories of SQL Statements

1. DDL (Data Definition Language)

DDL operations are used to define, modify, andDelete Databasestructure and attributes of an object. These operations do not directly involve manipulation of data, but rather affect the overall structure of the database. Common DDL operations include:

CREATE(Create command)
ALTER(modification order)
DROP(Delete command)
TRUNCATE(Empty command)

1. CREATE (creation order)

CREATE: forCreating a databaseobjects such as tables, indexes, views, etc.

a. Conventional tabulation

Grammar:

  CREATE TABLE table name
(field name(listings) Data type Constraints,
     field name(listings) data type
);

Creating a User Information Table

  CREATE TABLE USER_INFO
    (ID NUMBER primary key,
     U_NAME VARCHAR2(50),
     SEX VARCHAR2(10),
     BIRTHDAY DATE
    );

b. Tabulation based on query result sets

The method of building a table based on a query result set is also known asDuplicate table construction method The constraints of the original table (e.g. primary key, foreign key) cannot be copied.
Grammar:

  CREATE TABLE table nameAS
  SELECT query statement;

a. Create a table plus copy the original table data

  CREATE TABLE EMP_01 AS --will copy table data
  SELECT * FROM EMP;

b. Only the original table table fields are copied (but not the original table constraints).

  CREATE TABLE EMP_1010 AS
  SELECT * FROM EMP 
  WHERE 1=2;  --When a where filter does not hold, the data is not copied.

2. ALTER (modification order)

ALTER: Used to modify the structure of database objects, such as table structure changes, column additions, etc.

1. Add columns

--Grammar:
  ALTER TABLE table nameADD Column name Data type;
--Add columns to the User Information table (User Cell Phone Number field)
  ALTER TABLE USER_INFO ADD PHONE_NUMBER VARCHAR2(20)

在这里插入图片描述

2. Modify the data type of the column

--Grammar:
  ALTER TABLE table nameMODIFY Column name New data type;
--Change the data type of the user's cell phone number field to a numeric type
  ALTER TABLE USER_INFO MODIFY PHONE_NUMBER NUMBER(20);

在这里插入图片描述

3. Modify the column name

--Grammar:
  ALTER TABLE table nameRENAME COLUMN original listingTO new listing;  The --column keyword can't be missing
--Rename the cell phone number field PHONE_NUMBER to P_NUMBER in the user information table.
  ALTER TABLE USER_INFO RENAME COLUMN PHONE_NUMBER TO P_NUMBER;

在这里插入图片描述

4. Delete columns

--Grammar:
 ALTER TABLE table nameDROP COLUMN listings;  The --column keyword can't be missing
--Delete the user cell phone number P_NUMBER field in the User Information table.
 ALTER TABLE USER_INFO DROP COLUMN P_NUMBER;

在这里插入图片描述

5. Modify the table name

--Grammar:
  ALTER TABLE original table nameRENAME TO New watch name;
-- Rename the user information table USER_INFO to USER_INFO_TEST1.
  ALTER TABLE USER_INFO RENAME TO USER_INFO_TEST1;

3. DROP (delete command)

DROP: Used to delete database objects, such as deleting tables, indexes, views, etc.

--Grammar:
  DROP TABLE table name/indexing/view
--Delete table EMP_1009
  DROP TABLE EMP_1009;

4. TRUNCATE (clearing order)

TRUNCATE: Used to delete all data from a table, but retain the structure of the table.

TRUNCATE empties the table by essentially deleting the entire table and then recreating it.

--Grammar:
  TRUNCATE TABLE table name;
--Empty the user table EMP_1010 and keep the table structure.
  TRUNCATE TABLE EMP_1010;

Clear the table before: 在这里插入图片描述
After emptying the table:

2. DML (Data Manipulation Language)

DML operations are used to manipulate and process data in a database. These operations affect the content of the data, not the structure of the database objects. Common DML operations include:

INSERT: Used to insert new data into a table.
UPDATE: Used to modify the values of existing data in a table.
DELETE: Used to delete data from a table.
MERGE: Used to perform insert, update, and delete operations based on conditions, depending on the match between the source data and the target table.

1. INSERT Insertion

a. Query result insertion method

--Grammar:
INSERT INTO table name(field name1,field name2,...) select Query Result Set;
--Example:
INSERT INTO EMP_1010(ename,job,sal) select ename,job,sal from emp;  --Insert the query result set into the table

在这里插入图片描述

b. Conventional insertion

--Grammar:
 INSERT INTO table name(field name1,field name2,...) VALUES ((be) worth1,(be) worth2,...);
 COMMIT; --Submitted
--Example:
INSERT INTO USER_INFO_test1(ID,U_NAME,SEX,BIRTHDAY) VALUES (1234567,'AZHEN','W',TO_DATE('19990101', 'YYYYMMDD'));
INSERT INTO USER_INFO_test1(ID,U_NAME,SEX,BIRTHDAY) VALUES (1234568,'AQIANG','M',TO_DATE('19990201', 'YYYYMMDD'));
INSERT INTO USER_INFO_test1(ID,U_NAME,SEX,BIRTHDAY) VALUES (1234569,'FULAOSHI','M',TO_DATE('19580201', 'YYYYMMDD'));
COMMIT; --Submitted

在这里插入图片描述

2. UPDATE Updates

--Grammar:
update table nameset field=new valuewhere filtration conditions
-Example:
update ssm set name='xiaocui' where name='AZHEN';
update ssm set name='xiaoliu' where name='AZHEN' and age=27;

3. DELETE Delete

--Grammar:
delete from table namewhere ...
-Example:
delete from ssm where name='AZHEN' and age=27;

4. MERGE condition modification

The MERGE statement is used in Oracle databases to perform simultaneous INSERT and UPDATE operations, often to merge data between two tables. This is a very powerful feature, especially for data synchronization and update scenarios.The general syntax of the MERGE statement is as follows:

MERGE INTO target_table USING source_table
ON (condition)
WHEN MATCHED THEN
    UPDATE SET column1 = value1, column2 = value2, ...
WHEN NOT MATCHED THEN
    INSERT (column1, column2, ...) VALUES (value1, value2, ...);

Below is an explanation of each section:

MERGE INTO target_table: Specifies the target table, i.e., the table into which you want the data to be inserted or updated.
USING source_table: Specifies the source table, i.e., the table that provides the data.
ON (condition): Defines the condition that will be used to match the data between the target table and the source table. If the condition is valid, an UPDATE operation is performed, otherwise an INSERT operation is performed.
WHEN MATCHED THEN: This piece is executed if there is a match between the target and source tables. Here you can define how to update the data in the target table.
UPDATE SET column1 = value1, column2 = value2, ...: In the case of a match, you can use the UPDATE clause to update the columns of the target table.
WHEN NOT MATCHED THEN: This piece is executed if no matching data is found in the target table. Here you can define how to insert data from the source table into the target table.
INSERT (column1, column2, ...) VALUES (value1, value2, ...): In the case of a mismatch, you can use the INSERT clause to insert data from the source table into the target table.

Suppose we have a target table employees and a source table temp_employees and we want to merge the employees data from the source table into the target table. We can execute the following MERGE statement:

MERGE INTO employees e
USING temp_employees t
ON (e.employee_id = t.employee_id)
WHEN MATCHED THEN
    UPDATE SET e.salary = t.salary
WHEN NOT MATCHED THEN
    INSERT (e.employee_id, e.first_name, e.last_name, e.salary)
    VALUES (t.employee_id, t.first_name, t.last_name, t.salary);

In this example, if a matching employee (based on employee_id) is found in the target table, the salary column in the target table is updated. If no match is found in the target table, new employee data is inserted. In this way, you can ensure that the target table contains the most recent employee data.

3. DQL (Data Query Language)

DQL operations are used to retrieve data from a database. These operations help you query the data you need for viewing and analyzing. The only DQL operations are:

SELECT: Used to select columns from one or more tables, filter data by conditions and constraints, and perform operations such as aggregation and sorting.

select * from emp；

4. DCL (Data Control Language)

DCL operations are used to control access rights to database objects and rights management. It is used to manage the security and access levels of the database. Common DCL operations include:

GRANT: Used to grant users and roles access to database objects.
REVOKE: Used to revoke user and role access to database objects.
scope of one's jurisdiction

1. GRANT statement

The GRANT command is used to grant specific database privileges to users or roles. The following are some common types of privileges that can be granted using the GRANT command:

SELECT: Allows the user to retrieve data from a table.
INSERT: Allows the user to insert new data into the table.
UPDATE: Allows the user to modify existing data in the table.
DELETE: Allows the user to delete data from a table.
CREATE: Allows the user to create new tables, views, or other database objects.
ALTER: Allows users to modify the structure of existing database objects.
DROP: Allows the user to delete database objects.
EXECUTE: Allows the user to execute a procedure or function.
ALL: Gives the user all available privileges.
DBA Give database administration privileges.
connect grant
resourceGive permissions to view resources, create tables, and so on.
select any tableGives permission to view any table.
execute any procedureExecute procedure file permissions across users.
WITH GRANT OPTIONWhen using the GRANT command, you can add the WITH GRANT OPTION option to allow authorized users to subgrant the privileges they have been granted to other users or roles.

--Grammar:
grant scope of one's jurisdictionto subscribers--Granting User Privileges
grant scope of one's jurisdiction1Permissions2... to subscribers--Grant multiple privileges to users at the same time

grant dba to xiaocui;  --Give xiaocui users database administration privileges (DBA)
grant connect,resource to xiaocui;  --Grant basic privileges to xiaocui users (login, resource access)
grant select any table to xiaocui; --Give permission to view any table

2. REVOKE statement

REVOKE: Used to revoke user and role access to database objects.

--Grammar:
revoke scope of one's jurisdictionfrom subscribers--Remove user privileges
revoke scope of one's jurisdiction1Permissions2... from subscribers--Remove multiple privileges from a user at the same time

revoke dba from xiaocui;  --Reclaim DBA privileges for user xiaocui.
revoke select any table from xiaocui; --Remove user xiaocui's permission to view any table.

5. TCL (Transaction Control Language)

TCL operations are used to manage transactions in a database. A transaction is a set of database operations that are either all executed successfully or all rolled back. Common TCL operations include:

COMMIT: Used to permanently save changes from a pending transaction to the database.
ROLLBACK: Used to undo changes to a transaction that have not yet been committed, returning to the state prior to the start of the transaction.
SAVEPOINT: Used to create a save point in a transaction to which you can roll back.

In an Oracle database, theCOMMIT is a SQL command used to confirm and permanently save previously performed transaction operations. When you execute COMMIT, it saves the changes made in the current transaction to the database and ends the transaction. This is a key concept in transaction processing to ensure data integrity and consistency. Here is some important information about COMMIT and how to use it:

Submission of transactions：

The COMMIT command is used to commit the current transaction. Once COMMIT is executed, all changes made by DML (Data Manipulation Language) statements (e.g., INSERT, UPDATE, DELETE) previously executed in that transaction are permanently saved to the database.
Changes made by the current transaction are not visible to other sessions or transactions until they are committed. Once committed, other sessions or transactions can see the changes.

grammatical: The COMMIT command has no arguments and its syntax is very simple, just execute COMMIT;.

commit;

Automatic submission：

In an Oracle database, some operations (such as SELECT) are usually committed automatically if COMMIT is not explicitly executed. This means that each individual statement is treated as a separate transaction and is automatically committed upon execution.
To turn off auto-commit mode, you can use the SET AUTOCOMMIT OFF; command, which allows you to manually execute COMMIT after executing multiple statements.

rolling back：

If an error occurs before executing a COMMIT or you do not want to save previous changes, you can use the ROLLBACK command to roll back the transaction.ROLLBACK cancels all changes in the transaction and restores the database to the state it was in before the transaction began.
For example, ROLLBACK; can be used to cancel all changes to the current transaction.

transaction control：
Using COMMIT and ROLLBACK you can control the start and end of a transaction. Within a transaction, you can perform multiple operations and then decide whether to commit or rollback those operations as needed.

In an Oracle database, theROLLBACK is a SQL command used to undo all uncommitted changes in a transaction. It allows you to roll back a transaction and restore the database to the state it was in before the transaction started to ensure data integrity and consistency. Here is some important information about ROLLBACK and how to use it:

Rolling back transactions：

The ROLLBACK command is used to roll back all uncommitted changes in the current transaction. This includes changes made by all DML (Data Manipulation Language) operations (e.g., INSERT, UPDATE, DELETE) performed within the current transaction.
Executing ROLLBACK; restores the database to the state it was in before the start of the current transaction, canceling all changes performed in that transaction.

grammatical：
The ROLLBACK command has no parameters and its syntax is very simple, just execute ROLLBACK;.

rollback；

Rollback to save point：

Use the ROLLBACK TO command to roll back a transaction to a specific save point instead of rolling back the entire transaction. Savepoints are created using the SAVEPOINT command, which allows you to create temporary recovery points at different stages within a transaction and then roll back to those points when needed.
Example: ROLLBACK TO my_savepoint.

Rollback Control：
ROLLBACK allows you to control the rollback of a transaction. If an error occurs during the execution of a transaction or you do not want to save previous changes, you can use ROLLBACK to undo those changes.
Automatic rollback：
In an Oracle database, if any statement within a transaction causes an error, Oracle automatically performs a rollback operation to ensure database consistency. This is the default behavior of the database.

SAVEPOINT is a mechanism used in Oracle Database to implement transaction control that allows you to create a savepoint within a transaction to which you can roll back in subsequent operations. This is useful for partial rollbacks within a transaction without having to rollback the entire transaction.The general syntax of SAVEPOINT is as follows:

SAVEPOINT savepoint_name;

The following are examples and descriptions of how to use SAVEPOINT:

Creating a save point：
You can create a savepoint using the SAVEPOINT statement as follows:

SAVEPOINT my_savepoint;

This creates a savepoint named my_savepoint in the current transaction.

Rollback to save point：
Once you have created a savepoint, you can roll back to that savepoint in subsequent operations using the ROLLBACK TO statement, as shown below:

ROLLBACK TO my_savepoint;

This will undo all changes made between the creation of the savepoint and the rollback point, but will retain any changes made from the start of the transaction to the creation of the savepoint.

Releasing the save point：
You can use the RELEASE command to release a savepoint, thereby removing it from the transaction. Once released, you will not be able to roll back to that savepoint again. An example is shown below:

RELEASE SAVEPOINT my_savepoint;

nested savepoints：
You can create multiple savepoints in the same transaction to form a nested savepoint structure. This way, you can roll back to different savepoints at different points in time without affecting changes between other savepoints.

SAVEPOINT savepoint1;
-- Perform a number of operations
SAVEPOINT savepoint2;
-- Perform a number of operations
ROLLBACK TO savepoint1; -- can be rolled back to savepoint1, but will not affect savepoint2

SAVEPOINT is a useful feature, especially in complex transactions, that allows you to create temporary recovery points at different stages within a transaction to roll back to a specific state if needed without having to roll back the entire transaction. This is important for the maintenance of data integrity and consistency.