Oracle SQL statements Practice merge, fuzzy query, sort,

Oracle supported SQL directives can be divided into data manipulation language statements, data definition language statements, transaction control statements, session control statements, and several other types:
1. Data Manipulation Language statement
Data manipulation language statements (manipulation LANGUAGE,DML) are used for data retrieval and update operations. Data retrieval is the most frequently used type of operation in database applications, so the efficiency of data retrieval has a significant impact on the overall performance of the database. Data updates include operations such as inserting, modifying, and deleting data, and data update operations have a certain risk that the DBMS must ensure data consistency during its execution to ensure data is valid.
SELECT, INSERT, DELETE, UPDATE, MERGE
2. Data Definition Language statement
Data definition language,ddl is used to define the format and morphology of data, such as defining database objects such as data tables, views, and indexes. The first thing a user would want to use when establishing a database is a DDL statement.
CREATE, ALTER, DROP, RENAME, TRUNCATE
3. Data Control Language statement
Data Control LANGUAGE,DCL is used to authorize or revoke permissions for users, or to use roles to implement volume or revoke permissions on users, which is less useful at the application development level.
GRANT, REVOKE
4. Transaction control Statements
Transaction control statements (Transaction control Statement) are used to implement database transaction management
COMMIT, ROLLBACK, savepoint
5. Session Control statements
Session control Statement is used to dynamically modify the properties of the current user session and is rarely used at the application development level.
ALTER SESSION, SET ROLE
II. Foundation of SQL
1. Delete data from the table
Using the delet directive, you can delete data that is already in the data table, for example:
DELETE student; Or:
DELETE from student;
This is the most basic form of the "delete" instruction, which removes all data from the student table, but the structure of the table still exists, that is, you can then insert data operations on the table.
2. View table Structure
Use the "DESC" or "DESCRIBE" directives to view the structure of the specified table, including the names of the fields in the table, the data types, and whether to allow null values. For example:
DESC student;
DESCRIBE student;
3. Delete a table
Use the Drop directive to delete an existing data table, including the structure of the data and tables in the table, for example:
DROP TABLE student;
The student table is no longer available after deletion.

INSERT into Myemp (SELECT ename, Sal, hiredate from EMP);
INSERT into Myemp (name, salary) (SELECT ename, sal from EMP);

--Create a sample table
CREATE TABLE Myemp1 (empno number (4), ename VARCHAR2 (Ten), Job VARCHAR2 (9), salary number (7,2));
CREATE TABLE MYEMP2 (empno number (4), ename VARCHAR2 (Ten), Job VARCHAR2 (9), salary number (7,2));
CREATE TABLE Myemp3 (empno number (4), ename VARCHAR2 (Ten), Job VARCHAR2 (9), salary number (7,2));
CREATE TABLE Myempother (empno number (4), ename VARCHAR2 (Ten), Job VARCHAR2 (9), salary number (7,2));
INSERT all when job= ' MANAGER ' and into MYEMP1
When job= ' clerk ' and into MYEMP2
When job= ' ANALYST ' and into Myemp3
ELSE into Myempother
SELECT empno, ename, Job, Sal from EMP;

To modify a field:
ALTER TABLE EMP2 RENAME colunm SALARY to SAL;
========================
Merge Operation
The merge operation is used to implement a data merge--modify or insert the data in the table according to the criteria, perform an update operation if the record to be inserted already exists in the target table, or perform an insert operation. The syntax format is as follows:
MERGE into Dest_table [alias]
USING (source_table | view | sub_query) [alias]
On (join_condition)
When matched then
UPDATE SET column1 = column1_value, Column2 = Column2_value
When isn't matched then
INSERT (column_list) VALUES (column_values);
Where dest_table is used to specify the target table to which the data is to be merged, Source_table (view,sub_query) is the source table (view or subquery) that provides the data, and the ON clause is used to specify the connection criteria for the data in the merge operation. For each record in the source table, if it finds its corresponding record in the target table (a record that conforms to the join condition), then executes the UPDATE statement of the when matched and then branch, modifies the corresponding record in the target table, otherwise executes when is not matched Then branch of the INSERT statement that inserts the current record of the source table into the target table.
A visual example is given below:
DROP TABLE test1;
DROP TABLE test2;
--Create source data table Test1, insert two sample data
CREATE TABLE test1 (Eid number), name VARCHAR2 (a), birth date,salary number (8,2));
INSERT into Test1 VALUES (1001, ' Zhang San ', ' 20月-May-1970 ', 2300);
INSERT into Test1 VALUES (1002, ' John Doe ', ' 1 June-April-1973 ', 6600);
--select * from Test1;
--Create a Target data table test2, insert a boilerplate data
CREATE TABLE test2 (Eid number), name VARCHAR2 (a), birth date,salary number (8,2));
INSERT into Test2 VALUES (1001, ' Harry ', ' 2 September-November-2008 ', 2300);
--select * from Test2;
--Merge the data from the source table test1 into the target table test2
MERGE into Test2 USING test1 in (Test1.eid = Test2.eid) When matched then UPDATE SET name = test1.name, birth = Test1.birt H, salary = test1.salary when not matched then INSERT (Eid, name, birth, salary) VALUES (Test1.eid, Test1.name, Test1.birth , test1.salary);
--Query the data contents of the target table test2 after merging
SELECT * from Test2;
Sql> SELECT * from Test2;

