MySQL语法summary

重温一下MySQL的语法。

http://www.w3schools.com/sql/sql_join_inner.asp

A relational database consists of tables, which consists of rows and columns.
Primary Key, Unique Key (both primary and unique keys uniquely identifies each row), Foreign Key (connected to primary key or unique key in another table).

Query, Action Query, ResultSet.

SQL words are uppercase, the name I supply are lowercase.

1. SELECT

SELECT column1, column2
FROM table1
WHERE listPrice < 500 
AND category > 10
(OR orderID < 5)
ORDER BY listPrice ASC/DESC

2. JOIN

SELECT column_name
FROM table1
        INNER JOIN table2
        ON table1.column1 = table2.column2
ORDER BY listPrice DESC

INNER JOIN: 两个set的交集，挑出仅相等的index。
LEFT OUTER JOIN，是挑出所有左table，而右table挑出仅相等的。
RIGHT OUTER JOIN，是相反，挑出所有右table，左table挑出仅相等。
FULL JOIN，挑出左右两边所有的.

注意AND OR IN的用法

3. INSERT

INSERT INTO table1(column1, column2)
VALUES (value1, value2)

注意，INSERT是插入一行，不是一列

4. UPDATE

UPDATE table1
SET productName = 'abc'
WHERE productCode = 456
OR productID = 2

5. DELETE

DELETE FROM table1
WHERE productID = 1
AND City IN ('Paris','London')

INSERT/UPDATE/DELETE are action query. It gets return the number of rows being changed.

6. CREATE, USE, DROP database

CREATE DATABASE IF NOT EXISTS database1;
USE database1;
DROP IF EXISTS database1;

7. CREATE TABLE

CREATE TABLE IF NOT EXISTS table1
(
  customerID INT,  NOT NULL, NONE, PRIMARY KEY, AUTO_INCREMENT,
  firstName VARCHAR(60), NOT NULL, NONE,
  lastName VARCHAR(60), NOT NULL, NONE,
  emailAddress TEXT, NOT NULL, UNIQUE,
);

PRIMARY KEY (column1, column2) -> to create two column primary key

DROP TABLE IF EXISTS table1;

8. CREATE INDEX

CREATE UNIQUE INDEX column1
on table1 (column1);

最后，列举一些data type：

INT - 4-bytes, INT_MIN to INT_MAX
TINYINT - 1 byte, -127 to 128
VARCHAR - max size of 65535, fix length at initialization
CHAR - max size of 255, fix length at initialization
TEXT - variable length up to max size of 65535
DATE, 
TIME,
DATETIME,