SQL BASE Statement-sql query statement Select

SQL query statement describes the--select statement

1. Simple SELECT query statement

1.1 Check.

Sql> select * from EMP;

1.2 Check List

Sql> select Empno,ename from emp;

1.3 Related queries

Syntax for Oracle:

Select a.*,b.* from emp a,dept b where a.deptno=b.deptno;

General Syntax:

Select a.*,b.* from emp a joins Dept b on (a.deptno = B.deptno);

1.4 Sort

A. Ascending sort, ASC can omit

Select a.*,b.* from emp a joins Dept b on (a.deptno = B.deptno) Order BY A.empno [ASC];

B. Descending sort, desc

Select a.*,b.* from emp a joins Dept b on (a.deptno = B.deptno) Order BY a.empno Desc;

C. Mixed sorting

Select a.*,b.* from emp a joins Dept b on (a.deptno = B.deptno) Order by B.deptno Desc,a.empno ASC;
On the basis of the first field sort, there is a grouping, within the group, using the second field to sort

Use the eighth field and the first field shown in the select list for a mixed sort
Select a.*,b.* from emp a joins Dept b on (a.deptno = B.deptno) Order by 8 desc,1 ASC;

Attention:
The sorted fields do not necessarily appear in the select list:
Select A.ename,b.deptno from emp a joins Dept b on (a.deptno = B.deptno) Order BY empno Desc;

To sort by an alias of a field or an expression
Select A.ename,a.mgr,a.empno, (a.sal*12) as annsal from EMP a order by annsal;

1.5 Grouping

Select B.deptno,b.dname,sum (a.sal)
From EMP a,dept b
where A.deptno=b.deptno
GROUP BY B.deptno,b.dname
ORDER BY B.deptno Desc

Fields after GROUP by = ALL display fields except aggregate functions in the select list


The total wage, the number of people, the maximum wage, the minimum wage, 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

Attention:
(1) The field after GROUP by = ALL display fields except the aggregate function in the select list
(2) Order by to appear after group by
(3) sum (a.sal), Count (A.empno), Max (a.sal), Min (a.sal), round (avg (a.sal)) are called aggregate functions
(4) The grouping operation must have the participation of the aggregation function

SQL BASE Statement-sql query statement Select