sql修改语句（精选9篇）
1、实验目的：
（1）了解SQL Server数据库的逻辑结构和物理结构；
（2）掌握使用SQL语句修改数据库的方法；
2、实验准备：
（1）要明确能够修改数据库的用户必须是系统管理员，或者是被授权使用CREATE DATABASE语句的用户；
（2）修改数据库必须要明确：除存储位置，即物理文件名不能修改外，其它项均可以修改，包括数据库名、数据库大小和增长量等数据；
（3）了解使用SQL语句修改数据库的方法。
3、实验内容：
（1）删除实验三中创建的数据库；
（2）用自己名字创建一个数据库，包含一个主数据文件data1和一个日志文件log1，最大值都是5M；增长量分别为10%和1M；存储在自己的文件夹下；
（3）添加一个数据文件data2，最大值为3M；
（4）添加一个日志文件log2，最大值为4M；
（5）添加一个文件组group1；
（6）添加一个次数据文件data3放在文件组group1中；
（7）修改数据文件data2，将最大值改为5M；
（8）删除日志文件log1；
（9）删除次数据文件data3、data2；
（10）修改文件组，将group1改名为group2；
（11）删除文件组group2；
（12）将log2的增长量改为1M。
（13）缩小该数据库的大小，保留自由空间1M，保留释放的文件空间。
（14）将数据库的日志文件log2缩小到3M。
Oracle数据库当前应用非常广泛, 通信企业用于运营支撑的系统大部分使用的都是Oracle数据库。系统在实际运行当中, 每天都有各地市的工作人员在通过前台访问数据库, 有维护人员在后台操作数据库, 数据库访问量非常大。同时, 随着数据的增加, 数据库的规模也将不断扩大, 数据库系统的性能问题就会越来越突出。因此, 在现在的软、硬件资源基础上, 如何编写合理的SQL语句, 以优化数据访问, 减少系统开销, 获得最大的数据处理效果, 是摆在维护人员面前的新课题。
2 优化SQL语句
人们在使用SQL时往往会陷入一个误区, 即太关注于所得的结果是否正确, 而忽略了不同的实现方法之间可能存在的性能差异。对于我们日常使用的系统, 劣质SQL语句和优质SQL语句之间的速度差别可高达近百倍。SQL语句优化的基本原则是:通过尽可能少的磁盘访问来获得所需要的数据。文中各例SQL的执行时间是在业务非繁忙期时测得的。
2.1 索引的使用问题
众所周知, 索引是数据库中重要的数据结构, 它的根本目的就是为了提高查询效率。正确合理的使用索引能大大缩短系统的响应时间。下面以在工作中遇到的实际需求为例, 观察使用索引与不使用索引, 以及虽使用索引但方法不同, 所得系统执行效率明显不同的情况。
2.1.1 合理使用索引
需求:提取从6月至10月间小灵通健康行租机用户数据。要求提取时间:11月1日, 内容包括电话号码, 选择的健康行价格, 开始使用时间。serv_acc_nbr表 (电话号码) , serv_price表 (用户选择的价格ID) , price表 (价格名称) 。serv_id列上有唯一索引, price_plan_id列上有非唯一索引。
第一条SQL:
select b.sts_date, a.nxx_nbr||a.line_nbr, c.name
from serv_acc_nbr a, serv_price b, price c
where a.serv_id=b.serv_id and b.price_plan_id=c.price_plan_id
and b.price_plan_id between 30111 and 30131 and a.local_net_id=437 and b.local_net_id=437 and a.sts='A'and b.sts='A'and to_char (b.sts_date, 'yyyy/mm') between'2006/06'and'2006/10';
用时9秒
第二条SQL:
……where b.price_plan_id=c.price_plan_id and b.price_plan_id between 30111 and 30131 and a.serv_id=b.serv_id/*此条件顺序调后*/and a.local_net_id=437 and b.local_net_id=437 and a.sts='A'and b.sts='A'and to_char (b.sts_date, 'yyyy/mm') between'2006/06'and'2006/10';
用时12秒
第三条SQL:
……where a.sts='A'and b.sts='A'/*此条件放在最前*/and a.serv_id=b.serv_id and b.price_plan_id=c.price_plan_id and b.price_plan_id between 30111 and 30131 and a.local_net_id=437 and b.local_net_id=437and to_char (b.sts_date, 'yyyy/mm') between'2006/06'and'2006/10';
用时20秒
分析:这三条SQL连接条件完全一样, 只是顺序有所变化, 但系统的执行效率却有着明显的差异。造成差异的原因就是索引的使用问题, 第一条SQL合理的使用了索引;第二条SQL也用了索引, 但将非唯一索引列price_plan_id放在了前面;第三条SQL虽然也用了索引列, 但却将非索引列sts放在了最前面, 使得索引项不能发挥作用。
2.1.2 避免使用列联接导致索引失效
例如:现有serv_acc_nbr表记录着在用用户使用电话号码的情况, nxx_nbr列存放号头, line_nbr列存放小号。想查辽源地区3225678有没有在用用户, 正确的脚本是:
Select*from serv_acc_nbr where nxx_nbr='322'and line_nbr='5678'and local_net_id=437;
用时不足1秒。
若采用列联接, 将语句写成
Select*from serv_acc_nbr
where nxx_nbr||line_nbr='3225678'and local_net_id=437;
用时8秒。
分析:造成查询一条数据效率却相差8倍的原因是列连接使索引失效。前者使用了nxx_nbr和line_nbr上的索引, 所以速度快, 后者全表扫描, 速度慢。
2.1.3 慎用通配符%
Like是大家都很喜欢用的操作符, 因为里面的通配符%可达到几乎是任意的查询, 但是用得不好会产生性能上的问题。
例如, 用户想选322至327号段后四位带“999”的号码, 大多数人都会将脚本写成:
Select nxx_nbr||line_nbr from acc_nbr
Where nxx_nbr between'322'and'327'and line_nbr like'%999%'and sts='S';
用时11秒
这样写虽然通俗易懂, 但却降低了查询速度。由于通配符 (%) 在词首出现, 所以Oracle系统不会引用line_nbr上的索引, 造成了在322至327范围内的表搜索。可以将脚本改为:
Select nxx_nbr||line_nbr from acc_nbr
Where nxx_nbr between'322'and'327'and line_nbr like'999%'and sts='S';
用时1秒
这样Oracle就会引用line_nbr上的索引。
2.2 充分的连接条件
仍以2.1.1节中提取健康行数据为例, 因价格计划的ID各地市不重复, 又是6月开始推行, 11月1日提取, 查询列中又有时间, 可把语句改写成下面的脚本, 同样能得到所要的结果。
……where a.serv_id=b.serv_id and b.price_plan_id=c.price_plan_id
and b.price_plan_id between 30111 and 30131 and a.sts='A'and b.sts='A';
用时7分13秒
分析:这样修改虽然语句看起来简练了不少, 也合理的使用了索引, 但却因为未限制地区及日期, 造成在全省海量的数据中进行表搜索, 严重增加了系统开销, 效率相差几十倍。
2.3 注意where语句后面的条件顺序
例如:要查322号段的二级号都有哪些空闲可以使用。
Select nxx_nbr||line_nbr from acc_nbr
where nxx_nbr='322'and local_net_id=437 and acc_nbr_class_id=2 and sts='S';
用时1秒。而若写成
Select nxx_nbr||line_nbr from acc_nbr
where nxx_nbr='322'and local_net_id=437 and sts='S'and acc_nbr_class_id=2;
用时6秒
分析:虽然nxx_nbr上有索引, 但sts与acc_nbr_class_id两个列都没有索引, 所以执行的时候是在辽源322号段的10000个号内进行表搜索。acc_nbr_class_id=2的记录数为90条, 而sts='S'的记录数为3050条。在执行第一条SQL的时候, 是90条记录进行sts及acc_nbr_class_id的比较, 而执行第二条SQL的时候, 是3050条记录进行sts及acc_nbr_class_id的比较, 显然可以得出第一条SQL的CPU占用率明显比第二条低, 这就是为什么第一条SQL用时少的原因。
2.4 避免系统发生排序操作
例如:要查健康行入网的用户都有哪些, 下面的脚本
Select nxx_nbr||line_nbr from serv_acc_nbr where serv_id in (select serv_id from serv_price where price_plan_id=30131 and local_net_id=437 and sts='A') and local_net_id=437 and sts='A';
用时1分12秒
若改为:
Select a.nxx_nbr||a.line_nbr from serv_acc_nbr a, serv_price b
where a.serv_id=b.serv_id and b.price_plan_id=30131 and b.local_net_id=437 and a.local_net_id=437 and a.sts='A'and b.sts='A';
用时4秒
分析: (select serv_id……) 中的子查询只执行一次, 系统将结果排序, 保存在ORACLE的临时段中, 其中的每一个记录在返回时都会被父查询所引用。在子查询返回大量记录的情况下, 将这些结果集排序, 会增加大量的系统开销。改写后的脚本可以充分利用索引而不执行系统的排序操作, 从而提高了语句的执行效率。
3 结束语
在数据库运行期间, 要得到工作所需的理想结果, 编写SQL语句可以有多种方式, 如何既能达到目的, 又不降低系统的性能, 是系统维护人员不懈追求的目标。本人从大量的工作实践中总结出:必须在SQL中合理的使用索引, 并保证连接条件充分, 顺序正确, 才能以最少的磁盘访问来获得最大的数据处理效果, 减少系统开销, 提高执行效率, 进而改善系统性能。
摘要：Oracle数据库是目前电信企业广泛使用的数据库, 本文从系统维护的实际情况出发, 阐述了在数据库运行阶段, 如何编写SQL来优化数据访问, 改善系统性能的方法。
关键词：数据库；优化；SQL
中图分类号：TP311文献标识码：A文章编号：1009-3044(2007)15-30611-03
Analysis and Discussion of SQL Statement Optimization
LIU Ming-hua1,2, ZHOU Li1
(1. Glorious Sun School of Business and Management, Donghua University, Shanghai 200051, China；2. Information Center, Fujian Polytechnic of Information Technology, Fuzhou 350003, China)
Abstract:In database application systems, performance becomes one of their main problems needed to be resolved with the increase of data in it. For the demands that the customer wants the database are more and more effective, and this makes us optimize the design of the database. SQL optimization is a very important aspect in system optimization. This paper states some methods of SQL optimization from the application.
Key words:database；optimization；SQL
1 引言
在应用系统开发过程中，由于数据库数据的逐渐增加，系统的响应速度就成为系统设计中越来越需要解决的问题。数据库管理系统设计优化一个很重要的方面就是SQL语句的优化。对于数据库应用程序来说，重点在于SQL的执行效率，本文从SQL语言的定位、分析、调整、优化和技巧五个方面分别讨论SQL语句优化的一些具体方法。
2 SQL语句优化方法
2.1 SQL编码的定位
首先是查找大量使用内存及磁盘I/O的语句，并对其进行准确定位。例如：
selectdisk_reads,sql_text
fromv$sqlarea
where disk_reads>20000
order bydisk_reads desc；
Disk_read SQL_TEXT
12,987select order#,columns,types from orders
Where substr(ordered,1,2)=:1
11,231select custid,city from customers
Where city=CHICAGO
然后查找逻辑读最多的查询语句还有查找锁死其他用户操作的用户。例如：
Select a.serial#, a, sid, s.username, b.id1, c.sql_text
Form v$session a,v$lock b,v$ sqltext c
Where b.id1 in
(select distinct e.id1
From v$session d,v$lock e
Where d.lockwait=e.kaddr)
and a. Sid=b.sid
and c.hash_value=a.sql_hash_value
And b.request=0；
2.2 SQL编码分析
SQL语句的分析过程（无论是否使用优化器，分析过程都相同），包括以下几方面：
–检查语法
–搜索共享池
–搜索数据字典
–计算搜索路径
为提高SQL的共享性，建议使用统一的SQL编程标准如下：
–Put all verbs in one case
–Begin all SQL verbs on a new line
–Right or left align verbs with the initial verb
–Bind variables will ensure all SQL is equal so statements is not reparsed
2.3 SQL编码调整
使用下列信息确定访问路径：
–SQL语句
–在引用表上的索引
–执行计划
–当前优化模式
–提示变量
–表统计（如果是基于成本的优化，可知道选择何种优化）
2.4 SQL编码的具体优化
2.4.1 优化器
以Oracle为例，Oracle的优化器有三种：
–基于规则的优化（RULE）
–基于成本的优化（COST）
–基于选择性的优化（CHOOSE）
以下分别提出三种优化的方法。首先讨论基于规则的优化（rules），Oracle分析器从右到左处理，最后一个表最先被处理。最后一个表应该是包含父键或连接判定到其它表，SQL每一条件都被分级。如此类推，如果优化器有多个索引可选，唯一索引总排在非唯一索引的前面。若两个索引一样，如果是不同表上的两个索引，表的顺序将用于决定使用那个索引；如果两个索引在同一表上，首先引用的索引将被使用。除索引以外，WHERE子句中比较常量和变量比字段之间的比较优先。
然后是基于成本的优化（COST），基于成本的优化器没有固定的费用评估规则，评估是灵活的并基于分析统计的结果变化，逻辑读取的评估标准。用最少的资源实现最大吞吐量（ALL_ROWS）。用最少的资源实现首行最佳响应时间(FIRST_ROWS)。100%地选择唯一索引；计算非唯一索引的选择性；用列的最大最小值处理评估范围；Hint可被用于影响优化器。另外可以通过加入hint来强制改变优化策略。
FULL——强制进行全表扫描
SELECT /*+ FULL(table_name)*/ column1,column2…
INDEX——强制使用指定的索引
SELECT /*+ INDEX(table_name index_name1, index_name2…)*/
ORDERED——强制指定查询时的驱动表
SELECT /*+ ORDERED*/ column1,column2…FROM table1, table2
ALL_ROWS——基于成本的优化，最大的提高数据的吞吐性
SELECT /*+ ALL_ROWS */ (ALL_ROWS hint通常会禁用索引)
最后说明基于选择性的优化（CHOOSE）。From子句的内表外表次序：在表的连接中一个表作为外表，也称驱动表，另一个表作为内表。首先检查外表的每一行是否满足查询条件，对于满足条件的那些外表记录，扫描每一个内表记录并且与它在连接列上进行匹配。优化器选择外表的根据是：满足条件的返回数据行数少；或定位数据行时需要读操作的次数多(不能有效利用索引)；如果有三个或以上的表进行关联查询，中间的表应该作为外表，与之相反的表作为内表。以下是确定表连接次序的实例(基于Sybase)：
select TableA.colx, TableB.coly from TableA, TableB where TableA.col1 = TableB.col1 and TableB.col2=anything and TableA.col2 = something
假设A，B表都是有10条记录满足条件
表A：1,000,000条记录，每个数据页上10行记录，(共100,000页)，没有索引。（定位数据行时需要读操作的次数多-应选作外表)
表B：100,000条记录，每个数据页上10行记录，(共10,000页)，在连接列上有聚簇索引，索引树为3层。
通过以下的计算说明应该选表A为外表。如果表A是外表：对它的存取是通过全表扫描，当找到第一个满足条件的记录，在表B上用聚簇索引查找B表中col1列与从A表检索出的值匹配的数据行，做完后继续扫描A表，重复上面的过程，直到A表全部扫描完毕。假设A表有10条满足条件的记录，则该次查询所需的读数据页的次数如下：
- 读取的页数
- A表扫描100,000
- 0次B表索引扫描10*3=30
- 合计100,030
如果表B是外表：对它的存取是通过聚簇索引，当找到第一个满足条件的记录，在表A上用全表扫描A表中col1列与从B表检索出的值匹配的数据行，做完后继续扫描B表，重复上面的过程，直到B表全部扫描完毕。假设B表有10条满足条件的记录，则该次查询所需的读数据页的次数如下：
- 读取的页数
- B表扫描 3
- 10次 A表全表扫描10*100,000=1,000,000
- 合计1,000,003
WHERE子句后面参数的写法：
有索引且能用上索引的表达式，其格式有：
＜column＞＜operator＞＜expression＞
＜expression＞＜operator＞＜column＞
＜column＞ is null（对Oracle不适合）
不等操作符!>和!=是特殊情况，这时查询优化器不能使用索引来定位搜索。
下面的例子是可优化的：
au_lname = "Bennett"
price >= $12.00
price >= $12.00*3
advance >10000 and advance < 20000
下面的例子则不是：salary=commission /*两边都是列名*/
substring(lname,1,3) ="Ben" /*列名边不允许函数*/
advance * 2 = 5000 /*列名边不允许表达式*/
advance =$10000 or price = $12.00 /*含有or谓词*/
2.4.2 可以转化为优化的查询条件书写
(1)between谓词可以转化为>=and<=子句，比如：
price between 10 and 20可以转化为 price>= 10 and <=20
(2)like子句中匹配值的第一个字符是常量，也可以进行转换，例如：
like "sm%"可以转换成 >="sm" and <"sn"
表titles和表titleauthor是一对多关系，title_id在表titles中是唯一的。
1.select au_lname,title from titles t,titleauthor ta,authors a where t.title_id =ta.tatle_id and a.au_id=ta.au_id and t.title_id ="T81002"title_id在表titles中是唯一的。
2.select au_lname,title from titles t,titleauthor ta,authors a where t.title_id =ta.tatle_id and a.au_id=ta.au_idand ta.title_id="T81002"这是最差的，因为title_id在表titleauthor中非唯一。
3.select au_lname,title from titles t,titleauthor ta,authors a where t.title_id =ta.tatle_id and a.au_id=ta.au_id and t.title_id="T81002"and ta.title_id= "T81002"这是最优的。
查询1就可能比查询2执行的好，因为title_id是唯一的，筛选的效果会好些，第3个查询提供了两个表的搜索变量，给优化器更多的选择来调整连接操作表的顺序。
2.4.3 连接顺序（只适用于基于规划的优化器）
Oracel采用自下而上的顺序解析WHERE子句，可以过滤掉最大数量记录的条件写在WHERE子句的末尾。
SELECT ***
FROM EMP E
WHERE 30 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO)
AND SAL > 30000
AND JOB= 'MANAGER'
2.4.4 查询条件书写的注意事项
编写SQL语句时关于查询条件书写应注意在搜索子句的列名边要避免函数、算术操作符和其它的表达式避免不兼容的数据类型使用复合索引的第一个列名给优化器提供尽可能多的查询条件－可以用上索引的检索条件。
2.5 编码的技巧
2.5.1 or与in子句的优化
查询优化器对and和or子句的查询采用不同的处理方式，包含or或in子句查询的优化依赖于在这些子句中使用的表上的索引和这些子句是否返回重复的结果。or子句通常采用下面的形式：
where column1 =
where column1 =
如果or子句中使用的列上有任一个列没有索引或使用索引的开销超过了全表扫描的开销，则执行全表扫描。
OR策略：使用or子句的查询相当于多个查询的union，一些记录可能满足多个条件，但只应当返回一次。OR策略将使用每一列的索引把满足每个or子句的记录行merge 到一起，然后再去掉重复数据行。如果or子句不会返回重复记录，优化器就会使用多个匹配索引查询，每个or子句单独考虑，可以使用不同的索引。如or子句返回重复记录，则采用比较费时的or策略。先把满足每一个or子句的记录行的ID号存储到数据库tempdb中的工作表中，然后进行排序并删除重复的ID号，再利用行ID从基表中检索出不 重复的数据行。or策略在被访问的数据页上维护一共享锁所以会影响并发性。在使用隔离级别3或holdlock子句的情况下，要避免or查询。当在索引列上进行条件判断时，用>=替代>例如下面的查询，在int_col上建有索引：
select * from table where int_col > 3
该查询使用索引找到第一个int_col=3的值，然后向前扫描。如果表中有许多行中int_col的值是3，则该查询不得不扫描许多页后找到第一个int_col大于3的数据行。
若把查询写成下面的形式，则会更有效：
select * from table where int_col >= 4 exist和 not exist 在子查询，exists和in要比not exists和not in执行得快，因为对于exists和in，优化器只要找到一条记录就可以返回TRUE,而对于not exists和not in则必须检查所有的值。
2.6 SQL编码的开发调优
SQL编码的开发调优一般应该考虑以下一些原则：
1、在搜索子句的列名边要避免函数、算术操作符和其它的表达式，除非建立基于函数的索引；
2、使用复合索引的第一个列名；
3、SELECT子句中避免使用'*'；
4、用TRUNCATE替代DELETE；
5、尽量多使用COMMIT；
6、避免在索引列上使用IS NULL和IS NOT NULL(Oracle)；
7、用UNION-ALL 替换UNION(如果有可能的话)；
8、使用较大的BUFFER(比如10MB, 10,240,000)可以提高EXPORT和IMPORT的速度；
9、在物理存储上分离表和索引；
10、当连接的两个或多个数据表的数据量都过大时，考虑将数据表进行整合到一张表中；
11、对于批处理的操作，使用指定的大的回滚段；
12、当使用存储过程处理数据量比较大表时，可考虑使用中间表或临时工作表；
13、当没有可用的索引时，则只能创建工作表对结果数据排序；
14、将纵向表拆成横向表处理，比如费用字段拆成市话、长话、IP费用等；
15、将层次结构复杂（如雪花形结构）的维表如咨询树类型，或将信息比较多的维表比如客户信息表，直接取出维度信息放进事实表中，并对该表建立索引，增加查询的速度。
2.7 SQL编码的应用调优
SQL编码的应用调优应该注意以下几点：
1、对于频繁进行更新和插入的表，比如状态监控表和日志表使用行级锁；
2、对于频繁进行更新和插入的大数据量表，比如日志表，要考虑所建索引的数量，避免建过多的索引（会降低插入和更新操作的执行效率，并且占用大量的存储空间，可能比原表还大）；
3、对于字段较多并且包含memo，text，image等记录长度大的数据表，在查询时要考虑执行效率，比如对于批量工单查询，只需返回有限的几个字段（减少I/O和执行时间），对于工单的精确查询返回全部字段的值；
4、在应用中对业务的处理引入事务机制和异常处理机制，保证业务处理的完整性；
5、考虑主外键约束的使用（灵活与严整），使用触发器或事务处理的方式来保证参照完整性；
6、对于数据量不大的表，可以将数据一次取到内存中，然后再进行计算和查找，比如树形结构的展现。
3 总结
SQL的性能优化是一个复杂的过程，涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。不同的数据库都有各自不同的功能和特色，在实际应用环境中，应该根据实际情况选择较合理的数据库系统设计和优化策略，这样才能充分利用数据库管理系统提供的高性能服务使应用系统能充分发挥其高效的功能。
参考文献：
[1] 袁鹏飞. SQL Server7.0 数据库系统管理与应用开发[M]. 北京:人民邮电出版社,1999.
[2] Patrick O Neil, Elizabeth Oneil. DATABASE: Principles, Programming and Performance[M]. Beijing: Higher Education Press, Morgan Kaufmann Publishers, 2001.
[3] 微软公司. Microsoft SQL Server 7.0系统管理[M].北京:北京希望电子出版社,1999.
[4] SQL Server数据库性能优化技术[M]. 中国IT认证实验室(China IT Lab).
[5] 吕映芝, 张素芝, 蒋维杜. 编译原理[M]. 北京:高等教育出版社，2000.12.
一、实验目的（1）进一步掌握查询的概念和方法。
（2）熟练掌握单表查询的select语句。
（3）熟练掌握聚合函数的使用。
（4）熟练掌握连接查询方法
（5）熟练掌握嵌套查询方法
（6）掌握创建和管理视图的方法。
二、实验内容
1、查询时改变列标题的显示：
检索全体学生的学号、姓名、籍贯信息，并分别加上“学号”、“姓名”、“籍贯”的别名信息。SQL语句：
2、条件查询
（1）查询选修了课程号为“002”，且成绩大于80的学生的学号。
SQL语句：
（2）某些学生选修某门课程后没有参加考试，所以有选课记录，但没有考试成绩，查询缺少成绩的学生的学号和相应的课程号。
SQL语句：
3、基于IN子句的数据查询
从课程表中查询出“高数”、“C语言程序设计”的所有信息。
SQL语句：
4、基于Like子句的查询
从学生表中分别检索出姓张的所有同学的资料；检索名字的第二个字是“红”或“虹”的所有同学的资料。
SQL语句：
5、查询经过计算的值。
查询全体学生的姓名及其出生年份，需为计算值取别名“出生年份”。
SQL语句：
6、使用ORDER BY语句对查询的结果进行排序
显示所有学生的基本信息，并按学生的年龄的降序排列。
SQL语句：
7、使用Group子句进行查询
汇总总分大于是150分的学生的学号及总成绩。
8、使用聚合函数
计算“002”号课程的学生平均成绩、最高分、最低分。
SQL语句：
9、查询与“张虹”在同一个班级的学生学号、姓名、所属系。
（嵌套查询）SQL语句：
连接查询T-SQL语句：
10、创建一个名为stuview的视图，能检索出每位选课学生的学号、姓名、课程名、总成绩。SQL语句为：
11、创建一个名为stuview1的视图，从数据库XSGL的student表中查询出性别为“男”的所有学生的资料。并在创建视图时使用with check option。（注：该子句用于强制视图上执行的所有修改语句必须符合由Select语句where中的条件。）
SQL语句为：
13、管理视图中的数据
1）从视图stuview1查询出籍贯为“内蒙”的学生资料。
SQL语句：
思考：是否student表中所有籍贯为内蒙的同学都被查询出来了？
2）向视图stuview1中插入一行数据。学号：1006，姓名：赵青，籍贯：内蒙，性别：男，年龄：22。
SQL语句：
原student表中的内容有何变化？
思考：如向视图stuview1中插入一行数据。学号：1007，姓名：赵静，籍贯：内蒙，性别：女，年龄：22。
会出现什么样的结果？
原student表中的内容有何变化？
3）修改视图stuview1中的数据。
将stuview1中、姓名为“赵青”同学的年龄改为20岁。
SQL语句：
原student表中的内容有何变化？
4）从视图中stuview1将籍贯为内蒙、姓名为“赵青”同学删除。
一、数据定义功能
创建表：Createtable 表名（字段名……）修改表：Altertable表名
删除表：Droptable表名
二、数据操纵功能
数据插入：Insertinto 表名values(…，…，)数据更新：Update表名Set表达式 Where 条件 数据删除：DeleteFrom 表名Where 条件
三、查询功能
SELECT*FROM表名
WHERE条件
ORDERBY 字段排序
GROUPBY 字段分组
函数：SUM()AVG()COUNT()MAX()MIN()去向：
INTOTABLE表
INTOCURSOR临时表
一、实验目的1、掌握数据完整性控制的几种定义方法；
2、利用T-SQL命令和图形化界面操作常用控制数据的方法。
二、实验任务
1、使用SSMS对StudentCourseMS数据库3个表的各类约束进
行定义、查看、修改、删除等基本操作；
2、使用T-SQL命令对StudentCourseMS数据库3个表的各类约束进行定义、查看、修改、删除等基本操作；
三、实验内容
1、主键约束
(1)创建新的表
A）单一字段上添加主键约束
Create table 学生表
（学号 char(12)constraint pk_xh primarykey,姓名 char(8)not null,性别 char(2)）
B）多个字段上添加主键约束
Create table 学生选课表
（constraint pk_xx primary key(学号,课程号),学号 char(12),课程号 char(8),成绩 tinyint）
(2)修改表
Alter table 课程表
Add constraint pk_kch primary key(课程号)；
Go
(3)删除主键约束
Alter table 课程表
Drop constraint pk_kch;
(4)查看约束
Sp_help pk_xh2、唯一约束
(1)在创建表时创建唯一约束
Create table 学生表
（学号 char(12)constraint pk_xh primarykey, 姓名 char(8)not null unique,性别 char(2)）
(2)修改表
(3)删除唯一约束
(4)查看唯一约束
3、外键约束
4、检查约束
5、默认值约束
随着网络的广泛应用, 基于B/S模式的应用程序也越来越多。由于程序员的水平及经验也参差不齐, 一部分程序员在编写代码的时候, 不注意书写规范、对用户输入数据的合法性不进行判断, 使得应用程序执行效率低, 且存在网络安全等问题。
2. 问题描述及解决方法
查询是关系数据库中最基本的数据操作, 通过SELECT语句来实现, 其基本语法格式[1]:
SELECT[ALL|DISTINCT]字段列表
[INTO目标数据表]
FROM源数据表[, …n]
[WHERE条件表达式]
[GROUP BY分组表达式]
[HAVING搜索表达式]
[ORDER BY排序表达式[, …n][ASC][DESC]]
[COMPUTE行聚合函数名 (统计表达式) [, …n][BY分类表达式[, …n]]]
(1) *用法问题。对于初学者来说在编写SQL语句程序时, 为了图方便, 常用*来代替所有字段, 经常会出现:Select*from…这样的语句。这种语句的功能是显示出表中的每一个字段的全部信息, 包括不需要的信息也被显示出来, 这样会消耗大量的内存开销、降低程序执行效率;如果表中含有图像、备注这样类型的字段, 会导致程序出错无法正常运行。这种SQL写法虽然在逻辑上是没有问题, 但往往在具体的实际应用中由于这种不规范的SQL语句导致程序执行效率低、甚至出错无法正常运行。因此, 在编写SQL语句程序, 切记不要胡乱的使用*。
(2) 字段列表与源数据表排列顺序杂乱、不规范。有些编程者在编写SQL程序时很随意, 字段名、源数据表名任意排列、杂乱无序, 程序在执行时找不到合适的索引, 导致查找速度慢, 影响整个程序执行效率。因此, 在编写SQL语句程序, 字段名、源数据表名排列要遵循一定的规律 (升序或降序) , 这样程序在执行时, 才能够按照规律找到合适的索引, 提高查找效率, 提升程序运行效果。
(3) INTO子句问题。select查询的结果可以直接显示, 也可以不直接显示。在程序设计过程当中往往要用到查询结果, 但不直接显示, 有些编程者往往采用INTO语句将查询结果保存到一个数据表中或一个临时表中。在SQL Server数据库系统中, 如果要将查询结果存放到临时表, 则在临时表名前要加“#”号, 如果当用户断开连接时没有除去临时表, 将自动除去临时表。一旦被黑客捕捉到, 会利用下列类似的程序代码逻辑灌水, 导致服务器资源耗尽而宕机:
可以采用存储过程、游标等较为安全的方法来处理在程序中要用到查询结果。
(4) SQL注入问题。用户可以提交一段数据库查询代码, 根据程序返回的结果, 获得某些他想得知的数据, 这就是所谓的SQL Injection, 即SQL注入。SQL注入的方法相当灵活, 往往根据具体情况进行分析, 构造巧妙的SQL语句, 从而窃取想要的数据。例如下面的SQL语句:
SQL=”select u_right from tbl_user where ID=’’’+user_id+’’’+and PW=’’’+user_pw+’’’”
该语句通过用户从系统界面输入ID号user_id、PW密码user_pw查询用户的权限, 如果用户正常并正确输入自己的ID、PW, 则该语句能够按照程序设计的正常逻辑来运行。如果黑客输入以下字符串:
ID处输入:good (good可以用其它字符代替) 。
PW处输入:’OK’or’8’=’8’ (OK可以用其它字符代替) 。
则真正执行的SQL语句如下所示:
select u_righ t, user_id from tbl_user where ID=’good’and PW=’OK’or’8’=’8’
where子句中的条件永远为true, 结果导致表tbl_user中所有用户的信息都可以看到, 用户输入攻击字符串完成SQL注入。SQL语句强大的功能为SQL注入提供破坏力, 精心构造的攻击字符能够对软件系统、数据库进行非法操作、修改、植入等。
目前SQL注入的防范主要有手工检查、自动过滤、SQL命令编码以及专用的API等方法来防范。JAVA采用预编译语句集, 它内置了处理SQL注入的能力, 使用Prepared State ment来代替Statement来执行SQL语句, SQL注入攻击手段将无效。
3. 结论
有些程序在编写SQL应用程序时, 只专注程序功能的实现, 没有考虑程序的网络安全、执行效率等问题, 导致应用程序宕机、泄漏等问题。希望本文能够使更多的程序员在编写程序时提高程序的执行效率和安全性。
参考文献
[1]西尔伯沙茨.数据库系统概念- (第五版影印版) [M].北京:高等教育出版社, 2006.
关键词 数据库 SQL语句优化 影响因素
中图分类号：TP392 文献标识码：A
数据库系统本身是一个较为复杂的系统，影响其性能的因素也是多方面的。在不断探索中，SQL语句的执行效率逐渐得到重视，实验证明，SQL语句的执行效率在提升系统性能中占有重要地位。在Oracle数据库中，如何优化SQL语句很值得我们深入探讨。
1 影响SQL语句优化的因素
优化器可以完成部分工作，当人为改变优化器的执行计划时同样要遵循这部分基础。总的来说，Oracle优化器制定执行计划的选择基础也是影响执行计划的主要因素，这些因素都可能在特定情况下改变执行计划的内容，而这些因素都显式或隐式的由SQL语句的书写方式和执行情况影响，主要有以下几点：
1.1 表访问方式
表的访问方式主要有三种，全表扫描、Rowid访问、索引扫描。这里，通过Rowid访问表的速度是最快的，因为它不用顺序的读取每一块数据块去查找符合条件的记录，而是直接根据该记录在数据磁盘中的位置获取记录信息；其次是索引访问，它能获取到该行记录的Rowid值再查找进行判断；最后是全表扫描，它只能通过读取全部数据块进行查找，极大增加了系统由于逻辑读取进行的消耗。在实际应用中Rowid的值需要手动获取，因此并不可能在每个查询条件中都根据Rowid进行获取，所以使用最多的是索引扫描对表进行访问。
1.2 表连接顺序
当有两个或两个以上的表进行连接时，Oracle选择一种合适的顺序进行连接。在Oracle数据库中，无论进行连接的表一共有多少个，每次进行连接的表都是两个，连接成一张表之后再与下一张表进行连接，直至完成所有表的连接，所以每次连接的效率都只与当前连接的两个表有关。连接顺序之所以会影响执行效率是因为连接的两个表有驱动表和连接表之分。驱动表是主表，是作为连接操作的外层循环的表。连接表是与驱动表进行连接的表，在连接中处于内存循环的位置。CBO对驱动表的每一行都对连接表进行一次全扫描，所以Oracle优化器分别计算每个驱动表和连接表的记录条数，并选择最小时间复杂度的顺序在执行计划中被执行。
1.3 表连接方式
表的连接方式主要有三种：嵌套连接、合并连接和哈希连接。嵌套连接是使用驱动表的每一行与连接表的所有满足条件的行进行连接，这种连接方式使用于任何连接条件，当连接条件中有高选择性索引或限制性搜索时效率比较高。合并连接是分别取出两个连接表中满足查询条件的所有记录再对其排序然后进行笛卡尔连接，这种连接方式主要用于不等价连接，且在没有索引时效率较嵌套连接高。哈希连接是通过将驱动表中的连接列建立一个哈希函数，并对于连接表中满足条件的每一行计算哈希值进行匹配，这种连接方式只用于等价连接，且没有索引时比嵌套循环和合并连接效率高，尤其是当数据量较大时。三种连接方式均有各自匹配的情况，但一般情况来说哈希连接效率是最高的，其次是嵌套连接，而合并连接因为要对两个表都进行排序操作会增加由于排序引起的资源消耗。优化器根据返回的每个表的结果集的大小计算最合适的连接方式并在执行计划中被安排执行。
1.4 查询条件的筛选率
Oracle根据系统统计信息计算出SQL语句中每个蹄选条件所能返回的记录条数并计算所占的比率，然后选择每次选择蹄选率最大的语句最先执行。与此同时将返回的结果集作为数据源传递给下一个查询条件，下一个查询条件对此数据源执行新的蹄选操作，直至所有的查询条件执行完毕返回最终的记录集。因此具有较高筛选率的查询条件在执行计划中总是被最先安排执行。
1.5 查询条件的数据类型
优化器根据查询条件中传入的值隐式转换成表中字段所能匹配的类型。当传入的值和该字段在数据库中创建时的类型不同时，Oracle尝试进行将其进行类型转换，如果转换不成功则报错。所以Oracle可以识别隐式和显式的类型转换，但每次的转换都会耗费一定的CPU和内存资源。
1.6 排序操作
排序操作需消耗系统大量内存和CPU，尤其是中间过程的排序，大部分都在内存中直接排序并存储中间结果，当某个排序过程不是必须的或是可以被其他易操作的执行步骤代替时，Oracle的CBO就会节省系统资源和时间的消耗使用其他方式代替。当Oracle在执行路径中发现结果集已经按照一定顺序排好或者无需排序时就会省略排序过程直接下一步的操作，所以当排序不是必须的选择或已经进行过排序的结果集时，Oracle优化器在选择执行计划时忽略排序的步骤。
2 SQL语句优化的建议
ADDM工具和STA工具配合的使用使特定SQL语句优化方案的使用更为方便，使用以上两种工具进行优化的主要方法如下：
（1）将系统参数statistics—level设置为typica或all。这个参数的设置决定了收集的统计信息的内容，typical或all级别收集的信息包含在ADDM所需的统计信息内容中。
（2）获取两次快照的ID。当数据库中设置为自动获取快照时，此步骤可以省略，转为直接使用数据库对快照信息的分析结果。
（3）用户授权。优化建议的获取需要advisor的权限，需确保当前执行的用户拥有此权限。
（4）创建优化任务。创建优化任务是使用存储过程完成的，将传送过来的SQL语句和用户名作为参数，调用DBMS-SQLTUNE包的CREATE-TUNING-TASK方法为该任务创建一个任务名，同时使用包屮的EXECUTE-TUNING-TASK方法执行该任务。执行完成后系统根据该SQL语句和统计信息生成优化建议，并把执行结果存放在任务日志中。
（5）根据任务名查看优化建议。DBMS-SQLTUNE包提供了一个方法REPORT_TUNING_TASK （任务名）方法可以查看与任务名匹配的结果，结果中包含三部分。第一部分是此次优化任务的基本信息，如任务名称、执行时问、范围和执行的语句等；第二部分是关于此次任务所找到的问题和优化建议；第三部分是原有的执行计划以及采用优化建议以后的执行计划的对比。当该任务需要再次被运行时，需要删除在系统中存储的该任务名相关的信息，DBMS_SQLTUNE包提供了一个DROP_TUNING-TASK （任务名）的方法除同名的任务，以便于下次执行。
参考文献
[1] 郭霞.基于Oracle数据库的SQL语句优化分析[J].电脑知识与技术，2011，7（21）：5063-5065.
[2] 杨宇红，石红春.数据库管理中SQL查询优化技术的应用[J].信息与电脑，2011，1：108.
[3] 王跃.Oracle数据库中SQL语句的优化与分析[J].计算机光盘软件与应用，2011，8：162-163.
Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表
问题：
1、查询“001”课程比“002”课程成绩高的所有学生的学号;
代码如下:
select a.S# from (select s#,score from SC where C#=‘001‘) a,(select s#,score
from SC where C#=‘002‘) b
where a.score>b.score and a.s#=b.s#;
2、查询平均成绩大于60分的同学的学号和平均成绩;
代码如下:
select S#,avg(score)
from sc
group by S# having avg(score) >60;
3、查询所有同学的学号、姓名、选课数、总成绩;
代码如下:
select Student.S#,Student.Sname,count(SC.C#),sum(score)
from Student left Outer join SC on Student.S#=SC.S#
group by Student.S#,Sname
4、查询姓“李”的老师的个数;
代码如下:
select count(distinct(Tname))
from Teacher
where Tname like ‘李%‘;
5、查询没学过“叶平”老师课的同学的学号、姓名;
代码如下:
select Student.S#,Student.Sname
from Student
where S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=‘叶平‘);
6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
代码如下:
select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#=‘001‘and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#=‘002‘);
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
代码如下:
select S#,Sname
from Student
where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=‘叶平‘ group by S# having count(SC.C#)=(select count(C#) from Course,Teacher where Teacher.T#=Course.T# and Tname=‘叶平‘));
8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
代码如下:
Select S#,Sname from (select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#=‘002‘) score2
from Student,SC where Student.S#=SC.S# and C#=‘001‘) S_2 where score2
9、查询所有课程成绩小于60分的同学的学号、姓名;
代码如下:
select S#,Sname
from Student
where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);
10、查询没有学全所有课的同学的学号、姓名;
代码如下:
select Student.S#,Student.Sname
from Student,SC
where Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course);
11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;
代码如下:
select S#,Sname from Student,SC where Student.S#=SC.S# and C# in select C# from SC where S#=‘1001‘;
12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名;
代码如下:
select distinct SC.S#,Sname
from Student,SC
where Student.S#=SC.S# and C# in (select C# from SC where S#=‘001‘);
13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;
代码如下:
update SC set score=(select avg(SC_2.score)
from SC SC_2
where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname=‘叶平‘);
14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名;
代码如下:
select S# from SC where C# in (select C# from SC where S#=‘1002‘)
group by S# having count(*)=(select count(*) from SC where S#=‘1002‘);
15、删除学习“叶平”老师课的SC表记录;
代码如下:
Delect SC
from course ,Teacher
where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname=‘叶平‘;
16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、2、
号课的平均成绩;
代码如下:
Insert SC select S#,‘002‘,(Select avg(score)
from SC where C#=‘002‘) from Student where S# not in (Select S# from SC where C#=‘002‘);
17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示： 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分
代码如下:
SELECT S# as 学生ID
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#=‘004‘) AS 数据库
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#=‘001‘) AS 企业管理
,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#=‘006‘) AS 英语
,COUNT(*) AS 有效课程数, AVG(t.score) AS平均成绩
FROM SC AS t
GROUP BY S#
ORDER BY avg(t.score)
18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
代码如下:
SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分
FROM SC L ,SC AS R
WHERE L.C# = R.C# and
L.score = (SELECT MAX(IL.score)
FROM SC AS IL,Student AS IM
WHERE L.C# = IL.C# and IM.S#=IL.S#
GROUP BY IL.C#)
AND
R.Score = (SELECT MIN(IR.score)
FROM SC AS IR
WHERE R.C# = IR.C#
GROUP BY IR.C#
);
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序
代码如下:
SELECT t.C# AS 课程号,max(course.Cname)AS 课程名,isnull(AVG(score),0) AS平均成绩
,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数
FROM SC T,Course
where t.C#=course.C#
GROUP BY t.C#
ORDER BY 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC
20、查询如下课程平均成绩和及格率的百分数(用“1行”显示): 企业管理(001)，马克思(002)，OO&UML (003)，数据库(004)
代码如下:
SELECT SUM(CASE WHEN C# =‘001‘ THEN score ELSE 0 END)/SUM(CASE C# WHEN ‘001‘ THEN 1 ELSE 0 END) AS 企业管理平均分
,100 * SUM(CASE WHEN C# = ‘001‘ AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = ‘001‘ THEN 1 ELSE 0 END) AS 企业管理及格百分数
,SUM(CASE WHEN C# = ‘002‘ THEN score ELSE 0 END)/SUM(CASE C# WHEN ‘002‘ THEN 1 ELSE 0 END) AS 马克思平均分
,100 * SUM(CASE WHEN C# = ‘002‘ AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = ‘002‘ THEN 1 ELSE 0 END) AS 马克思及格百分数
,SUM(CASE WHEN C# = ‘003‘ THEN score ELSE 0 END)/SUM(CASE C# WHEN ‘003‘ THEN 1 ELSE 0 END) AS UML平均分
,100 * SUM(CASE WHEN C# = ‘003‘ AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = ‘003‘ THEN 1 ELSE 0 END) AS UML及格百分数
,SUM(CASE WHEN C# = ‘004‘ THEN score ELSE 0 END)/SUM(CASE C# WHEN ‘004‘ THEN 1 ELSE 0 END) AS 数据库平均分
,100 * SUM(CASE WHEN C# = ‘004‘ AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = ‘004‘ THEN 1 ELSE 0 END) AS 数据库及格百分数
FROM SC
21、查询不同老师所教不同课程平均分从高到低显示
代码如下:
SELECT max(Z.T#) AS 教师ID,MAX(Z.Tname) AS 教师姓名,C.C# AS 课程ID,MAX(C.Cname) AS 课程名称,AVG(Score) AS平均成绩
FROM SC AS T,Course AS C ,Teacher AS Z
where T.C#=C.C# and C.T#=Z.T#
GROUP BY C.C#
ORDER BY AVG(Score) DESC
22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理(001)，马克思(002)，UML (003)，数据库(004)
[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩
代码如下:
SELECT DISTINCT top 3
SC.S# As 学生学号,
Student.Sname AS 学生姓名 ,
T1.score AS 企业管理,
T2.score AS 马克思,
T3.score AS UML,
T4.score AS 数据库,
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) as 总分
FROM Student,SC LEFT JOIN SC AS T1
ON SC.S# = T1.S# AND T1.C# = ‘001‘
LEFT JOIN SC AS T2
ON SC.S# = T2.S# AND T2.C# = ‘002‘
LEFT JOIN SC AS T3
ON SC.S# = T3.S# AND T3.C# = ‘003‘
LEFT JOIN SC AS T4
ON SC.S# = T4.S# AND T4.C# = ‘004‘
WHERE student.S#=SC.S# and
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)
NOT IN
(SELECT
DISTINCT
TOP 15 WITH TIES
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)
FROM sc
LEFT JOIN sc AS T1
ON sc.S# = T1.S# AND T1.C# = ‘k1‘
LEFT JOIN sc AS T2
ON sc.S# = T2.S# AND T2.C# = ‘k2‘
LEFT JOIN sc AS T3
ON sc.S# = T3.S# AND T3.C# = ‘k3‘
LEFT JOIN sc AS T4
ON sc.S# = T4.S# AND T4.C# = ‘k4‘
ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC);
23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
代码如下:
SELECT SC.C# as 课程ID, Cname as 课程名称
,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 - 85]
,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 - 70]
,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 - 60]
,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -]
FROM SC,Course
where SC.C#=Course.C#
GROUP BY SC.C#,Cname;
24、查询学生平均成绩及其名次
代码如下:
SELECT 1+(SELECT COUNT( distinct平均成绩)
FROM (SELECT S#,AVG(score) AS平均成绩
FROM SC
GROUP BY S#
) AS T1
WHERE平均成绩 > T2.平均成绩) as 名次,
S# as 学生学号,平均成绩
FROM (SELECT S#,AVG(score)平均成绩
FROM SC
GROUP BY S#
) AS T2
ORDER BY平均成绩 desc;
25、查询各科成绩前三名的记录:(不考虑成绩并列情况)
代码如下:
SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
FROM SC t1
WHERE score IN (SELECT TOP 3 score
FROM SC
WHERE t1.C#= C#
ORDER BY score DESC
)
ORDER BY t1.C#;
26、查询每门课程被选修的学生数
代码如下:
select c#,count(S#) from sc group by C#;
27、查询出只选修了一门课程的全部学生的学号和姓名
代码如下:
select SC.S#,Student.Sname,count(C#) AS 选课数
from SC ,Student
where SC.S#=Student.S# group by SC.S# ,Student.Sname having count(C#)=1;
28、查询男生、女生人数
代码如下:
Select count(Ssex) as 男生人数 from Student group by Ssex having Ssex=‘男‘;
Select count(Ssex) as 女生人数 from Student group by Ssex having Ssex=‘女‘;
29、查询姓“张”的学生名单
代码如下:
SELECT Sname FROM Student WHERE Sname like ‘张%‘;
30、查询同名同性学生名单，并统计同名人数
代码如下:
select Sname,count(*) from Student group by Sname having count(*)>1;
31、1981年出生的学生名单(注：Student表中Sage列的类型是datetime)
代码如下:
select Sname, CONVERT(char (11),DATEPART(year,Sage)) as age
from student
where CONVERT(char(11),DATEPART(year,Sage))=‘1981‘;
32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
代码如下:
Select C#,Avg(score) from SC group by C# order by Avg(score),C# DESC ;
33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
代码如下:
select Sname,SC.S# ,avg(score)
from Student,SC
where Student.S#=SC.S# group by SC.S#,Sname having avg(score)>85;
34、查询课程名称为“数据库”，且分数低于60的学生姓名和分数
代码如下:
Select Sname,isnull(score,0)
from Student,SC,Course
where SC.S#=Student.S# and SC.C#=Course.C# and Course.Cname=‘数据库‘and score <60;
35、查询所有学生的选课情况;
代码如下:
SELECT SC.S#,SC.C#,Sname,Cname
FROM SC,Student,Course
where SC.S#=Student.S# and SC.C#=Course.C# ;
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;
代码如下:
SELECT distinct student.S#,student.Sname,SC.C#,SC.score
FROM student,Sc
WHERE SC.score>=70 AND SC.S#=student.S#;
37、查询不及格的课程，并按课程号从大到小排列
代码如下:
select c# from sc where scor e <60 order by C# ;
38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名;
代码如下:
select SC.S#,Student.Sname from SC,Student where SC.S#=Student.S# and Score>80 and C#=‘003‘;
39、求选了课程的学生人数
代码如下:
select count(*) from sc;
40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩
代码如下:
select Student.Sname,score
from Student,SC,Course C,Teacher
where Student.S#=SC.S# and SC.C#=C.C# and C.T#=Teacher.T# and Teacher.Tname=‘叶平‘ and SC.score=(select max(score)from SC where C#=C.C# );
41、查询各个课程及相应的选修人数
代码如下:
select count(*) from sc group by C#;
42、查询不同课程成绩相同的学生的学号、课程号、学生成绩
代码如下:
select distinct A.S#,B.score from SC A ,SC B where A.Score=B.Score and A.C# B.C# ;
43、查询每门功成绩最好的前两名
代码如下:
SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
FROM SC t1
WHERE score IN (SELECT TOP 2 score
FROM SC
WHERE t1.C#= C#
ORDER BY score DESC
)
ORDER BY t1.C#;
44、统计每门课程的学生选修人数(超过10人的课程才统计)，
50条SQL查询技巧、查询语句示例
，
要求输出课程号和选修人数，查询结果按人数降序排列，查询结果按人数降序排列，若人数相同，按课程号升序排列
代码如下:
select C# as 课程号,count(*) as 人数
from sc
group by C#
order by count(*) desc,c#
45、检索至少选修两门课程的学生学号
代码如下:
select S#
from sc
group by s#
having count(*) > = 2
46、查询全部学生都选修的课程的课程号和课程名
代码如下:
select C#,Cname
from Course
where C# in (select c# from sc group by c#)
47、查询没学过“叶平”老师讲授的任一门课程的学生姓名
代码如下:
select Sname from Student where S# not in (select S# from Course,Teacher,SC where Course.T#=Teacher.T# and SC.C#=course.C# and Tname=‘叶平‘);
48、查询两门以上不及格课程的同学的学号及其平均成绩
代码如下:
select S#,avg(isnull(score,0)) from SC where S# in (select S# from SC where score <60 group by S# having count(*)>2)group by S#;
49、检索“004”课程分数小于60，按分数降序排列的同学学号
代码如下:
select S# from SC where C#=‘004‘and score <60 order by score desc;
50、删除“002”同学的“001”课程的成绩
代码如下:
