Sql Server 知识点全集

    /*********************************************************
    **********************************************************
       * Name: 知识点全集
       * File: 知识点全集.sql
       * Description: sql数据知识点
       * CreateAuthor:梁飞龙
       * CreateDateTime: 2010-12-25
       * University: Huazhong Normal University
       * UpdateAuthor:
       * UpdateDateTime:
     *********************************************************
     ********************************************************/

--1.SQL由什么文件和什么文件组成？
/*
答：SQL由数据文件和日志文件组成
*/

--2.SQL的数据文件分哪两类？扩展名分别是什么？分别可以有多少个？
/*
答：SQL的数据文件分主数据文件和二级数据文件。
    主数据文件有且只有一个，二级数据文件0-N个
    扩展名：主数据文件：MDF
            二级数据文件：NDF
*/

--3.SQL文件组和文件的关系？
/*
答：文件存放在文件组中，一个文件只能属于一个文件组；
    一个文件组可以有多个文件。
*/

--4.用代码创建多个数据文件的数据库？
/*
答：
CREATE DATABASE DatabaseName
primary ON
 (
 name=逻辑名,
 filename=物理名, --扩展名是mdf
 size=初始大小,
 maxsize=最大值,
 filegrowth=增长
 ),               --多个文件组之间用逗号分隔
filegroup 文件组名
 (
 name=逻辑名,
 filename=物理名, --扩展名是ndf
 size=初始大小,
 maxsize=最大值,
 filegrowth=增长
 )
log ON
 (
 name=逻辑名,
 filename=物理名, --扩展名是ldf
 size=初始大小,
 maxsize=最大值,
 filegrowth=增长
 )
*/
--5.用代码创建表？
CREATE TABLE TABLE_NAME                --创建表的表名
(
 [ID] int IDENTITY(1,1) PRIMARY KEY, --设置ID为自增长的主键
 [NAME] varchar(20) NOT NULL ,       --设置名字不为空 
 [AGE] tinyint CHECK([AGE]<120)      --设置年龄小于120
 )

--6.用代码实现修改数据库，添加文件组？
ALTER DATABASE DATABASENAME              --要修改的数据库名
ADD FILEGROUP FILEGROUPNAME              --要添加的文件组名

--7.用代码实现修改文件，文件组？
/*
答：
--修改文件示例
ALTER DATABASE DATABASENAME
modify file
(
 [name]='原逻辑名',
        [NEWNAME] = '新逻辑名',
        [FILENAME] = '新文件名',
        [SIZE] = '文件大小',
        [MAXSIZE] = '文件的最大值',
        [FILEGROWTH] = '文件增长值或增长百分比'
 )
 */
 --修改文件组
ALTER DATABASE Demo_20091230NEW
MODIFY FILEGROUP filegroup2 name = Newfilegroup_name

-- 8.用代码修改表实现添加列、修改列、删除列？
-- 添加列示例代码
 ALTER TABLE Student
 ADD [S_Number] Varchar(20)
 
 -- 删除列例代码
 ALTER TABLE Student
 DROP COLUMN [S_Number]
 
 -- 修改列例代码
 ALTER TABLE Student
 ALTER COLUMN [S_Name] varchar(8)
 
 --修改列名通过系统存储过程
 SP_RENAME 'Student.S_Name' ,'S_NAME_NEW'
 
-- 9.用代码实现查看表、数据库的信息？
/*
答：查看表：sp_help 表名
    查看数据库：sp_helpDB 数据库名
*/
--示例代码：
--查看表：
  sp_help class
--查看表：
  sp_helpdb Demo_20091230NEW
 
-- 10.重命名数据库和表的SQL语句？
--重命名表的SQL语句(调用存储过程)
SP_RENAME class,classNew  --不添加引号
SP_RENAME 'class','classNew'  --添加引号
--重命名表的SQL语句(ALTER DATABASE)
ALTER DATABASE Demo_20091230NEW
Modify name = Demo_20091230

--重命名数据库的SQL语句
SP_RENAMEDB Demo_20091230NEW,Demo_20091230  --不添加引号
SP_RENAMEDB 'Demo_20091230','Demo_20091230NEW'  --添加引号

-- 11.什么是数据库的完整性？
/*答：确保数据库的一致性和精确性，
      可以用约束和触发器来实现。
*/

-- 12.数据库完整性的分类？
/*答：实体完整性：规定表的每一行在表中是唯一的实体。（主键）
      域完整性：是指表中列满足特定的数据类型和约束。
      引用完整性：两个表的主键和外键的关键字一致。
      自定义完整性：用户自己定义的数据类型的约束。
*/
 
-- 13.约束分为哪两个级别，它和完整性的关系？
/*
  答：约束分表级别和列级别，可以相互转换，只是写法不同。
      创建联合主键的时候一定要用表级约束
  PRIMARY KEY约束 (实现实体完整性)
  UNIQUE KEY约束 (实现域完整性)
  NOT NULL约束 (实现域完整性)
  CHECK约束 (实现域完整性)
  FOREIGN KEY约束 (实现引用完整性)
  自定义数据类型（实现自定义完整性）
  其中PRIMARY KEY、UNIQUE KEY、FOREIGN KEY
  可以新建表级别和列级别约束。
*/

-- 14.用代码创建NOT NULL 约束？
 /*
  答：直接在创建表的时候添加。
  */
--代码示例：
CREATE TABLE Student
(
 [ID] int NOT NULL
 )
-- 15.用代码创建primary key 约束（两种方式）？
 /*
  答：primary key 约束可以用列级和表级两种方式创建。
      创建联合主键的时候，必须用表级的方式创建。
  */
