Mysqlの基本2

ビュー view#

ビューは == 仮想テーブル == であり、その内容はクエリによって定義されます。実際のテーブルと同様に、ビューは名前付きの列と行データのセットを含みます。しかし、ビューはデータベース内に保存されたデータ値のセットとして存在するわけではありません。行と列のデータは、ビューを定義するクエリが参照するテーブルから取得され、ビューを参照する際に動的に生成されます。参照される基礎テーブルに対して、ビューの役割はフィルタリングに似ています。ビューを定義するフィルタは、現在のデータベースまたは他のデータベースの 1 つ以上のテーブル、または他のビューから来ることがあります。ビューを介してのクエリには制限がなく、それを介したデータの変更にもほとんど制限がありません。== ビューはデータベースに保存されているクエリの SQL 文 == であり、主に 2 つの理由から使用されます：セキュリティ上の理由として、ビューは一部のデータを隠すことができます。

ビューの作成#

CREATE VIEW ビュー名 AS SQL文
例：
CREATE VIEW v1 AS SELECT nid, name FROM tab1 WHERE nid > 4

ビューの削除#

DROP VIEW ビュー名

ビューの変更#

ALTER VIEW ビュー名 AS SQL文
例：
ALTER VIEW v1 AS
SELECT A.nid,B.NAME FROM tab1
LEFT JOIN B ON A.id = B.nid
LETF JOIN C on A.id = C.nid
WHERE tab1.id > 2

ビューの使用#

ビューを使用する際は、テーブルとして操作すればよいです。ビューは仮想テーブルであるため、実際のテーブルに対して作成、更新、削除操作を行うことはできません。== クエリにのみ使用できます ==。

select * from v1

ストアドプロシージャ procedure#

なぜストアドプロシージャを使用するのか#

私たちは、アプリケーションが 2 種類に分かれることを知っています。一つは Web ベース、もう一つはデスクトップベースで、どちらもデータベースと相互作用してデータの取得と保存を行います。今、これら 2 つのアプリケーションを含むアプリケーションがあると仮定します。今、その中の 1 つのクエリ SQL 文を変更する必要がある場合、対応するクエリ SQL 文を同時に変更しなければならないかもしれません。アプリケーションが非常に大規模で複雑な場合、問題が発生します。メンテナンスが困難です！さらに、SQL クエリ文を Web プログラムやデスクトップに置くと、SQL インジェクションの攻撃を受けやすくなります。ストアドプロシージャは、これらの問題を解決するのに役立ちます。

ストアドプロシージャの作成#

ストアドプロシージャの作成には主に 2 種類あります。一つはパラメータ付き、もう一つはパラメータなしです。

パラメータなしの例

//ストアドプロシージャの作成
delimiter   //カスタム文の終了記号、ここでは多くのSQL文を実行するため、エラーを防ぐためにカスタムします
create procedure p1()
BEGIN
    select * from tab1;
END
delimiter； //カスタムローカル終了記号の終了
//ストアドプロシージャの実行
call p1()

パラメータ付きの例

//ストアドプロシージャの作成
delimiter\\
create procedure p1(
    in i1 int,  //入力パラメータi1
    in i2 int,  //入力パラメータi2
    inout i3 int,   //入力と出力の両方が可能
    out r1 int  //出力パラメータ
)
BEGIN
    DECLARE temp1 int;
    DECLARE temp2 int default 0;
    set temp1=1;
    set r1 = i1 + i2 + temp1 + temp2;
    set i3 = i3 + 100;
END\\
delimiter;

//ストアドプロシージャの実行
DECLARE @t1 INT default 3;  //変数のデフォルト値を3に設定
DECLARE @t2 INT;    //変数の設定
CALL p1(1,2,@t1,@t2);   //ストアドプロシージャを実行し、パラメータを渡す。t2は自動的にキャンセルされる
SELECT @t1,@t2;     //ストアドプロシージャの入力結果を確認

