このデータベース クエリ ステートメントは、インターネット上の 50 個のデータベース クエリ練習問題の 1 つです。インターネット上のバージョンの中には、Oracle ステートメントで記述されているものもあります。ほとんどの企業では、依然として無料の MySQL データベースを使用しています。以下は、検証済みのすべての MySQL バージョンです。 テーブル名とフィールド–1. 学生リスト テストデータデータベース可視化ツールを使って演習を行うと非常に便利です。sqlyog の使用をお勧めします。ソフトウェアのアイコンはイルカです。 新しい接続にローカル アドレス、ユーザー名、パスワード、ポートを入力して、MySQL に直接接続します。 すべてのテストデータは次のとおりです。# -- テストデータを学生テーブルに挿入します 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', 'Chinese', '02'); コースに挿入VALUES('02', '数学', '01'); コースに VALUES('03', '英語', '03') を挿入します。 # -- テストデータを教師テーブルに挿入します INSERT INTO teacher VALUES('01', '张三'); INSERT INTO teacher VALUES('02' , '李四'); 教師にVALUES('03', '王五')を挿入します。 # -- スコア テーブルにテスト データを挿入します INSERT INTO sc VALUES('01', '01', 80); sc VALUES('01', '02', 90)に挿入します。 sc VALUES('01', '03', 99)に挿入します。 sc VALUES('02', '01', 70)に挿入します。 sc VALUES('02', '02', 60)に挿入します。 sc VALUES('02', '03', 80)に挿入します。 sc VALUES('03', '01', 80)に挿入します。 sc VALUES('03', '02', 80)に挿入します。 sc VALUES('03', '03', 80)に挿入します。 sc VALUES('04', '01', 50)に挿入します。 sc VALUES('04', '02', 30)に挿入します。 sc VALUES('04', '03', 20)に挿入します。 sc VALUES('05', '01', 76)に挿入します。 sc VALUES('05', '02', 87)に挿入します。 sc VALUES('06', '01', 31)に挿入します。 sc VALUES('06', '03', 34)に挿入します。 sc VALUES('07', '02', 89)に挿入します。 sc VALUES('07', '03', 98)に挿入します。 最後に、検証済みの MySQL バージョンのデータベース クエリ演習が 50 個あります。 1. コース「01」のスコアが「02」よりも高い学生の情報とコーススコアを照会します。 *から選択 (`s#` を sno1 として、`c#` を cno1 として選択し、`c#`=01 の場合にスコアを sc から取得) 左結合 (`s#` を sno2 として、`c#` を cno2 として選択し、`c#`=02 の場合に sc からスコアを選択します) b オン a.sno1 = b.sno2 ここで、a.スコア > b.スコア 1.1 「01」と「02」の両方のコースが存在する状況を照会する *から選択 (`s#` を sno1 として、`c#` を cno1 として選択し、`c#`=01 の場合にスコアを sc から取得) 左結合 (`s#` を sno2 として、`c#` を cno2 として選択し、`c#`=02 の場合に sc からスコアを選択します) b オン a.sno1 = b.sno2 sno2 が NULL ではない場合 1.2 「01」コースは存在するが、「02」コースが存在しない可能性があるケースを照会します(存在しない場合はnullとして表示されます) *から選択 (`s#` を sno1 として、`c#` を cno1 として選択し、`c#`=01 の場合にスコアを sc から取得) 左結合 (`s#` を sno2 として、`c#` を cno2 として選択し、`c#`=02 の場合に sc からスコアを選択します) b オン a.sno1 = b.sno2 1.3 コース「01」は存在しないがコース「02」が存在する状況を照会する
2. 平均点が60点以上の学生の学生ID、名前、平均点を照会する a.`s#`、b.`sname`、a.avg_score を選択 (SELECT `s#` ,AVG(score) AS avg_score FROM sc GROUP BY `s#`) AS a LEFT JOIN 学生 AS b オン a.`s#` = b.`s#` ここで、a.avg_score >=60 3. SCテーブル内の成績を含む学生情報を照会する
4. すべての学生の学生ID、名前、コースの合計数、すべてのコースの合計スコアを照会します(スコアがない場合はnullが表示されます) `s#`、sname、course_num、score_sumを選択します (SELECT `s#`, sname FROM student ) AS a 左結合 (SELECT `s#` AS sno ,COUNT(`c#`) AS course_num ,SUM(score) AS score_sum FROM sc GROUP BY sno) AS b ON a.`s#` = b.sno 4.1 成績とともに生徒の情報を確認する # 最外層で選択する場合、関数は使用できません `s#`、sname、course_num、score_sumを選択します (SELECT `s#`, sname FROM student ) AS a 左結合 (SELECT `s#` AS sno ,COUNT(`c#`) AS course_num ,SUM(score) AS score_sum FROM sc GROUP BY sno) AS b ON a.`s#` = b.sno Course_num が NULL ではない場合 5. 姓が「Li」の教師の数を照会する
6. 教師「張三」が教えるコースを受講した学生の情報を照会する # 張三先生はNo.01です。SELECT * FROM student WHERE `s#` IN (`s#` を sc から選択し、`c#` を = にします。 (SELECT `c#` FROM course WHERE `t#` = (SELECT `t#` FROM teacher WHERE tname='张三'))) # 7. すべてのコースを修了していない学生の情報を照会する
# 8. 学生番号「01」の学生と同じクラスに少なくとも1つのコースを持つ学生の情報を照会します。 SELECT * FROM 学生 WHERE `s#` IN (SELECT DISTINCT `s#` FROM sc WHERE `c#` IN (`c#` を sc から選択し、`s#`=01 を指定します)) かつ `s#` != 01 # 9. 学生番号「01」と同じコースを受講した他の学生の情報を照会する `s#` を選択 (scから*を選択 左結合 (`c#` を cno として sc から選択し、`s#` =01 を指定します) オンsc.`c#` = a.cno) AS b GROUP BY `s#` HAVING COUNT(b.`s#`) = (SELECT COUNT(`c#`) AS cno FROM sc WHERE `s#` =01) # 10. 教師「張三」が教えるコースを受講したことがない生徒の名前を照会する # 張三は01 # 01 先生は数学を教え、C#は02 SELECT * FROM student WHERE `s#` NOT IN (SELECT DISTINCT `s#` FROM sc WHERE `c#` IN (SELECT `c#` FROM course WHERE `t#` IN (SELECT `t#` FROM teacher WHERE tname = '张三'))) # 11. 2つ以上のコースで不合格になった学生の学生ID、名前、平均点を照会する `s#`、sname、avg_score を選択 (SELECT `s#`, sname FROM student WHERE `s#` IN (a.`s#` から選択 (SELECT `s#`,COUNT(`c#`) AS num FROM sc WHERE score <60 GROUP BY `s#`) a ここで、num >= 2)) AS b 左結合 (SELECT `s#` AS sno ,AVG(score) AS avg_score FROM sc GROUP BY `s#`) AS c オン b.`s#` = c.sno # 12. スコアが60未満のコース「01」の学生情報をスコアの降順で並べ替えて取得します。 `s#`、sname、score から選択 学生AS 左結合 (`s#` を sno,`c#`,score として sc から選択し、`c#`= 01 かつ score <60 の場合)b ON a.`s#` = b.sno スコアがNULLではない場合 スコア順で並べ替え # 13. すべての学生のすべてのコースの成績と平均成績を平均成績順に高い順から低い順に表示します
# 14. 各科目の最高得点、最低得点、平均得点を照会します。 SELECT DISTINCT a.`c#`,cname,最高スコア,最低スコア,平均スコア,合格率,中程度の割合,良好率,優秀率 FROM sc a LEFT JOIN コース ON a.`c#`=course.`c#` LEFT JOIN (SELECT `c#`、MAX(スコア)最高スコア、MIN(スコア)最低スコア、AVG(スコア)平均スコア FROM sc GROUP BY `c#`)b ON a.`c#`=b.`c#` LEFT JOIN (SELECT `c#`, ROUND( r1 /cnt * 100, 2 ) AS 合格率 FROM (SELECT `c#`, (SUM(CASE WHEN score >=60 THEN 1 ELSE 0 END)*1.00) AS r1 、 COUNT(*) AS cnt FROM sc GROUP BY `c#`) c1) c ON a.`c#`=c.`c#` LEFT JOIN (SELECT `c#`, ROUND( r2 /cnt * 100, 2 ) AS 中レート FROM (SELECT `c#`, (SUM(CASE WHEN score >=70 AND score<80 THEN 1 ELSE 0 END)*1.00) AS r2、COUNT(*) AS cnt FROM sc GROUP BY `c#`) d1) d ON a.`c#`=d.`c#` LEFT JOIN (SELECT `c#`, ROUND( r3 /cnt * 100, 2 ) AS good/good rate FROM (SELECT `c#`, (SUM(CASE WHEN score >=80 AND score<90 THEN 1 ELSE 0 END)*1.00) AS r3、COUNT(*) AS cnt FROM sc GROUP BY `c#`) e1) e ON a.`c#`=e.`c#` LEFT JOIN (SELECT `c#`, ROUND( r4 /cnt * 100, 2 ) AS 優れたレート FROM (SELECT `c#`, (SUM(CASE WHEN score >=90 THEN 1 ELSE 0 END)*1.00) AS r4、COUNT(*) AS cnt FROM sc GROUP BY `c#`) f1) f ON a.`c#`=f.`c#` # 15. 各科目の得点順に並び替えて順位を表示します。得点が重複している場合は順位を空けておきます。 # mysqlにはrank()関数はありません # このメソッドは繰り返しても順位を維持するため、最終的な順位は人数と同じになります SELECT `s#`, `c#`, score, rank FROM (SELECT `s#`, `c#`, スコア, @currank := IF(@prevrank = スコア、@currank、@incrank) AS ランク、 @incrank := @incrank + 1、 @prevrank := スコア sc から、( @現在ランク:= 0、@前ランク:= NULL、@インクランク:= 1 を選択 ) ORDER BY スコア DESC) s # 15.1 各科目の得点で並び替えて順位を表示する。得点が重複した場合は順位を統合する # 重複したランクがある場合、ランクは 1 つだけになるため、ランクの数は減ります SELECT `s#`, `c#`, score, 場合 @prevrank = スコアの場合、@currank @prevrank := score のとき、@currank := @currank + 1 END ASランク scより、 (SELECT @currentrank := 0, @prevrank := NULL) スコア順で並べ替え # 16. 生徒の合計得点を照会し、順位付けします。合計得点が重複している場合は、順位を空のままにします。 # SELECT `s#`, sum_score, rank FROM の後にテーブルエイリアスを追加する必要はありません (SELECT `s#`, sum_score, @currank := IF(@prevrank = sum_score, @currank, @incrank) AS ランク、 @incrank := @incrank + 1、 @prevrank := 合計スコア から (SELECT `s#`, SUM(score) AS sum_score FROM sc GROUP BY `s#`) c , (SELECT @currank := 0、@prevrank := NULL、@incrank := 1) ORDER BY sum_score DESC) s # 16.1 学生の合計得点を照会し、順位付けします。合計得点が重複している場合は、欠員は発生しません。 SELECT c.*, 場合 @prevrank = c.sum_score の場合、@currank @prevrank := c.sum_score の場合、@currank := @currank + 1 END ASランク から (SELECT a.`s#`,a.sname,SUM(score) AS sum_score FROM (student AS a RIGHT JOIN sc AS b ON a.`s#` = b.`s#`) GROUP BY a.`s#` ) c 、 (SELECT @現在ランク:= 0、@前ランク:=NULL) d sum_score で並べ替え DESC # 17. 各科目の各スコア範囲の生徒数を数えます: コース番号、コース名、[100-85]、[85-70]、[70-60]、[60-0]、およびパーセンテージ a.`c#`、b.cnameを選択します。 SUM(CASE WHEN score >=85 AND score <=100 THEN 1 ELSE 0 END ) '[100-85]', SUM(CASE WHEN score >=85 AND score <=100 THEN 1 ELSE 0 END )*1.00/COUNT(*) AS '[100-85]パーセント', SUM(CASE WHEN スコア < 85 AND スコア >= 70 THEN 1 ELSE 0 END ) '(85-70]', SUM(CASE WHEN score < 85 AND score >= 70 THEN 1 ELSE 0 END )*1.00/COUNT(*) AS '(85-70]パーセント', SUM(CASE WHEN スコア < 70 AND スコア >= 60 THEN 1 ELSE 0 END ) '(70-60]', SUM(CASE WHEN score < 70 AND score >= 60 THEN 1 ELSE 0 END )*1.00/COUNT(*) AS '(85-70]パーセント', SUM(CASE WHEN score < 60 AND score >= 0 THEN 1 ELSE 0 END ) '(60-0]', SUM(CASE WHEN score < 60 AND score >= 0 THEN 1 ELSE 0 END )*1.00/COUNT(*) AS '(85-70]パーセント', COUNT(*) AS カウント sc a から左にコース b を結合し、a.`c#` = b.`c#` を結合します。 GROUP BY `c#` # 18. 各科目の成績上位3名の生徒の記録を照会する SELECT * FROM sc a WHERE (SELECT COUNT(*) FROM sc WHERE `c#`=a.`c#` AND score>a.score)<3 ORDER BY a.`c#`, a.score DESC; # 19. 各コースを選択した学生の数を問い合わせる `c#`、COUNT(`s#`) から選択 (SELECT `s#`,`c#` FROM sc ORDER BY `c#`)a GROUP BY `c#` SELECT a.`c#` 、 b.cname 、COUNT(*) AS num FROM sc a LEFT JOIN course b ON a.`c#` = b.`c#` GROUP BY a.`c#`; # 20. 2つのコースのみを受講している学生の学生IDと名前を見つける a.`s#`、a.sname、cnt から選択 学生 左結合 (SELECT `s#`,COUNT(`c#`) AS cnt FROM sc GROUP BY `s#`) b オン a.`s#`=b.`s#` ここで cnt=2 # 21. 男の子と女の子の数を問い合わせる
# 22. 名前に「风」という単語が含まれる生徒情報を照会する
# 23. 同じ名前と性別の生徒のリストを照会し、同じ名前の生徒の数を数える 学生aからa.*、b.同じ名前の人の数を選択します LEFT JOIN (SELECT sname,ssex,COUNT(*) AS 同じ名前の人数 FROM student GROUP BY sname,ssex)b a.sname=b.sname かつ a.ssex=b.ssex の場合 b. 同じ名前の人の数 > 1 # 24. 1990年生まれの学生のリストを照会する
# 25. 各コースの平均スコアを照会し、結果を平均スコアの降順で並べ替えます。平均スコアが同じ場合は、コース番号の昇順で並べ替えます。
# 26. 平均点が85以上のすべての学生の学生ID、名前、平均点を照会する c.`s#`、sname、avg_scoreを選択します (学生c 左結合 (`s#`、avg_scoreを選択 (SELECT `s#` ,ROUND(AVG(スコア),2) AS avg_score FROM sc GROUP BY `s#` ORDER BY avg_score DESC)a 平均スコア >=85 の場合) b オン c.`s#` =b.`s#`) avg_score が NULL ではない場合 # 27. コース名が「数学」で、スコアが60未満の学生の名前とスコアを照会する a.`s#`、a.sname、b.math、b.score から選択します 学生 左結合 (SELECT `s#`,`c#` AS math ,score FROM sc WHERE `c#` IN (SELECT `c#` FROM course WHERE cname = '数学') かつsc.スコア<60) b オン a.`s#`=b.`s#` b.score が NULL ではない場合 # 28. すべての学生のコースとスコアを照会します(スコアがなく、コースを選択していない学生もいます) a.`s#`、a.`sname`、a.`sage`、a.`ssex`、b.`c#`、b.score から選択します 学生 a LEFT JOIN sc b ON a.`s#` = b.`s#` 左結合コース c ON c.`c#` = b.`c#` # 29. スコアが70以上のコースの名前、コース名、スコアを照会する a.`s#`、a.`sname`、a.`sage`、a.`ssex`、b.`c#`、b.score から選択します 学生 左結合 (`s#`,`c#`,score FROM sc WHERE score >70) b ON a.`s#`=b.`s#` 左結合コースc オン c.`c#`=b.`c#` スコアがNULLではない場合 # 30. 不合格コースを確認する
# 31. コース番号が01で、コーススコアが80点を超える学生の学生IDと名前を照会します。 a.`s#`、a.sname、b.score から選択 学生 左結合 (SELECT * FROM sc WHERE `c#`='01' AND score >= 80) b オン a.`s#` = b.`s#` スコアがNULLではない場合 # 32. 各コースの学生数を調べる
# 33. 成績は重複しません。教師「張三」が担当するコースを選択した生徒の中で、最も成績が良い生徒の情報とその成績を照会します。 a.`s#`、a.`sname`、b.`c#`、b.max_score から選択します 学生 左結合 (SELECT `s#` AS sid,`c#` ,MAX(score) AS max_score FROM sc WHERE `c#` IN (SELECT `c#` FROM course WHERE `t#` IN (SELECT `t#` FROM teacher WHERE tname = '张三'))) b オン a.`s#`=b.sid max_score が NULL ではない場合 #34. 重複したスコアがある場合は、「張三」先生のコースを選択した生徒の中で最高スコアの生徒の情報とそのスコアを照会します。 *から選択 (dd.*を選択、 場合 @prevrank = dd.score の場合、@currank @prevrank := dd.score のとき、@currank := @currank + 1 END ASランク から (SELECT a.*,b.score から 学生 左結合 sc b ON a.`s#` = b.`s#` 左結合コース c ON b.`c#` = c.`c#` LEFT JOIN teacher d ON c.`t#` = d.`t#` WHERE d.tname = '张三' ) dd,(SELECT @currank := 0 , @prevrank :=NULL ) ff ORDER BY スコア DESC) AS dddddddd ここで、rank = 1; # 35. 異なるコースで同じスコアを持つ学生の学生ID、コースID、学生スコアを照会する SELECT DISTINCT a.`s#`, a.`c#`, a.score FROM sc AS a JOIN sc AS b ここで、a.`c#` != b.`c#` であり、a.score = b.score であり、a.`s#` != b.`s#` です。 ORDER BY a.`s#`、a.`c#`、a.score # 36. 各科目の成績が最も良い上位2人の生徒を見つける # この質問は質問18と同じですSELECT * FROM sc a WHERE (SELECT COUNT(*) FROM sc WHERE `c#`=a.`c#` AND score>a.score)<2 ORDER BY a.`c#`, a.score DESC; # 37. 各コースを受講している学生の数を数えます(5人以上の学生がいるコースのみカウントされます) # コース番号と選択科目数の出力が必要です。クエリ結果は、学生数の降順でソートされます。学生数が同じ場合は、コース番号の昇順でソートされます。SELECT a.`c#`, COUNT(*) AS num FROM もちろん、a LEFT JOIN sc b ON a.`c#` = b.`c#` GROUP BY a.`c#` HAVING num > 5 num,a.`c#` で順序付けします # 38. 少なくとも2つのコースを受講した学生の学生IDを取得する
# 39. すべてのコースを受講した学生の情報を照会する *から選択 (SELECT `s#`,COUNT(*) AS num FROM sc GROUP BY `s#` ) b WHERE num = (SELECT COUNT(*) FROM course) # 40. 各生徒の年齢を学年のみで照会する
# 41. 今週誕生日の生徒を照会する *から選択 (SELECT * 、WEEK(sage)、MONTH(sage)、DAY(sage)、 WEEK(STR_TO_DATE(CONCAT_WS(',',YEAR(NOW()),MONTH(sage),DAY(sage)),'%y,%m,%d')) AS w FROM student) a ここで、w = WEEK(NOW()) # 42. 来週誕生日の生徒を照会する *から選択 (SELECT * 、WEEK(sage)、MONTH(sage)、DAY(sage)、WEEK(NOW())、 WEEK(STR_TO_DATE(CONCAT_WS(',',YEAR(NOW()),MONTH(sage),DAY(sage)),'%y,%m,%d')) AS w FROM student) a ここで、w + 2 = WEEK(NOW()) # 43. 今月誕生日の生徒を検索する SELECT * 、MONTH(sage)、MONTH(NOW()) FROM 学生 WHERE MONTH(sage) = MONTH(NOW()) # 44. 来月誕生日を迎える生徒を照会する SELECT * , MONTH(sage),MONTH(NOW()) FROM 学生 ここで、MONTH(sage) = MONTH(NOW()) + 1 最も包括的な 50 個の MySQL データベース クエリ演習に関するこの記事はこれで終わりです。より関連性の高い MySQL データベース クエリ コンテンツについては、123WORDPRESS.COM で以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後も 123WORDPRESS.COM を応援していただければ幸いです。 以下もご興味があるかもしれません:
|
<<: Rabbitmq heartbea ハートビート検出メカニズムの原理の分析
>>: HTML テーブルタグチュートリアル (8): 背景画像属性 BACKGROUND
私は長い間この問題に悩まされていましたが、検索してみたところ、実際にこの問題を解決した人がいることが...
バックエンドは thinkphp3.2.3 フレームワークを使用します。他の言語を使用している場合は...
フォーム内の読み取り専用および無効な属性1. 読み取り専用:サーバーは、ユーザーがデータを変更するこ...
Linux システム管理者にとって、サービスがポートに正しくバインドされているか、またはポートをリッ...
FlashFXPのダウンロードアドレスは、https://www.jb51.net/softs/95...
場合によっては、ジャンプを完了するために href の代わりにハイパーリンク <a> を...
私は 1 年以上 Java Web 開発に携わっており、HTML または JSP ページの作成は避け...
1.これは1. 誰が誰に電話をかけますか?例: 関数foo(){ console.log(&quo...
Gird レイアウトは Flex レイアウトといくつかの類似点があり、どちらもコンテナーの内部項目を...
目次複数テーブル結合の基本構文クロス結合と直積現象クロスコネクトデカルト積現象内部結合外部結合左外部...
解凍したフォルダ C:\web\mysql-8.0.11 を開き、フォルダ内に my.ini 構成フ...
この記事では、CSS3 を使用した Web サイトの商品表示の効果を紹介し、皆さんと共有します。詳細...
非常に便利な機能group_concat() について、マニュアルには次のように記載されています: ...
目次過去1週間の時間を取得する過去1か月の時間を取得する過去3か月分を取得新しい Date() と ...
目次概要1. jsの位置づけを明確に理解する2. 明確な学習パス3. 自己規律と粘り強さ4. 練習し...