oracle基础练习2021_06_01

oracle基础信息

oracle的服务：（需要开启的服务）
    orcaleServiceorcl:orcale启动服务
    OracleOraDb11g_home1TNSListener:oracle监听器服务
    OracleDBConsoleorcl:控制台服务
 oracle账户：
    sys:超级管理员
    system：管理员
    scott：普通用户 默认密码：tiger

oracle修改密码：

sqlplus / as sysdba

alter user 用户名 identified by 新密码；

sqlplus /nolog

conn as sysdba

输入已知用户名和密码

alter user 用户名 identified by 新密码；

使用别名：

select  name as 姓名 ，biz_code as 业务类型 from bank_oper;  使用as关键字

select  name 姓名 ，biz_code  业务类型 from bank_oper;  直接在字段后面添加别名

select  name “姓  名” ，biz_code  业务类型 from bank_oper;  特殊字段需要加双引号

使用链接符：

select name || biz_code from bank_oper;  使用||符号进行字符链接

select name || ‘ 的渠道为’ || biz_code from bank_oper;  使用||符号进行字符链接

去除重复 distinct

select distinct biz_code from bank_oper ;  查询去重的所有业务类型

select count (distinct biz_code) from bank_oper ;  查询去重的所有业务类型的数量

排序

select  * from bank_oper order by oper_time; 根据时间使用order by 进行排序

select  * from bank_oper order by oper_time  desc;   关键字desc根据时间从大到小进行排序

select  * from bank_oper order by oper_time  asc;   关键字asc根据时间从小到大进行排序（默认）

select  * from bank_oper order by oper_time ,id asc;  根据优先按照时间排序，然后根据id排序

select  * from bank_oper where oper_time is not null order by oper_time  desc,id asc; 时间不为null，按照时间倒序，id默认排序

简单的where子句

查询时间在2021年1月1号0点0分1秒之后的所有数据，并根据时间倒序

select * from bank_oper where oper_time > to_date('2021/01/01 00:00:01','YYYY/MM/DD HH24:MI:SS')  order by oper_time desc;

where子句使用关键字

and用于多条件的  与   筛选

select * from bank_oper where 条件 and  条件 and 条件.......

or用于多条件的  或  筛选

select * from bank_oper where 条件  or   条件   or  条件.......

in用于多条件的   或   筛选

select * from bank_oper  where 字段名  in（值，值，值.......）;

like用于模糊查询

select * from bank_oper where 字段名 like ‘%值%’ ；

is null 和is not null用来判断是否为空

select * from bank_oper where 字段名 is null；

函数

1. 使用小括号提升where筛选条件的执行优先级别
2. and的优先级别高于or

select * from bank_oper where biz_channel='A' or  biz_channel='B' and oper_aamount > 2500;

select * from bank_oper where (biz_channel='A' or  biz_channel='B') and oper_aamount > 2500;

使用函数  单行函数 转换函数  其他函数

--单行函数：不改变原始数据，只改变结果

• 字符函数

--initcap 函数将首字母大写

select initcap(cust_name) from bank_oper；

--lower 字母小写

select lower(cust_name) from bank_oper;

--replace 替换

select replace(cust_name,'张','zhang') from bank_oper;

• 数值函数 --math

-----伪表 dual

select * from dual；

select abs(-3) 绝对值，ceil(3.1415926)向上取整, floor(3.1415926)向下取整，power(2,3)幂, round(3,4)四舍五入  from dual；

• 日期函数

--months_between两个日期之间的月份数

select months_between('13-12月-2016','13-10月-2016') from dual;

--多行函数

max：max(oper_amount)  返回此字段的最大值

min：min(oper_amount)  返回此字段的最小值

avg：avg(oper_amount)  返回平均值

sum：sum(oper_amount)   返回字段的和

count(*),用来查询表中有多少条记录

count(oper_amount)  用来查询某个字段有值的个数

count(distinct oper_amount)  先去除重复再计数

注意：多行函数不能和普通字段直接出现在查询语句中，除非group by

