SQL Server笔记.doc

1.数据类型类型名称取值范围整数bigint、int、smallint、tinyint(922亿亿) 8(21亿) 4(32768) 2(0-255) 1位型Bit由0和1表示真、假。货币型money、smallmoney(922万亿)(21万)，精确到万分之一。十进制Decimal、Numeric1038-1，最大位数38位浮点数float、real(1.79E+308) (3.40E+38)日期时间Datetime、smalldatetime1753.1.1-9999.12.31,精确到3.33毫秒1900.1.1-2079.12.31，精确到分钟单字节字符char / varchar / text定/变长单字节字符,最长8000；Unicode字符nchar / nvarchar / ntext定/变长双字节字符，最长4000；二进制数据Binary / varbinary / image定/变长二进制数据，最长8000；变长二进制数据特殊类型timestampSQL 活动的先后顺序。uniqueidentifier全局唯一标识2.常量类型类型说明例如整型常量没有小数点和指数E60，25，-365实型常量decimal或numeric带小数点的常数，float或real带指数E的常数15.63、-200.25+123E-3、-12E5字符串常量单引号引起来学生，this is database双字节字符串前缀N必须是大写单引号引起来N学生日期型常量单引号()引起来6/5/03，May 12 2008，19491001货币型常量精确数值型数据，前缀$380.2二进制常量前缀0x。0xAE、0x12Ef、0x69048AEFDD010E全局唯一标识符前缀0x单引号()引起来0x6F9619FF8B86D011B42D00C04FC964FF6F9619FF-8B86-D011-B42D-00C04FC964FF3.SQL查询分析器常用快捷键快捷键功能快捷键功能CTRL+A全选F3 重复查找CTRL+C复制CTRL+H替换CTRL+V粘贴CTRL+SHIFT+L使选定内容为小写CTRL+X剪切CTRL+SHIFT+U使选定内容为大写CTRL+Z撤消CTRL+F5分析查询并检查语法TAB增大缩进F5 执行查询SHIFT+TAB减小缩进ALT+BREAK取消查询CTRL+SHIFT+C注释代码F1 查询分析器帮助CTRL+SHIFT+R删除注释SHIFT+F1对所选SQL语句的帮助CTRL+F查找F8 显示/隐藏对象浏览器4.select无源查询，即在客户的屏幕上显示内容SELECT 常量|变量|函数|表达式 as 别名,n5. 变量是由用户定义并可赋值的实体。变量有全局变量和局部变量两种。局部变量用DECLARE语句声明，声明时它被初始化为NULL，并由SET语句或SELECT语句赋值，它只能用在声明该变量的过程实体中，即使用范围是定义它的批、存储过程和触发器等。其名字由一个符号开始。 定义：DECLARE 局部变量名 数据类型,n赋值：SET 局部变量名=表达式,n如：declare 整型变量int, s nvarchar(9)set 整型变量=40set s=欢迎您到太原。select 整型变量, s6. 全局变量全局变量由SQL Server系统提供并赋值的变量，名字由符号开始。用户不能建立全局变量，也不可能使用SET语句去修改全局变量的值。通常应该将全局变量的值赋给在同一个批中的局部变量，以便保存和处理。全局变量分为两类： 与连接有关的全局变量，如：rowcount 影响的行数； 与系统内部信息有关的全局变量。如version SQL的版本号。SQL常用的全局变量名称说明connections返回当前到本服务器的连接的数目。rowcount返回上一条T-SQL语句影响的数据行数。error返回上一条T-SQL语句执行后的错误号。procid返回当前存储过程的ID号remserver返回登录记录中远程服务器的名字。spid返回当前服务器进程的ID标识。version返回当前SQL Server服务器的版本和处理器类型。language返回当前SQL Server服务器的语言。7. 函数可分系统定义函数和用户定义函数。系统定义函数中最常用的是：数学函数、字符串函数、日期时间函数、聚合函数、系统函数、系统统计函数。数学函数：函数名称ROUND(数字表达式, 小数位数)四舍五入FLOOR(数字表达式)整数函数（小于它的最大整数）CEILING(数字表达式)整数函数（大于它的最小整数）RAND()随机函数字符串函数：名称函数ASCII码ASCII(字符表达式)字符CHAR(数字表达式)统一代码UNICODE(字符表达式)字符NCHAR(数字表达式)小写LOWER(字符表达式)大写UPPER(字符表达式)长度LEN(字符表达式)空格SPACE(数字表达式)截取左字串LEFT(字符表达式,整数)截取右字串RIGHT(字符表达式,整数)截取中间字串SUBSTRING(字符表达式,起始点,n)求子串位置CHARINDEX(字符表达式1, 字符表达式2,开始位置)剪去左空格LTRIM(字符表达式)剪去右空格RTRIM(字符表达式)重复字串REPLICATE(字符表达式,n)倒置字串REVERSE(字符表达式)数值转字串STR(数字表达式)日期时间函数名称函数现在日期时间GETDATE()年YEAR(日期型表达式)月MONTH(日期型表达式)日DAY(日期型表达式)日期 部分DATEPART(格式串, 日期型表达式)日期 加DATEADD(格式串,数值,日期)日期 差DATEDIFF(格式串,日期1,日期2)聚合函数（除count外，其它的都忽略空值）名称最大最小求和平均计数函数MAXMINSUMAVGCOUNT系统函数函数说明APP_NAME当前会话的应用程序名称CURRENT_USER当前的数据库用户。USER_NAME用户数据库用户名。SESSION_USER会话用户名SYSTEM_USER系统用户名HOST_ID工作站标识号。HOST_NAME工作站名称。ISDATE是否为有效的日期。ISNULL是否为NULL。ISNUMERIC是否为一个有效的数字类型。ERROR最后执行的 T-SQL 语句的错误代码。TRANCOUNT当前连接的活动事务数。系统统计函数函数说明CONNECTIONS连接次数。CPU_BUSYCPU 的工作时间IDLE闲置的时间IO_BUSY输入和输出的时间TIMETICKS返回一刻度的微秒PACK_SENT发送的数据包数PACK_RECEIVED接受的数据包数PACKET_ERRORS发生的数据包错误TOTAL_WRITE写入磁盘的次数TOTAL_READ读取磁盘的次数TOTAL_ERRORS磁盘读/写错误次数8.逻辑运算运算符含义AND而且OR或者NOT否定IN在之内LIKE匹配(像)BETWEEN在之间。EXISTS存在ALL所有(都)ANY任一SOME有些运算符优先级（从上到下，相同级从左到右）类型运算符一元运算+(正)、-(负)、(按位 NOT)乘除模*(乘)、/(除)、%(模)加减串联+(加)、(+ 串联)、-(减)比较运算=, , =, =, 位运算(位异或)、&(位与)、|(位或)逻辑非NOT逻辑与AND逻辑或等ALL、ANY、BETWEEN、IN、LIKE、OR、SOME赋值=9. 批是一个SQL语句集，这些语句一起提交并作为一个组来执行。批结束的符号是“GO”。由于批中的多个语句是一起提交给SQL SERVER的，所以可以节省系统开销。使用批限制： CREATE DEFAULT、CREATE PROCEDURE、CREATE RULE、CREATE TRIGGER 和 CREATE VIEW 语句不能在批处理中与其它语句组合使用。批处理必须以 CREATE 语句开始，所有跟在CREATE后的其它语句将被解释为第一个 CREATE 语句定义的一部分。 在同一个批中不能既绑定到列又被使用规则或默认。 在同一个批中不能删除一个数据库对象又重建它。 在同一个批中不能改变一个表再立即引用其新列。10. 1RETURNRETURN的作用是无条件返回所在的批、存储过程和触发器。退出时，可以返回状态信息。在RETURN语句后面的任何语句不被执行。RETURN语句的语法形式：RETURN 整型表达式 2PRINT和RAISERRORPRINT语句的作用是在屏幕上显示用户信息。其语法形式为：PRINT 字符串 | 局部变量| 全局变量RAISERROR语句的作用是将错误信息显示在屏幕上，同时也可以记录在NT日志中。其语法形式为：RAISERROR(错误号|错误信息, 错误的严重级别, 错误时的状态信息)。 3复合语句(BEGIN.END)其语法形式为：BEGIN 执行的SQL语句END4CASE表达式根据测试/条件表达式的值的不同，取其相应的值。CASE WHEN 条件表达式0 THEN 结果表达式0,.nELSE 结果表达式nEND如：declare 分数 decimaldeclare 成绩级别 nchar(3)set 分数 = 88set 成绩级别 = case when 分数=90 and 分数=80 and 分数=70 and 分数=60 and 分数70 then 及格 when 分数60 then 不及格endprint 成绩级别或CASE 测试表达式WHEN简单表达式0 THEN 结果表达式0,.nELSE 结果表达式nEND如：declare 分数 decimaldeclare 成绩级别 nchar(3)set 分数 = 88set 成绩级别 = case floor(分数/10) when 10 then 优秀 when 9 then 优秀 when 8 then 良好 when 7 then 中等 when 6 then 及格 else 不及格endprint 成绩级别5判断语句(IF.ELSE)语法：IF 条件表达式 SQL语句1ELSE SQL语句26循环语句(WHILE)WHILE语句的作用是为重复执行某一语句或语句块设置条件。其语法形式为：WHILE 条件表达式 SQL语句 |复合语句说明：BREAK、CONTINUE位于复合语句内，为可选项。BREAK跳出循环之后执行、CONTINUE转到循环开始之处执行。 如：计算1到100之间偶数的个数和偶数的总和declare score int declare sum int declare count intset score=1 set count=0 set sum=0while score=100begin if score%2=0begin/当在控制流中需要用beginend来代替其他语言中的花括号.set sum=sum+score set count=count+1endset score=score+1endselect sum,count7注释多行注释：/*/ 单行 注释：- SQL杂汇1. 创建数据库CREATE DATABASE 数据库名 ON (NAME = 逻辑文件名, FILENAME = 物理文件名.mdf) LOG ON (NAME = 逻辑文件名_log, FILENAME = 物理文件名_log.ldf) 如：create database 教学成绩管理数据库on (name = 教学成绩管理数据库, filename =e:sql教学成绩管理数据库.mdf,size = 1, filegrowth = 10%)log on (name = 教学成绩管理数据库_log,filename =e:sql教学成绩管理数据库.ldf, size = 5,filegrowth = 10%)2.查看数据库，以下四种：sp_helpdb 数据库名/显示一个数据库的信息sp_databases/显示系统内所有可用的数据库sp_helpfile 数据库名sp_helpfilegroup 文件组名3.修改数据库ALTER DATABASE命令中分别用下列选项：ADD FILE 增加数据文件REMOVE FILE 删除数据文件MODIFY FILE 修改数据文件ADD LOG FILE 增加日志文件REMOVE LOG FILE 删除日志文件MODIFY LOG FILE 修改日志文件如：-【例5.12】设置教学成绩管理数据库自动收缩。alter database 教学成绩管理数据库 set auto_shrink on4.删除数据库DROP DATABASE 数据库名 ,5.表的管理创建表： CREATE TABLE 数据表名 ( 列名 数据类型 | 列名 AS 计算列表达式 ,.n )显示表的结构：sp_help 数据库对象名列的管理：1 添加列 ALTER TABLE 表名 ADD 列名 列的描述2 删除列 ALTER TABLE 表名 DROP COLUMN 列名,3 修改列 ALTER TABLE 表名 ALTER COLUMN 列名 列的描述示例：alter table 学生信息表 alter column 姓名 nchar(4) not null删除表：DROP TABLE 表名6.数据的管理1.插入数据：INSERT INTO 表名 (列名1,) Values (表达式1,) 2.更新数据：UPDATE 表名 SET 列名= 表达式 WHERE 条件3.删除数据：DELETE 表名 WHERE 条件 或 TRUNCATE TABLE 表名 4.图像、文本数据的管理：代研究。1. sp_tableoption 表名, text in row, 值 sp_tableoption 学生简历照片表,text in row,on 不大于256字节。sp_tableoption 学生简历照片表,text in row,1000 不大于1000字节sp_tableoption 学生简历照片表,text in row,off 不在行中存储2. 写入数据:WRITETEXT 表名.列名 指针变量 数据(TEXTPTR 函数获得有效的文本指针值)3. 修改数据:UPDATETEXT 表名.列名 指针变量 NULL|位置 NULL|长度 数据4. 读出数据: READTEXT 表名.列名 指针 位置 长度7.小结功能语句语法格式数据库创建数据库*CREATE DATABASE 数据库名 ON (NAME = 逻辑文件名, FILENAME = 物理文件名.mdf) LOG ON (NAME = 逻辑文件名_log, FILENAME = 物理文件名_log.ldf) 删除数据库*DROP DATABASE 数据库名数据表创建表*CREATE TABLE 数据表名( 列名 数据类型 |列名 AS 计算列表达式 ,.n )数据表修改表添加列ALTER TABLE 表名 ADD 列名 列的描述修改列ALTER TABLE 表名 ALTER COLUMN 列名 列的描述删除列ALTER TABLE 表名 DROP COLUMN 列名,删除表*DROP TABLE 表名数据操作插入数据*INSERT INTO 表名 (列名1,) Values (表达式1,) 修改数据*UPDATE 表名SET 列名= 表达式WHERE 条件删除数据*DELETE 表名 WHERE 条件数据查询1.select基本语法:SELECT 字段列表 INTO 目标数据表FROM 源数据表或视图,.nWHERE 条件表达式GROUP BY 分组表达式 HAVING 搜索表达式ORDER BY 排序表达式 ASC|DESCCOMPUTE 行聚合函数名(表达式),.n BY 表达式 ,.n 2.聚合函数函数名功能sum()对数值型列或计算列求总和avg()对数值型列或计算列求平均值min()返回一个数值列或数值表达式的最小值max()返回一个数值列或数值表达式的最大值count()返回满足指定的条件的记录的个数count(*)返回找到的行数3.连接查询(S1难点)1)内连接(自然连接): FROM 表1 inner JION 表2 ON 条件表达式1(查询两张表都存在的)2). 外连接: (1)左外连接：FROM 表1 LEFT OUTER JOIN 表2 ON 条件表达式(主表存在的,即紧跟from的表)(2)右外连接：FROM 表1 RIGHT OUTER JOIN 表2 ON 条件表达式(从表存在的,即后面的表)(3)全外连接：FROM 表1 FULL OUTER JOIN 表2 ON 条件表达式(两张表有一存在即可)3).自连接: 在同一张表内进行自身连接 (必须为表指定两个别名 ) 4).交叉连接：非限制连接，将两个表不加任何约束地组合起来: FROM 表1 CROSS JOIN 表2 或 FROM 表1,表24.合并结果集：UnionALL注意：(1)联合查询是将两个表(结果集)顺序连接。(2)UNION中的每一个查询所涉及的列必须具有相同的列数、相同位置的列数据类型要相同。若长度不同，以最长的字段作为输出字段的长度。(3)结果集中的列名来自第一个SELECT语句。(4)最后一个SELECT查询可以带ORDER BY子句,对整个UNION 操作结果集起作用。且只能用第一个SELECT查询中的字段作排序列。(5)系统自动删除结果集中重复的记录，除非使用ALL关键字。5. COMPUTE 行聚合函数名(统计表达式) ,.n BY 分类表达式 ,.n 函数描述AVG平均值COUNT行数MAX最高值MIN最低值SUM和STDEV统计标准偏差STDEVP填充统计标准偏差VAR统计方差VARP填充统计方差注意：(1)COMPUTE 或 COMPUTE BY 子句中的表达式，必须出现在选择列表中，并且必须将其指定为与选择列表中的某个表达式完全一样，不能使用在选择列表中指定的列的别名。(2)在COMPUTE或COMPUTE BY子句中，不能指定ntext、text和 image数据类型。(3)如果使用 COMPUTE BY，则必须也使用 ORDER BY 子句。表达式必须与在 QRDER BY 后列出的子句相同或是其子集，并且必须按相同的序列。例如，如果 ORDER BY 子句是：ORDER BY a, b, c 则 COMPUTE子句可以是下面的任意一个(或全部)： COMPUTE BY a, b, c 或 COMPUTE BY a, b 或 COMPUTE BY a(4)在 SELECT INTO 语句中不能使用 COMPUTE。因而，任何由 COMPUTE 生成的计算结果不出现在用 SELECT INTO 语句创建的新表内。数据视图6.创建视图： CREATE VIEW 视图名(列名1 ,n) AS 查询语句注意：(1) 定义视图的用户必须对所参照的表或视图有查询权限，即可执行SELECT语句。(2) 不能使用COMPUTE或COMPUTE BY子句。(3) 不能使用ORDER BY子句。(4) 不能使用INTO子句。(5) 不能在临时表或表变量上创建视图。修改视图：ALTER VIEW 视图名(列名1 ,n) AS 查询语句删除视图：DROP VIEW 视图名,n7. 注意事项：(1)只有在当前数据库中才能创建视图。(2)视图的命名必须遵循标识符命名规则，不能与表同名，且对每个用户视图名必须是惟一的，即对不同用户，即使是定义相同的视图，也必须使用不同的名字。(3)不能把规则、默认值或触发器与视图相关联。(4)不能在视图上建立任何索引，包括全文索引。(5)使用视图查询时，若其关联的基本表中添加了新字段，则必须重新创建视图才能查询到新字段。(6)如果与视图相关联的表或视图被删除，则该视图将不能再使用。8.小结SELECT语句语法格式SELECT 字段列表 INTO 目标数据表 FROM 源数据表或视图， WHERE 条件表达式 GROUP BY 分组表达式 HAVING 搜索表达式 ORDER BY 排序表达式 ASC|DESC COMPUTE 行聚合函数名1(表达式1),.n BY 表达式 ,.n 子句语法格式SELECTSELECT ALL|DISTINCTTOP n PERCENT 列1 ,.n1. * 所有列2. 表名 | 视图名 | 表别名.列名 指定列3. 列表达式 as 别名 | 计算字段名=表达式 列别名4. ALL | DISTINC 所有结果或去掉重复的结果5. TOP n PERCENT 前n条(n%)的结果FROM1.FROM 表1 AS 表别名1 | 视图1 AS 视图别名1 ，.n 2.FROM 表1 inner JION 表2 ON 条件表达式3.FROM 表1 LEFT OUTER JOIN 表2 ON 条件表达式4.FROM 表1 RIGHT OUTER JOIN 表2 ON 条件表达式5.FROM 表1 FULL OUTER JOIN 表2 ON 条件表达式6.FROM 表1 CROSS JOIN 表2 或 FROM 表1 ，表2WHEREWHERE 条件表达式1. 表达式 比较运算符 表达式 2. 表达式 AND|OR 表达式 或：NOT 表达式3. 表达式 NOT BETWEEN 表达式1 AND 表达式24. 表达式 NOT IN (表达式1, ,表达式n)5. 表达式 NOT LIKE 格式串 通配符：% _ ORDER BYORDER BY 表达式1 ASC| DESC ,nINTOINTO 目标数据表(如果要存到临时表里,则表名前要加#)GROUP BYGROUP BY 分组表达式 ,.n HAVING 搜索表达式COMPUTECOMPUTE 行聚合函数名1(统计表达式1) ,.n BY 分类表达式 ,.n UNION查询语句1 UNION ALL 查询语句2语句语法格式视图定义创建CREATE VIEW 视图名(列名1 ,n) AS 查询语句修改ALTER VIEW 视图名(列名1 ,n) AS 查询语句删除DROP VIEW 视图名,n数据操作插入INSERT INTO 表名视图名 (列名1,) Values (表达式1,) 修改UPDATE 表名视图名 SET 列名= 表达式 WHERE 条件删除DELETE 表名视图名WHERE 条件查询SELECT 字段列表 FROM 数据表视图，关于数据完整性创建check约束：ALTER TABLE ADD CONSTRAINT CHECK (约束内容)1.约束种类. NOT NULL（非空）约束 PRIMARY KEY（主键）约束 UNIQUE（惟一性）约束 CHECK（检查）约束 DEFAULT（默认）约束 FOREIGN KEY（外键）约束 2.非空约束：CREATE TABEL 数据表名 (列名 数据类型 CONSTRAIN 约束名 NULL | NOT NULL， )主键约束：CREATE TABEL 数据表名 (列名 数据类型 CONSTRAIN 约束名 PRIMARY KEY CLUSTERED | NONCLUSTERED ，)（说明：语法格式1定义单列主键，CLUSTERED和 NONCLUSTERED 分别代表聚集索引和非聚集索引。 ）语法格式2：CREATE TABEL 数据表名 (CONSTRAIN 约束名 PRIMARY KEY CLUSTERED | NONCLUSTERED (列名1,n),.)（说明：语法格式2定义多列组合主键，CLUSTERED和 NONCLUSTERED 分别代表聚集索引和非聚集索引。 ）唯一性约束：CREATE TABEL 数据表名 (列名 数据类型 CONSTRAIN 约束名 UNIQUE CLUSTERED | NONCLUSTERED ,.)（单列约束）语法格式2：CREATE TABEL 数据表名 (CONSTRAIN 约束名 UNIQUE CLUSTERED | NONCLUSTERED (列名1,n) ,.) （多列唯一性约束）CHECK约束：CREATE TABEL 数据表名 (列名 数据类型 CONSTRAINT 约束名 CHECK（逻辑表达式）,.) （说明：如果是在现有表中添加检查约束，可以设置WITH NOCHECK ，对现有数据强制约束约束检查）默认约束：语法格式1： CREATE TABEL 数据表名 (列名 数据类型 CONSTRAINT 约束名 DEFAULT 默认值 ,.) 语法格式2：CREATE TABEL 数据表名 (CONSTRAINT 约束名 DEFAULT 默认值 FOR 列,.) 外键约束：语法格式1：CREATE TABEL 数据表名 (列名 数据类型 CONSTRAIN 约束名 FOREIGN KEYREFERENCES 参照主键表 (参照列) ON DELETE CASCADE | ON UPDATE CASCADE ,.) （说明：语法格式1定义单列外键约束 。ON DELETE CASCADE表示级联删除，即父表中删除被引用行时，也将从引用表中删除引用行； ON UPDATE CASCADE表示级联更新，即父表中更新被引用行时，也将在引用表中更新引用行。 ）语法格式2： CREATE TABEL 数据表名 (CONSTRAIN 约束名 FOREIG