oracle--基本语法

基本语法

用户管理

• –新建用户
  create user 用户 identified by 密码;
• –解锁/锁定 用户
  alter user 用户 account lock/unlock;
• –修改密码
  alter user 用户名 identified by 新密码;
• –赋权
  grant select on 表名 to 用户;
• –删除用户
  drop user 用户;

表管理

• –建表
  create table stuInfo(
sid int primary key , --主键约束
sname varchar2(10) not null, --非空约束
sex char(2) check(sex in('男','女')), --检查约束
age number(3,1) constraint ck_stuInfo_age check(age>10 and age<100),
tel number(15) unique not null, --唯一约束，
address varchar2(200) default '大魔王' --默认值
)
• –表操作
  –加字段
  alter table 表名 add 字段名 类型
例：alter table classInfo add status varchar2(10) default '哈';
  –修改字段类型
  alter table 表名 modify 字段名 类型
例：alter table classInfo modify status number(1);
  –修改字段名
  alter table 表名 rename column 旧字段名 to 新的字段名
例：alter table classInfo rename column cname to 班级名;
  –删除字段
  alter table 表名 drop column 字段名
例：alter table classInfo drop column status ;
  –修改表名
  rename 旧表名 to 新表名;
rename classInfo to 班级信息；
  –删除表
  drop table 表名；–删除表结构和内容
  truncate table classInfo;–截断表,全量删除内容，效率高，无法ROLLBACK
  delete 表名 [where 条件];–条件删除，可以ROLLBACK
  –增加数据语法
  insert into 表名[(列名，....)] values (对应的数据的值）
  –删除的语法
  delete 表名 [where 条件];
  –修改记录的语法
  update 表名 set [字段='值' ] [where 条件];
  –添加多个时可以使用序列
  —-用序列来做自动增长
  create sequence seq_classInfo_cid start with 1001 increment by 1;
insert into classInfo values(seq_classInfo_cid.Nextval,'七班','未毕业');
insert into classInfo values(seq_classInfo_cid.Nextval,'八班','未毕业');
insert into classInfo values(seq_classInfo_cid.Nextval,'九班','未毕业');
insert into classInfo values(seq_classInfo_cid.Nextval,'十班','未毕业');
  –创建新表，并从另一个表取数
  create table newTable as select cname,cid from classInfo;
create table newTable1 as select *from classInfo;

高级操作

查询语法

• –简单查询
  select *from emp;
select empno as id,ename as name from emp;
select empno 编号,ename 姓名 from emp;
• –去除重复
  select distinct job,deptno from emp;
• 字符串连接
  select '员工编号是'||empno||'姓名是'||ename from emp;
• –区间查询
  select *from emp where sal>1500 and sal<3000;–(1500,3000)
  select *from emp where sal between 1500 and 3000; –[1500,3000]
• –时间区间
  select *from emp where hiredate between to_date('1981-01-01','yyyy-MM-dd') and to_date('1981-12-31','yyyy-MM-dd');
• –模糊查询
  select *from emp where ename like '_M%'; --第2个字母为M的
select *from emp where ename like '%M%';
select *from emp where ename like '%%'; --全查询
• –不等号的用法
  select * from emp where empno !=7369;
select *from emp where empno<> 7369;
• –对结果集排序
  select *from emp order by sal asc;–asc 升序,desc 降序
  select *from emp order by sal desc,hiredate desc;–当sal相同时就按hiredate来排序
• –变成大写
  select upper('smith') from dual;
• –变成小写
  select lower('SMITH') from dual;
• –首字母大写
  select initcap('smith') from dual;
• –连接字符串
  select concat('jr','smith') from dual; –只能在oracle中使用
  select 'jr' ||'smith' from dual;–推荐使用
• –截取字符串
  select substr('hello',1,3) from dual; –索引从1开始
• –获取字符串长度
  select length('hello') from dual;
• –字符串替换
  select replace('hello','l','x') from dual; --把l替换为x

通用函数

数值函数

• –四舍五入
  select round(12.234) from dual;–取整的四舍五入 12
  select round (12.657,2) from dual; –保留2位小数
  select trunc(12.48) from dual;–取整
  select trunc(12.48675，2) from dual; –保留2位小数
• –取余
  select mod(10,3) from dual;--10/3取余 =1;

日期函数

• –日期-数字=日期 ,日期+数字=日期, 日期-日期=数字

• –查询员工进入公司的周数
  select ename,round((sysdate -hiredate)/7) weeks from emp;

• –查询所有员工进入公司的月数
  select ename,round(months_between(sysdate,hiredate)) months from emp;
• –求三个月后的日期
  select add_months(sysdate,6) from dual;
select next_day(sysdate,'星期一') from dual; –下星期
  select last_day(sysdate) from dual;–本月最后一天
  select last_day(to_date('1997-1-23','yyyy-MM-dd')) from dual;
• –转换函数
  select ename ,
to_char(hiredate,'yyyy') 年，
to_char(hiredate,'mm')月，
to_char(hiredate,'dd') 日
from emp;
select to_char(10000000,'$999,999,999') from emp;
select to_number('20')+to_number('80') from dual; –数字相加
• –查询员工年薪
  select ename,(sal*12+nvl(comm,0)) yearsal from emp; –空和任何数计算都是空
• –Decode函数，类似if else if (常用）
  select decode(1,1,'one',2,'two','no name') from dual;
• –查询所有职位的中文名
  select ename, decode(job,
'CLERK',
'业务员',
'SALESMAN',
'销售',
'MANAGER',
'经理',
'ANALYST',
'分析员',
'PRESIDENT',
'总裁',
'无业')
from emp;
select ename,
case
when job = 'CLERK' then
'业务员'
when job = 'SALESMAN' then
'销售'
when job = 'MANAGER' then
'经理'
when job = 'ANALYST' then
'分析员'
when job = 'PRESIDENT' then
'总裁'
else
'无业'
end
from emp;

多表查询

• –简单多表
  select *from dept;
select *from emp,dept order by emp.deptno;
select *from emp e,dept d where e.deptno=d.deptno;
select e.*,d.dname from emp e,dept d where e.deptno=d.deptno;
• –外连接
  select *from emp order by deptno;
  –查询出每个部门的员工
  /*
  分析：部门表是全量表，员工表示非全量表，
  在做连接条件时，全量表在非全量表的哪端，那么连接时全量表的连接条件就在等号哪断
  */
  –左连接
  select * from dept d,emp e where d.deptno=e.deptno(+) order by e.deptno;
  –右连接
  select * from emp e，dept d where e.deptno(+)=d.deptno order by e.deptno;

—————————–作业
–查询与smith相同部门的员工姓名和雇佣日期
select *from emp t
where t.deptno= (select e.deptno from emp e where e.ename=’SMITH’)
and t.ename<> ‘SMITH’;

–查询工资比公司平均工资高的员工的员工号，姓名和工资
select t.empno,t.ename,t.sal
from emp t
where t.sal>(select avg(sal) from emp);

–查询各部门中工资比本部门平均工资高的员工号，姓名和工资
select t.empno,t.ename,t.sal
from emp t, (select avg(e.sal) avgsal,e.deptno from emp e group by e.deptno) a
where t.sal>a.avgsal and t.deptno=a.deptno;

–查询姓名中包含字母u的员工在相同部门的员工的员工号和姓名
select t.empno,t.ename from emp t
where t.deptno in( select e.deptno from emp e where e.ename like ‘%U%’)
and t.empno not in ( select e.empno from emp e where e.ename like ‘%U%’) ;

–查询管理者是king的员工姓名和工资
select t.ename,t.sal from emp t
where t.mgr in
(select e.empno from emp e where e.ename=’KING’);

---

—sql1999语法
select *from emp join dept using(deptno) where deptno=20;
select *from emp natural join dept;
select *from emp e join dept d on e.deptno=d.deptno;
select *from dept;
select *from dept d left join emp e on d.deptno=e.deptno;
select *from dept d,emp e where d.deptno=e.deptno(+);

—分组
select count(empno) from emp group by deptno;
select deptno,job,count(*) from emp group by deptno,job order by deptno;
select *from EMP for UPDATE;

–group by 后面有的字段，select后才可以有，group by后面没有的字段，select后面绝对不能有
select d.dname, d.loc, count(e.empno) from emp e, dept d where e.deptno = d.deptno group by d.dname, d.loc ;

---

–子查询
select *from emp t where t.sal>(select *from emp e where e.empno=7654);

select rownum ,t.* from emp t where rownum <6 ;

–pagesize 5
select from(select rownum rw,a. from (select *from emp ) a where rownum <16) b where b.rw>10;
select *from (select *from emp) where rownum>0;

–索引
create index person_index on person(p_name);

–视图
create view view2 as select *from emp t where t.deptno=20;
select *from view2;

---

–pl/sql

–plsql是对sql语言的过程化扩展

declare
begin
dbms_output.put_line(‘hello world’);

end;

declare
age number(3);
marry boolean := true; –boolean不能直接输出
pname varchar2(10) := ‘re jeknc’;
begin
age := 20;
dbms_output.put_line(age);
if marry then
dbms_output.put_line(‘true’);
else
dbms_output.put_line(‘false’);
end if ;
dbms_output.put_line(pname);
end;

–常量和变量
–引用变量,引用表中的字段的类型
Myname emp.ename%type; –使用into来赋值

declare
pname emp.ename%type;
begin
select t.ename into pname from emp t where t.empno=7369;
dbms_output.put_line(pname);
end;

–记录型变量
Emprec emp%rowtype; –使用into来赋值

declare
Emprec emp%rowtype;
begin
select t.* into Emprec from emp t where t.empno=7369;
dbms_output.put_line(Emprec.empno || ’ ‘||Emprec.ename||’ ‘||Emprec.job);
end;

–if分支

语法1：
IF 条件 THEN 语句1;
语句2;
END IF;
语法2：
IF 条件 THEN 语句序列1；
ELSE 语句序列 2；
END IF；
语法3：
IF 条件 THEN 语句;
ELSIF 条件 THEN 语句;
ELSE 语句;
END IF;
–1
declare
pname number:=#
begin
if pname = 1 then
dbms_output.put_line(‘我是1’);
else
dbms_output.put_line(‘我不是1’);
end if;
end;

–2
declare
pname number := #
begin
if pname = 1 then
dbms_output.put_line(‘我是1’);
elsif pname = 2 then
dbms_output.put_line(‘我是2’);
else
dbms_output.put_line(‘我不是12’);
end if;
end;

–loop循环语句
语法2：
Loop
EXIT [when 条件];
……
End loop

–1
declare
pnum number(4):=0;

begin
while pnum < 10 loop
dbms_output.put_line(pnum);
pnum := pnum + 1;
end loop;
end;

–2 (最常用的循环)
declare
pnum number(4):=0;
begin
loop
exit when pnum=10;
pnum:=pnum+1;
dbms_output.put_line(pnum);
end loop;
end;

–3
declare
pnum number(4);
begin
for pnum in 1 .. 10 loop
dbms_output.put_line(pnum);
end loop;
end;

---

–游标
语法：
CURSOR 游标名 [ (参数名 数据类型,参数名 数据类型,…)] IS SELECT 语句;
例如：cursor c1 is select ename from emp;

declare
cursor c1 is
select * from emp;
emprec emp%rowtype;
begin
open c1;
loop
fetch c1
into emprec;
exit when c1%notfound;
dbms_output.put_line(emprec.empno || ’ ’ || emprec.ename);
end loop;
close c1; –要记得关闭游标
end;

——–例外
–异常，用来增强程序的健壮性和容错性
– no_data_found (没有找到数据)
–too_many_rows (select …into语句匹配多个行)
–zero_divide ( 被零除)
–value_error (算术或转换错误)
–timeout_on_resource (在等待资源时发生超时)

–写出被0除的例外程序
declare
pnum number(4) := 10;
begin
pnum := pnum / 0;
exception
when zero_divide then
dbms_output.put_line(‘被0除了’);
when value_error then
dbms_output.put_line(‘算术或转换错误’);
when others then
dbms_output.put_line(‘其他异常’);
end;

–自定义异常
–No_data exception;
–要抛出raise no_data;

declare
cursor c1 is
select * from emp t where t.deptno = 20;
no_data exception;
emprec emp%rowtype;
begin
open c1;
loop
fetch c1
into emprec;
if c1%notfound then
raise no_data;
else
dbms_output.put_line(emprec.empno || ’ ’ || emprec.ename);
end if;
end loop;
close c1;

exception
when no_data then
dbms_output.put_line(‘无员工’);
when others then
dbms_output.put_line(‘其他异常’);
end;

–存储过程
语法：
create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]
AS
begin
PLSQL子程序体；
End;

或者

create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]
is
begin
PLSQL子程序体；
End 过程名;

