oracle日常训练

打印表种所有数据

select * from T_GIRL;

创建一个表 

create table 表名
(
  字段名1 数据类型 null,
  字段名2 数据类型 not null,
  ......,
  字段名n 数据类型 null
);

create table T_GIRL
(
  id        char(4)         not null,   -- 编号
  name      varchar2(30)    not null,   -- 姓名
  yz        varchar2(20)        null,   -- 颜值
  sc        varchar2(20)        null,   -- 身材
  weight    number(4,1)     not null,   -- 体重，单位：kg
  height    number(3)       not null,   -- 身高，单位：cm
  birthday  date            not null,   -- 出生时间，可精确到秒。
  memo      varchar2(1000)      null    -- 备注
);

插入数据

insert into wjx (ID,name) values ('1111','qqqq');
insert into wjx (ID,name) values ('2222','wwww');
insert into wjx (ID,name) values ('3333','eeee');
insert into wjx (ID,name) values ('4444','rrrr');

 查询数据

where是查找表中的某个字段，from是查看某个表

select ID,name from wjx where ID=1111 and name='qqqq';

 修改数据

update wjx set ID='7531',name='快快' where name='qqqq'

 删除数据

delete from wjx where name='快快'

删除表

 

drop table wjx

rollback

返回上一次commit的地方，但是如果是对表进行删除，则不可恢复

设置主键

方法一：

alter table wjx add constraint PK primary key(ID)

 方法二：

在定义表的时候就直接指定主键

create table wjx
(
 ID char(4) null ,--身份证
 name varchar(30) null,--姓名
 primary key(id)    -- 指定id为表的主键
)

Oracle事务

假设有A，B两个用户，每个用户账户上有1万元，现在A给B转了1千元，那么此时A就只有9000元，B就有11000元。

执行逻辑如下：

create table affairs
(    ID char(1) null, --身份
    Money number(10,1) null --账户
)
alter table affairs add constraint my_id primary key(ID)
insert into affairs (ID,money)values('B',10000);
insert into affairs (ID,money)values('A',10000);

先创建事务表 affairs，然后添加数据，设置主键

打印内容

select *from affairs

模拟A转给B

update affairs set Money=Money-1000 where ID='A'

这个时候A就只有9千了，

但是再开一个窗口，从另一个进程看，值还是没有变化

因为前一个进程没有提交修改，若要提交则输入commit

提交后，则在第二个进程里面可以看到此时的数据已经更新。

使用rollback返回上一步操作

但是如果使用commit提交后就没用了。

此时是9千

减去1000

使用rollback返回上一步

关于Oracle的事务只学习这些

虚表

虚表目前只需要执行两个功能：只会用到两个代码

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')from dual      //获取数据库的日期时间

select user from dual                                    //查看当前登录用户。

序列

序列生成器简称序列

创建一个简单的序列：

create sequence 序列名
       [minvalue n]
       [maxvalue n]
       [increment by n]
       [start with n]
       [cache n|nocache]
       [order|noorder]
       [cycle|nocycle];

create sequence SEQ_GIRL; 
//创建一个简单的序列

使用序列的方法有两种:

第一：使用虚表显示

select SEQ_GIRL.nextval from dual;     -- 获取序列SEQ_GIRL的下一个值。

select SEQ_GIRL.currval from dual;     -- 获取序列SEQ_GIRL的当前值。

在新的会话中，必须先使用nextval来产生一个值后才可以使用currval进行查看。产生的值默认为1

第二：使用SQL语句实现

先定义一个表

//定义一个表
create table T_GIRL
(
  name    varchar2(10),     -- 姓名
  keyid   number(10)        -- 记录编号
);


//插入数据
insert into T_GIRL2(name,keyid) values('西施'  ,SEQ_GIRL.nextval);
insert into T_GIRL2(name,keyid) values('妲已'  ,SEQ_GIRL.nextval);
insert into T_GIRL2(name,keyid) values('杨玉环',SEQ_GIRL.nextval);
insert into T_GIRL2(name,keyid) values('李师师',SEQ_GIRL.nextval);

//显示数据
select* from t_girl2

可以发现使用SEQ_GIRL.nextval虚表打印后，每次的序号都不需要手动制定了，自动按照1，2，3，4……排序，但是序号是不可逆的，就算此时把表删掉，这些已经累计的值还是会保留，回滚也无效

序列裂缝：就是序列不连续

伪列

rowid：表的每一行数据都有一个地址，在每一行数据的头部，Oracle就是通过rowid来实现访问数据的。一句话：rowid存放的是行码的物理地址

索引

索引相当于就是表的目录，通过索引可以快速查找目标内容，就不需要再轮询查询了，查找步骤是：先通过索引，找到目标的一些rowid伪列号，然后通过伪列号找到具体一行数据

创建索引：

create index 索引名 on 表名(字段名1,字段名2,......,字段名n);

create index IDX_GIRL_1 on T_GIRL(name)

唯一索引法可以使用折半查询法，而且主键也是唯一索引

删除索引

当把表删除后，索引也会自动删除，也可以手动删除索引

drop index 索引名字

索引的使用

使用字段时，如果表达式左边的字段是表达式，那么就不会使用索引，如果只有字段，才会使用索引。

where\order by和distinct

where子句

oracle里面没有=和==的区分，而且只有=，=表示相等

这句是对的，返回的是单行子集

select empno,ename,job,sal from EMP
 where deptno=(select deptno from DEPT where dname='ACCOUNTING');

这句是错的，返回的是多行子集

select empno,ename,job,sal from EMP
 where deptno=(select deptno from DEPT); 

