（一）Oracle

（一）Oracle

基本知识

数据库介绍：
1.数据库是一种软件
2.数据库是专门用来存储数据
3.数据库可以多用户共享访问
Oracle数据库：
1.一个数据库有多个实例组成
2.有一个客户端和一个服务端
3.实例是存储在内存中，数据库是存储在硬盘中

Oracle的启动和关闭

1.Oracle有客户端和服务器，现在开启服务端（镜像虚拟机）
2.需要用数据库管理员权限进入数据库
终端上：sqlplus / as sysdba
3.启动oracle服务
SQL>startup
4.关闭服务
SQL>shutdown
5.退出Oracle客户端
exit
6.开启远程服务，在终端上开启
lsnrctl start
7.关闭远程服务连接
lsnrctl stop
8.查看连接情况
lsnrctl status

数据库的基本操作（打开命令窗口）

1.查看当前用户
show user;
2.查看当前用户下的表
select *from tab;
3.查看表的结构
desc 表名；
4.设置行宽
set linesize 140;
5.设置页面
set pagesize 100;
6.修改用户密码
1)数据库管理员的身份进入数据库：sqlplus / as sysdba
2)修改密码：alter user scott identified by “123”
7.登录scott用户
在终端：sqlplus 用户名/密码 （如：sqlplus scott /123）
scott方案：自带4张员工信息表，部门表，薪资等级表，奖金表

sql语言的介绍

1.对数据库里的数据经行操作叫DML
1)增 insert 2) 删 delete 3)改 update 4) 查 select
2.对数据库里的表及其他进行操作的叫DDL
create table(创建表)
alter table(修改表)
truncate table(清空表)
drop table(删除表)
create view(视图)
create index(索引)
create sequence（序列）
3.对数据库的操作叫DCL
commit(提交)
rollback（回滚）:是回到最近一次提交事务的状态

在事务的过程中可以使用保存点来记录过程
创建保存点: savepoint 保存点名字
下次如果发现之前某一步做错了，不至于直接 rollback回到原始事务

回滚到保存点: rollback to savepoint 保存点名字

一但当前事务提交或者回滚，在当前事务建立的savepoint将失效

简单查询

1.查询emp表中的所有数据
select *from emp;

注意：* 表示把表中所有的列明都显示出来

2.查询员工号，姓名，月薪，奖金，年薪，年收入 并修改列明为中文
select empno as “工号”，ename “姓名”,sal 月薪，sal12 年薪,sal12+nvl(comm,0) 年收入 from emp;

注意：1.null和什么结合都为空
2.nvl函数，nvl(参数1，参数2)；如果参数一有数值，那么就返回参数1，如果为空，那么就返回参数2
3.给字段取中文（字段 as “中文名“）as 可以不写，” ”也可以不写

3.查看员工表中所有部门编号并去除重复
select distinct deptno from emp;

distinct去除重复

4.计算表达式3+2
select 3+2 from dual;

dual----虚表

单条件查询

格式：select … from …表名 where 列名

如查询10号部门的员工信息： select *from emp where deptno=10;
如果想查询员工入职时间：select *from v$nls_parameters;
若想修改为中式日期格式，则：alter session set NLS_DATE_FORMAT = ‘yyyy-mm-dd’;

多条件查询

我们在C语言中的 与 或 非 分别用 && ，|| ，！表示。而在SQL中是用 and , or , not 表示

1.查询10号部门中月薪为1300的员工信息：
select *from emp where deptno = 10 and sal=1300;
2.查询部门号是10或者20的员工信息
select *from emp where deptno=10 or deptno=20;
或者selsct *from emp where deptno in(10,20);
3.查询工资介于1000到2000的员工信息：
select *from emp where sal>=1000 and sal<=2000
或者select *from emp where sal between 1000 and 2000;

注意：between 是闭区间

4.查询没有奖金的员工信息：
select *from emp where comm is null;

模糊查询

格式：select *from 表名 where 列明 like …

% 表示任意字符任意次数；_ 表示单个任意字符

1. 查询员工首字母是S的员工信息：
   select *from emp ename link ‘S%’
2. 查询姓名带下划线的员工信息
   1)插入姓名带下划线的员工信息
   insert into emp values(8888,‘A_B’,‘ddd’,5555,sysdate,9999,null,10);

插入时需要根据要插入的表单格式进行，sysdate:获取当前时间

