Oracle_单表操作

数据库操作

数据库是什么？

答：能够进行数据的增删查改，对外提供访问服务。

数据库启动关闭

1. 首先要登录linux oracle用户
2. 以管理员的方式登录oracle

sqlplus / as sysdba

3. 进入到sqlplus的提示符之后，启动oracle

sql>  startup  

4. 关闭数据库

 sql>  shutdown 

5. 数据库监听服务的启动和关闭
   在终端而非sqlplus交互下

lsnrctl start
lsnrctl stop
lsnrctl status

数据库登录

1. 解锁scott用户

 sqlplus 以管理员执行以下命令
 alter user scott account unlock;

2. 修改scott用户密码

  alter user scott identified by "11";

3. 登录scott用户
   本地登录，数据库服务器就安装在本机

sqlplus 用户名/密码    

超级管理员方式登录 sys

sqlplus / as sysdba  
sqlplus sys/sys as sysdba

sqlplus scott/11  

远程登录

sqlplus 用户名/密码@//主机（ip)/实例名(默认都是orcl)

sqlplus scott/11@//192.168.177.228/orcl     

查看用户信息

1. 查看scott用户方案所拥有的表

查看当前用户名
show user

查看当前用户拥有的表
select * from tab;

查看表结构
desc 表名

desc emp; 

简单查询

 select *|col1 as 别名1 , col2 别名2 ...
    from 表名

查询emp表的所有数据

select * from emp;

自定义显示内容的换行与分页

set linesize 行数
set pagesize 页数

set linesize 140;
set pagesize 50;

显示指定的重命名数据

1. 查询员工号，姓名，月薪，奖金，年收入 并修改列名为中文

select empno as "工 号", ename as 姓名, sal 月薪, sal*12+nvl(comm, 0) 年收入 
	from emp;

3. 双引号可以省略，但是如果列的别名带有空格，就不能省略
4. 别名只是影响结果集的输出，不会真正修改原来的表
5. 数据里边有null值，表示该数据没有值， null做任何的数值运算，结果都为null
6. nvl(表达式,val) 如果表达式的值为null，那么就返回val

查看员工表所有部门编号并去除重复

select distinct deptno from emp;

计算表达式3+2

oracle提供了一张计算表达式用的虚表 dual, from语句是不能省略

select 3+2 from dual;

单条件查询

select ... 
from ...
where cond

查询10号部门的员工信息

select * 
	from emp 
		where deptno = 10;

查询KING的信息

select * 
	from emp 
		where ename = 'KING';

单引号都用于数据表示字符串，双引号用于数据库对象名、列名、表明…

查询薪水不等于1250的员工信息

select * 
	from emp
		where sal <> 1250;
		where sal != 1250;

多条件查询

         与    或    非
    c    &&    ||    !
    sql  and   or    not

查询10号部门中月薪为1300的员工

select * from emp where deptno=10 and sal = 1300

查询部门号是10或者20的员工信息

select * from emp where deptno=10 or deptno = 20
select * from emp where deptno in (10,20);
 	in表示deptno取值在10和20其中一个

查询部门不是10和20的员工信息

select * from emp where deptno != 10 and not deptno = 20;
select * from emp where deptno not in (10,20);

查询工资介于1000到2000之间的员工

select * from emp where sal >= 1000 and sal <= 2000;
select * from emp where sal between 1000 and 2000;
between and 是包括边界值

查询没有奖金的员工信息

结论：null做任何逻辑运算，结果都为假
判断null的方式不能直接用等于不等于，用is 或者 is not

select * from emp where comm is null or comm = 0;

查询有奖金的员工信息

select * from emp where comm is not null and comm != 0;

模糊查询

 select ...
    from ...
    where 列名 like '模式字符串'
        %   表示若干个字符
        _   表示一个字符

查询员工首字母是S的员工信息

select * from emp where ename like 'S%';

查询名字是四个字母的员工信息

select * from emp where ename like '____';

查询姓名带下划线的员工信息

select * from emp where ename like '%\_%' escapt '\';
以反斜杠作为转义字符

排序

select ...
from ...
where cond
order by 列明1 asc/desc， 列明2...

asc表示升序，desc表示降序

员工信息按先后入职日期排序

select * from emp order by hiredate;

员工信息按薪水从大到小排序

select * from emp order by sal desc;

员工信息按部门号和薪水排列

select * from emp order by deptno desc, sal desc;
asc或者desc只影响跟在前面那一列
多列排序的策略，首先按照第一列来排序，如果第一列值相等的情况下再按照第二列的值来排序

员工信息按奖金倒序

select * from emp order by comm desc nulls last;
null也会影响排序，如果想要null放在后边 添加 nulls last

员工信息按第2列排序

select * from emp order by 2;

字符函数

大小写函数

lower 将字符串转小写
upper 将字符串转大写
initcap 将首字母变成大写

select lower('heLLo WoRld'), upper('heLLo WoRld'), initcpt('heLLo WoRld') from dual;

