# GCP & MYSQL 比賽
1. 第一棒
- 安裝 mysql
- 建立 2 個使用者：一個是 bigred 管理者，一個是 bigboss 是分析師 只能執行新增，修改，查詢，刪除 SUN database 裡面的 table 資料
2. 第二棒
- 建立資料庫 (SUN) 跟建立兩個資料表 (customers, trans)
分別 匯入兩個檔案 customers.csv 及 trans.csv
3. 第三棒
- 產生以下的分析報告
B2898230 楊淑津 45 2015/11/30 優酪乳 瓶 1
B2959950 蕭悅蕾 45 2015/9/15 洗衣精 瓶 2
B2959950 蕭悅蕾 45 2015/9/15 蘋果 袋 2
B2959950 蕭悅蕾 45 2015/9/15 沐浴乳 瓶 3
依照上面的格式 列出 45 歲所有客戶所購買的產品
A1899959 許成堅 啤酒 瓶 5
B2890362 陳鈺薇 啤酒 瓶 3
依照上面的格式 列出所有購買啤酒的客戶名單
啤酒 2
依照上面的格式 列出大於20歲且有購買啤酒的客戶有幾個
42 11136
依照上面的格式 哪一個年紀的消費金額最高
# 答案
[文件](https://iter01.com/544562.html)
--授予使用者“test”通過外網IP對於該資料庫“testdb”中表的建立、修改、刪除許可權,以及表資料的增刪查改許可權
grant create,alter,drop,select,insert,update,delete on testdb.* to test@'%';
:::danger
- 安裝 mysql
$ sudo apt update
$ sudo apt -y upgrade
$ sudo apt install -y mysql-server
$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
```
[mysqld]
#找到
bind-address=127.0.0.1
#改成
bind-address=0.0.0.0
```
$ sudo systemctl restart mysql.service
- 建立 2 個使用者：一個是 bigred 管理者，一個是 bigboss 是分析師 只能執行 select 命令
$ sudo mysql
```b!
CREATE USER 'bigred'@'%' IDENTIFIED WITH mysql_native_password BY 'bigredpass';
GRANT ALL PRIVILEGES ON *.* TO 'bigred'@'%';
FLUSH PRIVILEGES;
```
– 建立 bigboss 帳號
```b!
CREATE USER 'bigboss'@'%' IDENTIFIED WITH mysql_native_password BY 'bigboss';
GRANT SELECT, INSERT, UPDATE, DELETE ON SUN.* TO 'bigboss'@'%';
FLUSH PRIVILEGES;
select user,host,plugin from mysql.user;
```
- 檢查
mysql> SHOW GRANTS FOR bigred;
mysql> SHOW GRANTS FOR bigboss;
```
+--------------------------------------+
| Grants for bigboss@% |
+--------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON `SUN`.* TO `bigboss`@`%` |
+--------------------------------------+
1 row in set (0.00 sec)
```
-**下載考試資料**
$ wget http://www.oc99.org/dt/dataset/hypermarket.csv
$ head -n 3 hypermarket.csv
tr: translate 翻譯
$ `cat hypermarket.csv | tr $'\t' ',' > myhypermarket.csv`
（`$'\t'`代表 tab建 大概7,8個空格），把 comma（逗號,） 來換掉tab建
$ cat myhypermarket.csv
$ head -n -2 myhypermarket.csv > myhypermarket1.csv (從頭到尾，最後一行不要，剪掉)
$ tail -n+2 myhypermarket1.csv > myhypermarket2.csv （從第二行開始剪到最後）
$ cat myhypermarket2.csv
$ head -n 1 hypermarket.csv
會員編號, 姓名, 年齡, 郵遞區號, 電話, 入會日期
$ `cat myhypermarket2.csv | cut -d ',' -f1-6 > customers.csv`
$ `cat myhypermarket2.csv | cut -d ',' -f1,7,8-12 > trans.csv`
$ `cat myhypermarket2.csv | cut -d ',' -f1,7,13-17 >> trans.csv`
$ `cat myhypermarket2.csv | cut -d ',' -f1,7,18-22 >> trans.csv`
:::
:::success
Unique Key = 不重覆鍵
$ cat customers.csv | uniq > tmp.csv
$ mv tmp.csv customers.csv
$ sudo cp customers.csv /var/lib/mysql-files/
$ sudo cp trans.csv /var/lib/mysql-files/
$ mysql -u bigred -pbigredpass
> create database sun;
> use sun;
> create table customers (
> c_id varchar(10),
> c_name varchar(20),
> c_age int,
> c_zip varchar(10),
> c_tel varchar(30),
> c_date varchar(12),
> primary key (c_id)
> );
`load data infile '/var/lib/mysql-files/customers.csv' into table customers fields terminated by ',';`
> create table trans (
> c_id varchar(10),
> t_date varchar(20),
> p_id varchar(20),
> p_name varchar(20),
> p_unit varchar(10),
> p_price varchar(30),
> p_qty varchar(12),
> foreign key (c_id) references customers(c_id)
> );
`load data infile '/var/lib/mysql-files/trans.csv' into table trans fields terminated by ',';`
select * from trans;
$`select c_name, p_name from customers join trans on customers.c_id=trans.c_id where c_age=45;`
$`select c_name, p_name from customers join trans on customers.c_id=trans.c_id where p_name='啤酒';`
$`select c_name, p_name from customers join trans on customers.c_id=trans.c_id where c_age>'20' and p_name='啤酒';`
```
+-----------+--------+
| c_name | p_name |
+-----------+--------+
| 陳鈺薇 | 啤酒 |
| 許成堅 | 啤酒 |
+-----------+--------+
2 rows in set (0.00 sec)
```
$`select p_name,count(*) from customers join trans on customers.c_id=trans.c_id where c_age>'20' and p_name='啤酒' group by p_name;`
```
+--------+----------+
| p_name | count(*) |
+--------+----------+
| 啤酒 | 2 |
+--------+----------+
1 row in set (0.00 sec)
```
$ `select c_age,sum(p_price*p_qty) from customers join trans on customers.c_id=trans.c_id group by c_age;`
```
+-------+--------------------+
| c_age | sum(p_price*p_qty) |
+-------+--------------------+
| 46 | 4150 |
| 31 | 5639 |
| 50 | 10772 |
| 40 | 2885 |
| 49 | 6408 |
| 39 | 5334 |
| 30 | 8683 |
| 41 | 4456 |
| 42 | 11136 |
| 48 | 10692 |
| 38 | 4167 |
| 36 | 5282 |
| 35 | 3861 |
| 44 | 3958 |
| 45 | 3032 |
| 43 | 4328 |
| 37 | 3173 |
| 32 | 9524 |
| 34 | 1417 |
| 47 | 6953 |
| 33 | 1890 |
+-------+--------------------+
21 rows in set (0.00 sec)
```
$ `select c_age,sum(p_price*p_qty) from customers join trans on customers.c_id=trans.c_id group by c_age order by sum(p_price*p_qty) desc limit 1;`
```
+-------+--------------------+
| c_age | sum(p_price*p_qty) |
+-------+--------------------+
| 42 | 11136 |
+-------+--------------------+
1 row in set (0.00 sec)
```
:::
# GCP + Win_Mysql
N1 : skylake 長期支援，這個版本5年有問題他會免費幫你修
ssd 是記憶體變硬碟
加到sudo 群組，擁有sudo 權限，
cat /etc/group | grep sudo
PermitUserEnvironment yes
環境變數，他會去看環境變數
==$ sudo apt autoremove
移除不需要用的套件 package==
==如何登入 Google 雲端虛擬主機==
1.改密碼
$ sudo passwd lovelydaisy0521
> sudo passwd 直接測新的密碼上去
> passwd 他會問舊的密碼
2.改 PasswordAuthentication yes
$ sudo nano /etc/ssh/sshd_config
```
PasswordAuthentication yes
```
3. sudo nano /etc/group
4.設定 mysql port 號
C:\Users\bigred>cd C:\tools\mysql\current\bin
C:\tools\mysql\current\bin>mysql.exe -u bigred -pbigredpass -h 35.185.171.129
mysql> exit
:::danger
$ ssh lovelydaisy0521@35.185.171.129
在虛擬主機
$ sudo apt update
$ sudo apt upgrade
$ sudo apt install -y mysql-server
$ sudo mysql
> CREATE USER 'rbean'@'60.251.66.131' IDENTIFIED WITH mysql_native_password BY 'rbeanpass';
> GRANT ALL PRIVILEGES ON *.* TO 'rbean'@'60.251.66.131';
> FLUSH PRIVILEGES;
> select user,host,plugin from mysql.user;
> exit
$ sudo systemctl restart mysql.service
在 windows 系統
C:\tools\mysql\current\bin>mysql.exe -u rbean -prbeanpass -h 35.185.171.129 -P 3306
==mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'rbean'@'125-228-190-231.hinet-ip.hinet.net' (using password: YES)==
在虛擬主機
$ sudo mysql
> CREATE USER 'rbean'@'125-228-190-231.hinet-ip.hinet.net' IDENTIFIED WITH mysql_native_password BY 'rbeanpass';
> GRANT ALL PRIVILEGES ON *.* TO 'rbean'@'125-228-190-231.hinet-ip.hinet.net';
> FLUSH PRIVILEGES;
> select user,host,plugin from mysql.user;
> exit
$ sudo systemctl restart mysql.service
在 windows 系統
C:\tools\mysql\current\bin>mysql.exe -u rbean -prbeanpass -h 35.185.171.129 -P 3306
mysql>
mysql> select user,host,plugin from mysql.user;
mysql> exit
:::
###### tags: `VMware + Linux`
Sign in with Wallet
Connect another wallet