会话(session) 是用户进程和oracle实例间的特定连接 同一用户可建立和存在多个用户
在专用服务器模式中,可谓多个用户建立服务器进程
ORACLE 进程
Oralce 进程分为服务器进程 ,又称影子进程 和后台进程;
服务器进程用于处理连接到该实例的用户进程的请求,当应用程序和oracle 运行在同一套主机时,用户进程和相应的服务器进程可组合到单个进程,以减少系统开销 当应用进程和oracle 运行在不同的主机中时 用户进程将通过一个单独的服务器进程与oracle 进行连接.
服务器进程完成的工作 (分析和执行sql语句所需要的数据不在SGA中时 从磁盘数据文件中拷贝数据到SGA的共享数据缓冲区 按要求返回结果)
后台进程在实例启动或者安装的时候建立,用于优化性能和协调多用户。
ORACLE 内存结构
1,软件代码区
2,系统全局区
3,程序全局区
4,排序区
ORACLE内存中保存的信息
1,程序代码 (正在执行的 和可能执行的)
2,连接和回话信息 (互动和不活动的)
3,程序执行期间需要的信息 (状态信息)
4,oracle进程间共享通信的信息 (锁信息)
5,高速缓存的外存中永久保留的数据(数据块 重做日志条目)
虚拟内存
可以使用虚拟内存,但整个sga 最好放在实际内存中
通过内存分页或交换实现虚拟内存和世纪内存的映射 交换已进程为单位分页以野为单位
分页和减缓使用和消耗大量的系统资源存取存盘比存取内存大概慢50倍
软件代码区(SGA)
软件去 用于保存正在执行和可能执行的代码
大小一般不同与安装和操作系统有关。
二, oracle 的一些规范
comment on column xx.name is '员工姓名'
create tablespace tjsgbb DATAFILE 'tjsgbb.ora' size 500M AUTOEXTEND ON;
create user tom identified by cat default tablespace tjsgbb;
grant connect to tom;
grant resource to tom;
grant dba to tom;
from table_name a
where rowid>(select min(rowid)
from table_name b
where b.pk_column_1=a.pk_column_1
and b.pk_column_2=a.pk_column_2
);
5,求m 行到n行的数据
--求出 stu表中 m行 到n行的数据 3 5
select rn,col1,col2 from (
select rownum rn,col1,col2 from
stu order by sid asc) where rn between 1 and 5;
6,一条记录 根据条件多表插入
insert all
when(id=1) then
into table_1(id,name)values(id,name)
when (id=2) then into table_2(id,name) values(id,name)
else
into table others
)
7,实现分组取前n条记录
select * from
(select deptno
ename
sal
row number() over (partition by depno order by sal desc) m
from emp
)
where rn<3
8,日期比较
select sysdate
,extract(yeat from sysdate) date_year
,EXTRACT(MONTH FROM SYSDATE) DATE_MONTH
,EXTRACT(DAY FROM SYSDATE) DATE_DAY
,SYSTIMERSTAMP
,EXTRACT(YEAT FROM SYSTIME STAMP) TIME_YEAR
,EXTRACT(MONTH FROM SYSTIME STAMP) TIME_MONTH
,EXTRACT(DAY FROM SYSTIME STAMP) TIME_DAY
,EXTRACT(HOUR FROM SYSTIME STAMP) TIME_HOUR
,EXTRACT(MINUTE FROM SYSTIME STAMP) TIME_(MINUTE
,EXTRACT(SECOND FROM SYSTIME STAMP) TIME_SECOND
9,SELECT SYSDATE 时间
,TRUNC(SYSDATE,'YEAR') 年初
,TRUNC(SYSDATE,'Q') 季初
,TRUNC(SYSDATE,'MONTH') 月初
,TRUNC(SYSDATE,'DAY') 周第一天
from dual;
="insert into EMPLOYEE(EMPNO,EMPNAME,GENGDER,AGE,SALARY,DEPTNO,BIRTHDAY,ENAME) values('"&A2&"','"&B2&"','"&D2&"',"&E2&","&F2&",'"&G2&"',to_DATE('"&H2&"','YYYY-MM-DD'),'"&C2&"'"&" );"
)
三 ,实例
create tablespace tjsgbb DATAFILE 'tjsgbb.ora' size 500M AUTOEXTEND ON;
create user tom identified by cat default tablespace tjsgbb;
grant connect to tom;
grant resource to tom;
grant dba to tom;
--删除 表
drop table EMPLOYEE;
--创建表 EMPLOYEE (员工基本信息表)
CREATE TABLE EMPLOYEE
(
EMPNO VARCHAR2(50)PRIMARY KEY,
EMPNAME VARCHAR2(100),
ename varchar2(20),
GENGDER CHAR(2),
AGE INTEGER,
SALARY DECIMAL(26,4),
DEPTNO VARCHAR2(50),
BIRTHDAY date
)TABLESPACE tjsgbb;
COMMENT ON TABLE EMPLOYEE IS '员工基本信息表';
COMMENT ON COLUMN EMPLOYEE.EMPNO IS '员工号';
COMMENT ON COLUMN EMPLOYEE.EMPNAME IS '姓名';
COMMENT ON COLUMN EMPLOYEE.GENGDER IS '性别';
COMMENT ON COLUMN EMPLOYEE.AGE IS '年龄';
COMMENT ON COLUMN EMPLOYEE.SALARY IS '薪资';
COMMENT ON COLUMN EMPLOYEE.DEPTNO IS '部门编号';
COMMENT ON COLUMN EMPLOYEE.ename IS '员工英文名';
COMMENT ON COLUMN EMPLOYEE.BIRTHDAY IS '员工生日';
select * from EMPLOYEE;
--向EMPLOYEE 表里面 添加 数据
insert into EMPLOYEE(EMPNO,EMPNAME,GENGDER,AGE,SALARY,DEPTNO,BIRTHDAY,ENAME) values('30132','小王','1',34,2530,'A',to_DATE('1983-06-01','YYYY-MM-DD'),'MR_WANG' );
insert into EMPLOYEE(EMPNO,EMPNAME,GENGDER,AGE,SALARY,DEPTNO,BIRTHDAY,ENAME) values('30133','小李','1',22,3410,'A',to_DATE(' 1995-3-21','YYYY-MM-DD'),'MR_LI' );
insert into EMPLOYEE(EMPNO,EMPNAME,GENGDER,AGE,SALARY,DEPTNO,BIRTHDAY,ENAME) values('30134','小吴','1',23,5026,'C',to_DATE(' 1995-3-22','YYYY-MM-DD'),'MR_WU' );
insert into EMPLOYEE(EMPNO,EMPNAME,GENGDER,AGE,SALARY,DEPTNO,BIRTHDAY,ENAME) values('30135','小张','0',25,1860,'B',to_DATE(' 1992-8-21','YYYY-MM-DD'),'ZHANG' );
insert into EMPLOYEE(EMPNO,EMPNAME,GENGDER,AGE,SALARY,DEPTNO,BIRTHDAY,ENAME) values('30136','小孙','0',22,3432,'B',to_DATE(' 1991-10-12','YYYY-MM-DD'),'SUN' );
insert into EMPLOYEE(EMPNO,EMPNAME,GENGDER,AGE,SALARY,DEPTNO,BIRTHDAY,ENAME) values('30137','小明','1',21,6422,'D',to_DATE(' 1991-10-13','YYYY-MM-DD'),'MING' );
CREATE TABLE DEPARTMENT
(
DEPTNO VARCHAR2(50) PRIMARY KEY,
DEPTNAME VARCHAR2(100)
)TABLESPACE tjsgbb;
SELECT * FROM DEPARTMENT;
--创建 表SCORS(成绩表)
DROP TABLE SCORS;
CREATE TABLE SCORES
(
STUNO VARCHAR2(50)
,SUBJECT VARCHAR2(20)
,SCORE DECIMAL(26,2)
) TABLESPACE tjsgbb
;
COMMENT ON TABLE SCORES IS '学生成绩表';
COMMENT ON COLUMN SCORES.STUNO IS '学号';
COMMENT ON COLUMN SCORES.SUBJECT IS '学科';
COMMENT ON COLUMN SCORES.SCORE IS '分数';
select * from SCORS;
select * from EMPLOYEE where birthday<to_date('1993-03-01','yyyy-mm-dd');
select * from EMPLOYEE where age<23;
---------like 的用法 (模糊查询的时候尽量用后模糊)
select * from EMPLOYEE where ename like 'M%';
select * from EMPLOYEE where empname like '小_';
select * from EMPLOYEE where lower(ename) like '_r%';
---------between and 包括两头 是>= 和<=的意思
select * from EMPLOYEE where salary between 3410 and 5026;
---------order by 升序降序运算(不推荐使用 使用的时候 会影响性能)
select e.empName,e.empno,e.salary from EMPLOYEE e order by e.empno desc;
--常见的一些 sql 语句 操作 更改(UPDATE)
update EMPLOYEE e set e.age=20 where e.empno='30136';
--常见的一些 sql 语句 操作 删除(DELETE)
delete from EMPLOYEE e where e.empno='30132';
--查看 表结构 (命令行里面)
desc EMPLOYEE
--重要 修改添加 记得commit;
commit;
--事务回滚
rollback;
--oracle 中的锁 防止数据的脏读
---高级sql
-----分组查询
select e.deptno,sum(e.salary) 薪水 from EMPLOYEE e group by e.deptno;
select e.deptno,sum(e.salary) 薪水 from EMPLOYEE e group by e.deptno having sum(e.salary)<6000;
-----count 尽量使用*
select count(1) from EMPLOYEE e where e.salary>2000;
-----去重的话 尽量不使用 distinct 使用group by
select e.empno,e.empname,e.ename,e.gengder,e.age,e.salary,e.deptno,e.birthday from EMPLOYEE e group by
e.empno,e.empname,e.ename,e.gengder,e.age,e.salary,e.deptno,e.birthday;
-----==值连接查询
select *from EMPLOYEE e,DEPARTMENT d where e.deptno=d.deptno;
-----左右连接查询
select * from EMPLOYEE e left join DEPARTMENT d on e.deptno=d.deptno and d.deptno='D' where d.deptno='D';
select * from EMPLOYEE e right join DEPARTMENT d on e.deptno=d.deptno;
-----自然连接 就是 inner join 内连接 查询 只要是从侧表的数据 来查询的话 都能使用内连接
select * from EMPLOYEE e INNER join DEPARTMENT d on e.deptno=d.deptno and e.deptno='D';
-----full join 先左连接 再右连接
select * from EMPLOYEE e full join DEPARTMENT d on e.deptno=d.deptno;
-----子查询
select * from DEPARTMENT d where d.deptno in (select e.deptno from EMPLOYEE e group by e.deptno having avg(e.salary)>3000);
select e.deptno,avg(e.salary) from EMPLOYEE e group by e.deptno;
select rownum 部门排名,c.deptno from (SELECT e.deptno,avg(e.salary) FROM EMPLOYEE e group by e.deptno order by avg(e.salary) desc) c
where rownum<=3;
---一些 其他的字符 all any
select * from EMPLOYEE e where exists (select d.deptno from DEPARTMENT d where d.deptname='部门C');
select e.empno,
e.empname,
e.ename,
e.gengder,
e.age,
case
when e.SALARY<3411 then 1000
ELSE 0
END,
e.deptno,
e.birthday
from EMPLOYEE e;
select empname,salary from EMPLOYEE;
---按照 薪资分等级
select e.empno,
e.empname,
e.ename,
e.gengder,
e.age,
case
when e.SALARY<3000 then '菜鸟入门'
when e.SALARY>3000 and e.SALARY<5000 then '老鸟'
ELSE '走向正轨'
END,
e.deptno,
e.birthday
from EMPLOYEE e;
----横变竖 竖变恒
select * from scores;
case
when s.subject='语文'
then s.score
else 0
end 语文,
case
when s.subject='数学'
then s.score
else 0
end 数学,
case
when s.subject='英语'
then s.score
else 0
end 英语
from scores s;
select s.stuno,
sum(
case
when s.subject='语文'
then s.score
else 0
end ) 语文,
sum(
case
when s.subject='数学'
then s.score
else 0
end) 数学,
sum(
case
when s.subject='英语'
then s.score
else 0
end) 英语
from scores s group by s.stuno;
----PL/SQL 是快结构化语言 快是PL/SQL基本单元 存储过程 和触发器