oracle数据库常用语法

save d:/aa.sql保存脚本
sqlplus hr/hr
@d:/aa.sql 加载脚本
varchar2字符类
number数值类
not null约束
unique唯一约束
主外键约束
check检查约束
关键字in（n,n,n,n,n,n,n,n）；可以是多个数据，不止两个
to_char（）是格式化字符串的函数
eg：select to_char(hire_date,'yyyy-mm-dd') from employees;
eg：select to_char(hire_date,'yyyy/mm dd hh24:mi:ss') from employees;这是格式化到当前的年月日
sysdate是得到当前系统时间


select sysdate from employees
select employee_id，sysdate-hire_date as day from employees；这是计算日期的


SQL> select * from employees where employee_id in(select employee_id from employ
ees where salary>1000);子查询


to_char（number|date，[formart]）将字符串改成日期或者数字格式
to_number('',[format])改成数字类型在运算
9：代表任意数字
0：数字。强制存在
$:美元符号
l：本地钱币
，：千位符
. :小数点符号
select to_number('1.125.56','9,999,00')+2000 form dual;
select to_char('3500','l9,999,00')+2000 form dual;
SQL> select to_number('￥1,125.56','L9,999.00')+2000 FROM dual;


TO_NUMBER('￥1,125.56','L9,999.00')+2000
----------------------------------------
                                 3125.56




SQL> select add_months(to_date('2014-1-1','yyyy-mm-dd'),6) from dual;


