文章/答案/技术大牛

发布

社区首页 >专栏 >MySQL必知必会笔记(1)

MySQL必知必会笔记(1)

卢衍飞

发布于 2023-02-16 07:03:18

1.7K00

代码可运行

文章被收录于专栏：技术分享交流技术分享交流

运行总次数：0

代码可运行

主键(primary key) 唯一标识表中每行的这个列(或这组列)称为主键

表中的任何列都可以作为主键，只要它满足以下条件：

任意两行都不具有相同的主键值；每个行都必须具有一个主键值(主键列不允许为NULL) 外键(foreign key) 外键为某个表(子表)中的一列，它是另一个表(父表)的主键值，建立起两个表之间的关系。

MySQL基本使用

第3章使用MySQL

create database crashcourse default character set utf8mb4 collate utf8mb4_general_ci; # 创建指定的数据库(指定字符集和排序规则) use crashcourse; # 指定使用的数据库 show databases; # 了解数据库，返回数据库列表 show tables; # 返回数据库内表的列表 show columns from customers; # 查看customers表中的所有列设置 describe customers; # 同上，查看customers表中的所有列设置 show full columns from customers; # 可查看所有字段的排序规则

show status; # 用于显示广泛的服务器状态信息 show create database crashcourse; #查看创建数据库crashcourse的mysql代码语句(包括字符集和排序规则等信息) show create table productnotes; #查看创建表productnotes表的mysql代码语句 show grants; #显示授予用户（所有用户或特定用户）的安全权限

show errors; # 显示服务器错误内容

show warnings； #显示服务器警告内容

alter table products default charset utf8mb4 collate utf8mb4_general_ci; # 修改表的默认字符集和排序规则 ubuntu20.04修改数据库默认字符集和排序规则配置文件位于/etc/mysql/mysql.conf.d/mysqld.cnf

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf 修改内容如下:

[mysql] default-character-set=utf8mb4 [mysqld] default-storage-engine=InnoDB character-set-server=utf8mb4 character-set-filesystem=utf8mb4 collation-server=utf8mb4_general_ci [client] default-character-set=utf8mb4 重启mysql服务

systemctl restart mysql.service distinct关键字对查询的结果去重，注意不能部分使用DISTINCT，DISTINCT关键字应用于所有列而不仅是前置它的列

select distinct vend_id from products; # 使用distinct关键字去重，distinc只能放在列名的前面 select distinct vend_id,prod_price from products; # distinct不仅对前置它的列vend_id起作用，同时也作用于prod_price，两列值有重复，才去重 limt关键字限制返回结果的数量和查询的范围，行数下标从0开始

select prod_name from products limit 5; #从第0行开始，返回前5行 select prod_name from products limit 5,5; #从第5行开始，检索5行 order by排序默认为ASC升序，降序可以使用DESC

按多列排序时，按列名的顺序进行，当第一列相同时，按第二列的顺序排序，依次类推

order by和limit结合可以找出最高和最低的几个数据

按单列排序

select prod_name from products order by prod_name; # 以字母顺序排序prod_name列 select prod_name from products order by prod_id; # 使用非检索的列排序数据也是合法的，如使用prod_id顺序排列prod_name

按多列排序

select prod_id, prod_price,prod_name from products order by prod_price, prod_name; #先按价格，再按产品名排序

降序排列 desc，desc只作用于直接位于其前面的列名

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; #先按价格降序排列，再按产品名升序排列 select prod_id, prod_price,prod_name from products order by prod_price desc, prod_name desc; #先按价格降序排列，再按产品名降序排列

使用order by 和limit组合，找出一列中最高或最低的值

顺序：order by子句必须在from子句之后，limit子句必须在order by之后

select prod_price from products order by prod_price desc limit 1; # 最高值 select prod_price from products order by prod_price limit 1; # 最低值 where语句表1.where语句操作符运算符说明 = 等于 <> 不等于 != 不等于 < 小于 <= 小于等于

大于 = 大于等于 between a and b 在[a,b]内 and 逻辑与 or 逻辑或 not 逻辑非 in 在集合中使用where语句对查询数据进行过滤

