Mysql 必知必会(一)

文章案例所需的SQL文件，点击下载

使用MySQL

进入mysql安装目录下的bin目录：

• 连接Mysql：mysql -uroot -p123456;
• 显示Mysql下的所有数据库：show databases;
• 切换数据库：use local;
• 显示数据库下所有表名：show tables;
• 显示表中字段名、数据 类型、是否允许NULL、键信息、默认值以及其他信息：show columns from fee;
• 显示允许的SHOW语句：help show;
• 显示创建数据库的语句以及使用字符： show create database local;
• 显示创建表的语句： show create talbe fee;
• 显示授予用户（所有用户或特定用户）的安 全权限：show grants;
• 显示服务器错误信息：show errors;
• 显示服务器警告信息：show warnings;

检索数据

selete

• 检索单列：select prod_name from products;
• 检索多列：select prod_id,vend_id,prod_name from products;
• 检索所有列：select * from products;
• 检索不同的行：select distinct vend_id from products;
• 限制结果： select vend_id from products limit 5; select vend_id from products limit 5,5;第一个数为开始位置，第二个数为要检索的个数。 使用完全限定的表名：select products.vend_id from mysql_crash_course.products;

排序检索数据

order

• 排序数据：select prod_name from products order by prod_name;
• 按多个列排序：select prod_id,prod_price,prod_name from products order by prod_name,prod_price; 仅在多个行具有相同的prod_price 值时才对产品按prod_name进行排序。如果prod_price列中所有的值都是唯一的，则不会按prod_name排序。
• 指定排序方向： select prod_id,prod_price,prod_name from products order by prod_price desc; 按价格以降序排序 select prod_id,prod_price,prod_name from products order by prod_price desc,prod_name; 以降序排序产品 （最贵的在最前面），然后再对产品名排序： DESC关键字只应用到直接位于其前面的列名。在上例中，只对 prod_price列指定DESC，对prod_name列不指定。因此， prod_price列以降序排序，而prod_name列（在每个价格内）仍然按标准 的升序排序。 (默认升序) 在字典（dictionary）排序顺序中， A被视为与a相同，这是MySQL （和大多数数据库管理系统）的默认行为。但是，许多数据库 管理员能够在需要时改变这种行为（如果你的数据库包含大量 外语字符，可能必须这样做）。 这里，关键的问题是，如果确实需要改变这种排序顺序，用简 单的ORDER BY子句做不到。你必须请求数据库管理员的帮助
• 使用ORDER BY和LIMIT的组合，能够找出一个列中最高或最低的值。 select prod_price from products order by prod_price desc limit 1;

过滤数据

where

select prod_price,prod_name from products where prod_price = 2.50;

从products表中检索两个列，但不返回所有行，只返 回prod_price值为2.50的行

WHERE子句操作符

• 检查单个值： select prod_price,prod_name from products where prod_name = 'fuses'; 检查WHERE prod_name=‘fuses’语句，它返回prod_name的值 为Fuses的一行。MySQL在执行匹配时默认不区分大小写，所 以fuses与Fuses匹配。 select prod_price,prod_name from products where prod_price < 10; 价格小于10美元的所有产品 select prod_price,prod_name from products where prod_price <= 10; 价格小于等于10美元的所有产品：
• 不匹配检查 select vend_id,prod_name from products where vend_id <> 1003; 不是由供应商1003制造的所有产品
• 范围值检查 select prod_name,prod_price from products where prod_price between 5 and 10; 检索价格在5美元和10 美元之间的所有产品 使用BETWEEN时，必须指定两个值 ——所需范围的低端值和高端值。这两个值必须用AND关键字 分隔。BETWEEN匹配范围中所有的值，包括指定的开始值和结束值。
• 空值检查 select prod_name from products where prod_price is null; NULL 无值（no value），它与字段包含0、空字符串或仅仅包含 空格不同。 is null子句用来检查具有NULL值的列。

AND操作符

select prod_id,prod_price,prod_name from products where vend_id = 1003 and prod_price <= 10;

句检索由供应商1003制造且价格小于等于10美元的所 有产品的名称和价格

OR操作符

select prod_id,prod_price,prod_name from products where vend_id = 1003 or vend_id = 1002;

检索由任一个指定供应商制造的所有产品的产品 名和价格。

计算次序:where子句从左往右，不要过分依赖。