--示例代码（列级）
CREATE TABLE Student
(
 [ID] int PRIMARY KEY,
 [NAME] varchar(20)
)
--示例代码（表级）
CREATE TABLE Student
(
 [ID] int ,
 [NAME] varchar(20)
 CONSTRAINT PK_STUDENT PRIMARY KEY ([ID],[NAME])
)
-- 16.用代码创建foreign key 约束（两种方式）？
 /*
  答：foreign key 约束可以用列级和表级两种方式创建。
  */
--示例代码（列级）
CREATE TABLE Student
(
 [ID] int PRIMARY KEY,
 [C_NAME] varchar(20) REFERENCES CLASS([C_NAME]) --学生表中的课程名引用课程表中的课程名
)

--示例代码（表级）
CREATE TABLE Student
(
 [ID] int PRIMARY KEY,
 [C_NAME] varchar(20),
 CONSTRAINT FK_C_NAME FOREIGN KEY ([C_NAME])
 REFERENCES CLASS([C_NAME]) --学生表中的课程名引用课程表中的课程名
)

-- 17.用代码创建unique key 约束（两种方式）？
 /*
  答：unique key 约束可以用列级和表级两种方式创建。
      在创建约束的列上添加唯一约束。
  */
--示例代码（列级）
CREATE TABLE Student
(
 [ID] int PRIMARY KEY,
 [C_NAME] varchar(20) REFERENCES CLASS([C_NAME]), --学生表中的课程名引用课程表中的课程名
 [S_NUM] varchar(20) UNIQUE  --学号必须唯一
)

--示例代码（表级）
CREATE TABLE Student
(
 [ID] int PRIMARY KEY,
 [C_NAME] varchar(20) REFERENCES CLASS([C_NAME]), --学生表中的课程名引用课程表中的课程名
 [S_NUM] varchar(20),
 CONSTRAINT UN_S_NUM UNIQUE([S_NUM]) --学号必须唯一
)
-- 18.用代码创建check 约束（两种方式）？
  /*
  答：check 约束可以用列级和表级两种方式创建。
  */
--示例代码（列级）
CREATE TABLE Student
(
 [ID] int PRIMARY KEY,
 [C_NAME] varchar(20) REFERENCES CLASS([C_NAME]), --学生表中的课程名引用课程表中的课程名
 [S_NUM] varchar(20) UNIQUE,  --学号必须唯一
 [S_AGE] tinyint CHECK([S_AGE]<60) --年龄必须小于60岁
)
--示例代码（表级）
CREATE TABLE Student
(
 [ID] int PRIMARY KEY,
 [C_NAME] varchar(20) REFERENCES CLASS([C_NAME]), --学生表中的课程名引用课程表中的课程名
 [S_NUM] varchar(20),
 [S_AGE] tinyint ,
 CONSTRAINT CK_S_AGE CHECK([S_AGE]<60) --学号必须唯一
)

-- 19.用代码实现修改、删除约束？
/*答：修改约束：只有NOT NULL能通过修改列的方式修改约束。
      删除约束：DROP CONSTRAINT 关键字，后面跟约束名称。
*/
--示例代码(通过修改列的方式修改NOT NULL约束。)：
ALTER TABLE class
ALTER COLUMN [NAME] varchar(20) NOT NULL

--删除代码：
DROP CONSTRAINT CK_S_AGE --删除学号唯一约束

-- 20.用代码实现查看约束的信息？
/*答：查看约束SP_HELPCONSTRAINT 约束名称
*/
--代码示例：
SP_HELPCONSTRAINT CK_S_AGE  --查看学号唯一约束

-- 21.SQL有哪些数据类型？
/*答：
日期类型：DATETIME、SMALLDATETIME
整数类型：BIGINT、INT、SMALLINT、TINYINT
浮点型：DOUBLE、FLOAT、DECIMAL、REAL、NUMERIC
货币类型：MONEY、SMALLMONEY
字符类型：CHAR、VARCHAR、NCHAR、NVARCHAR
二进制数据类型：BINARY、VARBINARY、IMAGE
全局唯一标识：UNIQUEIDENTIFIER
大容量数据类型：TEXT、NTEXT、IMAGE
*/

-- 22.SQL语句分类（DDL、DQL、DML、DCL）？
/*
答：数据定义语言：DDL(Data Definition Language)创建、删除、修改数据库对象
                 如：表、视图、模式、触发器、存储过程等。
    数据查询语言：DQL(Data Query Language)用户检索数据库的。
    数据操纵语言：DML(Data Manipulation Language)用于添加、修改、删除存储在
                 数据库对象中的数据。
    数据控制语言：DCL(Data Control Language)控制访问数据库权限的。
   
    数据定义语言：CREATE、DROP、ALTER
    数据查询语言：SELECT
    数据操纵语言：INSERT、UPDATE、DELETE
    数据控制语言：GRANT（授权）、DENY（拒绝）、REVOKE（移除）        
*/
 
-- 23.关键字identity是什么意思？
/*
答：关键字identity表示自动编号，自增长。
*/
-- 24.uniqueidentifier是什么意思？如何生成？
/*
答：uniqueidentifier表示全球唯一标识。用SELECT NEWID()生成
*/
--代码示例：
SELECT NEWID() AS 全球唯一标识

-- 25.默认值创建？绑定默认值？取消绑定默认值？
/*
答：创建默认值有两种：
    a)创建表的时候附带创建
    b)使用CREATE DEFAULT 关键字
    区别：后再独立于表存在，前者必须要有表。
    绑定默认值：使用sp_bindefault 默认值名,表名.列名。
               注意：只有使用CREATE DEFAULT 关键字才需要绑定。
    取消绑定默认值：使用sp_unbindefault 默认值名,表名.列名。
               注意：只有使用CREATE DEFAULT 关键字才需要绑定。

*/
--代码示例：创建表的时候附带创建
CREATE TABLE CLASS
(
 [ID] int PRIMARY KEY ,
 [NAME] varchar(20) DEFAULT('梁军卫')
 )
