MySQL必知必会

SQL Tutorial

Select

查询

SELECT column1, column2, ...
FROM table_name;

SELECT * FROM table_name;

Select Distinct

选择不同的项

SELECT DISTINCT column1, column2, ...
FROM table_name;

where

范围选择

SELECT column1, column2, ...
FROM table_name
WHERE condition;

symbol	describe
=	Equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
<>	Not equal. Note: In some versions of SQL this operator may be written as !=
BETWEEN	Between a certain range
LIKE	Search for a pattern
IN	To specify multiple possible values for a column

And、Or、Not

与或非选择

//与
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;
//或
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;
//非
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

Order By

排序，默认升序，DESC：倒序

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

Insert Into

插入数据

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

Null Value

空值

//寻找null value
SELECT column_names
FROM table_name
WHERE column_name IS NULL;
//寻找不为空
SELECT column_names
FROM table_name
WHERE column_name IS NULL;

Update

更新

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Delete

删除

DELETE FROM table_name WHERE condition;

Select Top

选择指定数量的结果，不同数据库略有差异

• SQL Server/MS Access : number
• MySQL: Limit
• Oracle：Rownum

--SQL Server / MS Access Syntax:
SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;

--MySQL Syntax:
SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;

--Oracle Syntax:
SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

Min和Max函数

Min返回给定字段最小的一项数据，Max则相反

SELECT MIN(column_name