:: phpmyadminを使ったMYSQLのSQL構文の入門編メモ書き

プログラムでもデータベースでもとにかく自分の開発環境の中で構文を作っては実行して、色々と試してみるのが一番だと思う。時にはデータを壊したり、テーブルの中身をぐしゃぐしゃにしちゃったり。とにかく手を動かしてその中で学んでいく。ということを自分に向けて伝えるためにも自分用のメモ。

SQLについて詳しく書かれているとっても参考になるサイト
techscore
DBonline

 

補足用語
縦軸: フィールド(列、カラム)　※id、createdなど
横軸: レコード　※1件毎のデータ

   

 

 

演算子	使用例	意味
=	a = b	a と b は等しい
<=>	a <=> b	a と b は等しい(NULL対応)
<>	a <> b	a と b は等しくない
!=	a != b	a と b は等しくない
<	a < b	a は b よりも小さい
<=	a <= b	a は b よりも小さいか等しい
>	a > b	a は b よりも大きい
>=	a >= b	a は b よりも大きいか等しい
IS NULL	a IS NULL	a はNULL
IS NOT NULL	a IS NOT NULL	a はNULLではない
IS	a IS boolean_value	a はboolean_value
IS NOT	a IS NOT boolean_value	a はboolean_valueではない
BETWEEN AND	a BETWEEN min AND max	a は minより大きいか等しくmaxより小さいか等しい
NOT BETWEEN AND	a NOT BETWEEN min AND max	NOT (a BETWEEN min AND max)のこと
IN	a IN (value,…)	a は 括弧内のいずれかの値に等しい

 

 

構文的にはおかしいですが、注意点だけに焦点を当てているのであしからずm__m

*************
select
max(usr.username),
rtrim(usr.email) as email,　←左端の空白を取り除いている。右端の場合はRTRIM
max(usr.group_id)　←ここは最後の行なのでカンマ「,」は入れない

from
users as usr,
clients as cli　←ここは最後の行なのでカンマ「,」は入れない

where
usr.id=cli.user_id
and usr.email !~ ‘-\d{2,}$’
and usr.email ~ ‘.*@.*\..*’
and usr.email not like ‘%’ || chr(13) || ‘%’　←文字列なのでシングルクォーテーションで区切る
where nl_ng_mail.email = usr.email
)

group by
usr.email
*************

※SQL構文(SELECT,FROM,WHEREなど)は大文字でも小文字でも可能
※「;」や「( )」、「,」は忘れやすいので特に注意すること
※文字列の時はシングルクォーテーションで囲むこと「’ ‘」

 

 

DBonlineさんの記事を引用しています。

整数型

TINYINT	-128から127 (符号無しの場合0から255)
SMALLINT	-32768から32767 (符号無しの場合0から65535)
MEDIUMINT	-8388608から8388607 (符号無しの場合0から16777215)
INT	-2147483648から2147483647 (符号無しの場合0から4294967295) 別名: INTEGER
BIGINT	-9223372036854775808から9223372036854775807 (符号無しの場合0から18446744073709551615)

 

浮動小数点数型

FLOAT	-3.402823466E+38 から -1.175494351E-38 0 1.175494351E-38 から 3.402823466E+38
DOUBLE	-1.7976931348623157E+308 から -2.2250738585072014E-308 0 2.2250738585072014E-308 から 1.7976931348623157E+308 別名: DOUBLE PRECISION, REAL

 

日付型と時刻型

TINYINT	-128から127 (符号無しの場合0から255)
SMALLINT	-32768から32767 (符号無しの場合0から65535)
MEDIUMINT	-8388608から8388607 (符号無しの場合0から16777215)
INT	-2147483648から2147483647 (符号無しの場合0から4294967295) 別名：INTEGER
BIGINT	-9223372036854775808から9223372036854775807 (符号無しの場合0から18446744073709551615)

 

CHAR型とVARCHAR型

CHAR(M)	固定長文字列 Mは文字数を指定。0から255文字 別名: CHAR(M)はCHARACTER(M)の別名
VARCHAR(M)	可変長文字列 Mはバイト数を指定。0から65535バイト 別名: VARCHAR(M)はCHARACTER VARYING(M)の別名

 

BINARY型とVARBINARY型

BINARY[(N)]	固定長バイナリバイト文字列 Nはバイトを指定。0から255文字 別名: CHAR BYTE
VARBINARY(M)	可変長バイナリバイト文字列 Mはバイト数を指定。0から65535バイト

 

BLOB型とTEXT型

TINYBLOB	最長255 (28 – 1) バイト
BLOB[(M)]	最長65,535 (216 – 1) バイト
MEDIUMBLOB	最長16,777,215 (224 – 1) バイト
LONGBLOB	最長4,294,967,295、または4GB (232 – 1) バイト
TINYTEXT	最長255 (28 – 1) バイト
TEXT[(M)]	最長65,535 (216 – 1) バイト
MEDIUMTEXT	最長16,777,215 (224 – 1) バイト 別名: LONG, LONG VARCHAR
LONGTEXT	最長4,294,967,295、または4GB (232 – 1) バイト

 

