碎点篇—— oracle 语法和练习

返回主目录

 

--select from where having group by order by 的正确执行顺序为：
--from-->where-->group by-->having-->select-->order by
--------------------------------------------------------
--1、去重：distinct 必须放在开头，多字段，每个字段不一样才去重
--2、条件比较：
-- =,!=,<>,<,>,<=,>=,any,some,all
-- is null, is not null
-- between x and y
-- in(list) , not in (list)
-- like _,%,escape '\' _\%escape '\'
-- not, (and,or) and优先级高

-- 按照单个列排序
-- order by col
-- 降序排列：order by col desc
-- 升序排列：order by col asc
-- 按多个列排序：(优先级)
-- order by col1 desc(asc) , col2 desc(asc)

--1.全集：union all
--2.并集：union (去重)
--3.交集：intersect
--4.差集：minus
--5.不懂怎么用，就选择 (*)
-----------------------------------------------------------
--基本查询语句
select * from emp;
select e.ename , e.sal from emp e where e.sal>1500;
select distinct e.deptno , d.dname from emp e , dept d;
---------------------------------------------------------
--组函数又被称作集合函数，用于对多行数据进行操作，并返回一个单一的结果
--组函数仅用于选择列表或查询的having子句单行函数对单个数值进行操作，并
--返回一个值。
--concat(string2,string3)连接两个字符串
--initcap(string) string中每个单词首字母大写
--lower(string) string 中每个单词首字母小写
--lpad , rpad 填充字符型数据
--ltrim/rtrim (string2,string3)
trim(A from B)
--substr() 提取字符串的一部分substr(string,1,2)
--upper(string)以大写形式返回string
--instr() 字符串出现的位置，instr(string,'A')
--length() 字符串长度

-------------------------------------------------------
--round(number,n):返回四舍五入的值
select round(23.434) from dual;
select round(23.434,2) from dual;
select round(23.436,2) from dual;
select round(23.434,-1) from dual;
--trunc(number,n): 返回截取的值
select trunc(23.434) from dual;
select trunc(23.434,2) from dual;
select trunc(23.436,2) from dual;
select trunc(23.434,-1) from dual;
--mod(x,y):求余数
select mod(13,5) from dual;
--ceil(number):向上取整
select ceil (19.2) from dual;
--floor(number):向下取整
select floor(19.2) from dual;

--avg(): 返回某列的平均值
--min(): 返回某列的最小值
--max(): 返回某列的最大值
--sum(): 返回某列值的和
--count(): 返回某列的行数
--组函数仅在选择列表和having子句中有效

--group by 子句
--group by 子句可以包含任意数目的列
--除组函数语句外，select 语句中的每个列都必须在 group by 子句中给出。
--如果分组列中具有null值，则null将作为一个分组返回。如果列中有多行null
--值，他们将分为一组。
--group by 子句必须出现在where 子句之后，order by 子句之前。
--过滤分组(having子句)
--where 过滤行，
--having 过滤分组
--having 支持所有的where操作
--分组和排序
--一般在使用group by 子句时，应该给出 order by 子句

--数据来自于多张表，92表连接
--注意：明确引用同名的列，必须使用表名，或者别名区分
--1.笛卡尔积：
--   select 字段列表 from 表1,表2,表3....
--2.等值连接：取关系列相同的记录
--   select 字段列表 from 表1,表2,表3....
--          where 表1.列 = 表2.列 and 表1.列 =表3.列
--3.非等值连接：取关系列不同的记录 != > < >= <= between and
--   select 字段列表 from 表1,表2,表3....
--          where 表1.列 != 表2.列 and 表1.列 != 表3.列
--4.自连接：(特殊的等值连接) 列来自于同一张表，不同角度看待表
--   select 字段列表 from 表1 e, 表2 d 
--          where e.列1 = d.列3
--5.外连接：在等值基础上，确保一张表(主表) 的记录都存在，从
--          表满足则匹配，不满足补充null
--右外：主表在右边
--左外：主表在左边

--sql1992：的语法规则暴露了这样额缺点：语句过滤条件和表连接的条件
--          都放到了where子句中。当条件过多时，联结条件多，过滤条件
--          多时，就容易造成混淆
--sql1999：修正了这个缺点，把联结条件，过滤条件分开来，包括以下新的table
--          join 的句法结构：
--cross join
--natural join 
--using 子句
--on 子句
--left outer join
--right outer join
--full outer join
-------------------------------------------------------
--创建视图
--with read only 表示只读
create or replace view v_emp_dept_a as 
       select e.deptno , e.ename , d.dname from
       emp e join dept d on e.deptno= d.deptno
       with read only ;

select * from v_emp_dept_a;
--当不再需要视图的时候，用"drop view"撤销。删掉视图不会导致数据的丢失
--因为视图是基于数据库的表之上的一个查询定义
drop view v_emp_dept_a;

--创建视图必须要有主键，不然无法插入数据。
create or replace view v_emp_dept_b as
       select e.empno , e.ename , e.sal , e.deptno from emp e;

select * from v_emp_dept_b;

