项目四 数据库查询.docVIP

项目四 数据查询 [实验目的及要求] 熟练掌握在SQL Server查询分析器中，使用Transact-SQL语句查询数据的方法。 [实验原理] SQL（Structured Query Language），即结构化查询语言，是关系数据库的标准语言，是一个通用的、功能极强的关系数据库语言。 用CREATE INDEX、DROP INDEX语句定义索引；用SELECT语句查询数据库。 SELECT select_list [ INTO new_table ] FROM table_source [ WHERE search_condition ] [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ] 语法 SELECT statement ::= query_expression [ ORDER BY { order_by_expression | column_position [ ASC | DESC ] } [ ,...n ] ] [ COMPUTE { { AVG | COUNT | MAX | MIN | SUM } ( expression ) } [ ,...n ] [ BY expression [ ,...n ] ] ] [ FOR { BROWSE | XML { RAW | AUTO | EXPLICIT } [ , XMLDATA ] [ , ELEMENTS ] [ , BINARY base64 ] } ] [ OPTION ( query_hint [ ,...n ]) ] query expression ::= { query specification | ( query expression ) } [ UNION [ ALL ] query specification | ( query expression ) [...n ] ] query specification ::= SELECT [ ALL | DISTINCT ] [ { TOP integer | TOP integer PERCENT } [ WITH TIES ] ] select_list [ INTO new_table ] [ FROM { table_source } [ ,...n ] ] [ WHERE search_condition ] [ GROUP BY [ ALL ] group_by_expression [ ,...n ] [ WITH { CUBE | ROLLUP } ] ] [HAVING search_condition] [实验内容及步骤] 一、数据查询 1．投影查询 查询,,,,生表中所有记录的姓名、性别、班号 Select sname,ssex,sclass from student 查询教师表中所有的单位 Select distinct depart from teacher 2．选择查询 查询性别为‘女’或年龄在20岁的,,,,生记录 Select * from student Where ssex=’女’or sage=20 查询成绩为85或86的记录 Select * from score Where degree in (85,86) 3.排序查询 以班号降序显示,,,,生表的所有记录 Select * From student Order by sclass desc 4.使用聚合函数 查询各门课程的最高分 Select cno,max(degree) From score Where not degree is null Group by cno 5.表的连接查询 查询所有,,,,生的,,,,号、选修的课程号及成绩 Select sno,cname,degree From score,course Where score.cno=course.cno 查询各班,,,,生所选课程的平均分 Select sclass,avg(degree) from student,score Where student.sno=score.sno Gr