oracle基础语法

• 生活家 
--注意分号很重要不能少
begin
insert into table (column1,column2) values(value1,value2);
insert into table (column1,column2) values(value1,value2);
insert into table (column1,column2) values(value1,value2);
end;

书上说创建主外键关系表是还是要分开写最好，因为还有很多约束，要分开写最好。

 

--为学号字段添加主键约束，字段推荐为PK_stuNo。
--为身份证号（stuID）字段添加唯一约束，约束名推荐为UQ_stuID
--为年龄字段添加检查（CHECK）约束，约束名推荐为CK_stuAge
--为学员成绩表添加主键约束，约束名推荐为PK_ExamNo
--为学员成绩表（stuMarks）添加外键（FOREIGN KEY）约束，约束名推荐为FK_stuNo
--创建学员信息表
CREATE TABLE  stuInfo
 (stuNo CHAR(6) NOT NULL, --学号，非空（必填）
  stuName VARCHAR2(20) NOT NULL,--学员姓名，非空（必填）
  stuAge NUMBER(3,0) NOT NULL,--年龄，非空（必填）
  stuID NUMERIC(18,0),--身份证号，NUMERIC (18，0)代表18位数字，小数位为0
  stuSeat NUMERIC(2,0)--座位号
);

---创建学员成绩表
CREATE TABLE stuMarks
（
  ExamNo CHAR(7) NOT NULL,--考号
  stuNo  CHAR(6) NOT NULL,--学号
  writetenExam NUMERIC(3,0),--笔试成绩
  LabExam NUMERIC(3,0) NOT NULL--机试成绩

）
--为stuInfo 添加主键约束（stuNo主键）
ALTER TABLE stuInfo
 ADD CONSTRAINT PK_stuNo PRIMARY KEY(stuNo);
 
 

--为stuMarks添加主键约束（ExamNo作为主键）
ALTER TABLE stuMarks 
 ADD CONSTRAINT PK_EXamno PRIMARY KEY (Examno);
 
 --添加唯一约束，（身份账号唯一，因为每个人的身份证号全国唯一）
 ALTER TABLE stuInfo
   ADD CONSTRAINT CK_stuAge CHECK(stuAge between 15 and 40);
   
 -- 添加外键约束（stuInfo和stuMarks建立关系，关联字段为：stuNo）
 ALTER TABLE stuMarks
  ADD CONSTRAINT FK_stuNo
  FOREIGN KEY(stuNo) REFERENCES stuInfo(stuNo);

删除约束的语法

 

 ---删除约束的语法(列入删除stuInfo表中年龄默认约束的语句，代码如下)
 
 ALTER TABLE stuInfo
 DROP CONSTRAINT CK_stuAge;

–使用sql语句查看约束

 

 

 SELECT * FROM USER_CONSTRAINTS WHERE table_name='STUINFO';

创建表空间时，以TBS_为前缀

 

创建表时，以TBL_为前缀

添加字段时，以字母开头，采用有特征含义的单词或缩写

添加主键时，以PK_为前缀

添加外键时，以FK_为前缀

 

 

