MySQLメモ

更新日：2018/08/12

MySQLについてのメモ書きです。随時更新する予定です。


よく使うコマンド
--◆テーブル一覧
SHOW TABLES;

SELECT TABLE_NAME,
       TABLE_ROWS,
       TABLE_COMMENT,
       TABLE_TYPE
  FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'データベース名'
   AND TABLE_TYPE LIKE '%TABLE%'

--◆インデックス一覧
SHOW INDEXES FROM 'テーブル名';

SELECT TABLE_NAME, INDEX_NAME
  FROM INFORMATION_SCHEMA.STATISTICS
 WHERE TABLE_SCHEMA = 'データベース名'
 GROUP BY TABLE_NAME, INDEX_NAME
 ORDER BY 1;

--◆DBエンジンINNODBのステータスを表示する
--トランザクションのロック情報を確認する
SHOW ENGINE INNODB STATUS \G


ログインユーザーのパスワード変更
--◆rootユーザー
mysqladmin -u root password
New password: パスワード
Confirm new password: パスワード


MySqlの起動確認
mysqladmin -u root -p ping


MySqlクライアント　ログイン・ログアウト
--◆ログイン
-- -u ユーザ名
-- -p パスワード
mysql -u root -p

--ポート番号指定
mysql -u root -p -P3306


--◆ログアウト
exit;
quit;


MySqlバージョンの確認
select version();


データベース
--◆データベースの一覧
show databases;


--◆データベースの作成
create database testdb;


--◆データベースの使用
use testdb;


テーブル操作
--◆テーブル一覧の表示
SHOW TABLES FROM データベース名;

SELECT TABLE_SCHEMA,
       TABLE_NAME,
       TABLE_ROWS,
       TABLE_COMMENT
  FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'データベース名'
   AND TABLE_TYPE   = 'BASE TABLE' -- BASE TABLE, VIEW

--◆テーブル構造の表示
SHOW FIELDS FROM テーブル名;
DESC テーブル名;
DESCRIBE テーブル名;


--◆テーブルの作成
--（例）
CREATE TABLE members (
    id        INT AUTO_INCREMENT              COMMENT 'ID',
    name      VARCHAR(30)                     COMMENT '名前',
    depart    VARCHAR(30) DEFAULT '無所属'    COMMENT '所属',
    age       INT                             COMMENT '年齢',
    updated   DATETIME                        COMMENT '更新日時',
    PRIMARY   KEY(id)
) DEFAULT CHARSET=utf8;


--◆テーブルの削除
DROP TABLE members;


レコード操作
--◆レコードの作成
INSERT INTO members (id, name, depart, age, updated) VALUES ( 1,  '山本　太郎', '営業部',       51, cast( sysdate() as datetime));
INSERT INTO members (id, name, depart, age, updated) VALUES ( 2,  '山本　次郎', '事業企画室',   19, cast( sysdate() as datetime));
INSERT INTO members (id, name, depart, age, updated) VALUES ( 3,  '山本　三郎', '経理部',       44, cast( sysdate() as datetime));
INSERT INTO members (id, name, depart, age, updated) VALUES ( 4,  '山本　四郎', '事業推進１部', 36, cast( sysdate() as datetime));
INSERT INTO members (id, name, depart, age, updated) VALUES ( 5,  '山本　五郎', '事業推進２部', 25, cast( sysdate() as datetime));
INSERT INTO members (id, name, depart, age, updated) VALUES ( 6,  '山本　六郎', '事業推進３部', 41, cast( sysdate() as datetime));
INSERT INTO members (id, name, depart, age, updated) VALUES ( 7,  '山本　七郎', '事業推進４部', 52, cast( sysdate() as datetime));
INSERT INTO members (id, name, depart, age, updated) VALUES ( 8,  '山本　八郎', '事業推進５部', 59, cast( sysdate() as datetime));
INSERT INTO members (id, name, depart, age, updated) VALUES ( 9,  '山本　九郎', '品質管理部',   35, cast( sysdate() as datetime));
INSERT INTO members (id, name, depart, age, updated) VALUES ( 10, '山本　十郎', '総務部',       27, cast( sysdate() as datetime));


--◆レコードの更新
UPDATE members SET depart = '秘書室', updated = '2018-01-01' WHERE id = 8;


--◆レコードの削除
DELETE FROM members WHERE id = 3;


ユーザ
--◆データベースユーザ一覧の表示
SELECT host, user FROM mysql.user;


