Oracle数据库基础（一）

了解数据库

数据库 基本概念：

    数据（data）：描述事物的符号；       

    数据库（DB）：存放数据的仓库；

    数据库管理系统（DBMS）:如何科学的组织和存储数据，如何搞笑的获取和维护数据需要依靠DBMS；

    数据库系统：数据库DB+数据库管理系统DBMS+数据库应用程序DBAS+数据库管理员DBA+用户USER。

数据库 发展阶段

1. 网状数据库

   按照网状数据结构建立的数据库系统称为网状数据库系统，其典型代表是DBTG(Database Task Group)。用数学方法可将网状数据结构转化为层次数据结构。（来自百度百科）

2. 层次数据库

   层次结构模型实质上是一种有根结点的定向有序树(在数学中”树”被定义为一个无回的连通图)。（来自百度百科）

3. 关系数据库

   关系式数据结构把一些复杂的数据结构归结为简单的二元关系(即二维表格形式)。由关系数据结构组成的数据库系统被称为关系数据库系统。

   在关系数据库中，对数据的操作几乎全部建立在一个或多个关系表格上，通过对这些关系表格的分类、合并、连接或选取等运算来实现数据的管理。dBASEⅡ就是这类数据库管理系统的典型代表。

   可以概括地说，一个关系称为一个数据库，若干个数据库可以构成一个数据库系统。数据库系统可以派生出各种不同类型的辅助文件和建立它的应用系统。（来自百度百科）

   关系数据库基本概念：
       元组：一行数据；
       属性：列数据；
       属性值。行和列的交叉位置表示某个属性值；
       主键：唯一确定元组的属性组；
       域：属性的取值范围
       关系模式。关系的描述称为关系模式。对关系的描述，一般表示为：关系名（属性1，属性2.....属性n）。
   

SQL

了解 SQL

    SQL，全称是结构化查询语言，是所有关系数据库管理系统的标准语言。SQL可以实现对数据库数据进行增删改查，数据库对象的创建、修改和删除以及事务控制。

SQL 常用语言

 1. 数据操作语言（DML）：select、insert、update、delete;
 2. 权限控制(DCL)：grant、revoke;
 3. 事务控制(TCL)：save、point、rollback、set、transaction、commit;
 4. 对象操作(DDL): creat、alter、drop; 

SQL 基础操作语句

基础查询语句及字符

    有一点先在此说明，SQL不区分大小写，所以接下来的代码或者语句中出现的大小写不作细究。

基础查询语句

select -- 查询;
distinct -- 去除重复行;
where —— 指定查询条件，例如：

--条件查询where   普通比较运算符
select empno, ename, sal from emp where sal = 1250;
select empno, ename, sal from emp where sal > 1300;
--<>为不等，没有><
select empno, ename, sal from emp where sal <> 1250;
select empno, ename, sal from emp where sal != 1250;

还有as可以为属性设置别名，||是oracle中的连接符等。

--起小名，别名，as可以省略，必要时用双引号引住
select empno 员工编号, ename as "员工姓名" from emp;

--连接符||
select empno || '的月薪是' || sal 月薪信息表 from emp;

查询条件关键字

between A and B —— 介于AB之间，包含A、B，即[A,B]，例如：

--条件查询   between A and B 
select empno, ename, sal from emp where sal between 1250 and 5000;

in(set) —— 出现在set中，解释一下，例如：

--
--条件查询   in（集合）
select empno, ename, sal from emp where sal in (1250, 3000);

这里的1250和3000就是上文的set，就是查找sal中是1250或者3000的。相当于：

select empno, ename, sal from emp where sal = 1250 or sal = 3000;

like ——模糊查询。like 有两个通配符，'' % '' 和 '' _ ''，“%”表示0或多个字符，“_“表示是少一个字符。当需要查询特殊字符时，需要使用escape关键字。下面给上具体实现代码：

--模糊查询   like
--以M开头
select ename from emp where ename like 'M%';
--包含A
select empno, ename from emp where ename like '%A%';
--以2结尾
select empno, ename from emp where empno like '%2';
--模糊查询的特殊字符问题，使用escape定义\为转义字符，使\后面的%不在当做通配符使用
select ename from emp where ename like '%\%%' escape '\';

is null 和 is not null ——为空值和不为空值。使用方法如下：

--条件查询   is null/is not null 
select empno, ename, sal, comm from emp where comm is not null;
select empno, ename, sal, comm from emp where comm is null;

逻辑运算符

三个逻辑运算符：
    and:  与运算符，有一为假则为假；
    or :   或运算符，有一为真则为真；
    not:  非运算符，逆运算。
代码如下：

--逻辑运算符的应用
--与  and
select ename, sal
  from emp
 where ename like '%A%'
   and sal > 1500;
