数据库指令

表的操作 

1.表的建立

create table Student
(
    Sno char(10) primary key,
    Sname varchar(20),
    Ssex char(2),
    Sage smallint,
    Sdept varchar(20)
);

2.表的修改

alter table student add phone char(12);

3.表的删除

drop table Student;

对数据的操作

1.插入数据

-- ********** 此处写“1、为Student表插入两行”的SQL语句 ********** --
insert into Student values('001','Smith','m',18,'CS');
insert into Student values('002','Ketty','f',19,'MA');

2.修改数据

-- ********** 此处写“1、将学生的学号前的‘S’删掉”的SQL语句 ********** --
update student set sno=substring(sno,2,4);

-- ********** 此处写“1、将不及格的学生成绩加5分”的SQL语句 ********** --
update SC set grade=grade+5 where grade<60;

3.删除数据

delete from SC where grade is null;

查询操作

-- ********** 此处写“4、查询姓名中带有'n'字母的学生的学号，姓名（使用like语句）”的SQL语句 ********** --
SELECT sno,sname
from Student
where sname LIKE'%n%';

-- ********** 此处写“5、使用distinct关键字查询学生表中不同的系，列出系（去除重复元祖）”的SQL语句 ********** --
select distinct sdept
from Student;

-- ********** 此处写“2、查询没有选C06（课程号）课程的同学的学号，姓名，性别”的SQL语句 ********** --
select sno,sname,ssex
from student
where sno NOT IN
    (
        select sno
        from sc
        where cno='C06'
    );

select sno,cno,grade
from sc
where grade  IN
    (select MAX(grade)
    from sc
    );

-- ********** 此处写“1、查询E系平均成绩最高的同学的姓名，列出姓名”的SQL语句 ********** --
select sname
from student
where sdept='E' and sno in 
    (
        select sno
        from sc
        group by sno
        having avg(grade)>=all(select avg(grade) from sc
        where sno in
        (
            select sno
            from student
            where sdept='E' 
        )
        group by sno
        )
    );
        

-- ********** 此处写“2、查询不及格人数大于等于2人的课程，列出课程号，课程名，不及格人数(列名为scnt)”的SQL语句 ********** --
select course.cno,cname,count(sno) as scnt from sc,course
where sc.cno=course.cno and grade<60
group by course.cno,cname
having count(sno)>=2;

索引

1.创建唯一索引

-- ********** 此处写“1、为Student表的sname列创建唯一索引（索引名：idx_student_sname）”的SQL语句 ********** --

create unique index idx_student_sname on Student(sname); 

2.降序索引

-- ********** 此处写“2、为sc表的grade列创建降序索引（索引名：idx_sc_grade）”的SQL语句 ********** --

create index idx_sc_grade on sc(grade desc);

约束

1.缺省约束

-- ********** 此处写“3、为Student表的ssex列添加缺省约束，缺省值为‘m’(约束名：stu_def_ssex)”的SQL语句 ********** --
alter table student add constraint stu_def_ssex default 'm' for ssex;

2.值约束

-- ********** 此处写“1、为Student表的Sage列添加约束，使其取值小于30岁(约束名：stu_chk_sage)”的SQL语句 ********** --

alter table student add constraint stu_chk_sage check(sage<30);

3.添加外码

-- ********** 此处写“1、为SC表的sno列添加外码（约束名：stu_ref_sno）”的SQL语句 ********** --
alter table sc add constraint stu_ref_sno foreign key(sno) references student(sno);

4.添加唯一约束

-- ********** 此处写“1、为Course表的cname列添加唯一约束（约束名：Course_un_cname）”的SQL语句 ********** --
alter table course add constraint Course_un_cname unique(cname); 

视图

1.创建视图

-- ********** 此处写“1、创建视图vcg，查询课程名为'Math'的课程中，成绩不及格的学生，列出学号，课程号，成绩。”的SQL语句 ********** --
create view vcg
as
select sno,cno,grade
from sc
where grade<60 and cno in (select cno from course where cname="Math");

2.修改、删除视图数据

-- ********** 此处写“2、修改视图vcg中的数据，将成绩加5分”的SQL语句 ********** --
update vcg
set grade=grade+5;

-- ********** 此处写“3、删除视图vcg中的数据，将成绩小于40分的选课信息删除。”的SQL语句 ********** --
delete 
from vcg
where grade<40;

3.视图的创建和使用

-- ********** 此处写“1、创建vsumc视图，使其包含每个学生的获得的学分（成绩及格才能得学分）。列出学号和总学分（列名：ssumc）”的SQL语句 ********** --
create view vsumc(sno,ssumc)
as
select sno,sum(ccredit)
from sc,course 
where grade>60 and sc.cno=course.cno
group by sno; 

-- ********** 此处写“3、使用vsumc视图，查询总学分最高的学生，列出学号，姓名，性别，年龄，所在系”的SQL语句 ********** --
select student.sno,sname,ssex,sage,sdept
from student
where exists (select * from vsumc where sno=student.sno and ssumc>=all(select ssumc from vsumc));

-- ********** 此处写“4、使用vsumc视图，查询平均分大于等于65分的学生的学号，平均分(列名：savg)，总学分（列名：ssumc）”的SQL语句 ********** --
select vsumc.sno,avg(grade) savg,ssumc from sc right join vsumc ON  sc.sno= vsumc.sno
group by vsumc.sno,ssumc
having avg(grade)>=65;