帆软SQL语句巩固测试题

巩固SQL语句，有一定基础的可以直接看第二部分，若有错误，欢迎指出，谢谢。相关的原始表可以参考帆软的论坛。

第一部分SQL语句学习

单表查询

1. 查询订购日期在1996年7月1日至1996年7月15日之间的订单的订购日期、订单ID、客户ID和雇员ID等字段的值

#指定列查询
select 订单ID,客户ID,雇员ID,订购日期 from 订单 where 订购日期 between '1996-07-01' and '1996-07-15';
#根据条件全查询
select * from 订单 where 订购日期 between '1996-07-01' and '1996-07-15';

效果如下：

2. 查询供应商的ID、公司名称、地区、城市和电话字段的值。条件是“地区等于华北”并且“联系人头衔等于销售代表”。

select 供应商ID,公司名称,地区,城市,电话 from 供应商 where 地区='华北' and 联系人职务='销售代表';

效果如下：

3. 查询供应商的ID、公司名称、地区、城市和电话字段的值。其中的一些供应商位于华东或华南地区，另外一些供应商所在的城市是天津

#第一种方法 or
select 供应商ID,公司名称,地区,城市,电话 from 供应商 where 地区='华东' or 地区='华南' or 城市='天津';
#第二种方法 or in()
select 供应商ID,公司名称,地区,城市,电话 from 供应商 where 地区 in('华东','华南') or 城市='天津';

效果如下：

4. 查询位于“华东”或“华南”地区的供应商的ID、公司名称、地区、城市和电话字段的值

select 供应商ID,公司名称,地区,城市,电话 from 供应商 where 地区 in('华东','华南');

效果如下：

多表查询

1. 查询订购日期在1996年7月1日至1996年7月15日之间的订单的订购日期、订单ID、相应订单的客户公司名称、负责订单的雇员的姓氏和名字等字段的值，并将查询结果按雇员的“姓氏”和“名字”字段的升序排列，“姓氏”和“名字”值相同的记录按“订单 ID”的降序排列

#方法一：inner join ... on
select 订单ID,公司名称,姓氏,名字,订购日期 from 订单 d inner join 客户 k on d.客户ID=k.客户ID inner join 雇员 g on d.雇员ID=g.雇员ID where 订购日期 between '1996-07-01' and '1996-07-15' order by 姓氏,名字 asc,订单ID DESC;

#方法二：where
#采用这种方法，若是后面不加条件（where）,则会出现笛卡尔积的效果。

#缺点：在下面语句中，实际上是创建了两张表的笛卡尔积，所有可能的组合都会被创建出来。列如：在笛卡尔连接中，如果有1000顾客和1000条销售记录，这个查询会先产生1000000个结果（乘积），然后通过正确的 ID过滤出1000条记录。 这是一种低效利用数据库资源，数据库多做100倍的工作。 在大型数据库中，笛卡尔连接是一个大问题，对两个大表的笛卡尔积会创建数10亿或万亿的记录。
select 订单ID,公司名称,姓氏,名字,订购日期 from 订单,客户,雇员 where 订单.客户ID=客户.客户ID and 订单.雇员ID=雇员.雇员ID and 订购日期 between '1996-07-01' and '1996-07-15' order by 姓氏,名字 asc,订单ID DESC;

为了避免笛卡尔积应该使用第一种方法：inner join

优点：使用inner join 这样数据库就只产生等于ID 的1000条目标结果。增加了查询效率。

有些数据库系统会识别出 WHERE连接并自动转换为 INNER JOIN。在这些数据库系统中，WHERE 连接与INNER JOIN 就没有性能差异。但是， INNER JOIN 是所有数据库都能识别的，因此DBA会建议在你的环境中使用它。

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。

LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。

RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

补充说明：
1.order by 多个字段，每个字段后面都有排序方式，默认ASC，可以省略不写。

2.inner join等同于join。
效果如下：

2.查询“10248”和“10254”号订单的订单ID、运货商的公司名称、订单上所订购的产品的名称

#第一种方法：用括号括住表名
select 订单.订单ID,公司名称,产品名称 FROM((订单 inner join 订单明细 on 订单.订单ID = 订单明细.订单ID)inner join 运货商 on 订单.运货商 = 运货商.运货商ID)inner join 产品 on 订单明细.产品ID = 产品.产品ID where 订单.订单ID IN(10248,10254);

#第二种方法：没有用括号括住表名
select 订单.订单ID,公司名称,产品名称 FROM 订单 inner join 订单明细 on 订单.订单ID = 订单明细.订单ID inner join 运货商 on 订单.运货商 = 运货商.运货商ID inner join 产品 on 订单明细.产品ID = 产品.产品ID where 订单.订单ID IN(10248,10254);

#第三种方法：
SELECT 订单.订单ID,公司名称,产品名称 FROM (订单,订单明细,运货商,产品) WHERE 订单.订单ID = 订单明细.订单ID AND 订单.运货商 = 运货商.运货商ID AND 订单明细.产品ID = 产品.产品ID and 订单.订单ID IN(10248,10254);

1.出现这种错误有可能是因为多表查询时没有用括号括住表名或者联合的表中没有该列内容

更多多表查询规格：多表查询

2.以下情况是因为多个表中有重复的列，查询时没有指定表面，不知道要查询哪个列，指定表名即可解决

效果如下：

3. 查询“10248”和“10254”号订单的订单ID、订单上所订购的产品的名称、数量、单价和折扣

#第一种方法：join ... on 两张表
#（自己测试，结果和三张表一样，借鉴别人的用的是三张表，目前没有看到两者的区别在哪里）
select 订单明细.订单ID,产品名称,数量,订单明细.单价,折扣 from 订单明细 join 产品 on 产品.产品ID=订单明细.产品ID where 订单明细.订单ID IN(10248,10254);

#第二种方法：inner join ... on 三种表
select 订单.订单ID,产品名称,数量,订单明细.单价,折扣 from 订单,订单明细,产品 where 订单.订单ID=订单明细.订单ID and 产品.产品ID=订单明细.产品ID and 订单.订单ID IN(10248,10254);

效果如下：

4. 查询“10248”和“10254”号订单的订单ID、订单上所订购的产品的名称及其销售金额

select 订单明细.订单ID,产品名称,数量*订单明细.单价*(1-折扣)<