SQL Server 2012 案例教程（贾祥素）——学习笔记

第2章 SQL Server 2012概述

1、SQL（Structed Query Language），结构化查询语言。

2、SSMS（SQL Server Mangement Studio），SQL Server 2012的操作环境。

3、连接SQL Server之前应先启动SQL Server服务，即SQL Server（MSSQLSERVER）：

   方法1 开始--所有程序--Microsoft SQL Server 2012--配置工具--SQL Server配置管理器。

   方法2 控制面板--系统和安全--管理工具--服务。【 √ 】

4、登录账户：

   (1) 创建：“Windows身份验证”登录--对象资源管理器--安全性--右击登录名--新建登录名

   (2) 修改密码：“Windows身份验证”登录--对象资源管理器--安全性--打开登录名--双击自定义的登录名

   (3) 赋予权限：双击自定义的登录名--属性--服务器角色 / 用户映射

   (4) 删除：右击自定义的登录名--删除

第3章 数据库的创建与管理

1、数据库按照用途分类：系统数据库和用户数据库。

   系统数据库：

     (1) master：核心，用户不能直接修改，损坏则SQL Server服务器不能工作，需定期备份。

     (2) model：模板，用模板中的默认值创建新的空白数据库。修改model将影响所有使用模板创建的数据库。

     (3) msdb：代理服务数据库，供SQL Server代理程序调度警报、作业及记录操作。

     (4) tempdb：临时数据库，所有建立的临时表和临时存储过程，启动时重建，关闭时清空。

   用户数据库：由用户自行创建的数据库。

2、文件

   (1) 数据库文件：存放数据库数据和数据库对象，可有多个数据库文件。

       类型：a. 主数据文件：“. mdf ”，有且只有一个，存储数据库启动信息数据，是数据库的起点。

             b. 次要数据库：“. ndf ”，可以有0或多个，除主数据文件外其余的都是次要数据文件。

   (2) 事务日志文件：“. ldf ”，至少有一个，记录数据库增删改及故障记录，可分析故障原因并恢复数据。

3、创建数据库 ?

4、数据库管理

   (1) 查看及修改：右键--属性

   (2) 更名：方法1 使用SSMS图形界面

             方法2 使用T-SQL语句：ALTER DATABASE mytest

                                     MODIFY NAME = db_mytest

   (3) 删除：方法1 使用SSMS图形界面

             方法2 使用T-SQL语句：DROP DATABASE mytest

5、数据库的分离，即只将用户数据库从SSMS中删除，对应的物理文件.mdf和.ldf不删除。

   (1) 分离：用户数据库--右键--任务--分离

   (2) 附加：数据库--右键--附加--添加

   (3) 移动：分离--剪切.mdf和.ldf--附加

   (4) 复制：(用户数据库--右键--任务--脱机)--复制.mdl和.ldf --(右键--联机)

第4章 数据表的创建与管理

1、数据表的创建

   (2) 使用T-SQL语句创建数据表

2、数据表的管理

   (1) 使用SSMS图形界面管理数据表

   (2) 使用T-SQL管理数据表：（单击“新建查询”打开空白.sql--输入T-SQL语句--单击V检查--单击！执行--检查表中数据）

      a. 添加字段：

        修改字段类型：

        删除字段：

      b. 添加约束CONSTRAINT： ALTER TABLE 表名

                                         ADD CONSTRAINT 约束名 约束类型 具体的约束说明

         主键约束 PRIMARY KEY：

         外键约束FOREIGN KEY（关系）：

         唯一约束UNIQUE（标识列）：

         默认约束DEFAULT：

         检查约束CHECK：

         删除约束： ALTER TABLE 表名

                       DROP CONSTRAINT 约束名（如CK_stutestemail）

3、数据表的删除  

    (1) 使用SSMS图形界面删除数据表

    (2) 使用T-SQL删除数据表：DROP TABLE 表名

第5章 数据管理（增、删、改、导入导出）

1、使用SSMS图形管理界面

   (1) 添加数据：右击表--编辑前200行--增加字段的值

   (2) 修改数据：右击表--编辑前200行--修改字段的值

   (3) 删除数据：右击表--编辑前200行--右击选中一条记录--删除

