MySQL必知必会笔记

1. 了解SQL

1.1 数据库基础

1.1.1 什么是数据库

数据库(database) 保存有组织的数据的容器(通常是一个文件或以组文件)
数据库软件DBMS (数据库管理系统)

1.1.2 表

表(table) 某种特定类型的结构化清单

1.1.3 列和数据类型

列(column) 表中的一个字段. 所有表都是由一个或多个列组成的

数据类型(datatype) 所容许的数据的类型. 每个表列都有相应的数据类型, 它限制(或容许)该列中存储的数据.

1.1.4 行

行(row) 表中的一个记录

1.1.5 主键

主键(primary key) 一列(或一组列) , 其值能够唯一区分表中每个行.

表中任何列都可以作为主键, 只要它满足一下条件:

任意两行都不具有相同的主键值;
每个行都必须具有一个主键值(主键列不允许null值)

1.2 什么是SQL

SQL(发音: S-Q-L或sequel) 是结构化查询语言(Structured Query Language) 的缩写 . 专门用来与数据库通信的语言.

SQL不是某个特定数据库供应商的专有语言 , 几乎所有重要的DBMS都支持SQL
简单易学
尽管看上去很简单, 但它实际上是一种强有力的语言 , 灵活使用其语言元素 , 可以进行非常复杂和高级的数据库操作 .

2. MySQL

开源 , 一般可以免费使用 , 甚至修改
性能强
可信赖
实用

3. 使用MySQL

3.1 连接

为了连接到 MySQL 需要一下信息

主机名 / IP
端口
用户名
口令

3.2 选择数据库

sql

输入:
	use 数据库名

输出:
	Database changed

3.3 了解数据库和表

sql

show databases

sql

show tables

sql

show columns from 表名

show status用于显示广泛的服务器状态信息
show create database和show create table 分别用来显示创建特定的数据库或表的MySQL语句.
show grants 用来显示授权用户的安全权限
show errors和show warnings 用来显示服务器错误或警告信息

4. 检索数据

4.1 SELECT语句

4.2 检索单个列

sql

SELECT 列名 FROM 表名

4.3 检索多个列

sql

select 列名,列名,列名 from 表名

4.4 检索所有列

sql

select * from 表名

4.5 检索不同的行

sql

SELECT 列名 FROM 表名

使用DISTINCT关键字可以让MySQL只返回不同(唯一)的行

sql

SELECT DISTINCT 列名 FROM 表名

4.6 限制结果

sql

SELECT 列名 FROM 表名 LIMIT 5

注: LIMIT 指示MySQL返回不多于5行的数据 , 如果要返回下一个5行 , 可以指定检索的开始行和列数

sql

SELECT 列名 FROM 表名 LIMIT 5,5

4.7 使用完全限定的表名

sql

SELECT 表名.列名 FROM 数据库名.表名

5. 排序检索数据

5.1 排序数据

sql

bSELECT 列名 FROM 表名 ORDER BY 列名1,列名2

5.2 指定排序方向

DESC 降序 asc升序 , 升序是默认的 , 不指定即升序

sql

SELECT 列名 FROM 表名 ORDER BY 列名1 DESC , 列名2 ASC

6. 过滤数据

6.1 使用WHERE子句

sql

SELECT 列名 FROM 表名 WHERE 条件

6.2 WHERE子句操作符

操作符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN	在指定的两个值之间

###6.3 空值检查

sql

SELECT 列名 FROM 表名 WHERE xxx IS NULL

7. 数据过滤

7.1 组合WHERE子句

####7.1.1 AND操作符

sql

SELECT 列名1,列名2 FROM 表名 WHERE 条件1 AND 条件2

7.1.2 OR操作符

sql

SELECT 列名1,列名2 FROM 表名 WHERE 条件1 OR 条件2

7.1.3 计算次序

需求 : 满足条件3 并且满足条件1 或者条件2 的所有产品

sql

错误实例: SELECT 列名1,列名2 FROM 表名 WHERE 条件1 OR 条件2 AND 条件3

(由于AND符在计算次序中优先级更高 , 此时得到的结果是 所有满足条件1 或者满足条件2和条件3组合的 列)

sql

正确实例: SELECT 列名1,列名2 FROM 表名 WHERE (条件1 OR 条件2) AND 条件3

7.2 IN操作符

sql

