基础Sql语句之二

汇总数据

函数	说明	实例
avg()	返回某列的均值	select avg(C0) from T0
count()	返回某列的列数	select count(C0) from T0
max()	返回某列的最大值	select max(C0) from T0
min()	返回某列的最小值	select min(C0) from T0
sum()	返回某列的和	select sum(C0) from T0

• 以上函数均忽略列值为null的行，除了 count(*)，其会对表中行的数目进行计数，不管列中包含的是否是null
• 以上五个聚集函数默认都会使用all，也可以显示的使用distinct参数来忽略相同的值
  select count(distinct C0) from T0
  
  

分组数据

• 分组允许把数据分为多个逻辑组，以便对每个组进行聚集运算
• 利用group by进行分组

• group by子句可以包含任意数目的列，使得能对分组进行嵌套
• 如果在group by子句中嵌套了分组，数据将在最后规定的分组上进行汇总
• 分组列中具有null值，则null将作为一个分组返回
• with rollup可以得到每个分组以及每个分组汇总级别的值
• having用于过滤分组
• gourp by关键字可以和group_concat()函数一起使用。group_concat()函数会把每个分组中指定的字段值都显示出来

使用子查询

• 子查询:嵌套在其他查询中的查询，在select语句中子查询总是自内而外进行的
• 相关子查询:涉及外部查询的子查询
• 当列名可能存在多义性，就必须使用完全限定表名

联结表

• 相同数据出现多次不是一件好事，此因素是关系型数据库设计的基础。关系表的设计就是要保证把信息分解成多个表，一类数据一个表。各表通过某些常用的值互相关联
• 外键:外键为某个表的一列，它包含另一个表的主键值，定义了两个表之间的关系。

• 联结是一种机制，用来在一条select语句中关联表。

  
  

• 内部联结:基于两个表之间的相等测试，对于这种联结可以使用 inner join on

  
  
• sql对一条select语句可以联结的表的数目没有限制。不要联结不必要的表，联结的表越多，性能下降的越厉害
• 笛卡尔积: 由没有联结条件的表关系返回的结果成为笛卡尔积。检索出的行的数目为第一个表中的行数乘以第二个表的行数
  
  

创建高级联结

• 别名除了用于列名和计算字段外，sql还允许给表名起别名，用于缩短sql语句，并且允许在单条select语句中多次使用相同表

• 自联结

  
  

• 自然联结(natural join)是一种特殊的等值联结，它要求两个关系中进行比较的分量必须是相同的属性组，并且在结果中把重复的属性列去掉。而等值连接并不去掉重复的属性列

  
  
• 外部联结:联结包含那些在相关表中没有关联行的行，使用关键字 right / left outer join
  
  
  

组合查询

• 有两种情况需要用到组合查询:
  A:在单个查询中从不同表返回类似的结构的数据
  B:在单个表执行多个查询，按单个查询返回结果
• 使用union来实现组合查询，规则:
  A:union必须由两条或以上的select语句组成，语句之间使用union分隔
  B:union的每个查询都必须包含相同列、表达式或聚集函数(列的顺序可以不同)
  C:列数据类型必须兼容:类型不必完全相同，但必须是能隐式转换的类型
• union会从结果集中去除重复行，使用union all 可以返回所有匹配行
• 使用union组合查询的时候，只能使用一条order by子句，它必须出现在最后一条select语句后

全文本搜索

• 使用like和regexp进行文本的搜索有几个缺点:
  A:性能不高，通配符和正则表达式通常要求mysql尝试匹配表中所有行，由于行数多，这些搜索可能很耗时
  B:使用正则表达式和通配符很难明确控制
  C:虽然基于通配符和正则表达式的搜索提供了非常灵活的效率，但它们都不能提供一种智能化的选择结果

• 一般在创建表时启用全文本搜索。create table语句接受fulltext子句。不要在导入数据时使用fulltext，而应该首先导入数据，然后定义fulltext，这样效率比较高

  
  

• 在索引之后，使用match()指定被搜索的列，against()指定要使用的搜索表达式。
  传递给match的值必须与fulltext中定义的相同，而且若指定多个列，列的顺序也要正确
  全文本搜索的一个重要部分是对结果排序，具有较高等级的行先返回，文中词靠前的行的等级比词靠后的行高

  
  

• 使用扩展查询(with query expansion):
  mysql对数据和索引进行两边扫描来完成搜索，首先找出与搜索条件匹配的所有行，其次mysql检查这些匹配行并选择所有有用的词，然后在进行一次全文本搜索，不仅使用原来的条件，而且还使用所有有用的词。扩展查询增加了返回的行数，但是也返回了一些没用的行

  
  
