データベースの学習 3 SQL言語

この記事は Google 翻訳を使用して処理されました

SQL Series

データベース学習 1 データベース入門: https://blog.yexca.net/ja/archives/86
データベース学習 2 リレーショナルモデル: https://blog.yexca.net/archives/ja/87
データベースの学習 3 SQL言語: 本文

以下のはまだ翻訳しないので、中国語（簡体字）のリンクだよ

数据库学习四关系数据库理论: https://blog.yexca.net/archives/89
数据库学习五范式: https://blog.yexca.net/archives/90
数据库学习六数据库设计: https://blog.yexca.net/archives/91
数据库学习七数据库的控制功能: https://blog.yexca.net/archives/92

SQL言語の分類

DDL（データ定義言語）

データベースシステムでは、データベース内の各データベース、テーブル、ビュー、インデックスなどがデータベースオブジェクトです。データベースオブジェクトを作成または削除するには、SQL 言語を使用します。 DDL には CREATE、ALTER、DROP などが含まれます。

DML（データ操作言語）

DML は、INSERT、DELETE、UPDATE など、データベース内のデータを追加、変更、削除するために使用されるステートメントを指します。

DQL (データクエリ言語)

クエリはデータベースの基本機能であり、クエリ操作は SQL データクエリ言語を通じて実装されます。たとえば、テーブルの内容を照会するにはSELECTを使用します。

DCL（データ制御言語）

DCL には、データベースオブジェクトの権限管理やトランザクション管理などが含まれます。

(I) DDLデータ定義言語

1. データベースを作成する

1

create database <データベース名>

2。基本テーブルを作成します

1
2
3
4
5
6
7


create table <テーブル名>
(
    <列名> <データ型> [列レベルの整合性制約],
    [···,]
    [テーブルレベルの整合性制約,]
    [···]
)

<1> データタイプ

INT / INTEGER : 整数
FLOAT(n): 少なくともn桁の精度を持つ浮動小数点数
NUMERIC(p, d) / DECIMAL(p, d) / DEC(p, d): p桁（符号と小数点を除く）のd桁の固定小数点数小数点以下の数字
CHAR(n): 長さnの固定長文字列
DATETIME: 日付と時刻の型

1
2
3
4
5
6


-- 例えば
create table test
(
    f1 char(10),
    f2 int
)

<2> ラインレベルの整合性の制約

not null: 空ではない
unique: ユニーク
not null unique: 空ではなくユニーク
default: デフォルト値を定義する

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


-- 例えば
create table test
(
    f1 int not null, 
    -- この列は空にできません
    
    f2 int unique, 
    -- この列の値は空でも構いませんが、各行は異なる必要があります
    
    f3 int not null unique, 
    -- 列は空ではなく、各行は異なります
    
    f4 char(5) default 'nashi' 
    -- 新しく追加された行に値が割り当てられていない場合、この列はデフォルトで「nashi」になります。
)

<3> テーブルレベルの整合性の制約

名前	条項	通信の完全性
主キー句	PRIMARY KEY	エンティティの整合性
チェック条項	CHECK	ユーザー定義の整合性
外部キー句	FOREIGN KEY	参照整合性

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20


-- 例えば
create table student
(
    sid int,
    primary key(sid) -- not null unique
)
-- または
create table student
(
    sid int primary key
)

create table course
(
    cid int,
    sid int,
    score int,
    check(score >= 0 and score <= 100),
    foreign key(sid) references student(sid)
)

整合性の検査

アサート（通常は推奨されません）

1
2
3
4


CREATE ASSERTION <アサーション名> <チェック条項>  
-- チェック句は、where 句の式に似ています。
-- アサーション名を削除
DROP ASSERTION <断言名>;

トリガー

テーブルが追加、削除、または変更されると、トリガー内の条件がチェックされます。条件が満たされている場合はトリガー内のアクションが実行され、満たされていない場合はアクションは実行されません。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


