MySQL学习（三）

创建新的字段

1、字段（field） 基本上与列（column）的意思相同，经常互换使用，不过数据库列一般称为列，而术语字段通常用在计算字段的连接上。
2、拼接----将值联结到一起构成单个值。可使用Concat()函数来拼接两个列。Concat()拼接串，即把多个串连接起来形成一个较长的串。Concat()需要一个或多个指定的串，各个串之间用逗号分隔。字符串需要加单引号，列名不需要加单引号。
3、MySQL支持RTrim() （去掉串右边的空格）、LTrim() （去掉串左边的空格）以及Trim()（去掉串左右两边的空格）。
4、别名----是一个字段或值的替换名。别名用AS关键字赋予。语句中计算字段之后跟了文本AS new_name。它指示SQL创建一个包含指定计算的名为new_name的计算字段，任何客户机应用都可以按名引用这个列，就像它是一个实际的表列一样。导出列 别名有时也称为导出列（derived column），不管称为什么，它们所代表的都是相同的东西。
5、SELECT提供了测试和试验函数与计算的一个很好的办法。例如，SELECT 3*2；将返回6, SELECT Trim(‘abc’) ；将返回abc，而SELECT Now()利用Now()函数返回当前日期和时间。
6、MySQL使用的日期必须为格式yyyy-mm-dd。

分组

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




9、目前为止所学过的所有类型的WHERE子句都可以用HAVING来替代。唯一的差别是WHERE过滤行，而HAVING过滤分组。
10、GROUP BY与ORDER BY：

一般在使用GROUP BY子句时，应该也给出ORDER BY子句。这是保证数据正确排序的唯一方法。千万不要仅依赖GROUP BY排序数据。
11、SELECT子句及其顺序：

12、子查询一般与IN操作符结合使用。

联结表

1、关系表的设计就是要保证把信息分解成多个表，一类数据一个表。各表通过某些常用的值（即关系设计中的关系（relational））互相关联。
2、 外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。如果两个表有相同的一列信息，那么一个表的主键就是另一个表的外键。
3、简单地说，联结是一种机制，用来在一条SELECT语句中关联表，因此称之为联结。
4、联结不是物理实体，在实际的数据库表中不存在。联结由MySQL根据需要建立，它存在于查询的执行当中。
5、维护引用完整性是指在关系列中插入合法的数据。
6、创建联结：SELECT id,name FROM vendor,product WHERE vender.id=product.id; WHERE子句中使用了完全限定列名，在引用的列可能出现二义性时，必须使用完全限定列名（用一个点分隔的表名和列名）。在一条SELECT语句中联结几个表时，相应的关系是在运行中构造的。
7、应该保证所有联结都有WHERE子句。
8、内部联结----INNER JOIN：SELECT id,name FROM vendor INNER JOIN product ON vendor.id=product.id;首选INNER JOIN语法。
9、可以联结多个表，但联结的表越多 性能下降越厉害。
10、WHERE子句中有多个条件时，用AND联结多个条件。
11、可以将子查询修改为使用多表联结的方式进行查询。
12、别名除了用于列名和计算字段外，SQL还允许给表名起别名。这样做有两个主要理由：① 缩短SQL语句；②允许在单条SELECT语句中多次使用相同的表。
13、表别名不仅能用于WHERE子句，它还可以用于SELECT的列表、ORDER BY子句以及语句的其他部分。
14、自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的，但有时候处理联结远比处理子查询快得多。应该试一下两种方法，以确定哪一种的性能更好。
15、无论何时对表进行联结，应该至少有一个列出现在不止一个表中（被联结的列）。标准的联结（前一章中介绍的内部联结）返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次。事实上，迄今为止我们建立的每个内部联结都是自然联结，很可能我们永远都不会用到不是自然联结的内部联结。
16、联结包含了那些在相关表中没有关联行的行。这种类型的联结称为外部联结。在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）。
17、MySQL不支持简化字符*=和=*的使用，这两种操作符在其他DBMS中是很流行的。
18、存在两种基本的外部联结形式：左外部联结和右外部联结。它们之间的唯一差别是所关联的表的顺序不同。换句话说，左外部联结可通过颠倒FROM或WHERE子句中表的顺序转换为右外部联结。因此，两种类型的外部联结可互换使用，而究竟使用哪一种纯粹是根据方便而定。

组合查询----UNION操作符

1、MySQL允许执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。这些组合查询通常称为并（union）或复合查询（compound query）。UNION几乎总是完成与多个WHERE条件相同的工作。
2、组合查询和多个WHERE条件性能不同 多数情况下，组合相同表的两个查询完成的工作与具有多个WHERE子句条件的单条查询完成的工作相同。换句话说，任何具有多个WHERE子句的SELECT语句都可以作为一个组合查询给出，在以下段落中可以看到这一点。这两种技术在不同的查询中性能也不同。因此，应该试一下这两种技术，以确定对特定的查询哪一种性能更好。
3、UNION指示MySQL执行两条SELECT语句，并把输出组合成单个查询结果集。例如：

在这个简单的例子中，使用UNION可能比使用WHERE子句更为复杂。但对于更复杂的过滤条件，或者从多个表（而不是单个表）中检索数据的情形，使用UNION可能会使处理更简单。

