验证中...
语言: SQL
分类: 编程语言基础
最后更新于 2018-06-18 16:53
片段 1 片段 2 片段 3 片段 4 片段 5 片段 6 片段 7 片段 8 片段 9
1.表
原始数据 复制代码
1.创建表
-- 用户信息表
create table users(
user_id varchar2(20) primary key,
user_name varchar2(36) unique not null,
user_password varchar2(36) default ('123456'),
user_realname varchar2(20),
user_email varchar2(36),
user_phonenumber varchar2(36)
)
☆2、视图 (用于面试)
原始数据 复制代码
☆2、视图 (用于面试)
是一张虚拟的表,视图本身(表中的数据)不占用物理空间
因为视图是建立在表之上的,是由一个查询SQL的结果集组成,视图本身就是一个临时的结果集。所以不占用物理空间。
视图本身就是一个查询的SQL语句,但是视图可以当做表来用,优势是可以屏蔽或者隐藏原始表的部分列,或者扩展原始表的列,视图的主要功能就是查询。
Eg:
create or replace view view_name as select*from table;
CREATE OR REPLACE VIEW SELECT_message AS SELECT EMP.*, DEPT.DNAME FROM EMP LEFT JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;
视图可以DML操作么?
答:视图可以分简单视图,也可以分复杂视图。
简单视图:由一张表或多张表的简单连接,组成的视图;简单视图如果不违反底层原始表的约束,就可以DML操作。但是简单视图,通常也不应DML操作,简单视图可以通过 with read only 设置为只读。
复杂视图:只要有聚合或者分组,组成的视图;复杂视图不支持增删改。
物化视图:会占用物理空间
☆3、索引 (用于面试)
原始数据 复制代码
☆3、索引 (用于面试)
为了提高查询速度,通常建立表的时候,在频繁使用的列上,建立索引,索引可以有效的大幅度提高SQL的查询速度,特别是当数据量比较大的时候。
由于建立表的时候,主键有默认的索引,所以说,通过主键查询速度非常的快。
索引非常类似于书的目录,索引也是数据,索引也是个表,索引也占用物理空间,物理上分布在Oracle的索引段上。
优劣点:当数据库的记录比较小的时候,不建议使用索引,数据量比较小,索引反而会降低SQL查询速度。(10万以下的数据不建议用索引)
优缺点:
1.索引提高查询速度,但是占用物理空间。索引不是越多越好,在频繁的使用列作为条件查询才更有效。数据量比较小,索引反而会降低查询速度。
2.索引会降低DML操作 的速度,因为每次增删改都要修改索引。
索引是SQL查询的时候表自动使用,不需要手动使用,但是需要手工建立索引。
语法:
create index 索引名 on 表名(表中的列名)
Oracle的主要几种索引类型:
1.B·Tree索引(默认索引)多子数
2.位图索引(bitmap) 适合在列的值重复性比较高的情况下
3.基于函数的索引 如果对索引列进行函数操作,索引会失效,如果需要频繁函数查询,应该为函数本身建立索引
4.反向键索引 1001,1002,1003,1004,1005——>1001,2001,3001,4001,5001 变成平衡树
SQL优化
原始数据 复制代码
SQL优化
如何让你的查询SQL语句更加合理有效的使用索引,而进行优化
1.避免使用 * , * 是代表所有的列,而且 * 需要解析
2.表要使用别名
3.where条件的顺序:多表连接的时候,连接条件要写在 where 的首位,或者是紧跟在 where 之后,然后在 and 具体的条件。
Eg:select *from A,B where A.id=B.id and B.sno=0210;
把最大程度上一次性能缩小范围的条件写在 where 右边
4.避免在有索引的字段上,进行函数操作
5.like语句通常不使用索引,只有一种情况使用索引,那就是右边模糊(比如: ABC%),其他模糊都不适用索引
6.尽量不使用“<>”,“!= ”这两种不适用索引
7.在设计表时,索引段 设置为 not null ,B·Tree索引忽略空值
8.使用 Union All(全部合并) 代替 Union(Union就是去重复)
9.使用Exists 代替 in (注意:不同的情况,不一定)
EG:
select *from emp e where e.id in (select id from job_history);
select *from emp e where exists (select id from job_history j where j.id=e.id); --子语句 返回true
如果两表数据很小,就用in
如果两表数据都很大,且A表小于B表的数据量,要是查询A表在B表有多少时,就用exists
4、序列 5、同义词 6、游标
原始数据 复制代码
4、序列
Oracle中没有自动增长的关键字,但是Oracle中有序列可以实现自动增长。
创建一个序列:create sequence 序列名
序列有两个函数:.nextval (下一个) .currval (当前)
EG:
insert into dept values (sep_empldm.nextval,'cehuabu','China') ;
5、同义词
6、游标
☆7、存储过程 (用于开发) 简单说就是利用数据库来进行编程
原始数据 复制代码
☆7、存储过程 (用于开发) 简单说就是利用数据库来进行编程
1.存储过程是一个有名字的PLSQL块
2.存储过程可以存储在数据库端,存储过程可以被应用程序调用(通过名字调用)
3.存储过程类似于Java中的方法,可以传参,可以返回数据
存储过程在被调用之前,必须首先预编译,存储过程预编译在数据库端,所以存储过程是一个数据库对象。
创建一个存储过程:
create or replace procedure 过程名(传参 in 参数类型,传参 out 参数类型)
is -- 定义变量
begin
-- 执行流程
end;
-- 代码写完后,按F8编译,编译通过后,关闭SQL窗口,在左边的文件中,找到Procedures文件中自己创建的存储过程
-- 点击右键,点recompile,成功后,在右击点test,按F8
eg:
create or replace procedure proc_name(v_x in number,v_y in number,v_z out number)
is
-- 定义变量
begin
v_z := v_x+v_y;
end;
什么是存储过程?
存储过程是一个预编译的SQL语句集,存储在数据库端,可以被Java应用程序调用。
因为是预编译,所以速度相对于比较快。
一般情况如果应用程序频繁的和数据库交互,且业务复杂,可以把复杂的业务逻辑写在存储过程中。
缺点是,每种数据库都有不同的存储过程写法,所以存储过程依赖于数据库,可移植性差
-- 编写一个存储过程,实现计算器的加减乘除,参数,两个操作 和结果 还有一个符号
create or replace procedure pro_calculator_ldm (v_a in number , v_b in number , v_y out number,v_symbol in varchar2,v_message out varchar2)
is
number_has_zero exception;
error_symbol exception;
begin
if v_symbol ='+' then
v_y := v_a + v_b;
elsif v_symbol ='-' then
v_y := v_a - v_b;
elsif v_symbol ='*' then
v_y := v_a * v_b;
elsif v_symbol ='/' then
if v_a = 0 or v_b = 0 then
raise number_has_zero;
end if;
v_y := v_a / v_b;
else
raise error_symbol;
end if;
exception
when error_symbol then
v_message := '运算符号错误!此计算器只能进行加减乘除。';
when number_has_zero then
v_message := '操作数不能为0!';
when others then
dbms_output.put_line(substr(sqlerrm,0,200));
end;
注意1:Oracle存储过程不能直接写DDL语句,如何执行DDL语句?
-- Oracle 执行DDL语句
create or replace procedure pro_test
as
begin
-- 把一个字符串当做SQL来执行
execute immediate 'truncate table emp2';-- 删掉emp2 表
end;
注意2:Oracle中DML语句需要手动提交或者回滚,所以说在存储过程中,不能忘记commit 和 rollback
Java如何调用存储过程呢?
答:使用CallableStatement 调用存储过程,SQL:{call 存储过程的名字(?,?,?)}
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
public class Test {
public static void main(String[] args) throws Exception{
Class.forName("oracle.jdbc.driver.OracleDriver");
//加载MySQL的固定语句
Connection conn = null;
conn = DriverManager.getConnection("jdbc:oracle:thin:@10.8.0.211:1521:orcl","hr","admin");
/*
* 如何调用存储过程
*/
CallableStatement callStatement =conn.prepareCall("call pro_calculator_ldm(?,?,?,?,?)");
//注册参数
callStatement.registerOutParameter(3, java.sql.Types.NUMERIC);
callStatement.registerOutParameter(5, java.sql.Types.VARCHAR);
callStatement.setObject(1, 10);
callStatement.setObject(2, 10);
callStatement.setObject(4, "*");
callStatement.execute();
Integer i=callStatement.getInt(3);
String result =callStatement.getString(5);
System.out.println(i);
conn.close();
}
}
8、触发器
原始数据 复制代码
8、触发器
由系统或者表自动调用的存储过程,成为触发器。
触发器分类:
1.系统级触发器
2.DDL触发器
3.替代触发器
4.DML触发器(重点)
例如:1.当表中的记录被删除的时候,自动备份;
2.实现MySQL中自动增长,当插入数据的时候,自动调用序列。
DML触发器的分类:
1.按影响结果分:表级触发器(语句级)和 行触发器(for each row)
2.按触发的时机分:before 和 after
eg:
-- 删除前备份
create or replace trigger tr_bak
before delete on emp
for each row
begin
insert into emp_bak values(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno); -- :old 即将删掉的一行
end;
触发器中是否可以提交事务?(commit 、rollback)
答:不可以,因为事务的提交或回滚是由SQL语句决定的,不能由触发器决定,例如:不小心删除了数据,客户是要回滚的。
如果在触发器中提交了,逻辑上就矛盾了。
如何获取触发器中即将插入的数据和删除的数据?
:new 表示即将插入的数据
:old 表示即将删除的数据
-- 练习: 使用触发器 完成自动增长
-- 创建一个表
create table animal (
id number,
name varchar2(255),
age integer
);
-- 创建一个序列
create sequence seq_animal;
-- 创建一个触发器
create or replace trigger tr_animal_insert
before insert on animal
for each row
begin
select seq_animal.nextval into :new.id from dual;
end;
-- 插入数据
insert into animal(name,age) values ('cat',2);
insert into animal(name,age) values ('dog',1);
insert into animal(name,age) values ('bird',3);
insert into animal(name,age) values ('tiger',8);
insert into animal(name,age) values ('fish',1);
insert into animal(name,age) values ('pig',4);
select* from animal;
9、函数
原始数据 复制代码
9、自定义函数
函数(function)是一个带有返回值(return 关键字)的存储过程
create or replace function 函数名(参数 参数类型) return 返回类型
is
begin
end;
-- 输入用户的编号,返回用户的名字
create or replace function fun_name(v_id emp.empno%type) return varchar
is
v_result emp.ename%type;
begin
select ename into v_result from emp where emp.empno=v_id;
return v_result;
end;
--
select fun_name(20) from dual;
-- 练习: 编写一个函数,输入一个部门编号,返回一个部门名称
create or replace function fun_department_name(v_id departments.department_id%type) return varchar2
is
v_result departments.department_name%type;
begin
select d.department_name into v_result from departments d where d.department_id=v_id;
return v_result;
end;
select e.*,fun_department_name(e.department_id) department_name from employees e;
作业
原始数据 复制代码
1.项目实战(人力资源系统的增删改查)
2.做一个小的游戏 比如 贪吃蛇(面向对象,集合)
3.好好看书
4.转账的存储过程编写完成
5.做卷子(特别是数据库的卷子)

评论列表( 0 )

你可以在登录后,发表评论

11_float_left_people 11_float_left_close