oracle查询表有多少数据类型,Oracle基础 二（Oracle 数据类型、多表查询、...

Oracle 数据类型

数据类型

参数

描述

char(n)

n=1 to 2000字节

定长字符串，n字节长。如果不指定长度，缺省为1个字节长(一个汉字为2字节)

varchar2(n)

n=1 to 4000字节

①可变长的字符串，具体定义时指明最大长度n，这种数据类型可以放数字、字母以及ASCII码字符集(或者EBCDIC等数据库系统接受的字符集标准)中的所有符号。

②如果数据长度没有达到最大值n，Oracle 8i会根据数据大小自动调节字段长度。

③如果你的数据前后有空格，Oracle 8i会自动将其删去。VARCHAR2是最常用的数据类型。可做索引的最大长度3209。

number(m,n)

m=1 to 38 n=-84 to 127

可变长的数值列，允许0、正值及负值，m是所有有效数字的位数，n是小数点以后的位数。

date

无

①从公元前4712年1月1日到公元4712年12月31日的所有合法日期。

②Oracle 8i其实在内部是按7个字节来保存日期数据，在定义中还包括小时、分、秒。缺省格式为DD-MON-YY，如07-11月-00 表示2000年11月7日。

long

无

①可变长字符列，最大长度限制是2GB，用于不需要作字符串搜索的长串数据，如果要进行字符搜索就要用varchar2类型。

②long是一种较老的数据类型，将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代。

raw(n)

n=1 to 2000

①可变长二进制数据，在具体定义字段的时候必须指明最大长度n，Oracle 8i用这种格式来保存较小的图形文件或带格式的文本文件，如Miceosoft Word文档。

②raw是一种较老的数据类型，将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代。

long raw

无

①可变长二进制数据，最大长度是2GB。Oracle 8i用这种格式来保存较大的图形文件或带格式的文本文件，如Miceosoft Word文档，以及音频、视频等非文本文件。

②在同一张表中不能同时有long类型和long raw类型，long raw也是一种较老的数据类型，将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代。

blob、clob、nclob

无

①三种大型对象(LOB)，用来保存较大的图形文件或带格式的文本文件，如Miceosoft Word文档，以及音频、视频等非文本文件，最大长度是4GB。

②LOB有几种类型，取决于你使用的字节的类型，Oracle 8i实实在在地将这些数据存储在数据库内部保存。可以执行读取、存储、写入等特殊操作。

bfile

无

①在数据库外部保存的大型二进制对象文件，最大长度是4GB。这种外部的LOB类型，通过数据库记录变化情况，但是数据的具体保存是在数据库外部进行的。

②Oracle 8i可以读取、查询BFILE，但是不能写入。大小由操作系统决定。

多表查询

指的是至少两张表具有关联关系的查询。

笛卡尔集

Select * from emp,dept;

会产生笛卡儿积建议不适用！

交叉连接 cross join

select * from empt CROSS JOIN dept;

产生笛卡尔积

给数据库起别名

select e.ename,e.job,e.sal,d.dname from emp e,dept d where e.empno=d.deptno;

给数据库起别名在多表查询里面大量使用，效率极高！

左右连接

select e.ename,e.job,e.sal,d.dname from emp e,dept d where e.empno=d.deptno; 这句话中等号左边的表为主表，右边为子表

左连接

select * from dept;

select e.empno,e.ename,d.deptno,d.dname,d.loc from emp e,dept d where e.deptno(+)=d.deptno;

右连接

select * from dept;

select e.empno,e.ename,d.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno(+);

①+在左边表示左连接，查询时以右边的表为查询条件

②+在右边表示右连接，查询的以左边的表为查询条件

自连接 natural join

select * from emp natural join dept;

可避免笛卡儿积 建议使用！

USING子句

select * from emp e JOIN dept d USING (deptno) where deptno=30;

分组函数

组函数

常用组函数有如下几个：

COUNT()求全部记录数

MAX()求最大记录数

MIN()求最小记录数

AVG()平均

SUM()求和

GROUP BY 子句

出现在 SELECT 列表中的字段或者出现在 order by 后面的字段.

如果不是包含在分组函数中，那么该字段必须同时在 GROUP BY 子句中出现。包含在 GROUP BY 子句中的字段则不必须出现在 SELECT 列表中。

可使用 where 字句限定查询条件

可使用 Order by 子句指定排序方式

如果查询中包含聚合函数，而所选择的列并不在聚合函数中，那么这些列就必须在GROUP BY子句中。

select deptno,COUNT(empno) from emp GROUP BY deptno;

HAVING 子句

WHERE子句只能用来对单行而不是行组进行过滤。要过滤行组，需使用HAVING子句。

