例如当account表与customer表进行连接时,开发过程

作者: www.9159.com  发布:2019-11-20

   嵌套子查询是SQL语句中比较常用的一种查询方法,开发过程中遇到查询需要将自己的某列作为待查询的数据,在参考别人的SQL语句的写法终于实现了自己需要的功能。

1. SQL读书笔记——SQL中的连接(1),sql读书笔记

 

《SQL学习指南》中的第5章    1.连接:      在某种机制下,需要将多个表中的数据进行整合到一起,即同一个查询的结果集中包含来自两个或者两个以上的表,这种机制被称为连接(join).    1.1 ANSI连接语法      本文采用的都是符合SQL92版本的ANSI SQL标准,所有的主流数据库都采用了SQL92的连接语法。由于这些数据库都出现SQL92标准发布之前,同时存在一些旧的连接语法,如下所示:      例子1:      SELECT e.fname, e.lname, d.`name`      FROM employee e , department d      WHERE e.dept_id = d.dept_id;          推荐使用SQL92标准 的连接方式,不推荐使用上面的连接方式,主要原因是:      1)连接条件和过滤条件被分隔到两个子句中(ON子句和WHERE子句),使得查询语句更利于理解;      2)每两个表的连接条件都在ON子句中列出,容易找出连接条件;      3)SQL92标准可以在各种数据库中通用,而旧的语法在不同的数据中的表现可能略有不同; 
         1.2 笛卡尔积      例子 1: 直接使用from连接employee 和 department表      方式一:      SELECT e.fname, e.lname, d.`name`      FROM employee e JOIN department             一个有18个雇员和3个部门,会产生54行数据,由于查询没有明确的指定两个表是如何连接的造成的,这时会默认两张表置换。这样的连接被称为交叉连接(cross join).  1.3 内连接            1)两表连接      例子1: 查询每个雇员所属的部门信息        SELECT e.fname, e.lname, d.`name`      FROM employee e JOIN department d           ON e.dept_id = d.dept_id            结果如图所示
     www.9159.com 1

 

           此时通过描述上面两个表是通过何种方式关联的,是通过dept_id来关联两个表的。      如果在一个表中的dept_id列中存在某个值,但该值在另一张表的dept_id列中不存在,那么此时相关的行的连接会失败,在结果集中的相关行的连接会失败,在结果集中将会排除包含该值的行,这种类型的连接被称为内连接     若想要包含某个表中的所有行,并不需要考虑该表的每一行都与另一张表匹配,那么可以使用外连接       2)多表内连接      -- 例子2.查询Woburn支行中所有熟练柜员(在2007年以前入职的柜员)开设的账户   SELECT a.account_id, a.product_cd,a.open_date FROM account a INNER JOIN employee e ON a.open_emp_id = e.emp_id INNER JOIN branch b ON e.assigned_branch_id = b.branch_id WHERE e.start_date < '2007-01-01' AND (e.title = 'Teller' OR e.title = 'Head Teller') AND b.`name` = 'Woburn Branch';   结果如图所示: www.9159.com 2

 

     现在FROM子句中包含3个表,两种连接类型和两个ON子句,看上去连接的顺序是account表,employee表,然后branch表,那么如果交换employ表和account表的连接顺序会出现什么情况呢?       SELECT a.account_id, a.product_cd,a.open_date FROM employee e INNER JOIN account a ON a.open_emp_id = e.emp_id INNER JOIN branch b ON e.assigned_branch_id = b.branch_id WHERE e.start_date < '2007-01-01' AND (e.title = 'Teller' OR e.title = 'Head Teller') AND b.`name` = 'Woburn Branch';   结果如图所示---无改变 www.9159.com 3 那么在更换employee和branch的顺序呢?   SELECT a.account_id, a.product_cd,a.open_date FROM branch b INNER JOIN account a ON a.open_branch_id = b.branch_id INNER JOIN employee e ON a.open_emp_id = e.emp_id WHERE e.start_date < '2007-01-01' AND (e.title = 'Teller' OR e.title = 'Head Teller') AND b.`name` = 'Woburn Branch';   结果如图所示---无改变 www.9159.com 4  那么更换ON语句的位置呢   SELECT a.account_id, a.product_cd,a.open_date FROM branch b INNER JOIN account a INNER JOIN employee e ON a.open_branch_id = b.branch_id ON a.open_emp_id = e.emp_id WHERE e.start_date < '2007-01-01' AND (e.title = 'Teller' OR e.title = 'Head Teller') AND b.`name` = 'Woburn Branch';   结果如图所示---报错!错误原因:ON语句连接关系要紧跟 JOIN语句 www.9159.com 5     例子二多种表达方式的原因:SQL是一种非过程化的语言,也就是说只需要描述要获取的数据对象, 而执行过程是数据库服务器负责(我只要结果集*_*)(但是左连接,右连接的问题,还不是很确定,稍后还要进行测试一下)   3)子查询作为结果集的内连接 例子二中的另一个版本:使用“连接 ”连接子查询,也就是将子查询的结果集作为查询表进行连接   1)       SELECT e.emp_id FROM employee e WHERE e.start_date < '2007-01-01' AND (e.title = 'Teller' OR e.title = 'Head Teller');   2)  SELECT b.branch_id FROM  branch b WHERE b.`name` = 'Woburn Branch';   3)  SELECT a.account_id, a.product_cd,a.open_date FROM account a INNER JOIN (SELECT e.emp_id FROM employee e WHERE e.start_date < '2007-01-01' AND (e.title = 'Teller' OR e.title = 'Head Teller') ) em ON a.open_emp_id = em.emp_id INNER JOIN (SELECT b.branch_id FROM  branch b WHERE b.`name` = 'Woburn Branch') br ON a.open_branch_id = br.branch_id;   4)自连接 例子3.列出雇员的姓名同时列出主管的姓名:   SELECT emp.emp_id AS emp_id,CONCAT(emp.fname,' ',emp.lname) AS em, e.emp_id AS super_em_id,CONCAT(e.fname,' ',e.lname) AS super_em FROM employee e INNER JOIN employee emp ON e.emp_id = emp.superior_emp_id;   结果如图所示:     www.9159.com 6     上面查询实现了对表自身进行连接,自连接,employee表中包含了指向自身的外键(superior_emp_id) 同时,employee表一共18行,但此查询返回了17行,这是由于银行的总经理MIchael Smith并没 自己的主管,它的superior_emp_id列为null,因此在改行上的内连接失败,在结果集中并不会显示。   5)不等连接 例子4.假如执行经理决定举办一次面向银行柜员的象棋比赛,现在要创建所有对弈者的列表      SELECT CONCAT(e.fname,' ',e.lname) as TEAM_A, 'VS', CONCAT(emp.fname,' ',emp.lname) as TEAM_B FROM employee e INNER JOIN employee emp ON e.emp_id < emp.emp_id WHERE e.title = 'Teller' AND emp.title = 'Teller'; 结果如图所示:  www.9159.com 7

 

                         

