MySQLの使い方

注：

SQLのワイルドカード文字は以下の通り
・ %: 任意の長さの文字列
・ _: 任意の位置文字
・ []: []内の任意の一文字に一致
・ [^] []内の任意の一文字以外に一致
ワイルドカード一致は like 演算子を使う
例: where user LIKE '%kamiya%';
ネットワーク越しのユーザー名は、'khlab'@'usrv.khlab.lan'などと指定する。
@を含めてクォートすると、SQLが@で区切れなくなるので注意すること。
ワイルドカードを使う場合は、''でくくって % を使う: 例　'khlab'@'%.khlab.lan'

★起動: mysql -h host_name -u user_name -p
★終了: mysql> exit
★rootへのパスワード設定: set password for root=PASSWORD('パスワード');
注意： flush privileges; が必要
★匿名ユーザーの削除:
mysql> use mysql;
mysql> delete from user where user='';
mysql> flush privileges;
★一般ユーザーの作成:
mysql> grant all on *.* to ユーザー名 identified by 'パスワード';
mysql> flush privileges;

★パスワードの設定:
mysql> set password for ユーザー名=PASSWORD('パスワード');
mysql> flush privileges;
★権限設定
mysql> grant all on woe11_db.* to woe11_web identified by 'PASSWORD' with grant option;
★パスワード変更
mysql> set password = PASSWORD('パスワード');
mysql> set password for ユーザー名 = PASSWORD('パスワード');
★データベース作製
mysql> create database woe11_db;
　　　(ユーザ名にwoe11-webが使えなかった)
mysql> flush privileges;
★データベースを使う宣言
mysql> use データベース名;
★データの取得
mysql> select * from データベース名.テーブル名;
mysql> select * from データベース名.テーブル名 where EMail='tkamiya' order by sn DESC;
mysql> select * from データベース名.テーブル名 where EMail like '%tkamiya%' order by sn DESC;
★データの作製
mysql> insert テーブル名(フィールド名,フィールド名,,,) values(値,値,,,);
★データの更新
mysql> update テーブル名 set フィールド名="値",フィールド名="値" where ...;
★データの削除
mysql> delete from テーブル名 where ..;
★テーブルの作製
mysql> create table テーブル名(
　　　　　　　フィールド名型オプション,
　　　　　　　);
★フィールドの型
　CHAR(バイト数) (最大255文字)
　VARCHAR(バイト数) (最大65,535文字)
　TEXT （65,535bytes） (最大65,535文字))
　MEDIUMTEXT （16,777,215bytes）
　LONGTXT （4,24,967,295bytes）
　JSON
　BLOB (バイナリ大オブジェクト。最大65,535byte)
　INT (32bit)
DECIMAL (固定小数点数)
　FLOAT
　DOUBLE
　TIME
　DATE
　DATETIME
　TIMESTAMP
　YEAR
　TINYINT (8bit)
　SMALLINT (16bit)
MEDIUMINT (32bit)
BIGINT (64bit)
　TINYTEXT (最大255文字)
　MEDIUMTEXT (最大16,777,215文字)
　LONGTEXT (最大4,294,967,295文字)
　MEDIUMBLOB (中程度のバイナリ大オブジェクト。最大16,777,215byte)
　LONGBLOB (大きなバイナリ大オブジェクト。最大4,9294,967,295byte)
　ENUM (列挙型)
　SET (集合)

例：create table a_tbl(
id int not null auto_increment,
name varchar(20) not null,
primary key(id)
);

★テーブル名変更
mysql> alter table テーブル名 rename 新テーブル名;
★テーブルのフィールド追加
mysql> alter table テーブル名 add フィールド名フィールド定義「FIRST|AFTER フィールド名];
mysql> alter table テーブル名 add BIKO VARCHAR(255);

