oracle基础语法

程序员文章站 2022-05-23 08:21:50

--注意分号很重要不能少 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 的大小。

上一篇：关于Hadoop1.x和2.x的区别和联系讲解

下一篇：每个朝代都是不一样的，那在清朝以前都有什么样的发型？

oracle基础语法

JavaScript 基础函数_深入剖析变量和作用域

javascript基础知识讲解

JavaScript基础教程——入门必看篇

Python程序设计入门(1)基本语法简介

详解如何用babel转换es6的class语法

LiteIDE开发工具简明使用指南(Go语言基础学习)

什么是网站优化(核心基础篇）

EmEditor 正则表达式使用基础与实战

现在互联网做什么赚钱，0成本0基础在家月入万元的互联网项目！

Python基础编程语法（for循环、推导式、函数）