SQL语句学习笔记

表结构：

-- 教师表

CREATE TABLE teacher(

tno INT NOT NULL PRIMARY KEY,

tname VARCHAR(20) NOT NULL

);

INSERT INTO teacher(tno,tname)VALUES(1,'张老师');

INSERT INTO teacher(tno,tname)VALUES(2,'王老师');

INSERT INTO teacher(tno,tname)VALUES(3,'李老师');

INSERT INTO teacher(tno,tname)VALUES(4,'赵老师');

INSERT INTO teacher(tno,tname)VALUES(5,'刘老师');

INSERT INTO teacher(tno,tname)VALUES(6,'向老师');

INSERT INTO teacher(tno,tname)VALUES(7,'李文静');

INSERT INTO teacher(tno,tname)VALUES(8,'叶平');

-- 学生表

CREATE TABLE student(

sno int NOT NULL PRIMARY KEY,

sname varchar(20) NOT NULL,

sage datetime NOT NULL,

ssex char(2) NOT NULL

);

INSERT INTO student(sno,sname,sage,ssex) VALUES(1,'张三','1980-1-23','男');

INSERT INTO student(sno,sname,sage,ssex) VALUES(2,'李四','1982-12-12','男');

INSERT INTO student(sno,sname,sage,ssex) VALUES(3,'张飒','1981-9-9','男');

INSERT INTO student(sno,sname,sage,ssex) VALUES(4,'莉莉','1983-3-23','女');

INSERT INTO student(sno,sname,sage,ssex) VALUES(5,'王弼','1982-6-21','男');

INSERT INTO student(sno,sname,sage,ssex) VALUES(6,'王丽','1984-10-10','女');

INSERT INTO student(sno,sname,sage,ssex) VALUES(7,'刘香','1980-12-22','女');

-- 课程表

CREATE TABLE course(

cno int NOT NULL PRIMARY KEY,

cname nvarchar(20) NOT NULL,

tno int NOT NULL

);

-- 添加外键

ALTER TABLE course

ADD CONSTRAINT fk_course_teacher

FOREIGN KEY (tno) REFERENCES teacher (tno);

insert into course(cno,cname,tno) values(1,'企业管理',3);

insert into course(cno,cname,tno) values(2,'马克思',1);

insert into course(cno,cname,tno) values(3,'UML',2);

insert into course(cno,cname,tno) values(4,'数据库',5);

insert into course(cno,cname,tno) values(5,'物理',8);

-- 创建成绩表
CREATE TABLE sc(

sno int NOT NULL,

cno int NOT NULL,

score int NOT NULL

);

ALTER TABLE sc

ADD CONSTRAINT fk_sc_course

FOREIGN KEY (cno) REFERENCES course (cno);

ALTER TABLE sc

ADD CONSTRAINT fk_sc_student

FOREIGN KEY (sno) REFERENCES student (sno);

INSERT INTO sc(sno,cno,score)VALUES(1,1,80);

INSERT INTO sc(sno,cno,score)VALUES(1,2,86);

INSERT INTO sc(sno,cno,score)VALUES(1,3,83);

INSERT INTO sc(sno,cno,score)VALUES(1,4,89);

INSERT INTO sc(sno,cno,score)VALUES(2,1,50);

INSERT INTO sc(sno,cno,score)VALUES(2,2,36);

INSERT INTO sc(sno,cno,score)VALUES(2,3,43);

INSERT INTO sc(sno,cno,score)VALUES(2,4,59);

INSERT INTO sc(sno,cno,score)VALUES(3,1,50);

INSERT INTO sc(sno,cno,score)VALUES(3,2,96);

INSERT INTO sc(sno,cno,score)VALUES(3,3,73);

INSERT INTO sc(sno,cno,score)VALUES(3,4,69);

INSERT INTO sc(sno,cno,score)VALUES(4,1,90);

INSERT INTO sc(sno,cno,score)VALUES(4,2,36);

INSERT INTO sc(sno,cno,score)VALUES(4,3,88);

INSERT INTO sc(sno,cno,score)VALUES(4,4,99);

INSERT INTO sc(sno,cno,score)VALUES(5,1,90);

INSERT INTO sc(sno,cno,score)VALUES(5,2,96);

INSERT INTO sc(sno,cno,score)VALUES(5,3,98);

INSERT INTO sc(sno,cno,score)VALUES(5,4,99);

INSERT INTO sc(sno,cno,score)VALUES(6,1,70);

INSERT INTO sc(sno,cno,score)VALUES(6,2,66);

INSERT INTO sc(sno,cno,score)VALUES(6,3,58);

INSERT INTO sc(sno,cno,score)VALUES(6,4,79);

INSERT INTO sc(sno,cno,score)VALUES(7,1,80);

INSERT INTO sc(sno,cno,score)VALUES(7,2,76);

INSERT INTO sc(sno,cno,score)VALUES(7,3,68);

INSERT INTO sc(sno,cno,score)VALUES(7,4,59);

INSERT INTO sc(sno,cno,score)VALUES(7,5,89);

-- 1、查询课程1的成绩比课程2的成绩高的所有学生的学号；
select a.sno from (select sno,score from sc where cno=1) a join (select sno,score from sc where cno=2) b on a.sno = b.sno where a.score > b.score;
-- 2、查询平均成绩大于60分的同学的学号和平均成绩；
select sno,avg(score) from sc group by sno having avg(score)>60;
-- 3、查询学过“叶平”老师所教所有课程的所有同学的学号、姓名；
select sno from (select sno,count(cno) num from sc where cno in (select cno from course where tno in (select tno from teacher where tname = '叶平'))group by sno) t where t.num = (select count(cno)from course where tno = (select tno from teacher where tname = '叶平'));
-- 4、查询所有课程成绩小于60分的同学的学号、姓名；
select sno,sname from student where sno in(select distinct sno from sc where score<60);
-- 5、查询所有同学的学号、姓名、选课数、总成绩；
select stu.sno,stu.sname,count(cno),sum(score) from student stu join sc s on stu.sno = s.sno group by stu.sno;