Oracle基本操作

         Oracle Database，指数据库管理系统。Oracle数据库管理系统是管理数据库访问的计算机软件。它由Oracle数据库和Oracle实例（instance）构成（区分mysql，mysql没有实例的概念）。在任何时刻，一个实例只能与一个数据库关联，访问一个数据库；而同一个数据库可由多个实例访问（RAC）。

   一、创建用户和表空间

 sqlplis /nologl         //启动sqlplus不登录

conn sys/root as sysdba;        //通过超级管理员以dba的身份登录

create tablespace 表空间名 datafile '文件路径' size 空间大小;        //创建表空间

create user 用户名 identified by 密码 default tablespace 表空间;       //创建用户并指定表空间

grant dba to 用户         /给用户授予dba的权限

• 以超级管理员身份登录并查看实例

conn sys/root as sysdba;

select instance_name from v$instance;

• 创建表空间

create tablespace scott_tb_space datafile 'c:/tbspace/scott_tb_space.dbf' size 200m;

• 创建用户并授权退出

create user scott identified by tiger default tablespace scott_tb_space;

grant dba to scott;

exit;

• 授权用户连接测试

conn scott/tiger@xe

  二、约束

• 主键约束（PRIMARY KEY）

        唯一且不为空，主键所在列必须具有索引（主键的唯一约束通过索引实现），如果不存在，将会在索引添加的时候自动创建。

• 唯一约束（UNIQUE）

        唯一约束可在单列或者多列上，对于这些列或组合，唯一约束保证每一行的唯一性。UNIQUE允许null值，UNIQUE的列可存在多个null。

• 非空约束（NOT NULL）

        顾名思义，所约束的列不能为NULL值。否则就会报错 

• 外键约束（FORENIGN KEY）

        用来维护从表（Child Table）和主表（Parent Table）之间的引用完整性. 外键约束是个有争议性的约束，它一方面能够维护数据库的数据一致性，数据的完整性。防止错误的垃圾数据入库； 另外一方面它会增加表插入、更新等SQL性能的额外开销，不少系统里面通过业务逻辑控制来取消外键约束。例如在数据仓库中，就推荐禁用外键约束。

• 检查约束（CHECK）

        表中每行都要满足该约束条件。条件约束既可以在表一级定义也可以在列一级定义。在一列上可以定义任意多个条件约束。

  三、select语法

 3.1、简单查询

• 查询部门信息

select * from dept;

• 查询所有部门的部门编号和部门名称

select deptno,dname from dept;

 3.2、去重（distinct）、别名（as）、排序（order by）

• 查询存在员工的部门编号

select deptno from emp;

• 去重

select distinct deptno from emp;

• 查询所有的员工名称、员工所在的部门编号

select distince ename, deptno from emp;

• 查询员工姓名，工资

select ename 姓名, sal 工资 from emp;

select ename as 姓名, sal  as 工资 from emp;

• 查询所有员工的姓名、工资，按照工资的降序排序（默认升序）

select ename as 姓名, sal  as 工资 from emp order by sal desc; 

• 查询所有员工的姓名、工资，员工所在部门，按照部门编号的升序排序，同部门按照工资降序排序

select ename , sal ,deptno from emp order by deptno asc ,sal desc; 

 3.3、伪列表达式和Null处理

• 查询员工的姓名、工资、提成、月收入、工资+提成

select * from emp;

select ename,sal,comm,sal+comm 月收入 from emp;   //comm为nulll的话查询不出月收入

select ename,sal,comm,sal+nvl(comm,0) 月收入 from emp;   //如果comm为空，则给0

• nulls first  /nulls last把空的放前面或者后面

select * from emp order by sal;

select * from emp order by comm desc; （comm部分为空）

select * from emp order by comm desc nulls first;

select * from emp order by comm desc nulls last;

• 字符串拼接（||）

select ename ,ename || 'a' 别名 from emp;

-- null

select ename,comm,ename ||comm test from emp;

 3.4、虚表使用（dual）:可以增删查改，但不要drop，里面只有一条记录，也可以提取系统变量

