金鳞

就算是一本再好的课本，如果不是自己亲手整理出来笔记，总觉得不是真正的读懂了!
以下内容引自：http://sql.1keydata.com/cn/sql-select.php

SQL
_________________________________________
Store_Information表格

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

_________________________________________

Geography 表格

region_name	store_name
East	Boston
East	New York
West	Los Angeles
West	San Diego

------------------------------------------
SELECT store_name FROM Store_Information;
SELECT DISTINCT store_name FROM Store_Information;
SELECT store_name FROM Store_Information WHERE Sales > 1000;
SELECT store_name FROM Store_Information WHERE Sales > 1000 OR (Sales < 500 AND Sales > 275) ;
SELECT * FROM Store_Information WHERE store_name IN ('Los Angeles', 'San Diego');
SELECT * FROM Store_Information WHERE Date BETWEEN 'Jan-06-1999' AND 'Jan-10-1999';
SELECT * FROM Store_Information WHERE store_name LIKE '%AN%';
SELECT * FROM Store_Information WHERE store_name LIKE 'A_Z';
SELECT * FROM Store_Information WHERE store_name LIKE 'ABC%';
SELECT * FROM Store_Information WHERE store_name LIKE '%XYZ';
SELECT store_name, Sales, Date FROM Store_Information ORDER BY Sales DESC;
SELECT store_name, Sales, Date FROM Store_Information ORDER BY Sales ASC;
SELECT SUM(Sales) FROM Store_Information;
//SELECT "函数名"("栏位名") FROM "表格名"
SELECT COUNT(store_name) FROM Store_Information WHERE store_name is not NULL;
SELECT COUNT(DISTINCT store_name) FROM Store_Information;
SELECT store_name, SUM(Sales) FROM Store_Information GROUP BY store_name;
SELECT store_name, SUM(sales) FROM Store_Information GROUP BY store_name HAVING SUM(sales) > 1500;
SELECT A1.store_name Store, SUM(A1.Sales) "Total Sales" FROM Store_Information A1 GROUP BY A1.store_name; //'inner join'or'left join'
SELECT A1.store_name, SUM(A2.Sales) SALES FROM Georgraphy A1, Store_Information A2 WHERE A1.store_name = A2.store_name (+) GROUP BY A1.store_name; //outer join
SELECT SUM(Sales) FROM Store_Information WHERE Store_name IN(SELECT store_name FROM Geography WHERE region_name = 'West'); //subquery
SELECT Date FROM Store_Information UNION SELECT Date FROM Internet_Sales; //联集
SELECT Date FROM Store_Information UNION ALL SELECT Date FROM Internet_Sales; //有重复项
SELECT Date FROM Store_Information INTERSECT SELECT Date FROM Internet_Sales; //交集
SELECT Date FROM Store_Information MINUS SELECT Date FROM Internet_Sales;
SELECT CONCAT(region_name,store_name) FROM Geography WHERE store_name = 'Boston'; //MySQL/Oracle 'EastBoston';
SELECT region_name || ' ' || store_name FROM Geography WHERE store_name = 'Boston'; //Oracle 'East Boston';
SELECT region_name + ' ' + store_name FROM Geography WHERE store_name = 'Boston'; //SQL Server 'East Boston';
SELECT SUBSTR(store_name, 3) FROM Geography WHERE store_name = 'Los Angeles'; //不适用于SQL Server
SELECT SUBSTR(store_name,2,4) FROM Geography WHERE store_name = 'San Diego'; //'an D'
SELECT TRIM('   Sample   ');
SELECT LTRIM('   Sample   ');
SELECT RTRIM('   Sample   ');
CREATE TABLE customer (First_Name char(50),Last_Name char(50),Address char(50),City char(50),Country char(25),Birth_Date date);
CREATE VIEW V_Customer AS SELECT First_Name, Last_Name, Country FROM Customer;
View V_Customer (First_Name char(50),Last_Name char(50),Country char(25));
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;
SELECT * FROM V_REGION_SALES;

INDEX,

现在假设我们有以下这个表格，
TABLE Customer(First_Name char(50),Last_Name char(50),Address char(50),City char(50),Country char(25),Birth_Date date);

1.若我們要在 Last_Name 這個栏位上建一個索引，我們就打入以下的指令，
CREATE INDEX IDX_CUSTOMER_LAST_NAME on CUSTOMER (Last_Name)

2.若我们要在 City和Country 这两个栏位上建一个索引，我们就打入以下的指令，
CREATE INDEX IDX_CUSTOMER_LOCATION on CUSTOMER (City, Country)

ALERT,
______________________________________________________
customer 表格

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

------------------------------------------------------
1.ALTER table customer add Gender char(1)

customer 表格

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

2.ALTER table customer change Address Addr char(50)

customer 表格

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

3.ALTER table customer modify Addr char(30)

customer 表格

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

4.ALTER table customer drop Gender

customer 表格

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

RIMARY KEY,

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

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』 ；也就是说，那个栏位一定不能没有资料。

FOREIGE KEY,

举例如下：

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); 


DROP TABLE customer;  //删除表格

TRUNCATE TABLE customer;   //清除表格中的所有资料

INSERT INTO Store_Information (store_name, Sales, Date) VALUES ('Los Angeles', 900, 'Jan-10-1999');    //一次性输入一笔资料

INSERT INTO "表格1" ("栏位1", "栏位2", ...) SELECT "栏位3", "栏位4", ...  FROM "表格2"; //一次性输入多笔资料

INSERT INTO Store_Information (store_name, Sales, Date) SELECT store_name, Sales, Date FROM Sales_Information WHERE Year(Date) = 2009;  //SQL Server一次性输入多笔资料，以上的语法是最基本的。这整句 SQL 也可以含有 WHERE、 GROUP BY、 及 HAVING 等子句，以及表格连接及别名等等。

INSERT INTO Store_Information (store_name, Sales, Date) SELECT store_name, Sales, Date FROM Sales_Information WHERE to_char(date,'yyyy')=1998; //  Oracle 一次性输入多笔资料 

UPDATE Store_Information  SET Sales = 500 WHERE store_name = "Los Angeles" AND Date = "Jan-08-1999" ; //一次性update一笔资料
UPDATE "表格" SET "栏位1" = [值1], "栏位2" = [值2] WHERE {条件} ;  //一次性update多笔资料