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创建/删除/配置数据库
- 法二：可以手工步骤直接创建