處理資料庫的常用SQL（SQL SERVER)

標籤：

常用SQL
-- 查看錶記錄數
SELECT a.id, b.[name], a.rowcnt FROM sysindexes a, sys.tables b WHERE a.id = b.[object_id] AND a.indid <=1 ORDER BY b.[name]

-- 查看檔案組
SELECT name, data_space_id 檔案組id, size/128 [檔案大小(兆)],
FILEPROPERTY(name, ‘SpaceUsed‘)/128 [已用空間(兆)],
size/128 - FILEPROPERTY(name, ‘SpaceUsed‘)/128 [未用空間(兆)],
FILEPROPERTY(name, ‘SpaceUsed‘)*100.0/size [使用率(%)],
max_size/128 [最大值(兆)],
case is_percent_growth when 0 then cast(growth/128 as nvarchar) + ‘兆‘ else cast(growth as nvarchar) + ‘%‘ end 增長值,
physical_name 實體路徑
FROM sys.database_files a ORDER BY a.[name]

--建立檔案組

ALTER DATABASE ComputingDB_EN ADD FILEGROUP [FG_DailyUsers_2017];ALTER DATABASE ComputingDB_EN ADD FILE (NAME=N‘FG_DailyUsers_2017‘,FILENAME=N‘......ndf‘,SIZE=10MB,MAXSIZE=UNLIMITED,FILEGROWTH=10MB) TO FILEGROUP [FG_DailyUsers_2017];

-- 查看資料庫大小
SELECT sum(size/128) [檔案(兆)] FROM sys.database_files

分區操作
-- 分區記錄數
select partition_number,rows from sys.partitions where object_id=object_id(‘xxxx‘) and index_id in (0,1) order by partition_number;

-- 切換分區
alter table PczsFunctionLog switch partition 19 to tmp_PczsFunctionLog partition 19;

-- 追加分區，只能一個一個加
ALTER PARTITION SCHEME [DATE_PS] NEXT USED [DATE_10];
ALTER PARTITION FUNCTION DATE_PF() SPLIT RANGE (20141001);

-- 合并分區
ALTER PARTITION FUNCTION DATE_PF() MERGE RANGE (20141001);

檔案組操作
-- 組建檔案組建立指令碼
select name,‘ALTER DATABASE ‘+DB_NAME()+‘ ADD FILEGROUP [‘+name+‘];ALTER DATABASE ‘+DB_NAME()+‘ ADD FILE (NAME=N‘‘‘+name+‘‘‘,FILENAME=N‘‘‘+physical_name+‘‘‘,SIZE=10MB,MAXSIZE=UNLIMITED,FILEGROWTH=10MB) TO FILEGROUP [‘+name+‘];‘
from sys.database_files a
where data_space_id>1
order by a.name

-- 刪除檔案組
drop table dbo.MobileUseLongLog_201305;
drop partition scheme day_ps_201305;
dbcc shrinkfile (‘data_201305‘,emptyfile);
alter database softuselogdb remove file data_201305;
alter database softuselogdb remove filegroup data_201305;

-- 添加檔案組
alter database ghw add filegroup [DATA_201207];
alter database ghw add file (name=‘DATA_201207‘,
filename=‘e:\data\ghw\DATA_201207.ndf‘,
size=10mb,
maxsize=unlimited,
filegrowth=10mb)
to filegroup DATA_201207

MySQL相關
-- 查看記錄數
select table_name,table_rows from information_schema.tables where table_schema=‘StDB‘

-- 查看分區數
select partition_ordinal_position part,table_rows
from information_schema.partitions
where table_schema = schema() and table_name=‘func_201506‘;

-- 表結構複製
create table if not exists functionlog_201508 like func_201506

-- 批次更新
update a inner join b on a.id=b.id set a.name=b.name

-- 唯一插入
insert into a select ?name from dual where not exists(select 1 from a where id=?id);

select last_insert_id();

-- 先更新後插入
update Cfg_PczsVID set name=?name where VID=?vid;
insert into Cfg_PczsVID(vid,name)
select ?vid,?name from dual where ROW_COUNT()=0;

-- 查看進程
show processlist

-- kill查詢，參考
KILL [CONNECTION | QUERY] thread_id

-- 顯示資料庫中所有儲存的預存程序基本資料，包括所屬資料庫，預存程序名稱，建立時間等
show procedure status

-- 顯示某一個預存程序的詳細資料
show create procedure sp_name

處理資料庫的常用SQL（SQL SERVER)