MySQL文とコマンド大全

19527 ワード
linux 維を運ぶ dba mysql
前言
ここに記録されているのは、この2年間の勉強の過程で出会ったよく使われるMySQL文と命令で、一部はネット上で収集されたもので、出典はもう覚えていません.ここではまずこれらの大物に感謝しました.この文書には、一般的なSQL文と、一部のディメンション文とコマンドが含まれています.詳細な説明はありませんが、一例を示します.これだけの文はもちろん全とは言えませんが、後続もどんどん増えていきます.記録が少し乱れていて、必要なものがあれば適宜抽出することができます.
一、ユーザー接続、作成、権限、削除
1.MySQL操作への接続mysql -h -u -P -p
SSL接続の使用mysql --ssl-ca=ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem -h -u -p
2.ユーザーの作成CREATE USER 'username'@'host' IDENTIFIED BY 'password';
  • hostは、そのユーザがどのホスト上にログイン可能かを指定する、ローカルユーザが利用可能であるlocalhostであれば、そのユーザが任意のリモートホストからログイン可能であるようにするには、ワイルドカード%を使用することができる.

    • 3.授権GRANT [all privileges/ ] ON databasename.tablename TO 'username'@'host';
    ユーザーに権限を与えるには、次のコマンドを使用します.GRANT all privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
    4.ユーザーのロック
    ALTER USER 'username'@'host' ACCOUNT LOCK;

    ロック解除
    ALTER USER 'username'@'host' ACCOUNT UNLOCK;

    一般的なシーン:
    1読み書き権限を作成するユーザー
    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, EXECUTE, CREATE VIEW, SHOW VIEW ON databasename.tablename TO 'username'@'host';

    2読取り専用アクセス権ユーザーの作成
    GRANT SELECT,SHOW VIEW ON databasename.tablename TO 'username'@'host';

    4.ユーザーパスワードの設定と変更
    方法1SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');
    現在ログインしているユーザーの場合SET PASSWORD = PASSWORD("newpassword");
    方法2
    ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';

    5.ユーザー権限の取り消しREVOKE [ALL/ ] ON databasename.tablename FROM 'username'@'host';
    あるユーザ権限は、コマンドSHOW GRANTS FOR 'username'@'host';で表示することができる.
    5.ユーザー名の変更
    rename user 'old_name'@'host' to 'new_name'@'host';

    7.ユーザーの削除
    DROP USER 'username'@'host';

    8.sslログインを要求
    #         
ALTER USER 'username'@'%' REQUIRE SSL;

#     
create user username_ssl@'%' identified by 'password' require ssl;

    権限テーブルのリフレッシュflush privileges;
    二、データベースとテーブルの表示、作成、削除
    1.データベースの表示&作成&削除
    --      
show databases; 

--    
create database [IF NOT EXISTS]  [character set='utf8'];

--    
drop database ;

    2.表の表示、作成、削除
    --      
use ;
show tables;

--    ：
create table    (      ) [engine=InnoDB] [charset=utf8mb4];

--        DDL   
show create table ;

--      
desc ;

--    
drop table [IF EXISTS] ; 

--    
CREATE TEMPORARY TABLE ();

    e.g. 
    CREATE TABLE
    USER
    (
        id INT NOT NULL AUTO_INCREMENT,
        stu_id INT NOT NULL,
        name VARCHAR(30) NOT NULL,
        phone VARCHAR(20),
        address VARCHAR(30) NOT NULL,
        age INT NOT NULL,
        PRIMARY KEY (id),
        UNIQUE KEY `un_stu_id` (stu_id),
        KEY `idx_name` (`name`) USING BTREE
    )
    ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

    注意:textなどのインデックスを作成する場合は、長さe.g.KEY idx_を指定します.text (f_text(64))
    三、テーブルのコピー、バックアップのリストアとクリア
    1.テーブル構造のコピー
    プライマリ・キーなどの情報を含む完全なテーブル構造: CREATE table LIKE book;
    テーブル構造のみで、プライマリ・キーなどの情報はありません.
    create table     select * from books;
 
