Database - MySQL Command and Syntax

登入 mysql (使用 `-p` 密碼登入)

root@test:/# mysql -u webadmin -p
root@test:/# mysql -u webadmin --password=00000000 db_name

連入遠端 mysql

mysql -h devHostName -u test -p
mysql>    #按ENTER是換第二行輸入，
          #不是送出而是繼續輸入，
          #如果不要再輸入了加上分號(;)表示結束
          #quit 或 ctrl + d : 中斷

進入 local mysql server (in docker)

mysql -h localhost -P 3306 --protocol=tcp -u root

Console

看目前連到 MySQL 的 process

SHOW PROCESSLIST;
.. or ...
SHOW FULL PROCESSLIST;
+--------+----------+-------------------+-------+---------+------+----------+-----------------------+
| Id     | User     | Host              | db    | Command | Time | State    | Info                  |
+--------+----------+-------------------+-------+---------+------+----------+-----------------------+
| 530566 | api      | 10.0.21.49:43537  | my-log | Async commit | 1 | cleaned up | INSERT INTO `device_log` (`model`, `did`, `uid`, `mac`, `category`, `action`, `ip`, `log_date`, `occurred_at`, `created_at`, `timestamp_milli`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

KILL PROCESS: 如果對 MySQL 下的指令造成 LOCK TABLE 或其他效能上的影響, 想取消的話, 可以刪除這個 process

先找出 process id

SHOW PROCESSLIST;
| 284349 | root      | 10.0.1.224:52018  | my-log     | Query   | 2568 | copy to tmp table               | ALTER TABLE `user` CHANGE `name` `name` VARCHAR(100) CHARACTER SET utf8 COLLATE |

mysql> kill 284349;
Query OK, 0 rows affected (0.00 sec)

Show schema threads

SELECT * FROM performance_schema.threads;

其他系統指令

SHOW ENGINE INNODB STATUS;
SHOW GLOBAL STATUS;

Show running queries

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != 'Sleep';

Shows all queries running for 5 seconds or more:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != 'Sleep' AND TIME >= 5;

Show all running UPDATEs:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != 'Sleep' AND INFO LIKE '%UPDATE %';

目前連接 mysql 的數量

SHOW STATUS WHERE `variable_name` = 'Threads_connected';

最多可以同時幾個連線 (注意，如果超過這個數量，mysql 就會回 Too many connections 錯誤)，如果使用 AWS RDS MySQL 要參考它不同 size 支援的 max_connections 量

SHOW VARIABLES LIKE 'max_connections';

MySQL version

SHOW VARIABLES LIKE "%version%";
或
select version();

binlog 是否開啟

SHOW VARIABLES LIKE 'log_bin';

slow query 是否開啟

SHOW VARIABLES LIKE '%slow%';

將執行過的 Query 寫到 log 檔

SET GLOBAL general_log = 'ON';

查詢指令使用index情況，在開頭加上EXPLAIN

XPLAIN SELECT *
FROM  `user_log`
WHERE uid =2

顯示系統狀態（詳細）

show status;

顯示系統狀態（簡單）

status;

查看自已的權限

mysql> show grants;

查看某個 user 的權限

show grants for test@'localhost';

列出 mysql 的 user

mysql> select user, host from mysql.user;

查看權限

select * from mysql.user where user='test';

查看 mysql 帳戶密碼及 host

select host, user, password from mysql.user;

mysql privileges

SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, EVENT, TRIGGER, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE

查看 mysql 帳號

desc mysql.user;

新增 User

use mysql;
INSERT INTO user(host,user,password) VALUES('127.0.0.1','test',password('qwer7890'));

限定權限

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE ON testDB.* TO test@localhost IDENTIFIED BY 'qwer7890';
FLUSH PRIVILEGES;

所有權限

GRANT ALL PRIVILEGES ON *.* TO test@localhost IDENTIFIED BY 'qwer7890' WITH GRANT OPTION;
等於
GRANT ALL PRIVILEGES ON *.* TO test@localhost IDENTIFIED BY PASSWORD '*9B25933BE82583D0F86E4AB11A340DE6A3611ACD' WITH GRANT OPTION;

Show cache

show variables like '%cache%';

顯示資料庫清單

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| exam               |
| mysql              |
| package            |
| performance_schema |
| phpmyadmin         |
| test               |
+--------------------+
7 rows in set (0.02 sec)

選擇資料庫

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

顯示資料表清單

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test           |
+----------------+
1 row in set (0.00 sec)

顯示table的欄位資訊

mysql> show columns from test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(40) | NO   |     | NULL    |                |
| hobby | varchar(40) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

或者：

mysql> desc test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(40) | NO   |     | NULL    |                |
| hobby | varchar(40) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

顯示資料表的 TABLE Schema

mysql> show CREATE table test;
+-------+------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                   |
+-------+------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
          `name` varchar(80) DEFAULT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

顯示 user 開頭的 table 列表

SHOW tables like 'user%';

Create database

CREATE DATABASE db_name

Use database

USE db_name

Drop database

DROP DATABASE db_name

DROP TABLE IF EXISTS `info`;

Create table

CREATE TABLE table_name (no char(4), name char(10));

CREATE TABLE `info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `model` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `version` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `url` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `version` (`model`,`version`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Drop table

DROP TABLE table_name;

Rename table name

RENAME TABLE table_name TO tt;

Display database list

SHOW DATABASES;

Display table list

SHOW TABLES;

Show 各 table 的筆數, 容量等等

SHOW TABLE STATUS;

Display table detail

DESC table_name;

Alter column

ALTER TABLE qq_tab CHANGE qq_col qq_col varchar(80) NOT NULL;

Add column

ALTER TABLE tt ADD phone varchar(40);

Primary key

ALTER TABLE gearman_queue ADD COLUMN `id` INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT;

Replace

UPDATE table_name SET field=replace(field, "android", "iphone");
UPDATE `supe_spacenews` SET `message` = REPLACE(`message`,'要替換的文字','替換後的文字') WHERE `message` LIKE '%要替換的文字%'

將欄位改成 allow null

ALTER TABLE my_table MODIFY url varchar(100);

Show index

SHOW INDEX FROM my_table;

Foreign key

SET FOREIGN_KEY_CHECKS=0;
SET FOREIGN_KEY_CHECKS=1;

顯示 Table 的 Index

SHOW INDEX FROM users;

Before making an index unique, check first

SELECT column1, column2 FROM my_table GROUP BY column1, column2 HAVING COUNT(*) > 1;

Show index of a table

SHOW INDEX FROM table_name;

刪除資料表內的所有資料,並且重置AUTO_INCREMENT

TRUNCATE TableName

LIKE IN 語法怎麼辦? 使用 REGEXP

SELECT * FROM students WHERE name REGEXP 'bob|test|joyce';

設定 AUTO_INCREMENT

ALTER TABLE tbl AUTO_INCREMENT = 5;

更新與 users.uid 的關聯 parent_id

UPDATE parents set parent_id = (SELECT uid from users where users.username = parents.parent) where parents.parent != ''

匯入匯出資料庫

mysqldump 無法將密碼寫在指令上 e.g. --password=00000000, 所要要改用 config 的方式

/home/web-admin/db_backup/my.cnf:

    [mysqldump]
    password=00000000

mysqldump --defaults-file=/home/web-admin/db_backup/my.cnf --opt -u root my_db > my_db_backup.sql

匯出test資料庫, user為test, 密碼送出才輸入, 匯出到目前的目錄, 檔案名稱為test_backup.sql

mysqldump --opt -u test -p db_name > db_name_backup.sql

建立 test 使用者及建立 test 資料庫

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*A8950BFBF9522A31DA227E7E1E751E2DC0691964' WITH GRANT OPTION;
mysql> create database db_name

匯入 test 資料庫 (不論是匯入 database 或是 table, 寫法都一樣)

mysql -u root -p db_name < db_name_backup.sql

匯出單筆 user 資料

mysqldump -u root my_db user -w "email='test_user@gmail.com'"

匯出 test DB, 無資料(但仍保留AUTO_INCREMENT)

mysqldump --opt -u test -p --no-data test > test_backup.sql

如果使用 innoDB 可能會有 lock 情況造成無法匯出, 加上 --lock-tables=false

匯出 test DB 裡的 qq table, 無資料(但仍保留AUTO_INCREMENT)

mysqldump --opt -u test -p --no-data test qq > test_qq_backup.sql

匯出 test DB 的 groups 資料表 uid=6 的資料

mysqldump -u root test groups -w "uid=6" > group_test.sql

匯出 test DB, 無資料, AUTO_INCREMENT重置

mysqldump -u test -p --no-data --skip-add-drop-table test | sed 's/AUTO_INCREMENT=[0-9]*\b//' > test_backup.sql

匯出遠端 mysql 資料

mysqldump -h sqlserver.com -u me -p123 me_test user -w "name='test'" > qq

Host : sqlserver.com
Account : me
Password : 123
DB : me_test
Table : user
WHERE 條件 : name='test'

程式裡會用到的語法

INSERT

INSERT INTO test2 (name, passwd) VALUES ('test', '123');

INSERT IF NOT EXISTS

INSERT INTO promote_grab (m_id)
SELECT (2)
FROM DUAL
WHERE NOT EXISTS(SELECT 1 FROM promote_grab WHERE m_id = 2)

插入 m_id = 2 到 promote_grab, 如果 promote_grab 不存在 m_id =2 :

INSERT IGNORE (if exists)

INSERT IGNORE INTO books (id, title, author, year_published) VALUES (1, 'Green Eggs and Ham', 'Dr. Seuss', 1960)

插入一樣的 index 如果已存在就會 ignore 不會噴 error

INSERT or UPDATE IF EXISTS

INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE name="A", age=19

INSERT Table to another one : 將 user 資料表的資料都 insert 到 user2 資料表 :

INSERT INTO `user2` SELECT * FROM `user`

Insert or Update

INSERT INTO student (id, name) VALUES('1', 'test') ON DUPLICATE KEY UPDATE name='Bob'

UPDATE

UPDATE question_options SET question_id = 20, optional_item = 5 WHERE id = 64;

UPDATE with EXISTS

UPDATE suppliers
SET supplier_name = (SELECT customers.customer_name
                     FROM customers
                     WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS (SELECT *
              FROM customers
              WHERE customers.customer_id = suppliers.supplier_id);

DELETE

DELETE FROM videos WHERE id=1;

DELETE with EXISTS

DELETE FROM suppliers
WHERE EXISTS (SELECT *
              FROM orders
              WHERE suppliers.supplier_id = orders.supplier_id);

將 time 欄位加30天

DATE_ADD(time, INTERVAL 30 DAY) AS after_time

select 特定時間

select * from device where date_format(update_time, '%Y-%m-%d') = '2014-10-23';

WHERE json 裡的欄位

SELECT * FROM `user` WHERE profile->"$.email" != ""                         // email 是欄位名稱
SELECT * FROM `user` WHERE profile->'$."example+11@example.com"' != ""      // 如果欄位名稱有符號要用 雙引號包起來

IFNULL(x, y): 如果 x 欄位的值是 NULl 就回傳 x, 否則是 y

GROUP BY IFNULL(ann_id, id)

FORCE INDEX 強制指定某個 index (寫在 from 前)

FORCE INDEX (uid+age+created_at)

LIMIT & OFFSET

// return only 10 records, start on record 16 (OFFSET 15)
SELECT * FROM Orders LIMIT 10 OFFSET 15
SELECT * FROM Orders LIMIT 15, 10

當資料筆數很大, 且 OFFSET 很大的話, 效能會很不好, 解決方式是不要用 OFFSET, 改用 PK id < 每次撈取的最後一個 id

UNION 將兩個不同表但欄位類似的 Query 合成一個結果

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

如果只取一個欄位的話, UNION 預設會幫你去除重覆的, 如果要顯示重覆的話要用 UNION ALL

UNION + Unique 每個欄位

SELECT * FROM (
    SELECT City, Code FROM Customers
    UNION
    SELECT City, Code FROM Suppliers
    ORDER BY City;
) C
GROUP BY City

用 DISTINCT 沒有用, 要用 GROUP BY

JOIN

1 aaa
2 bbb

1 bbb
2 ccc

Left join SELECT * FROM `A1` LEFT JOIN A2 ON A1.did = A2.did

id did id did
2 bbb 1 bbb
1 aaa NULL NULL

Right join SELECT * FROM `A1` RIGHT JOIN A2 ON A1.did = A2.did

id did id did
2 bbb 1 bbb
NULL NULL 2 ccc

Inner join SELECT * FROM `A1` INNER JOIN A2 ON A1.did = A2.did

id did id did
2 bbb 1 bbb

子查詢

限制

不可以傳回兩個欄位以上
不可以傳回兩筆以上的紀錄

SELECT * FROM users WHERE uid = (SELECT parent_id FROM parents WHERE student_id=1109 LIMIT 1)

其他

3-tier Category

+--------+---------------+-----------+
| cat_id | name          | parent_id |
+--------+---------------+-----------+
|      1 | Electronics   |         0 |
|      2 | Appliances    |         0 |
|      3 | Cell phones   |         1 |
|      4 | Computers     |         1 |
|      5 | Tablets       |         1 |
|      6 | Smartphones   |         3 |
|      7 | Tablet Phones |         3 |
+--------+---------------+-----------+

mysql> SELECT
    -> a.name AS main_category,
    -> b.name AS second_level_category,
    -> c.name AS thrid_level_category
    ->  FROM categories AS a
    -> LEFT JOIN categories AS b ON (a.cat_id=b.parent_id)
    -> LEFT JOIN categories AS c ON (b.cat_id=c.parent_id)
    -> WHERE a.parent_id=0;
+---------------+-----------------------+----------------------+
| main_category | second_level_category | thrid_level_category |
+---------------+-----------------------+----------------------+
| Electronics   | Cell phones           | Smartphones          |
| Electronics   | Cell phones           | Tablet Phones        |
| Electronics   | Computers             | NULL                 |
| Electronics   | Tablets               | NULL                 |
| Appliances    | NULL                  | NULL                 |
+---------------+-----------------------+----------------------+

Rails ORM 版

Category.from('categories AS a').where('a.parent_id = ?', 0).
         joins('LEFT JOIN categories AS b ON (a.id = b.parent_id)').
         joins('LEFT JOIN categories AS c ON (b.id = c.parent_id)').
         select('a.name AS main, b.name AS second, c.name AS third')

ref : http://stackoverflow.com/questions/21277663/mysql-normalization-with-category-with-1st-tier-sub-category-and-2nd-tier-sub-ca

LIKE 語法關鍵字 `_` `%`

% : 0 個或以上的任意字元
_ : 只能有一個字元

How to prevent sql injection

限制 db user 的權限
使用程式提供的工具去過濾，也盡量避免以下字元進入到 query : ' " \ & * ;
使用 Prepare 取代 query 的變數
使用一些 SQL 的檢查工具 e.g. sqlmap, SQLninja
避免將 SQL 錯誤發生時的資訊印出

支援儲存 unicode character(emoji,特殊字元)

utf8 vs utf8mb4

utf8 is a variable-length encoding. In the case of UTF-8, this means that storing one code point requires one to four bytes. However, MySQL’s encoding called “utf8” only stores a maximum of three bytes per code point.
utf8mb4 character set uses a maximum of four bytes per character

將欄位改成 utfmb4_unicode_ci
確認連線時的 Server charset, 如果預設是 UTF-8 Unicode (utf8), 那麼與 mysql 建立連線時的 dsn 就要加上 &charset=utf8mb4

SHOW VARIABLES WHERE variable_name LIKE ‘character%’ OR variable_name LIKE ‘collation%’

The server character set and collation are the values of the character_set_server and collation_server system variables.

The character set and collation of the default database are the values of the character_set_database and collation_database system variables.

The server takes the character_set_client system variable to be the character set in which statements are sent by the client.

如果連線的 client connection 沒有使用 utf8mb4, 在撈含有特殊字元的資料不會 error, 但字元會變成 ?

登入 mysql (使用 -p 密碼登入)