注意：多行函数和单行函数不能直接出现在查询语句中，除非group by

转换函数

在转换的时候改变的是数据的类型，数据内容不会改变，可以指定格式。

1. to_number：将数字字符转换为数字类型的数值，to_number(数字字符)
2. to_char：将数字转换为字符类型，将日期转换为字符类型to_char(数字/日期)
3. to_date：将字符类型的日期转换为日期类型，to_date(char)

number-->char  转换的时候使用的是默认格式，

char-->number  to_number(数字字符)，

select to_number('123') from bank_oper；

char-->date  转换的字符必须是日期格式的字符串，默认格式dd-mm-yyyy

select to_char(t.oper_time,'YYYY_MM_DD HH24:MI:SS') from bank_oper t;

date-->char  注意：因为日期本身就是具有一定的格式存在，不是指定格式的情况下会默认使用dd-mm-yyyy格式显示数据，指定的格式会作为日期转换为字符串类型的显示格式存在。

其他函数

1. nvl（）：nvl（字段名，执行）--相当于Java中的if条件判断
2. nvl2（）：nvl2（字段名，值，值）--相当于java中的if（）{}else{}判断
3. decode()：decode（字段名，条件1，执行内容1，条件2，执行内容2，默认执行内容）相当于java中的if（）{}else if（）{}...else{}

显示员工的职称

select ename，job，decode（job,'MANAGER','经理','SALESMAN','销售人员','普通员工'） from emp;

使用group  by分组

在多行函数中不能直接使用普通字段，除非group by

在多行函数中不能直接使用单行函数，除非group by

---1、使用group  by 进行数据分组 select 多行函数，分组字段 from 表名 group by 分组字段

---2、多字段进行分组的时候，按照字段顺序进行分组，第一条件分组完成后，继续使用其他条件依次分组

---3、group by 依然可以和 order by 联合使用

---4、可以和单行函数联合进行分组，注意使用了单行函数那么在查询语句中必须也要使用

使用having进行分组后筛选

---1、使用group by 分组后在进行数据筛选的时候，where 中不能出现多行函数，所以使用新的关键字having进行条件筛选。

---2、where 条件筛选的执行顺序：from--->where--->group by--->select

---3、having条件筛选的执行顺序：from--->group by --->having--->select

---4、where 的执行效率比having要高，能使用where的情况下，尽量不要使用having

插入数据学习及数据的备份

1、插入数据 insert into 表名（字段1，字段2，字段3，......）values（'值1','值2','值3'.....）

2、主键：用来唯一标识一条数据的字段通常设置主键，主键是唯一不可以重复的

3、如果插入的数据是全字段数据，字段可以省略不写。部分字段，必须加上字段说明和字段值，但是主键不能为空

4、事务的提交；如果一个事件是由多个动作组成，只要有一个动作没有执行成功，则自动将数据回滚到原始状态，此技术称之为事务保证数据的安全和完整。

事务的提交：

使用第三方插件的提交按钮；

使用commit语句。

创建数据的备份

1. create table 表名 as 查询语句；创建的是和查询结果一样的表，查询结果是什么就会备份一个相同的表。
2. insert into 表名 查询语句；注意：查询出来的结果在结构上必须和插入数据的表相同，字段个数必须相同。
3. 注意：备份表只有字段和数据相同，并不会备份约束。

1、备份完整的数据和表

create table bank_oper_0604 as select * from bank_oper;--备份bank_oper表和数据，只能备份数据和字段。

2、备份完整表

create table bank_oper_0604 as select * from bank_oper where 1 > 2 ;--备份表，不备份数据。

3、备份部分数据和表

create table bank_oper_0604 as select  t.oper_time,t.oper_amount from bank_oper t;---查询出来什么，就备份什么表结构和数据

4、给备份表添加数据 insert into 表名 查询语句；

数据的更新和删除 

更新数据：update  表名 set  字段1='值1',字段2='值2',.....where 条件；

update bank_oper set biz_code = 'C03' where id = '124354';

