Data Jungler

As mentioned in previous articles, MySQL is the programming language to manage and control the data in relational database. Nowadays, there is graphical query builder or popular web framework like Django so that you no longer need to code with SQL statement. Nonetheless, I still recommend to understand the principle of relational database and SQL especially who have less knowledge of database system design. There are several relational databases including MySQL, SQLite, PostgreSQL. In this tutorial, we will take an advantage of MySQL to administrate our own database. Imagine that you are the shop owner and start up a business, however,  resources are lacked to hire a technical to help you to construct the database structure. What could you do? Ask help? No, just do it by yourself~! Before building the operational database, we better to know the data flow in the process under this scenario. If you have never heard relational database model, you are recommended to understand more...

SQL, Structured Query Language, is the programming language to manage the data in relational database system (RDBMS). MySQL is also widely used in RDBMS and plays an significant role in LAMP, the software bundle which consists of four open-source software: Linux, Apache, MySQL, PHP. To beginners, SQL & MySQL is relatively monotonous and easier to grasp than other programming language. As a result, developers usually do not pay much attention to it unless some error occurs in the software.  Introduction Data Definition Language (DDL) A family of syntax for defining data structures, e.g. database schema CREATE: To make a new database, table, index or view ALTER: To modify the objects in database DROP: To remove an existing database, table, index, or view Data Manipulation Language (DML) A family of syntax elements to perform read-only queries of data SELECT: To query the record INSERT: To insert a new record UPDATE: To update the record DELETE: To remove the recor...

筆者初學資料庫的時候，第一個接觸的數據庫語言正正就是SQL(Structured Query Language)，用作跟 關聯式數據庫(Relational Database) 溝通的程式語言，一開始接觸覺得它在眾多程式語言中較容易上手，因為那些Keywords Syntax比較直覺和容易掌握，到現在也發覺這貨根本就是易學難精，尤其是要擷取大量數據又要保持良好的性能。而MySQL是其中一個Open Source的關聯式數據庫系統，效能高、成本低、可靠性好成為了其流行的原因。它也是LA M P（開發Apps/Web服務）的其中一位成員，負責為系統的後台儲存應有的數據。 簡介 數據定義語言 (DDL) 用於定義數據結構的語法系列，例如：database schema CREATE: 建立新的Database, Table, Index or View ALTER: 修改已有的Database設計 DROP: 移除現有的Database, Table, Index or View 數據操作語言 (DML) 讀取和查詢的語法系列 SELECT: 查詢記錄 INSERT: 插入新記錄 UPDATE: 更新記錄 DELETE: 刪除記錄 數據控制語言 (DCL) 控制數據庫中存儲的數據的語法系列 COMMIT: 永久性地更改數據庫 GRANT: 允許用戶執行特定任務 REVOKE: 拒絕先前的批准 ROLLBACK: 若未完成回到最初的起點 Create, modify and Delete 指令 描述 CREATE DATABASE <database_name>; 建立新的Database RENAME DATABASE <old_db_name> TO <new_db_name>; 更新Database的名稱 DROP DATABASE <database_name>; 刪除現有的Database CREATE TABLE <database_name> (<column_name1> <format> NOT NULL PRIMARY KEY...