select prod_name,prod_price from products where prod_price = 2.50; # 价格等于2.50的产品名、产品价格 select prod_name,prod_price from products where prod_name = "fuses"; # 默认不区分大小写 select prod_name,prod_price from products where prod_price < 10; # 价格小于10的产品名、产品价格 select prod_name,prod_price from products where prod_price <=10; # 价格小于等于10的产品名、产品价格

不匹配检查

select vend_id,prod_name from products where vend_id <> 1003; # 检索不是由1003供应商制造的所有产品 select vend_id,prod_name from products where vend_id != 1003; # 同上，检索不是由1003供应商制造的所有产品

范围值检索，between A and B，包括A和B

select prod_name,prod_price from products where prod_price between 5 and 10; # 价格大于等于5，小于等于10 的产品名、产品价格

空值检查

select prod_name from products where prod_price is null; # 返回prod_price为空值null的prod_name,无对应数据 select cust_id from customers where cust_email is null; # 检索cust_email为空值时的cust_id select cust_id from customers where cust_email is not null; # 检索cust_email不为空值时的cust_id

错误写法

select prod_name from products where prod_price != null; # 使用!=来过滤不为null的数据

and 或 or 操作符连接多个where子句

AND 用在WHERE子句中的关键字，用来指示检索满足所有给定条件的行

select vend_id,prod_price,prod_name from products where vend_id = 1003 and prod_price <= 10; #检索由供应商1003制造且价格小于等于10美元的产品信息

OR操作符，指示MySQL检索匹配任一条件的行

select vend_id,prod_name,prod_price from products where vend_id = 1002 or vend_id = 1003; # 检索由任一个指定供应商制造的所有产品的产品信息

and 和 or结合，and优先计算

优先计算and，查找vend_id为1003且价格>=10的产品，或者vend_id为1002的产品，不管价格如何

select prod_name,prod_price from products where vend_id = 1002 or vend_id = 1003 and prod_price >= 10;

使用圆括号明确运算顺序：查找vend_id为1002或1003，且价格>=10的产品

select prod_name,prod_price from products where (vend_id = 1002 or vend_id = 1003) and prod_price >= 10;

IN操作符

IN操作符后跟由逗号分隔的合法值清单，整个清单必须括在圆括号

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

NOT操作符

列出1002和1003之外的供应商生产的产品

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

Mysql支持not对in，between，exsits子句取反

列出价格小于3或大于10的产品

select prod_name,prod_price from products where prod_price not between 3 and 10; like模糊查询通配符(wildcard): 用来匹配值的一部分的特殊字符

搜索模式(search pattern): 由字面值、通配符或两者组合构成的搜索条件

注意不使用通配符时相当于相等查询 select prod_id,prod_name from products where prod_name like "JetPack 1000";

select prod_id,prod_name from products where prod_name = "JetPack 1000"; mysql的搜索模式匹配默认不区分大小写

常用通配符:

%:匹配任意字符出现任意次数(包括0) _: 匹配单个任意字符

找到所有产品名以jet开头的产品

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

通配符可以在搜索模式任意位置使用

比如下方出现在头尾两处，匹配任意位置包含文本anvil的值

select prod_id,prod_name from products where prod_name like "%anvil%";

比如下方出现在搜索模式的中间，匹配所有以s开头e结尾的值

select prod_name from products where prod_name like "s%e";

下划线_通配符：只能匹配一个任意字符，不能多不能少

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

技巧：不要过度使用通配符，把通配符至于搜索模式的开始处，搜索起来是最慢的！

正则匹配 MySQL中正则表达式特殊字符转义使用\，一般的正则表达式中只使用一个\

表2.特殊字符转义元字符说明 \\ 字符\ \n 换行 \f 换页 \r 回车 \t 制表 \v 纵向制表表3.字符类类说明 [:alnum:] 任意字母和数字(同[a-zA-Z0-9]) [:alpha:] 任意字母(同[a-zA-Z]) [:blank:] 空格和制表(同[\t]) [:cntrl:] ASCII控制字符(ASCII0到31和127) [:digit:] 任意数字(同[0-9]) [:print:] 任意可打印字符 [:graph:] 与[:print:]相同，但不包括空格 [:punct:] 既不在[:alnum:]又不在[:cntrl:]中的任意字符 [:space:] 包括空格在内的任意空白字符(同[\f\n\r\t\v]) [:lower:] 任意小写字母(同[a-z]) [:upper:] 任意大写字母(同[A-Z]) [:xdigit:] 任意十六进制数字(同[a-fA-FO-9]) -- 基本字符匹配

