Oracle学习 oracle11g

SQL语言

结构化查询语言

学习方法

1.学会固定语法使用

2.多进行案例的分析,以及多看表的结构

3.记住常用语法

注释

--单行注释
/*
多行
注释
*/

注意的事项

1.关键词(系统的命令,函数名称,表名,列名等其他对象名)不区分大小写,但是数据严格区分大小写。 2.每一句SQL语句的后面必须加上分号结尾。 3.执行SQL语句的时候,必须使用鼠标选中需要执行的行,点击执行。

用户对象

1. 只要是和对象相关的,则可以在创建的时候使用的关键字:create

2. 对象的编辑:alter

3. 对象的删除:drop

4. 创建用户只允许使用管理员账号进行创建

创建用户

--语法 CREATE user 用户名 identified by 密码；
CREATE USER test2 IDENTIFIED BY 123456;

赋权

--语法:grant 权限1,权限2..... to 用户名;
--connect,resource 普通用户权限
--dba 管理员权限
GRANT CONNECT ,resource TO test2;

收回权限

--语法:revoke 权限1,权限2..... from 用户名;
REVOKE CONNECT,resource FROM test2;

用户修改密码

--语法:alter user 用户名 identified by 新密码;
ALTER USER test2 IDENTIFIED BY qwe123;

用户锁定

--语法:alter user  用户名 account lock;
ALTER USER test2 account LOCK;

用户解锁

--语法:alter user  用户名 account unlock;
ALTER USER test2 account unlock;

删除用户

--语法:drop user 用户名 cascade;
DROP USER test2 CASCADE;

SQL语言分类

DQL语言:data query language 数据查询语言 select DML语言:data mainpulation language 数据操纵语言 insert update delete DDL语言:data definition language 数据定义语言 create alter drop DCL语言:data control language 数据控制语言 grant 授权 rollback 回滚

数据类型

number(n):数字类型,当前输入框最多输入n位,默认是整型 number(5):最大填入的数字:0-99999 number(n,m):当前输入框能够输入的最大位数为n为,小数点往前面移动m位 number(5,2):最大填入的数字:0-999.99 varchar2(n):字符串类型,最多输入n个英文字符,n/2个中文字符 varchar2(20):最多输入0-20个英文字符,0-10个中文 varchar2(21):最多输入0-21个英文字符,0-10个中文 char(n):不可变的长度,字符串类型 char(2):必须要输入2个字符,如果没有达到2个,则用空格来进行补齐 date :日期类型,默认定位到的YYYY-MM-DD HH:Mi:SS timestamp :时间戳,定位到世纪,毫秒 BloB:存储图像,声音,视频等文件,存储的最大空间为4G

常见运算符

+ 加
- 减
* 乘
/ 除
% 取余 10%3=1
** 取幂 2**3=8
= 等于
!= 不等于 <>不等于

逻辑运算符

表达式1 and 表达式2 :查询既满足表达式1和表达式2的数据内容 表达式1 or 表达式2:查询出满足表达式1或者满足表达式2的所有数据 age>=18 or age<=7 not 表达式 :非,是表达式的相反数据结果 sex='男' not sex='女' sex!='女'

表对象

表是数据库存储数据的最小单位。 对测试来说,一般很少会进行表的创建,创建表一般都是后台或者DBA进行管理和测试的。 相关的一种测试方法:字段测试(查看后台开发的表结构是否和需求一致),测试还需了解表的结构。 因为需要在测试执行的过程中,来进行测试数据的添加。

--PS:SQL语言自动过滤空格 --PS:列名的取名不能是用关键字 --PS:最后一个列的后面不需要加逗号 --PS:在oracle中所有的字符内容必须使用单引号引起来,数字不需要 --PS:oracle支持中文的表名和列名,但是不建议使用

新增表

--语法:create table 表名(列名 列的属性 [列的约束],列名2 列的属性 [列的约束]....);
--用户信息表
 --用户ID,昵称,密码,住址,联系方式,性别,出生年月,年龄,爱好
 Create Table userinfo(
 userid Varchar2(20) Primary Key,   --用户ID 主键约束
 nickname Varchar2(20) Not Null unique, --昵称 非空+唯一
 passwd Varchar2(18) Not Null,   --密码  非空
 address Varchar2(100) default('深圳南山'), --住址  默认
 Tel Number(11) Not Null check(Tel Between 10000000000 And 19999999999), --联系方式  非空+检查
 sex char(2) default('男') Check(sex In('男','女')),           --性别 默认
 born date,             --出生年月
 age number(2) Check(age>=18 And age<=35),         --年龄 检查
 hobbit Varchar2(200) Default('无') --爱好  默认
 );
 Create Table 商品信息(
 商品id Number(30) Primary key,
 商品名称 Varchar2(20) Not Null  unique,
 商品价格 number(11,2) Not Null Check(商品价格>0)
 );
 