--◆データベースユーザの作成
CREATE USER test1@localhost IDENTIFIED BY 'パスワード';


--◆データベースユーザへの権限付与
GRANT ALL PRIVILEGES ON jsp10.* TO test1@localhost;


--設定を反映する
FLUSH PRIVILEGES;


ビュー
--◆ビューの一覧
SELECT *
  FROM INFORMATION_SCHEMA.VIEWS
 WHERE TABLE_SCHEMA = 'データベース名'
 ORDER BY TABLE_NAME;

--◆ビューの作成
CREATE VIEW ビュー名 AS SELECT * FROM テーブル名;

--◆ビューの削除
DROP VIEW IF EXISTS ビュー名;


カラム
--◆カラム一覧
SELECT * 
  FROM INFORMATION_SCHEMA.COLUMNS 
 WHERE TABLE_SCHEMA = 'データベース名'
 ORDER BY TABLE_NAME, ORDINAL_POSITION


トランザクション
--◆AutoCommitの確認
select @@global.autocommit, @@session.autocommit;
show variables like 'autocommit';

--◆AutoCommitの設定
-- 0：無効　 1：有効
SET AUTOCOMMIT=0;

--◆セッションの表示
show processlist;

--◆ロックタイムアウト値
show global variables like 'innodb_lock_wait_timeout';

--◆ロックタイムアウト値の変更
SET innodb_lock_wait_timeout=1;

--◆InnoDBモニターの有効化
--トランザクションのロック情報を確認するときに使用する
SELECT @@GLOBAL.INNODB_STATUS_OUTPUT, @@GLOBAL.INNODB_STATUS_OUTPUT_LOCKS;
SET GLOBAL INNODB_STATUS_OUTPUT=ON|OFF
SET GLOBAL INNODB_STATUS_OUTPUT_LOCKS=ON|OFF

--◆DBエンジンINNODBのステータスを表示する
--トランザクションのロック情報を確認する
SHOW ENGINE INNODB STATUS \G

--◆トランザクション分離レベル
SELECT @@GLOBAL.TX_ISOLATION, @@SESSION.TX_ISOLATION;
SHOW VARIABLES LIKE 'TX_ISOLATION';
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

--◆行ロック（排他ロック）
SELECT *
  FROM TEST
 WHERE COL1 = 'AAA'
   FOR UPDATE;

--◆行ロック（共有ロック）
SELECT *
  FROM TEST
 WHERE COL1 = 'AAA'
  LOCK IN SHARE MODE;

--[補足]排他ロックと共有ロックの違い
--共有ロックは、他のトランザクションから参照（SELECT）が可です。
--排他ロックは、他のトランザクションから操作（SELECT,INSERT,UPDATE,DELETE）が不可です。

--◆ロック状態確認
--Table_locks_immediate
--Table_locks_waited
SHOW STATUS LIKE 'Table%';

--◆ロック件数確認
SELECT TRX_ID,
       TRX_STATE,
       TRX_MYSQL_THREAD_ID,
       TRX_TABLES_IN_USE,
       TRX_TABLES_LOCKED,
       TRX_ROWS_LOCKED,
       TRX_ROWS_MODIFIED,
       TRX_UNIQUE_CHECKS
  FROM INFORMATION_SCHEMA.INNODB_TRX;

[Windows 10]
C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
[mysqld]
innodb_lock_wait_timeout = 1

[AutoCommitの参考URL]
https://open-groove.net/mysql/autocommit/
https://atsuizo.hatenadiary.jp/entry/2016/12/05/000000


インデックス
--◆インデックスの表示
show indexes from テーブル名;

SELECT TABLE_NAME, INDEX_NAME
  FROM INFORMATION_SCHEMA.STATISTICS
 WHERE TABLE_SCHEMA = 'データベース名'
 GROUP BY TABLE_NAME, INDEX_NAME
 ORDER BY 1;


--◆インデックスの作成
CREATE INDEX インデックス名 ON テーブル名 (カラム名１, カラム名２, ・・・);
ALTER TABLE テーブル名 ADD INDEX インデックス名(カラム1, カラム2, ・・・);


--◆ユニークインデックスの作成
CREATE UNIQUE INDEX インデックス名 ON テーブル名 (カラム名１, カラム名２, ・・・);
ALTER TABLE テーブル名 ADD UNIQUE インデックス名(カラム1, カラム2, ・・・);


