MySQL 50題

11552 ワード
MySQL mysql
MySQL 50題
表の紹介
本文では4枚の表を用いた:学生表、カリキュラム表、教師表、成績表
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:スコア
データベースの作成
この文書では、MySQL 50というライブラリ名のデータベースを作成しました.
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 a.* FROM student a 
JOIN sc b ON a.s_id=b.s_id
JOIN course c ON b.c_id=c.c_id
JOIN teacher d ON c.t_id=d.t_id AND d.t_name="  ";

6.習ったことがあるか、習ったことがあるか、習ったことがあるかの学生情報(氏名、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(*)

9.少なくとも1つのカリキュラムが学号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(*) AS "   " 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;

16.各科の成績別に並べ替え、ランキングを表示する(並列、無間隔)
SELECT c_id,s_id,score,rank() over (PARTITION BY c_id ORDER BY score DESC) "  "FROM sc ;

17.学生の総成績を調べて順位をつける
SELECT s_id AS "ID",SUM(score) AS "  ",rank()over(ORDER BY SUM(score) DESC) "  "  
FROM sc GROUP BY s_id ;

18.異なる先生が教えてくれた異なる課程の平均点の高さから低さまでの表示を調べる(結果:教師番号、教師名、課程名、課程平均点を表示する)
SELECT a.c_id,c.t_name,b.c_name,AVG(a.score)AS "   " FROM sc a 
JOIN course b ON a.c_id=b.c_id 
JOIN teacher c ON b.t_id=c.t_id
GROUP BY a.c_id ORDER BY AVG(a.score) DESC;

19.全課程の成績2位から3位までの学生情報及び当該課程番号、成績、順位を調べる
SELECT c.*,d.c_id,d.score,d.r AS "  "
FROM student AS c,(SELECT * FROM 
(SELECT * ,dense_rank() over(PARTITION BY c_id ORDER BY score DESC)r FROM sc)a
WHERE r BETWEEN 2 AND 3)d 
WHERE d.s_id=c.s_id;

20.各科の成績を集計する各点数セグメントの人数:課程番号、課程名、[100-85],[85-70],[70-60],[0-60]および占める割合
SELECT e.c_name,a.c_id,a.c1 AS"100-85   ",
a.c1/6 AS "100-85      ",b.c2 AS "85-70   ",
b.c2/6 AS "85-70     ",c.c3 AS "70-60   ",
c.c3/6 AS "70-60     ",d.c4 AS "60     ",
d.c4/6 AS "60       " 
FROM (SELECT c_id,COUNT(*) c1 FROM sc WHERE score<60 GROUP BY c_id)a
LEFT JOIN (SELECT c_id,COUNT(*) c2 FROM sc WHERE score>= 60 AND score<70 GROUP BY c_id)b ON a.c_id=b.c_id
LEFT JOIN (SELECT c_id,COUNT(*) c3 FROM sc WHERE score>= 70 AND score<85 GROUP BY c_id)c ON a.c_id=c.c_id
LEFT JOIN (SELECT c_id,COUNT(*) c4 FROM sc WHERE score>= 85  GROUP BY c_id)d ON a.c_id=d.c_id
LEFT JOIN course AS e ON a.c_id=e.c_id;

21.学生の平均成績と順位を調べる(順位は並列、間隔がある)
SELECT s_id,AVG(score),rank() over(ORDER BY AVG(score) DESC) "  "FROM sc GROUP BY s_id ;

22.各科の成績上位3位の記録を調べる(順位は並列、間隔がある)
SELECT s_id,c_id,score,r AS "  " FROM
(SELECT *,rank() over(PARTITION BY c_id ORDER BY score DESC)r FROM sc) a WHERE r<4;

23.コースごとに選択された学生数を照会する
SELECT c_id,COUNT(*) AS "  " FROM sc GROUP BY c_id;

24.2つのコースしかないすべての学生の学号と名前を調べる
SELECT  a.s_id,a.s_name FROM student AS a,sc b WHERE a.s_id=b.s_id GROUP BY b.s_id HAVING COUNT(b.c_id)=2;

25.男子、女子の人数を調べる
SELECT s_sex,COUNT(*) AS "  " FROM student GROUP BY s_sex;

26.名前に「風」が入っている学生情報を調べる
SELECT * FROM student WHERE s_name LIKE"% %";

27.同名同性学生の情報を調べ、同名人数を統計する
同じ名前の学生を入力していないテーブルを作成し、データを検索できません
SELECT a.s_name,a.s_sex,COUNT(*) FROM student a 
JOIN student b ON a.s_name=b.s_name AND a.s_id!=b.s_id 
AND a.s_sex=b.s_sex GROUP BY a.s_name,a.s_sex;

28.1990年生まれの学生の情報を調べる
SELECT * FROM student WHERE YEAR(s_age)="1990" ;

29.各コースの平均成績を照会し、降順に並べ替える
SELECT c_id,AVG(score) FROM sc GROUP BY c_id ORDER BY AVG(score) DESC;

30.平均成績が85以上の学生情報の照会
SELECT a.*,AVG(b.score) AS "   " FROM student a,sc b 
WHERE a.s_id =b.s_id GROUP BY s_id HAVING AVG(score)>85;

31.「数学」の授業点数が60未満の学生の名前、点数を調べる
SELECT a.s_name AS "  ",b.score AS "  " FROM student a,sc b 
WHERE a.s_id=b.s_id AND c_id=(SELECT c_id FROM course WHERE c_name="  ") 
AND score<60;

32.学生の課程及び点数状況を調べ、結果は学生名、各科の課程名(課程名の下に成績を表示する)
SELECT DISTINCT a.s_id,e.s_name AS "  ",b.score AS "  ",
c.score AS " ",d.score AS "  " 
FROM sc a  LEFT JOIN sc b ON a.s_id=b.s_id  AND b.c_id=1 
LEFT JOIN sc c ON a.s_id=c.s_id AND c.c_id=2 
LEFT JOIN sc d ON a.s_id=d.s_id AND d.c_id=3 
LEFT JOIN student e ON a.s_id=e.s_id;

33.どの課程の成績が70点以上の学生の名前、課程名、成績を照会する
SELECT a.s_name,b.c_name,c.score FROM sc c 
JOIN course b ON c.c_id=b.c_id AND c.score>70 
JOIN student a ON a.s_id=c.s_id ;

34.不合格の課程の学生の名前、課程名、成績を調べる
SELECT a.s_name,b.c_name,c.score FROM sc c 
JOIN course b ON c.c_id=b.c_id AND c.score<60 
JOIN student a ON a.s_id=c.s_id ;

35.課程番号が01で課程成績が79点以上の学生の学号と名前を照会する
SELECT a.s_id,a.s_name FROM student a,sc b WHERE a.s_id=b.s_id AND b.score>79 AND b.c_id=1;

36.各課程の学生数を求め、課程名、人数を表示する
SELECT a.c_name AS "   ",COUNT(*) AS "  " FROM course a,sc b WHERE a.c_id=b.c_id GROUP BY b.c_id;

37.「張三」先生が受けた授業を選択した学生の中で、成績が最も高い学生情報とその成績(学生1人のみ)を調べる
SELECT d.*,a.score AS "  " FROM sc a 
JOIN course b ON a.c_id=b.c_id 
JOIN teacher c ON b.t_id=c.t_id AND c.t_name="  " 
JOIN student d ON a.s_id=d.s_id ORDER BY score DESC LIMIT 1;

38.課程番号が異なるが成績が同じ学生の学生番号、課程番号、学生成績を照会する
 SELECT DISTINCT (a.s_id),a.c_id,a.score FROM sc a 
 JOIN sc b ON a.s_id=b.s_id  AND a.c_id!=b.c_id AND a.score=b.score;

39.各科目の成績が最もよい上位2名を検索する(順位は重複可能で、異なる成績を取って、結果が課程番号、学番、成績、順位であることを示す)
SELECT c_id AS "    ",s_id AS "  ",score AS "  ",
a AS "  " FROM (SELECT c_id,s_id,score,dense_rank() over(PARTITION BY c_id ORDER BY score DESC)a 
FROM sc)b WHERE a<3;

40.少なくとも2つのコースを選択した学生番号、名前、コース数を問い合わせる
 SELECT b.s_name,b.s_id,COUNT(*) AS "      " 
 FROM sc a,student b WHERE a.s_id=b.s_id GROUP BY a.s_id 
 HAVING COUNT(a.c_id)>=2;

41.全課程を選択した学生の氏名、学号を照会する
 SELECT b.s_name,b.s_id FROM sc a,student b 
 WHERE a.s_id=b.s_id GROUP BY a.s_id 
 HAVING COUNT(*)=(SELECT COUNT(*) FROM course);

42.学生の名前、学生番号、年齢を調べる
SELECT  s_name,s_id,TIMESTAMPDIFF(YEAR, s_age, CURDATE()) AS "  " FROM student;

43.今週の誕生日の学生情報を調べる
 SELECT * FROM student WHERE WEEKOFYEAR(s_age) = WEEKOFYEAR(CURDATE());

44.今月の誕生日の学生情報を調べる
 SELECT * FROM student WHERE MONTH(s_age) = MONTH(CURDATE());

45.12月に誕生日を迎えた男子学生の情報(学号、氏名、性別、年齢)を調べる
SELECT  s_id,s_name,s_sex,TIMESTAMPDIFF(YEAR, s_age, CURDATE()) 
AS "  " FROM student WHERE s_sex=" " 
AND SUBSTR(s_age,6,7)=12;
Mysqlマルチライブラリ接続
Linux下Shellプログラミング実装HadoopベースETL(Sqoopパッケージ編)