项目四 学生基本信息查询 -by anita

1、学生基本信息查询,信息管理教研室：李姗 办公室：a4201 email: lis phoneno李姗 信管教研室,3,项目四 学生基本信息查询,模块 4-1 用sql语句进行简单查询 模块4-2 用视图进行查询 模块4-3 用函数进行查询,李姗 信管教研室,4,模块 4-1 用sql语句进行简单查询学习目标,能够用select语句进行简单查询 能够按照字段对信息进行筛选 能够对查询的记录进行排序,李姗 信管教研室,5,工作任务,查询student表中的若干列。 查询student表中的若干行。 查询信息的排序显示。 使用like子句实现模糊查询。,李姗 信管教研室,

2、6,任务1 查询student表中的若干列。,【任务分析】 查询信息是数据库的基本功能之一，通常可以使用select 语句来完成查询操作。本任务要完成对student表的若干列的查询，这里的若干列既可以是全部列、也可以是部分列、也可以是一些列的合成的结果集。因此该任务继续分为下面几个子任务： 任务1.1 查询student表的学生编码、姓名、性别和入学成绩(全部列) 任务1.2 查询student表的所有学生信息(部分列) 任务1.3 查询 student表的学生的姓名和年龄(列的合成的结果集),李姗 信管教研室,7,任务1.1：查询student表的学生编码、姓名、性别和入学成绩（代码8）,

3、程序代码： use student go select sno,sname,ssex,sscore from student go,select子句中依次列出要查询的字段(字段间用逗号加以分隔),from子句指明数据来源于哪张数据表或视图,李姗 信管教研室,8,执行结果,分析，执行上述代码,李姗 信管教研室,9,程序代码： use student go select * from student go,任务1.2：查询student表的所有学生信息（代码8）,查询所有信息，可以在select子句中依次列出表中的所有列，也可以使用通配符 “”,from子句指明数据来源于哪张数据表或视图,李姗 信

