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