select deptno, job, avg(sal)from emp

where hiredate >= to_date('1981-05-01','yyyy-mm-dd')

group by deptno,job

having avg(sal) > 1200

order by deptno,job;

练习

显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于￥5000。输出结果按月工资的合计升序排列

1.显示全部的非销售人员：job<>’SALESMAN’

select * from emp where job<>'salesman';

2.按工作分组同时求出工资的总和

Select job,SUM(sal) from emp WHERE job<>’SALESMAN’ GROUP BY job;

3.对分组条件进行限制

Select job,SUM(sal) from emp WHERE job<>’SALESMAN’ GROUP BY job HAVING SUM(sal)>5000;

4.使用排序，按升序排列

Select job,SUM(sal) su from emp WHERE job<>’SALESMAN’ GROUP BY job HAVING SUM(sal)>5000 order by su;

分组的简单原则

只要一列上存在重复的内容才考虑用分组

注意：分组函数可以嵌套使用，但是在组函数嵌套的时候不能再出现分组条件的查询语句

子查询

子查询的满足的条件

1：至少包含2个或者2和以上的 select 语句

2：将第一个select查询的结果。给第二个select作为条件去继续查询！

3：select 里面 可以嵌套select ----叫嵌套子查询

单行子查询

select * from emp

where sal > (select sal from emp where empno = 7566);

多行子查询

select * from emp

where sal > any(select avg(sal) from emp group by deptno);

select * from emp

where sal > all(select avg(sal) from emp group by deptno);

select * from emp

where job in (select job from emp where ename = 'MARTIN' or ename = 'SMITH');

子查询空值/多值问题

如果子查询未返回任何行，则主查询也不会返回任何结果

(空值)

select * from emp where sal > (select sal from emp where empno = 8888);

如果子查询返回单行结果，则为单行子查询，可以在主查询中对其使用相应的单行记录比较运算符

(正常)

select * from emp where sal > (select sal from emp where empno = 7566);

如果子查询返回多行结果，则为多行子查询，此时不允许对其使用单行记录比较运算符

(多值)

select * from emp where sal > (select avg(sal) from emp group by deptno);//非法

数据库更新操作

数据库的主要操作分为两种：

1…数据库的查询操作SELECT

2…数据库的更新操作 UPDATE， DELETE， INSERT

此时为了保存原始的emp表的信息，在进行更新 删除 插入表前先将表复制一份

Create table myemp AS select * from emp;此时数据已经复制出来

添加数据(INSERT)

Insert into myemp(empno,ename,job,hiredate,sal ,deptno)

Values (7899,’张三’,’清洁工’,TO_DATE(‘2009-07-19’,’yyyy-mm-dd’),9000, 40);

--使用TO_DATE()函数，将一个字符串类型的数据变为DATE类型的数据。

修改数据(UPDATE)

UPDATE 表名称 set 要修改的字段=新值，要修改的字段=新值….;

UPDATE 表名称 set 要修改的字段=新值，要修改的字段=新值…WHERE 修改条件.;

update emp set ename ='刘德华1';

update emp set ename ='刘德华1' where empno=7899;

update emp set ename ='刘德华1',job='明星' where empno=7899;

通过update…set修改表数据， 是安全的！不会破坏表数据 和 表数据！

删除数据(DELETE )

删除全部: DELETE FROM 表名称

局部删除: DELETE FROM 表名称 WHERE 删除条件;

事物处理

在 oracle 中每个连接都会产生一个 Session,一个 session 对数据库的修改，不会立刻反映到

数据库的真实数据上，是允许回滚的。只有当提交了，才变成持久数据了。

提交事务：COMMIT

回滚：Roolback

可能出现死锁的情况(如何保持数据完整性)

1.原子性(Atomicity)

2.一致性(Consistency)

3.隔离性(Isolation )

4.持久性(Durability )

原子性和一致性的差别？

①原子性：只一个事务中，包含若干个数据操作，这些操作是一个整体，要么一起完成，要么一起不完成，不能只完成其中的一部分。

比如你去银行转帐，从一个账户转帐到另一个账户，这是一个完整的事务，包括两个操作，从你第一个账户读数，增加到第二个账户，并减去第一个账户中的钱，如果这些操作有一个失败了，整个事务都必须还原成最开始的状态。

②一致性：是指数据库从一个完整的状态跳到另一个完整的状态，是用于保护数据库的完整性的。比如你修改数据库的某个外键值，如果没有和相应的主键对应，就违反了数据库的一致性。另外，还有读一致性，如：你刚写入一个数到数据库中，但还没有提交，这时候有人要读这个数，就涉及完整性问题，要保证读取的数据在整个数据库中是处于和其他数据一致的一个状态。

附：查询所需表