ストアドプロシージャの削除#

drop procedure p1;

関数 function#

MySQL には多くの組み込み関数があります。たとえば、平均値、合計、カウントなど、さまざまなものがあります。まず、いくつかの組み込み関数を紹介し、その後にユーザー定義関数について説明します。関数もパラメータを受け取ることができ、戻り値を受け取ることもできますが、関数は実行文から得られた結果を取得することはできませんが、ストアドプロシージャはできます。

CHAR_LENGTH(str)
        文字列strの長さを返します。長さの単位は文字です。1つのマルチバイト文字は1つの単一文字としてカウントされます。
        5つの2バイト文字セットを含む場合、LENGTH()の戻り値は10ですが、CHAR_LENGTH()の戻り値は5です。

CONCAT(str1,str2,...)
        文字列の結合
        いずれかのパラメータがNULLの場合、戻り値はNULLです。
CONCAT_WS(separator,str1,str2,...)
        文字列の結合（カスタム区切り文字）
       　CONCAT_WS()は空の文字列を無視しません。（ただし、すべてのNULLは無視します）。

CONV(N,from_base,to_base)
       　進数変換
       　例えば：
           　SELECT CONV('a',16,2); は、aを16進数から2進数の文字列表現に変換します。

FORMAT(X,D)
       　数字Xの形式を'#,###,###.##'として書き、四捨五入で小数点以下D桁を保持し、結果を文字列として返します。Dが0の場合、結果は小数点なし、または小数部分なしで返されます。
       　例えば：
           　SELECT FORMAT(12332.1,4); 結果は： '12,332.1000'
INSERT(str,pos,len,newstr)
       　指定された位置に文字列を挿入します
           　pos：置き換え位置の実際の位置
           　len：置き換えの長さ
           　newstr：新しい文字列
       　特別な場合：
           　posが元の文字列の長さを超えると、元の文字列が返されます
           　lenが元の文字列の長さを超えると、新しい文字列が完全に置き換えられます
INSTR(str,substr)
       　文字列strの中の部分文字列の最初の出現位置を返します。

LEFT(str,len)
       　文字列strの開始からlen位置の部分列文字を返します。

LOWER(str)
       　小文字に変換します。

UPPER(str)
       　大文字に変換します。

LTRIM(str)
       　空白文字が削除された文字列strを返します。
RTRIM(str)
       　末尾の空白文字が削除された文字列strを返します。
SUBSTRING(str,pos,len)
       　文字列の部分列を取得します。

LOCATE(substr,str,pos)
       　部分列のインデックス位置を取得します。

REPEAT(str,count)
       　繰り返された文字列strから構成される文字列を返します。文字列strの数はcountと等しいです。
       　count <= 0の場合、空の文字列が返されます。
       　strまたはcountがNULLの場合、NULLが返されます。
REPLACE(str,from_str,to_str)
       　文字列strと、すべての文字列from_strがto_strに置き換えられた文字列を返します。
REVERSE(str)
       　文字列strを逆順に返します。
RIGHT(str,len)
       　文字列strの後ろからlen個の文字で構成される部分列を返します。

SPACE(N)
       　N個の空白から構成される文字列を返します。

SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
       　lenパラメータなしの形式は、文字列strから位置posで始まる部分文字列を返します。lenパラメータ付きの形式は、文字列strから長さlenの部分文字列を返し、位置posで始まります。FROM形式は標準SQL構文です。posに負の値を使用することもできます。この場合、部分文字列の位置は文字列の末尾のpos文字から始まります。

       　mysql> SELECT SUBSTRING('Quadratically',5);
            -> 'ratically'

        mysql> SELECT SUBSTRING('foobarbar' FROM 4);
            -> 'barbar'

        mysql> SELECT SUBSTRING('Quadratically',5,6);
            -> 'ratica'

        mysql> SELECT SUBSTRING('Sakila', -3);
            -> 'ila'

        mysql> SELECT SUBSTRING('Sakila', -5, 3);
            -> 'aki'

        mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
            -> 'ki'

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)
       文字列strから、すべてのremstr接頭辞および/または接尾辞が削除されたものを返します。BOTH、LEADIN、またはTRAILINGのいずれかが指定されていない場合、BOTHと見なされます。remstrはオプションであり、指定されていない場合は空白が削除されます。

       　mysql> SELECT TRIM('  bar   ');
                -> 'bar'

        mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
                -> 'barxxx'

        mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
                -> 'bar'

        mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
                -> 'barx'

