---
# System prepended metadata

title: GCP & MYSQL 比賽
tags: [VMware + Linux]

---

# 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

![](https://i.imgur.com/AG1cXLq.png)

4.設定 mysql port 號
![](https://i.imgur.com/x6IjxLr.png)
![](https://i.imgur.com/8Dvb3sV.png)
![](https://i.imgur.com/2xjNqri.png)
![](https://i.imgur.com/4Sujqj5.png)
![](https://i.imgur.com/OnC7xNa.png)

![](https://i.imgur.com/Mqw9W46.jpg)

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

![](https://i.imgur.com/BwRFWGW.png)
:::



















###### tags: `VMware + Linux`