在本次团队项目编程过程中,我主要负责数据库的设计与建立;在知识不够,能力不足,无数据库设计经验的情况下,对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:\Tools\MySQL5.5.25\bin进入到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