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

《2022年知识点SQL语句学习及详细总结 .docx》由会员分享，可在线阅读，更多相关《2022年知识点SQL语句学习及详细总结 .docx（40页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、；一. 数据库简介和创建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 ）数据文件和日志文件的默认存放位置为：ProgrameFilesMicrosoftSQL ServerMSSQL.1MSSQLData文件夹；（2） ）数据的储备安排单位是数据

3、页；一页表是一块8KB 的连续磁盘空间；（3） ）页是储备数据的最小空间安排单位,页的大小打算了数据库表中一行数据的最大大小；3. SQL 语句 数据库操作（ 1）创建数据库CREATEDATABASEdatabase_name二. SQL 基础SQL （StructuredQuery Language ,结构化查询语言）是用户操作关系数据库的通用语言；1. SQL 功能概述2. 系统供应的数据类型2.1 数值数据类型储备数据类型说明空间储备数据类型说明空间bitbit 数据类型是整型,其值只能是0 、1 或空值；这种数据类型用于储备只有两种可1 字能值的数据,如 Yes或 No 、True或

4、 False、On或 Off. （很省空间的一种数据节类型,假如能够满意需求应当尽量多用；）tinyinttinyint数据类型能储备从0 到 255之间的整数；它在你只准备储备有限数目的数1 字值时很有用；节smallint数据类型可以储备从 - 2 的 15 次幂 -32768 到 2 的 15 次幂 32767 之间的smallint整数； 这种数据类型对储备一些常限定在特定范畴内的数值型数据特别有用；（如2 字果 tinyint 类型太单调不能满意您的需求,您可以考虑用 smallint类型, 由于这个类节型相对也是比较安全的,不接受恶意脚本内容的嵌入；）intint数据类型可以储备从

5、 - 2 的 31 次幂 -2147483648到 2 的 31 次幂 21474834 个647 之间的整数；储备到数据库的几乎全部数值型的数据都可以用这种数据类型字节numeric（ p,s ） 或decimalp,s数据类型能用来储备从 -10 的 38 次幂 -1 到 10 的 38 次幂 -1 的固定精度和范畴的数值型数据；使用这种数据类型时,必需指定范畴和精度； 范畴是小数点左右所能储备的数字的总位数；精度是小数点右边储备的数字的位数最多17 个字节2.2 一般编码字符串类型数据类型说明charnchar 数据类型用来储备指定长度的定长非统一编码型的数据,n 表示字符串的最大长度,

6、取值范畴为 18000（如实际字符串控件小于n, 系统自动在后面补空格）varcharn可变长度的字符串类型,n 表示字符串的最大长度,取值范畴为18000 ；texttext数据类型用来储备大量的非统一编码型字符数据；这种数据类型最多可以有231-1 或 20 亿个字符 .char 和 varchar 的区分：如某列数据类型为 varchar20 ,存字符串 ”Jone ”时,只占用 4 个字节,而 char（ 20）会在为填满的空间中填写空格；所以, varchar 类型比 char 类型更节约空间,但它的开销会大一些, 处理速度也慢一些； 因此,n 值比较小（小于 4 ）, 用 char

7、 类型更好些；2.3 统一编码字符串类型（ Unicode ）数据类型说明nchar数据类型用来储备定长统一编码字符型数据；统一编码用双字节结构来储备每个字符,而ncharn不是用单字节 一般文本中的情形；它答应大量的扩展字符；此数据类型能储备4000 种字符,使用的字节空间上增加了一倍.nvarcharnnvarchar数据类型用作变长的统一编码字符型数据；此数据类型能储备4000 种字符,使用的字节空间增加了一倍 .ntext最多可储备2 的 30 次方 -1 将近 10 亿个字符三. SQL 数据操作语言1. 数据查询语句1.1 查询语句的基本结构SELECT-需要哪些列From-来自哪

8、张表WHEREGROUPBYHAVINGORDERBYSELECT 子句用于指定输出的字段； FROM 子句用于指定数据的来源； 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例子 ：