CREATE TRIGGER <トリガー名>           
-- トリガー イベントが発生するたびに、トリガーがアクティブになります。
{BEFORE|AFTER} <トリガーイベント> ON <テーブル名> 
-- トリガー イベントの実行前または実行後にトリガーがアクティブ化されるかどうかを示します。
REFERERCING NEW|OLD ROW AS <変数>  
-- REFERENCINGは参照される変数を示す
FOR EACH{ROW|STATEMENT}            
-- トリガーの種類を定義し、アクション本体の実行頻度を指定します。
[WHEN <トリガーイベント>] <トリガーアクション本体>      
-- トリガーアクションはトリガー条件が真の場合にのみ実行されます
 
-- トリガーの削除
DROP TRIGGER <トリガー名> ON <テーブル名>

3。基本的なテーブル構造を変更します

新しい列を追加する

1
2
3
4


alter table <テーブル名> add <列名> <タイプ>

-- 例えば
alter table test add f3 datetime

列を変更する*

1
2
3
4
5
6
7


alter table <テーブル名> alter column <列名> <新しいタイプ>
-- MySQL
-- alter table <テーブル名> modify <列名> <新しいタイプ>

-- 例えば
alter table test alter column f3 float
-- alter table test modify f3 float

列の削除

1
2
3
4
5
6
7


alter table <テーブル名> drop column <列名>
-- MySQL
-- alter table <テーブル名> drop <列名>

-- 例えば
alter table test drop column f3
-- alter table test drop f3

4。基本テーブルを削除します

1
2
3
4


drop table <テーブル名>

-- 例えば
drop table test

(二)、DML データ操作言語

次のコンテンツの例は、これらのテーブルに基づいています

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34


-- 注: 理解しやすいように中国語を使用しています。実際の開発では英語を使用してください。
-- 学生テーブル
create table student
(
    学号 char(5) not null unique,
    姓名 char(8),
    性别 char(2),
    出生日期 datetime,
    班号 char(5)
)
-- 成績テーブル
create table score
(
    学号 char(5),
    课程号 char(5),
    分数 int
)
-- 教師テーブル
create table teacher
(
    教师编号 char(5) not null unique,
    姓名 char(8),
    性别 char(2),
    出生日期 datetime,
    职称 char(6),
    系别 char(10)
)
-- コーススケジュール
create table course
(
    课程号 char(5) not null unique,
    课程名 char(20),
    任课教师编号 char(5)
)

1. INSERT ステートメントを挿入

SQL基本テーブルにデータを挿入する方法は2つあります。タプル値を直接挿入するか、クエリの結果値を挿入するかです。

タプル値を直接挿入する

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


insert into テーブル名[(列名の順序)] values (タプル値)[, ···]
-- 或者
insert into テーブル名[(列名の順序)]
(table (タプル値)[, (タプル値), ···])

-- 例如
insert into student
values 
('108', '张三', '男', '2000-01-01', '09033'),
('109', '李四', '男', '2000-05-05', '09034')
-- 指定した列を挿入する
insert into student(学号，姓名，性别，出生日期)
values (110, '王五', '男', '2000-04-18')

クエリの結果値を挿入する

1
2
3
4
5


insert into テーブル名[(列名の順序)] select <クエリステートメント>

-- 例えば
insert into student
select 111, '赵六', '男', '2000-03-19', '09035'

2. DELETE 削除ステートメント

1
2
3
4
5
6
7


delete from <テーブル名> [where <条件式>]

-- 例えば
delete from student -- テーブル全体を削除する
-- 生徒番号111のタプルを削除します
delete from student
where 学号 = 111

3. UPDATE ステートメントを修正

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


update <テーブル名>
set <列名> = <価値表現>[, ···]
[where <条件式>]

-- 例えば
update student
set 班号 = '09058'
where 学号 = 110
-- または
update student
set 班号 = '09057', 性别 = '女'
where 学号 = 110 and 班号 = '09058'

(三)、DQL データクエリ言語

SQL データクエリ機能は SELECT 文を通じて実装されます。完全な構文は次のとおりです。

