第12章: 汇总数据 (我们常常需要汇总数据而不是把检索到的数据表示出来)
12.1 聚集函数: 运行在行组上,计算和返回单个值的函数。
- AVG() 返回某列的平均值
- COUNT() 返回某列的行数
- MAX() 返回某列的最大值
- MIN() 返回某列的最小值
- SUM() 返回某列值之和
12.2 AVG()
- 只作用于单个列, 并且列名必须作为函数参数给出。
- 如果要获得多个列的平均值,必须使用多个AVG()函数。
- 忽略列值为NULL的行。
12.3 COUNT()
- 有两种使用方式:
- 使用COUNT(*)对表中行的数目进行计数,不管表列中包含的是空值(NULL)还是非空值。
- 使用COUNT(column)对特定的列中具有值的进行计数,忽略NULL值。
12.4 MAX()
- 返回指定列中的最大值。 MAX()要求指定列名。
- 对于数值列,返回最大的数。 对于有序的字符串列,返回最后一行。
忽略列值为NULL的行。
12.5 MIN() 返回指定列中的最小值。 MIN()要求指定列名
- 对于数值列,返回最小的数。 对于有序的字符串列,返回最前面一行。
- 忽略列值为NULL的行。
12.6 SUM() 用来返回指定列值的和(总计)。
- 忽略列值为NULL的行。
12.7 聚集不同值
- 以上五个不同的聚集函数均可以如下使用:
- 对所有的行进行计算,指定ALL参数或不给参数(默认行为)
- 只包含不同的值, 指定DISTINCT参数。
12.8 取别名
- 在指定别名时最好不要使用表中实际的列名作为别名,这样会使得SQL语句更易于理解和使用。
12.9 使用聚集函数来汇总数据比在应用程序中汇总数据效率高得多。(因为这些函数是为此而专门高效设计的)
第13章 分组数据
13.1 GROUP BY 创建分组
- GROUP BY子句可以包含任意数目的列。
- GROUP BY子句中列出的每个列都必须是检索列或者是有效的表达式(但不能使聚集函数)
- 除聚集计算语句外,SELECT语句中的每一列都必须在GROUP BY子句中给出。
- 如果分组列中具有NULL值,则NULL将作为一个分组返回。 多个NULL行将被分为一组。
- GROUP BY子句必须出现在WHERE子句之后,ORDER BY子句之前。
13.2 HAVING 过滤分组
- HAVING支持WHERE所有的操作符,两者的区别仅在于WHERE过滤指定的行而HAVING过滤指定的分组。另一种理解方法是:WHERE在数据分组前进行过滤,HAVING在数据分组后进行过滤。
13.3 不要忘记ORDER BY
- 一般在使用GROUP BY子句时,应该也给出ORDER BY子句。这是保证数据正确排序的唯一方法。
- 千万不要仅依赖GROUP BY排序数据。
13.4 SELECT子句顺序
顺序 关键字 过滤等级
1 SELECT
2 FROM
3 WHERE 行级过滤
4 GROUP BY
5 HAVING 组级过滤
6 ORDER BY
7 LIMIT
第14章 使用子查询
14.1 格式化SQL
- 包含子查询的SELECT语句难以阅读和调试,特别是它们较为复杂的时候更是如此。子查询分解为多行并且适当的进行缩进,能极大的简化子查询的使用。
14.2 列必须匹配
- 在WHERE语句中使用子查询时,应该保证SELECT语句具有与WHERE语句中相同的列。
14.3 不止一种解决方案
14.4 逐渐增加子查询来建立查询
- 首先,建立和测试最内层的查询。
- 然后,用硬编码数据建立和测试外层查询,并且仅在确认它正常后才嵌入子查询。
- 最后,再次测试。
第15章 联结表
15.1 完全限定列名
- 在引用的列可能出现二义性时,必须使用完全限定列名。
15.2 不要忘了WHERE子句
15.3 INNER JOIN … ON …
15.4 性能考虑:
- MySQL在运行时关联指定的每个表以处理联结。这种处理可能是非常耗费资源的。因此不要联结不必要的表。
第16章 创建高级联结
16.1 使用表别名
- 优点
- 缩短SQL语句
- 允许在单条SELECT语句中多次使用相同的表
- 表别名只在查询执行中使用。 与列别名不一样,表别名不返回到客户机。
16.2 使用不同类型的联结
- 自联结
- 有时候自联结处理效率比子查询高得多。
- 自然联结
- 外部联结 OUTER JOIN … ON …
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
16.3 使用联结和联结条件
- 注意所使用的联结类型。 一般使用内部联结,但使用外部联结也是有效的。
保证使用正确的联结条件。 - 应该总是提供联结条件,否则会得出笛卡尔积。
- 在一个联结中可以包含多个表。
第17章 组合查询
17.1 需要用到组合查询的情况:
- 在单个查询中从不同的表返回类似结构的数据
- 对单个表执行多个查询,按单个查询返回数据
17.2 组合查询和多个WHERE条件
任何具有多个WHERE条件的SELECT语句都可以作为一个组合查询给出
17.3 创建组合查询 UNION
使用关键字UNION来联结多个SELECT语句
17.4 UNION规则
- UNION必须由两条或以上的SELECT语句构成,语句之间用UNOIN分隔;
- UNOIN中的每个查询必须包含相同的列、表达式或聚集函数(对列出现的次序没有要求);
- 列数据类型必须兼容;类型不必完全相同, 但必须是DBMS可以隐含转换的类型。
17.5 UNION会自动去除重复的行(与集合Set类似)
17.6 UNOIN ALL不会去除重复的行
17.7 组合查询中只允许出现一次ORDER BY语句,其出现位置在最后一条SELECT语句之后。
17.8 使用UNOIN的组合查询可以应用不同的表。
第18章 全文本搜索
- 两个最常使用的数据库引擎:MyISAM(支持)和InnoDB(不支持)。
- 使用FULLTEXT关键字表明指定列启用全文本搜索。
- 使用Match(column_name)和Against(word)来进行全文本搜索。其中column_name必须和FULLTEXT指定的列名相同。
- 不要在导入数据时使用FULLTEXT。如果正在导入数据到一个新表,此时不应该启用FULLTEXT,应该先导入所有的数据,然后再修改表。
- 搜索不区分大小写(除非使用BINARY方式)
- 文本中搜索词靠前的行的等级值比搜索词靠后的行的等级值高。
第19章 插入数据 INSERT
19.1 数据插入的方式:
- 插入完整的行
- 插入行的一部分
- 插入多行
- 插入某些查询的结果
19.2 插入完整的行
INSERT INTO table_name(column1, column2, ...) VALUES (value1, value2, ...);
- 省略列需要满足一下两个要求之一:
- 该列定义为允许NULL值
- 在表定义中给出默认值。 这表示如果不给出值,将使用默认值。
- 同时有INSERT和SELECT操作时, 可使用INSERT LOW_PRIORITY INTO 降低INSERT语句的优先级以提高效率。
19.3 插入多个行
- 使用多个INSERT语句,用分号隔开。
- 使用单个INSERT语句多个VALUES子句,子句之间用逗号隔开。 比前者效率高。
19.4 插入检索的数据
INSERT INTO table_name1(column1, column2, ...)
SELECT ...
FROM table_name2;- INSERT SELECT中的列名
- MySQL不关心SELECT返回的列名。它使用的是列的位置,即SELECT结果的第一列(不管其列名)将用来填充到INSERT中指定的第一列,其他列也是这样。
第20章 更新和删除数据
20.1 更新数据 UPDATE
UPDATE table_name SET column_name1 = value1, column_name2 = value2, ... WHERE filter_condition
- 在UPDATE中可以使用子查询,可用SELECT语句检索出来的数据更新列数据。
- IGNORE关键字
- 如果用UPDATE更新多行,并且在更新时出现了错误,则整个UPDATE操作被取消。错误发生前更新的所有行恢复到它们原来的值。
- 如果使用UPDATE IGNORE, 即使是发生了错误,也能继续更新。
20.2 删除数据 DELETE
DELETE FROM table_name WHERE filter_conditions
- DELETE删除的是表的内容而不是表本身
- 如果想删除表中的所有数据,TRUNCATE TABLE比DELETE更快。
20.3 更新和删除的指导原则:
- 除非确实打算更新和删除每一行,否则一定不要忘了带上WHERE子句
- 保证每个表都有主键
- 在对UPDATE或DELETE语句使用WHERE子句,应该先用SELECT对WHERE子句进行测试,以保证过滤的记录是正确的
- 使用强制实施引用完整性的数据库,这样MySQL将不允许删除具有与其他表相关联的数据的行。
第21章 创建和操作表
21.1 创建表
- 表创建基础
CREATE TABLE table_name;
- 语句格式化: 使用换行和缩进
- 在创建新表时,指定的表名必须不存在,否则将出错。
- 使用IF NOT EXISTS实现仅在表不存在的时候创建该表的功能。
- 使用NULL值
- NULL不是空串,是no value,与”不一样。
- 使用AUTO_INCREMENT
- 每个表只允许一个AUTO_INCREMENT列
- 覆盖AUTO_INCREMENT
- 可以在INSERT中指定AUTO_INCREMENT列的值,只要该值在AUTO_INCREMENT列中不存在即可。后续的增量将使用该手工插入的值。
- 确定AUTO_INCREMENT的值
- last_insert_id() 获得最近的一个自增id
- 默认值
- MySQL不允许函数作为默认值
- 引擎类型
- InnoDB是一个可靠的事务处理引擎, 它不支持全文本搜索;
- MEMORY在功能上等同于MyISAM,但由于数据存储在内存中,速度很快,特别适合与临时表;
- MyISAM是一个性能极高的引擎,它支持全文本搜索,但不支持事务处理。
- 引擎类型不能混用
- 外键不能跨引擎
21.2 更新表
- 更新表的定义: ALTER TABLE
- 理想状态下,当表中存储数据后,该表就不应该再被更新。 所以在表的设计过程中应该花费大量时间来考虑,以便后期不对该表进行大的改动。
- 添加列: ALTER TABLE table_name ADD new_column_name column_type;
- 删除列: ALTER TABLE table_name DROP COLUMN column_name;
- 使用ALTER TABLE前应该进行备份。
21.3 删除表
DROP TABLE table_name;
- 删除表操作没有确认过程,也不能撤销。
21.4 重命名表
RENAME TBALE table_name1 TO new_table_name1, table_name2 TO new_table_name2, ...;