数据库基础

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

去除重复项

SELECT DISTINCT firtName FROM persons 

索引

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可以快速访问数据库表中的特定信息。是为了提高表的搜索效率而对某些字段中的值建立的目录。
为表设置索引要付出代价：1.增加了数据库的存储空间，2.在插入和修改数据时要花费较多的时间，因为索引也要随之变动。

CREATE INDEX mytable_categoryid ON mytable(category_id);