SQL笔记

1、distinct ：

去重，必须放在开头，有多个列时，是多列去重。

2、concat()：

拼接串，多个串连接成一个串。

3、Trim()：

去掉左右多余空格，RTrim()去右边多余空格。LTrim()去掉左边多余空格。

IFNULL()：IFNULL() 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。

IFNULL(expression, alt_value)

4、时间函数

函数	说明
`AddDate()`	增加一个日期（天、周等）
`AddTime()`	增加一个时间（时、分等）
`CurDate()`	返回当前日期
`CurTime()`	返回当前时间
`Date()`	返回日期时间的日期部分
`DateDiff()`	计算两个日期之差
`Date_Add()`	高度灵活的日期运算函数
`Date_Format()`	返回一个格式化的日期或时间串
`Day()`	返回一个日期的天数部分
`DayOfWeek()`	对于一个日期，返回对应的星期几
`Hour()`	返回一个时间的小时部分
`Minute()`	返回一个时间的分钟部分
`Month()`	返回一个日期的月份部分
`Now()`	返回当前日期和时间
`Second()`	返回一个时间的秒部分
`Time()`	返回一个日期时间的时间部分
`Year()`	返回一个日期的年份部分

5、数值处理函数

函数	说明
`Abs()`	返回一个数的绝对值
`Cos()`	返回一个角度的余弦
`Exp()`	返回一个数的指数值
`Mod()`	返回除操作的余数
`Pi()`	返回圆周率
`Rand()`	返回一个随机数
`Sin()`	返回一个角度的正弦
`Sqrt()`	返回一个数的平方根
`Tan()`	返回一个角度的正切

6、聚集函数

函数	说明
`AVG()`	返回某列的平均值（忽略值为`NULL`的行）
`COUNT()`	返回某列的行数 `COUNT(*)` 不管表列中包含的是空值（`NULL` ）还是非空值 `COUNT(column)` 忽略`NULL` 值
`MAX()`	返回某列的最大值（忽略`NULL`）
`MIN()`	返回某列的最小值
`SUM()`	返回某列值之和（忽略`NULL`）

若在聚集函数中需要忽略相同值可以用COUNT(distinct a)。

7、分组数据

GROUP BY 子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。

GROUP BY列出的每个列都要死检索列或有效的表达式，不能是聚集函数。

如果分组列中有NULL值，则NULL值作为一个分组返回，多个NULL将被分为一组。

GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

使用ROLLUP：使用WITH ROLLUP关键字，可以在最后一行列出所有分组的总和。

8、过滤分组

HAVING和WHERE的差别：WHERE在数据分组前进行过滤，HAVING在分组后进行过滤。大前提是WHERE排除的行不在分组中。

9、`SELECT` 子句及其顺序

子句	说明	是否必须使用
`SELECT`	要返回的列或表达式	是
`FROM`	从中检索数据的表	仅在从表选择数据时使用
`WHERE`	行级过滤	否
`GROUP BY`	分组说明	仅在按组计算聚集时使用
`HAVING`	组级过滤	否
`ORDER BY`	输出排序顺序	否
`LIMIT`	要检索的行数	否

10、子查询

列必须匹配：在WHERE 子句中使用子查询，应该保证SELECT 语句具有与WHERE 子句中相同数目的列。通常，子查询将返回单个列并且与单个列匹配，但如果需要也可以使用多个列。

相关子查询：涉及外部查询的子查询。这种类型的子查询称为相关子查询。任何时候只要列名可能有多义性，就必须使用这种语法比如：

SELECT cust_name, cust_state, 
    (SELECT COUNT(*) FROM orders
    WHERE orders.cust_id = customers.cust_id) AS orders 
FROM customers ORDER BY cust_name;

中的WHERE orders.cust_id = customers.cust_id。

11、联结

等值联结（内部联结）

SQL最强大的功能之一就是能在数据检索查询的执行中联结表。联结是利用SQL的SELECT 能执行的最重要的操作，很好地理解联结及其语法是学习SQL的一个极为重要的组成部分。

笛卡儿积（cartesianproduct） 由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

比如：
```
SELECT vend_name, prod_name, prod_price
FROM vendors,products#没有用where限定条件，会输出（预定义venders表有a行，products有b行）a*b行。
ORDER BY vend_name, prod_name;
```

使用inner join

