oracle7天内任意几天,7天玩转oracle笔记2-表的管理

7.oracle表的管理

1.表和列的命名规范

必须字母开头

长度小于30字符

不能用oracle保留字

只能使用如下字符A_Z,a_z,0-9,$,#等

2.oracle支持的数据类型

字符型

char --定长，最大2000字符，查询效率高，浪费空间。

-例：char(10) “小韩”，前四个字符放“小韩”，后添6个空格补全。

varchar2(20) --变长，最大4000字符，节省空间，效率低。

-例：varchar2(10) “小韩”，oracle分配4个字符，这样节省空间。

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

数字型

number --可以表示整数，也可表示小数；范围 10的-38次方~10的38次方

例：number(5,2) --表示一个小数，总共有5个位数，2位小数，范围-999.99~999.99。

例：number(5) --表示一个5位整数，范围-99999~99999。

日期类型

date --年月日时分秒

timestamp --精确到毫秒级

图片

BLOB (binary large object) --二进制大对象，可以存放图片、声音、视频。

3.创建表

--用命令行，或pl/sql developer工具创建表

例：

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)

);

4.查询表

select * from user_tables; --查询当前用户所有表名；

select * from all_tables where owner='SYS'; --查询某用户的所有表名；

5.修改表

例：

-添加字段

alter table student add (classid number(2)) ;

-删除字段

alter table student drop column sal;

-修改字段类型(不能有数据)

alter table student modify (xm char(30))

-修改字段长度

alter table student modify (xm varchar2(30))

-修改字段名字(不能有数据)

alter table student rename colunm xm to name; --xm改名为name

-修改表名

rename student to stu;

-删除表

drop table student;

6.添加数据

添加所有字段数据

insert into student values(

'A001',

'张三',

'男',

'01-05月-05', --oracle默认日期格式'DD-MON-YY'。

10

);

oracle默认日期格式‘DD-NON-YY’，如：'09-6月-99'，表示1999年6月9日。

修改默认日期格式(临时生效)：

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

修改后，可以用新格式添加日期：

insert into student values (

'A002',

'MIKE',

'男',

'1905-05-06', --新日期格式'yyyy-mm-dd'。

10

);

添加部分字段数据

insert into student (xh,xm,sex) values('A003','ROSE','女');

插入空值

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

7.修改数据

改一个字段

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

修改多个字段

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

修改含有null值的数据

update student set birthday='1980-05-01' where birthday is null; --不能用=null。

8.删除数据

delete from student where xh='A001'; --删除一条记录。

delete from student; --只删数据，不删表结构，写日志，可恢复，速度慢。

truncate table student; --只删数据，不删表结构，不写日志，无法恢复，速度快。

drop table student; --删除表结构和数据，不可恢复。

9.查询数据

简单查询

查看表结构

desc dept;

显示查询耗时

set timing on;

查询所有列

select * from dept;

查询指定列

select ename,sal,job,deptno from emp;

如何取消重复行

select distinct deptno,job from emp;

使用算术表达式

select sal*12,ename from emp;

使用列别名

select sal*12 as '年工资',ename ‘姓名’ from emp;

null值处理

select sal*12+nvl(comm,0)*12,ename from emp; --nvl(column,0)空值转换为0函数。

连接字符串||

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

where子句

select ename,sal from emp where sal>3000; --显示工资高于3000的员工。

select ename from emp where hiredate>'1-1月-1982'; --查找1982.1.1后入职的员工，注意使用oracle默认日期格式才行。

select ename,sal from emp where sal>=2000 and sal<=2500; --显示工资大于2000，小于2500的员工。

like操作符

%--0到多个任意字符； _--任意单个字符；

select ename,sal from emp where ename like 'S%'; --显示首字符为S的员工姓名和工资。

select ename,sal from epm where ename like '__O%'; --显示第3个字符为大写O的所有员工姓名和工资。

in操作符

select * from emp where empno in (788,322,889); --显示员工编号为788,322,889,...的雇员情况。

is null操作符

select * from emp where mgr is null; --显示没有上级的雇员情况。

逻辑操作符and/or

排序子句order by

select * from emp order by deptno asc,empno desc; --按照部门编号升序，员工工资降序排列。

使用别名排序

select ename,(sal+nvl(comm,0))*12 as '年薪' from emp order by '年薪'; --按年薪排序员工情况。

复杂查询

数据统计 --max,min,avg,sum,count

数据分组 --group by 和having子句

多表查询

多表查询的条件如果小于表的个数减1，肯定会出现笛卡尔集，如果大于，也有可能出现笛卡尔集。

-自连接查询

例：。。。

-子查询 --嵌入在其他sql语句中的select语句，也叫嵌套查询。

-单行子查询：子查询语句只返回一列一行结果的。

例：select ename,deptno from emp where deptno=(select deptno from emp where ename='SMITH');--显示与SMITH同一部门的所有员工。

-多行子查询：子查询语句返回一列多行结果的。

例：select * from emp where job in (select distinct job from emp where deptno=10); --查询与10号部门的工作相同的员工信息。

-all操作符

例：select * from emp where sal> all (select sal from emp where deptno=30); --查询比30号部门所有人工资都高的员工信息。

或 select * from emp where sal>(select max(sal) from emp where deptno=30); --此方法效率较高。

-any操作符

例：select * from emp where sal > any (select sal from emp where deptno=30); --查询比30号部门任意一个人工资高的员工信息。

或 select * from emp where sal > (select min(sal) from emp where deptno=30); --此方法效率较高。

-多列子查询：子查询语句返回多列结果的。

例：select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH'); --查询与SMITH 部门和岗位完全相同的所有员工信息。

-from子句中使用子查询：将子查询结果作为一个表，又叫内嵌视图。

例：select a1.ename,a1.sal,a1.deptno,a2.avg_sal from emp a1,(select deptno,avg(sal) avg_sal from emp group by deptno) a2 where a1.sal>a2.avg_sal and a1.deptno=a2.deptno; --显示高于自己部门平均工资的员工信息。

-分页查询：3中方式。

1.ROWID分页(效率第一)

2.ROWNUM分页(效率第二)：公式--三层嵌套，第二层rownum要用别名RN，任何修改只需修改最里层查询即可。

例： select * from (select a1.*,rownum rn from (select * from emp order by empno) a1 where rownum<10) where rn>5; --按雇员ID号升序取出

3.分析函数分页(效率最差)

-合并查询union、union all、intersect、minus

union:并集去重复or

union all:并集不去重复or

intersect:交集and

minus:差集

创建数据库

dbca工具创建

手工创建

to_date函数

例：to_date('1988-1-1','yyyy-mm-dd')

10.子查询操作数据

用子查询创建表(快捷建表)

例：create table mytable (id,name,sal) as select empno,ename,sal from emp; --指定列建表。

或 create table mytable2 as select * from emp; --整表复制

使用子查询插入数据 --适合大量迁移数据

例：create table kkk (myid number(4),myname varchar2(50),mydept(5)); --创建一个空数据库。

insert into kkk select empno,ename,deptno from emp where deptno=10; --插入10号部门的员工信息到KKK表。

使用子查询更新数据

例：update emp set (job,sal,comm)=(select job,sal,comm from emp where ename='SMITH) where ename='SCOTT'; --更新SCOTT的岗位，工资，奖金信息与SMITH一样。