团队项目----数据库SQL语句学习总结与实践

　　在本次团队项目编程过程中，我主要负责数据库的设计与建立；在知识不够，能力不足，无数据库设计经验的情况下，对SQL语句进行了较为深入的学习，今天将成果整理之后，写了这篇博客，欢迎大家指出我的不足。

　　首先，介绍一下Windows下使用DOS命令进入MySQL数据库

　　　　1.桌面左下角windows图标--搜索框内输入cmd，结果如图所示，点击cmd.exe，或者使用快捷键Windows键（在键盘上有个Windows标志的按键）+R输入cmd后回车。

　　　　2.启动mysql数据库：在出来的DOS命令窗口中输入 net start mysql，或者使用快捷键Windows键（在键盘上有个Windows标志的按键）+ R直接输入net start mysql后回车。（另附：关闭的命令为net stop mysql）。

　　　　3.在DOS命令窗口输入 mysql -hlocalhost -uroot -p回车 进入mysql数据库，其中-h表示服务器名，localhost表示本地；-u为数据库用户名，root是mysql默认用户名；-p为密码，如果设置了密码，可直接在-p后链接输入，如：-p123456，用户没有设置密码，显示Enter password时，直接回车即可。注意，如果你的mysql没有安装在C盘下，你需要先使用DOS命令进入mysql的安装目录下的bin目录中。以我的电脑为例，方法如下：输入D:进入D盘，在输入cd D:ToolsMySQL5.5.25in进入到mysql的bin目录下才可以输入 mysql -hlocalhost -uroot -p。

　　　　4.输入show databases；显示你有的数据库（mysql数据库中的命令必须以分号结尾“；”）。

　　　　5.输入use "数据库名";在显示的数据库列表中选择你要使用的数据库。

　　　　6.进入数据库后，输入show tables；显示该数据库中的所有表。

　　　　7.使用SQL语句进行对表的操作。

　　　　8.如果要退出mysql数据库，输入exit;回车。

SQl语句的实践

如下，总共有四张表，现在对这些表进行操作，加深SQL语句的理解：

表1：班级表，存储学校的所有班级

表名：class

属性名	含义	类型	允许NULL	类别	域及约束
classNo	班级号	char(6)	N	PK	主键约束
className	班级名	varchar(30)	N
institute	所属学院	varchar(30)	N
grade	年级	smallint	N
classNum	班级人数	int	Y	派生	该属性是冗余属性，值是由表student自动计算出来的。

 

表2：课程表，存储学校的所有课程

表名：course

属性名	含义	类型	允许NULL	类别	域及约束
courseNo	课程号	char(3)	N	PK	主键约束
courseName	课程名	varchar(30)	N		唯一约束：课程名不能重复。
creditHour	学分	numeric(1,0)	N
courseHour	课时数	tinyint	N
priorCourse	先修课程	char(3)	Y	FK	外键约束，参照本表的主键courseNo

 

表3：学生表，存储学校的所有学生

表名：student

属性名	含义	类型	允许NULL	类别	域及约束
studentNo	学号	char(7)	N	PK	只能是全数字组成的7位字符，主键约束。
studentName	姓名	varchar(20)	N
sex	性别	char(2)	Y		取值范围：男、女或者NULL
birthday	出生日期	datetime	Y
native	籍贯	varchar(20)	Y
nation	民族	varchar(30)	Y		缺省为汉族
classNo	所属班级	char(6)	Y	FK	外键约束，参照class.classNo

 

 

表4：成绩表，表示有哪些学生，参加了哪些课程的考试，成绩如何。

表名：score

属性名	含义	类型	允许NULL	类别	域及约束
studentNo	学号	char(7)	N	PK,FK	外键约束，参照student. studentNo
courseNo	课程号	char(3)	N	PK,FK	外键约束，参照course. courseNo
score	成绩	numeric(5,1)	N		默认为0，必须是0-100之间的数

注意，表score的主键是studentNo和courseNo两个属性的联合主键，表示学生与课程之间是多对多的关系，即一个学生可以选修多门课程，一门课程可以由多个学生选择。

连接查询:
基于score,student,course三张表进行，首先要注意可能有些学号没有在score表中（即有些同学没有参考过任何一门课程的考试），还可能有些课程号没有在socre表中（即有些课程没有任何一个同学选修）。
  自然连接：根据score,student,course三张表显示学生的考试成绩情况，显示学号，姓名，课程号，课程名，成绩，查询结果按学号升序排列。只查询在score表中出现过的学号和课程号（参加了考试的学生和课程）的成绩：