9、SELECT * FROM Student（3） ）查询经过运算的列SELECT列名 FROM表名1例子 ：SELECT Sname,yeargetdata - yearBirthdate FROM Student1.2.2 挑选表中的如干元祖（1） ）排除取值相同的行： DISTINCTSELECTDISTINCTSnoFROM表名1例子 ：SELECT DISTINCT Sno FROM Student（2） ）查询满意条件的元祖查询条件谓 词查询条件谓 词比较= 、= 、=、 、.=、. 、.确定范畴BETWEEN AND 、 NOT BETWEENAND确定集合IN、NOT IN字符匹

10、配LIKE、NOT LIKE空值IS NULL 、IS NOT NULL多重条件（规律谓词）AND 、ORa. 比较大小例子 ：SELECT Sname FROM Student WHERE yeargetdata - yearBirthdate = 80 AND Grade = 90例子 ：SELECT Sno,Cno FROM SC WHERE Grade NOT BETWEEN 80 AND 90此查询等价于： SELECT Sno,Cno FROM SC WHERE Grade 90c. 确定集合IN 运算符的含义：当列中的值和集合中的 某个常量值相等时,结果为True ；NOT IN

11、 运算符的含义：当列中的值和集合中的 全部常量值都不相等时,结果为True ；例子 ：SELECT Sno FROM Student WHERE Dept IN 信息治理系 ,运算机系 此查询等价于： SELECT 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 运

12、算符用于查找指定列中与匹配串匹配的元祖；列名 NOTLIKE通配符含义_（下划线）匹配任意一个字符%（百分号）匹配 0 个或多个字符匹配 中的任意一个字符；如abcd 表示匹配 abcd 其中任何一个,如是连续的,可以用- 表示不匹配 中的任意一个字符；如 abcd 表示不匹配 abcd 其中任何一个,如是连续的,可以用例子 ：查询姓 “张”的同学具体信息 SELECT* FROMStudentWHERESnameLIKE张%查询不姓 “张”的同学具体信息 SELECT* FROMStudentWHERESnameNOTLIKE张%（查询姓 “张 ”、“李”的同学具体信息）SELECT* FR

13、OMStudentWHERESnameLIKE张李 %（查询名字的其次个字为“小” 或 “大”的同学具体信息）SELECT* FROMStudentWHERESnameLIKE_ 小大%e. 涉及空值的查询空值（ NULL ）在数据库中有特别含义,表示当前不确定或未知的值；判定是否为 NULL 时,不行用一般的比较运算符,需用IS NULL例子 ：SELECT Sno FROM Student WHERE Grade IS NULL1.2.3 对查询结果进行排序将查询结果依据指定的次序显示； ASC 表示按列值 升序排列（从上往下,值从大到小）；DESC 表示按列值 降序排列（从上往下,值从小

14、到大） ；默认为 ASC；ORDERBY ASC|DESC例子 ：SELECT Sno,Grade FROM SC ORDER BY Grade DESC1.2.4 使用聚合函数统计数据聚合函数也称为统计函数或集合函数, 作用是对一组值进行运算并返回一个统计结果；聚合函数含义COUNT*统计表中元祖的个数COUNTDISTINCT统计本列的非空列值个数SUM运算列值的和值（必需是数值型列）AVG运算列值的平均值（必需是数值型列）MAX运算列值的最大值MIN运算列值的最小值上述函数除 COUNT*外,其它函数在运算过程中均忽视NULL 值（统计同学总人数）SELECTCOUNT*FROMStud

15、ent（统计 “001 ”学号同学的考试平均成果）SELECTAVGGradeFROMSCWHERESno= 001（查询 “C001 ”号课程考试成果的最高分和最低分）SELECTMAXGrade最高分 ,MINGrade最低分 FROMSCWHERECno= C001聚合函数不能显现在 WHERE 子句中！1.2.5 对数据进行分组统计需要先对数据进行分组,然后再对每个组进行统计；分组子句GROUP BY ；在一个查询语句中,可以用多个列进行分组；分组子句跟在 WHERE 子句的后面：GROUPBY,.nHAVING（1） ）使用 GROUP BY子句（统计每门课程的选课人数,列出课程号和