Create Table TB_Order(     --新增淘宝订单表
 BuyID Varchar2(20) References userinfo(userid) Not null ,  --用户ID
 BuyName varchar(50) Not Null ,        --用户名称 非空
 GoodsID Number(30 )Not Null Unique References 商品信息(商品id), --商品ID 非空,唯一,外键约束(商品信息表:商品ID,商品的名称,商品的价格)
 goodsname Varchar2(100) Not Null Unique,     --商品名称 非空约束,唯一约束
 Orderid Varchar2(40) Primary Key,       --订单Id   主键约束
 goodsnum Number(5) Default(1)  Check(goodsnum>=1 And goodsnum<=55555) ,    --订单数量 检查1-55555 ,默认为1件
 goodprice  number(11,2) Not null,   --商品价格   非空

 );

六大约束

/*
Not null :非空约束
default(值) :默认约束 default('男') default(1) 
     --PS:默认约束不能和非空约束放在一起使用
     --DEFAULT的优先级最高,所以当存在多个不同约束时,必须放在所有约束之前
unique:唯一约束
check(表达式):检查约束,用来控制用户输入的内容
     check(age>=18 and age<=35) 等于 check(age between 18 and 35)
     check(name=A or  name=B or name=C) 等于 check(name in(A,B,C))
primary key :主键约束
      1.每一张表只能有一个主键约束
      2.选择主键约束的列,必须是可以作为其他表的调用的列
      3.主键列自带两个约束:not null非空  unique唯一
      4.主键表的创建一定要在外检表的创建之前进行
references 主键表(主键列名) :外键约束
      1.数据全部来自于另外一张表的主键列中
      2.作为外键列的名字可以和主键列的名字不一致
      3.但是数据类型和长度限制必须一致!!!!!
      4.外键约束没有自带任何约束
*/

表级和列级约束

--在正常的建表语句中,经常会先进行列和属性的定义,后面再把所有的相关约束放在一起进行编写
--后续如果要进行约束的变更时,方便查看,就用到了表级约束
--每一个约束都有自己的名字,默认是系统进行自动命名,也可以通过表级约束进行命名,方便约束的修改和删除
Create Table testa(sid number(10)Primary key);   --列级约束
--表级约束:写在列名定义之后
--命名要求:主键约束:PK_表名_列名 外键:FK_主表_列名_当前列的名 检查约束:CK_表名_列名 唯一性:UQ_表名_列名
--关键字:constraint 约束名 约束的类型(约束对象)
Create Table testa(sid number(10),Constraint PK_testa_sid Primary key(sid));
-- 表级的外键约束:
--Constraint 外键名 Foreign Key(当前表的列名) References 主键表名(主键列)
--PS:能够被设为表级约束的:检查,外键,主键,唯一性
--非空和默认不能够作为表级约束

CREATE TABLE classinfo(--班级信息表
cid varchar2(255),--班级编号
ctype VARCHAR2(20) NOT NULL,--班级类型
cpostation VARCHAR2(255) NOT NULL,--班级类型
stratTime DATE NOT NULL,--开班时间
cstatus CHAR(4) NOT NULL,--班级状态
CONSTRAINT PK_classinfo_cid PRIMARY KEY(cid),
CONSTRAINT CK_classinfo_ctype CHECK(ctype IN('UI','开发','测试')),
CONSTRAINT CK_classinfo_cstatus CHECK(cstatus IN('在读','毕业'))
);
CREATE TABLE studentinfo(--学员信息表
stuno NUMBER(12), --学号
stuname VARCHAR2(50) NOT NULL, --姓名
stusex CHAR(2) NOT NULL,    --性别
stuage NUMBER(3) NOT NULL,  --年龄
stutel varchar(11) NOT NULL, --联系方式
stuxueli Varchar2(20) NOT NULL, --学历
stuspac VARCHAR2(255) NOT NULL, --专业
stuschool Varchar2(255) NOT NULL, --毕业院校
stuaddress Varchar2(255) DEFAULT('地址不详'), --家庭住址
stujinyan VARCHAR2(255) DEFAULT('无'),  --工作经验
intime DATE, --参加培训时间
class_belong varchar2(255) NOT NULL, --所属班级
CONSTRAINT PK_studentinfo_stuno PRIMARY KEY(stuno),
CONSTRAINT CK_studentinfo_stusex CHECK(stusex IN('男','女')),
CONSTRAINT CK_studentinfo_stuage CHECK(stuage>=18 And stuage<=35),
CONSTRAINT CK_studentinfo_stuxueli CHECK(stuxueli IN('本科','专科','研究生')),
CONSTRAINT FK_studentinfo_class_belong FOREIGN KEY(class_belong) REFERENCES classinfo(cid)
);

编辑约束