2、使用T-SQL

  （单击“新建查询”打开空白.sql--输入T-SQL语句--单击V检查--单击！执行--检查表中数据）

   (1) 插入单行数据：

      语法：INSERT [ INTO ] <表名> [列名列表] VALUES <值列表>

             INTO：“[]”表示可选部分，增强可读性。

             表名：指定向哪个表插入数据。

             列名：若有多列可用逗号隔开，若省略列名则按数据表定义的顺序依次插入数据。

             值列表：插入各列对应的数值，逗号隔开，值与列对应，字符串用单引号。

         注：不要向标识列中插入数据值。                                                      （注：红色为常见易错处）

             在插入数据时注意不要违反CHECK约束。

             若字段不允许为空，且未设置默认值，则必须给该字段设置数据值。

             可以使用关键字DEFAULT代替插入的数值，这样可以改具有默认值的列插入数据。     

   (2) 插入多行数据：

     ● 从一个表中查询出来部分信息，将这些信息插入到另外一个表中：

            INSERT INTO Contact (con_name, con_email, con_phone)

            SELECT stu_name, stu_email, stu_phone

            FROM Student

     ● 通过Union关键字合并数据进行插入：

            INSERT INTO Contact (con_name, con_email, con_phone)

            SELECT ‘黄飞’, ‘huangfei@163.com’, ‘15720613345’ UNION

            SELECT ‘徐强’, ‘xuqiang@163.com’, ‘15720613346’ UNION

            SELECT ‘段悦’, ‘duanyue@163.com’, ‘15720613347’

            注：UNION关键字将两个不同的数据或查询结果组合成一个新的结果集，注意个数、顺序、类型。

     ● 通过INSERT...VALUE方式一次插入多条记录：

            INSERT INTO Contact (con_name, con_email, con_phone)

            VALUES (‘胡康’, ‘hukang@163.com’, ‘15720613265’) ,

                    (‘周星’, ‘zhouxing@163.com’, ‘15720613266’) ,

                    (‘李刚’, ‘ligang@163.com’, ‘15720613267’) 

   (3) 更新数据：UPDATE <表名> SET <列名=更新值> [WHERE<条件>]

                  SET指定更新的列和值，多个“列名=更新值”用逗号隔开，最后一个“列名=更新值”不加逗号

                     WHERE子句是可选的，若省略则对表中所有记录进行更新。

        ● 更新单行：UPDATE Contact SET con_email = ‘ww@163.com’, con_phone = ‘15788889999’ 

                    WHERE con_id=1

        ● 更新多行：UPDATE Contact SET con_email = ‘zjff@163.com’ WHERE con_email = NULL

        ● 更新所有：UPDATE Contact SET con_phone = ‘13388887777 ’

   (4) 删除数据：DELETE FROM <表名> [WHERE<条件>]

       ● 删除单行：DELETE FROM Contact WHERE con_id = 0

                注：在删除主表中某条数据信息时，若该信息在子表中以外键形式存在，则禁止删除主表中该信息。 

                    办法是将主表中要删除的信息先在子表中全部删除。简便的方法是触发器。

        ● 删除多行：DELETE FROM Contact WHERE con_class = ‘12自动化’

        ● 删除所有：DELETE FROM Contact

              扩展：TRUNCATE TABLE <表名>

                    TRUNCATE TABLE用来删除表中所有行，但表的结构、列、约束、索引等不会被改动，

                    比DELETE执行速度快，且占用资源和日志少，该操作不能用于有外键约束引用的表。

3、导入/导出数据

   (1) 文本文件·导入·数据库数据

      右击数据库--任务--导入数据--打开“SQL Server导入导出向导”窗口--下一步

       打开“选择数据源”窗口--“数据源”选“平面文件源”--单击“浏览”选择导入的文件路径--取消“在第一个数据中显示列名称”框

       下一步--下一步--选择要导入的数据库--下一步--选择要导入的数据表

       下一步--保持默认--下一步--选中“立即运行”复选框--下一步--完成--单击关闭按钮即可查看导入的数据。

   (2) 表格文件·导入·数据库数据

      右击数据库--任务--导入数据--打开“SQL Server导入导出向导”窗口--下一步

       “选择数据源”窗口--“数据源”选“Microsoft Excel”--单击“浏览”选择导入的Excel路径--取消“首行包含列名称”复选框

       下一步--选择要导入的数据库--下一步--选中“复制一个或多个表或视图的数据”单选按钮

       下一步--选中Sheet$选项--在目标处选择要导入的数据表

       下一步--保持默认--下一步--选中“立即运行”复选框--下一步--完成--单击关闭按钮即可查看导入的数据。

   (3) 数据库数据·导出·文本文件

      右击数据库--任务--导出数据--打开“SQL Server导入导出向导”窗口

      下一步--打开“选择数据资源”窗口--选择要导出的数据库

      下一步-打开“选择目标”窗口

      “目标”选择“平面文件目标”并选择文件路径及要保存的文件名

      下一步--打开“指定表复制或查询”窗口--保持默认

      下一步--打开“配置平面文件目标”窗口并在“源表或源视图”选择要导出的数据表

      下一步--打开“保存并运行”窗口--保持默认

      下一步--打开“完成该向导”窗口--单击完成打开“执行成功”窗口--单击关闭按钮即可查看导出的文件。

   (4) 数据库数据·导出·表格文件

      右击数据库--任务--导出数据--打开“SQL Server导入导出向导”窗口

      下一步--打开“选择数据资源”窗口--选择要导出的数据库

      下一步-打开“选择目标”窗口

      “目标”选择“Microsoft Excel”并选择文件路径及要保存文件名

      下一步--打开“指定表复制或查询”窗口--保持默认

      下一步--打开“选择源表或源视图”窗口并选择要导出的数据表

      下一步--打开“查看数据类型映射”窗口

      下一步--打开“保存并运行包”窗口

      下一步--打开“完成该向导”窗口--单击完成打开“执行成功”窗口--单击关闭按钮即可查看导出的文件。

