MySql必知必会

MySql必知必会

1. 主键的好习惯

• 不更新主键列中的值
• 补充用主键列的值
• 不再主键列中使用可能会更改的值

2. Mysql重大版本

• 4.0 引进 InnoDB 引擎，增加了事务处理，并，改进全文本搜索等的支持；
• 4.1 对函数库、子查询、集成帮助等有重大改进；
• 5 存储过程、触发器、游标、视图等；

3. 简单的查询

• 查看当前数据库实例：SHOW DATABASES；
• 查看数据库实例下的表：SHOW TABLES;
• 查看服务器状态信息：SHOW SATAUS;
• 创建数据库：SHOW CREATE DATABASES;
• 注：Sql语句中所有的空格都会被忽略；

4. 查询语句相关

• 使用DISTINCT关键字，会应用于所有列而不仅是前置的列。如果给出SELECT DISTINCT vend_id,prod_price from xxxx 除非指定的两个列不同，否则所有行都将被检索出来；
• 使用LIMIT子句限制返回行数，SELECT语句会返回所有匹配的行；LIMIT的用法Limit 3,4：从第3行开始取4行，或者使用limit 4 offset 3意义是相同的；

5. 排序检索数据

• ORDER BY 子句可以取一个所多个列的名字，据此对输出进行排序；
• 可以使用DESC 指定降序排序 (升序排序是默认的），DESC关键字只应用到直接位于其前面的列名上；
• 使用ASC指定升序排序；
  -注：在给出ORDER BY子句时，应该保证它在FROM子句之后 。如果使用LIMIT，它必须位于ORDER BY之后。使用子句的次序不对将产生错误消息；

6. 过滤数据

• 使用WHERE子句，并指定检索条件。
  -在同时使用ORDER BY 和 WHERE 子句时，应该让ORDER BY 位于WHERE 之后；
• WHERE子句操作符
  1. 等于：=；
  2. 不等于：<>;
  3. 不等于：!=；
  4. 小于：<；
  5. 小于等于：<=；
  6. 大于：>；
  7. 大于等于：>=；
  8. 指定值之间：BETWEEN；

-注：NULL与不匹配 在搜索时，数据库在匹配过滤或不匹配过滤是会自动忽略空值，因此在过滤数据时，一定要验证返回数据中确实给出了被过滤列具有NULL的行；

• ANDWHERE子句用的关键词，用来指示检索满足所有给定条件的行；
• OR WHERE子句中的关键字，用来指示检索匹配任意给定条件的行；
• IN 用来指定条件范围，范围中的每个条件都进行匹配，每个条件使用逗号进行分割且要位于圆括号中；
• 使用IN的优点：
  1. 在使用长的合法选项清单时，IN操作符的语法更加清楚且更直观；
  2. 在使用IN时，计算的次序更容易管理；
  3. 使用 IN 一般比 OR 执行更快；
  4. IN的最大优点是可以包含其他的 SELECT 子句；
     注：IN 的最大数量限制 1000
• NOT 可以对 IN、BETWEEN和EXISTS子句进行取反（与很多的DBMS有很大的差别）；
• 使用通配符LIKE进行过滤：
  • %在后：在MYSQL中搜索可以是区分大小写的 '123%' 则会搜索以123开头的词；
  • %%中间：例：%123%，会搜索所有包括123的词；
  • 关键词中间% 例：1%2，会搜索以1开头2结尾的词；
• 使用 ‘_‘下滑线统配符：使用方式同LIKE是相同的，但是下划线只匹配一个字符；
  -通配符的使用技巧：
  • 不要过度的使用通配符，如果其他操作可以达到相同的目的，优先使用其他操作符；
  • 在确实需要使用通配符时，若非绝对必要，否则不要把他们用在搜索模式的开始处；
  • 要注意通配符的位置；

7. 拼接字段

• 使用Concat() ：可以将多个串连接在一起，各个串之间使用逗号分割；
• 使用Trim()：消除空格，并可以通过 RTrim() 或 LTrin() 指定消除的方向，默认是量变都删；

8. 使用别名

• 使用 AS 关键字；

9. 执行算数

• MySql中支持在搜索中对列进行算数计算，包括 + 、-、*、/；

10. 数据处理函数

• 文本处理函数：
  • 返回串左边的字符：Left()；
  • 返回右边的字符：Right()；
  • 返回串的长度：Length()；
  • 找出串的一个子串：Locate()；
  • 将串转换为小写：Lowen()；
  • 返回串的Soundex值：Soundex()；转换为描述其语言表示的字母数字模式的算法；
  • 返回子串的字符：SubString()；
• 日期和时间处理函数：
  • 增加一个日期：AddDate()；
  • 增加一个时间：AddTime()；
  • 返回当前日期：CurDate()；
  • 返回当前时间：CurTime()；
  • 返回日期时间的日期部分：Date()；
  • 计算两个日期之差：DateDiff()；
  • 日期运算函数：Date_Add()；
  • 返回一个格式化的日期或时间串：Date_Format()；
  • 返回一个日期的天数部分：Day()；
  • 返回日期的星期：DayOfWeek()；
  • 返回一个时间的小时部分：Hour()；
  • 返回一个时间的分钟部分：Minute()；
  • 返回一个日期的月份：Month()；
  • 返回当期那日期和时间：Now()；
  • 返回一个时间的秒：Second()；
  • 返回一个日期时间的时间部分：Time();
  • 返回一个日期的年份：Year()；
• 数值处理函数
  • 返回绝对值：Abs()；
  • 返回余弦：Cos()；
  • 返回数的指数：Exp()；
  • 返回除操作的余数：Mod()；
  • 返回圆周率：Pi()；
  • 返回一个随机数：`Rand()；
  • 返回正弦：Sin()；
  • 返回平方根：Sqrt()；
  • 返回正切：Tan()；

11. 聚集函数

运行在行组上，计算和返回单个值的函数；

• AVG：对表中行数进行计数并计算特定列值之和之后计算平均数，只用于单个列，且列名必须作为函数的参数给出，并列会忽略NULL的行；

• COUNT：进行行计数，有两种使用方式

  1. 使用Count(*)：对表中行的数目进行计数，不管列表中包含的是控制还是非空值；
  2. 使用Count(列名）：对特定的列中具有值的行进行计数，忽略NULL值；

  注：在使用DISTINCT 时，只可作用于第二种情况；

• MAX：返回指定列中最大的值
  注：如果对非数值的列使用时，如果是日期会找出最大的，在用于英文文本时，返回按左起字母最大的；

• MIN：功能与Max() 相反；

• SUM： 用来返回指定列值的和；可以在多个列上使用；

12. 数据分组

分组是在 SELECT语句的GROUP BY子句中建立的，使用中有以下重要的规定：

• GROUP BY子句可以包含任意数目的列，能对分组进行嵌套，为数据分组提供更细致的控制；
• 如果在GROUP BY 子句中嵌套了分组，数据将在最后规定的分组上进行汇总。（在建立分组时，指定的所有列都一起计算）；
• GROUP BY 子句中前列出的每个列都必须是检索列或者有效的表达式 （不可以是聚集函数） 如果在SELECT中使用表达式，在GROUP BY中也要使用相同的表达式不可使用别名；
• 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出；
• 如果分组列中有NULL值，则NULL将作为一个分组返回；
• GROUP BY子句必须出现在WHERE子句的后面，ORDER BY子句的前面；
  注：使用ROLLUP可以得到每个分组及每个分组汇总级别的值 （最后一行）；

过滤分组：使用HAVING子句可以对分组进行过滤；
注：WHERE在数据分组前进行数据的过滤，HAVING在数据分组后进行过滤，WHERE所排除的行不会在分组中。换句话说WHERE是在对行进行过滤而HAVING是在对分组进行过滤；
分组和排序：在使用GROUP BY分组后使用ORDER BY子句可以使搜索结果有明确的排序规则，这是保证数据正确排序的唯一方法。
注:：不可依赖GROUP BY的数据排序；

13. 使用子查询

• 使用子查询进行过滤，可以吧一跳SELECT语句返回的结果用于另一条SELECT语句的WHERE子句中。在WHERE子句中使用子查询，应该保证SELECT语句具有与WHERE子句中相同数目的列，通常子查询将返回单个列并且与单个列匹配，如果需要也可以使用过个列。（子查询一般会与IN操作符结合使用）；
• 作为计算字段使用子查询：使用子查询创建计算字段；

14. 联结表–联结（Join）

• 创建联结：规定要联结的所有表以及他们如果关联即可；
  注1：在联结时两表有同名列的时候需要使用完全限定的列名在；
  注2：在联结的时候如果没有给定条件，其结果为笛卡尔积，即两个表行数的乘积；
• 内部联结：即等值联结，基于两个表之间某列相等进行的联结。也可以采用INNER JOIN xxx ON 条件来进行关联；
• 联结多个表，MySql在运行时关联指定的每个表处理联结，可能非常耗费资源，对性能的影响很大；
• 自联结：同一张表通过表别名自己和自己进行联结查询（一般情况下自联结比子查询要快）；
• 自然联结：会排除多次出现的列，是每个列只返回一次；
• 外部联结：使用outer join来指定联结类型，它可以包括没有关联行的行，在使用OUTER JOIN时必须使用 RIGHT 或 LEFT 关键字指定包括所有行的表（ RIGHT指出的是OUTER JOIN右边的表，LEFT相反）;
• 使用带有聚合函数的联结；
• 使用联结和联结条件：
  1. 注意所使用的联结类型；
  2. 保证使用正确的联结条件；
  3. 应该总是提供联结条件，否则返回笛卡尔积；
  4. 一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型；

15. 组合查询

MySql中允许执行多个查询，并将结果作为单个查询结果集返回——并
有以下情况一般使用组合查询

• 在单个查询中从不同的表返回类似结构的数据；
• 对单个表执行多个查询按单个查询返回数据；
  注：在多数情况下，组合相同标的两个查询完成的工作与具有多个WHERE子句条件的单条查询完成的工作相同，但是两种查询的性能是不相同的。
• 创建组合查询：使用 UNION操作符来组合数条SQL查询。
• UNION 的使用规则
  1. UNION 必须由两条或两条以上的SELECT语句组成，语句之间用关键字 UNION进行分割；
  2. UNION中的每个查询必须包含相同的列、表达式或聚集函数（列的次序不必相同）；
  3. 列数据类型必须是相互兼容的，即类型不必完全相同，但是DBMS可以银行地转换类型；
• 在UNION中会默认去掉重复的行，如果想重复行被保留可以使用UNION ALL；
• 对结果进行排序：在组合查询中只能使用一条ORDER BY子句，且必须出现在最后一条查询语句之后；

16. 全文本搜索

注：并不是所有的搜索引擎都支持全文本搜索，常见的两个引擎中 MyISAM 是支持全文本搜索的而InnoDB是不支持的。

• 使用全文搜索：要进行全文本所有必须索引被搜索的列，且要随着数据的改变不断的重新索引。在建立索引后SELECT是可与Match()和Against()一起使用以实际执行搜索；
• 进行搜索：在使用时Match()用于指定搜索的列，Against()指定要使用的搜索表达式。其中Match() 的值必须与FULLTEXT()定义相同，如果指定多个列，则必须列出他们且次序相同；
  注：搜索中不适用BINARY是不区分大小写的。
• 布尔文本搜索：以布尔方式可以提供关于如下内容的细节 （在没有索引的情况下也可以使用）：
  • 要匹配的词；
  • 要排斥的词；
  • 排列提示（指定某些词比其他词更重要）；
  • 表达式分组；
  • 另外一些内容；
    使用语句 IN BOLLEANMODE ；
• 全文本搜索的使用说明：
  • 在索引全文本数据时，短词被忽略且从索引中排除；（注：短词的数目默认是 3，但是可以更改）
  • MySql带有内建的非用词列表，这些词在索引全文本数据时是被忽略的；（注：如果有需要可以覆盖列表）
  • 许多词出频率出现的很高，如果一个词在50%以上的行中出现则会将它视作非用词，在布尔文本搜索时不会；
  • 如果表中的行数少于3行，全文本搜索不返回结果；
  • 忽略词中的引号；
  • 不具有词分割符的语言不能恰当的返回全文本搜索结果（汉语日语等）；

17. 插入数据

• 插入数据一般有以下几种情况：
  1. 插入完整的行；
  2. 插入行的一部分；
  3. 插入多行；
  4. 插入某些查询的结果；
• 如果在INSERT语句中表名后没有指定列名则在VALUES子句中给出的每个列必须提供值，没有的话可以使用 NULL。
• 省略列：如果表的定义允许则可以在INSERT操作中省略某些列，列必须满足下面条件中的一个：
  1. 列定义为允许 NULL值；
  2. 在表定义的时候给定了默认值；
• 提高性能：可以在 INSERT 和 INTO 语句之间添加 LOW_PRIORITY降低语句优先级;
• 插入多行的时候在VALUES中用小括号包着每组值，且括号间用逗号分割；
• 插入检索出的数据时，INSERT语句中的每个列都要和 SELECT语句中的列相对应
  例：INSERT INTO STUDENT(sno,sname) SELECT sno, sname From STUDENTNEW;
  注：INSERT中的列名不需要相同，MySql中只匹配列的位置。

18. 更新和删除数据

更新数据：使用UPDATE语句

• 可以采用以下两种方式：
  1. 更新表中的特定行；
  2. 更新表中的所有行；（注：在不给定条件的情况下会更新所有行）
• 更新语句：UPDATE 表名 SET 列名=xxx WHERE 条件；
• INGORE关键字：如果执行更新语句时，有多行被修改，那么但某一行出现错误时整条语句就会发生回滚，可以通过使用此关键字让语句在发生错误时继续执行；

删除数据：使用 DELETE语句

• 可以采用以下两种方式：
  1. 删除表中的特定行；
  2. 删除表中的所有数据；（注：在不给定条件的情况下会删除所有行）
• 如果想要更快的删除表中的所有行推荐使用 TRUNCATE TABLE语句，该语句会删除原来的表并重新创建一个新的表；

删除和更新的指导原则

• 除非确实打算更新或删除一行，否则不要使用不带 WHERE子句的删除或更新语句；
• 保证每个表都有主键；
• 在使用删除或更新语句之前，应当先查询一下所要操作即记录；

19. 创建和操纵表

创建表 ：使用 CREATE TABLE

• 创建表的时候要给出下列信息：
  1. 新表的名字，在关键字 TABLE 之后给出；
  2. 表列的名字和定义，用逗号分割;
• 处理现有表：在创建新表的时候指定的表名必须要求是不存在数据库当初的，否则会出错，可以通过在表名后加上 IF NOT EXISTS来避免，该语句只有在创建的表不存在时才会执行表创建；

使用 NULL值

• 列 NULL在定义的时候要给出，指定为 NOT NULL 的列在插入时是允许插入空串的即' '。空串在 NOT NULL 列中是允许的，它是一个有效的值。如果要插入 NULL值，则要使用关键字 NULL；

使用 AUTO_INCREMENT

• AUTO_INCREMENT: 在告诉数据库本列每增加一行就要自动增量；
• 可以使用last_insert_id()获取最后一个自增的ID值；

**使用 DEFAULT**指定默认值

• 不允许函数，与大多数的DBMS不同，在MySql中不允许使用函数作为默认是，支持处常量；
• 要使用默认值而不是 NULL值；

引擎类型

• InnoDB：是一个可靠的事物处理引擎；
• MEMORY：功能等同于MyISAM，由于数据存储在内存，速度很快适用于临时表；
• MyISAM：是一个性能极高的引擎，支持全文本搜索，但不支持事物处理；
  注：引擎是可以混用的，但是外键不可以跨引擎；

更新表：使用ALTER TABLE

• 必须给出下面的信息：
  1. 更改的表名；
  2. 更改的列表；
• 在更新表的时候要对表进行备份！

删除表：使用DROP TABLE 表名

重命名表：使用RENAME TABLE 新表名 TO 旧表名

20. 使用视图

为什么使用视图：

• 重用SQL语句；
• 简化复杂的SQL操作；
• 使用表的组成部分而不是整个表；
• 保护数据；
• 更改数据格式和表示；
  注：因为视图不包含任何一个数据，所以每次使用视图都会包含一个查询，当用多个视图联结时会降低性能。

视图的规则和限制

• 视图的名称必须唯一；
• 可以创建的视图数目没有限制；
• 创建视图需要有足够的权限；
• 视图可以进行嵌套；
• ORDER BY可以用在视图中，但如果在检索中也含有 ORDER BY 则会覆盖视图中的 ORDER BY；
• 视图不可以索引；
• 视图可以和表一起使用；
• 视图中的 WHERE子句与在查询使用 WHERE子句会自动进组合；

使用视图

• 视图的创建： CREATE VIEW xxx；
• 删除视图：DROP VIEW xxx；
• 更新视图：可以选择想删除再重新创建，也可以使用 CREATE OR REPLACE VIEW语句进行创建 ；

更新视图
如果MYSQL不能正确的确定被更新的基数据，则不允许更新，这意味着在视图中有以下操作则视图的数据不会更新：

• 分组（使用 GROUP BY 和 HAVING）
• 联结；
• 子查询；
• 并；
• 聚集函数；
• DISTINCT；
• 导出（计算）列；

21. 存储过程

• 引入存储过程的原因：

  1. 通过吧处理封装在容易使用的单元中，简化复杂的操作；
  2. 简化对变动的管理；
  3. 提高性能（使用存储过程比单独的SQL要快）；
  4. 可以编写功能更强的代码。

• 使用存储过程

  1. 执行存储过程：CALL xxx参数）;其中xxx为存储过程的名称；
  2. 创建存储过程：
     CREATE PROCEDURE xxxx( 参数)
BEGIN
语句；
END

• 删除存储过程：DROP PROCEDURE IF EXISTS xxxx

• 检查存储过程：SHOW PROCEDURE STATUS LIKE ‘名称’

22. 使用游标

• 游标的功能：可以在检索出来的行中前进或后退一行或多行；
  注：在MySql中游标只能用于存储过程；

• 使用游标

  1. 在能够使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，只是定义要使用的SELECT语句；
  2. 一旦声明后，必须打开游标以供使用；
  3. 对于添有数据的游标，根据需要取出各行；
  4. 在结束游标使用时，必须关闭游标。

• 创建游标：DECLARE xxx CURSOR FOR SELECT ...

• 打开/关闭游标：OPEN/CLOSE 游标名；

23. 触发器

• 创建触发器：CREATE TROGGER xxx [BEGIN|AFTER] [INSRT|DELETE|UPDTE] on [表名] for each row ...

  1. 唯一的触发器名称
  2. 触发器关联的表；
  3. 触发器应该响应的活动（ DELETE、 INSERT 、UPDATE）
  4. 触发器执行的时间（BEGIN 、AFTER）

• 删除触发器：DROP TRIGGER xxx；
  注：MySql中触发器不支持使用存储过程

24. 事务管理

• 相关术语

  • 事物（transaction）：一组SQL语句；
  • 回退（rollback）：撤销指定SQL语句的过程；
  • 提交（commit）：将未提交的SQL语句结果写入数据库；
  • 保留点（savepoint）：指事务处理中设置的临时占位符，可以对它发布回退）；
    一般情况下MySql 提交操作是自动的，但是在事务处理块中不会；

• 使用

  • 在执行SQL语句前启动事务：START TRANSACTION；
  • 在执行COMMIT或 ROLLBACK 语句后事务会关闭
  • 使用保留点：SAVEPOINT xxx
  • 回退保留点：ROLLBACK TO xxx
  • 释放保留点：RELEASE SAVEPOINT
    (注：MySql在执行一次 COMMIT 或 ROLLBACK 后会自动释放保留点)

• 更改默认的提交行为：SET autocommit = 0

25. 全球化和本地化

• 字符集和校对
  • 查看字符集和默认校对：SHOW CHARACTER SET;
  • 查看可用的校对和适用的字符集：SHOW COLLATION；

26. 补充

• 用户表：MySql中的用户存在名为mysql的数据库中，用户名存储在 user表中；
• 创建用户账号：CREATE USER xxx IDENTIFIED BY 'pw';口令不一定要在创建用户的时候指定；
• 查看用户权限：SHOW GRANTS FOR xxx;
• 授权：GREAN [权限] ON [数据库|表] TO xxx; .* 表示所有
• 收回授权：REVOKE [权限] ON [数据库|表] FROM xxx;
• 为用户修改口令：SETPASSWORD FOR xxx = Password('pw');;
• 检查表键是否正确：ANALYZE TABLE xxx；；
• 检查表：CHECK TABLE xxx;，可以针对许多问题对表进行检查（索引状态等）；
• -数据库日志：会输出在安装目录下的data目录中
  • 错误日志：hostname.err；
  • 查询日志：hostname.log；
  • 二进制日志：hostname-bin;
  • 缓慢查询日志：hostname-slow.log；

27. 优化

1. 使用恰当的数据库配置，包括但不限于内存、缓冲区等；
2. 在数据库性能不好使可以使用SHOW PROCESSLIST查看当前所有连接的进程；
3. 使用EXPLAIN语句可以查看MySql执行语句的策略；
4. 一般来说存储过程比一条一条的SQL执行的要快；
5. 使用正确的数据类型；
6. 不要使用 SELECT *
7. 导入数据的时候要关闭自动提交，最好先删除索引，在导入后重新建立；
8. 必须索引数据库表改善数据库的检索性能；
9. 使用多条的SELECT语句+UNION 可以有效的改善SELECT语句中OR的性能问题；
10. 索引会改善数据库检索的性能，但是会损失插入、修改和更新的性能；
11. 一般来说使用LIKE会很慢；
12. 数据库是一个不断变化的实体，一组优化良好的表可能一会就会面目全非，随着表的改变理想的优化和配置也在改变；
13. 重要的规则就是，每条规则在某些条件下都会被打破；