--学生表 student(sno,sname,ssex,smajor,sage) |
create table student( |
sno char (20) not null primary key , |
sname char (20) not null , |
ssex char (2), |
smajor char (20), |
sage int ); |
--创建课程表 课程(课号,课名,学分) |
--course(cno,cname,cclassh) |
create table course( |
cno char (20), |
cname char (20), |
cclassh int ); |
--创建选课表 选课(学号,课号,成绩) |
--sc(sno,cno,grade) |
create table sc( |
sno char (20), |
cno char (20), |
grade int ); |
insert into student |
values ( '2023001' , '雷鸿宇' , '男' , '计算机' , '19' ), |
( '2023002' , '沈宇' , '男' , '计算机' ,20), |
( '2023003' , '张家豪' , '男' , '数学' ,20), |
( '2023004' , '史凯' , '男' , '英语' ,19), |
( '2023005' , '王心怡' , '女' , '数学' ,20); |
insert into course |
values ( '1' , '数学' , '2' ), |
( '2' , '计算机' , '3' ), |
( '3' , '英语' , '2' ), |
( '4' , '政治' , '2' ); |
insert into sc |
values ( '2023001' , '2' , '78' ), |
( '2023005' , '2' , '80' ), |
( '2023001' , '1' , '80' ), |
( '2023004' , '3' , '59' ); |
select * from student |
select * from course |
select * from sc |
--[例2] 查询全体学生的详细记录。 |
select * from student; |
--[例3] 查全体学生的姓名及其出生年份。 |
select sname, year (getdate())-sage as sbir from student; |
--[例4] 查询姓名是冯涛所在的专业 |
select smajor from student |
where sname= '沈宇' ; |
--[例5] 查询选修了课程的学生学号。 |
select sno from sc |
--[例6]查询选修课程的各种成绩 |
select grade from sc |
--[例7]查询计算机专业全体学生的名单。 |
select * from student |
where smajor = '计算机' |
--(2)选择表中的若干元组 |
--[例8] 查询所有年龄在20岁以下的学生姓名及其年龄。 |
select sname,sage from student |
where sage <= 20 |
--[例9] 查询考试成绩在60分以下的学生学号。 |
select sno from sc |
where grade <=70 |
--[例10] 查询年龄在21~23岁(包括21岁和23岁)之间的学生的姓名、专业和年龄 |
select sname,smajor,sage from student |
where sage between '19' and '20' |
--[例11] 查询年龄不在21~23岁(包括21岁和23岁)之间的学生的姓名、专业和年龄 |
select sname,smajor,sage from student |
where sage not between '21' and '23' |
--[例12]查询计算机、数学专业学生的姓名和性别 |
select sname,ssex from student |
where smajor = '计算机' |
--[例13]查询学号为2023003的学生的详细情况。 |
select * from student |
where sno = 2023003 |
--[例14]查询学号不是为2023003的学生的详细情况。 |
select * from student |
where sno not like 2023003 |
--[例15] 查询所有姓王学生的姓名、学号、性别 |
select sname,sno,ssex from student |
where sname like '雷%' |
--[例17] 查询名字中第2个字为"阳"字的学生的姓名、学号 |
select sname ,sno from student |
where sname like '_宇%' |
--[例18] 查询数学专业且年龄在20岁以下的学生姓名 |
select sname from student |
where smajor = '计算机' or sage <=20 |
--(3)多查询结果进行排序 |
--[例1]查询选修了5号课程的学生的学号及其成绩,查询结果按分数降序排列 |
select sno grade from sc |
where cno = '2' |
order by grade desc |
--[例2]查询全体学生的年龄,按年龄降序排列。 |
select sage from student |
order by sage desc |
--(4)使用集函数 |
--[例3] 查询学生总人数。 |
select count (sno) from student |
--[例4] 查询选修了课程的学生人数。 |
select count ( distinct sno) from sc |
--[例5] 计算5号课程的学生平均成绩 |
select avg (grade) from sc |
where cno = '2' |
--[例6] 查询选修2号课程的学生最高分数 |
select max (grade) from sc |
where cno = '2' |
--(5)对查询结果进行分组 |
--[例7] 查询各个课程号及相应的选课人数,然后按照课程号分组 |
select cno, count (sno) from sc |
group by cno |
--[例8]求各个课程号及相应的课程成绩在85分以上的学生人数, |
select cno , count (sno) from sc |
where grade >= '85' |
group by cno ; |
--[9]统计每门课的最高成绩 |
select cno, max (grade) from sc |
group by cno |
--(1)笛卡尔积 |
--学生表、选课表、课程表三表联查 |
select student.*,sc.*,course.* from student,sc,course |
--(2)等值连接 |
--[例32] 查询每个学生及其选修课程的情况。 |
select sc.sno,course.cno,grade,sname,cname from student ,course,sc |
where student.sno=sc.sno and course.cno=sc.cno |
--[例33]查询数学专业学生的学号,姓名,专业,选修的课程号,课程名和成绩 |
select student.sno,sname,smajor,cname,grade ,sc.cno from student ,sc, course |
where student.sno=sc.sno and course.cno=sc.cno and smajor= '大数据技术' |
--(3)复合连接 |
-- 查询选修2号课程且成绩在55分以上的所有学生的姓名、性别、专业。 |
select sname ,ssex,smajor,grade from student,sc |
where cno= '2' and grade>=95 and student.sno=sc.sno |
--(4)内连接(等值连接) |
--查询所有的学生信息及其选修课程情况 |
select student.sno,sname,cname,grade from student,course,sc |
where student.sno=sc.sno and course.cno=sc.cno |
--(5)左连接:查询所有的学生信息及其选修课程情况(按照年龄降序排列) |
select sc.sno,sname,cno,grade from student left join sc |
on student.sno=sc.sno |
--(6)右连接:查询所有的学生信息及其选修课程情况 |
select sc.sno,sname,cno,grade from sc right join student |
on student.sno=sc.sno |
--(6)全连接:查询所有的学生信息及其选修课程情况 |
select sc.sno,sname,cno,grade from student full join sc |
on student.sno=sc.sno |
--1、创建触发器 |
--[例1]Student中创建DML触发器stu_unupdate,禁止修改学号Sno、姓名Sname字段。 |
go |
create trigger stu_unupdate on student |
after update |
as |
--执行语句 |
if update (sno) or update (sname) |
print( '禁止修改学号 姓名' ) |
rollback ; |
go |
--触发语句 |
update student |
set sno = '005' |
where sno = '2023005' |
--[例2] 在teach数据库的“学生”表Student中创建DML触发器stu_insert, |
--给表中每添加一条信息时,自动查看所有学生信息 |
go |
create trigger stu_insert on student |
after insert |
as |
--执行语句 |
select * from student |
go |
--触发语句 |
insert into student(sno,sname) |
values ( '2023023' , '刘' ),( '20239999' , '刘梦芝' ); |
--[例3]创建触发器,实现禁止删除课程号为1的课程信息 |
go |
create trigger cou_cno on course |
after delete |
as |
--执行语句 |
update course |
set cno = '2' |
print( '禁止删除课程号为2的课程信息' ) |
rollback ; |
go |
--[例4]设计一个触发器完成上述功能:假设student表, |
--当删除某一同学sno时,该同学的所有选课也都要删除。 |
go |
create trigger stu_delete on student |
after delete |
as |
--执行语句 |
--定义变量,代表删除的学号 |
declare @sno_old varchar (20) |
--初始化 |
select @sno_old = sno from deleted |
delete from sc |
where sc.sno = @sno_old |
go |
--触发语句 |
delete from student |
where sno = '2023005' |
select * from student |
--[例5]创建触发器,假设student表中某一学生要变更其主码sno的值, |
--如使其原来的20210023变更为20210056, |
--此时sc表中该同学已选课记录的sno也需自动随其改变。 |
go |
create trigger stu_update on student |
after update |
as |
--执行语句 |
--定义2个变量,代表修改前、修改后的学号 |
declare @sno_old varchar (20) |
declare @sno_new varchar (20) |
--初始化 |
select @sno_old = sno from deleted |
select @sno_new = sno from inserted |
update sc |
set sc.sno = @sno_new |
where sc.sno =@sno_old |
--触发语句 |
update student |
set sno= '666' |
where sno = '2023001' |