数据库实验—5

初始化操作（原来数据有缺的可以采用）

--Edit by David @ HeBei University 2018

DROP TABLE IF EXISTS SC
DROP TABLE IF EXISTS Student
DROP TABLE IF EXISTS Course

CREATE TABLE Student          
 (	
 Sno CHAR(9) PRIMARY KEY,        /* 列级完整性约束条件,Sno是主码*/                  
 Sname CHAR(20) UNIQUE,             /* Sname取唯一值*/
 Ssex CHAR(2),
 Sage SMALLINT,
 Sdept CHAR(20)
 ); 

CREATE TABLE  Course
 (	
 Cno CHAR(4) PRIMARY KEY,
 Cname CHAR(40),            
 Cpno CHAR(4),               	                      
 Ccredit SMALLINT,
 FOREIGN KEY (Cpno) REFERENCES  Course(Cno) 
 ); 

CREATE TABLE  SC
 (
 Sno CHAR(9), 
 Cno CHAR(4),  
 Grade SMALLINT,
 PRIMARY KEY (Sno,Cno),                     /* 主码由两个属性构成，必须作为表级完整性进行定义*/
 FOREIGN KEY (Sno) REFERENCES Student(Sno),  /* 表级完整性约束条件，Sno是外码，被参照表是Student */
 FOREIGN KEY (Cno)REFERENCES Course(Cno)     /* 表级完整性约束条件， Cno是外码，被参照表是Course*/
 ); 


INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215121','李勇','男','CS',20);
INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215122','刘晨','女','CS',19);
INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215123','王敏','女','MA',18);
INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215125','张立','男','IS',19);
INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215128','陈冬','男','IS',20);

SELECT * FROM Student

INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)	VALUES ('1','数据库',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)	VALUES ('2','数学',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)	VALUES ('3','信息系统',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)	VALUES ('4','操作系统',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)	VALUES ('5','数据结构',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)	VALUES ('6','数据处理',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)	VALUES ('7','Pascal语言',NULL,4);

UPDATE Course SET Cpno = '5' WHERE Cno = '1' 
UPDATE Course SET Cpno = '1' WHERE Cno = '3' 
UPDATE Course SET Cpno = '6' WHERE Cno = '4' 
UPDATE Course SET Cpno = '7' WHERE Cno = '5' 
UPDATE Course SET Cpno = '6' WHERE Cno = '7' 

SELECT * FROM Course

INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215121 ','1',92);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215121 ','2',85);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215121 ','3',88);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215122 ','2',90);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215122 ','3',80);

SELECT * FROM SC

单表查询

选择表中的若干列

【例3.19】查询全体学生的姓名及其出身年月日

select Sname,2014-Sage
 from Student;

           图一


【例3.20】查询全体学生的姓名、出生年月和所在院系，要求用小写字母表示系名，要求用小写字母来写系名，

select Sname,'Year of birth',2014-Sage,lower(Sdept)
from Student;

• 除了lower，我们还可以选择用upper来转成大写，还有很多其他的函数具体请参考SQLServer常用的字符串函数

• 用户还可以通过指定别名来改变查询结果也就是起别名的意思，对于上面3.20的例题我们可以使用如下操作

select Sname NAME,'year of birth' birth ,2014-Sage Birthday,lower(Sdept) Department
from Student; 

• 就是不起别名的时候我们一定不要加空格，起别名的方法就是A B,C D也就是B是A的别名，同理D是C的别名。

选择表中的若干列

【例3.21】查询选修了课程的学生学号

select sno
from SC;

• 在这里我们可以看到所有符合条件的Sno列，但是我们也能看到其中有很多重复的列，我们可以用distinct来消除。操作如下

【例3.21】查询选修了课程的学生学号（删除重复列）

select distinct sno
from SC;

• 如果不指定distinct的话其实我们是默认all的，其实也就相当于 select all Sno的意思
  
  
  
  
  
  