查找产品名中含有'1000'的所有行

select prod_name from products where prod_name regexp "1000";

.在正则表达式中，匹配任意一个字符

select prod_name from products where prod_name regexp ".000";

like + 通配符

select prod_name from products where prod_name like "%1000" order by prod_name; # 返回结果'JetPack 1000' select prod_name from products where prod_name like "%000" order by prod_name; # 返回结果 'JetPack 1000' 'JetPack 2000'

Regexp在列值中匹配

select prod_name from products where prod_name regexp ".000" order by prod_name; # 返回结果 'JetPack 1000' 'JetPack 2000'

regexp如何匹配整个列，同like效果呢，使用^和$定位符即可

-- 正则表达式匹配默认不分大小写，需使用BINARY区分大小写 -- select prod_name from products where prod_name regexp binary "JetPack .000"; -- 报错: ERROR 3995 (HY000): Character set 'utf8mb4_general_ci' cannot be used in conjunction with 'binary' in call to regexp_like. select prod_name from products where CAST(prod_name AS BINARY) regexp binary "JetPack .000";

-- 正则表达式的OR操作符： | select prod_name from products where prod_name regexp "1000|2000" order by prod_name;

-- 正则表达式匹配几个字符之一 [ ] -- []是另一种形式的OR语句 select prod_name from products where prod_name regexp '[123] Ton' order by prod_name; # [123]匹配单一字符：1或2或3 select prod_name from products where prod_name regexp '[1|2|3] Ton' order by prod_name; # [1|2|3]同[123]，匹配单一字符：1或2或3 select prod_name from products where prod_name regexp '1|2|3 ton' order by prod_name; # '1|2|3 ton'匹配1或2或'3 ton' select prod_name from products where prod_name regexp '1 ton' order by prod_name; # 1匹配除1、2、3外的其他字符

-- 正则表达式匹配范围 select prod_name from products where prod_name regexp '[1-5] Ton' order by prod_name; # [1-5]匹配1,2,3,4,5

-- 正则表达式匹配特殊字符，必须用\前导，进行转义 -- 多数正则使用单反斜杠转义，但mysql使用双反斜杠，mysql自己解释一个，正则表达式库解释一个 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; # '.'匹配除"\n"之外的任何单个字符，每行都会被检索出来

-- 正则表达式匹配字符类

[:digit:]匹配任意数字

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

-- 匹配多个实例

* 0个或多个匹配

+ 1个或多个匹配（等于{1,}）

? 0个或1个匹配（等于{0,1}）

{n} 指定数目的匹配

{n,} 不少于指定数目的匹配

{n,m} 匹配数目的范围: n到m（m不超过255）

select prod_name from products where prod_name regexp '$[0-9] sticks?$' order by prod_name; # 返回了'TNT (1 stick)'和'TNT (5 sticks)', ?表示前面的字符s出现0次或1次 select prod_name from products where prod_name regexp '[[:digit:]]{4}' order by prod_name; # [[:digit:]]{4}匹配连在一起的任意4位数字

-- 'ab*c'表示a和c之间出现0次或者多次'b' select 'ac' regexp 'ab*c'; # 1 select 'abbbbbbbc' regexp 'ab*c'; # 1 select 'adc' regexp 'ab*c'; # 0 -- 定位符

^ 文本的开始

$ 文本的结尾

select prod_name from products where prod_name regexp '^[0-9\.]' order by prod_name; #找出以一个数（包括以小数点开始的数）开始的所有产品 select prod_name from products where prod_name regexp '[0-9\.]' order by prod_name; #找出包括小数点和数字的所有产品

-- ^的双重作用

2:匹配除a、b、c之外的字符

^[abc]:匹配以a或b或c开头的字符串

-- 不使用数据库表也可以进行正则表达式的测试：如果匹配返回1，无匹配返回0 select 'hello' regexp '[0-9]'; # 返回 0 select 'hello' regexp '[:alnum:]'; # 返回 1 创建计算字段字段等价于数据库中表的某一列

-- 1.拼接字段

