日拱一卒无有尽，功不唐捐终入海

MySql子查询多表连接事务视图操作示例

DataBase Sam 749℃ 0评论

子查询

子查询–一个查询中包含另外一个查询。被包含的查询就称为子查询，。包含它的查询就称父查询

1.子查询的使用方式：使用（）包含子查询

2.子查询分类：

独立子查询：子查询可以直接独立运行
查询比“王七”年龄大的学员信息

MySQL
1
select * from Student where BornDate<(select BornDate from Student where StudentName=’王七’)

相关子查询:子查询使用了父查询中的结果

–子查询的三种使用方式
–1.子查询做为条件,子查询接在关系运算符后面 > < >= <= = <> !=,如果是接这关系运算符后面，必须保证 子查询只返回一个值
–查询六期班的学员信息

MySQL
1
select * from Student where ClassId=(select ClassId from grade where classname='八期班')

–子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。

MySQL
1
select * from Student where ClassId=(select ClassId from grade)

–查询八期班以外的学员信息
–当子查询返回多个值(多行一列),可以使用in来指定这个范围

MySQL
1
select * from Student where ClassId in(select ClassId from grade where classname<>'八期班')

–当没有用 EXISTS 引入子查询时，在选择列表中只能指定一个表达式。如果是多行多列或者一行多列就需要使用exists
–使用 EXISTS 关键字引入子查询后，子查询的作用就相当于进行存在测试。外部查询的 WHERE 子句测试子查询返回的行是否存在

MySQL
1
2
select * from Student where EXISTS(select * from grade)
select * from Student where ClassId in(select * from grade)

–2.子查询做为结果集–

MySQL
1
select top 5 * from Student --前五条

–使用top分页

MySQL
1
select top 5 * from Student where StudentNo not in(select top 5 studentno from Student)

–使用函数分页 ROW_NUMBER() over(order by studentno),可以生成行号,排序的原因是因为不同的排序方式获取的记录顺序不一样

MySQL
1
select ROW_NUMBER() over(order by studentno),* from Student

–查询拥有新生成行号的结果集 注意:1.子查询必须的别名 2.必须为子查询中所有字段命名,也就意味着需要为新生成的行号列命名

MySQL
1
2
3
select * from (select ROW_NUMBER() over(order by studentno) id,* from Student) temp where temp.id>0 and temp.id<=5
select * from (select ROW_NUMBER() over(order by studentno) id,* from Student) temp where temp.id>5 and temp.id<=10
select * from (select ROW_NUMBER() over(order by studentno) id,* from Student) temp where temp.id>10 and temp.id<=15

–3.子查询还可以做为列的值

MySQL
1
select (select studentname from student where studentno=result.studentno),(select subjectname from subject where subjectid=result.SubjectId), StudentResult from Result

–使用Row_number over()实现分页
–1.先写出有行号的结果集

MySQL
1
select ROW_NUMBER() over(order by studentno),* from Student

–2.查询有行号的结果集 子查询做为结果集必须添加别名，子查询的列必须都有名称

MySQL
1
select * from (select ROW_NUMBER() over(order by studentno) id,* from Student) temp where id>0 and id<=5

–查询年龄比“廖杨”大的学员，显示这些学员的信息

MySQL
1
select * from Student where BornDate<(select BornDate from Student where StudentName='廖杨')

–查询二期班开设的课程

MySQL
1
select * from Subject where ClassId=(select ClassId from grade where classname='二期班')

–查询参加最近一次“office”考试成绩最高分和最低分
–1查询出科目 ID

MySQL
1
select subjectid from Subject where SubjectName='office'

–2.查询出这一科目的考试日期

MySQL
1
select MAX(ExamDate) from Result where SubjectId=(select subjectid from Subject where SubjectName='office')

–3,写出查询的框架

MySQL
1
select MAX(StudentResult),MIN(StudentResult) from Result where SubjectId=() and ExamDate=()

–4.使用子查询做为条件

MySQL
1
2
3
4
5
6
7
select MAX(StudentResult),MIN(StudentResult) from Result where SubjectId=(
select subjectid from Subject where SubjectName='office'
) and ExamDate=(
select MAX(ExamDate) from Result where SubjectId=(
select subjectid from Subject where SubjectName='office'
)
)

表连接Join

–1.inner join :能够找到两个表中建立连接字段值相等的记录
–查询学员信息显示班级名称