--或  or
select ename, sal
  from emp
 where ename like '%A%'
    or sal < 1500;
--非  not
select ename, sal
  from emp
 where ename like '%A%'
   and sal not in (1250, 5000);

优先级：
    not>and>or,可以使用小括号（）来改变运算顺序。

逻辑运算符的优化：
    使用and时：检索结果较少的放后面；
    使用or时：检索结果较多的放后面。

常用字符函数

initcap(char) —— 首字母大写，将字符型的属性值改为首字母大写；

lowwer（char） —— 同上，不过是将属性值改为全小写；

upper（char） —— 这就是全大写了，下面附上实现代码：

--首字母大写    initcap
select initcap(ename) from emp where ename like '%A%';
--全小写    lower
select lower(ename) from emp;
--全大写    upper
select upper(empno) from emp;

ltrim（char，'str'） —— 左移除，将char中左边的首位字符或字符“str”移除。

rtrim（char，'str'） —— 右移除，将char中右边的首字符或字符串“str”移除。

注意：如果str缺省，则默认移除空格。

--左移除    ltrim   移除掉左边打头的指定字符或字符串,不定顺序，包含指定字符也会移除
select ltrim('sadsdddasdfghj', 'as') from dual;
--右移除    rtrim   移除掉右边打头的指定字符或字符串,不定顺序，包含指定字符也会移除
select rtrim('abcd12213', '213') from dual;
--第二个参数不写默认移除空格，也是只移除指定方向，ltrim移除左边的空格rtrim移除右边的空格
select rtrim('adfasfda               ') from dual;
--两个可以组合使用实现‘掐头去尾’
select ltrim(rtrim('asdfghj', 'ghj'), 'asd') from dual;

translate（'jack','ac'，'12'） —— 将字符串'jack'中的'ac'逐个替换成'12'。

replace（'jack','ac','12'）—— 将字符串'jack'中的'ac'整个替换成'12'。

--翻译    translate
select translate('jack', 'ac', '12') from dual;
--替换    replace
select replace('jack', 'ac', '12') from dual;

这两个字符函数非常相似，但是还是有区别的，详见以下代码：

--代码1,翻译    translate
select translate('jaak', 'aa', '12') from dual;
--代码2,替换    replace
select replace('jaak', 'aa', '12') from dual;

下面是两个代码的结果：

可见translate是以’aa’与’12’中的第一组a对应1来逐个替换，而replace 是将整个’aa’替换成’12’。

instr（char，'i'） —— 查找，在字符串char中查找i字符
concat（'字符串A','字符串B'）—— 连接AB两个字符串
substr（'字符串A','m',n） —— 字符串A中，从字符m开始，截取n个字符

--查找出现的位置      instr   查找f在字符串中第一次出现的位置
select instr('sfhdfjsdaklfj', 'f') from dual;
--连接    concat    连接两个字符或字符串
select concat('sadfaf', 'fdfsdffd') from dual;
--截取    substr  从第三个字符开始截取五个字符
select substr('aiwozhonghua', 3, 5) from dual;

数值函数

abs —— 取绝对值
ceil —— 向上取整
floor —— 向下取整
power（x，y） —— x的y次幂
trunc（x，y） —— 截断，对n保留m位，不考虑四舍五入
round （x，y） —— 四舍五入，保留n的m位
sqrt（n） —— 对n开平方根
mod（n/m） —— 求n/m的余数
sign（n） —— 取n的符号位，-为-1，+为+1，0为0。

--求绝对值      abs  
select abs(-123) from dual;
--向上取整      ceil
select ceil(12.2) from dual;
--向下取整      floor
select floor(13.2) from dual;
--x的y次幂      power
select power(2, 3) from dual;
--截断          trunc
select trunc(12.555, 2) from dual;
--四舍五入      round 
select round(12.415645, 2) from dual;
--开平方根      sqrt
select sqrt(16) from dual;
--取余数        mod
select mod(12, 5) from dual;
--去符号        sign
select sign(-23) from dual;

日期函数

tip：sysdate为系统当前时间

months_between（a，b） —— 返回日期ab之间有多少个月

add_months（a，n） —— 返回日期a指定的月份加上n个月指向的月份

next_day（a，week） —— 返回日期a后接下来的第一个week（星期几）是几号

last_day（a） —— 返回日期a这个月的最后一天是几号

round （a，'year'/'month'/'day'） —— 对日期进行四舍五入，规则是超过当前年/月/周的天数的一半就返回下一个年/月/周的第一天。

trunc（a，''year'/'month'/'day''） —— 对日期a进行截断,规则同round。

下面是实现代码：

