store_name | Sales | Date |
---|---|---|
Los Angeles | $1500 | Jan-05-1999 |
San Diego | $250 | Jan-07-1999 |
Los Angeles | $300 | Jan-08-1999 |
Boston | $700 | Jan-09-1999 |
region_name | store_name |
---|---|
East | Boston |
East | New York |
West | Los Angeles |
West | San Diego |
SELECT "栏位名" FROM "表格名"
查询表格中的所有【栏位名】
SELECT store_name FROM Store_Information
SELECT DISTINCT "栏位名" FROM "表格名"
表格中【栏位名的值】不重复数据
SELECT DISTINCT store_name FROM Store_Information
SELECT "栏位名" FROM "表格名" WHERE "条件"
表格中满足 WHERE 条件的栏位值
SELECT store_name
FROM Store_Information
WHERE Sales > 1000
SELECT "栏位名" FROM "表格名" WHERE "简单条件" {[AND|OR] "简单条件"}+
满足复杂 WHERE 条件的栏位值
SELECT store_name
FROM Store_Information
WHERE Sales > 1000
OR (Sales < 500 AND Sales > 275)
查询表格中满足 AND|OR 条件的【栏位名】,用 () 来代表条件的先后次序
SELECT "栏位名" FROM "表格名" WHERE "栏位名" IN ('值一', '值二', ...)
SELECT *
FROM Store_Information
WHERE store_name IN ('Los Angeles', 'San Diego')
查询表格中满足 WHERE 条件并且【事先已知道至少一个我们需要的值】IN 的【栏位名】
SELECT "栏位名" FROM "表格名" WHERE "栏位名" BETWEEN '值一' AND '值二'
查询表格中满足 WHERE【栏位名】 条件 值在 BETWEEN ‘值一’ AND '值二’的【栏位名】
SELECT *
FROM Store_Information
WHERE Date BETWEEN 'Jan-06-1999' AND 'Jan-10-1999'
SELECT "栏位名" FROM "表格名" WHERE "栏位名" LIKE {%模式%}
查询表格中满足 WHERE 条件的【模式】的【栏位名】
SELECT *
FROM Store_Information
WHERE store_name LIKE '%g%'
SELECT "栏位名" FROM "表格名"[WHERE "条件"] ORDER BY "栏位名" [ASC, DESC],"栏位名" [ASC, DESC]
查询表格中满足 WHERE 条件的【栏位名】按照(ASC升序)或者(DESC降序)返回
SELECT store_name, Sales, Date
FROM Store_Information
ORDER BY Sales DESC
1. AVG (平均)
2. COUNT (计数)
3. MAX (最大值)
4. MIN (最小值)
5. SUM (总合)
SELECT "函数名"("栏位名") FROM "表格名"
查询表格中【栏位名】按照函数名
SELECT SUM(Sales) FROM Store_Information
SELECT COUNT(store_name) FROM Store_Information WHERE store_name is not NULL
“is not NULL” 是 “这个栏位不是空白” 的意思。
SELECT COUNT(DISTINCT store_name)
FROM Store_Information
SELECT "栏位 1", SUM("栏位 2") FROM "表格名" GROUP BY "栏位 1"
查询表格中以【栏位 1】为基准计算【栏位 2】
SELECT store_name, SUM(Sales)
FROM Store_Information
GROUP BY store_name
SELECT "栏位 1", SUM("栏位 2") FROM "表格名" GROUP BY "栏位 1" HAVING (函数条件)
查询表格中以【栏位 1】为基准计算【栏位 2】 要满足 HAVING 条件
SELECT store_name, SUM(sales)
FROM Store_Information
GROUP BY store_name
HAVING SUM(sales) > 1500
SELECT "表格别名"."栏位 1" "栏位别名" FROM "表格名" "表格别名"
查询表格中的栏位,同时给表格和栏位重新命名别名
SELECT A1.store_name Store, SUM(A1.Sales) "Total Sales"
FROM Store_Information A1
GROUP BY A1.store_name
SELECT "表格别名1"."栏位 1" "栏位别名1", SUM("表格别名2"."栏位 2") "栏位别名2"
FROM 表格名1 表格别名1, 表格名2 表格别名2
WHERE 表格别名1.栏位别名1 = 表格别名2.栏位别名1
GROUP BY 表格别名1.栏位别名1
将表格中某个栏位相等的数据找出,对表格中栏位重新命名和值再计算
SELECT A1.region_name REGION, SUM(A2.Sales) SALES
FROM Geography A1, Store_Information A2
WHERE A1.store_name = A2.store_name
GROUP BY A1.region_name
在第一行中,我们告诉 SQL 去选出两个栏位:第一个栏位是 Geography 表格中的 region_name 栏位 (我们取了一个别名叫做 REGION);第二个栏位是 Store_Information 表 格中的 sales 栏位 (别名为 SALES)。请注意在这里我们有用到表格别名:Geography 表格 的别名是 A1,Store_Information 表格的别名是 A2。若我们没有用表格别名的话,第一行 SELECT Geography.region_name REGION, SUM(Store_Information.Sales) SALES 很明显地,这就复杂多了。在这里我们可以看到表格别名的功用:它能让 SQL 句容易被了 解,尤其是这个 SQL 句含盖好几个不同的表格时。 接下来我们看第三行,就是 WHERE 子句。这是我们阐述连接条件的地方。在这里,我们 要确认 Geography 表格中 store_name 栏位的值与 Store_Information 表格中 store_name 栏位的值是相等的。这个 WHERE 子句是一个连接的灵魂人物,因为它的角色是确定两个 表格之间的连接是正确的。如果 WHERE 子句是错误的,我们就极可能得到一个笛卡儿连 接 (Cartesian join)。笛卡儿连接会造成我们得到所有两个表格每两行之间所有可能的组合。 在这个例子中,笛卡儿连接会让我们得到 4 x 4 = 16 行的结果。
MySQL: CONCAT()
Oracle: CONCAT(), ||
SQL Server: +
SELECT CONCAT("栏位 1","栏位 2") FROM "表格名"
查询表格中 “栏位 1” 和 “栏位 2” 数据按照一定规则进行拼接。
SELECT CONCAT(region_name,store_name) FROM Geography
WHERE store_name = 'Boston';
MySQL: SUBSTR(), SUBSTRING()
Oracle: SUBSTR()
SQL Server: SUBSTRING()
SELECT SUBSTR(栏位名, 第<pos>位置开始, 接下去的<len>个字符)
FROM 表格名
WHERE 栏位名 = '已知栏位名';
SELECT SUBSTR(store_name, 3)
FROM Geography
WHERE store_name = 'Los Angeles';
SELECT SUBSTR(store_name,2,4)
FROM Geography
WHERE store_name = 'San Diego';
MySQL: TRIM(), RTRIM(), LTRIM()
Oracle: RTRIM(), LTRIM()
SQL Server: RTRIM(), LTRIM()
TRIM([[位置] [要移除的字符串] FROM ] 字符串)
[位置] 的可能值为 LEADING (起头), TRAILING (结尾), or BOTH (起头及结尾)。这个函数将把 [要移除的字符串] 从字符串的起 头、结尾,或是起头及结尾移除。如果我们没有列出 [要移除的字符串] 是什么的话,那空白就会被移除。
SELECT TRIM(' Sample ');
SELECT LTRIM(' Sample ');
SELECT RTRIM(' Sample ');
CREATE TABLE "表格名"
("栏位 1" "栏位 1 资料种类",
"栏位 2" "栏位 2 资料种类",
... )
CREATE TABLE customer2
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date)
[SQL]CREATE TABLE customer2
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date)
我们可以限制哪一些资料可以存入表格中。这些限制可以在表格初创时藉由 CREATE TABLE 语句来指定,或是之后藉由 ALTER TABLE 语句来指定。
NOT NULL
UNIQUE
CHECK
主键 (Primary Key)
外来键 (Foreign Key)
在没有做出任何限制的情况下,一个栏位是允许有 NULL 值得。如果我们不允许一个栏位含有 NULL 值,我们就需要对那个栏位做出 NOT NULL 的指定。
CREATE TABLE Customer
(SID integer NOT NULL,
Last_Name varchar (30) NOT NULL,
First_Name varchar(30));
“SID” 和 “Last_Name” 这两个栏位是不允许有 NULL 值,而 “First_Name” 这个栏位是可以有 NULL 值得。
UNIQUE 限制是保证一个栏位中的所有资料都是有不一样的值。
CREATE TABLE Customer
(SID integer Unique,
Last_Name varchar (30),
First_Name varchar(30));
“SID” 栏位不能有重复值存在,而 “Last_Name” 及 “First_Name” 这两个栏位则是允许有重复值存在。
请注意,一个被指定为主键的栏位也一定会含有 UNIQUE 的特性。相对来说,一个UNIQUE 的栏位并不一定会是一个主键。
CHECK 限制是保证一个栏位中的所有资料都是符合某些条件。
CREATE TABLE Customer
(SID integer CHECK (SID > 0),
Last_Name varchar (30),
First_Name varchar(30));
请注意,CHECK 限制目前尚未被执行于 MySQL 数据库上。
外来键是一个(或数个)指向另外一个表格主键的栏位。外来键的目的是确定资料的参考完整性(referential integrity)。换言之,只有被准许的资料值才会被存入数据库内。
> 举例来说,假设我们有两个表格:一个 CUSTOMER 表格,里面记录了所有顾客的资料;
> 另一个 ORDERS 表格,里面记录了所有顾客订购的资料。在这里的一个限制,就是所有的
> 订购资料中的顾客,都一定是要跟在 CUSTOMER 表格中存在。在这里,我们就会在
> ORDERS 表格中设定一个外来键,而这个外来键是指向 CUSTOMER 表格中的主键。这样
> 一来,我们就可以确定所有在 ORDERS 表格中的顾客都存在 CUSTOMER 表格中。换句
> 话说,ORDERS 表格之中,不能有任何顾客是不存在于 CUSTOMER 表格中的资料。
CUSTOMER 表格
栏位名|性质|
--|--|
SID|主键|
Last_Name||
First_Name||
ORDERS 表格
栏位名|性质|
--|--|
Order_ID|主键|
Order_Date||
Customer_SID|外来键|
Amount|
> 在以上的例子中,ORDERS 表格中的 customer_SID 栏位是一个指向 CUSTOMERS 表格中 SID 栏位的外来键。
以下列出几个在建置 ORDERS 表格时指定外来键的方式:
```
MySQL:
CREATE TABLE ORDERS
(Order_ID integer,
Order_Date date,
Customer_SID integer,
Amount double,
Primary Key (Order_ID),
Foreign Key (Customer_SID) references CUSTOMER(SID));
Oracle:
CREATE TABLE ORDERS
(Order_ID integer primary key,
Order_Date date,
Customer_SID integer references CUSTOMER(SID),
Amount double);
SQL Server:
CREATE TABLE ORDERS
(Order_ID integer primary key,
Order_Date datetime,
Customer_SID integer references CUSTOMER(SID),
Amount double);
```
以下的例子则是藉着改变表格架构来指定外来键。这里假设 ORDERS 表格已经被建置,而外来键尚未被指定:
```
MySQL:
ALTER TABLE ORDERS
ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(sid);
Oracle:
ALTER TABLE ORDERS
ADD (CONSTRAINT fk_orders1) FOREIGN KEY (customer_sid) REFERENCES
CUSTOMER(sid);
SQL Server:
ALTER TABLE ORDERS
ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(sid);
```
视观表 (Views) 可以被当作是虚拟表格。它跟表格的不同是,表格中有实际储存资料,而视观表是建立在表格之上的一个架构,它本身并不实际储存资料。
CREATE VIEW "VIEW_NAME" AS "SQL 语句"
“SQL 语句” 可以是任何一个我们在这个教材中有提到的 SQL。
使用 【Store_Information 表格】 和 【Geography 表格】 做视观表创建实例
CREATE VIEW V_REGION_SALES
AS SELECT A1.region_name REGION, SUM(A2.Sales) SALES
FROM Geography A1, Store_Information A2
WHERE A1.store_name = A2.store_name
GROUP BY A1.region_name
这就给我们有一个名为 V_REGION_SALES 的视观表。这个视观表包含不同地区的销售哦。
SELECT * FROM V_REGION_SALES
CREATE INDEX "INDEX_NAME" ON "TABLE_NAME" (COLUMN_NAME)
TABLE Customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date)
//若我们要在 Last_Name 这个栏位上建一个索引,我们就打入以下的指令,
CREATE INDEX IDX_CUSTOMER_LAST_NAME on CUSTOMER (Last_Name)
//我们要在 City 及 Country 这两个栏位上建一个索引,我们就打入以下的指令,
CREATE INDEX IDX_CUSTOMER_LOCATION on CUSTOMER (City, Country)
索引的命名并没有一个固定的方式。通常会用的方式是在名称前加一个字首,例如 “IDX_” ,来避免与数据库中的其他物件混淆。另外,在索引名之内包括表格名及栏位名也 是一个好的方式。
> 请读者注意,每个数据库会有它本身的 CREATE INDEX 语法,而不同数据库的语法会有
> 不同。因此,在下指令前,请先由数据库使用手册中确认正确的语法。
ALTER TABLE "table_name"
//对应修改语法:
//加一个栏位:
ADD "栏位 1" "栏位 1 资料种类"
//删去一个栏位:
DROP "栏位 1"
//改变栏位名称:
CHANGE "原本栏位名" "新栏位名" "新栏位名资料种类"
//改变栏位的资料种类:
MODIFY "栏位 1" "新资料种类"
DROP TABLE "表格名"
DROP TABLE customer
整个表格就消失,而无法再被用了。
TRUNCATE TABLE "表格名"
TRUNCATE TABLE customer
表格中的资料会完全消失,可是表格本身会继续存在。
INSERT INTO "表格名" ("栏位 1", "栏位 2", ...)
VALUES ("值 1", "值 2", ...)
INSERT INTO customer (First_Name,Last_Name,Address,City,Country,Birth_Date)
VALUES ("Liang","Yajun","四川成都龙泉","成都","四川","1991-04-06")
INSERT INTO user_info(store_name,Sales,Date)
SELECT CONCAT(First_Name," ",Last_Name),Birth_Date,Birth_Date FROM customer;
UPDATE "表格名"
SET "栏位 1" = [新值]
WHERE {条件}
UPDATE store_information
SET Sales = 1000
WHERE store_name = "San Diego"
AND Date = "Jan-07-1999"
DELETE FROM "表格名"
WHERE {条件}
DELETE FROM Store_Information
WHERE store_name = "Los Angeles"
store_name | Sales | Date |
---|---|---|
Los Angeles | $1500 | Jan-05-1999 |
San Diego | $250 | Jan-07-1999 |
Los Angeles | $300 | Jan-08-1999 |
Boston | $700 | Jan-08-1999 |
Date | Sales |
---|---|
Jan-07-1999 | $250 |
Jan-10-1999 | $535 |
Jan-11-1999 | $320 |
Jan-12-1999 | $750 |
[SQL 语句 1]
UNION
[SQL 语句 2]
SELECT Date FROM Store_Information
UNION
SELECT Date FROM Internet_Sales
UNION 的一个限制是 两个 SQL 语句所产生的栏位需要是同样的资料种类。另外,当我们用 UNION 这个指令 时,我们只会看到不同的资料值 (类似 SELECT DISTINCT)。
[SQL 语句 1]
UNION ALL
[SQL 语句 2]
SELECT Date FROM Store_Information
UNION ALL
SELECT Date FROM Internet_Sales
[SQL 语句 1]
INTERSECT
[SQL 语句 2]
SELECT Date FROM Store_Information
INTERSECT
SELECT Date FROM Internet_Sales
[SQL 语句 1]
MINUS
[SQL 语句 2]
SELECT Date FROM Store_Information
MINUS
SELECT Date FROM Internet_Sales
请注意,在 MINUS 指令下,不同的值只会被列出一次。
SELECT "栏位 1"
FROM "表格"
WHERE "栏位 2" [比较运算素]
(SELECT "栏位 1"
FROM "表格"
WHERE [条件])
SELECT SUM(Sales) FROM Store_Information
WHERE Store_name IN
(SELECT store_name FROM Geography
WHERE region_name = 'West')
SELECT "栏位 1"
FROM "表格 1"
WHERE EXISTS
(SELECT *
FROM "表格 2"
WHERE [条件])
SELECT SUM(Sales) FROM Store_Information
WHERE EXISTS
(SELECT * FROM Geography
WHERE region_name = 'West')
SELECT CASE ("栏位名")
WHEN "条件 1" THEN "结果 1"
WHEN "条件 2" THEN "结果 2"
...
[ELSE "结果 N"]
END
FROM "表格名"
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
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;
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);
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;
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;
//Select
SELECT "栏位" FROM "表格名"
//Distinct
SELECT DISTINCT "栏位"
FROM "表格名"
//Where
SELECT "栏位"
FROM "表格名"
WHERE "condition"
//And/Or
SELECT "栏位"
FROM "表格名"
WHERE "简单条件"
{[AND|OR] "简单条件"}+
//In
SELECT "栏位"
FROM "表格名"
WHERE "栏位" IN ('值 1', '值 2', ...)
//Between
SELECT "栏位"
FROM "表格名"
WHERE "栏位" BETWEEN '值 1' AND '值 2'
//Like
SELECT "栏位"
FROM "表格名"
WHERE "栏位" LIKE {模式}
//Order By
SELECT "栏位"
FROM "表格名"
[WHERE "条件"]
ORDER BY "栏位" [ASC, DESC]
//Count
SELECT COUNT("栏位")
FROM "表格名"
//Group By
SELECT "栏位 1", SUM("栏位 2")
FROM "表格名"
GROUP BY "栏位 1"
//Having
SELECT "栏位 1", SUM("栏位 2")
FROM "表格名"
GROUP BY "栏位 1"
HAVING (函数条件)
//Create Table
CREATE TABLE "表格名"
("栏位 1" "栏位 1 资料种类",
"栏位 2" "栏位 2 资料种类"",
... )
//Drop Table
DROP TABLE "表格名"
//Truncate Table
TRUNCATE TABLE "表格名"
//Insert Into
INSERT INTO "表格名" ("栏位 1", "栏位 2", ...)
VALUES ("值 1", "值 2", ...)
//Update
UPDATE "表格名"
SET "栏位 1" = [新值]
WHERE {条件}
//Delete From
DELETE FROM "表格名"
WHERE {条件}