SELECT 列名1,列名2 FROM 表名 WHERE 列名3 IN(合法值1,合法值2) ORDER BY 列名

分析: 此语句会检索列名3中符合值1和值2的所有产品 .
IN操作符后跟由逗号分隔的合法值清单 , 整个清单必须括在圆括号中

7.3 NOT操作符

WHERE子句中的NOT操作符有且只要一个功能 , 那就是否定它之后所跟的任何条件

sql

SELECT 列名1,列名2 FROM 表名 WHERE 列名3 NOT IN(合法值1,合法值2) ORDER BY 列名

8. 用通配符进行过滤

8.1 LIKE操作符

通配符(wildcard): 用来匹配值的一部分的特殊字符.
搜索模式(search pattern): 由字面值 , 通配符或两者组合构成的搜索条件

8.1.1 百分号(%)通配符

sql

SELECT 列名1,列名2 FROM 表名 WHERE 列名 LIKE 'mirror%'

SELECT 列名1,列名2 FROM 表名 WHERE 列名 LIKE '%mirror%'

SELECT 列名1,列名2 FROM 表名 WHERE 列名 LIKE 'mi%or'

8.1.1 下划线(_)通配符

另一个有用的通配符是下划线(_) . 下划线的用途和%一样, 但下划线只匹配单个字符而不是多个字符

sql

SELECT 列名1,列名2 FROM 表名 WHERE 列名 LIKE '_irror'

8.2 使用通配符技巧

不用过度使用通配符
在确实需要使用通配符时 , 除非绝对有必要 , 否则不要把它们用来搜索模式的开始处
仔细注意通配符的位置

9. 用正则表达式进行搜索

9.1 正则表达式介绍

所有种类的程序设计语言 , 文本编辑器 , 操作系统等都支持正则表达式
正则表达式用正则表达式语言来建立 , 正则表达式语言是用来完成刚讨论的所有工作以及更多工作的一种特殊语言

9.2 使用MySQL正则表达式

9.2.1 基本字符匹配

检索列名3包含文本1000的所有行

sql

SELECT 列名1,列名2 FROM 表名 WHERE 列名3 REGEXP '1000'

使用正则表达式.000来检索列名3包含文本1000或2000或...的所有行

sql

SELECT 列名1,列名2 FROM 表名 WHERE 列名3 REGEXP '.000'

9.2.2 进行OR匹配

语句中使用了正则表达式1000|2000 . | 为正则表达式的OR操作符 , 表达匹配其中之一 , 因此1000和2000都匹配并返回

sql

SELECT 列名1,列名2 FROM 表名 WHERE 列名3 REGEXP '1000|2000'

9.2.3 匹配几个字符之一

这里 , 使用正则表达式[123]mirror , [123]定义一组字符 , 它的意思是匹配1或2或3 , 因此1 mirror和 2 mirror都匹配返回

sql

SELECT 列名1,列名2 FROM 表名 WHERE 列名3 REGEXP '[123]mirror'

9.2.4 匹配范围

匹配1到5

sql

SELECT 列名1 FROM 表名 WHERE ,列名2 REGEXP '[1-5]mirror'

9.2.5 匹配特殊字符

为了匹配特殊字符 , 必须用\\为前导 . \\-表示查找- , \\.表达查找.

sql

SELECT 列名1 FROM 表名 WHERE ,列名2 REGEXP '\\.'

元字符	说明
\\f	换页
\\n	换行
\\r	回车
\\t	制表
\\v	纵向制表

匹配\ 使用\\\

匹配\或\\? 多数正则表达式实现单个反斜杠转义特殊字符 , 但是MySQL要求两个反斜杠(MySQL自己解释一个 , 正则表达式库解释另一个)

9.2.6 匹配字符类

类	说明
[:alnum:]	任意字母和数字 (同[a-zA-Z0-9])
[:alpha:]	任意字符 (同[a-zA-Z])
[:blank:]	空格和制表 (同[\\t])
[:cntrl:]	ASCII控制字符 (ASCII 0 到31和127)
[:digit:]	任意数字 (同[0-9])
[:graph:]	与[:print:]相同 , 但不包含空格
[:lower:]	任意小写字母 (同[a-z])
[:print:]	任意可打印字符
[:punct:]	既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:]	包含空格在内的任意空白字符
[:upper:]	任意大写字母
[:xdigit:]	任意十六进制数字(同[a-fA-F0-9])

