在这章中，你将写代码，许多的将运行在一个人力资源数据库这是一个假想的胶乳橡胶制造公司其他的代码在本章中将运行在Northwind 首先创建数据库表
CREATE TABLE Departments
(
Deptno int NOT NULL
CONSTRAINT PK_dept_deptno PRIMARY KEY,
deptname varchar(15) NOT NULL
)
CREATE TABLE Jobs
(
jobid int NOT NULL
CONSTRAINT PK_jobs_jobid PRIMARY KEY,
jobdesc varchar(15) NOT NULL
)
CREATE TABLE Employees
(
empid int NOT NULL
CONSTRAINT PK_emps_empid PRIMARY KEY,
empname varchar(10) NOT NULL,
deptno int NULL
CONSTRAINT FK_emps_depts
REFERENCES Departments(deptno),
jobid int NOT NULL
CONSTRAINT FK_emps_jobs REFERENCES Jobs(jobid),
salary decimal(7,2) NOT NULL
)
INSERT INTO Departments VALUES(100, 'Engineering')
INSERT INTO Departments VALUES(200, 'Production')
INSERT INTO Departments VALUES(300, 'Sanitation')
INSERT INTO Departments VALUES(400, 'Management')
INSERT INTO Jobs VALUES(10, 'Engineer')
INSERT INTO Jobs VALUES(20, 'Worker')
INSERT INTO Jobs VALUES(30, 'Manager')
INSERT INTO Jobs VALUES(40, 'Cleaner')
INSERT INTO Employees VALUES(1, 'Leo', 400, 30, 10000.00)
INSERT INTO Employees VALUES(2, 'George', 200, 20, 1000.00)
INSERT INTO Employees VALUES(3, 'Chris', 100, 10, 2000.00)
INSERT INTO Employees VALUES(4, 'Rob', 400, 30, 3000.00)
INSERT INTO Employees VALUES(5, 'Laura', 400, 30, 3000.00)
INSERT INTO Employees VALUES(6, 'Jeffrey', NULL, 30, 5000.00)
看人力资源数据库的架构
由于这里上传图片比较麻烦我就不把结构图放上来啦
内部连接
缩写形式的select语句
select
<select_list>
FROM
<table_source>
[where
<search_condition>]
在OLD-STYLE语法中，内部连接的情况下，是sql-89兼容，<table-source>包含列表以逗号分隔的表和<search_condition>包含连接条件见下sql
SELECT
<select_list>
from
T1,T2
WHERE
<join_condition> [AND <filter>]
如果你想获得员工和部门信息，你写查询语句如下
SELECT
empid,
empname,
salary,
E.deptno,
deptname
FROM
Employees AS E,
Departments AS D
WHERE
E.deptno = D.deptno
SQL−92 连接语法
SELECT
<select_list>
FROM
T1
<join_type> JOIN
T2 [ON <join_condition>]
[<join_type> JOIN
T3 [ON <join_condition>]
[WHERE
<filter>]
SQL−92 Two−Way 内连接
下面是92语法缩写形式
SELECT
<select_list>
FROM
T1
[INNER] JOIN
T2 ON <join_condition>
[WHERE
<filter>]
INNER 可以写也可以不写 因为他是默认的连接类型，你可以写如下的查询语句 92语法
select empid,empname,salary,E.deptno,deptname
from
Employees as E
join
Departments as d on E.deptno = D.deptno
如果你想添加工作简要信息你也可以添加查询
SELECT
empid,
empname,
salary,
E.deptno,
deptname,
E.jobid,
jobdesc
FROM
Employees AS E,
Departments AS D,
Jobs AS J
WHERE
E.deptno = D.deptno
AND
E.jobid = J.jobid
SQL−92 Three−Way Inner Joins
SELECT
empid,
empname,
salary,
E.deptno,
deptname,
E.jobid,
jobdesc
FROM
Employees AS E
JOIN
Departments AS D ON E.deptno = D.deptno
JOIN
23
Jobs AS J ON E.jobid = J.jobid
一个常见的问题,程序员询问的关于加入多于两个表的顺序是否影响查询的性能
在内部连接中不会有影响，
查询处理器会意识到这一点，会决定内部顺序访问根据表成本估计的
交叉查询 Cross Joins
交叉连接产生的笛卡尔乘积表进行
SELECT
deptname,
jobdesc
FROM
Departments,
Jobs
92语法
SELECT
deptname,
jobdesc
FROM
Departments
CROSS JOIN
Jobs
Outer Joins 外部连接查询
外部连接使你能够定义任意一个或两个参与连接的表，这意味着重这两个表中返回匹配的行，同时也会返回表中没有匹配的行返回的行用null来代替，sql-89不支持该语法
Listing 1−13: Old−Style Outer Join Syntax
SELECT
<select_list>
FROM
T1,
T2
WHERE
T1.key_col {*= | =*} T2.key_col [AND <filter>]
如果星号是等号左边，表的联接条件的左侧是保留表。这被称为左外部联接。
让我们在生产员工和部门信息，保留所有的 old−style 语法编写一个查询从雇员表中，行即使有没有匹配的部门，如清单 1−14 显示。
SELECT
*
FROM
Employees AS E,
Departments AS D
WHERE
E.deptno *= D.deptno
SQL−92 Two−Way Outer Joins
SELECT
<select_list>
FROM
T1
{LEFT | RIGHT | FULL} [OUTER] JOIN
T2 ON <join_condition>
[WHERE
<filter>
SQL−92 Two−Way Left Outer Joins
SELECT
*
FROM
Employees AS E
LEFT OUTER JOIN
Departments AS D ON E.deptno = D.deptno
此时员工会全部查询出来当遇到员工没有对应的部门的时候将用null替换
如果你想保留表Departments所有行，则你可以写一个简单的右查询
SELECT
*
FROM
Employees AS E,
Departments AS D
WHERE
E.deptno =* D.deptno
SQL−92 Two−Way Right Outer Joins
SELECT
*
FROM
Employees AS E
RIGHT OUTER JOIN
Departments AS D ON E.deptno = D.deptno
如果你想保留所有的人员和所有的部门你可以写一个查询语句
Listing 1−19: SQL−92 Two−Way Full Outer Join
SELECT
*
FROM
Employees AS E
FULL OUTER JOIN
Departments AS D ON E.deptno = D.deptno
你想查询出所有员工工作和和员工信息，同时查看部门信息则可以写如下sql
SELECT
*
FROM
Employees AS E
LEFT OUTER JOIN
Departments AS D ON E.deptno = D.deptno
RIGHT OUTER JOIN
Jobs AS J ON E.jobid = J.jobid
左和右外部联接，与表在查询中的显示的顺序确定的顺序及其处理
它们的顺序会影响输出顺序，但是并不影响整个输出，所以不能确定他们的处理顺序
--------考虑到比较乱所以重新整理-----------
知识点描述 一下全部是个人看本书的一些理解如果有不全的地方请各位大牛指出谢谢
1 sql 每个步骤都会产生一个虚拟表，作为下一个步骤的数据源，同时这些虚拟表对拥有者是没有权限操作的。只有最后返回表才属于拥有者
Customers/Orders场景下的查询
分析如下sql（该查询返回Maidrid且订单数少于3的消费者，并包含他们的订单数。查询结果排序，重小到大。
SELECT C.customerid,count(O.orderid) as numorders
FROM dbo.Customers as c
left outer join dbo.Orders as O
ON C.customerid = O.customerid
where c.city = 'Madrid'
GROUP BY C.customerid
HAVING COUNT(O.orderid) < 3
ORDER BY NUMORDERS;
逻辑查询步骤详解释
1 执行笛卡尔
对from子句的前两个表执行笛卡尔生成虚拟表VT1
VT1表结构
VT1行
VT1 左边表的行和右边表的行的每个可能的组合都包含一行。如果左表包含m行 右表包含n行则总共VT1表可以产生m*n行
VT1的列结构
VT1的列由该列的原表名限定。
步骤 2 应用ON帅选器
查询帅选器有三个ON、WHERE、HAVING
ON帅选器中的逻辑表达式被应用到上一步返回的虚拟表中的所有行。
只有连接条件为true的那些行才会包含在步骤2返回的虚拟表VT2中
三值逻辑
sql中的逻辑表达式的可能值包括TRUE FALSE UNKNOWN. 他们被称为三值逻辑。
sql中的UNKNOWN逻辑值通常出现在包含null值的逻辑表达式中如 下面表达式的逻辑值都是unknown null > 42 null = null;
2 not unknown 还是等于 unknown
UNKNOWN逻辑结果和null在不同的语言元素中被区别对待。
查询帅选器都把unknown当作false处理。
check约束中的unknown值被当做true对待
如果表中有一列定义了unique约束，将无法向表中插入该列值为null的两行
group by 子句把所有的null值分到一组
order by 子句把所有的null值排列在一起
步骤3 添加外部行
通过添加一个外部关联标识可以把其中一个或者两个表作为保留表。保留表则返回所有行。
这一步会生成虚拟表VT3
步骤4 应用where帅选器
对上一步虚拟表中的所有行进行where帅选只有符合条件的才会返回生产虚拟表VT4
注意因为数据还没有分组，现在还不能使用聚合帅选器。
ON 在添加外部行（步骤三 外部连接）之前应用，而where是在步骤三之后才应用。ON 帅选器对保留表中的部分行的移除并不是最终的。因为步骤三会把这些行添加回来。而where帅选器对行的移除是最终的。
5 分组
group by 子句中列表的每个唯一值组合为一组，上一步返回的每个基行被分配到一个组，且仅仅分配到一个组。得到虚拟表VT5 VT5由两部分组成实际组构成组部分和上一步返回的基行构成一部分。
如果在查询中指定啦group by 子句 则后面的所有步骤只能指定可以为成组得到标量值的表达式。
这一阶段认为两个null是相等的也就是说所有的null会被分配到一起
group by 子句的输入是上一步返回的虚拟表。 如果你指定group by all 在第四部被移除的组将被添加到这一步的结果虚拟表其中的原始部分包含空集合。
步骤6
步骤7
只有符合having 条件的才会成为这一步返回的虚拟表VT6的一部分，HAVING 是第一个也是唯一一个应用到已分组数据的帅选器
步骤8 处理select列表
select列表中的表达式可以是由上一步返回的虚拟表的基列，也可以说对这些列的操作。
前面曾经提到过，如果查询是一个聚合查询，在第五步以后你只能引用上一步所返回的虚拟表中那些组成部分指定的基列（group by 列表）如果你引用啦原始部分中的列必须进行聚合运算。