16、选课人数）SELECTCnoas课程号 , COUNTSnoas 选课人数 FromSCGroupBYCno（统计每个同学的选课门数和平均成果）SELECTSno学号, COUNT*选课门数 ,AVGGrade平均成果 FromSCGroupBY Sno带 WHERE子句的分组（统计每个系的女生人数）SELECTDept,COUNT*女生人数 FromStudentWhereSex= 女 GroupBYDe pt（2） ）使用 HAVING子句HAVING 子句用于对分组后的统计结果再进行挑选,它的功能与WHERE 子句类似,它用于组而不是单个记录；在HAVING 子句中可以使用聚合函数,但在

17、WHERE 子句中不能,通常与GROUP 子句一起使用；（查询选课门数超过3 门的同学的学号和选课门数）SELECTSno学号, COUNT*选课门数 ,AVGGrade平均成果 FromSCGroupBY SnoHAVINGCOUNT* 3（3） ） WHERE、GROUP BY、HAVING的作用及执行次序WHERE 子句用于挑选 FROM 子句中指定的数据所产生的行数据；GROUP BY 子句用于对经 WHERE 子句挑选后的结果数据进行分组；HAVING 子句用于对 分组后的统计结果再进行挑选；可以分组操作之前应用的挑选条件,在 WHERE 子句中指定它们更有效,这样可以削减参加分组的

18、数据行；在 HAVING 子句中指定的挑选条件应当是那些必需在执行分组操作之后应用的挑选条件；（查询运算机系和信息治理系每个系的同学人数）第一种：SELECTDept,COUNT*FROMStudentGROUPBYDeptHavingDeptin 运算机系,信息治理系 其次种：SELECTDept,COUNT*FROMStudentWHEREDeptin 运算机系 ,信息治理系 G ROUPBYDept以上例子比较： 第一种是依据系分组好了之后, 只实行全部系中的两个系, 明显效率不高；而其次种是先进行 WHERE 挑选条件之后,再进行 GROUP BY 运算,显示更好；1.3 多表连接查询

19、如一个查询同时涉及到两张或以上的表,就称为连接查询；1.3.1 内连接使用内连接时, 假如两个表的相关字段满意条件, 就从两个表中提取数据组成新的记录；FROM表 1 INNERJOIN表 2ON1留意：连接条件中的连接字段必需是可比的,必需是语义相同的列；（查询同学及选课的具体信息）SELECT* FROMStudentINNERJOINSC ONStudent.Sno= SC.Sno（查询运算机系同学的选课情形,列出该同学的名字、所修课程号、成果）行挑选条件SELECTSname,Cno,GradeFROMStudentINNERJOINSCONStudent.Sno= SC. SnoWH

20、EREDept= 运算机系 （统计每个系的平均成果）分组的多表查询SELECTDept,AVGGradeASAverageGradeFROMStudentS INNERJOINSCO N S.Sno= SC.SnoGroupBYDept（统计运算机系每个同学的选课门数、平均成果、最高成果、 最低成果）分组和行挑选条件的多表连接查询SELECTSno,COUNT*,AVGGrade,MAXGrade,MINGradeFROMStudentS JOI N SCONS.Sno= SC.SnoWHEREDept= 运算机系 GroupBYDept1.3.2 自连接自连接是一种特别的内连接, 相互连接的

