Oracle数据库管理-CSDN博客

本文链接：https://blog.csdn.net/qq_62898618/article/details/123015656

回顾：
1.创建表空间
2.创建表
3.创建用户
4.授权与取消授权

==========================================
注意：不要物理删除硬盘上的dbf文件，oracle启动时到时找不到文件，oracle也启动不了，需要重装oracle。

--删除一个数据库，是先删表空间，还是先删用户？
1.先删用户
2.再删表空间

--删用户：
drop user 用户名;
--删表空间：
drop tablespace 表空间;

--删除空的表空间，但是不包含物理文件
drop tablespace tablespace_name;
--删除非空表空间，但是不包含物理文件
drop tablespace tablespace_name including contents;
--删除空表空间，包含物理文件
drop tablespace tablespace_name including datafiles;
--删除非空表空间，包含物理文件
drop tablespace tablespace_name including contents and datafiles;
--如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS
drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;

视图也可以删，但别去用操作，要用就用代码。

==========================================

今日所学知识点：oracle sql基础
1.数据类型
2.操纵语言
3.SQL操作符

4.select语句（有点差别）
5.子句
6.增删改查
7.oracle特色点：伪表

=======================
1.oracle不区分大小写，注意加分号“;”
2.最重要的还是Tables中的表
3.对对象进行操作的语句create,alter,drop，对数据进行操作的语句insert,delete,update，对事物进行操作commit的语句，数据控制语言grant,revoke
4.在事物操作语句中的rollback，sql server中是返回到事物的开始点，但oracle比较强大些，可以设置返回点，返回到指定的点处。

======================

一、数据类型
1.字符：char，varchar2，long
char：固变 1~2000字节
varchar2：可变长度 1~4000字节
long：可变长度 2GB长度（一般不用）

2.数值
number：即是整数又是小数，默认整型
   number[(p[,s])] ==p精度，s小数位
   number(5,2) == 共5位，其中小数位2位
   --只要整型：number(5)

3.日期时间====>要转类型，像sql中用''是不行了。
Date：存储日期、时间类型。
日期转换函数：to_date('2016-02-02','yyyy-mm-dd')

   例：insert into tb_name(mydate)
   values(to_date('2016-02-02','yyyy-mm-dd hh:mi:ss'));

4.储存二进制数据,比如图片、视频、声音文件等
4.1 Raw或Raw Long
4.2 LOB (常用，就是所谓的大数据，最大4GB)
   Clob：
   Blob：
   Bfile：
记住，一般我们不把图片、视频等存在数据库中，只是把它们存放在硬盘地址保存到硬盘。
存在数据库里，要转换，资源消耗多。

===============================================================
不会写sql语句？把Tables中的表往代码中插，选择插入或查询，会自动产生代码给我们填写。
===============================================================

二、数据定义语言DDL
create table -- 创建表
alter table -- 修改表
truncate table -- 删除表（删除数据，保留结构）
drop table -- 删除表（删除结构和数据）

create表：-----------------------------------------------------
--创建表：
   create table tb_name
   (
   sid number,
   sname varchar2(20),
   sex varchar2(4)
   )

--复制表：
   sqlserver: select into 新表名 from 旧表 where 1=2
   oracle
   create table tb_name
   as select * from emp;

--只复制表结构，不要数据
   create table tb_name
   as select * from emp where 1=2;

--去除重复的数据
   select distinct 列名 from emp;

===============================================================

三、数据操纵语言DML
insert -- 与sql server一致
delete -- 与sql server一致
update -- 与sql server一致
select -- 与sql有些不一样

insert增：-----------------------------------------------------

--插入其它表中的记录（注意要先创建新表，然后再复制数据）
   insert into newtable
   select * from emp;

--将部门编号为20的员工插入到新表中
   insert into newtable
   select * from emp where deptno=20;

--使用union插入多条数据，要借用伪表
   insert into newtable
   select 1,'张三','男' from dual union
   select 2,'李四','女' from dual union
   select 3,'王五','男' from dual union
   select 4,'赵六','女' from dual;

delete删：------------------------------------------------------
--删除性别为男的

update改：------------------------------------------------------

--给所有的人提薪10%
   update emp set sal=sal+sal*10%;
   update emp set sal=sal*1.1;

