SQL入门及性能优化

柔光的暖阳◎ 2022-05-25 11:50 325阅读 0赞

学习路线：SQL语句基本用法 -> 数据库重要的基本概念 -> 了解InnoDB内部一些原理知道如何优化数据库

# SQL入门 #

SQL又叫做结构化查询语言，是一种数据库查询操作以及程序设计的语言。  
关系数据库：就是由许多有关系的二维表组成。关系就是二维表之间的关系。  
怎么设计关系数据库？也就是表中的关系如何确定，这是DBA的工作，一般通过E-R模式涉及，开发软件的仅仅需要调用和调优即可。  
包含3部分：

*  数据定义语言 （data definition language）DDL ， create drop alter
 *  数据操作部分（data manipulation language）DML, insert update delete
 *  数据控制语言(data control lang) DCL , grant revoke commit rollback

## 1、数据库例子 ##

下面学习SQL基本操作对应的数据库  
![这里写图片描述][70]

## 2、基本操作 ##

1、show databases; \#显示可用数据库

2、use databasename;  
显示对应的数据库，然后决定使用哪个数据库。

3、show tables;  
显示当前数据库有多少表格。

4、show columns from tablename  
显示一个表的列名，及相应的信息

5、show status;  
显示服务器信息。

6、help show  
用于显示show的帮助。

## 3、检索数据 ##

select cust_id from customers; 
    //从表中检索出cust_id列, ;分号结束SQL语句，SQL不区分大小写。
    
    select cust_id , cust_name , cust_address from customers;
    //从表中检索多个列。
    
    select * from customers; 
    //检索所有列，一般你必须明确使用表中的每个列，通配符将使用性能急剧下降。
    
    select distinct cust_country from customers;
    //检索出cust_country不同的行,distinct告诉MySQL仅仅返回不用的cust_country行
    //distinct 关键字必须放在列名前面
    
    select prod_name from products limit 3;
    //检索前3行
    
    select prod_name from products limit 3,2;
    //检索从第3行开始的后两行。第一个数为开始位置，第二个为检索的行数。
    //带一个值的LIMIT总是从第一行开始，给出的数为返回的行数。
    //带两个值的LIMIT可以指定从行号为第一个值的位置开始
    mysql> select prod_name from products;
    +----------------+
    | prod_name      |
    +----------------+
    | .5 ton anvil   |
    | 1 ton anvil    |
    | 2 ton anvil    |
    | .5 ton anvil   |
    | Detonator      |
    | Bird seed      |
    | Carrots        |
    | Fuses          |
    | JetPack 1000   |
    | JetPack 2000   |
    | Oil can        |
    | Safe           |
    | Sling          |
    | TNT (1 stick)  |
    | TNT (5 sticks) |
    +----------------+
    15 rows in set (0.00 sec)
    
    mysql> select prod_name from products limit 0 , 1;
    +--------------+
    | prod_name    |
    +--------------+
    | .5 ton anvil |
    +--------------+
    1 row in set (0.01 sec)
    
    mysql> select prod_name from products  limit 1 , 1;
    +-------------+
    | prod_name   |
    +-------------+
    | 1 ton anvil |
    +-------------+
    1 row in set (0.00 sec)
    
    
    select products.prod_name from FirstTest.products;
    //完全限定的表名。限定使用数据是哪个表中的哪个列。前面因为使用了use因此可以省略。
    //否则应该使用限定表名用法。

## 4、排序检索的数据 ##

select prod_name from products;
    //返回没有特定数据，这是顺序由底层表中出现的顺序显示也就是数据最初添加到表中的顺序。
    //但是，如果数据后来进行过更新或删除，则此顺序将会受到MySQL重用回收存储空间的影响。
    
    
    1、排序数据
    select prod_price
    from products
    order by prod_price;
    //列出价格列，然后按照价格排序。
    
    2、按多个列排序
    select prod_id, prod_price, prod_name 
    from products 
    order by prod_price, prod_name;
    //注意逗号分割是如何进行的。列出多个列，并按照多个列排序。仅在多个行具有相同的prod_price
    //值时才对产品按prod_name进行排序。如果prod_price列中所有的值都是输入输出唯一的，
    //则不会按prod_name排序。
    
    3、指定排序方向
    select prod_id, prod_price, prod_name 
    from products 
    order by prod_price desc;
    //指定按照价格降序排列
    //desc指定降序排列，asc指向按升序排列（默认是递增）
    
    select prod_id, prod_price, prod_name 
    from products 
    order by prod_price desc, prod_name;
    //首先将按照价格降序排列；价格相同后，再按照name升序排序。
    //desc 仅仅修饰位于前面的列名。 asc是递增通常不用，因为默认就是递增。
    
    select prod_id, prod_price, prod_name 
    from products 
    order by prod_price desc 
    limit 1;
    //降序排列后取出第一行，也就是价格最高的。

## 5、过滤数据 ##

通常不需要显示整个完整的表，不许添加一些过滤条件，SQL里面通过where进行。  
where支持的操作符：

*  = 等于
 *  <> 不等于
 *  != 不等于
 *  < 小于
 *  <= 小于等于
 *  > 大于
 *  >= 大于等于
 *  between 在指定两个值之间
 *  and or in not 可以联合多个条件

1、检查单个值
    select prod_name, prod_price 
    from products 
    where prod_price = 2.50;
    //显示价格是2.5的列
    
    select prod_name, prod_price 
    from products 
    where prod_name = 'fuses';
    //MySQL默认不区分大小写。
    //对于字符串匹配，字符串需要使用单引号 
    
    2、范围检查
    select prod_name, prod_price 
    from products 
    where prod_price between 5 and 10;
    //列出价格再5到10之间
    
    3、空值（NULL）检查
    select cust_id from customers where cust_email is NULL;
    //列出email是NULL的id列。
    
    4、and联合操作
    select * 
    from products 
    where vend_id = 1003 and prod_price <= 10; 
    //选择厂商是这个且价格满足上面条件的行。
    
    5、or联合操作
    select * 
    from products  
    where vend_id = 1002 or vend_id = 1003 
    order by vend_id;   
    
    6、or和and联合操作 
    select * 
    from products  
    where (vend_id = 1002 or vend_id = 1003) and prod_price >= 10;
    //注意逻辑优先级，and > or 所以将or括起来。
    
    7、in范围查找
    select * from products  
    where vend_id in (1002,1003) and prod_price >= 10 
    order by prod_price;
    //in操作符表明vend_id取值的范围。类似于or功能，但执行比or快速。
    //IN操作符的语法更清楚且更直观。
    //IN操作符一般比OR操作符清单执行更快。
    //IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建立WHERE子句。
    
    
    8、not操作符
    select * 
    from products  
    where vend_id not in (1002,1003) and prod_price >= 10 
    order by prod_price;
    //not 起到了否定的作用

## 6、使用通配符或正则过滤数据 ##

1、通配符

*  % 表示任何字符任意次数，字符不限且次数不限也就是任何字符。
 *  \_ 表示单个的任何字符。
 *  通配符处理较慢，不要过度使用，能避免则尽量避免使用。

2、[正则表达式][Link 1]

1、使用%通配符
    select * 
    from products  
    where prod_name like 'jet%';
    //名字开头是jet的列
    
    2、使用_通配符
    select vend_id , prod_name 
    from products  
    where prod_name like '_ ton anvil';
    
    mysql> select vend_id , prod_name  from products   where prod_name like '_ ton anvil';
    +---------+-------------+
    | vend_id | prod_name   |
    +---------+-------------+
    |    1001 | 1 ton anvil |
    |    1001 | 2 ton anvil |
    +---------+-------------+
    2 rows in set (0.00 sec)
    
    3、正则符号.
    select vend_id , prod_name
    from products 
    where prod_name regexp '.000' order by prod_name;
    //代替了上面的like表示正则表达式
    
    mysql> select vend_id , prod_name  from products  where prod_name regexp '.000' order by prod_name;
    +---------+--------------+
    | vend_id | prod_name    |
    +---------+--------------+
    |    1005 | JetPack 1000 |
    |    1005 | JetPack 2000 |
    +---------+--------------+
    2 rows in set (0.00 sec)
    
    
    4、正则符号|
    select vend_id , prod_name 
    from products 
    where prod_name regexp '1000|2000' order by prod_name;
    //正则的or匹配
    
    
    5、正则符号[]
    select vend_id , prod_name 
    from products 
    where prod_name regexp '[123] Ton' order by prod_name;
    
    mysql> select vend_id , prod_name 
        -> from products 
        -> where prod_name regexp '[123] Ton' order by prod_name;
    +---------+-------------+
    | vend_id | prod_name   |
    +---------+-------------+
    |    1001 | 1 ton anvil |
    |    1001 | 2 ton anvil |
    +---------+-------------+
    2 rows in set (0.00 sec)
    
    6、正则符号.
    select * 
    from products 
    where prod_name regexp '.';
    //.代表匹配正则的任意字符
    
    7、正则转义符号
    select * 
    from products 
    where prod_name regexp '\\.';
    //搜索含有.的行，通过\\将特殊字符转义
    
    8、正则符号转义及[0-9]
    select * 
    from products 
    where prod_name regexp '\$[0-9] sticks?\$';
    //用到了转义字符，正则表达式含义，自己去瞅瞅吧，很简单，没有那么复杂哦。
    创建和操纵表
    
    9、正则符号{}
    select * 
    from products 
    where prod_name regexp '[0-9]{4}';
    //连在一起的4个数字

## 7、函数及其使用 ##

**1、常见的文本处理函数**