--创建表空间
create tablespace user2_tablespace
datafile 'F:\app\Administrator\oradata\orcl\user2.dbf'
size 10M;
--创建用户 user2并使用表空间user2_tablespace
create user user2
identified by user2
default tablespace user2_tablespace;
--为用户user2授权临时用户角色和正式用户角色
grant connect,resource to user2;
------------------------------------------------------------------------------------------------------------
--表操作
--创建stuInfo表
create table stuInfo(
stuNo CHAR(6) NOT NULL,--学号
stuName VARCHAR2(20) NOT NULL,--学员姓名，非空（必填）
stuAge NUMBER(3,0) NOT NULL,--年龄，非空（必填）
stuID NUMERIC(18,0),--身份账号
stuSeat NUMERIC(2,0)--座位号
);
--修改表（增加两列）
AlTER TABLE stuInfo
ADD(
stutel_no VARCHAR2(12),
stuAddress VARCHAR2(20)
);
--修改表，修改先有列
ALTER TABLE stuInfo MODIFY(
stuName varchar2(25),
stuAge NUMBER(3,0) default 20
);
--修改表，删除现有列
ALTER TABLE stuInfo DROP(
stutel_no ,stuSeat
);
--删除表
drop table stuInfo;
---------------------------------------------------------------------------------------------------------------------
create table pertype(                       --------创建表的时候挺同时创建主外键（常用方法）
id number(3) primary key,--主键
typeName varchar2(20) not null
);
create  table persons(
id number(3) primary key,--主键
perName varchar2(20) not null,
type_id number(3) references pertype(id)--外键，引用pertype表的id主键
);
-----------------------------------------------------------------------------------------------------------------------
create table stuInfo(
stuNo char(6)not null,
stuName varchar2(20) not null,
stuAge number(3)not null,
beginDate date
);
select * from stuinfo;
--oralce不支持 insert into table values（），（），（）；这种方法插入。
insert into stuinfo values('034','tom',23,sysdate);
insert into stuinfo values('038','张三',27,sysdate);
insert into stuinfo values ('039','lily2',22,sysdate);
insert into stuinfo values ('040','lily3',22,sysdate);
commit;
update stuinfo set stuname='王五'where stuno='039';
update stuinfo set stuname='牛牛'，stuage=89 where stuno='040';--更新数据语句
--更新时间语句，时间格式（24小时制是hh24，非24小时制hh。因为oracle不区分大小写。月份用mm，分钟是mi）
--年月日格式
update stuinfo set begindate=to_date('2012-09-18','yyyy-mm-dd') where stuno='038';
--非24小时制
update stuinfo set begindate=to_date('2012-09-18 3:23:34','yyyy-mm-dd hh:mi:ss') where stuno='039';
update stuinfo set begindate=to_date('2012-09-18 3:23:34','yyyy-mm-dd hh:mi:ss') where stuno='039';
update stuinfo set begindate=to_date('2012-09-18 23:23:34','yyyy-mm-dd hh24:mi:ss') where stuno='040';
--用as方法复制表格式或者表数据
create table stuinfoback1 as select * from stuinfo where 1=2;--复制表格式（1=2不成立，数据就无法复制）
select* from stuinfoback3;
drop table stuinfoback3;
create table stuinfoback2 as select * from stuinfo ;--复制stuinfo中的数据和格式到stuinfoback2中
create table stuinfoback3 as select stuno,stuname from stuinfo ;--复制两行数据和格式
select 'S'||stuno as 学号，stuname "姓名" from stuinfoback3; --拼接S和学号显示在学号的每一行
---------------------------------------------------------------------------------------------------------------------------
scott表查询
create table mytable  as select * from scott.emp;
select * from mytable;
--查询销售人数
select count(*) from mytable where job='SALESMAN';
--查询奖金和工资总和最高的员工姓名(使用子查询)
select ename ,(sal+nvl(comm,0))from mytable where (sal+nvl(comm,0))in(select max(sal+nvl(comm,0)) from mytable);  
select count(job) from mytable;
--查询不同工作的人数
select count(distinct job) from mytable;
--入职时间最早的员工信息
select * from mytable where rownum=1 order by hiredate asc ;
--每个部门各有几名员工
select count(empno) from mytable group by deptno; 
-------------------------------------------------------------------------------------------------------------------------    --oracle中提供rownum伪劣作为 --oracle中提供rownum伪劣作为分页查询时用于筛选行数的技术
    select *　from (
    select rownum rn,t.* from
    (--增加伪劣的数据行
    select p.* from perinfo p order by name) t --业务sql语句,这里的rownum 和order by 不能在同一行，因为order by de的优先级低
     ) where rn>27 and rn <=30;--根据伪劣行数筛选需要的指定数据（根据页数和每页条数）
     (第几页-1*每页个数)+1  (第几页*每页个数)
        
                
     --在分页查询中，第一个需要确认总共分为几页
     select count(*) from perinfo;--根据业务语句所查询的结果行数（总记录数count）
   --计算总页数（count，pagesize）
   --totalPage=count %pageSize==0?count /pageSize:count/pageSize+1;
