SQL语法和常用功能汇总

SQL语法和常用功能

1. 基础概念

数据库：保存有组织的数据的容器（通常是一个文件或一组文件）。
表：某种特定类型数据的结构化清单。
模式：关于数据库和表的布局及特性的信息。
列：表中的一个字段，所有表都是由一个或多个列组成的。
数据类型：所允许的数据的类型，每个表列都有相应的数据类型，它限制该列中存储的数据。
行：表中的一个记录。
主键：一列（或一组列），其值能够唯一标识表中的每一行。
SQL：Structured Query Language（结构化查询语言）。

2. 检索数据（SELECT FROM）

• 检索一列：

  SELECT 列名 FROM 表名

• 检索多列：

  SELECT 列1， 列2… FROM 表名

• 检索所有列

  SELECT * FROM 表名

• 检索不同的值

  SELECT DISTINCT 列名 FROM 表名

• 限制检索结果

  SELECT 列名 FROM 表名 LIMIT 返回的行数 OFFSET 起始行

3. 排序检索数据 (ORDER BY)

• 排序数据

  SELECT 列名 FROM 表名 ORDER BY 列名

• 按多个列排序

  SELECT 列名 FROM 表名 ORDER BY 列1， 列2…

• 指定排序方向

  SELECT 列名 FROM 表名 ORDER BY 列名 DESC(降序)

4. 过滤数据 (WHERE)

• 过滤条件

  SELECT 列名 FROM 表名 WHERE 过滤条件

• WHERE 子句操作符

5. 高级数据过滤 (WHERE + AND/OR/IN/NOT)

• 按照多个条件进行过滤

  SELECT 列名 FROM 表名 WHERE 条件1 AND 条件2

• 选择满足某个条件的值（条件1满足不再执行条件2）

  SELECT 列名 FROM 表名 WHERE 条件1 OR 条件2

• 指定条件范围，范围中的值都可以匹配

  SELECT 列名 FROM 表名 WHERE 列值 IN(值1， 值2…)

• 否定某条件

  SELECT 列名 FROM 表名 WHERE NOT 过滤条件

6. 使用通配符 (LIKE)

• %任意字符出现任意次

  SELECT 列名 FROM 表名 WHERE 列值 LIKE ‘abc%’

• 下划线 _ 任意字符出现1次

  SELECT 列名 FROM 表名 WHERE 列值 LIKE ‘abc_’

7.计算字段

• 拼接字段

  SELECT Concat(列名1, xx, 列名2，xx) FROM 表名

• 使用别名

  SELECT 列名 AS 别名 FROM 表名

• 执行列的算数计算

  SELECT 列名，…，列值计算式 FROM 表名 WHERE 条件

8. 使用数据处理函数

• 将返回值变为大写

  SELECT UPPER(列名) FROM 表名

• 发音近似查找

  SELECT 列名 FROM 表名 WHERE SOUNDEX(列值) = SOUNDEX(值)

• 从日期中提取年份

  SELECT 列名 FROM 表名 WHERE YEAR(日期列) = 年份

• 数值处理

  ABS()、COS()、 EXP()、 PI()、 SIN()、 SQRT()、 TAN()

9. 汇总数据

• 返回列的平均值

  SELECT AVG(列名) FROM 表名

• 返回行的数目

  SELECT COUNT(*) FROM 表名

• 返回指定列中的最大值

  SELECT MAX(列名) FROM 表名

• 返回指定列中的最小值

  SELECT MIN(列名) FROM 表名

• 返回指定列值的和

  SELECT SUM(列名) FROM 表名

• 聚集唯一值

  SELECT AVG(DISTINCT 列名) FROM 表名

• 组合聚集函数

  SELECT 函数1(列1), 函数2(列2)…

10. 分组函数 (GROUP BY)

• 根据某列的值分组

  SELECT 列名 FROM 表名 GROUP BY 列名

• 过滤分组

  SELECT 列名 FROM 表名 GROUP BY 列名 HAVING 过滤条件

• 分组后排序

  SELECT 列名 FROM 表名 GROUP BY 列名 HAVING 过滤条件 ORDER BY 列名

• SELECT子句中关键字出现顺序

  SELECT > FROM > WHERE > GROUP BY > HAVING > ORDER BY

11. 使用子查询

• 使用子查询进行过滤

  SELECT 列名 FROM 表名 WHERE 列值 IN (SELECT 列名 FROM 表名…)

• 使用子查询作为计算字段

  SELECT 列名1，列名2，(SELECT 列名 FROM 表名) FROM 表名 WHERE 过滤条件

12. 联结表

• 创建联结

  SELECT 列名 FROM 表名1， 表名2 WHERE 表1.列1 = 表2.列2

• 内联结

  SELECT 列名 FROM 表1 INNER JOIN 表2 ON 表1.列1 = 表2.列2