--代码示例：使用CREATE DEFAULT 关键字创建
CREATE DEFAULT DF_NAME
AS '梁军卫'

--代码示例：绑定默认值到CLASS表的NAME列上
sp_bindefault DF_NAME,'CLASS.NAME'

--代码示例：取消绑定默认值到CLASS表的NAME列上
sp_unbindefault DF_NAME,'CLASS.NAME'

-- 26.规则创建？绑定规则？取消绑定规则？
/*
答：创建规则用CREATE RULE 关键字
    绑定规则用SP_BINDRULE系统存储过程
    取消绑定规则SP_UNBINDRULE系统存储过程
*/
--代码示例：使用CREATE DEFAULT 关键字创建
CREATE RULE RU_NAME
AS @NAME ='梁军卫'

--代码示例：绑定规则到CLASS表的NAME列上
SP_BINDRULE RU_NAME,'CLASS.NAME'

--代码示例：取消绑定规则到CLASS表的NAME列上
SP_UNBINDRULE 'CLASS.NAME'

-- 27.如何创建自定义类型？
/*
答：创建自定义类型：用系统存储过程：SP_ADDTYPE
*/
--代码示例：创建一个身份证的数据类型（18为字符）且不允许为空
SP_ADDTYPE IdCard,'varchar(18)','NOT NULL'

-- 28.Select查询执行顺序？
/*
答：1.执行FROM字句，根据FROM字句后面的一个或多个表创建工作表。
      如果是多个表，将对表进行交叉连接。
      如果只有一个表，就直接作为工作表。
    2.如果有WHERE字句，根据WHERE字句搜索满足条件的行。
    3.如果有GROUP BY字句，会对第二步产生的结果集进行分组汇总。
    4.如果有HAVING字句，会对第三步产生的结果集进行组筛选。
    5.如果有DISTINCT、TOP关键字，将在第四步的基础上进行过滤，去掉重复的行。
    6.如果有ORDER BY字句，会在第五步的基础上进行排序。
    7.显示查询结果。
*/

--29.数据库中的表关系有哪几种？分别是什么？
/*
答：分四种（一对一、一对多、多对一、多对多）
    多对多：必须要分表，分解成两个多对一
举例：如学生和课程是多对多关系，新增一个学生选修课程表。
      学生和学生选修课程表是一对多关系
      课程和学生选修课程表是一对多关系
*/

--30.级联删除、更新的关键字是什么？并写出代码？
/*
答：级联删除关键字:ON DELETE CASCADE
    级联更新关键字:ON UPDATE CASCADE
代码示例如下：
*/
--修改表添加级联删除、级联更新外键约束
ALTER TABLE StudentClass
ADD CONSTRAINT jlDelete FOREIGN KEY (Fk_S_ID)
REFERENCES Student(S_ID)
ON DELETE CASCADE 
ON UPDATE CASCADE

--31.表的连接有哪几种？它们的含义分别是什么？
/*
答：共五种。
    INNER JOIN :内连接（以两个表中匹配的记录为准）
    LEFT JOIN/LEFT OUTER JOIN： (以左边的表中有的记录为准)
    RIGHT JOIN/RIGHT OUTER JOIN： (以右边的表中有的记录为准)
    FULL JOIN/FULL OUTER JOIN： (以两边的表只要有一个表有的记录即可)
    CROSS JOIN：（两个表的记录的笛卡尔积）   
*/

--32.消除重复行的关键字是什么？
/*
答：DISTINCT（放在SELECT关键字后面）
*/

--33.分组时既能显示明细记录又能显示汇总值的关键字是什么？
--请写一个示例代码？
/*
答：关键字：COMPUTE\COMPUTE BY
*/
SELECT S_GROUP,S_AGE AS '平均年龄'
FROM Student
WHERE S_GROUP = '二组'
ORDER BY S_GROUP
COMPUTE AVG(S_AGE) BY S_GROUP

--34.SQL中的通配符有几种？每种的含义是什么？
/*
答：
   '_'    表示一个任意字符；
   '%'    表示0-N个任意字符；
   '[ABC]'表示A或B或C中的一个任意字符，常与'%'连用；
   '[^ABC]'表示不是A且B且C中的一个任意字符，常与'%'连用；
*/
--35.Rollup和CUBE的相同点和异同点是什么？
/*
答：都是对分组（GROUP）中的汇总结果集的扩展。
    CUBE的扩展要比Rollup多。
*/
--只根据'组'分组
SELECT S_Group,AVG(S_age) FROM Student
GROUP BY S_Group

SELECT S_Group,AVG(S_age) FROM Student
GROUP BY S_Group WITH ROLLUP

SELECT S_Group,AVG(S_age) FROM Student
GROUP BY S_Group WITH CUBE
 
--根据'组'和'姓名'分组
SELECT S_Group,S_Name,AVG(S_age) FROM Student
GROUP BY S_Group,S_Name

SELECT S_Group,S_Name, AVG(S_age) FROM Student
GROUP BY S_Group,S_Name WITH ROLLUP

SELECT S_Group,S_Name,AVG(S_age) FROM Student
GROUP BY S_Group,S_Name WITH CUBE

--36.子查询的种类？它们的区别是什么？
/*
答：子查询分标准子查询（嵌套子查询）和相关子查询
    区别：标准子查询（嵌套子查询）子查询只执行一次；
          相关子查询：子查询执行0-N次；
*/
--示例:
--查找年龄在23岁以内的学生的信息（用子查询的方式）
--标准子查询
SELECT * FROM student
WHERE S_NAME IN
         (SELECT S_NAME FROM student
          WHERE student.S_age <23)
         
