独角兽企业重金招聘Python工程师标准>>>
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#创建UserCREATE USER CH1IDENTIFIED BY 'pass'DEFAULT TABLESPACE TBS_01TEMPORARY TABLESPACE TEMPPROFILE DEFAULTACCOUNT UNLOCK;Error at line 1ORA-00988: missing or invalid password(s)CREATE USER CH1IDENTIFIED BY "pass"DEFAULT TABLESPACE TBS_01TEMPORARY TABLESPACE TEMPPROFILE DEFAULTACCOUNT UNLOCK;#创建DB_LinkCREATE DATABASE LINK "ORCL_HR."CONNECT TO HRIDENTIFIED 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 identifierSQL> select first_name from emp where "EMPLOYEE_ID" = 200;FIRST_NAME----------------------------------------JenniferSQL> select first_name as "name" from emp where employee_id = 200;name----------------------------------------JenniferSQL> 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----------------------------------------JenniferSQL> select first_name as "name" from emp where employee_id = '200'; --字符name----------------------------------------JenniferSQL> 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'COSTASQL> SELECT q'$D'COSTA$' NAME FROM DUAL;NAME-------D'COSTASQL> SELECT q'[D'COSTA]' NAME FROM DUAL;NAME-------D'COSTASQL> 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 nameSQL> create table test("a" varchar2(10), a varchar2(10));Table created.SQL> desc test;Name Null? Type----------------------------------------- -------- ----------------------------aVARCHAR2(10)AVARCHAR2(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
