Explain Oracle query operations (add, delete, modify, query, pseudo table, advanced query) and oracle Advanced Query

Explain Oracle query operations (add, delete, modify, query, pseudo table, advanced query) and oracle Advanced Query
SQL query operation: add, delete, modify, and queryI,SQL Operator

Arithmetic Operator + -*/

Comparison operator =! = <> <=> = ...... AND IN LINK IS NULL

Logical operator NOT AND OR

Concatenation operator | used to connect strings

Set operator UNION ALL INTERSECT MINUS

Example 1: About + and | Splicing

SQL Server statement: SELECT sid, sname, sex + 'sheng' FROM tb_name;

Oracle statement: SELECT sid, sname, sex | 'sheng' FROM tb_name;

Tip:

In SQL Server, "+" serves both arithmetic and String concatenation, but in Oracle, "+" serves only arithmetic.

Operator priority:

Arithmetic> connection> comparison> NOT> AND> OR

II, Composition of SQL language

Data Definition Language DDL: CREATE, ALTER, DROP

Data manipulation language DML: INSERT, DELETE, UPDATE, SELECT

Transaction control statement TCL: COMMIT, ROLLBACK, SAVEPOINT

Data Control Language DCL: GRANT, REVOKE

II, Data manipulation language DML 1. Add data

Insert into table name [(column name 1, column name 2,...)] VALUES (value 1, value 2 ,......);

Example 1:Insert employees numbered 20 to the new table

Insert into tb_new

SELECT * FROM emp WHERE deptno = 20;

Example 2:Use union to insert multiple data records. Use a pseudo table.

Insert into tb_new

SELECT 1, 'zhang san', 'male' FROM dual UNION

SELECT 2, 'lily', 'female' FROM dual UNION

SELECT 3, 'wang 5', 'male' FROM dual UNION

SELECT 4, 'zhao liu', 'female 'FROM dual;

2. Delete data

Delete from table name [WHERE <condition>]

Example: delete a male-gender record

Delete from emp WHERE sex = 'male'

3. Modify data

UPDATE table name SET column name = expression ,...... [WHERE <condition>]

Example 1: Modify the salary sal of 7369 In the emp table to 1000.

UPDATE emp SET sal = 1000 WHERE no. = '20140901'

Example 2: Modify the salary sal of 7369 In the emp table to 800, and add S after the name.

UPDATE emp SET sal = 800, name = name |'s 'where id = '20140901'

Exercise:

-- Modify the salary + 100 of the Department number in the emp table to 30.

-- Increase the salary of all employees in the emp table by 10%.

4. query/retrieve data

SELECT * FROM Table Name

Iii. query/search 1. Simple Query

SELECT * FROM tb_name;

SELECT sid, sname FROM tb_name;

Select distinct sname FROM tb_name;

SELECT age * 2 FROM tb_name;

SELECT sname AS name FROM tb_name;

2. Query the pseudo table dual

Dual pseudo table, which is indeed a table with only one Field

SELECT * FROM dual;

Why do we need a pseudo table?

According to the SQL standard in Oracle databases, the SELECT statement must have a FROM statement, that is, a table name.

SELECT 9*999 FROM dual;

SELECT 'good learn', 'daily up' FROM dual;

Tip: use dual to create a pseudo table, that is, use it when data is not in any table.

3. Pseudo-column ROWID and ROWNUM

ROWID is the storage address and unique identifier of the row in the table.

You can use ROWID to quickly find a row.

ROWNUM is similar to the ROW_NUMBER () function in SQL Server. It consecutively numbers each queried record.

In Oracle, paging is generally used.

Example 1: View pseudo Columns

SELECT *, ROWID, rownum from tb_name;

Example 2: Calculate the first two records (Oracle does not support TOP)

SELECT *, rownum from emp where rownum <3;

Example 3: Evaluate 3 ~ Five Records (ROWNUM can only act on smaller than number)

SELECT * FROM (

SELECT *, ROWNUM rid FROM emp

) Tb WHERE rid> 2 AND rid <5;

Example 4: the first five people who want to pay

SELECT t. *, rownum from (

SELECT * FROM emp order by sal DESC

) T where rownum <6;

4. Query clause 4.1 WHERE clause

WHERE is a conditional query, and is generally followed by the query conditions.

