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一样。
