–创建表空间 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’–不触发
学习来源与网络视频 纯手打 感谢支持
