一、练习题

  1. --1.显示职员的就职年度
  2. --使用round()函数将入职日期四舍五入到年份
  3. --利用连接操作符将获得的年份和“年度”字符串做拼接
  4. select e.ename "姓名",
  5. to_char(round(e.hiredate,'yyyy'）,'yyyy')||'年度' "入职年度"
  6. from emp e;
  7. --2.列出至少有三个雇员的所有部门
  8. select deptno,count(deptno) from emp  group by deptno having count(1)>=3;
  9. --3.列出薪金比“SMITH ”多的所有雇员
  10. select * from emp;
  11. select * from emp e where e.sal>(select e1.sal from emp e1 where e1.ename='SMITH'); 
  12. --4.列出所有“CLERK ”（办事员）的姓名及其部门名称
  13. select e.ename , d.dname from emp e,dept d where e.deptno=d.deptno and e.job='CLERK';
  14. --5.列出各种工作类别的最低薪金，显示最低薪金大于1500 的记录
  15. select min(sal),job from emp group by job;
  16. select job,min(sal) from emp group by job having min(sal)>1500;
  18. --6.找出各月最后一天受雇的所有雇员
  19. select e.* from emp e where last_day(e.hiredate)=e.hiredate;
  21. --7.获得当月最后一天函数 last_day(hiredate)
  22. select last_day(e.hiredate) from emp;

二、表空间

1、创建表空间

2、查询有哪些表空间

3、调整表空间大小

(1)、直接更改
(2)、再添加表空间

4、查询数据文件

5、删除表空间

6、查询所有用户信息

  1. --权限不足，需要使用管理员来创建
  2. create tablespace ts_42
  3. datafile 'd:\file1\ts_42_datafile.dbf'
  4. size 5M;
  5. --查询有哪些表空间
  6. select * from dba_tablespaces;
  7. --调整表空间大小
  8. --1.直接更改文件大小
  9. alter database datafile 'd:\file1\ts_42_datafile.dbf'
  10. resize 6M;
  11. --2.向表空间内再添加数据文件
  12. alter tablespace ts_42 
  13. add datafile 'd:\file1\ts_42_datafile2.dbf'
  14. size 5M;
  15. --查询数据文件
  16. select * from dba_data_files;
  17. --删除表空间  先备份再删除
  18. --删除表空间并且删除内容和数据文件
  19. drop tablespace ts_42 including contents and datafiles;
  20. --查询系统所有用户信息
  21. select * from dba_users;

三、创建用户和授权

1、创建用户

2、修改用户密码

3、授权

4、赋予查看其它用户表的权限

5、撤销权限

  1. --创建用户
  2. create user user_42
  3. identified by 123456;
  4. --修改用户密码
  5. alter user user_42 
  6. identified by 123123;
  7. --新密码登录前给用户授权
  8. grant connect,resource to user_42;
  9. --赋予user_42 用户可以查看用户scott中的emp 表的权限
  10. grant select on scott.emp to user_42;
  11. --grant update on scott.emp to user_42;
  12. --登录user_42 用户，查看用户scott的emp 表
  13. select * from scott.emp;
  14. --给 user_42 撤销权限
  15. revoke select on scott.emp from user_42;
  16. select * from scott.emp;

四、练习，创建用户，默认表空间和授权

  1. --1.以Sys用户连接Orcl数据库
  2. --创建tp_orders表空间大小10M
  3. create tablespace tp_orders
  4. datafile 'd:\file1\tp_orders_datafile.dbf'
  5. size 10M;
  6. --2.创建 A_oe 用户的默认表空间为tp_orders，密码为bdqn
  7. create user A_oe
  8. default  tablespace tp_orders
  9. identified by bdqn;
  10. select * from dba_users;
  11. --3.授予connect、resource权限
  12. grant connect,resource to A_oe;
  13. --4.授予访问scott用户的emp表的权限
  14. grant select on scott.emp to A_oe;
  16. select * from scott.emp;

五、监测表空间使用情况

  1. --监测表空间使用情况
  2. SELECT a.tablespace_name "表空间",
  3. Round(a.bytes/(1024*1024*1024),3) "大小G",
  4. Round(b.bytes/(1024*1024*1024),3) "已使用G",
  5. Round(c.bytes/(1024*1024*1024),3) "剩余G",
  6. Round(b.bytes*100/a.bytes,3) "使用百分比%",
  7. Round(c.bytes*100/a.bytes,3) "剩余百分比%"
  8. FROM sys.Sm$ts_Avail a, sys.Sm$ts_Used b,sys.sm$ts_free c
  9. WHERE a.tablespace_name=b.tablespace_name AND a.tablespace_name=c.tablespace_name;

六、创建、访问、修改、删除、使用序列

  1. --创建序列  序号从10 开始，间隔为 2 最大值是30  不循环 缓存5 个
  2. create sequence seq1
  3. start with 10
  4. increment by 2
  5. maxvalue 30
  6. minvalue 10
  7. nocycle
  8. cache 5;
  9. --使用序列  next 生成下一个值
  10. select seq1.nextval from dual;
  11. --select seq1.currval from dual;
  12. create table toys(
  13.        id int ,
  14.        name varchar2(30) 
  15. );
  16. select * from toys;
  17. insert into toys(id,name) values(seq1.nextval,'A');
  18. insert into toys(id,name) values(seq1.nextval,'B');
  19. --删除序列
  20. --drop sequence seq1;
  21. --生成 32 为全局唯一标识
  22. select sys_guid() from dual;

七、全局唯一标识

1、oracle中—生成 32 为全局唯一标识 select sys_guid() from dual;
2、java中 public static void main(String[] args) {
System.out.println(UUID.randomUUID().toString().replaceAll(“-“, “”));

八、授权练习

  1. --1.以Sys用户连接Orcl数据库
  2. --创建tp_orders表空间大小10M
  3. create tablespace tp_orders
  4. datafile 'd:\file1\tp_orders_datafile.dbf'
  5. size 10M;
  6. --2.创建 A_oe 用户的默认表空间为tp_orders，密码为bdqn
  7. create user A_oe
  8. default  tablespace tp_orders
  9. identified by bdqn;
  10. select * from dba_users;
  11. --3.授予connect、resource权限
  12. grant connect,resource to A_oe;
  13. --4.授予访问scott用户的emp表的权限
  14. grant select on scott.emp to A_oe;
  16. select * from scott.emp;

九、同义词

两类：私有同义词，共有同义词
私有：只能在当前用户下使用，共有：任何用户均能访问该表
注意：使用同义词前，要获得同义词对应对象的访问权限
最好不要同名，一旦同名，数据库会先选择私有或者对象，再选择共有作为目标

  1. -- user_42 使用私有同义词
  2. select * from s_t;
  3. -- user_42 删除私有同义词
  4. drop synonym s_t;
  7. --user_42 创建共有同义词
  8. create public synonym s_toys for toys;
  9. --sys 授权
  10. grant create public synonym to user_42; 
  12. -- user_42 / scott 使用共有同义词 本用户和其他用户下都可以用
  13. select * from s_toys;
  15. --登录sys 给 scott授权查询 toys 表达的权限
  16. grant select on user_42.toys to scott;
  18. --user_42 登录，删除共有同义词
  19. drop public synonym s_toys;
  20. --sys 授权删除权限
  21. grant drop public synonym to user_42;

十、索引的创建和删除

  1. select * from toys;
  2. --user42 创建索引
  3. create index index_toys_id on toys (id);
  4. --user42 删除索引
  5. drop index index_toys_id;

索引的作用：唯一性和提高查询速度
反向键索引：单个的索引才能键，减少I/O瓶颈的发生并提高查询速度
位图索引：适用于低基数列，值有限的列。
image.png
重建索引：
什么时候应该重建？？
1。用户表被移动到新的表空间
2。索引中包含很多已删除的项
3。需要将现有的正常索引转换成反向索引。

十一、表分区

表分区的优势：
1。改善表的查询性能
2。表更容易管理
3。便于备份和恢复
4。提高数据安全性

  1. drop table sales1;
  2. --1、范围分区  自己建P1 P2 P3 P4 分区
  3. create table sales1(
  4.        sales_id int,
  5.        sales_date date
  6.  )
  7.  --指定分区
  8.  partition by range(sales_date)
  9.  (
  10.       partition p1 values less than(to_date('2018-12-31','yyyy-mm-dd')),
  11.       partition p2 values less than(to_date('2019-12-31','yyyy-mm-dd')),
  12.       partition p3 values less than(to_date('2020-12-31','yyyy-mm-dd')),
  13.       partition p4 values less than(to_date('2021-12-31','yyyy-mm-dd')), 
  14.       partition p5 values less than(maxvalue)
  15.  );
  16.  --插入数据
  17.  insert into sales1( sales_id , sales_date) values(1,to_date('2021-04-01','yyyy-mm-dd'));
  18.  insert into sales1( sales_id , sales_date) values(2,to_date('2020-04-01','yyyy-mm-dd'));
  19.  insert into sales1( sales_id , sales_date) values(3,to_date('2019-04-01','yyyy-mm-dd'));
  20.  insert into sales1( sales_id , sales_date) values(4,to_date('2018-04-01','yyyy-mm-dd'));
  21.  select * from sales1;
  23.  --※使用分区表 查询的是2020 整个年度的数据
  24.  select * from sales1 partition(p3);
  27.  --2 、间隔分区  应用场景应用更广泛（系统帮着建P1 P2 P3 P4 分区）
  28.  create table sales2(
  29.         sales_id int,
  30.         sales_date date
  31.  )
  32.  partition by range(sales_date)
  33.  --每十二个月划分一次
  34.  interval(numtoyminterval(12,'month')) 
  35.  (   --从2018 年1月1号开始
  36.      partition p1 values less than(to_date('2018-01-01','yyyy-mm-dd'))
  37.  );
  38.  --查看此时分区情况
  39.  select * from user_tab_partitions where table_name='SALES2';
  41.  insert into sales2( sales_id , sales_date) values(1,to_date('2021-04-01','yyyy-mm-dd'));
  42.  insert into sales2( sales_id , sales_date) values(2,to_date('2020-04-01','yyyy-mm-dd'));
  43.  insert into sales2( sales_id , sales_date) values(3,to_date('2019-04-01','yyyy-mm-dd'));
  44.  insert into sales2( sales_id , sales_date) values(4,to_date('2018-04-01','yyyy-mm-dd'));
  46.  --使用间隔分区查询数据
  47.  select * from sales2 partition(SYS_P23);

十二、视图

  1. --scott 创建视图
  2. create view view_emp_dept as select e.*,d.dname  from emp e, dept d
  3. where e.deptno=d.deptno;
  5. --删除视图
  6. drop view view_emp_dept;
  8. --sys授权
  9. grant create view to scott;
  11. --使用视图
  12. select * from view_emp_dept;

十三、综合练习

  1. --1.找出奖金高于薪金的50%的员工姓名。
  2. select e.ename from emp e where e.sal*0.5<e.comm;
  3. --2.查询没有奖金的员工姓名。
  4. select e.ename from emp e where e.comm is null or e.comm=0;
  5. --3.查询第二个字母为A的员工姓名
  6. select e.ename from emp e where e.ename like '_A%';
  7. --4.查询部门平均工资大于2000 的部门名称和平均工资。
  8. --第一种
  9. select d.dname,avg(e.sal) from emp e, dept d where e.deptno=d.deptno  group by d.dname having avg(e.sal)>2000;
  10. --第二种
  11. select * from dept d join(
  12. select e.deptno de,avg(e.sal)
  13. from emp e group by e.deptno having avg(e.sal)>2000) d1
  14. on d.deptno=d1.de;
  17. select * from emp;