关系型数据库:基于关系代数理论
缺点:表结构不直观,实现复杂,速度慢
优点:健壮性高(通过二维表键的外键,由数据库系统保证健壮性),社区庞大
JOIN与GROUP BY:
product表
category表
运行SELECT * FROM ‘product’ join ‘category’,会出来笛卡尔机
运行SELECT * FROM ‘product’p join ‘category’ c
on p.’categoryId’=c.’categoryId’
运行SELECT * FROM ‘product’p LEFT JOIN ‘category’ c
on p.’categoryId’=c.’categoryId’
左外连接,使product表里的categoryId为null也能显示出来。
运行SELECT p.’categroyId’, COUNT(*) FROM ‘product’ p LEFT JOIN ‘category’ c
on p.’categoryId’=c.’categoryId’
GROUP BY p.’categoryId’
使用了聚合函数,算出每种类型里有几中商品
把nike添加到shoe类后
运行SELECT p.’categroyId’, ’categoryName’,COUNT(*) FROM ‘product’ p LEFT JOIN ‘category’ c
on p.’categoryId’=c.’categoryId’
GROUP BY p.’categoryId’,’categoryName’
每个类型商品有几个
运行SELECT p.’categroyId’, ’categoryName’, MIN(p.’price’) FROM ‘product’p LEFT JOIN ‘category’ c
on p.’categoryId’=c.’categoryId’
GROUP BY p.’categoryId’,’categoryName’
找出每个类型商品最便宜的价格是多少
找出每个类型商品中最便宜价格对应的商品名称的错误示范
运行SELECT p.’categroyId’, ’categoryName’,p.’productName’ MIN(p.’price’) FROM ‘product’ p LEFT JOIN ‘category’ c
on p.’categoryId’=c.’categoryId’
GROUP BY p.’categoryId’,’categoryName’
会显示出一条productName,但不一定是对应的那个
如果能把product的价格和每个category下最便宜的价格对应起来则可以查出来
因此
SELECT * ‘product’ p join(
SELECT p.’categroyId’, ’categoryName’, MIN(p.’price’) FROM ‘product’p LEFT JOIN ‘category’ c
on p.’categoryId’=c.’categoryId’
GROUP BY p.’categoryId’, ’categoryName’) AS cat_min
on p.’categoryId’ = cat_min.’categoryId’
SELECT p.*, cat_min.categoryName ‘product’ p join(
SELECT p.’categroyId’, ’categoryName’, MIN(p.’price’) as min_price FROM ‘product’p LEFT JOIN ‘category’ c
on p.’categoryId’=c.’categoryId’
GROUP BY p.’categoryId’, ’categoryName’) AS cat_min
on p.’categoryId’ = cat_min.’categoryId’
WHERE p.’price’ = cat_min.min_price
只要product表和categoryName就好