第6章 数据查询（T-SQL语句）

1、SELECT语句

       SELECT [ ALL | DISTINCT ] [ TOP n[ PRECENT ] [ WITH TIES ] ] { * | <字段列表> }

       FROM 表名 | 视图名

       [ WHERE <检索条件表达式> ]

       [ GROUP BY <分组依据列> ] [ HAVING <组提取条件表达式> ]

       [ ORDER BY <排序依据列> ] [ ASC | DESC ]

   注：ALL：默认，查询出的结果可以包含重复行。

       DISTINCT：查询的结果如果有值相同的行，则只显示其中一行。

       TOP n[ PRECENT ]：返回查询结果的前n行，或前百分之n行。

       { * | <字段列表> }：“*”表示查询所有字段；<字段列表>表示查询指定的字段，多个字段逗号隔开。

2、查询列

  ● 查询全部列：SELECT * FROM <表名>

  ● 查询指定列：SELECT <列名1> [ , ...n ] FROM <表名>

  ● 查询计算后的列：SELECT 2016-brthday AS age FROM <表名>             (注：在查询中使用常量列同理,包括‘字符串’)

  ● 改变查询结果的列名：SELECT <列名> AS <新名> FROM <表名>             (注：使用* 不可指定列别名,多列逗号隔开)

  ● TOP关键字限制返回行数：SELECT TOP n <列名> [, ...n][WITH TIES] FROM <表名>   注：TIES可并列

  ● 消除重复的数据行：SELECT DISTINCT <列名> FROM <表名>

3、查询行（条件查询）

  ● 比较判断符：SELECT * FROM Student WHERE stu_sex = ‘男’                          =、>、>=、<、<=、!=          

  ● 逻辑运算符：SELECT * FROM Student WHERE stu_sex = ‘男’ AND stu_age = ‘16’              AND、OR、NOT     

  ● 空值判断符：SELECT stu_name FROM Student WHERE stu_native IS [NOT] NULL

  ● 范围运算符：SELECT * FROM Student WHERE stu_age [NOT] BETWEEN 13 AND 16

  ● 列表运算符：SELECT * FROM Student WHERE stu_native [NOT] IN (‘北京’, ‘上海’)

  ● 模式匹配符：SELECT stu_name, stu_sex FROM Student WHERE stu_sex [NOT] LIKE ‘李%’

4、查询排序

  ● 对一列排序：SELECT * FROM Student ORDER BY stu_id ASC                          (注：可省略ASC，默认升序)

  ● 对多列排序：SELECT * FROM Score ORDER BY sco_overall DESC, sco_usual DESC             (矛盾处如何取舍？)

5、聚合函数

  ● SELECT SUM(sco_overall) AS ‘总评成绩总分’, AVG(sco_overall) AS ‘总评成绩平均分’ FROM Score

    注：SUM()和AVG()——所包含的列必须是数值型

  ● SELECT MAX(sco_overall) AS ‘总评成绩最高分’, MIN(sco_overall) AS ‘总评成绩最低分’ FROM Score

  ● SELECT COUNT(*) AS ‘及格数’, FROM Score WHERE sco_overall >= 60           (注：COUNT(*)——包括空值行)

    SELECT COUNT(stu_phone) AS ‘有手机号码的人数’ FROM Student           (注：COUNT(字段名)——不包括空值行)

