Oracle SQL Foundation (III): Data Manipulation language

The data manipulation language, manipulation language, which checks for DML, mainly includes the search (SELECT), insert, UPDATE, delete, is a core part of SQL. A DML begins a transaction, and the next DML is the statement in the same transaction until it commits (commits) or rolls back (ROLLBACK). Let's describe the syntax and instance resolution of INSERT, UPDATE, delete, and merge (merge) in Oracle.

One, INSERT statement

1. Syntax of INSERT statement

Insert single-line record syntax:insert into table [(column [, Column ...])] VALUES (value [, value ...]);

The statement adds rows to the list with the values clause, one row at a time. The field list is not required in the INSERT clause, and if you do not use a field list, the values must be in the default order of the fields in the table. To make the statement clearer, use the field list in the INSERT clause. Character and date values should be placed in single quotes, numeric values are not required, and if single quotes are used, the implicit conversion of numeric values can occur.

Insert subquery result (multiline) syntax:insert into table [(column [, Column ...])] subquery;

The number and data type of the columns in the field list of the INSERT clause must match the number of columns in the subquery and their data type. Inserting sub-query data in two cases: one from the related table or multiple tables to query the data to be inserted, the other is to query the specific data from the dual table, sub-query, to obtain the corresponding data.

2. Examples of INSERT statements

As the company has a new department, it needs to insert a data in the departmental table (DEPT), the department number is 50, the department name is the data center (datacentre), the department is located in China, and the employee table (EMP) Inserts the information of the department leader, the employee number is 7950, The name is Sjzh, the position, the leader number and other department manager, the entry time is today, the salary is the job for the manager salary average, the bonus is empty, the department number is 50. The implementation code for the INSERT statement is as follows

-----Insert department information for a new departmentINSERT  intoDept (Deptno,dname,loc)VALUES( -,'datacentre',' China');-----Insert employee information for new department managersINSERT  intoEMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)SELECT 7950,       'Sjzh',       'MANAGER',       (SELECT DISTINCTMgr fromEmpWHEREJob='MANAGER'), Sysdate, (SELECT avg(SAL) fromEmpWHEREJob='MANAGER'),       NULL,        - fromDual

INSERT Instance

Second, UPDATE statement

1. Syntax for UPDATE statements

Update syntax with value: Update table SET column=value[,column=value,...] [WHERE condition];

Typically, a single row is identified with a primary key, and if you use a different column, you might unexpectedly cause another row to be updated. Without constraints, the entire column will be updated.

Update syntax with subquery: Update table SET column=subquery[,column=subquery,...] [WHERE condition];

You can update columns based on the target table or other tables. The primary key is the only constraint in the table and cannot be duplicated, and the foreign key is a constraint that is set when the field in the table is the same as the primary key field name of the other table, and the data cannot be updated or inserted when the primary KEY constraint or FOREIGN KEY constraint is not met.

2. Examples of UPDATE statements

Now we need to transfer two people from other departments to the newly created Department (department number 50, department leader number 7950), one analyst Scott, pay unchanged, one is the lowest-paid clerk, and the pay is raised to the level of the clerk's pay ranking two people. The update implementation code is as follows

----Update analyst Scott's employee informationUPDATEEMPSETMgr=7590, Deptno= -WHEREEmpno=7788;----Update employee information for the lowest wage clerkUPDATEEMPSETMgr=7590, Deptno= -, Sal=(SELECTSal from(SELECTSal,rownum No from(SELECTJob,sal fromEMPWHEREJob='Clerk'                     ORDER  bySal)WHEREJob='Clerk'               )         WHERENo=2         )WHERESal=(SELECTSal from(SELECT *  fromEmpORDER  bySal)WHEREJob='Clerk'  andRowNum=1);

UPDATE Instance

Third, DELETE statement

1. Syntax for Dalete statements

Delete Record syntax:delete [from] table [wher condition];

If you do not specify a WHERE clause, all data in the table is deleted, and only the structure of the table is preserved, and another more efficient way to clear the table is to use the TRUNCATE statement.

A subquery can be used in a deleted condition, and a subquery can be a table for any table that is not a deleted row. Before committing the delete, take a look at whether you have added the condition or not, whether the condition is the only constraint, or if it is really the data to delete the condition constraint.

2. Example of DELETE statement

We need to cancel the operations department (OPERATIONS) of the Department table dept, and first remove the employee information from all operations departments in the Employee form EMP, and then delete the records from the Operations Department in Dept. If the employee's table also has employees in that department, deleting the department directly in the Department table will result in an error that violates the integrity constraints. The delete implementation code is as follows

-----Delete all employee information for department operationsDELETE  fromEMPWHEREDeptno=(SELECTDeptno fromDeptWHEREDname='OPERATIONS');-----Delete Department Operations department informationDELETE  fromDeptWHEREDname='OPERATIONS';

DELETE Instance

Iv. MERGE Statements

1, the syntax of the MERGE statement

The merge statement, which can be called a "dissolve" statement, is equivalent to the synthesis of INSERT and UPDATE statements. Typically, you operate on two tables, whether you perform an update operation on the destination table or an insert operation on the destination table, depending on the condition in the ON clause. Merge has the following features: Avoid distributed updates, improve performance and ease of use, and are useful in data warehousing applications. The syntax for merge merging is as follows

MERGE intoTable_nametable_alias---Specify the destination table you are updating or inserting using (Table|View|sub_query) Alias---Specify the data source to use for updating or inserting on(Joincondition)---similar to table join conditions, which can be updated or inserted in this condition whenMatchedThen ---When the condition is met, update the entry to the destination tableUPDATE 
SETcol1=col1_val[,col2=Col2_val ...] when  notMatchedThen ---Insert the entry into the destination table when the condition is not metINSERT(column_list)VALUES(column_values);

In a data warehouse environment, you may have a large fact table and a table with a smaller number of dimensions, and the rows in the small table need to be conditionally inserted into a large fact table, such as when working with data from multiple sources, some of which may be identical and need to conditionally add or modify rows. In this case, the merge statement is useful.

2. Example of a MERGE statement

Now, to merge the data from the employee table emp_copy into the Employee table EMP, match the empno column of the Emp_copy table with the empno column of the EMP table. If a match is found, the matched column values in the EMP table are updated with the column values of the matching rows in the Emp_copy table. If the matching row is not found, the column values of the matching rows in the Emp_copy table are inserted into the Copy_emp table.

----Construct an employee table for merging Emp_copyCREATE TABLEEmp_copy as SELECT *  fromEmpWHEREDeptnoinch(Ten, +);INSERT  intoemp_copy (Empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(7899,'ASD','Clerk',7839, To_date ('2017-1-23','YYYY-MM-DD'), -,0,Ten);UPDATEEmp_copySETSal=1900 WHEREEmpno=7934;----merge data from employee table emp_copy into the Employee form EMPMERGE intoemp eusing emp_copy C on(E.empno=c.empno) whenMatched ThenUPDATESETE.ename=C.ename,--since the e.empno=c.empno is already present in the on condition, the sentence cannot be found here. E.job=C.job, E.mgr=C.mgr, E.hiredate=c.hiredate, E.sal=C.sal, E.comm=C.comm, E.deptno=C.deptno when  notMatched ThenINSERTVALUES(C.EMPNO,C.ENAME,C.JOB,C.MGR,C.HIREDATE,C.SAL,C.COMM,C.DEPTNO);

MERGE Instance

Oracle SQL Foundation (III): Data Manipulation language