T-SQL 学习笔记

一.操作数据库
 1.创建数据库文件
 Create DataBase MyDateBase
 2.删除数据库文件
 Drop DataBase MyDateBase
二.操作基本表
 1.创建基本表
 Create Table MyTable
 (TId INT IDENTITY (1,2) NOT NULL UNIQUE,
 TName Char(10),
 TTime DateTime,
Primary Key(TId))
或 Create Table MyTable3
 (TId INT IDENTITY (1,2) NOT NULL Primary Key,
 TName Char(10),
 Tage int default 20;
 TTime DateTime)
 解释:TId:取值唯一，不允许为空.标识种子为1，标识递增量为2.且为主键。
 TName:字符串,长度为10.
 TTime:日期时间.
 2.修改基本表
 Alter Table MyTable
 '添加列
 [Add <新列名> <数据类型> [完整性约束]]
 '删除列约束
 [Drop <完整性结束>]
 '修改列定义
 [Modify <列名> <数据类型>]
 3.删除基本表
 Drop Table MyTable
三.操作索引
 1.建立索引
 Create [UNIQUE][CLUSTER] Index <索引名>
 On <表名> (<列名>[<DESC/ASC>][,<列名>[<排序方式>]]...)
 UNIQUE:每一索引值对应唯一的记录
 CLUSTER:聚簇索引
 2.删除索引
 Drop Index <索引名>
四.存储过程
1.查询(不带参数)
CREATE PROCEDURE 存储过程_用户 AS
Select * From 用户
2.插入(输入参数)
CREATE PROCEDURE 用户_插入
 (@姓名 [char](10),@密码 [char](10))
AS INSERT INTO 用户
 (姓名,密码)
VALUES (@姓名,@密码)
3.删除(输入参数)
CREATE PROCEDURE 用户_删除
 (@姓名 [char](10))
AS Delete From 用户 Where 姓名=@姓名
4.修改(输入参数)
CREATE PROCEDURE 用户_修改
 (@姓名 [char](10),@密码 [char](10))
AS Update  用户 Set
 密码=@密码
Where 姓名=@姓名
5.查询(输出参数)
CREATE PROCEDURE 用户_查询
 (@编号 [int],
 @姓名 [char](10) OUTPUT,
 @密码 [char](10) OUTPUT)
AS
Select @姓名=姓名,@密码=密码
From 用户
Where 编号=@编号
6.执行存储过程
USE 数据库名
Exec 存储过程名
[@输入参数1=值1,
...
@输入参数n=值n]
五.查询记录
 1.普通查询
 Select Top 10 * From MyTable
 2.高级查询
 Select DISTINCT TName,TAge-10 TNAge,'TTStr' TTStr
 Where TId Is Not NULL
 Order By TId DESC
 注:Order By只能对最终结果排序
 ①.比较:=,>,<,>=,<=,!=,<>,!>,!<和Not加以上比较运算符
 ②.确定范围:Between And,Not Between And
 ③.确定集合:In,Not In
 ④.字符匹配:Like,Not Like
 ☉ %:匹配任意长度
 ☉ _:匹配单个字符
 ☉ ESCAPE:换码关键字
 ☉ 例:Where TName Not Like 'C_B%' ESCAPE ''
 ⑤.空值:Is NULL,Is Not NULL
 ⑥.多重条件:And,Or
 3.分组查询
 Select TId,COUNT(TAge) From MyTable
 Group By TId
 HAVING COUNT(TAge)>3
 4.连接查询
 Select T1.TName,T2.Age From T1,T2
 Where T1.TId=T2.TId(*)
 5.嵌套查询
 Select TName Form T1
 Where TName In
  (Select TName From T2
  Where TId >ANY
   (Select TId From T3
   Where TAge=20))
 谓词:
 ⑴ >ANY:大于子查询结果中的某个值
 ⑵ >ALL:大于子查询结果中的所有值
 ⑶ <ANY:小于子查询结果中的某个值
 ⑷ <ALL:小于子查询结果中的所有值
 ⑸ >=ANY:大于等于子查询结果中的某个值
 ⑹ >=ALL:大于等于子查询结果中的所有值
 ⑺ <=ANY:小于等于子查询结果中的某个值
 ⑻ <=ALL:小于等于子查询结果中的所有值
 ⑼ =ANY:等于子查询结果中的某个值
 ⑽ =ALL:等于子查询结果中的所有值
 ⑾ !=(或<>) ANY:不等于子查询结果中的某个值
 ⑿ !=(或<>) ALL:不等于子查询结果中的所有值
 ⒀ EXISTS:存在量词
 6.并集查询
 Select * From T1
 UNION
 Select * From T2