查询条件	谓词
比较	=,>,<,>=,<=,!=,!>,!<
确定范围	between and,not between and
确定集合	in，not int
字符匹配	like，not like
空值	is null，is not null
多重条件	and or not

【例3.22】查询计算机科学系全体学生的名单

select Sname
from Student
where Sdept='CS';

【例3.23】查询所有年龄在20岁以下的学生姓名及其年龄

select Sname
from Student
where Sage<20;

【例3.24】查询所有考试成绩不合格的学生的学号

select distinct Sno
from SC
where Grade<60;

【例3.25】查询年龄在20~23岁（包括20-23）之间的学生的姓名、系别和年龄。

select Sname,Sdept,Sage
from Student
where Sage between 20 and 23;

【例3.26】查询年龄不在20~23岁（包括20-23）之间的学生的姓名、系别和年龄。

select Sname,Sdept,Sage
from Student
where Sage>=20 and Sage<=23;
--等价于下面语句
select Sname,Sdept,Sage
from Student
where Sage not between 20 and 23;

在这里我们再试试如果前后数字的位置相反会发生什么

select Sname,Sdept,Sage
from Student
where Sage not between 23 and 20;

select Sname,Sdept,Sage
from Student
where Sage between 23 and 20;

• 由此我们可以看出between语句对于位置是有要求的也就是between a and b 必须要求a>b，否则不会查询出正确的结果。
  
  
  【例3.27】查询计算机科学系、数学系、信息系的学生姓名和性别。

select Sname,Ssex
from Student
where Sdept in('CS','MA','IS')--in后面括号的意思其实是或的关系

【例3.27】查询不是计算机科学系、数学系、信息系的学生姓名和性别。

select Sname,Ssex
from Student
where Sdept not in('CS','MA','IS')--in后面括号的意思其实是或的关系

字符匹配

--谓词like一般用于字符串匹配，一般格式如下
【not】 like《字符串》【escape‘转换字符’】
--其含义是查找指定的属性列值与《匹配串》相同的元组，其中匹配串可以是一个完整的字符串，也可以含有通配符

通配符：%和_
%；代表任意长度（可以为0）的字符串。例如a%b代表以a开头，b结尾的字符串例如ab、acb等
_：代表任意单个字符，例如a_b是以a为开头，以b为结尾的长度为3的字符

【例3.29】查询学号为2012151121的学生的所有情况。

select *
from Student
where Sno like '201215121';
                                   --等价于
select *
from Student
where Sno = '201215121';

【例3.30】查询所有姓刘的同学的姓名、学号和性别

select Sname,Sno,Ssex
from Student
where Sname like '刘%';

• 在下个例题前我们可以先向student表中补充一些数据（建议ab，abc，abcd类型的）这样我们可以进行多种变换查询，我的插入如下欧阳、欧阳娜、欧阳娜娜
  【例3.31】查询姓“欧阳”且全面为三个汉字的同学姓名

select Sname,Sno,Ssex
from Student
where Sname like '欧阳_';

这里我们看到搜索欧阳_，但是出现了长度为2的姓名，查了其他的博客发现了一点规律，也就是当_放在最后一位时其实是可以作空值查询的，也就是可以为NULL，但是当不在最后一位时是一定不能为空的。
例【3.32】查询名字中第二个字为”阳“的学生姓名

select Sname,Sno
from Student
where Sname like '_阳%'

【例3.33】查询所有不姓“刘”的同学的姓名、学号及其性别

select Sname,Sno,Ssex
from Student
where Sname not like '刘%'

【例3.34】查询DB_Design课程的课程号和学分（执行前我们可以先手动插入）

select Cno,Ccredit
from Course
where Cname like'DB\_Design' escape'\';--\表示的是转义字符,_不再代表通配符

• escape“\”表示其是转换字符，这样\后面的字符不会再具有别的含义。
  
  

【例3.35】查询以“DB”开头，且倒数为第三个字符为i的课程的详细记录

select *
from Course
where Cname like'DB\_%i__' escape'\';

【例3.36】某些学生选修课程后没有参加考试，所以有选课记录，但是没有考试成绩，查询缺少成绩的学生的学号和课程号。

