代码改变世界

数据库中的几组概念

2012-08-19 16:33  coodoing  阅读(694)  评论(0)  编辑  收藏  举报

1、mysql中内存表和临时表

    在介绍内存表和临时表之前，必须明确的一点就是：

mysql中临时表的表建在内存里，数据在内存里 ；内存表的表建在磁盘里，数据在内存里 。

两种表结构的创建方式

1.临时表:表建在内存里，数据在内存里；
2.内存表:表建在磁盘里，数据在内存里。

临时表


mysql> create temporary table tmp1(id int not null);
Query OK, 0 rows affected (0.00 sec)
mysql> show create table tmp1;
+-------+----------------------------------------------------------------------------------------------+
| Table | Create Table                                                                               |
+-------+----------------------------------------------------------------------------------------------+
| tmp1   | CREATE TEMPORARY TABLE `tmp1` ( `id` int(11) NOT NULL)

ENGINE=MyISAM DEFAULT CHARSET=utf8    |
+-------+----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

内存表

mysql> create table tmp2(id int not null) TYPE＝HEAP;
Query OK, 0 rows affected (0.00 sec)
mysql> show create table tmp2;
+-------+------------------------------------------------------------------------------------+
| Table | Create Table                                                                       |
+-------+------------------------------------------------------------------------------------+
| tmp2   | CREATE TABLE `tmp2` (
   `id` int(11) NOT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

可以看出来临时表和内存表的ENGINE 不同，临时表默认的是MyISAM,而内存表是MEMORY 。

两种表的区别

内存表：
1. 参数控制：max_heap_table_size
2. 到达上线后报错。
3. 表定义保存在磁盘上，数据和索引保存在内存里面。
4. 不能包含TEXT,BLOB等字段。
临时表：
1. 参数控制：tmp_table_size。
2. 到达上线后创建文件在磁盘上。
3. 表定义和数据都在内存里。
4. 可以包含TEXT, BLOB等字段。

最后：做mysql主从复制的时候，mysql可以同步内存表，但是不能同步临时表！

     除了mysql中可以支持临时表外：SQL Server 也支持临时表。临时表就是那些名称以井号 (#) 开头的表。如果当用户断开连接时没有除去临时表，SQL Server 将自动除去临时表。临时表不存储在当前数据库内，而是存储在系统数据库 tempdb 内。
sql中临时表有两种类型：
1、本地临时表
      以一个井号 (#) 开头的那些表名。只有在创建本地临时表的连接上才能看到这些表。
2、全局临时表
      以两个井号 (##) 开头的那些表名。在所有连接上都能看到全局临时表。如果在创建全局临时表的连接断开前没有显式地除去这些表，那么只要所有其它任务停止引用它们，这些表即被除去。当创建全局临时表的连接断开后，新的任务不能再引用它们。

2、聚集索引和非聚集索引

     聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致。聚簇索引非常象目录表，目录表的顺序与实际的页码顺序是一致的。非聚簇索引则更象书的标准索引表，索引表中的顺序通常与实际的页码顺序是不一致的。一本书也许有多个索引。例如，它也许同时有主题索引和作者索引。同样，一个表可以有多个非聚簇索引。通常情况下，你使用的是聚簇索引。

     每个表只能有一个聚簇索引。因为一个表中的记录只能以一种物理顺序存放。通常你要对一个表按照标识字段建立聚簇索引。但是，你也可以对其它类型的字段建立聚簇索引，如字符型，数值型和日期时间型字段。从建立了聚簇索引的表中取出数据要比建立了非聚簇索引的表快。当你需要取出一定范围内的数据时，用聚簇索引也比用非聚簇索引好。例如，假设你用一个表来记录访问者在你网点上的活动。如果你想取出在一定时间段内的登录信息，你应该对这个表的DATETIME型字段建立聚簇索引。

      对聚簇索引的主要限制是每个表只能建立一个聚簇索引。但是，一个表可以有不止一个非聚簇索引。实际上，对每个表你最多可以建立249个非聚簇索引。你也可以对一个表同时建立聚簇索引和非聚簇索引。假如你不仅想根据日期，而且想根据用户名从你的网点活动日志中取数据。在这种情况下，同时建立一个聚簇索引和非聚簇索引是有效的。你可以对日期时间字段建立聚簇索引，对用户名字段建立非聚簇索引。如果你发现你需要更多的索引方式，你可以增加更多的非聚簇索引。
      非聚簇索引需要大量的硬盘空间和内存。另外，虽然非聚簇索引可以提高从表中 取数据的速度，它也会降低向表中插入和更新数据的速度。每当你改变了一个建立了非聚簇索引的表中的数据时，必须同时更新索引。因此你对一个表建立非聚簇索引时要慎重考虑。如果你预计一个表需要频繁地更新数据，那么不要对它建立太多非聚簇索引。另外，如果硬盘和内存空间有限，也应该限制使用非聚簇索引的数量。详细说明可参考： http://hi.baidu.com/guobeilei/blog/item/51f55afbda311e116c22eb0e.html

聚集索引的应用场景

聚集索引一般建立在：
1、最频繁使用的、用以缩小查询范围的字段上；
2、最频繁使用的、需要排序的字段上。

      建议使用聚集索引的场合为：
a.此列包含有限数目的不同值；
b.查询的结果返回一个区间的值；
c.查询的结果返回某值相同的大量结果集。
建议使用非聚集索引的场合为：
a.此列包含了大量数目不同的值；
b.查询的结束返回的是少量的结果集；
c.order by 子句中使用了该列。

3、存储过程和函数

具体区别参考上一篇文章：存储过程与函数的区别。

4、Sql server中的公共表表达式(CTE)

      微软从SQl2005起引入了CTE(Common Table Expression)以强化T-SQL。CTE可以看作是一个临时的结果集，可以在接下来的一个SELECT,INSERT,UPDATE,DELETE,MERGE语句中被多次引用。使用公用表达式可以让语句更加清晰简练。除此之外，根据微软对CTE好处的描述，可以归结为四点:

  •      可以定义递归公用表表达式(CTE)
  •      当不需要将结果集作为视图被多个地方引用时，CTE可以使其更加简洁
  •     GROUP BY语句可以直接作用于子查询所得的标量列
  •     可以在一个语句中多次引用公用表表达式(CTE)
公用表表达式(CTE)的定义
    公用表达式的定义非常简单，只包含三部分：
  1.   公用表表达式的名字（在WITH之后）
  2.   所涉及的列名（可选）
  3.   一个SELECT语句(紧跟AS之后)

    在MSDN中的原型：

   1: WITH expression_name [ ( column_name [,...n] ) ] 
   2: AS 
   3: ( CTE_query_definition )

    按照是否递归，可以将公用表（CTE）表达式分为递归公用表表达式和非递归公用表表达式。

非递归公用表表达式(CTE)
   非递归公用表表达式（CTE）是查询结果仅仅一次性返回一个结果集用于外部查询调用。并不在其定义的语句中调用其自身的CTE。   非递归公用表表达式（CTE）的使用方式和视图以及子查询一致。

递归公用表表达式(CTE)
    递归公用表表达式很像派生表（Derived Tables ），指的是在CTE内的语句中调用其自身的CTE.与派生表不同的是，CTE可以在一次定义多次进行派生递归。

5、case...when...then...else...end

     在dynamic sql'中，需要动态的在结果中添加column‘来显示不同的要求，这种情况下，就可以利用上述语法进行解决。

     已知表Student：

0YMJCF_Z()05_82$R96WP5R

     请用sql语句，转换为以下格式：

%6OS}DC)@_ES0A(O$(~YVAA

      相应的sql语句为：

   1: select name 姓名,
   2: max(case subject when '语文' then result else 0 end) 语文,
   3: max(case subject when '数学' then result else 0 end) 数学,
   4: max(case subject when '英语' then result else 0 end) 物理
   5: from student
   6: group by name