但是这行是对的，虽然返回多行子集，但是使用了关键字in

select empno,ename,job,sal from EMP
 where deptno in (select deptno from DEPT);

 order by子句

就是将数据按照指定规则排列

asc是从上到下逐渐变大增序

desc是从上到下逐渐变小降序

可以有多种排序方式，类似于excel里面的排序，以下就是先按照体重升序排列，然后再按照升高降序排列（相同情况触发）

select *from t_girl order by weight asc ,height desc

distinct

就是筛选出重复项

原本有很多重复的

但是使用distinct后就没有了

表达式运算

在SQL语句中，只要有字段出现的地方都可以用表达式，比如在语句里面使用加减法，或者是添加字符串

比如要在名字前面加一个姓名：，那就使用单引号包括住姓名，然后用||表示拼接

或者直接运算

拼接单引号

同时运行两行数据要加分号

Oracle函数

时间函数

就是to_char和to_date

nvl函数

nvl（x,value）如果x为空，就返回value，否则返回x

select nvl(name,'绝世美女'),nvl(age,22) from tt;

表示如果轮循到name是空的，则就添上绝世美女，如果age是空的，则显示22，

在插入值时，可以用null表示空

decode函数

就是一个if-else

decode(字段，1，返回值1，2，返回值2，3，返回值3，4，返回值4，缺省值)

相当于是if(字段=1)return  返回值1 else

if(字段=2)return  返回值2 else

if(字段=3)return  返回值3 else
……
retutrn 缺省值

create table tt(name varchar2(20),
                yz number(1),
                sc number(1));
insert into tt values('西施',1,1);
insert into tt values('东施',2,2);
insert into tt values('北施',3,3);
insert into tt values('南施',4,4);
insert into tt values('中施',5,5);
select * from tt;
select name,decode(yz,1,'绝世',2,'盛世',3,'花容',4,'香花','赞'),decode(sc,1,'绝',2,'妙',3,'巧',4,'好','赞') from tt;

userenv

select user,sysdate,userenv('isdba'),userenv('language'), userenv('lang'), userenv('sid') from dual;

用来实现获取当前指定信息，比如此时的用户，此时的时间，此时的语言

聚合函数

就是很多小函数的集合

min(字段)	最小值
max(字段)	最大值
avg(字段)	平均值
sum(字段)	求和
count(*)	就记录多少数目

select min(yz),max(yz),avg(yz),count(*),sum(yz) from tt 

除了count（*）会考虑null的数据外，其余函数都不会考虑null的数据

分组函数

就是excel里面的多级筛选，指定哪些要显示，哪些不显示，指定要显示的范围

select deptno,job,min(sal),max(sal),avg(sal),sum(sal),count(*) from EMP 
where deptno in(10,30) 
group by deptno,job 
order by deptno,job;

select *from EMP

而且，只要在函数SQL里面使用了上面的聚合函数，要么就只使用这些函数不显示其他字段，不然系统就会默认这个函数是分组函数，那么就必须在后面加上group by+前面指定显示的字段

having子句

和where子句很像，where是设置从表中获取数据，having是对统计的结果进程过滤

where子句不能使用分组函数，如以下就会报错，是用来>

where deptno in(10,20) and avg(deptno)>10

而having 子句可以使用分组函数，就是><=，只有>=10的数才能通过筛选，还有，having子句要放在分组函数的后面，排序函数的前面

select job,deptno,max(deptno),min(deptno),avg(deptno) from EMP 
where deptno in(10,20) 
group by job,deptno 
having avg(deptno)>=10
order by deptno asc

外键和主键

主键和外键其实没有多大关系，主键是信息的行信息的唯一标识，外键是一种限制。

外键只有和主表、从表一起说才有关系。从表受限制于主表，从表里面的某一个字段取值受限制于主表里面的某一个字段。

构建主从表关系如下：

完成后，从表字段只能取主表字段

删除外键

alter table 从表 drop constraint 外键名;

类似于excel里面的数据验证

别名

相当于在显示的时候换一个列名字

未使用别名

select id ,name ,yz ,sc ,weight ,height ,birthday  from t_girl;

使用别名

select id 编号,name 姓名,yz 颜值,sc 身材,weight 体重,height 身高,birthday 出生日期 from t_girl

如果别名中有特殊字符，则用双冒号引用

别名可以用在order by语句中，但是不能用在where中

子查询

就是在select语句中再次调用select语句，实现多重查询。

单行查询

一般要使用到表名限定，就是在字段前面加一个表明.字段名

where使用子查询

就是之前的in （）

使用子查询整表赋值

在insert里面使用select，然后赋值整个表

create table TT(id char(4),name varchar2(20));
insert into tt select id,name from t_girl
select *from tt

在创建表的时候直接赋值

但这只有基本的结构，没有主键，没有索引，没有表约束，没有列约束

子查询很强大，就是excel里面的index和match

表空间

第一：SYSTEM表空间

用于存放数据库的内部字典，在创建数据库时最先被创建，相当于电脑的C盘，对于数据库生死攸关。

第二：SYSAUX是辅助表空间，用于存储数据除了字典以外的其他数据对象，一定程度上减少了表空间负荷

第三：回滚表空间，

TEMP表空间

存放临时数据

USERS表空间

非DBA用户的默认空间

同义词

查看所有表

select*from tab

create sysnonym tab for tt

将表bb取个别名叫tt，可以直接用tt调用tab表

删除同义词

自定义函数

没什么用，但是如果被问到细节就按如下回答

注意

C++不能区分0和空，但是Oracle能区分0和空

如果出现资源正在忙，那就运行commit，提交任务，然后删除表即可。