sql基础语句3

1.SELECT Date FROM Store_Information
UNION
SELECT Date FROM Internet_Sales

2.SELECT Date FROM Store_Information
UNION ALL
SELECT Date FROM Internet_Sales

3.SELECT Date FROM Store_Information
INTERSECT
SELECT Date FROM Internet_Sales

4.SELECT Date FROM Store_Information
MINUS
SELECT Date FROM Internet_Sales

5.SELECT SUM(Sales) FROM Store_Information
WHERE Store_name IN
(SELECT store_name FROM Geography
WHERE region_name = ‘West’)

6.SELECT SUM(a1.Sales) FROM Store_Information a1
WHERE a1.Store_name IN
(SELECT store_name FROM Geography a2
WHERE a2.store_name = a1.store_name)

7.SELECT SUM(Sales) FROM Store_Information
WHERE EXISTS
(SELECT * FROM Geography
WHERE region_name = ‘West’)

8.SELECT store_name, CASE store_name
WHEN ‘Los Angeles’ THEN Sales * 2
WHEN ‘San Diego’ THEN Sales * 1.5
ELSE Sales
END
“New Sales”,
Date
FROM Store_Information

9.SELECT a1.Name, a1.Sales, COUNT(a2.sales) Sales_Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.Sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;

10.SELECT Sales Median FROM
(SELECT a1.Name, a1.Sales, COUNT(a1.Sales) Rank
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales < a2.Sales OR (a1.Sales=a2.Sales AND a1.Name <= a2.Name)
group by a1.Name, a1.Sales
order by a1.Sales desc) a3
WHERE Rank = (SELECT (COUNT(*)+1) DIV 2 FROM Total_Sales);

11.SELECT a1.Name, a1.Sales, SUM(a2.Sales) Running_Total
FROM Total_Sales a1, Total_Sales a2WHERE a1.Sales <= a2.sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;

12.SELECT a1.Name, a1.Sales, a1.Sales/(SELECT SUM(Sales) FROM Total_Sales) Pct_To_Total
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;

13.SELECT a1.Name, a1.Sales, SUM(a2.Sales)/(SELECT SUM(Sales) FROM Total_Sales)
Pct_To_Total
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;

SQL语法
1.Select
SELECT “栏位” FROM “表格名”

2.Distinct //无重复
SELECT DISTINCT “栏位”
FROM “表格名”

3.Where //选择性抓取
SELECT “栏位”
FROM “表格名”
WHERE “condition”

4.And/Or
SELECT “栏位”
FROM “表格名”
WHERE “简单条件”
{[AND|OR] “简单条件”}+

5.In //确定的条件
SELECT “栏位”
FROM “表格名”
WHERE “栏位” IN (‘值 1’, ‘值 2’, …)

6.Between
SELECT “栏位”
FROM “表格名”
WHERE “栏位” BETWEEN ‘值 1’ AND ‘值 2’

7.Like //模糊条件，包含
SELECT “栏位”
FROM “表格名”
WHERE “栏位” LIKE {模式}

8.Order By
SELECT “栏位”
FROM “表格名”
[WHERE “条件”]
ORDER BY “栏位” [ASC, DESC]

9.Count
SELECT COUNT(“栏位”)
FROM “表格名”

10.Group By
SELECT “栏位 1”, SUM(“栏位 2”)
FROM “表格名”
GROUP BY “栏位 1”

11.Having //对函数产生值进行设定
SELECT “栏位 1”, SUM(“栏位 2”)
FROM “表格名”
GROUP BY “栏位 1”
HAVING (函数条件)

12.Create Table
CREATE TABLE “表格名”
(“栏位 1” “栏位 1 资料种类”,
“栏位 2” “栏位 2 资料种类”",
… )

13.Drop Table
DROP TABLE “表格名”

14.Truncate Table //清除表格内容
TRUNCATE TABLE “表格名”

15.Insert Into
INSERT INTO “表格名” (“栏位 1”, “栏位 2”, …)
VALUES (“值 1”, “值 2”, …)

16.Update
UPDATE “表格名”
SET “栏位 1” = [新值]
WHERE {条件}

17.Delete From
DELETE FROM “表格名”
WHERE {条件}