# MySQL安裝清單

//mysql
sudo apt install -y mysql-server

python套件 - 連結MySQL
//pymsql
pip install pymysql

//sqlalchemy
pip install sqlalchemy

## MySQL設定

### MySQL安裝及基本設定

//開啟mysql
sudo systemctl start mysql.service

//執行mysql
sudo mysql

//設定mysql中root的密碼
mysql -> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
e.g : ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypassword';

//設定security
sudo mysql_secure_installation
按照自己的需求回答

### 新增MySQL使用者

//新增使用者
mysql ->CREATE USER 'username'@'localhost' IDENTIFIED WITH BY 'password';
e.g:CREATE USER 'chunlin'@'localhost' IDENTIFIED BY 'mypassword';

//給予使用者權限
mysql ->GRANT CREATE, ALTER, DROP, INSERT, UPDATE, INDEX, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'chunlin'@'localhost' WITH GRANT OPTION;

//如果要給予使用者所有權限
mysql ->GRANT ALL PRIVILEGES ON *.* TO 'chunlin'@'localhost' WITH GRANT OPTION;

//刷新特權
FLUSH PRIVILEGES;

//使用剛剛創見的使用者登錄
mysql -u username -p
e.g.:mysql -u chunlin -p

//創建新的資料庫
mysql -> CREATE DATABASE (your database name)
e.g. : CREATE DATABASE YFAD;

//查看MySQL內部database
mysql -> SHOW DATABASES;

//查看表中的資料
SELECT * FROM (table name);
e.g : mysql -> SELECT * FROM mytable;

## Django設定

### setting.py設定

//在專案下的setting.py內import pymysql，協助Django驅動MySQL

import pymysql
pymysql.install_as_MySQLdb()

//更改setting.py中database設定

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'your database name',
        'USER': 'your uer name',
        'PASSWORD': 'your password',
        'HOST':'localhost',
        'PORT':'3306', 此為MySQL預設port
    }
}

### 連結資料庫

import pymysql

# connect = sqlite3.connect('db.sqlite3')

engine = create_engine('mysql+pymysql://user_name:user_password@localhost:3306/database_name')
data = pd.read_sql(sql_string, engine)

## 資料匯入MySQL

### 1.使用.sql檔案
將放在別的資料庫的表export成.sql格式

//import到mysql資料庫內
$ mysql -u username -p database_name < file.sql
e.g. : mysql -u chunlin -p your_database_name < import_file.sql

### 2.使用.csv
1.[SQL語法](https://dev.mysql.com/doc/refman/8.0/en/load-data.html)
2.dataframe寫入資料庫

from sqlalchemy import create_engine

def csv2mysql(df,table_name):
    engine = create_engine('mysql+pymysql://user_name:user_password@localhost:3306/database_name')
    e.g. : 
    engine = create_engine('mysql+pymysql://chunlin:mypassword@localhost:3306/YFAD')
    
    df.to_sql(table_name, con=engine, if_exists='replace', index=False, dtype=dtypedict)

3.phpmyadmin

# phpmyadmin安裝清單

//phpmyadmin
sudo apt install phpmyadmin -y

//apache2
sudo apt-get -y install apache2

//php
sudo apt-get -y install php

透過按==空白==選擇apache2，選擇後會出現星號

透過設定檔來設定資料庫

輸入phpmyadmin使用者的密碼

到 127.0.0.1/phpmyadmin 使用者登錄頁面

遇到報錯

mysql said: ERROR 1819 (HY000) at line 1: Your password does not satisfy the current policy requirements
dbconfig-common: phpmyadmin configure: aborted.

[解決方法](https://www.digitalocean.com/community/tutorials/how-to-install-linux-apache-mysql-php-lamp-stack-on-ubuntu-22-04')