别名:重命名一个列标题,便于计算、查看,紧跟列名用空格隔开(也可以使用AS),如别名中包含空格或特殊字符及区分大小写,必须使用双引号。
SQL@itkaifang> SELECT empno a,ename AS b,sal "Cc",job AS "Dd",comm "!!" FROM emp; A B Cc Dd !! ---------- ---------- ---------- --------- ---------- 7369 SMITH 800 CLERK 7499 ALLEN 1600 SALESMAN 300 7521 WARD 1250 SALESMAN 500 7566 JONES 2975 MANAGER 7654 MARTIN 1250 SALESMAN 1400 7698 BLAKE 2850 MANAGER 7782 CLARK 2450 MANAGER 7788 SCOTT 3000 ANALYST 7839 KING 5000 PRESIDENT 7844 TURNER 1500 SALESMAN 0 7876 ADAMS 1100 CLERK 7900 JAMES 950 CLERK 7902 FORD 3000 ANALYST 7934 MILLER 1300 CLERK 14 rows selected. SQL@itkaifang>
以上为别名正确的命名方式,以下举例错误的命名方式。
SQL@itkaifang> SELECT empnoa FROM emp; SELECT empnoa FROM emp * ERROR at line 1: ORA-00904: "EMPNOA": invalid identifier SQL@itkaifang> SELECT ename Aa FROM emp; AA ---------- SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MILLER 14 rows selected. SQL@itkaifang> SELECT jobASc FROM emp; SELECT jobASc FROM emp * ERROR at line 1: ORA-00904: "JOBASC": invalid identifier SQL@itkaifang>
连接符
用concat或“||”表示,用于把列与列字符连接在一起,用来合成列。
SQL@itkaifang> SELECT CONCAT('name',ename) FROM emp; CONCAT('NAME', -------------- nameSMITH nameALLEN nameWARD nameJONES nameMARTIN nameBLAKE nameCLARK nameSCOTT nameKING nameTURNER nameADAMS nameJAMES nameFORD nameMILLER 14 rows selected. SQL@itkaifang> SELECT CONCAT('name',ename) AS name FROM emp; NAME -------------- nameSMITH nameALLEN nameWARD nameJONES nameMARTIN nameBLAKE nameCLARK nameSCOTT nameKING nameTURNER nameADAMS nameJAMES nameFORD nameMILLER 14 rows selected. SQL@itkaifang> SELECT CONCAT('name',ename)|| sal FROM emp; CONCAT('NAME',ENAME)||SAL ------------------------------------------------------ nameSMITH800 nameALLEN1600 nameWARD1250 nameJONES2975 nameMARTIN1250 nameBLAKE2850 nameCLARK2450 nameSCOTT3000 nameKING5000 nameTURNER1500 nameADAMS1100 nameJAMES950 nameFORD3000 nameMILLER1300 14 rows selected. SQL@itkaifang> SELECT CONCAT('name',ename)|| sal || comm FROM emp; CONCAT('NAME',ENAME)||SAL||COMM ---------------------------------------------------------------------------------------------- nameSMITH800 nameALLEN1600300 nameWARD1250500 nameJONES2975 nameMARTIN12501400 nameBLAKE2850 nameCLARK2450 nameSCOTT3000 nameKING5000 nameTURNER15000 nameADAMS1100 nameJAMES950 nameFORD3000 nameMILLER1300 14 rows selected. SQL@itkaifang>
引用操作符单引号‘
格式:q'['字符串]' 或 '''字符串‘
SQL@itkaifang> SELECT empno||q'['s]'||ename FROM emp; EMPNO||Q'['S]'||ENAME ---------------------------------------------------- 7369'sSMITH 7499'sALLEN 7521'sWARD 7566'sJONES 7654'sMARTIN 7698'sBLAKE 7782'sCLARK 7788'sSCOTT 7839'sKING 7844'sTURNER 7876'sADAMS 7900'sJAMES 7902'sFORD 7934'sMILLER 14 rows selected. SQL@itkaifang> SELECT empno||'''s'||ename FROM emp; EMPNO||'''S'||ENAME ---------------------------------------------------- 7369'sSMITH 7499'sALLEN 7521'sWARD 7566'sJONES 7654'sMARTIN 7698'sBLAKE 7782'sCLARK 7788'sSCOTT 7839'sKING 7844'sTURNER 7876'sADAMS 7900'sJAMES 7902'sFORD 7934'sMILLER 14 rows selected. SQL@itkaifang>