MySQL数据库第二版实训7答案 mysql数据库技术第二版实训7

-- ---------------------unit1----------------------
show databases;
use shop;
show tables;
-- create table
create table Product(
product_id char(4) not null,
product_name varchar(100) not null,
product_type varchar(32) not null,
sale_price integer,
purchase_price integer,
regist_date date,
primary key (product_id)
);
show columns from product;
alter table Product add column on_sale varchar(1) not null;
alter table Product drop column on_sale;
-- DML:insert data
start transaction;
insert into product values ('0001', 'T-Shirt', 'Clothes', 1000, 500, '2023-10-26');
INSERT INTO Product VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO Product VALUES ('0003', '运动T恤', '衣服', 4000, 2800, null);
INSERT INTO Product VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO Product VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO Product VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO Product VALUES ('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO Product VALUES ('0008', '圆珠笔', '办公用品', 100, NULL,'2009-11-11');
commit;
-- alter table's name
rename table product_1 to Product;
-- ------------------------unit2---------------------------
-- 2.1
-- 输出
select product_id, product_name, purchase_price from product;
-- query for all columns
select * from product;
-- use 'AS' to create another name for column
select product_id as id,
product_name as name,
purchase_price as price
from Product;
select product_id as "商品编号",
product_name as "商品名称",
purchase_price as "进货单价"
from Product;
select "商品" as string,
39 as number,
'2023-03-27' as date,
product_id,
product_name
from product;
-- 使用DISTINCT突出明显元素
select distinct product_type from product;
select distinct regist_date from product; -- null也能合并
select distinct product_type, regist_date from product;
-- where语句：查询特定条件
select * from product where product_type = "衣服"
-- 注释
-- 单行注释
/* 多行
注释 */
-- 2.2 算术运算符 和 比较运算符
select product_name as name,
sale_price as price,
sale_price * 2 as "double price"
from product;
select product_name, sale_price as price from product
where purchase_price >= 500;
select product_name, sale_price - purchase_price as margin from product
where sale_price - purchase_price > 500;
-- 2.2 字符串的大小比较
create table chars(chr char(3) not null, primary key(chr));
start transaction;
insert into chars values('1');
insert into chars values('2');
insert into chars values('3');
insert into chars values('10');
insert into chars values('11');
insert into chars values('222');
commit;
select chr from chars where chr > '2'; -- answer is '222' '3'.
-- is null, is not null: 取出/不要null的记录
select product_name, purchase_price
from product
where purchase_price is null;
select product_name, purchase_price
from product
where purchase_price is not null;
-- 2.3逻辑运算符
-- not: 取非
select product_name, product_type, sale_price from product
where not sale_price >= 1000;
-- 用and和or进行多条件查询
select product_name from product
where sale_price >= 1000 and purchase_price >= 500;
-- 括号改变优先级
select product_name from product
where product_type = "办公用品"
and (regist_date = "2009-09-11" or regist_date = "2009-09-20");
-- ----------------- unit3 --------------------
-- --3.1 对表进行聚合查询
-- count/sum/avg/max/min
select count(*) from product; -- 8
select count(purchase_price) from product; -- 6
select sum(sale_price) from product;
select sum(sale_price), sum(purchase_price) from product;
select avg(sale_price) from product;
select max(purchase_price), min(purchase_price) from product;
/* 暂时无法将null也纳入计算范围，具体方法详见 unit6 */
-- 用 聚合函数distinct 删除重复值
select count(distinct product_type) from product;
select sum(sale_price), sum(distinct sale_price) from product;
-- --3.2 对表进行分组（使用group by分组）
select product_type, count(*)
from product
where product_type = '厨房用具' or product_type = '办公用品'
group by product_type;
/* 注意：①使用group by的时候，select的列名可少不可多（聚合键除外）
②group by中不能使用别名（如把‘product_type’简写成‘pro’）（2023.11：MySQL经测试可以使用别名）
③where中不能使用聚合函数（count、sum、avg、max、min。。。只有select, having, order by可以）
*/
-- --3.3 为聚合结果指定条件（having）
select product_type, count(*)
from product
group by product_type
having count(*) >= 2;
select product_type, avg(sale_price) as avgsp
from product
group by product_type
having avgsp >= 2500;
-- --3.4 排序（order by）
select product_id, product_name, sale_price, purchase_price
from product
order by sale_price;
select product_id, product_name, sale_price, purchase_price
from product
order by sale_price desc;
-- 两个以上的，更详细的排序
select product_id, product_name, sale_price, purchase_price
from product
order by sale_price, product_id ;
-- null默认排前面
select product_id, product_name, sale_price, purchase_price
from product
order by purchase_price;
-- order by 允许使用别名
select product_id as pid,
product_name as pn,
sale_price as sp,
purchase_price as pp
from product
order by pp desc;
-- ----------------- unit4 插入、删除、更新 --------------------
-- 4.1 数据的插入 insert
-- 创建表
create table productins
(
product_id char(4) not null,
product_name varchar(100) not null,
product_type varchar(32) not null,
sale_price integer default 0,
purchase_price integer ,
regist_date date ,
primary key (product_id)
);
-- 从其他表中复制数据
CREATE TABLE ProductCopy
(product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER ,
purchase_price INTEGER ,
regist_date DATE ,
PRIMARY KEY (product_id));
insert into productcopy
select * from product;
-- insert ... select 也可以使用 where / grope by等语句
create table producttype(
product_type varchar(32) not null,
sum_sale_price integer ,
sum_purchase_price integer ,
primary key (product_type)
);
insert into producttype
select product_type, sum(sale_price), sum(purchase_price)
from product
group by product_type;
select * from producttype;
-- 4.2 数据的删除 delete
-- 清空
delete from productcopy;
-- 删除部分行
delete from productcopy
where sale_price >= 4000;
-- 4.3 数据的更新 update
-- 更改整列数据
update productcopy
set regist_date = '2009-10-10';
-- 指定条件
update productcopy
set sale_price = sale_price * 10
where product_type = '厨房用具';
-- 将有值的记录更新为null （null清空）
update productcopy
set regist_date = null
where product_id = '0008';
-- 同时对多列进行update(法1)
update productcopy
set sale_price = sale_price * 10,
purchase_price = purchase_price / 2
where product_type = '厨房用具';
-- 同时对多列进行update(法2, mysql不能使用，postgreSQL 和 DB2 可以使用)
update productcopy
set (sale_price, purchase_price) = (sale_price * 10, purchase_price / 2)
where product_type = '厨房用具';
-- 把之前的英文名称T-shirt记录改为中文
update product
set product_name = 'T恤衫',
product_type = '衣服',
regist_date = '2009-09-20'
where product_id = '0001';
-- 4.4事务
/* 现在，请大家把自己想象为管理 Product（商品）表的程序员或者
软件工程师。销售部门的领导对你提出了如下要求。
“某某，经会议讨论，我们决定把运动 T 恤的销售单价下调 1000 日元，
同时把 T 恤衫的销售单价上浮 1000 日元，麻烦你去更新一下数据库。” */
start transaction;
update product
set sale_price = sale_price - 1000
where product_name = "运动T恤";
update product
set sale_price = sale_price + 1000
where product_name = "T恤衫";
commit;
-- 事务回滚 rollback
start transaction;
update product
set sale_price = sale_price - 1000
where product_name = '运动T恤';
update product
set sale_price = sale_price + 1000
where product_name = 'T恤衫';
rollback;
/*
实际上，几乎所有的数据库产品的事务都无需开始指令,像这样不使用指令而悄悄开始事务
的情况下，应该如何区分各个事务呢？通常会有如下两种情况:
A 每条SQL语句就是一个事务（自动提交模式）
B 直到用户执行COMMIT或者ROLLBACK为止算作一个事务
默认使用自动提交模式的DBMS 有 SQL Server、PostgreSQL 和 MySQL 等。
在默认使用 B 模式的 Oracle 中，事务都是直到用户自己执行提交或者回滚指令才会结束。
*/
-- ----------------- unit5 复杂查询--------------------
-- 5.1 视图
-- 创建视图
create view productsum (product_type, cnt_product)
as
select product_type, count(*)
from product
group by product_type ;
-- 使用视图
select product_type, cnt_product
from productsum;
-- 视图上的视图（多重视图）
create view productsumjim(product_type, cnt_product)
as
select product_type, cnt_product
from productsum
where product_type = '办公用品';
/*
虽然语法上没有错误，但是我们还是应该尽量避免在视图的基础上创建视图。
对多数 DBMS 来说，多重视图会降低 SQL 的性能。因此，希望大家（特别是
刚刚接触视图的读者）能够使用单一视图。
*/
-- 另外，1.视图虽然能使用任何select语句，但唯独不能使用order by。
-- 2.经过聚合得到的视图基本无法更新
-- 非聚合视图的更新 ！注意！更新视图后，原表也会进行更新。
create view productjim
as
select *
from product
where product_type = '办公用品';
insert into productjim
values ('0009', '印章', '办公用品', 95, 10, '2009-11-30');
-- 删除视图 drop view (若删除一级视图，则二级视图也会失效)
drop view productsum;
-- 复原原表
delete from product
where product_id = '0009';
-- 5.2 子查询
-- 子查询 就是 一次性视图（select语句）
-- 正常创建视图，并进行查询（这不叫子查询）
create view productsum(product_type, cnt_product)
as
select product_type, count(*)
from product
group by product_type;
select * from productsum;
-- 或者使用 子查询：
select *
from (select product_type, count(*) as cnt_product
from product
group by product_type)
as productsum_2; -- 此 select创建的视图 用完就消失，不会保存，属于子查询。
-- 子查询多层嵌套(不建议)
select *
from (select *
from (select product_type, count(*) as cnt_product
from product
group by product_type) as productsum_1
where cnt_product = 4) as productsum_2;
-- 标量子查询
/* 只返回一行一列的数据，其返回值适用于 = 或者 <> 这样
需要单一值的比较运算符之中 */
-- 在where子句中使用标量子查询（因为where子句不能使用聚合函数）
select product_name, sale_price
from product
where sale_price > (select avg(sale_price)
from product);
-- 练习：查询 种类内平均售价 高于 所有商品的平均售价 的数据
select product_type, avg(sale_price) as avgsp
from product
group by product_type
having avgsp > (select avg(sale_price) from product);
-- 5.3 关联子查询
-- 场景：查询 高于自己种类平均售价 的商品。
select product_type, product_name, sale_price
from product as P1
where sale_price > (select avg(sale_price) from product as P2
where p1.product_type = p2.product_type
group by product_type);
-- ----------------- unit6 函数、谓词、CASE表达式--------------------
-- 6.1 函数
-- 建表
create table SampleMath
(m numeric (10,3),
n integer,
p integer);
start transaction;
insert into samplemath values (500, 0, null);
INSERT INTO SampleMath(m, n, p) VALUES (-180, 0, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, NULL, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 7, 3);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 5, 2);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 4, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (8, NULL, 3);
INSERT INTO SampleMath(m, n, p) VALUES (2.27, 1, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (5.555,2, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 1, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (8.76, NULL, NULL);
COMMIT;
select * from samplemath;
create table SampleStr
(str1 varchar(40),
str2 varchar(40),
str3 varchar(40));
start transaction;
insert into samplestr values ('opx','rt',null);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abc' ,'def' ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('山田' ,'太郎' ,'是我');
INSERT INTO SampleStr (str1, str2, str3) VALUES ('aaa' ,NULL ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES (NULL ,'xyz',NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('@!#$%' ,NULL ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('ABC' ,NULL ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('aBC' ,NULL ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abc太郎' ,'abc' ,'ABC');
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abcdefabc' ,'abc' ,'ABC');
INSERT INTO SampleStr (str1, str2, str3) VALUES ('micmic' ,'i' ,'I');
COMMIT;
select * from samplestr;
-- ------算术函数:------
-- abs绝对值
select m, abs(m) as abs_col from samplemath;
-- mod求余
select n, p,
mod(n, p) as mod_n_p
from samplemath ;
-- round(值，保留几位小数) 四舍五入
select m, round(m,2) as '保留两位小数'
from samplemath;
-- ------字符串函数:------
-- concat(字符串1, 字符串2, ...) 拼接
select str1, str2, concat(str1, str2) as str_concat
from samplestr;
select str1, str2, str3, concat(str1, str2, str3) as str_concat
from samplestr;
-- length(str) 、 char_length(str) 计算字符串长度
select str1, length(str1) as len_str1
from samplestr; -- length里，汉字这样的全角字符会占用 2 个以上的字节
select str1, char_length(str1) as charlen_str1
from samplestr;
-- lower(str) upper(str)
select str1, lower(str1) as low_str
from samplestr;
select str1, upper(str1) as upr_str
from samplestr;
-- replace（对象str，要替换的部分，替换内容） 把一部分字符串换成另一部分
select str1, str2, str3, replace(str1, str2, str3)
from samplestr;
-- substring(对象str from 起始位置 for 截取数量)
select str1, substring(str1 from 3 for 2) as sub_str
from samplestr;
-- where str in ('', '', '', ...)
-- ------日期函数:------
select current_date; -- 获取当前日期
select current_time; -- 获取当前时间
select current_timestamp -- 当前日期+时间
-- extract(元素 from 日期函数) 提取 日期函数内 的元素
select current_timestamp,
extract(year from current_timestamp) as year,
extract(month from current_timestamp) as month,
extract(day from current_timestamp) as day,
extract(hour from current_timestamp) as hour,
extract(minute from current_timestamp) as minute,
extract(second from current_timestamp) as second;
-- ------转换函数:------
-- cast（转换前的值 as 想转换的数据类型） 数据类型转换
select cast('0001' as signed integer) as int_col; -- 有符号整数
select cast('2009-12-14' as date) as date_col;
-- coalesce(数据1，数据2，数据3……) 返回 从左往右 第一个不是null的值
select coalesce(null, 1) as col_1,
COALESCE(NULL, 'test', NULL) AS col_2,
COALESCE(NULL, NULL, '2009-11-01') AS col_3;
select coalesce(str2, 'IS NULL') as coa_str2
from samplestr;
-- 6.2 谓词
-- 建表
create table SampleLike
(strcol varchar(10));
start transaction;
insert into samplelike values ('abcddd');
insert into samplelike values ('dddabc');
insert into samplelike values ('abdddc');
insert into samplelike values ('abcdd');
insert into samplelike values ('ddabc');
insert into samplelike values ('abddc');
commit;
-- like % _ 模糊查询
select strcol
from samplelike
where strcol like 'ddd%';
select strcol
from samplelike
where strcol like '%ddd%';
select strcol
from samplelike
where strcol like '%ddd'; -- % 任意字符
select strcol
from samplelike
where strcol like 'abc__'; -- _ 任意1个字符
-- between 范围查询
select product_name, sale_price
from product
where sale_price between 100 and 1000; -- between特点：包含临界值
-- is null, is not null 判断是否为null
select product_name, purchase_price
from product
where purchase_price is null;
select product_name, purchase_price
from product
where purchase_price is not null;
-- in, not in 把多条or变成一条in or……，or…… --》 in （……，……）
select product_name, purchase_price
from product
where purchase_price in (320, 500, 5000);
select product_name, purchase_price
from product
where purchase_price not in (select max(purchase_price)
from product); -- 标量子查询 运用：去掉最大值
-- in谓词 + 子查询 进行跨表查询 --
CREATE TABLE ShopProduct
(shop_id CHAR(4) NOT NULL,
shop_name VARCHAR(200) NOT NULL,
product_id CHAR(4) NOT NULL,
quantity INTEGER NOT NULL,
PRIMARY KEY (shop_id, product_id));
start TRANSACTION;
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0001', 30);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0002', 50);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0003', 15);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0002', 30);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0003', 120);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0004', 20);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0006', 10);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0007', 40);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0003', 20);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0004', 50);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0006', 90);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0007', 70);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000D', '福冈', '0001', 100);
COMMIT;
select product_id
from shopproduct
where shop_id = '000C'; -- ①
select product_name, sale_price
from product
where product_id in (select product_id
from shopproduct
where shop_id = '000C' -- ①
);
-- 练习：在product中选取 在东京店（000A）以外销售 的商品
select product_name, sale_price
from product p
where product_id not in (select product_id
from shopproduct
where shop_id = '000A'
);
-- exists exsits(关联子查询)
select product_name, sale_price
from product p
where exists (select * from shopproduct sp
where sp.shop_id = '000C' and sp.product_id = p.product_id
);
-- 对 exists 中， 关联子查询使用 select * 的说明
select 1
from product p ; -- 返回一列 列名和内容都为1 的列
select product_name, sale_price
from product p
where exists (select 1 from shopproduct sp
where sp.shop_id = '000C' and sp.product_id = p.product_id
); -- exists 只关心 where语句中的判断，因此即使 select 1 也不会影响结果
-- 可以把在 exists 的子查询中书写 SELECT * 当作 SQL 的一种习惯
-- 练习：用 not exist 在 product 中选取 在东京店（000A）以外销售 的商品名称、销售价格
select product_name, sale_price
from product p
where not exists (select * from shopproduct sp
where shop_id = '000A' and sp.product_id = p.product_id
);
-- 6.3 case (case表达式也是函数的一种，是SQL中数一数二的重要功能)
-- 搜索case 包含了 简单case的所有功能
-- 用case将 A、B、C 和 不同商品种类字段 合并
select product_name,
case when product_type = '衣服' then concat('A:', product_type)
when product_type = '办公用品' then concat('B:', product_type)
when product_type = '厨房用具' then concat('C:', product_type)
else null -- 含义：上述情况之外时返回 null。
-- ELSE 子句也可以省略不写，这时会被默认为 ELSE NULL。
end as abc_product_type -- end不能省略。
from product p;
-- 用 case 输出一个 行列转换表(相对于group by)
select product_type , sum(sale_price) as sum_price
from product
group by product_type;
/* sum_price_clothes | sum_price_kitchen | sum_price_office
------------------+-------------------+-----------------
5000 | 11180 | 600 */
select sum(case when product_type = '衣服' then sale_price else 0 end) as sum_price_clothes,
sum(case when product_type = '厨房用具' then sale_price else 0 end) as sum_price_kitchen,
sum(case when product_type = '办公用品' then sale_price else 0 end) as sum_price_office
from product p ;
-- ----------------- unit7 集合运算--------------------
CREATE TABLE Product2
(product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER ,
purchase_price INTEGER ,
regist_date DATE ,
PRIMARY KEY (product_id));
START TRANSACTION;
INSERT INTO Product2 VALUES ('0001', 'T恤衫' ,'衣服', 1000, 500, '2008-09-20');
INSERT INTO Product2 VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO Product2 VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO Product2 VALUES ('0009', '手套', '衣服', 800, 500, NULL);
INSERT INTO Product2 VALUES ('0010', '水壶', '厨房用具', 2000, 1700, '2009-09-20');
COMMIT;
-- 7.1 表的加减法
/* 集合运算注意事项
1. 列数 要相同，数据类型 要相同。
2. order by 只能在最后使用一次，其他select语句 无限制。 */
-- union 表的加法
select product_id, product_name
from product
union
select product_id, product_name
from product2;
select product_id, product_name
from product
union all -- union all 保留重复项
select product_id, product_name
from product2;
-- except 表的减法 注意：减数和被减数的位置不同，所得到的结果也不相同
select product_id, product_name
from product
except
select product_id, product_name
from product2;
-- intersect 表的交集
select product_id, product_name
from product
intersect
select product_id, product_name
from product2;
-- 7.2 联结（以列为单位对表进行联结）
CREATE TABLE InventoryProduct
( inventory_id CHAR(4) NOT NULL,
product_id CHAR(4) NOT NULL,
inventory_quantity INTEGER NOT NULL,
PRIMARY KEY (inventory_id, product_id));
start TRANSACTION;
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P001', '0001', 0);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P001', '0002', 120);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P001', '0003', 200);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P001', '0004', 3);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P001', '0005', 0);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P001', '0006', 99);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P001', '0007', 999);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P001', '0008', 200);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P002', '0001', 10);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P002', '0002', 25);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P002', '0003', 34);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P002', '0004', 19);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P002', '0005', 99);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P002', '0006', 0);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P002', '0007', 0);
INSERT INTO InventoryProduct (inventory_id, product_id, inventory_quantity) VALUES ('P002', '0008', 18);
COMMIT;
-- inner join 内联结
select sp.shop_id, sp.shop_name, sp.product_id, p.product_name, p.sale_price
from shopproduct sp inner join product p
on sp.product_id = p.product_id;
/* 要点：1. select 后使用 <表名>.<列名> 形式
2. from 后有两张以上的表
3. on 之后要指定两张表间的 联结键 */
-- 内联结和 where 子句的结合使用 （对 联结后得到的表 进行筛选）
select sp.shop_id, sp.shop_name, sp.product_id, p.product_name, p.sale_price
from shopproduct sp inner join product p
on sp.product_id = p.product_id
where sp.shop_id = '000A';
-- outer join 外联结 （对有外联结的DBMS来说，左右外联结果一样，彼此没有的数据以null表示）
/* 注意！ MySQL没有外联结的概念！
取而代之的是左联结、右联结：left join、right join。左右联结结果并不相同！
A left join B ： （ A ） B ）
A right join B ： （ A （ B ）
*/
select sp.shop_id, sp.shop_name, sp.product_id, p.product_name, p.sale_price
from shopproduct sp right outer join product p
on sp.product_id = p.product_id; -- 依product表，所以sp表中没有的项目以null表示。
-- 三张表以上的联结
select sp.shop_id, sp.shop_name, sp.product_id,
p.product_name, p.sale_price, ip.inventory_quantity
from shopproduct sp inner join product p
on sp.product_id = p.product_id
inner join inventoryproduct ip
on sp.product_id = ip.product_id
where ip.inventory_id = 'P001'
order by ip.inventory_quantity desc;
-- cross join 交叉联结
/* 交叉联结在实际业务中并不会使用，但是是所有联结运算的基础 */
select SP.shop_id, SP.shop_name, SP.product_id, P.product_name
from ShopProduct SP cross join Product P;
-- 除法集合运算
/* 本章中我们学习了以下 4 个集合运算符。
● UNION（并集）
● EXCEPT（差集）
● INTERSECT（交集）
● CROSS JOIN（笛卡儿积）
难道集合运算中没有除法吗？当然不是，除法运算是存在的。集合运算中的
除法通常称为关系除法。
除法运算是集合运算中最复杂的运算，但是其在实际业务中的应用十分广泛，
因此希望大家能在达到中级以上水平时掌握其使用方法。此外，想要详细了解 SQL
中除法运算实现方法的读者，可以参考拙著《達人に学ぶ SQL 徹底指南書》
( 翔泳社 ) 中的 1-4 节和 1-7 节。 */
-- ----------------- unit8 SQL高级处理--------------------
-- 8.1 窗口函数（OLAP函数 OnLine Analytical Processing 实时分析处理）
-- rank 排序 (1,1,1),4
select product_name, product_type, sale_price,
rank() over (partition by product_type
order by sale_price) as ranking
from product;
-- 去掉partition
select product_name, product_type, sale_price,
rank() over (order by sale_price) as ranking
from product;
-- dense_rank (1,1,1),2
select product_name, product_type, sale_price,
dense_rank() over(order by sale_price) as dranking
from product;
-- row_number (1,2,3),4
select product_name, product_type, sale_price,
row_number() over(order by sale_price) as dranking
from product;
-- practise_1
create table addressbook(
regist_no integer not null,
name varchar(128) not null,
address varchar(256) not null,
tel_no char(10),
mail_address char(20),
primary key (regist_no)
);
alter table addressbook add column postal_code char(8) not null;
drop table addressbook;
-- practise_2
select product_name, regist_date from product
where regist_date > "2009-04-28";
select product_name, product_type,
sale_price * 0.9 - purchase_price as profit
from product
where sale_price * 0.9 - purchase_price > 100;
-- practise_3
-- 3.1 错误点：1.对文本内容使用sum 2.
-- 3.2
select product_type, sum(sale_price), sum(purchase_price)
from product
group by product_type
having sum(sale_price) >= sum(purchase_price) * 1.5;
-- 3.3
select *
from product
order by regist_date desc, sale_price;
-- practise_4
-- 4.1 会得到一个空表，因为没有提交。
CREATE TABLE ProductCopy_2
(product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER ,
purchase_price INTEGER ,
regist_date DATE ,
PRIMARY KEY (product_id));
start transaction;
INSERT INTO ProductCopy_2 VALUES ('0001', 'T恤衫', '衣服', 1000, 500, '2008-09-20');
INSERT INTO ProductCopy_2 VALUES ('0002', '打孔器', '办公用品', 500, 320, '2008-09-11');
INSERT INTO ProductCopy_2 VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL)
select * from ProductCopy_2;
-- 4.2 可以这样做
/*错！ 正确答案是不行，违反了主键约束，即主键不能有重复记录*/
-- 4.3
CREATE TABLE ProductMargin
(product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
sale_price INTEGER,
purchase_price INTEGER,
margin INTEGER,
PRIMARY KEY(product_id));
insert into productmargin
select product_id,
product_name,
sale_price,
purchase_price,
sale_price - purchase_price as margin
from product
limit 3;
-- 4.4
/*
‘sale_price - purchase_price as margin’ 不会随着
sale_price 和 purchase_price的变化而变化，必须手动更改。
*/
update productmargin
set sale_price = sale_price - 1000,
margin = sale_price - purchase_price
where product_id = '0003';
-- practice_5
-- 5.1
create view ViewPractice5_1
as
select product_name, sale_price, regist_date
from product
where sale_price >= 1000 and regist_date = '2009-09-20';
select * from viewpractice5_1;
drop view viewpractice5_1;
-- 5.2
insert into viewpractice5_1 values('刀子'，300, '2009-11-02');
-- 答：往视图中插入不符合条件的记录，会出错
-- 5.3
select product_id,
product_name,
product_type,
sale_price,
(select avg(sale_price) from product) as sale_price_all
from product;
-- 5.4 关联子查询 练习
create view AvgPriceByType
as
select product_id, product_name, product_type, sale_price,
(select avg(sale_price)
from product as p2
where p1.product_type = p2.product_type
group by product_type) as avg_sale_price
from product as p1;
-- practice_6
-- 6.1
/*
对本章中使用的 Product（商品）表执行如下 2 条 SELECT 语句，能够得
到什么样的结果呢？ */
-- ①
SELECT product_name, purchase_price
FROM Product
WHERE purchase_price NOT IN (500, 2800, 5000);
-- ②
SELECT product_name, purchase_price
FROM Product
WHERE purchase_price NOT IN (500, 2800, 5000, NULL);
/* 答：①320，790
②选不出任何记录。这个是SQL的危险陷阱，属于中级学习范畴，
在平常使用in，not in时，参数里不要加入null。 */
-- 如何避免这一陷阱，或者选出null记录？ 使用coalesce 把 空值null 变成 字符串null 。
SELECT product_name, purchase_price
FROM Product
WHERE coalesce(purchase_price, 'NULL') not IN (500, 2800, 5000, 'NULL');
SELECT product_name, purchase_price
FROM Product
WHERE coalesce(purchase_price, 'NULL') IN (500, 2800, 5000, 'NULL');
-- 6.2
/* low_price | mid_price | high_price
----------+-----------+------------
5 | 1 | 2 */
select count(case when sale_price <= 1000 then 1 else null end) as low_price,
count(case when sale_price > 1000 and sale_price <= 3000 then 1 else null end) as mid_price,
count(case when sale_price > 3000 then 1 else null end) as high_price
from product; -- count原理 : 值不为 null 便计一次数
-- practice_7
-- 7.1 结果不变
-- 7.2
select coalesce(sp.shop_id, '不确定') as shop_id,
coalesce(sp.shop_name, '不确定') as shop_name,
coalesce(sp.product_id, '不确定') as product_id,
p.product_name,
p.sale_price
from shopproduct sp right join product p
on sp.product_id = p.product_id;