★テーブルのフィールド削除
mysql> alter table テーブル名 drop フィールド名
★テーブルのフィールド定義の変更
mysql> alter table テーブル名 change 現フィールド名新フィールド名定義;
mysql> alter table テーブル名 change name name text not null;
★テーブルの削除
mysql> drop table テーブル名;
★バックアップ
mysqldump --opt データベース名テーブル名 -u ユーザー名 -p > ダンプファイル名
mysqldump --opt --databases データベース名 -u ユーザー名 -p > ダンプファイル名
mysqldump --opt --all-databases -u ユーザー名 -p > ダンプファイル名
★リストア
mysql -u ユーザー名 -p データベース名 < ダンプファイル名
★情報の表示
★データベースの表示
mysql> show databases;
★テーブルの表示
mysql> show tables;
★サーバーステータス情報
mysql> show status;
★サーバー変数情報
mysql> show variables;
★プロセスの表示
mysql> show processlist;
★プロセスの強制終了
mysql> kill プロセス番号;

MariaDBでの文字化け (MySQL8.0でも類似の問題)

以下はまだ未解決です。プログラムが掲載されていても、あくまでも参考とし、動作を理解しない限り流用しないでください

　標準文字コードがutf8 (utf8mb3) からutf8mb4へ変わったことによるものと言われているが、症状・対処法は複雑。
特に、WindowsとLinuxで通信しているケースでは完全には解決できていない。

参考： https://itechblog.hatenablog.com/entry/2020/12/27/2020-12-27-093959/
collationの問題: https://zenn.dev/methane/articles/2023-mysql-collation
MySQL8.0での文字化け: MySQL8.0で文字コードを変更する方法(UTF8対応) #utf8mb4 - Qiita

環境と症状:
MariaDB: xampps on Windows11
DB driver: perl DBD:mysql on Windows11 / CentOS7 / AlmaLinux9

CentOS7からのperl接続は問題なし
Windows11からのperl接続は問題なし
python pymsqlからの接続で文字化け
mysqldumpで文字化け
AlmaLinux9からのperl接続で文字化け。
化けている文字コードの解読は失敗
・ perl、pythonによる文字コード変換で失敗
・文字化け解読ツール「もじばけらった」でも変換できる組み合わせが見つからない

Webで見つかる対処法:

・ my.iniを編集し、MariaDBの文字コードを修正
　・ mysqlコマンドで接続し、
　　　MariaDB [(none)]> show variables like '%char%';
　では、cp932が使われている
　・最近のxamppのmysql(mariadb)のmy.iniでは、文字コードはそもそもutf8mb4になっている。
　　念のためcharset設定を追加しても、variablesの内容は変わらない。

・データベース、テーブルのcharset, collate (検索順序) を修正
　・データべースの修正: MariaDB [(none)]> ALTER DATABASE {データベース名} CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
　・テーブルの修正　　 : MariaDB [(none)]> ALTER TABLE {テーブル名} CONVERT TO character SET utf8mb4 COLLATE utf8mb4_unicode_ci;

文字化けしないパターンと対策:

・ Windows11、perl DBD:mysqlで
　　set NAMES utf8mb4;
　を発行し、サーバのcollationを使うように設定する。
　これにより、perlプログラムでは文字化けしなくなった。
　ただし、pythonのpymsql、ORMでは文字化け。

・このperlプログラムをベースに、SQLスクリプトを生成するか、新規データベース・テーブルを作成してデータをコピーする

バージョンアップについて注意

Ver4.0=>4.1で、多言語の扱いとパスワードの長さが変わっているので、要注意:

パスワードについて： Ver4.1以降でも、my.cnfに"old-passwords"を指定することで、Ver4.0以前と同じ扱いができる
文字コードについて： Ver4.1以降では、文字コードを自動変換してしまう。
　　それを避けるため、必ずmy.cnfでdefault-character-set=sjisを指定する。

2010年にOracleがSun Microsystemsを買収したことから、MySQLもOracleが取得しました。

そのため、MariaDBがフォークされ、2012年のxampp 5.5.36から、DBがMariaDBに変わっています。