create table     as(select * from book);
 
create table     select * from books where1=2;

    注意:create table select ...はインデックスが失われます
    2.古いテーブルのデータを新しいテーブルに入力INSERT INTO SELECT * FROM ;
    3.テーブルを作成するDDL文を表示するshow create table ;
    4.テーブルデータを空にするtruncate table ;
    5.データベースのバックアップ
    単一ライブラリのバックアップ
    shell> mysqldump --single-transaction --master-data=2 --default-character-set=utf8 -u root -p  >database_name.sql

    テーブルのバックアップ
    shell> mysqldump --single-transaction --master-data=2 --default-character-set=utf8 -u root -p   > table_name.sql

    複数のライブラリのバックアップ
    shell> mysqldump --single-transaction --master-data=2 --default-character-set=utf8 -u username -p --databases   > Backup.sql

    すべてのライブラリをバックアップ
    shell> mysqldump --single-transaction --master-data=2 --default-character-set=utf8 -A -u root -p > back.sql

    一部のライブラリテーブルを無視
    --ignore-table=performance_schema.* --ignore-table=information_schema.* --ignore-table=sys.* --ignore-table=test.*

    テープ圧縮
    shell> mysqldump -A | gzip >> backup.sql.gz

    6.データベースのリストア
    shell> mysql -u root -p -f [database_name] < backup.sql

    7.バックアップファイルからデータを抽出する
    ライブラリのすべてのデータを抽出
    shell> sed -n '/^-- Current Database: `test_restore`/,/^-- Current Database:/p' mysql_back.sql

    テーブル文のみ抽出
    shell> sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `test1`/!d;q' mysql_back.sql

    データのみ抽出
    shell> grep -i 'INSERT INTO `test1`' mysql_back.sql

    すべてのライブラリ・テーブル・ステートメントの抽出
    shell> grep -iv 'INSERT INTO `' mysql_back.sql

    8.データのエクスポート
    9.データのインポートload data infile "" into table ;
    四、表の列と表名の変更
    1.列の名前を変更alter table change
    2.表の名称変更alter table rename
    3.テーブルのフィールドタイプを変更し、空または空でないように指定します.alter table change [not null]; alter table modify [not null];
    4.フィールドを1つ追加(列)alter table add column ;
    フィールドの後に追加alter table add column after ;
    いちばん前につけるalter table add column first;
    5.フィールド名を変更します(タイプとデフォルトを変更することもできます).alter table change ;
    6.フィールドのデフォルト値を変更するalter table alter set default ;
    この方法ではテーブルはロックされません
    7.フィールドのデータ型を変更するalter table change column ;
    8.フィールドの削除alter table drop column ;
    五クエリー表
    SELECT [DISTINCT] 

FROM  [ JOIN  ON ]

WHERE 

GROUP BY 

HAVING 

ORDER BY  [desc/asc]

LIMIT n[, m]

    1.GROUP BYと集約関数の使用上の注意点
    1集約関数を使用しない場合、groupby句にすべての列を含める必要があります.そうしないと、エラーが発生します.
    正しい:select name,age from test group by name,age; // select 2 groupby句に集約関数のカラム名を付けない
    2. having
    SQL規格では、havingがgroup句の列を参照するか、集約関数で処理した列を参照する必要があります.
    mysqlは、この基準をいくつか拡張し、havingがselectで取得したカラムと外部クエリのカラムを参照できるようにします.
    havingで使用される条件はgroupbyで表示されるか、selectの列で表示されるか、外部クエリーで表示されます.
    3. from
    fromサブクエリでは、データテーブルに別名を指定します.from (select ..) [as]別名where...
    4. union select union [all] select
    ユニオンは重くなる
    5.join外部接続クエリーselect * from tableA A [left、right] join tableB B on A.id = B.id
    6.joinクロスコネクションselect * from tableA,tableB select * from tableA cross join tableB
    カンマとcross joinの違いはカンマがonを使用できないことです
    結果はn*nレコード(デカルト積)
    7.join内部接続select * from tableA A inner join tableB B on A.id = B.id select * from tableA A inner join tableB B using(id) using( )は同じフィールドをマージすることができ、A.id=B.idに合致する.
    内部ジョインは、条件がない場合と交差ジョインの違いはありません.STRAIGHT_JOINドライバテーブルは手動で指定できます
    六索引の作成、削除、表示
    1.索引の作成
    方法1
    --     
