DataWhale SQL 学习小结 cheat sheet

01 创建表

-- 创建数据库
CREATE DATABASE shop; 
use shop;
-- 创建表
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)); 
-- 删除表
DROP TABLE product; 
-- 添加字段
ALTER TABLE product ADD COLUMN product_name_pinyin VARCHAR(100); 
-- 删除字段
ALTER TABLE product DROP COLUMN product_name_pinyin; 

-- 开始事务
START TRANSACTION;
-- 插入记录
INSERT INTO productins VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
-- 更新记录 
UPDATE product
   SET sale_price = sale_price * 10,
 WHERE product_type = '厨房用具';  
-- 提交事务
COMMIT; 

02 查询表

代码的书写顺序：

        SELECT  =>  FROM => WHERE => GROUP BY => HAVING => ORDER BY

代码的执行顺序：

        FROM => WHERE => GROUP BY => HAVING => SELECT => ORDER BY

03.1 创建视图

视图是基于实表的虚拟表，且同实表一样、记录的排列没有顺序（不可使用 oder by）

-- 根据两张表创建视图
CREATE VIEW view_shop_product(product_type, sale_price, shop_name)
AS
SELECT product_type, sale_price, shop_name
  FROM product,
       shop_product
 WHERE product.product_id = shop_product.product_id;

03.2 子查询

子查询从内层开始执行

-- 标量子查询，查询高于平均价格的的商品
SELECT product_id, product_name, sale_price
  FROM product
 WHERE sale_price > (SELECT AVG(sale_price) FROM product);
--关联子查询，查询各商品种类中 高于该种类平均价的商品
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);

简单易懂教你学会SQL关联子查询 - 知乎

03.3 函数、谓词

函数：

• 算术函数 （用来进行数值计算的函数）
• 字符串函数 （用来进行字符串操作的函数）
• 日期函数 （用来进行日期操作的函数）
• 转换函数 （用来转换数据类型和值的函数）
• 聚合函数 （用来进行数据聚合的函数）

谓词：

• LIKE
• BETWEEN
• IS NULL、IS NOT NULL
• IN
• EXISTS

03.4 CASE 表达式

 04 集合运算

ex 4.3 每类商品中售价最高的商品都在哪些商店有售 ？

ex 4.4 分别使用内连结和关联子查询每一类商品中售价最高的商品。

内连接

关联子查询

ex 4.5 用关联子查询实现：在 product 表中，取出 product_id, produc_name, slae_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和。