比如：

 SELECT vend_name, prod_name, prod_price
 FROM vendors INNER JOIN products
  ON vendors.vend_id = products.vend_id;

ANSI SQL规范首选INNER JOIN 语法。此外，尽管使用WHERE 子句定义联结的确比较简单，但是使用明确的联结语法能够确保不会忘记联结条件，有时候这样做也能影响性能。

性能考虑 MySQL在运行时关联指定的每个表以处理联结。这种处理可能是非常耗费资源的，因此应该仔细，不要联结不必要的表。联结的表越多，性能下降越厉害。

另外，部分子查询可以改成联结查询，联结查询更快，因为子查询会创建临时表，查询完毕后再删除临时表，子查询速度较慢。

自联结

自联结，就是用表别名的方式将一个表假设为两张一样的表，做多表查询。

现在有一个情景：一个表，里面有商品id：prod_id，商品名：prod_name，供应商id：vend_id，表名：products。

假如你发现某物品（其ID为DTNTR ）存在问题，想知道生产该物品的供应商生产的其他物品是否有问题。要查询这个供应商生产的所有物品，怎么查询？
```
SELECT p1.prod_id, p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
AND p2.prod_id = 'DTNTR';
```
自然联结

无论何时对表进行联结，应该至少有一个列出现在不止一个表中(被联结的列)。标准的联结返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次。

自然联结是这样一种联结，其中你只能选择那些唯一的列。这一般是通过对表使用通配符（SELECT * ），对所有其他表的列使用明确的子集来完成的。
外部联结

左外部联结(又叫左联结)

左表的行一定会列出，右表如果没有匹配的行，那么列值就为NULL。

特别需要注意的是如果右表有多行和左表匹配，那么左表相同的行会出现多次

例子：
```
select a.f1,b.f2 from a left outer join b on a.f3=b.f4(outer关键字可以省略)
```
右外部联结(又叫右联结)

和左联结类似，只不过以右表为主表而已，左联结和右联结可以相互转化。
```
select a.f1,b.f2 from a right outer join b on a.f3=b.f4(outer关键字可以省略)
```
全外部联结

返回左表和右表的所有行，不管有没有匹配，同时具有左联结和右联结的特性。
```
select a.f1,b.f2 from a full outer join b on a.f3=b.f4(outer关键字可以省略)
```
关于联结的示意图：

[图片上传失败...(image-e6a470-1589365058282)]

12、组合查询

可用UNION 操作符来组合数条SQL查询。利用UNION ，可给出多条SELECT 语句，将它们的结果组合成单个结果集。

比如：

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001,1002);

UNION规则：

UNION 必须由两条或两条以上的SELECT 语句组成，语句之间用关键字UNION 分隔（因此，如果组合4条SELECT 语句，将要使用3个UNION 关键字）。
UNION 中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。
列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。

UNION从查询结果集中会自动去除重复的行。（不分字段的类型，只要值一样就去重）

如果想返回所有匹配行，可使用UNION ALL 而不是UNION 。

在用UNION 组合查询时，只能使用一条ORDER BY 子句，它必须出现在最后一条SELECT 语句之后。

13、全文本搜索

并非所有引擎都支持全文本搜索，MyISAM支持全文本搜索。

为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重新索引。在对表列进行适当设计后，MySQL会自动进行所有的索引和重新索引。

一般在创建表时启用全文本搜索。CREATE TABLE 语句接受FULLTEXT 子句，它给出被索引列的一个逗号分隔的列表。

例如：

CREATE TABLE productnotes
(
  note_id    int           NOT NULL AUTO_INCREMENT,
  prod_id    char(10)      NOT NULL,
  note_date datetime       NOT NULL,
  note_text  text          NULL ,
  PRIMARY KEY(note_id),
  FULLTEXT(note_text)
) ENGINE=MyISAM;

这些列中有一个名为note_text 的列，为了进行全文本搜索，MySQL根据子句FULLTEXT(note_text) 的指示对它进行索引。这里的FULLTEXT 索引单个列，如果需要也可以指定多个列。

在定义之后，MySQL自动维护该索引。在增加、更新或删除行时，索引随之自动更新。

不要在导入数据时使用FULLTEXT 更新索引要花时间，虽然不是很多，但毕竟要花时间。如果正在导入数据到一个新表，此时不应该启用FULLTEXT 索引。应该首先导入所有数据，然后再修改表，定义FULLTEXT 。这样有助于更快地导入数据（而且使索引数据的总时间小于在导入每行时分别进行索引所需的总时间）。

