数据库应用第七章：查询和视图

一.为什么说视图是虚表？视图的数据存在什么地方？

1.视图是虚表，它在存储时只存储视图的定义，而没有存储对应的数据。
2.视图通过定义从基表中搜集数据，并展现给用户。
3.数据存储在对应的数据表中。

二.说明视图的优缺点。

 优点：（1）视图能简化用户的操作； 
      （2）视图机制可以使用户以不同的方式查询同一数据； 
      （3）视图对数据库重构提供了一定程度的逻辑独立性； 
      （4）视图可以对机密的数据提供安全保护。 

缺点：（1）降低性能，通过视图查询起来需要花费时间； 
     （2）视图是只读的，更新时需要更新原表，对用户来说很麻烦。

三.通过视图修改数据要注意哪些限制？

 对视图进行的修改操作有以下限制：
（1）若视图的字段来自表达式或常量，则不允许对该视图执行INSERT和UPDATE操作，
但允许执行DELETE操作。
（2）若视图的字段来自集合函数，则此视图不允许修改操作。
（3）若视图定义中含有GROUP BY 子句，则此视图不允许修改操作。
（4）若视图定义中含有DISTINCT短语，则此视图不允许修改操作。
（5）一个不允许修改操作视图上定义的视图，也不允许修改操作。

四.使用SQL语句创建学生基本信息表student（s _ no、s _ name、s _ sex、birthday、polity） 和学生成绩表sco时（s_no、c_no、score）。针对这两个表，利用SELECT语句实现下列查询。
1）所有学生的基本信息，并按学号排序。
2）所有女生的信息和女生的人数。
3）所有男生的姓名、出生日期和年龄。
4）有学生的姓名、出生日期、年龄、选修课程和成绩。
5）某个指定姓名学生的成绩。
6）不及格学生的姓名。
7）按女生进行分组查询。

1）SELECT * FROM student ORDER BY s_no

2）SELECT * FROM student WHERE s_sex=”女”  
   SELECT COUNT（*）as 女生人数 FROM student WHERE s_sex=”女” 

3）SELECT s_name，birtherday, YEAR（GETDATE（））-YEAR（birthday)AS 年龄 
    FROM student WHERE s_sex=”男” 

4）SELECT a.s_name, 
          a.birthday， 
          YEAR（GETDATE（））-YEAR（birthday）AS 年 龄， 
          b.c_no， 
          b.score  
   FROM student a  
   INNER JOIN sco b ON a.s_no=b.s_no 

5）SELECT a.s_name, b.score  
FROM student a  
INNER JOIN sco b ON a.s_no=b.s_no 
WHERE  a.s_name=”张三” 

6）SELECT a.s_name  
    FROM student a INNER JOIN sco b ON a.s_no=b.s_no WHERE b.score<60 

7）SELECT S_SEX，COUNT（S_SEX）AS 人数FROM student GROUP BY S_SEX

五.

create database 运动商品销售数据库
on primary
(  name=Pri_商品销售，
   filename='D:\DataFile\Pri_商品销售.mdf'，
   size=2MB，
  maxsize=10MB，
  filegrowth=1MB
)
log on
(  name=LF_商品销售,
   filename='d:\LogFile\LF_商品销售.ldf'，
   size=2MB，
   maxsize=10MB，
   filegrowth=1MB
)

create table 商品信息表
（商品标识    char（3）not null,
 商品名称    varchar（20），
 产地        varchar（50），
 进货价      decimal（18，2））

create table 商品销售记录表
（商品标识    char（3）not null，
 销售日期    datetime，
销售价格    decimal（18，2））

六.在students数据库中写出完成下面任务的SQL语句。students数据库模式如下：

create database students；
go
use students；
create table student（
    Sno char（9）primary key，
        Sname char（10）not null unique，
    Ssex char（2）default '男'，
    Sage numeric（2），
    Sdept char（2））；

create table course（
    Cno char（3）primary key，
    Cname   char（10）unique，
    Cpno    char（1），
    Ccredit numeric（1））； 

create table sc（
    sno char（9）not null，
    cno char（3）not null，
    grade   numeric（3）check（grade>=0 and grade<=100），
    primary key（sno，cno））

（1）将’数学’课不及格的成绩全改为60分。

（2）将选修’数学’课的女同学选课元组全部删除。

（3）查询选修了2门以上专业课的同学的姓名。

（4）求女同学的人数和平均年龄及平均成绩。

（1）将'数学'课不及格的成绩全改为60分。
    UPDATE SC SET GRADE=60 WHERE GRADE < 60 AND CNO IN (
        SELECT CNO FROM COURSE WHERE CNAME='数学') 

（2）将选修'数学'课的女同学选课元组全部删除。
    DELETE FROM SC WHERE CNO IN（
        SELECT CNO FROM COURSE WHERE CNAME='数学'）AND SNO IN（
            SELECT SNO FROM STUDENT WHERE SSEX='女'）

（3）查询选修了2门以上专业课的同学的姓名。
select s_name from students where s_no in 
（select  s_no from enrollment，courses
     where enrollment.c_no=courses.c_no and c_information='专业课' 
      group by s_no having count（enrollment.c_no）>2）

（4）求女同学的人数和平均年龄及平均成绩。
SELECT 
    COUNT（DISTINCT SNAME）， 
     AVG（SAGE），
     AVG（GRADE） 
FROM STUDENT，SC 
WHERE STUDENT.SNO=SC.SNO AND SSEX='女' GROUP BY SSEX