MySQL 50題

表的介紹

本文中共用到了四張表：學生表、課程表、教師表、成績表

1.學生表

student(s_id,s_name,s_age,s_sex)
s_id : 學?編號、s_name: 學?姓名、s_age: 出?年?、s_sex: 學?性別

2.課程表

course(c_id,c_name,t_id)?
c_id: 課程編號、c_name: 課程名稱、t_id: 教師編號

3.教師表

teacher(t_id,t_name)
t_id: 教師編號、t_name: 教師姓名

4.成績表

sc(s_id,c_id,score)
s_id: 學生編號、c_id: 課程編號、score: 分數

創建資料庫

本文中我創建了庫名為MySQL50的資料庫

CREATE DATABASE IF NOT EXISTS MySQL50;

使用該資料庫

避免創建表時創建錯位置，所以先使用該資料庫

USE MySQL50;

創建表

1.學生表

CREATE TABLE student(
s_id INT PRIMARY KEY AUTO_INCREMENT, 
s_name VARCHAR(10),
s_age DATETIME,
s_sex VARCHAR(10)
);

insert into student(s_name,s_age,s_sex) values 
( '趙雷' , '1990-01-01' , '男'),
( '錢電' , '1990-12-21' , '男'),
( '孫風' , '1990-04-10' , '男'),
( '李云' , '1990-04-06' , '男'),
( '周梅' , '1991-12-01' , '女'),
( '吳蘭' , '1992-03-01' , '女'),
( '鄭竹' , '1989-07-01' , '女'),
( '王菊' , '1990-01-20' , '女');

2.課程表

CREATE TABLE course(
c_id INT PRIMARY KEY AUTO_INCREMENT,
c_name VARCHAR(10),
t_id INT(10)
);

INSERT INTO course(c_name,t_id) VALUES  
('數學' , '01'),
('語文' , '02'),
('英語' , '03');

3.教師表

CREATE TABLE teacher(
t_id INT PRIMARY KEY AUTO_INCREMENT,
t_name VARCHAR(10)
);

INSERT INTO teacher(t_name) VALUES
('張三'),
('李四'),
('王五');

4.成績表

CREATE TABLE sc(
s_id INT(10),
c_id INT(10),
score INT(10)
);

INSERT INTO sc(s_id,c_id,score) VALUES
('01' , '01' , 80),
('01' , '02' , 90),
('01' , '03' , 99),
('02' , '01' , 70),
('02' , '02' , 60),
('02' , '03' , 80),
('03' , '01' , 80),
('03' , '02' , 80),
('03' , '03' , 80),
('04' , '01' , 50),
('04' , '02' , 30),
('04' , '03' , 20),
('05' , '01' , 76),
('05' , '02' , 87),
('06' , '01' , 31),
('06' , '03' , 34),
('07' , '02' , 89),
('07' , '03' , 98);

1.查詢課程1比課程2成績高的學生的資訊以及課程1課程2成績

SELECT a1.s_id,a1.s_name,a1.s_sex,b1.s1 AS "課程1成績",b1.s2 AS "課程2成績" FROM student a1, (
SELECT a.s_id AS s_id,a.score AS s1,b.score AS s2 FROM sc a,sc b
WHERE a.c_id=1 AND b.c_id=2 AND a.s_id=b.s_id
)b1  
WHERE s1>s2 AND a1.s_id=b1.s_id;								

2.找出平均分大于60分的學生的學號和平均分

select s_id,avg(score) AS "平均分" from sc group by s_id having avg(score)>60;

3.查詢每個學生的課程總數及所有課程的總成績

SELECT s_id,COUNT(*) AS "課程總數",SUM(score) AS "總分"FROM sc GROUP BY s_id;	

4.查詢李姓老師的人數

SELECT COUNT(*)AS "人數" FROM teacher WHERE t_name LIKE "李%";		

5.查詢學過張三老師的學生的資訊

select * from student where s_id in(
select s_id from sc where c_id=(
select c_id from course where t_id=(
select t_id from teacher where t_name="張三")
) );

6.查詢學過即學過課程1有學過課程2的學生資訊（姓名，ID）

SELECT c.s_name AS "姓名",c.s_id AS "ID" FROM student AS c,
(SELECT a.s_id AS s_id FROM (SELECT * FROM sc WHERE c_id=1) a ,(SELECT * FROM sc WHERE c_id=2) b 
WHERE a.s_id=b.s_id)d 
WHERE c.s_id=d.s_id;

7.查詢學過課程1但沒有學過課程2的學生資訊（姓名，ID）

SELECT c.s_name AS "姓名",c.s_id  AS "ID"FROM student AS c,
(SELECT  DISTINCT(a.s_id) AS s_id FROM (SELECT * FROM sc WHERE c_id=1)a,(SELECT * FROM sc WHERE s_id NOT IN (SELECT s_id FROM sc WHERE c_id=2))b WHERE a.s_id =b.s_id)d
WHERE c.`s_id`=d.s_id;

8.查詢沒有學全所有課程的學生的資訊（姓名，ID）

SELECT a.s_name AS "姓名",b.s_id AS "ID" FROM student AS a,sc AS b 
WHERE a.s_id=b.s_id GROUP BY a.s_id HAVING COUNT(*)<(SELECT COUNT(DISTINCT c_name)AS a FROM course);

9.查詢至少有一門課程與學號為1的學生相同的學生資訊（姓名，ID）

SELECT  a.s_name AS "姓名",a.s_id AS "ID" FROM student AS a,
(SELECT DISTINCT(s_id) FROM sc WHERE c_id IN(SELECT c_id FROM sc WHERE s_id=1))b WHERE a.s_id=b.s_id;

10.查詢與1號同學所修課程數相同的同學學號，課程數

SELECT s_id,COUNT(*) FROM sc WHERE c_id  IN(SELECT c_id FROM sc WHERE s_id=1)  GROUP BY s_id HAVING COUNT(*)=(SELECT COUNT(*) FROM sc WHERE s_id=1);

11.查詢沒學過張三老師課的學生的學號

SELECT DISTINCT s_id FROM sc WHERE s_id NOT IN( SELECT s_id FROM sc WHERE c_id=(SELECT c_id FROM course WHERE t_id=(SELECT t_id FROM teacher WHERE t_name="張三") ) );

12.查詢成績不及格的課程數大于2的學生的學號和課程數

SELECT s_id,COUNT(*) FROM sc WHERE sc<60 GROUP BY s_id HAVING COUNT(*)>=2;

13.查詢課程1小于60分的學生的學號，成績按降序排序

SELECT s_id,score FROM sc WHERE score<60  AND c_id=1 ORDER BY score DESC;

14.按平均成績高低顯示所有學生所有課程的成績及平均成績

SELECT a.*,b.avg FROM  sc AS a ,(SELECT s_id,AVG(score) AS AVG FROM sc GROUP BY s_id )b WHERE a.s_id =b.s_id ORDER BY b.avg DESC;

15.查詢各科成績最高分、最低分和平均分

select c_id,max(score) as "最高分",min(score) as "最低分",avg(score) as "平均分" from sc  group by c_id;

未完待續！