SQL使用总结

一、常用SQL Server

1.添加列
ALTER TABLE [dbo].[RO_RoRectificationNotice] 
ADD SerialNum varchar(20) NULL;

2.修改列的数据类型
ALTER TABLE [dbo].[RO_RoRectificationNotice] 
ALTER COLUMN InspectPerson varchar(40) NOT NULL;

3.创建表 
CREATE TABLE VE_VehicleDgTransRoute(
    VehicleDgTransRouteId    varchar(32)      NOT NULL,
    ProvinceArea             varchar(30)      NULL, 
    CONSTRAINT PK_VehicleDgTransRoute PRIMARY KEY NONCLUSTERED (VehicleDgTransRouteId) ON [VEHICLE_IDX]
)ON [VEHICLE_DATA]
go

4.添加数据
（1）INSERT INTO TableName (Column1,Column2,Column3,Column4) VALUES ('1','1','',1);
（2）INSERT INTO "表格1" ("栏位1", "栏位2", ...) SELECT "栏位3", "栏位4", ... FROM "表格2";

5.删除数据
delete from TableName where PermissionId = '001' 

6.修改数据

1）多表连接更新:
（1）update a set a.Telephone=b.Telephone from PSP_User a,RO_RoadOwner b
where a.ObjectId=b.OwnerId
（2）update PR_TAXIDRIVEREVALUATIONINFO A set A.LASTFEEDBACKTIME = (select max(FeedBackTime) from PR_TaxiDriverEvalProcess B where A.INFOID = B.InfoId)
2）根据身份证修改出生日期 （oracle）:
update PR_PRACREGISTERAPPLY set BIRTHDAY = to_date(CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(SUBSTR(IdcertNum, 7, 4), '-'), SUBSTR(IdcertNum, 11, 2)),'-'),SUBSTR(IdcertNum, 13, 2)),' 00:00:00'), 'yyyy-mm-dd hh24:mi:ss') ;

6.判断是否含有中文字符
（1）语法：select  case when LEN(字段)<>DATALENGTH(字段) then 0 else 字段 end FROM 表
（2）实例：
select * from PSP_User where UserType = 1 and LoginName like 'W%' and LEN(LoginName)<>DATALENGTH(LoginName)
select case when LEN(LoginName)<>DATALENGTH(LoginName) then 0 else 1 end from PSP_User;

二、常用Oracle SQL

1.添加字段
ALTER TABLE test1
ADD (name varchar2(30) default ‘无名氏’ not null,
age integer default 22 not null,
has_money number(9,2)
);

2.修改字段
ALTER TABLE PR_PracForeignRegister 
MODIFY (OwnerLicenseNum VARCHAR2(20) NULL);
or
Add/modify columns 
ALTER TABLE TABLE_NAME rename column FIELD_NAME to NEW_FIELD_NAME;

3.删除字段
alter table test1
drop column name;

4.创建表 
CREATE TABLE TableName(
    DelayId         VARCHAR2(32)     NOT NULL,
    ApplyToDate     DATE             NOT NULL,
    ApplyReason     VARCHAR2(500)    NOT NULL, 
    CONSTRAINT PK_TaxiDriverEvalApplyDelay PRIMARY KEY (DelayId)
    USING INDEX
TABLESPACE PRACAPPLY_IDX, 
    CONSTRAINT RefPR_TaxiDriverEvaluationI101 FOREIGN KEY (InfoId)
    REFERENCES PR_TaxiDriverEvaluationInfo(InfoId)
)
TABLESPACE PRACAPPLY_DATA
;

5.添加数据
INSERT INTO SP_PrintTemplate values('PracRegisterLabel','从业人员备案标签','','','Practitioner',1,1,'',1,'PracRegisterLabel','1');
COMMIT;

6.删除数据 
DELETE FROM SP_PrintTemplate where 

COMMIT;

7.修改数据

1）多表连接更新：
（1）update CH_CHARTERAPPLY a set a.startname = (select b.STARTPOINT from CH_CHARTEROWNER b where a.OwnerId = b.OwnerId ) ,
a.StartCode =  (select b.AreaCode from CH_CHARTEROWNER b where a.OwnerId = b.OwnerId ) 
where  a.startname = '省' ;
（2）update customers a -- 使用别名
set city_name=nvl((select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id),a.city_name)

8.修改表名
ALTER TABLE table_name RENAME TO new_table_name;

9.修改列的名称

ALTER TABLE table_name RENAME COLUMN supplier_name to sname;