Oracle SQL 单引号与双引号区别

原创
2015/09/28 16:46
阅读数 870

1.3 SQL 单引号与双引号

单引号:

  • 表示字符串常量,并不区分大小写。。
  • 字符串中的双引号仅仅被当作一个普通字符进行处理。此时,双引号不需要成对出现。
  • 转义符,对紧随其后出现的字符(单引号)进行转义 ' '' ' ----> '
  • 用于,字符串数据的操作,如创建表,查询,删除,添加,数据等
SQL> select 'hh24"小时""mi"分"""ss"秒"' AS RESULT from dual;

RESULT
-----------------------------------------
hh24"小时""mi"分"""ss"秒"

SQL> select '' from dual;

'
-

SQL> select ''' from dual;
ERROR:
ORA-01756: quoted string not properly terminated


#转移字符单引号
SQL> select '''' from dual;

'
-
'

SQL> select ' '' ' from dual;

'''
---
 '

SQL> select ' '''' ' from dual;

''''
----
 ''


双引号:

  • 假如建立对象的时候,对象名、字段名加双引号,则示意 Oracle将严格区分大小写,否则Oracl都默认大写。
  • 用于表名,字段名, 别名
  • 出现在to_char的格式字符串中时,双引号有特殊的作用,就是将非法的格式符包装起来
  • 用于创建用户时的密码,DB_link, 多用于数据库的操作
SQL> select to_char(sysdate, 'hh24"H"mi"M"ss"S"') AS RESULT from dual;

RESULT
---------
07H08M37S

#创建User
CREATE USER CH1
  IDENTIFIED BY 'pass'
  DEFAULT TABLESPACE TBS_01
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
  
Error at line 1
ORA-00988: missing or invalid password(s)

CREATE USER CH1
  IDENTIFIED BY "pass"
  DEFAULT TABLESPACE TBS_01
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
  
#创建DB_Link
CREATE DATABASE LINK "ORCL_HR.QA.WEBEX.COM"
 CONNECT TO HR
 IDENTIFIED BY "pass"
 USING 'tony1521';
 
 SQL> select first_name from emp where "employee_id" = 200;
select first_name from emp where "employee_id" = 200
                                 *
ERROR at line 1:
ORA-00904: "employee_id": invalid identifier


SQL> select first_name from emp where "EMPLOYEE_ID" = 200;

FIRST_NAME
----------------------------------------
Jennifer

SQL> select first_name as "name" from emp where employee_id = 200;

name
----------------------------------------
Jennifer

SQL> select first_name  'name' from emp where employee_id = 200;
select first_name  'name' from emp where employee_id = 200
                   *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected


注意

SQL> select first_name as "name" from emp where employee_id = 200;  --数字

name
----------------------------------------
Jennifer

SQL> select first_name as "name" from emp where employee_id = '200'; --字符

name
----------------------------------------
Jennifer

SQL> select first_name as "name" from emp where employee_id = "200"; 
select first_name as "name" from emp where employee_id = "200"
                                                         *
ERROR at line 1:
ORA-00904: "200": invalid identifier

#在oracle10g中可以运用其它分隔符($,<>,[],{}等),留心,运用这些分隔符时,不仅要在分隔符前后加单引号,还要带有前缀q.
SQL> SELECT 'D''COSTA' name FROM DUAL;

NAME
-------
D'COSTA

SQL> SELECT q'$D'COSTA$' NAME FROM DUAL;

NAME
-------
D'COSTA

SQL> SELECT q'[D'COSTA]' NAME FROM DUAL;

NAME
-------
D'COSTA

SQL> SELECT q'<D'COSTA>' NAME FROM DUAL;

NAME
-------
D'COSTA

'
#假如建立对象的时候,对象名、字段名加双引号,则示意 Oracle将严格区分大小写,否则Oracl都默认大写

SQL> create table test(a varchar2(10), a varchar2(10));
create table test(a varchar2(10), a varchar2(10))
                                  *
ERROR at line 1:
ORA-00957: duplicate column name

SQL> create table test("a" varchar2(10), a varchar2(10));

Table created.

SQL> desc test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 a                                                  VARCHAR2(10)
 A                                                  VARCHAR2(10)

SQL> create table test_1(a varchar2(1), "A" varchar2(1));
create table test_1(a varchar2(1), "A" varchar2(1))
                                   *
ERROR at line 1:
ORA-00957: duplicate column name


展开阅读全文
加载中

作者的其它热门文章

打赏
0
5 收藏
分享
打赏
0 评论
5 收藏
0
分享
返回顶部
顶部