建站技术网专注分享网站建设技术,网站建设教程,真正帮你学会做网站技术,轻松解决网站建设过程中遇到的常见问题,为广大站长服务。 收藏本站 | 订阅
域名投资,就来找米网,认准网址:www.zhaomi.cc 专业仿站,就到仿站网:www.fangzhan.org
  站内公告: ·帝国cms教程内容已更新,欢迎关注     ·建站技术网采用阿里云服务器,备案已通    
您当前的位置:首页 > 数据库教程 > Oracle教程

oracle基础教程(第二天)

时间:2018-02-02 13:08:01  来源:网络收集  整理: 建站技术网 www.jzjsw.com     点击:7次

一、oracle表的管理


表名和列的命名规范

1.必须以字母开头

2.长度不能超过30字符

3.不能使用oracle的保留字(关键字)

4.只能使用如下字符 A-Z,a-z,0-9,$,#等


oracle支持的数据类型

1.字符型

    char    定长  最大2000字符        比较占用空间,但是对于定长的数据很实用,查询速度极快,例如身份证号字段设置为char(18)

    例子:char(10) '小韩'    前四个字符放'小韩',后添6个空格不全

    varchar2  变长  最大4000字符

    例子:varchar2(10)  '小韩'  oracle分配四个字符,这样可以节省空间

     clob(character large object)  字符型大对象  最大4G    

2.数字型

    number  范围  -10的38次方-10的38次方

    可以表示整数,也可以表示小数

    number(5,2)

    表示一个小数有5位有效数,2位小数

    范围:-999.99~999.99

    number(5)

    表示一个五位整数

    范围:-99999~99999

3.日期类型

    date                    包括年月日和时分秒

    timestamp          这是oracle9i对date数据类型的拓展

4.图片类型

blob    二进制数据    可以存放图片/声音  最大4G


建表

--学生表

    SQL>create table student(  --  表名

    xh number(4),    --    学号

    xm varchar2(20),    --    姓名

    sex char(2),    --    性别

    birthday date,    --    出生日期

    sal number(7,2)    --    奖学金

    );

--班级表

    SQL>create table class(

    classId number(2),

    cname varchar2(20)

    );

修改表

添加一个字段

SQL>alter table student add (classId number(2));

修改字段的长度

SQL>alter table student modify (xm varchar2(30));

修改字段的类型/或是名字(不能有数据)

SQL>alter table student modify (xm char(30));

删除一个字段

SQL>alter table student drop column sal;

修改表的名字

SQL>rename student to stu;

删除表

SQL>drop table student;

添加数据

所有字段都插入

SQL>insert into student values ('A001','张三','男','01-5月-05',,10);

ORACLE中默认的日期格式'DD-MON-YY'  dd日子(天) mon 月份  yy 2位的年  '09-6月-99'  1999年6月9日

改日期的默认格式

SQL>alter session set nls_date_format = 'yyyy-mm-dd';

修改后,可以用我们熟悉的格式添加日期类型:

SQL>insert into student values ('A002','MIKE','男','1905-05-06',,10);

插入部分字段

SQL>insert into student (xh,xm,sex) values ('A003','JOHN','女');

插入空值

SQL>insert into student (xh,xm,sex,birthday) values ('A004','MARTIN','男',null);

改一个字段

SQL>update student set sex='女' where xh='A001';

修改多个字段

SQL>update student set sex='男',birthday='1980-04-01' where xh='A001';

删除数据

SQL>delete from student;

删除所有记录,表结构还在,写日志,可以恢复,速度慢

需要设置保存点:savepoint aa;  回滚保存点:rollback to aa;

SQL>drop table student;   删除表的结构和数据

SQL>delete from student where xh='A001';  删除一条记录

SQL>truncate table student;

删除表中的所有记录,表结构还在,不写日志,无法找回删除的记录,速度快

简单的查询语句

查看表结构

SQL>desc dept;

查询所有列

SQL>select * from dept;

查询指定列

SQL>select ename,sal,job,deptno from emp;

如何取消重复行

SQL>select distinct deptno,job from emp;

使用算术表达式显示每个雇员的年工资

SQL>select sal*12+nvl(comm,0)*12     "年工资",ename,comm from emp;

使用列的别名

SQL>select ename "姓名",sal*12 "年收入" from emp;

如何处理null值

使用nvl函数来处理

如何连接字符串(||)

SQL>select ename || ' is a ' || job from emp;

使用where子句

如何显示工资高于3000的员工?

SQL>select * from emp where sal >=3000;