6、分组查询

  ● GROUP BY分组：

    例：SELECT Cno AS ‘课程号’ COUNT(Sno) AS ‘选课人数’ FROM SC WHERE Sdept = ‘信息’ GROUP BY Cno

    /*先执行WHERE选出信息系学生，再执行GROUP BY对SC表按Cno分组(同Cno一组)，最后执行COUNT求每组人数*/

注：GROUP BY后不能用AS指定的别名，如‘课程号’；GROUP BY分组依据列不能是text、ntext、image和bit类型的列。

  ● HAVING分组

    说明：WHERE筛选FROM指定的表，GROUP BY对筛选结果进行分组，HAVING在对分组进行二次筛选）

    WHERE和HAVING的区别： ● WHERE中使用的条件在HAVING中同样适用；

                                ● WHERE中不可以包含聚合函数，HAVING可以；

                                ● WHERE的条件在分组之前应用，HAVING在之后。

    例：SELECT Sno, COUNT(*) AS ‘选课门数’ FROM SC WHERE Sdept = ‘信息’ GROUP BY Sno HAVING COUNT(*)>3

    /*先执行WHERE选出信息系学生，再执行GROUP BY将学生按学号Sno分组，再执行HAVING将COUNT(*)>3的选出*/

    辩：SELECT Sdept, COUNT(*) FROM Student WHERE Sdept IN(‘计算机’, ‘信息’) GROUP BY Sdept 【 √ 】

         ... ... ... ... ... ... ... ... ... ... ... ... ... .... GROUP BY Sdept HAVING Sdept IN(‘计算机’, ‘信息’) 【 √ 】

         SELECT Sdept, COUNT(*) FROM Student WHERE Sage<=20 GROUP BY Sdept 【 √ 】

         ... ... ... ... ... ... ... ... ... ... ... ... ... ...  GROUP BY Sdept HAVING Sage<=20 【 × 】

         注：GROUP BY后结果中只含分组依据列Sdept和五个聚合函数，即执行到HAVING时已没有Sage列 ! ! ! 

             即，HAVING中只能包含聚合函数和分组依据列 ! 

7、多表连接查询

 (1) 内连接 语法：SELECT 列表.列名 FROM 列表1 [INNER] JOIN 列表2 ON 连接条件表达式

    ● 等值连接：连接条件表达式使用“=”，查询结果中列出被连接表中的所有列，包括重复列。

      SELECT * FROM Student INNER JOIN Class ON Student.stu_classid=Class.cla_id   (注：*指Student的所有列)

    ● 自然连接：连接条件表达式使用“=”，查询结果中列出被连接表中的所有列，删除重复列。         (注：语法上与上区别？)

      SELECT Student.*, Class.cla_name FROM Student INNER JOIN Class ON Student.stu_classid=Class.cla_id 

    ● 使用表别名：原名 [AS] 别名                                            (注：后面改的名字，前面已经可以使用了？)

      SELECT t.tea_name, d.dep_name FROM Teacher AS t INNER JOIN Department AS d ON t.tea_depid=d.dep_id

● 多表内连接：每增加一张表，就增加一个[INNER] JOIN ... ON子句。

  SELECT Student.stu_name, Course.cou_name, Score.sco_overall FROM Score

  INNER JOIN Student ON Score.sco_stuno=Student.stu_no

  INNER JOIN Student ON Score.sco_courseid=Course.cou_id

 (2) 外连接

● 左外连接LEFT [OUTER] JOIN：结果不仅包括连接列所匹配的行，还包括LEFT [OUTER] JOIN指定的左表的所有行，

                                若左表某行在右表没有匹配行，则结果集中右表的所有选择列均为空值。

  语法：SELECT 列表.列名 FROM 表1 LEFT [OUTER] JOIN 表2 ON 连接条件表达式

● 右外连接RIGHT [OUTER] JOIN：同理。

  语法：SELECT 列表.列名 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 连接条件表达式

● 全外连接FULL [OUTER] JOIN：同理。

  语法：SELECT 列表.列名 FROM 表1 FULL [OUTER] JOIN 表2 ON 连接条件表达式

 (3) 交叉连接

      含义：将连接的两个表的所有行进行组合形成一个结果集，相当于两个表的“乘积”。

      语法：SELECT 列表.列名 FROM 表1 CROSS 表2

第7章 T-SQL语句

1、语言分类

  ● 数据操作语言DML：INSERT、DELETE、UPDATE、SELECT

  ● 数据控制语言DCL：GRANT、DENY、REVOKE

  ● 数据定义语言DDL：CREATE/DROP/ALTER TABLE/VIEW/INDEX/PROCEDURE/TRIGGER

  ● 附加语言元素：SET/BEGIN TRANSACTION/COMMIT、OPEN、CLOSE、DECLARE、ROLLBACK、FETCH、EXECUTE

  ● 注释：单行注释( “ -- ” )； 多行注释( “ /* ... */ ” )

