《数据库技术与应用-基于sql server 2019与mongodb 》课件 第3章 关系数据库标准语言SQL

数据库原理及应用基于SQLServer2019与MongoDB数据库设计-数据库原理E-R图层次模型网状模型关系模型数据结构数据操作完整性约束第3章关系数据库标准语言SQL—数据库应用

一、SQL概述二、数据定义三、数据操纵四、数据查询五、视图六、索引七、Transact-SQL基础八、存储过程九、触发器SQL语言的前身是SEQUEL语言，是1974年由Boyce(Codd与Boyce提出BC范式)和Chamberlin提出的，并在IBM公司研制的关系数据库系统SystemR上实现。后改为SQL(StructuralQueryLanguage)。1986年美国国家标准局ANSI将SQL语言作为关系数据库语言的美国标准，即SQL－86标准1987年，国际标准化组织（InternationalOrganizationforStandardization，ISO）正式采纳SQL-86作为国际标准；1989年，ISO对SQL-86标准进行了补充，推出了SQL-89标准；1992年，ISO推出了SQL-92标准，也称SQL2；1999年，ISO推出了SQL:1999标准，也称SQL3；2003年，ISO推出了SQL:2003标准；2008年，ISO推出了SQL:2008标准；2011年，ISO推出了SQL:2011标准；2016年，ISO推出了SQL:2016标准。一、SQL概述SQL的产生与发展一、SQL概述SQL的特点

综合统一高度非过程化：只提出做什么而无须知道怎么做面向集合的操作方式以同一种语法提供两种使用方式可以直接使用嵌入到其他高级语言中语言简洁，易学易用，9个动词一、SQL概述SQL语言的特点SQL视图V1视图V2基表B1基表B2基表B3基表B4存储文件S1存储文件S2存储文件S3存储文件S4用户外模式模式内模式一、SQL概述SQL的工作模式1．数据库的存储结构数据库的逻辑存储结构：指的是数据库是由哪些性质的信息所组成。诸如表、视图、索引、约束、存储过程等各种不同的数据库对象数据库的物理存储结构：指的是数据库文件是如何在磁盘上存储的。一个数据库至少应该包含一个数据库文件和一个事务日志文件。存储数据的文件，扩展名为“mdf”存储数据库的更新情况等日志信息，扩展名为“ldf”二、数据定义—创建数据库二、数据定义—创建数据库创建数据库的方法：（1）使用managementstudio创建数据库（2）使用SQL语句创建数据库二、数据定义—ManagementStudio创建数据库CREATEDATABASE数据库名