select Sno,Cno
from SC 
where Grade is  null;

【例3.36】查询所有有成绩的学生的学号和课程号。

select Sno,Cno
from SC 
where Grade is not null;、

--我们再来尝试一下错误用法

select Sno,Cno
from SC 
where Grade !=null;

【例3.38】查询所有计算机科学系年龄在20以下的学生的姓名

select Sname
from Student
where Sdept='CS' and Sage<20;

order by子句

用户可以用order by函数来对查询结果按照一个或者多个属性列来进行升降列排序。

【例3.39】查询选修了三号课程学生的学号及其成绩，查询结果按照分数的降序排列

select Sno,Grade
from SC
where Cno='3'
order by grade DESC;

【例3.40】查询全体学生情况，查询结果按照所在系的系号升序排列，同一系中的学生按年龄降序排列。

select *
from Student
order by Sdept,Sage DESC

-对于空值，排序是按照系统来实现的，2017的sql sever是空值在前

聚集函数

count 统计元组个数
sum 计算一列值的和（必须是数值型）
avg  计算一列的平均值（必须是数值型）
max 求一列的最大值
min  求一列的最小值
//如果指定 dinstinct可以取消指定列中的重复值，如果不指定的话默认all短语，不会取消重复值

【例3.41】查询学生总人数

select count(*) allstudent //allstudent是最上面的列名
from Student;

【例3.41】查询学生总人数

select count(distinct Sno) '选修了课程的学生人数'//distinct取消重复学生
from SC;

• 学生每选一个课程，sc都会有一条相应的记录
• 我们可以通过distinct来去掉重复计算的学生人数

【例3.43】计算选修1号课程的学生的平均成绩

select avg(Grade) '一号课程的学生平均成绩'
from SC
where Cno='1';

【例3.44】查询选修1号课程的学生的最高分数

select max(Grade) '一号课程的学生最高成绩'
from SC
where Cno='1';

【例3.45】查询学生 201215012 选修课程的总学分数

select sum(Ccredit) '查询学生201215012选修课程的总学分数'
from SC,Course
where Sno='201215121' and SC.Cno=Course.Cno;

注：当聚集函数遇到空值时，除count（*）外，都跳过空值只加载非空值。count( *)是对元组的统计，所以某个元组的一个或部分的空值不影响count的统计结果。>

where子句中是不能用聚集函数作为表达式条件的，聚集函数只能用于select、 group by、having三个子句中。

group by 函数

将查询结果按某一列或者多列的值分组，值相等的为一组。

分组后聚集函数将作用于每一个组，即每一组都有一个函数组。

【例3.46】 求每个课程号及其相应的选课人数

select Cno,count( Sno) '相应的选课人选'
from SC
group by Cno;

该语句对查询结果按Cno的值分组，所有具有相同值的元组为一组，然后对每一组作用聚集函数count来计算，以求得这一组的学生人数。

【例3.47】 查询选了三门及其以上课程的学生的学号

select Sno 
from SC
group by Sno
having count(*)>3;

----------------------------------------------------

select Sno 
from SC
group by Sno
having count(*)>=3;

这里先用group by语句按照Sno分组，再用聚集函数count对每一组计数；having短语给出了选择组的条件，只有满足条件的才会被选出来。

where语句和having的区别在作用对象的不同。where作用于基本表或者视图，从中选择满足条件的元组。having短语作用与组，从中选择满足条件的组

【例3.48】 查询平均成绩小于90的学生的学号和平均成绩

select Sno,avg(Grade)
from SC
where avg(Grade)>=90
group by Sno;
(
 An aggregate may not appear in the WHERE clause unless it is in a
   subquery contained in a HAVING clause or a select list, and the
   column being aggregated is an outer reference.
//也就是where不能用在组中。
)

--正确语句


select Sno,avg(Grade) '相应的平均成绩'
from SC
group by Sno
having avg(Grade)<90;

---

这次实验内容有一些多，以后还需要多回顾，如果有时间的话多做练习。