MySQL必知必会笔记(1)

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

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

systemctl restart mysql.service

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

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

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

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;

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

# 找到所有产品名以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";

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

-- 基本字符匹配 
# 查找产品名中含有'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();  # 返回当前日期和时间

-- 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' 

# 日期格式: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";

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

-- 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 分组 
# 按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			要检索的行数 				否 

-- 作为计算字段使用子查询
# 对客户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;

-- 创建连接 
# 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';

-- 自然连接(去除等值连接的重复列)
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; 

# 价格小于等于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.'
);