select name from salesreps where office in (select office from offies where sales > target)
//列出订单大于2500元的产品名称[exists测试] select description from products where exists (
select * from orders where product=prodct_id and amount > 2500.00 )
//列出完成销售目标10%的销售人员清单[any测试]
select name from salesreps where (0.1* quota) < any(select amount from orders where rep=empl_num)
----------------------------------------------------------- 2.3连接查询Table Joins
多表连接类型可分为三类(内/外/交叉连接)
主从表或者父子表进行多表连接多以主键和外键进行关联 Outer joins(LEFT OUTER, RIGHT OUTER, and FULL OUTER joins)
left outer join:查询的结果以左边表行数为准 right outer join:查询的结果以右边表行数为准
2.3.1.内连接inner join 功能:
语法:
SELECT select_list FROM table_1 [INNER] JOIN table_2 ON join_condition_1 [[INNER] JOIN table_3 ON join_condition_2]... 代码:
//没有where子句的内连接 SELECT * FROM Products INNER JOIN Suppliers
ON Products.SupplierID = Suppliers.SupplierID
//有where子句的内连接 SELECT
p.ProductID,
s.SupplierID,
p.ProductName,
s.CompanyName FROM Products p INNER JOIN Suppliers s ON p.SupplierID = s.SupplierID WHERE p.ProductID < 4
----------------------------------------------------------- 2.3.2.外连接outer join
功能:包括三种连接LEFT OUTER, RIGHT OUTER, and FULL OUTER joins
left outer :查询的结果以左边表行数为准 right outer :查询的结果以右边表行数为准
语法:select ... from table1 [left/right/full outer join ]table2 where ... 代码:
//以Customers表行数为标准去连接Orders表 SELECT c.CustomerID, CompanyName FROM Customers c
LEFT OUTER JOIN Orders o ON c.CustomerID = o.CustomerID WHERE o.CustomerID IS NULL
----------------------------------------------------------- 2.3.3.交叉连接cross join
功能:以主从表或者父子表之间的主键进行连接，最终以笛卡尔乘积运算的结果
语法:select ... from table1 cross join table2 where ...
代码:
//显示结果以表1行数*表2行数 假设Departments为4行记录 假设Jobs为3行记录
下面的显示结果为4*3=12行记录
SELECT deptname,jobdesc FROM Departments CROSS JOIN Jobs
//用关键字匹配的交叉连接 oc_head/oc_detail是主从表 oc_head(主键oc_number)
oc_detail(主键oc_number,item_number,ship_date)
SELECT h.customerid,d.item_number,d.ship_date from oc_head as h CROSS JOIN oc_detail as d where h.oc_number=d.oc_number
----------------------------------------------------------- 2.4汇总查询Group Query
//汇总查询相当于会计报表中的小计汇总的功能
语法: select ... from group by
[having search expression] 代码:
//求出每名销售人员的销售金额
select rep,sum(amount) from orders group by rep //每个销售点分配了多少销售人员
select rep_office,count(*) from salesreps group by rep_office //计算每名销售人员的每个客户和订单金额
select cust,rep,sum(amount) from orders group by cust,rep //Having子句应用
select rep,avg(amount) from orders having sum(quota) > 3000.00
/**********************************************************/
3.数据修改DATA MODIFY LANGUAGE 3.1插入数据Insert 3.2修改数据Update 3.3删除数据Delete
----------------------------------------------------------- 3.1插入数据Insert 3.1.1.单行插入 语
法
:insert
into
[