六.插入记录
 1.插入单个元组
 Insert Into MyTable[(TName,TTime,TAge)]
 Values('C',#2005-1-1#,20)
 2.插入查询结果
 Insert Into MyTable[(TName,TTime,TAge)]
  Select IName,TTime,TAge From T2
  Where TId=1
七.更新记录
 1.修改单个元组
 Update <表名>
 Set <列名>=<表达式>[,<列名>=<表达式>...]
 Where TId=1
 2.带子查询修改
 Update <表名>
 Set <列名>=<表达式>[,<列名>=<表达式>...]
 Where TId=
  (Select TId From T2
  Where TAge=20)
八.删除记录
 1.删除单个元组
 Delete From  MyTable
 Where TId=1
 2.带子查询删除
 Delete From MyTable
 Where TId=
  (Select TId From T2
  Where TAge=20)
九.操作视图
 1.定义视图
 Create View <视图名>[<列名>[,<列名>...]]
 As <子查询>
 [WITH CHECK OPTION]
 注:
 ①.子查询可以含有任意复杂的Select语句，但通常不允许含有Order By子句和Distinct短语。
 ②.WITH CHECK OPTION表示对视图进行更新、插入和删除时,要保证这些操作的行满足视图定义中的谓词条件(即子查询中的条件表达式)
 ③.组成视图的属性列名或者全部省略或者全部指定，没有第3种选择。如果省略，则隐含该视图由子查询中Select子句目标列中的诸字段组成。但在以下三种情况下必须明确指定组成视图的所有列名:
 ☉ 某个目标列不是单纯的属性名，而是集函数或列表达式
 ☉ 多表连接时选出了几个同名列作为视图的字段
 ☉ 需要在视图中为某个列启用新的更合适的名字
 2.删除视图
 Drop View <视图名>
 3.查询视图
 和对基本表操作一样
 4.更新视图
 通常和对基本表操作一样
 ①.若视图是由两个以上基本表导出的，则不允许更新；
 ②.若视图的字段来自字段表达式或常数，则不允许插入和更新，但可删除；
 ③.若视图的字段来自集函数，则不允许更新；
 ④.若视图定义中含有Group By子句，则不允许更新；
 ⑤.若视图定义中含有Distinct短语，则不允许更新；
 ⑥.若视图定义中有嵌套，并且内层查询的From子句中涉及的表也是导出该视图的基本表，则不允许更新；
 ⑦.一个不允许更新的视图上定义的视图也不允许更新。
十.数据控制
 1.授权
 Grant <权限>[,<权限>...]
  [ON <对象类型> <对象名>]
  To <用户>[,<用户>...]
  [WITH GRANT OPTION]
 ①.对象类型与权限
 ☉对象:属性列;对象类型:TABLE;操作权限:SELECT,INSERT,UPDATE,DELETE,ALL PRIVILEGES
 ☉对象:视图;对象类型:TABLE;操作权限:SELECT,INSERT,UPDATE,DELETE,ALL PRIVILEGES
 ☉对象:基本表;对象类型:TABLE;操作权限:SELECT,INSERT,UPDATE,DELETE,ALTER,INDEX,ALL PRIVILEGES
 ☉对象:数据库;对象类型:DATABASE;操作权限:CREATETAB
 ②.WITH GRANT OPTION
 指定是否能传播该权限
 2.收回权限
 Revoke <权限>[,<权限>...]
  [ON <对象类型> <对象名>]
  From <用户>[,<用户>...]
十一.函数
 1.常用数学函数：
 ①.ABS(X):返回X的绝对值
 ②.CEIL(X)或CEILING(X):返回>=X的最小整数
 ③.FLOOR(X):返回<=X的最大整数
 ④.MOD(X):返回X/Y的余数
 ⑤.POWER(X,Y):返回X和Y的次幂
 ⑥.ROUND(X,Y):把X四舍五入到小数点后Y位
 ⑦.SIGN(X):判断X的符号，正则返回+1，负则返回-1，0则返回0
 ⑧.SQRT(X):返回X的平方根
 2.常用的字符串函数和日期函数
 ①.LEFT(string,X):返回String串最左边的X个字符
 ②.UPPER(string,X):将String串全部转换成大写
 ③.String1+string2或String1 || string2:将两个字符串合并
 ④.SUBSTR(string,X,Y):从String串的X位置处开始抽取Y个字母
 ⑤.Sysdate或Getdate()或Current_Date:返回当天的日期
 3.常用集合函数
 ①.COUNT([DISTINCT|ALL]*):统计元组个数
 ②.COUNT([DISTINCT|ALL]<列名>):统计一列中值的个数
 ③.SUM([DISTINCT|ALL]<列名>):计算列值总和
 ④.AVG([DISTINCT|ALL]<列名>):计算一列值的平均值
 ⑤.MAX([DISTINCT|ALL]<列名>):求一列值中的最大值
 ⑥.MIN([DISTINCT|ALL]<列名>):求一列值中的最小值
 实例:
  Sql="Select Sum(字段名) As 别名 From 数据表 Where 条件表达式"
　Set rs=db.Excute(Sql)
　用 rs("别名") 获取统的计值，其它函数运用同上.
十二.数据字段
 1.VARCHAR类型可以存储的字符串最长为255个字符
 2.char(x)字符型数据用来存储固定长度的字符数据
 3.TEXT(文本型数据)可以存放超过二十亿个字符的字符串
 4.INT不允许超过2147483647(占用4个字节)
 5.SMALLINT 型数据可以存储从-32768到32768的整数
 6.TINYINT型数据只能存储从0到255的整数(占用1个字节)
 7.NUMERIC(x,y)型字段可以存储从-10e38到10e38范围内的数
   NUMERIC型数据的整数部分最大只能有28位,小数部分的位数必须小于或等于整数部分的位数,小数部分可以是零
 8.MONEY型数据可以存储从-922，337，203，685，477.5808到922，337，203，685，477.5807的钱数
 9.SMALLMONEY型数据只能存储从-214，748.3648到214，748.3647 的钱数
 10.MONEY型数据可以存储从-922，337，203，685，477.5808到922，337，203，685，477.5807的钱数
 11.DATETIME型的字段可以存储的日期范围是从1753年1月1日第一毫秒到9999年12月31日最后一毫秒
 12.SMALLDATETIME型的字段能够存储从1900年1月1日到2079年6月6日的日期,它只能精确到秒。一.操作数据库
 1.创建数据库文件
 Create DataBase MyDateBase
 2.删除数据库文件
 Drop DataBase MyDateBase
二.操作基本表
 1.创建基本表
 Create Table MyTable
 (TId INT IDENTITY (1,2) NOT NULL UNIQUE,
 TName Char(10),
 TTime DateTime,
Primary Key(TId))
或 Create Table MyTable3
 (TId INT IDENTITY (1,2) NOT NULL Primary Key,
 TName Char(10),
 Tage int default 20;
 TTime DateTime)
 解释:TId:取值唯一，不允许为空.标识种子为1，标识递增量为2.且为主键。
 TName:字符串,长度为10.
 TTime:日期时间.
 2.修改基本表
 Alter Table MyTable
 '添加列
 [Add <新列名> <数据类型> [完整性约束]]
 '删除列约束
 [Drop <完整性结束>]
 '修改列定义
 [Modify <列名> <数据类型>]
 3.删除基本表
 Drop Table MyTable
三.操作索引
 1.建立索引
 Create [UNIQUE][CLUSTER] Index <索引名>
 On <表名> (<列名>[<DESC/ASC>][,<列名>[<排序方式>]]...)
 UNIQUE:每一索引值对应唯一的记录
 CLUSTER:聚簇索引
 2.删除索引
 Drop Index <索引名>
四.存储过程
1.查询(不带参数)
CREATE PROCEDURE 存储过程_用户 AS
Select * From 用户
2.插入(输入参数)
CREATE PROCEDURE 用户_插入
 (@姓名 [char](10),@密码 [char](10))
AS INSERT INTO 用户
 (姓名,密码)
VALUES (@姓名,@密码)
3.删除(输入参数)
CREATE PROCEDURE 用户_删除
 (@姓名 [char](10))
AS Delete From 用户 Where 姓名=@姓名
4.修改(输入参数)
CREATE PROCEDURE 用户_修改
 (@姓名 [char](10),@密码 [char](10))
AS Update  用户 Set
 密码=@密码
Where 姓名=@姓名
5.查询(输出参数)
CREATE PROCEDURE 用户_查询
 (@编号 [int],
 @姓名 [char](10) OUTPUT,
 @密码 [char](10) OUTPUT)
AS
Select @姓名=姓名,@密码=密码
From 用户
Where 编号=@编号
6.执行存储过程
USE 数据库名
Exec 存储过程名
[@输入参数1=值1,
...
@输入参数n=值n]
五.查询记录
 1.普通查询
 Select Top 10 * From MyTable
 2.高级查询
 Select DISTINCT TName,TAge-10 TNAge,'TTStr' TTStr
 Where TId Is Not NULL
 Order By TId DESC
 注:Order By只能对最终结果排序
 ①.比较:=,>,<,>=,<=,!=,<>,!>,!<和Not加以上比较运算符
 ②.确定范围:Between And,Not Between And
 ③.确定集合:In,Not In
 ④.字符匹配:Like,Not Like
 ☉ %:匹配任意长度
 ☉ _:匹配单个字符
 ☉ ESCAPE:换码关键字
 ☉ 例:Where TName Not Like 'C_B%' ESCAPE ''
 ⑤.空值:Is NULL,Is Not NULL
 ⑥.多重条件:And,Or
 3.分组查询
 Select TId,COUNT(TAge) From MyTable
 Group By TId
 HAVING COUNT(TAge)>3
 4.连接查询
 Select T1.TName,T2.Age From T1,T2
 Where T1.TId=T2.TId(*)
 5.嵌套查询
 Select TName Form T1
 Where TName In
  (Select TName From T2
  Where TId >ANY
   (Select TId From T3
   Where TAge=20))
 谓词:
 ⑴ >ANY:大于子查询结果中的某个值
 ⑵ >ALL:大于子查询结果中的所有值
 ⑶ <ANY:小于子查询结果中的某个值
 ⑷ <ALL:小于子查询结果中的所有值
 ⑸ >=ANY:大于等于子查询结果中的某个值
 ⑹ >=ALL:大于等于子查询结果中的所有值
 ⑺ <=ANY:小于等于子查询结果中的某个值
 ⑻ <=ALL:小于等于子查询结果中的所有值
 ⑼ =ANY:等于子查询结果中的某个值
 ⑽ =ALL:等于子查询结果中的所有值
 ⑾ !=(或<>) ANY:不等于子查询结果中的某个值
 ⑿ !=(或<>) ALL:不等于子查询结果中的所有值
 ⒀ EXISTS:存在量词
 6.并集查询
 Select * From T1
 UNION
 Select * From T2
六.插入记录
 1.插入单个元组
 Insert Into MyTable[(TName,TTime,TAge)]
 Values('C',#2005-1-1#,20)
 2.插入查询结果
 Insert Into MyTable[(TName,TTime,TAge)]
  Select IName,TTime,TAge From T2
  Where TId=1
七.更新记录
 1.修改单个元组
 Update <表名>
 Set <列名>=<表达式>[,<列名>=<表达式>...]
 Where TId=1
 2.带子查询修改
 Update <表名>
 Set <列名>=<表达式>[,<列名>=<表达式>...]
 Where TId=
  (Select TId From T2
  Where TAge=20)
八.删除记录
 1.删除单个元组
 Delete From  MyTable
 Where TId=1
 2.带子查询删除
 Delete From MyTable
 Where TId=
  (Select TId From T2
  Where TAge=20)
九.操作视图
 1.定义视图
 Create View <视图名>[<列名>[,<列名>...]]
 As <子查询>
 [WITH CHECK OPTION]
 注:
 ①.子查询可以含有任意复杂的Select语句，但通常不允许含有Order By子句和Distinct短语。
 ②.WITH CHECK OPTION表示对视图进行更新、插入和删除时,要保证这些操作的行满足视图定义中的谓词条件(即子查询中的条件表达式)
 ③.组成视图的属性列名或者全部省略或者全部指定，没有第3种选择。如果省略，则隐含该视图由子查询中Select子句目标列中的诸字段组成。但在以下三种情况下必须明确指定组成视图的所有列名:
 ☉ 某个目标列不是单纯的属性名，而是集函数或列表达式
 ☉ 多表连接时选出了几个同名列作为视图的字段
 ☉ 需要在视图中为某个列启用新的更合适的名字
 2.删除视图
 Drop View <视图名>
 3.查询视图
 和对基本表操作一样
 4.更新视图
 通常和对基本表操作一样
 ①.若视图是由两个以上基本表导出的，则不允许更新；
 ②.若视图的字段来自字段表达式或常数，则不允许插入和更新，但可删除；
 ③.若视图的字段来自集函数，则不允许更新；
 ④.若视图定义中含有Group By子句，则不允许更新；
 ⑤.若视图定义中含有Distinct短语，则不允许更新；
 ⑥.若视图定义中有嵌套，并且内层查询的From子句中涉及的表也是导出该视图的基本表，则不允许更新；
 ⑦.一个不允许更新的视图上定义的视图也不允许更新。
十.数据控制
 1.授权
 Grant <权限>[,<权限>...]
  [ON <对象类型> <对象名>]
  To <用户>[,<用户>...]
  [WITH GRANT OPTION]
 ①.对象类型与权限
 ☉对象:属性列;对象类型:TABLE;操作权限:SELECT,INSERT,UPDATE,DELETE,ALL PRIVILEGES
 ☉对象:视图;对象类型:TABLE;操作权限:SELECT,INSERT,UPDATE,DELETE,ALL PRIVILEGES
 ☉对象:基本表;对象类型:TABLE;操作权限:SELECT,INSERT,UPDATE,DELETE,ALTER,INDEX,ALL PRIVILEGES
 ☉对象:数据库;对象类型:DATABASE;操作权限:CREATETAB
 ②.WITH GRANT OPTION
 指定是否能传播该权限
 2.收回权限
 Revoke <权限>[,<权限>...]
  [ON <对象类型> <对象名>]
  From <用户>[,<用户>...]
十一.函数
 1.常用数学函数：
 ①.ABS(X):返回X的绝对值
 ②.CEIL(X)或CEILING(X):返回>=X的最小整数
 ③.FLOOR(X):返回<=X的最大整数
 ④.MOD(X):返回X/Y的余数
 ⑤.POWER(X,Y):返回X和Y的次幂
 ⑥.ROUND(X,Y):把X四舍五入到小数点后Y位
 ⑦.SIGN(X):判断X的符号，正则返回+1，负则返回-1，0则返回0
 ⑧.SQRT(X):返回X的平方根
 2.常用的字符串函数和日期函数
 ①.LEFT(string,X):返回String串最左边的X个字符
 ②.UPPER(string,X):将String串全部转换成大写
 ③.String1+string2或String1 || string2:将两个字符串合并
 ④.SUBSTR(string,X,Y):从String串的X位置处开始抽取Y个字母
 ⑤.Sysdate或Getdate()或Current_Date:返回当天的日期
 3.常用集合函数
 ①.COUNT([DISTINCT|ALL]*):统计元组个数
 ②.COUNT([DISTINCT|ALL]<列名>):统计一列中值的个数
 ③.SUM([DISTINCT|ALL]<列名>):计算列值总和
 ④.AVG([DISTINCT|ALL]<列名>):计算一列值的平均值
 ⑤.MAX([DISTINCT|ALL]<列名>):求一列值中的最大值
 ⑥.MIN([DISTINCT|ALL]<列名>):求一列值中的最小值
 实例:
  Sql="Select Sum(字段名) As 别名 From 数据表 Where 条件表达式"
　Set rs=db.Excute(Sql)
　用 rs("别名") 获取统的计值，其它函数运用同上.
十二.数据字段
 1.VARCHAR类型可以存储的字符串最长为255个字符
 2.char(x)字符型数据用来存储固定长度的字符数据
 3.TEXT(文本型数据)可以存放超过二十亿个字符的字符串
 4.INT不允许超过2147483647(占用4个字节)
 5.SMALLINT 型数据可以存储从-32768到32768的整数
 6.TINYINT型数据只能存储从0到255的整数(占用1个字节)
 7.NUMERIC(x,y)型字段可以存储从-10e38到10e38范围内的数
   NUMERIC型数据的整数部分最大只能有28位,小数部分的位数必须小于或等于整数部分的位数,小数部分可以是零
 8.MONEY型数据可以存储从-922，337，203，685，477.5808到922，337，203，685，477.5807的钱数
 9.SMALLMONEY型数据只能存储从-214，748.3648到214，748.3647 的钱数
 10.MONEY型数据可以存储从-922，337，203，685，477.5808到922，337，203，685，477.5807的钱数
 11.DATETIME型的字段可以存储的日期范围是从1753年1月1日第一毫秒到9999年12月31日最后一毫秒
 12.SMALLDATETIME型的字段能够存储从1900年1月1日到2079年6月6日的日期,它只能精确到秒。