SQL多表连接查询..docx

/woshisap/article/details/7348676SQL多表连接查询与集合的并、交、差运算查询 use?db_sqlserver2??select?姓名,?工资,?面积,?金额,?(工资+金额/1000)?as?实发工资?from?职工,仓库,?订购单??where?职工.职工号=订购单.职工号?and?职工.仓库号=仓库.仓库号?select?姓名,?工资,?城市,?(select?AVG(工资)?from?职工)?as?平均工资?,?(工资-(select?AVG(工资)?from?职工))?as?与平均工资之差?from?职工,?仓库?where?仓库.仓库号=职工.仓库号?5：带保存功能的多表连接查询 在SQL语句中，利用into语句可以把查询到的结果保存成一张新表，然后再对新表进行数据信息查询。?select?仓库.仓库号,?城市,?面积,?姓名,?工资,?金额?into?多表连接产生的新表?from?仓库,?职工,?订购单?where?仓库.仓库号=职工.仓库号?and?职工.职工号=订购单.职工号?select?*?from?多表连接产生的新表?//查看不同仓库中的所有职工的仓库号、平均销售金额、最大销售金额、最小销售金额、最大销售金额与最小销售金额之差的信息[sql] /woshisap/article/details/7348676view plain/woshisap/article/details/7348676copy/woshisap/article/details/7348676print/woshisap/article/details/7348676?select?仓库号,?AVG(金额)?as?平均销售金额,?MAX(金额)?as?最大销售金额,?MIN(金额)?as?最小销售金额,(MAX(金额)?-?MIN(金额))?as?最大金额与最小金额之差?from?多表连接产生的新表?group?by?仓库号;??可以把分组查询结果再生成一张新表[sql] /woshisap/article/details/7348676view plain/woshisap/article/details/7348676copy/woshisap/article/details/7348676print/woshisap/article/details/7348676?select?仓库号,?AVG(金额)?as?平均销售金额,?MAX(金额)?as?最大销售金额,?MIN(金额)?as?最小销售金额,(MAX(金额)?-?MIN(金额))?as?最大金额与最小金额之差?into?分组查询产生的新表??from?多表连接产生的新表?group?by?仓库号;????select?*?from?分组查询产生的新表?6： 内连接查询(inner join) 使用比较运算符对表之间的某些数据进行比较，并列出这些表中与连接条件相匹配的数据行。?[sql] /woshisap/article/details/7348676view plain/woshisap/article/details/7348676copy/woshisap/article/details/7348676print/woshisap/article/details/7348676?select?姓名,?城市?from?仓库?inner?join?职工?on?职工.仓库号=仓库.仓库号??多表的内连接查询?[sql] /woshisap/article/details/7348676view plain/woshisap/article/details/7348676copy/woshisap/article/details/7348676print/woshisap/article/details/7348676?select?城市,面积,?姓名,?工资,?金额?from?仓库??inner?join?职工?on?职工.仓库号=仓库.仓库号??inner?join?订购单?on?职工.职工号=订购单.职工号??and?工资1800?and?面积1000?and?金额?!=?16600?? 7：左连接查询(left join) 除满足连接条件的记录显示外，第一张表中不满足条件的记录也显示在结果集中。select?姓名,?城市?from?仓库??left?join?职工?on?职工.仓库号=仓库.仓库号?and?城市?is?not?null?and?姓名?like?%王%??[sql] /woshisap/article/details/7348676view plain/woshisap/article/details/734867