--添加约束(主键+唯一+检查+外键)
--语法:alter table 表名 add constraint 约束名 约束类型(列名);
Alter Table userinfo Add Constraint UQ_userinfo_tel Unique(tel);
--添加约束(非空,默认)
--语法:alter table 表名 modify 列名 not null/列名 default(值);
Alter Table userinfo Modify age Not Null;
Alter Table userinfo Modify age default(20);
--关闭某个约束
--语法:alter table 表名 disable constraint 约束名;
Alter Table userinfo Disable Constraint UQ_userinfo_tel;
--启动某个约束
--语法:alter table 表名 enable constraint 约束名;
Alter Table userinfo enable Constraint UQ_userinfo_tel;
--删除约束
--语法:alter table 表名 drop constraint 约束名;
--PS:即使当前存在数据,也可以删除指定的约束,默认和非空无法直接删除,必须删除列
--PS:如果是要修改指定的主键/外键/检查约束时,必须把之前的约束删除,然后再新增.
Alter Table userinfo drop Constraint PK_userinfo_userid;
--取消非空约束
--语法:Alter Table 表名 Modify 列名 null

修改表结构

--添加列
--语法:alter table 表名 add 列名 列的属性 列的约束;
--PS:如果表中存在数据,则不能再添加列的时候,列的约束中不能使用非空/primary key
Alter Table userinfo Add money Number(11,2) Not Null;
Alter Table userinfo Add Constraint pk_id Primary Key(userid);
--删除列
--语法:alter table 表名  drop column 列名;
--PS:删除列为主键列时,不能直接删除,必须先将主键约束删除,然后才能删除指定的列
Alter Table userinfo Drop Column userid;

--修改列的名字
--语法:alter table 表名 rename column 列名 to 新列名;
Alter Table userinfo Rename Column money To SXmoney;

查询

基本查询

