mysql 连接符sql_SQL连接符与使用字符串

SQL连接符

·把列与列，列与字符连接在一起。

·用“||” 表示。

·可以用来“合成”列。

VAST@orcl> select last_name||job_id as "Employees" from employees;

Employees

-----------------------------------

OConnellSH_CLERK

GrantSH_CLERK

WhalenAD_ASST

HartsteinMK_MAN

FayMK_REP

SQL使用字符串

·字符串可以是select列表中的一个字符，数字，日期。

·日期和字符只能在单引号中出现。

·每当返回一行时，字符串被输出一次。

VAST@orcl> select last_name||' is '||job_id as "Employees" from employees;

Employees

---------------------------------------

OConnell is SH_CLERK

Grant is SH_CLERK

Whalen is AD_ASST

Hartstein is MK_MAN

Fay is MK_REP

根据连接符和字符串，我们可以做一些脚本的生成，简化工作。

例1：生成创建表

VAST@orcl> select 'create table t'||level||' as select * from t1;' from dual connect by level between 2 and 5;

'CREATETABLET'||LEVEL||'ASSELECT*FROMT1;'

---------------------------------------------------------------------------

create table t1 as select * from t1;

create table t2 as select * from t1;

create table t3 as select * from t1;

create table t4 as select * from t1;

create table t5 as select * from t1;

例2：在当前用户下创建hr所包含的所有表

VAST@orcl> select 'create table '|| table_name||' as select * from hr.'||table_name||';'from dba_tables where owner='HR';

'CREATETABLE'||TABLE_NAME||'ASSELECT*FROMHR.'||TABLE_NAME||';'

-----------------------------------------------------------------------------------------------

create table REGIONS as select * from hr.REGIONS;

create table COUNTRIES as select * from hr.COUNTRIES;

create table EMPLOYEES as select * from hr.EMPLOYEES;

create table LOCATIONS as select * from hr.LOCATIONS;

create table JOBS as select * from hr.JOBS;

create table DEPARTMENTS as select * from hr.DEPARTMENTS;

create table JOB_HISTORY as select * from hr.JOB_HISTORY;

例3：生成删除当前用户所有表

VAST@orcl> select 'drop table '||tname||' purge;' from tab;

'DROPTABLE'||TNAME||'PURGE;'

------------------------------------------------

drop table T1 purge;

drop table T2 purge;

drop table T3 purge;

drop table T4 purge;

drop table T5 purge;