--查找学生信息并统计班级每个学生与班级平均年龄的差值
--相关子查询
SELECT *,
  (SELECT AVG(S_age) FROM student) AS '班级平均年龄',
  S_age - (SELECT AVG(S_age) FROM student) AS '差值'
FROM student

--37.子查询的意义和写子查询的注意事项？
/*
答：子查询的意义：简化复杂的查询，
                  将一个复杂的查询逻辑上分解成几个简单的查询。
    子查询的注意事项：
      1.带有小括号
      2.可以在很多地方使用
         a）使用别名时；
         b）使用IN或NOT IN时；
         c）使用UPDATE、INSERT、DELETE语句时；
         e）使用比较运算符时；
         f）使用ANY、SOME、ALL时；
         g）使用EXIST或NOT EXIST时；
         h）使用表达式的地方。
*/

--38.关键字SOME、ALL的含义？请写出代码示例？
/*
答：SOME、ALL都是和比较运算符（>、>=、<、<=、!=、<>、=）联用，
    用于比较SOME、ALL字查询所返回的值。
*/
--示例：查找年长（年龄在平均年龄之上）的学生的信息

SELECT *
FROM u_student
WHERE [AGE] > ALL(SELECT AVG(ISNULL([AGE],0)) FROM u_student)

--39.关键字IN的含义？请写出代码示例？
/*
答：关键字IN表示在...里
*/
--示例：查找姓名等于张三或李四的学生的记录
SELECT *
FROM u_student
WHERE [NAME] in ('张三','李四')

--40.视图分为哪三类？请写出代码示例？
/*
答：单表视图：数据只来源于一个表。
    多表视图：数据只来源于多个表。
    嵌套视图：数据只来源于视图。
*/
--示例：嵌套视图
--创建学生信息视图（单表视图）
CREATE VIEW V_StudentInfo
AS
SELECT * FROM u_student us

--创建学生姓名信息视图（嵌套视图）
CREATE VIEW V_StudentNameInfo
AS
SELECT [NAME] FROM V_StudentInfo

--41.视图的优缺点是什么？
/*
答：视图优点：
    1.简化查询操作
    2.隐蔽敏感数据，提高安全性
    3.定制数据
    4.数据的查询和存储分离
    视图缺点：
    1.性能较低
    2.更新受限：SELECT语句中使用了HAVING、GROUP BY、TOP、DISTINCT、
                计算列、聚合函数关键字后就不能更新
*/

--42.索引的作用是什么？索引分为哪两类？它们的区别是什么？请写出代码示例？
/*
答：索引的作用是用来优化查询，提高查询速度的。
    索引分为聚集索引（簇索引）或非聚集索引（非簇索引）
    区别：1.聚集索引的页级页存放的是实际的数据而
            非聚集索引得页级页存放的是索引信息，这些索引信息存放在索引页中。
          2.非聚集索引可以建立在数据堆上也可以建立在聚集索引上。
          3.唯一索引属于非聚集索引，但比非聚集索引多了一个UNIQUE（唯一）约束。
    创建索引的关键字CREATE INDEX
*/

--示例：在u_STUDENT表的[NAME]列上创建聚集索引
CREATE CLUSTERED INDEX CLU_INDEX
ON u_STUDENT([NAME])

--示例：在u_STUDENT表的[NAME]列上创建非聚集索引
CREATE NONCLUSTERED INDEX CLU_INDEX
ON u_STUDENT([NAME])

--示例：在u_STUDENT表的[NAME]列上创建唯一聚集索引
CREATE UNIQUE INDEX CLU_INDEX
ON u_STUDENT([NAME])

--43.查看表的索引的SQL语句是什么？请写出代码示例？
/*
答：查看表的索引的SQL语句是 SP_HELPINDEX 表名
*/
--代码示例
SP_HELPINDEX Class

--44.重新整理索引的SQL语句是什么？请写出代码示例？
/*
答：重新整理索引的SQL语句是DBCC INDEXDEFRAG(数据库名称,表名,索引名)
    DBCC 是一个数据库工具集
*/
--代码示例
DBCC INDEXDEFRAG(Demo_20091230NEW,Class,PK__Class__49C3F6B7)

--45.SQL的盘区分为哪两种，它们的区别是什么？
/*
答：SQL的盘区分：统一扩展盘区和混合扩展盘区。
    统一扩展盘区：只存放一种类型数据页。
    混合扩展盘区：存放二种或两种以上的数据页。
   
--46.SQL的数据页一共有几种？分别是什么？
/*
答：SQL的数据页一共有八种。数据页、索引页、文本\图像页、可用空间页、
                           全局分配映射表、辅助全局分配映射表、索引分配映射表、
                           大容量差异映射表\大容量差异更改映射表。
    数据页：存放真实的数据。
    索引页：存放索引数据，如非聚集索引的页级页。
    文本\图像页：存放大容量数据类型，如Image、text、ntext。
    可用空间页：数据库中数据页的使用情况，0表示没有使用，1表示使用。
*/

--47.E-R（Entity-Relationship）模型的三要素是什么？
/*
答：E-R（Entity-Relationship）模型的三要素是：实体、关系、属性。
    实体：真实存在的事物。（客户为了完成自己的业务目标需要用到的人或事物）
    关系：实体间的关系：有一对一、一对多、多对一、多对多（需要分表）
    属性：实体的特征，可映射成数据库中的列。

--48.数据库设计的过程分为哪几个步骤？
/*
答：数据库的设计过程大致可分为4个步骤：
    a）需求分析：调查和分析用户的业务活动和数据的使用情况，
                 弄清所用数据的种类、范围、数量以及它们在业务活动中交流的情况，
                 确定用户对数据库系统的使用要求和各种约束条件；
    b）概念设计：用户要描述的现实世界的概念数据模型；
    c）逻辑设计：主要工作是将现实世界的概念数据模型设计成数据库；
    d）物理设计：确定数据库的存储结构。
*/

