MySQL数据源
driver: com.mysql.jdbc.Drvier
url: jdbc:mysql://localhost:3306/[database]
username:root
password:root
Oracle数据库源
driver: oracle.jdbc.OracleDriver
url: jdbc:oracle:thin:@localhost:1521:orcl
username: zhangsan
password: zhangsan
Oracle分组函数
distinct(去重)
count()统计数据条数
min()最小值
max()最大值
avg()平均值
sum()求和
分组统计
group by
分组统计条件限定查询
having
exists使用
in关键字少使用,可以使用exists来代替性能很高
exists()【存在】
not exists()【不存在】
并集union和union all
union去重数据
union all 不去重复数据
数据库的变更
insert into 表名(列名) values (值)
update 表名 set 列名1=值1,…… where 修改条件
delete from 表名 where 删除条件
数据库事务
增删改操作都需要开启事务
commit 【提交】
rollback【回滚】事务在没有提交前可以回滚
select * from empty for update 带着事务的查询【for update 带着事务的查询】
Oracle的DDL
数据类型
varchar,varchar2 字符串类型
number 数值类型
data 日期类型
CLOB 大文本数据类型,可存4G
BLOB 二进制数据,可存4G
建表
Create table 表名(
字段1 数据类型 [default 默认值],
……
字段n 数据类型 [default 默认值]
);【create 创建、table 表格、default 默认值】
删表
drop table 表名【drop 除名】
表的修改
添加语法:
alter table 表名称 add(列名1 数据类型 [default 默认值],列名2 数据类型 [default 默认值]……);【alter 改动、add 添加】
修改语法:
alter table 表名称 modify(列名1 数据类型 [default 默认值],列名1 数据类型 [default 默认值]……);【alter 改动、modify 修改】
截断表
truncate table 表名;(可以立即释放资源不需要提交commit)【谨慎使用】
约束
(使用约束可以保证数据的完整性)
主键约束
(主键约束都是在ID上使用,而且本身已经默认了内容不能为空,可以在建表的时候指定。)
create table 表名(
列名1 数据类型 primary key,(主键是唯一的,不能为空)【primary key 主键约束】
列名2 数据类型,
列名2 数据类型
);
自定义主键名称
create table 表名(
列名1 数据类型,
列名2 数据类型,
列名2 数据类型,
constraint 自定义主键名称(PK) primary key(指定列为主键,列名)【constraint 约束条】
);
非空约束
create table 表名(
列名1 数据类型,
列名2 数据类型 not null,【not null 非空约束】
列名2 数据类型,
constraint 自定义主键名称(PK) primary key(指定列为主键,列名)【constraint 约束条件、primary key 主键约束】
);
唯一约束
create table 表名(
列名1 数据类型,
列名2 数据类型 unique,【unique 唯一】
列名3 数据类型
);
自定义唯一约束名称
create table 表名(
列名1 数据类型,
列名2 数据类型,
列名3 数据类型,
constraint 自定义唯一约束名称(UK)unique (指定列名唯一约束)【constraint 约束条件 、unique 唯一】
);
检查约束
(检查约束可以约束字段值得合法范围,创建一张表性别只能是1或2)
create table 表名(
列名1 数据类型,
列名2 数据类型 check (列名2 in(1,2)),【check 检查】
列名3 数据类型
);
自定义检查约束名称
create table 表名(
列名1 数据类型,
列名2 数据类型,
列名3 数据类型,
constraint 自定义检查约束名称(CK)check(指定列名 in(1,2))【constraint 约束条件、check检查】
);
外键约束
(主键约束、非空约束、唯一约束、检查约束都是单表约束,外键约束是两张表的约束,可以保证关联数据的完整性。)
商品表名
create table orders(
orders_id number(4),
totalPrice number(7.2),
order_time date,
constraint orders_orders_id_pk primary key(orders_id)
);
明细表
create table order_detail(
detail_id number(4),
item_name varchar2(10),
price number(7.2),
quantity number(4),
orders_id number(4),
constraint order_detail_detail_id_pk primary key(detail_id),
constraint order_detail_orders_id_fk foreign key(orders_id) references orders(orders_id)
);【constraint 约束条件、foreign key 外键约束、references 参照】
注意:
1、子表插入时有外键约束,主表删除数据时也有外键约束,如果想要删除需要先删除字表的数据在删除主表。
2、主表删除时受外键的约束,需要先删除字表在删除主表。
可以强制删除表:
drop table orders cascade constraint;(不建议使用)
删除主表数据可以删除字表的关联数据,在删主表,也可以使用级联删除级联删除在外键约束上要加上 on delete cascade 如:
constraint order_detail_orders_id_fk foreign key(orders_id) references orders(orders_id) on delete cascade
这样删除主表数据的时候会把子表的关联数据一同删除
商品表
create table 表名(
列名1 数据类型,
列名2 数据类型,
列名3 数据类型,
constraint 自定义商品表名主键名称 primary key(指定列名为主键)
);
明细表
create table 表名(
列名1 数据类型,
列名2 数据类型,
列名3 数据类型,
列名4 数据类型,
列名5 数据类型,
constraint 自定义明细表主键名称(PK) primary key(指定列名为主键),
constraint 自定义外键约束名称(FK) foreign key(指定明细表列名) references 商品表名(商品表主键) on delete cascade
);【constraint 约束条件、primary key 主键约束 、foreign key 外键约束、references 参照、on delete cascade 级联删除】
rownum和分页
【rownum 行号】(rownum 不支持大于号)
select rownum, * from 表名
分页步骤
第一步查询全量的数据
select * from emp;
第二步以第一步的结果集作为一张表,限定条件是rownum小于结束行号,结果列把rownum作为结果集
select rownum rw, a.* from (
select * from emp
) a where rownum < 6;
第三步以第二步的结果集作为一张表,限定条件是第二步的rownum列大于开始行号,结果列是*
select * from (select rownum rw, a.* from (
select * from emp
) a where rownum < 6 ) b where b.rw > 0;
【已知条件
pageNO:当前的页码
pageSize:每页记录数
未知条件
startNum:开始行号
endNum:结束行号
pageNo pageSize startNum endNum
1 5 0 6
2 5 5 11
3 5 10 16
startNum = (pageNo - 1)* pageSize;
endNum = pageNo * pageSize + 1;
】
视图view
create view 视图名 select * from 表名 where 条件;
创建视图或覆盖视图
create or replace view 视图名 select * from 表名 where 条件;
修改视图,视图不建议修改
update 视图名 set 修改的值 where 修改的条件;
创建只读的视图
create or replace view 视图名 as select * from 表名 where 条件 with read only;
序列
语法:create sequence 序列名
[increment by n]
[start with n]
[{maxvalue/ minvalue n|nomaxvalue}]
[{cycle|nocycle}]
[{cache n|nocache}];
范例:创建一个seqpersonid的序列,验证自动增长的操作
create sequence seqpersonid;(序列虽然是给某个表使用,但是序列并没有绑定自某一张表,任何一张表使用这个序列都可以。)
查询序列的下一个值(重点)
select seqpersonid.nextval from dual;
查看当前值是多少
select seqpersonid.currval from dual;
创建单列索引
单列索引是基于单个列所建立的索引
create index 索引名 on 表名(列名);
创建复合索引
复合索引是基于两个列或多个列的索引,查询数据使用的时候是有索引顺序的
create index 索引名 on 表名(列名1,列名2);
(索引使用原则:
1、在大表上建立索引才有意义。
2、在where子句后面或者是连接条件上建立索引。
3、索引的层次不要超过4层。
)
pl/sql
程序语法:
declare
说明部分 (变量说明,游标申明,例外说明)
begin
语句序列 (dml语句)……
exception
例外处理语句
end;
常量和变量定义
变量的基本数据类型就是 Oracle 中的建表时字段的变量(char,varchar2,date,number,Boolean,long)
变量定义:
varl char(15);
psal number(9.2);(赋值 psalm := "null")
常量定义:
married Boolean:=true;
基本数据类型变量和常量
declare
pname carchear2(10);变量定义
age number(3):= 20;常量定义
begin
pname := 'zhangsan';
dbms_output.put_line(pname);
dbms_output.put_line(age);
exception
end;
引用数据类型变量
myname emp.ename%type;【变量名 表名.列名%type】
引用型变量,即 my_name 的类型与 emp 表中 ename 列的类型一样
在SQL中使用 into 来赋值(重点)
declare
pname myemp.ename%type;【变量名 表名.列名%type】
begin
select t.ename into pname from myemp t where t.empno=7369;【把 t.ename into 赋值给pname 】
dbms_output.put_line(pname);
exception
end;
记录类型变量
emprec emp%rowtype;【变量名 表名%rowtype】
declare
prec myemp%rowtype;
begin
select * into prec from myemp t where t.empno = 7369;
dbms_output.put_line(prec.ename || ' ' || prec.sal );
end;
PLSQL的if分支
语法1:
if 条件 then 语句1;
语句2;
end if;
语法2:
if 条件 then 语句序列1;
else 语句序列2;
end if;
语法3:
if 条件 then 语句;
else 条件 then 语句;
else 语句;
end if;
经商不灵活,门前客不多,见人三分笑,客人跑不掉
语法1示例:
declare
pno number(4) := #
beign
if pno < 5
dbms_output.put_lin('编号小于5');
end if;
end;
语法2示例:
declare
pno number(4) := #
begin
if pno = 1 then
dbms_output.put_line('我是1');
else
dbms_output.put_line('我不是1');
end if;
end;
语法3示例:
declare
pno number(4) := #
begin
if pno = 1 then
dbms_output.put_line('我是1');
elsif pno = 2 then
dbms_output.put_line('我是2');
else
dbms_output.put_line('其他');
end if;
end;
PLSQL的循环loop
语法1:
while total <=25000 loop
...
total := total+salary;
end loop;
语法2:(最常用的循环方式)
loop
exit(when 条件);
... ...
end loop;
语法3:(适用于连续的数值遍历)
for i in 1..3 loop
语句序列;
end loop;
语法1示例:
declare
total number(4) := 0;
beign
while total < 100 loop
total := total + 1;
dbms_output.put_line(total);
end loop;
exception
end;
语法2示例:
declare
total number(4) := 0;
beign
loop
exit when total < 100;
total := total +1;
dbms_output.put_line(total);
end loop;
exception
end;
语法3示例:
declare
total number(4) := 0;
beign
for total in 1..100 loop
dbms_output.put_line(total);
end loop;
exception
end;
游标cursor
语法:
cursor 游标名 [(参数名 数据类型,参数名 数据类型,……)] is select 语句;
列如:cursor c1 is select ename from emp;
使用步骤:
打开游标: open c1(打开游标执行查询)
取一行游标的值: fetch c1 into pjob;(取一行到变量中)
关闭游标: close c1;(关闭游标释放资源)
游标的结束方式: exit when c1%notfound
注意: 上面的pjob必须与emp表中的job列类型一致
定义:pjob emp.empjob%type;
使用游标方式输出emp表中的员工编号号姓名
declare
cursor c1 is select * from emp;--定义游标
prec emp%rowtype;--定义记录型的变量
beign
open c1;--打开游标
loop
fetch c1 into prec;--从游标中取值,取值后游标会自动向下移动一步
exit when c1%ontfund;
dbms_output.put_line(prec.empno || ' ' ||prec.ename);
end loop;--结束循环
close c1;--关闭游标
exception
end;
按员工的工种涨工资,总裁涨1000元,经理涨800元,其他人员涨400元。
declare
cursor c1 select * from myemp;
prec myemp%rowtype;
addsal(4);
beign
open c1;
loop
fetch c1 into prec;
exit when c1%notfound;
if prec.job = 'PRESIDENT' then
addsal :=1000;
elsif prec.job = 'MANAGER' then
addsal := 800;
else addsal := 400;
end if;
update myemp t set t.sal = t.sal + addsal where t.deptno = prec.deptno;
end loop;
close c1;
commit;
exception
end;
带有参数的游标,写一段PL\SQL程序,为部门号为10的员工涨工资。
declare
cursor c1(dno myemp.deptno%type) is select * from myemp t where t.deptno = dno;【t.deptno要等于游标c1 (dno)传过来的参数】
prec myemp%rowtype;
beign
open c1(10);--打开带有参数游标,除了此处其他地方游标都不用带参数
loop
fetch c1 into prec;
exit when c1%notfound;
update myemp t set t.sal = t.sal + 1000 where t.empno =prec.empno;
end loop;
close c1;--关闭游标
commit;--提交
exception
end;
例外:
no_data_found (没有找到数据)
too_many_rows (select ...into 语句匹配多个行)
zero_divide (被零除)
value_error (算术或转换错误)
timeout_on_resource (在等待资源是发生超时)
--查询部门编号是50的员工
declare
prec emp%rowtype;
cursor c1 is select * from emp r where e.deypno = 50;
no_data exception;--异常类型的定义
begin
open c1;
loop
fetch c1 into prec;
if c1%notfound then
raise no_data;--抛出异常
end if;
end loop;
close c1;
exception
when no_data then--处理异常
dbms_output.put_line('没有员工');
when others then
dbms_output.put_line('其他异常');
end;
存储过程procedure
创建存储过程语法:
create [or replace] procedure 存储过程名 [(参数名 in/out 数据类型)]
as
begin
PLSQL子程序体;
end;
或者
create [or replace] procedure 存储过程名 [(参数名 in/out 数据类型)]
is
begin
PLSQL子程序体;
end 存储过程名;
范例:
create or replace procedure helloworld is
begin
dbms_output.put_line('helloworld');
end helloworld;
调用存储过程
在PLSQL中调用存储过程
begin
--Call the procedure
hellloworld;
end;
范例2:给指定的员工涨100工资,并打印出涨前和涨后的工资
create or replace procedure addsal(pno in myemp.empno%type) as
prec myemp%rowtype;--定义变量
begin
select * into prec from myemp t where t.empno = pno;
update myemp t set t.sal = t.sal+100 where t.empno = pno;--事务的提交要在调用端做
dbms_output.put_line('涨工资前是:' || prec.sal || '涨工资后是:'|| (prec.sal + 100));
end;
范例3:计算指定员工的年薪
create or replace procedure countYsal(pno in emp.empno%type, ysal out number) is
psal emp.sal%type;
pcomm emp.comm%type;
begin
--多个值得赋值是按照先后顺序来赋值的
select t.sal, t.comm into psal, pcomm from emp t where t.empno = pno;
ysal := psal*12 + nvl(pcomm,0);
end countYsal;
PLSQL程序调用存储过程
--通过pl/SQL程序调用存储过程
declare
ysal number;
begin
countYsal(7369, ysal);
end;
function 存储函数
语法:
create or replace function 函数名(name in type,name in type,…)return 数据类型 is
结果变量 数据类型;
begin
return(结果变量);
end 函数名;
示例:
create or replace function countysalf(pno in emp.empno%type)return number is
Result number;
psal emp.sal%type;
pcomm emp.comm%type;
begin
select t.sal, t.comm into psal, pcomm from emp t where t.empno = pno;
Result := psal * 12 + nvl(pcomm, 0);
return(Result);
end countysalf;
存储过程和存储函数的区别
一般来讲,过程和函数的区别在于函数可以有一个返回值;而过程没有返回值。
但是过程都可以通过 out 指定一个或多个输出参数。我们可以利用 out 参数,在过程中实现 返回多个值。【建议使用存储过程】
java代码调存储过程和存储函数
触发器trigger
触发器可用于
数据确认
实施复杂的安全性检查
做审计,跟踪表上所做的数据操作等
数据的备份和同步
出发器的类型
语句级触发器:在指定的操作语句操作之前或之后执行一次,不管这条语句影响了多少行。
行级触发器(for each row):触发语句作用的每一条记录都被触发。在行级触发器中使用 old 和 new 伪记录变量,识别值得状态。
语法:
create [or replace] trigger 触发器名
{before | after}
{delete | insert | update [of 列名]}
on 表名
[for each row [when (条件)]]
begin
plsql 块
end 触发器名;
语句级触发器
范例:插入员工后打印一句话“员工插入成功”
create or replace trigger 触发器名
beofre insert on 表名
begin
dbms_output.put_line('一条记录被插入');
end 触发器名;
insert into person values(seqpersonid.nextval, '王五', 1, sysdate);
范例:不能再休息时间插入员工
create or replace trigger 触发器名
before insert on person
declare
cruday varchar2(10);--定义字符串
begin
select to_char(sysdate, 'day') into cruday from dual;
if cruday in '星期三' then
raise_application_error(-20001, '星期三不允许插入数据');--定义提示错误
end if;
end 触发器名;
触发语句 :old :new
insert 所有字段都是空(null) 将要插入的数据
update 更新以前该行的值 更新后的值
delete 删除以前该行的值 所有字段都是空(null)
行级触发器
范例:判断员工涨工资之后的工资的值一定要大于涨工资之前的工资
create or replace trigger 触发器名
before update of sal on myemp
for each row --行级触发器条件
begin
if :new .sal <= old.sal then
raise_application_error(-20002, '涨后的工资不能比涨前的低');
end if;
end 触发器名;
update myemp t set t.sal = t.sal + 100 where t.empno = 7369;
数据库建模