--查询骨架:
/*
select 列名,列名2..... from 表名 
[where 筛选条件]                      --通过where进行数据的筛选和过滤
[group by 列名] [having 筛选条件]      --分组
[order by 列名] [ASC|DESC];            --排序
*/
--oracle公司给新的使用者提供非常便捷学习的帮助文档以及适用项目,存在一个账号SCOTT账号可以
--给到新使用者了解语法以及了解数据库结构和优化的案例,平常这个账号为锁定中的状态.
/*
账号:scott 密码:tiger ,登录权限选择normal
账号默认为锁定状态: alter user scott account unlock;
*/
Select * from emp; 
Select * from dept; 
/*
--查询列的时候,如果想要把所有的列全部展现出来,则可以使用*代替所有列,也可以一列一列的名称描述
Select * from emp ;
Select empno,ename,JOB,MGR,hiredate,sal,comm,deptno from emp e; 
Select e.* from emp e; 
----------表别名,列别名-----------
--表别名:用户多表高级查询时使用,也方便我们书写列有帮助
--列别名:将查询的结果表可以使用中文列方便相关人员查阅,在后续高级查询也有帮助
--方法:列名 [as] 新的列名,表别名直接在表名后加空格即可 
--PS:在一句SQL语句中,表别名不能重复,表别名一般会取首字母
--查询出当前emp表中所有员工姓名以及工资
Select e.ename,e.sal from emp e; 
--查询出当前emp表中所有员工姓名以及工资,显示中文列名
Select e.ename As 员工姓名,e.sal As 员工薪水 from emp e; 
Select e.ename  员工姓名,e.sal  员工薪水 from emp e; 
-----------------where筛选条件-------------------
--只要产生的临时表数据有减少,则大部分都是通过where来进行筛选的 
--查询出[JAMES]的工资
Select sal from emp e Where ename='JAMES' ; --数据严格区分大小写
--查询出[30号]部门的所有员工信息
Select * from emp e Where deptno=30  ;   --如果是数字,则不需要使用引号
--查询出30号部门中所有销售人员的姓名以及工资
Select ename 员工姓名,sal 员工工资 from emp e Where deptno=30 and job='SALESMAN';
--查询出公司员工工资大于1000小于3000的员工信息
Select * from emp e Where sal>1000 And sal<3000; 
--查询出公司员工部门是20号部门,并且薪水大于1500以上的员工姓名以及薪水
Select ename,sal from emp e Where deptno=20 And sal>1500; 
--查询出公司员工工资是1500和3000,2850的员工姓名以及所在岗位和薪水
Select ename,job,sal from emp e Where sal=1500 Or sal=2850 Or sal=3000;
Select ename,job,sal from emp e Where sal In(1500,2850,3000);  
--查询出工资加上奖金大于2000以上的员工姓名以及薪水
Select ename 姓名 ,sal+nvl(comm,0) 薪水  from emp e Where sal+nvl(comm,0)>2000; 
--查询出没有奖金的员工姓名以及工作岗位
Select ename,job from emp e Where comm=0 Or comm Is null  ; 
--null 空值,属于字符
      --Is Null 为空的判断
      --Is Not Null 不为空的判断
--查询公司发奖金的员工姓名以及奖金金额
Select ename,comm from emp e Where comm>0; 
Select ename,comm from emp e Where comm Is Not Null And comm!=0; 
--转换函数:nvl(列名,要替换的值)
--查询出所有员工姓名以及员工的奖金,如果奖金为空,默认显示数据为0
Select  ename,nvl(comm,0) from emp e; 

模糊查询

--% :替换N个字符 张%:查询出所有姓张的人,后面可以是0个字符,也可以是N个字符
--_ :替换1个字符 张_:查询出所有姓张的人名,后面只能跟1个任意字符,下划线不包含0个字符
--PS :在模糊查询中不能直接使用=.把等于号换成like
--查询出公司员工姓名以J开头的员工姓名以及薪水
Select ename,sal from emp e Where ename Like 'J%'; 
--查询出公司姓名以S开头,T结尾的员工姓名
Select ename from emp e Where ename Like 'S%T'; 
--查询出公司以D结尾的员工姓名
Select ename from emp e Where ename Like '%D';
--查询出公司姓名包含M的员工姓名
Select ename from emp e Where ename Like '%M%';
--查询出公司姓名第三个字母为A的员工姓名
Select ename from emp e Where ename Like '__A%';
--查询出公司姓名只有5个字符的员工姓名
Select ename from emp e Where ename Like '_____';
--length(列名) 统计长度
Select ename from emp e Where length(ename)=5;

排序

--PS:where 永远是跟在from表名的后面,order by是在最后才进行排序
--关键字:order by 列名 asc|desc,列名 asc|desc ...  :按照指定列进行降序/升序,如果排序之后,有数据相同的,则按照列名进行升序/降序排序
--排序的方法: asc  升序(默认的排序方式,可以省略)|desc  降序
--按照工资的降序进行排序
Select * from emp e Order By sal desc; 
--按照员工的所在的部门编号进行升序排序
Select * from emp e Order By deptno asc; 
Select * from emp e Order By deptno ;
--按照员工的所在的部门编号进行升序排序,在部门中按照薪水进行降序排序
Select * from emp e Order By deptno asc,sal desc; 
--按照员工的所在的部门编号进行升序排序,在部门中按照薪水进行降序排序,如果薪水一致,则按照员工的员工编号进行升序排序
Select * from emp e Order By deptno asc,sal desc,empno asc; 
--查询出30号部门所有员工姓名以及薪水,并且按照薪水的降序排序
Select ename,sal from emp e Where deptno=30 Order By sal Desc; 
--查询出工资高于1500以上,并且所在部门为30号部门的所有员工,按照员工编号进行升序排序
Select * from emp e Where sal>1500 And deptno=30 Order By empno;
--查询出在job为销售以及分析师的员工信息,按照薪水进行降序排序,如果薪水一致,
--按照所在的部门进行升序排序,如果所在部门一致,按照员工入职时间进行降序排序
Select *
  From Emp e
 Where Job In ('SALESMAN', 'ANALYST')
 Order By Sal Desc, Deptno, Hiredate Desc;

聚合函数

--主要用来针对数据列的统计使用.经常会在软件种很多统计类的需求.
--聚合函数查询的结果一般都建议进行别名,方便查看
/*
sum(列):求和
max(列):当前列的最大值
min(列):最小值
avg(列):平均值
count(列):统计行数
 count()选择的列一般都会选择主键列,不会选择其他可以允许为空的列
*/
--查看当前emp表中工资最高,最低,平均工资,工资支出,员工人数
Select Max(Sal) 最高工资,
       Min(Sal) 最低工资,
       Avg(Sal) 平均工资,
       Sum(Sal) 工资总额,
       Count(Sal) 员工人数
  From Emp e;
--查出公司当月总支出
--PS:聚合函数可以自动过滤空行,除了count()
Select Sum(comm)+Sum(sal) 总支出 from emp e;
Select Sum(nvl(comm,0)+sal) 总支出 from emp e;
--查询出公司有多少个员工
Select count(mgr) from emp e; 
--PS:不建议在使用count函数时,选择其他列,或者直接使用count(*),可以统计当前表中的所有行
-------Select ename, max(sal)from emp e;  
--PS:不能将聚合函数和普通列的查询放在同一个查询语句的列中

分组函数

--骨架:select 列名 from 表名 where 筛选条件 group by 列名 having 筛选条件 order by列名 desc|Asc;
--分组的原理:将表中的数据,指定的列,按照数据的一致性进行分组
--分组函数的作用:
--1.去重的作用,可以将当前列中重复的数据进行去除
--2.可以结合聚合函数进行不同小组数据的统计
--PS:不能选择有唯一性约束的列进行分组
--PS:只要用上了group by ,则不再允许在列名位置使用* ,只能使用聚合函数或者分组的列名