--49.关键字UNION /UNION ALL的含义？请写出代码示例？
/*
答：关键字UNION /UNION ALL表示连接两个表的内容进行查询。
*/
--示例代码：连接查询出‘语文’、‘数学’的课程信息
SELECT * FROM Class
WHERE Class_Name = '语文'
UNION ALL
SELECT * FROM Class
WHERE Class_Name = '数学'

--50.关键字EXISTS的含义？请写出代码示例？
/*
答：关键字EXISTS用来表示子查询中是否存在满足条件的记录。
    如果存在返回TRUE，否则返回FALSE。
*/
--示例代码：查找‘数学’课的课程信息
SELECT * FROM Class a
WHERE EXISTS (SELECT * FROM Class b
              WHERE b.Class_Name = '数学' AND a.ID = b.ID)
             
--示例代码：判断CLASS表是否在，存在则删除
IF EXISTS(SELECT name
   FROM   sysobjects
   WHERE  name = 'Class'
   AND   type = 'U')
    DROP TABLE Class
GO

--51.数据库三范式是哪三范式？
/*
答：1NF：属性不可再分。
    2NF：在1NF的基础上消除部分依赖（适用于联合主键）。
    3NF：在2NF的基础上消除传递依赖。
*/

--52.用PowerDesigner生成数据库的步骤?
/*
答：1.创建概念数据模型：
    定义：只有关系，没有外键的模型，是在数据库设计的前期创建的。
    PowerDesigner操作步骤：File-》New -》Conceptual Data Model
    2.检查概念数据模型：Check Model
    PowerDesigner操作步骤：Tools-》Check Model
    3.生成物理数据模型：由概念数据模型转换而来，是真实的数据模型。
    PowerDesigner操作步骤：Tools-》Generate Physical Data Model
    4.用企业管理器创建新的空数据库
    5.创建SQL连接：
    PowerDesigner操作步骤：DataBase-》Connection
    6.生成SQL数据库：
    PowerDesigner操作步骤：DataBase-》Generate DataBase
*/

--53.全局变量和局部变量的区别是什么？
/*
答：全局变量是系统预先定义的。
    局部变量是用户自定义的。
*/

--54.定义局部变量的关键字是什么？请写出示例代码？
/*
答：定义局部变量的关键字DECLARE
*/
--示例代码：定义一个整型的局部变量。
DECLARE @Temp AS int

--55.给变量赋值有哪些方法？请写出示例代码？
/*
答：给变量赋值有2种，分别用SET关键字和SELECT关键字。
*/
--示例代码：用SET关键字给变量赋值
SET @Temp = 15
--示例代码：用SELECT关键字给变量赋值（查询赋值）
SELECT @Temp = AGE FROM Student
WHERE S_Name = '张三'

--56.数据类型转换的函数有哪些？请写出示例代码？
/*
答：CAST(字段 AS 数据类型) 和 CONVERT(数据类型，字段)
*/
--示例代码： 使用CAST将12转换成'12'再与‘张三’相加
SELECT '张三'+CAST(12 AS varchar(2)) AS 数据类型转换
--示例代码： 使用CONVERT将12转换成'12'再与‘张三’相加
SELECT '张三'+CONVERT(varchar(2),12) AS 数据类型转换

--57.全局变量@@RowCount、@@IDENTITY、@@Error的定义是什么？请写出示例代码？
/*
答：@@RowCount定义：返回受上一语句影响的行数。
    @@IDENTITY定义：返回最后插入的自增长值（一定要包含自增长列）
    @@ERROR定义：返回最后执行的 Transact-SQL 语句的错误代码。
*/
--示例代码：@@RowCount
SELECT * FROM StudentClass sc     --查询StudentClass表

SELECT @@RowCount AS 上一行语句影响的条数  --显示StudentClass表的记录数

--示例代码：@@IDENTITY
--先决条件：操作的表中一定要含有自增长列，
--否则@@IDENTITY全局变量一直显示为NULL

INSERT INTO StudentClass     --往StudentClass中插入记录
VALUES (1357,1)

SELECT @@IDENTITY AS 最后的自增长ID号

--示例代码：@@ERROR
priny 1              --编写一行错误的SQL语句

SELECT @@ERROR AS 最后一行SQL语句的错误编号     --查询最后一行SQL语句的错误编号

--58.SQL中表示循环的关键字有哪些？请写出示例代码？
/*
答：SQL中表示循环的关键字有：WHILE关键字
*/
--示例代码：
DECLARE @COUNT AS int             --定义一个计数器
SET @COUNT = 1                    --初始化计数器
WHILE @COUNT < 4                  --循环打印循环变量
BEGIN
 PRINT @COUNT
 SET @COUNT = @COUNT + 1
END

--59.关键字break与关键字Continue的区别是什么？请写出示例代码？
/*
答：关键字break：结束全部循环
    关键字Continue：跳过本次循环，进入下一次循环
*/
--示例代码：
DECLARE @COUNT AS int             --定义一个计数器
SET @COUNT = 1                    --初始化计数器
WHILE @COUNT < 4                  --循环打印循环变量
BEGIN
 IF @COUNT % 2 = 0         --跳过偶数循环
 BEGIN
  SET @COUNT = @COUNT + 1  --退出之前要加1
  CONTINUE          --跳过本次循环，进入下一次循环
 END
 PRINT @COUNT
 SET @COUNT = @COUNT + 1
END