4、管教研室,10,执行结果,分析，执行上述代码,李姗 信管教研室,11,程序代码： use student go select sname 姓名,year(getdate()-year(sbirthday) 年龄 from student go,任务1.3：查询 student表的学生的姓名和年龄（代码8）,该列的别名，用来友好地显示相关查询字段的 信息。,year()函数的功能是返回年份，getdate()函数的功能是返回系统当前的时间和日期。 year(getdate()-year(sbirthday) 表达式用来计算学生的年龄。,李姗 信管教研室,12,执行结果,分析，执行上述代码,李姗

5、信管教研室,13,任务2 查询student表中的若干行,【任务分析】 本任务要完成对student表的若干行的查询，可以通过where、top和distinct来实现。where子句可以筛选出满足条件的记录，top可以对记录的条数进行具体限定，而distinct则可以清除一些重复的行。因此该任务继续分为下面几个子任务： 任务2.1 查询student表的“07010211”班的男生信息 任务2.2 应用top子句查询choice表中选修“0101001”课程的三位学生 任务2.3 应用distinct子句消除重复行,李姗 信管教研室,14,select语句格式 select top n di

6、stinct列名列表 from 表名 where 查询条件,李姗 信管教研室,15,程序代码： use student go select * from student where classno=07010211 and ssex=男 go,任务2.1：查询student表的“07010211”班的男生信息 （代码8）,两个条件之间是并且的关系，可以用逻辑运算符and。,李姗 信管教研室,16,执行结果,分析，执行上述代码,李姗 信管教研室,17,练习（代码8）,1、查询teacher表中所有女教师的信息。 2、查询teacher表中所有女教师的姓名和职称。 3、查询teacher表中所有教

7、师的姓名和年龄。 4、查询teacher表中所有女教师的姓名和年龄。,李姗 信管教研室,18,程序代码： use student go select top 3 * from choice where cno=0101001 go,任务2.2：应用top子句查询choice表中选修“0101001”课程的三位学生 (代码8),表示查询前3条记录。还可在字段列表之前用top 20 percent关键字，查询结果将只显示前20%的记录。(p101-2.sql) top字句位于select和列名列表之间。,李姗 信管教研室,19,执行结果,分析，执行上述代码,李姗 信管教研室,20,练习(代码8),

8、1、查询student表中前10名学生的姓名。 2、查询student表中第10条到20条数据中学生的姓名。 3、查询student表中第10条数据的学生姓名。,李姗 信管教研室,21,程序代码： use student go select distinct sno from choice go,任务2.3：应用distinct子句消除重复行（代码8）,distinct用于清除一些重复的行，使用时介于select和列名列表之间。,李姗 信管教研室,22,执行结果,分析，执行上述代码,李姗 信管教研室,23,任务3 查询信息的排序显示,【任务分析】 在student表中查询全体学生的信息，查询结

9、果按所在的班级的班级代码降序排序，同一个班级的学生按照学号升序排序。对于结果的排序可以使用order by 语句来控制，其中asc表示升序，desc表示降序。,李姗 信管教研室,24,use student go select * from student order by classno desc,sno asc go,程序代码：（代码8）,层次递进说明，不是并列的。,李姗 信管教研室,25,执行结果,分析，执行上述代码,李姗 信管教研室,26,练习（代码8）,1、在student表中查询全体学生信息，查询结果按出生日期降序排列。 2、在teacher表中查询所有女教师的信息，按出生日期升序

10、排列。 3、在student表中查询06010111班的学生信息，按入学成绩升序排列。 4. 在choice表中查询所有学生的选课情况，查询结果按考试成绩从大到小排列,李姗 信管教研室,27,任务4 使用like子句实现模糊查询。,【任务分析】 在student表中查询陈姓学生的基本信息，查询结果出生年月降序排序。这里的查询条件“陈姓”学生含义比较宽泛，不能直接使用sname=陈来表示，而要使用like子句并跟上通配符的形式，。查询结果排序则可以使用order by 语句来控制，其中asc表示升序，desc表示降序。,李姗 信管教研室,28,use student go select * fr

11、om student where sname like 陈% order by sbirthday desc go,程序代码(代码8）,李姗 信管教研室,29,执行结果,分析，执行上述代码,李姗 信管教研室,30,练习（代码8）,1、在student表中查询张姓学生的基本信息，查询结果按出生日期的升序排列。 2、查student表中07级的学生信息，查询结果按所在班级的班级编号的升序排序，同一个班级的学生按照学号升序排列。 3、查询student表中入学成绩大于400分的学生的学号、姓名和入学成绩，结果按成绩升序排序。,李姗 信管教研室,31,知识说明 :（一）数据库关系,关系数据库的关系之间

12、可以通过运算获取相关的数据，其基本运算的种类主要有选择、投影和连接运算，它们来自关系代数中的并、交、差、选择、投影等运算。 投影 从一个表中选择一列或者几列形成新表的运算称为投影。投影是对数据表的列进行的一种筛选操作，新表的列的数量和顺序一般与原表不尽相同。在sql server中的投影操作通过select子句中限定列名列表来实现。 练习：查询teacher表中的教师编号和姓名。,李姗 信管教研室,32,知识说明,选择 从一个表中选择若干行形成新表的运算称为选择。选择是对数据表的行进行的一种筛选操作，新表的行的数量一般跟原表不尽相同。在sql server中的选择操作通过where 子句中限定

13、记录条件来实现。 练习：查询student表中1988年以后出生的学生的学号和姓名。 连接 从两个或两个以上的表中选择满足某种条件的记录形成新表的运算称为连接。连接与投影和选择不同，它的运算对象是多表。它可以分为交叉连接、自然连接、左连接、右连接等不同的类型。 例：查询“计应0711”班学生的信息。(见下页),李姗 信管教研室,33,查询“计应0711”班学生的信息：(代码8) use student go select sno,sname from student inner join class on student.classno=class.classno where classnam

14、e=计应0711,此处的inner join与join功能相同：如果表中有至少一个匹配，则返回行；否则，不返回。 left join：即使右表中没有匹配，也从左表返回所有的行。 right join：即使左表中没有匹配，也从右表返回所有的行。 full join:只要其中一个表中存在匹配，就返回行。,李姗 信管教研室,34,知识说明：（二）select,select 语句的基本语法格式 select select_list into new_table_name from table_list where search_condition1 group by group_by-list hav

15、ing search_condition2 order by order_listasc desc,李姗 信管教研室,35,知识说明：（三）where子句的常用查询条件,使用比较运算符 、=、=、!、!= 比较结果是布尔值，即：true(表达式结果为真)、false及unknown。数据类型为text、ntext、image的数据不可用比较运算符。 练习：(代码8) 查询所有成绩在70分以下的学生选课信息。,李姗 信管教研室,36,知识说明：（三）where子句的常用查询条件,使用逻辑运算符 and、or、not三种。 练习： (代码8) 查询职称为教授或者年龄超过50岁的女教师信息。 查询出

16、生日期在1975到1980间的教师信息。 查询出生日期不在1975到1980间的教师信息。,李姗 信管教研室,37,知识说明：（三）where子句的常用查询条件,使用 集合运算符 in、not in，用来查找某个值是否属于某个集合记录。 使用union可以将查询结果并成一个集合。 参加union操作的各个结果集合的集合元素的个数及类型必须相同，系统会清除重复的记录。union中的各个结果集一般来自于不同的数据表，当各个结果集来自同一张数据表时，一般使用集合运算符in。 练习： (代码8) 1、查询“07010111”班或者是“07010211”班的学生信息。 2、查询student123表中1

17、0101班和student456表中10105班的学生信息。 3、查询student表中07010111班和student1表中07010211班的学生信息。,李姗 信管教研室,38,知识说明：（三）where子句的常用查询条件,使用字符匹配运算符,李姗 信管教研室,39,练习： (代码8) 1、查询07级中学号尾号为5的学生的信息 2、查询姓“张”、“李”、“王”的教师的信息。 3、查询课程名为“vfp_6.0数据库”的课程的信息。p108,李姗 信管教研室,40,模块 4-2 用视图进行查询,学习目标： 会用sql server management studio创建视图 会用create

18、 view语句创建并保存视图 能够对查询的信息进行排序,李姗 信管教研室,41,工作任务,创建视图v_xs 利用视图查询计算机应用技术专业中女生的信息 利用视图修改学生信息。,李姗 信管教研室,42,任务1 在student数据库中查询“计算机应用技术”专业所有学生的信息，并将查询结果保存为视图v_xs。,【任务分析】 首先，要查询“计算机应用技术”专业的学生，首先要确定查询所需的数据表有哪些。通过分析我们可以知道使用简单查询（单表查询），显然无法完成。除了用到student表外，还需要用到与之相关的class表和professional表。 其次，查询的结果要保存成视图，视图是一种虚表，也是

19、sql server中的一种数据库的对象，它可以由查询语句生成。,李姗 信管教研室,43,操作步骤-方法一（在management studio界面中操作实现）,1、右击【视图】节点，在弹出的快捷菜单中单击【新建视图】命令，打开“视图设计器”窗口和“添加表”对话框,李姗 信管教研室,44,操作步骤,2、选择“student”、“class”和“professional”表，单击【添加】按钮将其添加进去作为视图的基表。,李姗 信管教研室,45,操作步骤,3、添加完毕后，单击【关闭】按钮关闭“添加表”对话框,李姗 信管教研室,46,操作步骤,4、在关系图窗格中勾选相应表中的相应列的复选框，即依次选

20、择“student”表中的sno、 sname字段和“class”表中的classname字段， 以及“professional”表中的 pname字段,李姗 信管教研室,47,操作步骤,5、在条件窗格中的“筛选器”中设置筛选记录的条件。即在条件窗格中选择“professional”表中的“pname”，在“筛选器”列中输入“计算机应用技术”,李姗 信管教研室,48,操作步骤,6、在“视图设计器”窗口中，单击【验证t-sql句法】按钮，检查语法错误。语法正确后，单击红色感叹号按钮执行，可以预览视图结果,李姗 信管教研室,49,操作步骤,7、选择“student”、“class”和“profes

21、sional”表，单击【添加】按钮将其添加进去作为视图的基表。,李姗 信管教研室,50,程序代码： create view v_xs as select dbo.student.sno, dbo.student.sname, dbo.class.classname, fessional.pname from dbo.class inner join fessional on dbo.class.pno = fessional.pno inner join dbo.student on dbo.class.classno = dbo.student.cl

22、assno where (fessional.pname = 计算机应用技术),方法二：（用create view）语句编程实现 （代码12）,李姗 信管教研室,51,执行结果,分析，执行上述代码,李姗 信管教研室,52,任务分析： 视图“v_xs”定义好后，可以像使用数据表一样用视图进行查询。本任务中要使用视图v_xs查询计算机应用技术专业中计应0611班的学生信息。,任务2 利用视图查询计应0611班的学生信息 （代码12）,李姗 信管教研室,53,程序代码： use student go select * from v_xs where classname=计应0611 g

23、o,利用视图进行查询时可以将视图当成一张普通的数据表，查询语句的基本格式和数据表查询完全相同。,李姗 信管教研室,54,执行结果,分析，执行上述代码,李姗 信管教研室,55,任务分析： 通过视图修改信息，包括数据的增加、删除与更新。本任务要求将视图“v_xs”视图中姓名为“金伟”的同学修改为“胡伟”，是对数据的更新。由于视图不存储数据实体，实际上的更新操作在源数据表中实施。要完成该任务，可以使用update语句。,任务3 将视图v_xs中的学生金伟更改为“胡伟” （代码12）,李姗 信管教研室,56,程序代码： use student go update v_xs set sname=胡伟 w

24、here sname=金伟 go,李姗 信管教研室,57,执行结果,分析，执行上述代码,李姗 信管教研室,58,知识说明：视图,视图的基础知识 1.含义：视图是一种数据库对象，它是从一个或多个表或视图导出的虚表，即它可以从一个或多个表中的一列或多个列中提取数据，并按照表的组成行和列来显示这些信息。 视图中的数据是在视图被使用时动态生成的，数据随着数据源表的变化而变化。 2.分类:标准视图、分区视图、索引视图。,李姗 信管教研室,59,知识说明：视图,视图的操作-创建 视图的创建既可以使用sql server management studio中的操作来完成，也可以使用create view语句

25、来定义，它的基本语法为： create view schema_name . view_name (column ,.n ) with ,.n as select_statement ; with check option := encryption schemabinding view_metadata ,强制视图上执行的所有数据修改语句都必须符合由select_statement设置的条件,表示对视图的创建语句进行加密,表示在select_statement语句中如果包含表、视图或引用用户自定义函数，则表名、视图名或函数名前必须有所有者前缀。,表示如果某一查询中引用该视图且要求返回浏览模式

26、的元数据时，sql server将向dblib和ole db apis返回视图的元数据信息。,李姗 信管教研室,60,练习（代码12）,1.创建视图v_xs0611,要求能够查询06010111班的学生信息。 2.创建视图v_jsjxs，要求能够查询计算机工程系的学生信息。,李姗 信管教研室,61,知识说明创建视图需注意的,1.用户是否对引用的数据表和视图拥有权限。 2.视图的命名要符合规范，不能和本数据库的其他数据库对象名称相同。 3.视图的基表既可以是表，也可以是其他视图。 4.不能在视图上运用规则、默认和触发器等数据库对象。不能在视图上创建索引。 5.删除视图所以来的数据表或其他视图时，

27、视图的定义不会被系统自动删除。,李姗 信管教研室,62,知识说明：视图的修改,视图定义完后，如果对其定义不太满意，既可以通过sql server management studio的可视化界面来修改，也可以alter view语句进行修改。 例.修改视图v_vx0611为加密视图，要求能够查询06010111班男生的信息。（代码12）,李姗 信管教研室,63,知识说明：视图的删除,如果视图不需要时，可以通过sql server management studio的可视化界面来删除，也可以使用drop 语句进行删除。drop语句的语法格式为： drop view view_name ,n 例.删

28、除视图v_xs0611(代码12),李姗 信管教研室,64,知识说明视图的运用,视图定义完成后，可以运用视图进行数据查询，也可以运用视图进行数据的增加，删除或更新。 例.利用视图v_jsjxs为数据表student增加一条记录。（代码12）,李姗 信管教研室,65,模块 4-3 用函数进行查询,学习目标： 知道常用的内置函数的种类 会使用字符串等常用的内置函数 会创建用户自定义函数,李姗 信管教研室,66,工作任务,使用函数查找姓陈的同学，并格式化显示其出生年月。 创建自定义函数xbxs，该函数可以根据输入的系部代码返回该系学生的学号、姓名和出生年份。,李姗 信管教研室,67,任务1 使用函数

29、查找姓陈的同学，并格式化显示其出生年月（代码12）,【任务分析】 本任务要查找“陈”姓学生的信息，可以使用前面已经介绍的模糊查询来实现。这个任务要求使用sql server中字符串函数left来查找“陈”姓的学生信息。 另外，本任务要求将查询的结果格式化为“1988年8月”这样的形式，直接在select子句中罗列字段sbirthday无法实现这样的显示效果，要用到日期时间函数year及字符串函数str、ltrim等。,李姗 信管教研室,68,程序代码： use student go select sname 姓名,str(year(sbirthday)+年+ltrim(str(month(sb

30、irthday)+月 as 出生年月 from student where left(sname,1)=陈 go,将数值数据转换为字符数据,去除字符串左边的空格,+是字符串的连接运算符，可以将多个字符串连接起来。,left(字符型表达式,整型表达式)函数返回字符串中从左边开始指定个数的字符。等价于使用通配符“陈%”。,李姗 信管教研室,69,执行结果,分析，执行上述代码,李姗 信管教研室,70,练习（代码12）,1.查询姓名由两个汉字组成的姓陈的学生的信息。,李姗 信管教研室,71,常用字符串函数,str() -函数把数值型数据转换为字符型数据 ltrim() -函数把字符串头部(左边)的空格去掉 rtrim() -函数把字符串尾部(右边)的空格去掉 left(),right(),substring() -函数返回部分字符串 例如： select substring(abd,2,2),李姗 信管教研室,72,常用日期函数,getdate() -函数以datetime 的缺省格式返回系统当前的日期和时间 day() -函数返回date_expression 中的日期