--查询emp表中有哪些工作岗位
Select job from emp e Group By job; 
--查询出emp表中有哪些工位,分别人数是多少
Select job 岗位,count(*)人数 from emp e Group By job; 
--查询出emp表中[每个]部门的最高工资,最低工资,以及平均工资,人数统计
Select Deptno 部门编号,
       Max(Sal) 最高工资,
       Min(Sal) 最低工资,
       Avg(Sal) 平均工资,
       Count(*) 人数
  From Emp e
 Group By Deptno;
 
 --distinct 列名和group by 列名的区别
 --都有去重的作用,但是ditinct仅仅只有去重的作用,没有产生临时表,不能进行其他的相关操作(统计)
 --group by有产生临时表,可以进行聚合函数的使用,并且效率更高,并且group by 的查询性能优于distinct
 Select Distinct(deptno)from emp e; 
 Select deptno from emp e Group By deptno;
 
-------------分组+having 筛选---------------
--where 和having的用法:
--1.where的筛选条件中是绝对不能直接使用聚合函数!
--2.having和group by 一起捆绑使用,而且having后面主要是使用聚合函数
--3.where永远会在having之前进行
--统计[各个部门的]所有员工总数,并且显示最高工资,平均工资
Select deptno,max(sal) from emp e Group By deptno ; 
--统计[各个部门的]所有员工总数,并且显示最高工资,平均工资,只显示平均工资高于2000以上的岗位
Select deptno,max(sal),avg(sal),count(*) from emp e  Group By deptno Having avg(sal)>2000 ; 
--统计各个部门工资高于2000以上的员工人数,并且只显示部门人数大于3人以上的部门编号以及人数
Select deptno ,count(*) from emp e Where sal>1000 Group By deptno  Having count(*)>3; 

wm_concat

--wm_concat(列名)+group by 列名:分组的时候,将指定的列的数据放在一个表格中存储
--wm_concat(列名):数据连接函数
--查询出每个部门的所有员工姓名
Select deptno,wm_concat(ename) 员工姓名 from emp e Group By deptno; 
--查看出每个部门的所有员工工资
Select deptno,wm_concat(sal) 员工姓名 from emp e Group By deptno; 
--|| 拼接符号  
Select deptno,wm_concat(ename || ':' || sal)  from emp e Group By deptno; 

--打印出一句话:XX的工资是XX,工作为XX岗位
Select Ename || '的工资是:' || Sal || ',工作为:' || Job || '岗位.' 员工信息
  From Emp e
 Where e.Ename = 'JAMES';

in,all any

--常用的一些查询关键字
--in(条件1,条件2...):同or的用法,会将满足条件1,条件2...等数据全部查询出来
--not in (条件1,条件2)..是前面in的使用的相反值
--------any的用法-----------
--=any(条件1,条件2....):作用和in以及or的用法一模一样
--查询工资等于1800,3000,1250的员工信息
Select * from emp e Where sal=1800 Or sal=3000 Or sal=1250; 
Select * from emp e Where sal In(1800,3000,1250);
Select * from emp e Where sal =Any(1800,3000,1250);
--<any(条件1,条件2..)：小于最大值
Select * from emp e Where sal <Any(1800,3000,1250);
-->any(条件1,条件2..)：大于最小值
Select * from emp e Where sal >Any(1800,3000,1250);
--=all:无法找到这样的数据
Select * from emp e Where sal =all(1800,3000,1250);
-->all:大于最大值
Select * from emp e Where sal >all(1800,3000,1250);
--<all:小于最小值
Select * from emp e Where sal <all(1800,3000,1250);

子查询

查询产生的临时表可以作为查询条件或者一张新表进行使用

使用子查询的步骤

1. 找到当前表中的关联字段（如果是跨表则找主键和外键，如果是单表，找关联信息的线索）

单表子查询

查询比SCOOT 工资更高的员工姓名以及薪水

SELECT  ename,sal FROM emp e WHERE sal >(SELECT sal FROM  emp WHERE ENAME ='SCOTT');

查询出比30号部门薪水更高的的员工信息

SELECT * FROM emp WHERE sal >ALL (SELECT SAL FROM EMP WHERE DEPTNO=30);

PS：如果子查询的结果不止一个，就必须使用in,any,all的方式进行查询

查询出比30号部门平均要高,并且比JONES还要高的员工姓名以及薪水

SELECT ename,sal FROM emp WHERE SAL >all(
(SELECT avg(SAL) FROM emp WHERE DEPTNO=30),
(SELECT SAL FROM emp WHERE ENAME ='JONES'));

多表子查询

数据跨表

查询出JAMES的上班地点

SELECT loc FROM DEPT d  WHERE DEPTNO =(SELECT DEPTNO FROM EMP e2 WHERE ename='JAMES');

查询出比SALES部门的所有员工信息

SELECT * FROM EMP e2 WHERE DEPTNO =(SELECT DEPTNO FROM DEPT d2 WHERE DNAME='SALES');

查询出比SALES部门工资还要高的员工信息

SELECT * FROM EMP e WHERE SAL >ALL (SELECT SAL FROM EMP e2 WHERE DEPTNO=(SELECT DEPTNO FROM DEPT d2 WHERE DNAME='SALES'));

