SQL知识点(一)
--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
--查看学号唯一约束 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('tom') )
--代码示例：使用CREATE DEFAULT 关键字创建 CREATE DEFAULT DF_NAME AS 'tom'
--代码示例：绑定默认值到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 ='tom'
--代码示例：绑定规则到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