orcle学习之路第二天

1.Order by:
select * from zybrxx order by zyh asc
Select * from zybrxx order by zyh desc
select zyh, brxm,bqh from zybrxx order by bqh,zyh
select zyh, brxm,bqh from zybrxx order by zyh,bqh
select zyh, brxm,bqh from zybrxx order by bqh asc,zyh desc
select * from zybq order by dm desc ,lc asc
2.Group by:select count(*) from zybrxx group by bqh
SQL 无法把正常的列和汇总函数结合在一起
select count(*),zyh from zybrxx group by bqh//会提示错误
无论在什么情况下进行分组 SELECT 语句中出现的字段只能是在 GROUP BY中出现过的才可以 ——除非你在 SELECT 子句中不指定任何字段
3.Having:select sum(zfze) from zybrxx group by zfze having sum(zfze)>0
表的联合：
4.在一个sql中使用多个表：交叉联合select * from ksmc,zybq
select no,xh,dm from ksmc,zybq
5.等值联合：select * from zybrxx a,ksmc b where b.no = a.ksbh and a.ksbh<2
6.不等值联合：select * from zybrxx a,ksmc b where b.no > a.ksbh
7.select * from zybrxx a join ksmc b on a.ksbh=b.no那个表在前就以那个表为主
8.右联接：select * from ksmc b right outer join zybrxx a on a.ksbh =b.no
9.左联接：select * from ksmc b left outer join zybrxx a on a.ksbh =b.no
内嵌的sql子句
10.select * from zybrxx a,ksmc b where a.ksbh=(select no from ksmc where mc=’内科’ ) and a.ksbh =b.no
11.子查询的嵌套：select * from zybrxx a,ksmc b where a.ksbh=(select no from ksmc where mc=(select mc from ksmc where no=1 )) and a.ksbh =b.no
操作数据
12.插入语句Insert values：insert into zybq(dm,mc,qx,dnfykf,bqfl,lc,srm1,srm2,cwyybz) values(‘12’,’病区2’,’**’,’02’,0,666,’88BQ’,’88UA’,’Y’)
插入空值：insert into zybq(dm,mc,qx,dnfykf,bqfl,lc,yzysl,srm1,srm2,cwyybz) values(‘15’,’病区2’,’**’,’02’,0,666,”,’88BQ’,’88UA’,’Y’)
Insert select:insert into new_table select * from zybq//可完成对表的备份
13.Update:update zybq set mc=’病区2眼科’ where dm=’15’
对多个列进行更新：update zybq set mc=’病区2眼科’,qx=’*’ where dm=’15’
不加条件全部更新：update zybq set qx=’**’
允许其他数值对数据进行修改：update zybq set yzysl=yzysl-1
14.Delete:delete from zybq where dm=’15’