经典表关联与多表查询
经典表关联与多表查询
目的：
1.掌握从多个表查询数据的基本知识
2.了解和学习外连接(out join)
3.掌握内连接
授课内容：
1.对多于一个表的数据查询
1.1现实情况中，在数据库应用中，数据存在于多个相关联的表中。基本上没有数据只
存在于一个表中的情况。小的应用系统一般也有十几个表，大型系统一般有上千个表。
1.2你经常要作的就是在多个表中进行数据查询。
1.3Oracle对多表查询使用表连接的技术（table join）
1.4表连接的基本条件：
(1)2个表必须有公共字段（同名字段或不同名字段）
(2)在一个表中，这个公共字段必须是主键（PK）
1.5二个表中的公共字段，在一个表中是主键，在另外一个表中就是外键(FK)。
1.6二表关联中，公共字段是主键的表称为父表（主表）。是外键的表称为子表（详细
表）。
1.7研究一下scott下的emp和dept表的关系。
1.8研究一下oe下的表：
CATEGORIES_TAB
CUSTOMERS
INVENTORIES
ORDERS
ORDER_ITEMS
PRODUCT_DESCRIPTIONS
PRODUCT_INFORMATION
1.9多表查询的语法
select 子句
from 表1[ 别名]，表2[ 别名]，视图[ 别名]，（select 子句）别名
where 连接语句and 其他条件语句
[oupy by 分类项目]
[having 子句]
[order by 子句]
1.10任务：查询每个员工的编号，姓名，部门名称，部门位置
select empno,ename, dname,loc
from emp a,dept b
where a.DEPTNO=b.DEPTNO
1.11多表查询的原则：对N个表连接，至少要有N-1个相等的条件。而且每个表的公
共字段必须出现一次。
1.12多表关联中，如果没有指定关联等式，将产生无效的结果，它将每个关联的表的
记录跟其他表的所有记录组合，产生笛卡尔积的数据。
测试：
select empno,ename, dname,loc
from emp a,dept b
1.13对OE用户的测试
查询公司库存信息，显示仓库名称，产品名称，库存数量，库存金额
select c.WAREHOUSE_NAME, b.PRODUCT_NAME,
a.QUANTITY_ON_HAND,a.QUANTITY_ON_HAND*
b.LIST_PRICE
from INVENTORIES a,PRODUCT_INFORMATION b,WAREHOUSES c
where a.WAREHOUSE_ID=c.WAREHOUSE_ID and a.PRODUCT_ID =b.PRODUCT_ID
2.内连接(self join)
1.当多表关联使用一个表进行数据进行数据查询，这种连接叫自连接。
2.自连接的主要功能是查询表中除了主键外，是否有重复的记录。
3.任务：查询员工表中，有同名，职位相同的员工信息（编号，项目，职位，工
资）
select a.empno, a.ename, a.job
from emp a, emp b
where a.empno<>b.empno and a.deptno=b.deptno and a.job=b.job
4.日常生活中在数据录入时产生的错误
(1)由于工作失误，一个数据录入到系统2次或多次。
(2)一般在进行自动的数据导入时，产生大量的重复记录。
5.子连接的要求：
自连接至少要2个或2个以上的等式条件，一个用于关联，其他用于表示重复的
数据。
3.外连接(out join):
1.1内连接是关联的表的公共字段值必须相同，所有不同的值的记录都没有了。
1.2外连接是值一个表的中的公共字段的值可以不与另一个表的公共字段值相同。一般
时它是null.
1.3任务：查询员工表，显示员工的项目，部门名称，部门位置，要求显示所有的员工，
即使员工没有部门。
select a.ename,b.dname,b.loc
from emp a left outer join dept b
on a.deptno=b.deptno
注：此任务无法使用正常的内连接。因为有一个员工没有部门，它的部门编号为空。
常见的任务如：信息系统中的文档，申请审批，当刚创建时，所有审批信息为null.. 但有的审批已经完成。如果与审批人表关联的话，要显示所有的申请，就必须使用外连接。
1.4外连接语法：
（1）左连接：取出左边的表的所有记录
select 子句
from 表1 left outer join 表2
on 表1.公共字段＝表2.公共字段
（2）右连接: 取出右边表的所有记录
select 子句
from 表1 right outer join 表2
on 表1.公共字段＝表2.公共字段
（3）全连接（左右连接）：左右两边的表的记录都取。
select 子句
from 表1 full outer join 表2
on 表1.公共字段＝表2.公共字段
select a.empno,a.deptno,b.deptno,b.dname
from emp a left outer join dept b
on a.deptno=b.deptno
select a.empno,a.deptno,b.deptno,b.dname
from emp a right outer join dept b
on a.deptno=b.deptno
select a.ename,b.dname,b.loc
from emp a full outer join dept b
on a.deptno=b.deptno
select a.dname, b.ename
from dept a full outer join emp b
on a.deptno=b.deptno
一般情况下，不使用上述的语法，而使用如下的语法：
select a.dname, b.ename
from dept a,emp b
where a.deptno(+)=b.deptno --
一般情况情况下，(+)放在关联表的主键的一侧，才有实际的意义。
没有(+)的表的取所有的记录，关联的表如果有记录对应就显示关联的值，没有关联的值显示null.
但使用(+)的情况下，无法实现全连接。因为无法在where 的左右同时使用(+).
select a.ename,b.dname
from emp a,dept b
where a.deptno=b.deptno(+)
下列的语句是无法通过的：
select a.ename,b.dname
from emp a,dept b
where a.deptno(+)=b.deptno(+)
1.5任务：
4.自关联(self-join)
4.1有些情况下，需要关联一个表，这种关联叫自关联。
4.2自关联经常使用的一般是查看表中的记录是否重复。在信息管理系统中，有时出现
数据录入的错误。同一个数据，被输入了2次以上，除了主键不一样，其他字段基
本上一样。即查询重复的记录。
4.3数据录入错误的发生可能的情况：
(1)数据的自动导入，新建系统从老系统中批量导入数据，导致大量的重复记录。
(2)用户输入错误的数据，将一个数据输入的2次。
(3)
4.4如SCOTT的员工表EMP, MGR字段是员工的经理的员工号。要查询每个员工的经
理的姓名。就需要使用自关联。
select a.ename, b.ename
from emp a, emp b
where a.mgr=b.empno
4.5查询emp表中可能同名的员工的记录。
select a.empno,a.ename
from emp a,emp b
where a.empno<>b.empno and a.ename=b.ename
4.6查询员工表emp的重复记录：
select a.empno,a.ename
from emp a,emp b
where a.empno<>b.empno and a.ename=b.ename and a.job=b.job and
a.sal=
b.sal
4.7子连接会导致对表的大量的操作，需要很大的内存。其他用户对自连接的表的操作
会等待很长的时间。一般情况下最好不要使用自关联。
4.8表的自关联的与内关联不同，自关联至少要2个或2个以上的等式条件。
5.查询结果的联合（UNION）（UNION ALL）
-将多个查询结果联合在一起：
-UNION将多个结果集联合在一起，去除重复的记录
-UNION ALL将多个结果联合在一起，不去除重复的记录
Table 7-1: Set Operators
Operator Description
UNION ALL Returns all the rows retrieved by the queries, including
duplicate rows.
UNION Returns all non-duplicate rows retrieved by the queries. INTERSECT Returns rows that are retrieved by both queries.
MINUS Returns the remaining rows when the rows retrieved by the
second query are subtracted from the rows retrieved by the
first query.
-union语法：
select
union
select
union
select
例子1：
select empno,ename
from emp
where deptno=10
union
select deptno,dname
from dept
例子2：
select*from emp where deptno=10
union
select*from emp where job='CLERK'
- union all 语法
select
union all
select
union all
select
例子1：
select*from emp where deptno=10
union all
select*from emp where job='CLERK'
6.查询结果的交集（INTERSECT）：
-将多个查询结果集联合在一起，只保留相同的记录。摘除不同的记录
-语法：
select 语句
intersect
select
intersect
select
例子：
select*from emp where deptno=10
intersect
select*from emp where job='CLERK'
7.查询结果的差集（MINUS）：
-将多个结果集联合在一起，保留它们差异的记录，将包含第2个结果集的记录减去。
-语法：
select
minus
select
minus
select
例子：
select*from emp where deptno=10
minus
select*from emp where job='CLERK'
注：
1.Oracle在合并2个结果集时，Oracle并不关心合并运算符的任何一边的列名，合并
的结果集以第一个结果集的列名为新的列名。
2.select语句必须有相同的列，如果被查询的结果集有不同的列，可使用Oracle的内
置表达式合成为相同的列数。
3.select的相对应的列必须为相同的类型。长度可以不同。
4.在对输出进行排序时，Oracle使用第1个select语句的列名给出查询结果，因为，
只有第1个select的列作为查询结果，因此只有第一个select的列名出现在order by
子句中。
8.Oracle9i实现SQL Server 2000 中的select top n 的SQL语句：
在Oracle9i中没有类似的select top n 的语句。但是它提供了ROWNUM内置函数。
可以实现top n的查询语句。
Select
From
Where rownum<=n
ROWNUM是Oracle在做查询时自动计算的。它会随着记录集的变化而动态变化。
ROWNUM返回第一次从表中选择时返回行的序列号。第1行的ROWNUM为1。
如果想返回一个复杂查询的结果集的top n, 要把此结果集作为中间结果集放在from中，再使用rownum函数。如下例子：
select*from
(select deptno, sum(sal)
from emp
group by deptno)
where rownum<=2
课前提问：
1.查询采购金额多于5000元的客户清单，以及每个客户的采购金额，并按总采购金额
排序（客户名称，采购金额）
select a.CUST_FIRST_NAME||' '|| CUST_LAST_NAME,
sum(c.UNIT_PRICE*C.QUANTITY)as totalRMB
from CUSTOMERS a,ORDERS b,ORDER_ITEMS c
where a.customer_ID=b.Customer_ID and b.order_id=c.Order_ID
group by a.CUST_FIRST_NAME||' '|| CUST_LAST_NAME
having sum(c.UNIT_PRICE*C.QUANTITY)>5000
order by totalRMB desc
2.查询总销售额大于5000元的产品清单，以及每种产品的累计销售额，按总销售额降
序排序（产品名称，销售金额）
select a.PRODUCT_NAME,sum(c.UNIT_PRICE*C.QUANTITY)as totalRMB
from PRODUCT_INFORMATION a,ORDER_ITEMS c
where a.PRODUCT_ID=c.PRODUCT_ID
group by a.PRODUCT_NAME
having sum(c.UNIT_PRICE*C.QUANTITY)>5000
order by totalRMB desc
[] 课后作业：
1.查询工作地点在‘NEWYORK’的员工的编号，姓名，职位，部门名称。
2.查询每个部门的名称，地点，汇总工资，平均公司。
3.查询订单号为2354的定单的明细信息（产品名称，销售数量，销售金额）
4.查询订单金额大于1000的订单信息（显示订单号，客户名称，订单日期，销售金额）
5.查询公司1999年每个月的订单数量，订单金额，最大的订单金额，最小的订单金额
二次作业
经典表关联与多表查询
经典表关联与多表查询 目的： 1.掌握从多个表查询数据的基本知识 2.了解和学习外连接(out join) 3.掌握内连接 授课内容： 1.对多于一个表的数据查询 1.1现实情况中，在数据库应用中，数据存在于多个相关联的表中。基本上没有数据只 存在于一个表中的情况。小的应用系统一般也有十几个表，大型系统一般有上千个表。 1.2你经常要作的就是在多个表中进行数据查询。 1.3Oracle对多表查询使用表连接的技术（table join） 1.4表连接的基本条件： (1)2个表必须有公共字段（同名字段或不同名字段） (2)在一个表中，这个公共字段必须是主键（PK） 1.5二个表中的公共字段，在一个表中是主键，在另外一个表中就是外键(FK)。 1.6二表关联中，公共字段是主键的表称为父表（主表）。是外键的表称为子表（详细 表）。 1.7研究一下scott下的emp和dept表的关系。 1.8研究一下oe下的表： CATEGORIES_TAB CUSTOMERS INVENTORIES ORDERS ORDER_ITEMS PRODUCT_DESCRIPTIONS PRODUCT_INFORMATION 1.9多表查询的语法 select 子句 from 表1[ 别名]，表2[ 别名]，视图[ 别名]，（select 子句）别名 where 连接语句 and 其他条件语句 [oupy by 分类项目] [having 子句] [order by 子句] 1.10任务：查询每个员工的编号，姓名，部门名称，部门位置 select empno,ename, dname,loc from emp a,dept b where=
数据库表关系模型解析6——多对多
数据库表关系模型解析6——多对多 狼奔代码生成器是一款为程序员设计的前期开发辅助工具，是一个软件项目智能开发的平台，它可以自动生成https://www.360docs.net/doc/5510254156.html,页面及后台代码。 实践开发过程中，我们使用PowerDesigner设计数据库模型。狼奔代码生成器就是读取PowerDesigner设计的数据库模型，分析其中的表与表之间的关系模型，分析其中的表和字段的说明信息中的关键字，自动生成不同的页面。 表与表之间的关系模型包括 1.单表数据模型 2.自连接数据模型 3.一对一数据模型 4.一对多数据模型 5.一对多数据模型中的一张表是自连接 6.多对多数据模型 7.多对多数据模型中的一张表是自连接 关键字包括 1.查询 2.状态 3.上传 4.工作流
架构图 数据访问层(DAL) 数据实体Entity Framework 业务实体和校验元数据 业务逻辑层(BLL) 业务处理 工作流 事务 接口层(IBLL)服务契约 展示层（App ）View （视图） Controller （控制器） Models （页面实体）对其他系统暴露服务Service （服务） 公共组件 安全组件 日志记录 异常捕获 公共类库(Common) 组件说明
图表1项目组件说明图 1)App——页面展示层 采用MVC框架，使用Jquery脚本库，控件选用Easyui。 2)WcfHost——服务宿主(后期扩展) 为对外的服务提供宿主，使用WCF技术，HTTPS通讯协议。 3)IBLL——业务接口层 业务逻辑层的方法对外暴露的接口和服务契约。 4)BLL——业务逻辑层 业务逻辑的操作，包括业务处理，事务，日志。 5)DAL——数据访问层 数据库访问的操作，数据实体，业务实体，数据校验，使用Entity Framework。6)Common——公共组件层 整个应用程序使用的公共辅助方法。 7)WFActivitys——工作流活动层(后期扩展) 定义了工作流需要的活动，使用微软WF技术。 8)WFDesigner——工作流设计器(后期扩展) 可以让实施人员自由配置工作流的设计器，使用微软WPF技术。 采购计划明细和分发的作用 业务需求：将采购计划明细中的物资分发到不同的站点 采购计划明细和分发之间有一张关联表，这三张表就构成了一个典型的“多对多数据模型” 下面我们以分发为例子分析“多对多数据模型”数据模型，代码已在生成的文件中，并且注释详备，此文不再赘述 数据模型 采购计划明细和分发之间是多对多的关系
多对多关系表
数据库建表-- 一对多/多对一/一对一/多对多关系 关联映射：一对多/多对一存在最普遍的映射关系，简单来讲就如球员与球队的关系；一对多：从球队角度来说一个球队拥有多个球员即为一对多多对一：从球员角度来说多个球员属于一个球队即为多对一数据表间一对多关系如下图： 关联映射：一对一关系就如球队与球队所在地址之间的关系，一支球队仅有一个地址，而一个地址区也仅有一支球队。数据表间一对一关系的表现有两种，一种是外键关联，一种是主键关联。图示如下： 一对一外键关联： 一对一主键关联：要求两个表的主键必须完全一致，通过两个表的主键建立关联关系 关联映射：多对多 多对多关系也很常见，例如学生与选修课之间的关系，一个学生可以选择多门选修课，而每个选修课又可以被多名学生选择。数据库中的多对多关联关系一般需采用中间表的方式处理，将多对多转化为两个一对多。 数据表间多对多关系如下图：
---------------------------------------------------------------------------------------------------------- 前言：多对多关系至少需要3个表，我们把一个表叫做主表，一个叫做关系表，另外一个叫做字典表或者副表（字典表是纪录比较少，而且基本稳定的，例如：版块名称；副表是内容比较多，内容变化的，例如）。按照数据库的增删查改操作，多对多关系的查找都可以用inner join或者 select * from 主表where id in (select 主表id from 关系表) 1，角色任命型 特点：关系表两外键组合无重复纪录，关系表一般不需要时间字段和主键，有一个表是字典类型的表。 界面特点：显示主表，用checkbox或多选select设置多选关系。 例如：任命版主（用户表-关系表-版块名称表），角色权限控制等，用户是5个版块版主，只要关系表5行纪录就可以确立，关系表的两个外键具有联合主键性质。 增加关系：如果没有组合纪录，insert之。 删除关系：如果有组合纪录，删除之。 2，集合分组型 特点：同角色任命型类似，关系表两外键组合无重复纪录，关系表一般不需要时间字段和主键。区别是主副表都不是字典表，可能都很大不固定。 界面特点：显示主表，用搜索代替简单的checkbox或多选select，或者一条一条的添加。 例如：歌曲专集（专集表-关系表-歌曲表）。手机分组（分组表-关系表-手机表）。用户圈子（圈子表-关系表-用户表）。文章标签（文章表-关系表-标签表） 增加关系：同版主任命型。 删除关系：同版主任命型。 3，明细帐型
Oracle -Update 多表关联
一条Update更新语句是不能更新多张表的，除非使用触发器隐含更新。而表的更新操作中，在很多情况下需要在表达式中引用要更新的表以外的数据。我们先来讨论根据其他表数据更新你要更新的表 一、MS SQL Server 多表关联更新 sql server提供了update的from 子句，可以将要更新的表与其它的数据源连接起来。虽然只能对一个表进行更新，但是通过将要更新的表与其它的数据源连接起来，就可以在update的表达式中引用要更新的表以外的其它数据。 一般形式： update A SET 字段1=B表字段表达式, 字段2=B表字段表达式 from B WHERE 逻辑表达式 例如： UPDATE dbo.Table2 SET dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB FROM dbo.Table2 INNER JOIN dbo.Table1 ON (dbo.Table2.ColA = dbo.Table1.ColA); 实际更新的操作是在要更新的表上进行的，而不是在from子句所形成的新的结果集上进行的 二、Oracle 多表关联更新 Oracle没有update from语法，可以通过两种实现方式： 1、利用子查询： update A SET 字段1=（select 字段表达 式 from B WHERE ...）, 字段2=（select 字段表达式 from B WHERE ...） WHERE 逻辑表达式 UPDATE多个字段两种写法：
写法一： UPDATE table_1 a SET col_x1 = (SELECT b.col_y1, b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m), col_x2= (SELECT b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m) WHERE EXISTS(SELECT * FROM table_2 b WHERE b.col_n = a.col_m) 或 UPDATE table_1 a SET col_x1 = (SELECT b.col_y1, b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m), col_x2= (SELECT b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m) WHERE a.col_m=(SELECT b.col_n FROM table_2 b WHERE b.col_n = a.col_m) 写法二： UPDATE table_1 a SET(col_x1, col_x2)= (SELECT b.col_y1, b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m) WHERE EXISTS(SELECT * FROM table_2 b WHERE b.col_n = a.col_m); 或 UPDATE table_1 a SET(col_x1, col_x2)= (SELECT b.col_y1, b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m) WHERE a.col_m=(SELECT b.col_n FROM table_2 b WHERE b.col_n = a.col_m)注意： 1. 对于子查询的值只能是一个唯一值，不能是多值。 2. 子查询在绝大多数情况下，最后面的where EXISTS子句是重要的，否则将得到错误的结果。且where EXISTS子句可用另一方法代替，如上。最后的子句是对a表被更新记录的限制，如无此句，对于a表中某记录，如在b表中关
搞清多表之间的关系
多表之间的关系操作总结 经典例子： 一对一：身份证号码与人 一对多：城市与大学，订单与订单项，部门与员工，班级与学生等等。 多对一：一对多的反面。订单项与订单，大学与城市，员工与公司，学生与班级。多对多：学生与老师 一对多：单向、双向。 一对多关系中单向与双向的区别: 单向体现在程序中就是你可以通过一方得到另一方，但不能通过另一方得到这一方双向就是彼此都能得到对方，相互都有关于对方的一个引用。（外键） 什么时候需要用单向，什么时候需要用双向。 网友答： 只需要从一方获取另一方的数据时就使用单向关联 双方都需要获取对方数据时就使用双向关系 部门---人员 使用人员时 如果只需要获取对应部门信息(user.getDeptarment()) 不需要从部门下的人员信息时，就配置成单向多对一 使用部门时 如果只需要获取部门下人员信息(deptartmanet.getUsers()) 不需要从人员获取部门信息时，就配置成单向一对多 既要获取部门下人员 deptartmanet.getUsers() 又要从人员获取部门信息 user.getDeptarment() 那就配置成双向一对多,也就是双向多一
看需求来配置了。 单向多对一”、“单向一对多，其实概念一样，记得在多的一端配置 双向一对多就是两边都要配，做到你中有我我中有你 弄清楚：关系维护端和关系被维护端。 1—m：多的一方是关系维护端，关系维护端负责外键记录的更新，关系被维护端没有权利更新外键字段。 不管是一对多，还是多对一，外键一定建在多的那方。外键一定是另一张表中已经存在的主键。 关于@mappedBy和@JoinColumn 表示声明一对多关系由对方维护，自己将不再维护，就算在自己这端设置值，保存到数据库后外键依然是null @mappedBy注解的作用：在JPA中，在@OneToMany里加入mappedBy属性可以避免生成一张中间表。 网上： a）只有OneT oOne,OneT oMany,ManyToMany上才有mappedBy属性，ManyToOne不存在该属性； b）mappedBy标签一定是定义在the owned side（被拥有方，也叫关系被维护端，即一的一方），他指向theowning side（拥有方，也叫关系维护端，即多的一方）； c）关系的拥有方负责关系的维护，在拥有方建立外键。所以用到@JoinColumn d）mappedBy跟JoinColumn/JoinTable总是处于互斥的一方，可以理解为正是由于拥有方的关联被拥有方的字段存在，拥有方才拥有了被拥有方。mappedBy这方定义JoinColumn/JoinT able总是失效的，不会建立对应的字段或者表。 @JoinColumn所在实体是关系拥有方，name的值即拥有方对应表到参考表的外键名称。@ mappedBy所在实体是关系的被拥有方，value值owner中表示被拥有类的属性。 举例子：创建两个实体类 一对多时，建立实体类时：一的一方需要一个Set或者List集合存储多的对象，多的一方需要定义一个一的对象。需要设置外键的一方需要加上@JoinColoumn注解。 例子： 城市与大学：一对多
Sql语句多表联合查询
Sql语句多表联合查询 查询所要用到的表 1．course表 USE[学生管理] GO /****** Object: Table [dbo].[course] Script Date: 04/28/2015 11:37:12 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE[dbo].[course]( [课号][nvarchar](20)NOT NULL, [课名][char](20)NOT NULL, [类型][char](10)NOT NULL, [学分][int]NOT NULL, PRIMARY KEY CLUSTERED ( [课号]ASC )WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF, IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON) ON[PRIMARY] )ON[PRIMARY] GO SET ANSI_PADDING OFF GO
2.score表 USE[学生管理] GO /****** Object: Table [dbo].[score] Script Date: 04/28/2015 11:38:18 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE[dbo].[score]( [学号][nvarchar](20)NOT NULL, [课号][nvarchar](20)NOT NULL, [考试成绩][decimal](9, 2)NULL, [平时成绩][decimal](9, 2)NULL, PRIMARY KEY CLUSTERED ( [学号]ASC, [课号]ASC )WITH (PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF, IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON) ON[PRIMARY] )ON[PRIMARY] GO 3.学生表 USE[学生管理] GO /****** Object: Table [dbo].[student] Script Date: 04/28/2015 11:39:09 ******/ SET ANSI_NULLS ON GO
一对多的自身关联
一对多的自身关联 一的一方和多的一方都属于同一个类 这种结构就类似于树状结构 每一个对象内部本身包括 一个父节点对象(此时这个原本的对象对于这个父节点对象是多对一的关系) 一个子节点的集合(此时这个对象对于子节点集合来说又是一对多的关系） 创建表 create table creature( id bigint primary key, name varchar(15), parent_creature_id bigint ); 一对一关联第一种关联方式通过一个表的主键由另一个表来产生 第一张表 create table husband( id varchar(100) primary key,
name varchar(100) default '' )character set utf8 collate utf8_general_ci;---能插入中文字符 create table wife( id varchar(100) primary key, name varchar(100) default '' )character set utf8 collate utf8_general_ci; Husband.hbm中uuid方式产生主键
mysql 多表联合查询
mysql 多表联合查询 Mysql多表查询，多表插入和多表更新 2010年8月27日星期四 11:24 A.M. /*************************************by garcon1986*****************************************************/ 多表查询： CREATE TABLE IF NOT EXISTS contact( contact_id int(11) NOT NULL AUTO_INCREMENT, user_name varchar(255), nom varchar(255), prenom varchar(255), mail varchar(64), passcode char(64), PRIMARY KEY(contact_id) ); CREATE TABLE IF NOT EXISTS droit( droit_id int( 11 ) NOT NULL AUTO_INCREMENT , droit varchar(255), PRIMARY KEY(droit_id) ); CREATE TABLE IF NOT EXISTS contactdroit( contactdroit_id int(11) NOT NULL AUTO_INCREMENT, contact_id int( 11 ), droit_id int( 11 ), PRIMARY KEY( contactdroit_id ) ); Insert into contact(contact_id, user_name) values(1,'user1'); Insert into contact(contact_id, user_name) values(2,'user2'); Insert into contact(contact_id, user_name) values(3,'user3'); Insert into droit(droit_id, droit) values(1,'admin'); Insert into droit(droit_id, droit) values(2,'superuser'); Insert into contactdroit(contact_id, droit_id) values(1, 1); Insert into contactdroit(contact_id, droit_id) values(2, 1); Insert into contactdroit(contact_id, droit_id) values(3, 2); SELECT c.contact_id, d.droit_id, d.droit FROM contact c, contactdroit cd, droit d where c.contact_id = cd.contact_id and cd.droit_id = d.droit_id; 结果： contact_id droit_id droit 1 1 admin 2 1 admin
数据库设计多对多关系的几种形态
数据库设计多对多关系的几种形态(转) 前言：多对多关系至少需要3个表，我们把一个表叫做主表，一个叫做关系表，另外一个叫做字典表或者副表（字典表是纪录比较少，而且基本稳定的，例如：版块名称；副表是内容比较多，内容变化的，例如）。按照数据库的增删查改操作，多对多关系的查找都可以用inner join或者select * from 主表where id in (select 主表id from 关系表) 1，角色任命型 特点：关系表两外键组合无重复纪录，关系表一般不需要时间字段和主键，有一个表是字典类型的表。 界面特点：显示主表，用checkbox或多选select设置多选关系。 例如：任命版主（用户表-关系表-版块名称表），角色权限控制等，用户是5个版块版主，只要关系表5行纪录就可以确立，关系表的两个外键具有联合主键性质。 增加关系：如果没有组合纪录，insert之。 删除关系：如果有组合纪录，删除之。 2，集合分组型 特点：同角色任命型类似，关系表两外键组合无重复纪录，关系表一般不需要时间字段和主键。区别是主副表都不是字典表，可能都很大不固定。 界面特点：显示主表，用搜索代替简单的checkbox或多选select，或者一条一条的添加。 例如：歌曲专集（专集表-关系表-歌曲表）。手机分组（分组表-关系表-手机表）。用户圈子（圈子表-关系表-用户表）。文章标签（文章表-关系表-标签表） 增加关系：同版主任命型。 删除关系：同版主任命型。 3，明细帐型
特点：关系表可以有重复纪录，关系表一般有时间字段，有主键，可能还有文字型的字段用来说明每次发生关系的原因（消费）。 界面特点：显示关系表，用radio或下拉设置单选关系。 例如：现金消费明细帐或订单（用户表-订单表-消费原因表），用户可能多次在同一事情上重复消费。积分变化纪录也属于这类。 增加关系：不管有没有组合纪录，insert之，纪录时间。 删除关系：根据关系表PK删除。 4，评论回复型 特点：同明细帐型关系表一般有时间字段，有主键，区别是重点在文字型的字段用来说明每次发生关系的内容（评论回复）。 界面特点：回复文本框。 例如：论坛回复（用户表-回复表-帖子表），用户可能多次在不同帖子上评论回复费。 增加关系：不管有没有组合纪录，insert之，纪录时间和文字。 删除关系：根据关系表（回复表）PK删除。 5，站内短信型 特点：主副表是同一个，关系表一般有时间字段，有主键，重点在关系表文字型的字段用来说明每次发生关系的内容（消息）或者其他标记位来表示文字已读状态时间等。 界面特点：回复文本框。 例如：站内短信（用户表-短信表-用户表），用户可能给用户群发或者单发，有标记位来表示文字已读状态时间等。 增加关系：不管有没有组合纪录，insert之，纪录时间和文字。 删除关系：根据关系表（回复表）PK删除。 6，用户好友型
ORACLE 多表关联 UPDATE 语句
ORACLE 多表关联UPDATE 语句 为了方便起见,建立了以下简单模型,和构造了部分测试数据: 在某个业务受理子系统BSS中， --客户资料表 create table customers ( customer_id number(8) not null, -- 客户标示 city_name varchar2(10) not null, -- 所在城市 customer_type char(2) not null, -- 客户类型 ... ) create unique index PK_customers on customers (customer_id) 由于某些原因，客户所在城市这个信息并不什么准确，但是在 客户服务部的CRM子系统中，通过主动服务获取了部分客户20%的所在 城市等准确信息，于是你将该部分信息提取至一张临时表中： create table tmp_cust_city ( customer_id number(8) not null, citye_name varchar2(10) not null， customer_type char(2) not null ) 1) 最简单的形式 --经确认customers表中所有customer_id小于1000均为'北京' --1000以内的均是公司走向全国之前的本城市的老客户:) update customers set city_name='北京' where customer_id<1000 2) 两表(多表)关联update -- 仅在where字句中的连接 --这次提取的数据都是VIP，且包括新增的,所以顺便更新客户类别update customers a -- 使用别名 set customer_type='01' --01 为vip，00为普通 where exists (select 1 from tmp_cust_city b where b.customer_id=a.customer_id ) 3) 两表(多表)关联update -- 被修改值由另一个表运算而来 update customers a -- 使用别名 set city_name=(select b.city_name from tmp_cust_city b where b.customer_id=a.customer_id) where exists (select 1
用PowerDisigner建立一对一和多对多模型解析
一对一模型 身份证----------用户 建立模型之前首先得想一下要建立一对一的模型的思路，建立模型思路才是最重要的，思路想好了用工具很快就会生成。 一对一模型的建立有两种方式： 相同主键 (继承来实现，只继承主键) 唯一外键：一对多的一种特殊形式（外键是unique）1、相同主键型的一对一模式 （1）先创建两个概念模型 身份证 用户
用户的概念模型中没有设置主键 （2）用inheritance，从用户模型连接到身份证模型 （3）双击设置继承关系，选择inherit only primary altributes，只继承主键属性，否则会把所有属性在用户里再显示
（4）tool--转换成物理模型
（5）Database--生成脚本文件，去掉check model（如果没有连接pl/sql就选script generation） （6）如果面板上还有其他模板，勾选掉
（7）接下来确定，就可以成功生成脚本文件了。。。 用生成的脚本文件在plsql的命令窗口运行，生成两个表，插入数据 先插入身份证 再插入用户表信息
想要在用户表中加入身份证号是3的用户就会出错。idnum既是用户表的主键又是用户表的外键。 2、唯一外键型的一对一模型 （1）各自创建两个概念模型都有主键 （2）表A-------表B建立一对多关系 （3）转换为物理模型 （4）生成脚本文件 （5）在脚本文件里把有外键的一个表的外键加上unique约束 （6）在pl/sql中运行脚本文件生成的两个表就是一对一关系。 多对多模型 学生--------课程 要实现多对多的先想一下思路
SQL 数据库多表连接详细讲解
SQL多表连接 应用背景 数据库是由多张表组成的存储结构，并通过多张表之间的关系建立起完整的有效的数据存储形式，形成关系型数据库。作为数据查询语言SQL，提供了功能强大的数据表连接查询功能，使多张表格之间形成有效的数据联系，使得关系数据库在大型数据库应用中占据了主角地位。 一个普通的大型数据库应用程序所使用的数据库中，有多达几百张表的数据，那么如何将这些表高效的有机的联系起来，就成为设计关系数据库的一个重要指标。优良的数据库设计指标包括： 1．减少数据冗余，去除掉多余的数据冗余，可以通过建立表之间的连接关系完成。 2．数据更新正确，不能因为表之间存在关系后，使得更新记录出现不正常的数据。 3．添加数据正常，添加数据过程中，应该保持数据表之间的关系，确定表之间的连接。 4．查询简便灵活，在建立数据连接的查询过程中，连接清晰简便，操作灵活准确。 数据库设计是应用软件成功与否的一项重要标志。设计数据库，除与系统分析结果，设计员的水平等有关外，还可以参考一些规范的设计范式，下面简单介绍数据库的2个基本设计范式： 1．第一范式：要求表的每列都是不可再分的简单数据项，所以1对N 关系就必须用多表表示，而不能用一张表表示。 2．第二范式：表中的每一个非主键列必须完全函数依赖于主键，就是说表中除主键之外的其他列，都必须通过主键能够唯一确定。 数据库的设计非常复杂，没有一成不变的东西，需要就地取材，解决问题，简单化问题。 知识要点 (1) 传统连接 连接就是将多个表中的数据连接到一起的查询，即连接操作可以在一个Select语句中完成从多个表中查找和处理数据，使用连接时可以使用名字相同的不同表的列，也可以不同，但要求连接的列不需可连接，即数据类型相同。 传统的连接语法如下： Select * from Tblname1 T1,Tblname2 T2 where T1.column=T2.column 连接SQL语句的明显标志为在From子句后边，有多个表Tblname1,
数据库中多对多的关系设计
数据库中多对多的关系设计 数据库设计多对多关系的几种形态 前言：多对多关系至少需要3个表，我们把一个表叫做主表，一个叫做关系表，另外一个叫做字典表或者副表（字典表是纪录比较少，而且基本稳定的，例如：版块名称；副表是内容比较多，内容变化的，例如）。 按照数据库的增删查改操作，多对多关系的查找都可以用inner join或者select * from 主表where id in (select 主表id from 关系表) 1，角色任命型 特点：关系表两外键组合无重复纪录，关系表一般不需要时间字段和主键，有一个表是字典类型的表。 界面特点：显示主表，用checkbox或多选select设置多选关系。 例如：任命版主（用户表-关系表-版块名称表），角色权限控制等，用户是5个版块版主，只要关系表5行纪录就可以确立，关系表的两个外键具有联合主键性质。 增加关系：如果没有组合纪录，insert之。 删除关系：如果有组合纪录，删除之。 2，集合分组型 特点：同角色任命型类似，关系表两外键组合无重复纪录，关系表一般不需要时间字段和主
键。区别是主副表都不是字典表，可能都很大不固定。 界面特点：显示主表，用搜索代替简单的checkbox或多选select，或者一条一条的添加。例如：歌曲专集（专集表-关系表-歌曲表）。手机分组（分组表-关系表-手机表）。用户圈子（圈子表-关系表-用户表）。文章标签（文章表-关系表-标签表） 增加关系：同版主任命型。 删除关系：同版主任命型。 3，明细帐型 特点：关系表可以有重复纪录，关系表一般有时间字段，有主键，可能还有文字型的字段用来说明每次发生关系的原因（消费）。 界面特点：显示关系表，用radio或下拉设置单选关系。 例如：现金消费明细帐或订单（用户表-订单表-消费原因表），用户可能多次在同一事情上重复消费。积分变化纪录也属于这类。 增加关系：不管有没有组合纪录，insert之，纪录时间。 删除关系：根据关系表PK删除。 4，评论回复型 特点：同明细帐型关系表一般有时间字段，有主键，区别是重点在文字型的字段用来说明每次发生关系的内容（评论回复）。 界面特点：回复文本框。
hibernate——一对一、多对一和多对多关系的比较
现在学习完了这几种映射关系，但是有点乱，这里来小结一下。关键是表之间如何产生映射关系，以及产生的表的结构。 1、一对一映射： 一对一是通过one-to-one标签来产生映射关系的，其实，如果单单说是建立两个表之间的关联，只要在一个映射文件中配置one-to-one标签就可以了，在另一个映射文件中，也做类似的配置，只会起到关联的作用，建立起双向的关联。这里举Person和IdCard的例子，IdCard类的映射文件如下： [html]view plaincopyprint? 1.
多对一,一对一,一对多
第一种关联关系：一对多（多对一） "一对多"是最普遍的映射关系，简单来讲就如消费者与订单的关系。一对多：从消费者角的度来说一个消费者可以有多个订单，即为一对多。 多对一：从订单的角度来说多个订单可以对应一个消费者，即为多对一。 一对多关系在hbm文件中的配置信息： 消费者（一方）：
SQL多表联合查询
SQL多表联合查询（Access数据库表） 2010-08-25 15:48 根据SQL语法，通过连接运算符可以实现多个表查询。连接可以在Select 语句的FROM 子句或Where子句中建立，在FROM子句中指出连接时有助于将连接操作与Where子句中的搜索条件区分开来。 SQL-92标准所定义的FROM子句的连接语法格式为： FROM join_table join_type join_table [ON (join_condition)] 其中join_table指出参与连接操作的表名，连接可以对同一个表操作，也可以对多表操作，对同一个表操作的连接又称做自连接。 join_type 指出连接类型，可分为三种：内连接、外连接和交叉连接。内连接(INNER JOIN)使用比较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。根据所使用的比较方式不同，内连接又分为等值连接、自然连接和不等连接三种。外连接分为左外连接(LEFT OUTER JOIN或LEFT JOIN)、右外连接(RIGHT OUTER JOIN 或RIGHT JOIN)和全外连接(FULL OUTER JOIN或FULL JOIN)三种。与内连接不同的是，外连接不只列出与连接条件相匹配的行，而是列出左表(左外连接时)、右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的数据行。 前提：假设已存在外部数据库，并且已经打开包含a表、b表、c表三表的Mdb数据库。根据SQL语法规则，写出如下代码： 1. 内查询（查询三个表中均存在的ID记录，任何一个表中不存在的id将均被过滤掉） .版本 2 记录集句柄＝外部数据库1.查询(“select * from a表 inner join b表 on a 表.id=b表.id inner join c表 on a表.id=c表.id ” ）'＋“ where ……”) ' where 条件暂时省略 2. 外查询（left join ,列出左表中的全部ID，不管b、c表中有无该ID） .版本 2 记录集句柄＝外部数据库1.查询(“select * from a表 left join b表 on a表.id=b 表.id left join c表 on a表.id=c表.id ” ）'＋“ where ……”) ' where 条件暂时省略 可是运行结果均为查询失败，如果仅仅两个表，查询则成功，三个或三个以上的表就会失败。 经过一天的捉摸，终于弄明白了问题原因，问题不在SQl，也不在易语言，而是没弄明白Access对SQL的支持，将上述代码改为如下代码，问题解决！ .版本 2 记录集句柄＝外部数据库1.查询(“select * from (a表 inner join b表 on a 表.id=b表.id) inner join c表 on a表.id=c表.id ”) ' ＋“ where ……”) ' where 条件暂时省略 .版本 2
多对多关系以及多表查询优化处理
多对多关系以及多表查询优化处理 前言：这两天机器坏了，正在送修中，写个系列的大型网站架构的文章，希望对有志在互联网做出一番事业的站长朋友们一些帮助。 注意：这里的大型网站架构只包括高互动性高交互性的数据型大型网站，基于大家众所周知的原因，我们就不谈新闻类和一些依靠HTML静态化就可以实现的架构了，我们以高负载高数据交换高数据流动性的网站为例，比如海内，开心网等类似的web2.0系列架构。我们这里不讨论是PHP还是JSP或者.NET环境，我们从架构的方面去看问题，实现语言方面并不是问题，语言的优势在于实现而不是好坏，不论你选择任何语言，架构都是必须要面对的。 文入正题： 首先讨论一下大型网站需要注意和考虑的问题 A. 海量数据的处理。 众所周知，对于一些相对小的站点来说，数据量并不是很大，select和update 就可以解决我们面对的问题，本身负载量不是很大，最多再加几个索引就可以搞定。对于大型网站，每天的数据量可能就上百万，如果一个设计不好的多对多关系，在前期是没有任何问题的，但是随着用户的增长，数据量会是几何级的增长的。在这个时候我们对于一个表的select和update的时候(还不说多表联合查询)的成本的非常高的。 B. 数据并发的处理 在一些时候，2.0的CTO都有个尚方宝剑，就是缓存。对于缓存，在高并发高处理的时候也是个大问题。在整个应用程序下，缓存是全局共享的，然而在我们进行修改的时候就，如果两个或者多个请求同时对缓存有更新的要求的情况下，应用程序会直接的死掉。这个时候，就需要一个好的数据并发处理策略以及缓存策略。 另外，就是数据库的死锁问题，也许平时我们感觉不到，死锁在高并发的情况下的出现的概率是非常高的，磁盘缓存就是一个大问题。 C. 文件存贮的问题 对于一些支持文件上传的2.0的站点，在庆幸硬盘容量越来越大的时候我们更多的应该考虑的是文件应该如何被存储并且被有效的索引。常见的方案是对文件按照日期和类型进行存贮。但是当文件量是海量的数据的情况下，如果一块硬盘存贮了500个G的琐碎文件，那么维护的时候和使用的时候磁盘的Io就是一个巨大的问题，哪怕你的带宽足够，但是你的磁盘也未必响应过来。如果这个时候还涉及上传，磁盘很容易就over了。 也许用raid和专用存贮服务器能解决眼下的问题，但是还有个问题就是各地的访问问题，也许我们的服务器在北京，可能在云南或者新疆的访问速度如何解决？如果做分布式，那么我们的文件索引以及架构该如何规划。 所以我们不得不承认，文件存贮是个很不容易的问题 D. 数据关系的处理 我们可以很容易的规划出一个符合第三范式的数据库，里面布满了多对多关系，还能用GUID来替换INDENTIFY COLUMN 但是，多对多关系充斥的2.0时代，第三范式是第一个应该被抛弃的。必须有效的把多表联合查询降到最低。