1.定义表
建立“学生”表student, 学号是主码, 姓名取值唯一。
create table student
(sno varchar(9) primary key, /* 列级完整性约束条件*/
sname varchar(20) unique, /* sname取唯一值*/
ssex char(2) ) check (ssex in ('男','女') )
sage smallint,
sdept char(20) /* 系代码*/
);
2.删除数据
删除学号为200215128的学生记录。
delete from student where sno= 200215128';
删除计算机科学系所有学生的选课记录。
delete from sc where sno in (select sno from student where sdept =‘cs');
删除没有考试的选课记录
delete from sc where grade is NULL;
3.条件查询
查询计算机系年龄在20岁以下的学生姓名。
select sname from student where sdept='cs'and sage<20;
排序
查询选修了3号课程的学生的学号及其成绩, 查询结果按分数降序排列。
select sno, grade from sc where cno='3'order by grade desc;
4.插入数据
将学生元组(学号:200215128;姓名:陈冬;性别:男;所在系:is;年龄:18岁)插入到student表中。
insert into student values ('200215128','陈冬','男','is', 18);
插入一条选课记录('200215128','1')。
insert into sc(sno, cno) values ('200215128','1', 80);
5.按条件修改数据
将学生200215121的年龄改为22岁
update student set sage=22 where sno='200215121';
将所有学生的年龄增加1岁
update student set sage= sage+1;
6.建立信息系的学生成绩视图。
create view Vstudent(sno, sname, ssex, cno, cname, grade)
as
select student.sno, student.sname, student.ssex, course.cno,
course.cname, grade
from student, sc, course
where student.sno=sc.sno and sc.cno=course.cno and sdept='is'
7.定义行级触发器,向成绩表中加入一条完整记录,如果加入的成绩大于100分自动将成绩设定为100分。
create trigger tsc
before insert on sc /*触发事件是插入操作*/
for each row /*行级触发器*/
begin
if (new.grade>100) then
set new.grade=100;
end if;
end;
8过程 将某学生的某个选课成绩(提供学号和课程号)降低30分,如果下降后成绩小于0分则将其成绩设定为0分。
create procedure psc(sno varchar(20),cno int)
as declare
newgrade int;
begin
select grade-30 into newgrade ;
if newgrade<0 then
set newgrade=0;
end if;
update sc set grade=newgrade where sno=sno and cno=cno;
end;
9.多表连接查询
自然连接
SELECT Student .sno,Sname
FROM Student ,SC
WHERE Student.sno=SC.sno AND
SC.Cno='2' AND SC.Grade>90;
多
SELECT Student,Sno,Sname,Cname,Grade
From Student,SC,Course 三个表
WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;
10.计算平均成绩(1号学生)
SELECT AVG(Grade)/MAX/COUNT(*)总/SUM
FROM SC
WHERE Cno='1';
11.求各个课程号相应的选课人数
SELECT Cno,COUNT(Sno)
FROM SC
GROUP BY Cno;
指定筛选条件
SELECT Sno
FROM SC
GROUP BY Sno;
HAVING COUNT(*)>3;
12,查询经过计算的值
使用 SELECT 关键字可以计算列之间的值,例如,计算某个学生的平均成绩:
SELECT name, (math_score + english_score + science_score)/3 AS avg_score
FROM students
WHERE name = 'Tom';