---------------------------------------------------------------------------------------------------------------------------
-- Created on 2016/10/13 by ADMINISTRATOR 
declare 
-- Local variables here
v_empno mytable.empno%type:=7788;
v_ename mytable.ename%type;
v_myemp_role mytable%rowtype;
begin
-- Test statements here
select * into v_myemp_role from mytable where empno=v_empno;
dbms_output.put_line('编号：'||v_empno||'的员工是'||v_myemp_role.ename||'薪水是'||v_myemp_role.sal);
end;      
----------------------------------------------------------------------------------------------------------------------------- Created on 2016/10/13 by ADMINISTRATOR 
declare 
-- Local variables here
v_count integer;
begin
-- Test statements here
select count(*) into v_count from mytable;
dbms_output.put_line('员工人数：'||v_count);
if v_count <=5 then 
dbms_output.put_line('员工人数不超过5人');
elsif v_count<=10 then --注意是elsif
dbms_output.put_line('员工人数不超过10人');
else --最后一个是else
dbms_output.put_line('员工人数超过15人');
end if; 
end;
--------------------------------------------------------------------------------------------------------------------------- -- Created on 2016/10/13 by ADMINISTRATOR 
declare 
-- Local variables here
v_count integer;--我的mytable有15个人。
begin
-- Test statements here
select count(*) into v_count from mytable;
dbms_output.put_line('员工人数：'||v_count);
if v_count <=5 then 
dbms_output.put_line('员工人数不超过5人');
elsif v_count<=10 then --注意是elsif
dbms_output.put_line('员工人数不超过10人');
else --最后一个是else
dbms_output.put_line('员工人数不超过15人');
end if;
dbms_output.put_line('------------------------------------------------------------');
case v_count
when 10 then 
dbms_output.put_line('有10名员工'); 
when 12 then 
dbms_output.put_line('有12名员工'); 
when 14 then 
dbms_output.put_line('有14名员工'); 
else
dbms_output.put_line('不是10.12.14名员工'); 
end case;
dbms_output.put_line('------------------------------------------------------------'); 
case 
when v_count <=5 then 
dbms_output.put_line('员工人数不超过5人');
when v_count <=10 then 
dbms_output.put_line('员工人数不超过10人');
when v_count <=14 then 
dbms_output.put_line('员工人数不超过14人');
else
dbms_output.put_line('员工人数超过15个人');
end case;
end;     
-----------------------------------------------------------------------------------------------------------------------     -- Created on 2016/10/13 by ADMINISTRATOR           --while 循环
declare 
-- Local variables here
i integer:=1;
begin
-- Test statements here
loop
dbms_output.put_line('第'||i||'次循环');
i:=i+1; 
exit when i>5; 
end loop; 
end;
--------------------------------------------------------------------------------------------------------------------     
-- Created on 2016/10/13 by ADMINISTRATOR            --while 循环的另一种方法
declare 
-- Local variables here
i integer:=1;
begin
-- Test statements here
while i<4 loop
dbms_output.put_line('第'||i||'次循环');
i:=i+1;
end loop;
end;      
-------------------------------------------------------------------------------------------------------------------
-- Created on 2016/10/13 by ADMINISTRATOR              ---for循环
declare 
-- Local variables here
i integer;
begin
-- Test statements here
for i in 1..10 loop
dbms_output.put_line('第'||i||'次循环');
end loop; 
end;
-------------------------------------------------------------------------------------------------------------------------
-- Created on 2016/10/13 by ADMINISTRATOR             --fot循环遍历结果集
declare 
-- Local variables here
begin
-- Test statements here
for empRow in(
select * from mytable
)loop
dbms_output.put_line('编号'||empRow.Empno||'员工薪水'||empRow.Sal);
end loop;
end;
--------------------------------------------------------------------------------------------------------------------------
-- Created on 2016/10/13 by ADMINISTRATOR                 -----捕获异常
declare 
-- Local variables here
i integer;
v_empno mytable.ename%type :=7788;
begin
-- Test statements here
update mytable set deptno=99 where empno=v_empno;
commit;
dbms_output.put_line('修改成功');
exception 
when others then
dbms_output.put_line('修改失败,错误编码：'||sqlcode||'错误信息'||sqlerrm);
end;
-------------------------------------------------------------------------------------------------------------------------
-- Created on 2016/10/13 by ADMINISTRATOR                 -----自定义异常
declare 
-- Local variables here
e_myexception exception;--声明异常
v_age integer:=11;
begin
-- Test statements here
if v_age>100 or v_age<0 then 
raise e_myexception;--触发异常规则时使用raise触发异常
end if ;
dbms_output.put_line('程序运行结束');
exception
when e_myexception then 
dbms_output.put_line('年龄不合法');
when others then 
dbms_output.put_line('发生其他错误'); 
end;

 