ALTER TABLE     ADD INDEX index_name (column_list)
--     
ALTER TABLE     ADD UNIQUE (column_list)
--     
ALTER TABLE     ADD PRIMARY KEY (column_list)

    方法2
    CREATE INDEX index_name ON     (column_list)
CREATE UNIQUE INDEX index_name ON     (column_list)

    column_listは、どのカラムをインデックスするかを示し、複数カラムの場合、各カラム間をカンマで区切る.インデックス名index_nameはオプションで、デフォルトではMySQLは最初のインデックス列に基づいて名前を付けます.また、ALTER TABLEでは、単一の文で複数のテーブルを変更できるため、同時に複数のインデックスを作成できます.
    2.索引の削除
    --     
DROP INDEX index_name ON    ;
ALTER TABLE     DROP INDEX index_name;
--     
ALTER TABLE     DROP PRIMARY KEY;

    3.索引の表示
    show index from    ;
show keys from    ;

    4.手動で索引を選択
  • USE INDEX:インデックス
    • の選択方法をオプティマイザに提示
  • IGNORE INDEX:インデックスを無視
  • FORCE INDEX:インデックス
    • の強制使用
    select * from tableA USE INDEX (key1, key2) where key1=1 and key2=2

    七外部キー
    1.外部キーを追加
    表作成時constraint foreign key( ) references ( );
    テーブルの変更alter table add constraint foreign key( ) references ( );
    2.外部キーの削除ALTER TABLE table-name DROP FOREIGN KEY key-id;
    八フロー制御&関数
    1内蔵関数&メソッド
    1.1 if IF(expr1,expr2,expr3)
    EXpr 1がTRUE(expr 1<>0 and expr 1<>NULL)である場合、IF()の戻り値はexpr 2である.そうでない場合、戻り値はexpr 3になります.
    1.2 CASE when SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END as testCol
    1.3 IFNULL IFNULL(expr1,expr2)
    expr 1がNULLでない場合、IFNULL()の戻り値はexpr 1である.そうでない場合、その戻り値はexpr 2です.
    2カスタムストレージ・プロシージャ&関数
    2.1表示
    データベース内のストアド・プロシージャと関数のクエリー
    -     
show procedure status;
select `name` from mysql.proc where db = '' and `type` = 'PROCEDURE';

--   
show function status;
select `name` from mysql.proc where db = '' and `type` = 'FUNCTION'

    ストアド・プロシージャまたは関数の作成コードの表示
    show create procedure ;
show create function ;

    9ビュー
    1.作成create or replace view (,...) as
    2.削除drop view [， 2.... n];
    10フリップフロップ
    trigger_time: { BEFORE | AFTER } --           
trigger_event: { INSERT | UPDATE | DELETE } --     
trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

    トリガを定義すると仮定し、ctimeを挿入するたびに現在の時間に設定します.
    delimiter // --      
create trigger 
before insert on 
for each row
begin
set new.ctime=now();
end;//
delimiter ;

    トリガを定義すると仮定し、更新ごとにmtimeを現在の時間に設定します.
    delimiter // --      
create trigger 
before update on 
for each row
begin
set new.mtime=now();
end;//
delimiter ;

    十一ステータスの表示
    #     
status;
show status;

# innodb   
show innodb status;

#     
show variables like '%    %';