21、表在物理上是一张表, 但在规律上可以看做是两张表；FROM表 1 AST1JOIN表 1 AST21通过为表取别名的方法, 可以让物理上的一张表在规律上成为两张表； （肯定要为表取别名！）（查询与刘晨在同一个系学习的同学的姓名、所在系）SELECTS1.Sname,S1.DeptFROMStudentS1JOINStudentS2ONS1.Dept= S2.Dept同一个系的同学WHERES2.Sname= 刘晨-S2 表作为查询条件ANDS1.Sname.= 刘晨 -S1 表作为结果表,并从中去掉刘晨 本人信息1.3.3外连接在内连接操作中,只有满意条件的元祖才能显现在查询结果集中；外连接是

22、只限制一张表中的数据必需 满意条件,而另一张表的数据可以 不满意条件；FROM表 1 LEFT|RIGHTOUTERJOIN表 2 ON1LEFT OUTERJOIN 称为左外连接 ,含义是限制 表 2 中的数据必需满意条件,但不管表 1 中的数据是否满意条件,均输出表1 中的数据；LEFT OUTERJOIN 称为右外连接 ,含义是限制 表 1 中的数据必需满意条件,但不管表 2 中的数据是否满意条件,均输出表 2 中的数据；内连接与外连接的区分：内连接： 表 A 与表 B 进行内连接,就结果为两个表中满意条件的记录集,即C部分；外连接： 假如表 A 和表 B 进行左外连接, 就结果为 记录

23、集 A + 记录集 C；假如表 A 和表 B 进行右外连接,就结果为记录集 B + 记录集 C；（查询没有人选的选修课程名）SELECTCnameFROMCourseC LEFTJOINSCONC.Cno= SC.CnoWHERE SC.CnoISNULL例子解析： 假如存在部分课程为被人挑选,就必定在Course 表中有但在 SC 表中没有显现,即在进行外连接时没人选的课程在与SC 表构成的连接结果集中 , 对应的 Sno、Cno 、Grade 列必定为空,所以只需 * 在连接后的结果中选出 *SC 表中 Sno 或 Cno 为空的元祖即可；（统计运算机系每个同学的选课门数,包括没选课的同学

24、）SELECTS.SnoAS学号 ,COUNTSC.CnoAS选课门数 FROMStudentS LEFTJOI N SCONS.Sno= SC.SnoWHEREDept= 运算机系 GROUPBYS.Sno例子解析： 上述例子要求统计每个同学的 .,所以在 GROUPBY 分组时, 是依据同学表中的学号来分；而对于聚合函数COUNT ,上述要求统计每个同学的选课门数,如写成 COUNTS.Sno 或 COUNT * ,就对没选课的同学都返回 1, 由于在外连接结果中, S.Sno不会是 NULL ,而 COUNT* 函数本身也不考虑NULL ,它是直接对元祖个数进行计数；留意：在对外连接的结

25、果进行分组、 统计等操作时, 肯定要留意分组依据列和统计列的挑选；1.4 使用 TOP 限制结果集行数在使用 SELECT 语句进行查询时,有时只需要前几行数据；TOPexpressionPERCENTWITHTIES1expression ：指定返回行数的数值表达式；假如指定了PERCENT ,expression 将隐式转换成 float,否就是 bigintPERCENT ：指定只返回结果集中前expression%行数据；WITH TIES ：指定从基本结果集中返回额外的数据行（只有在SELECT 子句中包含了 ORDER BY 子句时,才能使用）；TOP 谓词写在 SELECT 单词

26、的后面（假如有 DISTINCT ,就在 DISTINCT 后面）；（查询考试成果最高的3 个成果；列出学号、课程号、成果）SELECTTOP3 Sno,Cno,GradeFROMSCORDERBY GradeDESC如要包括并列第 3 名的成果：SELECTTOP3 Sno,Cno,GradeWITHTIESFROMSCORDERBYGradeDESC2. 数据更换功能2.1 插入数据INSERTINTO表名 列名 VALUES值1（1） ）简洁插入语句INSERTINTOStudentVALUES001, 陈东 ,男,1996/6/23, 信息治理系 1（2） ）多行插入语句INSERTI

