多表操作、外键、视图、多表联合

多表操作、外键、视图、多表联合

一、多表操作

方法一：

笛卡尔乘积（交叉连接）：一个表中的每一行分别与另一个表中的每一行生成一条新记录，默认生成新记录数＝a表的记录数*b表的记录数

语法：select 字段名列表 from 表名1，表名2 where 条件

select 员工表 . 姓名,性别,年龄,公司名称 from 员工表,公司表 where 员工表 . 姓名 = 公司表 . 姓名

使用as对列，对表进行换名输出：

select 姓名 as username,性别 as sex,年龄 as age from 员工表

select avg(年龄) as 平均年龄 from 员工表

select * from 员工表 as YG

select YG . 姓名,性别,年龄,公司名称 from 员工表 as YG,公司表 as GS where YG . 姓名 = GS . 姓名

方法二：join . 连接

A、内连接：

语法：select 字段名列表 from 表名1 [inner] join 表名2 . 条件

作用：与笛卡尔乘积一样，必须是满足on条件的记录才会显示

B、外连接：左外连接、右外连接、全外连接

左外连接：语法：select 字段 from 表名1 left join 表名2 . 条件

说明：以左表为主，显示左表的所有记录，如果在右表中没有与之相对应的记录，则相应字段为空（null）

右外连接：语法：select 字段 from 表名1 right join 表名2 . 条件

说明：以右表为主，显示右表的所有记录

全外连接：语法：select 字段 from 表名1 full join 表名2 . 条件

说明：显示两个表的所有记录，如果在另一个表中没有与之相对应的记录，则相应的字段为空（null）

实例：

用内连接显示员工表和公司表的内容

以左外连接显示两个表的数据

以右外连接显示两个表的数据

用交叉连接显示三个表的数据：

select 员工表 . 姓名,性别,年龄,公司名称,地址,网址 from 员工表,公司表,网址 where 员工表 . 姓名 = 公司表 . 姓名 and 公司表 . 公司名称 = 网址表 . 公司

使用内连接显示三个表的数据：

select 员工表 . 姓名,性别,年龄,公司名称,地址,网址 from 员工表 join 公司表 . 员工表 . 姓名 ＝ 公司表 . 姓名 join 网址表 . 公司表 . 公司名称 = 网址表 . 公司

二、实例

显示2005年参加工作的员工信息

select * from yuanGong where 工作日期 > '2004-12-31' and 工作日期 < '2006-1-1'

select * from yuanGong where 工作日期 like '%2005%'

月－日－年 时 分 秒

select * from yuanGong where 工作日期 like '_ _ _ _ _ _2005%'

显示工作日期是空的员工记录：处理空值 is

select * from yuanGong where 工作日期 is null

说明：SQL Server无法启动时如何处理

命令：net user administrator ldf

services . msc --服务

三、子查询

1、select：

注意：A、内层查询必须使用（）

B、执行顺序：先执行内层查询，返回此结果给外层查询，最后执行外层查询

实例：显示两个表都有的记录

方法一：select a . name from a ,b where a . name = b . name

方法二：select * from a where name in (select * from b)

说明： = 子查询的结果只能有一个

in 子查询的结果可以有多个或是一个

实例：显示a表有，b表没有的员工信息

select * from a where name not in (select * from b)

显示年龄最大的员工信息

select * from yuanGong where 年龄 = (select max(年龄) from yuanGong)

2、表的自连接：一个表进行的自我连接

select * from home as homea,home as homeb

实例：

求每个部门中不同性别的平均基本工资

select 部门,性别,avg(基本工资) from .e group by 部门,性别

找出基本工资大于本部门平均基本工资的员工信息

select * from yuanGong , (select 部门 , avg(基本工资) as 部门平均基本工资 from yuanGong group by 部门) as YG where yuanGong . 部门 = YG . 部门 and yuanGong . 基本工资 > YG . 部门平均基本工资

说明：YG为虚拟表