<table> 
 <thead> 
  <tr> 
   <th>函数名</th> 
   <th align="center">说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>concat()</td> 
   <td align="center">返回拼接的几个列</td> 
  </tr> 
  <tr> 
   <td>Left()</td> 
   <td align="center">返回串左边的字符</td> 
  </tr> 
  <tr> 
   <td>Length()</td> 
   <td align="center">返回串的长度</td> 
  </tr> 
  <tr> 
   <td>Locate()</td> 
   <td align="center">找出串的一个子串</td> 
  </tr> 
  <tr> 
   <td>Lower()</td> 
   <td align="center">将串转换为小写</td> 
  </tr> 
  <tr> 
   <td>LTrim()</td> 
   <td align="center">去掉串左边的空格</td> 
  </tr> 
  <tr> 
   <td>Right()</td> 
   <td align="center">返回串右边的字符</td> 
  </tr> 
  <tr> 
   <td>RTrim()</td> 
   <td align="center">去掉串右边的空格</td> 
  </tr> 
  <tr> 
   <td>Soundex()</td> 
   <td align="center">返回串的SOUNDEX值，匹配发音类似的</td> 
  </tr> 
  <tr> 
   <td>SubString()</td> 
   <td align="center">返回子串的字符</td> 
  </tr> 
  <tr> 
   <td>Upper()</td> 
   <td align="center">将串转换为大写</td> 
  </tr> 
 </tbody> 
</table>

**2、常见的日期和时间处理函数**

<table> 
 <thead> 
  <tr> 
   <th>函数名</th> 
   <th align="center">说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>AddDate()</td> 
   <td align="center">增加一个日期（天、周等）</td> 
  </tr> 
  <tr> 
   <td>AddTime()</td> 
   <td align="center">增加一个时间（时、分等）</td> 
  </tr> 
  <tr> 
   <td>CurDate()</td> 
   <td align="center">返回当前日期</td> 
  </tr> 
  <tr> 
   <td>CurTime()</td> 
   <td align="center">返回当前时间</td> 
  </tr> 
  <tr> 
   <td>Date()</td> 
   <td align="center">返回日期时间的日期部分</td> 
  </tr> 
  <tr> 
   <td>DateDiff()</td> 
   <td align="center">计算两个日期之差</td> 
  </tr> 
  <tr> 
   <td>Date_Add()</td> 
   <td align="center">高度灵活的日期运算函数</td> 
  </tr> 
  <tr> 
   <td>Date_Format()</td> 
   <td align="center">返回一个格式化的日期或时间串</td> 
  </tr> 
  <tr> 
   <td>Day()</td> 
   <td align="center">返回一个日期的天数部分</td> 
  </tr> 
  <tr> 
   <td>DayOfWeek()</td> 
   <td align="center">对于一个日期，返回对应的星期几</td> 
  </tr> 
  <tr> 
   <td>Hour()</td> 
   <td align="center">返回一个时间的小时部分</td> 
  </tr> 
  <tr> 
   <td>Minute()</td> 
   <td align="center">返回一个时间的分钟部分</td> 
  </tr> 
  <tr> 
   <td>Month()</td> 
   <td align="center">返回一个日期的月份部分</td> 
  </tr> 
  <tr> 
   <td>Now()</td> 
   <td align="center">返回当前日期和时间</td> 
  </tr> 
  <tr> 
   <td>Second()</td> 
   <td align="center">返回一个时间的秒部分</td> 
  </tr> 
  <tr> 
   <td>Time()</td> 
   <td align="center">返回一个日期时间的时间部分</td> 
  </tr> 
  <tr> 
   <td>Year()</td> 
   <td align="center">返回一个日期的年份部分</td> 
  </tr> 
 </tbody> 
</table>

**3、常见的数值处理函数**

<table> 
 <thead> 
  <tr> 
   <th>函数名</th> 
   <th align="center">说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>round()</td> 
   <td align="center">遵循四舍五入原则，用于把数值字段舍入为指定的小数位数round(12.9,0)=13</td> 
  </tr> 
  <tr> 
   <td>floor()</td> 
   <td align="center">返回大于或等于指定值（value）的最小整数floor(12.9)=12</td> 
  </tr> 
  <tr> 
   <td>ceiling()</td> 
   <td align="center">函数返回大于或等于指定值（value）的最小整数ceiling（12.9）=13</td> 
  </tr> 
  <tr> 
   <td>Abs()</td> 
   <td align="center">返回一个数的绝对值</td> 
  </tr> 
  <tr> 
   <td>Cos()</td> 
   <td align="center">返回一个角度的余弦</td> 
  </tr> 
  <tr> 
   <td>Exp()</td> 
   <td align="center">返回一个数的指数值</td> 
  </tr> 
  <tr> 
   <td>Mod()</td> 
   <td align="center">返回除操作的余数</td> 
  </tr> 
  <tr> 
   <td>Pi()</td> 
   <td align="center">返回圆周率</td> 
  </tr> 
  <tr> 
   <td>Rand()</td> 
   <td align="center">返回一个随机数</td> 
  </tr> 
  <tr> 
   <td>Sin()</td> 
   <td align="center">返回一个角度的正弦</td> 
  </tr> 
  <tr> 
   <td>Sqrt()</td> 
   <td align="center">返回一个数的平方根</td> 
  </tr> 
  <tr> 
   <td>Tan()</td> 
   <td align="center">返回一个角度的正切</td> 
  </tr> 
 </tbody> 
</table>

**4、常见的SQL聚集函数**

<table> 
 <thead> 
  <tr> 
   <th>函数名</th> 
   <th align="center">说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>AVG()</td> 
   <td align="center">返回某列的平均值</td> 
  </tr> 
  <tr> 
   <td>COUNT()</td> 
   <td align="center">返回某列的行数</td> 
  </tr> 
  <tr> 
   <td>MAX()</td> 
   <td align="center">返回某列的最大值</td> 
  </tr> 
  <tr> 
   <td>MIN()</td> 
   <td align="center">返回某列的最小值</td> 
  </tr> 
  <tr> 
   <td>SUM()</td> 
   <td align="center">返回某列值之和</td> 
  </tr> 
 </tbody> 
</table>

1、拼接concat将值连接到一起构成单个值  
    select concat(vend_name , ' (' , vend_country , ')') as concatcolumn
    from vendors
    order by vend_name;
    //显示将name和country拼成一列并按照名字排序。 这个拼接操作右数据库处理。
    //拼接的列没有名字，客户机不能使用，使用别名AS,新拼接的列没有名字，客户机可以应用。
    //创建了一个别名concatcolumn任何客户都可以引用。
    mysql> select concat(vend_name , ' (' , vend_country , ')') as concatcolumn
        -> from vendors
        -> order by vend_name;
    +-------------------------+
    | concatcolumn            |
    +-------------------------+
    | ACME (USA)              |
    | Anvils R Us (USA)       |
    | Furball Inc. (USA)      |
    | Jet Set (England)       |
    | Jouets Et Ours (France) |
    | LT Supplies (USA)       |
    +-------------------------+
    6 rows in set (0.00 sec)
    
    
    2、算术运算
    select vend_id  , vend_id * prod_price as total_price 
    from products;
    //显示所以列，并显示进行过算数运算的别名列，total_price就是进行算术运算使用的列
    mysql> select vend_id  , vend_id * prod_price as total_price 
        -> from products;
    +---------+-------------+
    | vend_id | total_price |
    +---------+-------------+
    |    1001 |     5995.99 |
    |    1001 |     9999.99 |
    |    1001 |    15004.99 |
    |    1001 |     5995.99 |
    |    1003 |    13039.00 |
    |    1003 |    10030.00 |
    |    1003 |     2507.50 |
    |    1002 |     3426.84 |
    |    1005 |    35175.00 |
    |    1005 |    55275.00 |
    |    1002 |     9007.98 |
    |    1003 |    50150.00 |
    |    1003 |     4503.47 |
    |    1003 |     2507.50 |
    |    1003 |    10030.00 |
    +---------+-------------+
    
    
    3、使用soundex
    select cust_contact , cust_id from customers 
    where soundex(cust_contact) = soundex('Y . Lie'); 
    //将和'Y . Lie'发音类似的'Y Lee'行显示出来。
    mysql> select cust_contact , cust_id from customers 
        -> where soundex(cust_contact) = soundex('Y . Lie'); 
    +--------------+---------+
    | cust_contact | cust_id |
    +--------------+---------+
    | Y Lee        |   10001 |
    | Y Lee        |   10006 |
    | Y Lee        |   10007 |
    | Y Lee        |   10008 |
    | Y Lee        |   10009 |
    | Y Lee        |   10014 |
    | Y Lee        |   10015 |
    +--------------+---------+
    7 rows in set (0.00 sec)
    
    4、使用date
    select order_num , order_date 
    from orders 
    where date(order_date) = '2005-09-01';
    //data指示MySQL仅提取列的日期部分，然后将其用于where比较
    mysql> select order_num , order_date 
        -> from orders 
        -> where date(order_date) = '2005-09-01';
    +-----------+---------------------+
    | order_num | order_date          |
    +-----------+---------------------+
    |     20005 | 2005-09-01 00:00:00 |
    +-----------+---------------------+
    1 row in set (0.00 sec)
    
    5、使用avg
    select avg(prod_price) as avg_price 
    from products;
    //返回均价
    
    select avg(prod_price) as avg_price 
    from products
    where vend_id = 1003;
    //返回特定行的平均值
    
    6、使用count
    //使用COUNT(*)对表中行的数目进行计数， 不管表列中包含的是空值（NULL）还是非空值。
    ‰//使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。
    
    select count(*) as num_cust
    from customers;
    //对所有行计数，不论各列中是什么值
    
    select count(prod_price) 
    from products;
    //返回列的行数
    
    7、使用max、min、sum
    select max(prod_price) from products;
    select min(prod_price) from products;
    select sum(prod_price) from products;
    //最大 最小 总和价格
    
    8、聚集不同值
    //对所有的行执行计算，指定ALL参数或不给参数（因为ALL是默认行为）；
    //只对只包含不同的值的行执行计算，指定DISTINCT参数
    
    select avg(distinct prod_price) from products;
    //排除价格中相同的，再进行求均值。
    
    9、聚集函数组合使用
    select count(*) as num_items , 
       min(prod_price) as price_min , 
       max(prod_price) as price_max 
    from products;
    //组合显示行数 最大价格 最小价格等信息。
    //数据库管理系统，将数据的处理和应用程序的逻辑处理进行相互隔离，
    //起到了很到的相互配合作用。

## 8、分组数据 ##

何为分组数据，类似excel，通过选定某一列，键某一列通过特定条件进行过滤分组，例如选择ID都为2的学生信息等，MySQL也支持分组。使用规则是先分组，然后再过滤分组即可。

