常用的SQL语句

这里只记录了常用的一些SQL语法。

1、数据查询

//基础查询
SELECT column1, column2 FROM table_name;
//查询所有列
SELECT * FROM table_name;
//带条件的查询
SELECT column1, column2 FROM table_name WHERE condition;
//去重查询
SELECT DISTINCT column_name FROM table_name;
//排序查询
SELECT column_name FROM table_name ORDER BY column_name ASC|DESC;
//聚合函数
SELECT COUNT(column_name) FROM table_name;
//分组查询
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;
//多表联合查询
SELECT Orders.OrderID, Customers.CustomerName  
FROM Orders  
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

2、数据插入

//插入新记录
INSERT INTO table_name (column1, column2, column3, ...)  
VALUES (value1, value2, value3, ...);

3、数据更新

//更新表中记录
UPDATE table_name  
SET column1 = value1, column2 = value2, ...  
WHERE condition;

4、数据删除

//删除表中的记录
DELETE FROM table_name WHERE condition;

5、聚合函数

//返回制定匹配条件的行数
SELECT COUNT(column_name) FROM table_name WHERE condition;
//最大，最小，总和，平均值
SELECT MAX(column_name) FROM table_name;  
SELECT MIN(column_name) FROM table_name;  
SELECT SUM(column_name) FROM table_name;  
SELECT AVG(column_name) FROM table_name;

6、连接

//返回两个表中匹配的记录
SELECT Orders.OrderID, Customers.CustomerName  
FROM Orders  
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
//返回左表中的所有记录和右表中的匹配的记录
SELECT Orders.OrderID, Customers.CustomerName  
FROM Orders  
LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;