作业：

A、显示广西的客户信息

select 客户姓名 from client where 客户编号 ＝(select 客户编号 from branches where 机构地址='广西')

B、显示吕泽所需要的服务信息

select * from server where 服务编号 in (select 服务编号 from branche-server where 机构编号 in(select 机构编号 from branches where 客户编号 in (select 客户编号 from client where 姓名='吕泽')))

C、显示每个分支机构所提供的服务信息

select 机构地址,服务名 from server,branches,branche-server where server.服务编号 = branche-server.服务编号 and branche-server.机构编号 = branches .机构编号

D、需要两种服务的客户信息

select * from client where 客户编号 in (select 客户编号 from branches group by 客户编号 having count(*)=2)

四、数据库关系图、关系（略）

五、外键

1、外键：将一个表的主键添加到另一个表中，以此键构建起表与表之间的关系，那么我们这个字段称为另一个表的外键

好处：A、减少数据的冗余

B、级联更新、级联删除

2、外键的创建：右击增加列

点击鼠标右键选择‘关系‘－－表和列规范－－保存

3、级联更新、级联删除：

关系－－insert、update－－层叠

通过数据库关系图：

右击创建－－右击空白选‘创建数据库关系图’

六、视图：窗户、虚拟表，不存放数据

1、引入视图的好处：

A、让用户限制在某些列

B、让用户限制在某些行

C、可以实现多表操作

D、可以实现安全机制（与用户管理放在一起使用）

2、视图的本质就是一个虚拟表，不存放任何数据，只存放select语句

3、创建视图的方法

A、通过图形－－右击新建视图

B、通过命令

实例：创建一个视图存放yuanGong表的所有数据

4、语法：

create view 视图名

as

select 语句

较为全面：

create view 视图名

[with encryption] -- 加密

as

select 语句

[with check option] -- 必须是满足条件的记录才参加操作

实例：通过命令创建视图存放所有女同志的信息

create view viewnv

as

select * from yuanGong where 性别='女'

通过视图插入记录：

insert into viewnv values ('0013','张飞','女'...)

select * from viewnv

限制只能插入女同志记录：

create view view2

as

select * from yuanGong where 性别='女'

with check option

5.查看视图的源代码：sp_helptext viewnv

6、加密使用 with encryption

create view viewnv

with encryption

as

select * from yuanGong where 性别='女'

7、插入失败的情况：

A、如果基本表有多个的话，则不允许插入

示例：

create view viewall

as

select 员工表.姓名，性别，年龄，地址，网址 from 员工表，公司表，网址表 where 员工表.姓名=公司表.姓名 and 公司表.公司名称 = 网址表 .公司

B、如果select语句中含有distinct、group by等不允许插入

示例：

create view view3

as

select distinct 部门 from yuanGong

insert into view3 values ('后勤部')

C、如果select语句中含有字段组合，则也不允许插入

示例：

create view view4

as

select 基本工资+奖金－税金 as 应发工资 from yuanGong

D、如果其他字段不允许有空值，则也不允许插入

示例：

create view view5

as

select 姓名，性别，年龄，籍贯，部门 from yuanGong

select * from view5

insert into view5 values ('ABC','MM',18,'BJ','技术部')

E、如果违反with check option条件，则也不允许插入

示例：

create view viewsp

as

select 食品名称，单价*数量 as 总价 from sp

select * from viewsp

七、多表联合：union

union与连接的区别：连接的对象是列，只是列的增加，联合的对象是行，只是行的增加

语法：select 语句 union select 语句

实例：select * into newall from nan union select * from nv

select * from newall

说明：多个表的字段数量及类型一定要一致

实例：select 姓名，性别，年龄 from yuanGong

union

select * from newall

说明：如果存在重复记录，则只显示一条，显示所有使用union all

实例：统计男女员工各多少人

select 性别,count(*) from (select * from nan

union all

select * from nv) as newall

group by 性别