select concat(vend_name,' (',vend_country,')') as 'name(location)' from vendors order by vend_name;
name(location)
ACME (USA)
Anvils R Us (USA)
Furball Inc. (USA)
Jet Set (England)
Jouets Et Ours (France)
LT Supplies (USA)

-- 2.去除空格 -- 删除数据左侧多余空格 ltrim() -- 删除数据两侧多余空格 trim() -- 删除数据右侧多余空格 rtrim() select ltrim(' apple'); # apple select trim(' apple '); # apple select rtrim('apple '); # apple

-- 3.算数运算 select prod_id,quantity,item_price,quantity * item_price as expanded_price from orderitems where order_num = 20005; # 计算总价expanded_price

-- 4.简单测试计算 select 2*3; select trim('abc'); select now(); # 返回当前日期和时间数据处理函数表4.文本处理函数函数说明 left(str,length) 返回str左边的length个字符 right(str,length) 返回str右边的length个字符 length(str) 返回串的长度 locate(str1,str2) str1在str2中第一次出现的位置(从1开始) upper(str) 将串转换为大写 lower(str) 将串转换为小写 trim(str) 去掉串两边的空格 soundex() 返回串的soundex值 substring(str, pos) 返回str的第pos个位置开始的子串,pos支持复数 -- upper()函数转换文本为大写 select vend_name, upper(vend_name) as vend_name_upcase from vendors order by vend_name;

-- soundex() 描述语音表示的字母数字模式的算法,对串按照发音比较而不是字母比较 select cust_name,cust_contact from customers where cust_contact = 'Y. Lie'; # 无返回 select cust_name,cust_contact from customers where soundex(cust_contact) = soundex('Y. Lie'); # 按发音搜索

-- 其他函数测试

select length('apple'); # 5 select left('apple',3); # 'app' select right('apple',3); # 'ple' select locate('ab','ababa'); # 1 select substring('aabbbc',2); # 'abbbc' select substring('aabbbc',-2); # 'bc' 表5.日期和时间处理函数函数说明 adddate() 增加一个日期（天，周等） addtime() 增加一个时间（时、分等） curdate() 返回当前日期 curtime() 返回当前时间 date() 返回日期时间的日期部分 datediff() 计算两个日期之差 date_add() 高度灵活的日期运算函数 date_format() 返回一个格式化的日期或时间串 day() 返回一个日期的天数部分 dayofweek() 对于一个日期，返回对应的星期几 hour() 返回一个时间的小时部分 minute() 返回一个时间的分钟部分 month() 返回一个日期的月份部分 second() 返回一个时间的秒部分 now() 返回当前日期和事件 time() 返回一个日期时间的时间部分 year() 返回一个日期的年份部分

日期格式:YYYY-yy-DD, 时间格式: HH:mm:SS

select adddate('2022-12-01',30); # 2022-12-31 select addtime('13:01:24','2:12:14'); # 15:13:38 select date_format('2022/12/13','%Y-%m-%d'); # 2022-12-13 select datediff('2022-12-13','2022-12-20'); # -7 select now(); # 2022-12-28 23:13:41 select time(now()); # 23:13:55 select year('2022-12-11'); # 2022

首选的日期格式yyyy-mm-dd，避免多义性

select cust_id,order_num from orders where order_date = "2005-09-01"; select * from orders; # order_date为datetime数据类型，含有时间信息；如果时间信息不是00:00:00,上句查找无结果

按照date()日期进行过滤信息，更可靠

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

检索2005年9月下的订单

select cust_id,order_num from orders where year(order_date) = 2005 and month(order_date) = 9; select cust_id,order_num from orders where date(order_date) between "2005-09-01" and "2005-09-30"; 表6.数值处理函数函数说明 abs() 返回一个数的绝对值 cos() 返回一个角度的余弦 exp() 返回一个数的指数值 mod() 返回除操作的余数 pi() 返回圆周率 sin() 返回一个角度的正弦 sqrt() 返回一个数的平方根 tan() 返回一个角度的正切 rand() 返回一个随机数 select abs(-10); # 10 select cos(pi()); # -1 select exp(0); # 1 select mod(3,2); # 1 select pi(); # 3.141593 select sin(pi()/2); # 1 select sqrt(4); # 2 select tan(pi()/4); # 0.9999999999999999 select rand()*10; # 6.432062197879015 吐槽应该没人会用这玩意来计算吧… 聚集函数表7.常用聚集函数函数说明 avg() 返回某列的平均值 count() 返回某列的行数 max() 返回某列的最大值 min() 返回某列的最小值 sum() 返回某列值之和 -- avg()

