复杂的高频SQL语句&清空表SQL语句

常见SQL语句练习

 

先来看一下表之间的结构和联系

创建表结构

CREATE TABLE course (cid INT, cname VARCHAR(20), tid INT, PRIMARY KEY(cid)) ENGINE = INNODB;

CREATE TABLE teacher (tid INT, tname VARCHAR(20), tcid INT,PRIMARY KEY(tid)) ENGINE = INNODB;

CREATE TABLE student (sid INT, sname VARCHAR(20), sbirth VARCHAR(20), ssex VARCHAR(10),PRIMARY KEY(sid)) ENGINE = INNODB;

CREATE TABLE score (sid INT, cid INT, sscore INT(3),PRIMARY KEY(sid,cid)) ENGINE = INNODB;

其他表结构不在赘述！

向表中添加数据：

• student 学生表

insert into  student select 1 , '赵雷' , '1990-01-01' , '男' ;
insert into  student select 2 , '钱电' , '1990-12-21' , '男' ;
insert into  student select 3 , '孙风' , '1990-05-20' , '男' ;
insert into  student select 4 , '李云' , '1990-08-06' , '男' ;
insert into  student select 5 , '周梅' , '1991-12-01' , '女' ;
insert into  student select 6 , '吴兰' , '1992-03-01' , '女' ;
insert into  student select 7 , '郑竹' , '1989-07-01' , '女' ;
insert into  student select 8 , '王菊' , '1990-01-20' , '女' ;

• course 课程表

insert into course select 1 , '语文' , '02';
insert into course select 2 , '数学' , '01';
insert into course select 3 , '英语' , '03';

• teacher 教师表 插入数据values

insert into teacher values(1 , '张三' , 1);
insert into teacher values(3 , '李四' , 2);
insert into teacher values(2 , '王五' , 3);

• score 分数表

insert into score values(1 , 1 , 80);
insert into score values(1 , 2 , 90);
insert into score values(1 , 3 , 99);
insert into score values(2 , 1 , 70);
insert into score values(2 , 2 , 60);
insert into score values(2 , 3 , 80);
insert into score values(3 , 1 , 80);
insert into score values(3 , 2 , 80);
insert into score values(3 , 3 , 80);
insert into score values(4 , 1 , 50);
insert into score values(4 , 2 , 30);
insert into score values(4 , 3 , 20);
insert into score values(5 , 1 , 76);
insert into score values(5 , 2 , 87);
insert into score values(6 , 1 , 31);
insert into score values(6 , 3 , 34);
insert into score values(7 , 2 , 89);
insert into score values(7 , 3 , 98);

插入十八条记录

1、查询1课程比2课程成绩低的学生的信息及课程分数  

思路解析: 需要查询的字段：学生信息 课程1分数 课程2分数

SELECT s.* FROM student s;
SELECT sc.sscore FROM score sc;
SELECT s.* , sc.sscore FROM student s JOIN score sc ON s.sid = sc.sid;
SELECT s.* , sc.sscore FROM student s JOIN score sc ON s.sid = sc.sid AND sc.cid = 1;
SELECT s.* , sc2.sscore  as score2 ,sc.sscore as score1 FROM student s JOIN score sc ON s.sid = sc.sid AND sc.cid = 1 LEFT JOIN score sc2 ON s.sid = sc2.sid AND sc2.cid = 2 where sc2.sscore > sc.sscore

2、查询平均成绩大于等于60分的学生编号和学生姓名和平均成绩

-- 查询student表的 sid、sname、avg(score.sscore)
SELECT s.sid , s.sname FROM student s;

SELECT s.sid , s.sname , sc.sscore FROM student s JOIN score sc ON s.sid = sc.sid;

SELECT s.sid , s.sname , sc.sscore FROM student s JOIN score sc ON s.sid = sc.sid GROUP BY s.sid , s.sname HAVING ROUND(AVG(sc.sscore),2)>=60; 

SELECT s.sid , s.sname , ROUND(AVG(sc.sscore),2) as avgScore FROM student s JOIN score sc ON s.sid = sc.sid GROUP BY s.sid , s.sname HAVING ROUND(AVG(sc.sscore),2)>=60; 

3、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
        -- (包括有成绩的和无成绩的)  根据成绩表来看，4、6、8学生的成绩不完整

SELECT
	s.sid,
	s.sname,
	ROUND( AVG( sc.sscore ), 2 ) AS avgScore 
FROM
	student s
	LEFT JOIN score sc ON s.sid = sc.sid 
GROUP BY
	s.sid,
	s.sname 
HAVING
	ROUND( AVG( sc.sscore ), 2 ) < 60 UNION
SELECT
	a.sid,
	a.sname,
	0 AS avgScore 
FROM
	student a 
WHERE
	a.sid NOT IN ( SELECT DISTINCT sid FROM score );

联合查询前面的是正常的三门课平均成绩不足60的  联合后面是无成绩的学生信息

4、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

SELECT s.sid , s.sname , COUNT(sc.cid) AS '总课程数', SUM(sc.sscore) AS '总分数' FROM student s LEFT JOIN score sc ON s.sid = sc.sid GROUP BY s.sid , s.sname;

SELECT
	s.sid,
	s.sname,
	COUNT( sc.cid ) AS '总课程数',
	SUM( sc.sscore ) AS '总分数' 
FROM
	student s
	LEFT JOIN score sc ON s.sid = sc.sid 
GROUP BY
	s.sid,
	s.sname;

5、查询学过"张三"老师授课的同学的信息 

根据表结构关系来看，需要通过中间表 score 建立起学生和教师之间的关系 

SELECT
	s.* 
FROM
	student s
	LEFT JOIN score sc ON s.sid = sc.sid 
WHERE
	sc.cid IN (
SELECT
	c.cid 
FROM
	course c 
WHERE
	c.tid = ( SELECT t.tid FROM teacher t WHERE t.tname = '张三' ));

清空表所有数据(慎用)

truncate table 表名;