//1、数据分组：类似excel的操作，通过id分组，一样的为一组。
    select vend_id , count(*) as num_prods 
    from products 
    group by vend_id;
    //先将表通过id分组，然后返回每个分组里面的id及其产品数目，定义了列的别名
    +---------+-----------+
    | vend_id | num_prods |
    +---------+-----------+
    |    1001 |         3 |
    |    1002 |         2 |
    |    1003 |         7 |
    |    1005 |         2 |
    +---------+-----------+
    
    //2、过滤分组：having可以过滤分组
    //先计算每个分组中订单数，然后通过having过滤分组产生数大于2的
    //将id一样的分为一组并统计按照having条件是否输出显示。可以想象程序是如何处理的。
    select vend_id , count(*) as orders from products 
    group by vend_id 
    having count(*) > 2;
    +---------+--------+
    | vend_id | orders |
    +---------+--------+
    |    1001 |      3 |
    |    1003 |      7 |
    +---------+--------+
    
    
    select vend_id , count(*) as orders 
    from products  
    where prod_price >= 10 
    group by vend_id 
    having count(*) > 2;
    //先通过where过滤行找出price>=10的行，然后通过id分组，然后统计count，再过滤分组>2的行
    
    //where和having的主要差别在于一个用于过滤行，一个用于过滤分组。
    
    
    //3、分组和排序
    select order_num , sum(quantity*item_price) as ordertotal 
    from orderitems 
    group by order_num 
    having sum(quantity*item_price) >= 50 
    order by ordertotal;
    //先按照num分组，然后过滤，然后排序

SQL中select字句的顺序。  
![这里写图片描述][70 1]

## 9、子查询、连接表(join)及组合查询(union) ##

**1、子查询**  
嵌套在其他查询中查询，也就是查询其他SQL语句查询的结构

select cust_id 
    from orders 
    where order_num in (select order_num 
                    from orderitems 
                    where prod_id = 'TNT2');
    //返回产品是TNT2的所有客户，因为客户和商品分了两个表，所以必须通过子查询。 
    
    select cust_name , (select count(*) from orders where customers.cust_id = orders.cust_id)as orders from customers;
    //找出每个客户的订单总数，通过子查询优势，比较复杂，如果使用联结，将很简单的。
    
    //检索出所有客户，并对每个客户订单数计数。通过分组前面的外部联结表实现，在分组之后进行count即可实现。
    select 
        customers.cust_id , 
        customers.cust_name , 
        count(orders.order_num) as num_orders   
    from customers left outer join orders 
    on customers.cust_id = orders.cust_id
    group by customers.cust_id
    order by customers.cust_id;
    +---------+----------------+------------+
    | cust_id | cust_name      | num_orders |
    +---------+----------------+------------+
    |   10001 | Coyote Inc.    |          2 |
    |   10002 | Mouse House    |          0 |
    |   10003 | Wascals        |          1 |
    |   10004 | Yosemite Place |          1 |
    |   10005 | E Fudd         |          1 |
    +---------+----------------+------------+
    5 rows in set (0.00 sec)

子查询通常不是一种高效的查询方法，可以使用下面的连接表更加高效。

**2、联接表**  
关系型数据库，其表与表之间必然是有关系，那么可以通过将多个表连接起来查询所需要的数据。通过join操作实现。

//1、自然联结
    //自然连接的定义是不出现重复的列，但是MySQL中自然连接和内部连接一样，
    //可以出现重复的列，将重复处理交给用户。
    select customers.cust_id , customers.cust_name , orders.cust_id 
    from customers , orders 
    where customers.cust_id = orders.cust_id;
    +---------+----------------+---------+
    | cust_id | cust_name      | cust_id |
    +---------+----------------+---------+
    |   10001 | Coyote Inc.    |   10001 |
    |   10003 | Wascals        |   10003 |
    |   10004 | Yosemite Place |   10004 |
    |   10005 | E Fudd         |   10005 |
    |   10001 | Coyote Inc.    |   10001 |
    +---------+----------------+---------+
    5 rows in set (0.00 sec)
    
    //2、内部联结又称为等值联结,仅仅将两个表中存在联结关系的字段(两个表中有相同的键)符合联结关系的那些记录形成记录集的联结。用inner join指定表之间的关系，用on子句指定联结条件。
    //目前为止所用的联结称为等值联结（equijoin），它基于两个表之间的
    //相等测试。这种联结也称为内部联结。
    select customers.cust_id , customers.cust_name , orders.cust_id 
    from customers inner join orders;
    //联结相当于两个表的笛卡尔积
    +---------+----------------+---------+
    | cust_id | cust_name      | cust_id |
    +---------+----------------+---------+
    |   10001 | Coyote Inc.    |   10001 |
    |   10002 | Mouse House    |   10001 |
    |   10003 | Wascals        |   10001 |
    |   10004 | Yosemite Place |   10001 |
    |   10005 | E Fudd         |   10001 |
    |   10001 | Coyote Inc.    |   10003 |
    |   10002 | Mouse House    |   10003 |
    |   10003 | Wascals        |   10003 |
    |   10004 | Yosemite Place |   10003 |
    |   10005 | E Fudd         |   10003 |
    |   10001 | Coyote Inc.    |   10004 |
    |   10002 | Mouse House    |   10004 |
    |   10003 | Wascals        |   10004 |
    |   10004 | Yosemite Place |   10004 |
    |   10005 | E Fudd         |   10004 |
    |   10001 | Coyote Inc.    |   10005 |
    |   10002 | Mouse House    |   10005 |
    |   10003 | Wascals        |   10005 |
    |   10004 | Yosemite Place |   10005 |
    |   10005 | E Fudd         |   10005 |
    |   10001 | Coyote Inc.    |   10001 |
    |   10002 | Mouse House    |   10001 |
    |   10003 | Wascals        |   10001 |
    |   10004 | Yosemite Place |   10001 |
    |   10005 | E Fudd         |   10001 |
    +---------+----------------+---------+
    25 rows in set (0.08 sec)
    
    //内部连接,可出现重复的列
    select customers.cust_id , customers.cust_name , orders.cust_id 
    from customers inner join orders 
     on customers.cust_id = orders.cust_id;
    +---------+----------------+---------+
    | cust_id | cust_name      | cust_id |
    +---------+----------------+---------+
    |   10001 | Coyote Inc.    |   10001 |
    |   10003 | Wascals        |   10003 |
    |   10004 | Yosemite Place |   10004 |
    |   10005 | E Fudd         |   10005 |
    |   10001 | Coyote Inc.    |   10001 |
    +---------+----------------+---------+
    5 rows in set (0.00 sec)
    
    
    //3、外部联结，外部联结包含了在相关表中没有关联的行，
    //存在左外部联结(left outer join)和右外部(right outer join)联结，分别以左边或右边表为标准。
    
    //与内部联结关联两个表中的行不同的是，外部联结还包括没
    //有关联行的行。在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字
    //指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT
    //指出的是OUTER JOIN左边的表）
    
    //左外部联结，10002在orders.cust_id中并没有，因为left，所以最终还是显示出来，orders列中，都通过NULL表示。
    
    
    select customers.cust_id , customers.cust_name , orders.cust_id 
    from customers left outer join orders 
    on customers.cust_id = orders.cust_id;
    
    +---------+----------------+---------+
    | cust_id | cust_name      | cust_id |
    +---------+----------------+---------+
    |   10001 | Coyote Inc.    |   10001 |
    |   10003 | Wascals        |   10003 |
    |   10004 | Yosemite Place |   10004 |
    |   10005 | E Fudd         |   10005 |
    |   10001 | Coyote Inc.    |   10001 |
    |   10002 | Mouse House    |    NULL |
    +---------+----------------+---------+
    6 rows in set (0.00 sec)
    
    //右外部联结
    select customers.cust_id , customers.cust_name , orders.cust_id 
    from customers right outer join orders 
    on customers.cust_id = orders.cust_id;
    +---------+----------------+---------+
    | cust_id | cust_name      | cust_id |
    +---------+----------------+---------+
    |   10001 | Coyote Inc.    |   10001 |
    |   10003 | Wascals        |   10003 |
    |   10004 | Yosemite Place |   10004 |
    |   10005 | E Fudd         |   10005 |
    |   10001 | Coyote Inc.    |   10001 |
    +---------+----------------+---------+
    5 rows in set (0.00 sec)
    和内部联结一样，因为orders.cust_id都存在于customers.cust_id中
    
    //检索出所有客户，包括哪些没有订单的客户。
    select customers.cust_id , customers.cust_name , orders.order_num 
    from customers left outer join orders 
    on customers.cust_id = orders.cust_id
    order by customers.cust_id;
    +---------+----------------+-----------+
    | cust_id | cust_name      | order_num |
    +---------+----------------+-----------+
    |   10001 | Coyote Inc.    |     20005 |
    |   10001 | Coyote Inc.    |     20009 |
    |   10002 | Mouse House    |      NULL |
    |   10003 | Wascals        |     20006 |
    |   10004 | Yosemite Place |     20007 |
    |   10005 | E Fudd         |     20008 |
    +---------+----------------+-----------+
    6 rows in set (0.00 sec)
    
    //检索出所有客户，并对每个客户订单数计数。通过分组前面的外部联结表实现，在分组之后进行count即可实现。
    select 
        customers.cust_id , 
        customers.cust_name , 
        count(orders.order_num) as num_orders   
    from customers left outer join orders 
    on customers.cust_id = orders.cust_id
    group by customers.cust_id
    order by customers.cust_id;
    +---------+----------------+------------+
    | cust_id | cust_name      | num_orders |
    +---------+----------------+------------+
    |   10001 | Coyote Inc.    |          2 |
    |   10002 | Mouse House    |          0 |
    |   10003 | Wascals        |          1 |
    |   10004 | Yosemite Place |          1 |
    |   10005 | E Fudd         |          1 |
    +---------+----------------+------------+
    5 rows in set (0.00 sec)
    
    
    //验证 以orders.order_num为基础统计行，当count(NULL)则为0
    select 
        customers.cust_id , 
        customers.cust_name , 
        count(orders.order_num) as num_orders   
    from customers left outer join orders 
    on customers.cust_id = orders.cust_id
    group by customers.cust_id
    having customers.cust_id = 10002;
    //可以通过过滤分组查看,count是如何处理NULL的。
    +---------+-------------+------------+
    | cust_id | cust_name   | num_orders |
    +---------+-------------+------------+
    |   10002 | Mouse House |          0 |
    +---------+-------------+------------+
    1 row in set (0.00 sec)