ユーザー定義関数の作成#

delimiter\\
create function f1(
    i1 int,
    i2,int)
returns int
BEGIN
    declare num int;
    set num = i1 + i2;
    return(num);
END\\
delimiter;

関数の削除#

drop function f1;

関数の実行#

//戻り値を取得
declare @i VARCHAR(32);
select UPPER('alex') into @i;
SELECT @i;

//クエリで使用
select f1(11,nid),name from tb2;

トランザクション#

トランザクションは、特定の操作の複数の SQL を原子操作として扱うために使用されます。一度でもエラーが発生すると、元の状態にロールバックされ、データベースのデータの完全性が保証されます。例えば、2 つの銀行カード間での送金時、甲の方からお金が出て、突然光ファイバーが壊れ、乙の方はまだお金を受け取っていない場合、お金はどこに行ったのでしょうか。このような状況を防ぐために、トランザクションが存在します。トランザクションはこのような事態を防ぐことができます。

トランザクションの適用例#

delimiter \\
create PROCEDURE p1(
    OUT p_return_code tinyint
)
BEGIN 
  DECLARE exit handler for sqlexception 
  BEGIN 
    -- エラー 
    set p_return_code = 1; 
    rollback; 
  END; 

  DECLARE exit handler for sqlwarning 
  BEGIN 
    -- 警告 
    set p_return_code = 2; 
    rollback; 
  END; 

  START TRANSACTION; 
    DELETE from tb1;                   -- SQL文はここにすべて置かれます
    insert into tb2(name)values('seven');
  COMMIT; 

  -- 成功 
  set p_return_code = 0; 

  END\\
delimiter ;

ストアドプロシージャの実行#

DECLARE @i TINYINT;
call p1(@i);
select @i;

トリガー TRIGGER#

トリガーは、簡単に言うと、特定の文を実行する前または後に一度増減改査をトリガーするものです。トリガーは、ユーザーがテーブルの行に対して【増 / 減 / 改】を行う前後の動作をカスタマイズするために使用されます。

基本構文#

# 挿入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 挿入後
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 削除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 削除後
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新後
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

例 1 挿入前

-- tab1にデータを挿入する前に、tab2にname = 張岩林を挿入します。もちろん、tab1に挿入する名前がaylinと等しいかどうかを確認します。
delimiter //
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN

IF NEW. NAME == 'aylin' THEN
    INSERT INTO tb2 (NAME)
VALUES
    ('張三')
END
END//
delimiter ;

例 2 挿入後

delimiter //
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    IF NEW. num = 666 THEN
        INSERT INTO tb2 (NAME)
        VALUES
            ('張三'),
            ('とてもイケメン') ;
    ELSEIF NEW. num = 555 THEN
        INSERT INTO tb2 (NAME)
        VALUES
            ('aylin'),
            ('非常にイケメン') ;
    END IF;
END//
delimiter ;

同様に、削除、変更、検索も同じ理屈です。

特別なこととして：NEW は挿入されるデータ行を示し、OLD は削除されるデータ行を示します。

トリガーの削除#

DROP TRIGGER tri_after_insert_tb1;

トリガーの使用#

トリガーはユーザーによって直接呼び出すことはできず、テーブルに対する【増 / 減 / 改】操作によって自動的に引き起こされます。

insert into tb1(name) values(‘張三’)