[黑马程序员五]：常用的T-SQL语句

-------   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语句的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。