AVG()返回products表中所有产品的平均价格

AVG()忽略列值为NULL所在行

select avg(prod_price) as avg_price from products;

返回特定供应商所提供产品的平均价格

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

avg()只能作用于单列，多列使用多个avg()

select avg(item_price) as avg_itemprice,avg(quantity) as avg_quantity from orderitems;

-- count()

COUNT(*)对表中行的数目进行计数，不忽略NULL值

select count(*) as num_cust from customers;

使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值

select count(cust_email) as num_cust from customers;

-- max() & min() -- max()和min()函数忽略列值为NULL的行。

MAX()返回products表中最贵的物品的价格

select max(prod_price) as max_price from products;

在用于文本数据时，如果数据按相应的列排序，则MAX()返回最后一行

select max(prod_name) from products;

MIN()返回products表中最便宜物品的价格

select min(prod_price) as min_price from products;

在用于文本数据时，如果数据按相应的列排序，则MIN()返回最前面一行

select min(prod_name) from products;

-- sum() -- sum()函数忽略列值为NULL的行。

检索所订购物品的总数（所有quantity值之和）

select sum(quantity) as items_ordered from orderitems; select sum(quantity) as items_ordered from orderitems where order_num = 20005;

订单20005的总订单金额

select sum(quantity * item_price) as total_price from orderitems where order_num = 20005;

-- 聚类不同值 distinct

使用了DISTINCT参数，因此平均值只考虑各个不同的价格

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

distinct 只能作用于count(),不能用于count(*)

distinct 同max(),min()的结合使用，一个列中的最小值和最大值不管是否包含不同值都是相同的，没有意义

select count(distinct prod_price) from products; # 12 -- select count(distinct) from products; # 错误 -- 组合聚类函数

4个聚集计算:物品的数目，产品价格的最高、最低以及平均值

SELECT

COUNT(*) AS num_items,
MIN(prod_price) AS price_min,
MAX(prod_price) AS price_max,
AVG(prod_price) AS price_avg

FROM

products;

group by和having group by子句可以包含任意数目的列 group by子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在select中使用表达式，则必须在group by子句中指定相同的表达式。不能使用别名。除聚集计算语句外，select语句中的每个列都必须在group by子句中给出。如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。 group by子句必须出现在where子句之后，order by子句之前。 having子句接在group by之后 having和where的差别：where在数据分组前进行过滤，having在数据分组后进行过滤。

-- group by 分组

按vend_id排序并分组数据

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

使用多个列进行分组，除聚集计算语句外，`select`语句中的每个列都必须在`group by`子句中给出。

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

使用WITH ROLLUP关键字，可以得到每个分组的汇总值，下述语句得到所有分组count(*)的和14

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

NULL值自成一组

select vend_state, count(*) from vendors group by vend_state;
vend_state	count(*)
MI	1
OH	1
CA	1
NY	1
NULL	2

-- having子句过滤分组

where过滤行，having过滤分组

WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤

查询 COUNT(*) >=2（两个以上的订单）的那些分组

select cust_id, count() as orders from orders group by cust_id having count()>=2;

-- where和having组合使用

列出具有2个（含）以上、价格为10（含）以上的产品的供应商

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

不加where条件，结果不同

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

-- 分组和排序

检索总计订单价格大于等于50的订单的订单号和总计订单价格

select order_num,sum(quantity item_price) as ordertotal from orderitems group by order_num having sum(quantity item_price) >=50;

按总计订单价格排序输出

SELECT

order_num, SUM(quantity * item_price) AS ordertotal

FROM

orderitems

GROUP BY order_num HAVING SUM(quantity * item_price) >= 50 ORDER BY ordertotal;

-- select子句总结及顺序

子句说明是否必须使用

select 要返回的列或表达式是

from 从中检索数据的表仅在从表选择数据时使用

where 行级过滤否

group by 分组说明仅在按组计算聚集时使用

having 组级过滤否

order by 输出排序顺序否

limit 要检索的行数否

