常用SQL语句操作数据库

在SQL中，主要的操作可以被分为以下几种：

	DDL(Data Definition Language): 用于定义、更改、删除数据库和数据库对象。
	DML(Data Manipulation Language): 用于增加、修改、删除和查询数据库记录。
	DCL(Data Control Language): 用于定义对数据库的访问权限和安全级别，及创建用户。

以下是一些基本的SQL语句例子:
创建数据库: CREATE DATABASE dbname;
删除数据库: DROP DATABASE dbname;
使用数据库: USE dbname;
查看数据库: SHOW databases;
创建表格: CREATE TABLE table_name (column1 datatype, column2 datatype);
删除表格: DROP TABLE table_name;
为现有表添加列: ALTER TABLE table_name ADD column datatype;
删除现有表中的列: ALTER TABLE table_name DROP COLUMN column_name;
重命名表: ALTER TABLE table_name RENAME TO new_table_name;
向表中插入数据: INSERT INTO table_name (column1, column2) VALUES (value1, value2);
从表中查询数据: SELECT column1, column2 FROM table_name;
从表中查询所有数据: SELECT * FROM table_name;
更新表中的数据: UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
从表中删除数据: DELETE FROM table_name WHERE condition;
查询表中唯一不同的值: SELECT DISTINCT column_name FROM table_name;
计数表中的记录: SELECT COUNT(column_name) FROM table_name;
对查询结果进行排序: SELECT column1, column2 FROM table_name ORDER BY column_name ASC|DESC;
设置查询条件: SELECT column1, column2 FROM table_name WHERE condition;
对查询结果进行分组: SELECT column1, COUNT(column2) FROM table_name GROUP BY column_name;
将多个条件组合在一起: SELECT column1, column2 FROM table_name WHERE condition1 AND|OR condition2;
对查询的结果进行限制: SELECT column_name(s) FROM table_name LIMIT number;
联接多个表：SELECT ORDER_ID, VENDOR_CODE, ORDER_AMOUNT FROM ORDERS INNER JOIN VENDORS ON ORDERS.VENDOR_ID = VENDORS.VENDOR_ID;
查询表的结构：DESC table_name;
更改表的列数据类型：ALTER TABLE table_name MODIFY COLUMN column_name column_type;
对多表查询的结果进行排序: SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders, Customers WHERE Orders.CustomerID = Customers.CustomerID ORDER BY Orders.OrderDate;
创建索引：CREATE INDEX index_name ON table_name (column1, column2, …);
删除索引: DROP INDEX index_name;
创建视图: CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition;
删除视图: DROP VIEW view_name;
删除表中的所有行: TRUNCATE TABLE table_name;
创建存储过程: CREATE PROCEDURE procedure_name AS sql_statement GO;
删除存储过程: DROP PROCEDURE procedure_name;
调用存储过程: EXEC procedure_name;
创建触发器: CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW BEGIN sql to be executed; END;
删除触发器: DROP TRIGGER table_name.trigger_name;
创建临时表: CREATE TEMPORARY TABLE table_name (column_name column_type);
复制表（结构+数据）: CREATE TABLE new_table AS SELECT * FROM existing_table;
复制表（只复制结构）: CREATE TABLE new_table AS SELECT * FROM existing_table WHERE 1=0;
取值非空的第一条记录: SELECT column_name(s) FROM table_name WHERE column_name IS NOT NULL;
查询某列的最大值：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;
更改默认排序规则：ALTER DATABASE dbname COLLATE collation_name;
更改表的默认排序规则：ALTER TABLE table_name COLLATE collation_name;
更改列的默认排序规则：ALTER TABLE table_name ALTER COLUMN column_name COLLATE collation_name;
创建函数: CREATE FUNCTION function_name ([param[, …]) RETURNS return_datatype language_name AS function_body;
删除函数: DROP FUNCTION function_name;
使用IF ELSE语句: SELECT IF(column_name = condition, ‘True’, ‘False’) AS AliasName FROM table_name;
使用CASE语句: SELECT column_name, CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE result3 END AS AliasName FROM table_name;