--DML
insert into v_emp_dept_b (empno , ename , sal) values(2000,'zhiyi',3554);
update v_emp_dept_b set ename='weiwo', deptno=20 where ename='zhiyi';
delete from v_emp_dept_b where ename like 'weiwo'
-----------------------------------------------------------------------
--我们要求平均薪水的等级最低的部门，它的部门名称是什么？
--我们完全使用子查询
--求部门的平均薪水
select e.deptno dt , avg(e.sal) avg_sal from emp e group by deptno;
--创建子类视图
create or replace view  deptno_avg_sal_a as
       select e.deptno dt , avg(e.sal) avg_sal from emp e group by deptno;
select *  from deptno_avg_sal_a;
--求部门薪水的等级
select t.dt dl , s.grade gr from deptno_avg_sal_a t join salgrade s on
       t.avg_sal between losal and hisal;
--创建子类视图
create or replace view dept_grade_a as
       select t.dt dl , s.grade gr from deptno_avg_sal_a t join salgrade s on
              t.avg_sal between losal and hisal;
select * from dept_grade_a;
--求出最低等级
select  min(gr) from dept_grade_a;
--求出部门名字
select d.dname from dept_grade_a de join dept d on de.dl = d.deptno
       where de.gr = (select min(gr) from dept_grade_a);
select * from dept;
--删除视图
drop view dept_grade_a;
drop view deptno_avg_sal_a;
drop view v_emp_dept_b;
----------------------------------------------------------------------
--创建表
create table temp as 
       select *from emp where 1=2;
----where 1=2 创建的是空表单
select * from temp;
create table temp2 as 
       select *from emp where 1=1;
select * from temp2;
----where 1=1 创建的是全部数据的表单
--插入数据
insert into temp select * from emp;
--删除表单
drop table temp;
drop table temp2;

---------------------------------------------------------------------
--number(x,y) : 数字类型，最长x x位，y y位小数
--varchar2(maxlength) : 变长字符串，这个参数的上限是32767字节。
--                     声明方式如下 varchar2(L L)，L L 为字符串长度，
--                     没有缺省值，作为变量最大32767个字节
--char(max_length) : 定长字符串 ，最大2000个字节
--date : 日期类型(只能精确到秒)
--timestamp : 时间戳(精确到微秒)
--long : 长字符串，最大 2GB

--了解类型
--clob : 最大长度4G -->大对象很少使用：如果存在大对象，一般的解决方案
--                     存入文件地址(地址为程序所在应用服务器的相对路径)
--blob : 存二进制文件

--主键约束是数据库中最重要的一种约束。在关系中，主键值不可为空，也不
--允许出现重复，即关系要满足实体完整性规则。
--主键从功能上看相当于非空且唯一
--一个表中只允许一个主键
--主键是表中能够唯一确定一个行数据的字段
--主键字段可以是单字段或者多字段的组合
--oracle为主键创建对应的唯一性索引。

-------------------------------------------------------------------
--procedural language和sql的结合体。通过增加变量、控制语句，使我们可以写
--           一些逻辑更复杂的数据库操作。

--匿名块由一下四部分组成：
--declare    (可选)
--声明各种变量或游标的地方
--begin      (必要)
--开始执行语句
--       单行注释用两个连在一起的‘-’表示。
         /*多行注释语句，可以换行*/
--exception     (可选)
--出错后的处理
--end;          (必要)(请注意end后面的分号)


--1.变量必须在 declare 语句块中声明
--   变量声明的语法规则
--identifier [constant] datatype [not null] [:=| default expr];
--如：
--变量名  类型 := 初始值;
-- v_str varchar2(10) := 'abc';

--2.变量的命名规则
--变量名不能够使用保留字，如from、select等
--变量名最多包含30个字符
--不要与数据库的表或者列同名
--每一行只能声明一个变量
--建议：
--a).普通变量 v_
--b).常量 c_
--3.赋值语句：
--a).PL/SQL中的赋值使用  :=
--b)."=" 被用作比较操作符
--c).赋值语句中有一些规则需要遵守
--     字符类型必须使用单引号
--     不可以使用group by
--     可以使用数学操作符或字符串连接操作符

--常用变量类型：
--普通变量
--  %type
--  %rowtype
--  varray
--  table
--  record
--变量声明规则
--  变量名不能够使用保留字，如from、select等
--  第一个字符必须是字母
--  变量名最多包含30个字符
--  不要与数据库的表或者列同名
--  每行只能声明一个变量
--  普通变量的类型主要有：
--    binary_integer : 整数，主要用来计数而不是用来表示字段类型
--    number(x,y) : 数字类型，最长x位，y位小数
--    varchar2(maxlength)  : 变长字符串，这个参数的上限是32767字节
--                         声明方式如下 varchar2(L) , L为字符串长度，
--                         没有缺省值，作为变量最大32767个字节
--char(max_length) : 定长字符串
--date : 日期
--boolean  : 布尔类型，可以取值为 true、false和null值


--table 义记录表(或索引表) 数据类型
--先声明table类型(声明具体的table类型)
--type table 类型的名称 is table of data_type [index by binary_integer];


--1.单选择语句：
--   if 条件  then
--   end if;
--2.双重选择：
--   if 条件 then
--   else
--   end if;
--3.多选择：
--   if 条件 then
--   elsif 条件 then
--   elsif 条件 then
--   else
--   end if;


--1.do-while 循环&#x