[ON[PRIMARY][<文件格式>[，…n]]][LOGON{<文件格式>[，…n]}]使用Transact-SQL创建数据库主文件日志文件[<文件格式>::=([NAME=逻辑文件名，]FILENAME=‘操作系统下的物理路径和文件名’

[，SIZE=文件初始大小][，MAXSIZE={文件最大大小|UNLIMITED}][，FILEGROWTH=增量值][，…n]]二、数据定义—SQL语句创建数据库大写Transact-SQL关键字。斜体Transact-SQL语法中用户提供的参数。|（竖线）分隔括号或大括号内的语法项目。只能选择一个项目。[]（方括号）可选语法项目。不必键入方括号。{}（大括号）必选语法项。不要键入大括号。[,...n]表示前面的项可重复n次。每一项由逗号分隔。[...n]表示前面的项可重复n次。每一项由空格分隔。二、数据定义—SQL语句创建数据库CREATEDATABASE数据库名

[ON[PRIMARY][<文件格式>[，…n]]][LOGON{<文件格式>[，…n]}]使用Transact-SQL创建数据库二、数据定义—SQL语句创建数据库例用createdatabase创建一个数据库，此数据库的名字为sm，其他选项均采用默认设置。createdatabasesm在默认位置创建数据库的主数据库文件－sm.mdf。和数据库的事务日志文件－sm_Log.ldf。C:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\DATA数据文件和日志文件的初始大小均为默认设置，增长方式－自动增长；大大小无限制。二、数据定义—SQL语句创建数据库createdatabaseCompanyOnprimary(name=Company_data,filename='C:\SQL\Data\Company.mdf',size=10,maxsize=unlimited,filegrowth=10%)Logon(name=Company_log,filename='C:\SQL\Data\Company.ldf',size=1,maxsize=5,filegrowth=1)数据文件逻辑名物理文件名日志文件逻辑名日志文件物理名二、数据定义—SQL语句创建数据库用SQL语句新建的数据库只能在下拉列表中显示出来，刷新数据库后，才能在“数据库”列表中显示出来。二、数据定义—SQL语句创建数据库mdf文件是数据库文件,这样的话也就意味着随着数据库的增大mdf就会相应的增大,在实际的应用中数据膨胀是太常见的事情了,然而windows对文件的大小是有要求的,这样的话很容易导致mdf文件达到windows所允许的文件大小的界限(于是数据库就崩溃了)。如左图，所有的数据文件都放到了Primary组里了。二、数据定义—文件组文件组是sqlserver管理文件的方式，可以均衡数据库的负载，提高文件存取的性能PRIMARY文件组是系统默认的文件组。用户可以自己定义文件组。数据库属性文件组二、数据定义—文件组建立数据库通常的做法是：分别建立不同的数据文件只有一个是主数据文件，扩展名为*.mdf多个辅助数据文件，扩展名为*.ndf这些不同的数据文件位于不同的文件组中。二、数据定义—文件组主数据文件包含数据库的启动信息，并指向数据库中的其他文件。每个数据库有一个主要数据文件，建议文件扩展名是.mdf。次要数据文件是可选的，由用户定义并存储用户数据。通过将每个文件放在不同的磁盘驱动器上，次要文件可用于将数据分散到多个磁盘上，建议文件扩展名是.ndf。事务日志文件保存用于恢复数据库的日志信息。每个数据库必须至少有一个日志文件，文件扩展名是.ldf20二、数据定义—文件组SQLServer数据文件和日志文件的文件结构不同，存储方式也不一样。21数据文件的存储

（1）数据页。SQLServer用于存储数据的基本单位是页每页容量为，行不能跨页存储。（2）扩展盘区。一个扩展盘区由8个物理上连续的页（64KB）组成，每1M包含16个扩展盘区（128页）。

二、数据定义—数据文件的存储

日志文件的存储结构：日志文件可以记录数据行从数据库创建到当前时刻对数据库所做的全部更改。针对数据库中任何一行执行的操作都将被作为一个日志行，并在事务提交时写入日志文件中。22二、数据定义—日志文件的存储日志文件的存储结构（1）SQLServer数据库日志的物理结构。日志文件并不包括在文件组内，SQLServer的日志文件中包含着一系列日志行。（2）SQLServer数据库日志的逻辑结构。日志以日志行为单位。每条日志行是由一个日志序列号（LogSequenceNum，LSN）标识。（3）SQLServer将数据库的回滚操作也放到日志中。23二、数据定义—日志文件的存储createdatabaseCompanyzzOnprimary(name=Company_data,filename='C:\Companyzz.mdf',size=10,maxsize=unlimited,filegrowth=10%),filegroupaa(name=Company_data1,filename='C:\Companyzz1.ndf',size=10,maxsize=unlimited,filegrowth=10%),filegroupbb(name=Company_data2,filename='C:\Companyzz2.ndf',size=10,maxsize=unlimited,filegrowth=10%)Logon(name=Company_log,filename='C:\Companyzz.ldf',size=1,maxsize=5,filegrowth=1)二、数据定义—SQL语句创建数据库二、数据定义—修改数据库修改数据库的方法：（1）使用managementstudio修改数据库（2）使用SQL语句修改数据库二、数据定义—SQL语句修改数据库使用ALTERDATABASE语句修改数据库语法格式如下：

ALTERDATABASEdatabase_name{MODIFYNAME=new_database_name |COLLATEcollation_name |ADDFILE<filespec>[,...n][TOFILEGROUP{filegroup_name}]|ADDLOGFILE<filespec>[,...n] |REMOVEFILElogical_file_name |MODIFYFILE<filespec> |ADDFILEGROUPfilegroup_name |REMOVEFILEGROUPfilegroup_name |MODIFYFILEGROUPfilegroup_name}例

在sm数据库中添加一个数据文件sm_data1alterdatabasesmaddfile(name='sm_data1',filename='C:\SQL\Data\sm_data1.ndf',size=4,maxsize=unlimited,filegrowth=10%)二、数据定义—SQL语句修改数据库例

将sm数据库中的数据文件sm_data1的最大大小改为120MBalterdatabasesmmodifyfile(name='sm_data1',maxsize=120MB)二、数据定义—SQL语句修改数据库二、数据定义—删除数据库删除数据库的方法：（1）使用managementstudio删除数据库（2）使用SQL语句删除数据库

删除一个数据库时也删除了该数据库的全部对象，从而将其所占的磁盘空间全部释放掉。删除数据库的基本语法：

DROPDATABASE数据库名[,...n]注：（1）删除数据库后应备份master数据库。（2）无法删除系统数据库（msdb,model,master,tempdb）（3）以下情况不能删除数据库：用户使用此数据库时数据库正在被恢复时数据库正在参与复制时dropdatabaseCompany删除数据库company二、数据定义—删除数据库二、数据定义—创建表创建表的方法：（1）使用managementstudio创建表（2）使用SQL语句创建表数据库设计-数据库原理E-R图层次模型网状模型关系模型数据结构数据操作完整性约束经过详细的需求分析，某大学的教学管理系统要求实现以下功能：（1）可以录入新生的信息、处理毕业生的信息、产生各种各样的学生统计表；（2）可以统计学生选课记录、录入学生成绩、产生学生成绩单和补考通知单等；（3）可以查询教师、班级、学生、课程、成绩等情况。根据上述描述，完成以下设计要求：（1）设计该教学管理系统的E-R图；（2）将对应的E-R图转换成关系模型；（3）注明每个关系模型的主键和外键（如果有），并按照实体完整性规则说明每个主键的取值要求；同时按照参照完整性规则说明每个外键的取值范围。语句格式CREATETABLE<表名>

（<列名><数据类型>[<列级完整性约束条件>][，<列名><数据类型>[<列级完整性约束条件>]]…[，<表级完整性约束条件>]）；<表名>：所要定义的基本表的名字<列名>：组成该表的各个属性（列）<列级完整性约束条件>：涉及相应属性列的完整性约束条件<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件二、数据定义—创建表一、SQL概述SQL语言的特点整数型bigint数据类型：大整数型，长度为8个字节，可以存储－263(－9223372036854775808)～263－1

(9223372036854775807)范围内的数字。int数据类型：整数型，长度为4个字节，可存储范围是－231(－2147483648)～231－1

(2147483647)。smallint数据类型：短整数型，长度为2个字节，可存储范围只有－215(－32768)～215－1

(32767)。tinyint数据类型：微短整数型，长度为1个字节，只能存储0-255范围内的数字。二、数据定义—创建表之数据类型精确小数数据类型：带固定精度和小数位数的数值。decimal[(p[,s])]numeric[(p[,s])]精度存储字节数1-9510-19920-281329-3817P精度（小数点两边的总位数,不包括小数点）scale刻度（小数点右边的位数）SQLServer分配给这两个类型数据的存储空间随精度的不同而不同二、数据定义—创建表之数据类型近似小数数据类型：能存储更大范围的数值数据，但不能精确存储。float[(n)]从－1.79E＋308到1.79E＋308之间的浮点数字数据，n必须为从1到53之间的值n所在范围精度存储大小1～247位数4B25～5315位数8B二、数据定义—创建表之数据类型非unicode字符串与unicode字符串Unicode字符串是使用unicodeucs-2字符集来表示字符串数据，也称为双字节字符串数据类型，使用两个字节存储一个字符。Unicode解决了字符转换问题和针对不同国家安装多国代码问题，从而使多种语言的数据存储变得更加容易。Unicode使用N开头来指定：N'AUnicodestring'。

二、数据定义—创建表之数据类型非unicode字符串char[(n)]：长度为n个字节的固定长度且非Unicode的字符数据，存储大小为n个字节。n必须是一个介于1和8000之间的数值。varchar[(n)]：长度为n个字节的可变长度且非Unicode的字符数据。n必须是一个介于1和8000之间的数值。存储大小为输入数据的字节的实际长度，而不是n个字节，所输入的数据字符长度可以为零。text数据类型：当要存储的字符类型数据非常庞大，以至于8000字节完全不够用时使用。用来声明变长且非unicode的字符数据。在定义过程中，不需要指定字符的长度。最大长度为231－1(2147483647)个字符，约2G。二、数据定义—创建表之数据类型有关char和varchar的使用如果数据项的大小一致，则使用char；对于char，因其固定长度，所以长度不足时，补英文半角空格，超出设置长度时，会报错。如果数据项的大小有差异，使用varchar；对于varchar，根据数据长度自动变化。如果站点支持多语言，考虑使用unicode字符串，以最大限度的消除字符转换问题。二、数据定义—创建表之数据类型Unicode字符串nchar（n）：定义固定长度的unicode字符串，n值在1-4000之间。存储大小为两倍n字节。nvarchar（n）：定义可变长度的unicode字符串，n值在1-4000之间。存储大小为数据的实际数据的2倍；ntext：定义长度可变的unicode数据，最大长度为230-1字符，存储大小为实际数据字符个数的两倍二、数据定义—创建表之数据类型Unicode字符型当列中各项所包含的Unicode字符数不同时(至多为4000)，使用nvarchar类型当列中各项为同一固定长度时(至多为4000个Unicode字符)，使用nchar类型当列中任意项超过4000个Unicode字符时，使用ntext类型，其最大长度为230－1二、数据定义—创建表之数据类型逻辑数值型（bit）SQLServer支持逻辑数据类型bit，它可以存储整数型数据1、0或NULL。如果输入0以外的其他值时，SQLServer均将其作为1看待。SOLServer优化用于bit列的存储。如果一个表中有不多于8个的bit列，这些列将作为一个字节存储。如果表中有9～16个bit列，这些列将作为两个字节存储。更多列的情况依此类推。二、数据定义—创建表之数据类型货币型money数据类型可存储的货币数据值介于－263～263－1之间，精确到货币单位的万分之一，存储大小为8个字节。smallmoney数据类型可存储的货币数据值介于－214748.3648～214748.3647之间，精确到货币单位的万分之一，存储大小为4个字节。二、数据定义—创建表之数据类型日期时间类型datetime从1753年1月1日到9999年12月31日的日期和时间数据，精确度为3‰s(3ms或0.003s)smalldatetime从1900年1月1日到2079年6月6日的日期和时间数据，精确到分钟。29.998s或更低的smalldatetime值向下舍入为最接近的分钟，29.999s或更高的smalldatetime值向上舍入为最接近的分钟。二、数据定义—创建表之数据类型binary[(n)]：固定长度的n个字节二进制数据。n必须在1～8000范围内。varbinary[(n)]：n个字节变长二进制数据。n必须在1～8000之间。存储空间大小为实际输入数据长度加上2个字节，而不是n个字节。image可变长度二进制数据：在0～231－1(2147483647)字节之间。数据类型可以用来存储超过8KB的可变长度的二进制数据，如Word文档、Excel电子表格、位图图像、图形交换格式(GIF)文件和联合图像专家组(JPEG)文件。二、数据定义—创建表之数据类型二进制数据类型语句格式CREATETABLE<表名>

（<列名><数据类型>[<列级完整性约束条件>][，<列名><数据类型>[<列级完整性约束条件>]]…[，<表级完整性约束条件>]）；<表名>：所要定义的基本表的名字<列名>：组成该表的各个属性（列）<列级完整性约束条件>：涉及相应属性列的完整性约束条件<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件二、数据定义—创建表Sqlserver中的完整性约束实体完整性：主键约束PRIMARYKEY参照完整性：外键约束FOREIGNKEY用户定义的完整性

NOTNULL（非空）约束

UNIQUE（唯一性）约束值域（CHECK）约束默认取值（DEFAULT）约束唯一性约束：用来限制不受主键约束的列上的数据的唯一性，一个表上可以放置多个唯一性约束．

表中任意两行在

指定列上都不允许有相同的值，但允许空（NULL）．二、数据定义—创建表之完整性约束[例]CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY，

SnameCHAR(20)UNIQUE，

SsexCHAR(1)NOTNULL，

SageINTDEFAULT18，

SdeptCHAR(15))；二、数据定义—创建表CREATETABLECourse(CnoCHAR(4)PRIMARYKEY,CnameCHAR(40),CpnoCHAR(4),CreditSMALLINTNULL,

FOREIGNKEYCpnoREFERENCESCourse(Cno))二、数据定义—创建表CREATETABLESC(SnoCHAR(9),CnoCHAR(4),Gradetinyint,

Primarykey(Sno,Cno),

FOREIGNKEY(Sno)REFERENCESStudent(Sno)，

FOREIGNKEY(Cno)REFERENCESCourse(Cno)，

CHECK（grade>=0andgrade<=100）)二、数据定义—创建表二、数据定义—修改表修改表的方法：（1）使用managementstudio修改表（2）使用SQL语句修改表<表名>：要修改的基本表ADD：增加新列和新的完整性约束条件DROP[constraint]：删除指定的完整性约束条件Dropcolumn:删除表中的一列或多列。ALTERCOLUMN子句：用于修改列的数据类型、长度等属性。ALTERTABLE<表名>{ADD<新列名><数据类型>[完整性约束]|DROP[constraint]<完整性约束名>[,…n]|dropCOLUMN<列名>[,…n]ALTERCOLUMN<列名><数据类型>}二、数据定义—SQL语句修改表createtableemployees(idchar(8)primarykey,namechar(20)notnull,departmentchar(20)null,memochar(30)null,ageintnull)altertableemployees

