爱华网1.从表格中选出一栏
SELECT store_name FROMStore_Information
2.选出表格中某栏位的所有值（不重复）
SELECT DISTINCT store_nameFROM Store_Information
3.选出营业额超过 $1,000 的资料（用WHERE关键字）
SELECT store_name
FROM Store_Information
WHERE Sales> 1000
4.复杂条件选择（AND OR关键字）
SELECT store_name
FROM Store_Information
WHERE Sales > 1000
OR (Sales< 500 ANDSales > 275)
5.在几个栏内选择（关键字IN）
SELECT *
FROM Store_Information
WHERE store_name IN('Los Angeles', 'San Diego')
6.在一个范围内取值（关键字BETWEEN）
SELECT *
FROM Store_Information
WHERE Date BETWEEN'Jan-06-1999' AND 'Jan-10-1999'
7.在栏位名中选择（关键字LIKE）
SELECT *
FROM Store_Information
WHERE store_name LIKE '%AN%'
8. 选出内容升序、降序排列（关键字ORDER BY，默认ASC）
SELECT store_name, Sales, Date
FROM Store_Information
ORDERBY Sales（2） DESC//对第二个栏位(Sales)降序排列
9.SQL函数
SELECT SUM(Sales)FROM Store_Information
10.数出在表格中有多少笔资料被选出来（关键字COUNT）
SELECT COUNT(store_name)//SELECTCOUNT(DISTINCT store_name)有多少笔不同名的资料
FROM Store_Information
WHERE store_name is notNULL
11.对各家店分别求和（关键字GROUP BY）
SELECT store_name, SUM(Sales)
FROM Store_Information
GROUP BY store_name
12.哪些店的总营业额有超过$1,500（不能用WHERE，因为此项是算出的值。条件关键字HAVING）
SELECT store_name, SUM(sales)
FROM Store_Information
//GROUP BY store_name
HAVING SUM(sales) >1500
13.ALIAS（别名）
SELECT"表格别名"."栏位1" "栏位别名"
FROM "表格名" "表格别名"
SELECT A1.store_nameStore, SUM(A1.Sales) "TotalSales"
FROM Store_Information A1
GROUP BY A1.store_name
14.表格链接
SELECTA1.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
15.OUTER JOIN (外部连接)
（表格之后加上一个 "(+)" 来代表说这个表格中的所有资料我们都要）
SELECTA1.store_name, SUM(A2.Sales) SALES
FROM Georgraphy A1, Store_InformationA2
WHERE A1.store_name = A2.store_name(+) //表A1中没有，表A2中有
GROUP BYA1.store_name
16.Subquery结构
SELECT SUM(Sales)FROM Store_Information
WHERE Store_name IN
(SELECT store_name FROM Geography
WHERE region_name ='West')
从另一个表格中西区店名与本表相同的店中，选出本表中这些店的营业额
17.UNION 指令的目的是将两个 SQL语句的结果合并起来（相同的只出现一次）
SELECT DateFROM Store_Information
UNION
SELECT Date FROM Internet_Sales
（相当于SELECT DISTINCT Date）
18.找出有店面营业额以及网络营业额的日子（结果中同一天会重复显示）
SELECT DateFROM Store_Information
UNION ALL
SELECT Date FROM Internet_Sales
19.INTERSECT 是选出量两表中数据的交集
SELECT DateFROM Store_Information
INTERSECT
SELECT Date FROM Internet_Sales
20.MINUS 指令找出（在第一个 SQL语句的结果中）不在（在第二个 SQL 语句的结果中）的资料（差集）
SELECT DateFROM Store_Information
MINUS
SELECT Date FROM Internet_Sales
21.连接字符串
MySQL/Oracle:
SELECT CONCAT(region_name,store_name) FROMGeography
WHERE store_name = 'Boston';
store_name为Boston处的region_name为East，所以结果为：EastBoston
Oracle:
SELECT region_name ||' ' ||store_name FROM Geography
WHERE store_name= 'Boston';
结果为：East Boston
SQL Server:
SELECT region_name +' ' +store_name FROM Geography
WHERE store_name= 'Boston';
结果为：East Boston
22.SUBSTRING
SUBSTR(str,pos)：从第pos个字符读到底
SUBSTR(str,pos,len)：从第pos个字符读len个字符
SELECT SUBSTR(store_name,3)
FROM Geography
WHERE store_name= 'Los Angeles';
结果: 's Angeles'
SELECT SUBSTR(store_name,2,4)
FROM Geography
WHERE store_name= 'San Diego';
结果: 'an D'
23.TRIM 函数是用来移除掉一个字串中的字头或字尾
SELECT TRIM(' Sample');
结果: 'Sample'
SELECT LTRIM(' Sample');
结果: 'Sample'
SELECT RTRIM(' Sample');
结果: 'Sample'
24.CREATE TABLE
CREATE TABLE "表格名"
("栏位 1" "栏位 1 资料种类",
"栏位 2" "栏位 2 资料种类",
... )
CREATE TABLEcustomer
(First_Name char(50),
Last_Name char(50),
Addresschar(50),
Citychar(50),
Countrychar(25),
Birth_Datedate)
25.CREATE VIEW（视观表）
CREATE VIEWV_REGION_SALES
AS SELECT A1.region_name REGION, SUM(A2.Sales)SALES
FROM Geography A1, Store_InformationA2
WHERE A1.store_name =A2.store_name
GROUP BY A1.region_name
这个视观表包含不同地区的销售，查询时打入：
SELECT * FROM V_REGION_SALES
26.Create Index（建立索引）
语法：CREATE INDEX "INDEX_NAME" ON"TABLE_NAME" (COLUMN_NAME)
CREATE INDEXIDX_CUSTOMER_LAST_NAMEON CUSTOMER(Last_Name)
27.ALTER TABLE（修改表格）
ALTER tablecustomer add Genderchar(1)
ALTER table customer change AddressAddr char(50) //改名
ALTER table customer modify Addr char(30)//改种类
ALTER tablecustomer drop Gender
28.主键 (PrimaryKey)用来独一无二地确认一个表格中的每一行资料
在建置新表格时设定主键的方式：
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 ADDPRIMARY KEY (SID);
主键的栏位不能为NULL
29.外来键：是一个(或数个)指向另外一个表格主键的栏位。
建置 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);
改变表格架构来指定外来键：
MySQL:
ALTER TABLE ORDERS
ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(sid);
Oracle:
ALTER TABLE ORDERS
ADD (CONSTRAINT fk_orders1) FOREIGN KEY (customer_sid) REFERENCESCUSTOMER(sid);
SQL Server:
ALTER TABLE ORDERS
ADD FOREIGN KEY (customer_sid)REFERENCES CUSTOMER(sid);
30.DROP TABLE（清除表格）
DROP TABLEcustomer
31.TRUNCATE TABLE 清除一个表格中的所有资料
TRUNCATE TABLEcustomer
32.把资料插入表格（INTO）
INSERT INTOStore_Information (store_name, Sales, Date)
VALUES ('Los Angeles', 900,'Jan-10-1999')
资料由另一表格获得：
INSERT INTOStore_Information (store_name, Sales, Date)
SELECT store_name, Sales, Date
FROM Sales_Information
WHERE Year(Date) = 1998
33.修改表格中的资料（UPDATE）
UPDATE "表格名"
SET "栏位1" = [新值]
WHERE {条件}
UPDATEStore_Information
SET Sales = 500
WHERE store_name = "Los Angeles"
AND Date = "Jan-08-1999"
34.数据库中去除一些资料（DELETEFROM）
DELETE FROM "表格名"
WHERE {条件}
DELETE FROMStore_Information
WHERE store_name = "LosAngeles"
参考资料：http://sql.1keydata.com/cn/
