MySQLデータベース

データベースの基本

データベースの分類

リレーショナルデータベースは、テーブルを使用してデータを保持し、主キーや外部キーなどのメカニズムによってテーブル間の関係を記述します。一般的なリレーショナルデータベースには、My SQL、Oracle、DB 2、SQL Serverがある。

非リレーショナルデータベースは通常、固定の2 次元テーブルモデルを使用せず、一般的なタイプにはキーバリューデータベース、ドキュメントデータベース、カラムデータベースなどがある。Redisは一般的なキーバリューデータベースです。

“非リレーショナル”とは、データ間に関係がないことを意味するのではなく、伝統的な関係モデルを主に組織化していないことを意味する。

基本概念は

概念	説明
データは	数字、テキスト、画像、オーディオ、ビデオなどの情報
データベースの種類	一定の構造でデータを整理して保存すること。
データベース管理システム	データベースを管理するソフトウェア、DBMS
データベース·システム	データベース、DBMS、アプリケーション、および関係者などからなる全体、略称 DBS

プロジェクトは1つ以上のデータベースを使用することができ、同じデータベースがシステムアーキテクチャに応じて複数のビジネスモジュールにサービスを提供することもできます。

My SQLのインストールとディレクトリ

Windows 環境にMySQLをインストールする場合は、特殊文字を含むコンピュータ名とインストールパスを使用しないでください。バージョン、インストール方法、およびオペレーティングシステムによってディレクトリが異なる場合があります。

共通カタログは以下の通り。

プログラムインストールディレクトリ：MySQLサーバー、クライアント、および関連ツールを保存します。
データ·ディレクトリデータベース·ファイル、ログ、構成データを保存します。
プロファイル：Windowsではmy.ini、Linuxではmy.cnfがよく见られます。

古いバージョンをアンインストールする場合は、重要なデータをバックアップしてから、対応するサービスを停止および削除してください。データの目的を確認せずに、データカタログを直接削除しないでください。

SQL 文の分類

SQLは、リレーショナルデータベースを操作するための構造化クエリ言語です。

分類	作用	共通キーワード
DDL	データベース·オブジェクトの定義	`CREATE`、`ALTER`、`DROP`
DML	操作表からのデータ	`INSERT`、`UPDATE`、`DELETE`
DQL	クエリー·データ	`SELECT`
DCL	ユーザーと権限の制御	`GRANT`、`REVOKE`
TCL	制御トランザクション	`COMMIT`、`ROLLBACK`、`SAVEPOINT`

MySQL 一般的なデータ型

integer 型

一般的な整数型は、TINYINT、SMALLINT、MEDIUMINT、INT、BIGINTである。タイプの選択は、ビジネスの範囲に応じて決定し、過剰なタイプを無意味に使用しないようにしてください。

固定点数と浮動小数点数

金額など正確な計算が必要なデータはDECIMALを優先します。

salary decimal(9, 2)

DECIMAL(9, 2)は、最大 9 桁の10 進数を表し、2 桁は小数です。

FLOATとDOUBLEは近似値型であり、浮動小数点誤差が許容されるシナリオに適しており、正確な計算が必要な金額を直接保存するのには適していません。

文字列型

CHAR(n)：固定長文字列で，ほぼ固定長のデータに適している.
VARCHAR(n)：可変長文字列で，長さの変化が大きいテキストに適している.
TEXT：長いテキストを保存するために使用します。

文字列リテラルには通常シングルクォートが使用されます。

日付と時刻のタイプ

DATE日付を保存する。
TIME保存時間.
DATETIME日付と時刻を保存します。
TIMESTAMP：タイムスタンプを保存し、範囲とタイムゾーンの変換動作がDATETIMEとは異なる。

binary 型の種類

BLOBはバイナリデータの保存に使用される。大きな画像、オーディオ、ビデオは、オブジェクトストアやファイルシステムに保存するのに適しており、データベースはファイルアドレスとメタデータを保持します。

データ

テーブルの作成

基本的な文法

create table 表名 (
    列名 数据类型 列属性,
    列名 数据类型 列属性
);

テーブル名とカラム名は、ビジネス上の意味を明確に表現し、統一された命名スタイルを維持する必要があります。

学生テーブルの作成

create table student (
    sno int,
    sname varchar(16),
    birthday date,
    height decimal(3, 2),
    tel char(11)
);

