sql 常见查询

创建测试表 

DROP TABLE IF EXISTS student;
CREATE TABLE student
  ( 
     sid INT NOT NULL , 
     sname varchar(32), 
     sage INT, 
     ssex varchar(8), 
    PRIMARY KEY (sid)
  ) ENGINE=InnoDB COMMENT='学生表';
DROP TABLE IF EXISTS course;
CREATE TABLE course 
  ( 
     
     cid INT NOT NULL, 
     cname varchar(32), 
     tid INT NOT NULL,
     PRIMARY KEY (cid)
  )ENGINE=InnoDB COMMENT='教师课程表';
DROP TABLE IF EXISTS sc;
CREATE TABLE sc
  ( 
     sid INT, 
     cid INT, 
     score INT 
  ) ENGINE=InnoDB COMMENT='学生课程表';
DROP TABLE IF EXISTS teacher;
CREATE TABLE teacher 
  ( 
     tid INT NOT NULL, 
     tname varchar(16) NOT NULL,
     PRIMARY KEY(tid) 
  )ENGINE=InnoDB COMMENT='教师表';

测试数据

INSERT INTO student (sid, sname, sage, ssex)
VALUES
	(1, '刘一', 12, '男'),
	(2, '钱二', 19, '女'),
	(3, '张三', 17, '男'),
	(4, '李四', 18, '女'),
	(5, '王五', 17, '男'),
	(6, '赵六', 19, '女');

INSERT INTO Teacher (tid, tname)
VALUES
	(1, '叶平'),
	(2, '贺高'),
	(3, '杨艳'),
	(4, '周磊');

INSERT INTO Course (cid, cname, tid)
VALUES
	(1, '语文', 1),
	(2, '数学', 2),
	(3, '英语', 3),
	(4, '物理', 4);

INSERT INTO SC (sid, cid, score)
VALUES
	(1, 1, 56),
	(1, 2, 78),
	(1, 3, 67),
	(1, 4, 58),
	(2, 1, 79),
	(2, 2, 81),
	(2, 3, 92),
	(2, 4, 68),
	(3, 1, 91),
	(3, 2, 47),
	(3, 3, 88),
	(3, 4, 56),
	(4, 2, 88),
	(4, 3, 90),
	(4, 4, 93),
	(5, 1, 46),
	(5, 3, 78),
	(5, 4, 53),
	(6, 1, 35),
	(6, 2, 68),
	(6, 4, 71)

1.查询"语文"课程比"数学"课程成绩低的所有学生的学号，语文学科成绩、数学课程成绩

下面这个方法可能不是最好的办法，如果您有更好的解决办法欢迎指正

SELECT
	s1.cname,
	s2.cname,
	s1.sname,
	s1.sid,
	s1.score,
	s2.score
FROM
	(
		SELECT
			sc.score,
			s.sid,
			s.sname,
			c.cname
		FROM
			course c
		LEFT JOIN sc ON sc.cid = c.cid
		LEFT JOIN student s ON sc.sid = s.sid
		WHERE
			c.cname = '语文'
	) s1
LEFT JOIN (
	SELECT
		sc.score,
		s.sid,
		s.sname,
		c.cname
	FROM
		course c
	LEFT JOIN sc ON sc.cid = c.cid
	LEFT JOIN student s ON sc.sid = s.sid
	WHERE
		c.cname = '数学'
) s2 ON s1.sid = s2.sid
WHERE
	s1.score > s2.score 

2.查询平均成绩大于60分的同学的姓名和平均成绩

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

3.查询所有同学的姓名，总成绩，选课数

SELECT
	s.sname,
	sum(sc.score) score,
	COUNT(sc.cid)
FROM
	student s,
	sc sc
WHERE
	s.sid = sc.sid
GROUP BY
	s.sname

4.查询姓杨的教师数

5.查询没有学过"叶平"老师教的课的同学的姓名

6.查询既学"语文"，又学"数学"的同学的姓名

7.查询学过"叶平"老师教过的所有课的同学的姓名，学过所有"叶平"老师教过的课的课程数与“叶平”老师教的所有课的课程数相等

8.查询"语文"课程成绩比"数学"课程成绩低的所有同学的姓名

9.查询有课程成绩小于60分的所有同学的姓名

查询张三的语文，数学，英语的成绩

未完待续