--示例代码：
DECLARE @COUNT AS int             --定义一个计数器
SET @COUNT = 1                    --初始化计数器
WHILE @COUNT < 4                  --循环打印循环变量
BEGIN
 PRINT @COUNT
 SET @COUNT = @COUNT + 1
 IF @COUNT > 2             --当计数器大于2的时候结束循环
 BEGIN
  BREAK             --退出循环
 END
END

--60.函数按照返回值分为几类？如何调用？请写出示例代码？
/*
答：函数按照返回值分为三类：
    a）标量函数：返回的是SQL数据类型不包括大容量数据类型（Image、text、ntext）。
    b）内联表值函数：返回的是SQL的数据表，类似于SQL参数化视图。
    c）多声明表值函数：返回的是预先定义好的SQL的数据表。
*/

--创建标量函数：
--判断函数是否存在，存在则删除
IF EXISTS(SELECT *
    FROM   sysobjects
    WHERE  name = 'Fn_BL_GetStudentNameByStudentID')
DROP FUNCTION Fn_BL_GetStudentNameByStudentID
GO
CREATE FUNCTION Fn_BL_GetStudentNameByStudentID(@StudentID int)
RETURNS VARCHAR(20)             --函数返回值是SQL数据类型
AS                              --函数开始的标志
BEGIN                           --函数体
 DECLARE @NAME AS varchar(20)  --定义一个变量
 SELECT @NAME = [S_NAME] FROM Student s --给变量赋值
 WHERE s.S_ID = @StudentID
 RETURN @NAME                  --返回姓名
END

--调用标量函数：
SELECT dbo.Fn_BL_GetStudentNameByStudentID(1357) AS 学生姓名

--创建内联表值函数：
--判断函数是否存在，存在则删除
IF EXISTS(SELECT *
    FROM   sysobjects
    WHERE  name = 'Fn_NL_GetStudentIntoByStudentID')
DROP FUNCTION Fn_NL_GetStudentIntoByStudentID
GO

CREATE FUNCTION Fn_NL_GetStudentIntoByStudentID(@StudentID int)
RETURNS TABLE
AS
 RETURN SELECT * FROM Student s
        WHERE s.S_ID = @StudentID
     
--调用内联表值函数：
SELECT * FROM dbo.Fn_NL_GetStudentIntoByStudentID(1357)

--创建多声明表值函数：
--判断函数是否存在，存在则删除
IF EXISTS(SELECT *
    FROM   sysobjects
    WHERE  name = 'Fn_DSM_GetStudentIntoByStudentID')
DROP FUNCTION Fn_DSM_GetStudentIntoByStudentID
GO

CREATE FUNCTION Fn_DSM_GetStudentIntoByStudentID(@StudentID int)
RETURNS @TempTable TABLE([Name] varchar(20),
                         [GROUP] varchar(20))
AS
BEGIN
 INSERT @TempTable
 SELECT [S_NAME],[S_GROUP] FROM Student s
 WHERE s.S_ID = @StudentID
 RETURN
END

--调用多声明表值函数：
SELECT * FROM dbo.Fn_DSM_GetStudentIntoByStudentID(1357)

--61.标量函数、内联表值函数、多声明表值函数创建语法的区别和相同点？
/*
答：相同点：
    1.都要使用CREATE FUNCTION关键字
    2.返回关键字都是RETURNS关键字
    3.都要使用AS关键字作为函数体开始的标识
    不同点：
    1.返回值类型不同。
    2.调用方式不同。
*/

--62.查看函数内容的脚本关键字是什么？
/*
答：SP_HELPTEXT 函数名
*/
--代码示例：
SP_HELPTEXT Fn_DSM_GetStudentIntoByStudentID

-- 63.创建存储过程的关键字是什么？请写出示例代码？
/*
答：创建存储过程的关键字是：Create Proc
*/
--------------------------扩展存储过程示例------
DECLARE @filename varchar (20), @message varchar (20)
EXEC xp_sscanf 'sync -b -fauthors10.tmp -rrandom', 'sync -b -f%s -r%s',
   @filename OUTPUT, @message OUTPUT
SELECT @filename, @message
-------------------------系统存储过程示例------
Sp_Helptext xp_sscanf
-------------------------用户存储过程示例------
IF Exists(Select * from sysobjects
  where name='UP_GetStuNameByID'
)
drop proc UP_GetStuNameByID
GO
Create Proc UP_GetStuNameByID(@ID int)
as
select * from Student
where S_ID=@ID

drop proc UP_GetStuNameByID

-- 64.存储过程和函数的区别是什么？
/*
答：函数与存储过程的相同点：1.预先编译
     2.分为物理与临时的
     3.都可以提高SQL脚本安全性
 区别：1.函数必须又返回值，而存储过程可以没有返回值
       2.函数的返回值只有一个，存储过程的返回值可以有多个且类型可以不一样
  
*/
 
-- 65.什么是存储过程？存储过程的优点是什么？
/*
答：存储过程Stored Procedure： 是一组为了完成特定功能的SQL语句集经编译后存储在数据库中用户通过指定存储过程的名字并给出参数如果该存储过程带有参数来执行它
存储过程的优点：
 1.实现组件化编程
 2.提高执行效率
 3.减少网络流量
 4.提高安全性
存储过程特点：
1.预先编译（速度快）
2.安全性高
3.兼容性好（便于跨用）
4.可以重复使用
5.移植性差（缺点）
6.调用与声明参数必须一一匹配
*/
-- 66.存储过程分为几类?
/*
答：存储过程分类：
 1.xp_扩展存储过程（系统自带，也可自己编写）
 2.SP_系统存储过程（系统自带）
 3.UP_用户存储过程
 A.物理
 B.临时（局部：#、全部：##）
*/
-- 67.存储过程加密和重新编译的关键字是什么？
/*
答：存储过程的加密：
  With encryption（加密）
  Sp_decrypt（解密）
    存储过程重新编译:
  sp_recompile

*/
--示例
Create Proc UP_GetStudent
With encryption
as
select * from Student
Drop Proc UP_GetStudent--删除
--测试存储过程
Sp_HelpText UP_GetStudent

