一、多表操作
![](https://i-blog.csdnimg.cn/blog_migrate/5952df5d69fe95db60db2e8013a7fd37.gif)
方法一:
笛卡尔乘积(交叉连接):一个表中的每一行分别与另一个表中的每一行生成一条新记录,默认生成新记录数=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为虚拟表
作业:
![](https://i-blog.csdnimg.cn/blog_migrate/ae962af00a0d82447cc897a99cf2133c.gif)
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 性别
转载于:https://blog.51cto.com/libotao/131034