addsalaryintnull，

addconstraintun_nameunique(name)，

dropcolumnage，

altercolumnmemovarchar(200)null，dropconstraintun_name二、数据定义—SQL语句修改表

利用DROPTABLE语句删除表 DROPTABLE语句可以删除一个表和表中的数据及其与表有关的所有索引、触发器、约束、许可对象。DROPTABLE语句的语法形式如下：DROPTABLEtable_nameDroptableemployee二、数据定义—SQL语句删除表三、数据操纵三、数据操纵插入数据INSERTINTO<object>[(column_list)] VALUES(value_list)[;][例]在SC表中

插入一条选课记录('95020'，'1')。

INSERTINTOSC(Sno，Cno)VALUES('95020'，'1')；新插入的记录在Grade列上取空值三、数据操纵插入数据[例]将一个新学生记录（学号：95020；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中。

INSERTINTOStudentVALUES('95020'，'陈冬'，'男'，'IS'，18)；三、数据操纵插入数据如果新元组要在表的所有属性列上都有指定值，且属性列的次序与CREATETBALE中的次序相同，则INTO子句中可以只指出表名，没有属性名。三、数据操纵插入数据利用VALUES子句插入多个元组的INSERT语句语法格式如下：INSERTINTO<object>[(column_list)] VALUES(value_list)[,(value_list)…]【例】在Student表中插入三个元组INSERTINTOStudent(Sno,Sname,Ssex,Sbirthday,Sdept)VALUES('201818104','钱壮壮','男','2001-1-14','大数据'),('201818153','陈东与','男','2001-11-08','会计学'),('201818133','任雾','女','2001-08-14','艺术学');DBMS在执行插入语句时会检查所插入元组是否破坏表上已定义的完整性规则实体完整性参照完整性用户定义的完整性对于有NOTNULL约束的属性列是否提供了非空值对于有UNIQUE约束的属性列是否提供了非重复值对于有check约束的属性列所提供的属性值是否在值域范围内三、数据操纵插入数据UPDATE<表名>SET<列名>=<表达式>[，<列名>=<表达式>]…[WHERE<条件>]；功能修改指定表中满足WHERE子句条件的记录SET子句指定修改方式：要修改的列，修改后取值WHERE子句指定要修改的元组缺省表示要修改表中的所有元组三、数据操纵修改数据[例]将学生95001的年龄改为22岁。

