Oracle快速入门基础练习语句sql

–创建表空间
create tablespace itheima
datafile ‘c:\itheima.dbf’
size 100m
autoextend on
next 10m;
–删除表空间
drop tablespace itheima;
–创建用户
create user itheima
identified by itheima
default tablespace itheima;
–给用户授权 dba角色
grant dba to itheima;

–切换到

–一个person表
create table person(
pid number(20),
pname varchar2(10)

);
–修改表结构
–添加一列
alter table person add gender number(1);
–修改列类型
alter table person modify gender char(1);
–修改列名称
alter table person rename column gender to sex;
–删除列
alter table person drop column sex;

—数据增删改 添加一条记录
insert into person (pid,pname) values(1,‘小明’);
commit;
–修改一条数据
update person set pname=‘小马’ where pid=1;
commit;
–三个删除
–删除表中全部记录
delete from person;
–删除表结构
drop table person;
–删除表在创建表 等于删除全部记录
–在数据量大，在带索引的时候，这样效率高 这个方式先删除索引 避开索引影响
–索引提高查询效率 但会影响增删改效率
truncate table person;

–序列不真的属于任何一张表，但是可以逻辑和表做绑定
–序列：默认从1开始，依次递增，给主键赋值
–dual是个续表 ，为了补全语法，没有意义
create sequence s_person;
–添加下一个
select s_person.nextval from dual;
–查看当前
select s_person.currval from dual;
–添加数据标准写法 用序列
insert into person (pid,pname) values(s_person.nextval,‘小明’);
commit;

–scott用户 密码默认tiger
–解锁
alter user scott account unlock ;
–解锁密码
alter user scott identified by tiger;
–切换scott 会话 登出 输入密码

–单行函数：作用于一行，返回一个值
–字符函数
select upper(‘yes’)from dual;–小写变大写
select lower(‘YES’)from dual;–大变小

–数值函数
select round(26.16,1) from dual;–四舍五入 参数标识保留的位数
select trunc（56.16，-1）from dual;–直接截取，不看后面位数是否大于5
select mod(10,3)from dual;–求余

–日期函数
–查询emp表中所有员工入职距离到现在几天
select sysdate-e.hiredate from emp e;–用系统时间-（减去）e.hireadate字段
–算出明天的此时此刻
select sysdate+1 from dual;
–查询emp表中所有员工入职距离到现在几月
select months_between(sysdate,hiredate)from emp;
–查询emp表中所有员工入职距离到现在几年
select months_between(sysdate,hiredate)/12 from emp;
–查询emp表中所有员工入职距离到现在几周
select round(（sysdate-e.hiredate）/7 )from emp e;–为了准确用天/7 并且四舍五入

–转换函数
select to_char(sysdate,‘fm yyyy-mm-dd hh24:mi:ss’) from dual;–日期转字符串
select to_date(’ 2019-10-30 14:10:2’,‘fm yyyy-mm-dd hh24:mi:ss’) from dual;–字符串转日期

–通用函数
–算出emp表中所有员工的年薪
select sal*12+nvl(comm,0)from emp;–排除null nvl（） null运算任何数都是null 如果是null值则调用后面的数

–条件表达式(通用版)
–给emp表中员工起中文名（等值判断）
select ename,
case ename
when ‘SMITH’ then ‘哈哈’
when ‘ALLEN’ then ‘嘻嘻’
else ‘无名’
end
from emp;

–判断emp表中员工工资，如果高于3000显示高收入，低于1500-3000显示中等收入，其余低收入（范围判断）
select sal,
(case
when sal>3000 then ‘高收入’
when sal>1500 then ‘中等输入’
else ‘低收入’
end) 评级
from emp;

–多行函数（聚合函数）：作用于多行，返回一个值
select count(1) from emp;–查询总行数 *是所有 1代表主键
select sum(sal) from emp;–工资总和
select max(sal) from emp;–最大
select min(sal) from emp;–最低
select avg(sal) from emp;–平均

–分组查询（查询每个什么，就是用什么分组）
–查询出每个部门的平均工资
–分组查询中，出现在group by后面的原始列，才能出现在select后面
–没有出现在group by后面的列，想在select后面，必须加上聚合函数
–聚合函数有一个特性，可以办多行记录变成一个值
select deptno,avg(sal)
from emp
group by deptno
–查询平均工资高于2000的部门
select deptno,avg(sal)
from emp
group by deptno
having avg(sal)>2000;
–查询出每个部门工资高于800的员工的平均工资
select deptno,avg(sal)
from emp
where sal>800
group by deptno
–查询出每个部门工资高于800的员工的平均工资,在查出平均工资高于2000的部门
select deptno,avg(sal)
from emp
where sal>800
group by deptno
having avg(sal)>2000;