各 SQLはセミコロンで終了することを推奨します。

列のプロパティ

デフォルト値

status tinyint not null default 0

データを挿入するときに列を省略し、データベースではデフォルト値が使用されます。

自己増分属性

AUTO_INCREMENTは、整数のプライマリ·キーによく使用されます。カラムを省略するか、データを挿入するときにNULLを渡すと、データベースは次の順序番号を生成します。

create table student (
    sno int auto_increment,
    sname varchar(16),
    birthday date,
    height decimal(3, 2),
    tel char(11),
    classno int,
    constraint pk_student primary key (sno)
);

自己インクリメントカラムはインデックス化する必要があり、テーブルには1つの自己インクリメントカラムしかありません。通常はプライマリ·キーと組み合わせて使用されます。

制約条件

制約は、データの整合性と一貫性を保証するために使用されます。制約に違反するデータは正常に書き込まれません。

プライマリ·キー制約{{ぷらいまりきーせい}}

プライマリ·キーは、レコードを一意に識別するために使用されます。プライマリ·キー値は一意であり、NULLではありません。テーブルはプライマリ·キーを1つだけ持つことができますが、プライマリ·キーには複数のカラムを含めることができます。

レベルの書き方：

create table student (
    sno int primary key,
    sname varchar(16),
    birthday date
);

表レベルの書き方：

create table student (
    sno int,
    sname varchar(16),
    birthday date,
    constraint pk_student primary key (sno)
);

外部キー制約#外部キー制約#

外部キーは参照整合性を保証するために使用されます。外部キーカラム内のnull 以外の値は、参照先テーブルのキー候補内にある必要があります。

参照先テーブルを作成してから参照テーブルを作成してください。

drop table if exists student;
drop table if exists classes;

create table classes (
    classno int,
    classname varchar(32),
    constraint pk_classes primary key (classno)
);

create table student (
    sno int,
    sname varchar(16),
    birthday date,
    height decimal(3, 2),
    tel char(11),
    classno int,
    constraint pk_student primary key (sno),
    constraint fk_student_classno
        foreign key (classno)
        references classes(classno)
);

データを挿入するときは、参照先テーブルを挿入してから参照テーブルを挿入してください。データを削除する場合は、参照レコードを処理してから参照レコードを削除してください。

外部キーでは、参照アクションを設定できます。

constraint fk_student_classno
    foreign key (classno)
    references classes(classno)
    on delete set null
    on update cascade

CASCADEおよびSET NULLは自動的に関連データに影響を与え、使用前にビジネスセマンティクスを確認してください。論理削除は通常、物理的な削除ではなくステータスフィールドで記録をマークします。

一意の拘束

一意性制約は、カラムまたはカラムの組み合わせで重複値が発生しないように制限するために使用します。

constraint uk_student_tel unique (tel)

My SQLでは、一意のインデックスは通常複数のNULLを許可します。この列に値が必要な場合は、NOT NULLも追加します。

空でない制約

sname varchar(16) not null

検査制約

constraint ck_student_birthday
    check (birthday < '2026-02-05')

MySQL 8ではCHECK制約が適用されます。以前のバージョンでは解決されても実行されない場合があるため、データベースのバージョンに注意する必要があります。

テーブル構造の変更

ALTER TABLEは、テーブル名、列、制約の変更に使用します。

テーブル名の変更

alter table student rename to student2;
alter table student2 rename to student;

列の追加

alter table student
add address varchar(255);

列名と型の変更

alter table student
change address addr varchar(255);

alter table student
modify addr varchar(32);

CHANGEでは列名と型の両方を変更できますが、MODIFYでは列定義のみを変更できます。

列の削除

alter table student
drop column addr;

拘束の追加

alter table student
add constraint pk_student primary key (sno);

alter table student
add constraint fk_student
foreign key (classno)
references classes(classno);

alter table student
add constraint uk_student_tel unique (tel);

テーブルを削除

drop table if exists student;

テーブルを削除すると、テーブル構造とデータの両方が削除されます。操作前にバックアップを確認してください。

DMLデータ操作文

データを挿入する

基本的な文法

insert into 表名 (列名, 列名)
values (值, 值);

カラム名の数、順序、および対応する値は一致する必要があります。テーブル構造の変更を避けるためにカラム名を明示的に記述することを推奨する。

レコードを挿入します

