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