复杂的sql
复杂的sql
通过前⾯的⽂章你可能觉得sql有点简单，接下来我们再来了解⼀些sql的其他的⽤法
AS别名
在⼀个复杂的sql中往往有着很多表名、列名，有时也需要⾃定义⼀下查询结果的列名此时就可以⽤到as
实例1
select*from student as a ;
实例2
select id as'序号',name as'姓名',sex as'性别'from student as a ;
实例3
select id as'序号',name as'姓名',sex as'性别'from student as a  where a.id>1;
join连接
join连接有四种
inner join、 left join 、right join、 full join
inner join可以理解为交集；
full join可以理解为并集；
left join在交集的基础上加上左表；
right join在交集的基础上加上右表；
union合并
union⽤来合并多个select查询的结果，注意列的个数、名称、顺序得⼀致，union会去掉重复的值，union all 不会。实例1
select id from student union select id from student ;
实例2
select id from student union all select id from student ;
实例3
select id from student union select id from student order by id asc;
实例4
select id from student union all select id from student  order by id asc;
结果进⾏了排序
实例5
sql中union多表合并select id from student order by id asc union all select id from student  ;
报错
实例6
(select id from student order by id)asc union all select id from student  ;
在实例5的基础上加上括号不报错，但是没有排序仿佛order by id没有执⾏。limit 、rownum、select top 返回条数
limit⽤于mysql ，rownum⽤于oracle，select top⽤于Microsoft SQL Server
实例1
select id from student order by id asc limit2;
实例2
select id from student order by id asc limit1,2;
表⽰返回2，3⾏数据，第⼀个参数表⽰偏移量（默认为0），第⼆个参数表⽰记录⾏数。实例3
select id from student where rownum<=2;
实例4
select top5 id from student order by id asc;