EID NAME BIRTH SALARY
------------------------------------------------------
1001 Zhang October-January -10 2300
1002 John Doe October-January -10 6600
As you can see, because a record row of eid=1001 already exists in the Test2 table, the merge operation only modifies it and inserts a new record (eid=1002).
It is important to note that when you do a merge operation, the UPDATE statement in the when matched and then branch does not allow updating the Reference field that establishes the record connection relationship between tables (such as the Eid field above), because it destroys the correspondence between records. For example, the above merge statement is changed to the following form:
MERGE into Test2 USING test1 on (Test1.eid = Test2.eid) When matched then UPDATE SET Eid = test1.e ID, name = test1.name, birth = test1.birth, salary = test1.salary when not matched then INSERT (Eid, name, birth, salary) VALUES (Test1.eid, Test1.name, Test1.birth, test1.salary);
Line 1th Error:
ORA-38104: Cannot update the column referenced in the ON clause: "TEST2". " EID "
===============================================================================

The

SQL Plus auto-commit
SQL plus executes the SQL statement by default to non-autocommit, or it can be explicitly set by using the following directives:
set autocommit on; Set to Autocommit
set autocommit OFF;--set to non-autocommit
The above settings are only valid for this database connection session and will revert to their default settings after SQL Plus is restarted. You can also perform the following command to see how it is currently submitted:
SHOW autocommit;
If the returned result "autocommit OFF" is not automatically committed, such as return "Autocommit IMMEDIATE" is automatically committed. The
Autocommit mode affects the performance and transaction logic of your application, and you cannot use AUTOCOMMIT if your application requires transactional integrity.
====================================
Save Point
If necessary, You can also use the savepoint (savepoint) in a transaction to create a tag at a key point in the current transaction, and in the future you can roll back to the specified token (savepoint) to implement a partial rollback of the transaction. For example:
INSERT into Dept VALUES (' Adv ', ' Beijing ');
INSERT into Dept VALUES (, ' Sec ', ' Shanghai ');
SavePoint p1;
INSERT into Dept VALUES (, ' Acc ', ' Tianjin ');
---
SELECT * from dept;
ROLLBACK to P1;
SELECT * FROM dept;
=================================
uses a connection expression
in an Oracle database, you can use the Join operator | | Concatenate a field with text, or other expressions, and get a new string that implements the function of the composite column. For example, the following statement:
sql> SELECT ' number: ' | | empno, ENAME | | ' Wages are: ', sal | | ' Meta ' from EMP;

' No.: ' | | EMPNO ename| | ' The salary is: ' sal| | ' Yuan '
--------------------------------------------- ------------------- ---------
Item No: 7369 Smith's salary is: 800 yuan
Item No: 7499 Allen's salary is: 1600 yuan
Item No: 7521 Ward's salary is: 1250 yuan
Item No: 7566 Jones's salary is: 2975 yuan
Item No: 7654 Martin's salary is: 1250 yuan
Item No: 7698 Blake's salary is: 2850 yuan
Item No: 7782 Clark's salary is: 2450 yuan
Item No: 7788 Scott's salary is: 3000 yuan
Item No: 7839 King's salary is: 5000 yuan
Item No: 7844 Turner's salary is: 1500 yuan
Item No: 7876 The salary of Adams is: 1100 yuan
Item No: 7900 James's salary is: 950 yuan
Item No: 7902 Ford's salary is: 3000 yuan
Item No: 7934 Miller's salary is: 1300 yuan
Sql> SELECT ' Dept. ' | | Deptno | | ' s name is ' | | Dname from DEPT;
ERROR:
ORA-01756: string in quotation marks does not end correctly

Sql> SELECT ' Dept. ' | | Deptno | | "s name is ' | | Dname from DEPT;