删除数据：delect  表名  where  条件；

delect bank_oper  where id = '124354';删除指定数据

delect bank_oper ;删除表

truncate table bank_oper;清空表数据，建议

sql的联合查询（多表查询）

---1、笛卡尔积：一件事情的完成需要很多步骤，而不同的步骤有很多种方式，完成这件事的所有方式称为笛卡尔积。

select * from bank_oper,bank_user order by cust_id;

等值链接，链接条件。等值链接的时候字段的名字可以不相同，但是字段的值要相同。

--查询员工姓名，工作，薪资，部门

select * from emp,dept where emp.deptno=dept.deptno;--使用等值链接进行结果筛选

select ename,job,sal,dname from emp,dept where emp.deptno=dept.deptno;--使用等值链接查询指定数据

select ename,job,sal,emp.deptno,dname from emp,dept where emp.deptno=dept.deptno;多表查询的时候，查看相同字段的值，必须声明所在。

select e.ename,e.job,e.sal,e.deptno,d.dname from emp e,dept d where e.deptno=d.deptno order by d.deptno;在查询指定字段值的时候，加上表名提高查询效率，还可以使用别名，且使用order by 排序

非等值链接

select * from emp salgrade where sal > losal and sal < hisal;

自链接：使用频率不是很高，因为自链接的条件要求不同信息共存在一张里，其实就是两张相同的表的等值链接。

create table bank_opers as select * from bank_oper;--新建一张相同表

select * from bank_oper t1,bank_opers t2 where t1.oper_time = t2.update_time;

交叉链接：

select * from emp cross join dept (i笛卡尔积)

自然链接：natural join

--1、自然链接会自动使用多表中所有相同字段（不但值相同，名字也要相同）进行筛选

前提：多表一定要有同名同值的字段。

注意：自然连接会自动使用所有的相同字段进行结果筛选。

--2、使用using关键字可以指定字段进行链接查询，但是必须式同名字段   inner  join

--3、使用on关键字可以直接在其后书写链接条件，没有限制   inner  join

查询员工及员工所在的部门信息

select * from dept natural join emp;

select * from dept d,emp e where d.deptno = e.deptno;

问题1：假如在链接查询的时候不想使用所有字段进行筛选怎么办？----使用using关键字

select * from dept join emp using(deptno);

问题2：假如在链接查询中没有同名字段，但是有同值字段怎么筛选？---使用on关键字

select * from dept d inner join emp e on d.deptno=e.deptno;

外链接

--1、左外链接 left outer join

--2、右外链接  right outer join

--3、全外链接  full outer join

--左外链接

select * from emp e ,dept d where d.deptno(+) = e.deptno

select * from emp e left join dept using(deptno);

--右外链接

select * from emp e ,dept d where d.deptno = e.deptno(+)

select * from emp e right outer join dept d on e.deptno = d.deptno;

--全链接

select * from emp e full outer join dept d on e.deptno=d.deptno;

自链接及三表联合查询

查询员工姓名、工作、薪资、部门名称，城市名称

实现方式一：select e.name,e.job,e.sal,d.dname,c.cname from emp e,dept d,city c where e.deptno=d.deptno and d.loc=c.cid;

实现方式 二：select e.name,e.job,e.sal,d.dname,c.cname from emp e inner join dept d on e.deptno=d.deptno inner join city c on d.loc=c.cid;

查询工资高于2000的员工姓名，工作，薪资，部门名称，城市名称

实现方式一：select e.name,e.job,e.sal,d.dname,c.cname from emp e,dept d,city c where e.deptno=d.deptno and d.loc=c.cid and e.sal > 2000;

实现方式二：select e.name,e.job,e.sal,d.dname,c.cname from emp e inner join dept d on e.deptno=d.deptno inner join city c on d.loc=c.cid where e.sal > 2000;

单行子查询

select 查询内容 from 表名 where 子查询语句；

1、什么时候使用子查询？--在不能直接获取有效信息的时候，考虑使用子查询。