select查：------------------------------------------------------

===============================================================

四、事务控制语言TCL
commit -- 提交
rollback -- 回滚
savepoint -- 回滚的点

===============================================================

五、数据控制语言DCL
grant -- 授权
revoke -- 撤销授权

===============================================================

六、伪表dual与伪列rowid,rownum

1. 伪表：
   select 'a' from dual;

2. 伪列：
   select * from tb_name
   --如果结果集中出现三列，其实它有五列，还有两个列：rowid、rownum
   --rowid：唯一标识符，这是系统给的，不是我们自己定义的
   --rownum：有点类似sql中的row_number()方法

   怎么查看？
   select t.*,rowid from tb_name t;
   select t.*.rownum from tb_name t;

   注意：在Oracle项目中，一般都使用rownum做分页。

   这两个可以同时用吗？可以
   select t.*,rowid,rownum from tb_name t

   --求前两条记录（Oracle不支持top）
   select a.*,rownum from emp a
   where rownum<3

   --求第3、4条记录（注意：rownum只能作用于小于号）
   --解决办法：rownum是a表的伪列，把这伪列变成实列
   select * from
   (
       select a.*,rownum rid from emp a
   ) t where rid>2 and rid<5

   S2阶段的重点是分页，这段代码要记住，以后经常用。

案例1：求薪水前5的人
--思路：薪水排序，再用上面的rownum?

select * from emp where rownum<6 order by sal desc; (错)
先排序再取，上面代码是先取再排序
select * from emp order by sal desc where rownum<6; (错)

正确的：
select t.*,rownum from
(
   select * from emp order by sal desc;
) t where rownum<6

案例2：求每个人的收入（薪水+奖金）
   select ename,sal,comm,sal+comm from emp; (错)
   结果是sal列中和comm列中有值的都能算出收入，如果有个列是null值，则算不出收入。

   怎么办？
   用nvl()函数，作用与isnull(comm,0)一样
   select ename,sal,comm,sal+nvl(comm,0) from emp;

   注意：select t.*,rownum from emp t order by sal+nvl(comm,0) 的话是先对数据进行分号，再排，所以rownum会显示乱掉。

   nvl(exp1,exp2,exp3) --当exp1为null，返回exp2，否则返回exp3
   select ename,sal,comm,sal+nvl(comm,0,comm+100) from emp;

案例3：查询没有奖金的人
   select * from emp where comm=null;
   (错，null不能用=号，就好比猪和人比，能比吗？种类都不一样)

   正确：select * from emp where comm is null;

=========================================

四、SQL操作符

1.算术运算符 + - * /
---------------------------------------------------------------------
2.比较运算符 = != < > <= >= between..and in like is null
---------------------------------------------------------------------
3.逻辑运算符 not and or
---------------------------------------------------------------------
4.集合运算符 union行合并，union all
---------------------------------------------------------------------
5.连接操作符 ||用来连接字符串

   SQL server语句：
   select sid,sname,sex+'生' from tb_name
   --看这案例，在SQL Server中这句话没问题

   在oracle中会报错：ORA-10722:无效数字
   在Sql Server中“+”既有算术作用，也有字符串拼接作用；
   但在Oracle中，“+”就只有算术作用。

   Oracle语句：
   select sid,sname,sex||'生' from tb_name

SQL操作符的优先级：算术 > 连接 > 比较 > not > and > or

--1.数据管理
#1.sql语言：  结构化查询语言
#（1）数定义语言create【创建】 alter【修改】 drop【删除】
#（2）数据控制语言grant【授权】 revoke【撤销权限】
#（3）数据操纵语言insert delete update select
#（4）事务控制语言commit【事务提交】 rollback【事务回滚(撤销)】  savepoint【还原点】


#2.通过create创建数据表
#2.1 数据类型
# number  数值型   number   number(10) number(10,2)
# char varchar2   char(4)-男   varchar2(20)-湖南
# date 日期

#2.2 oracle 6大约束
# (1)主键约束  primary key
# (2)非空约束   not null
# (3)检查约束   check
# (4)默认约束 default
# (5)唯一约束  unique
# (6)外键约束 references

