Home 存档 照片墙 年度计划
Home 存档 照片墙 年度计划

一文看完《mysql必知必会》-知识点总结版

数据库

基础（连接和选择数据库）

  1. 选择指定数据库：USE + ‘数据库名称’

  2. 查看所有可以使用的数据库： SHOW DATABASES

  3. 返回当前选择的数据库内可用表的列表：SHOW TABLES

  4. 显示表的所有列：SHOW COLUMNS FROM ‘表名’

  5. 其他常用SHOW语句：

    1. SHOW STATUS，用于显示广泛的服务器状态信息；
    2. SHOW CREATE DATABASE和SHOW CREATE TABLE，分别用来显示创建特定数据库或表的MySQL语句；
    3. SHOW GRANTS，用来显示授予用户（所有用户或特定用户）的安全权限；
    4. SHOW ERRORS和SHOW WARNINGS，用来显示服务器错误或警告消息。

select语句

  1. 检索单个列：SELESCT ‘列名’ FROM ‘表名’

  2. SQL其实不区分大小写，也会忽略所有空格，尽量命令末尾都加上分号，但这样写更易于阅读调试

  3. 检索多个列：SELECT ’列名1‘, ‘列名2‘, ‘列名3’ FROM ‘表名‘

  4. 检索所有列： SELECT * FROM table_name

  5. 使用通配符会降低检索效率，但可以检索出名字未知的列

  6. 检索不同的行：SELECT DISTINCT Column1 FROM table_name

  7. 不能部分使用DISTINCT DISTINCT关键字应用于所有列而不仅是前置它的列。如果给出SELECT DISTINCT vend_id,prod_price，除非指定的两个列都不同，否则所有行都将被检索出来。

  8. 限制结果数量：

    1. SELECT column FROM table_name LIMIT 5;（返回不多于5行）
    2. SELECT column FROM table_name LIMIT 5, 5;（LIMIT 5, 5指示MySQL返回从行5开始的5行。第一个数为开始位置，第二个数为要检索的行数。）
    3. MySQL 5支持LIMIT的另一种替代语法。LIMIT 4 OFFSET 3 意为从行3开始取4行，就像LIMIT 3, 4一样。

  9. 完全限定的表名：

    1. SELECT table_name.column FROM table_name
    2. SELECT table_name.column FROM database_name.table_name

排序检索数据

  1. 以某列排序：SELECT column FROM table_name ORDER BY column

  2. 按多个列排序：SELECT column1, column2, column3 FROM table_name ORDER BY column1, column2

  3. 指定降序排列：

    1. 某一列：SELECT column FROM table_name ORDER BY column DESC
    2. 多列：SELECT column1, column2, column3 FROM table_name ORDER BY column1 DESC, column2
    3. 多列都降序：SELECT column1, column2, column3 FROM table_name ORDER BY column1 DESC, column2 DESC

  4. 在给出ORDER BY子句时，应该保证它位于FROM子句之后。如果使用LIMIT，它必须位于ORDER BY之后。使用子句的次序不对将产生错误消息。

过滤数据

  1. 检索特定信息：SELECT column1, column2 FROM table_name WHERE column1 = xxx
  2. WHERE子句操作符

  1. MySQL在执行匹配时默认不区分大小写
  2. 范围值检查：SELECT column1, column2 FROM table_name WHERE column1 BETWEEN xx AND xxx
  3. 空值检查：SELECT column1, column2 FROM table_name WHERE column1 IS NULL
  4. NULL与不匹配： 在通过过滤选择出不具有特定值的行时，你可能希望返回具有NULL值的行。但是，不行。因为未知具有特殊的含义，数据库不知道它们是否匹配，所以在匹配过滤或不匹配过滤时不返回它们。因此，在过滤数据时，一定要验证返回数据中确实给出了被过滤列具有NULL的行。

数据过滤

  1. 组合WHERE子句

    1. AND操作符：SELECT column1, column2, column3 FROM table_name WHERE column1 = xxx AND column2 <> xxxxx
    2. OR操作符：SELECT column1, column2, column3 FROM table_name WHERE column1 = xxx OR column2 <> xxxxx

  2. 计算次序：SELECT column1, column2, column3 FROM table_name WHERE column1 = xxx AND column2 <> xxxxx OR column3 = xxxxxx

    1. SQL（像多数语言一样）在处理OR操作符前，优先处理AND操作符。此问题的解决方法是使用圆括号明确地分组相应的操作符。
    2. SELECT column1, column2, column3 FROM table_name WHERE column1 = xxx AND ( column2 <> xxxxx OR column3 = xxxxxx )
    3. 任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符。不要过分依赖默认计算次序，即使它确实是你想要的东西也是如此。使用圆括号没有什么坏处，它能消除歧义

  3. IN操作符：SELECT column1, column2 FROM table_name WHERE column1 IN (xx, xxx)

  4. NOT取反：SELECT column1, column2 FROM table_name WHERE column1 NOT IN (xx, xxx)