ADD_MONTHS(TO_
--------------
01-7月 -14
给日期增加几个月份add_months（date，n）
SQL> select add_months(to_date('2014-1-1','yyyy-mm-dd'),6)+10 from dual;


ADD_MONTHS(TO_
--------------
11-7月 -14
last_day（date）查询每个月的最后一天
SQL> select last_day(to_date('2014-1-1','yyyy-mm-dd')) from dual;


LAST_DAY(TO_DA
--------------
31-1月 -14
next_day（date，‘周几’）查询每个月最初的周几
SQL> select next_day(to_date('2014-1-1','yyyy-mm-dd'),'星期一') from dual;


NEXT_DAY(TO_DA
--------------
06-1月 -14


SQL> select last_day(to_date('2011-2-1','yyyy-mm-dd')) from dual;


LAST_DAY(TO_DA
--------------
28-2月 -11


SQL> select last_day(to_date('2011-2','yyyy-mm')) from dual;


LAST_DAY(TO_DA
--------------
28-2月 -11
alter session set nls_date_format='yyyy-mm-dd';动态修改当前的日期格式
SQL> alter session set nls_date_format='yyyy-mm-dd';


会话已更改。


SQL> select sysdate from dual;


SYSDATE
----------
2007-01-29
SQL> select parpamer from dual;查看动态当前参数


SYSDATE
----------
2007-01-
abs（number）；取绝对值得函数
concat（s1,s2）；将s1和s2连接起来
length（varchar2）；计数符长度算
selectlength（hire_date） from employees；


lower（varchar2）；转成小写
select lower（first_name）from employees；


upper（varchar2）；转成大写
select upper（first_name）from employees；


initcap（varchar2）；首字母大写
select initcap（first_name）from employees；


instr（s1,s2,m,n）；从s1的m位置开始查找s2，第n次出现的位置
select instr（‘Alana’，‘l’）from employees；


replace（s1,s2,s3）；将s1中包含的字符串替换成s3
select instr（‘Alana’，‘l’，‘s’）from employees where first_name=‘Alana’；


substr（s1,m,n）；从s1的第m位之开始截取长度为n的字符串
select substr（‘Alana’，1，2）from employees where first_name=‘Alana’；


select * from employees where lower(first_name)='join'；因为首字母大写所以不存在，只能转成小写


nvl（s1,s2）如果s1为空则返回s2反之返回s1;
select nvl（null，‘hi’） from dual；
nvl2（s1,s2,s3,s4）第一个的增强


decode('表达式','if条件',值,'if条件',值,0)相当于if else
select decode('abc','a',100,'abc',50,0) from dual;


在oracle中内连接查询属性时必须注明表名否则报错          起别名时如果是汉字用“”
select employees.salary,employees.employee_id from employees join jobs on employees.job_id=jobs.job_id join departments on employees.department_id=departments.department_id where salary between 6000 and 10000;
或者
select employees.salary,employees.first_name 
from employees，jobs，departments 
where 
employees.job_id=jobs.job_id and employees.department_id=departments.department_id  and salary between 6000 and 10000;hql语句连表查询的方案


外连接
left outter join on
right outter join on
full outter join on
join on或者inner join


自连接表中的一个字段的值来自于另外一个字段
select e.employee_id,e.first_name,m.employee_id as "主管的id",abs(m.salary-e.salary) as "相差的工资"from employees e join employees m on e.manager_id=m.employee_id;


授权scott用户查询hr用户下的hr.departments 表的权限
grant select on hr.departments to scott；
解锁用户
C:\Users\Administrator>sqlplus / as sysdba


SQL*Plus: Release 11.2.0.1.0 Production on 星期一 1月 29 16:15:12 2007


Copyright (c) 1982, 2010, Oracle.  All rights reserved.




连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> alter user scott account unlock;


用户已更改。


SQL> alter user scott identified by tigger;


用户已更改。


SQL> grant select on hr.departments to scott;


授权成功。
事务处理
commit 提交事务
rollback 回滚事务
savepoint 还原点
rollback to 回滚到还原点之前的数据


savepoint ‘abc’；
delect from departments where department_id in（151,152）；
rollback to abc；
增删改查和sql语句一样
select employee_id,salary,job_id from employees where deparmrent_id=520;
update employees set salary=salary*1.5 where salary<(select avg(salary) from employees) and employee_id not in (80,20); 
分组查询最大最小注意
select max(employees.salary),min(employees.salary),departments.department_id,avg(employees.salary),count(salary),department_name from employees join departments on employees.department_id=departments.department_id group by departments.department_id,department_name;


建表级别约束
constraint 约束名 约束类型（字段）


ddl语句约束，规则，主外键等不可以事务回滚
eg.创建组合的唯一约束：name+tel
constraint stu_unique unique (name,tel)组合约束
eg.创建组合的唯一约束：name
classname char(7) unique
create table t_student(
id number(6) primary key,
name varchar2(20) not null,
age number(6) check(age>18),检查约束
sex char(2) default'男',默认约束
tel varchar2(13) not null,不为空约束
constraint stu_unique unique (name,tel)
 );
insert into t_student values(1,'qq2',19,default,'13912345678');
insert into t_student values(4,'张三',19,default,'13912345678');
insert into t_student values(5,'李四',19,default,'13912345678');
insert into t_student values(6,'王五',19,default,'13912345678');
insert into t_student values(7,'孙六',19,default,'13912345678');
insert into t_student values(8,'钱七',19,default,'13912345678');
insert into t_student values(9,'赵八',19,default,'13912345678');
insert into t_student values(10,'王二',19,default,'13912345678');
insert into t_student values(11,'王男',19,'女','13912345678');
insert into t_student values(12,'王难',19,'女','13912345678');
insert into t_student values(13,'王南',19,'女','13912345678');
create table t_class(
id number(6) primary key,
classname char(7) unique
);
insert into t_class values(2015,'网络');
insert into t_class values(2015,'软件');
修改表结构
alter table t_student
add class_id number(8); 


alter table t_student
drop column class_id;删除列
drop table stu_class删除表


create table stu_class(
stu_id number(6),
class_id number(6),
CONSTRAINT FK_stu_id  FOREIGN KEY(stu_id) REFERENCES t_student(id),
CONSTRAINT FK_class_id  FOREIGN KEY(class_id ) REFERENCES t_class(id)
);
用查询到的值用decode函数插
insert into stu_class select id,decode(age,19,2014,21,2015,2013) from t_student;
select count(t_student.id),avg(t_student.age),t_class.classname from t_student inner join stu_class on t_student.id=stu_class.stu_id inner join t_class on stu_class.stu_id=t_class.id  group by t_class.classname;
select count(t_student.id),avg(t_student.age),t_class.classname from t_student，stu_class,t_class where t_student.id=stu_class.stu_id and stu_class.stu_id=t_class.id group by t_class.classname;
create table goods(
id number(6) primary key,
title char(100),
summary varchar2(20),
price number(20),
vip_price number(20),
publish_date varchar2(20),
outdate varchar2(20),
image number(6)
);
create table carts(
id number(6) primary key,
userid number(8),
goodid number(8),
price number(20),
cratedate varchar2(20),
count1 number(6),
constraint userid_fk foreign key(userid) references user1(id),
constraint goodid_fk foreign key(goodid ) references goods(id)
);
create table user1(
id number(6) primary key,
name char(20),
password number(20),
nice_name varchar2(20),
is_vip char(20),
money number(20),
is_qq char(20),
is_wb char(20),
image varchar2(100)
);
insert into goods values(1,'手表','智能手表',2000,1000,'2014-1-1','2015-1-1','d:/imge.1.jsp');
insert into goods values(2,'手机','折叠手机',2000,1000,'2014-1-1','2015-1-1','d:/imge.1.jsp');
insert into goods values(3,'电饭煲','热得快',2000,1000,'2014-1-1','2015-1-1','d:/imge.1.jsp');
insert into goods values(4,'智能手环','保温的',2000,1000,'2014-1-1','2015-1-1','d:/imge.1.jsp');
insert into goods values(5,'电磁炉','温度高',2000,1000,'2014-1-1','2015-1-1','d:/imge.1.jsp');


insert into user1 values(1,'天宇1',123456,b,b,200,'m','m','d:/imge.1.jsp');
insert into user1 values(2,'天宇2',123456,b,b,200,'m','m','d:/imge.1.jsp');
insert into user1 values(3,'天宇3',123456,b,b,200,'m','m','d:/imge.1.jsp');
insert into user1 values(4,'天宇4',123456,b,b,200,'m','m','d:/imge.1.jsp');
insert into user1 values(5,'天宇5',123456,b,b,200,'m','m','d:/imge.1.jsp');


insert into carts values(1,1,1,2000,'2014-1-1',5);
insert into carts values(2,2,2,2000,'2014-1-1',5);
insert into carts values(3,3,3,2000,'2014-1-1',5);
insert into carts values(4,4,4,2000,'2014-1-1',5);
insert into carts values(5,5,5,2000,'2014-1-1',5);
对于多张表存在的值如果查不到他的特定列可以使用视图查询，创建一个视图返回多张表的多个属性可以进行查询，增删改只能影响一张表的值
创建视图
create view check_money1
as
select g.price,us.money from goods g join carts on g.id=carts.goodid join user1 us on us.id=carts.userid;


Select department_id,employee_id,salary,job_id,hire_date 
from employees where salary in (select max(salary) from employees group by department_id);


使用视图
select * from check_money1;
删除视图
drop view check_money1;


select price*count1 as "购物的总价格" from carts;


IN潮流精选 清新日韩 女神最爱 高街欧美 运动休闲 型男潮搭 新超时髦
品牌男装   羽绒服 保暖棉衣 真皮皮衣 真品牛仔 舒适夹克
潮流女装   毛呢大衣 羽绒服 秋冬美裙 棉服 牛仔裤 短外套
时尚女鞋   女靴 短靴 裸靴 长靴 雪地靴 休闲鞋 乐福鞋
流行男鞋   帆布鞋 工装鞋 乐福鞋  增高鞋 小白鞋 休闲鞋
运动  夹克风衣 运动裤  篮球鞋 运动板鞋 运动包
户外 徒步鞋 休闲衣裤 潮溪鞋 户外T恤 军迷服饰


约束的管理
约束的数据字典
user_constraints;
user_cons_columns;
查询t_student中的所有约束
select constraint_name,constraint_type from user_constraints where table_name=upper('t_student');
查询列的所有列的约束
select constraint_name,column_name from user_cons_columns where table_name=upper('t_student');


select ucc.constraint_name,ucc.constraint_type,ucs.column_name  from user_constraints ucs join user_cons_columns ucc on ucc.constraint_name=ucs.constraint_name where table_name=upper('t_student');


禁用约束这是标准语法
alter table 表名 disable constraints 约束名称；
alter table t_student disable constraint sys_c0011055;
alter table t_student disable constraint sys_c0011055 cascade;禁用级联约束
启用约束时数据必须符合要求
alter table t_student enable constraint sys_c0011055;


alter table t_student enable constraint c_d_fk;启用与外键相关的约束


在创建主键约束时并声明删除级联后的设置
on delete cascade 外表的记录级联删除
on delete set null当主表的数据删除时，


alter table carts
drop constraint c_gid_fk;删除外键约束


alter table carts
add constraint c_gid_fk foreign key（good_id） references goods(goodsid) on delete cascade;


使用序列自动增长主键的值，例如sql的自增是事务回滚后序列是不能回滚的,一般在增删改查中使用，但是在where子句中不可使用
创建试图
create squence 序列名 
increment by 步长
start with 开始位置
maxvalue 最大值
cycle|nocycle
cache n|nocache


create sequence usep1
increment by 1
start with 7
maxvalue 9999999999
nocycle
nocache;


使用序列
nextval 伪列 当前序列的下一个值
currval 伪列 当前序列已达到的值
使用序列时当前开始的值要超过能超过已有数据的列的数字的值
insert into goods values(usep1.nextval,'手表','智能手表',2000,1000,'2014-1-1','2015-1-1','d:/imge.1.jsp');


修改序列
alter sequence usep1 increment by 3;


删除序列
drop sequence usep;


查看所有序列：user_squence 数据字典；


查询t_u_sed序列的步长，最小值最大值和当前使用的值
select increment_by,min_value,max_value,last_number from user_squences where sequence_name=upper('t_u_sed');
show error;--显示错误信息
PL/sql结构
<header> is|as
[声明类型]
begin
[程序块]
ecception
[异常处理]
end;
<header>包括函数function，存储过程procedure，包package，参数in输入参数默认的，out输出参数，inout输入输出参数
创建一个函数检查金额是否充足check_money
or replece代表的意思是如果此函数第二次创建则更新，如果不存在则创建
create or replace function check_money(price number,money in number)
return char
as
m_flage char(1):='N';--声明部分，“：=”是变量赋值
begin
--判断金额是否充足
 if price<money then
m_flage:='Y';
 else
  m_flage:='N';
end if;
return m_flage;
end check_money;--必须返回 --/ 表示执行的意思
/
函数的使用方法
select vc.price,u.money,check_money(vc.price,u.money) "是否足够" from goods vc join carts on vc.id=carts.goodid join user1 u on u.id=carts.userid;


create or replace function raise_salary(pid employees.employee_id%type,raise_p number) return number
as
 raise_v number(10,2):=0;
begin
select salary*raise_p
into raise_v
from employees
where employee_id=pid;
return raise_v;
end raise_salary;


select first_name,salary,raise_salary(employee_id,2) "提高工资" from employees where employee_id=176;


jsp/serlet基础