UPDATEStudentSETSage=22WHERESno='95001'；三、数据操纵修改数据[例]将所有学生的年龄增加1岁。

UPDATEStudentSETSage=Sage+1；三、数据操纵修改数据[例]将信息系所有学生的年龄增加1岁。

UPDATEStudentSETSage=Sage+1WHERESdept='IS'；三、数据操纵修改数据DBMS在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则实体完整性：主键约束参照完整性：外键约束用户定义的完整性

NOTNULL（非空）约束

UNIQUE（唯一性）约束值域（check）约束默认取值（default）约束三、数据操纵修改数据三、数据操纵删除数据

DELETEFROM<表名>[WHERE<条件>]；功能删除指定表中满足WHERE子句条件的记录WHERE子句指定要删除的元组缺省表示要修改表中的所有元组[例]删除学号为95019的学生记录。

DELETEFROMStudentWHERESno='95019'；三、数据操纵删除数据[例]删除2号课程的所有选课记录。

DELETEFROMSC；

WHERECno='2'；[例]删除所有的学生选课记录。

DELETEFROMSC；三、数据操纵删除数据四、数据查询四、数据查询：学生--课程数据库学生表：Student(Sno,Sname,Ssex,Sbirthday,Sdept)课程表：Course(Cno,Cname,Ccredit)选课表：SC(Sno,Cno,Grade)SnoSnameSsexSbirthdaySdept95001李勇男1999-10-21CS95002刘晨女1999-2-14IS95003王敏女2000-7-18MA95004张立男2000--5-27ISStudentCourseCnoCnameCcredit1数据库42数学