2、变量

  (1) 局部变量（前缀@）

     ● 声明：DECLARE @变量名 数据类型

     ● 赋值：方法1：SET @变量名=值

             方法2：SELECT @变量名=值     (注：要确保查询的记录不多于一条，否则把最后一条查询记录的值赋给变量)

     ● 例：DECLARE @name VARCHAR(10)

           DECLARE @classid VARCHAR(10)

           SET @name = ‘王濛’                                                          --为@name赋值

           SELECT @classid = stu_classid FROM Student WHERE stu_name = @name   --为@classid赋值

           SELECT * FROM Student WHERE stu_classid = @classid

  (2) 全局变量（前缀@@）

3、输出语句

方法1：SELECT 变量 AS 自定义列名0

    例：SELECT @@LANGUAGE AS ‘当前使用的语言’    （注：输出结果以表格方式显示）

方法2：PRINT 变量或字符串    

    例：PRINT ‘当前使用的语言是：’ + @@LANGUAGE  （注：输出结果以文本方式显示）

    例：PRINT ‘当前错误号是：’ + @@ERROR          （注：文本类型 + 整数类型 = 出错）

    改：PRINT ‘当前错误号是：’ + CONVERT(VARCHAR(10), @@ERROR)

4、流程控制语句

  技巧：将表格数据和文本消息输出在同一窗口——工具--选项--查询结果--将查询结果的默认方式改为：以文本格式显示结果！

5、批处理语句

批处理是一条或多条T-SQL语句集合，这些语句为完成一个整体的目标而同时执行，GO是结束标志。

   SQL Server规定，创建库、创建表、创建存储过程、创建视图等，都必须在结尾加上“GO”批处理标志。

第8章 数据查询进阶--子查询

    若一个SELECT嵌套在INSERT、DELETE、UPDATESELECT中，则称为嵌套查询。外层是主查询；内层是子查询，子查询用括号括起来，先执行，但子查询的SELECT语句中不能使用ORDER BY子句（ORDER BY只能对最终查询结果进行排序）。

1、使用比较运算符的(不相关)子查询：

语法：WHERE 表达式 比较运算符 (子查询)

例：SELECT stu_name FROM Student WHERE stu_sex = (SELECT stu_sex FROM Student WHERE stu_name = ‘王濛’)

2、使用[NOT] IN的(不相关)子查询：

语法：WHERE 表达式 [NOT] IN (子查询)

例：查询选修VB课程的学生的学号和姓名。

方法1：SELECT Sno, Smane FROM Student S ON S.Sno = SC.Sno JOIN Course C C.Cno = SC.Cno WHERE Cname =‘VB’

方法2：SELECT Sno, Sname FROM Student

        WHERE Sno IN (SELECT Sno FROM SC

        WHERE Cno IN (SELECT Cno FROM Course WHERE Cname = ‘VB’))

3、使用[NOT] EXISTS的(相关)子查询：

语法：WHERE 表达式 [NOT] EXISTS (子查询)

说明：系统对EXISTS后面的子查询进行运算判断它是否返回行，若至少返回一行，则EXISTS的结果为TRUE，否则为FALSE，

      外层查询在EXISTS为TRUE时才执行。由于EXISTS只返回真假，在子查询中指定列名没有意义，因此列名通常用“*”。

例：查询没有选修“c01”课程的学生的姓名。

法1：SELECT DISTINCT Sname FROM Student S JOIN SC ON S.Sno=SC.Sno WHERE Cno != ‘c01’              【×】

法2：SELECT Sname FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Cno != ‘c01’)               【×】

      ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... [NOT] IN (SELECT Sno FROM SC WHERE Cno = ‘c01’)         【√】

法3：SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=S.Sno AND Cno != ‘c01’)【×】

      ... ... ... ... ... ... ... ... ... ... ... ... [NOT] EXISTS (SELECT * FROM SC WHERE Sno=S.Sno AND Cno = ‘c01’)【√】

注：三种错误的原因相同，在查询时是组行判断的，而这三种情况下只要满足Cno != ‘c01’就作为结果，使得结果中既包含没有c01

的学生，也包括选c01的同时也选了其他课程的学生。

第9章 索引、视图、事务、存储过程、触发、游标（并发控制、函数）

第1章 下载及安装

SQL Server 2012 案例教程（贾祥素）——学习笔记

原文：http://www.cnblogs.com/xiaoxiaomeng94/p/5988566.html