• 布尔文本搜索(in boolean mode)
• 在布尔方式中，不按等级值降序排序返回行，即只排列而不排序
  可以在非fulltext列中进行布尔搜索，但是效率很低
• 全文本搜索说明:
  A:在索引全文本数据时，短词将被从索引中排除。短词定义为具有3个及以下的词(数目可更改)
  B:mysql带有一个内建的非用词列表，这些词在索引中总是被忽略(此表可被覆盖)
  C:如果一个词在行中出现的频率大于百分之50，则将其作为非用词进行忽略。此规则不用于布尔搜索中
  D:仅在myisam数据库引擎中支持全文本搜索
  E:对于汉语不能恰当的返回全文本搜索结果
  F:忽略词中的引号，如don't索引为dont

布尔操作符	说明
+	包含，词必须存在
-	排除，词必须不存在
>	包含，增加等级
<	包含，减少等级
()	把词组成表达式
~	取消一个值的排序值
*	词尾通配符
""	定义一个短语，与单个词的列表不同，它将匹配整个短语

插入数据

• insert用来插入行到数据库表中。插入可以以下几种方式进行:
  A:插入完整行
  B:插入行的一部分
  C:插入多行
  D:插入查询结果
• 插入时候最好提供列名，依赖列定义的顺序是不安全的
  insert into T0 (C0, C1, C2) values (V0, V1, V2)
• 如果表的定义中，某列允许被定义为NULL或者有默认值，则可以在insert中省略该列。可以在插入的时候指定低优先级的插入(在update和delete时候也生效):
  insert low_priority into T0 (C0, C1, C2) values (V0, V1, V2)
• 插入多行(比分次插入效率高)
  insert low_priority into T0 (C0, C1, C2) values (V0, V1, V2), (V3, V4, V5)
• 插入检索出来的值
  
  
  

更新和删除数据

• 为了更新表中的数据，可以使用update语句。可以更新所有行或者指定行

  
  
• 当更新失败的时候，则整个update被取消，若即使发生错误也继续进行则使用ignore

• 为了删除某个列的值，可以设置其为null(当此列允许为null值时)

  
  
• 为了从表中删除数据，可以使用delete语句，可以从表中删除特定行或者所有行
• 若要删除表内所有数据，一般使用truncate table 语句。此语句实际上是删除原来的表并重新创建一个表，而不是逐行进行删除表内数据
  
  
  

创建和操纵表

• 使用create table语句来创建表，若是仅仅是想在表不存在的情况下创建表可以使用create table if not exists
• 主键值必须唯一，即表中的每个行必须具有唯一的主键。如果主键使用单个列，则其值必须唯一，若主键使用多个列，则这些列的组合必须唯一
• auto_increment告诉mysql，本列每当增加一行时自动增量，每个表只允许一个auto_increment列，而且其必须被索引(如:使其成为主键)。last_insert_id()可以用于获取最后一个auto_increment值
• 如果在插入行时没给出值，mysql允许指定此时使用的默认值，使用关键字default来指定。mysql不允许使用函数作为默认值，只支持常量
• 引擎类型:
  innodb是一个可靠的事务处理引擎，不支持全文本搜索
  memory在功能上等同与myisam，但由于数据存储在内存而非磁盘所以速度很快，适用于临时表
  myisam是一个性能极高的引擎，支持全文本搜索，不支持事务处理。一般使用此引擎
• 引擎可以混用。但是混用引擎有一个缺点:外键不能跨引擎，即使用一个引擎的表不能引用具有使用不同引擎的表的外键

• 更新表:alter table

  
  
• 删除表:drop table
• 重命名表 rename table name0 to name1
  
  

使用视图

• 视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询
• 视图的应用场景:
  A:重用SQL语句
  B:简化复杂的sql语句
  C:使用表的组成部分而不是表
  D:保护数据，可以给用户授予表的特定部分的访问
  E:更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据
• 因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索。如果使用联结和过滤创建了复杂的视图或者嵌套了视图，可以导致性能很差
• 视图可以包含order by语句，但是如果从该视图检索数据的select语句也包含order语句，那么视图中的order语句将被覆盖。视图不能索引，也不能有关联的触发器或或默认值
• 使用create view来创建视图，使用show create view viewname来查看创建视图的语句。使用drop删除视图，语法为:drop view viewname。更新视图的时候可以直接drop后再create或者直接使用create or replace view。show tables 可以看到创建的视图和表
• 一般视图是可以更新的。更新一个视图将更新其基表。如果对视图增加或者删除行，实际上就是对基表进行增减行，当视图包含分组、联结、子查询、并、聚集函数、distinct、导出列时，将不可更新