MSSQL基础拾掇二
T-SQL语言主要有：
一：DML(数据操作语言)：用来查询、插入、删除和修改数据库中的数据，如select、insert、update、delete等。
二：DCL(数据控制语言)：用来控制数据库组件的存取许可、存取权限等，如grant、revoke等。
三：DDL(数据定义语言)：用来建立数据库、数据库对象和定义其列，大部分是以create开头的命令，如createtable、create view 、drop table等。
第一章：（SQLServer数据库基础）
1．用数据库来管理数据，将使数据的存储、检索变得更安全和高效。
2．SQL Server 2005是 Microsoft 公司提供的关系型数据库管理系统，SQL Server也是当今流行的数据库。
3．数据库是表和数据库访问对象的集合，其中表分类存储了不同的实体信息，每一行数据对应一个实体的描述信息。
4．数据用余是指数据库中存在一些重复的数据；数据完整新是指数据库中的数据能够正确地反映实际情况。数据库中允许有一些数据用余，但是要保持数据的完整性。
5．SQL Server Management Studio 是SQL Server 2005 最常用的操作环境，能够执行对数据库的日常管理操作和数据查询。
6．连接SQL Server 之前应先启动SQL Server 服务，建库之前建好使用该数据库的用户。
7．SQL Server 数据库的物理文件包括数据文件和日志文件两部分，在创建数据库时指定。
第二章（SQLServer数据库表管理）
为某一列设置check约束
例如：0<score and score < 100 或 SEmail like ‘%@%’ %表示任意多个字符。
1． SQL Server创建表的过程是规定数据列的属性的过程，同时也是实施数据完整性（包括实体完整性、引用完整性和域完整性等）的过程。
2． 实体完整性数据行不能有重复，每一行数据都由主键来唯一确定。
3． 域完整性实现了对输入到特定列的数值的限制。
4． SQL Server中存在5种约束，分别是主键约束，外键约束、检查约束、默认约束和唯一性约束。
5． 创建数据库表需要确定表的列名、数据类型、是否允许为空，还需要确定主键、必要的默认值、标示列和检查约束。
6． 如果建立了主表和子表的关系，则：
A：子表中的相关项目的数据，在主表中必须存在。
B：主表中相关项的数据更改了，则子表对应的数据项也应当随之更改。
C：在删除子表之前，不能够删除主表。
7． 导入和导出数据 实际上不仅仅可以完成数据库和文件格式的格式转换，还可以在不同的数据库之间进行数据传输 下面是把数据库Pubs表Sales中的数据导出，并保存文本文件。
（1） 在数据库右键菜单中选择“任务”→ “导出数据”选项。
（2） 选择要从何处取得数据。可以选择SQL Server自身，并在下方选择pubs数据库。
（3） 确定把数据导出到什么位置，这时候可以在上方的目标列表框中选择“平面文件目标”选项，然后在下方输入文件的名称，并确定文件相关选项。
（4） 选择是否用一条查询语句进行复制，单击“下一步”按钮。
（5） 选择表名并设置文本文件的格式。
（6） 确定是否立即运行，是否保存该转换设置，单击“下一步”按钮一直到完成转换。
SQLServer 数据类型
|
分 类 |
备注和说明 |
数据类型 |
说明 |
|
二进制数据类型 |
用来存储非字符和文本的数据 |
binary |
固定长度的二进制数据 |
|
varbinary |
可变长度的而进驻数据 |
||
|
image |
可用来存储图像 |
||
|
文本数据类型 |
字符数据包括任意字母、符号或数字字符的组合 |
char |
固定长度的非Unicode字符数据，最大长度为8000个字符 |
|
varchar |
可变长度的非Unicode数据 |
||
|
nchar |
固定长度的Unicode数据 |
||
|
nvarchar |
可变长度的Unicode数据 |
||
|
text |
存储长文本信息 |
||
|
ntext |
存储可变长度的长文本 |
||
|
日期和时间 |
日期和时间在单引号内分别输入 |
datetime |
日期和时间 |
|
数字数据 |
该数据仅包含数字，包括正数、负数以及分数 |
int smallint tinyint bigint |
整数 |
|
float |
浮点数 |
||
|
real |
|||
|
货币数据类型 |
货币数据类型用于十进制货币值，并且精确到小数点后面4为数字 |
Money |
|
|
Bit数据类型 |
表示是/否的数据，只有两种选择；如婚否、是否定购等。在SQL Server中用1和0表示，一般1表示是，0表示否 |
Bit |
存储布尔数据类型 更多:http://www.52mvc.com/tags.aspx |
主键用来实施实体完整性约束
第三章（SQLServer数据管理）
1． 条件表达式
（1） 常量：表示单个指定数据值的符号。一个常量由一个或多个字母、数字字符（a~z、A~Z、数字0~9）或符号（！、@、#等）组成。字母和datetime需要用引号括起来，而二进制字符串和数字常量则不需要。
（2） 列名：表中列的名称，表达式中仅允许使用列的名称。
（3） {一元运算符}：仅有一个操作数的运算符，其中“+”表示正数，“-”表示负数，“~”表示补数预算符。
（4） {二元运算符}：将两个操作数组合执行操作的运算符。二元运算符可以是算术运算符、赋值运算符（=）、位运算符、比较运算符、逻辑运算符、字符串串联（或连接）运算符（+）或一元运算符。
比较运算符
|
运 算 符 |
含 义 |
|
= |
等于 |
|
> |
大于 |
|
< |
小于 |
|
>= |
大于或等于 |
|
<= |
小于或等于 |
|
<> |
不等于 |
|
！ |
非 |
例如：
Price ＞ １００
Namelike ‘李%’
Grade<> ‘3’
通配符
|
通 配 符 |
解 释 |
示 例 |
|
‘_’ |
一个字符串 |
A like ‘C_’ |
|
% |
任意长度的字符串 |
B LIKE ‘CO_%’ |
|
[] |
括号中所指定范围内的一个字符 |
C LIKE ‘9W0 [1-2]’ |
|
[^] |
不在括号中所指定范围内的任意一个字符 |
D like ‘9w0[^1-2]’ |
例如：
Telcode like ‘13[5-9][0-9][0-9][0-9][0-9]’
2． 逻辑表达式
T-SQL支持的逻辑运算符有and、or和not。
And和or运算符连接条件、not否定条件。And 连接条件，并且仅当两个条件都为真时才返回true。Or也有连接两个条件，但只要其中一个为真就返回true。
当一个语句中使用了多个逻辑运算符是，首先求not的值，然后求and的值，最后再求or的值。
3． 使用T-SQL插入数据
（1） 一次插入一条记录
语法：
Insert [into] <.表名> [列名] values<值列表>
例：
Insert into students (sname,saddress,sgrade,semail,ssex)
Values(‘张庆擦’,’上海松江’,6,’ZQC@Sohu.com’,0)
（2）一次插入多条记录
要想向表中插入多条记录时，用：
Insert [into] 表名
Select 要插入的数据, 要插入的数据, 要插入的数据union
Select 要插入的数据, 要插入的数据, 要插入的数据 union
Select 要插入的数据, 要插入的数据, 要插入的数据
切记select后面不要加括号
(3）通过select into 语句将现有表中的数据添加到新表中
例1：
Insert into tongxunlu (姓名，地址，电子邮件) selectsname,saddress,semail from students
注意：
A．查询得到的数据个数、顺序、数据类型等，必须与插入的项保持一致。
B．tongxunlu表必须预先创建好，并且具有姓名、地址和电子邮件三个字段。
例2：
Selectstudents.sname,students.saddress,students.semail into tongxunlu
From students
Tongxunlu表不能预先存在。是执行查询语句是创建的。
4． 使用T-SQL更新数据
语法
Update <表名> set <列名=更新值> [where <更新条件>]
（1） Set 后面可以紧随多个数据列的更新值，不限一个。
（2） Where子句是可选的，用来限制条件。如果不限制，则整个表的所有数据行将被更新。
例：
Update studentsset ssex = 0 ;
Update scoresset scores = socres + 5 where scores <=95
主键列允许更新，但是主键列不允许更新成相同的值。
5． 使用T-SQL删除数据
语法 delete from <表名> [ where <删除条件>]
例：
Delete fromstudents where sname = ‘张青裁’
6． 使用Truncate table删除数据
Truncate table 用来删除表中的所有行，功能上它类似于没有where 子句的delete语句。
例：
Truncate tablestudents
Truncate table 比delete执行速度快，而且使用的系统资源和事务日志资源更少。
Truncatetable 删除表中的所有行，但是表结构、列、约束、索引等不会被改动。
Truncatetable不能用于有外键约束引用的表，这种情况下，需要使用delete语句。
Truncate table 删除表中的所有行后，如果表中存在标识列，则标识列从所设置的重新开始排列，而delete是从上一次的标示后继续累加。
第四章（数据查询<一>）
1． 使用select语句进行查询
语法 select <列名> from <表名> [where <查询条件表达式>] [order by <排序的列名> [asc 或desc]]。
Orderby 是用来排序的。
例：select scode as 学员编号,sname as 学员姓名,saddress as 学员地址
Fromstudents
Wheresaddress <> ‘河南新乡’
在SQL 语句中采用“is null”或者“is not null”来判断是否为空行。
Select snamefrom students where smail is null
在T-SQL中，限制行数使用Top关键字来约束。
例:
Select top 5sname,saddress from students where ssex = 0
还有一种例如 要提取20%的女生数据。
Select top 20percent sname,saddress from students where ssex = 0
2． 查询排序
如果需要按照一定的顺序排列查询语句选中的行，则需要使用order by子句，并且排序可以是 升序（asc）或者降序(desc)。如果不指定asc或者desc ，记录集按asc 升序排序。
例：
Select studentId as 学员编号，（score*0.9 + 5）as 综合成绩
From score
Where （score * 0.9+5）> 60
Order by score
3． 在查询中使用函数
SQL Server提供了4类内部函数分别是：字符串函数、日期函数、数学函数、系统函数。
部分常用的字符串函数
|
函 数 名 |
描 述 |
举 例 |
|
CharIndex |
用来寻找哦一个指定的字符串在另一个字符串中的起始位置 |
Select charindex (‘accp’,’my accp course’,1) 返回：4 |
|
Len |
返回传递给它的字符串长度 |
Select len(‘SQL Server课程’) 返回：12 |
|
Upper |
把传递给它的字符串转换为大些 |
Select upper(sql server课程) 返回：SQL SERVER课程 |
|
Ltrim |
清除字符左边的空格 |
Select ltrim(‘ 周志宇 ’) 返回：’周志宇 ’(后面的空格保留) |
|
Rtrim |
清除字符右边的空格 |
Select rtrim(‘ 周志宇 ’) 返回：’ 周志宇’(前面的空格保留) |
|
Right |
从字符串右边返回指定数目的字符 |
Select right(‘买卖提.吐尔松’,3) 返回：吐尔松 |
|
Replace |
替换一个字符串中的字符 |
Select replace(‘莫乐可切.杨可’,’可’,’兰’) 返回：莫乐兰切.杨兰’ |
|
Stuff |
在一个字符串中，删除指定长度的字符，并在该位置插入一个新的字符串 |
Select stuff (‘ABCDEFG’,2,3,’我的音乐我的世界’) 返回：A我的音乐我的世界EFG (2代表起始位置，3代表删除字符的个数) |
部分常用的日期函数
|
函 数 名 |
描 述 |
举 例 |
|
GetDate |
取得当前的系统日期 |
Select getdate() 返回：今天的日期 |
|
DateAdd |
将指定的数值添加到指定的日期部分后的日期 |
Select dateadd(mm,4,’01/01/99’) 返回：以当前的日期格式返回05/01/99 |
|
DateDiff |
两个日期之间的指定日期部分的区别 |
Select datediff(mm,’01/01/99’,’05/01/99’) 返回：4 |
|
DateName |
日期中指定日期部分的字符串形式 |
Select dataname (dw,’01/01/2000’) 返回：Saturday |
|
Datepart |
日期中指定日期部分的整数形式 |
Select datepart(day,’01/15/2000’) 返回：15 |
部分常用的数学函数
|
函 数 名 |
描 述 |
举 例 |
|
Abs |
取数值表达式的绝对值 |
Select abs(-43) 返回：43 |
|
Ceiling |
取大于或等于指定数值、表达式的最小整数 |
Select ceiling(43.5) 返回：44 |
|
Floor |
取小于或等于指定表达式的最大整数 |
Select floor(43.5) 返回：43 |
|
Power |
取数值表达式的幂值 |
Select power(5,2) 返回：25 |
|
Round |
将数值表达式四舍五入为指定精度 |
Select round(43.543,1) 返回：43.5 |
|
Sign |
对于正数返回+1，对于负数返回-1，对于0返回0 |
Select sign（-43） 返回：-1 |
|
Sqrt |
取浮点表达式的平方根 |
Select sqrt(9) 返回：3 |
部分常用的系统函数
|
函 数 名 |
描 述 |
举 例 |
|
Convert |
用来转变数据类型 |
Select convert(varchar(5),12345) 返回：字符串12345 |
|
Current_User |
返回当前用户的名字 |
Select current_user 返回：你登录的用户名 |
|
DataLength |
返回用于指定表达式的字节数 |
Select datalength(‘中国A联盟’) 返回：9 |
|
Host_Name |
返回当前用户所登录的计算机名字 |
Select host_name() 返回：你所登录的计算机的名字 |
|
System_User |
返回当前所登录的用户名称 |
Select system_user 返回：你当前所登录的用户名 |
|
User_Name |
从给定的用户ID返回用户名 |
Select user_name(1) 返回：从任意数据库中返回“dbo” |
5． 数据查询的案例分析
(1)某公司印了一批充值卡，卡的密码是随机生成的，现在出现了这个问题：
卡里面的“O和0”、“i和1”，用户反映说看不清，公司决定，把存储在数据库中的密码中所有的“O”都改成“0”，把所有的“i”都改成“1”；
数据库名：card； 密码字段名：password
Update card setpassword = replace( replace(password,’O’,’0’),’I’,’1’);
(2)在数据库表中有以下字符数据，如：
13-1、13-2、13-3、13-10、13-100、13-108、13-18、13-11、13-15、14-1、14-2
现在希望通过SQL语句进行排序，并且首先要按照前半部分的数字进行排序，然后再按照后半部分的数字进行排序，输出要排成这样：
13-1、13-2、13-3、13-10、13-11、13-15、13-18、13-100、13-108、14-1、14-2
数据库表名：SellRecord；字段名：ListNumber
SelectListNumber from SellRecord order by convert(int, left(ListNuber,charindex(’-’, ListNumber)-1)),convert（int,stuff(ListNumber,1,charindex(’-’,ListNumber),’’)）
第五章（数据查询<二>）
1． 模糊查询
a) 使用like进行模糊查询
例：
Select * fromstudents where sname like ‘张%’;
Select * fromcard where id like ‘00[^8]%[A,C]%’
b) 使用between在某个范围内进行查询
例：
Select * from score where score between 60 and 80;
Select * from sales where ord_date not between ‘1922-8-1’and ‘1992-8-1’
Not是对限制条件“取反”操作
c) 使用in在列举值内进行查询
Select sname as 学员姓名 from students where address in(‘北京’,’广州’,’上海’)
Order bysaddress
注：还可以把in关键字和not关键字合起来用，这样可以得到所有不匹配列举值的行。
2． SQL Server中的聚合函数
a) Sum 返回表达式中所有数值的总和。Sun只能用于数字类型的列。
例：
Selectsum(ytd_sales) from titles where type = ‘business’
注：这种查询只能返回一个数值，不能够直接与可能返回多行的列一起使用来进行查询。
b) Avg 函数返回表达式中所有数值的平均值，也只能用于数字类型的列。
例：
Selectavg(score) as 平均成绩 fromscore where score >= 60
c) Max和Min
Max返回表达式中的最大值，Min返回表达式中的最小值，它们都可以用于数字型、字符型以及日期/时间类型的列。
例：查询平均成绩、最高分、最低分的语句如下
Selectavg(score) as 平均成绩 ,max(score)as 最高分,min(score)as 最低分 from scorewhere score >= 60
d) Count返回提供的表达式非空值的计数，Count可以用于数字和字符类型的列。
可以使用星号（*）作为count的表达式。使用*可以不必指定的列而计算所有的行数。
例：
Select count(*)as 及格人数 fromscore where score >= 60
3． 分组查询
a) 使用group by 进行分组查询
i. 如果要统计内部测试的成绩表，则学员的编号、内部测试的编号不能存在重复，两项都重复的行的分数值要取平均值。因此，这是一个既按照学员编号、又按照内部测试编号进行分组的查询。SQL语句如下：
Select studentIdas 学员编号，courseIDas 内部测试 ,avg(score) as 内部测试平均成绩 fromscore group by studentID,courseID
在使用group by 关键字时，在select 列表中可以指定的项目是有限制的，
select语句中仅允许以下几项：
1． 被分组的列。
2． 为每个分组返回一个值的表达式，例如用一个列名作为参数的聚合函数。
b) 使用having子句进行分组筛选
i. 对分组后的条件进行筛选必须使用having子句。说明是在分组的基础上进行筛选。
例：
Select studentIdas 学员编号,courseIdas 内部测试,avg(score)as 内部测试平均成绩 fromscore group by studentID,courseId having count(score)>1
注：在select语句中，where ,group by , having子句和统计函数的执行次序如下：where子句从数据源中去掉不符合搜索条件的数据；group by 子句搜集数据行到各个组中，统计函数为各个组计算统计值；having子句去掉不符合其组搜索条件的各组数据行。
4． 多表联接查询
a) 多表联接查询的分类：内连接、外连接、交叉连接等类型。
i. 内联接 是最典型、最常用的联接查询，它根据表*同的列来进行匹配，特别是两个表存在主外键关系时通常会使用到内连接查询。
内联接使用Inner Join关键字来进行表之间的关联。
ii. 外联接
1. 左外联接：Left Join 或Left Outer Join
2. 右外联接：Right Join或Right Outer Join
3. 完整外联接： Full Join或FullOuter Join
iii. 交叉联接 cross join
b) 内联接查询
i. 在where子句中指定联接条件
例：
SelectStudents.SName,Score.CourseID,Score.Score
FromStudents,Score
WhereStudents.Scode = Score.StudentID
ii. 在from子句中使用join…on
例：
Select S.SName,C.CourseID,C.Score
From Students as S inner join Score as C
On (S.Scode = C.StudentID)
SQL Server执行Inner Join联接的速度跟执行第一种“在where子句中指定联接条件”查询的速度是不一样的。
联接涉及三表或三表以上的查询例子如下：
SelectS.SName as 学员姓名,CS.CourseNameas 课程名称, C.Scoreas 考试成绩
FromStudents as S
InnerJoin Score as C on (S.SCode = C.StudentID)
InnerJoin Course as CS on (CS.CourseID = C.CourseID)
c) 外联接查询
i. 左外联接查询 例子如下:
SelectS.SName,C.CourseID,C.Score
From Students asS
Left out joinScore as C
On S.SCode =C.StudentID
ii. 右外联接查询 例子如下：
SelectTitles.Title_id,Title.Title,Publishers.Pub_name
From titles
Right out joinPublishs on Titles.Pub_id = Publishers.Pub_id