MSSQL 新手学习要点
快捷键
功能
快捷键
功能
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
显示/隐藏对象浏览器
数据类型
名称
取值范围
整数
bigint、
int、
smallint、
tinyint
(±922亿亿) 8
(±21亿) 4
(±32768) 2
(0-255) 1
位型
Bit
由0和1表示真、假。
货币型
money、smallmoney
(±922万亿)
(±21万)，精确到万分之一。
十进制
Decimal、Numeric
±1038-1，最大位数38位
浮点数
float、real
(±1.79E+308) (±3.40E+38)
日期时间
Datetime、smalldatetime
1753.1.1-9999.12.31,精确到3.33毫秒
1900.1.1-2079.12.31，精确到分钟
单字节字符
char/varchar/text
定/变长单字节字符,最长8000；
Unicod字符
nchar / nvarchar / ntext
定/变长双字节字符，最长4000；
二进制数据
Binary/varbinary/image
定/变长二进制数据，最长8000；变长二进制数据
特殊类型
timestamp
SQL 活动的先后顺序。
uniqueidentifier
全局唯一标识
数学函数
名称
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(字符表达式)
转换成字符串
convert(char(1),数值)
数值转字串
STR(数字表达式)
现在日期时间
GETDATE()
年
YEAR(日期型表达式)
月
MONTH(日期型表达式)
日
DAY(日期型表达式)
日期 部分
DATEPART(格式串, 日期型表达式)
日期 加
DATEADD(格式串,数值,日期)
日期 差
DATEDIFF(格式串,日期1,日期2)
系统函数
说明
APP_NAME
当前会话的应用程序名称
CURRENT_USER
当前的数据库用户。
USER_NAME
用户数据库用户名。
SESSION_USER
会话用户名
SYSTEM_USER
系统用户名
HOST_ID
工作站标识号。
HOST_NAME
工作站名称。
ISDATE
是否为有效的日期。
ISNULL
是否为NULL。
ISNUMERIC
是否为一个有效的数字类型。
全局变量
说明
@@ERROR
最后执行的 T-SQL 语句的错误代码。
@@TRANCOUNT
当前连接的活动事务数。
系统统计函数
说明
@@CONNECTIONS
连接次数。
@@CPU_BUSY
CPU 的工作时间
@@IDLE
闲置的时间
@@IO_BUSY
输入和输出的时间
@@TIMETICKS
返回一刻度的微秒
@@PACK_SENT
发送的数据包数
@@PACK_RECEIVED
接受的数据包数
@@PACKET_ERRORS
发生的数据包错误
@@TOTAL_WRITE
写入磁盘的次数
@@TOTAL_READ
读取磁盘的次数
@@TOTAL_ERRORS
磁盘读/写错误次数
运算符
含义
BETWEEN
在…之间。
LIKE
匹配(像)
AND
而且
EXISTS
存在
SOME
有些
OR
或者
ALL
所有(都)
IN
在…之内
NOT
否定
ANY
任一
更多:http://www.52mvc.com/showtopic-1187.aspx
流程控制语句
1．RETURN
RETURN的作用是无条件返回所在的批、存储过程和触发器。退出时，可以返回状态信息。在RETURN语句后面的任何语句不被执行。
RETURN语句的语法形式：RETURN [整型表达式]
2．PRINT和RAISERROR
PRINT语句的作用是在屏幕上显示用户信息。其语法形式为：
PRINT{’字符串’ | 局部变量| 全局变量}
RAISERROR语句的作用是将错误信息显示在屏幕上，同时也可以记录在NT日志中。其语法形式为：RAISERROR(错误号|错误信息, 错误的严重级别, 错误时的状态信息)。
3．复合语句(BEGIN...END)
其语法形式为： BEGIN
执行的SQL语句
END
4．CASE表达式
CASE [测试表达式]
{WHEN简单表达式0 THEN 结果表达式0}[,...n]
[ELSE结果表达式n]
END
5．判断语句(IF...ELSE)
语法：
IF 条件表达式
SQL语句1
[ELSE
SQL语句2]
6．循环语句(WHILE)
WHILE 条件表达式
SQL语句|复合语句
7．注释
多行注释：/*……*/
单行 注释：--
约束：约束名=约束类型_约束字段
ALTERTABLE stuInfo 添加主键约束（stuNo作为主键）
ADD CONSTRAINT PK_stuNo PRIMARY KEY(stuNo)
ALTERTABLE stuInfo 添加唯一约束（因为每人的身份证号全国唯一）
ADD CONSTRAINT UQ_stuID UNIQUE (stuID)
ALTERTABLE stuInfo 添加默认约束（如果地址不填，默认为“地址不详”）
ADD CONSTRAINT DF_stuAddress
DEFAULT ('地址不详') FOR stuAddress
ALTERTABLE stuInfo 添加检查约束，要求年龄只能在15－40岁之间
ADD CONSTRAINT CK_stuAge
CHECK(stuAge BETWEEN 15 AND 40)
ALTERTABLE stuMarks 添加外键约束（主表stuInfo和从表stuMarks建立关系，关联字段为stuNo）
ADD CONSTRAINT FK_stuNo
FOREIGN KEY(stuNo) REFERENCESstuInfo(stuNo)
系统存储过程
1. 由系统定义，存放在master数据库中
2. 类似C语言中的系统函数
3. 系统存储过程的名称都以“sp_”开头或”xp_”开头
4. 返回0(成功),1(失败)
系统存储过程
说明
sp_databases
列出服务器上所有数据库
sp_helpdb
报告有关数据库或者所有数据库的信息
sp_renamedb
更改数据库的名称
sp_tables
返回当前环境下可查询的对象列表
sp_columns
返回某个表列的信息
sp_help
返回某个表的所有信息
sp_helpconstraint
返回某个表的约束
sp_helpindex
返回某个表的所有索引
sp_stored_procedures
列出当前环境中的所有存储过程
sp_password
修改登录帐户的密码
xp_cmdshell
运行DOS命令
语句
语法格式
创建数据库*
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、设置[教学成绩管理数据库]自动收缩。
alterdatabase 教学成绩管理数据库set auto_shrink on
2、用sp_dboption设置[教学成绩管理数据库]自动收缩。
Sp_dboption'教学成绩管理数据库', 'autoshrink', 'true'
SELECT语句语法格式
SELECT 字段列表 [INTO 目标数据表]
FROM 源数据表或视图，…
[WHERE 条件表达式]
[GROUP BY 分组表达式 [HAVING 搜索表达式]]
[ORDER BY 排序表达式 [ASC]|[DESC]]
[COMPUTE 行聚合函数名1(表达式1)[,...n]
[BY 表达式 [,...n ]]]
子句
语法格式
SELECT
SELECT [ALL|DISTINCT][TOP n [PERCENT]] 列1 [,...n]
1. * 所有列
2. [{表名 | 视图名 | 表别名}.]列名 指定列
3. 列表达式 [as] 别名 | 计算字段名=表达式 列别名
4. [ALL | DISTINC] 所有结果或去掉重复的结果
5. [TOP n [PERCENT]] 前n条(n%)的结果
FROM
1.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 ，表2
WHERE
WHERE 条件表达式
1. 表达式 比较运算符 表达式
2. 表达式 AND|OR 表达式 或：NOT 表达式
3. 表达式 [NOT] BETWEEN 表达式1 AND 表达式2
4. 表达式 [NOT] IN (表达式1, [,…表达式n])
5. 表达式 [NOT] LIKE 格式串 通配符：% _ [ ] [^]
ORDER BY
ORDER BY 表达式1 [ ASC| DESC] [,…n]]
INTO
INTO 目标数据表
GROUP BY
[GROUP BY 分组表达式 [,...n ] [HAVING 搜索表达式]]
COMPUTE
COMPUTE 行聚合函数名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 数据表\视图，…
创建索引：
CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED]
INDEX index_name
ONtable_name (column_name…)
[WITHFILLFACTOR=x]
l UNIQUE表示唯一索引，可选
l CLUSTERED、NONCLUSTERED表示聚集索引还是非聚集索引，可选
l FILLFACTOR表示填充因子，指定一个0到100之间的值，
l 该值指示索引页填满的空间所占的百分比
功能
语法格式
自定义函数
创建
1.标量函数
CREATE FUNCTION [所有者].自定义函数名1 ([参数[…n])
RETURNS 返回参数的类型 AS
BEGIN
函数体
RETURN 函数返回的标量值
END
2.内嵌表值函数
CREATE FUNCTION [所有者].自定义函数名2([参数[…n]]
RETURNS TABLE AS
RETURN(SELECT查询语句)
删除
DROP FUNCTION [所有者].自定义函数名
执行
1.标量函数：函数名自定义函数名1出现在表达式中；
2.内嵌表值函数：select 列名[,...] from自定义函数名2。
存储过程
创建
CREATE PROCDURE 存储过程名
[@参数 参数的数据类型] [OUTPUT] [,...n] AS
任意数量的T-SQL语句
删除
DROP PROCDURE 存储过程名
执行
[EXECUTE] 存储过程名 [参数1，…，参数n ]
触发器
创建
CREATE TRIGGER 触发器名
ON 表名或视图名
{ [FOR | AFTER] | INSTEAD OF }
{ [INSERT] [,] [UPDATE] [,] [DELETE]}
AS
[ IF UPDATE(列名1) [{AND|OR} UPDATE(列名2)] [ ...n ] ]
任意数量的T-SQL语句
删除
DROP TRIGGER 触发器名
游标的使用方法
(1) 声明游标：declare 游标名 cursor for select语句；
(2) 打开游标：open 游标名；
(3) 处理数据：
l 移动当前行并读取数据：fetch 游标名 [into @变量名,…]
l 删除当前行数据：delete from 表或视图名 where current of 游标名
l 修改当前行数据：update from 表或视图名 set 列名=表达式,…
where current of 游标名
(4) 关闭游标：close 游标名；
(5) 释放游标：deallocate 游标名；
事务控制语句的使用方法
begin transaction -- 事务开始
…… -- A组语句序列
save transaction 保存点1 --定义保存点
…… -- B组语句序列
if @@error <> 0
rollback transaction 保存点1 --回滚到保存点1
else
commit transaction --提交A组语句，同时如果未回滚B组语句则提交B组语句。
声明游标：
declare 游标名 cursor
[local |global]
[forward_only | scroll]
[static |keyset | dynamic | fast_forward]
[read_only |scroll_locks | optimistic]
for select语句
[for update[of 列名 [,...n]]]
l 游标名：游标命名必须符合标识符规则，不能超过30 个字符。
l select语句：定义结果集的标准select语句，且不许用compute、compute by、for browse和into子句。
l local：游标的作用域是局部的[创建它的批处理、存储过程或触发器]。
l global：游标的作用域是全局的。在由连接执行的任何存储过程或批处理中，都可以引用该游标名称。该游标仅在连接断开时自动释放。
l forward_only 只进。仅支持next。
l Scroll 滚动支持：next、prior、first、last、absolute、relative
l static 静态 open时在tempdb创建临时表复本保存结果集。只提取，不许修改
l dynamic 动态记录值、顺序等在每次提取时都可能因其他用户的更改而变动。不支持 absolute 提取选项。
l keyset 键集 open时在tempdb创建keyset表，记录结果集中每条记录的关键字段值和顺序。
l fast_forward 快速向前 优化的 forward_only、read_only.与scroll、for_update、forward_only互斥
l read_only 只读 在 update 或 delete 语句的 where current of 子句中不能引用游标
l scroll_locks 滚动锁定 当滚动记录指针提取当前记录时，系统将会锁定该行，确保游标更新或删除的成功
l Optimistic 乐观 行自从被读入游标以来，如果已修改该行，尝试进行的更新或删除将失败
l for update [of 列,...]]更新 指定可更新的列。默认所有列
打开游标
语法格式：OPEN [GLOBAL] 游标名
当游标被打开时，行指针会指在第一行之前
u 打开游标后,如果 @@error=0表示打开操作成功
u 打开游标后,可用 @@cursor_rows返回游标记录数
n -m 游标被异步填充.-m是键集中当前的行数
n -1 游标为动态。符合条件记录的行数不断变化
n 0 没有符合的记录、游标没打开、关闭或释放
n n 游标已完全填充。n是在游标中的总行数
数据处理语法格式：
fetch [[next|prior|first|last| absolute n |relative n]
from] [global] 游标名
[into @变量名[, ...n ]]
功能：在查询记录集中移动指针并提取一行数据。
l First 第一行；
l Next 下一行；
l Prior 上一行；
l Last 最后一行；
l absolute n n>0, 第一行开始正数的第n行；
n<0, 最后一行开始倒数的第n行；
l relative n n>0, 从当前行开始正数的第n行；
n<0, 从当前行开始倒数的第n行。
注意：
l 查询结果用into子句写入局部变量，须先声明局部变量的类型和宽度，且与select 语句中相应列的顺序、类型和宽度一致。
l 第一次执行fetch next，则将获取游标中的第一行数据。
l 打开游标后第一次执行fetch prior，则得不到任何数据。
l 用@@fetch_status返回执行FETCH操作之后，当前游标指针的状态。状态值如下：
n 0 表示行已成功地读取。
n -1 表示读取操作已超出了结果集。
n -2 表示行在表中不存在。
语法格式：
update from 表或视图
set 列名=表达式,…
wherecurrent of 游标名
功能：修改当前行指定字段的值
delete from 表或视图
wherecurrent of 游标名
功能：删除游标名指定的当前行数据。
关闭游标：
打开游标时锁定与其关联的查询结果集。
关闭完游标后释放与游标关联的查询结果集。
语法格式：CLOSE [GLOBAL] 游标名
释放游标：
释放游标，即释放其占用系统资源。
语法格式：DEALLOCATE [GLOBAL] 游标名
事务属性：
原子性：对数据的修改，要么都完成，要么都取消。
一致性：事务完成时，保持数据的一致性、完整性。
隔离性：并行事务之间相互隔离。
持久性：事务完成后，对数据所做的所有修改就保存到数据库中。
(1)设置隐性事务开始模式：
l set implicit_transactions on 启动隐性事务模式；
l set implicit_transactions off 关闭隐性事务模式。
显式事务：是明确地用begin transaction 语句定义事务开始、用commit或 rollback 语句定义事务结束的事务。
隐式事务：是用set implicit_transactions on 不明显地定义事务开始，用 commit 或 rollback 语句明显地定义事务结束的事务。
(2)设置自动回滚模式：
l set xact_abort on 当事务中任一条语句产生运行时错误，整个事务将终止并整体回滚；
l set xact_abort off 当事务中语句产生运行时错误，将终止本条语句且只回滚本条语句
事务控制语句：
(1) begin transaction [事务名] 定义事务开始
(2) commit transaction [事务名] 提交事务使事务对数据库的修改有效。
(3) rollback transaction [事务名] 回滚事务使得事务对数据库的修改无效。
数据库备份：
BACKUP DATABASE teach
TO disk='c:\bak\teach_bak\teach.bak'
数据库还原：
RESTORE DATABASE teach
FROM disk='c:\bak\teach_bak\teach.bak'
