MySQL - Cost-effective in RDBMS

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 record

Data Control Language (DCL)
A family of syntax used to access and control the data stored in a database
COMMIT: To made the change in database permanently
GRANT: to allow what user to perform specific task
REVOKE: to deny the permission of previous granted
ROLLBACK: To complete successfully or otherwise be rolled back

Create, modify and Delete
Command Description
CREATE DATABASE <database_name>; Create a new database
RENAME DATABASE <old_db_name> TO <new_db_name>; Update the name of database
DROP DATABASE <database_name>; Drop the existing database
CREATE TABLE <database_name> (<column_name1> <format> NOT NULL PRIMARY KEY AUTO_INCREMENT, <column_name2> <format>, <column_name3> <format>); Create a new table and columns with data type in the database
ALTER TABLE <table_name> ADD <column_name> <data_type>; Add a column with specific data type in the tables
ALTER TABLE <table_name> MODIFY <column_name> <data_type>; Modify the data type of the column
ALTER TABLE <table_name> DROP <column_name>; Delete a column with specific data type in the tables
ALTER TABLE <table_name> CHANGE <old_column_name> <new_column_name>  <data_type>; Update both column name and data type
INSERT INTO `<table_name>` (`<column_name1>`, `<column_name2>`, `<column_name3>`) VALUES (NULL, “XXX", “YYY”,'ZZZ'); Insert a row with specific values in the tables

Lookup
Command Description
SHOW DATABASES; Show all databases
USE <database_name>; Use one of the databases
SHOW TABLES; Show all tables in the database
DESCRIBE <database_name>; Show the schema in the tables
SELECT <column_name1> <column_name2> FROM <table_name> WHERE <conditions> ORDER BY <column_name> Under the conditions, specified column in tables is order by a column in ascending order (default)
SELECT <column_name1> <column_name2> FROM <table_name> WHERE <conditions> GROUP BY <column_name> DESC Under the conditions, specified column in tables is summarized by a column in descending order (default)


Installing MySQL / MySQL Workbench
You could install the MySQL so that your own computer becomes a database server to store and manage the data.


Window
1. Download the installer from official website: http://dev.mysql.com/downloads/windows/installer/
2. Follow the instruction to install
3. Input your root password and finish the installation
4. Start the MySQL in CMD:
> cd "C:\Program Files\MySQL\MySQL Server 5.6\bin"
> mysql -u root -p
> <password>

Mac
1. Download the installer from official website: http://dev.mysql.com/downloads/mysql/
2. Update the PATH environment variables in terminals
> export PATH=/usr/local/mysql/bin:$PATH
3. Open "System Preferences" and Click "Start MySQL Server"
4. Launch MySQL in terminals:
> mysql -u root -p
> <password> (Leave in blank and press enter if you have not set the password)

Linux
1. Install MySQL server in terminals:
> sudo apt-get install mysql-server
2. Input your own root password twice
3. Run the MySQL in terminals:
> mysql -u root -p
> <password> 

Then, you have become a database administrator!


Comments

Popular posts from this blog

Boosting vs Bagging? 別再胡亂用了!

機器學習之陷阱 - Imbalance Class Classification

Excel VBA - 自動生成分析報告