//计算999*666

select 999*666 from dual;

 3.5、语法顺序

--1、from

--2、select

--3、order by

--查询员工的姓名、员工工资 别名为 工资 并且将结果集安装工资降序排序

select ename,sal 工资 from emp order by 工资 desc;

  四、条件查询

--条件查询结构

select 查询内容 from 数据来源 where 行记录条件

--查询我们班所有男同学的信息

select 同学的信息 from 学生信息表 where 性别 = '男'

--解析过程

--学生信息表、判断是否为男、根据判断结果，将指定的记录放在select中

• 查询名称'guan' 员工的信息

select * from emp where ename = 'guan'

• 查询非10部门的员工姓名和所属部门编号（!=、<>，^=）

select ename, deptno from emp where deptno !=10

• 查询所有的工资在2000以上的员工信息

select * from emp where sal>2000

• 查询在1和2部门工作 in（1，2）的员工信息

select * from emp where depth in（1,2）

• 查询工资在2000到3000的员工信息

select * from emp where sal between 200 and 3000

• 条件连接运算（and,or,not）

--查询工资大于1500，并且在20部门的员工信息

select * from emp where sal > 1500 and deptno=20;

--查询工资大于1500，或者在20部门的员工信息

select * from emp where sal > 1500 or deptno=20;

--查询工资在2000到3000之间的员工信息

select * from emp where sal  between 2000 and  3000;

select * from emp where sal >= 2000 and sal <= 3000;

--查询不在20部门的员工信息

select * from emp where not deptno = 20;

select * from emp where deptno != 20;

• null运算和nvl函数使用

查询出所有可能获得奖金的员工信息

select * from emp where comm is not null;

select * from emp where not  comm is nul;

--nvl        当某个值为空时给出一个特定值，否则该值就为他自己

select * from emp where nvl  comm <=0; //如果comm为空则只查出comm<=0的

select * from emp where nvl（comm,0） <=0; //可以查出为null的值

• 模糊查询like

--查询名称当中包含有'G'的员工信息

select * from emp where ename like '%G%'

--查询名称当中首字母G'的员工信息

select * from emp where ename like 'G%'

--查询名称当中第二个字母'G'的员工信息

select * from emp where ename like '_G%'

--查询员工名称包含‘%’的员工信息

select * from emp where ename like '%a%%' escape（'a'）

--查询员工名称包含‘a%’的员工信息

select * from emp where ename like '%aaa%%' escape（'a'）

--查询员工名称包含‘%’ , '_' 的员工信息

select * from emp where ename like '%a%%a_%' escape（'a'）

• where条件子查询

--查询销售部（SALES）的员工信息

select * from emp where deptno = (select deptno from deptno where ename = ‘SALES’)；

--查询工资等级为2的员工信息

select * from emp where sal between (select losal from salgrade where grade = 2) and

                                                             (select hisal from salgrade where grade = 2);

•   分组（group by）与having

--求出每个部门的平均工资

select deptno, avg(val) from emp group by deptno;

--求出每个部门的员工数

select deptno, count(*) from emp group by deptno;

--查看平均工资大于2000的部门的部门编号和平均工资

select deptno ,avg(sal) from emp group by deptno having age(sal)>2000;

--查询部门编号和不部门里面的员工数量，只统计工资大于200并且工资大于200的员工数量在2以上的部门（where行过滤，group by 组过滤）

select deptno , count（*） from emp where sal>200 group by deptno having count(*)>=2;

  五、函数

       1、单行 函数：对应在表记录时，一条记录返回一个结果。例如lower(x)，将参数转换成小写

        2、多行函数：也称组函数或者聚合函数，此类函数可同时对多条记录进行操作并返回一个结果（重点）。例如max(x)求最大值。

 5.1 、常用的单行函数

• 字符函数

1、将字段名转换为小写，查询员工表姓名 lower

select lower(ename) from emp;

2、将字段名转换为大写，查询job为manager的员工 upper