1
2
3
4
5
6


select <対象テーブルの列名または列式のシーケンス>
from <リレーションテーブルシーケンス>
[where <行条件式>]
[group by <列名の順序>]
[having <グループ条件式>]
[order by <列名> [asc|desc]]

1. 投影クエリ

クエリテーブル内の任意の列を選択するには、SELECT コマンドを使用します。複数の列がある場合は、カンマ , を使用して区切ります。 FROM句に複数のリレーショナルテーブルがある場合、各テーブルの名前を記述する必要があります。

1
2
3
4
5
6
7
8
9


-- 例えば
select 1 + 1
select 学号 * 100 from student
-- 学生IDとスコアを照会する
select student.学号, score.分数
from student, score
-- すべての列をクエリする
select *
from student

重複行を削除するには DISTINCT を使用します

1
2
3


-- 重複行を削除し、選択したコースを照会する
select distinct 课程号
from score

クエリ結果の列名をカスタマイズするには AS を使用します

1
2
3


-- 例えば
select 学号 as 'sno'
from student

2. クエリを選択

選択クエリは、クエリ条件を指定し、ソーステーブルからクエリ条件を満たすレコードのみを抽出または表示します。

選択クエリでは通常、次の種類の操作が使用されます。

オペレーター	コード	説明する
セットメンバーシップ演算子	INNOT IN	セット内セットに含まれない
文字列マッチング演算子	LIKE	`_` および `%` を使用した単一または複数の文字の一致
NULL比較演算子	IS NULLIS NOT NULL	空の空にすることはできません
算術演算子	>>=«br /><==<>	より大きいより大きいか等しい未満以下等しい等しくない

<1> 条件式の比較演算

1
2
3
4
5
6


--- スコアが60～80のレコードを検索する
select *
from score
where 分数 between 60 and 80
-- 60と80を含めるにはbetweenを使用します。つまり[60, 80]です。
-- スコア >= 60 かつスコア <= 80 の場合と同等

<2> 文字列マッチング操作

1

where [not] like '一致する文字列' [escape '一致する文字列']

'一致文字列' は完全な文字列にすることも、ワイルドカード文字 % と _ を含めることもできます。

%: 任意の長さの文字を表します
_ : 任意の1文字を表す

<3> 集合の比較演算

1
2
3
4


-- スコアが85、86、または88のレコードをクエリします
select *
from score
where 分数 in (85, 86, 88)

<4> 論理的結合演算

1
2
3
4


-- クラス09031の女子生徒全員を照会する
select *
from student
where 班号 = '09031' and 性别 = '女'

3. 並べ替えクエリ

クエリ結果はORDER BY句を使用して並べ替えることができます。

1
2
3
4
5
6
7
8


-- 昇順 asc (デフォルト、省略可能)
select 学号, 姓名
from student
order by 学号
-- 降順 desc
select 学号, 姓名
from student
order by 学号 desc

複数の列を指定して並べ替えることもできます。最初の列が最初に並べ替えられ、次に2番目の列が並べ替えられます。

1
2
3
4


-- コース番号の昇順とスコアの降順で検索結果を表示します
select *
from score
order by 课程号 asc, 分数 desc

ORDER BY 句はクエリの最後に配置する必要があります。

4. 集計関数

集計関数は、データ統計やその他の機能を実装します。一連の値を計算し、単一の値を返すために使用されます。

通常は group by 句とともに使用される一般的な集計関数は次のとおりです。

関数名	説明
AVG	平均値
COUNT	指定された式で選択された項目の数をカウントしますCOUNT(*)はクエリによって出力された行数をカウントします
MIN	最小値
MAX	最大値
SUM	値の合計
STDWV	指定された式のすべてのデータの標準偏差を計算します
STDEVP	母集団標準偏差

集計関数の引数の一般的な形式は次のとおりです。

1

[all|distinct] expr