如何查找1982.1.1后入职的员工?

SQL>select * from emp where hiredate > '1-1月-1982';

如何显示工资在2000到2500的员工情况?

SQL>select ename,sal from emp where sal between 2000 and 2500;

如何使用like操作符

%:表示任意0到多个字符   _:表示任意单个字符

如何显示首字符为S的员工姓名和工资?

SQL>select ename,sal from emp where ename like 'S%';

如何显示第三个字符为大写O的所有员工的姓名和工资?

SQL>select ename,sal from emp where ename like '__O%';

在where条件中使用in

如何显示empno为123,345,800...的雇员情况?

SQL>select ename,empno from emp where empno in (123,345,7844);

使用is null的操作符

如何显示没有上级的雇员的情况?

SQL> select * from emp where mgr is null;

使用逻辑操作符号

查询工资高于500或是岗位为MANAGER的雇员,同时还要满足他们的姓名首字母为大写的J

SQL> select * from emp where (sal > 500 or job = 'MANAGER') and ename like 'J%';

使用order by子句

如何按照工资的从低到高的顺序显示雇员的信息?

SQL> select * from emp order by sal;

按照部门号升序而雇员的工资降序排列

SQL> select * from emp order by deptno,sal desc;

使用列的别名排序

SQL>select ename,sal*12 "年薪" from  emp order by "年薪";

oracle表复杂查询 - 单表查询

说明:在实际应用中经常需要执行复杂的数据统计,经常需要显示多张表的数据,现在我们给大家介绍较为复杂的select语句

数据分组函数-max,min,avg,sum,count

如何显示所有员工中最高工资和最低工资?

SQL>select max(sal),min(sal) from emp;

显示所有员工的平均工资和工资总和?

SQL> select sum(sal),avg(sal) from emp;

计算共有多少员工?

SQL> select count(*) from emp;


扩展要求:

请显示工资最高的员工的名字,工作岗位

SQL> select ename,sal from emp where sal = (select max(sal) from emp);

请显示工资高于平均工资的员工信息

SQL> select * from emp where sal > (select avg(sal) from emp);


group by和having子句

group by用于对查询的结果进行分组显示

having子句用于限制分组显示结果


如何显示每个部门的平均工资和最高工资

SQL>select avg(sal),max(sal),deptno from emp group by deptno;

显示每个部门的每种岗位的平均工资和最低工资

SQL>select avg(sal),min(sal),deptno,job from emp group by deptno,job;

显示平均工资低于2000的部门号和他的平均工资

SQL> select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)<2000;


对数据分组的总结

1.分组函数只能出现在选择列表、having、order by子句中

2.如果在select语句中同时包含有group by,having,order by,那么他们的顺序是group by,having,order by

3.在选择列中如果有列、表达式和分组函数,那么这些列和表达式必须有一个出现在group by子句中,否则会出错。

如select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)<2000;     这里deptno就一定要出现在group by中。



oracle表复杂查询 - 多表查询

说明

多表查询是指基于两个和两个以上的表或是视图的查询,在实际应用中,查询单个表可能不满足你的需求,(如显示sales部门位置和其员工的姓名),这种情况下需要使用到(dept表和emp表)


显示雇员名,雇员工资及其所在部门的名字【笛卡尔积】

规定:多表查询的条件是 至少不能少于表的个数-1

SQL>select t1.ename,t1.sal,t2.dname,t2.deptno from emp t1,dept t2 where t1.deptno=t2.deptno;

如何显示部门号为10的部门名、员工名和工资

SQL>select t1.ename,t1.sal,t2.dname,t2.deptno from emp t1,dept t2 where t1.deptno=t2.deptno and t1.deptno=10;

SQL>select t1.dname,t2.ename,t2.sal from dept t1,(select ename,sal,deptno from emp where deptno=10) t2 where t1.deptno=t2.deptno;

显示各个员工的姓名,工资,及其工资的级别

SQL>select t2.grade,t1.ename,t1.sal from emp t1,salgrade t2 where t1.sal between t2.losal and t2.hisal;

扩展要求:

显示雇员名,雇员工资及其所在部门的名字,并按部门排序。

SQL>select t2.ename,t2.sal,t1.dname,t1.deptno from dept t1,emp t2 where t1.deptno=t2.deptno order by t1.deptno;


自连接

自连接是指在同一张表的连接查询

显示某个员工的上级领导的姓名

SQL>select a1.ename,a1.mgr,a2.ename from emp a1,emp a2 where a1.mgr=a2.empno;


什么是子查询