23信息系统44操作系统35数据结构46数据处理

27PASCAL语言4四、数据查询：学生--课程数据库SCSnoCnoGrade950011929500128595001388950022909500238076四、数据查询：查询分类单表查询连接查询嵌套查询集合查询

SELECTselect_list[INTOnew_table]FROMtable_source[WHEREsearch_condition][GROUPBYgroup_by_expression][HAVINGsearch_condition][ORDERBYorder_by_expression[ASC|DESC]]四、数据查询78四、数据查询单表查询[例1]查询全体学生的学号与姓名。SELECTSno，Sname FROMStudent；SELECTSname，Sno，Sdept FROMStudent；[例2]查询全体学生的姓名、学号、所在系。79四、数据查询单表查询[例3]查询全体学生的详细记录。SELECTSno，Sname，Ssex，Sbirthday，Sdept FROMStudent；或

SELECT* FROMStudent；80四、数据查询单表查询SELECT查询的列还可以是算术表达式字符串常量函数列别名[例4]查全体学生的姓名及其出生年份。SELECTSname,YEAR(GETDATE())-YEAR(Sbirthday),'岁'FROMStudent;SELECTSnameAS姓名,YEAR(GETDATE())-YEAR(Sbirthday)AS年龄,'岁'AS岁FROMStudent;别名，有时为了方便，有时是必须的,as可以省略，空格不能省略。81四、数据查询单表查询[例5]查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。SELECTSname,’YearofBirth:’,year(birthday)，LOWER(Sdept)FROMStudent；82四、数据查询单表查询之消除取值重复的行ALL与DISTINCTSnoCnoGrade

14001c19214001c28514001c38814002c29014002c380[例6]查询选修了课程的学生学号。结果：Sno

1400114001140011400214002

(1)SELECTSnoFROMSC;或SELECTALLSnoFROMSC；(2)SELECTDISTINCTSnoFROMSC；结果：

Sno

1400114002

83四、数据查询单表查询之从结果中输出前n行TOPn[PERCENT]指定只从查询结果集中输出前n

行。n

是介于0和4294967295之间的整数。如果还指定了PERCENT，则只从结果集中输出前百分之n

行。当指定时带PERCENT时，n

必须是介于0和100之间的整数。如果查询包含ORDERBY子句，将输出由ORDERBY子句排序的前n

行（或前百分之n

行）。如果查询没有ORDERBY子句，行的顺序将任意[例6]查询选修了课程的学生学号，显示前3名。selecttop3sno学号fromsc[例6]查询选修了课程的学生学号，显示前3%。selecttop3percentsno学号fromsc四、数据查询单表查询：从结果中输出前n行

SELECTselect_list[INTOnew_table]FROMtable_source[WHEREsearch_condition][GROUPBYgroup_by_expression][HAVINGsearch_condition][ORDERBYorder_by_expression[ASC|DESC]]四、数据查询：行筛选86四、数据查询--单表查询：查询满足条件的行WHERE子句常用的查询条件常用的查询条件查

询

条

件谓

词比较=，>，<，>=，<=，!=，<>，!>，!<；确定范围BETWEEN，ANDNOTBETWEEN，AND确定集合IN，NOTIN字符匹配LIKE，NOTLIKE空

值ISNULL，ISNOTNULL多重条件AND，OR87在WHERE子句的<比较条件>中使用比较运算符=，>，<，>=，<=，!=或<>，!>，!<，逻辑运算符NOT+比较运算符[例8]查询所有年龄在18岁以下(含18)的学生姓名及其年龄。

