SQL基础教程
- 数据库的创建
- 表的创建
- 表的删除
- 表定义的更新
- SELECT语句基础
- 为列设定别名
- 常数的查询
- 从结果中删除重复行
- 根据WHERE语句来选择记录
- 算术运算符和比较运算符
- 比较运算符
- 对字符串使用不等号时的注意事项
- 不能对NULL使用比较运算符
- NOT运算符
- AND运算符和OR运算符
- 聚合函数
- 计算NULL之外的数据的行数
- 计算合计值
- 计算平均值
- 计算最大值和最小值
- 使用聚合函数删除重复值(关键字DISTINCT)
- GROUP BY 子句
- 聚合键中包含NULL的情况
- 使用WHERE子句时GROUP BY的执行结果
- HAVING 子句
- ORDER BY 子句
- 指定多个排序键
- NULL的排序
- 在排序键中使用显示用的别名
- ORDER BY 子句中可以使用的列
- 不要使用列编号
- INSERT语句的基本语法
- 列清单的省略
- 插入NULL
- 插入默认值
- 从其他表中复制数据
- 数据的删除
- 数据的更新
- 事务
- 创建视图的方法
- 删除视图
- 子查询
- 标量子查询
- 关联子查询
- 算术函数
- 字符串函数
- 日期函数
- 转换函数
- 谓词
- 表的加法——UNION
- UNION ALL——包含重复行
- INTERSECT——选取表中公共部分
- EXCEPT——记录的减法
- 内联结——INNER JOIN
- 外联结——OUTER JOIN
- 交叉联结——CROSS JOIN
- 窗口函数
- GROUPING运算符
数据库的创建
CREATE DATABASE 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;
表定义的更新
mysql/DB2/PostgreSQL
ALTER TABLE Product ADD COLUMN product_name_pinyin VARCHAR(100);
ALTER TABLE Product DROP COLUMN product_name_pinyin;
Oracle
ALTER TABLE Product ADD (product_name_pinyin VARCHAR2(100));
ALTER TABLE Product DROP (product_name_pinyin);
SQL Server
ALTER TABLE Product ADD product_name_pinyin VARCHAR(100);
ALTER TABLE Product DROP COLUMN product_name_pinyin;
-- DML:插入数据
START TRANSACTION;
INSERT INTO Product VALUES ('0001','T恤衫','衣服',1000,500,'2009-09-20');
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;
SELECT语句基础
从Product表中输出3列
SELECT product_id, product_name, purchase_price FROM Product;
+------------+--------------+----------------+
| product_id | product_name | purchase_price |
+------------+--------------+----------------+
| 0001 | T恤衫 | 500 |
| 0002 | 打孔器 | 320 |
| 0003 | 运动T恤 | 2800 |
| 0004 | 菜刀 | 2800 |
| 0005 | 高压锅 | 5000 |
| 0006 | 叉子 | NULL |
| 0007 | 擦菜板 | 790 |
| 0008 | 圆珠笔 | NULL |
+------------+--------------+----------------+
查询全部的列
SELECT * FROM Product;
+------------+--------------+--------------+------------+----------------+-------------+
| product_id | product_name | product_type | sale_price | purchase_price | regist_date |
+------------+--------------+--------------+------------+----------------+-------------+
| 0001 | T恤衫 | 衣服 | 1000 | 500 | 2009-09-20 |
| 0002 | 打孔器 | 办公用品 | 500 | 320 | 2009-09-11 |
| 0003 | 运动T恤 | 衣服 | 4000 | 2800 | NULL |
| 0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2009-09-20 |
| 0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2009-01-15 |
| 0006 | 叉子 | 厨房用具 | 500 | NULL | 2009-09-20 |
| 0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2008-04-28 |
| 0008 | 圆珠笔 | 办公用品 | 100 | NULL | 2009-11-11 |
+------------+--------------+--------------+------------+----------------+-------------+
为列设定别名
SELECT product_id AS id,
product_name AS name,
purchase_price AS price
from Product;
+------+------------+-------+
| id | name | price |
+------+------------+-------+
| 0001 | T恤衫 | 500 |
| 0002 | 打孔器 | 320 |
| 0003 | 运动T恤 | 2800 |
| 0004 | 菜刀 | 2800 |
| 0005 | 高压锅 | 5000 |
| 0006 | 叉子 | NULL |
| 0007 | 擦菜板 | 790 |
| 0008 | 圆珠笔 | NULL |
+------+------------+-------+
SELECT product_id AS "商品编号",
product_name AS "商品名称",
purchase_price AS "进货单价"
from Product;
+--------------+--------------+--------------+
| 商品编号 | 商品名称 | 进货单价 |
+--------------+--------------+--------------+
| 0001 | T恤衫 | 500 |
| 0002 | 打孔器 | 320 |
| 0003 | 运动T恤 | 2800 |
| 0004 | 菜刀 | 2800 |
| 0005 | 高压锅 | 5000 |
| 0006 | 叉子 | NULL |
| 0007 | 擦菜板 | 790 |
| 0008 | 圆珠笔 | NULL |
+--------------+--------------+--------------+
常数的查询
SELECT '商品' AS string, 38 AS number,'2009-02-24' AS date, product_id,product_name
FROM Product;
+--------+--------+------------+------------+--------------+
| string | number | date | product_id | product_name |
+--------+--------+------------+------------+--------------+
| 商品 | 38 | 2009-02-24 | 0001 | T恤衫 |
| 商品 | 38 | 2009-02-24 | 0002 | 打孔器 |
| 商品 | 38 | 2009-02-24 | 0003 | 运动T恤 |
| 商品 | 38 | 2009-02-24 | 0004 | 菜刀 |
| 商品 | 38 | 2009-02-24 | 0005 | 高压锅 |
| 商品 | 38 | 2009-02-24 | 0006 | 叉子 |
| 商品 | 38 | 2009-02-24 | 0007 | 擦菜板 |
| 商品 | 38 | 2009-02-24 | 0008 | 圆珠笔 |
+--------+--------+------------+------------+--------------+
从结果中删除重复行
SELECT DISTINCT product_type FROM Product;
+--------------+
| product_type |
+--------------+
| 衣服 |
| 办公用品 |
| 厨房用具 |
+--------------+
--DISTINCT关键字只能用在第一个列名之前
SELECT DISTINCT product_type,regist_date FROM Product;
+--------------+-------------+
| product_type | regist_date |
+--------------+-------------+
| 衣服 | 2009-09-20 |
| 办公用品 | 2009-09-11 |
| 衣服 | NULL |
| 厨房用具 | 2009-09-20 |
| 厨房用具 | 2009-01-15 |
| 厨房用具 | 2008-04-28 |
| 办公用品 | 2009-11-11 |
+--------------+-------------+
根据WHERE语句来选择记录
SELECT product_name,product_type FROM Product
WHERE product_type = '衣服';
+--------------+--------------+
| product_name | product_type |
+--------------+--------------+
| T恤衫 | 衣服 |
| 运动T恤 | 衣服 |
+--------------+--------------+
算术运算符和比较运算符
SELECT product_name,sale_price,sale_price * 2 AS "sale_price_x2"
FROM Product;
+--------------+------------+---------------+
| product_name | sale_price | sale_price_x2 |
+--------------+------------+---------------+
| T恤衫 | 1000 | 2000 |
| 打孔器 | 500 | 1000 |
| 运动T恤 | 4000 | 8000 |
| 菜刀 | 3000 | 6000 |
| 高压锅 | 6800 | 13600 |
| 叉子 | 500 | 1000 |
| 擦菜板 | 880 | 1760 |
| 圆珠笔 | 100 | 200 |
+--------------+------------+---------------+
比较运算符
--选取出sale_price列为500的记录
SELECT product_name,product_type
FROM Product
WHERE sale_price = 500;
+--------------+--------------+
| product_name | product_type |
+--------------+--------------+
| 打孔器 | 办公用品 |
| 叉子 | 厨房用具 |
+--------------+--------------+
--选取出sale_price列的值不是500的记录
SELECT product_name,product_type
FROM Product
WHERE sale_price <> 500;
+--------------+--------------+
| product_name | product_type |
+--------------+--------------+
| T恤衫 | 衣服 |
| 运动T恤 | 衣服 |
| 菜刀 | 厨房用具 |
| 高压锅 | 厨房用具 |
| 擦菜板 | 厨房用具 |
| 圆珠笔 | 办公用品 |
+--------------+--------------+
--选取出销售单价大于等于1000日元的记录
SELECT product_name,product_type,sale_price
FROM Product
WHERE sale_price >= 1000;
+--------------+--------------+------------+
| product_name | product_type | sale_price |
+--------------+--------------+------------+
| T恤衫 | 衣服 | 1000 |
| 运动T恤 | 衣服 | 4000 |
| 菜刀 | 厨房用具 | 3000 |
| 高压锅 | 厨房用具 | 6800 |
+--------------+--------------+------------+
--选取出登记日期在2009年
SELECT product_name,product_type,regist_date
FROM Product
WHERE regist_date < '2009-09-27'
--WHERE子句的条件表达式中也可以使用计算表达式
SELECT product_name,sale_price,purchase_price FROM Product
WHERE sale_price - purchase_price >= 500;
+--------------+------------+----------------+
| product_name | sale_price | purchase_price |
+--------------+------------+----------------+
| T恤衫 | 1000 | 500 |
| 运动T恤 | 4000 | 2800 |
| 高压锅 | 6800 | 5000 |
+--------------+------------+----------------+
对字符串使用不等号时的注意事项
--DDL 创建表
CREATE TABLE Chars
(chr CHAR(3) NOT NULL,
PRIMARY KEY(chr));
--DML 插入数据
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;
--选取出大于'2'的数据的SELECT语句
SELECT chr FROM Chars WHERE chr > '2';
+-----+
| chr |
+-----+
| 222 |
| 3 |
+-----+
不能对NULL使用比较运算符
SELECT product_name,purchase_price FROM Product WHERE purchase_price <> 2800;
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| T恤衫 | 500 |
| 打孔器 | 320 |
| 高压锅 | 5000 |
| 擦菜板 | 790 |
+--------------+----------------+
说明:执行结果中并没有“叉子”和“圆珠笔”。这两条记录由于进货单价不明(NULL),因此无法判断是不是2800日元。
--选取NULL的记录
SELECT product_name,purchase_price FROM Product WHERE purchase_price IS NULL;
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| 叉子 | NULL |
| 圆珠笔 | NULL |
+--------------+----------------+
--选取不为NULL的记录
SELECT product_name,purchase_price FROM Product WHERE purchase_price IS NOT NULL;
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| T恤衫 | 500 |
| 打孔器 | 320 |
| 运动T恤 | 2800 |
| 菜刀 | 2800 |
| 高压锅 | 5000 |
| 擦菜板 | 790 |
+--------------+----------------+
NOT运算符
--选取出销售单价大于等于100日元的记录
SELECT product_name,product_type,sale_price FROM Product WHERE sale_price >=1000;
+--------------+--------------+------------+
| product_name | product_type | sale_price |
+--------------+--------------+------------+
| T恤衫 | 衣服 | 1000 |
| 运动T恤 | 衣服 | 4000 |
| 菜刀 | 厨房用具 | 3000 |
| 高压锅 | 厨房用具 | 6800 |
+--------------+--------------+------------+
SELECT product_name,product_type,sale_price FROM Product WHERE NOT sale_price >=1000;
--等价于
SELECT product_name,product_type,sale_price FROM Product WHERE sale_price < 1000;
+--------------+--------------+------------+
| product_name | product_type | sale_price |
+--------------+--------------+------------+
| 打孔器 | 办公用品 | 500 |
| 叉子 | 厨房用具 | 500 |
| 擦菜板 | 厨房用具 | 880 |
| 圆珠笔 | 办公用品 | 100 |
+--------------+--------------+------------+
AND运算符和OR运算符
SELECT product_name,purchase_price FROM Product
WHERE product_type = '厨房用具'
AND sale_price >= 3000;
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| 菜刀 | 2800 |
| 高压锅 | 5000 |
+--------------+----------------+
SELECT product_name,purchase_price FROM Product
WHERE product_type = '厨房用具'
OR sale_price >= 3000;
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| 运动T恤 | 2800 |
| 菜刀 | 2800 |
| 高压锅 | 5000 |
| 叉子 | NULL |
| 擦菜板 | 790 |
+--------------+----------------+
--AND运算符优先于OR运算符
SELECT product_name,product_type,regist_date FROM Product
WHERE product_type='办公用品'
AND regist_date='2009-09-11'
OR regist_date = '2009-09-20';
+--------------+--------------+-------------+
| product_name | product_type | regist_date |
+--------------+--------------+-------------+
| T恤衫 | 衣服 | 2009-09-20 |
| 打孔器 | 办公用品 | 2009-09-11 |
| 菜刀 | 厨房用具 | 2009-09-20 |
| 叉子 | 厨房用具 | 2009-09-20 |
+--------------+--------------+-------------+
SELECT product_name,product_type,regist_date FROM Product
WHERE product_type='办公用品'
AND (regist_date='2009-09-11'
OR regist_date = '2009-09-20');
+--------------+--------------+-------------+
| product_name | product_type | regist_date |
+--------------+--------------+-------------+
| 打孔器 | 办公用品 | 2009-09-11 |
+--------------+--------------+-------------+
聚合函数
--COUNT:计算表中的记录数(行数)
--SUM: 计算表中数值列中数据的合计值
--AVG: 计算表中数值列中数据的平均值
--MAX: 求出表中任意列中数据的最大值
--MIN: 求出表中任意列中数据的最小值
SELECT COUNT(*) FROM Product;
+----------+
| COUNT(*) |
+----------+
| 8 |
+----------+
计算NULL之外的数据的行数
SELECT COUNT(purchase_price) FROM Product;
+-----------------------+
| COUNT(purchase_price) |
+-----------------------+
| 6 |
+-----------------------+
计算合计值
--NULL选项不在计算中
SELECT SUM(sale_price) ,SUM(purchase_price) FROM Product;
+-----------------+---------------------+
| SUM(sale_price) | SUM(purchase_price) |
+-----------------+---------------------+
| 16780 | 12210 |
+-----------------+---------------------+
计算平均值
--purchase_price中NULL值不在计算中,只有6个数值
SELECT AVG(sale_price),AVG(purchase_price) FROM Product;
计算最大值和最小值
SELECT MAX(sale_price),MIN(purchase_price) FROM Product;
+-----------------+---------------------+
| MAX(sale_price) | MIN(purchase_price) |
+-----------------+---------------------+
| 6800 | 320 |
+-----------------+---------------------+
使用聚合函数删除重复值(关键字DISTINCT)
SELECT COUNT(DISTINCT product_type) FROM Product;
+------------------------------+
| COUNT(DISTINCT product_type) |
+------------------------------+
| 3 |
+------------------------------+
SELECT DISTINCT COUNT(product_type) FROM Product;
+---------------------+
| COUNT(product_type) |
+---------------------+
| 8 |
+---------------------+
说明:想要计算值的种类时,可以在COUNT函数的参数中使用DISTINCT。
SELECT SUM(sale_price),SUM(DISTINCT sale_price) FROM Product;
+-----------------+--------------------------+
| SUM(sale_price) | SUM(DISTINCT sale_price) |
+-----------------+--------------------------+
| 16780 | 16280 |
+-----------------+--------------------------+
GROUP BY 子句
--按照商品种类统计数据行数
SELECT product_type,COUNT(*) FROM Product GROUP BY product_type;
+--------------+----------+
| product_type | COUNT(*) |
+--------------+----------+
| 衣服 | 2 |
| 办公用品 | 2 |
| 厨房用具 | 4 |
+--------------+----------+
聚合键中包含NULL的情况
SELECT purchase_price,COUNT(*) FROM Product GROUP BY purchase_price;
+----------------+----------+
| purchase_price | COUNT(*) |
+----------------+----------+
| 500 | 1 |
| 320 | 1 |
| 2800 | 2 |
| 5000 | 1 |
| NULL | 2 |
| 790 | 1 |
+----------------+----------+
使用WHERE子句时GROUP BY的执行结果
SELECT purchase_price,COUNT(*) FROM Product WHERE product_type='衣服'
GROUP BY purchase_price;
+----------------+----------+
| purchase_price | COUNT(*) |
+----------------+----------+
| 500 | 1 |
| 2800 | 1 |
+----------------+----------+
说明:GROUP BY和WHERE并用时SELECT语句的执行顺序FROM->WHERE->GROUP BY->SELECT
HAVING 子句
SELECT product_type,COUNT(*)
FROM Product
GROUP BY product_type
HAVING COUNT(*)=2;
+--------------+-----+
| product_type | num |
+--------------+-----+
| 衣服 | 2 |
| 办公用品 | 2 |
+--------------+-----+
SELECT product_type,AVG(sale_price)
FROM Product
GROUP BY product_type
HAVING AVG(sale_price)>=2500;
+--------------+-----------------+
| product_type | avg(sale_price) |
+--------------+-----------------+
| 衣服 | 2500.0000 |
| 厨房用具 | 2795.0000 |
+--------------+-----------------+
ORDER BY 子句
--升序
SELECT product_id,product_name,sale_price,purchase_price FROM Product ORDER BY sale_price;
+------------+--------------+------------+----------------+
| product_id | product_name | sale_price | purchase_price |
+------------+--------------+------------+----------------+
| 0008 | 圆珠笔 | 100 | NULL |
| 0002 | 打孔器 | 500 | 320 |
| 0006 | 叉子 | 500 | NULL |
| 0007 | 擦菜板 | 880 | 790 |
| 0001 | T恤衫 | 1000 | 500 |
| 0004 | 菜刀 | 3000 | 2800 |
| 0003 | 运动T恤 | 4000 | 2800 |
| 0005 | 高压锅 | 6800 | 5000 |
+------------+--------------+------------+----------------+
--降序
SELECT product_id,product_name,sale_price,purchase_price FROM Product ORDER BY sale_price DESC;
+------------+--------------+------------+----------------+
| product_id | product_name | sale_price | purchase_price |
+------------+--------------+------------+----------------+
| 0005 | 高压锅 | 6800 | 5000 |
| 0003 | 运动T恤 | 4000 | 2800 |
| 0004 | 菜刀 | 3000 | 2800 |
| 0001 | T恤衫 | 1000 | 500 |
| 0007 | 擦菜板 | 880 | 790 |
| 0002 | 打孔器 | 500 | 320 |
| 0006 | 叉子 | 500 | NULL |
| 0008 | 圆珠笔 | 100 | NULL |
+------------+--------------+------------+----------------+
指定多个排序键
--价格相同时按照商品编号的升序排列
--优先使用左侧的键,如果该列存在相同值的话,再接着参考右侧的键。
SELECT product_id,product_name,sale_price,purchase_price FROM Product ORDER BY sale_price,product_id;
+------------+--------------+------------+----------------+
| product_id | product_name | sale_price | purchase_price |
+------------+--------------+------------+----------------+
| 0008 | 圆珠笔 | 100 | NULL |
| 0002 | 打孔器 | 500 | 320 |
| 0006 | 叉子 | 500 | NULL |
| 0007 | 擦菜板 | 880 | 790 |
| 0001 | T恤衫 | 1000 | 500 |
| 0004 | 菜刀 | 3000 | 2800 |
| 0003 | 运动T恤 | 4000 | 2800 |
| 0005 | 高压锅 | 6800 | 5000 |
+------------+--------------+------------+----------------+
NULL的排序
--NULL会在结果的开头或末尾汇总显示
SELECT product_id,product_name,sale_price,purchase_price FROM Product ORDER BY purchase_price;
+------------+--------------+------------+----------------+
| product_id | product_name | sale_price | purchase_price |
+------------+--------------+------------+----------------+
| 0006 | 叉子 | 500 | NULL |
| 0008 | 圆珠笔 | 100 | NULL |
| 0002 | 打孔器 | 500 | 320 |
| 0001 | T恤衫 | 1000 | 500 |
| 0007 | 擦菜板 | 880 | 790 |
| 0003 | 运动T恤 | 4000 | 2800 |
| 0004 | 菜刀 | 3000 | 2800 |
| 0005 | 高压锅 | 6800 | 5000 |
+------------+--------------+------------+----------------+
在排序键中使用显示用的别名
SELECT product_id AS id,product_name,sale_price AS sp,purchase_price FROM Product ORDER BY sp,id ;
+------+--------------+------+----------------+
| id | product_name | sp | purchase_price |
+------+--------------+------+----------------+
| 0008 | 圆珠笔 | 100 | NULL |
| 0002 | 打孔器 | 500 | 320 |
| 0006 | 叉子 | 500 | NULL |
| 0007 | 擦菜板 | 880 | 790 |
| 0001 | T恤衫 | 1000 | 500 |
| 0004 | 菜刀 | 3000 | 2800 |
| 0003 | 运动T恤 | 4000 | 2800 |
| 0005 | 高压锅 | 6800 | 5000 |
+------+--------------+------+----------------+
ORDER BY 子句中可以使用的列
SELECT product_name,sale_price,purchase_price FROM Product
ORDER BY product_id;
+--------------+------------+----------------+
| product_name | sale_price | purchase_price |
+--------------+------------+----------------+
| T恤衫 | 1000 | 500 |
| 打孔器 | 500 | 320 |
| 运动T恤 | 4000 | 2800 |
| 菜刀 | 3000 | 2800 |
| 高压锅 | 6800 | 5000 |
| 叉子 | 500 | NULL |
| 擦菜板 | 880 | 790 |
| 圆珠笔 | 100 | NULL |
+--------------+------------+----------------+
SELECT product_type,COUNT(*) FROM Product
GROUP BY product_type
ORDER BY COUNT(*);
+--------------+----------+
| product_type | COUNT(*) |
+--------------+----------+
| 衣服 | 2 |
| 办公用品 | 2 |
| 厨房用具 | 4 |
+--------------+----------+
说明:在ORDER BY 子句中可以使用SELECT子句中未使用的列和聚合函数
不要使用列编号
--通过列名指定
SELECT product_id,product_name,sale_price,purchase_price FROM Product
ORDER BY sale_price DESC,product_id
--通过列编号指定
SELECT product_id,product_name,sale_price,purchase_price FROM Product
ORDER BY 3 DESC ,1;
+------------+--------------+------------+----------------+
| product_id | product_name | sale_price | purchase_price |
+------------+--------------+------------+----------------+
| 0005 | 高压锅 | 6800 | 5000 |
| 0003 | 运动T恤 | 4000 | 2800 |
| 0004 | 菜刀 | 3000 | 2800 |
| 0001 | T恤衫 | 1000 | 500 |
| 0007 | 擦菜板 | 880 | 790 |
| 0002 | 打孔器 | 500 | 320 |
| 0006 | 叉子 | 500 | NULL |
| 0008 | 圆珠笔 | 100 | NULL |
+------------+--------------+------------+----------------+
INSERT语句的基本语法
--通常的INSERT
INSERT INTO Product VALUES ('0001','T恤衫','衣服',1000,500,'2009-09-20');
INSERT INTO Product VALUES ('0002','打孔器','办公用品',500,320,'2009-09-11');
INSERT INTO Product VALUES ('0003','运动T恤','衣服',4000,2800,NULL);
--多行的INSERT(Oracle以外)
INSERT INTO Product VALUES ('0001','T恤衫','衣服',1000,500,'2009-09-20'),('0002','打孔器','办公用品',500,320,'2009-09-11'),('0003','运动T恤','衣服',4000,2800,NULL);
--Oracle中的多行INSERT
INSERT ALL INTO Product VALUES ('0001','T恤衫','衣服',1000,500,'2009-09-20') INTO Product VALUES ('0002','打孔器','办公用品',500,320,'2009-09-11') INTO Product VALUES ('0003','运动T恤','衣服',4000,2800,NULL);
列清单的省略
--包含列清单
INSERT INTO ProductIns (product_id,product_name,product_type,sale_price,purchase_price,regist_date) VALUES ('0005','高压锅','厨房用具',6800,5000,'2009-01-15');
--省略列清单
INSERT INTO ProductIns VALUES ('0005','高压锅','厨房用具',6800,5000,'2009-01-15');
说明:对表进行全列INSERT时,可以省略表名后的列清单。这时VALUES子句的值会默认按照从左到右的顺序赋给每一列。
插入NULL
--INSERT语句中想给某一列赋予NULL值时,可以直接在VALUES子句的值清单中写入NULL。
INSERT INTO ProductIns (product_id,product_name,product_type,sale_price,purchase_price,regist_date) VALUES ('0005','高压锅','厨房用具',6800,NULL,'2009-01-15');
插入默认值
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));
--通过显示方式设定默认值
INSERT INTO ProductIns (product_id,product_name,product_type,sale_price,purchase_price,regist_date) VALUES ('0005','高压锅','厨房用具',DEFAULT,5000,'2009-01-15');
--通过隐式方式设定默认值
INSERT INTO ProductIns (product_id,product_name,product_type,purchase_price,regist_date) VALUES ('0005','高压锅','厨房用具',5000,'2009-01-15');
说明:如果省略了没有设定默认值的列,该列的值就会被设定为NULL。
从其他表中复制数据
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 (product_id,product_name,product_type,sale_price,purchase_price,regist_date)
SELECT product_id,product_name,product_type,sale_price,purchase_price,regist_date FROM Product;
CREATE TABLE ProductType
( product_type VARCHAR(32) NOT NULL,
sum_sale_price INTEGER,
sum_purchase_price INTEGER,
PRIMARY KEY (product_type));
INSERT INTO ProductType (product_type,sum_sale_price,sum_purchase_price)
SELECT product_type,SUM(sale_price),SUM(purchase_price) FROM Product
GROUP BY product_type;
说明:INSERT语句的SELECT语句中,可以使用WHERE子句或者GROUP BY子句等任何SQL语法(但使用ORDER BY子句并不会产生任何效果。)
数据的删除
--删除表
DROP TABLE table_name;
DELETE FROM table_name WHERE xxx;
TRUNCATE table_nmae;
数据的更新
--方法1
UPDATE Prodcut
SET <列名>=<表达式>,<列名>=<表达式>,<列名>=<表达式>
WHERE <条件>;
--方法2
UPDATE Prodcut
SET (<列名>,<列名>,<列名>)=(<表达式>,<表达式>,<表达式>)
WHERE <条件>;
事务
--COMMIT提交处理
--SQL Server PostgreSQL
BEGIN 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;
--MySQL
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;
--Oracle DB2
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取消处理
BEGIN 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;
创建视图的方法
CREATE VIEW ProductSum (product_type,cnt_product)
AS
SELECT product_type,COUNT(*)
FROM Product
GROUP BY product_type;
说明:多重视图会降低SQL的性能。应该避免在视图的基础上创建视图。定义视图时不要使用ORDER BY子句。视图和表需要同时进行更新,因此通过汇总得到的视图无法进行更新。
--能够更新视图的情况
CREATE VIEW ProductJim(product_id,product_name,product_type,sale_price,purchase_price,regist_date)
AS
SELECT * FROM Product
WHERE product_type='办公用品';
INSERT INTO ProductJim VALUES ('0009','印章','办公用品',0=95,10,'2009-11-30');
删除视图
DROP VIEW ProductSum;
子查询
--在FROM子句中直接书写定义视图的SELECT =语句
SELECT product_type,cnt_product FROM (SELECT product_type,COUNT(*) FROM Product GROUP BY product_type) AS ProductSum;
说明:子查询作为内层查询会首先执行
标量子查询
SELECT product_id,product_name,sale_price FROM Product
WHERE sale_price > (SELECT AVG(sale_price) FROM Product);
说明:标量子查询就是返回单一值的子查询。
--由于不是标量子查询,因此不能在SELECT子句中使用
SELECT product_id,product_name,sale_price,(SELECT AVG(sale_price) FROM Product GROUP BY product_type) AS avg_price
FROM Product;
关联子查询
SELECT product_id,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);
+--------------+--------------+------------+
| product_type | product_name | sale_price |
+--------------+--------------+------------+
| 办公用品 | 打孔器 | 500 |
| 衣服 | 运动T恤 | 4000 |
| 厨房用具 | 菜刀 | 3000 |
| 厨房用具 | 高压锅 | 6800 |
+--------------+--------------+------------+
算术函数
ABS(数值)
MOD(被除数,除数)
ROUND(对象数值,保留小数的位数)
字符串函数
--Oracle DB2 PostgreSQL
||拼接函数
--MySQL
CONCAT()
--SQL Server
+拼接函数
--字符串长度
LENGTH(字符串)
--小写转换
LOWER(字符串)
--字符串的替换
REPLACE(对象字符串,替换前的字符串,替换后的字符串)
--字符串的截取
SUBSTRING(对象字符串 FROM 截取的起始位置 FOR 截取的字符数) --PostgreSQL/MySQL专用语法
SUBSTRING(对象字符串,截取的起始位置,截取的字符数)--SQL Server专用语法
SUBSTR(对象字符串,截取的起始位置,截取的字符数)--Oracle专用语法
--大写转换
UPPER()
日期函数
--CURRENT_DATE函数
--PostgreSQL/MySQL
SELECT CURRENT_DATE;
+--------------+
| current_date |
+--------------+
| 2024-06-12 |
+--------------+
--SQL Server
SELECT CAST(CURRENT_TIMESTAMP AS DATE) AS CUR_DATE;
--Oracle
SELECT CURRENT_DATE FROM dual;
--DB2
SELECT CURRENT_DATE FROM SYSIBM.SYSDUMMY1;
--CURRENT_TIME函数
--PostgreSQL/MySQL
SELECT CURRENT_TIME;
+--------------+
| current_time |
+--------------+
| 09:04:03 |
+--------------+
--SQL Server
SELECT CAST(CURRENT_TIMESTAMP AS TIME) AS CUR_TIME;
--Oracle
SELECT CURRENT_TIMESTAMP FROM dual;
--DB2
SELECT CURRENT TIME FROM SYSIBM.SYSDUMMY1;
--CURRENT_TIMESTAMP函数
--SQL Server/PostgreSQL/MySQL
SELECT CURRENT_TIMESTAMP;
+---------------------+
| current_timestamp |
+---------------------+
| 2024-06-12 09:11:06 |
+---------------------+
--Oracle
SELECT CURRENT_TIMESTAMP FROM dual;
--DB2
SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1;
--EXTRACT截取日期函数
EXTRACT(日期元素 FROM 日期)
--PostgreSQL/MySQL
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;
+---------------------+------+-------+------+------+--------+--------+
| CURRENT_TIMESTAMP | year | month | day | hour | minute | second |
+---------------------+------+-------+------+------+--------+--------+
| 2024-06-12 09:19:56 | 2024 | 6 | 12 | 9 | 19 | 56 |
+---------------------+------+-------+------+------+--------+--------+
--SQL Server
SELECT CURRENT_TIMESTAMP,
DATEPART(YEAR FROM CURRENT_TIMESTAMP) AS year,
DATEPART(MONTH FROM CURRENT_TIMESTAMP) AS month,
DATEPART(DAY FROM CURRENT_TIMESTAMP) AS day,
DATEPART(HOUR FROM CURRENT_TIMESTAMP) AS hour,
DATEPART(MINUTE FROM CURRENT_TIMESTAMP) AS minute,
DATEPART(SECOND FROM CURRENT_TIMESTAMP) AS second;
--Oracle
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
FROM DUAL;
--DB2
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
FROM SYSIBM.SYSDUMMY1;
转换函数
--CAST(转换前的值 AS 想要转换的数据类型)
--SQL Server/PostgreSQL
SELECT CAST('0001' AS INTEGER) AS int_col;
+---------+
| int_col |
+---------+
| 1 |
+---------+
--MySQL
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;
--Oracle
SELECT CAST('0001' AS INTEGER) AS int_col FROM DUAL;
--DB2
SELECT CAST('0001' AS INTEGER) AS int_col
FROM SYSIBM.SYSDUMMY1;
--SQL Server/PostgreSQL/MySQL
SELECT CAST('2009-12-14' AS DATE) AS date_col;
+------------+
| date_col |
+------------+
| 2009-12-14 |
+------------+
--Oracle
SELECT CAST('2009-12-14' AS DATE) AS date_col FROM DUAL;
--DB2
SELECT CAST('2009-12-14' AS DATE) AS date_col
FROM SYSIBM.SYSDUMMY1;
--COALESCE函数
--COALESCE(数据1,数据2,数据3......)
--SQL Server/PostgreSQL/MySQL
SELECT COALESCE(NULL,1) AS col_1,
COALESCE(NULL,'test',NULL) AS col_2,
COALESCE(NULL,NULL,'2009-11-01') AS col_3;
+-------+-------+------------+
| col_1 | col_2 | col_3 |
+-------+-------+------------+
| 1 | test | 2009-11-01 |
+-------+-------+------------+
--Oracle
SELECT COALESCE(NULL,1) AS col_1,
COALESCE(NULL,'test',NULL) AS col_2,
COALESCE(NULL,NULL,'2009-11-01') AS col_3
FROM DUAL;
--DB2
SELECT COALESCE(NULL,1) AS col_1,
COALESCE(NULL,'test',NULL) AS col_2,
COALESCE(NULL,NULL,'2009-11-01') AS col_3
FROM SYSIBM.SYSDUMMY1;
谓词
CREATE TABLE SampleLike
( strcol VARCHAR(6) NOT NULL,
PRIMARY KEY(strcol));
START TRANSACTION;
INSERT INTO SampleLike VALUES ('abcddd'),('dddabc'),('abdddc'),('abcdd'),('ddabc'),('abddc');
COMMIT;
--选取前方一致
SELECT * FROM SampleLike
WHERE strcol LIKE 'ddd%';
+--------+
| strcol |
+--------+
| dddabc |
+--------+
--选取中间一致
SELECT * FROM SampleLike
WHERE strcol LIKE '%ddd%';
+--------+
| strcol |
+--------+
| abcddd |
| abdddc |
| dddabc |
+--------+
--选取后方一致
SELECT * FROM SampleLike
WHERE strcol LIKE '%ddd';
+--------+
| strcol |
+--------+
| abcddd |
+--------+
--使用_代替%表示任意一个字符
SELECT * FROM SampleLike
WHERE strcol LIKE 'abc__';
+--------+
| strcol |
+--------+
| abcdd |
+--------+
SELECT * FROM SampleLike
WHERE strcol LIKE 'abc___';
+--------+
| strcol |
+--------+
| abcddd |
+--------+
--BETWEEN谓词——范围查询
SELECT product_name,sale_price FROM Product
WHERE sale_price BETWEEN 100 AND 1000;
--IS NULL、IS NOT NULL——判断是否为NULL
SELECT product_name,purchase_price
FROM Product
WHERE purchase_price IS NULL;
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| 叉子 | NULL |
| 圆珠笔 | NULL |
+--------------+----------------+
SELECT product_name,purchase_price
FROM Product
WHERE purchase_price IS NOT NULL;
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| T恤衫 | 500 |
| 打孔器 | 320 |
| 运动T恤 | 2800 |
| 菜刀 | 2800 |
| 高压锅 | 5000 |
| 擦菜板 | 790 |
+--------------+----------------+
--IN谓词——OR的简便用法
SELECT product_name,purchase_price
FROM Product
WHERE purchase_price IN (320,500,5000);
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| T恤衫 | 500 |
| 打孔器 | 320 |
| 高压锅 | 5000 |
+--------------+----------------+
SELECT product_name,purchase_price
FROM Product
WHERE purchase_price NOT IN (320,500,5000);
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| 运动T恤 | 2800 |
| 菜刀 | 2800 |
| 擦菜板 | 790 |
+--------------+----------------+
说明:使用IN和NOT IN时是无法选取出NULL数据的。
--使用子查询作为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 TANSACTION;
INSERT INTO ShopProduct VALUES ('000A','东京','0001',30);
INSERT INTO ShopProduct VALUES ('000A','东京','0002',50);
INSERT INTO ShopProduct VALUES ('000A','东京','0003',15);
INSERT INTO ShopProduct VALUES ('000B','名古屋','0002',30);
INSERT INTO ShopProduct VALUES ('000B','名古屋','0003',120);
INSERT INTO ShopProduct VALUES ('000B','名古屋','0004',20);
INSERT INTO ShopProduct VALUES ('000B','名古屋','0006',10);
INSERT INTO ShopProduct VALUES ('000B','名古屋','0007',40);
INSERT INTO ShopProduct VALUES ('000C','大阪','0003',20);
INSERT INTO ShopProduct VALUES ('000C','大阪','0004',50);
INSERT INTO ShopProduct VALUES ('000C','大阪','0006',90);
INSERT INTO ShopProduct VALUES ('000C','大阪','0007',70);
INSERT INTO ShopProduct VALUES ('000D','福冈','0001',100);
COMMIT;
--取得在大阪店销售的商品的销售单价
SELECT product_name,sale_price FROM Product WHERE product_id IN (SELECT product_id FROM ShopProduct WHERE shop_id='000C');
+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| 运动T恤 | 4000 |
| 菜刀 | 3000 |
| 叉子 | 500 |
| 擦菜板 | 880 |
+--------------+------------+
SELECT product_name,sale_price FROM Product
WHERE product_id NOT IN (SELECT product_id FROM ShopProduct WHERE shop_id='000A');
+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| 菜刀 | 3000 |
| 高压锅 | 6800 |
| 叉子 | 500 |
| 擦菜板 | 880 |
| 圆珠笔 | 100 |
+--------------+------------+
--EXISTS谓词
SELECT product_name,sale_price FROM Product AS P WHERE EXISTS (SELECT * FROM ShopProduct AS SP WHERE SP.shop_id='000C' AND SP.product_id=P.product_id);
+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| 运动T恤 | 4000 |
| 菜刀 | 3000 |
| 叉子 | 500 |
| 擦菜板 | 880 |
+--------------+------------+
SELECT product_name,sale_price FROM Product AS P WHERE NOT EXISTS (SELECT * FROM ShopProduct AS SP WHERE SP.shop_id='000A' AND SP.product_id=P.product_id);
+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| 菜刀 | 3000 |
| 高压锅 | 6800 |
| 叉子 | 500 |
| 擦菜板 | 880 |
| 圆珠笔 | 100 |
+--------------+------------+
--CASE表达式
SELECT product_name,
CASE WHEN product_type ='衣服'
THEN 'A:'||product_type
WHEN product_type='办公用品'
THEN 'B:'||product_type
WHEN product_type='厨房用具'
THEN 'C:'||product_type
ELSE NULL
END AS abc_product_type
FROM Product;
--简化CASE表达式
SELECT product_name,
CASE product_type
WHEN '衣服'
THEN 'A:'||product_type
WHEN '办公用品'
THEN 'B:'||product_type
WHEN '厨房用具'
THEN 'C:'||product_type
ELSE NULL
END AS abc_product_type
FROM Product;
--DECODE函数
SELECT product_name,DECODE(product_type,'衣服','A:'||product_type,
'办公用品','B:'||product_type,'厨房用具','C:'||product_type,NULL) AS abc_prioduct_type
FROM Product;
+--------------+------------------+
| product_name | abc_product_type |
+--------------+------------------+
| T恤衫 | A:衣服 |
| 打孔器 | B:办公用品 |
| 运动T恤 | A:衣服 |
| 菜刀 | C:厨房用具 |
| 高压锅 | C:厨房用具 |
| 叉子 | C:厨房用具 |
| 擦菜板 | C:厨房用具 |
| 圆珠笔 | B:办公用品 |
+--------------+------------------+
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;
+-------------------+-------------------+------------------+
| sum_price_clothes | sum_price_kitchen | sum_price_office |
+-------------------+-------------------+------------------+
| 5000 | 11180 | 600 |
+-------------------+-------------------+------------------+
表的加法——UNION
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,'2009-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;
select product_id,product_name from product union select product_id,product_name from product2;
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 0001 | T恤衫 |
| 0002 | 打孔器 |
| 0003 | 运动T恤 |
| 0004 | 菜刀 |
| 0005 | 高压锅 |
| 0006 | 叉子 |
| 0007 | 擦菜板 |
| 0008 | 圆珠笔 |
| 0009 | 手套 |
| 0010 | 水壶 |
+------------+--------------+
说明:集合运算符会除去重复的记录。
UNION ALL——包含重复行
select product_id,product_name from product union all select product_id,product_name from product2;
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 0001 | T恤衫 |
| 0002 | 打孔器 |
| 0003 | 运动T恤 |
| 0004 | 菜刀 |
| 0005 | 高压锅 |
| 0006 | 叉子 |
| 0007 | 擦菜板 |
| 0008 | 圆珠笔 |
| 0001 | T恤衫 |
| 0002 | 打孔器 |
| 0003 | 运动T恤 |
| 0009 | 手套 |
| 0010 | 水壶 |
+------------+--------------+
INTERSECT——选取表中公共部分
select product_id,product_name from product intersect select product_id,product_name from product2 order by product_id;
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 0001 | T恤衫 |
| 0002 | 打孔器 |
| 0003 | 运动T恤 |
+------------+--------------+
EXCEPT——记录的减法
--SQL Server/DB2/PostgreSQL
select product_id,product_name from product except select product_id,product_name from product2 order by product_id;
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 0004 | 菜刀 |
| 0005 | 高压锅 |
| 0006 | 叉子 |
| 0007 | 擦菜板 |
| 0008 | 圆珠笔 |
+------------+--------------+
--Oracle
select product_id,product_name from product MINUS select product_id,product_name from product2 order by product_id;
select product_id,product_name from product2 except select product_id,product_name from product order by product_id;
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 0009 | 手套 |
| 0010 | 水壶 |
+------------+--------------+
内联结——INNER JOIN
select sp.shop_id,sp.shop_name,sp.product_id,p.product_name,p.sale_price
from shopproduct as sp inner join product as p
on sp.product_id = p.product_id;
+---------+-----------+------------+--------------+------------+
| shop_id | shop_name | product_id | product_name | sale_price |
+---------+-----------+------------+--------------+------------+
| 000A | 东京 | 0001 | T恤衫 | 1000 |
| 000A | 东京 | 0002 | 打孔器 | 500 |
| 000A | 东京 | 0003 | 运动T恤 | 4000 |
| 000B | 名古屋 | 0002 | 打孔器 | 500 |
| 000B | 名古屋 | 0003 | 运动T恤 | 4000 |
| 000B | 名古屋 | 0004 | 菜刀 | 3000 |
| 000B | 名古屋 | 0006 | 叉子 | 500 |
| 000B | 名古屋 | 0007 | 擦菜板 | 880 |
| 000C | 大阪 | 0003 | 运动T恤 | 4000 |
| 000C | 大阪 | 0004 | 菜刀 | 3000 |
| 000C | 大阪 | 0006 | 叉子 | 500 |
| 000C | 大阪 | 0007 | 擦菜板 | 880 |
| 000D | 福冈 | 0001 | T恤衫 | 1000 |
+---------+-----------+------------+--------------+------------+
select sp.shop_id,sp.shop_name,sp.product_id,p.product_name,p.sale_price
from shopproduct as sp inner join product as p
on sp.product_id = p.product_id where sp.shop_id='000A';
+---------+-----------+------------+--------------+------------+
| shop_id | shop_name | product_id | product_name | sale_price |
+---------+-----------+------------+--------------+------------+
| 000A | 东京 | 0001 | T恤衫 | 1000 |
| 000A | 东京 | 0002 | 打孔器 | 500 |
| 000A | 东京 | 0003 | 运动T恤 | 4000 |
+---------+-----------+------------+--------------+------------+
外联结——OUTER JOIN
select sp.shop_id,sp.shop_name,p.product_id,p.product_name,p.sale_price
from shopproduct as sp right outer join product as p
on sp.product_id = p.product_id;
+---------+-----------+------------+--------------+------------+
| shop_id | shop_name | product_id | product_name | sale_price |
+---------+-----------+------------+--------------+------------+
| 000D | 福冈 | 0001 | T恤衫 | 1000 |
| 000A | 东京 | 0001 | T恤衫 | 1000 |
| 000B | 名古屋 | 0002 | 打孔器 | 500 |
| 000A | 东京 | 0002 | 打孔器 | 500 |
| 000C | 大阪 | 0003 | 运动T恤 | 4000 |
| 000B | 名古屋 | 0003 | 运动T恤 | 4000 |
| 000A | 东京 | 0003 | 运动T恤 | 4000 |
| 000C | 大阪 | 0004 | 菜刀 | 3000 |
| 000B | 名古屋 | 0004 | 菜刀 | 3000 |
| NULL | NULL | 0005 | 高压锅 | 6800 |
| 000C | 大阪 | 0006 | 叉子 | 500 |
| 000B | 名古屋 | 0006 | 叉子 | 500 |
| 000C | 大阪 | 0007 | 擦菜板 | 880 |
| 000B | 名古屋 | 0007 | 擦菜板 | 880 |
| NULL | NULL | 0008 | 圆珠笔 | 100 |
+---------+-----------+------------+--------------+------------+
select sp.shop_id,sp.shop_name,p.product_id,p.product_name,p.sale_price
from shopproduct as sp left outer join product as p
on sp.product_id = p.product_id;
+---------+-----------+------------+--------------+------------+
| shop_id | shop_name | product_id | product_name | sale_price |
+---------+-----------+------------+--------------+------------+
| 000A | 东京 | 0001 | T恤衫 | 1000 |
| 000A | 东京 | 0002 | 打孔器 | 500 |
| 000A | 东京 | 0003 | 运动T恤 | 4000 |
| 000B | 名古屋 | 0002 | 打孔器 | 500 |
| 000B | 名古屋 | 0003 | 运动T恤 | 4000 |
| 000B | 名古屋 | 0004 | 菜刀 | 3000 |
| 000B | 名古屋 | 0006 | 叉子 | 500 |
| 000B | 名古屋 | 0007 | 擦菜板 | 880 |
| 000C | 大阪 | 0003 | 运动T恤 | 4000 |
| 000C | 大阪 | 0004 | 菜刀 | 3000 |
| 000C | 大阪 | 0006 | 叉子 | 500 |
| 000C | 大阪 | 0007 | 擦菜板 | 880 |
| 000D | 福冈 | 0001 | T恤衫 | 1000 |
+---------+-----------+------------+--------------+------------+
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 values ('P001','0001',0),('P001','0002',120),('P001','0003',200),('P001','0004',3),('P001','0005',0),('P001','0006',99),('P001','0007',999),('P001','0008',200);
insert into inventoryproduct values ('P002','0001',10),('P002','0002',25),('P002','0003',34),('P002','0004',19),('P002','0005',99),('P002','0006',0),('P002','0007',0),('P002','0008',18);
commit;
select sp.shop_id,sp.shop_name,sp.product_id,p.product_name,p.sale_price,ip.inventory_quantity
from shopproduct as sp inner join product as p
on sp.product_id = p.product_id
inner join inventoryproduct as ip
on sp.product_id = ip.product_id
where ip.inventory_id = 'P001';
+---------+-----------+------------+--------------+------------+--------------------+
| shop_id | shop_name | product_id | product_name | sale_price | inventory_quantity |
+---------+-----------+------------+--------------+------------+--------------------+
| 000A | 东京 | 0001 | T恤衫 | 1000 | 0 |
| 000A | 东京 | 0002 | 打孔器 | 500 | 120 |
| 000A | 东京 | 0003 | 运动T恤 | 4000 | 200 |
| 000B | 名古屋 | 0002 | 打孔器 | 500 | 120 |
| 000B | 名古屋 | 0003 | 运动T恤 | 4000 | 200 |
| 000B | 名古屋 | 0004 | 菜刀 | 3000 | 3 |
| 000B | 名古屋 | 0006 | 叉子 | 500 | 99 |
| 000B | 名古屋 | 0007 | 擦菜板 | 880 | 999 |
| 000C | 大阪 | 0003 | 运动T恤 | 4000 | 200 |
| 000C | 大阪 | 0004 | 菜刀 | 3000 | 3 |
| 000C | 大阪 | 0006 | 叉子 | 500 | 99 |
| 000C | 大阪 | 0007 | 擦菜板 | 880 | 999 |
| 000D | 福冈 | 0001 | T恤衫 | 1000 | 0 |
+---------+-----------+------------+--------------+------------+--------------------+
交叉联结——CROSS JOIN
select sp.shop_id,sp.shop_name,sp.product_id,p.product_name from shopproduct as sp cross join product as p;
+---------+-----------+------------+--------------+
| shop_id | shop_name | product_id | product_name |
+---------+-----------+------------+--------------+
| 000A | 东京 | 0001 | 圆珠笔 |
| 000A | 东京 | 0001 | 擦菜板 |
| 000A | 东京 | 0001 | 叉子 |
| 000A | 东京 | 0001 | 高压锅 |
| 000A | 东京 | 0001 | 菜刀 |
| 000A | 东京 | 0001 | 运动T恤 |
| 000A | 东京 | 0001 | 打孔器 |
| 000A | 东京 | 0001 | T恤衫 |
| 000A | 东京 | 0002 | 圆珠笔 |
| 000A | 东京 | 0002 | 擦菜板 |
| 000A | 东京 | 0002 | 叉子 |
| 000A | 东京 | 0002 | 高压锅 |
| 000A | 东京 | 0002 | 菜刀 |
| 000A | 东京 | 0002 | 运动T恤 |
| 000A | 东京 | 0002 | 打孔器 |
| 000A | 东京 | 0002 | T恤衫 |
| 000A | 东京 | 0003 | 圆珠笔 |
| 000A | 东京 | 0003 | 擦菜板 |
| 000A | 东京 | 0003 | 叉子 |
| 000A | 东京 | 0003 | 高压锅 |
| 000A | 东京 | 0003 | 菜刀 |
| 000A | 东京 | 0003 | 运动T恤 |
| 000A | 东京 | 0003 | 打孔器 |
| 000A | 东京 | 0003 | T恤衫 |
| 000B | 名古屋 | 0002 | 圆珠笔 |
| 000B | 名古屋 | 0002 | 擦菜板 |
| 000B | 名古屋 | 0002 | 叉子 |
| 000B | 名古屋 | 0002 | 高压锅 |
| 000B | 名古屋 | 0002 | 菜刀 |
| 000B | 名古屋 | 0002 | 运动T恤 |
| 000B | 名古屋 | 0002 | 打孔器 |
| 000B | 名古屋 | 0002 | T恤衫 |
| 000B | 名古屋 | 0003 | 圆珠笔 |
| 000B | 名古屋 | 0003 | 擦菜板 |
| 000B | 名古屋 | 0003 | 叉子 |
| 000B | 名古屋 | 0003 | 高压锅 |
| 000B | 名古屋 | 0003 | 菜刀 |
| 000B | 名古屋 | 0003 | 运动T恤 |
| 000B | 名古屋 | 0003 | 打孔器 |
| 000B | 名古屋 | 0003 | T恤衫 |
| 000B | 名古屋 | 0004 | 圆珠笔 |
| 000B | 名古屋 | 0004 | 擦菜板 |
| 000B | 名古屋 | 0004 | 叉子 |
| 000B | 名古屋 | 0004 | 高压锅 |
| 000B | 名古屋 | 0004 | 菜刀 |
| 000B | 名古屋 | 0004 | 运动T恤 |
| 000B | 名古屋 | 0004 | 打孔器 |
| 000B | 名古屋 | 0004 | T恤衫 |
| 000B | 名古屋 | 0006 | 圆珠笔 |
| 000B | 名古屋 | 0006 | 擦菜板 |
| 000B | 名古屋 | 0006 | 叉子 |
| 000B | 名古屋 | 0006 | 高压锅 |
| 000B | 名古屋 | 0006 | 菜刀 |
| 000B | 名古屋 | 0006 | 运动T恤 |
| 000B | 名古屋 | 0006 | 打孔器 |
| 000B | 名古屋 | 0006 | T恤衫 |
| 000B | 名古屋 | 0007 | 圆珠笔 |
| 000B | 名古屋 | 0007 | 擦菜板 |
| 000B | 名古屋 | 0007 | 叉子 |
| 000B | 名古屋 | 0007 | 高压锅 |
| 000B | 名古屋 | 0007 | 菜刀 |
| 000B | 名古屋 | 0007 | 运动T恤 |
| 000B | 名古屋 | 0007 | 打孔器 |
| 000B | 名古屋 | 0007 | T恤衫 |
| 000C | 大阪 | 0003 | 圆珠笔 |
| 000C | 大阪 | 0003 | 擦菜板 |
| 000C | 大阪 | 0003 | 叉子 |
| 000C | 大阪 | 0003 | 高压锅 |
| 000C | 大阪 | 0003 | 菜刀 |
| 000C | 大阪 | 0003 | 运动T恤 |
| 000C | 大阪 | 0003 | 打孔器 |
| 000C | 大阪 | 0003 | T恤衫 |
| 000C | 大阪 | 0004 | 圆珠笔 |
| 000C | 大阪 | 0004 | 擦菜板 |
| 000C | 大阪 | 0004 | 叉子 |
| 000C | 大阪 | 0004 | 高压锅 |
| 000C | 大阪 | 0004 | 菜刀 |
| 000C | 大阪 | 0004 | 运动T恤 |
| 000C | 大阪 | 0004 | 打孔器 |
| 000C | 大阪 | 0004 | T恤衫 |
| 000C | 大阪 | 0006 | 圆珠笔 |
| 000C | 大阪 | 0006 | 擦菜板 |
| 000C | 大阪 | 0006 | 叉子 |
| 000C | 大阪 | 0006 | 高压锅 |
| 000C | 大阪 | 0006 | 菜刀 |
| 000C | 大阪 | 0006 | 运动T恤 |
| 000C | 大阪 | 0006 | 打孔器 |
| 000C | 大阪 | 0006 | T恤衫 |
| 000C | 大阪 | 0007 | 圆珠笔 |
| 000C | 大阪 | 0007 | 擦菜板 |
| 000C | 大阪 | 0007 | 叉子 |
| 000C | 大阪 | 0007 | 高压锅 |
| 000C | 大阪 | 0007 | 菜刀 |
| 000C | 大阪 | 0007 | 运动T恤 |
| 000C | 大阪 | 0007 | 打孔器 |
| 000C | 大阪 | 0007 | T恤衫 |
| 000D | 福冈 | 0001 | 圆珠笔 |
| 000D | 福冈 | 0001 | 擦菜板 |
| 000D | 福冈 | 0001 | 叉子 |
| 000D | 福冈 | 0001 | 高压锅 |
| 000D | 福冈 | 0001 | 菜刀 |
| 000D | 福冈 | 0001 | 运动T恤 |
| 000D | 福冈 | 0001 | 打孔器 |
| 000D | 福冈 | 0001 | T恤衫 |
+---------+-----------+------------+--------------+
窗口函数
select product_name,product_type,sale_price,rank() over(partition by product_type order by sale_price) as ranking
from product;
+--------------+--------------+------------+---------+
| product_name | product_type | sale_price | ranking |
+--------------+--------------+------------+---------+
| 圆珠笔 | 办公用品 | 100 | 1 |
| 打孔器 | 办公用品 | 500 | 2 |
| 叉子 | 厨房用具 | 500 | 1 |
| 擦菜板 | 厨房用具 | 880 | 2 |
| 菜刀 | 厨房用具 | 3000 | 3 |
| 高压锅 | 厨房用具 | 6800 | 4 |
| T恤衫 | 衣服 | 1000 | 1 |
| 运动T恤 | 衣服 | 4000 | 2 |
+--------------+--------------+------------+---------+
select product_name,product_type,sale_price,rank() over(order by sale_price) as ranking
from product;
+--------------+--------------+------------+---------+
| product_name | product_type | sale_price | ranking |
+--------------+--------------+------------+---------+
| 圆珠笔 | 办公用品 | 100 | 1 |
| 打孔器 | 办公用品 | 500 | 2 |
| 叉子 | 厨房用具 | 500 | 2 |
| 擦菜板 | 厨房用具 | 880 | 4 |
| T恤衫 | 衣服 | 1000 | 5 |
| 菜刀 | 厨房用具 | 3000 | 6 |
| 运动T恤 | 衣服 | 4000 | 7 |
| 高压锅 | 厨房用具 | 6800 | 8 |
+--------------+--------------+------------+---------+
select product_name,product_type,sale_price,rank() over(order by sale_price) as ranking ,
dense_rank() over (order by sale_price) as dense_ranking,
row_number() over(order by sale_price) as row_num
from product;
+--------------+--------------+------------+---------+---------------+---------+
| product_name | product_type | sale_price | ranking | dense_ranking | row_num |
+--------------+--------------+------------+---------+---------------+---------+
| 圆珠笔 | 办公用品 | 100 | 1 | 1 | 1 |
| 打孔器 | 办公用品 | 500 | 2 | 2 | 2 |
| 叉子 | 厨房用具 | 500 | 2 | 2 | 3 |
| 擦菜板 | 厨房用具 | 880 | 4 | 3 | 4 |
| T恤衫 | 衣服 | 1000 | 5 | 4 | 5 |
| 菜刀 | 厨房用具 | 3000 | 6 | 5 | 6 |
| 运动T恤 | 衣服 | 4000 | 7 | 6 | 7 |
| 高压锅 | 厨房用具 | 6800 | 8 | 7 | 8 |
+--------------+--------------+------------+---------+---------------+---------+
--聚合函数作为窗口函数
select product_id,product_name,sale_price,sum(sale_price) over (order by product_id) as current_sum from product;
+------------+--------------+------------+-------------+
| product_id | product_name | sale_price | current_sum |
+------------+--------------+------------+-------------+
| 0001 | T恤衫 | 1000 | 1000 |
| 0002 | 打孔器 | 500 | 1500 |
| 0003 | 运动T恤 | 4000 | 5500 |
| 0004 | 菜刀 | 3000 | 8500 |
| 0005 | 高压锅 | 6800 | 15300 |
| 0006 | 叉子 | 500 | 15800 |
| 0007 | 擦菜板 | 880 | 16680 |
| 0008 | 圆珠笔 | 100 | 16780 |
+------------+--------------+------------+-------------+
select product_id,product_name,sale_price,avg(sale_price) over (order by product_id) as current_avg from product;
+------------+--------------+------------+-------------+
| product_id | product_name | sale_price | current_avg |
+------------+--------------+------------+-------------+
| 0001 | T恤衫 | 1000 | 1000.0000 |
| 0002 | 打孔器 | 500 | 750.0000 |
| 0003 | 运动T恤 | 4000 | 1833.3333 |
| 0004 | 菜刀 | 3000 | 2125.0000 |
| 0005 | 高压锅 | 6800 | 3060.0000 |
| 0006 | 叉子 | 500 | 2633.3333 |
| 0007 | 擦菜板 | 880 | 2382.8571 |
| 0008 | 圆珠笔 | 100 | 2097.5000 |
+------------+--------------+------------+-------------+
select product_id,product_name,sale_price,avg(sale_price) over (order by product_id rows 2 preceding) as moving_avg from product;
+------------+--------------+------------+------------+
| product_id | product_name | sale_price | moving_avg |
+------------+--------------+------------+------------+
| 0001 | T恤衫 | 1000 | 1000.0000 |
| 0002 | 打孔器 | 500 | 750.0000 |
| 0003 | 运动T恤 | 4000 | 1833.3333 |
| 0004 | 菜刀 | 3000 | 2500.0000 |
| 0005 | 高压锅 | 6800 | 4600.0000 |
| 0006 | 叉子 | 500 | 3433.3333 |
| 0007 | 擦菜板 | 880 | 2726.6667 |
| 0008 | 圆珠笔 | 100 | 493.3333 |
+------------+--------------+------------+------------+
select product_id,product_name,sale_price,avg(sale_price) over (order by product_id rows between 1 preceding and 1 following) as moving_avg from product;
+------------+--------------+------------+------------+
| product_id | product_name | sale_price | moving_avg |
+------------+--------------+------------+------------+
| 0001 | T恤衫 | 1000 | 750.0000 |
| 0002 | 打孔器 | 500 | 1833.3333 |
| 0003 | 运动T恤 | 4000 | 2500.0000 |
| 0004 | 菜刀 | 3000 | 4600.0000 |
| 0005 | 高压锅 | 6800 | 3433.3333 |
| 0006 | 叉子 | 500 | 2726.6667 |
| 0007 | 擦菜板 | 880 | 493.3333 |
| 0008 | 圆珠笔 | 100 | 490.0000 |
+------------+--------------+------------+------------+
GROUPING运算符
--Oracle/SQL Server/DB2/PostgreSQL
select product_type,sum(sale_price) as sum_price
from product
group by rollup(product_type);
+--------------+-----------+
| product_type | sum_price |
+--------------+-----------+
| 办公用品 | 600 |
| 厨房用具 | 11180 |
| 衣服 | 5000 |
| NULL | 16780 |
+--------------+-----------+
--MySQL
select product_type,sum(sale_price) as sum_price
from product
group by product_type with rollup;
select product_type,regist_date,sum(sale_price) as sum_price
from product
group by rollup(product_type,regist_date);
+--------------+-------------+-----------+
| product_type | regist_date | sum_price |
+--------------+-------------+-----------+
| 办公用品 | 2009-09-11 | 500 |
| 办公用品 | 2009-11-11 | 100 |
| 办公用品 | NULL | 600 |
| 厨房用具 | 2008-04-28 | 880 |
| 厨房用具 | 2009-01-15 | 6800 |
| 厨房用具 | 2009-09-20 | 3500 |
| 厨房用具 | NULL | 11180 |
| 衣服 | NULL | 4000 |
| 衣服 | 2009-09-20 | 1000 |
| 衣服 | NULL | 5000 |
| NULL | NULL | 16780 |
+--------------+-------------+-----------+
select grouping(product_type) as product_type,grouping(regist_date) as regist_date,sum(sale_price) as sum_price
from product
group by rollup(product_type,regist_date);
+--------------+-------------+-----------+
| product_type | regist_date | sum_price |
+--------------+-------------+-----------+
| 0 | 0 | 500 |
| 0 | 0 | 100 |
| 0 | 1 | 600 |
| 0 | 0 | 880 |
| 0 | 0 | 6800 |
| 0 | 0 | 3500 |
| 0 | 1 | 11180 |
| 0 | 0 | 4000 |
| 0 | 0 | 1000 |
| 0 | 1 | 5000 |
| 1 | 1 | 16780 |
+--------------+-------------+-----------+
select case when
grouping(product_type)=1
then '商品种类 合计'
else product_type end as product_type,
case when grouping(regist_date)=1
then '登记日期 合计'
else cast(regist_date as char(16)) end as regist_date,
sum(sale_price) as sum_price
from product
group by rollup(product_type,regist_date);
+---------------------+---------------------+-----------+
| product_type | regist_date | sum_price |
+---------------------+---------------------+-----------+
| 办公用品 | 2009-09-11 | 500 |
| 办公用品 | 2009-11-11 | 100 |
| 办公用品 | 登记日期 合计 | 600 |
| 厨房用具 | 2008-04-28 | 880 |
| 厨房用具 | 2009-01-15 | 6800 |
| 厨房用具 | 2009-09-20 | 3500 |
| 厨房用具 | 登记日期 合计 | 11180 |
| 衣服 | NULL | 4000 |
| 衣服 | 2009-09-20 | 1000 |
| 衣服 | 登记日期 合计 | 5000 |
| 商品种类 合计 | 登记日期 合计 | 16780 |
+---------------------+---------------------+-----------+
select case when
grouping(product_type)=1
then '商品种类 合计'
else product_type end as product_type,
case when grouping(regist_date)=1
then '登记日期 合计'
else cast(regist_date as char(16)) end as regist_date,
sum(sale_price) as sum_price
from product
group by cube(product_type,regist_date);
select case when
grouping(product_type)=1
then '商品种类 合计'
else product_type end as product_type,
case when grouping(regist_date)=1
then '登记日期 合计'
else cast(regist_date as char(16)) end as regist_date,
sum(sale_price) as sum_price
from product
group by grouping sets(product_type,regist_date);