oracle--基础学习

---- oracle学习

--- 语法：select (子查询) from(子查询) where(子查询)group by(子查询)having(子查询)order by （asc/desc）子

--- 1.PLSQl-DEV：oracle自带XE数据库，XE数据库表里面存在一系列以hr开头的表。
--- 2.oracle 查询语句 （select）
select * from hr.employees e

--- 3.oracle的算术表达式（加减乘除）-- dual临时表
--- 3.1  +（加）   加法
select 5+6 ,5+null from dual
--- 3.2  ||（加） 字符串相加
select 5 || null from dual
--- 3.3  -（减）   减法
select 5-6  from dual
--- 3.4 *（乘）   乘法
select 5*6  from dual
--- 3.5  /（除）   除法
select 5/6  from dual
--- 3.6  mod（模）返回一个除法的整数余数
select mod(13,3)  from dual
--- 3.7 power(2,3):求2的3次方
select power(3,3) from dual
--- 求8开根3后的值
select power(8,1/3) from dual
--- 3.8 round(值,小数位):4舍5入
--小数位为0,表示四舍五入以后，返回整数
select round(0.49)from dual
select round(0.5)from dual
--保留2位小数
select round(0.33333,2)from dual
--- 3.9 Floor:小数部分直接舍弃,整数不变
select floor(10.9)from dual
--- 3.10 CEIL:小数部分直接舍弃，整数+1
select ceil(10.9)from dual
--- 3.11 sign(n)若n=0，则返回0,否则n>0,则返回1，n<0返回-1
select sign(19) from dual;
--- 3.12 sqrt(n)n的平方根
select sqrt(36) from dual;

--- 4. oracle 别名    as 后面的别名使用双引号
select floor(10.9) as "取整（小数部分直接舍弃）" from dual

--- 5.oracle 去重复行 distinct
select distinct 字段1 from 表

--- 6 oracle的条件限定
--- 6.1 比较条件（>,<,= 后面的字符串使用单引号并且区分大小写,!=  <>  not字段= ,>=,<=）
select * from emp WHERE ename = 'SCOTT';
select * from emp where sal <> 3000;   
select * from emp where sal !=3000;    
select * from emp where not sal = 3000;
--- 6.2 between  and 范围查询
select * from emp where sal between 1500 and 3000;
--- 6.3 in,not in,exist ,not exist 在/不在 ..中
select * from emp where ename in ('KING','SCOTT');
select * from emp where  ename not in ('KING','SCOTT');
--- 6.4 is null,is not null ..为空/不为空
select * from emp where not comm is null;
select * from emp where comm is not null;
select * from emp where comm is  null;
--- 6.5 like模糊查询    使用单引号，且%与_
select * from emp where ename like '_L%';
----ESCAPE ‘’ 相当于自己定义一个转义符
--查询员工姓名中带有_的所有的信息
select * from emp where ename like '%a_%' ESCAPE 'a';

--- 7.oracle的逻辑判断 与and  或or  非not 

--- 8.oracle 排序查询  desc 降序（倒排序）  ASC 升序（正排序）

--- 9.oracle 关联查询 
1、内连接（inner join……on/join on）:只连接匹配的行
2、左连接(left join……on)：包含左边表的全部行，不管右边表的是否存在与它们匹配的行
3、右连接(right join……on)：包含右边表的全部行，不管左边表是否存在与它们匹配的行
4、全外连接(full outer join……on或者full join……on)：包含左右两张表的全部行，不管另外一边表中是否存在与它们匹配的行

--- 10 .oracle 统计(聚合)函数
--- （1）avg(x):返回x的平均值
--- （2）sum(x)：返回x的和
--- （3）count(x):统计x个数
--- （4）MAX\MIN(x)：返回x最大值或x最小值
--- （5）median(x):返回x的中间值
--- （6）stddev(x)：返回x的标准差

--- 11.oracle 常用字符函数(括号里面的字符串用单引号括起来)
--- （1）initcap(char)把每个字符串的第一个字符转成大写
select initcap('fengtao') from dual;
--- （2）lower(char)整个字符串小写
select lower('FengTao') from dual;
--- （3）replace(char,str1,str2)字符串中所有str1换成str2
select replace('zx520,520zx','520','我爱你') from dual
--- （4）substr(char,m,n)取出从m字符开始的n个字符串的子串
select substr('好像爱这个世界',3,1) from dual;
--- （5）length(char)求字符串的长度
select length('不想离开，是因为你说你在') from dual