相关子查询相关子查询（correlated subquery) : 涉及外部查询的子查询。

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

对客户10001的订单进行计数

select count(order_num) from orders where cust_id = 10001;

显示customers 表中每个客户的订单总数

select cust_name,cust_state, (select count(*) from orders where orders.cust_id = customers.cust_id) as orders from customers order by cust_name; 表的连接 /images/all/sql_join_1.png 完全限定列名：在引用的列可能出现二义性时，必须使用完全限定列名（用一个点分隔的表名和列名）

笛卡儿积(cartesianproduct)：由没有连接条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

-- 创建连接

where子句连接

select vend_name,prod_name,prod_price from vendors,products where vendors.vend_id = products.vend_id order by vend_name,prod_name;

-- 笛卡尔积 / 叉连接 /*由没有连接条件的表关系返回的结果为笛卡尔积。检索出的行的数目将是第一个表中的行数乘以第二个表的行数。*/

删除where连接条件

返回的数据用每个供应商匹配了每个产品，它包括了供应商不正确的产品

select vend_name,prod_name,prod_price from vendors,products order by vend_name,prod_name;

-- 内连接inner join，也称为等值连接 -- 中间表为经过on条件过滤后的笛卡尔积 select vend_name,prod_name,prod_price from vendors inner join products on vendors.vend_id = products.vend_id;

编号为20005的订单中的物品及对应情况

select prod_name,vend_name,prod_price,quantity from orderitems,products,vendors where products.vend_id = vendors.vend_id and orderitems.prod_id = products.prod_id and order_num = 20005;

订购产品TNT2的客户列表

select cust_name,cust_contact from customers,orders,orderitems where customers.cust_id = orders.cust_id and orders.order_num = orderitems.order_num and prod_id = 'TNT2'; MySQL在运行时关联指定的每个表以处理连接。这种处理可能是非常耗费资源的，因此应该仔细，不要连接不必要的表。连接的表越多，性能下降越厉害。

其他连接类型自然连接: R\Join S

外连接:

左外连接(left outer join)：以第一个关系（左表）为主，在第二个关系（右表）中根据匹配条件找到满足条件的元素，并把它们连接起来，如果右表中没有对应的元素，则在相应位置上的值为NULL，左外连接的结果行数等于左表的行数右外连接(right outer join)：以第二个关系（右表）为主，在第一个关系（左表）中根据匹配条件找到满足条件的元素，并把他们连接起来，如果左表中没有对应的元素，则在相应位置上的值为NULL，右外连接的结果行数等于右表的行数全外连接(full join): 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。MySQL中没有full join关键字，可以通过LEFT JOIN UNION RIGHT JOIN来实现注意注意所使用的连接类型。一般我们使用内部连接，但使用外部连接也是有效的。

保证使用正确的连接条件，否则将返回不正确的数据。应该总是提供连接条件，否则会得出笛卡儿积。

在一个连接中可以包含多个表，甚至对于每个连接可以采用不同的连接类型。

-- 自然连接(去除等值连接的重复列) select * from customers natural join orders;

-- 内连接 select customers.cust_id,orders.order_num from customers inner join orders on customers.cust_id = orders.cust_id;

-- 左外连接 select customers.cust_id,orders.order_num from customers left outer join orders on customers.cust_id = orders.cust_id;

-- 右外连接 select customers.cust_id,orders.order_num from customers right outer join orders on customers.cust_id = orders.cust_id;

-- 左外连接不包含内连接 select customers.cust_id,orders.order_num from customers left outer join orders on customers.cust_id = orders.cust_id where orders.cust_id is null;

-- 右外连接不包含内连接 select customers.cust_id,orders.order_num from customers right outer join orders on customers.cust_id = orders.cust_id where customers.cust_id is null;

-- 全连接 select customers.cust_id,orders.order_num from customers left outer join orders on customers.cust_id = orders.cust_id; UNION select customers.cust_id,orders.order_num from customers right outer join orders on customers.cust_id = orders.cust_id; 聚合函数和连接

检索所有客户分别对应的订单数，inner join

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;

检索所有客户分别对应的订单数，包括没有订单的客户，left outer join