-- 68.查看存储过程、重命名存储过程的脚本是什么？
/*
答：查看存储过程:sp_helptext
    重命名存储过程:sp_rename 原存储过程名，新存储过程名
*/
--示例
sp_Rename UP_GetStudent,UP_GetStudentIfo

-- 69.什么是触发器？触发器的分类是什么？写出示例代码？
/*
答：触发器trigger：
 1.是特殊的存储过程（自己触发），
 它在表的数据发生变化时起作用，
 可以维护数据的完整性
 2.触发器是建立在表之上的，
 而约束可以建立在表上与列上，
 他们都是维护数据的完整性，都是数据库的对象
 3.触发器不能接受参数
Trigger种类：
 1.After： 在……之后触发（有insert、Delete、update）
 2.Instead of ：用……来替换insert、update、 Delete

*/
-- 70.创建触发器的关键字是什么？写出示例代码？
/*
答：Create Trigger StudentSeven
*/
--示例：
if exists(select *
  from sysobjects
  where name='tr_stuinsert'
)
drop trigger tr_stuinsert
go
create trigger tr_stuinsert
on student   --在student表上
for insert   --创建insert触发器
as
declare @s_name varchar(20)
select @s_name=s_name from inserted
if @s_name='dnf'
begin
 update student
 set s_name='阿什顿'
 where s_name='dnf'
end
else if @s_name='adf'
begin
 update student
 set s_name='阿萨德'
 where s_name='adf'
END
-- 71.删除触发器的语句是什么？写出示例代码？
/*
答：DROP TRIGGER StudentSeven
*/
DROP TRIGGER StudentSeven

-- 72.修改触发器的语句是什么？写出示例代码？
/*
答：Alter Trigger
*/
--示例
alter trigger tr_stuinsert
on student   --在student表上
for insert   --创建insert触发器
as
declare @s_name varchar(20)
select @s_name=s_name from inserted
if @s_name='dnf'
begin
 update student
 set s_name='阿什顿'
 where s_name='dnf'
end
else if @s_name='adf'
begin
 update student
 set s_name='阿萨德'
 where s_name='adf'
end 

-- 73.查看触发器的语句是什么？写出示例代码？
/*
答：sp_helptext tr_stuinsert --查看触发器的文本
*/
--查看触发器的文本
sp_helptext tr_stuinsert
--查看表上对象的信息
sp_depends student

-- 74.写出在修改指定列以后触发的触发器的示例代码？
--（两种方式）
/*第一种方式：UPDATE (列名1) AND UPDATE (列名2)
              来判断 列1和列2是否都修改
  第二种方式：COLUMNS_UPDATED()&列掩码来判断列是否修改。         
*/
--示例：第一种方式：
--判断触发器是否存在
IF EXISTS (SELECT name
    FROM   sysobjects
    WHERE  name = 'tr_UpdateAgeToName'
    AND    type = 'TR')
    DROP TRIGGER tr_UpdateAgeToName
GO
--创建触发器在StudentSeven1表上
CREATE TRIGGER tr_UpdateAgeToName    --创建触发器
ON StudentSeven1                     --在StudentSeven1表上
FOR UPDATE                           --为更新操作
AS                                   --作为（开始标志）
IF (UPDATE([AGE]))                   --如果[AGE]列被更新
BEGIN                                --BEGIN。。。END
 UPDATE StudentSeven1
 SET NAME = '被修改'
 --在INSERTED测试表中查找被更新记录的姓名
 WHERE [NAME] in (SELECT [NAME] FROM INSERTED)
END

--测试更新数据
--查看原始数据
SELECT * FROM StudentSeven1
--修改年龄
UPDATE StudentSeven1
SET AGE = 25
WHERE [ID] = 4

--修改学号
UPDATE StudentSeven1
SET S_NUM = 'TB0902645'
WHERE [ID] = 5

--示例：第二种方式：
--判断ID、S_NUM、AGE是否全部被更新  它的代码是1101（13）
--创建触发器在StudentSeven2表上
CREATE TRIGGER tr_UpdateAgeToName2    --创建触发器
ON StudentSeven2                     --在StudentSeven2表上
FOR UPDATE                           --为更新操作
AS
IF(COLUMNS_UPDATED()&13)=13
BEGIN
 UPDATE StudentSeven2
 SET NAME = '被修改'
 --在INSERTED测试表中查找被更新记录的姓名
 WHERE [NAME] in (SELECT [NAME] FROM INSERTED)
END

--查看原始数据
SELECT * FROM StudentSeven2
WHERE id = 4
--更新除了学号以外的所有的信息
UPDATE StudentSeven2
SET
NAME = '好啊',
AGE = 31
WHERE [ID] = 4

UPDATE StudentSeven2
SET ID = 555,
NAME = '你好啊'
WHERE [ID] = 6

-- 75.什么是嵌套触发器、递归触发器？
/*
   嵌套触发器:当一个触发器执行时能够触发
              另一个触发器，就称之为嵌套触发器。
   递归触发器：分间接递归和直接递归。
   间接递归：表A的触发器出发表B的触发器，
             表B的触发器又触发表A的触发器。
   直接递归：表A的触发器触发表A的触发器。          
   在默认的情况下SQL禁止直接递归。
   通常我们禁止使用递归触发器。           
*/

