1、SQL SERVER2005 数据库笔记基础部分第一章 企业管理器1.1 数据表的管理 SQL Server2005 的数据类型一个数据库可以存放很多张表，而一张表是由行和列组成，每一行表示一个实体，而每一列表示实体的属性。表 1.1 SQL Server2005 的数据类型分类 备注和说明 数据类型 说明二进制数据类型存储非子符和文本的数据 Image 可用来存储图像Char 固定长度的非 Unicode 字符数据Varchar 可变长度非 Unicode 数据Nchar 固定长度的 Unicode 数据Nvarchar 可变长度 Unicode 数据Text 存储长文本信息文本数据类型字符
2、数据包括任意字母、符号或数字字符的组合Ntext 存储可变长度的长文本日期和时间 日期和时间在单引号内输入Datetime 日期和时间intsmallint整数数字数据 该数据仅包含数字，包括正数、负数以及分数floatreal数字货币数据类型 用于十进制货币值 Money Bit 数据类型 表示是/否的数据 Bit 存储布尔数据类型1.2 数据的完整性数据的完整性包括数据的可靠性和数据的准确性，创建表保证数据的完整性，其实就是创建约束，所谓创建约束就是保证数据的可靠和准确。在 SQL Server2005 中，数据的完整性包括：1. 实体完整性 指保证在一张表中不可以有两行完全相同。2. 域
3、完整性 指保证一张表中的数据是合法的数据。3. 引用完整性 指保证一张从表中的某列来自于另外一张主表中的列。1.3 创建约束1.主键所谓主键是指能够唯一的表示一列的列就叫做主键。选择主键的原则：（1） 尽量选择单个键作为主键（2） 尽量选择值更新较少的列作为主键。2. 检查约束为了满足域完整性，可以通过设定检查约束来保证。3. 外键为了满足引用完整性。所谓外键是指一张表（从表）中的一列数据必须取自于另外一张表（主表）中主键列的数据。我们将这一列称之为外键。当两张表存在主外键关系时,那么它们就具有以下特点：(1).当主表中没有对应的记录时，不能将记录添加到子表；(2).不能更改主表中的值，而导致
4、子表中的数据孤立存在；(3).子表中如果存在与主表对应的记录，则不能从主表中删除该行；(4).删除主表前，应该先删除子表。4.默认约束为某张表的某列添加默认值。就是默认约束。5.标识列标识列的值由系统生成一组有规律的数字。7.唯一约束唯一约束用于指定一个或者多个列的组合值具有唯一性，以防止在列中输入重复的值。唯一键允许为空，但是系统为保证其唯一性，最多只可以出现一个 NULL 值。当使用唯一约束时，需要考虑两个因素：(1).使用唯一性的约束的字段允许为空值(2).一个表中可以允许有多个唯一性约束。第二章 查询分析器2.1 SQL 和 T-SQL2.2 创建数据库T-SQL 创建数据库的语法：c
5、reate database 数据库名on primary()log on()示例：创建一个 Teacher 数据库，可以写成：create database TeacherGO2.3 删除数据库删除数据库的语法如下：drop database 数据库名比如要删除 Teacher 数据库，语法是：drop database TeacherGO2.4 创建表创建表的语法：create table 表名（字段 1 数据类型 属性 约束字段 2 数据类型 属性 约束字段 N 数据类型 属性 约束）其中，列的特征包括该列是否为空（NULL） 、是否是表示列、默认值、主键等。示例：创建一张学生信息表（S
6、tudentInformation）create table StudentInformation - 创建学生信息表(StudentID int not null, -学生学号， 不能为空StudentName varchar(10) not null, -学生姓名， 不能为空StudentAge int not null, -学生年龄， 不能为空StudentSex char(2) not null, -学生性别， 不能为空StudentAddress varchar(50) not null, -学生住址，可以为空)Alter table StudentInformation add c
7、onstraint default constraint;2.5 删除表删除表的语法为：drop table 表名示例：删除产品信息表（ProductInfo） ，其代码如下：drop table ProductInfoGO2.6 管理约束创建约束的目的：确保表中数据的完整性。常见的约束类型如下： 主键约束（primary key constraint） 唯一约束（unique constraint） 检查约束（check constraint ） 默认约束（default constraint ） 外键约束（foreign key constraint）2.7 添加约束在创建表的同时，也可以
8、在字段后面添加各种约束。添加约束的语法为：alter table 表名add constraint 约束名 约束类型 具体的约束说明示例：为学生信息表（StudentInfo）添加约束。代码如下：create table StudentInfo - 创建学生信息表(StudentID int not null primary key , -学生学号， 不能为空 ，主键StudentName varchar(10) unique not null, -学生姓名， 不能为空 ，唯一键StudentAge int not null check (StudentName=0 and StudentNa
9、me 大于= 大于或等于不等于! 非3.2 逻辑运算符表 3.2 逻辑运算符逻辑表达式 说明 示例AND 逻辑与 1 AND 1 = ; 1 AND 0 = 0; 0 AND 0 = 0;OR 逻辑或 1 OR 1 = 1; 1OR 0 = 1; 0 OR 0 = 0;NOT 逻辑非 NOT 1 = 0; NOT 0 = 1;3.3 SELECT 语句SELECT 语句的语法格式如下:SELECT FROMWHERE其中，WHERE 条件表达式不是必需的，如果没有限制条件，就查询返回所有的行，如果要查询返回数据表所有行和列的数据，就需要用到通配符“*” ，表示查询表中所有的列。如：SELECT
10、 * FROM 表名3.4 INSRT 语句INSERT 用于向数据表中插入数据。 INSERT 语句的语法格式如下：INSERT INTO VALUES其中，INTO 关键字是可选的，可以省略。表名是必需的，不可以省略。表中的列名可以省略；列名之间用逗号隔开，VALUES 里面的数据值也是一样，且各项数据的顺序和数据表中的列的顺序一致。示例：向学生信息表（StudentInfo）中插入一条记录。INERT INTO StudentInfo VALUES(101,JackyChen,52)插入数据时需要注意的几个问题： 每次执行 INSERT 语句必需出入正行数据，不能只在一行的部分列中插入值
11、。如果该列可以为空，可以不在该列插入数据，否则必须在该列插入数据。 VALUES 括号里面的数据值的数目必须与表中列的数目相同，并且数据类型、精度也必须与对应的列匹配。 插入字符类型和日期类型数据的时候，需要用单引号括起来。 插入的数据必须复合 CHECK 约束的要求。 虽然可以省略列名，但为了避免插入的数据的顺序发生错误，最好还是制定列名。如果指定了列名，对具有默认值的列和允许为空的列插入数据，就需要用到 DEFAULT 和 NULL 关键字。如下示例： INSERT INTO StuInfo(StuID,StuName,StuSex)VALUES(1,NULL,DEFAULT)3.5 插入
12、过行数据当向表中同时插入多行数据的时候，可以使用 SELECTUNION 来完成，例如向StuInfo 表同时插入多行数据，语句如下：INSERT StuInfo (StuID,StuName,StuSex）SELECT 1,张三,0 UNIONSELECT 2,李四,1 UNIONSELECT 3,王五,1 UNIONSELECT 4,赵六,0 UNIONSELECT 5,钱七,0 如果需要将一张数据表中的数据拷贝到另外一张新数据表中，可以使用 SELECT INTO 语句 例如：SELECT StuID,StuName,StuSex INTO Students FROM StuInfo 执
13、行上述 T-SQL 语句，将会创建一个表名是 Students 的新数据表，并且将 StuInfo 数据表里的 StuID、StuName、StuSex 三列做为新表的列。INSERTSELECT 语句同样也可以向数据表中插入多行数据，但不同的是，插入的表必须事先创建好，而不是执行 T-SQL 语句的时候创建 INSERT INTO Students（StuID,StuName,StuSex）SELECT StuID,StuName,StuSexFROM StuInfo 3.6 UPDATE 语句UPDATE 语句用于修改数据表中特定记录或字段的数据，语法格式如下：UPDATE SET WHE
14、RE 条件表达式 其中表名是指要修改的表；SET 子句给出要修改的列名以及修改后的更新值；WHERE 子句指定要修改的选择条件，当 WHERE 子句省略时，则修改表中的所有行。如：UPDATE StuInfo SET StuName=Andy WHERE StuID=1如果要修改数据表中多列的数据，可以在 SET 子句后面跟随多个列名以及更新值，中间用逗号隔开，如：UPDATE StuInfo SET StuName=Jacky,StuSex=3 WHERE StuID=1对考号是 1 的学生进行提分，在原来的分数上加 5 分：UPDATE StuMarks SET Score=Score+5
15、 WHERE ExamNO=13.7 DELETE 语句DELETE 语句用于删除数据表中的数据，语法格式如下：DELETE FROM WHERE 条件表达式如果要删除数据表中所有的信息，把 WHERE 条件语句省略即可：DELETE FROM DELETE 语句删除的是一正行数据，而不是删除某列，因此 DELETE 关键字和 FROM 关键字之间不能芳列名。第四章 数据查询4.1 设置字段别名语法格式如下：“原字段名 AS 字段别名”也可以写成“字段别名=原字段名”比如：SELECT StuName AS 姓名 , StuID AS 学号 FROM StuInfo4.2 使用 ALL 关键字
16、返回全部记录如果 SELECT 子句中没有任何关键字，则默认使用 ALL 关键字，在这种情况下，选择查询将返回符合条件的全部记录，而且允许在查询结果中包含重复记录。如：SELECT ALL StuID FROM StuInfo ORDER BY StuID4.3 使用 DISTINCT 关键字过滤重复记录当用 SELECT 语句查询的结果有可能存在重复记录的情况下，就在字段列表前面加上选择关键字 DISTINCT，如此就可以消除查询结果中的重复记录。如：SELECT DISTINCT StuName FROM StuInfo4.4 使用 TOP 关键字仅显示前面若干条记录如果在 SELECT
17、子句的前面加上 TOP n，则在查询的;结果中输出前面 n 条记录；如果在字段列表前面使用 TOP n PERCENT，表示在查询结果中显示前面占总记录数的百分比为n%的记录。如：SELECT TOP 5 * FROM StuInfoSELECT TOP 30 PERCENT * FROM StuInfo4.5 对查询的结果进行筛选把某一字段中内容在特定范围内的记录查询出来，称之为模糊查询。.范围运算符（判断表达式值是否在指定的范围）：BETWEEN.ANDNOT BETWEENANDSELECT StudentID, Score FROM SCore WHERE Score BETWEEN
18、60 AND 80列表运算符( 判断表达式是否为列表中的指定项) ：IN(项 1，项 2) NOT IN(项 1，项2)空值判断符( 判断表达式是否为空) ：IS NULL、IS NOT NULL逻辑运算符( 用于多条件的逻辑连接) ：NOT 、AND、OR模式匹配符( 判断值是否与指定的字符通配格式相符) ：LIKE 、NOTLIKE例如：SELECT * FROM StuInfo WHERE StuID LIKE ps%以上语句是查询学生 ID 以 ps 开头的记录。4.6 对查询结果进行排序1. 使用 OEDER BY 子句当要对查询的结果进行排序时，就需要在 SELECT 语句加上 O
19、RDER BY 子句。ORDER BY 子句中可以使用一个或多个排序要求，优先级次序从坐到右。排序的方向可以是升序(ASC)或降序(DESC).例如：查询学生成绩表（Marks）中的所有记录，按 score 字段升序排列和按降序排列SELECT * FROM StuInfo ORDER BY score ASCSELECT * FROM StuInfo ORDER BY score DESC2. ORDER BY 子句与 TOP 关键字一起使用用户可以通过 ORDER BY 子句与 TOP 关键字搭配使用来选取按照排序之后查询结果中前若干行或前百分比的数据。4.7 对查询结果进行计算1. 使用
20、 SUM 函数计算字段的累加和SUM 函数用于统计数值字段的总和，它只能用于数值型字段，而且 NULL 值将被忽略。SELECT SUM(score) FROM Marks -查询分数表中分数字段的总和2. 使用 AVG 函数计算字段的平均值SELECT AVG(score) FROM Marks3. 使用 MAX 和 MIN 函数计算字段的最大值和最小值SELECT MAX(score) FROM Marks 查询 Marks 表中 score 字段的最大值SELECT MIN(score) FROM Marks 查询 Marks 表中 score 字段的最小值4. 使用 COUNT 函数统
21、计记录的行数COUNT 函数用于统计字段中选取的项数或查询输出记录的行数。SELECT COUNT(*) FROM StuInfo -查询 StuInfo 表中一共有多少条记录4.8 对查询的结果分组1. 使用 GROUP BY 子句GROUP BY 子句指定将结果集内的记录分成若干个组来输出，每个组中的记录在指定的字段中具有相同的值。字段列表中的每个输出字段必须在 GROUP BY 子句中出现或者用在某个聚合函数中。使用 GROUP BY 子句时，如果在 SELECT 子句的字段列表中包含有聚合函数，则针对每个汇总值，从而实现对查询结果的分组统计。SELECT type,COUNT(type
22、) AS total FROM BOOKING GROUP BY type 查询图书的种类和每个种类的个数2. 使用 HAVING 子句HAVING 子句用于指定的组或聚合的搜索条件，该子句通常与 GROUP BY 子句一起使用，如果不使用 GROUP BY 子句，则 HAVING 子句的行为与 WHERE 子句一样，不同的是，WHERE 子句搜索条件在进行分组之前应用，而 HAVING 搜索条件在分组操作之后应用，HAVING 子句可以包含聚合函数，HAVING 子句可以引用字段表中出现的任意项。HAVAING 子句可以随运算聚合后的数据进行筛选。SELECT type,MAX(price)
23、 FROM BOOKING GROUP BY type HAVING MAX(price)20-按书籍的分类，查出每种书籍的最大值大于 204.9 使用 SELECTINTO.语句SELECT.INTO语句用于把查询结果放到一个新表中。其中 INTO 后面直接跟上新建表的名字。新表列由 SELECT 子句中制定的列构成，新表中的数据行是由 WHERE 子句指定的。-产生一个新表 book，且该新表拥有 BOOKING 表的所有数据SELECT * INTO book FROM BOOKINGSELECT * FROM book因此，如果在 WHERE 子句中指定一个值为 FALSE 的条件，则
24、可以使用SELECT.INTO.语句来创建一个表名不同，但结构和数据类型与原表相同的新表。如：SELECT * INTO book FROM BOOKING WHERE 170BEGINPRINT 成绩优秀,前三名的成绩为： SELECT TOP 3 * FROM STUMARKS ORDER BY WRITTENEXAM DESCENDELESBEGINPRINT 成绩较差，后三名的成绩为： SELECT TOP 3 * FROM MARKS ORDER BY WRITTENEXAMEND242 WHILECONTINUEBREAK 语句WHILEBEGINBREAKCONTINUE命令行或
25、程序ENDWHILECONTINUEBREAK 语句的使用问题：在 StuScore 表中如果学生的平均成绩没有达到 80 分，则对每个学生进行加分，每次给数学成绩加 1 分，看平均分是否超过 80 分，如果没有超过，则继续加，如此反复，直到平均分达到 80 分。分析：1.先计算 StuScore 表中的学生的平均分。2.如果平均分没有超过 80 分，就执行加分操作。3.循环判断。示例：DECLARE Scores FLOAT -定义一个局部变量来接受平均分SELECT Scores =AVG(CHINESE+ENGLISH+MATH)/3) FROM StuScore -对平均分赋值WHIL
26、E (Scores THENWHEN THENELSEEND问题：将 StuScore 表中的学生成绩用优、良、中、差显示优：90 分以上，并且小于 100良：7090 分中：6079 分差：059 分分析：根据每个学生的成绩进行多分支判断SELECT ID,语文 =CASEWHEN CHINESE90 AND CHINESEage GO 方法二：使用子查询实现SELECT * FROM stuInfoWHERE stuAge( SELECT stuAge FROM stuInfo where stuName=李斯文)GO 子查询在 WHERE 语句中的一般用法：SELECT FROM 表 1
27、 WHERE 字段 1 (子查询) 外面的查询称为父查询，括号中嵌入的查询称为子查询 UPDATE、INSERT、DELETE 一起使用，语法类似于 SELECT 语句 将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个 3.1.2 使用子查询替换表连接问题：查询笔试刚好通过（60 分）的学员。方法一：SELECT stuName FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExam=60GO方法二：使用子查询SELECT stuName FROM stuInfo WH
28、ERE stuNo=(SELECT stuNo FROM stuMarks WHERE writtenExam=60)GO3.2 使用 IN 和 NOTIN 子查询语句问题：查询笔试成绩大于 80 分的学生信息SELECT * FROM stuMarks WHERE writtenExam80使用 IN 关键字在 stuInfo 中找出学号在上述查询结果中的信息：SELECT * FROM stuInfo WHERE stuNo IN (SELECT stuNo FROM stuMarks WHERE writtenExam80)使用 NOTIN 子句完成子查询问题：查询未参加考试的学生名单。
29、分析：加上否定的 NOTIN 即可：SELECT stuName FROM stuInfo WHERE stuNo NOT IN (SELECT stuNo FROM stuMarks)GO查询结果如下：3.3 使用 EXISTS 和 NOT EXISTS 完成子查询例如：检测数据库是否存在。IF EXISTS (SELECT * FROM StudentDatabase WHERE name=stuInformation)DROP DATABASE stuInformationEXISTS 子查询的语法：IF EXISTS (子查询)语句-如果子查询的结果非空，则 EXISTS(子查询) ，
30、将返回真(true)，否则返回假(false).-EXISTS 也可以做为 WHERE 语句的子查询，但一般都能用 IN 子查询替换。示例：问题：查询本次考试，本班如果有人的笔试成绩达到 80 分以上的，则每人加 2 分；否则每人允许提 5 分。分析：是否有人的成绩在 80 分以上，可采用 EXISTS 检测IF EXISTS(SELECT * FROM stuMarks WHERE writtenExam80)BEGINPRINT 本班有人的成绩大于 80 分，每人加 2 分，加分后的成绩为:UPDATE stuMarks SET writtenExam=writtenExam+2SELEC
31、T * FROM stuMarksENDELSEBEGINPRINT 没有人的成绩在 80 分以上，每人加 5 分，加分后的成绩为：UPDATE stuMarks SET writtenExam = writtenExam+5SELECT * FROM stuMarksEND查询结果：3.4 使用 NOT EXISTS 完成子查询示例：问题：查询本次考试，如果没有一人通过考试(笔试和机试都大于 60 分) ，则试题偏难，每人加 3分，否则每人加 1 分。分析：没有一人通过考试，也就是说笔试和机试都大于 60 分不存在，则可采用 NOT EXISTS 检查IF NOT EXISTS(SELECT
32、 * FROM stuMarks WHERE writtenExam60 AND LabExam60)BEGINPRINT 无人通过考试，试题偏难，每人加 3 分，加分后的成绩为：UPDATE stuMarks SET writtenExam = writtenExam+3,LabExam=LabExam+3SELECT * FROM stuMarksENDELSEBEGINPRINT 考试成绩一般，每人只加 1 分，加分后的成绩为：UPDATE stuMarks SET writtenExam=writtenExam+1,LabExam=LabExam+1SELECT * FROM stuM
33、arksENDGO运行结果：T-SQL 的综合应用示例：有学生信息表(stuInfo)和学生成绩表(stuScore) 两张表问题：1.统计出本次考试的缺考情况、应到人数、实到人数。2.比较笔试平均分和机试平均分，较低者进行循环提分，但提分后的最高分不能超过 97 分，加分后重新统计通过情况。3.统计功过率。代码：-先提取本次考试的所有数据SELECT COUNT(*) FROM stuInfoSELECT COUNT( *) FROM stuMarks-统计缺考情况SELECT COUNT(*) AS 应到人数 , (SELECT COUNT(*) FROM stuMarks) AS 实到人
34、数,(COUNT(*)-(SELECT COUNT(*) FORM stuMarks) FROM stuInfoSELECT stuName AS 姓名, stuSex AS 性别, 机试成绩=CASEWHEN LabExam=LabExam IS NULL THEN 缺考ELSECONVERT(VARCHAR(10),LabExam)END,笔试成绩=CASEWHEN writtenExam=writtenExam IS NULL THEN 缺考ELSECONVERT (VARCHAR(10),writtenExam)END,是否通过=CASEWHEN writtenExam=60 AND
35、LabExam=60 THEN 是ELSE 否FROM newtableEND-比较笔试和机试的平均分，决定加哪一科DECLARE AVGwrit FLOATDECLARE AVGlab FLOATSELECT AVGwrit=AVG(writtenExam) FROM newtable WHERE writtenExam IS NULL SELECT AVGlab=AVG(LabExam) FROM newtable WHERE LabExam IS NULLIF AVGwrit97BREAKENDELSEBEGINUPDATE newtable SET LabExam=LabExam+1I
36、F (SELECT MAX(LabExam) FROM newtable)97BREAKEND-通过率SELECT 总人数=COUNT(*) , 通过人数=SUM(writtenExam+LabExam),通过率=(CONVERT(VARCHAR(5),AVG(writtenExam+LabExam)*100)+%) FROM newtableGO第四章事务和游标5.1 事务的特点事务必须有个特点：.原子性：事务内的所有工作是不能再分的操作，它不存在只有一部分得到完成的情况.一致性：事务内的任何操作都不能违反数据库的任何约束或规则，事务完成后所有的数据必须都是正确的.隔离性：事务之间是相互隔离
37、的。如果有两个事务对同一个数据库对象进行操作，任何一个事务看到的所有内容要么是其它事务完成前的状态，要么是完成之后的状态，它不可能出现中间状态。.持久性：事务完成后，该事务的数据就永久的被保存在数据库对象中。5.2 事务处理事务的开始BEGIN TRANSACTION语句，它标记一个本地事务的开始，并将TRANSACTION全局变量（用来记录事务的数目）的值加.COMMIT TRANSACTION语句，它用于提交事务。标记一个显式事务或隐式事务的结束。ROLLBACK TRANSACTION语句，它用于事务的回滚。-下面是一个关于银行转账业务的事务示例：Create database Bank
38、use Bankcreate table banks(customerName varchar(10) not null,customerMoney money not null,)insert into banks values(JackyChen,2000)insert into banks values(袁紫衣,1500 )select * from banks-开始一个事务begin transaction banks-定义一个用于记录错误的变量declare error int set error=0-从 JackyChen的账户中转到袁紫衣的账户上去,那么JackyChen 就少，
39、袁紫衣账户就多update banks set customerMoney=customerMoney-300where customerName=JackyChenset error=error+error-袁紫衣的账户增加update banks set customerMoney=customerMoney+300where customerName=袁紫衣 set error=error+errorif error70print 本班成绩：优秀elseprint 本班成绩：较差print =print 没有通过考试的学生名单：select stuName,stuInfo.stuNo,writtenExam,LabExamfrom stuInfo inner join stuMarks on stuInfo.stuNo=stuMarks.stuNowhere writtenExam60 or LabExam60GO