MySQL
1
2
3
select Student.StudentNo,Student.StudentName,grade.classname
from Student
inner join grade on Student.ClassId=grade.ClassId

–左连接: 关键字前面的表是左表，后面的表是右表
–左连接可以得到左表所有数据，如果建立关联的字段值在右表中不存在，那么右表的数据就以null值替换

MySQL
1
2
3
select PhoneNum.*,PhoneType.*
from PhoneNum
left join PhoneType on PhoneNum.pTypeId=PhoneType.ptId

–右连接: 关键字前面的表是左表，后面的表是右表
–右连接可以得到右表所有数据，如果建立关联的字段值在右左表中不存在，那么左表的数据就以null值替换

MySQL
1
2
3
4
5
6
7
8
select PhoneNum.*,PhoneType.*
from PhoneNum
right join PhoneType on PhoneNum.pTypeId=PhoneType.ptId
 
--full join :可以得到左右连接的综合结果--去重复
select PhoneNum.*,PhoneType.*
from PhoneNum
full join PhoneType on PhoneNum.pTypeId=PhoneType.ptId

事务

一种处理机制。以事务处理的操作，要么都能成功执行，要么都不执行

事务的四个特点 ACID：

A:原子性：事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。它是一个整体，不能再拆分

C：一致性：事务在完成时，必须使所有的数据都保持一致状态。。某种程度的一致

I:隔离性：事务中隔离，每一个事务是单独的请求将单独的处理，与其它事务没有关系，互不影响

D：持久性：如果事务一旦提交，就对数据的修改永久保留

使用事务：

将你需要操作的sql命令包含在事务中

1.在事务的开启和事务的提交之间
2.在事务的开启和事务的回滚之间

三个关键语句：

开启事务：begin transaction
提交事务：commit transaction
回滚事务：rollback transaction

MySQL
1
2
3
4
declare @num int =0 --记录操作过程中可能出现的错误号
begin transaction
update bank set cmoney=cmoney-500 where name='aa'
set @num=@num+@@ERROR

–说明这一句的执行有错误 但是不能在语句执行的过程中进行提交或者回滚
–语句块是一个整体，如果其中一句进行了提交或者回滚，那么后面的语句就不再属于当前事务，
–事务不能控制后面的语句的执行

MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
update bank set cmoney=cmoney+500 where name='bb'
set @num=@num+@@ERROR
select * from bank
if(@num<>0 ) --这个@@ERROR只能得到最近一一条sql语句的错误号
begin
print '操作过程中有错误，操作将回滚'
rollback transaction
end
else
begin
print '操作成功'
commit transaction
end

–事务一旦开启，就必须提交或者回滚
–事务如果有提交或者回滚，必须保证它已经开启

视图

视图就是一张虚拟表，可以像使用子查询做为结果集一样使用视图

MySQL
1
select * from vw_getinfo

使用代码创建视图

语法：

MySQL
1
2
3
4
create view vw_自定义名称
as
查询命令
go

–查询所有学员信息

MySQL
1
2
3
4
5
if exists(select * from sysobjects where name='vw_getAllStuInfo')
drop view vw_getAllStuInfo
go --上一个批处理结果的标记
create view vw_getAllStuInfo
as

–可以通过聚合函数获取所以记录数

MySQL
1
2
3
4
5
6
7
select top (select COUNT(*) from Student) Student.StudentNo,Student.StudentName,grade.ClassId,grade.classname from Student
inner join grade on Student.ClassId=grade.ClassId order by StudentName --视图中不能使用order by
--select * from grade --只能创建一个查询语句
--delete from grade where ClassId>100 --在视图中不能包含增加删除修改
go
--使用视图。。就像使用表一样
select * from vw_getAllStuInfo

–对视图进行增加删除和修改操作–可以对视图进行增加删除和修改操作,只是建议不要这么做:所发可以看到：如果操作针对单个表就可以成功，但是如果 多张的数据就会报错：不可更新，因为修改会影响多个基表。

MySQL
1
update vw_getAllStuInfo set classname='asdas' ,studentname='aa' where studentno=1

 

转载请注明：PHP笔记 » MySql子查询多表连接事务视图操作示例

喜欢 (0)
发表我的评论
取消评论

表情

Hi，您需要填写昵称和邮箱！

  • 昵称 (必填)
  • 邮箱 (必填)