-- 76.创建游标的关键字？请写出代码示例？
/*
   创建游标的关键字:
   DECLARE 游标名称 游标类型 CURSOR
*/
--示例：定义一个游标
DECLARE cur_Student SCROLL CURSOR 
FOR
SELECT * FROM Student

-- 77.使用游标有哪几个步骤？分别是什么？
/*
   使用游标有五个步骤：
   a）定义游标：DECLARE 游标名称 游标类型 CURSOR
   b）打开游标  OPEN 游标名称
   c）检索游标  FETCH 检索方式 FROM 游标名称 INTO 变量
   d）关闭游标  CLOSE 游标名称
   e）释放游标  DEALLOCATE 游标名称
  
   SCROLL 类型游标的检索方式有：
   NEXT:向后检索
   PRIOR:向前检索
   FIRST：返回第一行
   LAST：返回最后一行
   ABSOLUTE n ：绝对定位到第N行
   （正数从上往下数，负从下往上数）
   RELATIVE n : 相对定位到第N行
   （正数从上往下数，负从下往上数）
*/
--示例：使用游标
--第一步：定义一个游标
DECLARE cur_Student SCROLL CURSOR 
FOR
SELECT s_Id FROM Student

--第二步：打开游标
OPEN cur_Student

--第三步：检索游标（向下检索）
DECLARE @tempID AS int
FETCH NEXT FROM cur_Student
INTO @tempID
PRINT @tempID

--第四步：关闭游标
CLOSE cur_Student

--第五步：释放游标
DEALLOCATE cur_Student

-- 78.全局变量@@CURSOR_ROWS、@@FETCH_STATUS的含义是什么？举例说明？
/*
   @@CURSOR_ROWS:用于显示最后执行游标记录集的行数。
   @@FETCH_STATUS：用于返回游标是否获取成功的。
*/
-- 79.事务的四大特性分别是什么？
/*
   原子性：事务作为一个工作单元，
           其中所有的SQL操作要么成功要么失败。
   一致性：事务执行或撤销以后，
           其中所有的SQL操作状态一致。
   持久性：事务一旦提交，所做的修改就保存下来。
   隔离性：多个事务同时进行的时候，相互之间没有干扰。
*/
-- 80.开始事务、回滚事务、提交事务的关键字是什么？举例说明？
/*
    开始事务:BEGIN TRANSACTION
    回滚事务：ROLLBACK TRANSACTION
    提交事务：COMMIT TRANSACTION
*/
select * FROM Student

--示例：向Student表中添加数据
BEGIN TRANSACTION
INSERT INTO Student
VALUES('张三','第一组',35)
SAVE TRANSACTION save1  --设置保存点
INSERT INTO Student
VALUES('李四','第二组',45)
ROLLBACK TRANSACTION save1  --回滚事务到保存点1
INSERT INTO Student
VALUES('王五','第二组',55)
COMMIT TRANSACTION --提交事务

-- 81.事务的隔离级别有哪些？
/* 事务的隔离级别有4种：
   READ UNCOMMITTED：允许未提交数据的读取。
   READ COMMITTED：不允许未提交数据的读取。
   REPEATABLE READ：实现可重复数据的读取。
   SERIALIZABLE：可串行化。
   其中四个级别安全性逐一递增。
*/
--示例：设置事务的隔离级别为 READ UNCOMMITTED
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- 82.SQL登陆方式有哪两种？它们的区别是什么？
/*
   SQL登陆方式有:仅WINDOWS集成身份验证
                 WINDOWS集成身份验证和SQL数据库身份验证
*/
-- 83.SQL授权和拒绝的关键字是什么？
/*
    SQL授权和拒绝属于T-SQL中的DCL（数据控制语言）
    SQL授权关键字GRANT
    SQL拒绝关键字DENY
*/
--示例：授权Student表的SELECT、INSERT权限给Userd用户
GRANT SELECT,INSERT  --授予SELECT、INSERT权限
ON Student           --在Student表上的
TO Userd             --给Userd用户

--示例：拒绝Student表的SELECT、INSERT权限给Userd用户
DENY SELECT,INSERT  --拒绝SELECT、INSERT权限
ON Student           --在Student表上的
TO Userd             --给Userd用户
--
-- 84.SQL Server是如何与Internet结合紧密的？
/*
   集成对XML的支持。
*/
-- 85.自定义类型会存在于哪个系统数据库中？
/*
   model数据库中。
*/
-- 86.备份的分类及各种备份间的区别？
/*
  备份的分类：完全备份、增量备份、差异备份、日志备份
*/
-- 87.哪种备份需要的空间最小适合频繁备份？
/*
  日志备份.
*/
-- 88.安全机制包括哪些？
/*
  操作系统安全性，服务器登录安全性，数据库使用安全性.
*/
-- 89.SQL Server登陆模式有哪些？
/*
  集成登陆、标准登陆
*/
-- 90.锁的分类及区别？
/*
  共享锁、排它锁、更新锁、意向锁、模式锁
*/
-- 91.死锁时，SQL Sever会怎样处理？
/*
  终止占用CPU较少的进程，并回滚事务产生一个1205的错误.
*/
-- 92.采用ADO对象模型编程的正确步骤？
/*
   5个步骤：(1)引用ADO对象库;
            (2)声明对象变量;
            (3)实例化对象;
            (4)使用对象变量访问数据库；
            (5)关闭对象并释放对象变量
*/
-- 93.常用ADO对象有哪些？
/*
   connection、recordset、command
*/
-- 94.网格控件的数据源属性是什么？
/*
   各种网格控件均有一个数据源属性（data source）
   可以用于和ADODC数据源的绑定，
   也可以和记录集对象进行绑定。
*/
-- 95.什么是Datagrid？
/*
   Datagrid 是有数据源控件，
   显示和编辑数据依赖打开的数据源。
*/