MySQL必知必会笔记(1)

说明
原书的代码基于MySQL5,本文基于MySQL8,部分SQL语句有所修改

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

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

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

外键为某个表(子表)中的一列,它是另一个表(父表)的主键值,建立起两个表之间的关系。

#############################
# 第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; # 修改表的默认字符集和排序规则

配置文件位于/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关键字应用于所有列而不仅是前置它的列

select distinct vend_id from products;  # 使用distinct关键字去重,distinc只能放在列名的前面
select distinct vend_id,prod_price from products;  # distinct不仅对前置它的列vend_id起作用,同时也作用于prod_price,两列值有重复,才去重

限制返回结果的数量和查询的范围,行数下标从0开始

select prod_name from products limit 5; #从第0行开始,返回前5行
select prod_name from products limit 5,5; #从第5行开始,检索5行

默认为ASC升序,降序可以使用DESC

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

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

# 按单列排序 
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; # 最低值 
表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;

通配符(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 '[^123] ton' order by prod_name;  # [^123]匹配除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;  #找出包括小数点和数字的所有产品

-- ^的双重作用 
# [^abc]:匹配除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) str1str2中第一次出现的位置(从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子句可以包含任意数目的列
  • 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.'
);

相关文章