筆記

https://hackmd.io/GsfjtXfcTh64DMlRmvvrZg?both Effective SQL 資料庫模型設計 確定所有資料表都有主鍵 Customer Column 2 Column 3 John A. Smith Text Text Smith, John A. -------- -------- John Smith -------- -------- John A Smith -------- -------- Smith, John -------- -------- 上表中不一致的資料，雖然對電腦來說每筆都是獨特的，但其實可能是重複的， 而這種重複沒有程式來判斷行不通，就算將Customer訂為主鍵，符合了唯一需求，仍不是個好的主鍵。 主鍵特性: 儲存獨特值 不能為空 穩定(不會改變) 盡可能簡單(整數型態、非浮點數、字元，單一欄位而非多欄位) 常見手段，使用自動產生的無意義值作為主鍵，名稱由RMDBS軟體所訂， 例如IBM DB2、MSSQL、 Oracel 12c 的 IDENTITY，MS Access 的 AutoNumber、 MySQL 的 AUTO_INCREMENT，PostgreSQL 的 serial。 (搭配 redis 產生 sequence) 資料參考完整性(Referential Integrity) RI 在 RMDBS 中是一個非常重要的觀念。 強制 RI 表示具有非空的外來鍵的子資料表中的每個列必須在父資料表中有相對應的紀錄。 消除重複儲存資料 減少不一致資料與處理資雲 消除重複群組 欄代價高，列代價低 每個欄位只存一個屬性 通常不會儲存計算出的資料 定義FK保護參考完整性 確保資料表關係的合理 不是任何關聯都應該被建立 3NF以上正規化 對資訊倉儲使用反正規化 大量讀取少量寫入時，帶有全部所需資料的表索引應用更有效率，可以加快速度。

http://xyz.cinc.biz/2013/05/mysql-transaction.html

http://www.1keydata.com/tw/sql/sqlselect.html http://note.drx.tw/2012/12/mysql-syntax.html

[eric_tu@localhost ~]$ mysql ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (13) [eric_tu@localhost ~]$ mysql -u eric_tu -h 127.0.0.1 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.7.18 MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> show databases; mysql> use Msgboard;

http://www.1keydata.com/tw/sql/sqlorderby.html ORDER BY: SELECT "欄位名" FROM "表格名" [WHERE "條件"] ORDER BY "欄位名" [ASC, DESC]; [] 代表 WHERE 子句不是一定需要的。 不過，如果 WHERE 子句存在的話，它是在 ORDER BY 子句之前。 ASC 代表結果會以由小往大的順序列出，而 DESC 代表結果會以由大往小的順序列出。 如果兩者皆沒有被寫出的話，那我們就會用 ASC。

1 # For advice on how to change settings please see 2 # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html 3 4 [mysqld] 5 sql_mode = "STRICT_TRANS_TABLES" 6 7 # 8 # Remove leading # and set to the amount of RAM for the most important data 9 # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. 10 # innodb_buffer_pool_size = 128M 11 # 12 # Remove leading # to turn on a very important data integrity option: logging 13 # changes to the binary log between backups. 14 # log_bin 15 # 16 # Remove leading # to set options mainly useful for reporting servers. 17 # The server defaults are faster for transactions and fast SELECTs. 18 # Adjust sizes as needed, experiment to find the optimal values. 19 # join_buffer_size = 128M 20 # sort_buffer_size = 2M 21 # read_rnd...

MySQL MYSQL SETTING [root@localhost conf.d]# sudo service mysqld start Redirecting to /bin/systemctl start mysqld.service [root@localhost conf.d]# sudo grep 'temporary password' /var/log/mysqld.log 2017-05-03T05:55:14.109793Z 1 [Note] A temporary password is generated for root@localhost: TaiOnb;3!B3k [root@localhost conf.d]# [root@localhost conf.d]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 21 Server version: 5.7.18 Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Eric_tu168'; mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '...

版本不符合 http://stackoverflow.com/questions/10759334/headers-and-client-library-minor-version-mismatch 先將PHP的資訊列出來 php -i > log.txt vim log.txt /API 可以發現 Client API library version => 5.6.31 Client API header version => 5.5.50-MariaDB [root@localhost nginx]# mysql -V mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using EditLine wrapper [root@localhost nginx]# php -v PHP 5.4.16 (cli) (built: Nov 6 2016 00:29:02) yum list|grep -i mysql php-mysqlnd.x86_64 5.4.16-42.el7 base 更新PHP版本即可解決，不過更新完可能還會遇到各種問題...。 更新PHP 5.4 到 5.6 後各種設定跑掉 確定一下php-fpm 各種設定 nginx 應該不用動 不過要重啟 權限要設定好 更改 conf.d/default 中的 user 從apache 到 nginx

``` [root@localhost ~]# mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) ``` http://mustgeorge.blogspot.tw/2011/11/mysql-error-1045-28000-using-password.html https://dev.mysql.com/doc/refman/5.7/en/adding-users.html # creat user 先使用root登入 ``` [root@localhost ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 5.7.18 MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE USER 'eric _tu'@'localhost' IDENTIFIED BY 'Eric_ tu168'; Query OK, 0 rows affected (0.02 sec) ``` ``` ...