首页 > 代码库 > Oracle的学习二:表管理(数据类型、创建/修改表、添加/修改/删除数据、数据查询)

Oracle的学习二:表管理(数据类型、创建/修改表、添加/修改/删除数据、数据查询)

1.Oracle表的管理

  • 表名和列名的命名规则:
  • 必须以字母开头;
  • 长度不能超过30个字符;
  • 不能使用oracle的保留字;
  • 只能使用如下字符:A-Z, a-z, 0-9, $, # 等。
  • Oracle支持的数据类型:
  • 字符型

char 定长 最大2000个字符

char(10)----‘小韩‘ 前四个字符放‘小韩‘,后添加6个空格补全。(查询效率高)

varchar2(20) 变长 最大4000个字符

vachar2(10)----‘小韩‘ oracle分配四个字符,这样可以节省空间。

clob(character large object的缩写) 字符型大对象 最大4G

  • 数字型

number ---范围: 负的10的38次方----10的38次方; ---可以表示整数,也可以表示小数

eg:number(5,2) ---表示一个小数有5位有效数, 2位小数; 范围:-999.99 -- 999.99

number(5) --- 表示一个五位整数; 范围: -99999 -- 99999

  • 日期类型

date --- 包含年月日和时分秒

timestamp --- oracle9i对date数据类型的扩展,精确到毫秒级的。

  • 图片

blob --- 二进制数据, 可以存放图片/声音 4G

注意:存放图片/声音时,一般在数据库中存放路径,除非要求安全性特别高。

  • 创建表
  • create table 表名(列名 列类型,...);

查看表的结构: desc 表名;

  • 修改表
  • 添加一个字段:

sql>alter table 表名 add (列名, 列类型);

  • 修改字段的长度

sql>alter table student modify (列名, 列类型);

  • 修改字段的类型/名字(不能有数据)

sql>alter table student modify (列名, 列类型);

  • 删除一个字段(一般不建议此操作)

sql>alter table student drop column 列名;

  • 修改表的名字

sql>rename 表名 to 新表名;

  • 删除表

sql>drop table 表名;

  • 表查询(后面再说)
  • 插入数据
  • 所有字段都插入

insert into 表名 values(一条记录);

提示:Oracle中默认的日期格式是:‘DD_MON(eg:5月)-YY/YYYY‘;

更改日期的默认格式:alter session set nls_date_format = ‘yyyy-mm-dd‘;

  • 插入部分字段

insert into 表名(列1,列2,列3,...) values(值1,值2,值3,...);

  • 插入空值

insert into 表名(列1,列2,列3,...) values(值1,null(空值),值3,...);

  • 更新数据
  • 改一个或多个字段

update student set 列名1 = 实际值1,列名2 = 实际值2,... where 列名 =‘预提供值‘;

  • 修改含有null值的数据
  • 删除数据
  • delete from 表名; --- 删除所有记录,表结构还在,写日志,可以恢复,速度慢。

恢复数据 --- a.设置保存点:savepoint 点名称; b.回滚到保存点: rollback to 点名称;

  • drop table 表名; --- 删除表的结构和数据
  • delete from 表名 where 列名=‘值‘; --- 删除一条记录。
  • truncate table 表名; --- 删除表中的所有记录,表结构还在,不写日志,无法恢复,速度快。

2.Oracle表的基本查询

  • 简单的查询语句
  • 查看表结构:

desc 表名;

  • 查询所有列:

select * from 表名;

tips: 1.set timing on/off; ---开启时间消耗的开关;

2.表的列名大小写不区分,记录的值大小写时区分的

  • 查询指定列:

select 指定的列名 from 表名;

  • 取消重复行:

select distinct 列名1,列名2,... from 表名;

  • 使用算数表达式: + - * /
  • 使用列的别名(空格 或 as): 表的别名不用as,用空格

select ename(列名) "姓名(别名)", sal*12 as "年收入(别名)" from emp(表名);

当使用的别名中包含大小写、空格、特殊字符时,那么就需要双引号("")而不是单引号将其括起来;经常给表达式一个有意义的别名。

  • 如何处理null值:

使用nvl函数来处理: nvl(列名,0) --- 若记录对应的列为null,则列的值为0,否则为列的实际值。

  • 如何连接字符串(||): 字符串用单引号(‘‘)括起来。

