成都东软学院2021/5/231 信息管理教研室：李姗 办公室：A4201 Email: LiShanccniit.com PhoneNo:028-828781952021/5/232模块 4-1 用SQL语句进行简单查询模块4-2 用视图进行查询模块4-3 用函数进行查询2021/5/233能够用SELECT语句进行简单查询能够按照字段对信息进行筛选能够对查询的记录进行排序2021/5/2341.查询student表中的若干列。2.查询student表中的若干行。3.查询信息的排序显示。4.使用LIKE子句实现模糊查询。2021/5/235【任务分析】 查询信息是数据库的基本功能之一，通常可以使用SELECT 语句来完成查询操作。本任务要完成对student表的若干列的查询，这里的若干列既可以是全部列、也可以是部分列、也可以是一些列的合成的结果集。因此该任务继续分为下面几个子任务：任务1.1 查询student表的学生编码、姓名、性别和入学成绩(全部列)任务1.2 查询student表的所有学生信息(部分列)任务1.3 查询 student表的学生的姓名和年龄(列的合成的结果集)2021/5/236程序代码： USE studentGOSELECT sno,sname,ssex,sscoreFROM studentGOSelect子句中依次列出要查询的字段(字段间用逗号加以分隔)From子句指明数据来源于哪张数据表或视图2021/5/237分析，执行上述代码 2021/5/238程序代码： USE studentGOSELECT *FROM studentGO任务1.2：查询查询student表的所有学生信息表的所有学生信息（代码（代码8） 查询所有信息，可以在select子句中依次列出表中的所有列，也可以使用通配符 “”From子句指明数据来源于哪张数据表或视图2021/5/239分析，执行上述代码 2021/5/2310程序代码： USE studentGOSELECT sname 姓名,YEAR(GETDATE()-YEAR(sbirthday) 年龄 FROM studentGO任务1.3：查询 student表的学生的姓名和年龄（代码8）该列的别名，用来友好地显示相关查询字段的 信息。Year()函数的功能是返回年份，getdate()函数的功能是返回系统当前的时间和日期。 YEAR(GETDATE()-YEAR(sbirthday) 表达式用来计算学生的年龄。2021/5/2311分析，执行上述代码 2021/5/2312【任务分析】 本任务要完成对student表的若干行的查询，可以通过WHERE、TOP和DISTINCT来实现。WHERE子句可以筛选出满足条件的记录，TOP可以对记录的条数进行具体限定，而DISTINCT则可以清除一些重复的行。因此该任务继续分为下面几个子任务：任务2.1 查询student表的“07010211”班的男生信息任务2.2 应用TOP子句查询choice表中选修“0101001”课程的三位学生任务2.3 应用DISTINCT子句消除重复行2021/5/2313Select语句格式 select top n distinct列名列表 from 表名 where 查询条件2021/5/2314程序代码： USE studentGOSELECT *FROM studentWHERE classno=07010211 AND ssex=男GO 任务任务2.1：查询：查询student表的表的“07010211”班班的男生信息的男生信息 （代码（代码8）两个条件之间是并且的关系，可以用逻辑运算符and。2021/5/2315分析，执行上述代码 2021/5/23161、查询teacher表中所有女教师的信息。2、查询teacher表中所有女教师的姓名和职称。3、查询teacher表中所有教师的姓名和年龄。4、查询teacher表中所有女教师的姓名和年龄。2021/5/2317程序代码： USE studentGOSELECT TOP 3 *FROM choiceWHERE cno=0101001GO任务任务2.2：应用应用TOP子句查询子句查询choice表中选修表中选修“0101001”课程的三位学生课程的三位学生 (代码代码8)表示查询前3条记录。还可在字段列表之前用top 20 percent关键字，查询结果将只显示前20%的记录。(p101-2.sql)Top字句位于select和列名列表之间。2021/5/2318分析，执行上述代码 2021/5/23191、查询student表中前10名学生的姓名。2、查询student表中第10条到20条数据中学生的姓名。3、查询student表中第10条数据的学生姓名。2021/5/2320程序代码： USE studentGOSELECT DISTINCT snoFROM choiceGO任务任务2.3：应用：应用DISTINCT子句消除重复行（代码子句消除重复行（代码8） Distinct用于清除一些重复的行，使用时介于select和列名列表之间。2021/5/2321分析，执行上述代码 2021/5/2322【任务分析】 在student表中查询全体学生的信息，查询结果按所在的班级的班级代码降序排序，同一个班级的学生按照学号升序排序。对于结果的排序可以使用ORDER BY 语句来控制，其中ASC表示升序，DESC表示降序。 2021/5/2323USE studentGOSELECT *FROM studentORDER BY classno DESC,sno ASC GO 程序代码：（代码8）层次递进说明，不是并列的。2021/5/2324分析，执行上述代码 2021/5/23251、在student表中查询全体学生信息，查询结果按出生日期降序排列。2、在teacher表中查询所有女教师的信息，按出生日期升序排列。3、在student表中查询06010111班的学生信息，按入学成绩升序排列。4. 在choice表中查询所有学生的选课情况，查询结果按考试成绩从大到小排列2021/5/2326【任务分析】 在student表中查询陈姓学生的基本信息，查询结果出生年月降序排序。这里的查询条件“陈姓”学生含义比较宽泛，不能直接使用sname=陈来表示，而要使用LIKE子句并跟上通配符的形式，。查询结果排序则可以使用ORDER BY 语句来控制，其中ASC表示升序，DESC表示降序。 2021/5/2327USE studentGOSELECT *FROM studentWHERE sname LIKE 陈%ORDER BY sbirthday DESCGO 程序代码(代码8）2021/5/2328分析，执行上述代码 2021/5/23291、在student表中查询张姓学生的基本信息，查询结果按出生日期的升序排列。2、查student表中07级的学生信息，查询结果按所在班级的班级编号的升序排序，同一个班级的学生按照学号升序排列。3、查询student表中入学成绩大于400分的学生的学号、姓名和入学成绩，结果按成绩升序排序。2021/5/2330 关系数据库的关系之间可以通过运算获取相关的数据，其基本运算的种类主要有选择、投影和连接运算，它们来自关系代数中的并、交、差、选择、投影等运算。投影 从一个表中选择一列或者几列形成新表的运算称为投影。投影是对数据表的列进行的一种筛选操作，新表的列的数量和顺序一般与原表不尽相同。在SQL Server中的投影操作通过SELECT子句中限定列名列表来实现。 练习：查询teacher表中的教师编号和姓名。2021/5/2331 选择 从一个表中选择若干行形成新表的运算称为选择。选择是对数据表的行进行的一种筛选操作，新表的行的数量一般跟原表不尽相同。在SQL Server中的选择操作通过WHERE 子句中限定记录条件来实现。 练习：查询student表中1988年以后出生的学生的学号和姓名。连接 从两个或两个以上的表中选择满足某种条件的记录形成新表的运算称为连接。连接与投影和选择不同，它的运算对象是多表。它可以分为交叉连接、自然连接、左连接、右连接等不同的类型。 例：查询“计应0711”班学生的信息。(见下页)2021/5/2332查询“计应0711”班学生的信息：(代码8) use student go select sno,sname from student inner join class on student.classno=class.classno where classname=计应0711此处的inner join与join功能相同：如果表中有至少一个匹配，则返回行；否则，不返回。Left join：即使右表中没有匹配，也从左表返回所有的行。Right join：即使左表中没有匹配，也从右表返回所有的行。Full join:只要其中一个表中存在匹配，就返回行。2021/5/2333 SELECT 语句的基本语法格式语句的基本语法格式 SELECT select_list INTO new_table_name FROM table_list WHERE search_condition1 GROUP BY group_by-list HAVING search_condition2 ORDER BY order_listASC DESC 2021/5/2334使用比较运算符 、=、=、!、!= 比较结果是布尔值，即：true(表达式结果为真)、false及unknown。数据类型为text、ntext、image的数据不可用比较运算符。练习：(代码8)1.查询所有成绩在70分以下的学生选课信息。2021/5/2335使用逻辑运算符 and、or、not三种。 练习： (代码8)1.查询职称为教授或者年龄超过50岁的女教师信息。2.查询出生日期在1975到1980间的教师信息。3.查询出生日期不在1975到1980间的教师信息。 2021/5/2336使用 集合运算符in、not in，用来查找某个值是否属于某个集合记录。使用union可以将查询结果并成一个集合。 参加union操作的各个结果集合的集合元素的个数及类型必须相同，系统会清除重复的记录。Union中的各个结果集一般来自于不同的数据表，当各个结果集来自同一张数据表时，一般使用集合运算符in。练习： (代码8)1、查询“07010111”班或者是“07010211”班的学生信息。 2、查询student123表中10101班和student456表中10105班的学生信息。3、查询student表中07010111班和student1表中07010211班的学生信息。2021/5/2337使用字符匹配运算符通配符含义举例说明%代表零个或多个字符的任意字符串like LO%表示所有以”LO”开头的名字_任意单个字符like _rt表示所有以”rt”结尾的三个字母的名字 任意在指定范围内或集合中的单个字符like a-dear表示所有以”a”到”d”中任意一个字母开头，以”ear”结尾的四个字母的名字任意不在指定范围或集合中的单个字符like DC%表示所有以”D”开头、第二个字母不为”C”的名字2021/5/2338练习： (代码8)1、查询07级中学号尾号为5的学生的信息2、查询姓“张”、“李”、“王”的教师的信息。3、查询课程名为“VFP_6.0数据库”的课程的信息。p1082021/5/2339学习目标：会用SQL Server Management Studio创建视图会用CREATE VIEW语句创建并保存视图能够对查询的信息进行排序2021/5/23401.创建视图v_xs2.利用视图查询计算机应用技术专业中女生的信息3.利用视图修改学生信息。2021/5/2341【任务分析】首先，要查询“计算机应用技术”专业的学生，首先要确定查询所需的数据表有哪些。通过分析我们可以知道使用简单查询（单表查询），显然无法完成。除了用到student表外，还需要用到与之相关的class表和professional表。其次，查询的结果要保存成视图，视图是一种虚表，也是SQL Server中的一种数据库的对象，它可以由查询语句生成。 2021/5/23421、右击【视图】节点，在弹出的快捷菜单中单击【新建视图】命令，打开“视图设计器”窗口和“添加表”对话框 2021/5/23432、选择“student”、“class”和“professional”表，单击【添加】按钮将其添加进去作为视图的基表。 2021/5/23443、添加完毕后，单击【关闭】按钮关闭“添加表”对话框 2021/5/23454、在关系图窗格中勾选相应表中的相应列的复选框，即依次选择“student”表中的sno、 sname字段和“class”表中的classname字段， 以及“professional”表中的 pname字段 2021/5/23465、在条件窗格中的“筛选器”中设置筛选记录的条件。即在条件窗格中选择“professional”表中的“pname”，在“筛选器”列中输入“计算机应用技术” 2021/5/23476、在“视图设计器”窗口中，单击【验证T-SQL句法】按钮，检查语法错误。语法正确后，单击红色感叹号按钮执行，可以预览视图结果 2021/5/23487、选择“student”、“class”和“professional”表，单击【添加】按钮将其添加进去作为视图的基表。 2021/5/2349程序代码： CREATE VIEW v_xsASSELECT dbo.student.sno, dbo.student.sname, dbo.class.classname, dbo.professional.pnameFROM dbo.class INNER JOIN dbo.professional ON dbo.class.pno = dbo.professional.pno INNER JOIN dbo.student ON dbo.class.classno = dbo.student.classnoWHERE (dbo.professional.pname = 计算机应用技术)方法二：（用create view）语句编程实现 （代码12）2021/5/2350分析，执行上述代码 2021/5/2351任务分析： 视图“v_xs”定义好后，可以像使用数据表一样用视图进行查询。本任务中要使用视图v_xs查询计算机应用技术专业中计应0611班的学生信息。 任务任务2 利用视图查询计应利用视图查询计应0611班的学生信班的学生信息息 （代码12）2021/5/2352程序代码： USE studentGOSELECT * FROM v_xsWHERE classname=计应0611GO利用视图进行查询时可以将视图当成一张普通的数据表，查询语句的基本格式和数据表查询完全相同。2021/5/2353分析，执行上述代码 2021/5/2354任务分析： 通过视图修改信息，包括数据的增加、删除与更新。本任务要求将视图“v_xs”视图中姓名为“金伟”的同学修改为“胡伟”，是对数据的更新。由于视图不存储数据实体，实际上的更新操作在源数据表中实施。要完成该任务，可以使用UPDATE语句。 任务任务3 将视图将视图v_xs中的学生金伟更改为中的学生金伟更改为“胡伟胡伟” （代码12）2021/5/2355程序代码：USE studentGOUPDATE v_xs SET sname=胡伟WHERE sname=金伟GO 2021/5/2356分析，执行上述代码 2021/5/2357视图的基础知识视图的基础知识1.含义：视图是一种数据库对象，它是从一个或含义：视图是一种数据库对象，它是从一个或多个表或视图导出的虚表，即它可以从一个多个表或视图导出的虚表，即它可以从一个或多个表中的一列或多个列中提取数据，并或多个表中的一列或多个列中提取数据，并按照表的组成行和列来显示这些信息。按照表的组成行和列来显示这些信息。 视图中的数据是在视图视图中的数据是在视图被使用时被使用时动态生成的，动态生成的，数据随着数据源表的变化而变化。数据随着数据源表的变化而变化。2.分类分类:标准视图、分区视图、索引视图。标准视图、分区视图、索引视图。 2021/5/2358视图的操作视图的操作-创建创建 视图的创建既可以使用SQL Server Management Studio中的操作来完成，也可以使用CREATE VIEW语句来定义，它的基本语法为：CREATE VIEW schema_name . view_name (column ,.n ) WITH ,.n AS select_statement ; WITH CHECK OPTION := ENCRYPTION SCHEMABINDING VIEW_METADATA 强制视图上执行的所有数据修改语句都必须符合由select_statement设置的条件表示对视图的创建语句进行加密表示在select_statement语句中如果包含表、视图或引用用户自定义函数，则表名、视图名或函数名前必须有所有者前缀。表示如果某一查询中引用该视图且要求返回浏览模式的元数据时，sql server将向DBLIB和OLE DB APIS返回视图的元数据信息。2021/5/23591.创建视图v_xs0611,要求能够查询06010111班的学生信息。2.创建视图v_jsjxs，要求能够查询计算机工程系的学生信息。2021/5/23601.用户是否对引用的数据表和视图拥有权限。2.视图的命名要符合规范，不能和本数据库的其他数据库对象名称相同。3.视图的基表既可以是表，也可以是其他视图。4.不能在视图上运用规则、默认和触发器等数据库对象。不能在视图上创建索引。5.删除视图所以来的数据表或其他视图时，视图的定义不会被系统自动删除。2021/5/2361视图定义完后，如果对其定义不太满意，既可以通过SQL Server Management Studio的可视化界面来修改，也可以ALTER VIEW语句进行修改。例.修改视图v_vx0611为加密视图，要求能够查询06010111班男生的信息。（代码12） 2021/5/2362如果视图不需要时，可以通过SQL Server Management Studio的可视化界面来删除，也可以使用DROP 语句进行删除。DROP语句的语法格式为： DROP VIEW view_name ,n 例.删除视图v_xs0611(代码12)2021/5/2363视图定义完成后，可以运用视图进行数据查询，也可以运用视图进行数据的增加，删除或更新。例.利用视图v_jsjxs为数据表student增加一条记录。（代码12）2021/5/2364学习目标：知道常用的内置函数的种类会使用字符串等常用的内置函数会创建用户自定义函数2021/5/23651.使用函数查找姓陈的同学，并格式化显示其出生年月。2.创建自定义函数xbxs，该函数可以根据输入的系部代码返回该系学生的学号、姓名和出生年份。2021/5/2366【任务分析】1.本任务要查找“陈”姓学生的信息，可以使用前面已经介绍的模糊查询来实现。这个任务要求使用SQL Server中字符串函数LEFT来查找“陈”姓的学生信息。2.另外，本任务要求将查询的结果格式化为“1988年8月”这样的形式，直接在SELECT子句中罗列字段sbirthday无法实现这样的显示效果，要用到日期时间函数YEAR及字符串函数STR、LTRIM等。 2021/5/2367程序代码：USE studentGOSELECT sname 姓 名 ,STR(YEAR(sbirthday)+年+LTRIM(STR(MONTH(sbirthday)+月 AS 出生年月FROM studentWHERE LEFT(sname,1)=陈GO 将数值数据转换为字符数据去除字符串左边的空格+是字符串的连接运算符，可以将多个字符串连接起来。Left(字符型表达式,整型表达式)函数返回字符串中从左边开始指定个数的字符。等价于使用通配符“陈%”。2021/5/2368分析，执行上述代码 2021/5/23691.查询姓名由两个汉字组成的姓陈的学生的信息。2021/5/2370STR() -函数把数值型数据转换为字符型数据LTRIM() -函数把字符串头部(左边)的空格去掉RTRIM() -函数把字符串尾部(右边)的空格去掉LEFT(),RIGHT(),SUBSTRING() -函数返回部分字符串 例如： select substring(abd,2,2)2021/5/2371GETDATE() -函数以DATETIME 的缺省格式返回系统当前的日期和时间DAY() -函数返回date_expression 中的日期值MONTH() -函数返回date_expression 中的月份值YEAR() -函数返回date_expression 中的年份值DATEDIFF( , ,)-函数返回两个指定日期在datepart 方面的不同之处2021/5/2372例如：select datediff(year,sbirthday,getdate() from student2021/5/2373【任务分析】 除了系统函数外，用户还可以根据需要自定义函数，并且调用自定义函数。这个任务主要是完成一个用户自定义函数的创建，函数的主要功能是能够根据输入的班级编码，显示学生表中该班级编码对应的学生的学号、姓名和出生日期信息。这里使用CREATE FUNCTION命令来创建用户自定义函数。 2021/5/2374程序代码： USE studentGOCREATE FUNCTION bjxs(bjbm char(8) RETURNS tableAS RETURNSELECT sno 学号,sname 姓名,ssex 性别,sbirthday 出生日期FROM studentWHERE classno=bjbmGOSELECT *FROM dbo.bjxs(06010211) 函数的输入参数说明函数将返回一张数据表表示根据函数输入参数的值来筛选记录2021/5/2375分析，执行上述代码 2021/5/2376数据库中函数的种类数据库中函数的种类 系统函数系统函数也成为系统内置函数，它是SQL Server 2005直接提供给用户使用的。一般又可以分为标量函数（包括数学函数、日期时间函数、字符串函数等）、和聚合函数两大类。用户自定义函数用户自定义函数是用户为了实现某项特殊的功能自己创建的，用来补充和扩展内置函数。 2021/5/2377数据库中函数含义及格式数据库中函数含义及格式 SQL函数与其他程序设计语言中的函数类似，具有特定的功能，其目的是为了给用户提供方便。它的形式一般包含函数名，输入及输出参数。例如ABS函数的格式为：ABS 它的功能是返回给定数值表达式的绝对值。 2021/5/2378部分资料从网络收集整理而来，供大家参考，感谢您的关注！