SELECT * FROM tb_name WHERE sex = 'male ';

SELECT * FROM tb_name WHERE age <18;

SELECT * FROM tb_name WHERE name LIKE 'sheet % ';

4.2 group by clause

Group by is a GROUP query, that is, grouping BY a column. It is generally used for grouping statistics and is often used in combination with Aggregate functions.

Select count (*) FROM tb_name group by deptno;

4.3 HAVING clause

HAVING is used to filter the query results after grouping queries.

Select count (*) FROM tb_name group by deptno having count (*)> 2;

4.4 order by clause

Order by is a column in ascending or descending ORDER.

SELECT * FROM tb_name order by birthday DESC;

5. Query common functions 5.1 Aggregate functions

COUNT (* or column name), SUM (column name), AVG (column name), MAX (column name), MIN (column name)

5.2 NVL (exp1, exp2)

If exp1 is NULL, exp2 is used. Same as isNull () in SQL Server.

Example: Calculate the income of each person in the emp table (salary + bonus)

SELECT sal + NVL (comm, 0) FROM emp;

Note:

SELECT t. *, rownum from emp t order by sal + NVL (comm, 0 );

Use the ROWNUM semicolon before sorting. The result will cause ROWNUM confusion.

5.3 NVL (exp1, exp2, exp3)

If exp1 is NULL, exp2 is returned; otherwise, exp3 is returned.

Example:

SELECT sal + NVL (comm, 0, comm + 100) FROM emp;

-- If comm is NULL, 0 is used to replace comm; otherwise, comm + 100 is used.

5.4 IS NULL

Oracle requires that NULL cannot use "=" to judge the logical relationship. It must use "is null.

Example: Query persons without bonuses

SELECT * FROM emp WHERE comm is null;

Iv. Advanced Query 1. Multi-table join query

Cartesian product cross query:

SELECT e. ename, d. dname FROM emp e, dept d

Inline query: returns data from multiple tables.

SELECT * FROM emp e, dept d WHERE e. deptno = d. deptno;

SELECT * FROM emp e inner join dept d ON e. deptno = d. deptno;

Left and right outer query:

The left outer query is based on the left table, and the right outer query is based on the right table.

How can I determine the left table or the right table? The preceding table is the left table, and the following table is the right table.

SELECT * FROM emp e left join dept d ON e. deptno = d. deptno;

SELECT * FROM emp e right join dept d ON e. deptno = d. deptno;

(+) Left outer method:

SELECT * FROM emp e, dept d WHERE e. deptno = d. deptno (+ );

(+) Right outer method:

SELECT * FROM emp e, dept d WHERE e. deptno (+) = d. deptno;

Tip: (+) =: put it on the left of the equal sign, indicating right join;

= (+): Place it on the right of the equal sign, indicating left join;

Full join query:

Full join does not support (+ ).

SELECT * FROM emp e full join, dept d ON e. deptno = d. deptno;

2. Subquery (nested query) 2.1 simple subquery

Simple subquery refers to the subquery returnOne ValueAs a condition for external queries. (Use>, =, <, etc)

SELECT deptno FROM emp WHERE ename = 'King'

Example 1: 3rd ~ 6 employees

SELECT * FROM (

SELECT t. *, ROWNUM mya FROM (

SELECT * FROM emp order by sal + NVL (comm, 0) DESC

) T

) A WHERE mya BETWEEN 3 AND 6;

-- ROW_NUMBER:

SELECT * FROM (

SELECT t. *, ROW_NUMBER () OVER (order by sal + NVL (comm, 0) DESC) myr FROM emp t

) T WHERE myr BETWEEN 3 AND 6;

2.2 IN or not in subquery

IN or not in subqueries are returned by subqueries.Multiple values.

SELECT * FROM tb_name WHERE sid IN (

SELECT sid FROM tb_name WHERE sex = 'male'

);

2.3 EXISTS or not exists subquery

The EXISTS operation checks the rows that exist in the subquery result set.

Example 1: Check whether an emp table exists

If exists (SELECT * FROM sysdatabases WHERE name = 'emp ')

Example 2: Find the manager with at least one employee

SELECT * FROM emp

Where exists (

SELECT 'x' FROM employees WHERE r_id = e_id

);

If X is returned, TRUE is returned. Otherwise, FALSE is returned. If TRUE is returned, X is returned.