参考网址:http://www.1keydata.com/cn/sql/
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 |
SUM 求和
SELECT SUM(Sales)FROM Store_Information;
结果
SUM(Sales) |
2750 |
类似的函数:
AVG (平均)
COUNT (计数)
MAX (最大值)
MIN (最小值)
SUM (总合)
COUNT 计数
SELECT COUNT(Store_Name) FROMStore_Information WHEREStore_Name IS NOT NULL;
结果
COUNT (Store_Name) |
4 |
SELECT COUNT(DISTINCT Store_Name) FROMStore_Information;
结果 COUNT (DISTINCT Store_Name) |
3 |
GROUP BY 分组查询
SELECTStore_Name, SUM(Sales) FROMStore_Information GROUP BYStore_Name;
结果
Store_Name | SUM(Sales) |
Los Angeles | 1800 |
San Diego | 250 |
Boston | 700 |
注:至少有一个列包含函数运算才能使用group by,并且除了有包括函数的列,其它都需要包含在group by里面。
HAVING 把函数产生的值当条件
SELECTStore_Name, SUM(Sales) FROMStore_Information GROUP BYStore_Name HAVING SUM(sales) > 1500;
结果
Store_Name | SUM(Sales) |
Los Angeles | 1800 |
Alias 别名
SELECTA1.Store_Name Store, SUM(A1.Sales) 'Total Sales' FROM Store_Information A1 GROUP BYA1.Store_Name;
Store | Total Sales |
Los Angeles | 1800 |
San Diego | 250 |
Boston | 700 |
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 |
表格链接(左链接left join)
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;
结果
REGION | SALES |
Eas | 700 |
West | 2050 |
外部链接(outjoin)
SELECTA1.Store_Name, SUM(A2.Sales) SALES
FROM Georgraphy A1, Store_Information A2
WHERE A1.Store_Name = A2.Store_Name (+)
GROUP BY A1.Store_Name;
结果
Store_Name | SALES |
Boston | 700 |
New York | |
Los Angeles | 1800 |
San Diego | 250 |
注:oracle中使用+表示该表中所有数据都需要选出。
subquery 子查询
SELECTSUM(Sales) FROM Store_Information
WHERE Store_name IN
(SELECT store_name FROM Geography
WHERE region_name = 'West');
结果
SUM(Sales) |
2050 |
CREATE TABLE 创建表
CREATE TABLECustomer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date datetime);
Customer 表格
栏位名称 | 资料种类 |
First_Name | char(50) |
Last_Name | char(50) |
Address | char(50) |
City | char(50) |
Country | char(25) |
Birth_Date | datetime |
CREATE VIEW V_Customer
AS SELECT First_Name, Last_Name, Country
FROM Customer;
得到一个 V_Customer 视图表
栏位名称 | 资料种类 |
First_Name | char(50) |
Last_Name | char(50) |
Country | char(25) |
假如有以下两个表格
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 |
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;
结果
REGION | SALES |
East | 700 |
West | 2050 |
CREATE INDEX 索引
假如有一个表格:
Customer 表格
栏位名称 | 资料种类 |
First_Name | char(50) |
Last_Name | char(50) |
Address | char(50) |
City | char(50) |
Country | char(25) |
Birth_Date | datetime |
CREATE INDEX IDX_CUSTOMER_LAST_NAME
ON Customer (Last_Name);
CREATE INDEX IDX_CUSTOMER_LOCATION
ON Customer (City, Country);
ALTER TABLE 修改
假如有个表格:
Customer 表格
栏位名称 | 资料种类 |
First_Name | char(50) |
Last_Name | char(50) |
char(50) | |
City | char(50) |
Country | char(25) |
Birth_Date | datetime |
加入一个Gender栏:
ALTER TABLE Customer ADD Gender char(1);
将Address修改为Addr:
ALTER TABLE Customer CHANGE Address Addrchar(50);
将Addr类型修改为char(30):
ALTER TABLE Customer MODIFY Addr char(30);
删除Gender栏:
ALTER TABLE Customer DROP Gender;
总结:alter使用的参数:
Add:增加
Drop:删除
Change:修改属性名
Modify:修改类型
PRIMARY KEY
Mysql:
创建表的时候设置主键:
CREATE TABLE Customer
(SID integer,
Last_Name varchar(30),
First_Name varchar(30),
PRIMARYKEY (SID));
添加主键:
ALTER TABLE Customer ADD PRIMARY KEY (SID);
使用ALTER添加主键前需要把对应的栏设为NOT NULL
假如有一下两个表:
CUSTOMER 表格
栏位名 | 性质 |
SID | 主键 |
Last_Name | |
First_Name |
ORDERS 表格
栏位名 | 性质 |
Order_ID | 主键 |
Order_Date | |
Customer_SID | 外键 |
Amount |
外键:一个或多个指向另外一个表格主键的栏
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));
修改外键:
ALTER TABLE ORDERS
ADD FOREIGN KEY (Customer_SID) REFERENCES CUSTOMER (SID);
DROP TABLE 删除表
DROP TABLE Customer;
TRUNCATE TABLE 删除表格的内容
TRUNCATE TABLE Customer;
INSERT INTO 添加数据
Store_Information 表格
栏位名称 | 资料种类 |
Store_Name | char(50) |
Sales | float |
Txn_Date | datetime |
INSERT INTO Store_Information (Store_Name,Sales, Txn_Date)
VALUES ('Los Angeles', 900, 'Jan-10-1999');
INSERT INTO Store_Information (Store_Name,Sales, Txn_Date)
SELECT store_name, Sales, Txn_Date
FROM Sales_Information
WHERE Year (Txn_Date) = 1998;
UPDATE 修改表
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 |
UPDATE Store_Information
SET Sales = 500
WHERE Store_Name = 'Los Angeles'
AND Txn_Date = 'Jan-08-1999';
修改后为:
Store_Information 表格
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 |
DELETE FROM 删除内容
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 |
DELETE FROM Store_Information
WHERE Store_Name = 'Los Angeles';
结果
Store_Information 表格
Store_Name | Sales | Txn_Date |
San Diego | 250 | 07-Jan-1999 |
Boston | 700 | 08-Jan-1999 |