insert into student(sname, birthday)
values('Jack', '2026-01-01');

デフォルト値の使用

insert into student
values(default, 'Rose', '2025-01-01', 1.70, '13312345678', null);

複数のレコードを挿入

insert into student(sname, birthday)
values
    ('Rose1', '2025-01-01'),
    ('Rose2', '2025-01-02');

危険な暗黙の型変換に頼らないでください。例えば、無効な日付文字列は有効な日付として挿入しないでください。

データの変更

update student
set height = 1.77,
    tel = '13312345678'
where sno = 3;

元の値に基づいて更新できます。

update student
set height = height + 0.03
where sno = 3;

UPDATEを実行する前に、WHERE条件を確認してください。条件を省略すると、テーブル全体が変更されます。

データを削除

delete from student
where sno = 5;

WHERE 条件を省略すると、テーブル内のすべてのレコードが削除されます。DELETEはデータを削除しますが、テーブル構造は保持します。

SELECTクエリ文{{SELECTくえりぶん}}

単純なクエリ

select *
from student;

ビジネスに必要な列のみをクエリーすることを推奨します

select sno, sname, birthday
from student;

クエリの結果を結果セットと呼びます。

式とnull 値処理

NULLは、不明または欠落値を示します。通常の数値演算では、結果は通常NULLのままです。

select ifnull(lowest_sal, 0) + 100,
       highest_sal + 500
from job_grades;

接続文字列にはCONCAT()を使用できます。

select concat('86', tel)
from student;

カラムの別名

select ifnull(lowest_sal, 0) + 100 as 最低工资,
       highest_sal + 500 as 最高工资
from job_grades;

ASは省略可能だが、明示的に書いた方が読みやすい。

重複レコードの除去

select distinct sname, birthday
from student;

DISTINCTは、選択した列の組み合わせを重み付けします。

条件式

select case
           when lowest_sal < 3000 then '低工资'
           when lowest_sal between 3000 and 5000 then '中等工资'
           else '高工资'
       end as 工资等级,
       highest_sal
from job_grades;

条件付きクエリー

条件の比較

select *
from student
where sname = 'Rose';

select *
from student
where sname <> 'Rose';

select *
from student
where height >= 1.70;

論理的条件

select *
from student
where height >= 1.70
  and birthday < '2025-01-03';

select *
from student
where height >= 1.70
   or birthday > '2025-01-01';

ANDはORよりも優先度が高い。複雑な条件は、括弧を使用して論理を明確にしてください。

範囲条件の範囲

BETWEENには両端境界が含まれます。

select *
from student
where height between 1.70 and 1.72;

ファジーマッチ

LIKEの%は0 個以上の任意文字を表し、_は1 個の任意文字を表す。

select *
from student
where sname like 'J%';

select *
from student
where sname like 'J_c%';

select *
from student
where sname like '%a%';

集合条件の集合

select *
from student
where sname in ('Rose', 'Jack', 'Tom');

Nullの条件

NULL 値はIS NULLまたはIS NOT NULLを使用する必要があります。

select *
from student
where tel is null;

select *
from student
where tel is not null;

tel = nullを使用してnull 値を判断することはできません。

ソート·ソート

select *
from student
order by height asc;

select *
from student
order by birthday desc,
         height desc;

ASCは昇順、DESCは降順を示します。ORDER BYではクエリ結果のカラム別名を使用できますが、WHEREでは通常、同階層クエリで定義された別名を使用できません。

集計関数{{かんすう}}

一般的な集积关数には、SUM()、AVG()、MAX()、MIN()、およびCOUNT()がある。

select sum(salary),
       avg(ifnull(salary, 0)),
       max(salary),
       min(salary)
from employee;

COUNT(*)を除き、ほとんどの集計関数ではNULLは無視されます。

select count(*)
from employee;

COUNT(*)は結果セットの行数をカウントしますので、推奨できません。COUNT(*)、COUNT(1)、またはCOUNT(非空列)を使用するかどうかは、セマンティクスと実行計画に基づいてください。

クエリのグループ化

`GROUP BY`

select department_id,
       avg(salary) as avg_salary,
       count(*) as employee_count
from employee
group by department_id;

集計に関与していないクエリ·リストのカラム。GROUP BYに表示されるはずです。ONLY_FULL_GROUP_BYを有効にすると、MySQLはルールを厳密にチェックします。