查询出在CHICAGO上班的员工总数

SELECT COUNT(*) FROM EMP e WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT d2 WHERE loc='CHICAGO');

查询出在CHICAGO上班并且工资高于2500以上的员工信息

SELECT * FROM EMP WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT d2 WHERE loc='CHICAGO') AND sal>2500;

分页查询

rowid

真实存在的地址，当前行数据存放的真实的物理地址

SELECT e.*,rowid FROM emp e;

查询名称为SOCTT的员工信息

SELECT * FROM  EMP e  WHERE ROWID= (SELECT rowid FROM EMP e WHERE e.ENAME ='SCOTT');

rownum

伪列:不存在的编号,每一张表都有一个伪列，临时表也存在，而且每一张表，伪列数字永远从1开始

rownum的解题思路

1.如果是要查询当前表中前几行的数据,则可以直接使用:列名<=行数 2.如果想要查询中间几行的数据 A.先把rownum列加入到原表中 B.为了区分和后面临时表中的rownum,则必须将原表中的rownum起一个别名 3.在分页查询中增加了排序 1.如果只是查询前几行的数据,则先用子查询的方式进行排序,直接使用rownum<=行数,即可查询结果 2.查询中间几行数据的时候: A.先做一个子查询,进行排序 B.在已经排好序的表中增加rownum并且取别名 C.从当前已经排好序,并且包含rownum列的临时表中,进行查询指定的区间数据即可

查询出emp表中前5行的数据

Select * From emp e  Where Rownum<=5

查询出emp表中3-10行数据

SELECT * FROM (SELECT e.*,rownum r FROM EMP e) WHERE r>=3 AND r<=10;

查询出工资最高的三位员工信息

SELECT * FROM (SELECT * FROM EMP e ORDER BY sal DESC) WHERE rownum<=3;

查询出工资排在第3-6的员工信息