或SELECTSname，year(getdate())-year(sbirthday) FROMStudent WHERENOT(year(getdate())-year(sbirthday))>18；SELECTSname，year(getdate())-year(sbirthday) FROMStudent WHERE(year(getdate())-year(sbirthday))<=18；四、数据查询--单表查询：查询满足条件的行88[例10]查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄。使用谓词确定范围：BETWEEN…AND…NOTBETWEEN…AND…SELECTSname,Sdept,year(getdate())-year(sbirthday)FROMStudentWHEREyear(getdate())-year(sbirthdayBETWEEN20AND23；四、数据查询--单表查询：查询满足条件的行89[例11]查询年龄不在20~23岁之间的学生姓名、系别和年龄。SELECTSname，Sdept，year(getdate())-year(sbirthday) FROMStudent WHEREyear(getdate())-year(sbirthday)NOTBETWEEN20AND23；四、数据查询--单表查询：查询满足条件的行90[例12]查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别。使用谓词确定集合：IN<值表>,NOTIN<值表><值表>：用逗号分隔的一组取值SELECTSname，Ssex FROMStudent WHERESdeptIN(‘IS’，‘MA’，‘CS’)；[例13]查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。SELECTSname，SsexFROMStudent WHERESdeptNOTIN('IS'，'MA'，'CS');四、数据查询--单表查询：查询满足条件的行91字符串匹配：[NOT]LIKE‘<匹配串>’<匹配串>：固定字符串或含通配符的字符串%(百分号)代表任意长度(长度可以为0)的字符串例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab等都满足该匹配串；_(下横线)代表任意单个字符例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串四、数据查询--单表查询：查询满足条件的行通配符描述%0或多个字符串_任何单个的字符[]在指定区域或集合内的任何单个字符[^]不在指定区域或集合内的任何单个字符92通配符用法示例符号含义LIKE'5[%]'5%LIKE'5%'5后跟0个或更多字符的字符串LIKE'[-]n'-nLIKE'_n'an,in,on(andsoon)LIKE'[a-cdf]'a,-,c,d,orfLIKE'[-acdf]'-,a,c,d,orfLIKE'[[]'[LIKE']']四、数据查询--单表查询：查询满足条件的行93使用LIKE时，请注意查询条件中的字符串内，所有的字符都有效，包括开始和结尾的空格LIKE只可用于下列数据类型：char、nchar、varchar、nvarchar和datetime四、数据查询--单表查询：查询满足条件的行941)匹配串为固定字符串当匹配串为固定字符串时， 可以用=运算符取代LIKE谓词 用!=或<>运算符取代NOTLIKE谓词等价于：

SELECT*FROMStudentWHERESno=‘14001'；[例14]查询学号为14001的学生的详细情况。SELECT*FROMStudentWHERESnoLIKE‘14001'；四、数据查询--单表查询：查询满足条件的行952)匹配串为含通配符的字符串[例15]查询所有姓“王”学生的姓名、学号和性别。[例16]查询姓“王”且全名为三个汉字的学生的姓名。SELECTSname，Sno，SsexFROMStudentWHERESnameLIKE‘王%’；SELECTSnameFROMStudentWHERESnameLIKE‘王__'；四、数据查询--单表查询：查询满足条件的行疑问:做例16时，有的同学会出现把类似姓名是“王平”,即姓名是两个字的这种结果也查询出来.其原因在于:设计student表时,sname列采用的是nchar或者是char类型的，因为采用char或者nchar类型时,如果数据长度不够,sqlserver会以半角空格自动填充。解决方法:sname列可以改成nvarchar或者是varchar数据类型,或者用Rtrim函数去掉空格。97[例17]查询姓王，且名字字长为3，第3个字为“芳"字的学生的姓名和学号。

SELECTSname，SnoFROMStudentWHERESnameLIKE‘王_芳'；2)匹配串为含通配符的字符串[例17.1]查询姓王，且名字最后一个字为“芳"字的学生的姓名和学号。

SELECTSname，SnoFROMStudentWHERESnameLIKE‘王%芳'；[例18]查询所有不姓王的学生姓名。

SELECTSname，Sno，SsexFROMStudentWHERESnameNOTLIKE‘王%'；四、数据查询--单表查询：查询满足条件的行98[NOT]LIKE‘<匹配串>’[ESCAPE‘<换码字符>’]当用户要查询的字符串本身就含有%或_时：可以使用方括号[],表示匹配指定区域或集合内的任何单个字符；也可以要使用ESCAPE‘<换码字符>’短语对通配符进行转义,紧跟在换码字符后面的字符不再具有通配符的含义。四、数据查询--单表查询：查询满足条件的行993)使用换码字符ESCAPE将通配符转义为普通字符 SELECTCno，Ccredit FROMCourse WHERECnameLIKE‘DB\_Design’ESCAPE‘\’；换码字符为\，紧跟在\后面的字符“_”不再具有通配符的含义。 SELECTCno,credit FROMCourse WHERECnameLIKE'DBw_Design'ESCAPE'w‘换码字符为w，紧跟在w后面的字符“_”不再具有通配符的含义。SELECTCno,credit FROMCourse WHERECnameLIKE'DB[_]Design'[例19]查询DB_Design课程的课程号和学分。四、数据查询--单表查询：查询满足条件的行100使用换码字符将通配符转义为普通字符(续)[例20]查询以"DB_"开头，且倒数第3个字符为i的课程的详细情况。

