Oracle数据sql语句

###############[一]Oracle知识大纲############
1.创建用户、给与登录和操作权限
2.sql单表查询
查询表所数据
查询表指定数据
添加别名
链接符
去除重复(去行)
单/多字段排序(升序 asc 降序 desc)

3.where子句查询筛选
使用运算符进行筛选 =,>,>=,<,<=,<> 单个条件中
查看入职日期在81年后的员工信息
多条件筛选（where子句关键字：and,or,like,is null,is not null, in ,between and）
4.单行函数
字符函数 数值函数 日期函数
5.多行函数
6.换行函数
7.其他函数
8.分组
9.数据的增删改及备份

10.多表联合查询
sql92和sql99:

11.子查询
12.oracle的账户管理
13.二维表管理
14.序列、索引、视图。
15.oracle分页查询

其他知识点:
a.修改在结果中直接修改数据SQL语句: select * from emp for update;

###############[二]Oracle知识点详细##############
1.创建用户、给与登录和操作权限
create user djh0810 identified by 123456;
grant connect to djh0810;
grant resource to djh0810;

2.orcal 修改密码
cmd命令中:
sqlplus/nolog
conn /as sysdba
alter user 要修改的用户名 identified by 要修改的密码。

3.sql单表查询
1)查询表所数据：select * from emp;
2)查询表指定数据：select empno from emp;
3)添加别名：select empno 员工编号, job as “工作” from emp;
4)链接符(字符使用单引号)：select empno || ‘–’ || job from emp;
5)去除重复(去行)：select distinct job, mgr from emp;
6)单字段排序(升序 asc 降序 desc)：select * from emp order by empno desc;
7)多字段排序(升序 asc 降序 desc)：select * from emp order by empno,job desc;

4.where子句查询筛选
1)查看入职日期在81年后的员工信息 注意：oracle默认的日期格式为 日-月-年，示例’03-1月-1981’
select * from emp where hiredate>=‘01-1月-1981’ order by hiredate
2)使用运算符进行筛选 =,>,>=,<,<=,<> 单个条件中
select * from emp where ename=‘SMITH’
3)and :
select * from emp where sal>=2000 and sal<3000
4)or:
select * from emp where job=‘SALESMAN’ or job=‘ANALYST’ or job=‘MANAGER’ order by job
5)like:
select * from 表名 where 字段名 like ‘%字符%’
6)is null:
select * from 表名 where 字段名 is null
7)is not null:
select * from emp where comm is not null and comm>0
8)in:
select * from emp where job in(‘SALESMAN’,‘ANALYST’,‘MANAGER’)
9)between and :
select * from emp where sal between 2000 and 3000

5.单行函数
1)字符函数
首字母大写:initcap(‘hello’)[Hello]
转换为小写:lower(‘FUM’)[fun]
转换为大写:upper(‘sun’)[SUN]
左剪裁:ltrim(‘xyzsdms’,‘xyz’)[sdms]
右剪裁:rtrim(‘xyzsdms’,‘dms’)[xyzs]
按字符翻译:translate(‘jack’,‘abcd’,‘1234’)[j13k]
字符串替换:replace(‘jack and jue’,‘j’,‘bl’)[black and blue]
查找子串位置:instr(‘worldwide’,‘d’)[5]
取子字符串:substr(‘abcdefg’,3,2)[cd]
连接字符串:concat(‘Hello’,‘world’)[Helloworld]
2)数值函数
伪表:是不存在的表，是为了方便数据的验证而临时存在的表[dual]
取绝对值:abs(-15)[15]
向上取整:ceil(44.778)[45]
正弦:sin(1.57)[0.9999996]
余弦:cos(0)[1]
取符号:sign(-32)[-1] sign(32)[1]
向下取整:floor(100.2)[100]
m的n次幂:power(4,2)[16]
取余数:mod(10,3)[1]
四舍五入:round(100.256,2)[100.26]
截断:trunk(100.256,2)[100.25]
平方根:sqrt(4)[2]
3)日期函数
返回两个日期的月份：months_between(‘04-11月-05’,‘11-1月-01’)[57.777]
返回把月份数加到日期上的新日期：add_months(‘06-2月-03’,1)[06-3月-03]
返回指定日期的星期对应的新日期：next_day(‘06-2月-03’,‘星期一’)[10-2月-03]
返回指定日期所在的月的最后一天:last_day(‘06-2月-03’)[‘28-2月-03’]
按指定格式对日期进行四舍五入:
round(to_date(‘13-2月-03’),‘YEAR’)[‘01-1月-03’]
round(to_date(‘13-2月-03’),‘MONTH’)[‘01-2月-03’]
round(to_date(‘13-2月-03’),‘DAY’)[‘16-2月-03’]
对日期按指定方式进行截断:
trunc(to_date(‘06-2月-03’),‘YEAR’)[‘01-1月-03’]
trunc(to_date(‘06-2月-03’),‘MONTH’)[‘01-2月-03’]
trunc(to_date(‘06-2月-03’),‘DAY’)[‘02-2月-03’]
4)其他函数
获取字段长度: length(字段名)
获取系统当前时间: sysdate

