oracle实战第二天 | 您所在的位置:网站首页 › oracle更新表字段长度 › oracle实战第二天 |
第二天
内容介绍 1.oracle的表的管理。 2.基本查询。 3.复杂查询。 4.oracle数据库的创建。
期望目标 1.掌握oracle表的管理(创建/维护)。 2.掌握对oracle表的各种查询技巧。 3.学会创建新的oracle数据库。
表名和列的命名规则: 1.必须以字母开头。 2.长度不能超过30字符。 3.不能使用oracle的保留字。 4.只能使用如下字符A-Z,a-z,0-9,$,#等。
Oracle支持的数据类型
1.字符型 Char 定长 最大2000字符。 例子:char(10) ‘小明’前四个字符放’小明’,后添6个空格补全。 Varchar2(20) 变长 最大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
Oracle表的管理 怎样创建表: 建表 学生表 create table student( sno number(4), sname varchar2(20), sex char(2), birthday date, sal number(7,2));
班级表 create table classes( cid number(2), cname varchar2(40));
修改表 添加一个字段 alter table student add (cid number(2)); 修改字段的长度 alter table student modify (sanme varchar2(30)); 修改字段的类型或是名字(不能有数据) alter table student modify (sname char(30)); 删除一个字段 alter table student drop column sal; 修改表的名字 rename student to stu; 删除表 drop table student;
添加数据 所有字段都插入 insert into student values ('A001','zhangsan','man','01-05月-05',10); Oracle中默认的日期格式为’dd-mon-yy’ 改日期的格式: alter session set nls_date_format = 'yyyy-mm-dd'; 修改后就可以用我们熟悉的方式进行数据插入 insert into student values('A002','Mike','male','2003-05-09',10);
插入部分字段 insert into student(sid,sname,sex) values('A003','john','female'); 插入空值 insert into student(sid,sname,sex,birth) values('A004','martin','male',null);
修改数据
修改一个字段 update student set sex = 'male' where sid = 'A001'
修改多个字段 update student set sex = 'male', birth = '1980-04-01'where sid = 'A001'
修改含有null值的数据 update student set birth = '1900-01-01' where birth is null;
删除数据
删除所有记录,表结构还在,写日志,可以恢复的,速度慢。 delete from student; 删除表的结构和数据。 drop table student; 删除一条记录 delete from student where sid = 'A001'; 删除表中所有记录,表结构还在,不写日志,无法找回删除的记录,速度快。 truncate table student;
一个有经验的DBA ,在开始工作前会先做一个保存点 Savepoint aa; 以便于在操作出现错误时,可以返回到操作之前的状态 rollback to aa;
表的基本查询 以scott用户中的表(emp,dept)作演示; Sql>clear;--清屏 查看表结构: desc dept; 查询所有列 select * from dept; 查询指定列 select ename,sal,job,deptno from emp; 如何取消重复行 select distinct deptno,job from emp; 显示操作时间 set timing on; 演示查询所有列和查询指定列耗时的差别 create table users( userid varchar2(20), username varchar2(30), userpss varchar2(30)); insert into users values('A0001','zhangfachao','[email protected]'); --重复执行这条语句直到插入8192行 insert into users(userid,username,userpss) select * from users; 效果对比 select * from users; 8192 rows selected Executed in 102.5 seconds
select username from users; 8192 rows selected Executed in 66.86 seconds 从结果可以看出,查询所有列比查询指定列耗时得多。 查询指定一行记录 select ename,deptno,job,sal from emp where ename='SMITH'; 使用算术表达式和列的别名 select ename,sal*12 as yearsal from emp; 如何处理null值 select sal*13+nvl(comm,0)*13 as yearsal,ename from emp; Nvl函数用于某字段为空时,给一个默认值。 如何连接字符串(||) select ename || ' is a ' || job from emp;
使用where子句 显示工资高于3000的员工 select ename,sal,job from emp where sal > 3000; 查出1982.1.1后入职的员工 select ename,sal,job from emp where hiredate > '1-1月-1982'; 显示工资在2000到2500的员工情况 select ename,sal,job from emp where sal between 2000 and 2500;
如何使用like操作符: %:表示0到多个字符 _:表示单个字符 显示首字符为S的员工姓名和工资 select ename,sal from emp where ename like 'S%'; 显示第三个字母为大写O的所有员工的姓名和工资 select ename,sal from emp where ename like '__O%';
在where条件中使用in: 显示empno为7844,7788,7698…的雇员情况 select * from emp where empno in (7844,7788,7698); 使用is null的操作符: 显示没有上级的雇员的情况 select * from emp where mgr is null;
使用逻辑操作符号 查询工资高于500或是岗位为manager的雇员,同时还要满足他们的姓名首写字母为大写的J select * from emp where (sal >500 or job='MANAGER')and ename like 'J%';
使用order by字句 按照工资从低到高的顺序显示雇员的信息 select * from emp order by sal asc; 按照部门号升序而雇员的工资降序排列 select * from emp order by deptno asc,sal desc;
使用列的别名排序(中文的列名要使用””号括起来) select ename,sal*12 "年薪" from emp order by "年薪" asc;
Oracle表复杂查询 说明:在实际应用中经常需要执行复杂的数据统计,经常需要显示多张表的数据,主要用到了select语句 数据分组-max,min,avg,sum,count 如何显示所有员工中最高工资和最低工资 select max(sal),min(sal) from emp; 显示所有员工的平均工资和工资总和 select avg(sal),sum(sal) from emp; 计算共有多少员工 select count(*) from emp;
扩展要求 请显示工资最高的员工的名字,工作岗位。 select ename,job from emp where sal in (select max(sal) from emp); 请显示工资高于平均平均工资的员式信息。 select * from emp where sal > (select avg(sal) from emp);
Group by和having子句 Group by用于对查询的结果分组统计 Having子句用于限制分组显示结果 如何显示每个部门的平均工资和最高工资 select min(sal),avg(sal),max(sal),deptno from emp group by deptno; 显示每个部门的每种岗位的平均工资和最高工资 select min(sal),avg(sal),max(sal),deptno,job from emp group by deptno,job; 显示平均工资低于2000的部门号和它的平均工资 select min(sal),avg(sal),max(sal),deptnofrom empgroup by deptnohaving avg(sal) > 2000order by deptno;
对数据分组的总结 1.分组函数只能出现在选择列表、having、order by子句中。 2.如果在select语句中同时包含有group by, having,order by,那么他们的顺序是 Group by Having Order by 3.在选择列中如果有列,表达式和分组函数,那么这些列和表达式必须有一个出现在group by子句中,否则就会出错。 如: select min(sal),avg(sal),max(sal),deptnofrom empgroup by deptnohaving avg(sal) 这里deptno一定要出现在group by中。
Oracle表复杂查询 多表查询 多表查询是指基于两个以上的表或是视图的查询。在实际应用中,查询单个表可能不能满足你的需求(如显示sales部门位置和其员工的姓名),这种情况下需要使用到dept表和emp表。 规定:多表查询的条件是至少不能少于表的个数减1 显示雇员员,雇员工资及所在部门的名字(笛卡尔集) select ename,sal,dname from dept,emp where dept.deptno = emp.deptno; 如何显示部门为10的员工名、工资、部门名称。 select e.ename,e.sal,d.dname from emp e,dept d where e.deptno= d.deptnoand e.deptno = 10; 显示各个员工的姓名,工资及其工资的级别。 select e.ename,e.sal,s.gradefrom emp e,salgrade swhere e.sal between s.losal and s.hisal;
扩展要求: 显示雇员名,雇员工资及所在部门的名字,并按部门排序。 select e.empno,e.sal,d.dnamefrom emp e,dept dwhere e.deptno = d.deptnoorder by d.deptno;
自连接 自连接是指在同一张表的连接查询 显示某个员工的上级领导的名字 select e1.ename worker,e2.ename bossfrom emp e1,emp e2where e1.mgr = e2.empno; 扩展要求: 只显示”FORD”的上级 select e1.ename worker,e2.ename bossfrom emp e1,emp e2where e1.mgr = e2.empnoand e1.ename = 'FORD';
子查询 子查询是指嵌入在其它sql语句中的select语句,也叫嵌套查询
单行子查询 单行子查询是指只返回一行数据的子查询语句 请思考:如何显示与SMITH同一部门的所有员工? select * from empwhere deptno = (select deptno from emp where ename = 'SMITH');
多行子查询 多行子查询指返回多行数据的子查询。 请思考:如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号。 select * from emp where job in (select distinct job from emp where deptno = 10);
在多行子查询中使用all操作符 请思考:如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号 select ename,sal,deptno from empwhere sal > all (select sal from emp where deptno = 30); 扩展要求: 想想还有没有别人的查询方法。 select ename,sal,deptno from empwhere sal > (select max(sal) from emp where deptno = 30);
在多行子查询中使用any操作符 请思考:如何显示工资比部门30的任意一个员工的工资高的员工的姓名、工资、部门号 select ename,sal,deptno from empwhere sal > any (select sal from emp where deptno = 30); 扩展要求: 想想还有没有别的方法 select ename,sal,deptno from empwhere sal > (select min(sal) from emp where deptno = 30);
多列子查询 多列子查询是指查询返回多个列数据的子查询语句。 请思考:如何查询与SMITH的部门和岗位完全相同的所有雇员。 select * from empwhere (deptno,job) =(select deptno,job from emp where ename='SMITH');
在from 子句中使用子查询 请思考:如何显示高于自己部门平均工资的员工的信息。 select e1.ename,e1.sal,e2.avgsal,e1.deptno from emp e1,(select deptno,avg(sal) avgsal from emp group by deptno) e2where e1.deptno = e2.deptnoand e1.sal > e2.avgsal;
这里需要说明的是,当在from子句中使用子查询时,该子查询会被当作为一个视图来对待,因此叫作内嵌视图,当在from子句中使用子查询时,必须给子查询指定别名。
分页查询 Oracle分页一共有三种: 假设有七万条记录 1.rownum分页(花了0.1秒,排第二,查询效率一般,容易理解) select * from (select t.*,rownum rn from (select * from emp) t where rownum )where rn > 4; 在分页基础上的几个查询变化: a.指定查询列,只需修改最里层的子查询 select * from (select t.*,rownum rn from (select ename,deptno,sal,job from emp) t where rownum )where rn > 4; b.如何排序 select * from (select t.*,rownum rn from (select ename,deptno,sal,job from emp order by deptno desc) t where rownum )where rn > 4;
2.根据rowid来分(花了0.03秒,排第一,查询效率最高,最不容易理解) select * from empwhere rowid in (select rid from (select rownum rn ,rid from (select rowid rid,deptno from emp order by deptno desc) where rownumwhere rn>4) order by deptno desc;
3.按分析函数来分(花了1.01秒,排第三,查询效率最差,最容易理解) select * from (select t.*,row_number() over (order by deptno desc) rk from emp t)where rkand rk >4;
推荐使用第一和第二种。
用查询结果创建新表 这个命令是一种快捷的建表方法 create table t_emp(id,name,sal,job,deptno)as select empno,ename,sal,job,deptno from emp;
合并查询 有时在实际应用中,为了合并多个select语句的结果,可以使用集合操作符号 Union union all intersect minus a.union 该操作符用于取得两个结果集的并集。当使用该操作符时,会自动去掉结果集中重复行,并按第一列默认升序排序。 select ename,sal,job from emp where sal > 2500union select ename,sal,job from emp where job = 'MANAGER';
b.union all 该操作相似于union,但是它不会取消重复行,而且不会排序。 select ename,sal,job from emp where sal > 2500union allselect ename,sal,job from emp where job = 'MANAGER';
c.intersect 使用该操作符用于取得两个结果集的交集。 select ename,sal,job from emp where sal > 2500intersectselect ename,sal,job from emp where job = 'MANAGER';
d.minus 使用该操作符用于取得两个结果集的差集,它只会显示存在第一个集合中,而不显示存在第二个集合中的数据。 select ename,sal,job from emp where sal > 2500minusselect ename,sal,job from emp where job = 'MANAGER';
创建数据库有两种方法: 1,通过oracle提供的向导工具database configuration assistant。 2,我们可以手工步骤直接创建。
|
CopyRight 2018-2019 实验室设备网 版权所有 |