concat 字符串连接

select concat('hello', 'world') from dual
concat函数只支持两个参数   
select 'hello'||'world' || 123 from dual;

substr 提取子串

select substr('hello world', 3, 5) from dual;
从第三个字符开始取5个字符
select substr('helloworld',3),     
从第三个字符开始一直取子串到最后

instr 查找某个字符串是否另一个字符串的子串

select instr('hello world', 'llo') from dual;

lpad,rpad 左右填充

select lpad('hello', '10', '#') from dual;
往hello左边填充字符#，使字符串长度到达10

trim 裁剪

select trim ( '    Hello world    ') from dual;   
默认裁剪空白字符
select trim('H' from 'hello worldHHHH') from dual;

replace 替换子串

select replace('hello world', 'llo', '#');

数值函数

round  --四舍五入
trunc  --截取
ceil   --向上取整
floor  --向下取整
mod    --取模 %

select round(45.926, 2),
	trunc(45.926, 2),
	ceil(45.926),
	floor(45.926),
	mod(1600, 1000)
	from dual

转换函数

数值转字符串

select ename, sal, to_char(sal, 'L9999') from emp;
L  表示本地货币字符
9  表示一位数字
将sal转为4位本地货币格式的字符串

字符串转数字

select to_number('￥800', 'L9999') from dual;
以什么格式转化为字符串，就以什么格式转回number类型

日期转字符串 to_char

select to_char(sysdate,'dd-mm-yyyy dy hh24:mi:ss') from dual;

字符串转日期 to_date

 select to_date('30-05-2019 星期四 17:01:57','dd-mm-yyyy dy hh24:mi:ss') from dual;
 以什么格式转化为字符串，就以什么格式转回date类型

日期函数

sysdate 获取系统当前时间

select sysdate from dual ;
显示昨天、今天、明天
select sysdate-1 /sysdate /sysdate +1 from dual;

计算员工工龄，按照日、周、月、年显示

select sysdate-hiredate 日,
              (sysdate-hiredate)/7 周,
              (sysdate-hiredate)/30 月,
              (sysdate-hiredate)/365 年
        from emp;

months_between 计算两个时间相差的月份

select 
     (sysdate-hiredate)/30 月,
      months_between(sysdate,hiredate)
from emp;

add_months 计算明年今日

select add_months(sysdate,12) from dual;

last_day 获取当前月份最后一天

 select last_day(sysdate) from dual

next_day 获取下一个星期几是哪一天

 select next_day(sysdate,'星期二') from dual

通用函数

nvl 判断NULL

nvl(exp, val)
若exp为NULL， 则返回val， 否则返回exp的值

select ename, nvl(comm, 0) from emp
若comm是NULL，则返回0，不是NULL，返回comm

nvl2判断NULL

 nvl2(exp,val1,val2)
如果exp为null，返回val2 ，否则返回val1

select ename,nvl2(comm,'有奖金','没奖金') from emp
comm有奖金就显示'有奖金'，NULL就显示'没奖金'

条件语句

case when then else end

select ename, job, sal 涨前工资,
	case job
		when 'MANAGER' then sal+1000
		when 'SALESMAN' then sal+500
		else sal+200
	end 涨后工资
from emp;

多行计算函数

不是通过单行的数据就能够计算出结果，而是要遍历多行数据之后得到一个汇总结果，再输出

sum 求员工工资总和

select sum(sal) from emp;

求员工数量,有奖金的员工数

select count(*), count(comm) from emp;
null不会参与统计函数的计算

求工作岗位数量

select count(distinct job) from emp;
统计计数中去重

max/min 求员工最高工资和最低工资

select max(sal), min(sal) from emp;

avg 请员工平均奖金

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

分组统计

select里边填的列只能是group by 出现的列，或者是统计函数

select ...
    from ...
    where cond
    group by 列名1[,列名2,....]
    having cond

查询各部门平均工资

select deptno, avg(sal) from emp group by deptno;
统计函数，在没有分组的情况，统计的是全表，在有分组的情况下，统计的是对应的分组

查询平均薪水大于2000的部门

select deptno,avg(sal)
        from emp
        where avg(sal)>2000
        group by deptno;
报错：where里边不允许使用分组函数, 改用having

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

求10号部门员工的平均薪水

select deptno, avg(sal) where deptno = 10 from emp group deptno;
select deptno, avg(sal) from emp group deptno having deptno = 10;

having与where的区别

注意：能使用where不要使用having

selct ....
        from ....
        where cond1
        group by ....
        having cond2
        order by ....

1. 从表集合中查询数据，首先要满足where条件cond1，满足的数据就筛选出来，称为集1；
2. 从集1中使用group by指定的列进行分组，计算统计函数的值，称为结果集2；
3. 集2继续筛选，筛选条件就是having的条件cond2，结果称为集3；
4. 集3进行order by的条件进行排序，得到最终的结果集

数据越早进行筛选越好，对于后面计算量控制有优势