. SQL读书笔记——SQL中的连接(1),sql读书笔记 《SQL学习指南》中的第5章 1.连接: 在某种机制下,需要将多个表中的数据进行整合到一起,即...

SQL中的连接(2)——SQL读书笔记,sql读书笔记

 

《SQL学习指南》中的第10章     1.4 外连接       在多表连接中,存在连接条件可能无法表中所有的行匹配的问题,例如当account表与customer表进行连接时,会存在account表中cust_id列值无法匹配customer表中的cust_id列值,内连接在无法匹配成功时,是不会将这条匹配失败的结果放入返回的结果集中,假如我们需要强调某个表,也就是某个表的所有行必须返回,这个表的行数决定了返回结果集的行数。          1)左外连接           关键词:left outer join & left join ,left指出了连接了左边的表决定结果集的行数,而右边只负责提供与之匹配的列值。      例子1.         1)查询所有的商业客户账户 ——这是内连接             SELECT a.account_www.9159.com,id ,b.cust_id,b.`name`  FROM  account a INNER JOIN business b  ON a.cust_id = b.cust_id;        结果如图所示:
        www.9159.com 8

         2)查询所有的客户,但同时如果是商业客户,返回其客户名称。

                   SELECT a.account_id ,a.cust_id,b.`name` FROM  account a LEFT OUTER JOIN business b ON a.cust_id = b.cust_id;                     结果如图所示:           www.9159.com 9

            3)查询所有的客户,但同时如果是私人客户,返回其客户名称。

               SELECT a.account_id ,a.cust_id,  CONCAT(i.fname,' ',i.lname) AS gustname  FROM  account a INNER JOIN individual i  ON a.cust_id = i.cust_id;           结果如图所示:              www.9159.com 10

     2)右外连接

          关键词:right outer join & rigth join ,同理right指出了连接了右的表决定结果集的行数,而左边只负责提供与之匹配的列值。           1)           SELECT c.cust_id , b.`name`  FROM customer c  RIGHT JOIN business b           ON c.cust_id = b.cust_id;           结果如下图所示           www.9159.com 11

      3)多表外连接

                 1)获取所有的账户列表,其中包含个人客户的姓名以及商业客户的企业名称                   SELECT a.account_id,  CONCAT(i.fname,' ',i.lname) AS person_name, b.`name` as business_name FROM account a LEFT JOIN individual i ON a.cust_id = i.cust_id LEFT JOIN business b ON a.cust_id = b.cust_id ;                    结果如下图所示           www.9159.com 12

               SELECT COUNT(*) FROM account a;

          结果如下           www.9159.com 13

              上面多表进行外连接,以account为主表,去匹配individual表中用户,若匹配不成功,显示为null;再去匹配business表中用户,同理匹配不成功,显示为null.同时说明了外连接中,存在以哪个表为主表,所以主表的顺序是不可变,解决上一次笔记中的遗留问题:内连接与表的顺序无关,但是外连接要注意主表所在的位置。

                  通过子查询的方式实现上面的三个表同时进行的自连接:           SELECT indi.id, person_name,bussiness_name FROM (     SELECT a.account_id AS id, CONCAT(i.fname,' ',i.lname) as person_name     FROM account a     LEFT JOIN individual i   ON a.cust_id = i.cust_id )  AS indi INNER JOIN (     SELECT a.account_id AS id, b.`name` AS bussiness_name     FROM  account a     LEFT JOIN business b     ON a.cust_id = b.cust_id ) AS busi ON indi.id =  busi.id; 结果如下图所示         www.9159.com 14        4)自外连接                  1)前面通过内连接实现的一个问题:获取雇员及其主管的信息          SELECT e.emp_id,CONCAT(e.fname,' ',e.lname) AS emp_name, em.emp_id AS su_emp_id,CONCAT(em.fname,' ', em.lname) AS superior_name FROM employee e INNER JOIN employee em ON e.superior_emp_id = em.emp_id;   结果如下图所示: www.9159.com 15

 

