sql常用操作

1、新增字段

ALTER TABLE SubmitReply   --表名 
ADD
GG3   int      ---新增的字段名和类型 
not null  default  0      --默认值   可选参数，无此参数的话就缺省情况默认NULL 

2、两个表关联更新

UPDATE B

SET B.loginname=c.loginname
FROM t_employee A, t_systemuser B, oa_employee c
WHERE  A.employeeid=B.belonguserid AND A.oaemployeeid = c.id

　　

 

 

3、非唯一索引创建和删除   

 

--创建
create index UQ_report_paylog_carNo on report_paylog(carNo)

create index UQ_report_checkout_carNo on report_checkout(carNo)


create index UQ_report_present_carNo on report_present(carNo)
create index UQ_report_present_userType on report_present(userType)


--删除

drop index UQ_report_paylog_carNo on report_paylog

drop index UQ_report_checkout_carNo on report_checkout

drop index UQ_report_present_carNo on report_present

drop index UQ_report_present_userType on report_present

　　

---    cross join用法

INSERT INTO [lpn_parkV5].[dbo].[user_permission_detail]
([userId]
,[carNo]
,[channelId]
,[opType]
,[operateTime]
,[downloadFlage]
,[endTime]
,[userType])
select c.userid,c.carno,pc.channelId,0,getdate(),0,u.endtime,u.usertype from user_info u inner join user_car c on u.userid=c.userid cross join park_channel pc where u.usertype='FixNoLimit'

 

　　

--  sqlserver 中GO 的用法

go代表事务， 用GO分开的 脚本互不影响

--一次性给数据库所有表增加相同字段

select ' alter table '+name+' add [date] datetime ' from sys.tables where type ='U'

--mysql   查询所有表名 并进行相关操作

select CONCAT('delete ',  table_name,' where parkcode=506')  from information_schema.tables where table_schema='ee_main'

--查询所有字段

select name from syscolumns where id=(select max(id) from sysobjects where xtype='u' and name='表名') --读取指定表的所有列名

　　

--sqlserver 查询所有表和字段的关系

 SELECT
c.[name] AS 字段名,
 (select  top 1 name   from  sys.sysTypes tp where c.[system_type_id]=tp.[xtype])+'('+CAST(c.[max_length]  as nvarchar(200))+')' as  类型,
   

t.[name] AS 表名,
cast(ep.[value] as nvarchar(200)) AS [字段说明] 
FROM sys.tables AS t 
INNER JOIN sys.columns 
AS c ON t.object_id = c.object_id 
LEFT JOIN sys.extended_properties AS ep 
ON ep.major_id = c.object_id AND ep.minor_id = c.column_id  where  t.type='u'

--mysql  查询所有表和字段的关系

select COLUMN_NAME,COLUMN_TYPE,'orders_success',column_comment from INFORMATION_SCHEMA.Columns where  table_schema='lpnservice'

    and   table_name='orders_success'