• 联结多个表

  SELECT 列名 FROM 表1，表2，表3 WHERE 表1.列1 = 表2.列2 AND 表2.列3 = 表3.列3

13. 创建高级联结

• 左右联结

  SELECT 列名 FROM 表1 LEFT/RIGHT OUTER JOIN 表2 ON 表1.列1 = 表2.列1

• 带聚集函数的联结

  SELECT 表1.列1，函数1(表x.列x) FROM 表 1 LEFT/RIGHT OUTER JOIN 表2 ON 表1.列1 = 表2.列1

14. 组合查询

• 创建组合查询

  SELECT 列名 FROM 表名 WHERE 过滤条件 UNION SELECT 列名 FROM 表名 WHERE 过滤条件

• 组合查询包含重复的行

  SELECT 列名 FROM 表名 WHERE 过滤条件 UNION ALL SELECT 列名 FROM 表名 WHERE 过滤条件

15. 插入数据

• 插入完整的行

  INSERT INTO 表名 VALUES (‘列1’, ‘列2’…按顺序的全部列)

• 更安全的插入方法

  INSERT INTO 表名 (‘列1’, ‘列2’…可以不按顺序的非空列) VALUES (‘列1’, ‘列2’…和前面顺序相同的非空列)

• 插入检索出的数据

  INSERT INTO 表名 (‘列1’, ‘列2’…可以不按顺序的非空列) SELECT ‘列1’, ‘列2’…和前面顺序相同的非空列 FROM 表名

• 从一个表复制到另一个表

  CREATE TABLE 表2 AS SELECT * FROM 表1

16. 更新和删除

• 更新数据

  UPDATE 表名 SET 列名 = ‘value’ WHERE 过滤条件

• 删除某列

  UPDATE 表名 SET 列名 = ‘NULL’

• 删除某行

  DELETE FROM 表名 WHERE 列名 = ‘value’

17. 表操作

• 创建表

  CREATE TABLE 表名（列名1 数据类型 是否为空，… ）

• 创建表的列非空

  CREATE TABLE 表名（列名1 数据类型 NOT NULL，… ）

• 指定表的默认值

  CREATE TABLE 表名（列名1 数据类型 NOT NULL DEFAULT 默认值，… ）

• 添加列

  ALTER TABLE 表名 ADD 列名 数据类型

• 删除列

  ALTER TABLE 表名 DROP COLUMN 列名

• 删除表

  DROP TABLE 表名

18. 使用视图

• 创建视图

  CREATE VIEW 视图名 AS SELECT 列1, 列2 FROM 表1, 表2 WHERE 过滤条件

• 从视图中检索数据

  SELECT * FROM 视图名

19.使用存储过程（相当于函数）

• 创建存储过程

  CREATE PROCEDURE 过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 …]
  BEGIN
  ……
  END

• 调用存储过程

  SET @参数=初值;
  CALL 存储过程名(@参数);
  SELECT @参数;

20. 管理事务处理

• BEGIN或START TRANSACTION；显式地开启一个事务；

• COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改成为永久性的；

• ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

• SAVEPOINT identifier；SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT；

• RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

• ROLLBACK TO identifier；把事务回滚到标记点；

• SET TRANSACTION；用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ
  COMMITTED、REPEATABLE READ和SERIALIZABLE。

21. 使用游标

• 定义游标

  DECLARE cursor_name CURSOR FOR select_statement

• 开启游标

  OPEN cursor_name

• 使用游标

  FETCH cursor_name INTO var_name [, var_name] …将当前行放置到声明的变量中

• 关闭游标

  CLOSE cursor_name

• 删除游标

  DECLEAR cursor_name

22. 高级特性

• 创建表时定义主键

  CREATE TABLE 表名(列名 数据类型 NOT NULL PRIMARY KEY, ……)

• 添加列时定义主键

  ALTER TABLE 表名 ADD CONSTRAINT PRIMARY KEY(列名)

• 创建表时定义外键

  CREATE TABLE 表名(列名 数据类型 NOT NULL PRIMARY KEY, ……列名 数据类型 NOT NULL REFERENCES 表2(列名))

• 添加列时定义主键

  ALTER TABLE 表名 ADD CONSTRAINT FOREIGN KEY(列名) REFERENCES 表2(列名)

• 检查约束

  CREATE TABLE 表名(列名 数据类型 NOT NULL PRIMARY KEY, ……列名 数据类型 NOT NULL CHECK (quantity > 0))

• 创建索引

  CREATE INDEX indexName ON mytable(username(length))

• 删除索引

  DROP INDEX [indexName] ON mytable

• 创建触发器

  CREATE TRIGGER trigger_name
  trigger_time
  trigger_event ON tbl_name
  FOR EACH ROW
  trigger_stmt
  trigger_name：标识触发器名称，用户自行指定；
  trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；
  trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；
  tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；
  trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句。