#       
select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

    12インポートのエクスポート
    1.ファイルにエクスポート
    select * into outfile      [    ] form tableA;

    csvにエクスポートし、圧縮
    shell> mysql -B -u   -p -e "SELECT  " | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/
    //g" | gzip > data.csv.gz

    コントロールフォーマットとインポートファイル
    2.ファイルのインポート
    load data infile     [replace|ignore] into table    [    ]
  • replaceおよびignore:プライマリ・キー・リピートを示すデータ処理方式
    • .
  • 制御フォーマットfields terminated by '\t' enclosed by '' escaped by '\\'
    • e.g. 
    SELECT * INTO OUTFILE '/tmp/data.txt' 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '
    '
FROM tableA;

    十三統計文
    1.最大使用可能メモリの計算
    MySQL >= 8 
    select
(@@key_buffer_size + @@query_cache_size + @@tmp_table_size
+ @@innodb_buffer_pool_size +
@@innodb_additional_mem_pool_size
+ @@innodb_log_buffer_size
+ @@max_connections * (
@@read_buffer_size + @@read_rnd_buffer_size
+ @@sort_buffer_size+ @@join_buffer_size
+ @@binlog_cache_size + @@thread_stack
)
)/1024/1024/1024 as max_mem_G;

    MySQL < 8 
    select
(@@key_buffer_size + @@query_cache_size + @@tmp_table_size
+ @@innodb_buffer_pool_size
+ @@innodb_log_buffer_size
+ @@max_connections * (
@@read_buffer_size + @@read_rnd_buffer_size
+ @@sort_buffer_size+ @@join_buffer_size
+ @@binlog_cache_size + @@thread_stack
)
)/1024/1024/1024 as max_mem_G;

    2.データサイズ
    データの合計サイズ
    SELECT ROUND(SUM(DATA_LENGTH)/1024/1024/1024,2) as data_size_G,ROUND(SUM(INDEX_LENGTH)/1024/1024/1024,2) as index_G, ROUND(SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024/1024,2) as total_size_G,SUM(TABLE_ROWS) as rows FROM information_schema.TABLES;

    ライブラリサイズ
    SELECT ROUND(SUM(DATA_LENGTH)/1024/1024/1024,2) as data_size_G,ROUND(SUM(INDEX_LENGTH)/1024/1024/1024,2) as index_G, ROUND(SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024/1024,2) as total_size_G,SUM(TABLE_ROWS) as rows FROM information_schema.TABLES WHERE TABLE_SCHEMA='  ';

    統計すべてのライブラリをサイズ順に並べ替え
    SELECT TABLE_SCHEMA, ROUND(SUM(DATA_LENGTH)/1024/1024/1024,2) as data_size_G,ROUND(SUM(INDEX_LENGTH)/1024/1024/1024,2) as index_G, ROUND(SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024/1024,2) as total_size_G,SUM(TABLE_ROWS) as rows FROM information_schema.TABLES group by TABLE_SCHEMA order by data_size_G desc;

    3.統計接続IP
    select SUBSTRING_INDEX(host,':',1) as ip , count(*) from information_schema.processlist group by ip;

    4.ロックの表示
    mysql5.6 
    SELECT
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM
    information_schema.innodb_lock_waits w
INNER JOIN
    information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN
    information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;



waiting_trx_id --      ID
waiting_thread --      ID
waiting_query --    SQL  
blocking_trx_id --           ID
blocking_thread --      ID
blocking_query --       SQL，        ，  SQL       （              SQL  ）

    mysql5.7 
    select * from sys.innodb_lock_waits;

    5.プライマリ・キーのないテーブルの表示
    SELECT
    table_schema, table_name
FROM
    information_schema.TABLES
    WHERE
    table_name NOT IN (
        SELECT DISTINCT
            TABLE_NAME
        FROM
            information_schema.COLUMNS
        WHERE
            COLUMN_KEY = 'PRI')
        AND table_schema NOT IN ('mysql' , 'information_schema','sys', 'performance_schema');

    6.インデックスの妥当性
    SELECT
    t.TABLE_SCHEMA,t.TABLE_NAME,INDEX_NAME, CARDINALITY, TABLE_ROWS,
    CARDINALITY/TABLE_ROWS AS SELECTIVITY