--- 12.日期型函数
--- （1）sysdate返回当前日期和时间
select sysdate from dual
--- （2）last_day(d)日期d当月的最后一天
select last_day('23-AUG-19') from dual
select last_day(sysdate) from dual
--- （3）add_months(d,n)当前日期d后推n个月
select add_months(sysdate,2) from dual
select add_months('23-AUG-19',2) from dual
--- （4）months_between(d,n)日期d和n相差月数
select months_between(sysdate,'23-AUG-19') from dual
select months_between(sysdate,to_date('20191011','YYYYMMDD')) from dual
--- （5）next_day(d,day)：表示d后第一周指定day的日期（day格式有：Monday、tuesday、Wednesday、thursday、friday、saturday、Sunday）
select next_day(sysdate,'Monday') from dual;

--- 13.特殊格式的日期型函数
--- （1）Y或YY或YYY：表示年的最后一位或者两位、三位
select to_char(sysdate,'YYYY') from dual;
--- （2）Q：表示季度，1-3个月为一个季度
select to_char(sysdate,'Q') from dual;
--- （3）MM：表示月份数
select to_char(sysdate,'MM') from dual;
--- （4）RM：表示将月份转换成罗马的日期格式
select to_char(sysdate,'RM') from dual;
--- （5）month：表示用英文字符表示月份（比如august指的是8月）
select to_char(sysdate,'month') from dual;
--- （6）ww:表示当年的第几周
select to_char(sysdate,'ww') from dual;
--- （7）w：表示本月的第几周
select to_char(sysdate,'w') from dual;
--- （8）DDD：表示当年第几天，一月一日是当年的第一天：001，二月一日是当年的第32天：032
select to_char(sysdate,'DDD') from dual;
--- （9）DD：表示当月第几天
select to_char(sysdate,'DD') from dual;
--- （10）D：表示周内第几天
select to_char(sysdate,'D') from dual;
--- （11）DY：表示周内第几天缩写
select to_char(sysdate,'DY') from dual;
--- （12）hh12:表示12小时制小时数
select to_char(sysdate,'hh12') from dual;
--- （13）hh24：表示24小时制小时数
select to_char(sysdate,'hh24') from dual;
--- （14）Mi：表示分钟数
select to_char(sysdate,'Mi') from dual;
--- （15）ss：表示秒数
select to_char(sysdate,'ss') from dual;
--- （16）to_number()：将字符串数字化
select to_number('12345') from dual;
--- （17）to_char():表示将数字转换为字符串
select to_char(20200501) from dual;

--- 14.oracle 分组查询 group by(分组的时候，查询字段，只能是统计函数，或者被分组的字段)
---以部门进行分组，查询部门平均薪资
select avg(salary),e.department_id  from hr.employees e group by e.department_id

--- 15.oracle having 对分组函数进行条件判断
--- 以部门进行分组，找出部门平均薪资高于5000的数据
select avg(salary),e.department_id  from hr.employees e
group by e.department_id 
having avg(salary) >5000

--- 16.oracle 子查询（子查询即可以简单的理解成同时执行多条sql语句 ，将一条sql语句的查询结果作为条件来执行另一条sql语句）
--- 统计月薪高于Bruce 人月薪的人数
select count(*) from hr.employees e where e.salary > (select e.salary from hr.employees e where e.first_name= 'Bruce')

--- 13.oracle 分页查询（使用rownum）
select * from (
       select rownum r,e1.* from (
              select e.* from hr.employees e
       ) e1
) e2
where e2.r >=startIndex and e2.r <= endIndex

--- 14.oracle创建表
creat table 表名(
      字段名 字段类型
)
---表名/列名命名规则
1.必须以字母开头
2.长度一定是1到30个字符
3.只能包含a_z,A_Z,0-9,_,$,#
4.不能和同一用户拥有的其他对象重名
5.不能是oracle服务器的保留字
--- varchar和varchar2的区别
两种都是变长的，对于汉字(unicode)的处理有区别：
varchar如果放的是英文和数字，就用一个字节存放，如果是汉字(unicode)，就用两个字节，varchar2 统一使用两个字节（一般都直接使用varchar2）
--- oracle的数据类型
（1）varchar2(size)：可变长度字符数据
（2）char(size )：固定长度字符数据
（3）number (p,s)：可变长度数字数据
（4）Date：日期和时间值
（5）Long：可变长度字符数据最多为2千兆字节
（6）clob：字符数据，最多为4千兆字节
（7）raw and long raw ：原始(raw)二进制数据
（8）bfile：存储在外部文件中的二进制数据，最多为4千兆字节
（9）rowid：64位基本编号系统，表示行在表中的唯一地址

