...
MySQL不支持EXCEPT
以列为单位对表进行联结(JOIN)
...
| 代码块 |
|---|
| language | sql |
|---|
| linenumbers | true |
|---|
|
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、GROUP BY、HAVING、ORDER BY 等工具都可以正常使用.
...
| 代码块 |
|---|
| language | sql |
|---|
| linenumbers | true |
|---|
|
SELECT SP.shop_id, SP.shop_name, SP.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; |
...
| 代码块 |
|---|
| language | sql |
|---|
| linenumbers | true |
|---|
|
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'; |
DCL（Data Control Language，数据控制语言）
创建事务(START TRANSACTION) - 提交处理(COMMIT)
| 代码块 |
|---|
| language | sql |
|---|
| linenumbers | true |
|---|
|
START TRANSACTION;
-- 将运动T恤的销售单价降低1000日元
UPDATE Product
SET sale_price = sale_price - 1000
WHERE product_name = '运动T恤';
-- 将T恤衫的销售单价上浮1000日元
UPDATE Product
SET sale_price = sale_price + 1000
WHERE product_name = 'T恤衫';
COMMIT; |
取消处理(ROLLBACK)
| 代码块 |
|---|
| language | sql |
|---|
| linenumbers | true |
|---|
|
START TRANSACTION;
-- 将运动T恤的销售单价降低1000日元
UPDATE Product
SET sale_price = sale_price - 1000
WHERE product_name = '运动T恤';
-- 将T恤衫的销售单价上浮1000日元
UPDATE Product
SET sale_price = sale_price + 1000
WHERE product_name = 'T恤衫';
ROLLBACK; |
