知识点、SQL语句学习及详细总结.docx
《知识点、SQL语句学习及详细总结.docx》由会员分享，可在线阅读，更多相关《知识点、SQL语句学习及详细总结.docx（27页珍藏版）》请在冰点文库上搜索。
一.数据库简介和创建
1.系统数据库
在安装好SQLSERVER后，系统会自动安装5个用于维护系统正常运行的系统数据库：
（1）master：
记录了SQLSERVER实例的所有系统级消息，包括实例范围的元数据（如登录帐号）、端点、链接服务器和系统配置设置。
（2）msdb：
供SQLSERVER代理服务调度报警和作业以及记录操作员的使用，保存关于调度报警、作业、操作员等信息。
（备份还原时）
（3）model：
SQLSERVER实例上创建的所有数据库的模板。
（4）tempdb：
临时数据库，用于保存临时对象或中间结果集，为数据库的排列等操作提供一个临时工作空间。
（每次启动都会重新创建）
（5）Resource：
一个只读数据库，包含了SQLSERVER的所有系统对象。
（隐藏的数据库）
2.数据库的组成
2.1数据文件
（1）主要数据文件：
扩展名为.mdf，每个数据库有且只能有一个。
（2）次要数据文件：
扩展名为.ndf，可以没有或有多个。
2.2日志文件
扩展名为.ldf，用于存放恢复数据库的所有日志信息。
2.3数据的存储分配
（1）数据文件和日志文件的默认存放位置为：
\ProgrameFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\Data文件夹。
（2）数据的存储分配单位是数据页。
一页表是一块8KB的连续磁盘空间。
（3）页是存储数据的最小空间分配单位，页的大小决定了数据库表中一行数据的最大大小。
3.SQL语句数据库操作
（1）创建数据库
CREATEDATABASEdatabase_name
二.SQL基础
SQL（StructuredQueryLanguage，结构化查询语言）是用户操作关系数据库的通用语言。
1.SQL功能概述
2.系统提供的数据类型
2.1数值数据类型
数据类型
说明
存储空间
bit
bit数据类型是整型，其值只能是0、1或空值。
这种数据类型用于存储只有两种可能值的数据，如Yes或No、True或False、On或Off. （很省空间的一种数据类型，如果能够满足需求应该尽量多用。
）
1字节
tinyint
tinyint数据类型能存储从0到255之间的整数。
它在你只打算存储有限数目的数值时很有用。
1字节
smallint
smallint数据类型可以存储从-2的15次幂（-32768）到2的15次幂（32767）之间的整数。
这种数据类型对存储一些常限定在特定范围内的数值型数据非常有用。
（如果tinyint类型太单调不能满足您的需求，您可以考虑用smallint类型，因为这个类型相对也是比较安全的，不接受恶意脚本内容的嵌入。
）
2字节
int
int数据类型可以存储从-2的31次幂（-2147483648）到2的31次幂（2147483647）之间的整数。
存储到数据库的几乎所有数值型的数据都可以用这种数据类型
4个字节
numeric（p,s）或decimal（p,s）
数据类型能用来存储从-10的38次幂-1到10的38次幂-1的固定精度和范围的数值型数据。
使用这种数据类型时，必须指定范围和精度。
范围是小数点左右所能存储的数字的总位数。
精度是小数点右边存储的数字的位数
最多17个字节
2.2普通编码字符串类型
数据类型
说明
存储空间
char（n）
char数据类型用来存储指定长度的定长非统一编码型的数据,n表示字符串的最大长度，取值范围为1~8000（若实际字符串控件小于n,系统自动在后面补空格）
n字节_______
varchar（n）
可变长度的字符串类型,n表示字符串的最大长度，取值范围为1~8000。
字符数+2字节额外开销
text
text数据类型用来存储大量的非统一编码型字符数据。
这种数据类型最多可以有231-1或20亿个字符.
每个字符一个字节
char和varchar的区别：
若某列数据类型为varchar（20），存字符串”Jone”时，只占用4个字节，而char（20）会在为填满的空间中填写空格。
所以， varchar类型比char类型更节省空间，但它的开销会大一些，处理速度也慢一些。
因此，n值比较小（小于4），用char类型更好些。
2.3统一编码字符串类型（Unicode）
数据类型
说明
存储空间
nchar（n）
nchar数据类型用来存储定长统一编码字符型数据。
统一编码用双字节结构来存储每个字符，而不是用单字节（普通文本中的情况）。
它允许大量的扩展字符。
此数据类型能存储4000种字符，使用的字节空间上增加了一倍.
2n字节_______
nvarchar（n）
nvarchar数据类型用作变长的统一编码字符型数据。
此数据类型能存储4000种字符，使用的字节空间增加了一倍.
字符数+2字节额外开销
ntext
最多可存储2的30次方-1将近10亿个字符
每个字符两个字节
三.SQL数据操作语言
1.数据查询语句
1.1查询语句的基本结构
SELECT<目标列名序列>--需要哪些列
From<表名>--来自哪张表
[WHERE<行选择条件>]
[GROUPBY<分组依据列>]
[HAVING<组>]
[ORDERBY<排序依据列>]
SELECT子句用于指定输出的字段；
FROM子句用于指定数据的来源；
WHERE子句用于指定数据的选择条件；
GROUPBY子句用于对检索到的记录进行分组；
HAVING子句用于指定组的选择条件；
ORDERBY子句用于对查询的结果进行排序；
以上子句中，SELECT子句和FROM子句是必需的，其它是可选的。
1.2单表查询
1.2.1选择表中若干列
（1）查询指定的列
SELECT列名FROM表名
·1
例子：
SELECTSname,SnoFROMStudent
（2）查询全部列
SELECT*FROM表名
·1
例子：
SELECT*FROMStudent
（3）查询经过计算的列
SELECT列名FROM表名
·1
例子：
SELECTSname,year（getdata（））-year（Birthdate）FROMStudent
1.2.2选择表中的若干元祖
（1）消除取值相同的行：
DISTINCT
SELECTDISTINCTSnoFROM表名
·1
例子：
SELECTDISTINCTSnoFROMStudent
（2）查询满足条件的元祖
查询条件
谓词
比较
=、>、>=、<=、<、<>、!
=、!
>、!
<
确定范围
BETWEEN…AND、NOTBETWEEN…AND
确定集合
IN、NOTIN
字符匹配
LIKE、NOTLIKE
空值
ISNULL、ISNOTNULL
多重条件（逻辑谓词）
AND、OR
a.比较大小
例子：
SELECTSnameFROMStudentWHEREyear（getdata（））-year（Birthdate）<20
b.确定范围
BETWEEN…AND和NOTBETWEEN…AND可用于查找属性值在或不在指定范围。
列名|表达式|[NOT]BETWEEN下限值AND上限值
·1
BETWEEN…AND 代表的范围是在上限值和下限值之间（包括边界值），即为true。
NOTBETWEEN…AND 代表的范围是不在上限值和下限值之间（不包括边界值），即为true。
（若判断值为边界值时，为false）
例子：
SELECTSno,CnoFROMSCWHEREGradeBETWEEN80AND90
此查询等价于：
SELECTSno,CnoFROMSCWHEREGrade>=80ANDGrade<=90
例子：
SELECTSno,CnoFROMSCWHEREGradeNOTBETWEEN80AND90
此查询等价于：
SELECTSno,CnoFROMSCWHEREGrade<80ORGrade>90
c.确定集合
IN运算符的含义：
当列中的值和集合中的某个常量值相等时，结果为True。
NOTIN运算符的含义：
当列中的值和集合中的全部常量值都不相等时，结果为True。
例子：
SELECTSnoFROMStudentWHEREDeptIN（'信息管理系','计算机系'）
此查询等价于：
SELECTSnoFROMStudentWHEREDept='信息管理系'ORDept='计算机系'）
例子：
SELECTSnoFROMStudentWHEREDeptNOTIN（'信息管理系','计算机系'）
此查询等价于：
SELECTSnoFROMStudentWHEREDept!
='信息管理系'ANDDept!
='计算机系'）
d.字符串匹配
Like运算符用于查找指定列中与匹配串匹配的元祖。
列名[NOT]LIKE<匹配串>
通配符
含义
_（下划线）
匹配任意一个字符
%（百分号）
匹配0个或多个字符
[]
匹配[]中的任意一个字符。
如[abcd]表示匹配abcd其中任何一个，若是连续的，可以用-表示，如[a-d]
[^]
不匹配[]中的任意一个字符。
如[^abcd]表示不匹配abcd其中任何一个，若是连续的，可以用-表示，如[^a-d]
例子：
（查询姓“张”的学生详细信息）
SELECT*FROMStudentWHERESnameLIKE'张%'
（查询不姓“张”的学生详细信息）
SELECT*FROMStudentWHERESnameNOTLIKE'张%'
（查询姓“张”、“李”的学生详细信息）
SELECT*FROMStudentWHERESnameLIKE'[张李]%'
（查询名字的第二个字为“小”或“大”的学生详细信息）
SELECT*FROMStudentWHERESnameLIKE'_[小大]%'
e.涉及空值的查询
空值（NULL）在数据库中有特殊含义，表示当前不确定或未知的值。
判断是否为NULL时，不可用普通的比较运算符，需用ISNULL
例子：
SELECTSnoFROMStudentWHEREGradeISNULL
1.2.3对查询结果进行排序
将查询结果按照指定的顺序显示。
ASC表示按列值升序排列（从上往下，值从大到小）。
DESC表示按列值降序排列（从上往下，值从小到大）。
默认为ASC。
ORDERBY<列名>[ASC|DESC]
例子：
SELECTSno,GradeFROMSCORDERBYGradeDESC
1.2.4使用聚合函数统计数据
聚合函数也称为统计函数或集合函数，作用是对一组值进行计算并返回一个统计结果。
聚合函数
含义
COUNT（*）
统计表中元祖的个数
COUNT（[DISTINCT]<列名>）
统计本列的非空列值个数
SUM（<列名>）
计算列值的和值（必须是数值型列）
AVG（<列名>）
计算列值的平均值（必须是数值型列）
MAX（<列名>）
计算列值的最大值
MIN（<列名>）
计算列值的最小值
上述函数除COUNT（*）外，其它函数在计算过程中均忽略NULL值
（统计学生总人数）
SELECTCOUNT（*）FROMStudent
（统计“001”学号学生的考试平均成绩）
SELECTAVG（Grade）FROMSCWHERESno='001'
（查询“C001”号课程考试成绩的最高分和最低分）
SELECTMAX（Grade）最高分,MIN（Grade）最低分FROMSCWHERECno='C001'
聚合函数不能出现在WHERE子句中！
1.2.5对数据进行分组统计
需要先对数据进行分组，然后再对每个组进行统计。
分组子句GROUPBY。
在一个查询语句中，可以用多个列进行分组。
分组子句跟在WHERE子句的后面：
GROUPBY<分组依据列>[,...n]
[HAVING<组筛选条件>]
（1）使用GROUPBY子句
（统计每门课程的选课人数，列出课程号和选课人数）
SELECTCnoas课程号,COUNT（Sno）as选课人数FromSCGroupBYCno
（统计每个学生的选课门数和平均成绩）
SELECTSno学号,COUNT（*）选课门数,AVG（Grade）平均成绩FromSCGroupBYSno
带WHERE子句的分组（统计每个系的女生人数）
SELECTDept,COUNT（*）女生人数FromStudentWhereSex='女'GroupBYDept
（2）使用HAVING子句
HAVING子句用于对分组后的统计结果再进行筛选，它的功能与WHERE子句类似，它用于组而不是单个记录。
在HAVING子句中可以使用聚合函数，但在WHERE子句中不能，通常与GROUP子句一起使用。
（查询选课门数超过3门的学生的学号和选课门数）
SELECTSno学号,COUNT（*）选课门数,AVG（Grade）平均成绩FromSCGroupBYSnoHAVINGCOUNT（*）>3
（3）WHERE、GROUPBY、HAVING的作用及执行顺序
·WHERE子句用于筛选FROM子句中指定的数据所产生的行数据。
·GROUPBY 子句用于对经 WHERE 子句筛选后的结果数据进行分组。
·HAVING 子句用于对分组后的统计结果再进行筛选。
可以分组操作之前应用的筛选条件，在WHERE子句中指定它们更有效，这样可以减少参与分组的数据行。
在HAVING子句中指定的筛选条件应该是那些必须在执行分组操作之后应用的筛选条件。
（查询计算机系和信息管理系每个系的学生人数）
第一种：
SELECTDept,COUNT（*）FROMStudentGROUPBYDeptHavingDeptin（'计算机系','信息管理系'）
第二种：
SELECTDept,COUNT（*）FROMStudentWHEREDeptin（'计算机系','信息管理系'）GROUPBYDept
以上例子比较：
第一种是按照系分组好了之后，只采取所有系中的两个系，显然效率不高。
而第二种是先进行WHERE筛选条件之后，再进行GROUPBY计算，显示更好。
1.3多表连接查询
若一个查询同时涉及到两张或以上的表，则称为连接查询。
1.3.1内连接
使用内连接时，如果两个表的相关字段满足条件，则从两个表中提取数据组成新的记录。
FROM表1[INNER]JOIN表2ON<连接条件>
·1
注意：
连接条件中的连接字段必须是可比的，必须是语义相同的列。
（查询学生及选课的详细信息）
SELECT*FROMStudentINNERJOINSCONStudent.Sno=SC.Sno
（查询计算机系学生的选课情况，列出该学生的名字、所修课程号、成绩）------行选择条件
SELECTSname,Cno,GradeFROMStudentINNERJOINSCONStudent.Sno=SC.SnoWHEREDept='计算机系'
（统计每个系的平均成绩）------分组的多表查询
SELECTDept,AVG（Grade）ASAverageGradeFROMStudentSINNERJOINSCONS.Sno=SC.SnoGroupBYDept
（统计计算机系每个学生的选课门数、平均成绩、最高成绩、最低成绩）------分组和行选择条件的多表连接查询
SELECTSno,COUNT（*）,AVG（Grade）,MAX（Grade）,MIN（Grade）FROMStudentSJOINSCONS.Sno=SC.SnoWHEREDept='计算机系'GroupBYDept
1.3.2自连接
自连接是一种特殊的内连接，相互连接的表在物理上是一张表，但在逻辑上可以看做是两张表。
FROM表1AST1JOIN表1AST2
·1
通过为表取别名的方法，可以让物理上的一张表在逻辑上成为两张表。
（一定要为表取别名！
）
（查询与刘晨在同一个系学习的学生的姓名、所在系）
SELECTS1.Sname,S1.DeptFROMStudentS1JOINStudentS2
ONS1.Dept=S2.Dept---同一个系的学生
WHERES2.Sname='刘晨'---S2表作为查询条件
ANDS1.Sname!
='刘晨'----S1表作为结果表，并从中去掉‘刘晨’本人信息
1.3.3外连接
在内连接操作中，只有满足条件的元祖才能出现在查询结果集中。
外连接是只限制一张表中的数据必须满足条件，而另一张表的数据可以不满足条件。
FROM表1LEFT|RIGHT[OUTER]JOIN表2ON<连接条件>
·1
LEFT[OUTER]JOIN 称为左外连接，含义是限制表2中的数据必须满足条件，但不管表1中的数据是否满足条件，均输出表1中的数据。
LEFT[OUTER]JOIN 称为右外连接，含义是限制表1中的数据必须满足条件，但不管表2中的数据是否满足条件，均输出表2中的数据。
内连接与外连接的区别：
内连接：
表A与表B进行内连接，则结果为两个表中满足条件的记录集，即C部分。
外连接：
如果表A和表B进行左外连接，则结果为记录集A+记录集C；如果表A和表B进行右外连接，则结果为记录集B+记录集C。
（查询没有人选的选修课程名）
SELECTCnameFROMCourseCLEFTJOINSCONC.Cno=SC.CnoWHERESC.CnoISNULL
例子解析：
如果存在部分课程为被人选择，则必定在Course表中有但在SC表中没有出现，即在进行外连接时没人选的课程在与SC表构成的连接结果集中，对应的Sno、Cno、Grade列必定为空，所以只需**在连接后的结果中选出**SC表中Sno或Cno为空的元祖即可。
（统计计算机系每个学生的选课门数，包括没选课的学生）
SELECTS.SnoAS学号,COUNT（SC.Cno）AS选课门数FROMStudentSLEFTJOINSCONS.Sno=SC.SnoWHEREDept='计算机系'GROUPBYS.Sno
例子解析：
上述例子要求统计每个学生的….，所以在GROUPBY分组时，是按照学生表中的学号来分。
而对于聚合函数COUNT，上述要求统计每个学生的选课门数，若写成COUNT（S.Sno）或COUNT（*），则对没选课的学生都返回1，因为在外连接结果中，S.Sno不会是NULL，而COUNT（*）函数本身也不考虑NULL，它是直接对元祖个数进行计数。
注意：
在对外连接的结果进行分组、统计等操作时，一定要注意分组依据列和统计列的选择。
1.4使用TOP限制结果集行数
在使用SELECT语句进行查询时，有时只需要前几行数据。
TOP（expression）[PERCENT][WITHTIES]
·1
·expression：
指定返回行数的数值表达式。
如果指定了PERCENT，expression将隐式转换成float，否则是bigint
·PERCENT：
指定只返回结果集中前expression%行数据。
·WITHTIES：
指定从基本结果集中返回额外的数据行（只有在SELECT子句中包含了ORDERBY子句时，才能使用）。
TOP谓词写在SELECT单词的后面（如果有DISTINCT，则在DISTINCT后面）。
（查询考试成绩最高的3个成绩。
列出学号、课程号、成绩）
SELECTTOP3Sno,Cno,GradeFROMSCORDERBYGradeDESC
若要包括并列第3名的成绩：
SELECTTOP3Sno,Cno,GradeWITHTIESFROMSCORDERBYGradeDESC
2.数据更改功能
2.1插入数据
INSERTINTO表名[（列名）]VALUES（值）
·1
（1）简单插入语句
INSERTINTOStudentVALUES（'001','陈东','男','1996/6/23','信息管理系'）
·1
（2）多行插入语句
INSERTINTOSCVALUES（'001','C001',90）,
（'001','C002',30）,
（'001','C005',NULL）
（3）不按表顺序插入语句
按与表列顺序不同的顺序插入数据
INSERTINTOStudent（Sno,Sname,Sex,Dept）VALUES（'001','陈东','男','1996/6/23','信息管理系'）
2.2更新数据
UPDATE表名SET列名=值
（1）无条件更新
UPDATESCSETGrade=Grade+10
（2）有条件更新
（将“C001”号课程的学分改成5分）
UPDATECourseSETGrade=5WHERECno='C001'
（将计算机系全体学生的成绩加5分）
UPDATESCSETGrade=Grade+5FROMSCJOINStudentSONS.Sno=SC.SnoWHEREDept='计算机系'
2.3删除数据
DELETE[TOP（expression）[PERCENT]]
FROM表名
（1）无条件删除
DELETEFROMStudent
（2）有条件删除
（删除所有考试成绩不合格的学生的选课记录）
DELETEFROMSCWHEREGrade<60
（删除Student表中2.5%的行数据）
DELETETOP（2.5）PERCENTFROMStudent
四.高级查询
1.CASE函数
CASE函数是一种多分支函数，它可以根据条件列表的值返回多个可能的结果表达式中的一个。
1.1简单CASE函数
CASEinput_expression
WHE
升级会员 