6.多行函数
1)多行函数一共有: max 、min、avg、sum、count
2)多行函数注意点:
多行函数不能和普通字段直接混用，除非分组。
多行函数不能和单行函数，除非分组。
3)count用法有: count(*) 、count(字段名) 、count(distinct 字段名)

7.转换函数
1)to_number(数值类型的字符)
字符转数字: to_number(‘123’)[123]
2)to_char(数值或是日期)
数字转字符: to_char(123)[‘123’]、 to_char(123222,'$999,99,999^{\prime }){[}^{\prime }123^{\prime }]日期转为字符:t{o}_{c}har(newdate(){,}^{\prime }yyyy-mm-d{d}^{\prime })格式有：9表示位置的占位L表示人民币符号，$表示美元符号
0(零)也可以表示占位分组，值不够可以用0进行部位。
3)to_date(日期格式的字符)
字符转日期:to_date(‘2019-09-09’,‘yyyy-mm-dd’)
格式:
年yyyy 月mm 日dd
‘YYYY-MM-DD HH24:MI:SS AM DY’ -> 2020-01-03 00:13:37 上午 星期五

8.其他函数
1)nvl(comm,0):
如果comm为null就是0，不是null就是自己。
2)nvl2(comm,sal+comm,sal):
如果comm为null就是sal+comm，不是null就是sal。
3)decode(job,‘MANAGER’,‘经理’,‘PERSIDENT’,‘董事长’,‘普通员工’)：
if–elseif–else

9.分组
1)关键字：
group by 分组字段名 …

2)注意1：
使用了分组后，在select 语句中只允许出现分组字段和多行函数。
如果分组字段使用多个，则先按第一个分组字段进行分组，然后每个小组后在按照第二个字段继续分组，以此类推。
where条件中不能使用多行函数
3)分组筛选
关键字: having[针对分组后进行结果进行筛选，允许使用多行函数]
注意点: having必须和分组结合使用。不允许单独使用。
4)where和having比较
where条件中不能使用多行函数
where子句和having都可以使用普通字段直接进行筛选，但是where的效率高于having
where执行顺序：form -> where-> group by ->select->order by
having执行顺序：form -> group by ->select->having -> order by
where筛选字段，having筛选多行函数

10.数据的增删改及备份

1)增加数据
a.增加SQL语句：
insert into 表名(字段名，字段名，字段名…) valuse(字段值,字段值,字段值…)

2)删除数据
a.删除SQL语句:
delete from 表名;[清空表中所有数据]
truncate table dept;[清空表中所有数据,效率高于delete]
delete from 表名 where 条件 ;[删除表中符合条件所有数据]

3)修改数据
a.修改SQL语句:
update 表名 set 字段名=新的值，字段名=新的值 …
update 表名 set 字段名=新的值，字段名=新的值 … where 条件

4)数据的备份
a.表级别备份:
create table deptBak as select * from dept;[备份表数据]
注意: 只备份表数据和表结构，约束不会备份。
b. 部分备份:
create table deptBak as select deptno,dname from dept;
c.数据整体插入:
insert into deptBak select * from dept;
注意:查询语句的结果字段电话机必须和插入表名的字段数据量，类型一致

11.多表联合查询
a.SQL92方式:
笛卡尔积: select * from emp,dept ;
等值连接：
select * from emp,dept where emp.deptno= dept.deptno;
select e.ename,e.job,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno;
不等值连接：select * from emp e,salgrade s where e.sal>s.losal and e.sal=s.hisal;
自连接：select e1.* from emp e1, emp e2 where e1.mgr= e2.empno;
外连接：
左外连接: select * from emp,dept where emp.deptno(+)= dept.deptno;
右外连接: select * from emp,dept where emp.deptno= dept.deptno(+);

b.SQL99方式[重要 难于书写易于阅读]:
笛卡尔积: select * from emp cross join dept ;
自然连接: 先笛卡尔积然后按照相同字段自动等值筛选。
select * from emp natural join dept;
select * from emp inner join dept using (deptno,ename);
select * from emp inner join dept on emp.deptno= dept.deptno;[重要]
外连接:
左外连接: select * from emp left [outer可以省略不写] join dept on depton=deptno; [重要]
右外连接: select * from emp right join dept on emp.deptno=dept.deptno; [重要]
全外连接: select * from emp full join dept on emp.deptno=dept.deptno; [重要]
自连接:
select * from emp e1 inner join emp e2 on e1.mgr=e2.empno;

c.SQL99实现3张表查询
select * from emp e
inner join dept d on e.deptno=d.deptno
inner join city c on d.loc=c.cid
where e.sql>2000 or e.comm is not null;

