欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  IT编程

oracle基础语法

程序员文章站 2023-03-16 20:21:23
--注意分号很重要不能少 begin insert into table (column1,column2) values(value1,value2); insert...
--注意分号很重要不能少
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 的大小。

上一篇： Oculus Rift今年第一季度就要上市了

下一篇： 赵恒在政治上做了哪些改变？才使北宋出现了咸平之治

推荐阅读