`HAVING`

WHEREはグループ化前に行をフィルタし、HAVINGはグループ化後にグループ化結果をフィルタする。

select department_id,
       avg(salary) as avg_salary
from employee
where department_id in (5001, 5002)
  and salary > 5000
group by department_id
having avg(salary) > 6000
order by avg_salary;

ページング·クエリ

MySQLでは、結果のロー数をLIMITで制限します。

select *
from employee
limit 5;

select *
from employee
limit 5, 5;

LIMIT offset, row_countのオフセットは0から始まります。

pageNoページ、1ページあたりのpageSizeレコードの場合のオフセットは次のとおりです。

(pageNo - 1) * pageSize

上記の変数式は通常の静的 SQLに直接書き込むことはできません。アプリケーションはオフセットを計算してからパラメータを渡します。

マルチテーブルクエリ

デカルト積と結合条件

複数の表を直接FROMに書くと、デカルト積を成す。正しい組み合わせは、接続条件によって保持されている必要があります。

select *
from employee e,
     departments d
where e.department_id = d.department_id;

最新のSQLは明示的なJOIN構文を推奨します。

内部接続

select e.first_name,
       e.department_id,
       d.department_name
from employee e
inner join departments d
    on e.department_id = d.department_id
where e.department_id = 5001;

接続関係はONに記述し、最終結果の一般的なスクリーニング条件は通常WHEREに記述します。

複数テーブル内接続

北京で働く従業員の名前、部門名、都市を検索する：

select e.first_name,
       d.department_name,
       loc.city
from employee e
inner join departments d
    on e.department_id = d.department_id
inner join locations loc
    on d.location_id = loc.location_id
where loc.city = '北京';

不等価接続

select e.first_name,
       e.salary,
       j.grade_level
from employee e
inner join job_grades j
    on e.salary >= j.lowest_sal
   and e.salary < j.highest_sal;

外部接続とは

左外部ジョインは、左テーブルのすべてのレコードを保持します。右表に一致レコードがない場合、右表列はNULLを返す。

select e.first_name,
       e.department_id,
       d.department_name
from employee e
left join departments d
    on e.department_id = d.department_id;

すべてのプログラマーとその勤務都市を検索する：

select e.first_name,
       loc.city
from employee e
left join departments d
    on e.department_id = d.department_id
left join locations loc
    on d.location_id = loc.location_id
where e.job_id = '程序员';

WHEREで右テーブル列にNullでない条件を設定すると、左ジョイン効果が内側ジョインに近くなる可能性があることに注意してください。

セルフ·コネクション

自己ジョインは、同じテーブルが異なるエイリアスでジョインに参加しています。

select e.first_name as employee_name,
       m.first_name as manager_name
from employee e
left join employee m
    on e.manager_id = m.employee_id;

従業員とマネージャーの勤務地を確認する：

select e.first_name as employee_name,
       employee_location.city as employee_city,
       m.first_name as manager_name,
       manager_location.city as manager_city
from employee e
left join employee m
    on e.manager_id = m.employee_id
left join departments employee_department
    on e.department_id = employee_department.department_id
left join locations employee_location
    on employee_department.location_id = employee_location.location_id
left join departments manager_department
    on m.department_id = manager_department.department_id
left join locations manager_location
    on manager_department.location_id = manager_location.location_id;

完全に接続

My SQLはFULL OUTER JOINを直接サポートしていません。ビジネス·ニーズに応じて、左ジョイン、右ジョイン、およびUNIONシミュレーションを使用できますが、重複するローを処理する必要があります。

複数のテーブル更新

MySQLは更新文で接続を使用することをサポートします。

update employee e
left join departments d
    on e.department_id = d.department_id
left join locations loc
    on d.location_id = loc.location_id
set e.salary = e.salary + 5
where loc.city = '北京';

My SQLもマルチテーブル削除をサポートしていますが、シンタックスはシングルテーブルDELETEとは異なります。実施前に影響範囲を慎重に確認する必要がある。

サブクエリ

サブクエリは、他のSQLにネストされたクエリです。WHERE、FROM、SELECTなどの位置に存在することができる。

シングル·ライン·サブクエリ

サブクエリが値を1つだけ返す場合、通常の比較演算子と組み合わせることができます。

select first_name, salary
from employee
where salary > (
    select salary
    from employee
    where first_name = 'Rose'
);

