常用sql
一、数据定义
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