创建表的时候同时创建外键

 

create table pertype(                             --------创建表的时候挺同时创建主外键（注意 references）
id number(3) primary key,--主键
typeName varchar2(20) not null
);
create  table persons(
id number(3) primary key,--主键
perName varchar2(20) not null,
type_id number(3) references pertype(id)--外键，引用pertype表的id主键
);

序列

 

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> create sequence SEQ_TEST1
2  minvalue 1
3  maxvalue 1000
4  start with 1
5  increment by 1
6  cache 20
7  order;
序列已创建。
-- 刚刚创建的序列必须先用 nextval 来获取一个初始值
SQL> select SEQ_TEST1.currval from dual;
select SEQ_TEST1.currval from dual
*
ERROR 位于第 1 行:
ORA-08002: 序列 SEQ_TEST1.CURRVAL 尚未在此会话中定义
SQL> select SEQ_TEST1.nextval from dual;
NEXTVAL
----------
1
SQL> select SEQ_TEST1.currval from dual;
CURRVAL
----------
1
-- 清空 cache 中缓存的序列值
SQL> alter system flush shared_pool;
系统已更改。
-- 查询当前值，没有变化，仍是1
SQL> select SEQ_TEST1.currval from dual;
CURRVAL
----------
1
-- 查询下一个值：Oracle 发现 cache 中的序列值没有了，会再次产生20个序列值供使用。
-- 所以这里得到的结果不是2，而是21。
SQL> select SEQ_TEST1.nextval from dual;
NEXTVAL
----------
21
-- 再试一次
SQL> alter system flush shared_pool;
系统已更改。
SQL> select SEQ_TEST1.currval from dual;
CURRVAL
----------
21
SQL> select SEQ_TEST1.nextval from dual;
NEXTVAL
----------
41
-- 问题：Oracle 下一次取的20个值是从哪里开始计算的呢，是 currval + 20，
-- 还是每个 cache + 20 呢？我们试验一下。
SQL> select SEQ_TEST1.nextval from dual;
NEXTVAL
----------
42
-- 现在序列的当前值是42。如果是用 currval + 20 来计算，那么清空 cache 后，
-- 获得的 nextval 应该是62；如果是 cache + 20，那应该是 61。
-- 看看实验结果吧：
SQL> alter system flush shared_pool;
系统已更改。
SQL> select SEQ_TEST1.currval from dual;
CURRVAL
----------
42
SQL> select SEQ_TEST1.nextval from dual;
NEXTVAL
----------
61
结论：cache 可以用来提高序列值的获取速度，但有可能会浪费一些序列号，应该根据实际情况来设置 cache 的大小。
赞 (0)
上一篇 2022年3月22日
下一篇 2022年3月22日

相关推荐