eg:select ename || ‘is a ‘ || job from emp;

  • 使用where子句的查询:

eg:日期在1982年1月1日以后:time>‘1-1月-1982‘;sal在2000到2500之间: sal>2000 and sal<2500;

  • 查询条件:

1、比较运算符: <、 >、 <=、 >=、 <>/!、 =

2、使用逻辑操作符:OR AND NOT

3、其它运算符:

LIKE和NOT LIKE操作符: %:任意0到多个字符; _:表示任意单个字符; NOT LIKE/LIKE ‘匹配字符串‘ [ESCAPE ‘换码字符‘] eg:like ‘%\_A%‘ ESCAPE ‘\‘;

IN()和NOT IN():确定离散范围。 --- where 列名 in (列值1,列值2,列值3,...);

BETWEEN a AND b NOT BETWEEN a AND b: 确定连续范围。

IS NULL 和NOT IS NULL:测试是否为空

注意事项:

1.在where子句中使用数字值时,既可以使用单引号也可以不使用;使用字符串值、日期值时都必须使用单引号。并且字符串值时大小写敏感的,日期值是格式敏感的。

2.日期值的默认格式是DD-MM-YY,否则必须用TO_DATE函数转换。

  • 使用order by语句

order by 列名1 ASC(升序),列名2 DESC(降序);

注意:可以对多列进行排序;order by子句必须是最后一个子句;可以使用列的别名、列的位置进行排序;用于排序的列也可以不是查询的列(有distinct不行);

eg:select 列名1,列名2 as "别名",... from 表名 order by "别名" ASC;

  • 分页查询(三种方式) 速度:法二>法一>法三

法一:rownum分页: rownum --行页码 eg: 记录:6--10

a.做一个子查询,并看做是一个内嵌视图; select * from 表;

b.显示rownum[oracle分配的]; select a1.*,rownum rn from (select * from 表) a1;

c.显示行号小于10的记录;select a1.*,rownum rn from (select * from 表) as a1 where rownum<=10;

d.显示行号大于6的记录; select * from (select a1.*,rownumrn rn from (select * from 表) as a1 where rownum<=10) where rn>6;

解释:1.只查询部分列信息时,只需修改最里层表的指定列信息即可。

2.排序:只需最里层表的列信息排序即可,另外分组等也只需改动最里层表的列信息即可。

3.步骤a,b,c里的rownum、rn等的位置不能变动。

法二:根据ROWID来分:

select * from 表名 where rowid in

(select rid from

(select rownum rn, rid from

(select rowid rid,cid from 表名 order by cid desc)

where rownum<10000)

where rn>9980)

order by desc;

法三:按分析函数来分:

select * from (select t.*, row_number() over(order by cid desc) rk from 表名 t) where rk<10000 and rk>9980

3.Oracle表复杂查询

  • 数据分组
  • 分组函数:

max--列或表达式的最大值(数据最大、时间最晚),<--> min,

avg--列或表达式的平均值, sum--列或表达式的合计值

count--返回记录行的行数

variance --列或表达式的方差; stddev --列或表达式的标准差

注意:1.当在where中,列与分组函数比较查询时,可用子查询解决此问题。

2.分组函数只能出现在查询列表、having、order by子句中,绝对不能出现在where子句和group by子句中;

3.在选择列表中同时包含列、表达式和分组函数,那么这些列和表达式都必须出现在group by子句中,否子就会出错;

4.如果在select语句中同时包含有group by, having, order by, 那么他们的顺序是group by, having, order by;

5.除count(*)外,其它分组函数,包括count(列名)都会忽略列为NULL的列;

6.函数(distinct/all 列名)

  • group by 和 having

group by --- 用于对查询的结果分组统计;

分组方法:可以对一列或多列进行分组; 使用rollup和cube生成横向和纵向的统计结果。

having子句 --- 用于限制分组显示结果,组筛选--只显示满足条件的组;

1.having子句必须和group by子句一起使用,但group by子句可以自己使用。

eg:select ..... from 表名 where 条件 group by 列名1,列名2,...having 组筛选;

  • 连接查询

