MySQL 基礎#
今日目標：
- MySQL のインストールと基本操作の完了
- SQL を使用してデータベースに対して CRUD 操作ができる
- SQL を使用してテーブルに対して CRUD 操作ができる
- SQL を使用してデータに対して CRUD 操作ができる
mysql にログイン#
mysql -uroot -p 123456
1. データベース関連の概念#
以前、私たちがシステムを作成する際、データの永続的な保存にはファイルストレージを使用していました。ファイルに保存することで、システムを閉じてもデータが失われない効果を得ることができますが、もちろんファイルストレージにも欠点があります。
ファイルに以下のデータを保存すると仮定します：
名前 年齢 性別 住所
張三 23 男 北京西三旗
李四 24 女 北京西二旗
王五 25 男 西安ソフトウェア新城
今、李四の性別データを男に変更したい場合、現在学んでいる IO 技術を使用して、すべてのデータをメモリに読み込み、変更してからそのファイルに保存することができます。この方法で操作すると大きな問題が発生します。現在は 3 つのデータしかありませんが、ファイルに 1T のデータが保存されている場合、メモリに全く収まりません。
したがって、データを永続的に保存できるだけでなく、上記の問題を回避できる技術が私たちのシステムで使用される必要があります。それがデータベースです。
1.1 データベース#
-
== データを保存し管理する倉庫であり、データは組織的に保存されます。==
-
データベースの英語名は DataBase、略して DB です。
データベースはデータをハードディスクに保存し、永続的な保存を実現します。それでは、どのようにして上記の問題を解決するのでしょうか？データベース管理システムを使用します。
1.2 データベース管理システム#
-
== データベースを管理する大規模なソフトウェア ==
-
英語：DataBase Management System、略して DBMS
コンピュータにデータベース管理システムをインストールすると、データベースを作成してデータを保存したり、そのシステムを使用してデータベース内のデータに対して CRUD 操作を行ったりできます。私たちが普段言う MySQL データベースは実際には MySQL データベース管理システムです。
上記の説明から、皆さんは「データベース管理システム」と「データベース」の関係を理解したはずです。それでは、一般的なデータベース管理システムにはどのようなものがあるのでしょうか？
1.3 一般的なデータベース管理システム#
次に、上記に挙げたデータベース管理システムについて簡単に紹介します：
- Oracle：有料の大規模データベース、Oracle 社の製品
- ==MySQL==：オープンソースで無料の中小規模データベース。後に Sun 社が MySQL を買収し、Sun 社は Oracle に買収されました。
- SQL Server：Microsoft 社の有料の中規模データベース。C#、.net などの言語でよく使用されます。
- PostgreSQL：オープンソースで無料の中小規模データベース
- DB2：IBM 社の大規模有料データベース製品
- SQLite：組み込み型のミニデータベース。例：Android 内蔵データベースとして
- MariaDB：オープンソースで無料の中小規模データベース
私たちのコースで学ぶのは MySQL データベース管理システムですが、PostgreSQL は一部の企業でも使用されています。この場合、皆さんは今後、私たちが学んでいない PostgreSQL データベース管理システムを会社で使用する場合はどうすればよいかと考えるかもしれませんが、その点については心配する必要はありません。以下の図を見てください：
私たちはデータベース管理システムを通じてデータベースを操作し、データベース内のデータに対して CRUD 操作を行うことができます。そして、ユーザーがデータベース管理システムと対話する方法は、プログラミング言語（SQL）を使用することで実現できます。
1.4 SQL#
- 英語：Structured Query Language、略して SQL、構造化クエリ言語
- 関係型データベースを操作するプログラミング言語
- すべての関係型データベースを操作するための統一基準を定義し、SQL を使用してすべての関係型データベース管理システムを操作できます。今後、他のデータベース管理システムを使用する場合でも、同様に SQL を使用して操作します。
- MySQL
2.1-2.4 mysql のインストール#
2.5 MySQL データモデル#
関係型データベース：
関係型データベースは関係モデルに基づいて構築されたデータベースであり、簡単に言えば、関係型データベースは相互に接続可能な複数の二次元テーブルで構成されています。
以下の図のように、注文情報表と顧客情報表はどちらも行と列を持つ二次元テーブルであり、これを関係型データベースと呼びます。
次に、関係型データベースの利点を見てみましょう：
- すべてテーブル構造を使用し、フォーマットが一貫していて、メンテナンスが容易です。
- 一般的な SQL 言語を使用して操作でき、使いやすく、複雑なクエリにも対応できます。
- 関係型データベースはすべて SQL を使用して操作できるため、使いやすいです。
- 複雑なクエリ。001 号の注文データを照会する必要がある場合、この注文は 1 号顧客の注文であり、1 号注文は李聡という顧客のものです。将来的には、1 つのテーブルで統計分析などの操作を行うこともできます。
- データはディスクに保存され、安全です。
データモデル：
上記の図のように、クライアントはデータベース管理システムを通じてデータベースを作成し、データベース内にテーブルを作成し、テーブルにデータを追加できます。作成された各データベースはディスク上の 1 つのフォルダーに対応します。たとえば、SQL 文を使用してデータベースを作成できます（データベース名は db1）：
私たちはデータベースのインストールディレクトリ内の data ディレクトリでdb1というフォルダーが追加されたのを見ることができます。したがって、MySQL では 1 つのデータベースがディスク上の 1 つのフォルダーに対応します。
1 つのデータベース内には複数のテーブルを作成できます。MySQL に自動的に付属している mysql データベースのフォルダーディレクトリに移動します：
上の図の右側のdb.frmはテーブルファイルで、db.MYDはデータファイルであり、これら 2 つのファイルを使用してデータを照会し、二次元テーブルとして表示することができます。
小結：
- MySQL では複数のデータベースを作成でき、各データベースはディスク上の 1 つのフォルダーに対応します。
- 各データベース内には複数のテーブルを作成でき、各テーブルはディスク上の 1 つの frm ファイルに対応します。
- 各テーブルは複数のデータを保存でき、データはディスク上の MYD ファイルに保存されます。
3. SQL 概説#
データモデルを理解した後、次に SQL 文を学び、SQL 文を使用してデータベース、テーブル、データに対して CRUD 操作を行います。
3.1 SQL の紹介#
- 英語：Structured Query Language、略して SQL
- 構造化クエリ言語であり、関係型データベースを操作するプログラミング言語
- すべての関係型データベースを操作するための統一基準を定義します。
- 同じ要求に対して、各データベースの操作方法にはいくつかの違いがある場合があります。これを「方言」と呼びます。
3.2 一般的な構文#
- SQL 文は単一行または複数行で記述でき、セミコロンで終了します。
show databases;
上記のように、セミコロンで終了することが完全な SQL 文です。
- MySQL データベースの SQL 文は大文字と小文字を区別しません。キーワードは大文字を使用することをお勧めします。
同じ SQL 文を以下のように書き換えても、結果を得ることができます。
Show DataBases;
-
コメント
-
単一行コメント: -- コメント内容 または #コメント内容（MySQL 特有）
注意：-- を使用して単一行コメントを追加する場合、-- の後に必ずスペースを追加する必要がありますが、# にはその要件はありません。
- 複数行コメント: /* コメント */
-- コメント内容
#コメント内容（MySQL特有）
/* コメント */
3.3 SQL の分類#
·DDL(Data Definition Language) データ定義言語、データベースオブジェクトを定義するために使用します：データベース、テーブル、列など
·DML(Data Manipulation Language) データ操作言語、データベース内のテーブルのデータに対して CRUD 操作を行うために使用します
·DQL(Data Query Language) データクエリ言語、データベース内のテーブルのレコード（データ）を照会するために使用します
·DCL(Data Control Language) データ制御言語、データベースのアクセス権限とセキュリティレベルを定義し、ユーザーを作成するために使用します
- DDL（データ定義言語）：データベースオブジェクトを定義するために使用します：データベース、テーブル、列など
DDL は簡単に言えば、データベースやテーブルを操作するために使用されます。
- DML（データ操作言語）：データベース内のテーブルのデータに対して CRUD 操作を行うために使用されます。
DML は簡単に言えば、テーブル内のデータを操作するために使用されます。
- DQL（データクエリ言語）：データベース内のテーブルのレコードを照会するために使用されます。
DQL は簡単に言えば、データを照会する操作です。データベーステーブルから必要なデータを照会します。
- DCL（データ制御言語）：データベースのアクセス権限とセキュリティレベルを定義し、ユーザーを作成するために使用されます。
DML は簡単に言えば、データベースの権限を制御するために使用されます。たとえば、特定のデータベーステーブルを特定のユーザーのみが操作できるようにするなどです。
注意：今後、最も頻繁に操作するのは
DMLとDQLです。なぜなら、開発中に最も頻繁に操作するのはデータだからです。
4. DDL：データベースの操作#
まず、DDL を学んでデータベースを操作します。データベースの操作は主にデータベースの CRUD 操作です。
4.1 照会#
すべてのデータベースを照会します。
SHOW DATABASES;
上記の文を実行すると、以下のような結果が得られます：
上記の照会結果は、MySQL がインストールされたときに自動的に付属するこれらのデータベースです。今後、これらのデータベースを操作しないでください。
4.2 データベースの作成#
- データベースを作成する：
CREATE DATABASE データベース名;
実行結果は以下のようになります：
データベースを作成する際、db1 データベースがすでに作成されているかどうかわからないため、再度 db1 という名前のデータベースを作成するとエラーが発生します。
上記のエラーを回避するために、データベースを作成する際には、存在しない場合にのみ作成するようにします。
- データベースを作成する（存在しない場合に作成）：
CREATE DATABASE IF NOT EXISTS データベース名;
実行結果は以下のようになります：
上記の結果から、db1 データベースがすでに存在していても、再度 db1 を作成してもエラーは発生せず、db2 データベースは正常に作成されます。
4.3 データベースの削除#
- データベースを削除する：
DROP DATABASE データベース名;
- データベースを削除する（存在する場合に削除）：
DROP DATABASE IF EXISTS データベース名;
実行結果は以下のようになります：
4.4 データベースの使用#
データベースが作成されたら、データベース内にテーブルを作成するためには、どのデータベースで操作するかを明確にする必要があります。この時、データベースを使用する必要があります。
- データベースを使用する：
USE データベース名;
- 現在使用しているデータベースを確認する：
SELECT DATABASE();
実行結果は以下のようになります：
5. DDL：テーブルの操作#
テーブルの操作は、テーブルに対して CRUD 操作を行うことです。
5.1 テーブルの照会#
- 現在のデータベース内のすべてのテーブル名を照会する：
SHOW TABLES;
私たちが作成したデータベースにはテーブルがないため、MySQL に自動的に付属している mysql データベースに入り、上記の文を実行して確認します。
- テーブル構造を照会する：
DESC テーブル名;
mysql データベース内の func テーブルの構造を確認するために、実行文は以下のようになります：
5.2 テーブルの作成#
- テーブルを作成する：
CREATE TABLE テーブル名 (
列名1 データ型1,
列名2 データ型2,
…
列名n データ型n
);
注意：最後の行の末尾にカンマを追加してはいけません。
テーブルを作成する文を知ったので、以下の構造のテーブルを作成します。
create table tb_user (
id int,
# varcharは文字列型を表し、括弧内は制限長です
username varchar(20),
password varchar(32)
);
実行文は以下のようになります：
5.3 データ型#
MySQL は多くのデータ型をサポートしており、3 つのカテゴリに分けることができます：
- 数値
tinyint : 小整数型，占用1バイト
int ： 大整数型，占用4バイト
eg ： age int
double ： 浮動小数点型
使用形式： 列名 double(総長,小数点以下の桁数)
eg ： score double(5,2)
- 日付
date ： 日付値。年、月、日を含む
eg ： birthday date ；
datetime ： 日付と時間の混合値。年、月、日、時、分、秒を含む
- 文字列
char ： 定長文字列。
利点：ストレージ性能が高い
欠点：スペースを無駄にする
eg ： name char(10) もし保存するデータの文字数が10未満であっても、10のスペースを占有します。
varchar ： 可変長文字列。
利点：スペースを節約
欠点：ストレージ性能が低い
eg ： name varchar(10) もし保存するデータの文字数が10未満であれば、その文字数分だけのスペースを占有します。
注意：その他のデータ型については資料の《MySQL データ型].xlsx》を参照してください。
ケーススタディ：
要求：学生テーブルを設計し、データ型、長さの合理性に注意してください。
1. 番号
2. 名前、名前は最大10文字の漢字を超えない
3. 性別、値は2種類しかないため、最大1文字の漢字
4. 誕生日、値は年月日
5. 入学成績、小数点以下2桁を保持
6. メールアドレス、最大長は64を超えない
7. 家庭の電話番号、必ずしも携帯電話番号とは限らず、-などの文字が含まれる可能性がある
8. 学生の状態（数字で表す、正常、休学、卒業...）
文の設計は以下のようになります：
create table student (
id int,
name varchar(10),
gender char(1),
birthday date,
score double(5,2),
email varchar(15),
tel varchar(15),
status tinyint
);
5.4 テーブルの削除#
- テーブルを削除する：
DROP TABLE テーブル名;
- テーブル内のデータをクリアする：truncate
形式：truncate table テーブル名
SELECT * FROM ADDRESS;
住所テーブルをクリアする：truncate table ADDRESS;
住所テーブル内のすべてのレコードを照会する：SELECT * FROM ADDRESS;
- テーブルを削除する際にテーブルが存在するかどうかを確認する：
DROP TABLE IF EXISTS テーブル名;
実行結果は以下のようになります：
5.5 テーブルの変更#
- テーブル名を変更する：
ALTER TABLE テーブル名 RENAME TO 新しいテーブル名;
-- テーブル名studentをstuに変更
alter table student rename to stu;
- 列を追加する：
ALTER TABLE テーブル名 ADD 列名 データ型;
-- stuテーブルにaddress列を追加し、そのフィールドタイプはvarchar(50)
alter table stu add address varchar(50);
- データ型を変更する：
ALTER TABLE テーブル名 MODIFY 列名 新しいデータ型;
-- stuテーブル内のaddressフィールドのタイプをchar(50)に変更
alter table stu modify address char(50);
- 列名とデータ型を変更する：
ALTER TABLE テーブル名 CHANGE 列名 新しい列名 新しいデータ型;
-- stuテーブル内のaddressフィールド名をaddrに変更し、タイプをvarchar(50)に変更
alter table stu change address addr varchar(50);
- 列を削除する：
ALTER TABLE テーブル名 DROP 列名;
-- stuテーブル内のaddrフィールドを削除
alter table stu drop addr;
6. navicat の使用#
上記の学習を通じて、コマンドラインで SQL 文を書くのが非常に不便であることがわかりました。特にテーブル作成文を書く際には、メモ帳に書いてからコマンドラインに直接コピーして実行する必要があります。それでは、私たちが使用するための便利なツールはあるのでしょうか？あります。
6.1 navicat の概要#
- Navicat for MySQL は、MySQL または MariaDB の管理と開発の理想的なソリューションです。
- この包括的なフロントエンドツールは、データベース管理、開発、メンテナンスのための直感的で強力なグラフィカルインターフェースを提供します。
- 公式サイト：http://www.navicat.com.cn
6.2 navicat のインストール#
参考：資料 \navicat インストールパッケージ \navicat_mysql_x86\navicat インストール手順.md
6.3 navicat の使用#
6.3.1 MySQL サービスへの接続を確立する#
最初のステップ：接続をクリックし、MySQL を選択します。
次のステップ：データベース接続に必要な情報を入力します。
上記の操作に問題がなければ、以下のような画面が表示されます：
6.3.2 操作#
接続が成功すると、以下のような画面が表示されます：
- テーブル構造を変更する：
以下の図の操作を通じて、テーブル構造を変更します：
テーブル設計をクリックすると、以下のような画面が表示されます。赤枠内で直接フィールド名、タイプなどの情報を変更します：
- SQL 文を作成して実行する：
以下の図のように操作すれば、SQL 文を作成して実行できます。
7. DML#
DML は主にデータに対して追加（insert）、削除（delete）、変更（update）操作を行います。
7.1 データの追加#
- 指定した列にデータを追加する：
INSERT INTO テーブル名(列名1,列名2,…) VALUES(値1,値2,…);
- すべての列にデータを追加する：
INSERT INTO テーブル名 VALUES(値1,値2,…);
- データを一括追加する：
INSERT INTO テーブル名(列名1,列名2,…) VALUES(値1,値2,…),(値1,値2,…),(値1,値2,…);
INSERT INTO テーブル名 VALUES(値1,値2,…),(値1,値2,…),(値1,値2,…);
- 練習
以下の追加、削除、変更操作が成功したかどうかを示すために、すべてのデータを照会する文を紹介します：
select * from stu;
-- 指定した列にデータを追加する
INSERT INTO stu (id, NAME) VALUES (1, '張三');
-- すべての列にデータを追加する。列名のリストは省略可能
INSERT INTO stu (id, NAME, sex, birthday, score, email, tel, STATUS) VALUES (2, '李四', '男', '1999-11-11', 88.88, '[email protected]', '13888888888', 1);
INSERT INTO stu VALUES (2, '李四', '男', '1999-11-11', 88.88, '[email protected]', '13888888888', 1);
-- データを一括追加する
INSERT INTO stu VALUES
(2, '李四', '男', '1999-11-11', 88.88, '[email protected]', '13888888888', 1),
(2, '李四', '男', '1999-11-11', 88.88, '[email protected]', '13888888888', 1),
(2, '李四', '男', '1999-11-11', 88.88, '[email protected]', '13888888888', 1);
7.2 データの変更#
- テーブルデータを変更する：
UPDATE テーブル名 SET 列名1=値1,列名2=値2,… [WHERE 条件];
注意：
- 変更文に条件を加えない場合、すべてのデータが変更されます！
- 上記の文中の中括弧は、SQL 文を書く際に省略可能であることを示しています。
-
練習
-
張三の性別を女に変更する：
update stu set sex = '女' where name = '張三';
- 張三の誕生日を 1999-12-12 に、スコアを 99.99 に変更する：
update stu set birthday = '1999-12-12', score = 99.99 where name = '張三';
- 注意：もし update 文に where 条件が加えられない場合、テーブル内のすべてのデータが変更されます！
update stu set sex = '女';
上記の文を実行した後、照会結果は以下のようになります：
7.3 データの削除#
- データを削除する：
DELETE FROM テーブル名 [WHERE 条件];
- 練習
-- 張三のレコードを削除する
delete from stu where name = '張三';
-- stuテーブル内のすべてのデータを削除する
delete from stu;
8. DQL#
以下は、黒馬プログラマーが試験問題データベースのデータを表示するページです。
ページに表示されるデータは、データベース内の試験問題テーブルに保存されており、私たちはデータベースからデータを照会してユーザーに表示する必要があります。上の図は最も基本的な照会結果ですが、データベースは多くのデータを含んでおり、すべてのデータを 1 ページに表示することはできません。ページ上にはページネーションの表示効果があります。
もちろん、上の図の難易度フィールドをクリックすると、ソート照会操作も実現できます。この例から、データベースの照会は柔軟で多様であり、具体的な要求に基づいて実現する必要があることがわかります。データベースの照会操作は最も重要な操作であるため、この部分は特に習得する必要があります。
次に、照会の完全な構文を紹介します：
SELECT
フィールドリスト
FROM
テーブルリスト
WHERE
条件リスト
GROUP BY
グループ化フィールド
HAVING
グループ化後の条件
ORDER BY
ソートフィールド
LIMIT
ページ制限
皆さんに照会文を示すために、まずテーブルといくつかのデータを準備する必要があります：
-- stuテーブルを削除する
drop table if exists stu;
-- stuテーブルを作成する
CREATE TABLE stu (
id int, -- 番号
name varchar(20), -- 名前
age int, -- 年齢
sex varchar(5), -- 性別
address varchar(100), -- 住所
math double(5,2), -- 数学の成績
english double(5,2), -- 英語の成績
hire_date date -- 入学日
);
-- データを追加する
INSERT INTO stu(id, NAME, age, sex, address, math, english, hire_date)
VALUES
(1, '馬運', 55, '男', '杭州', 66, 78, '1995-09-01'),
(2, '馬花疼', 45, '女', '深圳', 98, 87, '1998-09-01'),
(3, '馬スカ', 55, '男', '香港', 56, 77, '1999-09-02'),
(4, '柳白', 20, '女', '湖南', 76, 65, '1997-09-05'),
(5, '柳青', 20, '男', '湖南', 86, NULL, '1998-09-01'),
(6, '劉德花', 57, '男', '香港', 99, 99, '1998-09-01'),
(7, '張学右', 22, '女', '香港', 99, 99, '1998-09-01'),
(8, 'デマシア', 18, '男', '南京', 56, 65, '1994-09-02');
次に、最も基本的な照会文から学び始めましょう。
8.1 基本照会#
8.1.1 構文#
- 複数のフィールドを照会する：
SELECT フィールドリスト FROM テーブル名;
SELECT * FROM テーブル名; -- すべてのデータを照会
- 重複レコードを除去する：
SELECT DISTINCT フィールドリスト FROM テーブル名;
- エイリアスを付ける：
AS: ASも省略可能
8.1.2 練習#
- name、age の 2 列を照会する：
select name, age from stu;
- すべての列のデータを照会する。列名のリストは * で代用できます：
select * from stu;
上記の文中の * は、SQL 文を読みやすくするために使用しないことをお勧めします。フィールドリストを書くことで、各フィールドに対する説明を追加できます。
授業中、時間を短縮するために、先生は多くの場所で * を使用します。
- 住所情報を照会する：
select address from stu;
上記の文を実行した結果は以下のようになります：
上記の結果から、重複データがあることがわかります。distinctキーワードを使用して重複データを除去することもできます。
- 重複レコードを除去する：
select distinct address from stu; -- 重複する湖南と香港を削除します
- 名前、数学の成績、英語の成績を照会し、math と english にエイリアスを付ける（as キーワードは省略可能）：
select name, math as 数学の成績, english as 英語の成績 from stu;
-- asを省略できますが、元の名前とエイリアスの間には少なくとも1つのスペースを残す必要があります
select name, math 数学の成績, english 英語の成績 from stu;
8.2 条件照会#
8.2.1 構文#
SELECT フィールドリスト FROM テーブル名 WHERE 条件リスト; -- whereはifに似ています
where >=xx && <bb; -- かつ
where >=xx and <bb; -- かつ
where between xx and bb; -- xxとbbの間にある 相当するのは>=xx <=bb
# MySQLでは日付dataを直接上記の方法で範囲をフィルタリングできます
# MySQLでは等しいを判断するために==を使用できず、=を使用する必要があります
<> !=; # 等しくない
|| or; # または
-- またはの例
select * from stu where age = 18 or age = 20 or age = 22; -- 煩雑
select * from stu where age in (18,20,22); -- 簡略化
# nullデータを照会する
-- nullを照会するには=や!=を使用できず、isまたはis notを使用する必要があります
# 例
select * from stu where english is null; # 英語の空値を照会
select * from stu where english is not null; # 英語が空でないすべてのデータを照会
-- likeを使用したあいまい照会 _は単一の任意の文字 %は複数の任意の文字
# 姓が'馬'の学習者情報を照会する
select * from stu where name like '馬%'; # %は任意の文字を表し、数量に制限はありません
# 2番目の文字が'花'の学習者情報を照会する
select * from stu where name like '_花%'; # _は任意の単一の文字
# 名前に'德'が含まれる学習者情報を照会する
select * from stu where name like '%德%'; # 前方任意、後方任意、'德'を含む
- 条件
条件リストには以下の演算子を使用できます。
8.2.2 条件照会の練習#
- 年齢が 20 歳以上の学習者情報を照会する：
select * from stu where age > 20;
- 年齢が 20 歳以上の学習者情報を照会する：
select * from stu where age >= 20;
- 年齢が 20 歳以上かつ 30 歳以下の学習者情報を照会する：
select * from stu where age >= 20 && age <= 30;
select * from stu where age >= 20 and age <= 30;
上記の文中、&& と and はともに「かつ」を意味します。and を使用することをお勧めします。
また、between ... and を使用して上記の要求を実現することもできます。
select * from stu where age BETWEEN 20 and 30;
- 入学日が '1998-09-01' から '1999-09-01' の間の学習者情報を照会する：
select * from stu where hire_date BETWEEN '1998-09-01' and '1999-09-01';
- 年齢が 18 歳の学習者情報を照会する：
select * from stu where age = 18;
- 年齢が 18 歳でない学習者情報を照会する：
select * from stu where age != 18;
select * from stu where age <> 18;
- 年齢が 18 歳または 20 歳または 22 歳の学習者情報を照会する：
select * from stu where age = 18 or age = 20 or age = 22;
select * from stu where age in (18,20,22);
- 英語の成績が null の学習者情報を照会する：
null 値の比較には = や！= を使用できません。is または is not を使用する必要があります。
select * from stu where english = null; -- この文は無効です
select * from stu where english is null;
select * from stu where english is not null;
8.2.3 あいまい照会の練習#
あいまい照会には like キーワードを使用し、ワイルドカードを使用してプレースホルダーを指定できます：
（1）_ : 単一の任意の文字を表します
（2）% : 任意の数の文字を表します
- 姓が ' 馬' の学習者情報を照会する：
select * from stu where name like '馬%';
- 2 番目の文字が ' 花' の学習者情報を照会する：
select * from stu where name like '_花%';
- 名前に ' 德' が含まれる学習者情報を照会する：
select * from stu where name like '%德%';
8.3 ソート照会#
8.3.1 構文#
SELECT フィールドリスト FROM テーブル名 ORDER BY ソートフィールド名1 [ソート方式1], ソートフィールド名2 [ソート方式2] …;
上記の文中のソート方式には 2 つの種類があります：
- ASC ： 昇順に並べる （デフォルト値）
- DESC ： 降順に並べる
注意：複数のソート条件がある場合、現在の条件の値が同じである場合にのみ、次の条件に基づいてソートされます。
8.3.2 練習#
- 学生情報を年齢の昇順で照会する：
select * from stu order by age;
- 学生情報を数学の成績の降順で照会する：
select * from stu order by math desc;
- 学生情報を数学の成績の降順で照会し、数学の成績が同じ場合は英語の成績の昇順で照会する：
select * from stu order by math desc, english asc;
8.4 集約関数#
8.4.1 概念#
==1 列のデータを全体として扱い、縦に計算します。==
どう理解すればよいでしょうか？以下のような表があると仮定します。
現在、表内のすべてのデータの数学の成績の合計を求めるという要求があります。これは math フィールドに対して縦に合計を求めることです。
8.4.2 集約関数の分類#
| 関数名 | 機能 |
|---|---|
| count (列名) | 数量をカウント（一般的には null でない列を選択） |
| max (列名) | 最大値 |
| min (列名) | 最小値 |
| sum (列名) | 合計 |
| avg (列名) | 平均値 |
8.4.3 集約関数の構文#
SELECT 集約関数名(列名) FROM テーブル;
注意：null 値はすべての集約関数の計算に参加しません。
8.4.4 練習#
- クラスに何人の学生がいるかをカウントする：
select count(id) from stu;
select count(english) from stu;
上記の文は特定のフィールドに基づいてカウントしますが、そのフィールドのある行の値が null である場合、カウントされません。したがって、count (*) を使用してすべてのデータをカウントすることをお勧めします。はすべてのフィールドデータを表し、1 行のすべてのデータが null であることはないため、count () を使用することをお勧めします。
select count(*) from stu; # すべてのデータをカウント
- 数学の成績の最高点を照会する：
select max(math) from stu; # 最高点を1つのデータとして取得します
- 数学の成績の最低点を照会する：
select min(math) from stu; # 最低点を1つのデータとして取得します
- 数学の成績の合計を照会する：
select sum(math) from stu; # 合計を1つのデータとして取得します
- 数学の成績の平均点を照会する：
select avg(math) from stu; # 平均点を1つのデータとして取得します
- 英語の成績の最低点を照会する：
select min(english) from stu; # null値を照会できません
8.5 グループ照会#
8.5.1 構文#
SELECT フィールドリスト FROM テーブル名 [WHERE グループ前の条件限定] GROUP BY グループ化フィールド名 [HAVING グループ後の条件フィルタリング];
注意：グループ化後、照会するフィールドは集約関数とグループ化フィールドであり、他のフィールドを照会することは無意味です。
8.5.2 練習#
- 男性と女性のそれぞれの数学の平均点を照会する：
select sex, avg(math) from stu group by sex; # 性別でグループ化
# 性別が縦に表示され、フィールドデータが横に表示されます
注意：グループ化後、照会するフィールドは集約関数とグループ化フィールドであり、他のフィールドを照会することは無意味です。
select name, sex, avg(math) from stu group by sex; -- ここでnameフィールドを照会することは無意味です
| name | sex | avg(math) |
|---|---|---|
| 馬化騰 | 女 | 91 |
| 馬云 | 男 | 72.6 |
- 男性と女性のそれぞれの数学の平均点と人数を照会する：
select sex, avg(math), count(*) from stu group by sex;
| sex | avg(math) | count(*) |
|---|---|---|
| 女 | 91 | 3 |
| 男 | 72.6 | 5 |
- 男性と女性のそれぞれの数学の平均点と人数を照会し、条件としてスコアが 70 点未満のものはグループ化に参加しない：
select sex, avg(math), count(*) from stu where math > 70 group by sex;
- 男性と女性のそれぞれの数学の平均点と人数を照会し、条件としてスコアが 70 点未満のものはグループ化に参加せず、グループ化後の人数が 2 人以上であることを要求する：
select sex, avg(math), count(*) from stu where math > 70 group by sex having count(*) > 2; # havingはwhereと同様にグループ照会で使用されます
where と having の違い：
- 実行タイミングが異なります：where はグループ化の前に制限を行い、where 条件を満たさない場合はグループ化に参加しませんが、having はグループ化後の結果をフィルタリングします。
- 判断できる条件が異なります：where は集約関数に対する判断ができませんが、having はできます。
- 実行順序： where > 集約関数 > having
8.6 ページネーション照会#
以下の図のように、皆さんは多くのウェブサイトで似たような効果を見たことがあるでしょう。例えば、京東、百度、淘宝など。ページネーション照会は、データを 1 ページずつユーザーに表示することです。ユーザーは次のページのデータを表示するためにクリックすることもできます。
次に、ページネーション照会の構文を説明します。
8.6.1 構文#
SELECT フィールドリスト FROM テーブル名 LIMIT 開始インデックス , 照会件数;
注意：上記の文中の開始インデックスは 0 から始まります。
計算式： 開始インデックス =(現在のページ番号 - 1) * 各ページに表示される件数
tips：
ページネーション照会 limit は MySQL データベースの方言です。
Oracle のページネーション照会は rownumber を使用します。
SQL Server のページネーション照会は top を使用します。
8.6.2 練習#
- 0 から始めて、3 件のデータを照会する：
select * from stu limit 0, 3; # インデックスカウントのルールは配列に似ています
- 各ページに 3 件表示し、1 ページ目のデータを照会する：
select * from stu limit 0, 3; -- 0 1 2
- 各ページに 3 件表示し、2 ページ目のデータを照会する：
select * from stu limit 3, 3; -- 3 4 5
- 各ページに 3 件表示し、3 ページ目のデータを照会する：
select * from stu limit 6, 3; -- 6 7 8
上記の練習から開始インデックスの計算式を導き出します：
開始インデックス = (現在のページ番号 - 1) * 各ページに表示される件数