知识点SQL语句学习及详细总结.docx

1、知识点SQL语句学习及详细总结一. 数据库简介和创建1. 系统数据库在安装好SQL SERVER后，系统会自动安装5个用于维护系统正常运行的系统数据库：（1）master：记录了SQL SERVER实例的所有系统级消息，包括实例围的元数据（如登录）、端点、服务器和系统配置设置。（2）msdb：供SQL SERVER 代理服务调度报警和作业以及记录操作员的使用，保存关于调度报警、作业、操作员等信息。（备份还原时）（3）model：SQL SERVER 实例上创建的所有数据库的模板。（4）tempdb：临时数据库，用于保存临时对象或中间结果集，为数据库的排列等操作提供一个临时工作空间。（每次启动都

2、会重新创建）（5）Resource：一个只读数据库，包含了SQL SERVER 的所有系统对象。（隐藏的数据库）2. 数据库的组成2.1 数据文件（1）主要数据文件：扩展名为 .mdf ，每个数据库有且只能有一个。（2）次要数据文件：扩展名为 .ndf ，可以没有或有多个。2.2 日志文件扩展名为 .ldf ，用于存放恢复数据库的所有日志信息。2.3 数据的存储分配（1）数据文件和日志文件的默认存放位置为：Programe FilesMicrosoft SQL ServerMSSQL.1MSSQLData文件夹。（2）数据的存储分配单位是数据页。一页表是一块8KB的连续磁盘空间。（3）页是存储

3、数据的最小空间分配单位，页的大小决定了数据库表中一行数据的最大大小。3. SQL语句数据库操作（1）创建数据库CREATE DATABASE database_name二. SQL基础SQL（Structured Query Language，结构化查询语言）是用户操作关系数据库的通用语言。1. SQL功能概述2. 系统提供的数据类型2.1 数值数据类型数据类型说明存储空间bitbit数据类型是整型，其值只能是0、1或空值。这种数据类型用于存储只有两种可能值的数据，如Yes 或No、True 或False 、On 或Off.（很省空间的一种数据类型，如果能够满足需求应该尽量多用。）1字节tin

4、yinttinyint 数据类型能存储从0到255 之间的整数。它在你只打算存储有限数目的数值时很有用。1字节smallintsmallint 数据类型可以存储从- 2的15次幂(-32768)到2的15次幂(32767)之间的整数。这种数据类型对存储一些常限定在特定围的数值型数据非常有用。（如果tinyint类型太单调不能满足您的需求，您可以考虑用smallint类型，因为这个类型相对也是比较安全的，不接受恶意脚本容的嵌入。）2字节intint 数据类型可以存储从- 2的31次幂(-2147483648)到2的31次幂 (2147483 647)之间的整数。存储到数据库的几乎所有数值型的数据

5、都可以用这种数据类型4个字节numeric（p,s）或 decimal(p,s)数据类型能用来存储从-10的38次幂-1到10的38次幂-1的固定精度和围的数值型数据。使用这种数据类型时，必须指定围和精度。围是小数点左右所能存储的数字的总位数。精度是小数点右边存储的数字的位数最多17个字节2.2 普通编码字符串类型数据类型说明存储空间char(n)char数据类型用来存储指定长度的定长非统一编码型的数据,n表示字符串的最大长度，取值围为18000 （若实际字符串控件小于n,系统自动在后面补空格）n字节_varchar(n)可变长度的字符串类型,n表示字符串的最大长度，取值围为18000。字符数

6、+2字节额外开销texttext 数据类型用来存储大量的非统一编码型字符数据。这种数据类型最多可以有231-1或20亿个字符.每个字符一个字节char 和 varchar的区别：若某列数据类型为varchar(20)，存字符串”Jone”时，只占用4个字节，而char（20）会在为填满的空间中填写空格。所以，varchar类型比char类型更节省空间，但它的开销会大一些，处理速度也慢一些。因此，n值比较小（小于4），用char类型更好些。2.3 统一编码字符串类型（Unicode）数据类型说明存储空间nchar(n)nchar 数据类型用来存储定长统一编码字符型数据。统一编码用双字节结构来存储

7、每个字符，而不是用单字节(普通文本中的情况)。它允许大量的扩展字符。此数据类型能存储4000种字符，使用的字节空间上增加了一倍.2n字节_nvarchar(n)nvarchar 数据类型用作变长的统一编码字符型数据。此数据类型能存储4000种字符，使用的字节空间增加了一倍.字符数+2字节额外开销ntext最多可存储2的30次方-1将近10亿个字符每个字符两个字节三. SQL数据操作语言1.数据查询语句1.1 查询语句的基本结构SELECT -需要哪些列From -来自哪表 WHERE GROUPBY HAVING ORDERBY SELECT子句用于指定输出的字段；FROM子句用于指定数据的来