SELECT * FROM (SELECT e.*,rownum r FROM (SELECT * FROM EMP ORDER BY sal DESC）e) WHERE r>=3 AND r<=6;

增删改

新增数据

--语法:insert into 表名 values(值,值2);
--对数据库进行数据的新增,编辑和删除,都必须使用事务语句:
--commit; 提交(不是一句sql语句一格commit,可以将所有的数据全部添加成功之后,再进行一次commit即可)
--rollback;回滚
/*
1如果表名后没有增加指定的列,则值必须和表中的字段数量保持一致
2列名可以切换位置,但是后面values对应的值也必须和前面的列名保持一致
3指定某些列进行数据传入的时候,必须一定要选择非空的列在其中
4.插入的数据一定要结合当前需求中的实际约束来进行数据的提供
*/
Insert Into userinfo Values(123,'胡歌',19);
Insert Into userinfo(sid,sname,sage) Values(123,'刘德华');--报错,没有足够多的值
Insert Into userinfo(sname,sid,sage) Values('刘德华',1234,23);
--传入空值时
--PS:插入数据到指定的列中,如果想要传入的数据为空,可以在insert 语句中使用关键字null
--千万不能不填!!
Insert Into userinfo Values(1245,'刘亦菲',19,null);
--传入时间类型
--PS:时间是一段字符串,所以必须使用单引号引起来
--月份必须使用大写的缩写,前三个字母
Insert Into userinfo Values(12456,'黄渤',19,13333333333,'26-JAN-2020');
--使用to_date('时间数据','时间的格式'):格式必须和传入的数据类型保持一致
--YYYY-MM-DD HH:mi:ss :默认的是12H制
--YYYY-MM-DD HH24:mi:ss :指定24H制度
Insert Into Userinfo
Values
  (124567,
   '王宝强',
   19,
   13333333336,
   To_Date('2020-10-26 14:49:20', 'YYYY-MM-DD HH24:Mi:SS'));
/*
January 一月
February 二月
march 三月
April 四月
may 五月
june 六月
july 七月
August 八月
septemper 九月
October 十月
november 十一月
December 十二月
*/
--插入外键数据时,必须使用的数据是主键中存在的数据
Insert Into userorder Values('8888',8838888,'飞机');
Commit;

--插入几条数据
--emp表中插入一条数据
Select * from emp e;

更新数据

--语法:update 表名 set 列=新的值 [where 筛选条件];
--PS:如果不加where,会导致当前列的所有数据全部更新
--PS:修改的时候,需要注意表的结构,不能修改成唯一性的数据
--也得在语句的后面使用commit进行提交
Update userinfo Set sage=30 Where sname='刘德华';
--把年龄低于20岁的信息,每个人增加2岁,高于25岁的人员年龄较少1岁
--A=1:把1赋予A这个变量  
--A=A+1:把A+1的结果赋予变量A(新的变量)
Update userinfo Set sage=sage+2 Where sage<20;
Update userinfo Set sage=sage-1 Where sage>25;
--在研发语言中存在A+=1自增,但是oracle 不存在

-------------删除数据-------------------
--语法:delete from 表名 [筛选条件];
--PS:一定要记得加上where筛选条件!!!
--PS:如果存在主键的数据正在被外键进行调用,则必须将外键的数据删除之后,才能删除成功
--delete from 表名:删除表中所有数据
Delete From userinfo;
Select * From userinfo e;
Select * from Userorder;
--删除电话号码为空的用户信息
Delete from userinfo Where tel Is Null;
--清空表
--truncate table 表名 cascade; 清空表数据
--存在主键被其他表调用时,不能使用truncate进行清空
Truncate Table userorder ;

--将emp表中工资低于1000以下的员工奖金增加200
Update emp Set comm=nvl(comm,0)+200 Where sal<1000; 

--将10号部门的所有员工薪水上涨10%;
Update emp Set sal=sal*(1+10%) Where deptno=10
 
--删除30号部门中工资高于6000以上的员工信息
Delete emp Where sal>6000 And deptno=30;

删除数据

--语法:delete from 表名 [筛选条件];
--PS:一定要记得加上where筛选条件!!!
--PS:如果存在主键的数据正在被外键进行调用,则必须将外键的数据删除之后,才能删除成功
--delete from 表名:删除表中所有数据
Delete From userinfo;
Select * From userinfo e;
Select * from Userorder;
--删除电话号码为空的用户信息
Delete from userinfo Where tel Is Null;
--清空表
--truncate table 表名 cascade; 清空表数据
--存在主键被其他表调用时,不能使用truncate进行清空
Truncate Table userorder ;

--将emp表中工资低于1000以下的员工奖金增加200
Update emp Set comm=nvl(comm,0)+200 Where sal<1000; 

--将10号部门的所有员工薪水上涨10%;
Update emp Set sal=sal*(1+10%) Where deptno=10
 
--删除30号部门中工资高于6000以上的员工信息
Delete emp Where sal>6000 And deptno=30;

常用函数

--dual:临时表,不能存储数据的一张表,可以将结果暂时存放在dual表中,只能用于查询存放
Select sysdate from dual; 
--sysdate:系统时间

------------------to_date ,to_number ,to_char 转换函数---------------
--to_date('时间','格式'):把字符串转换成时间,一般用于数据的插入
Select to_date(Sysdate,'DD-MM-YY') from dual; 
Select to_date(Sysdate,'DD/MM/YY') from dual;
Select to_date(Sysdate,'DD.MM.YY') from dual; 
Select to_date('2020-10-28 10:13:23','YYYY-MM-DD HH:Mi:SS') from dual;  
Select to_date('2020-10-28 10:13:23','YYYY-MM-DD HH24:Mi:SS') from dual; 
--to_char(时间列):把时间类型转换成数字
--主要用于数据连接时,将时间类型和字符串类型进行拼接时使用
--打印一句话:scott的入职日期为XX
Select ename ||'的入职日期是:'|| to_char(e.hiredate) from emp e Where e.ename='SCOTT'; 
--to_number():如果想要把时间格式转换成数字,必须先在中间转换成字符串类型
--to_date提取需要用到的时间数据,然后用to_char将时间数据转换成字符串,在后面的转换格式中,要去掉符号,否则无法转成数字
--,最终to_number转换成数字
Select to_number(to_char(to_date('2020-10-28 14:23:33','YYYY-MM-DD hh24:mi:ss'),'YYYYMMDDHHMISS') )from dual;

------------------------时间处理函数-------
--查询在1981/06/09入职的员工
Select * from emp e Where e.hiredate=to_date('1981/06/09','YYYY-MM-DD');
--查看SCOTT入职公司多久了
Select floor(Sysdate-hiredate) from emp e Where ename='SCOTT';
Select Sysdate-hiredate from emp e Where ename='SCOTT';
--可以使用日期直接减去整数的数字,作用是减掉指定的天数
Select sysdate-1 from dual; 
Select sysdate+1 from dual; 


--查看SCOTT用户和JAMES的入职日期相差多少天,向下取整
Select floor((Select Hiredate From Emp Where Ename = 'SCOTT') -
       (Select Hiredate From Emp Where Ename = 'JAMES')) 入职日期相差多少天
  From dual;
 
--months_between():计算两个时间之间的月份差
Select months_between(Sysdate,hiredate) from emp e Where ename='SCOTT';
--add_months:往一个指定的时间加上指定的月份:只能加上整月的数据
Select add_months(hiredate,402) from emp e Where ename='SCOTT';
--next_day():当前系统时间的下一星期N的时间
Select next_day(to_date('2020-10-24','YYYY-MM-DD'),6) From dual; 
--查询和scott在同一周的员工信息
--last_day():当前月份的最后一天
Select last_day(sysdate) From dual; 

---------------------数据的处理函数-----------
--floor:向下取整,不会四舍五入
Select floor(99.99) from dual;
--ceil:向上取整,只要有数据,都会取整
Select ceil(88.2) From dual;
--mod:取模(取余)
Select mod(100,8) from dual;--余数为4
--round():四舍五入
Select round(88.8) From dual;
--trunc():截断
--month:截断为当月的第一天
--year:当年的第一天
--day:当周的第一天
Select trunc(sysdate,'year') from dual; 

-------------字符处理函数---------------
--upper():将查询的结果大写字母显示
Select upper('helloworld') from dual; 
--lower():将指定的字符内容小写显示
Select lower(ename) from emp e; 
--initcap():首字母大写
Select initcap(ename) from emp e; 
--concat(A,B):将A和B进行连接,只能允许两个
Select concat(ename,sal) from emp e; 
--substr(字符串,n,m):截取当前传入的字符内容,n代表要截取的位置,m代表要截取到的长度
Select substr((Select ename From emp Where sal=5000 ),1,2) from dual; 
--length():统计字符长度
Select length('helloworld') from dual; 
Select ename,length(ename) from emp e; 
--lpad():指定字符长度,如果不够长度,则用* 或者#进行替换
Select lpad(ename,5,'*') from emp e; 
Select lpad(ename,10,'#') from emp e; 
--trim:将指定的字符内容删除,只能针对一个字符
Select Trim('B'From ename) from emp e; 
--replace():将指定的字符替换成其他字符
Select replace(ename,ename,'*******') from emp e; 

表连接

将多张表通过一个共同的字段（一般会选择主键或外键）连接在一起。

内连接

select * from A inner join B on A.字段=B.字段；
select * from A,B where A.字段=B.字段;

左连接

select * from A left join B on A.字段=B.字段；
select * from A,B where A.字段=B.字段(+);

右连接

select * from A right join B on A.字段=B.字段；
select * from A,B where A.字段(+)=B.字段;

全连接

select * from A full join B on A.字段=B.字段；

视图

视图是一张临时表

视图的作用？

1.可以将比较复杂，而且将使用频率比较高的查询语句做成一个个的视图，方便进行使用

2.权限管理，通过视图创建一些只有指定权限才能看到的数据

视图的缺点？

占用空间，消耗内存

创建视图

--创建视图不是所有用户都有权限:
Grant Create Any View To scott;
create or replace view 视图名 # 取名vw_表名
as
主体语句;（查询语句）
with read only; #只读
CRANT CREATE ANY VIEW TO 用户名; #给用户添加创建视图权限

删除视图

DROP VIEW 视图名；

PS:如果基表中数据产生变化，则视图的执行结果也会产生变化

索引

--索引的作用?
--用来做数据库查询优化的.索引是oracle自行维护的,如果列变动了,则索引自动失效
--我们只需要决定是否需要添加索引
--索引的原理:
--类似数本中的目录,不需要从整张表的数,一行一行的去进行筛选,而是通过目录的方式进行缩小范围性搜索
/*
语法:create index 索引名 on 表名(列名);
其中,主键列自带索引,order by 列名,distinct(group by) 列名也会增加索引
*/
--单一索引:真针对单个列
Create Index idx_emp_sal On emp(sal);
--组合索引:如果where查询语句中涉及多个列的时候,则可以使用组合索引
Create Index idx_emp_empsal On emp(empno,sal);
--创建带有函数的索引
Create Index idx_emp_lename On emp(lower(ename));
--创建一个工资总额的索引--聚合函数不能作为索引
Create Index idx_emp_sumsal On emp(sum(sal));
--修改列名之后,索引会失效,不会自动同步,需要手动进行重建
Alter Table emp Rename Column sal To newsal;
--重构索引(当列名产生变更时)
--语法:alter index 索引名 rebuild;
Alter Index idx_emp_sal Rebuild;
--删除索引
--语法:drop index 索引名;
Drop Index idx_emp_sal;

存储过程

--PLSQL语言:可以批量执行一批达到指定目的的SQL语句
--存储过程和代码里面的函数一样,需要被调用的时候,才会被执行
--测试为什么要使用存储过程?
--批量新增数据
/*
语法:
create or replace procedure 名字(参数 参数的类型,....)
as
begin
       主体SQL语句
end;
调用存储过程:
begin
名字(参数);
end;
*/
--创建
Create Or Replace Procedure proc_testa_Insert
As
Begin
       Insert Into testa Values(88,'test',25);
       Commit;
End;
--调用
Begin
       proc_testa_Insert;
End;
--------------批量增加数据-------------
Create Or Replace Procedure proc_testa_Insert
As
Begin
     For i In 1..1000  --在1-1000中间,进行取值,把取到的每一个值,赋予i这个变量
          Loop
          --dbms_output.put_line('今天天气真好!!');--打印的语句
          Insert Into testa Values(i,'test'|| i,25);
          End Loop;
       Commit;  --最好是写在循环之后,只要添加指定的数据之后,只需要执行一次commit即可,减少性能消耗
End;

Delete From testa
Select * from testa e;