Oracle DDL+DML+DCL Instances

The SQL language is divided into four categories:

Data Query Language DQL:

The DQL basic structure of the data query language is a query block consisting of a SELECT clause, a FROM clause, a WHERE clause.

Data Manipulation Language DML:

DML is shorthand for data manipulation language (manipulation Language). If the SELECT statement is a read operation on the data, the DML statement writes to the data. The action object of a DML statement is a row in a table that can affect one or more rows of data at a time.

Data Definition Language DDL:

The DDL (data definition Language) is used to manipulate the properties of objects and objects, such as the database itself, as well as database objects, such as tables, views, and so on, and the DDL's management and definition of these objects and properties is represented in the Create, Drop and alter on. Special note: The concept of "object" for DDL operations, "Objects" includes the properties of objects and objects, and the smallest objects are more hierarchical than records. For example: Create creates a data table, alter can change the field of the table, drop can delete the table, from here we can see the height of the DDL station, he will not operate on the specific data.

Data Control Language DCL:

The operation of the DCL (Data Control Language) is the permission of the database object, the determination of which makes the data more secure, the user has the right to operate on a certain kind of data is determined by the DBA, the DCL operator (user) at this time the user refers to the database user.

Put a diagram of the three people on the Internet

Ddl

Main statement of the DDL (operation)
Create statement: Some objects of the database and database can be created.
Drop statement: You can delete data tables, indexes, triggers, conditional constraints, and permissions for data tables.
Alter statement: Modifies the data table definition and properties.

-to create a table named P_studentCreate Tablep_student (Snovarchar2(5) not NULL Primary Key, Snamevarchar2( -) not NULL, Ssexvarchar2(1) not NULL, Sagevarchar2(2));

Table status after creation see

--Modifying the type of a columnAlter TableP_student Modify (Sno Number(4));--Rename a columnAlter TableP_student RenamecolumnSage toAge ;--Add a data columnAlter TableP_studentAdd(Score Number(2));--Delete a data columnAlter TableP_studentDrop columnSsex;--Rename table nameAlter TableP_student Rename toStudent

Modify the property status of the table after the completion of the table structure see

-- Delete a p_student table that already exists Drop table Student;

Dml

DML consists of three actions: INSERT, Delete, modify (UPDATE).

The purpose of the INSERT statement is to insert a row into the table in the syntax format:

INSERT into table (column 1, column 2 ...) VALUES (expression 1, expression 2). ) ;

 --  No column option default full insert   Insert  into  student values  (1 , "  ABC   ", 23 , 99);  --  insert  into  student (sno,sname,age) values  (2 , "  oka   ", 24 ); 

The INSERT statement also allows you to copy data from another table by using a subquery in the INSERT statement in the following syntax format:
INSERT into table 1 (column 1, column 2 ... ）
SELECT column 1, column 2??? From table 2 WHERE conditional expressions;

The SELECT clause here is actually a subquery.

When executing such a statement, the SELECT clause is executed first, and the returned query result is inserted into the table as the value of the specified column.
You can insert multiple rows into the table in this way, but be aware that the columns specified by the table are aligned with the columns in the SELECT clause in the order, data type, and quantity.

 --Copying TablesCreate TableStudent_copy as Select *  fromstudent;--modifying data in a tableUpdateStudent_copySetSno=3 whereSno=1;UpdateStudent_copySetSno=4 whereSno=2;--each column of the entire record is insertedInsert  intoStudentSelect *  fromStudent_copywhereSno=3;--Insert the specified column of a recordInsert  intoStudent (Sno,sname)SelectSno,sname fromStudent_copywhereSno=4;

Operation Complete data status see

UPDATE statement

The purpose of the UPDATE statement is to modify the data that already exists in the table.

It can modify one row at a time, or you can modify multiple rows.
The syntax format for this statement is:

UPDATE table Name set column name = Expression 1, column name = expression 2 WHERE condition;

The UPDATE statement modifies the column value to the specified expression by specifying a new value for the specified column through the SET clause.

Specify all the columns that need to be modified in the SET clause.
By default, the UPDATE statement does not require a WHERE clause, and the UPDATE statement modifies all rows in the table.

If a condition is specified through a WHERE clause, the UPDATE statement modifies only the rows that satisfy the condition.

Subqueries can also be used in the WHERE clause of the UPDATE statement. The condition at this point is not a definite condition, but rather depends on the query on the other table.

-- all rows in the table are modified by default without a Where condition Update Set score=;

--update the specified columnUpdateStudentSetSname='xiaoming', age=' -' whereSno=4;--updating with a subqueryUpdateStudentSet(Sname,age)=(SelectSname,age fromStudent_copywhereSno=3)whereSno=4;UpdateStudentSetSname=(SelectSname fromStudent_copywhereSno=3), age=(SelectAge fromStudent_copywhereSno=4)whereSno=3; UpdateStudentSetSname=(SelectSname fromStudent_copywhereSno=3), age=(SelectAge fromStudent_copywhereSno=4);

Delete statement

The DELETE statement is used to delete the specified row from the table, which can delete one row at a time, or you can delete multiple rows.
The syntax format for the DELETE statement is:

DELETE from table name WHERE condition;

By default, the DELETE statement can not use the WHERE clause, and all rows in the table will be deleted.

If you want to delete only a subset of the data in a table, you need to specify the criteria through where.

A subquery can also be used in the WHERE clause of the DELETE statement, which is the same as the subquery usage in the SELECT statement.

--Delete the specified row dataDelete  fromStudentwhereSno=4;--Delete Row data with name ABCDelete  fromStudentwhereSname='ABC'--Delete all dataDelete  fromstudent;--using the delete of subqueriesDelete  fromStudentwhereSname=(SelectSname fromStudent_copywhereSno=3);Delete  fromStudentwhereSnameinch(SelectSname fromStudent_copy);

DCL

Main statement of the DCL (operation)

Grant statement: Allows the creator of an object to give certain permissions to a user or to a group or all users (public).
Revoke statement: You can revoke access rights for a user or group or all users

--DCL Data Control Language/** System permissions: Is the right to perform a specific operation in the database, which is not targeted at a particular object, but is scoped to the entire database.  Common system permissions are: Create SESSION connected to the database create SEQUENCE creation sequence, sequence is a series of numbers, usually used to automatically populate the primary key column create synonym created object with the same name create TABLE creates any Table creating tables in any Mode drop table delete tables drop any table delete tables in any schema create PROCEDURE creates a stored procedure execute any PROCEDURE execute stored procedure in any mode create U SER Create user Drop user Delete the CREATE view creates the View object permission is the right to perform actions on a particular pattern object, only to set and manage objects for the schema object new Ah, these objects including tables, views, stored procedures and other commonly used object permissions are: SELECT allows the execution of the check Action Insert allows the insert operation update allows the modify operation delete allows the delete operation execute word order execution stored procedure **/--Create userCreate UserXiaoming identified by 123;--Assigning permissions to usersGrant CreateSessionCreate User,Create Table  toxiaoming;--revoke a user's permissionRevoke Create Table  fromxiaoming;--View system and object permissions for the current userSelect *  fromUser_sys_privs;Select *  fromUser_tab_privs;

Oracle DDL+DML+DCL Instances