多表查询---基于两个或两个以上的表或视图的查询。笛卡尔集规定:多表查询的条件至少是表的个数-1(避免出现笛卡尔连接);

  • 多个不同表的查询

select a表的列1,...,b表的列1,... from a表,b表,... where a.列=b.列 AND 其它条件等 order by 列

  • 自连接 ---- 在同一张表的连接查询。

---- 做法:将一个表赋予两个不同的别名,其它的和多个不同表的查询一致;

  • 集合查询

为合并多个select语句的结果,可以使用集合操作符union、union all、intersect、minus。但注意:不同结果集的列个数和对应数据类型匹配(长度可不同); 不同结果集的列的名称可不同,采用第一个结果集的列名称; 只能有一个order by子句;

  • union

该操作符用于取得两个结果集的并集,使用时,会自动去掉结果集中的重复行。select ...from .. union select ... order by 列名;

  • union all

该操作符用于取得两个结果集的并集,使用时,不会去掉结果集中的重复行,并且不会排序。 用法类似。

  • intersect

该操作符用于取得两个结果集的交集。 用法类似。

  • minus

使用该操作符,取得两个结果集的差集,它只会显示存在于第一个集合但是不存在于第二个集合中的结果集。 用法类似。

  • 子查询 --- 指嵌入在其它sql语句中的select语句,也叫嵌套查询。
  • 单列子查询:

--- 单行子查询:只返回一行数据的子查询语句;

①可以放在Select语句的where子句、having语句、from子句中;

②运算符: =、 >、 <、 >=、 <=、 <>

--- 多行子查询:返回多行数据的子查询;

①运算符:IN、NOT IN、EXISTS、NOT EXISTS、ANY和ALL(ANY和ALL必须和比较运算符结合使用)

②in 和 exists区别:in 是把外表和内表作hash join,而exists是对外表作loop,每次loop再对内表进行查询。如果查询的两个表大小相当,那么用in和exists差别不大。 如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in;

③not in 和not exists 的区别:如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;而not extsts 的子查询依然能用到表上的索引。所以无论哪个表大,用not exists都比not in要快。尽量不要使用not in子句。使用minus 子句都比not in 子句快,虽然使用minus子句要进行两次查询:

④在多行子查询中使用all操作符; eg:sal>all(select sal ...)-- sal比右边的所有的都大 用max也可以

⑤在多行子查询中使用any操作符: eg:sal>any(select sal ...)-- sal中存在比右边的大 用min也可以

  • 多列子查询:

---指查询返回多个列数据的子查询语句;可以成对比较,也可以非成对比较。

单行:select * from 表名 where (列1,列2,...)=(select 列1,列2,... from 表名);

多行:用IN

tips:数据库的执行是从右到左

  • 相关子查询:引用了父查询中某些表或列的子查询;
  • 标量子查询:只返回单行单列的数据。
  • 在from子句中使用子查询

逻辑---a.查询出各个部门的平均工资和部门号; 表 a2

b.把上一步的查询结果看做是一张子表(内嵌视图);(select ... from ...) a1 --不能用as

c.用上述两张表a2,a1 进行多表查询

总结:挡在from子句中使用子查询时,该子查询会被作为一个视图来对待,因此也叫内嵌视图,当在from子句中使用子查询时,必修给予子查询指定别名(不能用as指定别名)。

  • 在DDL、DML语句中使用子查询
  • 在DDL语句中使用子查询

在create table语句中使用子查询:

create table 表名(列名1,列名2,...) as select 列名1,列名2,... from 表名 where ...;

create table 表名 as select * from 表名 where ...;

在create view中使用子查询:

create or replace view 视图名 as select ...;

  • 在DML语句中使用子查询

在update中使用子查询:

update 表名 set (...)=(select ... from ...) where 列名=(select ... from ...);

在delete中使用子查询:

delete from 表名 where 列名=/in (select ... from ...where...);

在insert中使用子查询:

插入全部列:insert into 表名 select .....

插入部分列:insert into 表名(部分列) select ...; 部分列中一定要有主键列和NOT NULL列

4.创建新的数据库

  • 法一:使用DataBase Configuration Assistant创建/删除/配置数据库
  • 法二:可以手工步骤直接创建

Oracle的学习二:表管理(数据类型、创建/修改表、添加/修改/删除数据、数据查询)