select customers.cust_name, customers.cust_id, count(orders.order_num) as num_ord from customers left outer join orders on customers.cust_id = orders.cust_id group by customers.cust_id; 组合查询使用UNION操作符来组合SELECT语句。利用UNION，可把多条查询的结果作为一条组合查询返回，不管它们的结果中包含还是不包含重复。

价格小于等于5的所有物品

select vend_id,prod_id,prod_price from products where prod_price <=5;

供应商1001和1002生产的所有物品

select vend_id,prod_id,prod_price from products where vend_id in (1001,1002);

价格小于等于5的所有物品的列表，而且包括供应商1001和1002生产的所有物品（不考虑价格）

方法1 使用union

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

方法2 使用where

select vend_id,prod_id,prod_price from products where prod_price <=5 or vend_id in (1001,1002);

union默认自动去除重复的行

union all，匹配所有行，不取消重复行

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); # 有一行出现2次

对union组合结果进行排序

SELECT语句的输出用ORDER BY子句排序。

在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后

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; 本书使用的表和数据建表语句

MySQL Crash Course

http://www.forta.com/books/0672327120/

Example table creation scripts

Create customers table

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 , cust_country char(50) NULL , cust_contact char(50) NULL , cust_email char(255) NULL , PRIMARY KEY (cust_id) ) ENGINE=InnoDB;

Create orderitems table

CREATE TABLE orderitems ( order_num int NOT NULL , order_item int NOT NULL , prod_id char(10) NOT NULL , quantity int NOT NULL , item_price decimal(8,2) NOT NULL , PRIMARY KEY (order_num, order_item) ) ENGINE=InnoDB;

Create orders table

CREATE TABLE orders ( order_num int NOT NULL AUTO_INCREMENT, order_date datetime NOT NULL , cust_id int NOT NULL , PRIMARY KEY (order_num) ) ENGINE=InnoDB;

Create products table

CREATE TABLE products ( prod_id char(10) NOT NULL, vend_id int NOT NULL , prod_name char(255) NOT NULL , prod_price decimal(8,2) NOT NULL , prod_desc text NULL , PRIMARY KEY(prod_id) ) ENGINE=InnoDB;

Create vendors table

CREATE TABLE vendors ( vend_id int NOT NULL AUTO_INCREMENT, vend_name char(50) NOT NULL , vend_address char(50) NULL , vend_city char(50) NULL , vend_state char(5) NULL , vend_zip char(10) NULL , vend_country char(50) NULL , PRIMARY KEY (vend_id) ) ENGINE=InnoDB;

Create productnotes table

CREATE TABLE productnotes ( note_id int NOT NULL AUTO_INCREMENT, prod_id char(10) NOT NULL, note_date datetime NOT NULL, note_text text NULL , PRIMARY KEY(note_id), FULLTEXT(note_text) ) ENGINE=MyISAM;

Define foreign keys

ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num); ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id); ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id); ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id); 插入数据

MySQL Crash Course

http://www.forta.com/books/0672327120/

Example table population scripts

Populate customers table

INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES(10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@coyote.com'); INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact) VALUES(10002, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '43333', 'USA', 'Jerry Mouse'); INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES(10003, 'Wascals', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'rabbit@wascally.com'); INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES(10004, 'Yosemite Place', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Y Sam', 'sam@yosemite.com'); INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact) VALUES(10005, 'E Fudd', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'E Fudd');

Populate vendors table

INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country) VALUES(1001,'Anvils R Us','123 Main Street','Southfield','MI','48075', 'USA'); INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country) VALUES(1002,'LT Supplies','500 Park Street','Anytown','OH','44333', 'USA'); INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country) VALUES(1003,'ACME','555 High Street','Los Angeles','CA','90046', 'USA'); INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country) VALUES(1004,'Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA'); INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country) VALUES(1005,'Jet Set','42 Galaxy Road','London', NULL,'N16 6PS', 'England'); INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country) VALUES(1006,'Jouets Et Ours','1 Rue Amusement','Paris', NULL,'45678', 'France');

Populate products table

INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('ANV01', 1001, '.5 ton anvil', 5.99, '.5 ton anvil, black, complete with handy hook'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('ANV02', 1001, '1 ton anvil', 9.99, '1 ton anvil, black, complete with handy hook and carrying case'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('ANV03', 1001, '2 ton anvil', 14.99, '2 ton anvil, black, complete with handy hook and carrying case'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('OL1', 1002, 'Oil can', 8.99, 'Oil can, red'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('FU1', 1002, 'Fuses', 3.42, '1 dozen, extra long'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('SLING', 1003, 'Sling', 4.49, 'Sling, one size fits all'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('TNT1', 1003, 'TNT (1 stick)', 2.50, 'TNT, red, single stick'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('TNT2', 1003, 'TNT (5 sticks)', 10, 'TNT, red, pack of 10 sticks'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('FB', 1003, 'Bird seed', 10, 'Large bag (suitable for road runners)'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('FC', 1003, 'Carrots', 2.50, 'Carrots (rabbit hunting season only)'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('SAFE', 1003, 'Safe', 50, 'Safe with combination lock'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('DTNTR', 1003, 'Detonator', 13, 'Detonator (plunger powered), fuses not included'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('JP1000', 1005, 'JetPack 1000', 35, 'JetPack 1000, intended for single use'); INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc) VALUES('JP2000', 1005, 'JetPack 2000', 55, 'JetPack 2000, multi-use');

Populate orders table

INSERT INTO orders(order_num, order_date, cust_id) VALUES(20005, '2005-09-01', 10001); INSERT INTO orders(order_num, order_date, cust_id) VALUES(20006, '2005-09-12', 10003); INSERT INTO orders(order_num, order_date, cust_id) VALUES(20007, '2005-09-30', 10004); INSERT INTO orders(order_num, order_date, cust_id) VALUES(20008, '2005-10-03', 10005); INSERT INTO orders(order_num, order_date, cust_id) VALUES(20009, '2005-10-08', 10001);

Populate orderitems table

INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES(20005, 1, 'ANV01', 10, 5.99); INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES(20005, 2, 'ANV02', 3, 9.99); INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES(20005, 3, 'TNT2', 5, 10); INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES(20005, 4, 'FB', 1, 10); INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES(20006, 1, 'JP2000', 1, 55); INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES(20007, 1, 'TNT2', 100, 10); INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES(20008, 1, 'FC', 50, 2.50); INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES(20009, 1, 'FB', 1, 10); INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES(20009, 2, 'OL1', 1, 8.99); INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES(20009, 3, 'SLING', 1, 4.49); INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price) VALUES(20009, 4, 'ANV03', 1, 14.99);

Populate productnotes table

INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES(101, 'TNT2', '2005-08-17', 'Customer complaint: Sticks not individually wrapped, too easy to mistakenly detonate all at once. Recommend individual wrapping.' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES(102, 'OL1', '2005-08-18', 'Can shipped full, refills not available. Need to order new can if refill needed.' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES(103, 'SAFE', '2005-08-18', 'Safe is combination locked, combination not provided with safe. This is rarely a problem as safes are typically blown up or dropped by customers.' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES(104, 'FC', '2005-08-19', 'Quantity varies, sold by the sack load. All guaranteed to be bright and orange, and suitable for use as rabbit bait.' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES(105, 'TNT2', '2005-08-20', 'Included fuses are short and have been known to detonate too quickly for some customers. Longer fuses are available (item FU1) and should be recommended.' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES(106, 'TNT2', '2005-08-22', 'Matches not included, recommend purchase of matches or detonator (item DTNTR).' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES(107, 'SAFE', '2005-08-23', 'Please note that no returns will be accepted if safe opened using explosives.' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES(108, 'ANV01', '2005-08-25', 'Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES(109, 'ANV03', '2005-09-01', 'Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES(110, 'FC', '2005-09-01', 'Customer complaint: rabbit has been able to detect trap, food apparently less effective now.' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES(111, 'SLING', '2005-09-02', 'Shipped unassembled, requires common tools (including oversized hammer).' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES(112, 'SAFE', '2005-09-02', 'Customer complaint: Circular hole in safe floor can apparently be easily cut with handsaw.' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES(113, 'ANV01', '2005-09-05', 'Customer complaint: Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.' ); INSERT INTO productnotes(note_id, prod_id, note_date, note_text) VALUES(114, 'SAFE', '2005-09-07', 'Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added. Comment forwarded to vendor.' );