oracle基础01

oracle

创建表空间 ： create tablespace 表空间名

存放数据文件： datafile '文件路径.dbf' size 初始存储大小m autoextend on(自动增长) next 需要扩展的大小m；

删除表空间: drop tablespace 表空间名

创建用户: create user 用户名 identified by 密码 (default tablespace 表空间名): 出生的表空间;

给用户授权: oracle数据库中常用角色

1. connect--连接角色,基本角色

2. resource--开发者角色

3. dba--超级管理员角色

例:给用户授予dba角色： grant dba to 用户名;

oreacle数据类型

No	数据类型	描述
1	Varchar,varchar2	表示一个字符串
2	NUMBER	NUMBER(n)表示一个整数,长度是n，NUMBER(m,n)：表示一个小数,总长度是m，小数是n，整数是m-n
3	DATA	表示日期类型
4	CLOB	大对象,表示大文本数据类型,可存4G
5	BLOB	大对象,表示二进制数据,可存4G

表的管理

1.1建表

语法: create table 表名(字段1 数据类型 [default 默认值],字段2 数据类型 [default 默认值] ...)

创建一个表：

create table 表名(
​
        字段名 number(20),
        字段名 varchar2(10)
);

1.2修改表结构

添加一列:

alter table 表名 add (
​
    字段名 number(1)
​
);
​

修改列类型:

alter table 表名 modify 字段名 char(1);

修改列名称:

alter table 表名 rename column 原字段名 to 新字段名;

删除一列:

alter table 表名 drop column 字段名;

查询表中记录:

 select * from 表名

添加一条记录:

insert into 表名 (字段名,字段名) values(1,'小明'); 
​
commit;  //不提交事务是脏数据，需要手动提交事务

修改一条记录：

update 表名 set 字段名 = '小马' where 字段名 = '1'';
​
commit;

三个删除:

1. 删除表中全部记录: delete from 表名

2. 删除表结构: drop table 表名

3. 先删除表,再次创建表。效果等同于删除表中全部记录.在数据量大的情况下,尤其在表中带有索引的情况下,该操作效率高,索引可以提供查询效率,但是会影响增删改效率

   truncate table 表名

 

序列

1. 在oracle中完成自动增长的功能，只能依靠序列完成

2. 序列不属于任何一张表,但是可以逻辑和表做绑定

3. 序列: 默认从1开始,依次递增,主要用来给主键赋值使用

4. dual: 虚表,只是为了补全语法,没有任何意义

语法: create sequence 序列名

在序列中提供了两种操作:

1. nextval: 取得序列的下一个内容

2. currval: 取得序列的当前内容

3. 第一次执行序列化时，需先执行nextval，否则报错

Scott用户，密码tiger

解锁Scott用户

    alter user scott account unlock;

解锁Scott密码,也可以重置密码

    alter user scott identifed by tiger;

单行函数: 作用于一行，返回一个值

字符函数
    select upper('yes') from dual;--YES
    select lower('YES') from dual;--yes
数值函数
    select round(12.12,2) from dual;--四舍五入,后面的参数表示保留的位数
    select rtunc(12.12,-1) from dual;--直接截取,不再看后面位数的数字是否大于5.
日期函数
以scott用户的emp表为例
查询出emp表中的所有员工入职距离现在几天
    select sysdate-e.hiredate from emp e;
算出明天此刻
    select sysdate+1 from dual;
查询出emp表中所有员工日志距离现在几月
    select months between(sysdate,e.hiredate) from emp e;
查询出emp表中所有员工入职距离现在几年
    select months between(sysdate,e.hiredae)/12 from emp e;
查询出emp表中所有员工入职距离现在几周
    select round((sysdate-e.hiredate)/7) from emp e;
转换函数
日期转字符串
    select to_char(sysdate,'fm yyyy-mm-dd hh24:mi:ss') from dual;
字符串转日期
    select to_date('2020-12-17 10:00:00', 'fm yyyy-mm-dd hh24:mi:ss') from dual;
通用函数
算出emp表中所有员工的年薪
奖金里面有null值,如果null值和任意数字做算术运算,结果都是null
    select e.sal*12+nvl(e.comm,0) from emp e;
条件表达式
条件表达式的通用写法,mysql和Oracle通用
给emp表中员工起中文名
    select e.ename,
        case e.ename
            when 'SMITH' then '张三'
                when 'ALLEN' then '李四'
                    when 'WARD' then '王五'
                        else '李六'
                            end
    from emp e;
判断emp表中员工工资,如果高于3000显示高收入,如果高于1500低于3000显示显示中等收入，其余显示低收入
    select e.sal,
        case
            when e.sal > 3000 then '高收入'
                when e.sal > 1500 then '中等收入'
                    else '低收入'
                        end
    from emp e;
oracle中除了起别名，都用单引号
oracle专用条件表达式
    select e.ename,
        decode(e.ename,
            'SMITH', '张三',
                'ALLEN', '李四',
                    'WARD', '王五',
                        '李四' ) "中文名"
    from emp e;

多行函数[聚合函数]:作用于多行,返回一个值

    select count(1) from emp;--查询总数量
​
    select sun(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 e.deptno,avg(e.sal)
    from emp e
    group by e.deptno;
查询出平均工资高于2000的部门信息
    select e.deptno,avg(e.sal) asal
    from emp e
    group by e.deptno
    having avg(e.sal)>2000;
所有条件都不能使用别名来判断
比如下面的条件语句也不能使用别名当条件
    select ename,sal s from emp where sal > 1500;
查询出每个部门工资高于800的员工的平均工资
    select e.deptno,avg(e.sal) asal
    from emp e
    where e.sal > 800
    group by e.deptno;
where是过滤分组前的数据，having是过滤分组后的数据
表现形式：where必须在group by 之前,having是在group by之后.
查询出每个部门工资高于800的员工的平均工资
然后再查询出平均工资高于2000的部门
    select e.deptno,avg(e.sal) asal
    from emp e
    where e.sal > 800
    having avg(e.sal) > 2000;
多表查询中的一些概念
笛卡尔积
    select * 
    from emp e,dept d;
等值连接
    select * 
    from emp e,dept d
    where e.deptno=d.deptno;
内连接
    select * 
    from emp e inner 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;
oracle中专用外连接
    select * 
    from emp e,dept d
    where e.deptno(+) = d.deptno;
查询表中所有数据
    select* from emp;
查询出员工姓名,员工领导姓名
自连接:自连接其实就是站在不同的角度把一张表看成多张表
    select e1.ename,e2.ename
    from emp e1,emp e2
    where e1.mgr = e2.empno;
查询出员工姓名,员工部门名称,员工领导姓名,员工领导部门名称
    select e1.ename,d1.dname,e2.ename,d2.dname
    form emp e1,emp e2,dept d1,dept d2
    where e1.mgr = e2.empno
    and e1.deptno = d1.deptno
    and e2.deptno = d2.deptno
子查询
子查询返回一个值
查询出工资和scott一样的员工信息
    select * from emp where sal in
     (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) msal
    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
    and t.msal = e.sal
    and e.deptno = d.deptno;

oracle中的分页

rownum行号:当我们做select操作的时候
每查询出一行记录,就会在该行上加上一个行号,
行号从1开始,依次递增,不能跳着走
排序操作会影响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, tt.* from(
            select * from emp order by sal desc
            ) tt where rownum < 11
    )where rn > 5