2、单行子查询需要注意那些事项？

• 子查询结果必须只有一个值
• 可以直接使用算术连接符
• 子查询出现在where中，一般出现在条件语句的右边

示例：查询所有比雇员CLARk工资高的员工信息

1、需要知道员工CLARK的工资

--select sal from emp where ename='CLARK';

2、比这个值高的员工信息

--select * from emp where sal>(select sal from emp where ename='CLARK');

示例二：查询工资高于平均工资的员工姓名和工资

select ename,sal from emp where sal>(select avg(sal) from emp) order by sal; 

示例三：查询和soctt属于同一部门且工资比他低的员工资料

select * from emp where deptno=(select deptno from emp where ename='soctt') and sal < (select sal from emp where ename='soctt');

查询工资最高的员工资料

select * from emp where sal = (select max(sal) from emp ); 

多行子查询

多行子查询学习其实就是使用关键字：any all in

注意：子查询返回多个值建议使用多行子查询，返回单个值使用单行子查询

查询工资高于所有salesman的员工信息

select * from emp where sal > (select max(sal) from emp where job='salesman');--单行子查询

select * from emp where sal > all(select sal from emp  where job='salesman');--多行子查询

查询部门20中同部门10的雇员工作一样的雇员信息

select * from emp where deptno='20' and job in (select job from emp where deptno='10');

select * from emp where deptno='20' and job = any(select job from emp where deptno='10');

关于用户相关

用户权限分为：system（系统账户），sys（超级管理员），scott（普通用户）

创建自定义用户：create user 用户名 identified by 密码；

--create user tang identified by 123456;

*注意：普通用户不具备具有创建用户的权限，需要有system账户进行创建。

直接创建好的用户不能登录，需要system进行权限分配（角色）

赋予角色：grant 角色名，角色名....to 用户名;

--grant connect to tang;--赋予链接库权限

--grant resource to tang;--赋予操作资源权限

--grant dba to tang;--赋予DBA角色

删除用户权限：

revoke dba from tang；

revoke connect from tang;

删除用户：

drop user tang;

关于创建表和字段：

创建表的标准语句：create table 表名（字段名 类型，字段名 类型，.....）;

创建学生表：学号、姓名、年龄、性别、邮箱、奖学金。

create table student （

snum number(10),--制定学号数字长度为10

sname varchar2(100),--存储上限为100

age number,

sex char(2),

mail varchar（50）,

sal number(6,2)--指定金额的整数位为6位，小数位为2位

）

字段类型：

varchar：字符类型，用来声明存储字符的字段，会根据存储的数据自动调整大小，长度上限为2000.

varchar2：与varchar无区别，最大存储长度上限为4000.

number：数字类型，用来声明存储数字的字段，number（指定数字的整数长度，指定数字的小数位长度）

char：字符类型，用来声明存储字符的字符，会开辟指定大小的内存来存储数据

date：存储日期类型

*注意char和varchar区别：

• char存储效率高于varchar2
• char是开辟指定大小的内存空间，varchar2是根据数据大小来开辟空间的大小

关于修改表

查看表结构：--desc 表名

添加新字段：--alter table 表名 add 字段名 类型；

alter table bank_oper add oper_time date;

修改字段类型：--alter table 表名 modify 字段名 类型；

alter table bank_oper modify oper_time varchar2(60);

删除字段： alter table 表名 drop column 字段名；

alter table bank_oper drop column oper_time;

修改表名：rename 表名 to 新表名；

rename bank_oper to bnak_oper_2021;

删除表：drop table 表名；

drop table bnak_oper_2021;

关于约束

create table student(

        snum number(10),--primary key,--使用主键约束   系统自动分配主键名
        sname varchar2(100), --not null,
        sex char(4), --default '男' check(sex='男' or sex='女') not null,
        age number check(age>0 and age<120),
        qq number, --unique,
        sal number(6,2),
        mail varchar2(50)

        --constraints pk_student_snum primary key(snum);

        --constraints ck_student_aname check (sname is not null);

        --constraints ck_student_aname check(sex='男' or sex='女') ;

        --constraints uk_student_qq unique(qq);

)

