1.软件架构
B/S架构:Browser(浏览器)、S(Server)
C/S架构:Client(客户端)、S(Server)
2.数据类型:
oracle:
number(10,2):数字类型:表示这个数字总长度是10位,其中有两位是小数类型
varchar2(长度):字符串
date:时间类型
blob:存放较大的数据类型【比如图片、视频】,基本不用。
3.SQL:[外语]
1.DDL【Database Definition Language】: create table 表名/index/序列/view,drop table 表名/index/序列/view
2.DML:数据库操作语言,比如:insert、update、delete
3.DCL:数据库控制语言,比如:commit、rollback、savepoint
4.DQL:数据库查询语言,select
4.DQL:数据库查询语言 【单表—>多表—>子查询】
4.1单表:
1). 简单查询【限定列用select完成】
语法:
SELECT [DISTINCT] * | 列名称 [别名] , ... ? 确定数据显示列
FROM 表名称 [别名] ;
注意:
1. 在oracle中给表的列名起别名用as关键字,as关键字可以省略。
2. 在oracle中别名用双引号引起来,当然双引号有时可以省略。当别名里边出现特殊字符的时候就不能省略
3. 在oracle中只有起别名的时候用到双引号,其它地方【字符串】用的是单引号。单引号和双引号不一样。
4.||是Oracle中的连接符,类似于java中的+
2). 限定查询 【限制行数】
语法:
SELECT [DISTINCT] * | 列名称 [别名] , ...
FROM 表名称 [别名]
WHERE 限定条件(s) ;
可以作为where条件的:
1.关系运算符: < <=、>、>= 、 !=( <> )
2.范围运算符between...and、in
2.1 between...and是否包含边界值:包含边界值 相当于 起始值 =< 字段 <= 终止值
2.2 between...and 后面的值要比前面的值要大。
3.空和非空:null
用: is null 、is not null
1.null值不能参与运算,null值参与的运算结果都为null
4.模糊查询:like
_:可以匹配任意一个字符
%:任意多个任意字符
3)数据排序:
语法:
SELECT [DISTINCT] * | 列名称 [别名] , ... ? 3、设置要显示的数据列
FROM 表名称 [别名] ? 1、确定数据来源
【WHERE 限定条件(s) 】 ? 2、针对于数据行进行筛选
[ORDER BY 排序字段 [ASC |DESC] ,排序字段 [ASC |DESC] , .. ]; ? 4、数据列排序
注意点:
1.ORDER BY语句是位于SQL语句的最后一行。
2.order by后面默认的排序方式是ASC【Ascend:升序】,DESC【Descend:降序】
3.order by排序字段是有优先级的,先按着第一个排序字段排,在第一个排序字段相等的情况下,会再按着指定的第二个排序字段,依次类推。
赵六`s salary is 15000
4)函数
-
单行函数
1)字符串类型函数: UPPER()、LOWER()、INITCAP()、SUBSTR()、TRIM()、LENGTH()、REPLACE() select upper('hello world'),lower('Hello WORLD'),initcap('hello world') from dual; select substr('hello world',3,3) from dual; substr('hello world',3,3): 第一个参数:被截取的字符串 第二个参数:表示从第几位开始截取,起始位置默认1 第三个参数:表示截取的长度 trim:取出字符串前后空格 length:求字符串长度 REPLACE() 案例:select replace('hello world','o','e') from dual; 第一个参数:被替换的字符串 第二个参数:对被替换的字符串的哪一部分替换 第三个参数:想替换为什么字符串 2) 数字类型函数: floor、ceil、abs、power(); floor:求不大于一个数的最大整数 ceil:求不小于一个数的最小整数 abs:求绝对值 power:求幂指数 sqrt(16) 3) 时间类型函数: 注意:对于时间类型,只能进行加减操作,不能进行乘除操作。 · 日期 + 数字 = 日期,表示若干天之后的日期; · 日期 – 数字 = 日期,表示若干天之前的日期; · 日期 – 日期 = 数字,表示两个日期之间所经历的天数。 4) 相互转换函数:数字、字符串、时间相互转换的函数 select * from orders where to_char(ORDER_DATE,'yyyy/MM/dd') = '2013/11/28'; select * from orders where ORDER_DATE = to_date('2013-11-28','yyyy-MM-dd'); select * from emp where to_char(eid) = '2'; select * from emp where eid = to_number('2'); 5)通用函数:对数字、字符串、时间都适用 NVL():相当于java中的if语句 语法:NVL(列,默认值); 如果这一列有值,就用该列值,如果这一列值为null,还可以为这一列指定默认值。 select eid,name,salay+nvl(comm,0) as "salary" from emp where eid = 1; nvl2():相当于java中的if..else语句,对nvl的增强版 语法:NVL2(列,列,默认值);表示该列不为null的时候,返回值1,为null的时候返回指定的值2 select eid,name,salay+nvl2(comm,100,200) as "salary" from emp where eid = 4; DECODE()。相当于java中的switch...case语句 语法: 数据 DECODE(列,比较数据1,显示数据1,比较数据2,显示数据2 , …. [,默认返回]);
-
多行函数
【分组统计函数】: max、min、avg、sum、count
注意点:
1.分组统计函数是针对组而言的,如果sql语句中没有出现group by分组语句,默认是将整张表当做一组
面试题:请解释COUNT(*)、COUNT(字段)、COUNT(DISTINCT 字段)的区别?
2.count(*)/count(数字):是一致的
count(列):统计列值不为null的行数
COUNT(DISTINCT 字段):表示对某一列先去重,然后再统计有多少行
分组语句:
语法:
SELECT [DISTINCT] * | 列名称 [别名] , … | 统计函数 | 分组字段
FROM 表名称 [别名] , 表名称 [别名] , …
[WHERE 过滤条件(s)]
[GROUP BY 分组字段 , 分组字段 , ...] ? 字段数据有重复
[ORDER BY 字段 [ASC | DESC] , 字段 [ASC | DESC] ,...] ;
注意点:
1.分组统计函数是针对组而言的
2.一旦使用了group by分组语句,select后面的列名必须出现在group by之后,这和mysql不一样!
3.GROUP BY 子句可以对多个字段分组
4.一旦分组语句之后,如果想使用分组函数进行条件过滤,要把分组函数放在having子句中使用,不能放在where子句中。
SQL执行顺序:
from -- join-- where ----GROUP BY----->having子句---->select --->ORDER BY语句
-
连表操作:
横向连接 where:条件过滤的 语法: select 表1.某些列,表2.某些列 from 表1,表2 where 表1.列=表2.列 join关键字 语法: select from 表1 inner |left | right join 表2 on 表1.列=表2.列 where 过滤条件 纵向连接 union: 要求 1.纵向连接的两张表的列数一致! 2.对应列的数据类型保持一致
-
子查询:
一条SQL查询语句里面嵌套另一条SQL查询语句,被嵌套的SQL查询语句,称之为子查询 where子查询:当子查询语句出现在where子句中,称之为where子查询 having子查询:当子查询语句出现在having子句中,称之为having子查询 from子查询: 当子查询语句出现在from子句中,称之为from子查询 案例:where子查询 -- 查询出员工信息,要求该员工的薪资比公司所有员工的平均工资要高 select * from emp where salay > (select avg(salay) from emp) having子查询: --查询出 哪种产品的订单项均值 大于 所有产品订单项均值的 select product_id, avg(money) from order_items where product_id is not null group by product_id having avg(money) > (select avg(money) from order_items where product_id is not null) from子查询: 连表操作: select o.id,o.address,o.order_date,count(*),sum(money) from orders o join order_items oi on o.id= oi.order_id where oi.order_id is not null group by o.id,o.address,o.order_date from子查询: select o.id,o.address,o.order_date,tmp.ct,tmp.sm from orders o,(select order_id, count(*) ct,sum(money) sm from order_items group by order_id) tmp where o.id = tmp.order_id
在上面三种查询语句中,from子查询是可以和连表查询相互转换。
-
mysql分页:借助于limit关键字
select 列名称
from 表名
where 条件
group by分组
having 分组统计函数过滤
order by 排序字段
limit 0,3 -
Oracle的分页 :
1.借助于于rownum列,伪列!rownum的起始索引是从1开始的。
2.rownum默认支持< <=、但是不支持>、>=; 也不支持between …and
3.oracle的分页要使用rownum别名 + 子查询的方式select * from (select rownum rn,id,address,order_date from orders) tmp where tmp.rn <= 5 and tmp.rn >= 3
DDL:create table 表名/ view 视图名/index 索引名/sequence 序列名 、drop table/view/index/sequence
DML: inset、update、delete
DCL: commit;rollback;savepoint
创建表的方式:
1.create table 表名(列名 列类型 ,)
2.
create table 表名a
as
select 列名1,列名2 from b表 where 1=2
序列:[sequence]
作用:在oracle中不像mysql一样,oracle只有主键没有自增功能,如果想在oracle中实现主键自增,需要借助于oracle中的序列。
语法:
CREATE SEQUENCE 序列名称
[INCREMENT BY 步长] [START WITH 开始值]
[MAXVALUE 最大值 | NOMAXVALUE]
[MINVALUE 最小值 | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE 缓存个数 | NOCACHE] ;
注意:cache值要小于CYCLE的个数
使用:
序列名.nextval:序列的下一个值
序列名.currval:序列的当前值
在首次使用的时候必须使用序列的nextval属性
视图:【view】
定义:视图实际是一张伪表。是建立在表的基础之上,相当于一面镜子!
作用:数据安全。
语法:
创建:
CREATE [OR REPLACE] VIEW 视图名称
AS
子查询 ;
删除:drop view 视图名称 ;
修改:使用和表一样:update 视图名 set 列名= 列值..where 条件
查询:和查询一张表的语句一样
同义词:【synonym】
CREATE [PUBLIC] SYNONYM 同义词名称 FOR 用户名.表名称 ;
索引:
索引本质是一种数据的组织结构;底层:hash索引、btree索引、b+tree索引
索引可以加快查询效率,
数据表的有些约束是自带了索引的,比如:primary key,unique
create index aaa_index on aaa(username,password,telephone)
语法:
创建:create index 索引名 on 表名(列字段)
普通索引:
联合索引:
删除:drop index 索引名;
什么时候适合创建索引:
1.这表的数据量比较大的时候【最低:上万条】
2.表的查询次数要远远高于增删改的次数
3.要为那些区分度比较高的字段【手机号、身份证号】
4.经常出现在where 查询条件中的列
索引失效的情况:
1.不遵循左前缀原则:
2.like 尽量少用
SQL优化:
1.索引
2.limit 1000,4--->挺低--->where id>=1000 limit 0,4
3.数据库引擎:innodb...
缓存:
4.触发器:
定义:
触发器的定义就是说某个条件成立的时候,触发器里面所定义的语句就会被自动的执行。因此触发器不需要人为的去调用,也不能调用
触发器是由一个事件来启动运行,即触发器是当某个事件发生时自动地隐式执行。并且触发器不能接受参数。所以运行触发器就叫触发或点火(firing),
oracle事件指的是对数据库的表进行的Insert、Update及DELETE操作或对视图进行类似的操作。Oracle将触发器的功能拓展到了触发Oracle。Oracle将
触发器的功能拓展到了触发Oracle,如数据库的启动与关闭等。
触发器的组成:
触发事件:即在何种情况下触发Trigger,例如:Insert、Update、DELETE
触发时间:即该Trigger是在触发事件发生之前(Before)还是之后(After)触发,也就是触发事件和该Trigger的操作顺序。
触发器本身:即该Trigger被触发之后的目的和意图,正是触发器本身要做的事情.
触发频率:说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。
语句级(STATEMENT)触发器:是指当某触发事件发生时,该触发器只执行一次;
行级(ROW)触发器:是指当某触发事件发生时,对收到该操作影响的每一行数据,触发器都单独执行一次。
举例:给每个员工加100的工资,是我们添加完员工之后,更新工工资,只触发一次:语句级触发器
还是我们每添加一个员工,都给每个员工加100,针对每一行的:行级(ROW)触发器
创建触发器:
create [or replace] trigger 名字
before|after
insert|delete|update
on 表名
[for each row]
begin
sql 语句
end;
注意:在Oracle中用:old和:new表示执行前的行,和执行后的行。
案例1:
编写一个触发器,在对表employee记录删除的时候,在emp_back表中备份对应的记录
先创建一个employee表:
create table employee
as
select eid,name from emp
再创建一个emp_back表,内容为空
create table emp_back
as
select eid,name from emp
where 1=2
此时查看:
select * from employee;有数据
select * from emp_back;没有数据
创建触发器:
create or replace trigger delete_emp_trigger
before
delete on employee
for each row
begin
insert into emp_back
values(:old.eid,:old.name);
end;
delete from employee;清空表
select * from employee;没有了数据
select * from emp_back;//发现数据过来了。
案例2:结合序列完成表中字段的值自动增加
create or replace trigger increase_user_id
before
insert on users
for each row
begin
select crm_seq.nextval into :new.id from dual;
end;
当然要提前创建crm_seq序列,:new.id
删除触发器:
drop trigger 触发器名称;
触发器不能被调用哦,只能被触发!
5.存储过程:
1.含义:
oracle提供可以把PL/SQL程序存储在数据库中,并可以在任何地方来运行它。这样就叫存储过程。
存储过程是存在数据库中,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。
2.语法:
定义:
create or replace procedure 存储过程名称
is
begin
Sql语句
end 存储过程名称;
调用:
3.案例1:
1)定义一个没有参数的存储过程,往emp表中插入一行数据:
create or replace procedure p1
is
begin
insert into emp values(1,‘张安’);
end p1;
2)调用:
call p1();
3.案例2:带输入参数的存储过程,这里首先要准备好crm_seq序列:
1)定义:
create procedure p2(newname in varchar2)
is
begin
insert into emp_back values(20,10||newname);
end p2;
2)调用:
call p2(‘李四’);
4.案例3:携带输出参数的存储过程
1)定义:
create or replace procedure p3(newcount out number)
is
begin
– into 关键字 将查询的结果赋值给变量 newcount
select count(*) into newcount from ta;
end p3;
2)调用:
注意:这时候我们就不能使用call p3(4)这种调用方式了,因为此时输出参数是的值是我们想获得的,而不是我们传入的
所以,我们还需要有另外一种调用方式:
调用存储过程p3
declare
newcount number;
begin
p3(newcount);
insert into emp_back values(30,newcount);
end;
当然例2中我们也可以使用这种方式:
declare
newname varchar2(32);
begin
newname := '小红';
p2(newname);
end;