このうち、ALL はすべての値に対して集計関数演算を実行することを意味し、これがデフォルト値となります。また、DISTINCT は、すべての一意の値が考慮されることを指定します。 exprは式です

1
2
3
4


-- クラス「09031」の生徒数を照会する
select count(*) as '人数'
from student
where 班号 = '09031'

5. データのグループ化

集計関数は1つの結果しか出力しないため、列をクエリして集計関数をまとめるとエラーが報告されます。

特定のカテゴリの集計関数の結果は、データをグループ化することで計算できます。つまり、集計関数の範囲は各グループ内のすべてのレコードになります。

集計関数をフィルター条件として使用する場合は、それを HAVING 句の後に置く必要があります。

1
2
3
4
5
6


-- 最低スコアが 70 を超え、最高スコアが 90 未満の学生の学生 ID を照会します。
select 学号
from score
where 分数 is not null
group by 学号
having min(分数) > 70 and max(分数) < 90

クエリ内に WHERE 句、GROUP BY 句、HAVING 句、および集計関数が同時に出現する場合、SELECT コマンドの実行順序は次のようになります。

WHERE句を実行してテーブルから行を選択します
選択した行を「GROUP BY」でグループ化する
集計関数を実行する
HAVING句を実行して条件を満たすグループを選択します。

1
2
3
4


-- 各コースの平均スコアを照会する
select 课程号, avg(分数)
from score
group by 课程号

集計関数は条件として HAVING 句の後にのみ配置でき、 WHERE 句の後には配置できません。

SELECT句が列名と集計関数で構成されている場合、すべての列名をグループ化する必要があります。

6. テーブル接続クエリ

FROM 句では結合するテーブルを指定し、WHERE 句では結合条件を指定します。

1
2
3


select <列名1>[, ···]
from <表1>[, ···]
where 接続条件

2つのテーブルの共通列には、student.学号などのプレフィックスを指定する必要があります。

SQL Server では、接続は内部接続と外部接続に分けられます。

<1> 内部結合

Θ 結合、等価結合、自然結合

内部結合は最も基本的な結合方法です。INNER JOIN キーワードを使用します。このキーワードを使用しない場合、デフォルトは内部結合になります。

等接続

つまり、2つのテーブル間の接続関係は「等しい」です。

1
2
3
4
5
6
7
8
9


-- すべての学生の名前、コース番号、スコアの列を照会する
select student.姓名, score.课程号, score.分数
from student, score
where student.学号 = score.学号
-- したがって
select student.姓名, score.课程号, score.分数
from student
inner join score
on student.学号 = score.学号

入力を簡略化するために、テーブルエイリアスを使用することができます。

1
2
3
4


-- 前の例の簡略化
select x.姓名, y.课程号, y.分数
from student x, score y
where x.学号 = y.学号

非等価結合

つまり、2つのテーブル間の接続関係は「等しい」ではない。

自己接続

同じテーブルを結合することを自己結合と呼びます。

1
2
3
4
5
6


-- '3-105' を受験し、そのスコアが生徒 '109' のスコアより高い生徒の記録を照会します。
select x.课程号, x.学号, x.分数
from score x, score y
where x.课程号 = '3-105' and x.分数 > y.分数
		and y.学号 = '109' and y.课程号 = '3-105'
order by x.分数 desc

<2> 外部結合 OUTER JOIN

左外部結合: LEFT OUTER JOIN または LEFT JOIN

右外部結合: RIGHT OUTER JOIN または RIGHT JOIN

完全外部結合: FULL OUTER JOIN または FULL JOIN

1
2
3
4
5


-- 左外部結合の例
-- すべてのコースの教師の名前を照会する
select course.课程名, teacher.姓名
from course left join teacher
on (course.任课教师编号 = teacher.教师编号)

7. サブクエリ

<1> 一般的なサブクエリ

あるクエリが別のクエリの条件である場合、つまり、テーブルから行を選択する条件がテーブル自体または他のテーブルの結合情報に依存する場合、サブクエリを実装する必要があります。

サブクエリは、SELECT ステートメントの WHERE 句で最もよく使用されます。