错误SQL：select prod_id,prod_price,prod_name from products where vend_id = 1003 or vend_id = 1002 and prod_price >= 10;

正确SQL：select prod_id,prod_price,prod_name from products where (vend_id = 1003 or vend_id = 1002) and prod_price >= 10;

检索价格为10美元（含）以上且由1002或1003制 造的所有产品

IN操作符

select prod_name,prod_price from products where vend_id in (1002,1003) order by prod_name;

句检索供应商1002和1003制造的所有产品。

为什么要使用IN操作符？其优点具体如下。

• 在使用长的合法选项清单时，IN操作符的语法更清楚且更直观。
• 在使用IN时，计算的次序更容易管理（因为使用的操作符更少）。
• IN操作符一般比OR操作符清单执行更快。
• IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建 立WHERE子句。

NOT操作符

select prod_name,prod_price from products where vend_id not in (1002,1003) order by prod_name;

检索出除1002和1003之外的所有供应 商制造的产品

用通配符进行过滤

LIKE操作符

百分号（%）通配符

value%：表示以value开始任意字符结尾且不限次数。

%value%：表示字符包含value，不限开头和结尾。

%value：表示以任意字符开头，value结尾。

select prod_id,prod_name from products where prod_name like 'jet%';

检索出所有以词jet起头的产品。%告诉MySQL接受jet之后的任意字符，不 管它有多少字符。

下划线（_）通配符

下划线的用途与%一样，但下划线只匹配单个字符而不是多个字符。

select prod_id,prod_name from products where prod_name like '_ ton anvil';

查询第一个字符为任意字符，后ton anvil

使用通配符的技巧

尽管MySQL通配符很有用，但它要比一般检索所花的时间更长，应合理使用：

• 不要过度使用通配符。如果其他操作符能达到相同的目的，应该 使用其他操作符。
• 在确实需要使用通配符时，除非绝对有必要，否则不要把它们用 在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起 来是最慢的。
• 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

总之，通配符是一种极重要和有用的搜索工具，以后我们经常会用 到它。

用正则表达式进行搜索

使用MySQL正则表达式

正则表达式的作 用是匹配文本，将一个模式（正则表达式）与一个文本串进行比较。MySQL 用WHERE子句对正则表达式提供了初步的支持，允许你指定正则表达式， 过滤SELECT检索出的数据。

MySQL仅支持多数正则表达式实现的一个很小的子集。

基本字符匹配

select prod_name from products where prod_name regexp '1000' order by prod_name;

检索列prod_name包含 文本1000的所有行 REGEXP后所跟的东西作 为正则表达式（与文字正文1000匹配的一个正则表达式）处理。

select prod_name from products where prod_name regexp '.000' order by prod_name;

正则表达式.000。.是正则表达式语言中一个特殊 的字符。它表示匹配任意一个字符，因此，1000和2000都匹配且返回。

LIKE与REGEXP差别：

select prod_name from products where prod_name like '1000' order by prod_name;

select prod_name from products where prod_name regexp '1000' order by prod_name;

如果执行上述两条语句，会发现第一条语句不返回数据，而第 二条语句返回一行。LIKE匹配整个列。如果被匹配的文本在列值 中出现，LIKE将不会找到它，相应的行也不被返回（除非使用 通配符）。而REGEXP在列值内进行匹配，如果被匹配的文本在 列值中出现，REGEXP将会找到它，相应的行将被返回。这是一 个非常重要的差别。

自版本 3.23.4后，MySQL中的正则表达式匹配不区分大小写。为区分大 小写，可使用BINARY关键字，如WHERE prod_name REGEXP BINARY 'JetPack .000'。

进行OR匹配

select prod_name from products where prod_name regexp '1000|2000' order by prod_name;

了正则表达式1000|2000。|为正则表达式的OR操作 符。它表示匹配其中之一，因此1000和2000都匹配并返回。

匹配几个字符之一

select prod_name from products where prod_name regexp '[123]' order by prod_name;

正则表达式[123] Ton，[123]定义一组字符，它的意思是匹配1或2或3，因此，1 ton和2 ton都匹配且返回。

select prod_name from products where prod_name regexp '1|2|3 Ton' order by prod_name;

匹配'1'或 '2'或'3 ton

匹配范围

集合可用来定义要匹配的一个或多个字符。例如，下面的集合将匹 配数字0到9： [0123456789]简化：[0-9]