サブクエリが複数行を返す場合、上記の記述は間違っています。

マルチロー·サブクエリ

マルチロー·サブクエリは、IN、ANY、ALL、またはEXISTSでよく使用されます。

`IN`

select *
from employee
where department_id in (
    select department_id
    from departments
    where manager_id = 100
);

`EXISTS`

EXISTSは、サブクエリが少なくとも1つのローを返すかどうかのみを判断します。

select *
from employee e
where exists (
    select 1
    from departments d
    where d.department_id = e.department_id
      and d.manager_id = 100
);

EXISTSはINよりも速いとは限らない。オプティマイザはクエリをオーバーライドでき、インデックス、データ分散、実行計画の決定を組み合わせる必要があります。

`ANY`

select first_name, salary
from employee
where salary > any (
    select salary
    from employee
    where department_id = 5001
);

給与がサブクエリー結果の少なくとも1つの値より大きいことを示します。

`ALL`

select first_name, salary
from employee
where salary > all (
    select salary
    from employee
    where department_id = 5001
);

給与がサブクエリー結果の各値よりも大きいことを示します。

`FROM`サブクエリー

FROM以降のサブクエリは派生テーブルを形成し、別名を設定する必要があります。

select max(department_avg_salary)
from (
    select avg(salary) as department_avg_salary
    from employee
    group by department_id
) department_salary;

オプティマイザは派生テーブルをマージまたは実体化することができ、単に“一度だけ実行”することはできません。

結果セットの統合

`UN`と`UNALL`

UNIONとUNION ALLは、結果セットを縦連結するために使用されます。各クエリのカラム数は同じでなければならず、対応するカラムのデータ型は互換性がある必要があります。

UNIONは軽量化されるが、UNION ALLは軽量化されず、後者は通常コストが低い。

select first_name as name
from employee
union
select city as name
from locations;

select first_name
from employee
union all
select first_name
from employee;

概要行の追加

select first_name,
       salary
from employee
union all
select '总计',
       sum(salary)
from employee;

行列構造の変換例

旧表は科目別である。

create table score (
    sname varchar(16),
    shuxue int,
    yuwen int,
    yingyu int
);

行としての新しい表

create table score2 (
    sname varchar(16),
    kemu varchar(32),
    chengji int
);

UNION ALLを使用して、指定学生のデータを変換できます。

insert into score2(sname, kemu, chengji)
select sname, 'shuxue', shuxue
from score
where sname = 'Tom'
union all
select sname, 'yuwen', yuwen
from score
where sname = 'Tom'
union all
select sname, 'yingyu', yingyu
from score
where sname = 'Tom';

データベースのバックアップ

コールドバックアップ

コールドバックアップは、データベースサービスが停止した後にデータファイルをコピーすることです。操作は簡単ですが、ダウンタイムが発生します。また、使用中のデータディレクトリを自由にコピーすることはできません。

ホット·バックアップ

ホットバックアップは、データベースサービスの実行中に完了するバックアップです。一般的な方法としては、論理エクスポートとオンラインバックアップをサポートするツールの使用があります。

バックアップの有効性は回復演習で確認してください。リカバリ·プロセスを検証せずにバックアップ·ファイルを作成するだけでは不十分です。

データベーストランザクション

トランザクションの概念

トランザクションとは、論理単位として実行される一連の操作です。トランザクション内の操作は、すべて正常にコミットされるか、失敗した場合にすべてロールバックされます。

ローカルトランザクション内の操作は通常、同じデータベースリソース内で行われます。分散トランザクションには複数のデータベースやその他のリソースが含まれ、追加の調整メカニズムが必要です。

ACIDプロパティACIDぷろぱてぃ

の特性	の説明
原子性の問題	トランザクション内の操作はすべて完了または取り消します。
コヒーレンス	トランザクション実行前後、データは確立された整合性ルールを満たす
孤立している	独立性レベルに応じた同時トランザクション間の可視性と影響の制御
持続性の問題	トランザクションがコミットされると、変更は確実に保存されます。

SQLトランザクション制御{{SQLとらんざくしょんせいぎょ}}

My SQLは自動コミットを開始する。オートコミットをオフにした後は、トランザクションを手動で制御できます。

set autocommit = 0;

insert into departments(department_id, department_name)
values(5005, '行政部');

