IT开放社区

SELECT查询语句别名、连接符与引用操作符

 别名:重命名一个列标题,便于计算、查看,紧跟列名用空格隔开(也可以使用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>

相关文章

发表评论:

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

网站分类
最新文章
随机文章
站点信息
  • 文章总数:113
  • 页面总数:2
  • 分类总数:3
  • 标签总数:0
  • 评论总数:0
  • 浏览总数:24663
左邻右舍

BlogPowerBy Z-BlogPHP 1.5 Zero ;Theme By 爱墙纸

IT开放社区:京ICP备13044647号.初创于Oracle DBA实战班.邮箱:admin@itkaifang.com

分享:

支付宝

微信