SQL学习笔记
来源:互联网 发布:淘宝装修外包费用 编辑:程序博客网 时间:2024/06/03 14:54
参考网址: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)
Address
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));
添加主键:
ALTERTABLE 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
- SQL、PL/SQL学习笔记
- SQL、PL/SQL学习笔记
- SQL、PL/SQL学习笔记
- 学习sql语言笔记
- PL/SQL学习笔记
- 学习sql server笔记。
- SQL学习笔记!
- sql学习笔记
- PL/SQL学习笔记
- PL/SQL学习笔记
- SQL学习笔记
- PL/SQL学习笔记
- PL/SQL学习笔记
- PL/SQL学习笔记
- 学习sql语言笔记
- sql学习笔记
- sql学习笔记
- sql学习笔记
- AndEngine画矩形程序(练习 三)
- 深入理解ARM体系架构(S3C6410)---认识S3C6410
- DLL劫持注入技术分析、过各种游戏保护!让你做你爱做的事情!
- centos下安装xampp
- 多维数组转成一维数组
- SQL学习笔记
- session和cookie的区别
- 不开index monitor的情况下判断索引是否被使用过
- C中如何调用C++函数?
- hibernate 映射文件 当表名或字段名与数据库关键字冲突的解决办法
- LA 4609 ,poj 3842 An Industrial Spy
- 如何在C++中调用C的代码
- Windows下 Python 安装包的配置
- 程序设计的总体纲领