insert into employee(first_name, phone_number, department_id)
values('Jack', '13312346788', 5005);

commit;

エラーが発生した場合にロールバックできます。

rollback;

セーブポイントセーブポイント

set autocommit = 0;

insert into departments(department_id, department_name)
values(5019, '行政部2');

savepoint after_department;

insert into employee(first_name, phone_number, department_id)
values('Jack', '13312346788', 5019);

rollback to after_department;
commit;

ROLLBACK TOはセーブポイント以降のオペレーションをロールバックし、トランザクションを自動的に終了しません。

ログとトランザクションのリカバリ

InnoDBトランザクションには以下のログが含まれます。

undo log：ロールバックとマルチバージョン同時性制御のために古いバージョン情報を保存します。
redo log：クラッシュ·リカバリと持続性のサポートのためのレコード·ページの変更。
binlog：レプリケーションとポイントインタイムリカバリのためのMy SQL Server 層の論理ログ。

コミットした後、すべてのデータを一度にデータベースファイルに書き込むとは言えません。データページ、ログバッファ、ディスクブラッシングは、WALなどのメカニズムに従ってデータベースによって調整されます。コミットの成功には、通常、関連するログが構成要件を満たしている必要があります。一方、データページはバックグラウンド·スレッドによって後でディスクに書き込み戻すことができます。

同時読み取りの問題

ダーティリーディング

あるトランザクションは、別のトランザクションがコミットしていないデータを読み取ります。後者がロールバックすると、前者は無効なデータを読み取ります。

繰り返しはできない

同じトランザクション内で同じローを複数回フェッチすると、異なる結果が得られます。これは通常、他のトランザクションが変更をコミットしたためです。

幻読を読む

同じトランザクションで同じ条件で複数回クエリされると、返されるレコードのコレクションが変更されます。通常は、他のトランザクションが条件に一致する行を挿入または削除したためです。

トランザクション独立性レベル{{とらんざくしょんどくりつせいれべる}}

SQL 標準では、次の4つの独立性レベルが定義されています。

独立性レベル	説明
`READ UNCOMMITTED`	コミットされていないデータの読み取りを許可
`READ COMMITTED`	コミットされたデータのみ読み取り可能
`REPEATABLE`	同一トランザクション内での繰り返し読み取りは通常一貫性を維持する
`SERIALIZABLE`	より厳密な方法での同時アクセスのシリアル化

InnoDBのデフォルトの独立性レベルは通常REPEATABLE READです。MVCCとロックメカニズムを介して、一貫した読み取りとカレント読み取りを処理します。ファンタジーリードを避けることは、“常にテーブル全体にテーブルロックをかける”こととは異なります。InnoDBは、レコードロック、ギャップロック、キープロロックなどのメカニズムを使用できます。

ロックの基本概念

共有ロック：他のトランザクションが読み取りを継続できるようにするが、競合する書き込みは制限する。
排他的ロック：データを変更し、他のトランザクションが同じリソースに競合するアクセスを制限するために使用されます。
行レベルのロック索引レコードまたは範囲をロックします。通常は並行性が高くなります。
テーブルレベルのロック：テーブル全体をロックし、通常は並列性が低くなります。

ロックの実際の範囲は、インデックス、SQL 条件、独立性レベルに関係します。適切なインデックスがないと、スキャンとロックの範囲が拡大します。

View Viewビュー

ビューは、クエリ定義に基づいた仮想テーブルです。通常のビューは通常、結果データの単一のコピーを保持せず、クエリ時にその定義をデータベースによって処理します。

create view v_emp as
select e.employee_id,
       e.first_name,
       e.salary,
       e.department_id
from employee e;

select *
from v_emp;

単純な単一テーブル·ビューは、次の条件が満たされると更新できます。

update v_emp
set salary = 8100
where employee_id = 100;

集計、グループ化、DISTINCT、連合などの構造を含むビューは、通常、直接更新できません。

Viewの役割

複雑なクエリを含む。
外部に安定したデータアクセス構造を提供する。
ユーザーが一部の行と列にアクセスできるように制限します。
上位プログラムのクエリコードを簡素化する。

ビューは、テーブル構造のバージョン管理を完全に置き換えることはできません。基礎となるテーブルに互換性のない変更があった場合、ビュー自体も変更する必要がある場合があります。

`WITH CHECK OPTION`