select a.studentNo,b.studentName,a.courseNo,c.courseName,a.score
from Score a,Student b,Course c
where a.studentNo=b.studentNo and a.courseNo=c.courseNo
order by a.studentNo
  外连接：根据score,student,course显示学生的考试成绩情况，显示学号，姓名，课程号，课程名，成绩，查询结果按学号升序排列。除了查询参加了考试的学生外，还要查询出未参加过考试的学生，未被学生选择的课程不显示：
select b.studentNo,b.studentName,c.courseNo,c.courseName,a.score
from Score a join Course c on a.courseNo=c.courseNo
right join Student b on a.studentNo=b.studentNo
order by b.studentNo
或者写成
select b.studentNo,b.studentName,c.courseNo,c.courseName,a.score
from Student b left join Score a on a.studentNo=b.studentNo
left join Course c on a.courseNo=c.courseNo
order by b.studentNo
  外连接：根据score,student,course显示学生的考试成绩情况，显示学号，姓名，课程号，课程名，成绩，查询结果按学号升序排列。除了查询出被学生选择过的课程外，还要查询出未被学生选过的课程，未参加过考试的学生不显示：
select b.studentNo,b.studentName,c.courseNo,c.courseName,a.score
from Score a join Student b on a.studentNo=b.studentNo
right join Course c on a.courseNo=c.courseNo
order by b.studentNo
或者写成
select b.studentNo,b.studentName,c.courseNo,c.courseName,a.score
from Student b join Score a on a.studentNo=b.studentNo
right join Course c on a.courseNo=c.courseNo
order by b.studentNo
  全连接：根据score,student,course显示学生的考试成绩情况，显示学号，姓名，课程号，课程名，成绩，查询结果按学号升序排列。除了查询参加了考试的学生外，还要查询出未参加过考试的学生；除了查询出被学生选择过的课程外，还要查询出未被学生选过的课程。
select b.studentNo,b.studentName,c.courseNo,c.courseName,a.score
from Score a right join Student b on a.studentNo=b.studentNo
full outer join Course c on a.courseNo=c.courseNo
order by b.studentNo
或者写成
select b.studentNo,b.studentName,c.courseNo,c.courseName,a.score
from Student b left join Score a on a.studentNo=b.studentNo
full outer join Course c on a.courseNo=c.courseNo
order by b.studentNo
  自连接：根据course表，查询出所有课程的信息：课程号，课程名称，学分，课时数，先修课程名称：
select a.courseNo,a.courseName,a.creditHour,a.courseHour,b.courseName as 先修课程
from Course as a left join Course as b
on b.courseNo=a.priorCourse

聚合查询:
  查询学号为0800005同学所选修改课程的总学分
select SUM(creditHour) 总学分
from Score a , Course b
where studentNo='0800005' and a.courseNo=b.courseNo
  查询每个同学的选课门数、平均分，最高分
select StudentNo,COUNT(*) 门数,AVG(Score) 平均分,MAX(score) 最高分
from Score
group by studentNo
嵌套子查询
   在score表中查询分数大于平均分的学生
select * from score where score>=(select avg(score) from score)
  查询每个班级的平均分和最高分，显示的属性为：班级号，班级名称，平均分，最高分，最后的结果按平均分倒序显示。
select c.classNo,d.className,c.平均分,c.最高分
from (select a.classNo,avg(score) 平均分,max(score) 最高分 from Student a,Score b
where a.studentNo=b.studentNo group by a.classNo) c , Class d
where c.classNo=d.classNo
order by 平均分 desc
  视图设计
  创建一个包含学号，姓名和年龄的视图StudentAgeView
create view StudentAgeView as
select StudentNo,studentName,YEAR(getdate())-YEAR(birthday) age from Student
go
select * from StudentAgeView

在三表或者多表外连接或者全连接的情况下，连接的顺序能不能交换？为什么？
比如：
select b.studentNo,b.studentName,c.courseNo,c.courseName,a.score
from Score a
right join Student b on a.studentNo=b.studentNo
full outer join Course c on a.courseNo=c.courseNo
order by b.studentNo
能不能改成：
select b.studentNo,b.studentName,c.courseNo,c.courseName,a.score
from Score a
full outer join Course c on a.courseNo=c.courseNo
right join Student b on a.studentNo=b.studentNo
order by b.studentNo

不能，但是可以改成
select b.studentNo,b.studentName,c.courseNo,c.courseName,a.score
from Score a
right join Course c on a.courseNo=c.courseNo
full outer join Student b on a.studentNo=b.studentNo
order by b.studentNo