' DEPT ' | | deptno| | " Snameis ' | | Dname
----------------------------------------------------------------------
Dept.10 ' s name is ACCOUNTING
dept.20 ' s name is
dept.30 ' s name is SALES
dept.40 ' s name is OPERATIONS
=====================================
using aliases
In a SELECT statement, you can use aliases to rename the target table, as well as the fields (or expressions) in the query results, to enhance readability. If you use special characters in aliases, or if they are case-sensitive, enclose the alias in double quotation marks, otherwise prohibit the use of quotation marks, or else an error occurs, whereas a join expression uses single quotation marks. For example:
Sql> select Empno as number, ename name, sal*12 annual salary, ename, ename "ename", sal*12 "Anual Salary" from EMP T1;
Numbered name annual salary ename ename anual Salary
---------- ---------- ---------- ---------- ---------- ------------
7369 Smith 9600 Smith Smith 9600
7499 Allen 19200 Allen Allen 19200
7521 Ward 15000 Ward Ward 15000
7566 Jones 35700 Jones Jones 35700
7654 Martin 15000 Martin Martin 15000
7698 BLAKE 34200 BLAKE BLAKE 34200
7782 Clark 29400 Clark Clark 29400
7788 Scott 36000 Scott Scott 36000
7839 King 60000 King King 60000
7844 Turner 18000 Turner Turner 18000
7876 Adams 13200 Adams Adams 13200
7900 James 11400 James James 11400
7902 Ford 36000 Ford Ford 36000
7934 Miller 15600 Miller Miller 15600
The alias "Anual Salary" contains the special word spaces, and the alias "Ename" is to enforce its capitalization in the query results, so enclose it in double quotation marks. The keyword "as" between a field/expression and its alias can be omitted, and the keyword "as" is not allowed between the table name and its alias.

Sql> SELECT ' sid: ' | | Sid number, ' name: ' | | Name title, age-1 years from student; Null value in
============================================
expression
The entire expression result is empty if there is a null value in the arithmetic expression. This is because null values are unknown, indeterminate values, and cannot simply be treated as numeric values, so any arithmetic expression with null values is unknown and indeterminate. For example, the result of a null value multiplied by 2 is still null. When you use the ' | | ' operator, the null value that appears is treated as an empty (zero-length) string. This is because Oracle currently handles empty strings in the same way as null values.
School name Age
-------------------------------------------------
sid:101 names: 317
sid:102 Name: John Doe
Sid : 103 Name: Harry
sid:104 name:35
===================================================
remove Duplicate rows
By default, the query results contain all record rows that match the criteria, including duplicate rows. For example, the following statement
SELECT deptno from EMP;
Returns the value of the Deptno field for each row of records in the EMP table, with repeated occurrences, and if you just want to see the department number that is present/involved in the current EMP table, you can use the DISTINCT keyword in the SELECT statement to filter out all duplicate rows in the query results. Example statement:
sql> SELECT DEPTNO from EMP;
DEPTNO
----------

Ten
(
)
,
,
,
,
,
,
,
,
,
,
14 rows have been selected.
sql> SELECT DISTINCT DEPTNO from EMP;
DEPTNO
----------

20
10
The scope of the distinct is the combination of all subsequent fields, which means that no duplicate field combinations will occur, but a single field value in the composition may appear duplicated, such as the following SQL statement:
SELECT DISTINCT deptno, job from EMP;
The function of query/statistic department number and position setting in department is realized.
DROP TABLE student;
CREATE TABLE Student (
Sid VARCHAR2 (10),
Name VARCHAR2 (20),
Age Number (3)
);
INSERT into student VALUES (101, ' Zhang San ', 18);
INSERT into student VALUES (102, ' John Doe ', 25);
INSERT into student VALUES (103, ' Zhang San ', 28);
INSERT into student VALUES (103, ' Zhang San ', 28);
SELECT * from student;
SELECT DISTINCT * from student;
============================================
Sort by single field
Select statement query results are arranged by default in the physical order of the records in the table (that is, the order in which records are inserted), or you can use the ORDER BY clause in the SELECT statement to sort the results of the query, including ascending (ascending) and descending (descending). Identified using the keyword ASC and DESC, respectively, by default ascending.
Sql> SELECT empno,ename,sal from EMP ORDER by SAL;
EMPNO ename SAL
---------- ---------- ----------
7369 SMITH 800
7900 JAMES 950
7876 ADAMS 1100
7521 WARD 1250
7654 MARTIN 1250
7934 MILLER 1300
7844 TURNER 1500
7499 ALLEN 1600
7782 CLARK 2450
7698 BLAKE 2850
7566 JONES 2975
7902 FORD 3000
7788 SCOTT 3000
7839 KING 5000
8888
15 rows have been selected.

Sql> SELECT empno,ename,sal from EMP ORDER by SAL DESC;
EMPNO ename SAL
---------- ---------- ----------
8888
7839 KING 5000
7902 FORD 3000
7788 SCOTT 3000
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
7499 ALLEN 1600
7844 TURNER 1500
7934 MILLER 1300
7521 WARD 1250
7654 MARTIN 1250
7876 ADAMS 1100
7900 JAMES 950
7369 SMITH 800