1
2
3
4
5
6
7
8


-- 学生番号「105」の学生と同じ年に生まれた学生を検索します
select 学号, 姓名, year(出生日期) as '出生年份'
from student
where year(出生日期) = (
						select year(出生日期)
    					from student
    					where 学号 = '105'
						)

<2> 相関サブクエリ

通常のサブクエリでは、各サブクエリは 1 回だけ実行され、返された値はメインクエリの WHERE 句で使用されます。

メインクエリが各行を選択するかどうかを判断するときは、サブクエリを「起動」し、メインクエリにフィードバックする必要があります。つまり、内部条件に外部属性が含まれるサブクエリは相関サブクエリと呼ばれます。

1
2
3
4
5
6
7
8
9


-- コースの平均スコアよりも低いスコアを持つ学生を照会する
select 学号, 课程号, 分数
from score a
where 分数 < (
				select avg(分数)
    			from score b
    			where a.课程号 = b.课程号 
    				and 分数 is not null
				)

<3> EXISTS テストを含むサブクエリ

EXISTS は存在量指定子 ∃ を表し、論理真または論理偽の値のみを返します。

1
2
3
4
5
6
7
8


-- すべての教師の名前と部門を照会する
select 姓名, 系别
from teacher a
where exists (
				select *
    			from course b
    			where a.教师编号 = b.任课教师编号
				)

あるいは、結果を否定する「NOT EXISTS」がある。

8. クエリ結果の結合、交差、差の演算

次の演算子を使用して、2 つの SELECT クエリの結果に対して操作を実行できます。

UNION : および
交差:
例外: 差異

1
2
3
4
5
6
7
8


-- ANDの例
-- すべての教師と生徒の名前、性別、生年月日を照会する
select 姓名, 性别, year(出生日期) as '出生年份'
from teacher
union
select 姓名, 性别, year(出生日期) as '出生年份'
from student
-- order by 生年

(四)、DCL データ制御言語

データ制御は、ユーザーのデータ保存権限を制御し、DBA（データベース管理者）によって決定されます。

データベースセキュリティ

不安要素:

データベースへの不正アクセスと破壊
データベースから重要なデータが漏洩
安全保障環境の脆弱性

セキュリティコントロール:

ユーザーID認証: 静的パスワード認証、動的パスワード認証、生体認証、スマートカード認証
アクセス制御
自律的なアクセス制御方式（ユーザーが操作権限をカスタマイズし、他のユーザーに割り当てることができる）

自律アクセス制御

DBMS（データベース管理システム）データ制御には以下の機能が必要です

GRANTとREVOKEを通じてシステムに権限を通知し、データ辞書に保存する
ユーザーがリクエストを行った場合、認可ステータスに基づいて操作リクエストを実行するかどうかを確認します。

SQL 標準には、DELETE、INSERT、SELECT、および UPDATE 権限が含まれます。 SELETE 権限は READ 権限に対応します。SQL には REFERENCES 権限も含まれており、これはリレーションを作成するときにユーザーが外部キーを定義する機能を制限するために使用されます。

1. 認可ステートメントの形式

1
2
3
4


grant <権限>[, ···] 
on <オブジェクトタイプ> <オブジェクト名>[, ···]
to <ユーザー>[, ···]
[with grant option]

操作オブジェクトの種類によって権限は異なります。一般的な権限は次のとおりです。

オブジェクト	オブジェクトタイプ	操作権限
属性列	TABLE	SELECT、INSERT、UPDATE、DELETE、ALL PRIVILEGES (4つの権限の合計)
ビュー	TABLE	SELECT、INSERT、UPDATE、DELETE、ALL PRIVILEGES (4つの権限の合計)
基本テーブル	TABLE	SELECT、INSERT、UPDATE、DELETE、ALTER、INDEXALL PRIVILEGES (6つの権限の合計)
データベース	DATABASE	CREATETAB テーブルを作成する権限。DBA が一般ユーザーに付与できます。

