select 'truncate table ' + Name + ';' from sysobjects where xtype='U' order by name asc; -- 查询出指定库的 所有表，并且进行 truncate table 操作
select 'select count(*) as ['+Name+'] from ' + Name + ';' from sysobjects where xtype='U' order by name asc;
insert into ynculture.dbo.PLUGINS_DISTRICT(ID, CODE, TITLE,EN_TITLE,CITY_CODE,PARENT_ID,CLASS_LAYER,CLASS_LIST,IMG_URL,PINYIN,SORT,IS_DELETE,ADD_TIME,UPDATE_TIME) select ID, CITY_ID, TITLE,EN_TITLE,CODE,PARENT_ID,CLASS_LAYER,CLASS_LIST,IMG_URL,CITY_PINYIN,SORT,IS_DELETE,GETDATE(),GETDATE() from hyxs.dbo.PLUGINS_DISTRICT; -- 将库2 的表2 的内容插入 库1 的表1 中
insert into dashihui.dbo.PLUGINS_DISTRICT(ID, TITLE,EN_TITLE,CITY_TYPE,CITY_CODE,TEL_CODE,PARENT_CODE,PARENT_ID,ROOT_ID,CLASS_LAYER,CLASS_LIST,IMG_URL,PINYIN,SORT,IS_DELETE,ADD_TIME,UPDATE_TIME,CENTER_LON,CENTER_LAT) select ID, TITLE,EN_TITLE,CITY_TYPE,CITY_CODE,TEL_CODE,PARENT_CODE,PARENT_ID,ROOT_ID,CLASS_LAYER,CLASS_LIST,IMG_URL,PINYIN,SORT,IS_DELETE,GETDATE(),GETDATE(),CENTER_LON,CENTER_LAT from meigongheTravel.dbo.PLUGINS_DISTRICT;
select * from USERS where USER_NAME in (select USER_NAME from USERS where IS_DELETE=0 group by USER_NAME having COUNT(*)>1) -- 查询某一列值重复 的记录
update dbo.PRODUCT_IMG_LIST set PATH = replace(PATH,'http://www.baidu.com',''); --批量替换某字段中某值
SELECT * FROM [Master].[dbo].[SYSPROCESSES] WHERE [DBID] IN ( SELECT [DBID] FROM [Master].[dbo].[SYSDATABASES] WHERE NAME='库名称')
insert into system_area(`code`,`name`,`level`,parent_code,layer,layer_list,children_count,creator,create_time,updater,update_time,deleted) select `code`,`name`,`level`,parent_code,layer,layer_list,children_count,creator,create_time,updater,update_time,deleted from system_area_2
update system_area as c set c.children_count = ifnull((
select e.count from (
select parent_id, count(*) as count from system_area as d GROUP BY d.parent_id
) as e where e.parent_id = c.id
) ,0)
UPDATE system_area AS a JOIN system_area AS b ON a.parent_id = b.id
SET a.layer_list = CONCAT(b.layer_list, a.id, ',')
WHERE a.`level` > 1;
ALTER SEQUENCE sequence_name RESTART WITH new_value;
SELECT currval('sequence_name');
SELECT nextval('sequence_name');
SELECT currval('system_menu_seq') INTO parentId;
