8-mySQL-一些常用语句表格处理

CREATE TABLE

表格是数据库中储存资料的基本框架。

格被分为栏位 (column) 及列位 (row)。每一列代表一笔资料，而每一栏代表一笔资料的一部份。

CREATE TABLE 的语法是：

CREATE TABLE "表格名"
("栏位 1" "栏位 1 资料种类",
"栏位 2" "栏位 2 资料种类",
... );

若我们要建立顾客表格（表格中包含姓、名、地址、城市、国家、生日等），我们可以这样：

CREATE TABLE Customer(
First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date datetime
);

CREATE VIEW

VIEW 可以看作是一个虚拟表格。和表格不同的是，表格中有实际储存的资料，而 VIEW 表是建立在表格之上的一个架构，本身并不实际储存资料。

语法如下：

CREATE VIEW "VIEW_NAME" AS "SQL 语句";

假如我们有这样一个表格

Customer 表格

栏位名称	资料种类
First_Name	char(50)
Last_Name	char(50)
Address	char(50)
City	char(50)
Country	char(25)
Birth_Date	datetime

若要在这个表格上建立一个包括 First_Name， Last_Name， 和 Country 这三个栏位的视观表，就可以

CREATE VIEW V_Customer
AS SELECT First_Name, Last_Name, Country
FROM Customer;

我们现在就有了一个叫做 V_Customer 的 VIEW 表。

V_Customer 视观表

栏位名称	资料种类
First_Name	char(50)
Last_Name	char(50)
Country	char(25)

假如我们有两个这样的表格，我们可以通过 VIEW（视观表）来连接两个表格，然后直接由一个 VIEW 表中找到需要的资料。

Store_Information 表格

Store_Name	Sales	Txn_Date
Los Angeles	1500	05-Jan-1999
San Diego	250	07-Jan-1999
Los Angeles	300	08-Jan-1999
Boston	700	08-Jan-1999

Geography 表格

Region_Name	Store_Name
East	Boston
East	New York
West	Los Angeles
West	San Diego

可以用下面的指令来建一个包括每个地区销售额的 VIEW 表。

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;
结果：

REGION	SALES
East	700
West	2050

CREATE INDEX

Index （索引）
可以帮助我们更快找到需要的资料。

如果表格中没有索引的话，资料库系统就需要将整个表格的资料读出（即 table scan），如果有索引的话，资料库系统就可以先由这个索引去找出需要的资料在表格什么地方，然后直接过去抓取资料。

一个索引可以涵盖一或多个栏位。建立索引的语法如下：

CREATE INDEX "INDEX_NAME" ON "TABLE_NAME" (COLUMN_NAME);

假如我们有这样一个表格

Customer 表格

栏位名称	资料种类
First_Name	char(50)
Last_Name	char(50)
Address	char(50)
City	char(50)
Country	char(25)
Birth_Date	datetime

如果我们想在 Last_Name 这里建立一个索引，可以这样子

CREATE INDEX IDX_CUSTOMER_LAST_NAME
ON Customer(Last_Name);

如果一个索引包含两个栏位，city 和 country

CREATE INDEX IDX_CUSTOMER_LOCATION
ON Customer (City, Country);

索引的命名并没有一个固定的方式。通常会用的方式是在名称前加一个字首，例如 “IDX_” ，来避免与资料库中的其他物件混淆。另外，在索引名之内包括表格名及栏位名也是一个好的方式。

ALTER TABLE

在表格被建立在资料库中后，我们常常会发现，这个表格的结构需要有所改变。常见的改变如下：

• 加一个栏位
• 删去一个栏位
• 改变栏位名称
• 改变栏位的资料种类

ALTER TABLE 也可以被用来作其他的改变，例如改变主键定义。

其语法如下：

ALTER TABLE "table_name"
[改变方式];

[改变方式] 的详细写法会依我们想要达到的目标而有所不同。在以上列出的改变中，[改变方式] 如下：

• 加一个栏位: ADD “栏位 1” “栏位 1 资料种类”
• 删去一个栏位: DROP “栏位 1”
• 改变栏位名称: CHANGE “原本栏位名” “新栏位名” “新栏位名资料种类”
• 改变栏位的资料种类: MODIFY “栏位 1” “新资料种类”

假如原来的表是这样子的。

Customer 表格

栏位名称	资料种类
First_Name	char(50)
Last_Name	char(50)
Address	char(50)
City	char(50)
Country	char(25)
Birth_Date	datetime

尝试一下 ADD ，添加一个 Gender 的栏位。

ALTER TABLE Customer ADD Gender char(1);

表格架构就变成

First_Name	char(50)
Last_Name	char(50)
Address	char(50)
City	char(50)
Country	char(25)
Birth_Date	datetime
Gender	char(1)

尝试一下改名
Address 改成 Addr

ALTER TABLE Customer CHANGE Address Addr char(50);

栏位名称	资料种类
First_Name	char(50)
Last_Name	char(50)
Addr	char(50)
City	char(50)
Country	char(25)
Birth_Date	datetime
Gender	char(1)

“Addr” 栏位的资料种类改为 char(30)

ALTER TABLE Customer MODIFY Addr char(30);

First_Name	char(50)
Last_Name	char(50)
Addr	char(30)
City	char(50)
Country	char(25)
Birth_Date	datetime
Gender	char(1)

要删除 “Gender” 栏位

ALTER TABLE Customer DROP Gender;

栏位名称	资料种类
First_Name	char(50)
Last_Name	char(50)
Addr	char(30)
City	char(50)
Country	char(25)
Birth_Date	datetime