SELECT*FROMCourseWHERECnameLIKE'DB\_%i__'ESCAPE'\'；SELECT*FROMCourseWHERECnameLIKE'DB[_]%i__'ESCAPE'\'四、数据查询--单表查询：查询满足条件的行101涉及空值的查询：使用谓词ISNULL或ISNOTNULL“ISNULL”不能用“=NULL”代替[例21]某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号(显示成绩空值)。[例22]查所有有成绩的学生学号和课程号。 SELECT*FROMSCWHEREGradeISNULL；

SELECTSno，CnoFROMSCWHEREGradeISNOTNULL；四、数据查询--单表查询：查询满足条件的行102符合条件查询：用逻辑运算符AND和OR来联结多个查询条件AND的优先级高于OR可以用括号改变优先级[例23]查询计算机系年龄在20岁以下的学生姓名。

SELECTSnameFROMStudentWHERESdept='CS'AND(year(getdate())-year(sbirthday))<20；四、数据查询--单表查询：查询满足条件的行103[例12]查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptIN('IS'，'MA'，'CS')可改写为：SELECTSname，SsexFROMStudentWHERESdept='IS'ORSdept='MA'ORSdept='CS';四、数据查询--单表查询：查询满足条件的行104[例10]查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄。SELECTSname，Sdept，(year(getdate())-year(sbirthday))FROMStudentWHERE(year(getdate())-year(sbirthday))BETWEEN20AND23；可改写为：SELECTSname，Sdept，(year(getdate())-year(sbirthday))FROMStudentWHERE(year(getdate())-year(sbirthday))>=20AND(year(getdate())-year(sbirthday))<=23；四、数据查询--单表查询：查询满足条件的行在选课系统数据库中进行以下查询1、查询全体学生的学号与姓名2、查询全体学生的姓名、学号、所在系3、查询全体学生的详细记录4、查询全体学生的姓名及出生年份5、查询全体学生的姓名及年龄，其中年龄改变列标题为“年龄”6、查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名7、查询选修了课程的学生学号，显示前2行并取消重复行。8、查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄。9、查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别。10、查询学号为14001的学生的详细情况。11、查询所有姓“王”学生的姓名、学号和性别。12、查询姓“王”且全名为三个汉字的学生的姓名。13、查询姓王，且名字字长为3，第3个字为“芳"字的学生的姓名和学号。14、查询姓王，且名字最后一个字为“芳"字的学生的姓名和学号。15、查询所有不姓王的学生姓名。16、查询DB_Design课程的课程号和学分。17、查询以"DB_"开头，且倒数第3个字符为i的课程的详细情况。18、查询计算机系年龄在20岁以下的学生姓名。19、查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别。20、查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄。四、数据查询：学生--课程数据库学生表：Student(Sno,Sname,Ssex,Sbirthday,Sdept)课程表：Course(Cno,Cname,Ccredit)选课表：SC(Sno,Cno,Grade)SnoSnameSsexSbirthdaySdept95001李勇男1999-10-21CS95002刘晨女1999-2-14IS95003王敏女2000-7-18MA95004张立男2000--5-27ISStudentCourseCnoCnameCcredit1数据库42数学

23信息系统44操作系统35数据结构46数据处理

27PASCAL语言4四、数据查询：学生--课程数据库SCSnoCnoGrade9500119295001285950013889500229095002380

SELECTselect_list[INTOnew_table]FROMtable_source[WHEREsearch_condition][GROUPBYgroup_by_expression][HAVINGsearch_condition][ORDERBYorder_by_expression[ASC|DESC]]四、数据查询：排序109四、数据查询单表查询对查询结果排序：使用ORDERBY子句当排序列含空值时ASC：排序列为空值的元组最先显示；DESC：排序列为空值的元组最后显示。[例24]查询选修了2号课程的学生的学号及其成绩，查询结果按分数降序排列。SELECTSno，GradeFROMSCWHERECno='c2'ORDERBYGradeDESC；可以按一个或多个属性列排序，多个属性列时，写在前面的优先排序。升序：ASC；降序：DESC；缺省值为ASC.110[例25]查询全体学生情况，查询结果按所在系的系名字母升序排列，同一系中的学生按年龄降序排列。SELECT*FROMStudentORDERBYSdeptASC，SageDESC；四、数据查询单表查询111使用ORDERBY子句为结果集中的行排序时，有升序和降序两种。在安装SQLServer时，排序规则已指定“在MicrosoftSQLServer

中，排序规则指定表示每个字符的位模式以及存储和比较字符所使用的规则。”服务器属性常规四、数据查询单表查询112除非使用ORDERBY指定顺序，SQLServer并不主动为结果集中的行排序SQLServer缺省地按升序排列ORDERBY子句中指定的列并不一定要出现在选择列表中ORDERBY子句指定的列不能超过8060字节可以按照字段名、计算出的值或表达式进行排序ORDERBY子句不能用于数据类型text或image字段四、数据查询单表查询113使用统计函数：5类主要统计函数计数(计非空字段)COUNT([DISTINCT|ALL]*)：统计元组的个数COUNT([DISTINCT|ALL]<列名>)：统计一列中值的个数ALL为默认值。

计算一列值的平均值：数值型AVG([DISTINCT|ALL]<列名>)计算一列值的总和：数值型SUM([DISTINCT|ALL]<列名>)求一列值中的最小值

