SQL数据定义语句（表结构的操作）
SQL语言的数据定义包括对数据库、基本表、视图、索引等数据库对象的创建和删除
一、数据库
数据库定义
语法
CREATE DATABASE <数据库名> <其他参数>
语句
CREATE DATABASE SaleProduct
数据库删除
语法
DROP DATABASE <数据库名> <其他参数>
语句
DROP DATABASE SaleProduct、
二、基本表
常用完整性约束
NULL |NOT NULL：该列值可以为空，或者不能为空。
UNIQUE：唯一性约束，说明该列取值必须唯一。
PRIMARY KEY：主码约束，说明该列为基本表的主码。
FOREIGN KEY：外码约束，说明表之间的参照关系。
CHECK：域完整性约束，说明该列的取值需要满足的约束条件。
DEFAULT：默认值设置，表示该列在未定义时的默认取值。
SQL数据类型
网址：https://www.w3school.com.cn/sql/sql_datatypes.asp
插入数据时，啥样的数据加引号，啥样的不加？
答：字符串类型的char,nchar,varchar,nvarchar,text,ntext都要加引号
日期类型也要加引号,
数值型,逻辑型的不用加引号.
（varchar,char,nvarchar,nchar,text,ntext,datetime都需要加单引号，int,numeric,bit不需要加）
基本表的定义
语法
CREATE TABLE <基本表名>
(<列名1> <列数据类型> [列完整性约束]
[,<列名2> <列数据类型> [列完整性约束]
…]
[,表级完整性约束])
说明：其中，“< >”中的内容是必选项，“[ ]”中的内容是可选项。
语句
创建客户表Customer
CREATE TABLE Customer(
custID varchar(5) PRIMARY KEY,
custName varchar(20) NOT NULL UNIQUE,
custCity varchar(10),
custPhone varchar(13) ) ;
创建客户表Customer并为约束重命名
CREATE TABLE Customer(
custID varchar(5) constraint PK_Customer PRIMARY KEY,
custName varchar(20) NOT NULL constraint UQ_Customer UNIQUE,
custCity varchar(10),
custPhone varchar(13) ) ;
check及default的使用
CREATE TABLE Product(
pdID varchar(5) constraint PK_Product PRIMARY KEY,
pdName varchar(20) NOT NULL,
pdSpec varchar(10),
pdModel varchar(10),
pdSupplier varchar(20) NOT NULL,
pdPrice numeric(10, 2) DEFAULT 0 CHECK (pdPrice>=0),
pdStockSize int DEFAULT 0 CHECK (pdStockSize>=0) )
Check约束也可以在定义基本表时定义成表级约束
CREATE TABLE Product(
pdID varchar(5) constraint PK_Product PRIMARY KEY,
pdName varchar(20) NOT NULL,
pdSpec varchar(10),
pdModel varchar(10),
pdSupplier varchar(20) NOT NULL,
pdPrice numeric(10, 2) DEFAULT 0,
pdStockSize int DEFAULT 0 ,
CHECK (pdPrice>=0) ,
CHECK (pdStockSize>=0) )
外码
CREATE TABLE Orders(
orderID varchar(16) PRIMARY KEY,
orderDate date NOT NULL,
custID varchar(5) NOT NULL ,
FOREIGN KEY REFERENCES Customer(custID)
ON DELETE cascade
ON UPDATE cascade,
orderAddress varchar(40) NOT NULL,
orderAmount numeric(10, 2) NOT NULL)
外码约束定义为表级约束
CREATE TABLE Orders(
orderID varchar(16) PRIMARY KEY,
orderDate date NOT NULL,
custID varchar(5) NOT NULL ,
orderAddress varchar(40) NOT NULL,
orderAmount numeric(10, 2) NOT NULL ,
FOREIGN KEY (custID) REFERENCES Customer(custID)
ON DELETE cascade
ON UPDATE cascade)
基本表的修改
语法
ALTER TABLE <表名> [ALTER COLUMN <列名> <列参数> [,…]]
[ADD [ <新列名> <列数据类型> [列完整性约束] [,…]]
| [新的完整性约束][,…]]
[DROP [CONSTRAINT <完整性约束>][,…]
|[COLUMN <列名>] [,…]]
语句
更改数据类型
将Product表中的属性列pdName的数据类型改为varchar(30)
ALTER TABLE Product
ALTER COLUMN pdName varchar(30)
增加列
在Product表中增加一个列：pdMemo（备注），数据类型为varchar(100)
ALTER TABLE Product
ADD pdMemo varchar(100)
增加约束
在Customer表中增加默认值约束：custCity(所在城
市)，其默认值为“北京”
ALTER TABLE Customer
ADD CONSTRAINT DF_custCity
DEFAULT '北京' for custCity
删除约束
删除Customer表中的完整性约束DF_custCity
ALTER TABLE Customer
DROP CONSTRAINT DF_custCity
基本表的删除
语法
DROP TABLE <基本表名1>[,……]
语句
DROP TABLE OrderDetail
三、索引
说明
（1）建立索引的目的 ：加快查询速度
（2）为表设置索引要付出代价：
1. 增加了数据库的存储空间
2. 在插入和修改数据时要花费较多的时间
（3）索引分类
1. 聚集索引
是指表中行的物理顺序与键值的逻辑（索引）顺序相同
一个表只能包含一个聚集索引
2. 非聚集索引
(4)在哪些列上可以创建索引 ？
1. 在经常需要搜索的列上，可以加快搜索的速度；
2. 在作为主码的列上，强制该列的唯一性和组织表中数据的排列结构；
3. 在经常用在连接的列上，这些列主要是一些外码，可以加快连接的速度；
4. 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
5. 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
6. 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
(5)在哪些列上不能创建索引 ？
1. 对于那些在查询中很少使用或者参考的列不应该创建索引。
2. 对于那些只有很少数据值的列也不应该增加索引。
3. 对于那些定义为text, image和bit数据类型的列不应该增加索引。
4. 当修改性能远远大于检索性能时，不应该创建索引。
实现
语法格式
CREATE [UNIQUE] [CLUSTERED] INDEX <索引名>
ON <表名> (<列名>[<次序>][,<列名>[<次序>]]…)
说明：
1. UNIQUE：规定索引的每一个索引值只对应于表中唯一的记录
2. CLUSTER：聚簇索引
3. <次序>：可选ASC（升序）或DESC（降序）。若不指定，默认为升序。
4. 索引的排列方式：先以第一个列名值排序；该列值相同的记录，则按下一列名排序。
建立索引
例：在Customer表的属性列custPhone上创建一个唯一索引。
CREATE UNIQUE INDEX idx_uq_custPhone
on Customer(custPhone)
说明：
（1）对于已含重复值的属性列不能建UNIQUE索引
（2）对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束
（3）索引建立以后，系统在存取数据时会自动选择合适的索引作为存取路径，用户不必也不能显式地选择索引。
删除索引
语法：
DROP INDEX <索引名> ON <基本表名>
说明：不适用于通过定义 PRIMARY KEY 或 UNIQUE约束创建的索引。若要删除该约束和相应的索引，可使用带有 DROP CONSTRAINT 子句的ALTER TABLE。
例：删除Customer表的索引idxuqcustPhone。
DROP INDEX idx_uq_custPhone ON Customer
四、视图
视图
1. 是外模式一级数据结构的基本单位
2. 虚表，由基本表或其它视图导出的表，其本身不存在于数据库中。
3. 只存放视图的定义，而不存放视图对应的数据。
4. 视图一经定义，就可被检索或删除，但更新操作有一定的限制，也可再定义其它视图。
视图的建立
语法
CREATE VIEW <视图名> [(<列名> [，<列名>]…)]
AS <子查询>
[WITH CHECK OPTION]
说明
（1）WITH CHECK OPTION：
对视图进行UPDATE，INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）
对BJCustomer视图的更新操作：
修改操作：RDBMS自动加上custCity='北京' 的条件
删除操作：RDBMS自动加上custCity='北京' 的条件
插入操作：RDBMS自动检查custCity='北京'
如果不是，则拒绝该插入操作
如果没有提供custCity属性值，则自动定义custCity为'北京'
（2）子查询不允许含有ORDER BY子句和DISTINCT短语
（3）组成视图的属性列名，或者全部省略，或者全部指定。
以下三种情况下必须明确指定全部属性列：
1. 子查询SELECT子句里列名中有常数、聚集函数或列表达式。
2. 子查询SELECT子句里列名中有从多个表中选出的同名属性列。
3. 需要用更合适的新列名作视图列的列名。
语句
1. 建立一个所在城市为“北京”的客户信息视图BJCustomer。
CREATE VIEW BJCustomer
AS
SELECT custID, custName , custPhone
FROM Customer
WHERE custCity='北京'；
2. 建立一个生产厂商为“天津南生”的商品视图TJ_Product，并要求进行增、删、改操作时仍需保证该视图只有“天津南生”的商品。
CREATE VIEW TJ_Product
AS
SELECT pdID,pdName,pdPrice
FROM Product
WHERE pdSupplier='天津南生'
WITH CHECK OPTION
3. 建立订购了“天津南生”的商品的订单信息视图TJ_Order，包括订单编号、客户名称和客户电话。
CREATE VIEW TJ_Order(orderID, custName, custPhone)
AS
SELECT Orders.orderID, custName,custPhone
FROM Customer, Orders, OrderDetail,Product
WHERE Customer.custID= Orders.custID AND
Orders.orderID=OrderDetail.orderID AND
OrderDetail.pdID=Product.pdID AND
pdSupplier='天津南生'
4. 建立所有商品的商品编号和销售总量的视图。
CREATE VIEW E_Product(pdID, total)
AS
SELECT pdID, SUM(quantity)
FROM OrderDetail
GROUP BY pdID
虚拟列（SUM(quantity)）
派生的属性列
在基本表中并不实际存在，可以减少冗余
5. 创建一个名为 Student_Score_120136 的视图，包含班号为“ 120136” 的所有学生的学号、姓名、所选课程名称和成绩。数据首先按照学号升序排列，学号相同的按照课程名称升序排列。
create view Student_Score_120136
as
select top 100 percent student.sno ,sname,cname,score
from student,score,course
where student.sno= score.sno
and score.cno = course.cno
and clsno = '120136'
order by student.sno,sname;
【注】这里用到了top 100 percent
ORDER BY子句的查询不能用作表的表达式，其中表的表达式包括：视图、内联表值函数、子查询、派生表和共用表达式。select+order by在视图、子查询中的返回值不是表，而且是游标，所以会报错。
解决方案：只要我们在嵌套子查询视图里面加入: top 100 percent 即可
其中top 100 percent 的意思是：返回符合条件的100%的记录，即所有符合条件的记录
视图的删除
格式
DROP VIEW <视图名>
语句
删除视图TJ_Product。
DROP VIEW TJ_Product
视图的查询
说明
1. 从用户角度：查询视图与查询基本表相同
2. 视图是不实际存在于数据库当中的虚表
3. RDBMS实现视图查询的方法:视图消解
（1）进行有效性检查，检查查询的表、视图等是否存在。如果存在，则从数据字典中取出视图的定义
（2）把视图定义中的子查询与用户的查询结合起来，转换成等价的对基本表的查询
（3）执行修正后的查询
语句
（1）在视图TJ_Product中查找价格高于100元的商品信息。
SELECT pdID, pdName, pdPrice
FROM TJ_Product
WHERE pdPrice >100 ;
（2）在视图E_Product中查找销售总量高于100的商品编号、
SELECT pdID
FROM E_Product
WHERE total>100 ;
视图的更新
说明
（1）用户角度：更新视图与更新基本表相同
（2）由于视图是虚表，所以对视图的更新实际是转换成对基本表的更新。
（3）RDBMS实现视图更新的方法
视图消解法
（4）指定WITH CHECK OPTION子句后
DBMS在更新视图时会进行检查，防止用户通过视图对不属于视图范围内的基本表数据进行更新
（1）在视图BJCustomer中插入一个新元组(907,'李陵'，'15103124078')。
INSERT INTO BJCustomer
VALUES(907, '李陵', '15103124078') ;
（2）视将图TJ_Product中的商品价格提高10%
UPDATE TJ_Product
SET pdPrice= pdPrice*1.1 ;
视图的作用
1. 着重于特定数据
（1）让用户能够着重于他们所感兴趣的特定数据和所负责的特定任务
（2）增强了数据的安全性
2. 简化了数据操作
当视图中数据不是直接来自基本表时，定义视图能够简化用户的操作
(1)基于多张表连接形成的视图
(2)基于复杂嵌套查询的视图
(3)含导出属性的视图
3. 提供一定程度的逻辑独立性
如果用户程序是通过视图来访问数据库的，当数据库的逻辑结构发生改变时，只需要修改用户的视图定义，来保证用户的外模式不变，使得用户的程序不必改变。
SQL的数据操纵
一、插入数据
语法
INSERT INTO <基本表名> [(<列名1>,<列名2>,…,<列名n>)]
VALUES(<列值1>,<列值2>,…,<列值n>)
语句
向客户表中插入一个客户记录('907','林巍','北京'，'17705678942')。
INSERT INTO Customer
VALUES ('907','林巍','北京','17705678942')
在订单明细表OrderDetail中插入一个订单明细记录
('D201508201002006','11205', 47,2)。
INSERT INTO OrderDetail
VALUES('D201508201002006', '11205',47,2)
运行结果：操作失败
分析原因：违反了FOREIGN KEY 约束
二、删除数据
语法
DELETE FROM <表名> [WHERE<条件>]
语句
删除Customer中客户名称为“张小宁”的客户的
基本信息。
DELETE FROM Customer
WHERE custName= '张小宁'
删除Product中商品编号为“11206”的商品信息。
DELETE FROM Product
WHERE pdID= '11206'
运行结果：操作失败
原因：违反参照引用关系
三、更改数据
语法
UPDATE <表名>
SET <列名>=<表达式>[, <列名>=<表达式>]
[WHERE<条件>]
语句
将客户名称为“里奇”的所在城市改为“深圳”。
UPDATE Customer
SET custCity ='深圳'
WHERE custName='里奇'
将客户表中客户编号为“902”的客户编号改为
“910”。
UPDATE Customer
SET custID = '910'
WHERE custID = '902'
运行结果：Customer中客户编号为“902”的客户编
号成功改为“910”。同时订单表Orders中所有引用
“902”的元组的custID也同时更新为“910”。
SQL的数据查询
Select语句
一般格式：
SELECT [ALL | DISTINCT][TOP 表达式1 [PERCENT]] [WITH
TIES]
<列名或表达式> [列别名1] [,<列名或表达式> [列别名2]…]
[INTO 新基本表]
FROM <表名或视图名> [表别名1] [,<表名或视图名> [表别名2]…]
[WHERE <条件表达式1>]
[GROUP BY <列名1> [HAVING <条件表达式2>]]
[ORDER BY <列名2> [ASC | DESC]]
说明：
SELECT子句：指定查询结果要显示的结果列清单
FROM子句：指定查询结果的数据来源，即查询所涉及的基本表或视图
WHERE子句：指定从数据来源选取元组需要满足的条件
GROUP BY子句和HAVING子句：用于分组和分组过滤处理。
ORDER BY子句：决定查找出来的元组的排列顺序。
语句的执行过程：
从FROM子句指定的基本表或视图中，选取符合WHERE子句中指定的<条件表达式1>的元组，按SELECT子句中的目标列表，选出元组中的分量值形成结果表。
若有INTO子句，则创建新的基本表，并将查询结果存入新建的基本表中。
若有GROUP BY子句，则将符合<条件表达式1>的元组，按照指定的列名1的值分组，值相同的元组分在一组，每个组产生结果表中的一个元组；若有HAVING短语，则在分组结果中去掉不满足HAVING短语<条件表达式2>的分组。
若有ORDER BY子句，则结果表要根据指定的列名2的值按升序或降序排序。
一、简单查询
WHERE子句常用的查询条件：
比较：<、<=、>、>=、=、!=、 < >、!>、!<
确定范围：BETWEEN A AND B
NOT BETWEEN A AND B
确定集合：IN、NOT IN
字符匹配：LIKE，NOT LIKE
空值：IS NULL、IS NOT NULL
多重条件：AND、OR、NOT
简单（带条件）的查询
查询订单总金额超过5000元的订单信息
SELECT *
FROM Orders
WHERE orderAmount>5000 ;
使用BETWEEN AND的查询（包含等于）
查询价格不在100-500之间的商品编号。
SELECT pdID
FROM Product
WHERE pdPrice NOT BETWEEN 100 AND 500 ;
字符串匹配查询
形如：<列名> [NOT] LIKE <字符串常数>[ESCAPE <转义字符>]
<字符串常数>可以包含通配符
% (百分号) 代表任意长度（长度可以为0）的字符串
例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab等都满足该匹配串。
_ (下划线) 代表任意单个字符
例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串。
查询客户“里奇”的联系电话。
SELECT custPhone
FROM Customer
WHERE custName LIKE '里奇' ;
查询所有姓张的客户的基本情况。
SELECT *
FROM Customer
WHERE custName LIKE '张%' ;
查询客户名称第2个字为“立”的客户的基本情况。
SELECT *
FROM Customer
WHERE custName LIKE '_立%' ;
查询所有不姓贾的客户的基本情况。
SELECT *
FROM Customer
WHERE custName NOT LIKE '贾%' ;
查询所有客户名称以“张_”开头的客户的基本情况。
注：当用户要查询的字符串本身就含有 % 或 _ 时，要使用
ESCAPE ‘<转义字符>’ 短语对通配符进行转义。
SELECT *
FROM Customer
WHERE custName LIKE '张\_%' ESCAPE '\' ;
空值查询
查询缺少联系电话的客户名单。
SELECT custName
FROM Customer
WHERE custPhone IS NULL;
查询所有有联系电话的客户名单。
SELECT custName
FROM Customer
WHERE custPhone IS NOT NULL ;
使用IN查询
查询北京和广州的客户的基本情况。
SELECT *
FROM Customer
WHERE custCity IN ('北京', '广州') ;
等价于：
SELECT *
FROM Customer
WHERE custCity ='北京' OR custCity = '广州' ;
查询除了北京和广州之外，其余城市的客户的基本情况
SELECT *
FROM Customer
WHERE custCity NOT IN ('北京', '广州') ;
DISTINCT短语的使用
带DISTINCT的语句：
SELECT DISTINCT custCity
FROM Customer ;
注意：
DISTINCT短语的作用范围是所有目标列，在SELECT子句中只出现一次。
使用into的查询（存储查询结果）
查询所有北京客户的客户编号、客户名称和客户电话，并将其存入新基本表BJ_Customer中。
SELECT custD,custName,custPhone
INTO BJ_Customer
FROM Customer ;
带排序的查询
格式：
ORDER BY <列名1> [ASC | DESC][, <列名2> [ASC | DESC]…]
说明：
将SELECT语句的查询结果中所有元组按照要求的顺序排列
首先按第一个属性列值排列；
第一个属性列值相同者，再按下一个属性列值排列依次类推。
ASC，则表示该列值以升序排列；
DESC，则表示该列值以降序排列。省略不写，默认为升序排列。
查询所有商品的基本信息，并首先按照生产厂商升序排列，同一个生产厂商的商品按照价格降序排列。
SELECT *
FROM Product
ORDER BY pdSupplier, pdPrice DESC ;
TOP短语的使用
格式：[TOP 表达式 [PERCENT]] [WITH TIES]
说明：
1. 指定返回查询结果的“前”一组结果，该短语必须和ORDER BY一起使用。
2. 表达式指出返回多少结果
3. 如果同时选用了PERCENT则按百分比进行计算
4. 如果选用WITH TIES则返回与规定的最后一个元组具有相同排序值的其他所有行。
查询所有商品中库存数量最少的两种商品的基本信息。
SELECT TOP 2 WITH TIES *
FROM Product
ORDER BY pdStockSize ;
由出生日期计算年龄
查询班号为“ 120136” 的所有学生的学号、姓名和年龄，显示时使用别名“ 学号”、“ 姓名” 和 “ 年龄”，并将其存入新建表 Student_120136。
select
sno as '学号',sname as '姓名',year(getdate())-year(birthday) as '年龄'
into student_120136
from student ;
说明：getdate()为内置函数，是获取当前日期
集合运算
数据操作种类：
并操作(UNION)
交操作(INTERSECT)
差操作(EXCEPT )
注：参与集合操作的各查询结果必须是相容的，即列数必须相同，且对应属性列的数据类型也必须相同。
并
查询北京和上海的客户的基本信息。
SELECT *
FROM Customer
WHERE custCity='北京'
UNION
SELECT *
FROM Customer
WHERE custCity='上海' ;
交
查询“上海双喜”生产的价格在100元以上的商品信息。
SELECT *
FROM Product
WHERE pdSupplier='上海双喜'
INTERSECT
SELECT *
FROM Product
WHERE pdPrice>100 ;
差
查询包含编号为“11203”的商品但不包含编号为“11206”的商品的订单编号。
SELECT orderID
FROM OrderDetail
WHERE pdID='11203'
EXCEPT
SELECT orderID
FROM OrderDetail
WHERE pdID='11206' ;
二、连接查询
连接的操作执行过程
1. 首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。
2. 表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。
3. 重复上述操作，直到表1中的全部元组都处理完毕。
一般连接查询
查询北京客户的订单编号、客户名称、商品编号、商品名称和订购数量。
SELECT Order.orderID, custName, Product.pdID,
pdName, quantity
FROM Customer, Orders, Product, OrderDetail
WHERE Customer.custID=Orders.custID AND
Orders.orderID=OrderDetail.orderID AND
OrderDetail.pdID=Product.pdID AND
custCity='北京' ;
内连接查询（INNER JOIN…ON…）
用INNER JOIN…ON…重新实现上述查询北京客户的订单编号、客户名称、商品编号、商品名称和订购数量。
SELECT Order.orderID, custName, Product.pdID,
pdName, quantity
FROM Customer INNER JOIN Orders ON
Customer.custID=Orders.custID
INNER JOIN OrderDetail ON
Orders.orderID=OrderDetail.orderID
INNER JOIN Product ON
OrderDetail.pdID=Product.pdID
WHERE custCity='北京' ;
别名和自连接查询
查询和客户“环宇”在同一城市的客户的基本信息。
SELECT CA.*
FROM Customer CA, Customer CB
WHERE CA.custCity = CB.custCity AND
CB.custName='环宇' ;
说明：在一个表中的查询，将表通过命名的方式模拟为两个一模一样的表，在第一个表中取出满足第二个表中要求取到的数据（第二个表要求取到的数据就是题目的要求）
外连接查询
外连接与普通连接的区别
1. 普通连接操作只输出满足连接条件的元组
2. 外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出
3. 有利于更清晰的展现出表，比如商店的订单表，及时没有卖出去，也应该有商品的基本信息，用外连接要好一些
形如：
左外连接（LEFT JOIN…ON…）
右外连接（RIGHT JOIN…ON…）
查询所有客户订单的客户编号、客户名称、订单编号和送货地址(用左外连接实现)
SELECT Customer.custID, custName, orderID,
orderAddress
FROM Customer LEFT JOIN Orders ON
Customer.custID=Orders.custID ;
三、分组及汇总查询
对查询结果进行分组计算和汇总计算
1. 分组查询用GROUP BY子句完成
2. 计算的函数称为聚合函数
1. COUNT ( * )：统计元组个数
2. COUNT (< 列名>)：统计一列中值的个数
3. SUM (< 列名>)：计算一列值的总和
4. AVG (< 列名>)：计算一列值的平均值
5. MAX (< 列名>)：求一列中的最大值
6. MIN (< 列名>)：求一列值的最小值
说明：
1. 除COUNT（*）外，其它聚集函数在计算时会忽略空值。
2. 在<列名>前加入DISTINCT保留字，会将查询结果的列去掉重复值在计算。
带明细的分组汇总查询
GROUPING SETS短语：在显示汇总的同时也显示明细信息
解决SELECT子句的列名列表中只能出现分组属性和集函数，可以出现多列明细表
查询所有商品信息的订单明细，并分类汇总各种商品的销售数量。
SELECT pdID, orderID, SUM(quantity) as Total
FROM OrderDetail
GROUP BY GROUPING SETS ( pdID,
( pdID, orderID ) ) ;
一般汇总查询
统计客户所在城市的个数。（并将数据列进行命名）
SELECT COUNT（DISTINCT custCity）AS
custCityNumber
FROM Customer ;
查询订单的个数和总金额之和。
SELECT COUNT(*) AS orderNumber,
SUM(orderAmount) AS SumAmount
FROM Orders ;
使用GROUP BY的分组汇总查询
GROUP BY 子句
1. 把元组按某一指定列（或一些列）上的值相等的原则分组，然后再对每组数据进行规定的操作。
2. 对分组进行汇总可通过聚合函数实现
HAVING短语
（1）与WHERE子句的区别：
作用对象不同
WHERE子句作用于基表或视图，从中选择满足条件的元组。
HAVING短语作用于组，从中选择满足条件的组。
（2）集函数的条件关系必须用HAVING ， WHERE中不应出现集函数
查询每一种商品的销售总量。
SELECT pdID, SUM(quantity) AS Total
FROM orderDetail
GROUP BY pdID ;
查询每个城市的客户总数。
SELECT custCity, COUNT(*) AS custCount
FROM Customer
GROUP BY custCity ;
查询被购买3次以上的商品的商品编号和销售数量的最大值和最小值。
SELECT pdID, MAX(quantity) AS MAX_quantity,
MIN(quantity) AS MIN_quantity
FROM OrderDetail
GROUP BY pdID HAVING COUNT(*)>=3 ;
四、嵌套查询
定义：
将一个查询块(SELECT-FROM-WHERE)嵌套在另一个查询块的WHERE子句或HAVING短语的条件表达式中的查询 ，称为嵌套查询。
说明：
其中外层查询称为父查询，内层查询称为子查询。
执行过程:
是由里至外的，每一个子查询是在上一级查询处理之前完成的。
注意:
子查询中不能用ORDER BY 语句
内外层不相关的嵌套查询
查询编号为“D201005161628001”的订单的客户名称和联系电话。
SELECT custName,custPhone
FROM Customer
WHERE custID =
(SELECT custID
FROM Orders
WHERE orderID='D201005161628001') ;
查询比编为“D201006101628004”订单的订单金额还高的订单编号和客户编号。
SELECT orderID, custID
FROM Orders
WHERE orderAmount >
(SELECT orderAmount
FROM Orders
WHERE orderID='D201006101628004' ) ;
返回多值的子查询
使用ALL、ANY等谓词
ANY：任意一个值
ALL：所有值
需要配合使用比较运算符
> ANY 大于子查询结果中的某个值
> ALL 大于子查询结果中的所有值
< ANY 小于子查询结果中的某个值
< ALL 小于子查询结果中的所有值
>= ANY 大于等于子查询结果中的某个值
>= ALL 大于等于子查询结果中的所有值
<= ANY 小于等于子查询结果中的某个值
<= ALL 小于等于子查询结果中的所有值
= ANY 等于子查询结果中的某个值
=ALL 等于子查询结果中的所有值（通常没有实际意义）
!=（或<>）ANY 不等于子查询结果中的某个值
!=（或<>）ALL 不等于子查询结果中的任何一个值
查询比所有“天津南生”的商品价格都高的商品信息
SELECT *
FROM Product
WHERE pdPrice>ALL
(SELECT pdPrice
FROM Product
WHERE pdSupplier='天津南生'）
查询至少比“上海双喜”一种商品价格低的商品信息。
SELECT *
FROM Product
WHERE pdPrice<ANY
(SELECT pdPrice
FROM Product
WHERE pdSupplier='上海双喜')
查询至少订购了“上海双喜”一种商品的订单编号。
SELECT DISTINCT orderID
FROM OrderDetail
WHERE pdID=ANY
(SELECT pdID
FROM Product
WHERE pdSupplier='上海双喜')
内外层相关的嵌套查询
子查询的查询条件依赖于父查询，称为相关子查询
查询每个客户的具有最高总金额的订单信息
SELECT *
FROM Orders A
WHERE orderAmount =
( SELECT MAX(orderAmount)
FROM Orders B
WHERE B.custID = A.custID )
ORDER BY orderID ;
使用EXISTS的嵌套查询
说明：
形如：[NOT] EXISTS（子查询）
结果只与（子查询）的结果是否为空有关
若（子查询）结果为空，则 EXISTS返回“假”，
NOT EXISTS返回“真”；
若（子查询）结果为不空，则 EXISTS返回“真”，
NOT EXISTS返回“假”
查询订购了“羽毛球”的订单编号
SELECT DISTINCT orderID
FROM OrderDetail
WHERE EXISTS
( SELECT *
FROM Product
WHERE Product.pdID = OrderDetail.pdID AND
pdName= '羽毛球' )；
可能执行过程
首先取外层查询中OrderDetail表的第一个元组，根据它的pdID值处理内层查询
若在Product表中找到等于外层元组pdID值且pdName值为“羽毛球”的元组，则内层查询结果非空，EXISTS返回“真”值，OrderDetail的元组符合条件，将其orderID输出到结果表；
若在Product表中找不到符合条件的元组，则内层查询结果为空，EXISTS返回“假”值，OrderDetail的元组不符合条件，跳过。
取OrderDetail表的下一个元组，重复上述过程，直至OrderDetail表全部检索完毕。
2.检索北京客户的订单编号和送货地址。
SELECT orderID,orderAddress
FROM Orders
WHERE EXISTS
( SELECT *
FROM Customer
WHERE Customer.custID = Orders.custID AND
custCity= '北京') ;
3.查询没有订购编号为“11206”商品的订单编号。
SELECT orderID
FROM Orders
WHERE NOT EXISTS
( SELECT *
FROM OrderDetail
WHERE OrderDetail.orderID = Orders.orderID
AND pdID='11206' ) ;
-使用IN的等价查询
查询订购了“羽毛球”的订单编号。
SELECT DISTINCT orderID
FROM OrderDetail
WHERE pdID IN
( SELECT pdID
FROM Product
WHERE pdName= '羽毛球' ) ;
查询没有订购编号为“11206”商品的订单编号。
SELECT orderID
FROM Orders
WHERE orderID NOT IN
( SELECT orderID
FROM OrderDetail
WHERE pdID='11206' )
五、需要查询支持的数据操作
插入
语句格式：
INSERT INTO <基本表名> [(<列名1>，<列名 2>，……，<列名n>)] 子查询；
功能：
将子查询结果插入指定表中
说明：
如果列名序列省略，则子查询所得到的数据列必须和指定基本表的数据列完全一致。
如果列名序列给出，则子查询结果与列名序列要一一对应。
创建新数据表：商品销售总量表SalePd_sum(pdID,total)
CREATE TABLE SalePd_sum
( pdID varchar(6) PRIMARY KEY,
total int ) ;
向SalePd_sum表中插入每种商品的销售总量。
INSERT INTO SalePd_sum(pdID, total)
SELECT pdID, sum(quantity)
FROM OrderDetail
GROUP BY pdID ;
修改
将所有销售总量少于10的商品价格降低10%。
UPDATE Product
SET pdPrice= pdPrice *0.9
WHERE pdID NOT IN
( SELECT pdID
FROM OrderDetail
GROUP BY pdID
HAVING SUM(quantity)>=10) ;
根据订单明细中的订购数量×销售价格之和更新每个订单的总金额。
UPDATE Orders
SET orderAmount=
( SELECT SUM（quantity*pdSellPrice)
FROM OrderDetail
WHERE orderID=Orders.orderID ) ;
删除
删除905号客户的全部订单的订单细节。
DELETE FROM OrderDetail
WHERE orderID IN
( SELECT orderID
FROM Orders
WHERE custID='905') ;
