Basic SQL _dml statements (1)

1. Remove duplicate rows:

Select distinct ename from EMP;

2. Connection expression

Select ename | 'department ID' | deptno from EMP;

3. process null

Select nvl (ename, 'noname') from EMP; -- if the ename of a row is null, The noname is displayed. If not null, the value of the ename is displayed.

4. date type display format

If the birthday column in the Arwen table is of the date type

Select to_char (birthday, 'yyyy-MM-DD ') from Arwen

5. Between... and in the WHERE clause

Equal to or greater than and less than or equal

6. Like fuzzy query. % indicates 0 to multiple characters _ indicates a single character

7. escape characters:

If there are % in the table, escape characters must be used for symbols like \

Assume that the name in the Arwen table is a % name.

Select * From Arwen where ename like 'a \ % name ';

You can also write select * From Arwen where ename like 'as % name' escape's '; -- s can be replaced with any other character.

8. Multi-column sorting:

In sorting, null is treated as the maximum value.

Select ename, deptno, Sal from EMP

Order by ename ASC, Sal DESC; -- ASC indicates ascending order. By default, all are in ascending order. DESC descending order. This indicates that names are sorted first. names are the same and Sal is used.

Special case: If Union is used to connect two tables, Columns cannot be specified during sorting. Only numbers can be used.

Seelct deptno, dname from Dept

Union

Select empno, ename from EMP

Order by 1; -- indicates sorting by reference to the first column. If it is written as 2, the second column is used.

9. Grouping Functions

Select AVG (empno), sum (empno), max (empno), min (empno), count (empno) from EMP;

-- The grouping function indicates the average value, sum, maximum value, minimum value, and total number of rows respectively.

NULL in the grouping function is ignored and is not in the scope of calculation. Therefore, if empno is null, the total number of rows in the entire table is not calculated. While count (*) is always the total number of rows in the table.

10. Group by usage

The grouping function above applies to the entire table. If we only use the average value of a certain department in the Schedule, and so on.

Select deptno, AVG (SAL) from EMP

Group by deptno;

If you want to divide a group into groups, for example, each department group and each position group.

Select deptno, job, AVG (SAL) avg_sal, from EMP

Group by rollup (deptno, job );

 

Deptno job agv_sal

-------------------------------------------------------------------

10 clerk 4500

10 manager 8500

10 6500

20 clerk 5000

20 manager 9000

20 7000

6750

Among them, 6500 is the average salary of Department 10, 7000 is the average salary of Department 20, and 6750 is the average salary of two departments.

Note: If the query statement was last year, the colored numbers will not appear.

Select deptno, job, AVG (SAL) avg_sal, from EMP

Group by cube (deptno, job );

Deptno job agv_sal

-------------------------------------------------------------------

10 clerk 4500

10 manager 8500

10 6500

20 clerk 5000

20 manager 9000

20 7000

6750

Clerk 4750

Management 8750

 

If only the positions with an average salary greater than 5000 are displayed:

Select deptno, job, AVG (SAL) avg_sal from EMP

Group by deptno, job

Having AVG (SAL) & gt; 5000;

NOTE: If having is changed to where, an error occurs. The WHERE clause cannot contain grouping functions.