Mysql 必知必会(一)

Scroll Down

文章案例所需的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子句操作符
操作符说明
=等于
<>,!=不等于
<小于
<=小于等于
>大于
>=大于等于
between在指定的两值之间
  • 检查单个值:

    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都匹配且返回。

LIKEREGEXP差别:

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;

常用文本处理函数:

函数说明
Left()返回串左边的字符
Length()返回串的长度
Locate()找出串的一个子串
Lower()将串转换为小写
LTrim()去掉串左边的空格
Right()返回串右边的字符
RTrim()去掉串右边的空格
Soundex()返回串的SOUNDEX值
SubString()返回子串的字符
Upper()将串转换为大写

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

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

日期和时间处理函数

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

函数说明
AddDate()增加一个日期(天、周等)
AddTime()增加一个时间(时、分等)
CurDate()返回当前日期
CurTime()返回当前时间
Date()返回日期时间的日期部分
DateDiff()计算两个日期之差
Date_Add()高度灵活的日期运算函数
Date_Format()返回一个格式化的日期或时间串
Day()返回一个日期的天数部分
DayOfWeek()对于一个日期,返回对应的星期几
Hour()返回一个时间的小时部分
Minute()返回一个时间的分钟部分
Month()返回一个日期的月份部分
Now()返回当前日期和时间
Second()返回一个时间的秒部分
Time()返回一个日期时间的时间部分
Year()返回一个日期的年份部分

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

数值处理函数

函数说明
Abs()返回一个数的绝对值
Cos()返回一个角度的余弦
Exp()返回一个数的指数值
Mod()返回除操作的余数
Pi()返回圆周率
Rand()返回一个随机数
Sin()返回一个角度的正弦
Sqrt()返回一个数的平方根
Tan()返回一个角度的正切

汇总数据

聚集函数

聚集函数(aggregate function) 运行在行组上,计算和返回单 个值的函数。

常用SQL聚集函数:

函数说明
AVG()返回某列的平均值
COUNT()返回某列的行数
MAX()返回某列的最大值
MIN()返回某列的最小值
SUM()返回某列值之和

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 BYORDER BY经之间的差别:

ORDER BYGROUP BY
排序产生的输出分组行。但输出可能不是分组的顺序
任意列都可以使用(甚至 非选择的列也可以使用)只可能使用选择列或表达式列,而且必须使用每个选择 列表达式
不一定需要如果与聚集函数一起使用列(或表达式),则必须使用

SELECT子句顺序

子句说明是否必须使用
SELECT要返回的列或表达式
FROM从中检索数据的表仅在从表选择数据时使用
WHERE行级过滤
GROUP BY分组说明仅在按组计算聚集时使用
HAVING组级过滤
ORDER BY输出排序顺序
LIMIT要检索的行数