锁定老帖子 主题:oracle学习笔记之二
精华帖 (0) :: 良好帖 (0) :: 新手帖 (0) :: 隐藏帖 (0)
|
|
---|---|
作者 | 正文 |
发表时间:2011-07-09
表:建表:create table user_list ( user_name varchar2(23), user_age int ); 查看表结构:desc user_list; 添加字段:alter table user_list add (userno int ); 修改字段:alter table user_list modify ( user_name varchar2(50)); 删除字段:alter table user_list drop column user_age; 对表添加数据:insert into user_list(user_name ,userno) values('gouchao' ,10001); insert into user_list values('xuyongchao',10003); insert into user_list values('gouchao' ,S_userno.Nextval); 对表修改数据 :update user_list set user_name ='gouli' where user_name ='gouchao'; 对表删除数据:delete from user_list where userno = 10001; 删除表格:drop table user_list; 查询:查询所有记录:select * from user_list; select userno from user_list; select userno ID from user_list; select user_list.userno from user_list; select a.userno from user_list a ; 查询所有记录的某些字段:select empno ,ename from emp; 查询某些字段的不同记录:select distinct job from emp; 单条件 的查询:=select * from emp where job='CLERK'; !=select * from emp where job!='CLERK'; >select * from emp where sal > 1600; <select * from emp where sal < 1600; >=select * from emp where sal >=1600; <=select * from emp where sal <= 1600; inselect * from emp where sal in (1600,3000); not inselect * from emp where sal not in(1600,3000); between andselect * from emp where sal between 1600 and 3000 ; Likeselect * from emp where job like 'M%'; select * from emp where job like 'M_'; select * from emp where job like 'MANAGE_'; not likeelect * from emp where job not like 'M_'; select * from emp where job not like 'M%'; Is nullselect * from emp where sal is null; select * from emp where job is null; Is not nullselect * from emp where sal is not null; select * from emp where job is not null; 组合条件的查询:andselect * from emp where job ='CLERK' and sal <3000; orelect * from emp where job ='CLERK' or sal <3000; notselect * from emp where not job ='CLERK'; 排序查询:select * from emp order by job asc , sal desc; 分组查询:group by havingselect empno ,ename ,job ,sal from emp group by job ,empno , ename ,sal having sal <=2000; where group by select empno , ename , job , sal from emp where sal <=2000 group by job ,empno ,ename ,sal ; 字段运算查询:+select ename ,job , mgr+sal from emp; -select ename ,job , mgr-sal from emp; *select ename ,job , mgr*sal from emp; /select ename ,job , mgr/sal from emp; 变换查询显示:select empno 编号,job 工作,sal 薪水 from emp ; 用 SQL 进行多表查询无条件多表查询:产生笛卡尔积select a.empno , a.ename , a.sal , b.deptno , b.dname ,b.loc from emp a , dept b ; 等值多表查询select a.empno , a.ename , a.sal , b.deptno , b.dname ,b.loc from emp a , dept b where a.deptno = b.deptno; 非等值多表查询select a.empno , a.ename , a.sal , b.deptno , b.dname ,b.loc from emp a , dept b where a.deptno != b.deptno and a.deptno = 10; 用 SQL 进行嵌套查询简单嵌套查询select * from emp where sal <=(select sal from emp where ename = 'SMITH'); select * from emp where sal <(select sal from emp where ename = 'SMITH'); select * from emp where sal >=(select sal from emp where ename = 'SMITH'); select * from emp where sal >(select sal from emp where ename = 'SMITH'); 带【in】的嵌套查询select * from emp where sal IN(select sal from emp where ename = 'WARD'); select * from emp where sal not IN(select sal from emp where ename = 'WARD'); 带【any】的嵌套查询select * from emp where sal > any(select sal from emp where job='MANAGER'); 带【some】的嵌套查询select * from emp where sal = some (select sal from emp where job='MANAGER'); 带【all】的嵌套查询select * from emp where sal > all(select sal from emp where job='MANAGER'); <!--EndFragment-->声明:ITeye文章版权属于作者,受法律保护。没有作者书面许可不得转载。
推荐链接
|
|
返回顶楼 | |
浏览 2640 次