ENUM型

ENUM (‘value1’, ‘value2’,…)	ENUM型では文字列定数をリストします。このデータ型が設定されたカラムに値を格納する場合、このリストの中の文字列定数の一つを指定します。(例外はあります)。またリストできる値は65,535までです。
例	mysql> CREATE TABLE test(col ENUM(‘red’,’blue’,’yellow’));

 

SET型

SET (‘value1’, ‘value2’,…)	SET型では文字列定数をリストします。このデータ型が設定されたカラムに値を格納する場合、このリストの中の0個以上の定数を指定します。(2個以上指定する場合はカンマで区切ります)。またリストできる値は64までです。ENUM型とSET型は似ていますが、ENUM型は格納可能な値の一覧を定義し一つ選択しているのに対して、SET型は指定可能な値の組み合わせを定義し、データとして格納するのは組み合わせを指定するものです。
例	mysql> CREATE TABLE test(col SET(‘red’,’blue’,’yellow’));

 

 

データベースの新規作成

CREATE DATABASE データベース名;

 

テーブルの新規作成

CREATE TABLE テーブル名 ( カラム名 data_type, カラム名 data_type, カラム名 data_type, …);

例: CREATE TABLE test_table ( id int, name varchar(255), created datetime, modified datetime );

 

SQLでデータベース、テーブル、テーブル内容を表示する

・データベースの一覧
SHOW DATABASES;

・テーブルの一覧
SHOW TABELS;

・テーブル内容の確認
DESC テーブル名;

 

レコードの追加

INSERT INTO テーブル名 ( カラム名1, カラム名2 , …) VALUES ( value1, value2,.,. );

例: INSERT INTO test_table (id, name, age ) VALUES ( 77, ‘test_user’, 35 );

複数のVALUESを追加する場合
INSERT INTO テーブル名 ( カラム名1, カラム名2 , …) VALUES ( value1, value2 ), ( value1, value2 );

例: INSERT INTO test_table (id, name, age ) VALUES ( 77, ‘test_user’, 35 ), ( 55, ‘test_user02’, 37 );

※全てのカラムに入力する場合はカラム名を省略することができるが、実際には省略しないことの方が多い。
※VALUES( ),VALUES( ),というようにカンマ「,」でつなげる。

 

レコードの更新

UPDATE テーブル名 SET カラム名 WHERE カラムの条件;

例: UPDATE test_table SET name=’test_user’ WHERE id = 1;

 

レコードの削除

DELETE FROM テーブル名 WHERE カラムの条件;

例: DELETE from test_table WHERE id > 5;

 

レコードの抽出

・全件抽出
SELECT カラム名 FROM テーブル名;

・条件付で抽出
SELECT カラム名 FROM テーブル名 WHERE カラムの条件;

・並べ替えて抽出
SELECT カラム名 FROM テーブル名 WHERE カラムの条件 ASC | DESC;
※DESCは降順、ASCは昇順。

・指定した件数のみ抽出
SELECT カラム名 FROM テーブル名 LIMIT num;

例: SELECT id, age FROM test_table ORDER BY age DESC LIMIT 10;

・開始位置から指定件数のみ抽出
SELECT カラム名 FROM テーブル名 LIMIT num OFFSET start_position;

例: SELECT id, age FROM test_table ORDER BY age DESC LIMIT 10 OFFSET 5 ;
※OFFSETが5でDESC(降順)なので、上位の0〜5を飛ばして6件目から5レコード分のidとageを表示する。

・行数を取得
SELECT COUNT( カラム名 ) FROM テーブル名 WHERE カラム条件;
SELECT COUNT( id ) FROM test_table WHERE event_no=100;
※event_noが100に参加しているユーザーのIDをtest_tableから抽出する。

 

データベースの管理

・ユーザー作成
GRANT priv_type ON db_name.データベース名 TO ‘user_name’ @ ‘user_domain’ IDENTIFIED BY ‘password’;

※権限の種類(priv_type)
ALL, PRIVILEGES, ALTER, CREATE, INSERT, DELETE, SELECT,…

※ユーザーの例
‘user’ @ ‘localhost’　←userが自分自身からアクセスできる
‘user’ @ 192.168.%’　←userがイントラネット上からアクセスできる

例: GRANT SELECT ON test_db.* TO ‘test_user’ @ ‘localhost’ IDENTIFIED BY ‘test_user’;
test_dbの全てのテーブル( * )に、test_userにlocalhostからSELECT文の実行ができる権限を、test_userのパスワードで作成する
※*の代わりにテーブル名を指定することができる

・テーブル削除
DROP TABLE テーブル名;

・データベース削除
DROP DATABASE データベース名;

 

以上、今回のメモ書きでかなり理解が深まったな〜って気がする。次のステップはユーザーの権限管理などのより実践的なこと。さらにphpmyadminではなくターミナル経由で操作できるように機会があればメモ書きとして残していきます。