CREATE DATABASE

CREATE DATABASE my_db

CREATE TABLE

CREATE TABLE persons
(
    lastName varchar(255),
    city varchar (255)
)

INSERT INTO

INSERT INTO persons VALUES ('bo','beijing')
INSERT INTO persons (city) VALUES ('shanghai')

UPDATE

UPDATE persons SET city = 'guangzhou' WHERE lastName = 'bo'

DELETE

DELETE FROM persons WHERE lastName = 'bo'
DELETE * FROM persons

JOIN

SELECT persons.lastName , orders.orderNo FEOM 
persons INSER JOIN orders ON persons.id_p = orders.id_p
ORDER BY persons.lastName

BETWEEN

SELECT * FROME persons WHERE lastName BETWEEN 'adams' AND 'carter'
SELECT * FROME persons WHERE lastName NOT BETWEEN 'adams' AND 'carter'

LIKE

SELECT * FROME persons WHERE city LIKE 'N%'
SELECT * FROME persons WHERE city LIKE '%g'
SELECT * FROME persons WHERE city LIKE '%lon%'

TOP LIMIT

SELECT * FROM persons LIMIT 5
SELECT TOP 2 * FROM persons

AND OR

SELECT *FROM person WHERE lastName = 'li' AND city = 'tianjin'
SELECT *FROM person WHERE lastName = 'li' OR city = 'tianjin'
SELECT *FROM person WHERE (lastName = 'bo' OR city = 'tianjin') AND firstName = 'li'

DISTINCT

去除重復(fù)項(xiàng)

SELECT DISTINCT firtName FROM persons 

索引

索引是對(duì)數(shù)據(jù)庫(kù)表中一列或多列的值進(jìn)行排序的一種結(jié)構(gòu)，使用索引可以快速訪問(wèn)數(shù)據(jù)庫(kù)表中的特定信息。是為了提高表的搜索效率而對(duì)某些字段中的值建立的目錄。
為表設(shè)置索引要付出代價(jià)：1.增加了數(shù)據(jù)庫(kù)的存儲(chǔ)空間，2.在插入和修改數(shù)據(jù)時(shí)要花費(fèi)較多的時(shí)間，因?yàn)樗饕惨S之變動(dòng)。

CREATE INDEX mytable_categoryid ON mytable(category_id);