SQL 主键

主键（Primary Key）中的每一笔资料都是表格中的唯一值。

用来独一无二地确认一个表格中的每一行资料。

主键可以包含一或多个栏位。当主键包含多个栏位时，称为组合键 (Composite Key)。

主键可以在建置新表格时设定 (运用 CREATE TABLE 语句)，或是以改变现有的表格架构方式设定 (运用 ALTER TABLE)。

几个在建置新表格时设定主键的方式：

MySQL:

CREATE TABLE Customer
(SID integer,
Last_Name varchar(30),
First_Name varchar(30),
PRIMARY KEY (SID));


Oracle:

CREATE TABLE Customer
(SID integer PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));


SQL Server:

CREATE TABLE Customer
(SID integer PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));

以下则是以改变现有表格架构来设定主键的方式：

MySQL:

ALTER TABLE Customer ADD PRIMARY KEY (SID);


Oracle:

ALTER TABLE Customer ADD PRIMARY KEY (SID);


SQL Server:

ALTER TABLE Customer ADD PRIMARY KEY (SID);

注意，在用 ALTER TABLE 语句来添加主键之前，我们需要确认被用来当做主键的栏位是设定为 『NOT NULL』 ；也就是说，那个栏位一定不能没有资料。

SQL 外来键

外来键是一个(或数个)指向另外一个表格主键的栏位。外来键的目的是确定资料的参考完整性 (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 栏位是一个指向 CUSTOMER 表格中 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);

DROP TABLE

如果我们想从数据库中清除一个表格。可以使用 DROP TABLE 语法。

格式是：

DROP TABLE "表格名";

比如清除顾客表格

DROP TABLE Customer;

TRUNCATE TABLE

清除一个表格中的所有资料。但表格仍然存在。

TRUNCATE TABLE "表格名";

比如清除顾客表格的所有资料：

TRUNCATE TABLE Customer;

INSERT INTO

资料如何进入表格的呢。

一种是一次输入一笔资料，另一种是一次输入好几笔资料。我们先来看一次输入一笔的方式。

语法是

INSERT INTO "表格名" ("栏位1", "栏位2", ...)
VALUES ("值1", "值2", ...);

假如已经存在这样的表格
Store_Information 表格

栏位名称	资料种类
Store_Name	char(50)
Sales	float
Txn_Date	datetime

我们要加以下的这一笔资料进去这个表格：在 January 10, 1999，Los Angeles 店有$900 的营业额。那我们就可以这样子操作：

NSERT INTO Store_Information (Store_Name, Sales, Txn_Date)
VALUES ('Los Angeles', 900, 'Jan-10-1999');

而一次输入多笔的情况：
要用 SELECT 指令来指明要输入表格的资料，资料是从另一个表格来的。

格式是：

INSERT INTO "表格1" ("栏位1", "栏位2", ...)
SELECT "栏位3", "栏位4", ...
FROM "表格2";

这整句 SQL 也可以含有 WHERE、 GROUP BY、及 HAVING 等子句，以及表格连接及别名等等。

举例来说，若我们想要将 1998 年的营业额资料放入 Store_Information 表格，而我们知道资料的来源是可以由 Sales_Information 表格取得的话，就可以这样子写：

INSERT INTO Store_Information (Store_Name, Sales, Txn_Date)
SELECT store_name, Sales, Txn_Date
FROM Sales_Information
WHERE Year (Txn_Date) = 1998;

UPDATE

如果我们想要修改表格中的资料，就需要使用 UPDATE 指令。

语法格式是：

UPDATE "表格名"
SET "栏位1" = [新值]
WHERE "条件";

假如有这样一个表格
Store_Information 表格

Store_Name	Sales	Txn_Date
Los Angeles	1500	05-Jan-1999
San Diego	250	07-Jan-1999
Los Angeles	300	08-Jan-1999
Boston	700	08-Jan-1999

我们发现说 Los Angeles 在 08-Jan-1999 的营业额实际上是 $500，而不是表格中所储存的 $300，那么可以：

UPDATE Store_Information
SET Sales = 500
WHERE Store_Name = 'Los Angeles'
AND Txn_Date = 'Jan-08-1999';

表格内容就变成了

Store_Name	Sales	Txn_Date
Los Angeles	1500	05-Jan-1999
San Diego	250	07-Jan-1999
Los Angeles	500	08-Jan-1999
Boston	700	08-Jan-1999

在这个例子中，只有一笔资料符合 WHERE 子句中的条件。如果有多笔资料符合条件的话，每一笔符合条件的资料都会被修改的。

我们也可以同时修改好几个栏位。这语法如下：

UPDATE "表格"
SET "栏位1" = [值1], "栏位2" = [值2]
WHERE "条件";

DELETE FROM

在一些情况下，我们会根据需要直接由数据库中去除一些资料。使用 DELETE FROM 来实现。

语法是：

DELETE FROM "表格名"
WHERE "条件";

假如有这样一个表格
Store_Information 表格

Store_Name	Sales	Txn_Date
Los Angeles	1500	05-Jan-1999
San Diego	250	07-Jan-1999
Los Angeles	300	08-Jan-1999
Boston	700	08-Jan-1999

如果要把有关 Los Angeles 的资料全部去除，可以这样试试：

DELETE FROM Store_Information
WHERE Store_Name = 'Los Angeles';

表格内容就变成了：

Store_Name	Sales	Txn_Date
San Diego	250	07-Jan-1999
Boston	700	08-Jan-1999