select prod_name from products where prod_name regexp '[1-5] Ton' order by prod_name;

正则表达式[1-5] Ton。[1-5]定义了一个范围，这个 表达式意思是匹配1到5，因此返回3个匹配行。由于5 ton匹配， 所以返回.5 ton。

匹配特殊字符

查找字符包含.字符的值

错误：select vend_name from vendors where vend_name regexp '.' order by vend_name;

.匹配任意字符，需要使用转义\\.

正确：select vend_name from vendors where vend_name regexp '\\.' order by vend_name;

创建计算字段

拼接字段

Concat()函数：将值联结到一起构成单个值。

select concat(vend_name,'(',vend_country,')') from vendors order by vend_name;

RTrim()函数：删除数据右侧多余的空格来整理数据

select concat(vend_name,'(',RTrim(vend_country),')') from vendors order by vend_name;

MySQL除了支持RTrim()（正如刚才所见，它去掉 串右边的空格），还支持LTrim()（去掉串左边的空格）以及 Trim()（去掉串左右两边的空格）

使用别名

别名（alias）是一个字段或值 的替换名。别名用AS关键字赋予。

select concat(RTrim(vend_name),'(',RTrim(vend_country),')') as vend_title from vendors order by vend_name;

执行算术计算

select prod_id,quantity,item_price,quantity*item_price as expanded_price from orderitems where order_num = 20005;

汇总物品的价格（单 价乘以订购数量）

使用数据处理函数

文本处理函数

Upper()函数：将文本转换为大写。

select upper(vend_name) from vendors;

常用文本处理函数：

select cust_name,cust_contact from customers where soundex(cust_contact) = soundex('Y Lie');

使用Soundex()函数进行搜索，它匹配所有发音类似于 Y.Lie的联系名

日期和时间处理函数

常用日期和时间处理函数:

select cust_id,order_num,order_date,Date(order_date) from orders where Date(order_date) = '2005-09-01';

数值处理函数

汇总数据

聚集函数

聚集函数（aggregate function） 运行在行组上，计算和返回单 个值的函数。

常用SQL聚集函数：

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

仅过滤出vend_id为1003的产品的平均值。

聚集不同值

DISTINCT：只包含不同的值。

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

**取别名：**在指定别名以包含某个聚集函数的结果时，不应该使 用表中实际的列名。虽然这样做并非不合法，但使用唯一的名 字会使你的SQL更易于理解和使用。

分组数据

GROUP BY子句

select vend_id,count(*) as num_prods from products group by vend_id;

因为使用了GROUP BY，就不必指定要计算和估值的每个组了。系统 会自动完成。GROUP BY子句指示MySQL分组数据，然后对每个组而不是 整个结果集进行聚集。

在具体使用GROUP BY子句前，需要知道一些重要的规定。

• GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套， 为数据分组提供更细致的控制。
• 如果在GROUP BY子句中嵌套了分组，数据将在后规定的分组上 进行汇总。换句话说，在建立分组时，指定的所有列都一起计算 （所以不能从个别的列取回数据）。
• GROUP BY子句中列出的每个列都必须是检索列或有效的表达式 （但不能是聚集函数）。如果在SELECT中使用表达式，则必须在 GROUP BY子句中指定相同的表达式。不能使用别名。
• 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子 句中给出。
• 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
• GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

WITH ROLLUP关键字

使用WITH ROLLUP关键字，可以得到每个分组以 及每个分组汇总级别（针对每个分组）的值。

select vend_id,count(*) as num_prods from products group by vend_id with rollup;

过滤分组

HAVING：。HAVING非常类似于WHERE。事实上，目前为止所 学过的所有类型的WHERE子句都可以用HAVING来替代。唯一的差别是 WHERE过滤行，而HAVING过滤分组。

select vend_id,count(*) as num_prods from products group by vend_id having count(*) >= 2;

这条SELECT语句的前3行类似于上面的语句。后一行增加了 HAVING子句，它过滤COUNT(*) >= 2。

HAVING和WHERE的差别：这里有另一种理解方法，WHERE在数据 分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重 要的区别，WHERE排除的行不包括在分组中。这可能会改变计 算值，从而影响HAVING子句中基于这些值过滤掉的分组。

分组和排序

GROUP BY和ORDER BY经之间的差别：

SELECT子句顺序