2）查询带下划线的员工信息
select *from emp where ename link ‘%_%’ escape ‘’;

排序

格式：select *from emp where … order by 列名1，列名2…
1.员工信息按先后入职日期排序
select *from emp order by hiredate;（默认升序）
select *from emp order by hiredate desc;

注意：desc 是降序，asc是升序

2.员工信息按第2列排序
select *from emp order by 2;
select ename,sal,deptno from emp order by 2;

by后边的2表示要显示的列数中的第2列

3.员工信息按别名排序
select ename,ename 工号 from emp order by 工号;

单行函数

字符函数

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

select lower（‘HELLO’）from emp;
1.concat(a,b) //连接字符串a和b，只能连接两个
select concat(‘hello’,‘world’) from emp;
或者这样可以链接三个
select ‘hello’ || ‘world’ || ‘hahaha’ from emp;
2.substr(a,b) //获取字串 从a中，第b位开始取（计数从1开始），取到结尾
select substr(‘abcdef’,2) form emp;
3.length:计算字符数，lengthb:计算字节数
select length(‘hello我’)，lengthb(‘world我’) from emp;
4.instr(B,A) // 用来查找A字符串在B字符串中的位置
select instr(‘hello’,‘llo’) from emp;
5.lpad,rpad //左右填充
select lpad(‘hello’,10,’#’),rpad(‘world’,10,’$’)from emp;
6.trim:去掉前后指定的字符
select trim(‘h’ from ‘hhhhhhelohhhhhh’) from dual;
7.replace:替换
select replace(‘hello’,‘l’,’*’) from emp;//heo*

数值函数

round 四舍五入
trunc 截取数值
ceil、floor 向上取整、向下取整
mod 取模

select round(49.982,2),trunc(49.982,2),ceil(49.982),floor(49.982),mod(10,3) from dual;

转换函数

1.将数字转字符串：to_char
select ename,sal,to_char(sal,‘L9999.99’)from emp;

将薪资转换成本地货币字符串
说明：L表示本地货币符，9表示数字

2.字符串转数字：to_number
select to_number(’$8000’,‘L9999.99’) from dual;
3.日期转字符串/字符串转日期：to_char / to_date
select ename,to_char(hiredate,‘dd,mm,yy’) from emp;
select to_date(‘17,12,1981’,‘dd,mm,yyyy’) from dual;

日期函数

1.sysdate:表示今天日期
select sysdate-1 昨天，sysdate 今天，sysdate+1 明天 from emp;
计算员工工龄，按照日、周、月、年显示
== select sysdate-hiredate 日,
(sysdate-hiredate)/7 周,
(sysdate-hiredate)/30 月,
(sysdate-hiredate)/365 年
from emp;==

输入sql语句多的话，打开编辑界面：ed
注意： 在编辑界面写sql语句时，最后不可加分号
到SQL>用 \ 而不是分号

2 months_between:计算两个时间日期的数值相差多少个月
select months_between(sysdate,hiredate) from emp;
3.add_months:计算明年今日
select add_months(sysdate,12) from dual;
4.last_day:计算月份的最后一天
select last_day(sysdate) from dual;
5.next_day:计算下一个星期几
select next_day(sysdate,‘Monday’) from dual;
6.nvl(exp,val1,val2)// 如果exp为null，就返回val2，否则返回val1
select ename,nvl(comm,‘有奖金’,‘没奖金’) from emp where comm!=0 or comm is null;

条件语句

SQL格式：select …case when… then else end…from emp;

总裁决定给大家涨工资，主管涨1000，销售涨500，其他涨200
select ename,job,sal “涨薪资前”,
case job
when ‘MANAGER’ then sal+1000
when ‘SALESMAN’ then sal+500
else sal+200
end “涨薪资后”
from emp;
switch(job)
{
case ‘MANAGER’
sal+1000
break;
case ‘SALESMAN’
sal+500
break;
default:
sal+200
}

orcale的写法格式：select …decode(expr,val1,val2,val3,val4 …,default) from emp;

select ename,job,sal “涨薪资前”,
decode(job,‘MANAGER’,sal+1000,‘SALESMAN’,sal+500,sal+200) “涨薪资后” from emp;

多行函数

统计函数

1.sum //计算和

select sum(sal) from emp; //求员工工资总和
2.count //只求某一行某一列有值
select count(ename),count(comm) from emp;//求员工数量,有奖金的员工数
3.max/min //求员工最高工资和最低工资
select max(sal),min(sal) from emp;
4.avg //求平均值
//求员工平均工资
select avg(sal) from emp;
select sum(sal)/count(ename) from emp;

分组统计

格式：select …
from …
where cond1
group by 列1,列2… #根据某一列或者多列来分组
having cond2

//查询各部门平均工资
select deptno,avg(sal) //第二步
from emp
group by deptno;//第一步

//查询平均薪水大于2000的部门
select deptno,avg(sal) //第二步
from emp
group by deptno//第一步
having avg(sal)>2000;//第三步

///求10号部门员工的平均薪水
select deptno,avg(sal)//avg计算是第三步
from emp
where deptno=10//第一步
group by deptno;//第二步

having与where的区别：
1.where优先于having运行，先where进行筛选，能够减少计算数据，提高效率
2.能用where就尽量用where

多表查询及笛卡尔积

1.如果要获取的数据不在同一个表中，要是有多表查询
2.笛卡尔积

|1.查询多张表时,会出现笛卡尔集问题,会多余垃圾信息
2.笛卡尔全集的行数是两张表的行数相乘,列数是两张表相加

查询员工信息：员工号，姓名，月薪和部门名称
员工、姓名、月薪 在 EMP表
部门名称 在 dept
select empno,ename,sal,dname
from emp e,dept d
where e.deptno=d.deptno;

不等值连接

|查询员工信息：员工号，姓名，月薪和月薪级别（salgrade表）
select empno,ename,sal,GRADE
from emp,salgrade
where sal between LOSAL and HISAL;

外连接

按部门统计员工人数，显示如下信息：部门号，部门名称，人数（注意统计40号部门）
部门号，部门名称在dept表
人数的统计在emp表中来统计
第一步:两张表进行连接,剔除垃圾数据
select *from dept,emp where dept.deptno=emp.deptno;
第二步:分组统计
select d.deptno,d.dname from dept d,emp e
where d.deptno=e.deptno
group by d.deptno,d.dname
第三步:统计人数
select d.deptno,d.dname,count(empno)
from dept d,emp e
where d.deptno=e.deptno
group by d.deptno,d.dname

// 外连接的目的是:保留没有通过筛选条件的数据
// 外连接分为:左外连接和右外连
select d.deptno,d.dname,count(empno)
from dept d,emp e
where d.deptno=e.deptno(+)
group by d.deptno,d.dname
如果要保留等号的左边表中没有通过筛选的数据,就叫左外连接
如果要保留等号的右边表中没有通过筛选的数据,就叫右外连接
写法:
左外连接的写法是在等号的右边的表名后面加上(+)
右外连接的写法是在等号的左边的表名后面加上(+)

子查询

1.一个select语句里边可以嵌套其他select语句，就是子查询
2.子查询优点 ： 方便理解、实现更复杂的查询、提高查询效率

//查询比scott工资高的员工信息

1.查询scott的工资 select sal from emp where ename=‘SCOTT’;//3000

2.查询比3000高的员工信息 select *from emp where sal>3000

答案:
select *from emp where sal>
(
select sal from emp where ename=‘SCOTT’
);

//查询10号部门的员工号、员工姓名、部门编号、部门名称
select empno,ename,deptno,
(
–将10号部门的名称查询出来
select dname from dept
where deptno=10
) “部门名称”
from emp
where deptno=10;

集合运算

1.交集：intersect

2.并集：union

3.全并集 ：union all

4.差集：minus

部门号是10的员工和部门号是20的员工信息做并集
select *from emp
where deptno=10
union
select *from emp
where deptno=20;

插入

1.格式：insert into 表名(列明1，列明2，…) values(val1,val2,val3)…

insert into dept values(50,‘SUPPORT’,‘WASHINGTON’);
insert into dept values(60,‘TEST’,null);
insert into dept(deptno,dname) values(70,‘PURCHASING’);

更新

格式：update 表名 set 列明1=值1，列明2=值2…[where cond]

将60号部门的LOC改成MIAMI
update dept set LOC='MIAMI' where deptno=60;


将50号部门的部门名字改成 SUPPORT1 ，LOC改为NULL
update dept set dname='SUPPORT1',LOC=null where deptno=50;

删除

格式：delete from 表名 where cond

注意:删除数据都是以行为单位

删除部门号为50的部门信息
delete from dept where deptno=50;
   
删除部门号大于40的部门信息
delete from dept where deptno>40;