MIN([DISTINCT|ALL]<列名>) 求一列值中的最大值

MAX([DISTINCT|ALL]<列名>)四、数据查询单表查询114使用统计函数[例26]查询选修课程的学生总人次DISTINCT短语：在计算时要取消指定列中的重复值

ALL短语：不取消重复值

ALL为缺省值SELECTCOUNT(DISTINCTSno)FROMSC；注：用DISTINCT以避免重复计算学生人数SELECTCOUNT(sno)FROMSC；[例27]查询选修了课程的学生人数四、数据查询单表查询115[例28]计算1号课程的学生平均成绩[例29]查询选修1号课程的学生最高分数SELECTAVG(Grade)FROMSCWHERECno='c1'；SELECTMAX(Grade)FROMSCWHERECno='c1'；四、数据查询单表查询使用统计函数

SELECTselect_list[INTOnew_table]FROMtable_source[WHEREsearch_condition][GROUPBYgroup_by_expression][HAVINGsearch_condition][ORDERBYorder_by_expression[ASC|DESC]]四、数据查询：四、数据查询：学生--课程数据库学生表：Student(Sno,Sname,Ssex,Sbirthday,Sdept)课程表：Course(Cno,Cname,Ccredit)选课表：SC(Sno,Cno,Grade)SnoSnameSsexSbirthdaySdept95001李勇男1999-10-21CS95002刘晨女1999-2-14IS95003王敏女2000-7-18MA95004张立男2000--5-27ISStudentCourseCnoCnameCcredit1数据库42数学

23信息系统44操作系统35数据结构46数据处理

27PASCAL语言4四、数据查询：学生--课程数据库SCSnoCnoGrade9500119295001285950013889500229095002380

SELECTselect_list[INTOnew_table]FROMtable_source[WHEREsearch_condition][GROUPBYgroup_by_expression][HAVINGsearch_condition][ORDERBYorder_by_expression[ASC|DESC]]四、数据查询：分组Group:分组Groupby：依据….进行分组SnoCnoGrade9500119295001285950013889500229095002380SnoSnameSsexSageSdept95001李勇男20CS95002刘晨女19IS95003王敏女18MA95004张立男19IS四、数据查询：分组121使用GROUPBY子句分组：将查询结果表按某一列或多列值进行分组，值相等的为一组。作用：细化统计函数的作用对象。未对查询结果分组，统计函数将作用于整个查询结果；对查询结果分组后，统计函数将分别作用于每个组；[例30.1]求各个课程号及相应的选课人数。SELECTCno，COUNT(Sno)FROMSCGROUPBYCno；分组方法：值相等的为一组使用GROUPBY子句后，SELECT子句的列名列表中只能出现分组属性（cno）和统计函数。四、数据查询单表查询122SELECTCno，COUNT(Sno)FROMSCGROUPBYCnoHAVINGCOUNT(Sno)>3；

HAVING短语[例30.2]查询选课人数大于3的课程号和选课人数。只有满足HAVING短语指定条件的组才输出四、数据查询单表查询123SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(sno)>=3;[例30.3]查询选修了3门以上课程的学生的学号四、数据查询单表查询124SELECTSno,COUNT(sno)FROMSCWHEREGrade>=90GROUPBYSnoHAVINGCOUNT(sno)>=3;[例31]查询有3门以上课程是90分以上的学生的学号及相应的90分以上的课程数。HAVING短语与WHERE子句的区别：作用对象不同WHERE子句作用于基表或视图，从中选择满足条件的元组。HAVING短语作用于组，从中选择满足条件的组。注意：select语句的执行过程四、数据查询单表查询

SELECTselect_list[INTOnew_table]FROMtable_source[WHEREsearch_condition][GROUPBYgroup_by_expression][HAVINGsearch_condition][ORDERBYorder_by_expression[ASC|DESC]]四、数据查询：将查询结果输出到文件查询结果的输出以文本格式显示结果；以网格显示结果；将结果保存到文件；四、数据查询单表查询查询结果的输出

将结果保存到文件使用into子句将查询后的结果保存到一个新的表中。新表中的列按照查询语句指定的顺序创建。格式：intonew_table1、存储到基本表中例：查询工商系所有学生的信息，并存储到gs_student表中Select*intogs_studentfromstudentWheresdept=‘工商系’说明：在选课系统数据库中，可以看到新建的表：gs_student.2、存储到临时表中例：查询学分为3的所有课程信息，并存储到course3临时表中Select*into#course3fromcoursewherecredit=3说明：在系统数据库tempdb中，可以看到临时表course3四、数据查询单表查询

SELECTselect_list[INTOnew_table]FROMtable_source[WHEREsearch_condition][GROUPBYgroup_by_expression][HAVINGsearch_condition][ORDERBYorder_by_expression[ASC|DESC]]四、数据查询SELECT子句：指定要显示的属性列；FROM子句：指定查询对象(基本表或视图)；WHERE子句：指定查询条件GROUPBY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中使用统计函数。HAVING短语：筛选出只有满足指定条件的组ORDERBY子句：对查询结果表按指定列值的升序或降序排序

SELECTselect_list[INTOnew_table]FROMtable_source[WHEREsearch_condition][GROUPBYgroup_by_expression][HAVINGsearch