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