在索引之后，使用两个函数Match() 和Against() 执行全文本搜索，其中Match() 指定被搜索的列，Against() 指定要使用的搜索表达式。

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');

14、插入数据

插入多行

INSERT INTO customers(cust_name,
   cust_address,
   cust_city,
   cust_state,
   cust_zip,
   cust_country)
VALUES(
        'Pep E. LaPew',
        '100 Main Street',
        'Los Angeles',
        'CA',
        '90046',
        'USA'
     ),
      (
        'M. Martian',
        '42 Galaxy Way',
        'New York',
        'NY',
        '11213',
        'USA'
   );

提高INSERT的性能 此技术可以提高数据库处理的性能，因为MySQL用单条INSERT 语句处理多个插入比使用多条INSERT 语句快。

插入检索出的数据

这个例子把一个名为custnew 的表中的数据导入customers 表中。

INSERT INTO customers(cust_id,
    cust_contact,
    cust_email,
    cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country)
SELECT cust_id,
    cust_contact,
    cust_email,
    cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country
FROM custnew;

15、更新数据

不要省略WHERE 子句 在使用UPDATE 时一定要注意细心。因为稍不注意，就会更新表中所有行。

例如：

UPDATE customers
SET cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;

在UPDATE 语句中使用子查询 UPDATE 语句中可以使用子查询，使得能用SELECT 语句检索出的数据更新列数据。

IGNORE 关键字 如果用UPDATE 语句更新多行，并且在更新这些行中的一行或多行时出一个现错误，则整个UPDATE 操作被取消（错误发生前更新的所有行被恢复到它们原来的值）。为即使是发生错误，也继续进行更新，可使用IGNORE 关键字。

16、删除数据

例子：

DELETE FROM customers
WHERE cust_id = 10006;

更快的删除 如果想从表中删除所有行，不要使用DELETE 。可使用TRUNCATE TABLE 语句，它完成相同的工作，但速度更快（TRUNCATE 实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据）。

17、创建和操纵表

处理现有的表 在创建新表时，指定的表名必须不存在，否则将出错。如果你仅想在一个表不存在时创建它，应该在表名前给出IF NOT EXISTS 。这样做不检查已有表的模式是否与你打算创建的表模式相匹配。它只是查看表名是否存在，并且仅在表名不存在时创建它。

默认值

默认值用CREATE TABLE语句的列定义中的DEFAULT关键字指定。

quantity int NOT NULL DEFAULT 1,

与大多数DBMS不一样，MySQL不允许使用函数作为默认值，它只支持常量。许多数据库开发人员使用默认值而不是NULL列，特别是对用于计算或数据分组的列更是如此。

数据库引擎

MySQL有一个具体管理和处理数据的内部引擎。在你使用CREATE TABLE语句时，该引擎具体创建表，而在你使用SELECT语句或进行其他数据库处理时，该引擎在内部处理你的请求。多数时候，此引擎都隐藏在DBMS内，不需要过多关注它。MySQL与其他DBMS不一样，它具有多种引擎。它打包多个引擎，这些引擎都隐藏在MySQL服务器内，全都能执行CREATE TABLE和SELECT等命令。

以下是几个需要知道的引擎：

InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索；
MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）；
MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。

引擎类型可以混用。比如一个表使用MyISAM外，其他表都使用InnoDB。但是要注意外键不能跨引擎，即使用一个引擎的表不能引用具有使用不同引擎的表的外键。

更新表

使用ALTER TABLE 更改表结构，必须给出下面的信息：

在ALTER TABLE 之后给出要更改的表名（该表必须存在，否则将出错）；
所做更改的列表。

ALTER TABLE 的一种常见用途是定义外键。下面是用来定义本书中的表所用的外键的代码：

ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_orders
FOREIGN KEY (order_num) REFERENCES orders (order_num);

ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id)
REFERENCES products (prod_id);

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id)
REFERENCES customers (cust_id);

ALTER TABLE products
ADD CONSTRAINT fk_products_vendors
FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

这里，由于要更改4个不同的表，使用了4条ALTER TABLE 语句。为了对单个表进行多个更改，可以使用单条ALTER TABLE 语句，每个更改用逗号分隔。

重命名表

使用RENAME TABLE 语句可以重命名一个表：

