目录
3、查询选修课程1 且成绩在80-90 之间的学生学号和成绩,并将成绩乘以系数0.75
4、查询计算机专业的姓“小”的学生的信息(一顿胡编乱造,哈哈哈)
5、查询“1”课程的成绩高于小红的学生学号和成绩(用到了连接查询)
1、将student, course 和student_course表中学生的学号,姓名,课程号,课程名,成绩定义为视图V_SC_S
1、将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G
2、当然还有我们之前就已经使用的查询操作一样适用,删除操作也适用。
1. 对 student 表上入校时间在 2006 年以后的学生修改班级号,其他的学生修改班级代码。(if..else)
2. 查询 student 表,只要有年龄小于 20 岁的学生,就将年龄最小的那个学生删掉,如此循环下去,直到所有
的学生的年龄都不小于 20 岁,或是学生的总人数小于 20 个了就推出循环。(while, break,continue,if…else,
3. 显示所有专业号为‘001’,并且在服务器显示之前,暂停 1 分钟。(waitfor)
1、声明一个游标,用于查询所有计算机专业的学生的学号、籍贯、和年龄
前言:
前面我们已经在数据库里面创建好了数据库,也创建了几张表,创建了索引,进行了一系列的操作。这里我们从插入数据开始,一步步的进入查询、视图、游标的世界,干货满满哦,不信你仔细看看。
一、插入
为了进行后面的查找工作,我们应该存有一定的数据,所以这里我们就来演示如何插入数据的吧。
(小插曲):
那么多数据的话,这里给你一个Access mdb表,你可以直接导入sql中,这样就拥有很多可以直接使用的数据了。
具体操作如下:https://jingyan.baidu.com/article/0f5fb09935fd0a6d8334ea35.html。
如果你的服务器名称没有显示出来,你可以自己手写上去,只需要验证数据库是否可以显示就知道服务器名称有没有写对了。这个xf.mdb里面是这样的:
获取的渠道直接从我的下载列表里面免费的下载吧,这里我会在原来创建的表上个创建5条数据(太懒了,哈哈哈)。如果你想要数据量大一些,就可以导入提供的数据。下载链接:https://download.csdn.net/download/qq_43919400/12464485
那么我们看看如何向数据表里面添加数据吧!
原来的表如下:
语法如下:
insert into table_name(列名1,列名2) values(值1, 值2);
1、向course表中添加数据
老规矩,先看看表的结构,才可以对症添加数据:
那么我们插入的语句就有:
use EDUC
insert into course values('1','101','数据库','11','11','40','1','3');
insert into course values('2','102','密码学','12','12','40','1','3');
insert into course values('3','103','OS','10','13','43','2','3');
insert into course values('4','104','用户界面','14','14','40','1','3');
insert into course values('5','105','高等数学','15','15','40','1','3');
select * from course;
当然,如果你不小心插入错误想删除,直接delete from 表名 where 列名=?就可以。比如我想删除课程号为1的那一条数据,即有:delete from course where cno=1;
插入之后这里用到了select看看成功没有:
温馨小贴士:
一定要注意单引号,插入数据是和MySql的不一样哦,不要把SQLserver的习惯用到MySQL中,哈哈哈......
2、向student表中添加数据
一样的哦:
use EDUC
insert into student values('2018','小红','男','中国武汉','2000-01-03','计科','1','1804','2015-03-03','武汉','110123');
insert into student values('2019','小兰','男','中国武汉','2000-01-04','计科','1','1804','2015-03-03','武汉','110124');
insert into student values('2020','小紫','女','中国武汉','2000-01-06','计科','1','1804','2015-03-03','武汉','110125');
insert into student values('2021','小绿','女','中国武汉','2000-01-07','计科','1','1804','2015-03-03','武汉','110126');
insert into student values('2022','小黑','男','中国武汉','2000-01-08','计科','1','1804','2015-03-03','武汉','110127');
3、向student_course表中添加数据
一样的哦:
use EDUC
insert into student_course values('2018','1','90');
insert into student_course values('2019','2','92');
insert into student_course values('2020','1','93');
insert into student_course values('2021','3','94');
insert into student_course values('2022','4','100');
select * from student_course;
4、向teacher表中添加数据
一样的哦:
use EDUC
insert into teacher values('1','大壮','男','1978-03-06','计科','1','中国武汉','1','120110','dazhuang.com');
insert into teacher values('2','大白','女','1978-03-07','计科','1','中国武汉','1','120111','dabai.com');
insert into teacher values('3','大黑','男','1978-03-08','计科','1','中国武汉','1','120112','dahei.com');
insert into teacher values('4','大红','女','1978-03-09','计科','1','中国武汉','1','120113','dahong.com');
insert into teacher values('5','大绿','男','1978-03-10','计科','1','中国武汉','1','120114','dalv.com');
select * from teacher;
5、向teacher_course表中添加数据
一样的哦:
use EDUC
insert into teacher_course values('1','1','1','1804','1','1','2018','9:00-10:00','9栋','4');
insert into teacher_course values('2','2','1','1804','2','1','2018','9:00-10:00','10栋','4');
insert into teacher_course values('3','3','1','1804','3','1','2018','9:00-10:00','11栋','4');
insert into teacher_course values('4','4','1','1804','4','1','2018','9:00-10:00','12栋','4');
insert into teacher_course values('5','5','1','1804','5','1','2018','9:00-10:00','13栋','5');
select * from teacher_course;
二、开始查询模块(简单查询和连接查询)
1、查询计算机应用专业的学生学号和姓名
我们直接从student表中查找即可:
select sno,sname from student where dno='计科';
2、查询选修了课程的学生学号
只有选课的同学才有成绩,所以我们直接从student_course中找:
select sno from student_course where tcid is not null;
3、查询选修课程1 且成绩在80-90 之间的学生学号和成绩,并将成绩乘以系数0.75
select sno,score*0.75 from student_course where tcid=1 and score between 80 and 90;
注意使用between and 结构哦。
4、查询计算机专业的姓“小”的学生的信息(一顿胡编乱造,哈哈哈)
select * from student where sname like '小_' and dno='计科';
这里要注意通配符_下划线哦。
5、查询“1”课程的成绩高于小红的学生学号和成绩(用到了连接查询)
select student.sno,score--首先选择我们需要投影的列
from student,student_course--从学生表和学生选课表中操作
where tcid='1'and--课程号为1,为了得到小红同学的成绩,我们需要将两张表依靠学号连接
student.sno=student_course.sno and
score in (select score
from student,student_course
where sname='小红'and
student.sno=student_course.sno
)
6、查询没有选修“2”课程的学生姓名
select sname from student,student_course where student.sno=student_course.sno and tcid!=2;
三、子查询/聚集函数
1、求学生的总人数
直接count函数即可:
select count(*) from student;
2、求选修了课程的学生人数
select count(*) from student_course where tcid is not null;
3、求课程的课程号和选修该课程的人数
select count(tcid)
from student_course group by tcid;
你看我们计数课程号,然后得到了:
4、求选修课程超过2 门课的学生学号
select sno from student_course group by sno
having count(*)>1;
四、创建视图
1、将student, course 和student_course表中学生的学号,姓名,课程号,课程名,成绩定义为视图V_SC_S
我们用资源管理器很好的就可以创建视图,但是这里我们的重点都是SQL语句,所以我们来看看怎么创建吧。
create view v_sc_g
as
select student.sno,sname,student_course.tcid,cname,score
from student,student_course,course
where student_course.sno=student.sno and
student_course.tcid=course.cno;
那你是不是想知道自己创建成功了没有,除了资源管理器可以查看,我们直接select * from v_sc_g也可以啊,看看吧:
记得要刷新一下哦。
2、定义一个反映学生出生年份的视图V_YEAR
create view v_year
as
select sno,sname,2020-year(birthday) age
from student;
--当然,你也可以使用select sno,sname,year(getdate())-year(birthday) age from student;
我们查看一下:select * from v_year;
如果你的表中显示时间数据转换错误或者你想操作更多的时间函数,可以看这个哦https://www.cnblogs.com/liu224/archive/2019/04/23/10755402.html
五、使用视图
1、将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G
create view v_avg_s_g
as
select student.sno,count(tcid)cnum,avg(score)avg_age
from student_course,student
where student_course.sno=student.sno
group by student.sno;
2、当然还有我们之前就已经使用的查询操作一样适用,删除操作也适用。
例如:
查询我们创建的v_year:
select * from v_yrea;
删除上面我们创建的v_year:
drop view v_year;
六、流控制语句
流控制语句当然大家时很熟悉的,这里我们要看看SQLserver中是怎么操作的,然后能够熟练使用这些流控制语句(包括:begin..end , if…else, while, break, continue, waitfor, case)。
可以先看看我这篇博客,然后在来操作这些小例子:SQL SERVER掌握变量和流控制语句使用的小demo
1. 对 student 表上入校时间在 2006 年以后的学生修改班级号,其他的学生修改班级代码。(if..else)
declare @entime smalldatetime
declare @sno char(10)
declare student1 cursor for
select student.sno,student.entime
from student
open studnet1
fetch next from student1 into @entime,@sno
while @@fetch_status=0
begin
if year(@entime)>2007
begin
update student
set classno='009'
where student.sno=@sno
end
else
begin
update student
set classno='001'
where student.sno=@sno
end
fetch next from student1 into @entime,@sno
end
close student1
deallocate student1
select *
from student
2. 查询 student 表,只要有年龄小于 20 岁的学生,就将年龄最小的那个学生删掉,如此循环下去,直到所有
的学生的年龄都不小于 20 岁,或是学生的总人数小于 20 个了就推出循环。(while, break,continue,if…else,
可以建视图来处理年龄问题。)
use EDUC
while(select birthday from student)<1988
begin
delete from student
where birthday<1988
if(select count(sno) from student)<20
break else
continue
end
3. 显示所有专业号为‘001’,并且在服务器显示之前,暂停 1 分钟。(waitfor)
waitfor delay '00:01' select * from
student where classno='001'
七、游标
1、声明一个游标,用于查询所有计算机专业的学生的学号、籍贯、和年龄
declare youbiao cursor for
select sno from student
2、 打开该游标
3、 将该游标的内容提取到变量中
fetch<游标名>
into<主变量>
4、 关闭该游标
close<游标名>
虽然操作比较简单,但是还是希望在了解的基础上来进行操作,会更加的深刻。
八、再谈谈约束
1、了解 MS SQL Server 完整性约束技术。 包括实体完整性、域完整性、引用完整性、用户定义完整性。2、了解主键(PRIMARY KEY)约束3、了解外键(FOREIGN KEY)约束4、了解唯一性(UNIQUE)约束5、了解检查(CHECK)约束6、了解 DEFAULT 约束7、了解允许空值约束
这里给出一点简单的例子,毕竟前面我们在1/3有提到过约束:
假如我们有表如下:
use qixin
drop table 职工
drop table 部门
create table 部门
(
部门号 char(4),
名称 varchar(20) not null,
经理名 varchar(8),
地址 varchar(50),
电话号 varchar(20),
constraint PK_部门号 primary key(部门号),
constraint U_名称 unique(名称) )
create table 职工
(
职工号 char(4) , 姓名 varchar(8) not null , 年龄 int ,
- 36 -/ 122
职务 varchar(10) , 工资 money,
部门号 char(4),
constraint PK_职工号 primary key(职工号),
constraint FK_部门号 foreign key(部门号) references 部门(部门号) on delete cascade,
constraint CK_年龄 check(年龄<=60)
)
1、验证主键(PRIMARY KEY)约束
2、验证唯一性(UNIQUE)约束
3、验证检查(CHECK)约束
4、验证外键(FOREIGN)约束
后记:
2/3部分也完结,其实你会发现,跟着做的话,大部分的操作已经完结了对吧,但是还是需要我们多加练习。之后的3/3部分会谈到数据库设计,比如ER图等等。如有误,请指出,觉得好的话,搜藏点赞都可以哦,谢谢。