—–创建一个存储过程helloworld
create or replace procedure helloworld is
begin
dbms_output.put_line(‘hello world’);
end helloworld;

——创建一个涨工资的
create or replace procedure addsal(eno in emp.empno%type) is
emprec emp%rowtype;
begin
select * into emprec from emp t where t.empno = eno;

update emp t set t.sal = t.sal + 100 where t.empno = eno;
dbms_output.put_line(‘涨工资前是’ || emprec.sal || ‘,涨工资后是’ ||
(emprec.sal + 100));
end addsal;

---

–java代码调用存储过程和函数

–存储过程

create or replace procedure acc_yealsal(eno in emp.empno%type,yearsal out number) is
pcomm emp.comm%type;
psal emp.sal%type;
begin
select t.sal,t.comm into psal,pcomm from emp t where t.empno=eno;
yearsal :=psal*12 +nvl(pcomm,0);
end;

—-存储函数
create or replace function 函数名(Name in type, Name in type, .. .)
return 数据类型 is
结果变量 数据类型;
begin

return(结果变量);
end函数名;
–存储函数计算年薪
create or replace function accf_yearsal(eno 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 = eno;
Result := psal * 12 + nvl(pcomm, 0);
return(Result);
end accf_yearsal;

---

—触发器
–触发语句：增删改：
语法：
CREATE [or REPLACE] TRIGGER 触发器名
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE [OF 列名]}
ON 表名
[FOR EACH ROW [WHEN(条件) ] ]
begin
PLSQL 块
End 触发器名

—插入一个新员工则触发
create or replace trigger insert_person
after insert on emp
begin
dbms_output.put_line(‘插入新员工’);
end;

select *from emp;
insert into emp values(1001,’李四’,’管理’,7902,sysdate,100,100,20);

–raise_application_error(-20001, ‘不能在非法时间插入员工’)

–==============================================================================
SQL> @ E:\powerDesigner\A_脚本\user.sql –导入脚本文件

select *from H_USER ;

insert into h_user valuer(sequserid.nextval,’a’,’a’,sysdate,’北京’,1);

---

–数据库建模
–一对多：多的一端是2，箭头指向的是表1，即少的一端
–在实体类中一的一端的实体类有多的一端的实体类的集合属性
–使用powerDesiger进行数据库建模，然后将数据导入，导入到plsql中进行使用

——————–连接远程数据库
–方法1，修改localhost的地址
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.lan)
)
)
–方法2
–或者直接在登陆界面在database中输入远程数据库的ip地址和端口号进行远程登陆