经典sql语句
1、数据库myschool
CREATE DATABASE myschool;
2、表名student 、course 、teacher 、sc
//学生表student
CREATE TABLE student(
Sid VARCHAR(10), //学号
Sname NVARCHAR(20), //姓名
Sage DATETIME, //年龄
Ssex NVARCHAR(10) //性别
);
//课程表course
CREATE TABLE course(
Cid VARCHAR(10), //课程号
Cname NVARCHAR(20), //课程名称
Tid VARCHAR(10) //教师编号
);
//教师表teacher
CREATE TABLE teacher(
Tid VARCHAR(10), //教师编号
Tname NVARCHAR(10) //教师姓名
);
//分数表score
CREATE TABLE sc(
Sid VARCHAR(10), //学号
Cid VARCHAR(10), //课程号
score DECIMAL(18,1) //分数
);
3、插入数据
INSERT INTO student VALUES('01' , '赵雷' , '1990-01-01' , '男');
INSERT INTO student VALUES('02' , '钱电' , '1990-12-21' , '男');
INSERT INTO student VALUES('03' , '孙风' , '1990-05-20','男');
INSERT INTO student VALUES('04' , '李云' , '1990-08-06' , '男');
INSERT INTO student VALUES('05' , '周梅' , '1991-12-01' , '女');
INSERT INTO student VALUES('06' , '吴兰' , '1992-03-01' , '女');
INSERT INTO student VALUES('07' , '郑竹' , '1989-07-01' , '女');
INSERT INTO student VALUES('08' , '王菊' , '1990-01-20' , '女');
INSERT INTO course VALUES('01' , '语文' , '02');
INSERT INTO course VALUES('02' , '数学' , '01');
INSERT INTO course VALUES('03' , '英语' , '03');
INSERT INTO teacher VALUES('01' , '张三');
INSERT INTO teacher VALUES('02' , '李四');
INSERT INTO teacher VALUES('03' , '王五');
INSERT INTO sc VALUES('01' , '01' , 80);
INSERT INTO sc VALUES('01' , '02' , 90);
INSERT INTO sc VALUES('01' , '03' , 99);
INSERT INTO sc VALUES('02' , '01' , 70);
INSERT INTO sc VALUES('02' , '02' , 60);
INSERT INTO sc VALUES('02' , '03' , 80);
INSERT INTO sc VALUES('03' , '01' , 80);
INSERT INTO sc VALUES('03' , '02' , 80);
INSERT INTO sc VALUES('03' , '03' , 80);
INSERT INTO sc VALUES('04' , '01' , 50);
INSERT INTO sc VALUES('04' , '02' , 30);
INSERT INTO sc VALUES('04' , '03' , 20);
INSERT INTO sc VALUES('05' , '01' , 76);
INSERT INTO sc VALUES('05' , '02' , 87);
INSERT INTO sc VALUES('06' , '01' , 31);
INSERT INTO sc VALUES('06' , '03' , 34);
INSERT INTO sc VALUES('07' , '02' , 89);
INSERT INTO sc VALUES('07' , '03' , 98);
4、多表联查
(1)、查询01课程比02课程成绩低的学生的信息及课程分数
SELECT a.*, b.`score` 课程01的分数, c.`score` 课程02的分数
FROM student a, sc b, sc c
WHERE a.`Sid`=b.`Sid`
AND a.`Sid`=c.`Sid`
AND b.`Cid`='01'
AND c.`Cid`='02'
AND b.`score`<c.`score`;
(2)、查询平均成绩大于等于60分的同学的学生编号和学生姓名
SELECT a.`Sid`, a.`Sname`, AVG(b.`score`) 平均成绩
FROM student a,sc b
WHERE a.`Sid`=b.`Sid`
GROUP BY a.`Sid`
HAVING 平均成绩>60
ORDER BY a.`Sid`;
(3)、 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT a.`Sid`,a.`Sname`,COUNT(b.`Cid`) 选课总数, SUM(b.`score`) 所有课程的总成绩
FROM student a,sc b
WHERE a.`Sid`=b.`Sid`
GROUP BY a.`Sid`
ORDER BY a.`Sid`;
(4)、查询李姓老师数量
SELECT COUNT(t.`Tname`) 李姓老师数量
FROM teacher t
WHERE t.`Tname` LIKE '李%' ;
(5)、查询学过张三老师授课的同学的信息
SELECT DISTINCT a.*
FROM student a,course b,teacher c,sc d
WHERE a.`Sid`=d.`Sid`
AND d.`Cid`=b.`Cid`
AND b.`Tid`=c.`Tid`
AND c.`Tname`='张三'
ORDER BY a.`Sid`;
(6)、查询没学过张三老师授课的同学的信息
SELECT student.*
FROM student
WHERE student.`Sid` NOT IN(
SELECT DISTINCT a.`Sid`
FROM student a,course b,teacher c,sc d
WHERE a.`Sid`=d.`Sid`
AND d.`Cid`=b.`Cid`
AND b.`Tid`=c.`Tid`
AND c.`Tname`='张三')
ORDER BY student.`Sid`;
(7)、查询学过编号为01并且也学过编号为02的课程的同学的信息
SELECT DISTINCT a.*
FROM student a,sc b
WHERE a.`Sid`=b.`Sid`
AND b.`Cid`='01'
AND EXISTS(SELECT 1 FROM sc c WHERE c.`Sid`=b.`Sid` AND c.`Cid`='02')
ORDER BY a.`Sid`
(8)、查询学过编号为01但是没学过编号为02的课程的同学的信息
SELECT DISTINCT a.*
FROM student a,sc b
WHERE a.`Sid`=b.`Sid`
AND b.`Cid`='01'
AND NOT EXISTS(SELECT 1 FROM sc c WHERE c.`Sid`=b.`Sid` AND c.`Cid`='02')
ORDER BY a.`Sid`
(9)、查询没有学全所有课程的同学
SELECT a.*
FROM student a,sc b
WHERE a.`Sid`=b.`Sid`
GROUP BY a.`Sid`,a.`Sname`,a.`Sage`,a.`Ssex`
HAVING COUNT(Cid)<(SELECT COUNT(Cid) FROM course);
(10)、查询至少有一门课与学号为01的同学所学相同的同学的信息
SELECT DISTINCT a.*
FROM student a,sc b
WHERE a.`Sid`=b.`Sid`
AND Cid IN (SELECT Cid FROM sc WHERE Sid='01')
AND a.`Sid` <>'01';
(11)、查询和01同学学习的课程完全相同的其他同学的信息
SELECT student.*
FROM student
WHERE Sid IN(
SELECT sc.`Sid` FROM sc
WHERE sc.`Cid` IN(SELECT DISTINCT Cid FROM sc WHERE Sid='01')
AND sc.`Sid` <> '01'
GROUP BY sc.`Sid`
HAVING COUNT(1)=(SELECT COUNT(1) FROM sc WHERE Sid='01')
);
(12)、查询俩门及其以上不及格的同学的学号,姓名及其平均成绩
SELECT a.Sid,a.Sname,CAST(AVG(b.score) AS DECIMAL(18,2)) 平均成绩
FROM student a,sc b
WHERE a.Sid=b.Sid
AND a.Sid IN(
SELECT Sid FROM sc WHERE score<60
GROUP BY Sid
HAVING COUNT(1)>=2
)
(13)、检索01课程分数小于60,按分数降序排列的学生信息
SELECT a.*,b.`Cid`,b.`score`
FROM student a,sc b
WHERE a.Sid=b.Sid
AND b.score<60
AND b.Cid='01'
ORDER BY b.score DESC
(14)、查询不同老师所教不同课程平均分从高到低显示
SELECT teacher.`Tid`,teacher.`Tname`,CAST(AVG(sc.`score`) AS DECIMAL(18,2)) avg_score
FROM course,sc,teacher
WHERE course.`Tid`=teacher.`Tid`
AND course.`Cid`=sc.`Cid`
GROUP BY teacher.`Tid`,teacher.`Tname`
ORDER BY avg_score DESC
(15)、查询出只有两门课程的全部学生的学号和姓名
SELECT student.`Sid`,student.`Sname`
FROM student,sc
WHERE student.`Sid`=sc.`Sid`
GROUP BY student.`Sid`,student.`Sname`
HAVING COUNT(sc.`Cid`)=2
(16)、查询课程名称为”数学”,且分数低于60的学生姓名和分数
SELECT student.`Sname`,sc.`score`
FROM student,sc,course
WHERE student.`Sid`=sc.`Sid`
AND sc.`Cid`=course.`Cid`
AND course.`Cname`='数学'
AND sc.`score`<60
(17)、查询所有学生的课程及分数情况;
SELECT student.`Sid`,student.`Sname`,course.`Cname`,sc.`score`
FROM student, sc,course
WHERE student.`Sid`=sc.`Sid`
AND sc.`Cid`=course.`Cid`
(18)、检索至少选修两门课程的学生学号
SELECT sc.`Sid`
FROM sc
GROUP BY sc.`Sid`
HAVING COUNT(sc.`Cid`)>=2
(19)、查询选修了全部课程的学生信息
SELECT *
FROM student
WHERE Sid IN(
SELECT Sid FROM sc
GROUP BY Sid
HAVING COUNT(1)=(SELECT COUNT(1) FROM course))