MySQL必知必会（1-13章）

自动增量式mysql可以自动为每个行为分配一个可用编号，不用再添加一行时手动分配唯一值。

show databases; 显示数据库

use testdb(database);切换数据库

show tables；返回当前选择的数据库内可用表的列表

show columns from defend_log; 显示表的列，也可以用DESCRIBE defend_log,即是前面的快捷方式。

show status;用于显示广泛的服务器状态信息

show create database testdb; 显示数据库testdb的创建语句

show create table defend_log; 显示数据表defend_log的创建语句

show grants; 显示授予用户的安全权限

show errors; show warnings; 显示服务器错误或警告消息

help show; 显示允许的show语句

MySQL 5支持一个新的INFORMATION_SCHEMA命令，可用来获得和过滤模式信息

DISTINCT：显示不同的值，去除相同的值

SELECT prod_name FORM products LIMIT 5; 显示前五行，带一个值得是行数，从第一行开始

SELECT prod_name FROM products LIMIT 5, 5; 后两个值指定要检索的开始行和行数，即从第5行开始的5行。第一个数为开始位置，第二个数为要检索的行数。

DESC：降序，只应用到直接位于其前面的列名。如果想在多个列上进行降序排序，必须对每个列制定DESC关键字。

SELECT proid_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name;

先以prod_price降序排列，然后再以prod_name升序排序

使用ORDER BY和LIMIT组合，可以找出一个列中最高或最低的值。

SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 1; //选出价格最高的一行

在给出ORDER BY子句时，应保证它位于FROM子句之后,且是最后一个子句。如果使用LIMIT，则LIMIT必须位于ORDER BY之后。

同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE之后，否则将会产生错误。

空值检查：可以检查一个列是否包含空值，空值表示为NULL，不包含0，空字符串，或仅仅包含空格

SELECT prod_name FROM products WHERE prod_price IS NULL;  返回价格为空的产品

AND 优先级高于OR

SELECT prod_name, prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;  

返回id为1002 或1003中价格大于10的产品名称，价格

SELECT prod_name, prod_price FROM products WHERE vend_id IN (1002, 1003) ORDER BY prod_name;

用or也可以实现

SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 ORDER BY prod_name;

IN优点：

比OR执行速度快，且可以包含其他SELECT语句

使用通配符和正则表达式进行匹配时，需要用''单引号将其表达式包围起来。

使用通配符的时候要使用LIKE操作符，表示使用通配符匹配而不是相等。

通配符%匹配任何字符出现任何次数  ‘jet%’匹配以jet开头出现的所有字符串

_:匹配一个字符

通配符搜索一般要比前面的其他搜索所花时间更长

正则表达式匹配，将LIKE代替为REGEXP,表示REGEXP后面使用的是正则表达式

LIKE与REGEXP的一个重要区别是：LIKE匹配整个列，如果被匹配的文本在列中出现，LIKE不会找到他，不会返回该行。

REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，该行会被返回。要使REGEXP像LIKE一样匹配整个行，需要使用^(开始)与$(结束)定位符。

如下：

mysql> select prod_name from products where prod_name LIKE '1000';
Empty set (0.00 sec)

mysql> select prod_name from products where prod_name REGEXP '1000';
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
+--------------+
1 row in set (0.00 sec)

|:匹配两个串之一，相当于or，如'1000|2000|3000'

[123]匹配1 或2或3

[^123]除1 2 3这几个字符之外的其他任何东西

匹配范围[1-9] [a-z]匹配任意字符

特殊字符用\转义   \\-表示查找-    \\.表示查找.

*：0或多个匹配   +：1或多个匹配相当于{1，}  ？：0个或1个匹配相当于{0， 1}

{n}：指定书目的匹配   {n, }:不少于指定数目的匹配  {n, m}:匹配数目的范围

^: 文本的开始  $:文本的结束   [[:<:]]词的开始   [[:>:]]词的结尾

如果找一个以一个数开始（包括小数点开始的数），[0-9\\.]或[[:digit:]\\.]

select prod_name from products where prod_name REGEXP '^[0-9\\.]' order by prod_name;

^有两种用法：如果在集合中[^]则是找不包含该集合的行，如果不在集合中表示串的开始

创建计算字段：

Concat():拼接字符串，各个串之间用,隔开

SELECT Concat(vend_name, '(', vend_country, ')') FROM vendors ORDER BY vend_name;

RTrim():去掉右边的所有空格；LTrim():去掉左边的所有空格；Trim():去掉左右两边的空格

AS可以表示别名，如果一列太长，可以使用别名(或者也可以省略)。

select Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') AS vend_title FROM vendors order by vend_name;

NOW():返回当前日期和时间，select Now();

数据处理函数：

SubString():找出子串的字符， Upper():串转换为大写， Lower():小写