用通配符过滤

  1. 百分号通配符：

    1. xxx开头：SELECT column1, column2 FROM table_name WHERE column1 LIKE ‘xxx%’
    2. xxx结尾：SELECT column1, column2 FROM table_name WHERE column1 LIKE ‘%xxx’
    3. 包含xxx：SELECT column1, column2 FROM table_name WHERE column1 LIKE ‘%xxx%’
    4. s开头e结尾：SELECT column1, column2 FROM table_name WHERE column1 LIKE ‘s%e’
    5. 虽然似乎%通配符可以匹配任何东西，但有一个例外，即NULL。即使是WHERE column1 LIKE ‘%’也不能匹配用值NULL作为产品名的行。

  2. 下划线通配符

    1. 另一个有用的通配符是下划线（_）。下划线的用途与%一样，但下划线只匹配单个字符而不是多个字符。
    2. SELECT column1, column2 FROM table_name WHERE column1 LIKE ‘_xxx’

  3. 通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长。

    1. 不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
    2. 在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。

正则表达式搜索

  1. 基本字符匹配：SELECT column1 FROM table_name WHERE column1 REGEXP ‘xxx’

  2. MySQL中的正则表达式匹配（自版本3.23.4后）不区分大小写（即，大写和小写都匹配）。为区分大小写，可使用BINARY关键字，如WHERE prod_name REGEXP BINARY ‘JetPack .000’。

  3. 进行OR匹配：SELECT column1 FROM table_name WHERE column1 REGEXP ‘xxx | yyy | zzz’

  4. 匹配几个字符之一：SELECT column1 FROM table_name WHERE column1 REGEXP ‘[123] xxx’

    1. 类似于OR匹配：SELECT column1 FROM table_name WHERE column1 REGEXP ‘[1 | 2 | 3] xxx’

  5. 匹配范围：SELECT column1 FROM table_name WHERE column1 REGEXP ‘[1-3] xxx’

    1. [0-9]任意数字，[a-z]任意字母字符

  6. 匹配特殊字符，利用转义：SELECT column1 FROM table_name WHERE column1 REGEXP ‘\.’

  7. 使REGEXP起类似LIKE的作用 本章前面说过，LIKE和REGEXP的不同在于，LIKE匹配整个串而REGEXP匹配子串。利用定位符，通过用^开始每个表达式，用$结束每个表达式，可以使REGEXP的作用与LIKE一样。

  8. ^ 的双重用途 ^有两种用法。在集合中（用[和]定义），用它来否定该集合，否则，用来指串的开始处。

  9. 匹配字符类，匹配多个实例，定位符：

创建计算字段

  1. 拼接字段：SELECT Concat( column1, ‘ (‘ , column2 , ‘)’ ) FROM table_name

    1. 输出为column1（column2）格式
    2. Concat()拼接串，即把多个串连接起来形成一个较长的串。Concat()需要一个或多个指定的串，各个串之间用逗号分隔。
    3. 使用trim函数删去可能存在的空格：SELECT Concat( RTrim(column1), ‘ (‘ , RTrim(column2) , ‘)’ ) FROM table_name
    4. Trim函数 MySQL除了支持RTrim()（正如刚才所见，它去掉串右边的空格），还支持LTrim()（去掉串左边的空格）以及Trim()（去掉串左右两边的空格）。

  2. 使用别名：SELECT Concat( RTrim(column1), ‘ (‘ , RTrim(column2) , ‘)’ ) AS column3 FROM table_name

    1. 别名有时也称为导出列（derived column）

  3. 执行算术计算：SELECT column1, column2, column1*column2 AS column3 FROM table_name

使用数据处理函数

  1. 文本处理函数，发音比较：

    1. 完全等于：SELECT column1, column2 FROM table_name WHERE column1 = ‘xxx’
    2. 听起来像：SELECT column1, column2 FROM table_name WHERE Soundex(column1) = Soundex(‘xxx’)

  2. 日期和时间处理函数：

    1. 根据日期过滤：SELECT column1, column2 FROM table_name WHERE Date(column1) = ‘xxxx-xx-xx’

  3. 数值处理函数：

    1. 数值处理函数仅处理数值数据。

