T-SQL全部语句.doc

T-SQL语言一、数据库1创建数据库CREATE DATABASE database_nameON PRIMARY (NAME=logical_file_name,FILENAME=os_filename_name,SIZE=size,MAXSIZE=max_sizeUNLIMITED,FILEGROWTH=grow_increment),nLOG ON(NAME=logical_file_name,FILENAME=os_filename_name,SIZE=size,MAXSIZE=max_sizeUNLIMITED,FILEGROWTH=grow_increment),nCOLLATE collation_name2使用T-SQL语句管理数据库(1)打来数据库 USE database_name(2)查看数据库信息 EXECUTEsp_helpdb database_name (3)增长或减少数据库容量ALTER DATABASE database_nameADD FILE (NAME=logical_file_name,FILENAME=os_filename_name,SIZE=size,MAXSIZE=max_sizeUNLIMITED,FILEGROWTH=grow_increment)ADD LOG FILE (NAME=logical_file_name,FILENAME=os_filename_name,SIZE=size,MAXSIZE=max_sizeUNLIMITED,FILEGROWTH=grow_increment)MODIFY FILE (NAME=file_name,SIZE=newsize)REMOVE FILE logical_file_name (4)收缩数据库容量DBCC SHRINKDATABASE (database_name)(5)查看数据库选项EXEC sp_dboption (database_name)二、数据表的基本操作1、创建数据表：create table table_name（列定义，计算列定义，表约束定义）2、表的修改与删除： alter table 表名alter column 列名 修改表列属性 add (列定义，计算列定义，表约束定义) drop column 列名 删除列三、数据的基本操作1、数据的添加 Insert into table_name (column1,column2,column3, )Values (expression1,expression2,expression3, )2、数据的修改：update table_namesetcolumn_name=where 3、数据的删除：1)使用DELETE语句删除数据： Delete talbe_nameWhere search_conditions2)使用TRUNCATE清空表格：Truncate table table_name4、简单查询SELECT语句的基本语法格式：Select select_listInto new_table_nameFrom talbe_listWhere search_conditionsGroup by group_by_listHaving search_conditionsOrder by order_listASC | DESC选择表中的若干列：1）输出表中的部分列：Select column1,column2,From talbe_name2）输出表中的所有列： Select * From table_name3）输出计算列：（例）Select 姓名,year(getdate() - year(出生日期)From 学生4）为结果集内的列指定别名：Select 列名（表达式） 列别名 FROM 数据源Select 列名（表达式） AS 类别名 FROM 数据源Select 列别名=列名（表达式） FROM 数据源5、选择表中的若干记录（以实例为例）1）消除取值重复的行Select distinct 学号From 课程Go2）限制返回行数Select top 3 * 或 select n percent * From 课程注册 返回前三条记录或者表中前20%的记录Go3）查询满足条件的元组比较大小：Where + 条件表达式确定范围：列表达式 【NOT】BETWEEN 起始值 AND 终止值例：Where 出生日期 between 1971-01-01 and 1980-12-31确定集合：where not in (列表值1，列表值2)字符匹配：not like 【ESCAPE】涉及空值的查询：not column_name IS NULL多重条件查询：not 逻辑表达式 and | or not 逻辑表达式6、对查询结果排序 order by column1ASC|DESC , columnASC|DESC , 7、对数据进行统计1） 使用集合函数Count ( distinct | all * ) 统计记录个数Count ( distinct | all )统计一列中值的个数Sum ( distinct | all )统计一列值的总和（此列必须是数值型）Avg ( distinct | all )统计一列值的平均值（此列必须是数值型）Max ( distinct | all )统计一列值的最大值Min ( distinct | all )统计一列值的最小值Distinct 是去掉制定列中的重复值，all是不取消重复值，默认状态下是all。例： 1 Select count(*) as 学生总数2 Select 性别,count distinct 性别 as 个数Group by 性别3 Select avg ( year(getdate()-year(出生日期) ) as 平均年龄2） 对结果进行分组: group by 列名Select 子句的列名必须是Group by 子句已有的列名或计算机列3） 使用COMPUTE子句: compute 集合函数 by 列名 By必须与order by 子句一起使用例：Select *From 课程注册Order by 学号Compute sum(成绩) desc4） 查询结果生成新表：into new_table_name例： Select * into 班级副本 from 班级5） 合并结果集 ： select 语句 union select 语句,.n参加union操作的各结果集的列数必须相同，对应的数据类型也必须相同，最后结果集的列名来自第一个select语句。四、数据的高级查询1、内连接（等值与非等值连接查询）：Select 列名列表 From 表名1 inner join 表名2 On 表名1.列名=表名2.列名（Select 列名列表 From 表名1，表名2 Where 表名1.列名=表名2.列名） 2、符合连接条件查询（多表连接）：Select 列名列表 From 表名1 Join 表名2 On 表名1.列名=表名2.列名 Join 表名3 On 表名1/2.列名=表名3表别名：数据表名 as 表别名数据表名 表别名3、子查询（嵌套查询）：1） 带有IN运算符的子查询：例： Select * From 班级 Where 系部代码 in（select 系部代码 from 系部where 系部名称=计算机系or 系部名称=经济管理系）2） 带有比较运算符的子查询：例： Select * from 学生 Where 学号 in （Select 学号 from 课程注册Where 成绩（Select avg（成绩）From 课程注册Where 课程号=003） And 课程号=003）3） 带有ANY 或 ALL 运算符的子查询：例： Select 学号，姓名From 学生 Where 出生日期any(select 出生日期 from 学生 where 班级代码=1) And 班级代码14） 带有EXISTS运算符的子查询：例： Select 学号，姓名From 学生 Where exists（ select * from 课程注册where 学号=学生。学号 and 课程号=1）五、约束1、创建主键约束： Alter table table_nameAdd constraint pk_namePrimary key ( column_name ) 2、 创建UNIQUE约束：Alter table table_nameAdd constraint uk_nameUnique ( column_name ) 3、 创建CHECK约束：Alter table table_nameAdd constraint ck_nameCheck ( logical_espression ) 4、 创建DEFAULT约束：Alter table table_name Add constraint dk_name Default constant_expression for column_name Constant_expression 是默认值。例： 默认为当前时间：default getdate() for column_name5、 创建FOREIGN KEY约束：Alter table table_name Add constraint fk_name Foreign key(cloumn_name,.) references tef_table(ref_column_name,.) ref_table 是主键表名称ref_column_name 是主键表的主键列名称6、查看约束定义：EXEC sp_help constraint_name查看约束文本信息：EXEC sp_helptext constraint_name7、 删除约束： Alter table table_nameDrop constraint constraint_name,.六、视图1、创建视图： Create view view_nameAs Select _statement2、 通过视图修改数据：（例）（1）添加：Insert into V_aaValues （0803031101,李菲,女,1989/02/08,2008/09/01）（2）修改：Update V_aaSet 姓名=李非 where 姓名=李菲（3）删除 ：Delete from V_aa where 姓名=李非3、 重命名视图：sp_rename old_name,new_name4、 删除视图：drop view view_name,.5、 查看视图的定义信息：EXECsp_helptext view_name七、变量1、输出全局变量： Print 当前所有SOL Server版本信息如下：Print VERSIONPrint Print 目前所用的SOL Server服务器名称为：+SERVERNAME2、 声明局部变量：declare 局部变量 数据类型,.3、 给局部变量赋值： Set 局部变量=表达式,.Select 局部变量=表达式,.八、流程控制语句1、BEGIN.AND 语句例： Use studentGoIf exists（select* from 班级 where 系部代码=02）Begin Print 满足条件的班级有： Select 班级代码，班级名称 from 班级 where 不系部代码=02EndGo 2、PRINT语句：print 字符串 | 局部变量 | 全局变量3、IF。ELSE语句：if 布尔表达式 语句1 else 语句24、CASE语句：1） 简单CASE语句Case 测试表达式When 测试值1 then 结果表达式1When 测试值2 then 结果表达式2,.n Else 结果表达式n+1End 2） 搜索CASE语句Case When 布尔表达式1 then 结果表达式1When 布尔表达式2 then 结果表达式2,.nElse 结果表达式n+1End 5、WAITFOR语句：waitfor delay 时间 | time时间例： Use studentGoWaitfor time 22:00:00Select total_writeGo6、WHILE语句：While 布尔表达式Begin语句序列1break语句序列2continue语句序列3End 九、SQL函数 1、创建用户自定义函数Create function function_name(parameter_name parameter_data_name)Returns return_data_typeBeginFunction_bodyReturn scalar_expressionEnd 2、创建用户自定义内联表值函数用户自定义内联表值函数返回的结果是表，其表由单个select语句形成。例：Use studentGoCreate function xuesheng(input char(2) Returns tableAsReturn Select 学号，姓名，入学时间From 学生 Where 班级代码 in ( Select 班级代码From 班级Where 系部代码=input)Go 调用：Select * from dbo.xuesheng(02)3、创建用户自定义多语句表值函数返回的结果也是表，区别在于：returns子句指定的table类型带有列及数据类型，则该函数是多语句表值函数。例：Use studentGoCreate function