MySQL with Json Data
===

###### tags:`MySQL`
mySql的常用指令

一、登入mySql的方式
mysql -hhostname -uroot -ppassword

Example： mysql -uroot -proot -h10.10.10.10

二、離線指令
quit
\q

三、顥示連結的狀態
status

四、建立資料庫
create database 資料庫名稱;

五、刪除資料庫
drop database 資料庫名稱;

六、show 出資料庫
show databases;

七、檢查使用中的資料庫
select database();

八、使用資料庫
use 資料庫名稱;

九、顯示資料庫內的資料表名稱
show tables;

十、顯示資料庫欄位名稱及內容
describe 資料表名稱;

十一、查看mysql的目前有那些在執行
show processlist;

十二、顯示索引表
show index 資料表名稱;

十三、匯入資料
mysql -uroot -p 資料庫 [資料檔名.sql]

# create database:

create database lib13;

# use database:

use lib13; 

# create table:

create table Test ( 按 Enter
id int primary key auto_increment, 按 Enter
name varchar(16) 按 Enter
); 

# show scheme in table:

show columns from Test; 
or 
describe Test;

# MySql 新增使用者與設定權限
Django book page:318
account :pi
password:raspberry

REF:http://pclevin.blogspot.tw/p/mysql_28.html

1.先在本地端登入MySQL服務:

mysql -u root -p

然後輸入root的密碼

2.登入成功會看到prompt:

mysql>

3.輸入權限設定命令:

GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'IP' IDENTIFIED BY 'PASSWORD';

> USERNAME 一般來說就是root
> IP 是開放連線的IP位址, 如果用‘%’, 會讓所有電腦皆可連線
> PASSWORD 是連線密碼

4.將權限flush, 然後離開MySQL:

mysql> FLUSH PRIVILEGES;
mysql> quit;

5.設定 Ubuntu 防火牆:

ufw enable
ufw allow 3306

6.設定 MySql的bind-address:
將bind-address取消

#nano /etc/mysql/my.cnf
[mysqld]
……
bind-address = 127.0.0.1 (在前面加#註解掉此行)

7.重新啟動MySql服務

service mysql restart

8.遠端啟動mysql client登入，以 windows 為例：
C:\AppServe\MySQL\bin\mysql –h xxx.xxx.xxx.xxx –u root -p
xxx.xxx.xxx.xxx為伺服器IP位址

# 利用Mysql來處理Json資料:

MySQL 5.7 Introduces a JSON Data Type
Alter table:
ALTER TABLE people ADD COLUMN (tags json);
JSON_ARRAY_APPEND:新增新tag在原有的json array之後

UPDATE people SET tags = JSON_ARRAY_APPEND(tags, "$", "Lorem") WHERE id = 0;

* The three arguments to JSON_ARRAY_APPEND() are:
* The column to append to
* The path within the column, using the JSON Path Syntax
* The value to append

* Json search :尋找該tags name是否位於json array裡面
* The column to search
* Either 'one' or 'all', depending on whether you want MySQL to just return the first match it finds, or return all matches
* The value we're looking for
* eg: SELECT name, tags from people WHERE JSON_SEARCH(tags, 'one', 'Lorem') IS NOT NULL;

* JSON_SET() 修改json array 資料

UPDATE people SET profile = JSON_SET(profile, "$.salary", 52000) WHERE id = 3;
UPDATE people SET profile = JSON_SET(profile, "$.first_aid", true) WHERE id = 3;
$代表JSON Path Syntax
Profile is column name

Once we've made these changes, you can inspect the state of the dataset again:

+----------------+---------------------------------------------------------------------------------------------------------------+
| name           | profile                                                                                                       |
+----------------+---------------------------------------------------------------------------------------------------------------+
| Howard Ortega  | {"email": "[e-mail]", "salary": 52000, "twitter": "@estvelit", "direct_reports": 7}                   |
| Miller Gamble  | {"salary": 52000, "join_date": "2013-05-13T10:02:22 -01:00"}                                                  |
| Harriett Leon  | {"email": "[e-mail]", "driver": true, "salary": 63000, "twitter": "@commodoproident"}               |
| Claudia Durham | {"email": "[e-mail]", "salary": 52000, "first_aid": true, "vegetarian": false, "direct_reports": 12} |
| Cox Huff       | {"salary": 61000, "twitter": "@mollitconsequat"}                                                              |
+----------------+---------------------------------------------------------------------------------------------------------------+

* JSON_EXTRACT() for query as same as column->path:

SELECT name, profile->"$.direct_reports" reports, profile->"$.salary" salary FROM people WHERE profile->"$.direct_reports" >= 10;

# Ref:
Mysql manual:
https://dev.mysql.com/doc/refman/5.7/en/