8、源；WHERE子句用于指定数据的选择条件；GROUP BY子句用于对检索到的记录进行分组；HAVING 子句用于指定组的选择条件；ORDER BY 子句用于对查询的结果进行排序；以上子句中，SELECT 子句和FROM子句是必需的，其它是可选的。1.2 单表查询1.2.1选择表中若干列（1）查询指定的列SELECT列名FROM表名1例子：SELECT Sname,Sno FROM Student（2）查询全部列SELECT * FROM表名1例子：SELECT * FROM Student（3）查询经过计算的列SELECT列名FROM表名1例子：SELECT Sname,year(getdat

9、a() - year(Birthdate) FROM Student1.2.2 选择表中的若干元祖（1）消除取值相同的行：DISTINCTSELECTDISTINCT Sno FROM表名1例子：SELECT DISTINCT Sno FROM Student（2）查询满足条件的元祖查询条件谓词比较=、=、=、!=、!、!确定围BETWEENAND、 NOT BETWEENAND确定集合IN 、NOT IN字符匹配LIKE 、NOT LIKE空值IS NULL、IS NOT NULL多重条件（逻辑谓词）AND、ORa.比较大小例子：SELECT Sname FROM Student WHERE

10、 year(getdata() - year(Birthdate) = 80 AND Grade = 90例子：SELECT Sno,o FROM SC WHERE Grade NOT BETWEEN 80 AND 90此查询等价于：SELECT Sno,o FROM SC WHERE Grade 90c. 确定集合IN运算符的含义：当列中的值和集合中的某个常量值相等时，结果为True。NOT IN运算符的含义：当列中的值和集合中的全部常量值都不相等时，结果为True。例子：SELECT Sno FROM Student WHERE Dept IN (信息管理系,计算机系)此查询等价于：SEL

11、ECT Sno FROM Student WHERE Dept = 信息管理系 OR Dept = 计算机系)例子：SELECT Sno FROM Student WHERE Dept NOT IN (信息管理系,计算机系)此查询等价于：SELECT Sno FROM Student WHERE Dept != 信息管理系 AND Dept != 计算机系)d. 字符串匹配Like运算符用于查找指定列中与匹配串匹配的元祖。列名 NOT LIKE 通配符含义_（下划线）匹配任意一个字符%（百分号）匹配0个或多个字符匹配中的任意一个字符。如abcd表示匹配abcd其中任何一个，若是连续的，可以用

12、- 表示，如a-d不匹配中的任意一个字符。如abcd表示不匹配abcd其中任何一个，若是连续的，可以用 - 表示，如a-d例子：(查询姓“”的学生详细信息)SELECT * FROM Student WHERE Sname LIKE%(查询不姓“”的学生详细信息)SELECT * FROM Student WHERE Sname NOTLIKE%（查询姓“”、“”的学生详细信息）SELECT * FROM Student WHERE Sname LIKE%（查询名字的第二个字为“小” 或 “大”的学生详细信息）SELECT * FROM Student WHERE Sname LIKE_小大%

13、e. 涉及空值的查询空值（NULL）在数据库中有特殊含义，表示当前不确定或未知的值。判断是否为NULL时，不可用普通的比较运算符，需用IS NULL例子：SELECT Sno FROM Student WHERE Grade IS NULL1.2.3 对查询结果进行排序将查询结果按照指定的顺序显示。ASC表示按列值升序排列（从上往下，值从大到小）。DESC表示按列值降序排列（从上往下，值从小到大）。默认为ASC。ORDERBY ASC|DESC例子：SELECT Sno,Grade FROM SC ORDER BY Grade DESC1.2.4 使用聚合函数统计数据聚合函数也称为统计函数或集

14、合函数，作用是对一组值进行计算并返回一个统计结果。聚合函数含义COUNT(*)统计表中元祖的个数COUNT(DISTINCT)统计本列的非空列值个数SUM()计算列值的和值（必须是数值型列）AVG()计算列值的平均值（必须是数值型列）MAX()计算列值的最大值MIN()计算列值的最小值上述函数除 COUNT(*) 外，其它函数在计算过程中均忽略NULL值（统计学生总人数）SELECTCOUNT(*) FROM Student（统计“001”学号学生的考试平均成绩）SELECTAVG(Grade) FROM SC WHERE Sno = 001（查询“C001”号课程考试成绩的最高分和最低分）S