15 rows have been selected.
For the ascending order of numeric fields, each row of the query results is arranged in the order of the field values from small to large. Similarly, the ascending arrangement of text-type fields is arranged in the English dictionary order of the text content (the string), and the ascending order of date/Time fields is arranged by date/time from "early" to "late". The reverse is the case in descending order.
Sort by multi-field combination
If you want to implement sorting by multiple fields, you can give the list of fields to be sorted sequentially in the ORDER BY clause, setting how each field should be sorted, such as the following statement:
Sql> SELECT deptno, empno, ename, Sal from emp ORDER by Deptno, Sal;

DEPTNO EMPNO ename SAL
---------- ---------- ---------- ----------
Ten 7934 MILLER 1300
7782 CLARK 2450
Ten 7839 KING 5000
7369 SMITH 800
1100 7876 ADAMS
7566 JONES 2975
7788 SCOTT 3000
7902 FORD 3000
7900 JAMES 950
7654 MARTIN 1250
7521 WARD 1250
7844 TURNER 1500
7499 ALLEN 1600
2850 7698 BLAKE

14 rows have been selected.

Sql> SELECT deptno, empno, ename, Sal from emp ORDER by Sal,deptno;

DEPTNO EMPNO ename SAL
---------- ---------- ---------- ----------
7369 SMITH 800
7900 JAMES 950
1100 7876 ADAMS
7521 WARD 1250
7654 MARTIN 1250
Ten 7934 MILLER 1300
7844 TURNER 1500
7499 ALLEN 1600
7782 CLARK 2450
2850 7698 BLAKE
7566 JONES 2975
7788 SCOTT 3000
7902 FORD 3000
Ten 7839 KING 5000

14 rows have been selected. In the first
Null value in sort
The query results in the Oracle database are sorted, and if a null value is present in the sort field (null), the default is that NULL is the maximum value, so if ascending (ASC) is sorted then the row of records to which the null field belongs is the last, and the descending (DESC) sort is the first.
Sql> SELECT empno, ename, comm from EMP WHERE sal<2000 ORDER by comm;

EMPNO ename COMM
---------- ---------- ----------
7844 TURNER 0
7499 ALLEN 300
7521 WARD 500
7654 MARTIN 1400
7369 SMITH
7876 ADAMS
7900 JAMES
7934 MILLER

8 rows have been selected.

Sql> SELECT empno, ename, comm from EMP WHERE sal<2000 ORDER by Comm DESC;

EMPNO ename COMM
---------- ---------- ----------
7369 SMITH
7900 JAMES
7876 ADAMS
7934 MILLER
7654 MARTIN 1400
7521 WARD 500
7499 ALLEN 300
7844 TURNER 0

8 rows have been selected.
The Oracle database also supports the use of the keyword nulls first or nulls last in the ORDER BY clause to specify that record rows that have a null value for the Sort field are ranked at the top or bottom of the result set (whether in ascending or descending order)
Sql> SELECT empno, ename, comm from EMP WHERE sal<2000 ORDER by comm DESC NULLS last;

EMPNO ename COMM
---------- ---------- ----------
7654 MARTIN 1400
7521 WARD 500
7499 ALLEN 300
7844 TURNER 0
7934 MILLER
7876 ADAMS
7900 JAMES
7369 SMITH

8 rows have been selected.
=======================================
Fuzzy query
% (Percent semicolon)--a wildcard character that represents 0 or more arbitrary characters.
_ (underscore)-a wildcard that identifies a single character and can match a single character.
For example, to query employee information with all names beginning with the letter ' S ', you can use SQL statements:
Sql> SELECT * from EMP WHERE ename like ' s% ';

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH Clerk 7902 1 July-December-80 800 20
7788 SCOTT ANALYST 7566 1 September-April-87 3000 20
It is important to note that characters other than wildcards in the pattern string are case-sensitive, and ' s% ' can only match strings that begin with the uppercase letter ' s ', not the string that begins with the lowercase ' s ', such as ' Smith '.
Sql> SELECT * from EMP WHERE ename like ' _a% ';

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7521 WARD salesman 7698 2 February-February-81 1250 500 30
7654 MARTIN salesman 7698 2 August-September-81 1250 1400 30
7900 JAMES Clerk 7698 March-December-81 950 30
For special symbols in fuzzy queries you can use escape identifiers to escape lookups, such as to find information about employees whose names contain characters ' \ ', you can use the following statement:
SELECT * from emp WHERE ename like '%\_% ' ESCAPE ' \ ';
where escape ' \ ' specifies the character ' \ ' as an escape character, ' \_ ' in the pattern string '%\_% ' is escaped to, or is represented by the character ' _ ' itself, and is no longer used as a wildcard character.
Sql> Select Empno,ename from emp where ename like '%\_% ' ESCAPE ' \ ';

EMPNO ename
---------- ----------
8888 Rusky_lu

Oracle SQL statements Practice merge, fuzzy query, sort,