**3、组合查询**  
将几条SQL语句输出通过并的方式组合在一起，类似数学里面的并集类似。

*  UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合4条SELECT语句，将要使用3个UNION关键字）。
 *  UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。
 *  列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。
 *  union默认**自动去除重复的行（和where完全一样）**。union all 返回所有匹配的行。如果确实需要返回所以行（包括重复的行）必须使用union all。
 *  union组合中仅仅允许在最后使用一条 order by语句进行排序。

//1、union的基本使用
    //价格小于等于5
    select vend_id ,prod_id , prod_price from products where prod_price <= 5; +---------+---------+------------+
    | vend_id | prod_id | prod_price | +---------+---------+------------+
    |    1003 | FC      |       2.50 |
    |    1002 | FU1     |       3.42 |
    |    1003 | SLING   |       4.49 |
    | 1003 | TNT1 | 2.50 | +---------+---------+------------+
    4 rows in set (0.00 sec)
    
    //id是1001或1002，使用in简化了where的使用。
    select vend_id ,prod_id , prod_price from products where vend_id in (1001,1002); +---------+---------+------------+
    | vend_id | prod_id | prod_price | +---------+---------+------------+
    |    1001 | ANV01   |       5.99 |
    |    1001 | ANV02   |       9.99 |
    |    1001 | ANV03   |      14.99 |
    |    1001 | ANV2301 |       5.99 |
    |    1002 | FU1     |       3.42 |
    | 1002 | OL1 | 8.99 | +---------+---------+------------+
    6 rows in set (0.16 sec)
    
    //将两条语句组合起来，将输出并，输出里面仅仅含有一个FU1
    select vend_id , prod_id ,  prod_price from products where prod_price <= 5
    union
    select vend_id , prod_id , prod_price from products where vend_id in (1001,1002);
    +---------+---------+------------+
    | vend_id | prod_id | prod_price | +---------+---------+------------+
    |    1003 | FC      |       2.50 |
    |    1002 | FU1     |       3.42 |
    |    1003 | SLING   |       4.49 |
    |    1003 | TNT1    |       2.50 |
    |    1001 | ANV01   |       5.99 |
    |    1001 | ANV02   |       9.99 |
    |    1001 | ANV03   |      14.99 |
    |    1001 | ANV2301 |       5.99 |
    | 1002 | OL1 | 8.99 | +---------+---------+------------+
    9 rows in set (0.01 sec)
    
    //上述等效于
    select vend_id , prod_id ,  prod_price from products where prod_price <= 5 or vend_id in (1001,1002); //可以看出union可以简化where的或操作。 //2、union并中显示所以的行，包括重复的行
    //如果确实需要返回所有的行(包括重复的行)必须使用union all,不能使用where
    select vend_id , prod_id ,  prod_price from products where prod_price <= 5
    union all
    select vend_id , prod_id , prod_price from products where vend_id in (1001,1002);
    +---------+---------+------------+
    | vend_id | prod_id | prod_price | +---------+---------+------------+
    |    1003 | FC      |       2.50 |
    |    1002 | FU1     |       3.42 |
    |    1003 | SLING   |       4.49 |
    |    1003 | TNT1    |       2.50 |
    |    1001 | ANV01   |       5.99 |
    |    1001 | ANV02   |       9.99 |
    |    1001 | ANV03   |      14.99 |
    |    1001 | ANV2301 |       5.99 |
    |    1002 | FU1     |       3.42 |
    | 1002 | OL1 | 8.99 | +---------+---------+------------+
    10 rows in set (0.00 sec)  这里是10行，而不是9行。

## 10、操作表及表中的数据 ##

主键：一列（或一组列），其值能够唯一区分表中每个行，唯一标识表中每行的这个列（或这组列）称为主键。  
外键：外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。外键确定了两个表直接联结的关系，一个表主键改变，则这个外键会相应的变化。

