主键(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 ‘¹ ton’ order by prod_name; # ¹匹配除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; #找出包括小数点和数字的所有产品

— ^的双重作用

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

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

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

— 1.拼接字段

— 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值自成一组

— 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.’
);