Shulou(Shulou.com)06/01 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.