Oracle笔记002

Oracle表的管理

1.char类型   （定长，不够用空格补全）最高2000字符；

    varchar2类型（变长，根据数据的大小改变长度，节省空间） 最高4000字符

2.数字型

    number  范围-10的-38次方--10的38次方 可以表示整数和小数

    number（5,2） 表示一个小数有5位有效数，2位小数 范围-999.99--999.99

    number（5）  表示一个5位整数 范围-99999--99999

3.日期类型

date 包含年月日和时分秒

timestamp这是oracle9i对date数据类型的扩展（精度很高，一般用不着）

4.图片

blob 二进制数据 可以存放图片/声音4G（一般不图片，只存地址，处于安全的考虑可以存放在数据库里面）

5.添加一个字段

Alter table student add(classId number(2));

  修改字段长度

  Alter table ****** modify(xm varchar2(30));

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

Alter table ****** modify(xm char(30));

  删除一个字段

Alter table ****** drop column sal;   (基本上不用)

  修改表的名字

Rename student to stu;

  删除表

Drop table student;

6.添加数据

、所有字段都插入

insert into student values(1,'小明','男','11-12月-1997',2345.6,12);  //注意日期格式；

也可以自定义日期格式

、alter session set nls_date_format='yyyy-mm-dd';

7.查询数据

查询没有生日的人：select * from student where birthday is null;

8.修改数据

、改一个字段

Update student set sex=’女’ where xh=’A001’;

、修改多个字段

Update student set sex=’男’,birthday=’1980-04-01’ where xh=’A001’;

、修改含有null值的数据

类似于7中所说的查找生日，不要用等号，要用is.

9.删除数据

Savepoint a;创建保存点

Delete from student;//删除表中的数据;//删除所有记录，表结构还在，写日志，可以恢复，速度慢

Rollback to a；回滚至还原点a.

-----------------------------

Drop table student; 删除表的结构和数据

Delete from student where xh=’A001’;删除一条记录

Truncate table student;删除表中所有的记录，表结构还在，不写日志，无法找回删除的记录，速度快。

 

EMP表

EMP NO雇员编号、ENAME雇员名字、JOB工作-CLERK普通雇员-PRESIDENT老大-MANAGER经理、MGR上级的编号、HIREDATE入职时间、SAL薪水、COMM奖金、DEPTNO部门

 

Dept表

DEPTNO部门编号、DNAME部门名字-ACCOUNTING财务部-RESERCH研发部门-SALES销售部-OPERATIONS业务部、LOC部门所在地点

 

10.简单查询语句

查看表结构Desc dept          

查询所有列 Select * from dept

查询指定列Select  ename,sal,job,deptno from emp;(会有重复的行↓)

如何取消重复行 select distinct deptno.job from emp;

11.使用算数表达式

、显示每个雇员的年工资：

、、使用列的别名

、、、select ename “姓名”,sal*12 as “年收入” from emp;

、如何处理null值（有null值的表达式结果也是null值）

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

如果comm的值是null则用0代替

、如何连接字符串（||）

Select ename ||’is a’||job from emp;

12.简单的查询语句

a）如何显示工资高于3000的员工和工资

select ename,sal from emp where sal>3000;

b）如何查找1982.1.1后入职的员工和工资

Select ename,hiredate from emp where hiredate>’(1-1月-1982)’;

c）如何显示工资在2000-2500之间的员工和工资

Select ename,sal from emp where sal>2000 and sal<2500;

  、如何使用like操作符

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

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

Select ename,sal from emp where ename like ‘S%’;

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

Select ename,sal from emp where ename like ‘__O%’;

  、如何查询员工号为123,234,345的员工的雇员情况

Select * from emp where empno in (123,234,345);

  、如何查询没有上级的字段

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;（（desc）从高到低排序，默认asc升序）

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

select * from emp order by deptno,sal desc;

  、使用列的别名排序

Select ename,sal*12 “年薪” from emp order by “年薪” asc;别名需要用引号

  、分页查询

按雇员的id号升序取出

13.复杂查询

a)数据分组-max，min，avg，sum，count

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

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

显示所有员工的平均工资和工资总和、、绿色部分是平均工资

select * from emp where sal<(select avg(sal) from emp);

计算有多少员工

Select count(*) from emp;

--扩展要求

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

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

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

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

b)group by 和 having字句

分部门查找最高工资group by

select avg(sal),max(sal),deptno from empgroup by deptno;

每个部门每种岗位的平均工资和最高工资

select avg(sal),max(sal),deptno,job from emp group bydeptno,job;//直接连用

显示平均工资低于2000的部门号和它的平均工资//having语句用于限制

select avg(sal),max(sal),deptno 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中。