表:
建表:
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;
in
select * from emp where sal in (1600,3000);
not in
select * from emp where sal not in(1600,3000);
between and
select * from emp where sal between 1600 and 3000 ;
Like
select * from emp where job like 'M%';
select * from emp where job like 'M_';
select * from emp where job like 'MANAGE_';
not like
elect * from emp where job not like 'M_';
select * from emp where job not like 'M%';
Is null
select * from emp where sal is null;
select * from emp where job is null;
Is not null
select * from emp where sal is not null;
select * from emp where job is not null;
组合条件的查询:
and
select * from emp where job ='CLERK' and sal <3000;
or
elect * from emp where job ='CLERK' or sal <3000;
not
select * from emp where not job ='CLERK';
排序查询:
select * from emp order by job asc , sal desc;
分组查询:
group by having
select 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');
分享到:
相关推荐
Oracle学习笔记精华版Oracle学习笔记精华版Oracle学习笔记精华版Oracle学习笔记精华版
Oracle学习笔记Oracle学习笔记Oracle学习笔记Oracle学习笔记Oracle学习笔记Oracle学习笔记Oracle学习笔记Oracle学习笔记Oracle学习笔记Oracle学习笔记Oracle学习笔记Oracle学习笔记Oracle学习笔记
oracle 个人学习笔记oracle 个人学习笔记oracle 个人学习笔记oracle 个人学习笔记oracle 个人学习笔记oracle 个人学习笔记oracle 个人学习笔记oracle 个人学习笔记oracle 个人学习笔记oracle 个人学习笔记
Oracle非常有用的笔记。。。。。。。。。。。Oracle学习笔记Oracle学习笔记Oracle学习笔记Oracle学习笔记
oracle学习笔记 oracle学习笔记oracle学习笔记 oracle学习笔记
本文档主要是网易云李兴华老师进行授课时所作笔记,从Oracle11g数据库的安装到复杂查询做了详细的文档记录。
oracle学习笔记,包含所有oracle概念,包,函数,oracle的组成,oracle的plsq,oracle的存储过程,oracle的事务等
Oracle学习笔记 以下是我这一周学习oracle整理的笔记,包括课堂的内容和自己看额外看的视频补充的一些内容,基本上囊括了所有oracle的基本知识。主要的形式是例子代码加代码解释加运行结果,我个人认为对于没有学习...
oracle学习笔记-入门基础-01-张园
分享给需要的朋友们韩顺平oracle学习笔记.pdf 大家来下载吧!
资源名称:Oracle学习笔记-日常应用、深入管理、性能优化内容简介:Oracle学习笔记-日常应用、深入管理、性能优化Oracle 11g是最具代表性的高端关系型数据库管理系统,它在世界各地的大型商务数据库应用系统中被广泛...
学习oracle知识笔记整理,包括pl/sql编程,过程、函数、游标开发等。
oracle学习笔记
超详细Oracle学习笔记,详细记录了oracle的学习过程中遇到的各种问题及基础知识,适合初中级oracle使用人员学习参考。
Oracle学习笔记
MSDN oracle学习笔记 很好的学习资料
Oracle10g学习笔记,Oracle10g学习笔记Oracle10g学习笔记Oracle10g学习笔记Oracle10g学习笔记Oracle10g学习笔记