alter table student add constraints pk_student_snum primary key(snum)--在创建表后添加主键

alter table student drop constraints pk_student_snum;--删除主键

alter table student modify sname varchar(100) not null;--添加非空约束

alter table student modify sname varchar(100)  null;--修改字段为null

alter table student add constraints ck_student_sex check (sex='男' or sex='女');--添加检查约束

alter table student drop constraints ck_student_sex;--删除检查约束

alter table student add constraints uk_student_qq unique(qq); --添加唯一约束

alter table student drop constraints uk_student_qq;删除唯一约束

外键约束：

问题：插入学生信息的时候，出现该学生没有班级信息？

使用外键约束：

• 在字段后使用references 参照表表名（参照字段）
• 在所有字段后使用constraints fk_表名_字段名 foreign key （字段名）references 参照表名（参照字段名）
• 在创建表后使用alter table 表名 add constraints fk_表名_字段名 foreign key (字段名) references 参照表名（参照字段名）

创建学生表：      

create table stu(
           snum number(10) primary key,
           sname varchar2(100) not null,
           sex  char(4) check(sex='男' or sex='女'),
           cinfo number(10)-- references clazz(cid)--外键 
        --constraints fk_stu_cinfo foreign key (cinfo) references class(cid);
       )

创建班级表：

create table clazz(
           cid number(10) primary key,
           cname varchar2(100) not null,
           cdesc varchar2(500)

       )

alter table stu add constraints fk_stu_cinfo foreign key (cinfo) refernces class(cid) on delete cascade；--添加外键

alter table stu add constraints fk_stu_cinfo foreign key (cinfo) refernces class(cid) on delete set null；--添加外键

alter table stu drop constraints fk_stu_cinfo;--删除外键

问题：在删除父表数据的时候需要先删除子表数据？

解决方式1：先解除主外键关联，然后删除数据，再然后添加主外键关联

解决方式2：在创建外键的时候使用级联操作

--在创建外键时 on delete cascade

--在创建外键时 on delete set null

问题：怎么选取外键？：一般将主表的主键作为子表的外键

问题：外键的值能为not null？：不建议在外键后使用非空约束

关于序列

问题：如果数据量比较大，插入新的数据的时候id怎么选取才能避免重复？

使用序列：

创建序列 create sequence 序列名；

特点：默认没有初始值的，nextval的默认值从1开始，默认每次自增+1

特点：可以使用 序列名.nextval作为主键使用

create sequence cnum;

select cnum.currval from dual;--获取当前序列值

select cnum.nextval from dual;--序列自增后返回当前值

truncate table class；--清空表数据

insert into class values(cnum.nextval,'班级','王老师');

问题：如果当前表中已有很多数据，但是接下来需要使用序列进行主键自增；

创建序列时指定序列初始值及步长：

create sequence cc;

start with 90;--设定序列初始值

increment by 5;--设定步长

insert into class values(cc.nextval,'班级',‘李老师’);

完整的序列格式-----------------------------------
create sequence aa--序列名
start with 20  --设置序列初始值
increment by 10 --设置步长
maxvalue 10000---设置最大值
cache  10  -----设置缓存

关于索引

问题：当表中数据量比较大的时候，使用条件查询就会出现效率问题

使用索引可以解决这个问题。

1、原理：类似于java中数组的折半查找，在数据库中数据使用B树形结构进行数据的存储，
          --这样可以对外提供快速的数据查找方式。
2、创建索引 create index 表名_index_字段名 on 表名(字段名);

特点：显示的创建，隐式的执行，在数据中会给主键默认创建索引。

create index emp_index_sal on emp (sal);--创建查询索引

create index emp_index_sal_desc on emp(sal desc);--创建单排序索引

create index emp_index_sal_desc2 on emp(sal desc,job asc);--创建多条件排序索引

3、删除索引drop index 索引名

drop index emp_index_sal_desc;