4、UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。
5、在使用UNION时，重复的行被自动取消。如果想返回所有匹配行，可使用UNION ALL而不是UNION。
6、在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后。不允许使用多条ORDER BY子句。例如：
这条UNION在最后一条SELECT语句后使用了ORDER BY子句。虽然ORDER BY子句似乎只是最后一条SELECT语句的组成部分，但实际上MySQL将用它来排序所有SELECT语句返回的所有结果。

索引

1、一般在创建表时使用FULLTEXT子句启用全文本搜索。
2、不要在导入数据时使用FULLTEXT 更新索引要花时间，虽然不是很多，但毕竟要花时间。如果正在导入数据到一个新表，此时不应该启用FULLTEXT索引。应该首先导入所有数据，然后再修改表，定义FULLTEXT。这样有助于更快地导入数据（而且使索引数据的总时间小于在导入每行时分别进行索引所需的总时间）。
3、在索引之后，使用两个函数Match()和Against()执行全文本搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索表达式。
4、传递给Match()的值必须与FULLTEXT()定义中的相同。如果指定多个列，则必须列出它们（而且次序正确）。
5、除非使用BINARY方式（本章中没有介绍），否则全文本搜索不区分大小写。
6、全文本搜索的一个重要部分就是对结果排序。具有较高等级的行先返回（因为这些行很可能是你真正想要的行）。LIKE不能排序。例如，如果指定多个搜索项，则包含多数匹配词的那些行将具有比包含较少词（或仅有一个匹配）的那些行高的等级值。
7、查询扩展用来设法放宽所返回的全文本搜索结果的范围。Against(‘xxx’ WITH QUERY EXPANSION)，步骤：①首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有行；②其次，MySQL检查这些匹配行并选择所有有用的词（我们将会简要地解释MySQL如何断定什么有用，什么无用）。③再其次，MySQL再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词。查询扩展极大地增加了返回的行数，但这样做也增加了你实际上并不想要的行的数目。
8、MySQL支持全文本搜索的另外一种形式，称为布尔方式（boolean mode）。Against(‘xxx’ IN BOOLEAN MODE)，但这是一种非常缓慢的操作（其性能将随着数据量的增加而降低）。
9、全文本搜索布尔操作符-和**, -排除一个词，而是截断操作符（可想象为用于词尾的一个通配符）。

在布尔方式中，不按等级值降序排序返回的行。
10、关于全文本搜索的某些重要的说明。

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

事务

1、事务处理（transaction processing）可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。事务处理是一种机制，用来管理必须成批执行的MySQL操作，以保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们或者作为整体执行，或者完全不执行（除非明确指示）。如果没有错误发生，整组语句提交给（写到）数据库表。如果发生错误，则进行回退（撤销）以恢复数据库到某个已知且安全的状态。
2、ROLLBACK只能在一个事务处理内使用（在执行一条START TRANSACTION命令之后）。
3、事务处理用来管理INSERT、UPDATE和DELETE语句。你不能回退SELECT语句。
4、一般的MySQL语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交（implicit commit），即提交（写或保存）操作是自动进行的。但是，在事务处理块中，提交不会隐含地进行。为进行明确的提交，使用COMMIT语句。当COMMIT或ROLLBACK语句执行后，事务会自动关闭（将来的更改会隐含提交）。
5、为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符。这样，如果需要回退，可以回退到某个占位符。这些占位符称为保留点。为了创建占位符，可使用SAVEPOINT语句。保留点越多越好 可以在MySQL代码中设置任意多的保留点，越多越好。为什么呢？因为保留点越多，你就越能按自己的意愿灵活地进行回退。保留点在事务处理完成（执行一条ROLLBACK或COMMIT）后自动释放。自MySQL 5以来，也可以用RELEASE SAVEPOINT明确地释放保留点。
6、为指示MySQL不自动提交更改，需要使用以下语句：SET autocommit=0;

性能优化技巧

1、使用SHOW PROCESSLIST显示所有活动进程（以及它们的线程ID和执行时间）。用KILL命令终结某个特定的进程（使用这个命令需要作为管理员登录）。
2、总是有不止一种方法编写同一条SELECT语句。应该试验联结、并、子查询等，找出最佳的方法。
3、使用EXPLAIN语句让MySQL解释它将如何执行一条SELECT语句。
4、不要用SELECT *（除非你真正需要每个列）。
5、决不要检索比需求还要多的数据。换言之，不要用SELECT *（除非你真正需要每个列）
6、在导入数据时，应该关闭自动提交。
7、必须索引数据库表以改善数据检索的性能。确定索引什么不是一件微不足道的任务，需要分析使用的SELECT语句以找出重复的WHERE和ORDER BY子句。如果一个简单的WHERE子句返回结果所花的时间太长，则可以断定其中使用的列（或几个列）就是需要索引的对象。
8、SELECT语句中有一系列复杂的OR条件时，通过使用多条SELECT语句和连接它们的UNION语句，能获得极大的性能改进。
9、索引改善数据检索的性能，但损害数据插入、删除和更新的性能。如果你有一些表，它们收集数据且不经常被搜索，则在有必要之前不要索引它们。（索引可根据需要添加和删除。）
10、LIKE很慢。一般来说，最好是使用FULLTEXT而不是LIKE。
11、位于http://dev.mysql.com/doc/的MySQL文档有许多提示和技巧（甚至有用户提供的评论和反馈）。一定要查看这些非常有价值的资料。