Database
===

### E-R模型
- 數據列(字段)
- 能唯一標記某字段: 主鍵(primary key)
- 數據行(紀錄)
- 數據表(行集合)
- 數據庫(表集合)

### 字段
- 類型
- 整數: int,
- 小數: decimal(需指定總位數與小數位數)
- `decimal(5,2)` > 總共5位, 小數2位
- 1.1(o), 1000.11(x)
- 字符串: char, varchar, text
- char & varchar > 有限長度的str
- char>
- 固定
- char(8) > "abcd " > 不足會補空格
- varchar
- 可變
- varchar(8) > "abcd" > 不會補空格
- text > 文本
- datetime
- bit
- enum
- 說明:
- 圖片、音檔、影檔等存在服務器上，表中存路徑
- 使用數據類型原則: 夠用即可, 越小越好
- [參考](https://blog.csdn.net/anxpp/article/details/51284106)

### 約束
- primary key
- 不會重複, 唯一標示
- `vs.unique`
- 只能建有一個
- 存儲結構系按照主鍵來維護
- not null
- unique
- 可以建立多個惟一
- default
- foreign key: 存的值都是別的表的主鍵, 該列稱為外鍵
- auto_increment 表自動增長

## RDBMS(Relational Database Management System)
### 主要
- Oracal: 常用於大型項目
- MySQL: 常用於Web
- MS SQL Server: 微軟
- SQLite: 行動端

### [排名](https://db-engines.com/en/ranking)

## SQL(Structured Query Language)
> 用來操作RDBMS的數據庫語言, 亦即SQL可操作上述產品
- 主要語句
- DQL: 數據查詢語言, 如select
- DML: 數據操作語言, 如insert、udpate、delete
- DDL: 數據定義語言, 如create、drop
- TPL: 事務處理語言, 如begin transaction、commit、rollback
- DCL: 數據控制語言, 如grant、revoke
- CCL: 指針控制語言, 如declare cursor
- Web程序員的重點在於CRUD(增刪改查)
- SQL不區分大小寫

### 小結
> - RDBMS: 關係數據庫的管理系統, 有多種產品MySQL, Oracal等
> - 數據庫: 各系統產生其特殊文件, 稱為數據庫
> - 數據庫由各種數據表組成, 數據表由各種行(紀錄)列(字段)組成
> - SQL係用來與各系統溝通的語言

## MySQL

- 服務器端
- 啟動/重啟/停止(start/restart/stop)
- Ubuntu `$ sudo service mysql start`
- Mac `$ sudo /usr/local/mysql/support-files/mysql.server start`
- 配置文件目錄
- Ubuntu`/etc/mysql/mysql.cnf`
- Mac 默認沒有, 可以自己建
- 配置項目
- Ubuntu`/etc/mysql/mysql.cnf.d/mysql.cnf`
- cnf > config 簡稱
- Mac 默認沒有

- 客戶端

- 改帳密
- 進去
- 先用sudo`$ sudo -uroot -proot`
- 或查帳密
- `$ sudo cat /etc/mysql/debian.cnf`
- `$ sudo mysql -u debian-sys-maint -p密碼`
- 重設

    mysql> use mysql;
    mysql> update user set authentication_string=PASSWORD('密碼') where user='帳號' and Host ='localhost';
    mysql> update user set plugin = "mysql_native_password" where user='帳號';
    mysql> flush privileges;
    mysql> quit;

- 重啟 `$ sudo /etc/init.d/mysql restart`

-- 進入 
  -- $ mysql -uooxx -pxxoo
  -- $ mysql -uooxx -p
    -- $ mysql -uhaha -phehe
-- 退出 
   exit;/quit;/ctrl+d
   
   
  
-- 查看所有數據庫
   show databases;
   
-- 建立數據庫
   create database DatabaseName;
   create database DatabaseName charset=utf8;
   -- 沒打編碼會預設

-- 查看創建數據庫
   show create database DatabaseName;
   
-- 刪除數據庫
   drop database DatabaseName; 
   -- 注意, 如果database name 有一些特殊符號, 如haha-4
   -- 那麼可能在刪除的時候會出錯
   -- 可以把整個名字用``匡起來, 代表一個整體
   -- 例如, drop database `haha-4`;
   
-- 查看當前使用數據庫:
   select database();

-- 使用數據庫:
   use DatabaseName;
   

   

-- 查看當前數據庫的表
   show tables;
   
-- 創建表
   -- create table 數據表名字 (字段 類型 約束[, 字段 類型 約束])
      create table ooxx(id int, name varchar(30));
      create table xxoo(id int primary key not null auto_increment, name varchar(30));
   -- 太長?換行寫
      create table oxox(
      id int primary key not null auto_increment,
      name varchar(30)
      );
      
      insert into students values(0, "GodJJ", 168.88, "he", 0);
      
-- 查看表之結構
   -- desc 表名;
   -- show create table 表名;
   
-- Q. 創建students表(id, name, high, gender, cls_id)
create table students(
    id int unsigned not null auto_increment primary key ,
    name varchar(30),
    high decimal(5,2),
    gender enum("he", "she", "zir", "hir", "secret") default "secret",
    cls_id int unsigned
);
   -- 先int 才接unisgned , 與C相反(unsigned int a;)

-- Q. 創建classes(id, name)
create table classes(
    id int unsigned not null primary key auto_increment,
    name varchar(30)
);

insert into classes values(0, "Alimamado");

-- if not exists
-- 如果不存在, 我就執行; 反之, 不執行
create table if not exists classes(oo xx,xx oo);


-- 添加字段:
--alter table 表名 add 列名 類型;
alter table students add birthday datetime;

-- 修改字段(不改名):
-- alter table 表名 modify 列名 類型 約束;
alter table students modify birthday date;

-- 修改字段(改名):
-- alter table 表名 change 舊列名 新列名 類型 約束;
alter table students change birthday birth date default "2019-08-06";

-- 刪除字段:
-- alter table 表名 drop 列名;
alter table students drop high;

-- 刪除表:
-- drop table 表名;
drop table ooxx;



-- CRUD(增刪改查)

 desc students;
+--------+---------------------------------------+------+-----+------------+----------------+
| Field  | Type                                  | Null | Key | Default    | Extra          |
+--------+---------------------------------------+------+-----+------------+----------------+
| id     | int(10) unsigned                      | NO   | PRI | NULL       | auto_increment |
| name   | varchar(30)                           | YES  |     | NULL       |                |
| gender | enum('he','she','zir','hir','secret') | YES  |     | secret     |                |
| cls_id | int(10) unsigned                      | YES  |     | NULL       |                |
| birth  | date                                  | YES  |     | 2019-08-06 |                |
+--------+---------------------------------------+------+-----+------------+----------------+


   -- 新增數據
   -- insert into ooxx vaulues();
   insert into students values(0, "Toyz", 168.88, "he", "1993-01-01");
   insert into students values(null, "Toyz", 168.88, "he", "1993-01-01");
   insert into students values(default, "Toyz", 168.88, "he", "1993-01-01");
   
   -- enum
   -- 輸入非選項
   insert into students values(default, "Toyz", "other", 1, "1993-01-01");
   Error: Data truncated for column 'gender' at row 1
   
   -- enum下標從1開始
   insert into students values(default, "Toyz", 0, 1, "1993-01-01");
   ERROR: Data truncated for column 'gender' at row 1
   
   insert into students values(default, "Toyz", 1, 1, "1993-01-01");
   
   insert into students values(default, "Toyz", 6, 1, "1993-01-01");
   ERROR: Data truncated for column 'gender' at row 1
   
   
   -- 部分插入
   -- insert into 表名(列,...) values (值,...);
   insert into students (name, gender) values ("Harry", 1);
   
   -- 多行插入
   insert into students (name, gender) values ("Snape", 1), ("Albus", 1);

   insert into students values
   (0, "Naluto", 168.88, 1, "1993-01-01"),
   (0, "Sasuki", 168.88, 1, "1993-01-01");


   -- 修改數據
   -- update 表名 set 列1=值1,列2=值2,... where 條件;
   update students set gender=1; -- 性別全改成男
   update students set gender=1 where name="Toyz"; -- 叫Toyz的都改成男
   update students set gender=1 where id=4; -- 標號4改男
   update students set gender=1, cls_id=3 where id=7; -- 標號7改男跟三班


   -- 查詢數據
   -- 查詢所有
   -- select * form 表名;
   select * from students;
   
   -- 查詢條件數據
   -- select * from 表名 where 條件;
   select * from students where id>=3;
   
   -- 查詢指定列
   -- select 列1,列2,.. from 表名;
   select name, cls_id from students;

   
   -- 改名顯示
   -- select 列1 as 名1, 列2 as 名2,.. from 表名;
   select name as 姓名, gender as 性別 from students;
   
   -- 字段順序
   select gender, name from 表名;
   
   -- 刪除
   
      -- 物理刪除
      -- delete from 表名 where 條件;
      delete from students; -- 全刪
      delete from students where name="Toyz";
         -- 實務上盡量不要物理刪除, Data非常重要
      
      -- 邏輯刪除
      -- 用一個字段來表示這條紀錄是否能使用
      alter table students add is_delete default 0;
      update students set is_delete=1 where id=10;
      -- 利用查詢來看有效帳號
      -- bit 太小, 故會有無法顯示的情形, 屬正常

-- 顯示當前版本
   select version();
-- 顯示當前時間
   select now();
   

-- ctrl + a 回行首
-- ctrl + e 回行末

#### MySQL儲存引擎
- InnoDB
- 支援外鍵
- 支援事務處理: 要馬不做
- 支援行級鎖

### 查詢補充

-- select 表名.列1, 表名.列2... from 表名;
select students.name, students.age from students

-- 給表取別名
-- select 別名.列1, 別名.列2... from 表名 as 別名;
select s.name, s.age from students as s;

-- 取別名就要用
select students.name, s.age from students as s;
ERROR: Unknown column 'students.name' in 'field list'

-- 消除重複行
-- distinct 字段
select distinct gender from students;

-- 條件查詢
   -- 比較運算符 >, <, =, >=, <=, !=(or <>)
   select * from students where age>18;
      -- cf. 指定顯示訊息沒有包含條件也沒有衝突
      select name, gender from students where age>18; 
      -- cf. <> 在python2可以用, python3就不能用了
   -- 邏輯判斷符 and, or, not
   select * from students where age>18 and age<28;
      -- 判斷語句必須寫完整
      select * from students where age >18 and <28;
      Erroe: You have an error in your SQL syntax;
  select * from students where age>18 and gender=2;
  select * from students where age>18 and gender="she";
  
  -- 不在18歲以上女性的訊息
  select * from students where not (age>=18 and gender=2);
  -- 不在18歲以上且為女性的訊息
  select * from students where not age>=18 and gender=2;
   
   
-- 模糊查詢
   -- like
   -- % 替換一個或多個
   -- _ 替換一個
   
   -- 查詢以"小"開頭的名字
   select name from students where name like "小%";
   -- 查詢名字有"小"的名字
   select name from students where name like "%小%";
   
   -- 查詢名字兩個字
   select name from students where name like "__";
   
   -- 查詢名字兩個字以上
   select name from students where name like "__%";


   -- rlike 加 正則
   -- 周開頭
   select name from students where name rlike "^周.*";  
   -- 周開頭倫結尾
   select name from students where name rlike "^周.*倫$";
   
   
-- 範圍查詢
   -- 非連續: in, not in
   -- 查詢年齡18,12,34
   select name, age from students where age=18 or age=12 or age=34;
   select name, age from students where age in (18, 12, 34);
   select name, age from students where age not in (18, 12, 34);
   
   -- 連續: between
   -- 查詢年齡18~28
   select name, age from students where age between 18 and 28;
   -- 查詢年齡非18~28
   select name, age from students where age not between 18 and 28;
   select name, age from students where not age between 18 and 28;
      -- cf not between 為一個用法, 不能拆開
      select name, age from students where age not (between 18 and 28);
      Error: You have an error in your SQL syntax;
      
   -- 判空 is null
   select * from students where height is null;
   select * from students where height is not null;
   
   
-- 排序
   -- order by 字段
   -- 由低到高 asc
   -- 18~34歲男性 年齡由低到高排序
   select * from students where (age between 18 and 34) and gender=1 order by age;
   select * from students where (age between 18 and 34) and gender=1 order by age asc;
      -- order by 預設為 asc.
      -- (age between) 小括號增加可讀性, 沒括也沒問題
   
   -- 由高到低 desc
   -- 18~34歲女性 年齡由高到低排序
   select * from students where (age between 18 and 34) and gender=2 order by age desc;
   
   -- order by 多字段,
   -- 18~34女性, 身高高到矮, 相同身高照年齡小到大
   select * from students where (age between 18 and 34) order by height desc, age asc;
   
   -- 按年齡小到大, 身高高到矮, id大到小
   select * from students order by age, height desc, id desc; 
   
   
-- 聚合函數
   -- 總數count
   -- 查詢男性有多少人
   select count(*) from students where gender=1;
   
   -- 最大值max
   -- 查詢最大年齡
   select max(age) from students;
   -- 查詢女性最高身高
   select max(height) from students where gender=2;
   
   -- 和sum
   -- 所有年齡總合
   select sum(age) from students;
   
   -- 平均avg
   select avg(age) from students;
   -- 計算平均年齡
   select sum(age)/count(*) from students;
   
   -- 四捨五入 round(value, 小數)
   -- 四捨五入平均年齡到小數第二位
   select round(sum(age)/count(*), 2) from students;
   
   -- ps. C語言存儲的小數都是約等於, 
   -- 在銀行裡絕對不允許約等於
   -- 解決辦法: 將小數放大, 不要使用小數
   -- ex. 3.14 -> 3.14*100
   
   -- ERROR 1140
   select sum(age), name from students;
      -- 總和為一個值, 要用哪個名字來代表？故失敗
   
-- 分組
 
   -- group by
   -- 按性別分組
   select gender from students group by gender;
      -- 失敗 ERROR 1055
      select * from students group by gender;
      select name from students group by gender;
         -- 名字有很多個, 要用哪一個來當組名？故失敗   
   -- 計算各性別人數
   select gender, count(*) from students group by gender;
   
   -- group_concat(欲顯示的東西)
   -- 查詢同性別的姓名
   select gender, group_concat(name) from students group by gender;
   -- (裡面寫啥都可)
   select gender, group_concat("GodJJ") from students group by gender;
   -- 查詢同性姓名與年齡
   select gender, group_concat(name, " ", age) from students group by gender;
   -- 查詢男性姓名與年齡
   select gender, group_concat(name, " ", age) from students where gender=1 group by gender;
   
   -- Q. group by 與 distinct 差別?
      -- group by 效率較低, 但延展性較高
      -- distinct 效率高, 但就是單純把重複的名稱刪掉
   
   
   -- having 條件判斷
   -- 查詢平均年齡超過30的性別及該性別姓名
   select gender, group_concat(name), avg(age) from students group by gender having avg(age)>30;
   
   -- 查詢性別人數大於二的信息
   select gender, group_concat(name) from students group by gender haveing count(*)>2;


   -- Q. where與having區別?
   -- where是對表進行條件, having是對分組的組別進行條件
   
   -- 分組實際運用: 常用於按照部門分
   -- 分組與聚合一起運用才有意義
   
   
-- 分頁: 限制查詢各數, 避免一次顯示太多
   -- limit start, count
   -- 每頁顯示2個, 頁面1
   select * from students limit 0, 2;
   -- 每頁顯示2個, 頁面2
   select * from students limit 2, 2;
   -- 每頁顯示2個, 頁面3
   select * from students limit 4, 2;
   -- 每頁顯示2個, 頁面4
   select * from students limit 6, 2;
   -- 每頁顯示2個, 頁面5
   select * from students limit 8, 2;
   -- limit (第n頁-1)*每頁個數, 每頁個數;
   
   -- 注意, 不要使用式子
   -- ERROR 1064
   select * from students limit (5-1)*2, 2;
   
   
   -- 注意, limit一定放在最後
   -- ERROR 1064 
   select * from students limit 5,2 order by age asc;
   -- 正確
   select * from students order by age asc limit 5,2;
   
   
   
-- 連接查詢
   -- 內連接[圖一]: 只顯示雙方有交集的相關信息
   -- inner join ... on
   
   -- 查詢能夠對應班級及學生的信息
   select * from students inner join classes on students.id_cls = classes.id;
   -- id_cls跟classes.id 重複顯示了
   select students.*, classes.name from students inner join classes on students.cls_id = classes.id;
   -- 取別名
   select s.*, c.name from students as s inner join classes as c on s.cls_id = c.id;
   
   -- 將上述信息按班級排, 同班級按id小至大, 班級信息在第一列
   select c.name, s.* from students as s inner join classes as c on c.id = s.cls_id order by c.name, s.id;
   
   
   -- 左連接[圖二]: 以左邊為基準, 顯示與左右交集的右邊信息
   -- left join ... on
   select * from students as s left join classes as c on c.id = s.cls_id;
   
   -- 查詢沒有對應班級的學生信息
   select * from students as s left join classes as c on c.id = s.cls_id having c.id is null;
   select * from students as s left join classes as c on c.id = s.cls_id where c.id is null;
   
   -- 雖然having跟where都可以, 
   -- 習慣上對於原表的篩選條件都是以where為主
   -- 而對於新產生的表所使用的篩選條件則以having為主
   -- 此連接表屬於新產生的表, 故習慣上還是以having為主
   
   
   -- 右連接[圖三] > 很少用, 因為左連接的兩表對調即可達成效果

> - 圖一
> 
> - 圖二
> 
> - 圖三
> 

### 自關聯
> 一個表中的值使用表中另一個值

--外鍵--
# 表一 city    # 表二 District   # 表三 
id  c_name    id  d_name c_id  id  Village d_id
1   台北市     1    信義區   1    1  西村里     1
2   新竹縣     2    竹北市   2    2  東平里     2
3   金門縣     3    金城鎮   3    3  珠沙里     3

--自關聯--
id  name  tw_id
1  台北市   null
2  新竹縣   null
3  金門縣   null
4  信義區    1
5  竹北市    2
6  金城鎮    3
7  西村里    4
8  東平里    5
9  珠沙里    6

-- 查詢所有直轄市/縣/市
   select * from areas where tw_id is null;
-- 查隸屬台北市的所有區
   select id from areas where name="台北市";
   select * from areas where tw_id=1;

-- 一行搞定
   -- 邏輯: 叫表兩次後關聯在一起 
   select * from areas as city inner join areas as district 
   on city.id = district.tw_id having city.name = "台北市";
   -- 顯示需要的信息
   select c.name, d.name from areas as c inner join areas as d 
   on c.id = d.tw_id having c.name = "台北市";

#### 如何直接匯入.sql數據

# 首先進入SQL前要先到該檔案的同層路徑
$ ls
xxx.sql
$ cat xxx.sql
INSERT INTO `haha` VALUES ('9527', 'GodJJ', '65535');
INSERT INTO `haha` VALUES ('9527', 'Toyz', '65536');

$ mysql -uxx -pxx

mysql> use xxx
mysql> source xxx.sql;

### 子查詢
> 查詢裡面嵌套查詢

-- 查詢身高最高男生的資料
   select * from students where height = (select max(height) from students where gender=1);
   
-- 查詢台北市資料
   select * from areas where tw_id = (select id from areas where name = "台北市"); 

## 數據庫設計

#### 範式1: 列拆到不能再拆

contact                  name   tel     addr
小明, 0987654321, US  >   小明   0987..   US

#### 範式2:
1. 表需有主鍵
2. 其他信息需完全依賴主鍵, 不能部分依賴

表 OrderDetail
OrderID  ProductID UnitPrice  Quantity  ProcductName
100       01       10          2          洗髮水

  - 主鍵可以有兩個以上
    - 此表的主鍵應該是OrderID跟ProductID所構成
    - 如此才能描述這張訂單的全貌
  - 惟商品名跟商品單價僅依賴商品ID, 而非完全依賴主鍵(訂單ID+商品ID)
  - 故此數據不符合範式2

表 OrderDetail
OrderID  ProductID Quantity
100       01        2

表 Product
ProductID UnitPrice ProcductName
01         10        洗髮水

  - 如此才符合範式2

#### 範式3: 非主鍵列須直接依賴主鍵, 不能傳遞依賴

表 Order
OrderID Date CustomerID CustomerName CustomerAddr CustomerPhone
100     0305   10       小明          台北          0987..

  - 顧客ID依賴訂單ID沒問題, 但是顧客訊息沒有直接依賴訂單ID
  - 顧客訊息先依賴顧客ID後, 顧客ID才依賴訂單ID


表 Order
OrderID Date CustomerID
100     0305   10      

表 Customer
CustomerID CustomerName CustomerAddr CustomerPhone
10          小明          台北          0987..

表 Order
OrderID Date CustomerID
100     0305   10      

表 OrderDetail
OrderID  ProductID Quantity
100       01        2
100       03        1

表 Product
ProductID UnitPrice ProcductName
01         10        洗髮水
02         5         牙膏
03         2         牙刷

表 Customer
CustomerID CustomerName CustomerAddr CustomerPhone
1           小花          台中          0978..
2           小子          花蓮          0912..
10          小明          台北          0987..


  -  表Order的訂單ID關聯到訂單細節,
  -  表Order的顧客ID關聯到顧客資料
  -  ...
  -  如此稱之為關係數據庫

## E-R模型
#### 一對一

# 當列太多時，可以拆表，將不常用的資訊放到另一張表
- Customer
id name   tel   infor_id
1  GodJJ  0987    1

- Information
id  mom   dad
1   haha  hehe

#### 一對多

# 一對多時, 必須在多的表添加
# 否則未來haha跟hehe又生小孩時, 該關系列也要修改
# 故實務上多在N表添加
- Customer
id name   tel   infor_id
1  GodJJ  0987    1
2  GodGG  0932    1

- Information
id  mom   dad
1   haha  hehe

#### 多對多

科目              學生
id  subject       id  name
1   國文            1  haha
2   英文            2  hehe
3   數學            3  hoho

# 一個學生可以上多個課, 
# 一堂課也可以有多個學生上
# 解法: 多一張表, 該表稱為『聚合表』

科目-學生
1  1
1  2
2  2
2  3

#### 16.2練習

-- 查詢每種類型中最貴的信息
   -- 問題: 只有一個表 > 叫表兩次, 關聯在一起

-- 表一
select * from goods;
-- 表二
select cate_name, max(price) from goods group by cate_name;

-- 實驗: 查詢出來的表也可以直接使用, 只是要有別名
-- ERROR 1248 (42000): Every derived table must have its own alias
select * from (select cate_name from goods group by cate_name);
-- 取別名就沒問題
select * from (select cate_name from goods group by cate_name) as g;

-- 邏輯:
-- select * from 表二 left join goods on 
-- 表二.max_price = goods.price and 表二.cate_name = goods_cate_name;
select * from (
    select cate_name, 
    max(price) as max_price from goods group by cate_name
) as g_new left join goods as g
on g_new.max_price = g.price and g_new.cate_name = g.cate_name;
   -- 因為不能寫 g_new.max(price),
   -- 所以在 max(price) 所產生的列取一個別名 max_price
   
-- 整理
select g.name, g.cate_name, g.price from (
    select cate_name, 
    max(price) as max_price from goods group by cate_name
) as g_new left join goods as g
on g_new.max_price = g.price and g_new.cate_name = g.cate_name 
order by g.cate_name;

   -- 此方法稱為『表級子查詢』

#### 16.2 拆表

-- 把商品類型表拆出來
-- 1. 創建
create table goods_cates(
    id int unsigned primary key auto_increment,
    name varchar(40) not null
);

-- 2. 寫入數據
-- 我要的數據
select cate_name from goods group by cate_name;

-- 部分插入:
-- insert into 表名(列,...) values (值,...);
-- 注意！ 使用子查詢來新增時沒有Values, 否則語法錯誤
insert into goods_cates(name) select cate_name from goods group by cate_name;

-- ERROR 1064 (42000): You have an error in your SQL syntax; 
insert into goods_cates(name) values select cate_name from goods group by cate_name;

-- 補充: 創表時直接插入數據
create table goods_cates (
    id int unsigned primary key auto_increment,
    name varchar(40) not null) 
select cate_name as name from goods group by cate_name;
   -- 注意查詢出來的列名要跟欲插入的列名相同,
   -- 不同名時要取別名



-- 3. 同步表數據
-- update 表名 set 列1=值1,列2=值2
-- update goods set cate_name = xxx;

-- 邏輯: 使用關聯條件, 
-- goods的cate_name跟goods_cates的name一樣時, 
goods as g inner join goods_cates as c on g.cate_name = c.name
-- 就把cate_name列改成goods_cates的ID
update goods as g inner join goods_cates as c on g.cate_name = c.name set g.cate_name = c.id;


-- 4. 修改表結構
-- 由於兩表的這兩列要同步, 故表結構也應該相同
desc goods_cates;
desc goods;

-- 習慣上會把該列改成欲同步的表名_id > cate_id
-- alter table 表名 change 舊列名 新列名 類型 約束;
alter table goods change cate_name cate_id int unsigned not null;

-- 5. 設定外鍵
-- 為了避免cate_id列出現goods_cates.id列以外的東西
-- 例如類別只有十種, cate_id列卻有17的數字, 造成數據對不起來
-- 故應做一些限制, 方法有二
   -- (1) 在新增數據的時候做判斷該id或名稱是否存在, 符合才能新增
   -- (2) 將該列新增外鍵限制, 不符合時系統會報錯

-- alter table 表名 add foreign key (列) references 對應表名(對應列名);
alter table goods add foreign key (cate_id) references goods_cates(id);

-- 注意
   -- (1) 如果表裡已經有不符合的數據, 則會新增失敗> 先把不符合的數據刪改後再設定
   -- (2) 外鍵只有innodb數據引擎支援




-- 刪除外鍵
-- 事實上實務上很少使用外鍵, 因為外鍵的效率低
-- 1. 先查出外鍵約束名稱, 由系統自動產生
-- show create table ooxx;
show create table goods;
goods | CREATE TABLE `goods` (
...
CONSTRAINT `goods_ibfk_1` FOREIGN KEY (`cate_id`) REFERENCES `goods_cates` (`id`),
CONSTRAINT `goods_ibfk_2` FOREIGN KEY (`brand_id`) REFERENCES `goods_brands` (`id`)
...
)
   -- CONSTRAINT 後面就是外鍵約束名稱

-- 2. alter table goods drop foreign key 外键名称;
alter table goods drop foreign key goods_ibfk_1;
alter table goods drop foreign key goods_ibfk_2;
-- 或是一次改
alter table goods 
drop foreign key goods_ibfk_1,
drop foreign key goods_ibfk_2;



-- MySQL 基本上係存儲於硬盤中
-- 速度: CPU>內存>固態硬盤>機械硬盤
-- 故硬盤速度慢
-- 解法: 把主鍵存在內存中, 要搜尋時到內存中找要快得多

-- Q. 為何不都存到內存？
-- 貴

## Python操作MySQL
#### 首先先載模塊

$ pip3 install pymysql
$ pip insatll 

#### Python訪問數據庫流程
1. 創建connection > 獲取cursor
2. 執行SQL
3. 關閉cursor > 關閉connection

### 查

In [1]: from pymysql import connect

# 創建conn對象
In [4]: conn = connect(host="localhost", port=3306, user='root', password='youknow.', database='jing_dong', charset='utf8')
# 獲取cursor
In [5]: cursor = conn.cursor()

# 使用execute來執行SQL語句
In [6]: cursor.execute("select * from goods")
# execute執行成功後會返回生效行數
Out[6]: 21

# Q. 返回行數沒用啊, 數據呢?
# 存在該游標對象裡, 透過fatch來取出
# fatch有三種 all, one, many, 跟字面上一樣意思
# 
# fetchone返回一行元祖
In [7]: cursor.fetchone()
Out[7]: (1, 'r510vc 15.6英吋筆記本', 1, 1, Decimal('3399.000'), b'\x01', b'\x00')
In [8]: cursor.fetchone()
Out[8]: (2, 'y400n 14.0英吋筆記本電腦', 1, 2, Decimal('4999.000'), b'\x01', b'\x00')
In [9]: cursor.fetchone()
Out[9]: (3, 'g150th 15.6英吋遊戲本', 2, 3, Decimal('8499.000'), b'\x01', b'\x00')

# fatchmany可以輸入參數, 代表取幾行, 預設一行
# fatchmany返回元祖嵌元祖
In [10]: cursor.fetchmany()
Out[10]: ((4, 'x550cc 15.6英吋筆記本', 1, 1, Decimal('2799.000'), b'\x01', b'\x00'),)
In [11]: cursor.fetchmany(2)
Out[11]: 
((5, 'x240 超極本', 3, 2, Decimal('4880.000'), b'\x01', b'\x00'),
 (6, 'u330p 13.3英吋超極本', 3, 2, Decimal('4299.000'), b'\x01', b'\x00'))

In [12]: cursor.fetchall()
((7, 'svp13226scb 觸控超極本', 3, 4, Decimal('7999.000'), b'\x01', b'\x00'),
...
 (21, '商務雙肩背包', 7, 4, Decimal('99.000'), b'\x01', b'\x00'))

# 取完就沒了
In [13]: cursor.fetchone()


# 實際運用範例
In [14]: cursor.execute("select * from goods")
Out[14]: 21

# 找對象存起來
In [15]: content = cursor.fetchone() 
In [17]: content   
Out[17]: (1, 'r510vc 15.6英吋筆記本', 1, 1, Decimal('3399.000'), b'\x01', b'\x00')

In [16]: content[0]
Out[16]: 1
In [18]: content[1]
Out[18]: 'r510vc 15.6英吋筆記本'

In [19]: for temp in content: 
    ...:     print(temp) 
    ...:            
1
...
b'\x00'

In [20]: content = cursor.fetchmany(3)
In [21]: content   
Out[21]: 
((2, 'y400n 14.0英吋筆記本電腦', 1, 2, Decimal('4999.000'), b'\x01', b'\x00'),
 (3, 'g150th 15.6英吋遊戲本', 2, 3, Decimal('8499.000'), b'\x01', b'\x00'),
 (4, 'x550cc 15.6英吋筆記本', 1, 1, Decimal('2799.000'), b'\x01', b'\x00'))

In [22]: content[1]
Out[22]: (3, 'g150th 15.6英吋遊戲本', 2, 3, Decimal('8499.000'), b'\x01', b'\x00')


# 使用完要把cursor跟conn關閉
In [24]: cursor.close()
In [25]: conn.close()

### 實作

> - 查所有信息
> - 查所有分類
> - 查所有品牌分類

#coding:utf-8

from pymysql import connect

conn = connect(host="localhost", port=3306, user="root", password="youknow.", database="jing_dong", charset="utf8")
cs = conn.cursor()

while True:

    a = input("請問您要查詢什麼? 1.所有商品信息, 2. 所有分類 3. 所有品牌分類 4. 結束 ")
    if a == "1":
        cs.execute("select * from goods")
    elif a == "2":
        cs.execute("select name from goods_cates")
    elif a == "3":
        cs.execute("select name from goods_brands")
    elif a == "4":
        break

    lines = cs.fetchall()
    for temp in lines:
        print(temp)

cs.close()
conn.close()

#coding:utf-8

from pymysql import connect


class JD(object):
    def __init__(self):
        self.conn = connect(host="localhost", port=3306, user="root", password="youknow.", database="jing_dong", charset="utf8")
        self.cs = self.conn.cursor()

    def __del__(self):
        self.cs.close()
        self.conn.close()

    @staticmethod
    def print_menu():
        print("--京東--")
        print("1. 所有商品信息")
        print("2. 所有分類")
        print("3. 所有品牌分類")
        print("0. 結束")
        return input("請輸入功能號碼")

    def execute_sql(self, sql):
        self.cs.execute(sql)
        lines = self.cs.fetchall()
        for temp in lines:
            print(temp)

    def show_all_items(self):
        sql = "select * from goods;"
        self.execute_sql(sql)

    def show_all_cates(self):
        sql = "select name from goods_cates;"
        self.execute_sql(sql)

    def show_all_brands(self):
        sql = "select name from goods_brands;"
        self.execute_sql(sql)
    
    def run(self):
        while True:
            num = self.print_menu()
            if num == "1":
                self.show_all_items()
            elif num =="2":
                self.show_all_cates()
            elif num =="3":
                self.show_all_brands()
            elif num =="0":
                break

def main():
    jd = JD()
    jd.run()

if __name__ == "__main__":
    main()

### 增刪改
> - 增刪改除SQL語句不同外
> - 增刪改需要多一道connect.commit的動作來確認, 因為他會改變數據庫
> - 注意是connect 不是 cursor
> - 反悔不改時, 提交connect.rollback即可

In [1]: from pymysql import connect
In [2]: conn = connect(host="localhost", port=3306, user="root", password="youknow.", database="jing_dong", charset="utf8")
In [3]: cs = conn.cursor()

# show create table goods_cates;
# AUTO_INCREMENT=8

# 注意
# In [6]: cs.execute("insert into goods_cates (name) value ("硬盤1")") 
    # SyntaxError: invalid syntax
    # 前兩個雙引號包再一起了，會兩個也是, 硬盤1沒有包到
    # 解決方法有二:
    # 1. 內雙引號改成單引號
    # 2. 外雙引號改成三個雙引號

In [7]: cs.execute("""insert into goods_cates (name) value ("硬盤1")""")
Out[7]: 1
In [8]: cs.execute("""insert into goods_cates (name) value ("硬盤2")""")
Out[8]: 1
In [9]: cs.execute("""insert into goods_cates (name) value ("硬盤3")""")
Out[9]: 1

# show create table goods_cates;
# AUTO_INCREMENT=11
# 但是select * from goods_cates; 沒有變

# 經過commit後才有新增進數據庫
# Q 為何AUTO_INCREMENT會新增? 
    # A 為了避免多任務併發產生問題
In [10]: conn.commit()

# rollback
In [11]: cs.execute("""insert into goods_cates (name) value ("硬盤4")""")
Out[11]: 1
In [12]: cs.execute("""insert into goods_cates (name) value ("硬盤5")""")
Out[12]: 1
In [14]: cs.execute("""insert into goods_cates (name) value ("硬盤6")""")
Out[14]: 1
In [15]: conn.rollback() 

# 此時查詢數據沒有新增
# AUTO_INCREMENT=14

# select * from goods_cates;
    # | 14 | 硬盤7               |
    # 11~13在剛剛被取消了
In [16]: cs.execute("""insert into goods_cates (name) value ("硬盤7")""")
Out[16]: 1
In [17]: conn.commit()

# 新增品牌分類

    @staticmethod
    def print_menu():
        print("--京東--")
        print("1. 所有商品信息")
        print("2. 所有分類")
        print("3. 所有品牌分類")
        print("4. 新增品牌分類")
        print("0. 結束")
        return input("請輸入功能號碼")

    def add_brands(self):
        brands_name = input("請輸入品牌名稱: ")
        sql = """insert into goods_brands (name) value ("%s")""" % brands_name
        self.cs.execute(sql)
        self.conn.commit()
        
    def run(self):
    while True:
        num = self.print_menu()
        if num == "1":
            self.show_all_items()
        elif num =="2":
            self.show_all_cates()
        elif num =="3":
            self.show_all_brands()
        elif num =="4":
            self.add_brands()
        elif num =="0":
            break

### SQL注入

# 新增一個指名查詢功能

    @staticmethod
    def print_menu():
        print("--京東--")
        print("1. 所有商品信息")
        print("2. 所有分類")
        print("3. 所有品牌分類")
        print("4. 新增品牌分類")
        print("5. 查詢指定商品信息")
        print("0. 結束")
        return input("請輸入功能號碼: ")
        
   def get_info_by_name(self):
        find_name = input("請輸入商品名稱: ")
        sql = """select * from goods where name = '%s';""" % find_name
        print("-->%s<--"% sql)
        self.execute_sql(sql)

    def run(self):
        while True:
            num = self.print_menu()
            if num == "1":
                self.show_all_items()
            elif num =="2":
                self.show_all_cates()
            elif num =="3":
                self.show_all_brands()
            elif num =="4":
                self.add_brands()
            elif num =="5":
                self.get_info_by_name()
            elif num =="0":
                break

$ python3 SQL注入.py
--京東--
1. 所有商品信息
2. 所有分類
3. 所有品牌分類
4. 新增品牌分類
5. 查詢指定商品信息
0. 結束
請輸入功能號碼: 1
(1, 'r510vc 15.6英吋筆記本' ...)
(...)

--京東--
1. 所有商品信息
2. 所有分類
3. 所有品牌分類
4. 新增品牌分類
5. 查詢指定商品信息
0. 結束
請輸入功能號碼: 5
請輸入商品名稱: r510vc 15.6英吋筆記本
-->select * from goods where name = 'r510vc 15.6英吋筆記本';<--
(1, 'r510vc 15.6英吋筆記本' ...)

--京東--
1. 所有商品信息
2. 所有分類
3. 所有品牌分類
4. 新增品牌分類
5. 查詢指定商品信息
0. 結束
請輸入功能號碼5
請輸入商品名稱: 'or 1=1 or '1
-->select * from goods where name = ''or 1=1 or '1';<--
(1, 'r510vc 15.6英吋筆記本' ...)
(...)


# 竟然全部都弄出來了!!!
# 因為將where條件補齊前後兩個引號無關緊要的條件, 
# 並加上1=1, 讓條件判斷為True

# """select * from goods where name = '%s';""" % find_name
  # 當輸入普通的商品名時
  """select * from goods where name = 'apple';""" % find_name
  # SQL注入 ' or 1=1 or '1
  """select * from goods where name = ''or 1=1 or '1'""";
  
# KeyWord 烏雲網

# 解決辦法: 不要自己填%s ,使用execute參數列表來填
    def get_info_by_name(self):
        find_name = input("請輸入商品名稱: ")
        # sql = """select * from goods where name = '%s';""" % find_name
        # print("-->%s<--"% sql)
        # self.execute_sql(sql)
        sql = """select * from goods where name = %s"""
        # 元祖列表都行, 可迭代都好
        self.cs.execute(sql, [find_name])
        print(self.cs.fetchall())
        

# sql = """select * from goods where name = %s"""
# 如此就沒有'%s'的漏洞可以讓人補
# 
# 注意:
# 如果有多個參數, 那%s就要有多個讓函數填
# [V1, V2,...]

## 視圖
> - Select語句返還的結果,
> - 對若干表引用,
> - 原數據表更改數據, 視圖亦同步修改

#### 建立

-- 建立
-- create view 視圖名 as select語句;
   -- 建議以 v_ 開頭
create view v_goods_info as select 
g.*, c.name as cate_name, b.name as brand_name from goods as g 
left join goods_cates as c on g.cate_id = c.id
left join goods_brands as b on g.brand_id = b.id;

-- 查詢
-- select * from v_goods_info;

-- 刪除
-- drop view 視圖名;
drop view v_goods_info

#### 優點
> - 修改數據而不影響程序運行
> - 當程序編碼的SQL語句都以視圖為主時, 原數據重構, 不影響程序運行
> - 注意! 修改數據是修改原數據表的數據, 而非修改視圖
> ```SQL
> update v_goods_info set name = "GodJJ" where id = 21;
> ERROR: The target table v_goods_info of the UPDATE is not updatable
> ```
> - 提高安全性
> - 僅提供用戶視圖, 可以選擇性提供信息
> - SQL簡潔
> - select xxxx 好長一坨, 把他弄成一張視圖
> - `select * from xx;` 搞定
>

## 事務
> 保證操作序列要馬全部成功, 要馬全部失敗
> 舉例: A轉帳給B, A-10, B+10
> - 檢查A帳戶>10
> - A-10
> - B+10
> - 為了避免A扣款後系統故障而導致B沒加10的窘境
> - 三個都完成才提交,
> - 說白話就是 connect.commit 與 connect.rollback 的操作

-- 1. 打開事務
start transaction;
-- 或
begin;

-- 2. 操作序列
select balance from checking where customer_id = 1;
update checking set balance = balance - 10 where customer_id = 1;
update savings set balance = balance + 10 where customer_id = 2;

-- 3. 提交
commit;

-- 3. 退回
rollback;

-- Python在使用pymysql的增刪改時, 預設就是開啟事務, 故最後才需要commit
-- MySQL也是, 惟每次Enter後會自動調用commit

#### 事務4特性(ACID)
> 1. 原子性(atomicity)
> - 事務不可分割, 不會只改其中一部分(A-10)
> 2. 一致性(consistency)
> - 要馬成功, 要馬不成功
> 3. 隔離性(isolation)
> - 當你手動開啟事務時, 打完操作序列後`select * from xx;`
> - 此時你看到的表是已修改的
> - 惟另外開啟一個MySQL來查看時會發現尚未修改!!
> - 因為該修改僅僅存於本地緩存中
> ```SQL
> -- MySQL1
> start transaction;
> update goods set name = "GodJJ" where id = 21;
> select id, name from goods;
> | 21 | Toyz喊在 |
>
> -- MySQL2
> select id, name from goods;
> | 21 | 商務雙肩背包 |
> ```
> - 另外, 當兩個客戶端欲更改『同一行數據』時, (不同的沒差)
> - 必須等先開啟事務者commit後才能操作
> ```SQL
> -- MySQL2
> -- 此時MySQL1還沒結束, 故須等待, 等待時間過久就會報錯
> update goods set name = "Toyz喊在" where id = 21;
> ERROR: Lock wait timeout exceeded; try restarting transaction
>
> -- MySQL1
> commit;
>
> -- MySQL2
> update goods set name = "Toyz喊在" where id = 21;
> Query OK, 1 row affected
>
> -- 此設定類似於線程搶鎖, 誰搶到誰用, 沒搶到就等
> -- 故將兩個客戶端隔離了
> ```
> 4. 持久性(durability)
> - 提交後永久保存於數據庫
>

## 索引

> - 對數據表裡所記錄的引用指針
> - InnoDB 數據表組成本來就包含索引, 有些引擎是獨立成一個文件
> - 主鍵外鍵其實就是索引的具體實現

-- 查看索引
Show index from 表名;

-- 創建索引
   -- 字段類型是字符串時需指定長度, 一般跟定義相同即可
   -- 不是字符串就不用寫長度
create index 索引名 on 表名(字段名(長度))

-- 刪除索引
drop index 索引名 on 表名

-- 創建了一個十萬筆資料的數據
select count(*) from test_index;
+----------+
| count(*) |
+----------+
|   100000 |
+----------+

-- 打開時間監測
set profiling=1;

-- 查詢最後一筆
select * from test_index where title='ha-99999';
-- 建立索引
create index title_index on test_index(title(10));
-- 查詢最後一筆
select * from test_index where title='ha-99999';

-- 查看時間
show profiles;
+----------+------------+---------------------------------------------------+
| Query_ID | Duration   | Query                                             |
+----------+------------+---------------------------------------------------+
|        1 | 0.03428600 | select * from test_index where title='ha-99999'   |
|        2 | 0.15015800 | create index title_index on test_index(title(10)) |
|        3 | 0.00240100 | select * from test_index where title='ha-99999'   |
+----------+------------+---------------------------------------------------+

> - SQL索引是透過B Tree 的數據結構來完成
> 

## 帳戶管理
> - database裡有一個mysql數據庫, 裡面管理所有帳戶相關
> `use mysql;`
> - 用戶管理及權限存在user數據庫的user表中
> `desc user;`
> - user表有各種字段, 其中
> `select host, user, authentication_string from user;`
> - Host: 允許訪問主機
> - % 表示任意電腦都可
> - Localhost 表示本地
> - User: 用戶名
> - authentication_string: 密碼
> - 該行顯示的值為加密後的密碼

#### 創建用戶

# 使用root帳戶
$ mysql -uroot -p

-- 創建用戶
-- create user '用戶名'@'訪問主機' identified by '密碼';
create user 'godjj'@'localhost' identified by '123456'

select user, host, authentication_string from user;
| godjj            | localhost | 加密後密碼 |


-- 查看權限
-- show grants for 用戶名@訪問主機;
show grants for godjj@localhost;
| GRANT USAGE ON *.* TO `godjj`@`localhost` |

-- 授與權限
-- grant 權限 on 數據庫 to 用戶名@訪問主機
   -- 主要權限包含: create、alter、drop、insert、update、delete、select
   -- 分配所有權限: all privileges
grant select on jing_dong.* to godjj@"localhost" identified by "123456"

show grants for godjj@localhost;
| GRANT USAGE ON *.* TO `godjj`@`localhost`            |
| GRANT SELECT ON `jing_dong`.* TO `godjj`@`localhost` |

$ mysql -ugodjj -p123456

show databases;
| information_schema |
| jing_dong          |

use jing_dong;

select * from goods;
21 rows in set (0.00 sec)
select * from goods_cates;
11 rows in set (0.00 sec)

-- 剛剛只有給select, 故只能做select操作
update goods set name = "Toyz" where id = 21;
Error: UPDATE command denied to user 'godjj'@'localhost' for table 'goods'

#### 修改權限

$ mysql -uroot -p

-- 修改權限
-- grant 權限 on 數據庫 to 用戶名@訪問主機 with grant option;

-- 刷新權限
flush privileges;

$ mysql -ugodjj -p123456

use jing_dong;

update goods set name = "Toyz" where id = 21;
Query OK, 1 row affected (0.01 sec

#### 修改密碼

$ mysql -uroot -p

use mysql;

-- alter user '用戶'@'訪問主機' indentified by '密碼';
ALTER USER 'godjj'@'localhost' IDENTIFIED BY '123';

flush privileges;

-- update user set authentication_string=password('密碼') where user='用戶';
-- 一直語法錯誤,  不知道為啥

#### 遠程登入

`用戶@'%'`

$ mysql -u帳號 -p密碼 -hIP -p端口
$ mysql -utoyz -p123 -h192.111.23.34 -p3306

> - Ubuntu 要把mysqld.cnf文件裡的bind-address 給註釋(#)掉
> - 非必要絕對不要用!
> - 避免有心人士使用[IP端口掃描器](https://zh.wikipedia.org/wiki/%E7%AB%AF%E5%8F%A3%E6%89%AB%E6%8F%8F%E5%B7%A5%E5%85%B7)找到你的IP端口
> - 大家的帳戶都有個root, 接著只要瘋狂輸入密碼, 被找到就進去了, 接著你的Database就GG了
> - 替代方案: SSH, 帳號不知道的話很難破解, 但只缺密碼難度就相對低了

#### 刪除帳戶

-- 1. root登入

-- 2. drop user '用户'@'主機';
drop user godjj@localhost;

-- 或
-- 2. delete from user where user='用户';
-- 沒試過

## 主從(我還沒成功過XD)

> 讀寫分離，負載均衡，數據備份

Step1 手動備份

# 主備份
$mysqldump -uroot -p 數據庫名 > 備份名.sql
$ mysqldump -uroot -p jing_dong > test.sql

# 從導入
# 1. 先登入msyql, 創建一個數據庫
# 2. 退出登入, $ mysqldump -uroot -p 數據庫名 < 備份名.sql
     $ mysqldump -uroot -p test < test.sql
    
# 或
use 數據庫;
source xxx.sql;
-------------------------------------------------
# 註: 如果一開始就倒出所有數據, 導入就不用寫數據庫了
$ mysqldump -uroot -p --all-databases --lock-all-tables > ~/test2.sql
  # --lock-all-tables: 防止操作時有數據修改
  # > 後面可以寫位置, 沒寫就是當前
  
$ mysql –uroot –p < ~/test2.sql

Step2 調整設置

# Ubuntu
$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

# 主服務器把 server-id 與 log_bin 打開(刪掉#)
# 從服務器把 server-id打開
# server-id 不能一樣, 習慣上都會改成IP最後的值

# 配置完後sudo service mysql restart

Step3 連接

# 主服務器創建用於同步的帳號
-- create user '用戶名'@'訪問主機' identified by '密碼';
create user 'slave'@'%' identified by '123';

-- grant 權限 on 數據庫 to 用戶名@訪問主機
grant replication slave on *.* to 'slave'@'%';
flush privileges;

# 查詢日誌文件名與位置 master_log_file, matser_log_pos
show master status;


# 從服務器設置連接
change master to master_host='主服務器IP',
master_user='同步用戶帳號', master_password='同步用戶密碼',
master_log_file='日誌文件名', master_log_pos=日誌文件位置;

# 開啟同步
start slave;

# 查看同步狀態
show slave status \G;
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
  # 表示同步運行正常