SQL语言基础（1）（转载《Oracle PL/SQL DBA编程入门》林树泽一书）-CSDN博客

1）在查询中使用 DISTINCT ，可使查询的结果没有重复内容

eg: SELECT DISTINCT job
FROM emp;

2）在查询中使用 || 连接符，在连接字符串时使用单引号''

eg:

SQL> SELECT ename || 'is a '||job ||' and 1month salary is :'|| sal AS
2 "The imployees's information " FROM emp;

The imployees's information
----------------------------------------------------------------------
SMITHis a CLERK and 1month salary is :800
ALLENis a SALESMAN and 1month salary is :1600
WARDis a SALESMAN and 1month salary is :1250
JONESis a MANAGER and 1month salary is :2975
MARTINis a SALESMAN and 1month salary is :1250
BLAKEis a MANAGER and 1month salary is :2850
CLARKis a MANAGER and 1month salary is :2450
SCOTTis a ANALYST and 1month salary is :3000
KINGis a PRESIDENT and 1month salary is :5000
TURNERis a SALESMAN and 1month salary is :1500
ADAMSis a CLERK and 1month salary is :1100

The imployees's information
----------------------------------------------------------------------
JAMESis a CLERK and 1month salary is :950
FORDis a ANALYST and 1month salary is :3000
MILLERis a CLERK and 1month salary is :1300

已选择14行。

3) 逻辑判断函数 DECODE ，语法为:DECODE(col | expression,search1,result1[,search2,result2,...] [,defalult])

eg:

SQL> SELECT ename,job ,sal,
2 DECODE (job,'SALESMAN' ,1.20*sal,
3 'MANAGER',1.30*sal,
4 'ANALYST',1.40*sal,
5 sal)
6 Last_Salary
7 FROM EMP
8 ORDER BY job;

ENAME      JOB              SAL LAST_SALARY
---------- --------- ---------- -----------
SCOTT      ANALYST         3000        4200
FORD       ANALYST         3000        4200
MILLER     CLERK           1300        1300
JAMES      CLERK            950         950
SMITH      CLERK            800         800
ADAMS      CLERK           1100        1100
BLAKE      MANAGER         2850        3705
JONES      MANAGER         2975      3867.5
CLARK      MANAGER         2450        3185
KING       PRESIDENT       5000        5000
TURNER     SALESMAN        1500        1800

ENAME      JOB              SAL LAST_SALARY
---------- --------- ---------- -----------
MARTIN     SALESMAN        1250        1500
WARD       SALESMAN        1250        1500
ALLEN      SALESMAN        1600        1920

已选择14行。

4) 分组函数对表中的多行数据进行操作，

4.1 AVG() SUM() 函数

eg:
SQL> SELECT avg(sal)"平均工资" ,SUM(sal)"总工资"
2 FROM emp;

平均工资总工资
---------- ----------
2073.21429 29025

4.2 MAX() MIN() 函数该对函数既可以操作数字型数据，也可以操作字符型数据和日期型数据

eg:

SQL> SELECT MAX(sal) "Highest salary ",MIN(sal)"Lowest salary"
2 FROM emp;

Highest salary Lowest salary
--------------- -------------
5000 800

eg:

SQL> SELECT MAX(HIREDATE)"Last day",MIN(HIREDATE) "First day"
2 FROM emp;

Last day First day
-------------- --------------
23-5月 -87 17-12月-80

4.3 count函数用于返回经过计算得到的行数，包括空行和重复的行

eg:

SQL> SELECT count(*) "表中的行数"
2 FROM emp;

表中的行数
----------
14

若是想得到表中的职位种类数，即去掉重复行
SQL> SELECT count(distinct job)
2 FROM emp;

COUNT(DISTINCTJOB)
------------------
5

以上说明emp表中共12行记录，5种工作职位

4.4 GROUP BY 字句用于按照具体职位分组

eg:

SQL> SELECT job,AVG(sal)"平均工资",SUM(sal)"总工资"
2 FROM emp
3 GROUP BY job;

JOB         平均工资     总工资
--------- ---------- ----------
CLERK         1037.5       4150
SALESMAN        1400       5600
PRESIDENT       5000       5000
MANAGER   2758.33333       8275
ANALYST         3000       6000