create view v_beijing_employee as
select *
from employee
where department_id = 5001
with check option;

そのビューからデータを追加または変更する場合、結果はビュー条件を満たしている必要があります。

ストアド·プロシージャ

ストアド·プロシージャは、一連のSQL 文とプロセス制御文をカプセル化し、パラメータを介して入力を受け取ったり、出力を返したりできます。

drop procedure if exists proc_get_user_info;

delimiter //

create procedure proc_get_user_info(
    in p_user_id int,
    in p_include_address boolean,
    out p_result_code int
)
begin
    declare v_error int default 0;
    declare continue handler for sqlexception set v_error = 1;

    set p_result_code = 0;

    if p_include_address then
        select id,
               username,
               age,
               address,
               create_time
        from t_user
        where id = p_user_id;
    else
        select id,
               username,
               age,
               create_time
        from t_user
        where id = p_user_id;
    end if;

    if v_error = 1 then
        set p_result_code = 1;
        select '查询用户信息失败' as error_msg;
    end if;
end //

delimiter ;

呼び出し例：

call proc_get_user_info(1, true, @result_code);
select @result_code;

カスタム定義関数

カスタム定義関数はパラメータを受け取り、再利用可能な計算ロジックをカプセル化するのに適した値を返します。

drop function if exists get_salary_level;

delimiter //

create function get_salary_level(p_salary decimal(10, 2))
returns varchar(20)
deterministic
begin
    if p_salary < 3000 then
        return '低工资';
    elseif p_salary <= 5000 then
        return '中等工资';
    else
        return '高工资';
    end if;
end //

delimiter ;

select first_name,
       get_salary_level(salary)
from employee;

トリガTrigger

指定したテーブルでINSERT、UPDATE、またはDELETEのイベントが発生すると、トリガが自動的に実行されます。

次の例では、データの書き込みを毎日 8 時から17 時の間に制限します。

drop trigger if exists trg_employee_insert_time;

delimiter //

create trigger trg_employee_insert_time
before insert on employee
for each row
begin
    if current_time() < '08:00:00'
       or current_time() > '17:00:00' then
        signal sqlstate '45000'
            set message_text = '当前时间不允许新增员工数据';
    end if;
end //

delimiter ;

トリガーは暗黙的に実行され、使用しすぎるとトラブルシューティングが難しくなります。データベースレベルの監査や制約の強制に適したシナリオでは、すべてのビジネスロジックをトリガーに入れないでください。

MySQLデータベース

MySQLデータベース

データベースの基本

データベースの分類

基本概念は

My SQLのインストールとディレクトリ

SQL 文の分類

MySQL 一般的なデータ型

integer 型

固定点数と浮動小数点数

文字列型

日付と時刻のタイプ

binary 型の種類

データ

テーブルの作成

基本的な文法

学生テーブルの作成

列のプロパティ

デフォルト値

自己増分属性

制約条件

プライマリ·キー制約{{ぷらいまりきーせい}}

外部キー制約#外部キー制約#

一意の拘束

空でない制約

検査制約

テーブル構造の変更

テーブル名の変更

列の追加

列名と型の変更

列の削除

拘束の追加

テーブルを削除

DMLデータ操作文

データを挿入する

基本的な文法

レコードを挿入します

デフォルト値の使用

複数のレコードを挿入

データの変更

データを削除

SELECTクエリ文{{SELECTくえりぶん}}

単純なクエリ

式とnull 値処理

カラムの別名

重複レコードの除去

条件式

条件付きクエリー

条件の比較

論理的条件

範囲条件の範囲

ファジーマッチ

集合条件の集合

Nullの条件

ソート·ソート

集計関数{{かんすう}}

クエリのグループ化

GROUP BY

HAVING

ページング·クエリ

マルチテーブルクエリ

デカルト積と結合条件

内部接続

複数テーブル内接続

不等価接続

外部接続とは

セルフ·コネクション

完全に接続

複数のテーブル更新

サブクエリ

シングル·ライン·サブクエリ

マルチロー·サブクエリ

IN

EXISTS

ANY

ALL

FROMサブクエリー

結果セットの統合

UNとUNALL

概要行の追加

`GROUP BY`

`HAVING`

`IN`

`EXISTS`

`ANY`

`ALL`

`FROM`サブクエリー

`UN`と`UNALL`

`WITH CHECK OPTION`