Locate():找出串的一个子串   Soundex：返回串的SOUNDEX，即对串的发音进行比较而不是字母

SELECT cust_name, cust_contact FROM customers WHERE Soundex(cust_contact) = Soundex('Y.Lie')

找到cust_contact的发音与Y.Lie发音相似的行

Exp():返回一个数的指数  Rand():返回一个随机数

COUNT(): 

使用COUNT(*)对表中行的数目进行计数，不管表中包含的是空值还是非空值

使用COUNT(column)对特定列中具有值得行进行技术，忽略NULL值

sum():返回某列的和

AVG(),COUNT(), MAX(), MIN(), SUM():都可指定ALL和DISTINCT值，ALL表示所有行执行计算，是默认的，DISTINCT表示只对不同的行进行计算，如

select avg(distinct prod_price) as avg_price from products where vend_id = 1003;

DISTINCT不能用于COUNT(*),DISTINCT必须使用列名，不能用于计算或表达式，DISTINCT用于MIN()与MAX（）并没有实际意义。

使用这些聚集函数一般比在应用程序中计算要快的多。

Group by对数据进行分组，之后队每个组而不是整个结果集进行聚集，group by经常和聚集函数一起使用，如：

select vend_id, count(*) as num_prods from products group by vend_id;  //按vend_id进行分组，且分别对每个分组进行行数统计，不包括NULL

可以使用WITH ROLLUP关键字，队每个分组以及每个分组汇总级别的值，结果包括了NULL,

使用Group by的一些规定：

1 Group by子句可以包含任意数目的列，使得能对分组进行嵌套，为数据分组提供更细致的控制

2 如果Group by子句中嵌套了分组，数据将在最后规定的分组上进行汇总，

3 Group by子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在select中使用表达式，则必须在group by子句中指定相同的表达式，不能使用别名

4 除聚集语句外(select中可以有聚集函数，聚集函数的列不一定要出现在group by子句中)，select语句中的每个列都必须在group by子句中给出(select语句中没有出现在聚集函数中的列必须在group by子句中出现)，如

select vend_id, min(prod_price) as min_prods from products group by vend_id WITH ROLLUP;

5 如果分组列中有NULL值，则NULL将作为一个分组返回。如果有多行NULL值，将它们分成一组

6 Group by子句必须出现在Where子句之后，order by子句之前。

group by与order by区别：

order by

order by	group by
排序产生的输出	分组行，但输出可能不是分组的排序
任意列都可以使用（即使非选择的列也可以使用）	只可能使用选择列或表达式列，而且必须使用每个选择列表达式，子句中不能使用聚集函数，select可以出现聚集函数

经常发现group by分组的输出是按照分组顺序输出的，但是并不总是这样，不是SQL规范，如果要保证数据正确分组，则应该和order by子句一起使用，例如检索总计订单价格大于50的订单的订单号和总计订单价格：

select order_num, SUM(quantity*item_price) AS ordertotal from orderitems group by order_num HAVING SUM(ordertotal) >= 50;

+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20005 |     149.87 |
|     20006 |      55.00 |
|     20007 |    1000.00 |
|     20008 |     125.00 |
|     20009 |      38.47 |
+-----------+------------+
5 rows in set (0.00 sec)

如果要按照ordertotal进行排序，则需要order by:

select order_num, SUM(quantity*item_price) AS ordertotal from orderitems group by order_num HAVING SUM(ordertotal) >= 50 order by ordertotal;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20009 |      38.47 |
|     20006 |      55.00 |
|     20008 |     125.00 |
|     20005 |     149.87 |
|     20007 |    1000.00 |
+-----------+------------+
5 rows in set (0.00 sec)

HAVING:过滤分组，HAVING支持所有WHERE操作，区别是HAVING过滤分组，WHERE过滤行。还有一个区别是WHERE在分组前进行过滤，HAVING在分组后进行过滤，WHERE排除的行不包括在分组中。where子句中不能使用聚集函数。WHERE和HAVING可以一起使用,例如列出具有2个以上，价格为10以上的产品的供应商：

SELECT vend_id, count(*) as num_prods from products where prod_price >= 10 group by vend_id having count(*) >= 2;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1003 |         4 |
|    1005 |         2 |
+---------+-----------+
2 rows in set (0.00 sec)

select子句的顺序：select, from, where, group by, having, order by, limit;

当同时含有where子句、group by 子句 、having子句及聚集函数时，执行顺序如下：
--执行where子句查找符合条件的数据；
--使用group by 子句对数据进行分组；对group by 子句形成的组运行聚集函数计算每一组的值；最后用having 子句去掉不符合条件的组。
--having 子句中的每一个元素也必须出现在select列表中。有些数据库例外，如oracle.
--having子句和where子句都可以用来设定限制条件以使查询结果满足一定的条件限制。
--having子句限制的是组，而不是行。where子句中不能使用聚集函数，而having子句中可以。