RENAME TABLE customers2 TO customers;

多个表重命名：

RENAME TABLE backup_customers TO customers,
             backup_vendors TO vendors,
             backup_products TO products;

18、视图

关于视图创建和使用的一些最常见的规则和限制：

与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）。
对于可以创建的视图数目没有限制。
为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予。
视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图。
ORDER BY 可以用在视图中，但如果从该视图检索数据SELECT 中也含有ORDER BY ，那么该视图中的ORDER BY 将被覆盖。
视图不能索引，也不能有关联的触发器或默认值。
视图可以和表一起使用。例如，编写一条联结表和视图的SELECT 语句。

性能问题 因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索。如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降得很厉害。因此，在部署使用了大量视图的应用前，应该进行测试。

视图的创建：

视图用CREATE VIEW 语句来创建。
使用SHOW CREATE VIEW viewname; 来查看创建视图的语句。
用DROP 删除视图，其语法为DROP VIEW viewname; 。
更新视图时，可以先用DROP 再用CREATE ，也可以直接用CREATE OR REPLACE VIEW 。如果要更新的视图不存在，则第2条更新语句会创建一个视图；如果要更新的视图存在，则第2条更新语句会替换原有视图。

语法格式如下：

CREATE VIEW <视图名> AS <SELECT语句>

对于创建视图中的 SELECT 语句的指定存在以下限制：

用户除了拥有 CREATE VIEW 权限外，还具有操作中涉及的基础表和其他视图的相关权限。
SELECT 语句不能引用系统或用户变量。
SELECT 语句不能包含 FROM 子句中的子查询。
SELECT 语句不能引用预处理语句参数。

19、limit和offset的区别

SQL查询语句中的 limit 与 offset 的区别：

limit y 分句表示: 读取 y 条数据
limit x, y 分句表示: 跳过 x 条数据，读取 y 条数据
limit y offset x 分句表示: 跳过 x 条数据，读取 y 条数据

比如分页获取数据：

第1页：从第0个开始，获取20条数据

select * from testtable limit 0, 20; 
select * from testtable limit 20 offset 0;

第2页：从第20个开始，获取20条数据

select * from testtable limit 20, 20; 
select * from testtable limit 20 offset 20;

第3页：从第40个开始，获取20条数据

select * from testtable limit 40, 20; 
select * from testtable limit 20 offset 40;

20、CHAR和VARCHAR的区别

CHAR和VARCHAR的主要区别有三方面：

最大长度
有效长度
存储方式
是否保留末尾空格

最大长度

CHAR的长度范围为0~255
VARCHAR的长度范围为0~65535

有效长度

CHAR的长度按照声明的长度保持不变
VARCHAR的长度是可变的，VARCHAR数据的有效长度应在声明时指定的长度范围内
当插入的数据超出CHAR或VARCHAR声明的长度时，非严格模式会报警告并截断数据，严格模式下会报错
当插入的数据超出CHAR或VARCHAR声明的长度，且当超出的部分仅为数据末尾空格时，无论何种模式下，CHAR型数据会截断数据且不提示任何信息，而VARCHAR型数据会截断数据但报警告

存储方式

CHAR型数据仅存储数据本身；

VARCHAR型数据的存储方式为：1~2字节的长度前缀 + 数据；
CHAR型数据存储时，若数据的长度小于声明的长度，则会自动在该数据的右侧用空格补全长度；

VARCHAR型数据存储时，若数据长度小于声明的长度，不会用空格补全；

以长度为4的CHAR和VARCHAR举例：

值	CHAR(4)	所需存储空间	VARCHAR(4)	所需存储空间
''	`' '`（存储了4个空格）	4byte	`''`	1byte
'ab'	`'ab '`（存储了字符ab及两个空格）	4byte	`'ab'`	3byte
'abcd'	`'abcd'`	4byte	`'abcd'`	5byte
'abcdefg'	`'abcd'`	4byte	`'abcd'`	5byte

是否保留末尾空格

CHAR型数据检索时，会去除数据末尾的所有空格
VARCHAR型数据检索时，会保留数据末尾的所有空格

举例：

CREATE TABLE varchar_and_char (v VARCHAR(4), c CHAR(4));
INSERT INTO varchar_and_char VALUES ('ab  ', 'ab  ');
SELECT CONCAT('(', v, ')'), CONCAT('(', c, ')') FROM varchar_and_char;

输出：