select * from emp where job = upper('manager');

3、substr 方法参数('被截取的字符串'，'从哪一位开始截取'，'截取的位数');

查询姓名以M开头的所有员工 substr

select * from emp where substr(ename,1,1)='M';

4、返回字段长度 length

select length(ename) from emp;

5、去除首尾空格，但是不能去除中间空格  trim   ltrim   rtrim   

select * from emp where job = trim('  MANAGER ');

6、round  四舍五入

select round(1234567.44687, 2) from dual;

6、concat()字符串连接

select ename || job namejob from emp;

select concat(ename,job) namejob from emp;

• 时间函数

--获取当前系统函数

select sysdate from dual;

select current_date from dual;

--获取当前后一天系统函数

select sysdate+1 from dual;

--查询所有员工的转正日期，3个月的试用期,add_months(hiredate,3)

select empno,ename,hiredate,add_months(hiredate,3) 转正日期 from emp;

--查看当前这个月的最后一天

select last_day(sysdate) from dual;

--查询每个员工工作到现在为止，一共上了几个月的班,moths_between(sysdate,hiredate)

select sysdate, hiredate , moths_between(sysdate,hiredate) from emp;

--获取当前时间点的下一个星期一是什么时候

select next_day(sysdate,'星期一') from dual;

--查看每个员工的上班时间（录用时间的下一个星期三入职）

select hiredate, next_dat（hiredate,'星期三'） 入职时间 from emp;

• 转换函数

to_char(x,c)   将日期或数据x按c的格式转换为char数据类型

        select  hiredate from  emp;

        select  to_char(hiredate,'mm/dd/yyy') from emp;

        select to_char(hiredate,'mm “月” dd “日” yyyy“年”') from emp;

to_date(x,c)   将字符串x按照c的格式转换为日期

        select to_date('1990/01/01','yyyy/mm/dd')+4 from dual

to_number(x)  将字符串x转换成数字型

        select to_number('11')+1 from dual;

        select to_number('11','xx') from dual;

•  5.2、常用组函数

avg（）        平均值

        select avg（sal） from emp;

sum（）        求和

        select  sum（sal） from emp where deptno = 20;

min（）        最小值

        select  min(sal)  from emp;

max（）        最大值

        select  max(sal)  from emp;

count（）        统计

        select  count（empno） from emp;

注：null不参与运算

• 去重统计

--统计有几个部门里面有员工

select count(distinct deptno) from emp;

--统计整个公司有几个部门

select count(deptno) from dept;

--求出所有员工的平均奖金

select avg(comm) from emp;

select sum(comm) / count(*) from emp;

--用奖金列求出员工总数

select count(nvl(comm,0)） from emp;

  六、表连接

• 6.1、等值连接

--查询员工信息以及所在部门信息

select  * from emp e,dept d where e.deptno = d.deptno;

--查询出每一个有员工存在部门的信息和部门人数

select  count(*),deptno from emp group by deptno; //查询出有员工的部门的员工人数

select * from dept d, (select  count(*),deptno from emp group by deptno) c   where

d.deptno =c.deptno;

• 6.2、非等值连接

！=、>、<、<>、between and

select * from dept d,(select count(*) , deptno from emp group by deptno) c

where d.deptno = c.deptno(+)  //92标准外连接

select * from dept d,(select count(*) , deptno from emp group by deptno) c

where d.deptno(+) = c.deptno

• 6.3、自连接

--查询出每一个员工（有上级存在的员工）自己的信息，以及上级的信息

select * from emp e,enp m where e.mgr = m.empni;

• 6.4、99标准实现表连接

--笛卡尔积 cross join

select * from dept cross join emp;

--自然连接 natural join（需要有同名列、主外键）

select ename,empno,deptno,dname from emp natural join dept;

--万能join on多表连接

查询的数据：员工的姓名，工资，所属部门编号，工资等级

select ename , sal,deptno,grade from emp e join salgrade s on sal between losal and hisal;

--外连接  right/left  outer join  on 

select  e.empno 员工编号,e.ename 员工名称,e.mgr 上级编号,m.ename 上级名称

from emp m right outer join emp e on e.mgr = m.empno;

select  e.empno 员工编号,e.ename 员工名称,e.mgr 上级编号,m.ename 上级名称

from emp m left outer join emp e on e.mgr = m.empno;

• 6.5、交、并、差

--集合操作

--union 并集（去重）

--union all 全集（不去重）

--intersect 交集

--minus  差集

select 'a' , 'b' from dual

union

select 'c' , 'd' from dual)

