SQL(DDL, DML, DCL)

DDL, DML, DCLって何?

DDL(Data Definition Language)
CREATEやDROP、ALTERなどデータベースオブジェクトの生成や削除変更を行うコマンド

DML(Data Manipulation Language)
SELECT/INSERT/UPDATE/DELETEなどテーブルに対するデータの取得、追加、更新、削除を行うコマンド

DCL(Data Control Language)
BEGIN, COMMIT、ROLLBACKなどトランザクションを制御するためのコマンド

実務上はあまり使用しない名称だから、こんな言い方もあるんだなー程度か。

key

>KEY は通常、INDEX のシノニムです。キー属性 PRIMARY KEY もまた、カラム定義内で指定する場合は、単に KEY として指定できます。これは、ほかのデータベースシステムとの互換性のために実装されました。
Indexって理解でOK?
->確実に 識別する(identification) ための 主たる制約

MySQL unsigned

Integer types can handle positive and negative numbers, but if the data type is followed by UNSIGNED, only 0 and positive numbers can be stored. Such data types are called unsigned integer types. For example, to make INT type unsigned, write as follows.

mysql> create table numtest(
-> num1 smallint unsigned
-> );
Query OK, 0 rows affected (0.17 sec)

mysql> insert into numtest values(5000);
Query OK, 1 row affected (0.12 sec)

mysql> insert into numtest values(-10);
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> select * from numtest;
+——+
| num1 |
+——+
| 5000 |
| 0 |
+——+
2 rows in set (0.00 sec)

MySQLのcomments

CREATE TABLEの際にcommentを追加する
mysql> CREATE TABLE corporate(
-> name varchar(255) comment ‘商号’,
-> address varchar(255) comment ‘所在地’
-> )
-> default charset=utf8
-> comment=’会社テーブル’;
Query OK, 0 rows affected (0.36 sec)

mysql> describe corporate;
+———+————–+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———+————–+——+—–+———+——-+
| name | varchar(255) | YES | | NULL | |
| address | varchar(255) | YES | | NULL | |
+———+————–+——+—–+———+——-+
2 rows in set (0.06 sec)

show full columnsでカラムのcomment表示
mysql> show full columns from corporate;
+———+————–+—————–+——+—–+———+——-+—– —————————-+———–+
| Field | Type | Collation | Null | Key | Default | Extra | Priv ileges | Comment |
+———+————–+—————–+——+—–+———+——-+—– —————————-+———–+
| name | varchar(255) | utf8_general_ci | YES | | NULL | | sele ct,insert,update,references | 商号 |
| address | varchar(255) | utf8_general_ci | YES | | NULL | | sele ct,insert,update,references | 所在地 |
+———+————–+—————–+——+—–+———+——-+—– —————————-+———–+
2 rows in set (0.00 sec)

カラムの要件定義でカラムの意味は決めるので、sqlの中にわざわざcommentを追加する必要性は限られるようにも思うが、ケースバイケースということか?

mysqlの権限参照

– どのユーザーがいるか知りたい
SELECT user, host, password FROM mysql.user
mysql> select user,host from mysql.user;
+——+———————–+
| user | host |
+——+———————–+
| user | % |
| root | 127.0.0.1 |
| root | ::1 |
| | localhost |
| root | localhost |
| saru | localhost |
| | localhost.localdomain |
| root | localhost.localdomain |
+——+———————–+
8 rows in set (0.21 sec)

-ユーザー権限
SHOW GRANTS FOR root@`localhost`

mysql> show grants for root@localhost;
+———————————————————————+
| Grants for root@localhost |
+———————————————————————+
| GRANT ALL PRIVILEGES ON *.* TO ‘root’@’localhost’ WITH GRANT OPTION |
| GRANT PROXY ON ”@” TO ‘root’@’localhost’ WITH GRANT OPTION |
+———————————————————————+
2 rows in set (0.06 sec)

– 管理用ユーザー
GRANT ALL ON *.* TO adminuser@`%` IDENTIFIED BY ‘password’ WITH GRANT OPTION;

– IP制限
GRANT ALL ON *.* TO adminuser@’172.16.0.0/255.255.255.0′ IDENTIFIED BY ‘password’ WITH GRANT OPTION;

– 全てのデータベースにアクセス
GRANT ALL ON *.* TO testuser@’%’ IDENTIFIED By ‘password’;

– 特定のデータベースにアクセス
GRANT ALL ON testdatabase.* TO testuser@’%’ IDENTIFIED BY ‘password’;

ref: create user with GRANT Privileges in Terminal

nesting query

You can combine multiple queries to control the output of other queries with th output generated by one query. To put it a bit plainly, nesting queries so that the inner query generates a value that the outer query’s predicate evaluates to determine if it is true.

SELET 受注番号 FROM 受注表
 WHERE 商品コード = 
    (SELECT 商品コード FROM 商品表
    WHERE 商品名 = 'JUIICE');

入れ子になっていますね。

mysql: group by

mysql> select * from staff;
+——+——–+——–+
| id | name | deptid |
+——+——–+——–+
| 1 | yamada | 1 |
| 2 | honda | 4 |
| 3 | kudou | 6 |
| 4 | nishi | 1 |
| 5 | tagawa | 3 |
+——+——–+——–+
5 rows in set (0.00 sec)

mysql> select deptid, sum(id) from staff grop by deptid;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘by deptid’ at line 1
mysql> select deptid, sum(id) from staff group by deptid;
+——–+———+
| deptid | sum(id) |
+——–+———+
| 1 | 5 |
| 3 | 5 |
| 4 | 2 |
| 6 | 3 |
+——–+———+
4 rows in set (0.02 sec)

なるほど、あるカラムの値に重複がある場合に、group byでまとめて計算できるのね。

なんとなくわかってきた。

mysql select count: レコード数のカウント

mysql> select count(*) from kawasaki;
+———-+
| count(*) |
+———-+
| 69 |
+———-+
1 row in set (0.00 sec)

mysql> select * from staff;
+——+——–+——–+
| id | name | deptid |
+——+——–+——–+
| 1 | yamada | 1 |
| 2 | honda | 4 |
| 3 | kudou | 6 |
| 4 | nishi | 1 |
| 5 | tagawa | 3 |
+——+——–+——–+
5 rows in set (0.00 sec)

mysql> select count(deptid) from staff;
+—————+
| count(deptid) |
+—————+
| 5 |
+—————+
1 row in set (0.00 sec)

データ量なので、重複したデータも+1とカウントされるんですね。

mysql show index;

show indexは、indexが貼られているか確認

mysql> show index;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” at line 1

あれ、、

mysql> show index from staff;
Empty set (0.00 sec)

あら、fromが必要でしたね。

select distinct -> 重複データを取り除く

SELECT文を使ってデータを取得した時に、重複したデータを取り除いてデータを取得する

SELECT DISTINCT column name, ... from table name;

では、早速見てみましょう。
mysql> select * from staff
-> ;
+——+——–+——–+
| id | name | deptid |
+——+——–+——–+
| 1 | yamada | 1 |
| 2 | honda | 4 |
| 3 | kudou | 6 |
| 4 | nishi | 1 |
| 5 | tagawa | 3 |
+——+——–+——–+
5 rows in set (0.06 sec)

mysql> select distinct deptid from staff;
+——–+
| deptid |
+——–+
| 1 |
| 4 |
| 6 |
| 3 |
+——–+
4 rows in set (0.06 sec)

なるほど、deptidの重複が削除されますねー