27、NTOSCVALUES001,C001,90,001,C002,30,001,C005,NULL（3） ）不按表次序插入语句按与表列次序不同的次序插入数据INSERTINTOStudentSno,Sname,Sex,DeptVALUES001, 陈东, 男,1996/6/23,信息治理系 2.2 更新数据UPDATE表名 SET列名 = 值（1） ）无条件更新UPDATESCSETGrade= Grade+10（2） ）有条件更新（将 “C001 ”号课程的学分改成5 分）UPDATECourseSETGrade= 5 WHERECno= C001（将运算机系全体同学的成果加5 分）UPDAT

28、ESCSETGrade= Grade+5FROMSCJOINStudentSONS.Sno= SC.Sno WHEREDept= 运算机系 2.3 删除数据DELETETOPexpressionPERCENTFROM表名（1） ）无条件删除DELETEFROMStudent（2） ）有条件删除（删除全部考试成果不合格的同学的选课记录）DELETEFROMSCWHEREGrade=90 THEN优WHENGradeBETWEEN80 AND99THEN 良WHENGradeBETWEEN70 AND79THEN 中WHENGradeBETWEEN60 AND69THEN 及格ENDAS成果FRO

29、MSCONWHERECno= C0012. 子查询假如一个 SELECT 语句嵌套在另一个 SELECT 、INSERT 、UPDATE 或 DELETE语句中,就称为子查询或内层查询；而包含子查询的语句称为主查询；子查询通常用于满意以下需求之一：把一个查询分解成一系列的规律步骤供应一个列表作为 WHERE 子句和 IN、EXISTS 、ANY、ALL 的目标对象供应由外层查询中每一条记录驱动的查询子查询通常有几种形式：WHERE列名NOT IN 子查询WHERE列名比较运算符 子查询WHERE EXISTS 子查询2.1 使用基于集合测试的嵌套子查询使用嵌套子查询进行基于集合的测试时, 子查

30、询返回的是一个值列表, 外层查询通过运算符 IN 或 NOT IN ,对子查询返回的结果集进行比较；SELECT FROM .WHERE NOTIN SELECT FROM包含这种子查询形式的查询语句是分步骤实现的,即先执行子查询, 然后在子查询的结果基础上执行外层查询（ 先内后外 ）；子查询返回的结果是一个集合,外层查询就是在这个集合上使用 IN 运算符进行比较；（查询与刘晨在同一系的同学）SELECTSno,Sname,DeptFROMStudentWHEREDeptINSELECTDeptFromStudentWhereSname= 刘晨 （查询选修了JAVA 课程同学的姓名）SELEC

31、TSnameFROMStduentWHERESno INSELECTSnoFROMSCWHERECnoINSELECTCnoFROMCourseWHERECname= JAVA其次个例子也可以用了连接查询来做：SELECTSnameFromStudentS JOINSCONS.Sno= SC.SnoJOINCourseC O N C.Cno= SC.CnoWHERECname= JAVA上述的例子可以看出 子查询和连接查询 可以互通,但在某些情形下是不行以的：（统计选了 JAVA课程的同学的姓名和所在系）子查询：SELECTSno学号,COUNT*选课门数 ,AVGGrade平均成果FROMS

32、CWHERESnoINSELECTSnoFROMSCJOINCourseCONC.Cno= SC.CnoWHERECname= JAVAGROUPBY Sno这个查询不能完全用 连接查询 实现,由于这个查询的语义是要先找出选了JAVA课程的同学,然后再运算这些同学的选课门数和平均成果；连接查询：SELECTSno学号,COUNT*选课门数 ,AVGGrade平均成果FROMSCJOINCourseC ONC.Cno= SC.CnoWHERECname= JAVAGROUPBYSno以上查询是错误的,查出来同学的选课门数都是1 ！ 实际上这个 1 指的是JAVA 这一门课程,其平均成果也是JAVA 成果；【留意：】连接查询和子查询的区分：之所以这样, 是由于在执