minus

(select 'a' , 'b' from dual

union

select 'e' , 'f' from dual)

  七、表操作（DDL）

• 创建表

create table 表名{

        字段名        类型（长度）,

        ...其他字段...

}

• 从其他表拷贝结构

create table 表名 as select 字段列表 from 已有表 where 1！=1;

• 修改表结构

--修改表名

rename   原表名  to  新表名

--修改列名

alter  table  表名  rename  colum  列名  to 新列名

--修改字段类型

alter  table  表名  modify(字段  类型)

--修改字段可为空/不可为空

alter table 表名 modify 列名  null;

--添加列

alter  table 表名  add 字段  类型

--删除列

alter  table 表名  drop  column 字段

--删除表

drop  table  表名

• 创建表同时创建约束

create table tb_user{

        userid  number(5) primary key,  //唯一且非空

        username  varchar2(30)  check(length(username) between 4 and 20) not null;

        userpwd  varchar2(20) not null check(length(userpwd) between 4 and 18),

        age number(3)  default(18)  check(age>=18),

        gender char(3) default('男') check(gender in ('男','女')),

        email  varchar2(30)  unique,

        regtime   date defalt(sysdate)

        txtid number(5) references tb_txt(txtid) on delete set nu ll;

}

• 追加约束

--追加主键约束

alter table tb_user add constraint pk_user_id  primary key(userid);

--追加的一般检查约束

alter table tb_user add constraint ck_user_name  check(length(username) between 4 and 20);

--追加非空约束

alter table tb_user modify(username constraint nn_user_name not null);

--追加默认约束

alter table tb_user modify(age default(18));

--追加外键的三种方式

//强制不让删

alter table tb_txt add constraint fk_tx_user_id foreign key(userid) references tb_user(userid)

//自动设为null

alter table tb_txt add constraint fk_tx_user_id foreign key(userid) references tb_user(userid) on delete set null;

//强制不让删

alter table tb_txt add constraint fk_tx_user_id foreign key(userid) references tb_user(userid) on delete cascade;

  八、增删改（DML）

• 插入

--添加/插入记录 

--使用默认的方式插入,数据必须和表结构里面字段顺序和个数一致

--当某个字段存在默认值时，也必须给出值，不能不给

insert  into 表名 values()

insert into tb_user values(10001,'marry','132',18,'女','9730654@qq.com',to_date('1990-1-1','yyyy-mm-dd'));

commit;

--通过指定列添加,若果某字段可以为null或有默认值可以不指定，由默认值填充

insert into tb_user(userid,retime,username,userpwd,email,gender,age) values(10001,to_date('1990-1-1','yyyy-mm-dd'),'tom1','132','9730654@qq.com',女',18,);

--从其他表中拷贝数据

insert into temp value(select * from temp1 where deptno=30);//全部数据

--拷贝部分字段

insert into temp value(select empno , ename,deptno,sal  from emp where deptno=20);

commit;

--如果有外键的表，添加的值需要主表里面有，否则会报错。

• 更新

update 表名 set 字段1 = 值1【字段2=值2....】 where 过滤行记录

update tb_user set userpwd = 8888 where 1=1;

update tb_user set(username,userpwd) = (select 'god','block'  from dual) where userid = 1;

• 删除

通过delete语句删除表中的记录。（注意存在主外键约束的记录）

delete from 表名  where 条件；

delete from tb_user where userid<10;

delete from tb_user;