汇总数据

  1. 聚集函数：只返回汇总之后的数据，不需要返回具体数据

    1. AVG：SELECT AVG(column) AS column2 FROM table_name

      1. 只能用于单个列，为了获得多个列的平均值，必须使用多个AVG()函数。
      2. AVG()函数忽略列值为NULL的行

    2. COUNT：

      1. 使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。
      2. ‰ 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。

    3. MAX：

      1. 对非数值数据使用MAX() ，则MAX()返回最后一行。
      2. MAX()函数忽略列值为NULL的行。

    4. SUM：

      1. SUM()函数忽略列值为NULL的行

  2. 聚焦不同值：只要求包含不同的值，指定DISTINCT参数。

    1. SELECT AVG(DISTINCT column) AS column2 FROM table_name
    2. 如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*)，因此不允许使用COUNT（DISTINCT），否则会产生错误。类似地，DISTINCT必须使用列名，不能用于计算或表达式。

分组数据

  1. 分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算。

  2. 创建分组：SELECT column1, COUNT(*) AS column2 FROM table_name GROUP BY column1

    1. GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
    2. 如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
    3. GROUP BY子句中列出的每个列都必须是检索列或有效的表达式但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
    4. 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。
    5. 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
    6. GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。
    7. 使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值

  3. 过滤分组

    1. HAVING支持所有WHERE操作符，差别是WHERE过滤行，HAVING过滤分组
    2. SELECT column1, COUNT() AS column2 FROM table_name GROUP BY column1 HAVING COUNT() >= 123;
    3. 一般在使用GROUP BY子句时，应该也给出ORDER BY子句。这是保证数据正确排序的唯一方法。千万不要仅依赖GROUP BY排序数据。

子查询

  1. 可以把一条SELECT语句返回的结果用于另一条SELECT语句的WHERE子句。

联结表

  1. SELECT T1column1, T2column1 FROM table1, table2 WHERE table1.T1column1 = table2.T2column1

  2. 等值联结（equijoin），它基于两个表之间的相等测试。这种联结也称为内部联结。

    1. SELECT T1column1, T2column1 FROM table1 INNER JOIN table2 ON table1.T1column1 = table2.T2column1
    2. 首选INNER JOIN语法。此外，尽管使用WHERE子句定义联结的确比较简单，但是使用明确的联结语法能够确保不会忘记联结条件，有时候这样做也能影响性能

创建高级联结

  1. 使用表别名：SELECT T1column1, T2column1 FROM table1 AS T1 , table2 AS T2 WHERE T1.T1column1 = T2.T2column1

  2. 其他类型的连接

    1. 自联结：SELECT T1.column1, T1.column2 FROM table1 AS T1 , table1 AS T2 WHERE T1.column1 = T2.column1 AND T2.column3 > 123

      1. 此查询中需要的两个表实际上是相同的表
      2. 自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的，但有时候处理联结远比处理子查询快得多。

    2. 自然联结:内部联结返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次

      1. 自然联结是你只能选择那些唯一的列。这一般是通过对一个表使用通配符（SELECT *），对所有其他表的列使用明确的子集来完成的
      2. 迄今为止我们建立的每个内部联结都是自然联结，很可能我们永远都不会用到不是自然联结的内部联结

    3. 外部连结：SELECT T1column1, T2column1 FROM table1 LEFT OUTER JOIN table2 ON table1.T1column1 = table2.T2column1

      1. 外部联结还包括没有关联行的行。
      2. 上面的例子使用LEFT OUTER JOIN从FROM子句的左边表（table1表）中选择所有行。

组合查询

  1. UNION规则

    1. UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合4条SELECT语句，将要使用3个UNION关键字）。
    2. UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。
    3. 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）
    4. UNION从查询结果集中自动去除了重复的行（换句话说，它的行为与单条SELECT语句中使用多个WHERE子句条件一样）。如果想返回所有匹配行，可使用UNION ALL而不是UNION。
    5. UNION几乎总是完成与多个WHERE条件相同的工作。UNION ALL为UNION的一种形式，它完成WHERE子句完成不了的工作。如果确实需要每个条件的匹配行全部出现（包括重复行），则必须使用UNION ALL而不是WHERE

全文本搜索

  1. 在创建表时指定FULLTEXT，或者在稍后指定（在这种情况下所有已有数据必须立即索引）。

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

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

  4. SELECT note_text FROM productnotes WHERE Match(note_text) Against(‘rabbit’);

    1. 此SELECT语句检索单个列note_text。由于WHERE子句，一个全文本搜索被执行。Match(note_text)指示MySQL针对指定的列进行搜索，Against(‘rabbit’)指定词rabbit作为搜索文本。

  5. 传递给 Match() 的值必须与FULLTEXT()定义中的相同。如果指定多个列，则必须列出它们（而且次序正确）。

  6. 除非使用BINARY方式（本章中没有介绍），否则全文本搜索不区分大小写。

  7. 查询扩展：

    1. 首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有行；
    2. 其次，MySQL检查这些匹配行并选择所有有用的词（我们将会简要地解释MySQL如何断定什么有用，什么无用）。
    3. 再其次，MySQL再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词。
    4. SELECT note_text FROM productnotes WHERE Match(note_text) Against(‘rabbit’ WITH QUERY EXPANSION);

  8. 布尔文本搜索