//1、创建表
    create table customers
    (
      cust_id      int       NOT NULL AUTO_INCREMENT,
      cust_name    char(50)  NOT NULL ,
      cust_address char(50)  NULL , 
      cust_city    char(50)  NULL ,
      cust_state   char(5)   NULL ,
      cust_zip     char(10)  NULL  default "KKJJ",
      cust_country char(50)  NULL ,
      cust_contact char(50)  NULL ,
      cust_email   char(255) NULL ,
      PRIMARY KEY (cust_id)
    ) ENGINE=InnoDB;
    /* NULL表示没有值或缺值允许插入时候不提供数值，否则必须提供值。 PRIMARY KEY定义了此表的主键。 AUTO_INCREMENT表示在插入不提供值的时候，自动从上一行增加一个，否则使用提供的值。 default提示MySQL，当不提供值，则使用此默认值。 ENGINE代表选用的引擎。 */
    
    //2、修改表结构
    //表创建了一般不需要更新表的结构，因为创建表是DBA重要的事情。
    
    alter table vendors
    add vend_phone char(20) NULL;//表中添加一列
    
    alter table vendors
    drop column vend_phone;//删除刚刚添加一列
    
    //3、删除表
    drop table tablename;
    
    //4、重命名表
    rename table name1 to name2;//重命名单个表
    rename table tablename  to tablename1
                 tablename2 to tablename3   
                 tablename4 to tablename5;//重命名多个表
    //
    
    //5、更新数据行
    //更新不能省略where语句,因为需要确定特定的行。否则可能更新所有行
    update customers
    set cust_email = 'wangjj@163.com',cust_name = "wangjj"
    where cust_id = 10001;
    //更新特定行的特定列，通过where和set实现。
    update ignore customers
    set cust_email = 'wangjj@163.com',cust_name = "wangjj"
    where cust_id = 10001;
    //ignore关键字说明更新一行或多行时候发生某些错误，则此次更新全部取消，已更新的回滚到原先值。
    update ignore customers
    set cust_email = NULL,cust_name = "wangjj"
    where cust_id = 10001;
    //NULL指明删除某列值
    
    //6、删除数据行
    delete from customers wherer cust_id = 10016;
    //从客户中删除id是10016的行。 
    
    truncate table from tablename
    //将删除所有行（删除当前表，然后创建一个空表）
    
    //7、插入数据行
    insert into customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
    values(10006, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@coyote.com');
    /* 插入单行数据。 如果提供了前面的小括号内容，则插入数据灵活，并不一定非要按照建表顺序插入。只要行和值一一对应即可。 */
    insert low_priority into customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
    values(
    'Coyote Inc.', 
    '200 Maple Lane', 
    'Detroit', 
    'MI', 
    '44444', 
    'USA', 
    'Y Lee', 
    'ylee@coyote.com'
    ),
    (
    'Coyote Inc.', 
    '200 Maple Lane', 
    'Detroit', 
    'MI', 
    '44444', 
    'USA', 
    'Y Lee', 
    'ylee@coyote.com'
    );
    /* 插入多行数据，同单行数据一样，只不过value后面多个了括号。 low_priority表明，使用较低的优先级插入，当并行性提高，则插入可能费时。 主键并没有提供数值，使用了默认自增的值。 */
    
    //8、插入检索出的数据
        2、插入检索出的数据：
           就是将select出的insert到表中。
    insert low_priority into customers(
    cust_name, 
    cust_address, 
    cust_city, 
    cust_state, 
    cust_zip, 
    cust_country, 
    cust_contact, 
    cust_email
    )    
    select cust_name, 
    cust_address, 
    cust_city, 
    cust_state, 
    cust_zip, 
    cust_country, 
    cust_contact, 
    cust_email from customers;
    //将select返回的值，与前面的列表一一对应，并插入。id并没有插入，防止重复，使用系统自增的功能。

更新和删除操作都没有undo操作，所以在使用这些语句的时候，必须清楚。

## 11、使用视图、存储过程、触发器 ##

视图：视图是一个虚拟的表，主要用来包装复杂的SQL语句，DBA将复杂功能的SQL语句编写完成后，程序员可以通过简单的视图调用就很容易。在添加或更改这些表中的数据时，视图将返回改变过的数据。视图不能索引，视图可以和其他表联合使用。**视图提供了一种MySQL的SELECT语句层次的封装，可用来简化数据处理以及重新格式化基础数据或保护基础数据。**

*  视图可以简化复杂的SQL联结，也就是相当于将此包装起来。
 *  视图可极大地简化复杂的数据处理。

//1、简化复杂的SQL语句
    select prod_name , vend_name , item_price , quantity from products , orderitems , vendors where products.prod_id = orderitems.prod_id and products.vend_id = vendors.vend_id and orderitems.order_num = '20005';
    //内部连接三个表，显示编号位20005订单中的商品名字、厂商、价格、数量。联结三个表即可。
    +----------------+-------------+------------+----------+
    | prod_name | vend_name | item_price | quantity | +----------------+-------------+------------+----------+
    | .5 ton anvil   | Anvils R Us |       5.99 |       10 |
    | 1 ton anvil    | Anvils R Us |       9.99 |        3 |
    | TNT (5 sticks) | ACME        |      10.00 |        5 |
    | Bird seed | ACME | 10.00 | 1 | +----------------+-------------+------------+----------+
    4 rows in set (0.00 sec)
    
    //创建视图，简化上述操作
    create view orderview as
    select order_num , prod_name , vend_name , item_price , quantity 
    from products , orderitems , vendors 
    where products.prod_id = orderitems.prod_id 
    and products.vend_id = vendors.vend_id;
    //通过create创建orderview，这个视图包含了每个订单号对应的商品名、价格、厂商、数量。
    select * from orderview; +-----------+----------------+-------------+------------+----------+
    | order_num | prod_name | vend_name | item_price | quantity | +-----------+----------------+-------------+------------+----------+
    |     20005 | .5 ton anvil   | Anvils R Us |       5.99 |       10 |
    |     20005 | 1 ton anvil    | Anvils R Us |       9.99 |        3 |
    |     20005 | TNT (5 sticks) | ACME        |      10.00 |        5 |
    |     20005 | Bird seed      | ACME        |      10.00 |        1 |
    |     20006 | JetPack 2000   | Jet Set     |      55.00 |        1 |
    |     20007 | TNT (5 sticks) | ACME        |      10.00 |      100 |
    |     20008 | Carrots        | ACME        |       2.50 |       50 |
    |     20009 | Bird seed      | ACME        |      10.00 |        1 |
    |     20009 | Oil can        | LT Supplies |       8.99 |        1 |
    |     20009 | Sling          | ACME        |       4.49 |        1 |
    | 20009 | 2 ton anvil | Anvils R Us | 14.99 | 1 | +-----------+----------------+-------------+------------+----------+
    11 rows in set (0.00 sec)
    
    select * from orderview where order_num = '20005'; +-----------+----------------+-------------+------------+----------+
    | order_num | prod_name | vend_name | item_price | quantity | +-----------+----------------+-------------+------------+----------+
    |     20005 | .5 ton anvil   | Anvils R Us |       5.99 |       10 |
    |     20005 | 1 ton anvil    | Anvils R Us |       9.99 |        3 |
    |     20005 | TNT (5 sticks) | ACME        |      10.00 |        5 |
    | 20005 | Bird seed | ACME | 10.00 | 1 | +-----------+----------------+-------------+------------+----------+
    //直接简化了查找操作，DBA应该多多创建一些有用的视图，使得程序员可以更好的写程序，这就是DBA的作用，所以DBA还是需要有专门的人员处理的。必然程序员可以没有多余的精力去弄DBA的事情。
    
    //2、使用视图重新格式化检索出的数据
    select concat(vend_name , ' (' , vend_country , ')') as vend_title from vendors order by vend_name;
    //每次都需要从vendors中返回这种形式的vend_title，也就是重新格式化这种输出，为了不必每次重复写这种SQL，直接使用视图简化。
    +-------------------------+
    | vend_title | +-------------------------+
    | ACME (USA)              |
    | Anvils R Us (USA)       |
    | Furball Inc. (USA)      |
    | Jet Set (England)       |
    | Jouets Et Ours (France) |
    | LT Supplies (USA)       |
    +-------------------------+ 
    create view vendorlocation as 
    select concat(vend_name , ' (' , vend_country , ')') as vend_title from vendors order by vend_name;
    select * from vendorlocation; +-------------------------+
    | vend_title | +-------------------------+
    | ACME (USA)              |
    | Anvils R Us (USA)       |
    | Furball Inc. (USA)      |
    | Jet Set (England)       |
    | Jouets Et Ours (France) |
    | LT Supplies (USA)       |
    +-------------------------+ 
    
    //3、视图过滤数据
    create view custmailist as
    select cust_id , cust_name , cust_email from customers where cust_email is not NULL;
    +---------+----------------+---------------------+
    | cust_id | cust_name | cust_email | +---------+----------------+---------------------+
    |   10001 | wangjj         | wangjj@163.com      |
    |   10003 | Wascals        | rabbit@wascally.com |
    |   10004 | Yosemite Place | sam@yosemite.com    |
    |   10006 | Coyote Inc.    | ylee@coyote.com     |
    |   10007 | Coyote Inc.    | ylee@coyote.com     |
    |   10008 | Coyote Inc.    | ylee@coyote.com     |
    |   10009 | Coyote Inc.    | ylee@coyote.com     |
    |   10011 | Wascals        | rabbit@wascally.com |
    |   10012 | Yosemite Place | sam@yosemite.com    |
    |   10014 | Coyote Inc.    | ylee@coyote.com     |
    | 10015 | Coyote Inc. | ylee@coyote.com | +---------+----------------+---------------------+
    
    //4、视图操作
    create view name;//创建视图，将SQL包装。
    show create view name;//显示创建的视图语句。
    drop view name;//删除视图。
    create or replace viem name;//存在则更新，不存在则删除。

存储过程：是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，虽然它们的作用不仅限于批处理，类似于子函数所以也可以传递参数，里面可以写一些含有复杂逻辑功能的SQL语句，这区别于视图；执行存储过程类似与执行子函数的调用，因此通过call执行。

//1、创建存储过程
    delimiter //
    create procedure productpricing()
    begin
    select avg(prod_price) as priceavg
    from products;
    end //
    delimiter ;
    /* delimiter //是因为命令行客户机使用;作为命令结束符，而过程里面使用了; 所以为了过程完整性，暂时将命令结束符换为//,在过程编写完毕后，切换会原来的。 ()里面可以传入参数。 begin、end用于限定存储过程执行体。 */
    call productpricing();//调用过程
    +-----------+
    | priceavg  |
    +-----------+
    | 16.133571 |
    +-----------+
    
    //2、删除存储过程
    drop procedure productpricing;//删除存在的过程，否则报错。
    drop procedure if exists productpricing;//仅当存在删除则删除，不存在，语句也不报错。
    
    //3、使用带参数的存储过程，内部含有变量
    delimiter //
    create procedure productpricing(
        out plow decimal(8,2),
        out pmax decimal(8,2),
        out pavg decimal(8,2)
    )
    begin
        select avg(prod_price) into pavg from products;
        select min(prod_price) into plow from products;
        select max(prod_price) into pmax from products;
    end //
    delimiter ;
    /* out 表示过程输出参数，必须指定类型，调用需要指定临时变量。 in 表示调用着输入。 inout 表示值-结果传递过程。临时变量输入，执行完毕通过这临时变量返回结果。 into 表示将select的结果传递给临时变量。 */
    
    call productpricing(@pricelow , @pricemax , @priceavg); 
    //执行存储过程，将过程输入传入临时变量，变量必须用@引用。
    
    select @pricelow , @pricemax , @priceavg;
    //显示变量值
    +-----------+-----------+-----------+
    | @pricelow | @pricemax | @priceavg |
    +-----------+-----------+-----------+
    |      2.50 |     55.00 |     16.13 |
    +-----------+-----------+-----------+
    
    
    //创建带有输入的存储过程，输入订单号，返回订单号对应的总钱数。
    delimiter //
    create procedure ordertotal(
    in onumber int,
    out ototal decimal(8,2)
    )
    begin
    select sum(item_price * quantity) into ototal
    from orderitems
    where order_num = onumber;
    end //
    delimiter;  
    
    call ordertotal(20005 , @total);
    Query OK, 1 row affected (0.02 sec)
    
    select @total;//显示总价格
    +--------+
    | @total |
    +--------+
    | 149.87 |
    +--------+
    1 row in set (0.00 sec)
    
    //4、建立带有逻辑功能的存储过程
    
    //5、游标：存储的是SQL语句先前返回的结果集，然后可以逐条取出处理。
    //在当前点存储游标之后，应用程序可以根据需求滚动或浏览其中的数据。
    //游标只能在存储过程之中使用。构成循环的条件过程。很easy。

触发器：所谓触发器就是当某个事件发生时自动执行的SQL语句。例如

*  每当增加一个顾客到某个数据库表时，都检查其电话号码格式是否正确，州的缩写是否为大写；
 *  每当订购一个产品时，都从库存数量中减去订购的数量；
 *  无论何时删除一行，都在某个存档表中保留一个副本。  
    上述提到的功能，都应该使用触发器实现。只有delete、insert、update这三个关键字才支持触发器且只有表（视图不可以）才支持，触发器名必须唯一。

1、insert触发器
    触发器可以引用new，new为虚表，代表新插入的数据行，通过这个虚表访问交互。 
    create trigger newproduct after insert on products
    for each row select 'Product added' into @temp;
    /* create trigger表示创建触发器。 newproduct 触发器名，必须唯一。 after insert on products 在表插入成功之后执行，可以设定为before insert。 for each row 表示对每成功加入一行，都显示。 into 表示将信息输出到临时变量。 */
    
    insert into orders(cust_id , order_date )
    value(124 , '2005-10-23 00:00:00'),(234 , '2005-10-23 00:00:01');
    Query OK, 2 rows affected (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    mysql> select @temp;
    +---------------+
    | @temp         |
    +---------------+
    | Product added |
    +---------------+
    1 row in set (0.00 sec)
    
    insert into orders(order_date , cust_id)
    values(
    now(),
    10001
    );
    mysql> select @temp;
    +-------+
    | @temp |
    +-------+
    | 20010 |
    +-------+   
    
    2、delete触发器
    delete触发器代码内，可以引用一个只读的old虚表，访问那些被删除的行。
    delimiter //
    create trigger deleteorder before delete on orders
    for each row
    begin
    insert into archive_orders(order_num , order_date , cust_id)
    values(
        old.order_num,
        old.order_date,
        old.cust_id
    );
    end //
    delimiter ;
    //任意订单被删除前都将执行此触发器。
    //创建一个删除的触发器，删除之前将删除的备份，使用old访问被删除的内容。 
    
    //3、update触发器
    //UPDATE触发器在UPDATE语句执行之前或之后执行。
    //在UPDATE触发器代码中，可以引用一个名为只读old的虚拟表访问以前（UPDATE语句前）的值，
    //引用一个名为NEW的虚拟表访问新更新的值。触发器访问old和new。
    
    create trigger updateevendor before update on vendors
    for each row
    begin
    set new.vend_sate = upper(new.vend_state);
    end
    //在update之前，先执行触发器代码，将输入行变成大写字母，在更新进表中。
    //这个触发器使用起来很鸡贼。

## 12、使用事务功能 ##

只有InnoDB存储引擎全面支持事务功能，具体事务是啥，在下一篇博客中详细解答。  
事务表示成批的SQL语句，用于维护数据库的完整性，指成批的SQL语句要么完成执行，要么完成不执行。  
只有insert、delete、update语句才支持事物处理。  
关于事物处理的专业术语：

*  事务（transaction）指一组SQL语句；
 *  回退（rollback）指撤销指定SQL语句的过程；
 *  提交（commit）指将未存储的SQL语句结果写入数据库表；
 *  保留点（savepoint）指事务处理中设置的临时占位符（placeholder），你可以对它发布回退（与回退整个事务处理不同）。
 *  start事务之后，必须通过回退或者提交结束，所以要么SQL语句生效，要么失效。

//1、使用rollback
    //撤销整个事务(SQL语句执行结果)处理，
    //执行SQL，发生错误则执行回滚操作。
    mysql> start transaction;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> delete from customers;
    Query OK, 15 rows affected (0.00 sec)
    
    mysql> select * from customers;
    Empty set (0.01 sec)
    
    mysql> rollback;
    Query OK, 0 rows affected (0.16 sec)
    
    mysql> select * from customers;
    
    /* 只有insert、delete、update语句才支持事物处理,因为只有删除、更新或者插入修改了表，吃后悔药才有用。不能回退create或drop */
    
    //2、使用commit
    //一般的MySQL语句都是直接针对数据库表执行和编写。这就是隐含提交（implicit commit），
    //即SQL执行结果提交（写或保存）操作是自动进行。
    //而通过事务必须通过指令保证提交，保证所以语句执行成功，才写入数据库，否则数据库不变。
    mysql> select * from orders;
    +-----------+---------------------+---------+
    | order_num | order_date          | cust_id |
    +-----------+---------------------+---------+
    |     20005 | 2005-09-01 00:00:00 |   10001 |
    |     20006 | 2005-09-12 00:00:00 |   10003 |
    |     20007 | 2005-09-30 00:00:00 |   10004 |
    |     20008 | 2005-10-03 00:00:00 |   10005 |
    |     20009 | 2005-10-08 00:00:00 |   10001 |
    |     20010 | 2005-10-23 00:00:00 |     123 |
    |     20013 | 2005-10-23 00:00:00 |     124 |
    |     20014 | 2005-10-23 00:00:01 |     234 |
    +-----------+---------------------+---------+
    8 rows in set (0.00 sec)
    
    mysql> start transaction;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> delete from orders where order_num = 20014;
    Query OK, 1 row affected (0.01 sec)
    
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from orders;
    +-----------+---------------------+---------+
    | order_num | order_date          | cust_id |
    +-----------+---------------------+---------+
    |     20005 | 2005-09-01 00:00:00 |   10001 |
    |     20006 | 2005-09-12 00:00:00 |   10003 |
    |     20007 | 2005-09-30 00:00:00 |   10004 |
    |     20008 | 2005-10-03 00:00:00 |   10005 |
    |     20009 | 2005-10-08 00:00:00 |   10001 |
    |     20010 | 2005-10-23 00:00:00 |     123 |
    |     20013 | 2005-10-23 00:00:00 |     124 |
    +-----------+---------------------+---------+
    7 rows in set (0.00 sec)
    //当commit或rollback执行完毕后，将自动更改为隐含提交。也就是再执行普通的SQL语句，将自动提交。
    
    
    3、使用savepoint
    /* rollback可以撤销整个事务，commit可以提交整个事务。但是对于复杂，可以需要部分 提交或者回退。于是就有了保留点。 为了支持回退部分事务处理，必须能在事务处理块中合适的位置放占位符。这样，如果需要回退，可以回退到某个占位符处。这些占位符称为保留点。为了创建占位符，使用savepoint delete1; 占位符名字必须唯一。 */
    savepoint delete1;
    rollback to delete1;
    //保留点越多越好。保留点在事务处理完成（rollback或commit被执行）之后自动释放。
    //release savepoint 也可以明确释放保留点。
    
    //4、更改默认的提交行为
    /* InnoDB引擎对于所以的SQL语句都是支持事务的。只是默认的MySQL行为是自动提交所有更改。执行任何SQL语句，实际上都是针对表执行，所做的更改立即生效。 为了指示不自动提交更改。可以设定set autocommit = 0; autocommit标志是针对每个连接而不是服务器的。 设置autocommit为0(假)指示MySQL不自动提交更改，每次SQL语句想生效必须使用commit。 （直到autocommit被设置为真为止）。 */

## 13、使用全文搜索 ##

**MySQL5.7版本的InnoDB，已经全面支持了全文索引功能，所以不要再InnoDB不支持全文索引了。**  
为什么需要有全文索引？

## 14、使用索引 ##

create index idx_book_card on book(card);//在book的card上创建一个索引叫idx_book_card
    show index from book;//显示表中全部索引
    drop index idx_article_ccv on article;//删除article表中的idx_article_ccv索引。

## 15、MySQL支持的数据类型 ##

1、整型  
在MySQL中支持的 5 个主要整数类型是TINYINT，SMALLINT，MEDIUMINT，INT和 BIGINT。这些类型在很大程度上是相同的，只有它们存储的值的字节是不相同的。  
tinyint对应1字节、smallint对应2字节、mediumint对应3字节、int或integer4字节、bigint对应8字节。也可以指定显示宽度。  
例如 int(11)表示整数，显示宽度是11字节。  
2、浮点型  
MySQL使用浮点数和定点数来表示小数。包含的类型有：  
浮点类型：float、double。  
定点类型：decimal。  
也可以指定显示宽度。  
float(7,3)，显示宽度是7位，小数点后面有4位。  
3、字符串类型  
MySQL 提供了8个基本的字符串类型,分别:char、varchar。  
char占用字节大小为0-255字节用于定长字符串。varchar占用0-255字节属于变长字符串。

**括号修饰符必须区分与整形的含义。**

char类型用于定长字符串，并且必须在圆括号内用一个大小修饰符来定义。这个大小修饰符的范围从0-255。比指定长度大的值将被截短，而比指定长度小的值将会用空格作填补，类似于静态分配内存。  
varchar类型也必须带有一个范围在0-255 之间的指示器，这个指示器指定了此类型可以存储的最大字符，当实际字符小于指示器值，则实际所只用空间是实际字符大小。不会用空格弥补。这个可以节约空间，相当于内部使用了sizeof大小，然后malloc动态分配内存。

# SQL性能优化 #

## 1. 为查询缓存优化你的查询 ##

大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一，而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候，这些查询结果会被放到一个缓存中，这样，后续的相同的查询就不用操作表而直接访问缓存结果了。

这里最主要的问题是，对于程序员来说，这个事情是很容易被忽略的。因为，我们某些查询语句会让MySQL不使用缓存。请看下面的示例：

// 查询缓存不开启
    $r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()"); // 开启查询缓存 $today = date("Y-m-d"); $r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

上面两条SQL语句的差别就是CURDATE() ，MySQL的查询缓存对这个函数不起作用。所以，像NOW()和RAND()或是其它的诸如此类的SQL函数都不会开启查询缓存，因为这些函数的返回是会不定的易变的。所以，你所需要的就是用一个变量来代替MySQL的函数，从而开启缓存。

## 2. explain你的select查询 ##

explain的查询结果还会告诉你索引主键被如何利用的，你的数据表是如何被搜索和排序等。通过explain可以给出MySQ如何理解你发送给他的一条SQL指令。  
![这里写图片描述][70 2]

**1、id 重要**  
id表示select查询的序列号包含一组数字，表示查询过程中MySQL加载表的顺序。  
![这里写图片描述][70 3]

**2、select\_type**  
表示查询的类型，用于区别普通查询、联合查询、子查询等复杂查询。

*  SIMPLE：简单SELECT查询，查询中不包含子查询或者union。
 *  PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为此。
 *  SUBQUERY：在select或where里面的子查询。
 *  DERIVED：在from列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放到临时表。
 *  UNION：UNION中的第二个或后面的SELECT语句。
 *  UNION RESULT：两个查询结果，UNION合并的结果。

**2、type重要**  
联接之后的每个表的访问类型，type显示的是访问表类型（例如某个表需要全局扫描ALL，某个表通过索引范围访问等等），是较为重要的一个指标，结果值从好到坏依次是：  
system > const > eq\_ref > ref > fulltext > ref\_or\_null > index\_merge > unique\_subquery > index\_subquery > range > index > ALL（这是官网的排序。）

system > const > eq\_ref > ref > range > index > ALL（这是工作中常见的排序。）  
一般来说，得保证查询至少达到range级别，最好能达到ref。

*  system：表只有一条记录(=系统表)。这是const联接类型的一个特例，几乎不会出现，可忽略。
 *  const：表示通过索引一次就可以找到，const用于primary key或unique索引。因为匹配一个数据，通过主键，索引一次即可。  
    ![这里写图片描述][70 4]
 *  eq\_ref：唯一性索引扫描，对于每个索引键，**表中只有一条记录与之匹配**。  
    ![这里写图片描述][70 5]
 *  ref：非唯一性索引扫描，返回匹配某个单独值的所有行。例如银行中很多交小明。通过where name=小明，则返回所有行。  
    ![这里写图片描述][70 6]
 *  range：只检索给定范围的行，一般是使用一个索引，然后通过where id between a to b的范围查找。  
    ![这里写图片描述][70 7]
 *  index：index与ALL的区别在于index类型只遍历索引树，通常比ALL块，因为索引文件比较小，加载到内存也很快。  
    ![这里写图片描述][70 8]
 *  ALL：全表扫描。对于每个来自于先前的表的行组合,进行完整的表扫描。  
    ![这里写图片描述][70 9]

**一般来说通过调整SQL语句实现相同功能，至少保证查询达到range级别，最好能达到ref。**

**2、possible\_keys、key**  
possible\_keys：这次select，可能应用到该表中的索引有多个，但是最后查询不一定使用其中的某一个。  
key：实际使用的索引。如果为NULL则没有使用索引。**若查询中使用了覆盖索引，则该索引和查询的select字段重叠。**  
![这里写图片描述][70 10]

**3、key\_len**  
显示MySQL决定可能使用的键最大长度，但并不一定是真是的长度如果键是NULL,则长度为NULL，在不损失精度的情况下，长度越短越好。

[https://www.cnblogs.com/yycc/p/7338894.html][https_www.cnblogs.com_yycc_p_7338894.html]，这个博客解释了explain输出各项的作用。

**4、ref**  
显示被引用了哪些列的索引，如果可能的话是一个常数。  
![这里写图片描述][70 11]

**5、rows重要**  
显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。  
![这里写图片描述][70 12]  
**6、extra重要**  
该列包含MySQL解决查询的详细信息。

*  **Using filesort最重要：**说明MySQL会对数据使用一个额外的排序，而不是按照表内的索引排序直接将数据读取出来。这种无法利用索引已经排好序的优势的特点成为外部排序，大数据中出现是非常不好的情况，如果可以尽快优化起来。  
    ![这里写图片描述][70 13]
 *  **Using temporary最重要：**使用了临时表保存中间结果，MySQL在对查询排序时，使用了临时表。常见于order by和分组。这种就会出现很大的问题，最好立马修改。  
    ![这里写图片描述][70 14]
 *  **Using index最重要：**说明select操作使用了覆盖索引，避免了访问表的数据行，效果不错。如果同时出现了using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，则表明索引用来读取而非进行查找操作。覆盖索引：需要查找的字段，可以直接从索引文件中获取，而无需读取表的行。因为索引种存储了某些列的信息，详细理解索引原理就可以理解，因此要使用覆盖索引，那么就不要使select \*。因为全部字段加索引，那和建立表有啥区别呢？  
    ![这里写图片描述][70 15]

![这里写图片描述][70 16]

挑一个你的select语句（推荐挑选那个最复杂的，有多表联接的），把关键字EXPLAIN加到前面。你可以使用phpmyadmin来做这个事。然后，你会看到一张表格。下面的这个示例中，我们忘记加上了group\_id索引，并且有表联接：  
![这里写图片描述][70 17]  
当我们为 group\_id 字段加上索引后：  
![这里写图片描述][70 18]  
我们可以看到，前一个结果显示搜索了7883行，而后一个只是搜索了两个表的9和16 行。查看rows列可以让我们找到潜在的性能问题。

### 索引优化案例分析 ###

## 3. 当只要一行数据时使用LIMIT 1 ##

当你查询表的有些时候，你已经知道结果只会有一条结果，但因为你可能需要去fetch游标，或是你也许会去检查返回的记录数。

在这种情况下，加上LIMIT 1可以增加性能。这样一来，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。

下面的示例，只是为了找一下是否有“中国”的用户，很明显，后面的会比前面的更有效率。（请注意，第一条中是Select \*，第二条是Select 1）

// 没有效率的：
    $r = mysql_query("SELECT * FROM user WHERE country = 'China'");
    if (mysql_num_rows($r) > 0) {
        // ...
    }
    
    // 有效率的：
    $r = mysql_query("SELECT 1 FROM user WHERE country = 'China' LIMIT 1");
    if (mysql_num_rows($r) > 0) {
        // ...
    }

## 4. 为搜索字段建索引 ##

索引并不一定就是给主键或是唯一的字段。如果在你的表中，有某个字段你总要会经常用来做搜索，那么，请为其建立索引吧。  
![这里写图片描述][70 19]  
从上图你可以看到那个搜索字串 “last\_name LIKE ‘a%’”，一个是建了索引，一个是没有索引，性能差了4倍左右。

另外，你应该也需要知道什么样的搜索是不能使用正常的索引的。例如，当你需要在一篇大的文章中搜索一个词时，如：“WHERE post\_content LIKE ‘%apple%’”，索引可能是没有意义的。**你可能需要使用MySQL全文索引**或是自己做一个索引（比如说：搜索关键词或是Tag什么的）。

## 5. 在Join表的时候使用相当类型的例，并将其索引 ##

如果你的应用程序有很多join查询，你应该确认两个表中Join的字段是被建过索引的。这样，MySQL内部会启动为你优化Join的SQL语句的机制。

而且，这些被用来Join的字段，应该是相同的类型的。例如：如果你要把DECIMAL字段和一个INT字段Join在一起，MySQL就无法使用它们的索引。对于那些STRING类型，还需要有相同的字符集才行。（两个表的字符集有可能不一样）

// 在state中查找company
    $r = mysql_query("SELECT company_name FROM users LEFT JOIN companies ON (users.state = companies.state) WHERE users.id = $user_id"); // 两个 state 字段应该是被建过索引的，而且应该是相当的类型，相同的字符集。

## 6. 千万不要ORDER BY RAND() ##

想打乱返回的数据行？随机挑一个数据？真不知道谁发明了这种用法，但很多新手很喜欢这样用。但你却不了解这样做有多么可怕的性能问题。

如果你真的想把返回的数据行打乱了，你有N种方法可以达到这个目的。这样使用只让你的数据库的性能呈指数级的下降。这里的问题是：MySQL会不得不去执行RAND()函数（很耗CPU时间），而且这是为了每一行记录去执行，然后再对其排序。就算是你用了Limit 1也无济于事（因为要排序）

下面的示例是随机挑一条记录

// 千万不要这样做：
    $r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1"); // 这要会更好： $r = mysql_query("SELECT count(*) FROM user"); $d = mysql_fetch_row($r); $rand = mt_rand(0,$d[0] - 1); $r = mysql_query("SELECT username FROM user LIMIT $rand, 1");

## 7. 避免 SELECT \* ##

从数据库里读出越多的数据，那么查询就会变得越慢。并且，如果你的数据库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载。

所以，你应该养成一个需要什么就取什么的好的习惯。

//不推荐
    $r = mysql_query("SELECT * FROM user WHERE user_id = 1");
    $d = mysql_fetch_assoc($r);
    echo "Welcome { $d['username']}";
    
    //推荐
    $r = mysql_query("SELECT username FROM user WHERE user_id = 1");
    $d = mysql_fetch_assoc($r);
    echo "Welcome { $d['username']}";

## 8. 永远为每张表设置一个ID ##

我们应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的（推荐使用UNSIGNED），并设置上自动增加的AUTO\_INCREMENT标志。

就算是你`users`表有一个主键叫“email”的字段，你也别让它成为主键。使用`VARCHAR`类型来当主键会使用得性能下降。另外，在你的程序中，你应该使用表的ID来构造你的数据结构。

而且，在MySQL数据引擎下，还有一些操作需要使用主键，在这些情况下，主键的性能和设置变得非常重要，比如，集群，分区……

在这里，只有一个情况是例外，那就是“关联表”的“外键”，也就是说，这个表的主键，通过若干个别的表的主键构成。我们把这个情况叫做“外键”。比如：有一个“学生表”有学生的ID，有一个“课程表”有课程ID，那么，“成绩表”就是“关联表”了，其关联了学生表和课程表，在成绩表中，学生ID和课程ID叫“外键”其共同组成主键。

## 9. 使用ENUM而不是VARCHAR ##

ENUM 类型是非常快和紧凑的。在实际上，其保存的是`TINYINT`，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美。

如果你有一个字段，比如“性别”，“国家”，“民族”，“状态”或“部门”，你知道这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是 VARCHAR。

MySQL也有一个“建议”（见第十条）告诉你怎么去重新组织你的表结构。当你有一个 VARCHAR 字段时，这个建议会告诉你把其改成 ENUM 类型。使用 PROCEDURE ANALYSE() 你可以得到相关的建议。

## 10. 从 PROCEDURE ANALYSE() 取得建议 ##

PROCEDURE ANALYSE() 会让MySQL帮你去分析你的字段和其实际的数据，并会给你一些有用的建议。只有表中有实际的数据，这些建议才会变得有用，因为要做一些大的决定是需要有数据作为基础的。

例如，如果你创建了一个`INT`字段作为你的主键，然而并没有太多的数据，那么，PROCEDURE ANALYSE()会建议你把这个字段的类型改成`MEDIUMINT`。或是你使用了一个`VARCHAR`字段，因为数据不多，你可能会得到一个让你把它改成`ENUM`的建议。这些建议，都是可能因为数据不够多，所以决策做得就不够准。

在phpmyadmin里，你可以在查看表时，点击 “Propose table structure” 来查看这些建议  
![这里写图片描述][70 20]  
一定要注意，这些只是建议，只有当你的表里的数据越来越多时，这些建议才会变得准确。一定要记住，你才是最终做决定的人。

## 11. 尽可能的使用 NOT NULL ##

除非你有一个很特别的原因去使用NULL值，你应该总是让你的字段保持NOT NULL。这看起来好像有点争议，请往下看。

首先，问问你自己“Empty”和“NULL”有多大的区别（如果是INT，那就是0和NULL）？如果你觉得它们之间没有什么区别，那么你就不要使用NULL。（你知道吗？在Oracle里，NULL和Empty的字符串是一样的！)

不要以为NULL不需要空间，其需要额外的空间，并且，在你进行比较的时候，你的程序会更复杂。 当然，这里并不是说你就不能使用NULL了，现实情况是很复杂的，依然会有些情况下，你需要使用NULL值。

下面摘自MySQL自己的文档：

> “NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”

## 12. Prepared Statements ##

Prepared Statements很像存储过程，是一种运行在后台的SQL语句集合，我们可以从使用prepared statements获得很多好处，无论是性能问题还是安全问题。

Prepared Statements 可以检查一些你绑定好的变量，这样可以保护你的程序不会受到“**SQL注入式**”攻击。当然，你也可以手动地检查你的这些变量，然而，手动的检查容易出问题，而且很经常会被程序员忘了。当我们使用一些framework或是ORM的时候，这样的问题会好一些。

在性能方面，当一个相同的查询被使用多次的时候，这会为你带来可观的性能优势。你可以给这些Prepared Statements定义一些参数，而MySQL只会解析一次。

虽然最新版本的MySQL在传输Prepared Statements是使用二进制形式，所以这会使得网络传输非常有效率。

当然，也有一些情况下，我们需要避免使用Prepared Statements，因为其不支持查询缓存。但据说版本5.1后支持了。

在PHP中要使用prepared statements，你可以查看其使用手册：mysqli 扩展 或是使用数据库抽象层，如： PDO.

// 创建 prepared statement
    if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) {
    
        // 绑定参数
        $stmt->bind_param("s", $state);
    
        // 执行
        $stmt->execute();
    
        // 绑定结果
        $stmt->bind_result($username);
    
        // 移动游标
        $stmt->fetch();
    
        printf("%s is from %s\n", $username, $state);
    
        $stmt->close();
    }

## 13. 无缓冲的查询 ##

正常的情况下，当你在你的脚本中执行一个SQL语句的时候，你的程序会停在那里直到没这个SQL语句返回，然后你的程序再往下继续执行。你可以使用无缓冲查询来改变这个行为。

关于这个事情，在PHP的文档中有一个非常不错的说明： mysql\_unbuffered\_query() 函数：

> “mysql\_unbuffered\_query() sends the SQL query query to MySQL without automatically fetching and buffering the result rows as mysql\_query() does. This saves a considerable amount of memory with SQL queries that produce large result sets, and you can start working on the result set immediately after the first row has been retrieved as you don’t have to wait until the complete SQL query has been performed.”

上面那句话翻译过来是说，mysql\_unbuffered\_query() 发送一个SQL语句到MySQL而并不像mysql\_query()一样去自动fethch和缓存结果。这会相当节约很多可观的内存，尤其是那些会产生大量结果的查询语句，并且，你不需要等到所有的结果都返回，只需要第一行数据返回的时候，你就可以开始马上开始工作于查询结果了。

然而，这会有一些限制。因为你要么把所有行都读走，或是你要在进行下一次的查询前调用 mysql\_free\_result() 清除结果。而且， mysql\_num\_rows() 或 mysql\_data\_seek() 将无法使用。所以，是否使用无缓冲的查询你需要仔细考虑。

## 14. 把IP地址存成 UNSIGNED INT ##

很多程序员都会创建一个 VARCHAR(15) 字段来存放字符串形式的IP而不是整形的IP。如果你用整形来存放，只需要4个字节，并且你可以有定长的字段。而且，这会为你带来查询上的优势，尤其是当你需要使用这样的WHERE条件：IP between ip1 and ip2。

我们必需要使用UNSIGNED INT，因为 IP地址会使用整个32位的无符号整形。

而你的查询，你可以使用 INET\_ATON() 来把一个字符串IP转成一个整形，并使用 INET\_NTOA() 把一个整形转成一个字符串IP。在PHP中，也有这样的函数 ip2long() 和 long2ip()。

1  
r="UPDATEusersSETip=INETATON(′$SERVER\[′REMOTEADDR′\]′)WHEREuserid=  r =" U P D A T E u s e r s S E T i p = I N E T A T O N ( ′ $ S E R V E R \[ ′ R E M O T E A D D R ′ \] ′ ) W H E R E u s e r i d = user\_id”;  
15. 固定长度的表会更快  
如果表中的所有字段都是“固定长度”的，整个表会被认为是 “static” 或 “fixed-length”。 例如，表中没有如下类型的字段： VARCHAR，TEXT，BLOB。只要你包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了，这样，MySQL 引擎会用另一种方法来处理。

固定长度的表会提高性能，因为MySQL搜寻得会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。

并且，固定长度的表也更容易被缓存和重建。不过，唯一的副作用是，固定长度的字段会浪费一些空间，因为定长的字段无论你用不用，他都是要分配那么多的空间。

使用“垂直分割”技术（见下一条），你可以分割你的表成为两个一个是定长的，一个则是不定长的。

1.  垂直分割  
    “垂直分割”是一种把数据库中的表按列变成几张表的方法，这样可以降低表的复杂度和字段的数目，从而达到优化的目的。（以前，在银行做过项目，见过一张表有100多个字段，很恐怖）

示例一：在Users表中有一个字段是家庭地址，这个字段是可选字段，相比起，而且你在数据库操作的时候除了个人信息外，你并不需要经常读取或是改写这个字段。那么，为什么不把他放到另外一张表中呢？ 这样会让你的表有更好的性能，大家想想是不是，大量的时候，我对于用户表来说，只有用户ID，用户名，口令，用户角色等会被经常使用。小一点的表总是会有好的性能。

示例二： 你有一个叫 “last\_login” 的字段，它会在每次用户登录时被更新。但是，每次更新时会导致该表的查询缓存被清空。所以，你可以把这个字段放到另一个表中，这样就不会影响你对用户ID，用户名，用户角色的不停地读取了，因为查询缓存会帮你增加很多性能。

另外，你需要注意的是，这些被分出去的字段所形成的表，你不会经常性地去Join他们，不然的话，这样的性能会比不分割时还要差，而且，会是极数级的下降。

1.  拆分大的 DELETE 或 INSERT 语句  
    如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询，你需要非常小心，要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的，表一锁住了，别的操作都进不来了。

Apache 会有很多的子进程或线程。所以，其工作起来相当有效率，而我们的服务器也不希望有太多的子进程，线程和数据库链接，这是极大的占服务器资源的事情，尤其是内存。

如果你把你的表锁上一段时间，比如30秒钟，那么对于一个有很高访问量的站点来说，这30秒所积累的访问进程/线程，数据库链接，打开的文件数，可能不仅仅会让你泊WEB服务Crash，还可能会让你的整台服务器马上掛了。

所以，如果你有一个大的处理，你定你一定把其拆分，使用 LIMIT 条件是一个好的方法。下面是一个示例：

while (1) \{  
//每次只做1000条  
mysql\_query(“DELETE FROM logs WHERE log\_date <= ‘2009-11-01’ LIMIT 1000”);  
if (mysql\_affected\_rows() == 0) \{  
// 没得可删了，退出！  
break;  
\}  
// 每次都要休息一会儿  
usleep(50000);  
\}  
18. 越小的列会越快  
对于大多数的数据库引擎来说，硬盘操作可能是最重大的瓶颈。所以，把你的数据变得紧凑会对这种情况非常有帮助，因为这减少了对硬盘的访问。

参看 MySQL 的文档 Storage Requirements 查看所有的数据类型。

如果一个表只会有几列罢了（比如说字典表，配置表），那么，我们就没有理由使用 INT 来做主键，使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。如果你不需要记录时间，使用 DATE 要比 DATETIME 好得多。

当然，你也需要留够足够的扩展空间，不然，你日后来干这个事，你会死的很难看，参看Slashdot的例子（2009年11月06日），一个简单的ALTER TABLE语句花了3个多小时，因为里面有一千六百万条数据。

1.  选择正确的存储引擎  
    在 MySQL 中有两个存储引擎 MyISAM 和 InnoDB，每个引擎都有利有弊。酷壳以前文章《MySQL: InnoDB 还是 MyISAM?》讨论和这个事情。

MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要update一个字段，整个表都会被锁起来，而别的进程，就算是读进程都无法操作直到读操作完成。另外，MyISAM 对于 SELECT COUNT(\*) 这类的计算是超快无比的。

InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。他是它支持“行锁” ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务。

下面是MySQL的手册

target=”\_blank”MyISAM Storage Engine  
InnoDB Storage Engine  
20. 使用一个对象关系映射器（Object Relational Mapper）  
使用 ORM (Object Relational Mapper)，你能够获得可靠的性能增涨。一个ORM可以做的所有事情，也能被手动的编写出来。但是，这需要一个高级专家。

ORM 的最重要的是“Lazy Loading”，也就是说，只有在需要的去取值的时候才会去真正的去做。但你也需要小心这种机制的副作用，因为这很有可能会因为要去创建很多很多小的查询反而会降低性能。

ORM 还可以把你的SQL语句打包成一个事务，这会比单独执行他们快得多得多。

目前，个人最喜欢的PHP的ORM是：Doctrine。

1.  小心“永久链接”  
    “永久链接”的目的是用来减少重新创建MySQL链接的次数。当一个链接被创建了，它会永远处在连接的状态，就算是数据库操作已经结束了。而且，自从我们的Apache开始重用它的子进程后——也就是说，下一次的HTTP请求会重用Apache的子进程，并重用相同的 MySQL 链接。

PHP手册：mysql\_pconnect()  
在理论上来说，这听起来非常的不错。但是从个人经验（也是大多数人的）上来说，这个功能制造出来的麻烦事更多。因为，你只有有限的链接数，内存问题，文件句柄数，等等。

而且，Apache 运行在极端并行的环境中，会创建很多很多的了进程。这就是为什么这种“永久链接”的机制工作地不好的原因。在你决定要使用“永久链接”之前，你需要好好地考虑一下你的整个系统的架构。

[70]: /images/20220525/0cb33e8f6d54430f972ecfa2e6f7cd5c.png
[Link 1]: https://blog.csdn.net/u010710458/article/details/77441909
[70 1]: /images/20220525/50d4968e2ce54153866af2804fd029dd.png
[70 2]: /images/20220525/1d92acfb2b7e440c81df620065ddb985.png
[70 3]: /images/20220525/a16707dc95e84b00b4450ea495f4ff22.png
[70 4]: /images/20220525/2bf0cb20e05c4e3d9aeac0e5c226c25e.png
[70 5]: /images/20220525/6b8e4a66feae4cac8f116e0bca19be02.png
[70 6]: /images/20220525/6c24982cec204afc90149c41d5469fc5.png
[70 7]: /images/20220525/91067fb1a64048fb8a28e991481f446b.png
[70 8]: /images/20220525/4e48425e1cb54918a14bab311ac4478b.png
[70 9]: /images/20220525/f7b747d1dd4844919d03570aead389f6.png
[70 10]: /images/20220525/ddedbaf185f34820b7001e9f2412072a.png
[https_www.cnblogs.com_yycc_p_7338894.html]: https://www.cnblogs.com/yycc/p/7338894.html
[70 11]: /images/20220525/d31b931e61004a36b02e84ca4082fefd.png
[70 12]: /images/20220525/c8924576c7924c1185a62bf18960e4eb.png
[70 13]: /images/20220525/ffb6b387db104893a1b7ddc57cbed0c4.png
[70 14]: /images/20220525/d0cfa0e8eb124d4bb7fd5c8d6bf64241.png
[70 15]: /images/20220525/2ec1ddbddc1746e594596deebdfec17a.png
[70 16]: /images/20220525/2fa08507550f4698a0786a542f638ea0.png
[70 17]: /images/20220525/81c05ddfa707405ea993406f4d13d080.png
[70 18]: /images/20220525/3a17e2a9579440b98a41e36bca3197c2.png
[70 19]: /images/20220525/b9462888812f4035a038868044631b7f.png
[70 20]: /images/20220525/e062f0688f414dfbb4f254f48d9e103d.png