一,数据定义
1,创建新数据库:CREATE DATABASE database_name
2,创建新表:CREATE TABLE table_name (column_name datatype,column_name datatype,...)
3,修改数据表:
添加列:ALTER TABLE table_name ADD column_name datatype
删除列:ALTER TABLE table_name DROP COLUMN column_name
修改列:ALTER TABLE table_name ALTER COLUMN column_name datatype
4,删除表:DROP TABLE table_name
二,数据操作
选取数据:SELECT 列名称 FROM 表名称
SELECT * FROM 表名称
过滤重复:SELECT DISTINCT 列名称 FROM 表名称
指定条件:SELECT 列名称 FROM 表名称 WHERE 列 运算符 值
多项条件:SELECT 列名称 FROM 表名称 WHERE 列 运算符 值 AND(OR) 列 运算符 值
排列顺序:SELECT 列名称 FROM 表名称 ORDER BY 列 1,列 2 DESC(列 1 升序然后列 2 倒叙)
插入数据:INSERT INTO 表名称 VALUES (值 1, 值 2,...)
INSERT INTO table_name (列 1, 列 2,...) VALUES (值 1, 值 2,....)
更新数据: UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
删除数据:DELETE FROM 表名称 WHERE 列名称 = 值
三,常用 SQL 语句范例
选取:SELECT LastName,FirstName FROM Persons
去重:SELECT DISTINCT Company FROM Orders
条件:SELECT * FROM Persons WHERE City='Beijing'
AND 与:SELECT * FROM Persons WHERE FirstName='Thomas' AND LastName='Carter'
OR 或:SELECT * FROM Persons WHERE firstname='Thomas' OR lastname='Carter'
升序:SELECT Company, OrderNumber FROM Orders ORDER BY Company
降序:SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC
插入 1:INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')
插入 2:INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees')
更新:UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'
删除:DELETE FROM Person WHERE LastName = 'Wilson'
限制条数:SELECT * FROM Persons LIMIT 5
字符检索:SELECT * FROM Persons WHERE City LIKE '%BJ%'
通配符:%:替代一个或多个字符
_:仅替代一个字符;
[ABC]:ABC 三者之一;
[^ABC]:除 ABC 以外
多选条件:SELECT * FROM Persons WHERE LastName IN ('Adams','Carter')
两者之间:SELECT * FROM Persons WHERE LastName BETWEEN 'Adams' AND 'Carter'
指定别名:SELECT po.OrderID, p.* FROM Persons AS p, Product AS po WHERE p.LastName='Adams' AND p.FirstName='John'
联合查询:SELECT Persons.*, Orders.OrderNo FROM Persons, Orders WHERE Persons.Id_P = Orders.Id_P
JOIN 联合:SELECT Persons.*, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.Id_P = Orders.Id_P
合并结果:SELECT E_Name FROM Employees_China UNION SELECT E_Name FROM Employees_USA
来源: http://www.bubuko.com/infodetail-2463450.html