【教学课件】第三章T-SQL介绍.ppt

第三章T-SQL介绍,T-SQL 介绍,1 T-SQL Statements,Data Definition Language Statements Data Control Language StatementsData Manipulation Language Statements,T-SQL 介绍,1.1 DDL Statements,Define the Database ObjectsCREATE object_nameALTER object_nameDROP object_name,T-SQL 介绍,1.2 DCL Statements,Set or Change PermissionsGRANTDENYREVOKE,T-SQL 介绍,1.3 DML Statements,查询和修改数据SELECTINSERTUPDATE DELETE,T-SQL 介绍,2 Batch,GO批处理的结束GO 不是 Transact-SQL 语句osql、isql、查询分析器EXEC执行函数、存储过程等,T-SQL 介绍,3 注释 Comments,行注释 块注释/*/,T-SQL 介绍,4 数据类型,NumbersDatesCharactersBinaryUnique Identifiers(GUID),SQL VariantsImage and TextTableCursorUser-defined,T-SQL 介绍,5 变量,类型局部变量全局变量定义DECLARE Statement赋值SET or SELECT 举例,T-SQL 介绍,6 系统函数,聚集函数标量函数Rowset函数（链接服务器）,SELECT*FROM OPENQUERY(OracleSvr,SELECT name,id FROM owner.titles),USE northwindSELECT AVG(unitprice)AS AvgPrice FROM productsGO,USE northwindSELECT DB_NAME()AS databaseGO,T-SQL 介绍,7 流控制语言,Statement LevelBEGIN END blockIF ELSE blockWHILE constructsRow LevelCASE expression,DECLARE n tinyintSET n=5IF(n BETWEEN 4 and 6)BEGIN WHILE(n 0)BEGIN SELECT n AS Number,CASE WHEN(n%2)=1 THEN EVEN ELSE ODD END AS Type SET n=n-1 END ENDELSE PRINT NO ANALYSISGO,Example 2,T-SQL 介绍,8 动态执行语句,EXECUTE,T-SQL 介绍,9 Using Batches,用go区分不能在同一个批中存在的语句CREATE PROCEDURECREATE VIEWCREATE TRIGGERCREATE RULECREATE DEFAULT,T-SQL 介绍,10 脚本Scripts,Transact-SQL语句的集合.sql作为扩展名倒出脚本Note:可以直接使用copy and paste在EM中将对象定义倒出,T-SQL 介绍,