15、ELECTMAX(Grade) 最高分,MIN(Grade) 最低分FROM SC WHERE o = C001聚合函数不能出现在WHERE子句中！1.2.5 对数据进行分组统计需要先对数据进行分组，然后再对每个组进行统计。分组子句GROUP BY。在一个查询语句中，可以用多个列进行分组。分组子句跟在WHERE子句的后面：GROUPBY,.nHAVING （1）使用GROUP BY 子句（统计每门课程的选课人数，列出课程号和选课人数）SELECT o as课程号, COUNT(Sno) as选课人数From SC GroupBY o（统计每个学生的选课门数和平均成绩）SELECT Sno 学号

16、, COUNT(*) 选课门数,AVG(Grade) 平均成绩 From SC Group BY Sno带WHERE子句的分组（统计每个系的女生人数）SELECT Dept, COUNT(*)女生人数From Student Where Sex = 女GroupBY Dept（2）使用HAVING 子句HAVING子句用于对分组后的统计结果再进行筛选，它的功能与WHERE子句类似，它用于组而不是单个记录。在HAVING子句中可以使用聚合函数，但在WHERE子句中不能，通常与GROUP子句一起使用。（查询选课门数超过3门的学生的学号和选课门数）SELECT Sno 学号, COUNT(*) 选课

17、门数,AVG(Grade) 平均成绩 From SC Group BY Sno HAVING COUNT(*) 3（3）WHERE 、GROUP BY 、HAVING 的作用及执行顺序WHERE子句用于筛选FROM子句中指定的数据所产生的行数据。GROUP BY子句用于对经WHERE子句筛选后的结果数据进行分组。HAVING子句用于对分组后的统计结果再进行筛选。可以分组操作之前应用的筛选条件，在WHERE子句中指定它们更有效，这样可以减少参与分组的数据行。在HAVING子句中指定的筛选条件应该是那些必须在执行分组操作之后应用的筛选条件。（查询计算机系和信息管理系每个系的学生人数）第一种：SEL

18、ECT Dept,COUNT(*) FROM Student GROUPBY Dept Having Dept in(计算机系,信息管理系)第二种：SELECT Dept,COUNT(*) FROM Student WHERE Dept in (计算机系,信息管理系)GROUPBY Dept 以上例子比较：第一种是按照系分组好了之后，只采取所有系中的两个系，显然效率不高。而第二种是先进行WHERE筛选条件之后，再进行GROUP BY 计算，显示更好。1.3 多表连接查询若一个查询同时涉及到两或以上的表，则称为连接查询。1.3.1 连接使用连接时，如果两个表的相关字段满足条件，则从两个表中提取数

19、据组成新的记录。FROM表1 INNER JOIN表2ON 1注意：连接条件中的连接字段必须是可比的，必须是语义相同的列。（查询学生及选课的详细信息）SELECT * FROM Student INNERJOIN SC ON Student.Sno = SC.Sno（查询计算机系学生的选课情况，列出该学生的名字、所修课程号、成绩）-行选择条件SELECT Sname,o,Grade FROM Student INNERJOIN SC ON Student.Sno = SC.Sno WHERE Dept = 计算机系（统计每个系的平均成绩） -分组的多表查询SELECT Dept,AVG(Gra

20、de) AS AverageGrade FROM Student S INNERJOIN SC ON S.Sno = SC.Sno GroupBY Dept（统计计算机系每个学生的选课门数、平均成绩、最高成绩、最低成绩）-分组和行选择条件的多表连接查询SELECT Sno,COUNT(*),AVG(Grade),MAX(Grade),MIN(Grade) FROM Student S JOIN SC ON S.Sno = SC.Sno WHERE Dept = 计算机系GroupBY Dept1.3.2 自连接自连接是一种特殊的连接，相互连接的表在物理上是一表，但在逻辑上可以看做是两表。FRO

21、M表1AS T1 JOIN表1AS T21通过为表取别名的方法，可以让物理上的一表在逻辑上成为两表。（一定要为表取别名！）（查询与晨在同一个系学习的学生的、所在系）SELECT S1.Sname,S1.Dept FROM Student S1 JOIN Student S2 ON S1.Dept = S2.Dept -同一个系的学生WHERE S2.Sname =晨 -S2表作为查询条件AND S1.Sname != 晨 -S1表作为结果表，并从中去掉晨本人信息1.3.3 外连接在连接操作中，只有满足条件的元祖才能出现在查询结果集中。外连接是只限制一表中的数据必须满足条件，而另一表的数据可以不

