SQL总结

一、MySQL基础

1、数据库（database）： 保存有组织的数据的容器（通常是一个文件或一组文件）。

2、表（table） ：某种特定类型数据的结构化清单。

3、列（column）：表中的一个字段。所有表都是由一个或多个列组成的。

4、行（row） 表中的一个记录。

5、主键（primary key）一一列（或一组列），其值能够唯一区分表中每个行。

二、创建表

CREATE TABLE mytable (
  # int 类型，不为空，自增
  id INT NOT NULL AUTO_INCREMENT,
  # int 类型，不可为空，默认值为 1，不为空
  col1 INT NOT NULL DEFAULT 1,
  # 变长字符串类型，最长为 45 个字符，可以为空
  col2 VARCHAR(45) NULL,
  # 日期类型，可为空
  col3 DATE NULL,
  # 设置主键为 id
  PRIMARY KEY (`id`));

三、修改表

添加列

ALTER TABLE mytable ADD col CHAR(20);

删除列

ALTER TABLE mytable DROP COLUMN col;

删除表

DROP TABLE mytable;

四、插入

INSERT INTO mytable(col1, col2) VALUES(val1, val2);

五、更新

UPDATE mytable SET col = val WHERE id = 1;

六、删除

DELETE FROM mytable WHERE id = 1;

七、查询

相同值只会出现一次。它作用于所有列，也就是说所有列的值都相同才算相同。

SELECT DISTINCT col1, col2 FROM mytable;

LImit

限制返回的行数。可以有两个参数，第一个参数为起始行，从 0 开始；第二个参数为返回的总行数。

八、排序

• ASC ：升序（默认）
• DESC ：降序

可以按多个列进行排序，并且为每个列指定不同的排序方式：

SELECT * FROM mytable ORDER BY col1 DESC, col2 ASC;

九、通配符

通配符也是用在过滤语句中，但它只能用于文本字段。

• % 匹配 >=0 个任意字符；

• _ 匹配 ==1 个任意字符；

• [ ] 可以匹配集合内的字符，例如 [ab] 将匹配字符 a 或者 b。用脱字符 ^ 可以对其进行否定，也就是不匹配集合内的字符。

  使用 Like 来进行通配符匹配。

SELECT * FROM mytable WHERE col LIKE '[^AB]%'; -- 不以 A 和 B 开头的任意文本

注：通配符的效率很低，因此尽量避免使用通配符。

十、函数

AVG()    返回某列的平均值

COUNT()  返回某列的行数

MAX()  返回某列的最大值

MIN()  返回某列的最小值

SUM()  返回某列值之和

注： AVG() 会忽略 NULL 行。

       使用 DISTINCT 可以汇总不同的值。

SELECT AVG(DISTINCT col1) AS avg_col FROM mytable;

十一、分组

把具有相同的数据值的行放在同一组中。

可以对同一分组数据使用汇总函数进行处理，例如求分组数据的平均值等。

指定的分组字段除了能按该字段进行分组，也会自动按该字段进行排序。

SELECT col, COUNT(*) AS num FROM mytable GROUP BY col;

WHERE 过滤行，HAVING 过滤分组，行过滤应当先于分组过滤。

SELECT col, COUNT(*) AS num FROM mytable WHERE col > 2 GROUP BY col HAVING num >= 2;

分组规定：

• GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 子句之前；
• 除了汇总字段外，SELECT 语句中的每一字段都必须在 GROUP BY 子句中给出；
• NULL 的行会单独分为一组；
• 大多数 SQL 实现不支持 GROUP BY 列具有可变长度的数据类型。

十二、子查询

子查询中只能返回一个字段的数据。

可以将子查询的结果作为 WHRER 语句的过滤条件：

SELECT * FROM mytable1 WHERE col1 IN (SELECT col2 FROM mytable2);

十三、连接

连接用于连接多个表，使用 JOIN 关键字，并且条件语句使用 ON 而不是 WHERE。

连接可以替换子查询，并且比子查询的效率一般会更快。

可以用 AS 给列名、计算字段和表名取别名，给表名取别名是为了简化 SQL 语句以及连接相同表。

内连接

内连接又称等值连接，使用 INNER JOIN 关键字。

SELECT A.value, B.value
FROM tablea AS A INNER JOIN tableb AS B
ON A.key = B.key;

可以不明确使用 INNER JOIN，而使用普通查询并在 WHERE 中将两个表中要连接的列用等值方法连接起来。

SELECT A.value, B.value
FROM tablea AS A, tableb AS B
WHERE A.key = B.key;

自连接

自连接可以看成内连接的一种，只是连接的表是自身而已。

一张员工表，包含员工姓名和员工所属部门，要找出与 Jim 处在同一部门的所有员工姓名。

子查询版本

SELECT name
FROM employee
WHERE department = (
      SELECT department
      FROM employee
      WHERE name = "Jim");

自连接查询版本

SELECT e1.name
FROM employee AS e1 INNER JOIN employee AS e2
ON e1.department = e2.department
      AND e2.name = "Jim";

自然连接是把同名列通过等值测试连接起来的，同名列可以有多个。

内连接和自然连接的区别：内连接提供连接的列，而自然连接自动连接所有同名列。

SELECT A.value, B.value
FROM tablea AS A NATURAL JOIN tableb AS B;

外连接

外连接保留了没有关联的那些行。分为左外连接，右外连接以及全外连接，左外连接就是保留左表没有关联的行。

检索所有顾客的订单信息，包括还没有订单信息的顾客。

SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;

  十四、视图

视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。

对视图的操作和对普通表的操作一样。

视图具有如下好处：

• 简化复杂的 SQL 操作，比如复杂的连接；
• 只使用实际表的一部分数据；
• 通过只给用户访问视图的权限，保证数据的安全性；
• 更改数据格式和表示。

CREATE VIEW myview AS
SELECT Concat(col1, col2) AS concat_col, col3*col4 AS compute_col
FROM mytable
WHERE col5 = val;

十五、存储过程

存储过程可以看成是对一系列 SQL 操作的批处理。

使用存储过程的好处：

• 代码封装，保证了一定的安全性；
• 代码复用；
• 由于是预先编译，因此具有很高的性能。

命令行中创建存储过程需要自定义分隔符，因为命令行是以 ; 为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。

包含 in、out 和 inout 三种参数。

给变量赋值都需要用 select into 语句。

每次只能给一个变量赋值，不支持集合的操作。

十六、游标

在存储过程中使用游标可以对一个结果集进行移动遍历。

游标主要用于交互式应用，其中用户需要对数据集中的任意行进行浏览和修改。

使用游标的四个步骤：

1. 声明游标，这个过程没有实际检索出数据；
2. 打开游标；
3. 取出数据；
4. 关闭游标；