--- 15.oracle插入数据(commit:提交这一次的插入行为，否则数据库不会生效)
insert into 表名（字段1，字段2，字段。。。） values（值1，值2，值。。）；
commit;

--- 16.oracle 序列
--- 创建sequence(创建了一个名字为hero_seq的序列 ,这个序列从1开始，每次增加1，最大值是9999999)
create sequence hero_seq
increment by 1
start with 1
maxvalue 9999999
--- 使用sequence
--- 获取hero_seq的下一个值
select hero_seq.nextval from dual
--- 获取hero_seq的当前值
select hero_seq.currval from dual 
--- 作为id插入列表中
insert into hero (id,name,hp,mp,damage,armor,speed) values(hero_seq.nextval,'炸弹人',450,200,45,3,300);
 
--- 17.oracle修改数据 update
update 表名 set 字段A = 值 

--- 18.oracle删除数据delete（可回滚恢复数据）
delete from 表名
 
--- 19.oracle舍去表 truncate（truncate 比delete更快，更彻底，并且不能恢复，不能回滚）
truncate from 表名

 --- 20.oracle修改表结构 alter
--- 添加新列
alter table 表名 add(列名 列类型)
--- 修改列
alter table 表名 modify(列名 列类型)
--- 删除列 
alter table  表名 drop column 列名

--- 21.oracle 删除表
drop table 表名

--- 22.oracle 约束
--- 唯一约束unique
--- 非空约束not null
--- 主键约束primary key
--- 外键约束foreign key
--- 创建表的时候设置约束
creat table hero(
      id number,
      name varchar2(20) unique
)
--- 给约束命名
create table hero(
       id number,
       name varchar2(20),
       consistent uk_hero_name unique(name)
)
--- 给已存在的表增加约束
alter table hero add constraint pk_hero_id primary key(id)
--- 删除约束
alter table hero drop constraint uk_hero_name
--- 创建外键约束
create table hero(
       id number,
       name varchar2(20),
       killerid number,
       consistent fk_hero_name foreign key(killerid) reference hero(id)
)

--- 23.oracle视图
--- 创建视图
1权限: 要在当前方案中创建视图, 用户必须具有create view系统权限; 要在其他方案中创建视图, 用户必须具有create any view系统权限. 视图的功能取决于视图拥有者的权限.

2 语法: create [ or replace ] [ force ] view [schema.]view_name
                      [ (column1,column2,...) ]
                      as 
                      select ...
                      [ with check option ]                      [ constraint constraint_name ]
                      [ with read only ];

tips:
 1 or replace: 如果存在同名的视图, 则使用新视图"替代"已有的视图
 2 force: "强制"创建视图,不考虑基表是否存在,也不考虑是否具有使用基表的权限
 3 column1,column2,...：视图的列名, 列名的个数必须与select查询中列的个数相同; 如果select查询包含函数或表达式, 则必须为其定义列名.此时, 既可以用column1, column2指定列名, 也可以在select查询中指定列名.
 4 with check option: 指定对视图执行的dml操作必须满足“视图子查询”的条件即,对通过视图进行的增删改操作进行"检查",要求增删改操作的数据, 必须是select查询所能查询到的数据,否则不允许操作并返回错误提示. 默认情况下, 在增删改之前"并不会检查"这些行是否能被select查询检索到. 
 5 with read only：创建的视图只能用于查询数据, 而不能用于更改数据.
--- 查询视图没有什么限制, 插入/更新/删除视图的操作会受到一定的限制; 所有针对视图的操作都会影响到视图的基表; 
--- 为了防止用户通过视图间接修改基表的数据, 可以将视图创建为只读视图(带上with read only选项)

--- 24.oracle存储过程，包，包体，函数（先暂时不看，项目遇到了再去学习）