# 2.3 表（table）
/*
create table tb_表名称
(
       字段1 数据类型(长度) 约束,
       字段1 数据类型(长度) 约束,
       字段1 数据类型(长度) 约束
)
*/
create table tb_stu_0219
(
       sid number primary key,
			 sname varchar2(10) not null,
			 ssex char(2) check( ssex in ('男','女')),
			 sage number,
			 saddress varchar2(100)
)
--insert  新增
#语法： insert into tb_表(字段1,...) values(值1,...);

insert into tb_stu_0219 values(1,'斗霸','男',19,'浪琴湾小区');
--多行插入
--注意事项：在使用select关键词时，后面必须有from关键词
--除了from关键词  必须还需要接表

--补充知识点1：  伪表 dual
insert into tb_stu_0219
select 2,'小三','女',16,'长沙' from dual
union all
select 3,'小三三','女',16,'长沙'from dual
commit;
--删除（delete|truncate【截断】）
delete from tb_stu_0219 where sname = '小三'
rollback;
delete tb_stu_0219;
truncate table tb_stu_0219;--注意：使用truncate截断所有数据后不能事务回滚【rollback】

--修改update
--将表中所有人的年龄-2岁
update tb_stu_0219 set sage = sage - 2

update tb_stu_0219 set sname = '小五' where sname = '小三';

update tb_stu_0219 set sname = '张三',ssex = '男',sage = 99 where sname = '小五';

--查询select
select * from tb_stu_0219;

select sname,sage from tb_stu_0219;

--起别名
select sname as 姓名 from tb_stu_0219;

select sname "姓名" from tb_stu_0219;

--补充知识点2  
--拼接||
#查询张三和李四两个姓名一起输出。
select '张三'||'李四' from dual;

--排序 order by    asc     desc
select * from emp order by sal;
select * from emp order by sal desc;

--between....and
select * from emp where sal between 1000 and 2000;

--复制表
select * from emp;
/*
复制表的语法

create table 新表名称
as
select * from 旧表

*/
--将emp表中的员工编号，姓名，薪水字段复制到新表  tb_sb2
select * from tb_sb3;

create table tb_sb3
as
select empno,ename,sal from emp where 1=2;
select * from tb_stu_0219;

---------------------------------------------------------------------
#2.伪列
#oracle中提供了2个伪列：rowid  rownum
#rowid和rownum是不需要手动创建，不管是系统表还是自定义表，在创建的时候，rowid和rownum自动生成，默认是不可见，需要手动显示出来。

select * from emp;
select * from tb_stu_0219;

#rowid:唯一标识列，类似身份证号码，无规律，都是通过字母随机组合的。
select rowid,emp.* from emp;

#rownum:唯一标识，生成一连串有顺序的标号，从1开始。其本质就是行号。
#取前几条数据。Oracle没有top关键词

select rownum,a.* from emp a;
--求最高工资的那个人的信息
--select top 1 * from emp oreder by sal desc;--sqlserver
--将一个select查询的结果集当作一张虚拟表（新表）继续条件查询，前提起别名。
select rownum,sb.* from
(
       select * from emp order by sal desc
)sb where rownum = 1;
--子查询的原理
select * from emp where sal = 
(
       select max(sal) from emp
)

-----------------------------------------------------------
rownum的核心实现分页。
--需求：不考虑排序，直接显示emp表中的第一条----》第五条的数据
--注意事项：如果select中标明了rownum伪列字段，查询条件中的大于符号无效
select a.*,rownum from emp a where rownum >=6 and rownum <= 10
--解决方案：将位列rownum  变成  明列即可。  就是起别名，让rownum这个位列字段成为表的字段。
select b.* from(

select rownum as rid,a.* from emp a 
) b where b.rid between 11 and 15

int pageIndex = 1;//页码   等于1  第一页   等于2  第二页
int pageSize = 5;//每一页显示的数据的条数

start 开始区间
end   结束区间

pageIndex = 1第一页  start = 1   end = 5
pageIndex = 2第二页   start = 6  end = 10
pageIndex = 3第三页   start = 11  end = 15

不管pageIndex = ??
start = end - pageSize +1
start = (pageIndex - 1)* pageSize +1
end = pageIndex * pageSize