22、满足条件。FROM 表1LEFT|RIGHT OUTER JOIN表2ON 1LEFT OUTER JOIN称为左外连接，含义是限制表2中的数据必须满足条件，但不管表1中的数据是否满足条件，均输出表1中的数据。LEFT OUTER JOIN称为右外连接，含义是限制表1中的数据必须满足条件，但不管表2中的数据是否满足条件，均输出表2中的数据。连接与外连接的区别：连接：表A与表B进行连接，则结果为两个表中满足条件的记录集，即C部分。外连接：如果表A和表B进行左外连接，则结果为记录集A + 记录集C；如果表A和表B进行右外连接，则结果为记录集B + 记录集C。（查询没有人选的选修课程名）SELECT

23、 ame FROM Course C LEFTJOIN SC ON C.o = SC.o WHERE SC.o ISNULL例子解析：如果存在部分课程为被人选择，则必定在Course表中有但在SC表中没有出现，即在进行外连接时没人选的课程在与SC表构成的连接结果集中，对应的Sno、o、Grade列必定为空，所以只需*在连接后的结果中选出*SC表中Sno或o为空的元祖即可。（统计计算机系每个学生的选课门数，包括没选课的学生）SELECT S.Sno AS学号,COUNT(SC.o) AS选课门数FROM Student S LEFTJOIN SC ON S.Sno = SC.Sno WHERE

24、Dept = 计算机系GROUPBY S.Sno例子解析：上述例子要求统计每个学生的.，所以在GROUP BY分组时，是按照学生表中的学号来分。而对于聚合函数COUNT，上述要求统计每个学生的选课门数，若写成COUNT(S.Sno)或COUNT(*)，则对没选课的学生都返回1，因为在外连接结果中，S.Sno不会是NULL，而COUNT(*)函数本身也不考虑NULL，它是直接对元祖个数进行计数。注意：在对外连接的结果进行分组、统计等操作时，一定要注意分组依据列和统计列的选择。1.4 使用TOP限制结果集行数在使用SELECT语句进行查询时，有时只需要前几行数据。TOP (expression)

25、PERCENTWITH TIES1expression：指定返回行数的数值表达式。如果指定了PERCENT，expression将隐式转换成float，否则是bigintPERCENT：指定只返回结果集中前 expression% 行数据。WITH TIES：指定从基本结果集中返回额外的数据行（只有在SELECT子句中包含了ORDER BY子句时，才能使用）。TOP谓词写在SELECT单词的后面（如果有DISTINCT，则在DISTINCT后面）。（查询考试成绩最高的3个成绩。列出学号、课程号、成绩）SELECT TOP 3 Sno,o,Grade FROM SC ORDERBY Grade

26、DESC若要包括并列第3名的成绩：SELECT TOP 3 Sno,o,Grade WITH TIES FROM SC ORDERBY Grade DESC2.数据更改功能2.1 插入数据INSERTINTO表名(列名) VALUES (值)1（1）简单插入语句INSERTINTO Student VALUES (001,东,男,1996/6/23,信息管理系)1（2）多行插入语句INSERTINTO SC VALUES(001,C001,90), (001,C002,30), (001,C005,NULL)（3）不按表顺序插入语句按与表列顺序不同的顺序插入数据INSERTINTO Stude

27、nt(Sno,Sname,Sex,Dept) VALUES (001,东,男,1996/6/23,信息管理系)2.2 更新数据UPDATE表名SET列名 = 值（1）无条件更新UPDATE SC SET Grade = Grade+10（2）有条件更新（将“C001”号课程的学分改成5分）UPDATE Course SET Grade = 5WHERE o = C001（将计算机系全体学生的成绩加5分）UPDATE SC SET Grade = Grade+5FROM SC JOIN Student S ON S.Sno = SC.Sno WHERE Dept = 计算机系2.3 删除数据DE

28、LETE TOP (expression) PERCENTFROM表名（1）无条件删除DELETEFROM Student（2）有条件删除（删除所有考试成绩不合格的学生的选课记录）DELETEFROM SC WHERE Grade 60（删除Student表中2.5%的行数据）DELETE TOP (2.5) PERCENT FROM Student四. 高级查询1. CASE函数CASE函数是一种多分支函数，它可以根据条件列表的值返回多个可能的结果表达式中的一个。1.1 简单CASE函数CASE input_expressionWHEN when_expression THEN result_expression .n ELSE else_expressionENDinput_expression：所计算的表达式，可以是一个变量名、字段名、函数或子查询。when_expression：要与input _expression进行比较的简单表达式。简单表达式中不可包含比较运算法，只需给出被比较的表达式或值。else_expression：比较结果均不为TRUE时返回的表达式。（查询选了JAVA课程的学生的学号、所在系