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数据类型

主键用来实施实体完整性约束

第三章（SQLServer数据管理）

1． 条件表达式

（1）      常量：表示单个指定数据值的符号。一个常量由一个或多个字母、数字字符（a~z、A~Z、数字0~9）或符号（！[email protected]#等）组成。字母和datetime需要用引号括起来，而二进制字符串和数字常量则不需要。

（2）      列名：表中列的名称，表达式中仅允许使用列的名称。

（3）      {一元运算符}：仅有一个操作数的运算符，其中“+”表示正数，“-”表示负数，“~”表示补数预算符。

（4）      {二元运算符}：将两个操作数组合执行操作的运算符。二元运算符可以是算术运算符、赋值运算符（=）、位运算符、比较运算符、逻辑运算符、字符串串联（或连接）运算符（+）或一元运算符。

比较运算符

例如：

       Price　＞　１００

       Namelike ‘李%’

       Grade<> ‘3’ 

通配符

例如：

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,[email protected],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类内部函数分别是：字符串函数、日期函数、数学函数、系统函数。

部分常用的字符串函数

部分常用的日期函数

部分常用的数学函数

部分常用的系统函数

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