create table student( sid varchar(8) primary key, sname varchar(16) not null, sex varchar(2) check (sex='男' or sex = '女')default '女', age int check(age > 0 and age < 120), tel varchar(16) unique);create table course( cid varchar(8) primary key, cname varchar(8) not null);create table sc( sid varchar(8) foreign key references student(sid), cid varchar(8) foreign key references course(cid), score int);insert into studentvalues('s001','丰登儿','男',18,'13527542451');insert into studentvalues('s002','班克尔','女',17,'13884233134');insert into studentvalues('s003','车笔刀','女',13,'15086623248');insert into studentvalues('s004','趴耳朵','男',19,'15323535256');insert into studentvalues('s005','直角','女',23,'15653579258');insert into studentvalues('s006','扳手','女',19,'13663279788');insert into studentvalues('s007','俄石板','女',21,'13656529396');insert into coursevalues('c001','语文');insert into coursevalues('c002','数学');insert into coursevalues('c003','外语');insert into coursevalues('c004','物理');insert into coursevalues('c005','化学');insert into scvalues('s001','c001',70);insert into scvalues('s001','c002',78);insert into scvalues('s001','c003',82);insert into scvalues('s001','c004',63);insert into scvalues('s001','c005',92);insert into scvalues('s002','c001',52);insert into scvalues('s002','c002',67);insert into sc(sid,cid)values('s002','c003');insert into scvalues('s002','c004',82);insert into scvalues('s002','c005',88);insert into scvalues('s003','c001',52);insert into sc(sid,cid)values('s003','c002');insert into scvalues('s003','c003',72);insert into sc(sid,cid)values('s003','c004');insert into scvalues('s003','c005',88);insert into scvalues('s004','c001',76);insert into scvalues('s004','c003',89);--二、执行一下操作--1. 修改sc表中s001号学生的c002号课程的成绩,改为82--(4分)select * from sc;update scset score = 82where sid = 's001' and cid='c002'--2. 删除sc表中学号为s004的学生成绩(4分)delete from scwhere sid='s004'--3. 查询年龄在18~20岁的学生信息(4分)select *from studentwhere age between 18 and 20--4. 查询姓名以儿结尾的学生信息(4分)select * from studentwhere sname like '%儿'--5. 查询电话号码 倒数第3位是2的学生信息(4分)select *from studentwhere tel like '%2__'--6. 查询男女生各多少人(4分)select sex,COUNT(*) 人数from student--where sex = '女'group by sex--7. 查询每门课程的最高分、最低分、总分和平均分(4分)select cname 课程名, MAX(score) 最高分, MIN(score) 最低分, SUM(score) 总分, AVG(score) 平均分from student,sc,coursewhere student.sid = sc.sid and course.cid = sc.cidgroup by cname--8. 查询每个人的总分和平均分(4分)select student.sname 姓名, SUM(score) 总分, AVG(score) 平均分from student,scwhere student.sid = sc.sidgroup by student.sname--9. 查询比耙耳朵 大的学生信息(4分)select * from studentwhere age > ( select age from student where sname = '趴耳朵');--10. 查询每个学生的学号、姓名、性别、科目和成绩(4分)select student.sid 学号,student.sname 姓名,sex 性别, course.cname 课程名, sc.score 分数from student,sc,coursewhere student.sid = sc.sid and course.cid = sc.cidgroup by student.sid,student.sname,sex,course.cname,sc.score--orselect student.sid,sname,sex,cname,scorefrom student,sc,coursewhere student.sid=sc.sidand course.cid=sc.cid--11. 查询没及格的学生的学号、姓名、性别、科目和成绩(5分)select student.sid 学号,student.sname 姓名,sex 性别, course.cname 课程名, sc.score 分数from student,sc,coursewhere student.sid = sc.sid and course.cid = sc.cid and sc.score<60--12. 查询学生表中第3~6个人的信息(分页查询5分)select top 3 *from studentwhere sid not in ( select top 3 sid from student)--13. 查询没有参加考试的学生的学生信息(5分)select * from studentwhere student.sid not in ( select sc.sid from sc)祝愿看到这篇文章的人，学业进步，事业有成，(^__^) 嘻嘻！
版权声明：本文为博主原创文章，未经博主允许不得转载。