oracle入门(一)

怼烎@ 2022-01-05 12:45 266阅读 0赞

### 一、体系结构 1. 数据库 ： 只有一个数据库 2. 实例 : 后台运行的一个进程 3. 表空间: 逻辑存储单位 4. 数据文件: 物理存储单位 5. 用户:面向用户管理，由用户来管理表空间,用户向表空间中添加数据，最终存储到数据文件中 用户：sys 超级管理员 system:普通的管理员 ``` ### 二、表空间 ``` 1. 创建表空间的语法 create tablespace 表空间名称 datafile '路径/xxx.dbf' size 200M autoextend on 开启自动扩展 / off next 30M; 每次的扩展量 2. demo: 　　　　create tablespace 666 datafile 'c:/666' size 200M autoextend on next 30M; 3. 删除的语法 drop tablespace 表空间名称; -- 只能删除逻辑概念 drop tablespace 表空间名称 including contents and datafiles; -- 逻辑,物理同时删除 ``` ### 三、用户 ``` 1. 创建用户 create user 用户名 identified by 密码 default tablespace 表空间名称; 2. demo create user rose identified by rose default tablespace 666; 3. 权限 connect ：基本权限：create session resource :开发人员权限 dba：管理 select * from session_privs;--查询当前用户的所有权限 4. 赋予权限 grant 权限列表 to 用户列表; 5. 回收权限 revoke 权限列表 from 用户列表; ``` ### 四、表的管理 ``` 1. 数据类型 字符串 char（4000）: 定长字符串 , char(10) , 取出的时候,不顾的用空格补齐 varchar：变长字符串 varchar(10) varchar2()：变长字符串, 向后兼容 long : 最大存储量：2G ，使用clob取代 日期 date:mysql 中的datetime timestamp: 时间戳类型, 精确到秒后9位 数值 number(11) : 整数 number(m,n) : 浮点数:m ：总的位数 n： 小数点后的位数 555.55 大数据类型 clob: 字符大数据类型, 最大存储量 4G blob：字节大数据类型, 2. 创建表 create table 表（ 字段名 类型 约束, .... ） 3. 修改表 添加列 alter table 表名 add 列名 类型 约束; alter table customer add address varchar2(200); 删除列 alter table 表名 drop column 列名; alter table customer drop column address; 修改列属性 alter table 表名 modify 列名 类型 约束; alter table customer modify custName varchar2(400); 重命名列名 alter table 表名 rename column 原列名 to 新列名; alter table customer rename column custName to cname; 4. 约束 主键，外键，非空，默认，唯一，检查 create table customer( custId number primary key , custName varchar2(200) not null unique, age number default 18 check(age between 1 and 120), sex char(2) check(sex in ('男','女')) ) 5. 事务 特性： 原子性，隔离性，一致性，持久性 隔离级别： read uncommited read commited repeatable read serializable mysql： 支持四个隔离级别， 默认的是 repeatable read oracle: 支持的read commited， serializable， read only,默认的是：read commited 6. sql语句的分类 DCL: 数据控制语言 ：grant ， revoke DQL：数据查询语言 ：select DDL：数据定义语言 : create drop , alter DML: 数据操作语言： insert update ,delete 7. DML语句 insert into 表名 values(值,....) insert into 表(字段,...) values(值,....) update 表 set 字段=值, 字段=值,... where 条件 delete from 表 where 条件; truncate table 表; -- 删除表中的所有数据, 效率极高, 8. 序列 最常用语法 create sequence 序列名; 属性： nextval : 下一个值 currval： 当前值: 必须先执行一次nextval，才能获取当前值 dual --虚表,为了完善语法结构 select c_seq.nextval from dual; select c_seq.currval from dual; 完整的语法 create sequence 序列名 start with 1 起始值 increment by 1 自增量 maxvalue 9999 /nomaxvalue /minvalue 1 /nominvalue cycle 循环 cache 20; ``` ### 五、scott下的表 ``` 1. soctt : oracle公司的第一位程序员, pointbase 2. 默认有一个用户scott ，密码是tiger 3. 解锁： alter user 用户名 account unlock; / lock alter user scott account unlock; 4. 重置密码： alter user 用户名 identified by 密码； alter user scott identified by tiger; 5. emp ,dept ,salgrade ``` ### 六、函数(单行，多行) ``` 1. 单行函数 一行记录返回一行结果 字符串函数 upper lower select * from emp where upper(ename) like upper('%Ia%'); 日期函数 两个日期相减 == 天 select sysdate - hiredate from emp 两个日期相减/7 == 周 select (sysdate - hiredate) /7 from emp 月差 select months_between(sysdate ,hiredate) from emp; 数值函数 round: 四舍五入 select round(2.777) from dual; trunc: 截取 select trunc(2.777, 1) from dual; mod : 模运算符 select mod(3 , 10) from dual; 通用函数 --在Oracle中空+任何数值=空 nvl(p1, p2) select sal * 12 + nvl(comm,0),sal , comm from emp; 转换函数 to_char(p1 ,p2) 将日期转换为字符串 p1 : 将要转换的日期 p2 : 格式 to_date(p1 ,p2) 将字符串转换为日期 p1 : 将要转换的字符串 p2 : 格式 insert into emp(empno ,hiredate) values(5000,to_date('2018-8-24','yyyy-mm-dd')) select sysdate,to_char(sysdate ,'yyyy-mm-dd hh24:mi:ss day') from dual; select '2018-08-24 11:16:45 friday' , to_date('2018-08-24 11:16:45 friday','yyyy-mm-dd hh24:mi:ss day') from dual; 2. 多行函数(聚合函数) 多行记录返回一行结果 select count(*) from emp; count ,sum ,avg ,max ,min ``` ### 七、分组举例 ``` 0. 分组后能查询的列 聚合函数, group by中出现列 1. 查询人数大于5的部门信息 select * from dept where deptno in (select deptno from emp group by deptno having count(1) > 5) --select中 deptno, count(1)不能颠倒 select t.deptno ,t.num,d.dname ,d.loc from dept d ,(select deptno,count(1) num from emp group by deptno) t where d.deptno = t.deptno and num > 5 select e.deptno ,count(1),d.dname,d.loc from emp e,dept d where e.deptno = d.deptno group by e.deptno ,d.dname,d.loc having count(1) > 5; ``` ### 八、多表查询 ``` 1. 内连接: 只能显示符合条件的记录 -- 查询员工的领导信息 select e.empno,e.ename ,m.empno ,m.ename from emp e,emp m where e.mgr = m.empno; -- 在上面的基础上,查询员工的部门名称 select e.empno,e.ename,d.dname ,m.empno ,m.ename from emp e,emp m ,dept d where e.mgr = m.empno and e.deptno = d.deptno; -- 在上面的基础上,查询领导的部门名称 select e.empno,e.ename,d1.dname ,m.empno ,m.ename ,d2.dname from emp e,emp m ,dept d1,dept d2 where e.mgr = m.empno and e.deptno = d1.deptno and m.deptno = d2.deptno; -- 在上面的基础上，查询领导和员工的工资等级 select e.empno,e.ename,d1.dname,s1.grade ,m.empno ,m.ename ,d2.dname,s2.grade from emp e,emp m ,dept d1,dept d2,salgrade s1 ,salgrade s2 where e.mgr = m.empno and e.deptno = d1.deptno and m.deptno = d2.deptno and e.sal between s1.losal and s1.hisal and m.sal between s2.losal and s2.hisal; 2. 外连接: 能显示基准表中所有的记录 左外连接： left join on 右外连接 : right join on 特殊外连接 : (+)：以对方表为基准表 demo：查询员工的领导信息（没有领导也要显示） select e.empno,e.ename ,m.empno ,m.ename from emp e left join emp m on e.mgr = m.empno; select e.empno,e.ename ,m.empno ,m.ename from emp m right join emp e on e.mgr = m.empno; select e.empno,e.ename ,m.empno ,m.ename from emp e,emp m where e.mgr = m.empno(+); ``` ### 九、子查询 ``` 1. 什么是子查询 一个查询语句包含另一个查询语句 外查询，内查询 2. demo -- 查询与7369工作的一致的员工 select job from emp where empno = 7698; select * from emp where job = (select job from emp where empno = 7698); -- 查询高于本部门平均薪水的员工 select e.ename ,e.sal ,e.deptno ,t.avgsal ,t.deptno from emp e,(select avg(sal) avgsal,deptno from emp group by deptno) t where e.deptno = t.deptno and e.sal > t.avgsal -- 查询存在员工的部门信息 -- 只要在员工出现过的部门编号 select * from dept where deptno in (select distinct deptno from emp); 3. 小结 返回一行一列： = > < != <> 返回多行多列： 作为表使用 返回一列多行： in =any =some ``` ### 十二、分页查询 ``` 1. rownum : 伪列, 行号，从1开始，依次递增 , 在加载时生成行号 2. select rownum ,e.* from emp e; -- 查询前三条记录 select rownum ,e.* from emp e where rownum <= 3 -- 查询4 - 6 条记录 select rownum ,e.* from emp e where rownum between 4 and 6; 先生成行号，作为表,再条件筛选 select t.* from (select rownum rn,e.* from emp e) t where t.rn between 4 and 6; -- 查询工资最高的前三名 nulls last nulls first 先排序，取前三名 select t.*,rownum from (select e.* from emp e order by sal desc)t where rownum <= 3; -- 查询工资较高的4-6名 select m.* from (select t.*,rownum rn from (select e.* from emp e order by sal desc)t) m where m.rn between 4 and 6; ```

转载于:https://www.cnblogs.com/itworkerlittlewrite/p/9535451.html