–多表查询
–笛卡尔积解决办法
select * from emp e,dept d;
–需要等值连接
select *from emp e,dept d
where e.deptno=d.deptno;
–内连接
select *from emp e join dept d
on e.deptno=d.deptno;
–查询出所有部门，以及部门下的员工信息(外连接)
select * from emp e right join dept d
on e.deptno=d.deptno;
–查询所有员工信息，以及员工所属部门
select * from emp e left join dept d
on e.deptno=d.deptno;
–orcale专业左连接
select *from emp e,dept d
where e.deptno（+）=d.deptno;
–查询出员工姓名，员工领导姓名 自连接（站在不同的角度把一张表看成多张表）
select e1.ename,e2.ename
from emp e1，emp e2
where e1.mgr=e2.empno
–查询出员工姓名和部门名称，员工领导姓名和部门名称
select e1.ename,d1.deptno,e2.ename,d2.deptno
from emp e1，emp e2,dept d1,dept d2
where e1.mgr=e2.empno and e1.deptno=d1.deptno and e2.deptno=d2.deptno;

–子查询
–子查询返回一个值
–查询出员工工资和SCOTT一样的员工信息 =的隐患就是如果有null值 就会报错 保险用in
SELECT*FROM emp where sal=(
select sal from emp where ename=‘SCOTT’)
–子查询返回一个集合
–查询出工资和10号部门任意员工一样的员工信息
select * from emp where sal in(
select sal from emp where deptno=10);
–子查询返回一张表
–查询出每个部门最低工资，和最低工资员工姓名，和该员工所在部门名称
–1，先查询出每个部门最低工资 在没有表有这些字段的时候 要创造出这个表
select deptno,min(sal) masl
from emp
group by deptno
–2,三表联查，得到最终结果
select t.deptno,t.msal,e.ename,d.dname
from (select deptno,min(sal) msal
from emp
group by deptno)t,emp e,dept d
where t.deptno=e.deptno–t确认部门e
and t.deptno=e.deptno–t确认最低工资e
and t.msal=e.sal–t确认最低工资员工e
and e.deptno=d.deptno;–确认部门名称

–oracle中的分页
–rownum行号：当我们做select操作的时候，
–每查询出一行记录，就会在该行上加上一个行号
–行号从1开始，依次递增，不能跳着走
----emp表工资倒叙排列后，每页五条记录，查询第二页
–排序操作会影响rownum的顺序
select rownum，e. * from emp e order by e.sal desc
–如果涉及到排序，但是还要使用rownum的话，我们可以再次嵌套查询
select rownum，t.* from（
select rownum，e. * from emp e order by e.sal desc）t;
–emp表工资倒叙排列后，每页五条记录，查询第二页
–rownum行号不能写上大于一个整数
select* from
(select rownum rn,e.from–设置本轮查询的行号为rn 方便调用
(selectfrom emp order by sal desc)e–1.排序 分页查询的固定格式是这样的 只改这行 其他不变
where rownum<11)–2.取到小于11的行号
where rn>5;–使用子查询中的查询好的行号做选择 避免了无法错误 行号顺序要从1开始

–视图：视图就是提供一个查询的窗口，所有数据来自于原表
–查询语句创建表
create table emp as select * from scott.emp;
–创建视图（必须要有dba权限）
create view v_emp as select ename,job from emp with read only;
–查询视图
select * from v_emp
–修改视图(不建议修改，会修改原表)
update v_emp set job=‘CLERK’ where ename=‘ALLEN’;
commit;
–视图的作用
–1.可以屏蔽掉一些敏感字段 2.保证本部和分布数据统一 本部用数据，分部用视图

–索引：索引就是在表的列上构建一个二叉树，达到大幅度提高查询效率的目的，但是索引会影响增删改的效率
–二叉树 就像给数据加上个目录 查询更快 但增删改的时候都要修改这个目录 所以慢
–单列索引 触发必须 必须是索引列中的原始值，单行函数，模糊查询，都有影响
create index idx_ename on emp(ename);
–复合索引
create index idx_enamejob on emp(ename,job);
select * from emp where ename=‘SCOTT’–触发单列索引
select * from emp where ename='SCOTT’and job=‘ANALYST’–复合
select * from emp where ename='SCOTT’or job=‘ANALYST’–不触发

学习来源与网络视频 纯手打 感谢支持