--◆インデックスの削除
ALTER TABLE テーブル名 DROP INDEX インデックス名;

[参考]
http://phpjavascriptroom.com/?t=mysql&p=index


関数
--システム日時
--SYSDATE() は NOW() のシノニム
SELECT NOW(), NOW()+0, SYSDATE() FROM DUAL;
+---------------------+----------------+---------------------+
| NOW()               | NOW()+0        | SYSDATE()           |
+---------------------+----------------+---------------------+
| 2018-09-24 22:46:51 | 20180924224651 | 2018-09-24 22:46:51 |
+---------------------+----------------+---------------------+

--システム日付
--CURRENT_DATE と CURRENT_DATE() は、curdate()のシノニム。
SELECT CURDATE(), CURDATE()+0, CURRENT_DATE, CURRENT_DATE() FROM DUAL;
+------------+-------------+--------------+----------------+
| CURDATE()  | CURDATE()+0 | CURRENT_DATE | CURRENT_DATE() |
+------------+-------------+--------------+----------------+
| 2018-09-24 |    20180924 | 2018-09-24   | 2018-09-24     |
+------------+-------------+--------------+----------------+


設定ファイル
◆設定ファイルの場所
[Windows 10]
C:\ProgramData\MySQL\MySQL Server 5.7\my.ini

[CentOS x.x]


ポート番号変更
[Windows 10]
C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
[client]
#port=3306
port=3307

[mysqld]
#port=3306
port=3307


文字コード
--◆文字コードの確認
show variables like '%char%';

--◆作成したテーブル/カラム/の文字コード確認
show create table 'テーブル名';


◆文字コードの変更
[Windows 10]
C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
[mysqld]
character-set-server=utf8

[client]
default-character-set=utf8


開発環境構築
環境
・Windows 10
・GlassFish 4.1.1
・MySQL

手順
（１）JDBCライブラリの配置
（２）コネクションプールの作成
（３）JDBCリソースの作成
（４）持続性ユニットの作成（JPAを使用する場合）
（５）その他

（１）JDBCライブラリの配置
JDBCファイル：mysql-connector-java-5.1.45-bin.jar
配置先パス：C:\Users\ユーザ名\AppData\Roaming\NetBeans\8.2\config\GF_4.1.1\domain1\lib

（２）コネクションプールの作成
GlassFish のコネクションプール
cd C:\Program Files\glassfish-4.1.1\glassfish\bin
asadmin create-jdbc-connection-pool --datasourceclassname com.mysql.jdbc.jdbc2.optional.MysqlDataSource --restype javax.sql.DataSource mySqlPool

GlassFishの管理画面から、作成したコネクションプールに対して、プロパティを設定する。
＜例＞
ServerName　  ：localhost
DatabaseName　：MySQL
user　        ：test1
password　    ：test1のパスワード
URL　         ：jdbc:mysql://locahost:3306/データベース名
port　        ：3306


[補足]
GlassFish コネクションプールの削除
cd C:\Program Files\glassfish-4.1.1\glassfish\bin
asadmin delete-jdbc-connection-pool --cascade true mySqlPool


（３）GlassFish JDBCリソースの作成
cd C:\Program Files\glassfish-4.1.1\glassfish\bin
asadmin create-jdbc-resource mySqlPool
Enter the value for the connectionpoolid option> jdbc/mySqlResource
Enter the value for the jndi_name operand> 
JDBC resource jdbc/mySqlResource created successfully.
Command create-jdbc-resource executed successfully.


[補足]
GlassFish JDBCリソースの削除
cd C:\Program Files\glassfish-4.1.1\glassfish\bin
asadmin delete-jdbc-resource
Enter the value for the jdbc_resource_name operand> jdbc/mySqlResource
JDBC resource jdbc/tsukuyomiPool deleted successfully
Command delete-jdbc-resource executed successfully.


（４）持続性ユニットの作成（JPAを使用する場合）


（５）その他
◆Javaプログラムからのルックアップ先
java:comp/env/jdbc/mySqlResource



以上
-
ラベル: ,

0 件のコメント:

コメントを投稿

登録: コメントの投稿 (Atom)

最新

【PHP】スーパーグローバル変数 $_GET $_POST $_REQUEST

スーパーグローバル変数 $_REQUESTは、HTTPメソッドがGETとPOSTのいずれの場合もデータを取得することができる。 $_REQUEST['xxx'] $_GET['xxx'] $_POST['xxx'] ...