FROM
    information_schema.TABLES t,
    (
        SELECT
            table_schema,
            table_name,
            index_name,
            cardinality
        FROM information_schema.STATISTICS
        WHERE (table_schema,table_name,index_name,seq_in_index) IN (
            SELECT
                table_schema,
                table_name,
                index_name,
                MAX(seq_in_index)
            FROM
                information_schema.STATISTICS
            GROUP BY table_schema , table_name , index_name
        )
    ) s
WHERE
    t.table_schema = s.table_schema
        AND t.table_name = s.table_name 
        AND t.table_schema = '    ' --        
ORDER BY SELECTIVITY;
  • SELECTIVITYが1に近いほど、合理的な
  • 集約インデックスはstatisticsテーブルに複数のデータを生成するので、MAX(seq_in_index)は完全インデックスの
    • を取得することができる.
    7.統計processlist各状態数
    shell> mysql -uroot -p -e 'show processlist \G' | grep 'State:' | sort | uniq -c | sort -rn

    14次元文&コマンド
    1.統計の更新
    analyze table ;

    2.表の再整理
    optimize table ;

    3.チェックシート(MyISAM)
    check table ;

    4.修復テーブル(MyISAM)
    repair table ;

    5.一括検査または修復表
    #      
shell> mysqlcheck -u root -p -A -c 

#      
shell> mysqlcheck -u root -p -A -c

    6.統計毎秒遅いログ
    shell> awk '/^# Time:/{print $3, $4, c;c=0}/^# User/{c++}' slowquery.log

    7.binlogログの表示
    positionベース
    mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS --start-position= --stop-position=  > result.sql

    ポイントベース
    mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS --start-datetime='' --stop-datetime=''  > result.sql

    posのログを表示
    mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS  | grep -A '20'

    8.オープンハンドル数
    各プロセスが開くハンドルの数を統計します.
    lsof -n|awk '{print $2}'|sort|uniq -c|sort -nr | head -n 10

#     
lsof -n|awk '$2=="" {print $2}'|sort|uniq -c|sort -nr | head -n 10

    各ユーザーのオープンハンドル数の統計
    lsof -n|awk '{print $3}'|sort|uniq -c|sort -nr

# mysql  
lsof -n|awk '$3 == "mysql" {print $3}'|sort|uniq -c|sort -nr

    各コマンドのオープンハンドル数の統計
    lsof -n|awk '{print $1}'|sort|uniq -c|sort -nr

    9.ユーザー権限のエクスポート(shellスクリプト)
    #/bin/bash

user='username'
pass='password'
sock='socket'

expgrants()  
{  
  mysql -B -u"${user}" -p"${pass}" -S"${sock}" -N $@ -e "SELECT CONCAT(  'SHOW CREATE USER   ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \
  mysql -u"${user}" -p"${pass}" -S"${sock}" -f  $@ | \
  sed 's#$#;#g;s/^\(CREATE USER for .*\)/-- \1 /;/--/{x;p;x;}' 
 
  mysql -B -u"${user}" -p"${pass}" -S"${sock}" -N $@ -e "SELECT CONCAT(  'SHOW GRANTS FOR ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \
  mysql -u"${user}" -p"${pass}" -S"${sock}" -f  $@ | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/-- \1 /;/--/{x;p;x;}'   
}

    次にスクリプトを実行
    10.一括殺接続
    方法1
    select concat('KILL ',id,';') from information_schema.processlist where user='    ' into outfile '/tmp/kill.txt';
source /tmp/kill.txt;

    方法2
    mysqladmin -uroot -p processlist|awk -F "|" '{if($3 == "        ")print $2}'|xargs -n 1 mysqladmin -uroot -p kill
    51サービスのオープン方式
    luaベース