上面的结果由于是内连接的原因,雇员信息会缺失一条,因为总经理是最高级别职员,没有上级,所以匹配不成功,没有在结果集中显示。下面通过左连接的方式,可以解决这个问题。           SELECT e.emp_id,CONCAT(e.fname,' ',e.lname) AS emp_name, em.emp_id AS su_emp_id,CONCAT(em.fname,' ', em.lname) AS superior_name FROM employee e LEFT JOIN employee em ON e.superior_emp_id = em.emp_id;   结果如下图所示: www.9159.com 16

 

       将上面的左连接更改成右连接,这时候就是获取每个主管的下属职员          SELECT e.emp_id,CONCAT(e.fname,' ',e.lname) AS emp_name, em.emp_id AS su_emp_id,CONCAT(em.fname,' ', em.lname) AS superior_name FROM employee e RIGHT JOIN employee em ON e.superior_emp_id = em.emp_id; 结果如下图所示: www.9159.com 17

当某个主管下面存在n个职员时,这样就会在该主管对应的数据增加n-1条数据,这也就是18个雇员,为什么查出的结果集中有28条数据的。

                                                       

《SQL学习指南》中的第10章 1.4 外连接 在多表连接中,存在连接条件可能无法表中所有的行匹配...

        查询语句如下:

        SELECT DISTINCT dbo.a.ProxyID, dbo.a.account AS adminAccount, dbo.b.LevelName, dbo.a.ProfitProportion, dbo.a.totalUpScore, dbo.a.score, dbo.a.members, dbo.a.authority, dbo.a.registerDate, dbo.a.freezeState, temp.belongName

FROM dbo.a INNER JOIN dbo.b ON dbo.a.ProxyLevel = dbo.b.LevelId INNER JOIN (SELECT   dbo.c.BelongsAgent, a.ProxyID, a.account AS belongName FROM dbo.a AS a INNER JOIN dbo.c ON a.ProxyID = BelongsAgent AND a.ProxyID = dbo.c.BelongsAgent) AS temp ON dbo.a.belongsAgent = temp.BelongsAgent

        查询结果如下:

        www.9159.com 18

        参考:

 

本文由9159.com发布于www.9159.com,转载请注明出处:例如当account表与customer表进行连接时,开发过程

关键词:

上一篇:没有了
下一篇:没有了