全文本搜索的一些说明：

  • 在索引全文本数据时，短词被忽略且从索引中排除。短词定义为那些具有3个或3个以下字符的词（如果需要，这个数目可以更改）。
  • MySQL带有一个内建的非用词（stopword）列表，这些词在索引全文本数据时总是被忽略。如果需要，可以覆盖这个列表（请参阅MySQL文档以了解如何完成此工作）。
  • 许多词出现的频率很高，搜索它们没有用处（返回太多的结果）。因此，MySQL规定了一条50%规则，如果一个词出现在50%以上的行中，则将它作为一个非用词忽略。50%规则不用于IN BOOLEAN MODE。
  • 如果表中的行数少于3行，则全文本搜索不返回结果（因为每个词或者不出现，或者至少出现在50%的行中）。
  • 忽略词中的单引号。例如，don’t索引为dont。
  • 不具有词分隔符（包括日语和汉语）的语言不能恰当地返回全文本搜索结果。

插入数据

  1. 插入完整的行：INSERT INTO table1(column1, column3, column6) VALUSE(‘xxxx’, NULL, ‘123123’)
  2. 插入多条数据：INSERT INTO table1(column1, column3, column6) VALUSE(‘xxxx’, NULL, ‘123123’), (‘xx’, ‘xxxx’, ‘222’)

更新和删除数据

  1. 更新：UPDATE table1 SET column1 = ‘xxxxxx’, column3 = ‘123123’ WHERE column2 = ‘yyyy’

    1. 如果用UPDATE语句更新多行，并且在更新这些行中的一行或多行时出一个现错误，则整个UPDATE操作被取消（错误发生前更新的所有行被恢复到它们原来的值）。为即使是发生错误，也继续进行更新，可使用IGNORE关键字，如下所示：UPDATE IGNORE customers…
    2. 为了删除某个列的值，可设置它为NULL（假如表定义允许NULL值）。

  2. 删除：DELETE FROM table1 WHERE column1 = ‘xxx’

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

  3. 在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确

视图

  1. 视图用CREATE VIEW语句来创建。
  2. 使用SHOW CREATE VIEW viewname；来查看创建视图的语句。
  3. 用DROP删除视图，其语法为DROP VIEW viewname;。
  4. 更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE ORREPLACE VIEW。如果要更新的视图不存在，则第2条更新语句会创建一个视图；如果要更新的视图存在，则第2条更新语句会替换原有视图。
  5. 应该将视图用于检索（SELECT语句）而不用于更新（INSERT、UPDATE和DELETE）

使用存储过程

  1. 存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，虽然它们的作用不仅限于批处理。

使用游标

  1. 需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。游标（cursor）是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集
  2. MySQL游标只能用于存储过程（和函数）
  3. 我称之为存储过程里的函数操作

触发器

  1. 在创建触发器时，需要给出4条信息：

    1. 唯一的触发器名；
    2. 触发器关联的表；
    3. 触发器应该响应的活动（DELETE、INSERT或UPDATE）；
    4. 触发器何时执行（处理之前或之后）

  2. 每个表最多支持6个触发器（每条INSERT、UPDATE和DELETE的之前和之后）。单一触发器不能与多个事件或多个表关联

  3. 触发器不能更新或覆盖。为了修改一个触发器，必须先删除它，然后再重新创建：DROP TRIGGER xxxx

管理事务处理

  1. 事务处理（transaction processing）可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。保证原子性

    1. 事务（transaction）指一组SQL语句；
    2. 回退（rollback）指撤销指定SQL语句的过程；
    3. 提交（commit）指将未存储的SQL语句结果写入数据库表；
    4. 保留点（savepoint）指事务处理中设置的临时占位符（placeholder），你可以对它发布回退（与回退整个事务处理不同）

  2. 事务处理用来管理INSERT、UPDATE和DELETE语句。你不能回退SELECT语句。（这样做也没有什么意义。）你不能回退CREATE或DROP操作

  3. ROLLBACK只能在一个事务处理内使用（在执行一条START TRANSACTION命令之后）

本作品采用 知识共享署名-非商业性使用-禁止演绎 4.0 国际许可协议 (CC BY-NC-ND 4.0) 进行许可。