--months_between（）  返回两个日期之间的月份数
select months_between(sysdate, '20-9月-96') from dual;
--add_months()        返回日期加n的月份，修改月份
select add_months(sysdate, 2) from dual;
--next_day            返回日期后接下来的星期几是几号
select next_day(sysdate, 3) from dual;
--last_day            返回日期指定的这个月的最后一天的日期
select last_day(sysdate) from dual;
--round               日期四舍五入
select round(sysdate, 'YEAR') from dual;
--trunc               对日期进行截断
select trunc(sysdate, 'year') from dual;

转换函数

    数据库中有三大种数据类型，number，character和date，number和character以及character和date可以互相转换，而number与date则需要character作为中间人实现转换。
    三者之间的转换函数是to_char(),to_number,to_date.

格式：

日期：
    YYYY/YY    4,2位年
    MM         数字的月份
    MON        格式中的“月”字
    DD         数字的天
    DY         星期缩写
    HH24/12    24/12小时制小时
    MI         分
    SS         秒

数字：     

    9代表一个数字，没有不显示，在小数部分会强制补0
    0代表一个数字，没有强制补0
    L代表本地（时区）货币
    . 小数端
    , 千分位
    $ 美元符
下面在代码里详细说明：

tip：dual是oracle中临时数据表，用于测试。     

--转换函数
--日期转字符
select to_char(sysdate, 'yyyy-mm-dd hh:mi:ss') from dual;
--字符转日期
select to_date(to_char(sysdate, 'yyyy-mm-dd hh:mi:ss'),
               'yyyy-mm-dd hh:mi:ss')
  from dual;

日期和字符相互转换都需要严格按照彼此的格式，如上代码中的’yyyy-mm-dd hh:mi:ss’，在日期转字符时，如果字符串格式缺省，那么默认转换格式为oracle数据库默认时间格式，即’dd-mon-yy’

--数字转字符串
select to_char(123456789, 'L999,999,999.99') from dual;
--字符串转数字
select to_number('$123,456,789.12', '$999,999,999.99') from dual;

数字和字符串互相转换时，要注意格式的数字个数智能比源数据多，就是’$999,999,999.99’可以多几个9，但是绝对不能少，少了会出现格式不对的错误。

其他函数

nvl（n1，n2） ——如果n1为空，则返回n2。

nvl2（n1，n2，n3） ——如果n1为空，则返回n3，否则返回n2。

decode（你，1，then f1，2，then f2 ....else m） —— 如果n的值为1，则返回f1，为2就返回f2，最后否则返回m

case ，when，then，end—— 如果case的值为when，then，最后end

    实现代码如下，其中case ，when，then，end能够实现两种概念，一是java中的if else，二是switch case：

--其它函数
--nvl（1，2）如果1为空，则返回2
select nvl(comm, 0) from emp;
--nvl2(1,2,3)如果1为空，返回3，不空返回2
select nvl2(comm, 1, 0) from emp;
--decode(属性，if1,then1,if2,then2...else 3)如果属性的值为if1，返回then1
select decode(sal, 1250, '中等工资', '弱爆了') from emp;
--case when then end 
select case when sal>5000 then '厉害了' when sal>2000 then '可以了' end from emp;
select case sal when 1250 then 'good' when 3000 then 'very good' when 5000 then 'godlike' end from emp;

多行函数

对一组数据进行运算的函数

max（） —— 求最大值；
min（） —— 求最小值；
sum（） —— 求和
avg（） —— 求平均值
count（）—— 计数

--多行函数
--max     查询最大值
select max(sal) from emp;
--min     最小值
select min(sal) from emp;
--sum     求和
select sum(sal) from emp;
--avg     求平均值
select avg(sal) from emp;
--count   计算一共有几行数据   *表示以行为单位计数
select count(*) from emp;
select count(sal) from emp;
--多行函数可写到一行，下行为以工资计算最大值最小值求和平均工资和多少个人的工资，
select max(sal) as 工资最大值,min(sal) 工资最小值,sum(sal)工资总和,avg(sal)平均工资,count(sal) 工资份数 from emp;

8.17日增加内容：
    group by —— 以指定属性分组
    having —— group by 之后的查询条件
    详见代码段

--group by 分组
--查询部门的平均工资
select deptno,avg(sal) from emp group by deptno;
--having 分组后的查询条件
--查询每个部门的平均工资要求平均工资大于2000
select avg(sal) from emp group by deptno having avg(sal)>2000;
--查询部门编号为10的部门和平均工资
--where 实现
select deptno , avg(sal) from emp where deptno = 10 group by deptno ;
--having实现
select deptno ,avg(sal) from emp group by deptno having deptno = 10;
--统计人数小于5的部门的平均工资
select avg(sal) from emp group by deptno having count(deptno)<5;
--统计各部门的最高工资，排除最高工资小于3000的部门
select deptno,max(sal) from emp group by deptno having max(sal)>=3000;

Oracle 数据库基础（一）暂时就先到这里。