12.子查询
使用时机:当查询条件不明确时，考虑使用子查询。
单行子查询
a.使用时机:筛选条件不明确需要执行一次，并且查询结果只有一个数据。
b.例子:select * from emp where sal>(select sal from emp ename=‘CLARK’);
C.where子句允许出现查询语句，该查询语句成为子查询。
多行子查询
a.使用时机:查询一个字段但有多n个值，考虑使用多行子查询。
b.使用关键字: any[任意一个] 、all[所有的] 、in[任意一个相当于 “=any”]、 not in
c.例子:select * from emp where sal > any (select sal from emp where job=‘CLERK’);

13.oracle的账户管理
a.创建账户
创建账户和密码:
create user user1 identified by 123456;

b.维护账户
给账户赋予权限:
grant connect to user1; [登录权限]
grant resource to user1; [资源操作权限]
grant dba to user1 ; [dba权限]
删除权限
revoke dba from user1;
revoke resource from user1;

c.删除账户
drop user user1;

14.二维表管理
a.二维表操作
二维表创建: create table student(
sno number(10),
sname varchar2(100),
sage number(3),
ssex char(4),
sbirth date
)
添加新的字段：alter table student add sphone number(11);
修改原有字段:
修改字段类型：alter table student modify sphone varchar2(11);
修改字段名字：alter table student rename column sphone to phone;
删除字段：alter table student drop column phone;
修改表名: rename student to student2;
删除表: drop table student;

b.数据类型
number类型: number(a,b)、number(a)
varchar2类型: varchar2(ln) 【ln表示：字符的最大长度】
char类型：char(ln)【ln表示：不管字符数据长度多大，直接开辟ln大小的空间存储数据】
date类型： date

c.主键，非空唯一
直接在字段后使用: primary key ;
在创建表的语句最后字段后面加: constraints pk_student_sno primary key(sno);
在创建表后使用: alter table student add constraints pk_student_sno primary key(sno);
删除主键：alter table student drop constraints pk_student_sno;

d.非空约束
直接表的字段后加: not null
在表后:constraints ck_student_sname check(sname is not null);
在创建表后使用: alter table student add constraints ck_student_sname check(sname is not null);
删除非空约束：alter table student drop constraints ck_student_sname;

e.检查约束
----年龄检查约束---------------------------
直接表的字段后加：check(sage<150 and sage>0);
在表后:constraints ck_student_sage check(sage<150 and sage>0);
在创建表后使用: alter table student add constraints ck_student_sage check(sage<150 and sage>0);
删除非空约束：alter table student drop constraints ck_student_sage;
----性别检查约束---------------------------
chenk(ssex=‘男’ or ssex=‘女’)

f.使用唯一约束
----qq检查约束---------------------------
在表字段后: unique
在创建表的语句后面使用: constraints un_student_sqq unique(sqq);
在创建表后使用: alter table student add constraints un_student_sqq unique(sqq);
删除非空约束：alter table student drop constraints un_student_sqq;

g.二维表外键约束
在表字段后: reference 表名(字段); 例如:reference clazz(sno);
在创建表的语句后面使用:constraints fk_student_cid foreign key(cid) reference clazz(sno);
在创建表后使用: alter table student add constraints fk_student_cid foreign key(cid) reference clazz(sno);
删除外键约束：alter table student drop constraints fk_student_cid;
-----外键缺点及优点-------
外键缺点：
无法直接删除父表数据，除非级联删除。
alter table student add constraints fk_student_cid foreign key(cid) reference clazz(sno) on delete cascade ;
无法直接删除父表数据，除非级联删除,无法保留历史数据。
alter table student add constraints fk_student_cid foreign key(cid) reference clazz(sno) on delete set null ;

15.序列、索引、视图
a.序列：可以作为主键使用。
创建序列：create sequence cc; [默认是没有值]
查询序列值：select cc.currval from dual;
序列指向一个值： select cc.nextval from dual;
创建自定义序列:
create sequence aa
start with 5
maxvalue 10000
cache 10
increment by 2;
删除序列：drop sequence aa;

b.索引：可以提升查询效率。
注意: oracle会自动把表的主键创建索引。
创建索引：create index 索引名 on 表名(字段名);
删除索引：drop index 索引名 ;

c.视图：作用是隐藏真实表，保护核心数据。
注意:
视图可以增删改查操作，并会影响真实表数据。
需要dba权限才可以创建视图。
创建视图: create view stu as select sno,sname,sage from student;
创建视图只能读: create view stu as select sno,sname,sage from student with read only;
删除视图: drop view stu;

16.oracle分页查询
rownum: 行号，rownum只能做<=判断。
每页大小：pageSize 当前页： currentPage 总页数： count
③select * from (
②select rownum r,t1.* from (
① select * from emp
) t1 where rownum <= pageSizecurrentPage
)t2 where t2.r > pageSize(currentPage-1);