-------???Windows Phone 7手機開發、.Net培訓、期待與您交流！?-------

經過一天的奮斗，終于總結出這些常用的T-SQL語句。

--創建數據庫 CREATE DATABASE SQLTest ON (NAME=SQLTest_date,FILENAME='E:\數據庫\SQLTest.mdf',SIZE=3,FILEGROWTH=1 ) LOG ON (NAME=SQLTest_log,FILENAME='E:\數據庫\SQLTest.ldf',SIZE=1,FILEGROWTH=1 ) GO--刪除數據庫 DROP DATABASE SQLTest--修改數據庫(重命名) ALTER DATABASE SQLTest MODIFY name=Test--備份數據庫 BACKUP DATABASE SQLTest TO DISK ='E:\數據庫\SQLTest.bak'--還原數據庫 RESTORE DATABASE SQLTest FROM DISK='E:\數據庫\SQLTest.bak'--創建數據庫中的表 USE SQLTest CREATE TABLE Teacher ( Id INT IDENTITY(1,1) NOT NULL, Name VARCHAR(20) NOT NULL, Sex CHAR(2) NULL, Phone VARCHAR(20) NOT NULL, Address VARCHAR(100) NULL, Email VARCHAR(30) NULL )--刪除數據庫中的表 DROP TABLE Teacher--更新數據庫中的表 --添加字段 ALTER TABLE Teacher ADD Sallary smallmoney null--刪除字段 ALTER TABLE Teacher DROP COLUMN Sallary--修改字段 ALTER TABLE Teacher ALTER COLUMN Phone VARCHAR(12) NULL--數據安全性 --創建規則 CREATE RULE SEX_RULE AS @Sex IN('男','女') GO--使用規則 EXECUTE sp_bindrule 'SEX_RULE','Teacher.Sex'--解除規則 EXECUTE sp_unbindrule 'Teacher.Sex'--刪除規則 DROP RULE SEX_RULE--數據添加 INSERT INTO Teacher VALUES('Tom','男','145656789','sdkjagfi','jkgsdhlfuihl',2000) INSERT INTO Teacher (Name,Sex) VALUES('Juery','男')--數據刪除(無條件會刪除所有元組) DELETE FROM Teacher WHERE Id='02'--數據更新(無條件會更新所有元組) UPDATE Teacher SET Sex='女' WHERE Name='Jack'--數據查詢 --查詢所有列 SELECT * FROM Teacher--查詢指定列 SELECT Name,Sex FROM Teacher--在查詢指定列中使用表達式 SELECT Name,Sex,Sallary*1.2 AS Sallary FROM Teacher--重新命名結果列名 SELECT Name AS 姓名 FROM Teacher SELECT 姓名=Name FROM Teacher--將查詢的表生成一個新表 SELECT Name,Sex INTO NEWTeacher FROM Teacher--取出查詢結果的部分內容 SELECT TOP 10 * FROM Teacher--使用邏輯表達式進行查詢 SELECT * FROM Teacher WHERE Sallary<=1000 OR Sallary>=3000 SELECT * FROM Teacher WHERE Sallary>1000 AND Sallary<3000 SELECT * FROM Teacher WHERE NOT(Sallary>3000)--使用通配符進行模糊查詢 SELECT * FROM Teacher WHERE Name LIKE '_ac%' SELECT * FROM Teacher WHERE Name LIKE '[J,T]%'--使用BETWEEN...AND關鍵字進行查詢 SELECT * FROM Teacher WHERE PostDate BETWEEN '2012-12-01' AND DATEADD (DAY ,3, '2012-12-01')--使用IN關鍵字進行查詢 SELECT * FROM Teacher WHERE Sex IN ('男','女') SELECT * FROM Teacher WHERE Sex IN (SELECT Sex FROM Teacher GROUP BY Sex)--使用NULL關鍵字進行查詢 SELECT * FROM Teacher WHERE Name IS NOT NULL--排序查詢 SELECT * FROM Teacher ORDER BY Sallary ASC SELECT * FROM Teacher ORDER BY Sallary DESC--分組查詢 SELECT Email FROM Teacher GROUP BY Email SELECT Sex, COUNT(Name) AS Count FROM Teacher GROUP BY Sex HAVING Sex='男'--使用聚集函數查詢 SELECT COUNT(*) AS 總個數 FROM Teacher --"*"包括空值行 SELECT SUM(Sallary) AS 總和 FROM Teacher SELECT AVG(Sallary) AS 平均數 FROM Teacher SELECT MAX(Sallary) AS 最大值 FROM Teacher SELECT MIN(Sallary) AS 最小值 FROM Teacher--多表連接查詢 --內連接 SELECT Teacher.Name,Course.Name FROM Teacher,Course WHERE Teacher.Id=Course.TeacherId SELECT Teacher.Name,Course.Name FROM Teacher INNER JOIN Course ON Teacher.Id=Course.TeacherId--外連接 SELECT Teacher.Name,Course.Name FROM Teacher FULL OUTER JOIN Course ON Teacher.Id=Course.TeacherId SELECT Teacher.Name,Course.Name FROM Teacher LEFT OUTER JOIN Course ON Teacher.Id=Course.TeacherId SELECT Teacher.Name,Course.Name FROM Teacher RIGHT OUTER JOIN Course ON Teacher.Id=Course.TeacherId--循環控制語句 --WHILE語句 DECLARE @num int SELECT @num=0 WHILE @num<10 BEGIN SELECT @num=@num+1 SELECT @num END--GOTO語句 BEGIN SELECT Name FROM Teacher GOTO SKIP SELECT * FROM Teacher SKIP: PRINT 'SELECT * FROM Teacher did not get excuted' END--IF...ELSE語句 DECLARE @X INT, @Y INT,@Z INT SELECT @X=2,@Y=5,@Z=4 IF @X>@Y PRINT 'X>Y' ELSE IF @Y>@Z PRINT 'Y>Z' ELSE PRINT 'Z>Y'--CASE語句 SELECT Sallary, CASE WHEN Sallary>=3000 THEN 'VERYGOOD' WHEN Sallary>=2000 THEN 'GOOD' ELSE 'BAD' END FROM Teacher--WHILE語句 DECLARE @I INT SET @I=5 WHILE @I>-1 BEGIN PRINT @I SET @I=@I-1 IF @I=1 BREAK END--WAITFOR語句 SELECT * FROM Teacher WAITFOR DELAY '00:00:10'--10秒后執行 SELECT * FROM Teacher WAITFOR TIME '00:00:10'--直到00:00:10才執行--常用函數 --CHARINDEX函數 SELECT CHARINDEX ('a','I have many friends') SELECT CHARINDEX('a','I hava many friends',5)--LEFT函數 SELECT LEFT('I hava many friends',10)--RIGHT函數 SELECT RIGHT('I have many friends',10)--LEN函數 SELECT LEN('I have many friends')--LTRIM函數 SELECT LTRIM(' I have many friends')--RTRIM函數 SELECT RTRIM('I have many friends ')--LOWER函數 SELECT LOWER('I Have Many Friends')--UPPER函數 SELECT UPPER('I have many friends')--PATINDEX函數(字符串中的第一個a) SELECT PATINDEX('%a%','I have many friends')--REPALCE函數(替換) SELECT REPLACE ('He is a student','student','teacher')--REVERSE函數(反轉) SELECT REVERSE('ABCDEFG')--STR函數 SELECT STR(123.45) SELECT STR(123.45,6,2)--SUBSTRING函數 SELECT SUBSTRING('I have many friends',1,LEN('I have many friends')/2)--DATEADD函數 SELECT DATEADD(DAY,2,'2012-12-05')--DATENAME函數 SELECT DATENAME(WEEKDAY,'2012-12-05') AS 星期幾--DATEPART函數 SELECT DATEPART(WEEKDAY,'2012-12-05') AS 星期的第幾天--DAY函數 SELECT DAY('2012-12-05') AS 天--MONTH函數 SELECT MONTH('2012-12-05') AS 月--YEAR函數 SELECT YEAR('2012-12-05') AS 年--GETDATE函數 SELECT GETDATE() AS 目前時間 SELECT YEAR (GETDATE())--CAST函數(數據類型轉換) SELECT CAST('010101' AS DATETIME) AS 日期年月日 SELECT CAST('010101' AS FLOAT) AS '0不見了'--CONVERT函數(數據格式轉換) SELECT CONVERT(VARCHAR(8),GETDATE(),1) AS 日期 SELECT CONVERT(VARCHAR(8),GETDATE(),8) AS 時間--事務 --顯示事務 USE SQLTest BEGIN TRANSACTION TA INSERT INTO Teacher (Name,Sex) VALUES ('LILEI','男') INSERT INTO Teacher (Sex) VALUES('女') ROLLBACK TRANSACTION SELECT * FROM Teacher--隱式事務 SET IMPLICIT_TRANSACTIONS ON --隱式事務不需要BEGIN TRANSACTION INSERT INTO Teacher (Name,Sex) VALUES ('LILEI','男') COMMIT TRANSACTION INSERT INTO Teacher (Name,Sex) VALUES ('LILI','女') ROLLBACK TRANSACTION SELECT * FROM Teacher SET IMPLICIT_TRANSACTIONS OFF --自動處理事務 INSERT INTO Teacher (Name,Sex) VALUES ('LILEI','男') COMMIT TRANSACTION BEGIN TRANSACTION --不再是隱式事務 INSERT INTO Teacher (Name,Sex) VALUES ('LILI','女') ROLLBACK TRANSACTION SELECT * FROM Teacher--事務案例 BEGIN TRANSACTION INSERT INTO Teacher (Name,Sex) VALUES ('LILI','女') INSERT INTO Teacher (Name,Sex) VALUES ('LILI','女') INSERT INTO Teacher (Name,Sex) VALUES ('LILI','女') INSERT INTO Teacher (Name,Sex) VALUES ('LILI','女') DECLARE @COUNT INT SET @COUNT=(SELECT COUNT(Name) FROM Teacher WHERE Name='LILI') IF @COUNT>4 BEGIN ROLLBACK TRANSACTION PRINT '數據過多，失敗！' SELECT * FROM Teacher END ELSE BEGIN COMMIT TRANSACTION PRINT '數據添加成功！' SELECT * FROM Teacher END--鎖 --查看系統鎖 EXECUTE sp_lock--鎖定表的某一行 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT * FROM Teacher ROWLOCK WHERE Id=5--鎖定整個表 SELECT * FROM Teacher TABLOCK WHERE 1=1--排它鎖 BEGIN TRANSACTION TRAN1 UPDATE Teacher SET Phone='5842156624' WHERE Name='LILI' WAITFOR DELAY '00:00:10' COMMIT TRANSACTION BEGIN TRANSACTION TRAN2 SELECT * FROM Teacher WHERE Name='LILI' COMMIT--共享鎖 BEGIN TRANSACTION TRAN1 SELECT * FROM Teacher WITH(HOLDLOCK) WAITFOR DELAY '00:00:10' COMMIT TRANSACTION BEGIN TRANSACTION TRAN2 SELECT Name,Sex FROM Teacher UPDATE Teacher SET Phone='111111111' WHERE Name='LILI' COMMIT TRANSACTION--死鎖 BEGIN TRANSACTION TRAN1 UPDATE Teacher SET Phone='33333333' WHERE Name='LILEI' WAITFOR DELAY '00:00:10' UPDATE Teacher SET Phone='44444444' WHERE Name='LILI' COMMIT TRAN BEGIN TRAN TRAN2 UPDATE Teacher SET Phone='44444444' WHERE Name='LILI' WAITFOR DELAY '00:00:10' UPDATE Teacher SET Phone='33333333' WHERE Name='LILEI' COMMIT TRANSACTION--存儲過程 --創建存儲過程 CREATE PROCEDURE SELECTBYSEX @COUNTS INT OUTPUT AS BEGIN SET NOCOUNT ON SET @COUNTS=(SELECT COUNT(*) FROM Teacher WHERE Sex='女') END GO--執行存儲過程 DECLARE @COUNTS INT EXECUTE SELECTBYSEX @COUNTS OUTPUT PRINT @COUNTS GO--查看存儲過程 EXECUTE sp_help SELECTBYSEX GO--刪除存儲過程 DROP PROCEDURE SELECTBYSEX GO

　　

轉載于:https://www.cnblogs.com/zhang-z-qiang/archive/2013/04/02/2997063.html

總結

以上是生活随笔為你收集整理的[黑马程序员五]：常用的T-SQL语句的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。