PUBLIC: 権限を受け取るユーザーは、単一のユーザーまたは複数の特定のユーザーです。PUBLIC パラメータは、すべてのユーザーに権限を付与できます。
GRANT OPTION付き: この句が指定されている場合、権限を取得したユーザーは他のユーザーに権限を付与することもできます。

2. 権限取り消しステートメントの形式

1
2
3


revoke <権限>[, ···]
on <オブジェクトタイプ> <オブジェクト名>[, ···]
from <ユーザー>[, ···]

データベースの役割

役割とは、CEO、取締役、一般従業員など、人物のタイプを指します。人物のタイプを承認できます。

キャラクター作成

1

create role <キャラクター名>

ロールの承認

1

grant <権限> on <オブジェクトタイプ> <オブジェクト名> to <役割1>[, ···]

他のユーザーまたはロールにロールを付与する

1
2


grant <役割> to <役割>または<ユーザー名>
[with admin option]

with admin option は、このユーザーが他のロールまたはユーザーにこの権限を付与できることを意味します。

ロール権限の取り消し

1

revoke <権限> on <オブジェクト名> from <キャラクター名>

ビューのメカニズム

ビューは、1 つ以上の基本テーブルまたはビューから派生したテーブルです。これは仮想テーブルです。

ユーザーごとに異なるビューを定義し、ユーザーが間違いを起こさないように不要なデータを非表示にします。

ビューの作成

1
2
3


create view <ビュー名(リスト名)>
as select <クエリ句>
[with check option]

サブクエリのSELECT文には通常、ORDER BY句とDISTINCT文を含めることはできません。
WITH CHECK OPTION は、UPDATE、INSERT、および DELETE 操作の場合、操作対象の行がビュー定義の述語条件 (つまり、サブクエリの条件式) を満たす必要があることを意味します。
ビューを構成する属性列名がすべて省略されているか、すべて指定されています。省略した場合、ビューは SELECT サブクエリのターゲット列のプライマリ属性で構成されていることが暗黙的に示されます。

1
2
3
4
5
6
7
8


-- 関係モデル：student(sno, sname, sex, SD, email)
-- 「コンピュータサイエンス（CS）」の学生向けのビューを作成し、変更操作を実行する場合
-- ビュー内のコンピュータサイエンスの学生のみが影響を受けるようにする
create view cs-student
	as select sno, sname, sex
	from student
	where SD = 'CS'
	with check option

ビューの削除

1
2
3
4


drop view <ビュー名>

--例えば
drop view cs-student

監査

データベースに対するすべての操作は監査ログに記録され、そのログを使用して不正な動作があるかどうかを確認できます。

1
2
3
4


-- 「SC」データを変更する監査操作
AUDIT UPDATE ON SC;
-- 「SC」テーブルのすべての監査をキャンセルします
NOAUDIT UPDATE ON SC;

データ暗号化

いくつかの暗号化アルゴリズムにより、プレーンテキストは暗号テキストに変換され、他の人が閲覧できなくなります。

＃＃索引

インデックスを使用すると、データベースプログラムはテーブル全体をスキャンしなくても、テーブル内の必要なデータを見つけることができます。

データベースのインデックスは、テーブルの 1 つ以上の列の値のコレクションと、これらの値を物理的に識別するテーブル内のデータページへの論理ポインターの対応するリストです。

内部スキーマによって定義されます（内部スキーマはすべての内部レコードタイプ、インデックス、およびファイル構成を定義します）

インデックスを作成する

1
2


create [unique] [cluster] index <インデックス名>
	on <テーブル名>(列名[順序], ···)

順序：オプションASC（デフォルト）または DSC

UNIQUE ：このインデックスの各インデックス値が 1 つのデータレコードのみに対応することを示します。

CLUSTER ：作成されるインデックスがクラスター化インデックスであることを示します。つまり、インデックス項目の順序は、テーブル内のレコードの物理的な順序と一致します。

インデックスの削除

1

drop index <インデックス>