[SQL]小練習
匯整一下之前找點部落的資料
使用資料庫環境: Sql Server 2008
測試表格: EMPLOYEE
定義如下
[STATUS] [nvarchar](1) COLLATE Chinese_Taiwan_Stroke_BIN NULL,
[STAFF] [nvarchar](6) COLLATE Chinese_Taiwan_Stroke_BIN NOT NULL,
[NAME] [nvarchar](30) COLLATE Chinese_Taiwan_Stroke_BIN NULL,
[BRNO] [nvarchar](4) COLLATE Chinese_Taiwan_Stroke_BIN NULL,
[ID] [nvarchar](18) COLLATE Chinese_Taiwan_Stroke_BIN NULL,
[CHECKNO] [nvarchar](14) COLLATE Chinese_Taiwan_Stroke_BIN NULL,
[BIRTHDA] [nvarchar](7) COLLATE Chinese_Taiwan_Stroke_BIN NULL,
[ENTERDA] [nvarchar](7) COLLATE Chinese_Taiwan_Stroke_BIN NULL,
[ASSIGNDA] [nvarchar](7) COLLATE Chinese_Taiwan_Stroke_BIN NULL,
[POST] [nvarchar](6) COLLATE Chinese_Taiwan_Stroke_BIN NULL,
[BASPAY] [nvarchar](3) COLLATE Chinese_Taiwan_Stroke_BIN NULL,
[POINT] [nvarchar](5) COLLATE Chinese_Taiwan_Stroke_BIN NULL,
[JOBPAY] [float] NULL,
[HEXFEE] [float] NULL,
[LINSAMT] [nvarchar](5) COLLATE Chinese_Taiwan_Stroke_BIN NULL,
[HINSAMT] [nvarchar](6) COLLATE Chinese_Taiwan_Stroke_BIN NULL,
[RAISEN] [float] NULL,
[FAMYNO] [float] NULL,
[SALDEB] [float] NULL,
[SPERAMT] [float] NULL,
[CRINSFLG] [nvarchar](1) COLLATE Chinese_Taiwan_Stroke_BIN NULL,
[EXPPAY] [float] NULL,
[SEPARAT] [nvarchar](2) COLLATE Chinese_Taiwan_Stroke_BIN NULL,
[SEPARDA] [nvarchar](7) COLLATE Chinese_Taiwan_Stroke_BIN NULL,
[DISCOUNT] [nvarchar](1) COLLATE Chinese_Taiwan_Stroke_BIN NULL,
[ARRIVEDA] [nvarchar](7) COLLATE Chinese_Taiwan_Stroke_BIN NULL,
[RESTDATE] [nvarchar](7) COLLATE Chinese_Taiwan_Stroke_BIN NULL,
[RESTDAY] [nvarchar](2) COLLATE Chinese_Taiwan_Stroke_BIN NULL,
[JOBCODE] [nvarchar](6) COLLATE Chinese_Taiwan_Stroke_BIN NULL,
[DEPTID] [nvarchar](6) COLLATE Chinese_Taiwan_Stroke_BIN NULL,
[EMP_TYPE] [nvarchar](2) COLLATE Chinese_Taiwan_Stroke_BIN NULL,
[GRADE] [nvarchar](2) COLLATE Chinese_Taiwan_Stroke_BIN NULL,
[MAIL] [nvarchar](50) COLLATE Chinese_Taiwan_Stroke_BIN NULL,
[STATUS_CODE] [nvarchar](1) COLLATE Chinese_Taiwan_Stroke_BIN NULL
) ON [PRIMARY]測試的sql如下
/*第一筆到第10筆記錄*/
Select Top 10 * From EMPLOYEE order by STAFF
/*顯示查詢分頁結果,一頁五筆從第一頁開始,主鍵值為STAFF*/
declare @pagesize int ,@cureentpage int , @Pkkey varchar(60)
Set @pagesize=5
Set @cureentpage=3
select * from ( select TOP (@pagesize) * FROM ( SELECT TOP (@pagesize*@cureentpage) * from EMPLOYEE ORDER BY STAFF ASC ) as aSysTable ORDER BY STAFF DESC ) as bSysTable ORDER BY STAFF ASC
/*倒數第十筆到最後一筆*/
Select Top 10 * From EMPLOYEE order by STAFF desc
/*取得EMPLOYEE資料總筆數*/
SELECT top 1 sys.partitions.rows from sys.partitions where object_id = OBJECT_ID('EMPLOYEE')
/*按照員工編號從頭排到尾*/
SELECT ROW_NUMBER() OVER ( ORDER BY STAFF ASC ) AS 依照員工編號排序 , STAFF as 員工編號 FROM EMPLOYEE
/*依照分行代號群組後排序*/
SELECT ROW_NUMBER() OVER ( PARTITION BY BRNO ORDER BY STAFF ASC ) AS 依照分行代號群組後排序,
STAFF as 員工編號 , BRNO as 分行代號 from EMPLOYEE
/*依據相同的值給予同一編號，但是下次給號”會跳號”（常用於排名次）*/
SELECT RANK() OVER(ORDER BY BIRTHDA ASC) AS 跳號 , BIRTHDA as 員工生日 from EMPLOYEE
/*依據相同的值給予同一編號，但是下次給號”不會跳號”（常用於排名次）*/
SELECT DENSE_RANK() OVER(ORDER BY BIRTHDA ASC) AS 不會跳號 , BIRTHDA as 員工生日 from EMPLOYEE
/*將查詢結果分成10群*/
/*依據給予的引數，給予資料列散發到指定數目的群組中，對於每個資料列，NTILE 都會傳回資料列所屬群組的號碼*/
SELECT NTILE(10) OVER(ORDER BY STAFF ASC) AS 每三列編一號 , STAFF as 員工編號 FROM EMPLOYEE
/*按姓氏筆劃排序EMPLOYEE資料*/
select * from EMPLOYEE order by NAME Collate Chinese_Taiwan_Stroke_BIN
/*查看磁碟可用空間*/
EXEC master..xp_fixeddrives
/*比較EMPLOYEE,EMPLOYEE2是否相等*/
if (select checksum_agg(binary_checksum(*)) from EMPLOYEE)
=
(select checksum_agg(binary_checksum(*)) from EMPLOYEE2)
print '相等'
else
print '不相等'
/*修改資料庫的名稱F711_20101113為F711_20101112*/
sp_renamedb 'F711_20101113', 'F711_20101112'
/*獲取當前資料庫中的所有表格*/
select name from sysobjects where xtype='U' and status>=0 order by name
/*取得表格EMPLOYEE所有欄位名稱*/
select name from syscolumns where id=object_id('EMPLOYEE')
/*建立視圖(View)*/
CREATE VIEW V_Employee
AS SELECT NAME
FROM EMPLOYEE
/*刪除視圖(View)*/
DROP VIEW V_Employee
/*查看與EMPLOYEE表格相關的視圖、存儲過程、函數*/
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%EMPLOYEE%'
/*查看當前資料庫中所有存儲過程(Store Procedure)*/
select name as 存儲過程名稱 from sysobjects where xtype='P'
/*查詢登入連線帳號建立的所有資料庫*/
/*第一種方式*/
select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')
/*第二種方式*/
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01
/**************************************************************************************************************/
/*查詢某一個表的欄位和資料類型*/
/*先建立一個視圖View*/
Create view fielddesc
as
SELECT TOP (100) PERCENT a.TABLE_CATALOG, a.TABLE_NAME, p.PK, a.COLUMN_NAME, a.DATA_TYPE, a.CHARACTER_MAXIMUM_LENGTH,a.IS_NULLABLE, a.COLUMN_DEFAULT, c_1.value AS MS_Description, Y.value AS Table_Description
FROM INFORMATION_SCHEMA.COLUMNS AS a LEFT OUTER JOIN
(SELECT o.name AS TableName, c.name AS ColumnName, ep.value
FROM sys.syscolumns AS c RIGHT OUTER JOIN sys.objects AS o
INNER JOIN sys.extended_properties AS ep ON o.object_id = ep.major_id
ON c.colid = ep.minor_id AND c.id = ep.major_id
WHERE ep.name = N'MS_Description') AS c_1 ON a.COLUMN_NAME = c_1.ColumnName
AND a.TABLE_NAME = c_1.TableName
LEFT OUTER JOIN
(SELECT o.name AS TableName, c.name AS ColumnName, ep.value
FROM sys.syscolumns AS c RIGHT OUTER JOIN sys.objects AS o INNER JOIN
sys.extended_properties AS ep ON o.object_id = ep.major_id ON c.colid = ep.minor_id
AND c.id = ep.major_id WHERE (ep.name = N'reference')) AS w
ON a.COLUMN_NAME = w.ColumnName AND a.TABLE_NAME = w.TableName
LEFT OUTER JOIN
(SELECT o.name AS TableName, c.name AS ColumnName, ep.value
FROM sys.syscolumns AS c RIGHT OUTER JOIN sys.objects AS o INNER JOIN
sys.extended_properties AS ep ON o.object_id = ep.major_id ON c.colid = ep.minor_id
AND c.id = ep.major_id
WHERE (ep.name = N'memo')) AS X ON a.COLUMN_NAME = X.ColumnName
AND a.TABLE_NAME = X.TableName
LEFT OUTER JOIN
(SELECT o.name AS TableName, c.name AS ColumnName, ep.value
FROM sys.syscolumns AS c RIGHT OUTER JOIN sys.objects AS o INNER JOIN
sys.extended_properties AS ep ON o.object_id = ep.major_id ON c.colid = ep.minor_id
AND c.id = ep.major_id
WHERE (ep.name = N'MS_Description') AND (c.name IS NULL)) AS Y
ON a.TABLE_NAME = Y.TableName
LEFT OUTER JOIN
(SELECT d.name AS tb, c.name AS col, 'PK' AS PK
FROM sys.sysindexes AS a INNER JOIN sys.sysindexkeys AS b ON a.indid = b.indid
AND a.id = b.id INNER JOIN sys.syscolumns AS c ON a.id = c.id AND b.colid = c.colid
INNER JOIN sys.sysobjects AS d ON c.id = d.id
WHERE (a.name LIKE N'pk%')) AS p ON a.TABLE_NAME = p.tb
AND a.COLUMN_NAME = p.col LEFT OUTER JOIN sys.objects AS q
ON a.TABLE_NAME = q.name
ORDER BY a.TABLE_NAME, a.ORDINAL_POSITION
/*再對TABLE_NAME進行過濾*/
select * from fielddesc where TABLE_NAME='EMPLOYEE'
/**************************************************************************************************************/
/*將表格EMPLOYEE複製到表格EMPLOYEE2*/
select * into EMPLOYEE2 from EMPLOYEE
/*將表格EMPLOYEE複製到表格EMPLOYEE3(只複製結構)*/
select top 0 * into EMPLOYEE3 from EMPLOYEE
/*從EMPLOYEE隨機取出三筆資料*/
SELECT top 3 * FROM EMPLOYEE ORDER BY NEWID()
/*取1到100之間的亂數*/
select convert(int, rand()*100) % 100 + 1 as randomnumber
/*找出資料庫中沒有建立索引(index)的表格*/
SELECT USER_NAME(OBJECTPROPERTY(A.id, 'OwnerID' )) as OwnerName
, B.name
, A.name as IndexName
, A.rowcnt
FROM sysindexes A, sysobjects B
WHERE A.indid = 0 AND A.id = B.id
AND B.type = N'U'
ORDER BY A.rowcnt desc
/*以下是列出資料庫中所有索引(index)的列表，包含最後異動日期*/
SELECT USER_NAME( OBJECTPROPERTY( i.id, 'OwnerID' ) ) AS OwnerName,
OBJECT_NAME( i.id ) AS TableName,
i.name AS IndexName,
CASE INDEXPROPERTY( i.id , i.name , 'IsClustered')
WHEN 1 THEN 'YES'
ELSE 'NO'
END AS IsClustered,
CASE INDEXPROPERTY( i.id , i.name , 'IsUnique' )
WHEN 1 THEN 'YES'
ELSE 'NO'
END AS IsUnique,
STATS_DATE( i.id , i.indid ) AS LastUpdatedDate
,rowcnt
FROM sysindexes i
WHERE OBJECTPROPERTY( i.id, 'IsMSShipped' ) = 0 And
1 NOT IN ( INDEXPROPERTY( i.id , i.name , 'IsStatistics' ) ,
INDEXPROPERTY( i.id , i.name , 'IsAutoStatistics' ) ,
INDEXPROPERTY( i.id , i.name , 'IsHypothetical' ) ) And
i.indid BETWEEN 1 And 250
ORDER BY OwnerName, TableName, IndexName
/*取得所有存儲過程(Store Procedure)資訊*/
SELECT A.[NAME] AS '結構敘述',
B.[NAME] AS '預存程序名稱',
C.[NAME] AS '預存程序傳入參數',
C.[PARAMETER_ID] AS '傳入參數順序',
D.[NAME] AS '傳入參數型別',
C.[MAX_LENGTH] AS '長度',
C.[PRECISION] AS '精確度',
C.[IS_OUTPUT] AS '傳出參數',
B.[CREATE_DATE] AS '建立時間',
B.[MODIFY_DATE] AS '修改時間'
FROM SYS.SCHEMAS A INNER JOIN SYS.PROCEDURES B
ON A.[SCHEMA_ID] = B.[SCHEMA_ID]
INNER JOIN SYS.PARAMETERS C
ON B.[OBJECT_ID] = C.[OBJECT_ID]
INNER JOIN SYS.TYPES D
ON C.[SYSTEM_TYPE_ID] = D.[SYSTEM_TYPE_ID] AND C.[USER_TYPE_ID] = D.[USER_TYPE_ID]
WHERE B.[TYPE] = 'P'
ORDER BY A.[NAME], B.[NAME], C.[PARAMETER_ID] ASC
/*取得星期*
Sun=1
Mon=2
Tue=3
Wed=4
Thu=5
Fri=6
Sat=7
*/
SELECT DATEPART(WEEKDAY, GETDATE())as 星期
/*取得星期顯示*/
select DATENAME(Weekday, GETDATE()) as 星期顯示
參考資料
點部落的各位佛心高手