初学阶段

[sql]代码库

--学生表  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'