The basic statement of sql-the query statement select of sql
Introduction to SQL query statement-- select statement
1. Simple select query statement
1.1 Bank check
SQL > select * from emp
1.2 lookup column
SQL > select empno,ename from emp
1.3 Associated query
The syntax of oracle:
Select a. From emp. * dept b where a.deptno=b.deptno.
General syntax:
Select a. * from emp a join dept b on (a.deptno = b.deptno)
1.4 sort
a. Sort in ascending order, asc can be omitted
Select A. order by a.empno. * from emp a join dept b on (a.deptno = b.deptno) order by a.empno [asc]
b. Descending sort, desc
Select a. * from emp a join dept b on (a.deptno = b.deptno) order by a.empno desc
c. Mixed sorting
Select a. * from emp a join dept b on (a.deptno = b.deptno) order by b.deptno desc,a.empno asc
On the basis of the sorting of the first field, there are groups, and within the group, the second field is used for sorting.
Use the eighth field and the first field shown in the select list for mixed sorting
Select a.maiden b. * from emp a join dept b on (a.deptno = b.deptno) order by 8 desc,1 asc
Note:
Sorted fields do not have to appear in the select list:
Select a.ename.b.deptno from emp a join dept b on (a.deptno = b.deptno) order by empno desc
Sort by the alias of a field or expression
Select a.enamemema. Mgremere a.empno, (a.sal*12) as annsal from emp an order by annsal
1.5 grouping
Select b. Deptno. B. dame. sum (a.sal)
From emp a,dept b
Where a.deptno=b.deptno
Group by b.deptno,b.dname
Order by b.deptno desc
Field after group by = all display fields in the select list except the aggregate function
Calculate the total wage, the number of people, the maximum wage, the minimum wage and the average wage of each part
Select B. deptno, b. Dname. sum (a.sal), count (a.empno), max (a.sal), min (a.sal), round (avg (a.sal))
From emp a,dept b
Where a.deptno=b.deptno
Group by b.deptno,b.dname
Order by b.deptno desc
Note:
(1) Field after group by = all display fields in the select list except the aggregate function
(2) order by will appear behind group by.
(3) sum (a.sal), count (a.empno), max (a.sal), min (a.sal), round (avg (a.sal)) are all called aggregate functions.
(4) the aggregate function must be involved in the grouping operation.