子查询是指嵌入到其他sql语句中的select语句,也叫嵌套查询

单行子查询是指只返回一行数据的子查询语句

请思考,如何显示与smith同一部门的所有员工。

SQL>select * from emp where deptno=(select deptno from emp where ename='SMITH');

多行子查询是指返回多行数据的子查询语句

请思考,如何查询和部门10的工作相同的雇员的名字,岗位,工资和部门号

SQL>select ename,job,sal,deptno from emp where job in (select job from emp where deptno=10);

在多行子查询中使用all操作符

请思考,如何显示    工资比部门30的所有员工的工资高的员工的姓名,工资和部门号

SQL>select * from emp where sal > all (select sal from emp where deptno=30);

扩展要求:

大家想想还有没有别的查询方法。

SQL>select * from emp where sal > (select max(sal) from emp where deptno=30);

在多行子查询中使用any操作符

请思考,如何显示工资比部门30的任意一名员工的工资高的员工的姓名,工资和部门号

SQL>select * from emp where sal > any (select sal from emp where deptno=30);

扩展要求:

大家想想还有没有别的查询方法。

SQL>select * from emp where sal > (select min(sal) from emp where deptno=30);

多列子查询

多列子查询是指查询返回多个列数据的子查询语句

请思考如何查询与smith的部门和岗位完全相同的所有雇员

SQL> select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');

在from子句中使用子查询

请思考,如何显示高于自己部门平均工资的员工的信息

SQL>select * from emp t1,(select deptno,avg(sal) avg_sal from emp group by deptno) t2 where t1.sal>t2.avg_sal and t1.deptno=t2.deptno;

这里需要说明的是当在from子句中使用子查询的时候,该子查询会被作为一个视图来对待,因此叫做内嵌视图。党在from子句中使用子查询时,必须给子查询指定别名。


分页查询

oracle分页一共有三种方式:

1、rownum分页

(select * from emp)

2、显示rownum[oracle分配的]

SQL>select a1.*,rownum rn from (select * from emp) a1;

3、按rownum分页查询

SQL>select * from (select a1.*,rownum rn from (select * from emp) a1) where rn<=10;

SQL>select * from (select * from (select a1.*,rownum rn from (select * from emp) a1) where rn<=10) where rn>=6;

4、几个查询变化

a.指定查询列,只需要修改最里层的子查询

b.如何排序,只需要修改最里层的子查询


用查询结果创建新表

这个命令是一种快捷的建表方法

SQL>create table mytable(id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp;


合并查询

有时候在实际应用中,为了合并多个select语句的查询结果,可以使用集合操作符号,union(合并去重),union all(合并不去重),intersect(取交集),minus(取差集)。比and,or的效率高很多。


在oracle中操作数据-使用特定格式插入日期值

如何插入列带有日期的表,并按照年-月-日的格式插入。

SQL>insert into emp values('9996','ROSE','CLERK','7902',to_date('1993/7/22','yyyy/mm/dd'),'1000.00','200.00','10');


SQL>select * from emp where to_char('RKSJ','yyyy-mm-dd') >='2016-05-12';


在oracle中操作数据-使用子查询插入数据

当使用values子句时,一次只能插入一行数据,当使用子查询插入数据时,一条insert语句可以插入大量的数据。当处理行迁移或者装载外部表的数据到数据库时,可以使用子查询来插入数据。

SQL>insert into kkk (myid,myname,mydept) select empno,ename,deptno from emp where deptno = 10;


在oracle中操作数据-使用子查询更新数据

使用update语句更新数据时,既可以使用表达式或者数值直接修改数据,也可以使用子查询修改数据

希望员工scott的岗位、工资和补助与smith员工一样。

SQL>update emp set (job,sal,comm) = (select job,sal,comm from emp where ename = 'SMITH') where ename = 'SCOTT';


文章标题:oracle基础教程(第二天)
文章网址:http://www.jzjsw.com/shujuku/Oracle/3787.html
上一篇:oracle基础教程(第一天)
下一篇:oracle基础教程(第三天)Java操作数据库
来顶一下
返回首页
返回首页
相关文章
    无相关信息
推荐资讯
如何通过SEO优化排名赚钱?SEO赚钱方法
如何通过SEO优化排名赚
seo优化教程:网站seo内容优化
seo优化教程:网站seo
SEO细节:企业SEO如何快速为新站做出效果
SEO细节:企业SEO如何
电商技术将如何发展?2018年有这五个大胆预测
电商技术将如何发展?
最新文章
栏目更新
栏目热门