9.2.7 匹配多个实例

元字符	说明
*	0个或者多个匹配
+	1个或多个匹配
?	0个或一个匹配(等于{0,1})
	指定数目的匹配
	不少于指定数目的匹配
	匹配数目的范围(m不超过255)

正则表达式 \$[0-9] mirrors?\$
\\(匹配( [0-9] 匹配任意数字 , mirrors?匹配mirror和mirrors(s后的?使s可选 , 因为?匹配它前面的任何字符的0次或1次出现 , \\(匹配(

sql

SELECT 列名1,列名2 FROM 表名 WHERE 列名3 REGEXP '\\([0-9] mirrors?\\)'

[:digit:]匹配任意数字 , 因而它为数字的一个集合 . {4}确切地要求它前面的字符(任意数字)出现4次 , 所以[:digit:]{4}匹配连在一起的任意4位数字

sql

SELECT 列名1,列名2 FROM 表名 WHERE 列名3 REGEXP '[:digit:]{4}'

####9.2.8 定位符

匹配特定位置的文本 , 可以使用定位符

元字符	说明
^	文本的开始
$	文本的结尾
[[:<:]]	词的开始
[[:>:]]	词的结尾

10. 创建计算字段

10.1 计算字段

字段(field)基本上与列(column)的意思相同,经常互换使用,不过数据库一般称为列,而术语字段通常用在计算字段连接上.

10.2 拼接字段

拼接(concatenate) 讲值联结到一起构成单个值 .

在 MySQL的 SELECT语句中 , 可使用 Concat() 函数来拼接两个列 .

sql

select Concat(列名1,'(',列名2,')') from 表名 ORDER BY 列名1

Concat() 拼接串 , 即把多个串连接起来形成一个较长的串 . Concat() 需要一个或多个指定的串 , 各个串直接用逗号分隔 .

存储在列1中的名字
包含一个空格和一个左圆括号的串
存储在列2中的数据
包含一个右圆括号的串

RTrim() 函数去掉值右边的所有空格 . 通过使用RTrim() , 各个列都进行整理 .

sql

select Concat(RTrim(列名1),'(',RTrim(列名2),')') AS 别名 from 表名 ORDER BY 列名1

10.3 执行算术运算

例子: orders表包含收到的所有订单 , orderitems表包含每个订单中的各项物品 , order_price列包含订单中每项物品的单价

sql

SELECT prod_id,quantity,item_price,quantity*item_price AS expanded_price from orderitems where order_num=20005

SELECT Now() 利用Now函数返回当前日期和时间

11. 使用数据处理函数

11.1 函数

SQL支持利用函数来处理数据

11.2 使用函数

用于处理文本串(如: 如删除或填充值 , 转换大小写) 的文本函数
用于数值数据上执行算术操作(如 : 返回绝对值 , 进行代数运算) 的数值函数
用于处理日期时间并从这些值长提取特定成分 (如: 返回两个日期之差 , 检查日期的有效性等) 的日期和时间函数
返回DBMS正使用的特殊信息(如: 返回用户登录信息 , 检查版本细节)的系统函数

11.2.1文本处理函数

sql

SELECT 列名1,Upper(列名1) AS 别名 FROM 表名 ORDER BY 列名

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

SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法!
SOUNDEX是考虑了类似的发音字符和音节, 使得能对串进行发音比较而不是字母比较

sql

SELECT 列名1,列名2 FROM 表名 WHERE Soundex(列名2) = Soundex('读音')

会查询出发音相似的匹配项

11.2.2 日期和时间处理函数

日期和时间采用相应的数据类型和特殊的格式存储 , 以便能快速和有效地排序和过滤 , 并且能节省物理存储空间

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

sql

SELECT 列名1 FROM 表名 WHERE Date(时间列名) = '2005-09-01'

sql

SELECT 列名1 FROM 表名 WHERE Date(时间列名) BETWEEN '2005-09-01' AND '2006-09-01'

sql

SELECT 列名1 FROM 表名 WHERE Year(时间列名) ='2005' AND Month(时间列名) ='9'

检索出时间列名为2005年9月份的所有行

11.2.3 数值处理函数

数值处理函数仅处理数值数据
一般主要用于代数 , 三角或几何运算 , 因此没有串或日期时间处理函数使用那么频繁

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

12. 汇总数据

12.1 聚集函数

运行在行组上 , 计算和返回单个值的函数

确定表中的行数
获得表中行组的和
找出表列(或所有行或某些特定的行)的最值和平均值

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

12.1.1 AVG()函数

sql

SELECT AVG(列名) AS 别名 FROM 表名

12.1.2 COUNT()函数

sql

SELECT COUNT(列名) AS 别名 FROM 表名

12.1.3 MAX()函数

sql

SELECT MAX(列名) AS 别名 FROM 表名

MySQL允许将它用来返回任意列中的最大值 , 在用于文本数据时 , 如果数据俺早相应的列排序 , 则MAX() 返回最后一行
MAX() 函数忽略列值为NULL的行

12.1.4 MIN()函数

sql

SELECT MIN(列名) AS 别名 FROM 表名

MySQL允许将它用来返回任意列中的最大值 , 在用于文本数据时 , 如果数据俺早相应的列排序 , 则MIN() 返回最前面的行
MIN() 函数忽略列值为NULL的行

12.1.5 SUM()函数

sql

SELECT SUM(列名) AS 别名 FROM 表名 WHERE 条件

sql

SELECT SUM(列名1*列名2) AS 别名 FROM 表名 WHERE 条件

利用标准的算术操作符 , 所有聚集函数都可用来执行多个列上的计算
SUM() 函数会忽略NULL值的行

12.2 聚集不同值

ALL不需要指定 , 因为它是默认行为 , 如果不指定DISTINCT , 则假定为ALL

使用AVG() 函数返回某列的平均值 , 使用DISTINCT函数 , 平均值只考虑不同的值来计算

sql

SELECT AVG(DISTINCT 列名) AS 别名 FROM 表名 WHERE 条件

DISTINCT不能用于COUNT(*) . DISTINCT必须使用列名 . 不能用于计算或表达式
DISTINCT用于MAX()和MIN()没有意义

12.3 组合聚集函数

sql

SELECT 
	COUNT(*) AS 别名,
	MIN(列名) AS 别名,
	MAX(列名) AS 别名,
	AVG(列名) AS 别名
FROM 表名

13. 分组数据

13.1 数据分组

分组数据允许把数据分为多个逻辑组 , 以便能对每个组进行聚集计算

13.2 创建分组

GROUP BY的一些规定

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

sql

SELECT 列名1,COUNT(*) AS 别名 FROM 表名 GROUP BY 列名1;

13.3 过滤分组

除了能用GROUP BY分组数据外 , MySQL还允许过滤分组 , 规定包括哪些分组 , 排除哪些分组

MySQL为此目的提供了另外的子句 , 那就是HAVING子句 , HAVING非常类似WHERE

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

sql

SELECT 列名1,COUNT(*) AS 别名 
FROM　表名
GROUP BY 别名1
HAVING COUNT(*)>=2;

过滤了COUNT>=2的那些分组

WHERE在分组前进行过滤HAVING在分组后进行过滤 , WHERE排除的行不包括在分组中 , 这可能会改变计算值 , 从而影响HAVING子句中基于这些值过滤掉的分组

13.4 分组和排序

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

sql

SELECT 列名1,SUM(列名2*列名3) AS 别名 
FROM　表名
GROUP BY 别名1
HAVING SUM(列名2*列名3)>=50
ORDER BY 别名

GROUP BY 子句用来按订单号(别名1 列)分组数据 , 以便SUM(*)函数能够返回总计订单价格 . HAVING子句过滤数据 , 使得只返回总计(订单价格)大于等于50的订单 , 最后用ORDER BY排序输出

13.5 SELECT子句顺序

SELECT语句使用时必须遵守的次序

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

14. 使用子查询

14.1 子查询

查询(query) 任何SQL语句都是查询 . 但此术语一般指SELECT语句

14.2 利用子查询进行过滤

可以把一条SELECT语句返回的结果用于另一条SELECT语句的WHERE子句

例子:

假如需要列出订购物品TNT2的所有客户

检索包含物品TNT2的所有订单的编号
检索具有前一步列出的订单编号的所有用户ID
检索前一步返回的所有客户ID的客户信息

第一条SELECT语句的含义很明确 , 对于prod_id为TNT2的所有订单物品 , 它检索其order_num列 .

sql

SELECT order_num
FROM orderitems
WHERE prod_id = "TNT2"

下一步 , 查询具有订单20005和20007的客户ID .

sql

SELECT cust_id
FROM orders
WHERE order_num IN (20005,20007)

把第一个查询变成子查询组合两个查询

sql

SELECT cust_id
FROM orders
WHERE order_num IN
(SELECT order_num
FROM orderitems
WHERE prod_id = "TNT2")

在SELECT语句中 , 子查询总是从内向外处理

现在得到了订购物品TNT2的所有用户ID , 下一步检索这些客户ID的客户信息

sql

SELECT cust_name,cust_concact
FROM customers
WHERE cust_id IN(10001,10004)

把其中的WHERE子句转换为子查询而不是硬编码这些客户ID

sql

SELECT cust_name,cust_concact
FROM customers
WHERE cust_id IN(SELECT cust_id
                 FROM orders
                 WHERE order_num IN (SELECT order_num
                                     FROM orderitems
                                     WHERE prod_id = "TNT2"))

实际使用时由于性能的限制 , 不能嵌套太多的子查询
列必须匹配 : 在WHERE子句中使用子查询 , 应该保证SELECT语句具有返回与WHERE子句中相同数目的列
通常 , 子查询将返回单个列并且与单个列匹配 , 但如果需要也可以使用多个列

子查询可以一般与IN操作符结合使用 , 但也可以用于测试等于(=) , 不等于(<>)等

14.3 作为计算字段使用子查询

例:

假如需要显示customers表中的每个用户的订单总数

从customers表中检索客户列表
对于检索出的每个客户 , 统计其在orders表中的订单数目

对客户10001的订单进行计数

sql

SELECT COUNT(*) AS orders
FROM orders
WHERE cust_id = 10001

为了对每个客户执行COUNT(*)计算 , 应该将COUNT(*)作为一个子查询

sql

SELECT cust_name,
 	   cust_state,
       (SELECT COUNT(*)
            FROM orders
            WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_name

相关子查询 : 涉及外部查询的子查询

15. 联结表

15.1 联结

联结 : join

15.1.1 关系表

外键 (foreign key) 外键为某个表中的一列 , 它包含另一个表的主键值 , 定义了两个表之间的关系

总之 , 关系数据可以有效地存储和方便地处理 , 因此 , 关系数据库的可伸缩性远比非关系型数据库好

可伸缩性(scale) 能够适应不断增加的工作量 , 而不失败 , 设计良好的数据库或应用程序称之为 可伸缩性好

15.1.2 为什么要用联结

维护引用完整性重要的是 , 要理解联结不是物理实体 , 它在数据库中并不存在 , 联结由MySQL根据需要建立 , 它存在于查询的执行当中

15.2 创建联结

sql

SELECT	表名.列名1,表名.列名2,表名.列名3
FROM 表名1,表名2
WHERE 表名1.xxx_id = 表名2.xxx_id
ORDER BY 列名1,列名2

15.2.1 WHERE子句的重要性

笛卡儿积 (cartesian product) 由于没有联结条件的表关系返回的结果为笛卡儿积 . 检索出的行的数目将是第一个表中的行数乘以第二个表中的行数

15.2.2 内部联结

sql

SELECT	表名.列名1,表名.列名2,表名.列名3
FROM 表名1
INNER JOIN 表名2
ON 表名1.xxx_id = 表名2.xxx_id

15.2.3 联结多个表

sql

SELECT	表名.列名1,表名.列名2,表名.列名3,表名.列名4
FROM 表名1,表名2,表名3
WHERE 表名1.xxx_id = 表名2.xxx_id
AND 表名2.xxxx_id = 表名3.xxxx_id
AND 条件

性能考虑 MySQL在运行时关联指定的每个表以处理联结 , 这种处理可能是非常耗费资源的 , 因此应该仔细 , 不要联结不必要的表

多做实验正如所见 , 为执行任一给定的SQL操作 , 一般存在不止一种方法 , 很少有绝对正确或错误的方法 , 性能可能受到操作类型 , 表中数据量 , 是否存在索引的或键以及一些条件的影响 . 因此 , 有必要对不同的选择机制进行实验 , 以找出最适合具体情况的方法

16. 创建高级联结

16.1 使用表别名

例:

sql

SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')')AS vend_title
FROM vendors
ORDER BY vend_name

sql

SELECT 字段1,字段2
FROM 表1 AS a , 表2 AS b , 表3 AS c
WHERE a.xxxid = b.xxxid
AND c.xxx_num = b.xxx_num
AND 条件

16.2 使用不同类型的联结

简单联结: 内部联结 , 等值联结
其他联结: 自联结 , 自然联结 , 外部联结

16.2.1 自联结

例:

sql

SELECT p1.pro_id p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
AND p2.prod_id = "xxx"

此查询中需要的两张表实际上是相同的表 , 因此products表在FROM子句中出现两次 , 虽然这是完全合法的 , 但对products的引用具有二义性 , 因此MySQL不知道你引用的是products表中的哪个实例

自联结通常作为外部语句用来代替从相同表中检索数据时使用的子查询语句 , 虽然结果都是一样的 , 但是有时候处理联结远比处理子查询快得多

16.2.2 自然联结

无论何时对表进行联结 , 应该至少有一个列出现在不止一个表中(被联结的列) . 内部联结返回所有数据 , 甚至相同的列多次出现 , 自然联结 排除多次出现 , 使每个列只返回一次

16.2.3外部联结

没有 *=操作符 MySQL不支持简化字符*=和=*的使用 , 这两种操作符在其他DBMS中是很流行的

16.3 使用带聚集函数的联结

例如: 如果要检索所有客户及每个客户所下的订单数

sql

SELECT customers.cust_name,
	   customers.cust_id,
	   COUNT(orders.order_num) AS num_ord
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id

16.4 使用联结和联结条件

注意使用的联结类型 , 一般我们使用内部联结 , 但使用外部联结也是有效的
保证使用正确的联结条件 , 否则将返回不正确的数据
应该总是提供联结条件 , 否则会得出笛卡儿积
在一个联结中可以包含多个表 , 甚至对于每个联结可以采用不同的联结类型

17. 组合查询

17.1 组合查询

多数SQL查询都只包含从一个或多个表中返回数据的单条SELECT语句. MySQL也允许执行多个查询(多条SELECT语句)并将结果作为单个查询结果返回. 这些组合查询通常称为并(union)或复合查询(compound query)

有两种基本情况 , 其中需要使用组合查询:

在单个查询中从不同表返回类似结构的数据
对单个表执行多个查询 , 按单个查询返回数据

17.2 创建组合查询

17.2.1 使用UNION

例: 假如需要价格小于等于5的所有物品的一个列表 , 而且还想包括供应商1001和1002生产的物品(不考虑价格) . 当然 , 可以使用where子句完成此工作

正如所述 , 创建UNION涉及编写多条SELECT语句 . 首先来看单条语句:

sql

SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price<=5;

sql

SELECT vend_id,prod_id,prod_price
FROM products
WHERE vend_id IN(1001,1002);

第一条SELECT检索价格不高于5的所有物品 . 第二条SELECT用IN找出供应商1001和1002生产的所有物品

为了组合这两条语句 , 按如下进行:

sql

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);

这条语句由前面的两条SELECT语句组成 , 语句中用UNION关键字分隔 . UNION指示MySQL执行两条SELECT语句 , 并把输出组合成单个查询结果集.

作为参考 , 这里给出使用多条WHERE子句而不是使用UNION的相同查询

sql

SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price<=5;
OR vend_id IN(1001,1002);

在这个简单的列子中 , 使用UNION可能比使用WHERE子句更为复杂 . 但对于更复杂的过滤条件 , 或者从多个表中检索数据的情形 , 使用UNION可能会使处理更简单 .

17.2.2 UNION规则

并是非常容易使用的 , 但在进行并时有几条规则需要注意:

UNION必须由两条或两条以上的SELECT语句组成 , 语句之间用UNION关键字分隔(因此 , 如果组合4条SELECT语句 , 将要使用3个UNION关键字)
UNION中每个查询必须包含相同的列 , 表达式和聚集函数(不过各个列不需要以相同的次序列出)
列数据类型必须兼容: 类型不必完全相同 , 但必须是DBMS可以隐含地转换的类型

17.2.3 包含或取消重复的行

UNION从查询结果集中自动去除了重复的行 , 如果两个检索都返回那一行 , 在使用UNION时 , 重复的行将被自动取消 .

这是UNION的默认行为 , 如果想返回所有行 , 可以使用UNION ALL 而不是 UNION

UNION ALL , MySQL不取消重复的行

17.2.4 对组合查询结果排序

SELECT语句的输出用ORDER BY子句排序 , 在用UNION组合查询时 , 只能使用一条ORDER BY子句 , 它必须出现在最后一条SELECT语句之后 , 对于结果集 , 不存在用一种方式排序一部分 , 而又用另一种方式排序另一部分 , 因此不能使用多条ORDER BY子句.

sql

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);
ORDER BY vend_id,prod_price

虽然似乎只有一条SELECT语句使用了ORDER BY , 但是MySQL实际上将用它来排序所有SELECT语句的返回的所有结果

18. 全文本搜索

18.1 理解全文本搜索

并非所有引擎都支持全文本搜索: MySQL支持几种基本的数据库引擎 . 并非所有的引擎都支持本书所描述的全文本搜索 . 两个最常使用的引擎为MyISM和InnoDB , 前者支持全文本搜索 , 后者则不支持 .

虽然这些搜索机制非常有用 , 但存在几个重要的限制

性能--通配符和正则表达式匹配通常要求MySQL尝试匹配表中所有行(而且这些搜索极少使用表索引) . 因此由于被搜索行数不断增加 , 这些搜索可能非常耗时
明确控制--使用通配符和正则表达式匹配 , 很难(而且并不总是能)明确地控制匹配什么和不匹配什么
智能化的结果--虽然基于通配符和正则表达式的搜索提供了非常灵活的搜索 , 但它们都不能提供一种智能化的选择结果的方法

MySQL必知必会笔记 ​

1. 了解SQL ​

1.1 数据库基础 ​

1.1.1 什么是数据库 ​

1.1.2 表 ​

1.1.3 列和数据类型 ​

1.1.4 行 ​

1.1.5 主键 ​

1.2 什么是SQL ​

2. MySQL ​

3. 使用MySQL ​

3.1 连接 ​

3.2 选择数据库 ​

3.3 了解数据库和表 ​

4. 检索数据 ​

4.1 SELECT语句 ​

4.2 检索单个列 ​

4.3 检索多个列 ​

4.4 检索所有列 ​

4.5 检索不同的行 ​

4.6 限制结果 ​

4.7 使用完全限定的表名 ​

5. 排序检索数据 ​

5.1 排序数据 ​

5.2 指定排序方向 ​

6. 过滤数据 ​

6.1 使用WHERE子句 ​

6.2 WHERE子句操作符 ​

7. 数据过滤 ​

7.1 组合WHERE子句 ​

7.1.2 OR操作符 ​

7.1.3 计算次序 ​

7.2 IN操作符 ​

7.3 NOT操作符 ​

8. 用通配符进行过滤 ​

8.1 LIKE操作符 ​

8.1.1 百分号(%)通配符 ​

8.1.1 下划线(_)通配符 ​

8.2 使用通配符技巧 ​

9. 用正则表达式进行搜索 ​

9.1 正则表达式介绍 ​

9.2 使用MySQL正则表达式 ​

9.2.1 基本字符匹配 ​

9.2.2 进行OR匹配 ​

9.2.3 匹配几个字符之一 ​

9.2.4 匹配范围 ​

9.2.5 匹配特殊字符 ​

9.2.6 匹配字符类 ​

9.2.7 匹配多个实例 ​

10. 创建计算字段 ​

10.1 计算字段 ​

10.2 拼接字段 ​

10.3 执行算术运算 ​

11. 使用数据处理函数 ​

11.1 函数 ​

11.2 使用函数 ​

11.2.1文本处理函数 ​

11.2.2 日期和时间处理函数 ​

11.2.3 数值处理函数 ​

12. 汇总数据 ​

12.1 聚集函数 ​

12.1.1 AVG()函数 ​

12.1.2 COUNT()函数 ​

12.1.3 MAX()函数 ​

12.1.4 MIN()函数 ​

12.1.5 SUM()函数 ​

12.2 聚集不同值 ​

12.3 组合聚集函数 ​

13. 分组数据 ​

13.1 数据分组 ​

13.2 创建分组 ​

13.3 过滤分组 ​

13.4 分组和排序 ​

13.5 SELECT子句顺序 ​

14. 使用子查询 ​

14.1 子查询 ​

14.2 利用子查询进行过滤 ​

14.3 作为计算字段使用子查询 ​

15. 联结表 ​

15.1 联结 ​