30分钟带你入门Oracle

ps:关键字和语法用红色字体标明，代码用蓝色字体标明

一.一些定义

 1.数据类型

    字符数据类型
      char  固定长度字符串 1~2000字节
      varchar2 可变长度字符串 1~4000字节
      long  可变长度字符串 2GB 一张表中只能出现一次long 不能建立主键，唯一约束，索引数值数据类型
      number[(p[,s])] p表示精度，s表示小数点的位数 最高精度为38位

   日期时间类型(包括年月日，时分秒)
      date
      timestamp

   RAW/LONG RAW(用于存储二进制数据，如音频、视频)
      RAW      2000字节
      LONG RAW 2GB

   LOB (音频、视频、长篇小说) 4GB 用于大数据
      CLOB
      BLOB
      BFILE

 2.四种语言

   DDL：数据定义语言 用户，表空间，表
      create 创建
      alter  修改
      drop   删除

   DML：数据操作语言
      insert into 插入
      update      修改数据
      delete     删除数据
      select      查询数据

   TCL：事物控制语言
      savepoint 保存点;    //设置保存点
      rollback to 保存点;  //回到保存点
      commit    ;          //提交事物

   DCL：数据控制语言
      grant 授权语句

 3.事务

   事务是最小的工作单元，作为一个整体进行工作
   保证事务的整体成功或失败，称为事务控制
  用于事务控制的语句有：
     COMMIT - 提交并结束事务处理
     ROLLBACK -  撤销事务中已完成的工作
     SAVEPOINT – 标记事务中可以回滚的点

 4.主键和外键

  主键：表中的一个或多个字段，它的值用于唯一地标识表中的某一条记录。

         特点：不能为空，不能重复，最好是可以自增的

         语法：在字段后面直接加上 primary key

         例：create table student(

                            sid int primary key,

                            Name varchar2(10)          

                  )

     外键：外键是对另外一张表的主键的引用

      语法：constraints 外键名称 foreign key(外键字段) references 被引用的表名(字段名) on delete cascade;

     例：create table cs(

                csid number primary key,

                sid number,
                grade number(3),

                constraints fk_cs_students foreign key(sid) references students(sid) on delete cascade

          )

二.基本操作

1.登录数据库

conn 用户名/口令;    如：conn system/orcl;

2.提供了一个示例的普通用户 scott,这个用户在默认情况下是被锁定的

解锁：alter user scott account unlock;

给scott设置口令：alter user scott identified by scott;

3.创建用户

create user 用户名 identified by 密码;  如：create user llk identified by 1111;

4.给用户设置权限

grant 权限 to 用户名;  如：grant connect to llk;

5.查看用户

show user;

6.删除用户

droup user 用户名;  如：drop user llk;

三.操作表

1.创建表

create table 表名(字段名 类型，字段名 类型...)

如：create table student(
         userNum number(10),
         userName varchar2(20),
         userSex char(2)

)

2.修改表结构

修改类型： alter table 表名 modify(字段名 类型)  如：alter table student modify(userSex char(4));
增加字段： alter table 表名 add(字段名 类型)  如：alter table student add(userBirth date);

3.查看表结构

desc 表名;  如：desc student;

4.删除表

drop 表名;  如： drop student;

四.操作表内数据(增删改查)

1.增加数据

       插入数据到student表(可以不写字段名，如果不写字段名，values的值就是按照创建表的时候的字段给所有字段赋值)
insert into 表名（字段名，字段名...） values (值1，值2...)

如：insert into student values(1,'张三,'男',to_date('1984-12-10','YYYY/MM/DD'));

2.修改数据

update 表名 set 字段=新值; (所有字段都会改掉)
update 表名 set 字段=新值 where 字段=值;(加入条件后可以自由选择需要改的某一行)

如：updata student set userName='李四'  where userNum=1;

3.删除数据

delete from 表名;(删除所有数据)
delete from 表名 where 条件;(加入条件后可以自由选择需要删除的某一行)

如： delete from student where userNum=1;

4.查询数据(以下查询的数据均来自于scott实例)

(1)一般查询

--最简单的查询
select 字段名，字段名，...from 表名;  如：select * from emp;(*表示所有数据)

--别名查询
select 字段名 as 别名，...from 表名;  如：select empno as 编号,ename as 姓名,sal as 工资from emp;

--带条件的查询
select 字段名,字段名,...from 表名 where 条件;  如：select ename ,sal from empbak where deptno=10;

--模糊查询 % _
% 通配1个或者多个字符；_ 通配1个字符

例：查询名字带M的
SELECT * FROM EMP WHERE ENAME LIKE '%M%';  --名字中含有M的数据
例：查询名字的M开头的
SELECT * FROM EMP WHERE ENAME LIKE 'M%' ;   --名字以M开头的数据

例：查询名字以M结尾的
SELECT * FROM EMP WHERE ENAME LIKE '%M'; --名字以M结尾的数据
例：查询名字是四个字的
SELECT * FROM EMP WHERE ENAME LIKE '____' ; --查询名字为4个字符的数据

--排序查询

对数据进行排序  order by

例：对部门为10的员工按照工资从少到多排序
select ename,sal,from emp where deptno=10 order by sal asc;
例：对部门为10的员工按照工资从多到少排序
select ename,sal,from emp where deptno=10 order by sal desc;

(2)使用函数进行查询

--分组函数

COUNT 函数：统计数据

例：统计部门为10的总人数
select count(empno) from emp where empno=10;

SUM 函数：求和

例：统计本月应发的总工资
select sum(sal) from emp;

AVG 函数：求平均值

例：计算部门为10的平均工资
select avg(sal) from emp where empno=10;

MAX 函数：获得最大值

例：获得工资最高工资
select max(sal) from emp;

MIN 函数：获得最小值

例：获得工资最低工资
select min(sal) from emp;

--分组语句：查询中含有分组函数 group by  ...having...

例：统计每个部门每个月发出的工资总和
select deptno,sum(sal) from emp group by deptno;

例：按照部门统计各个部门的平均工资，如果部门人数少于4个的不统计

select deptno,avg(sal) from emp group by deptno having count(*)>=4;

--单行函数：对于从表中查询的每一行只返回一个值

--只给出一些常用的单行函数，后面会附上常用函数的文件，可以自己去查看

单行函数可以大致划分为：
日期函数

    ADD_MONTHS
       例：第15个月后是什么时候
       select add_months(sysdate,15) from dual;
数字函数

   abs()：返回指定数字的绝对值。 如：Select abs(-15) from dual;  输出：15
   ceil()：返回大于或者等于指定表达式的最小整数 如：Select ceil(44.778) from dual; 输出：45
   floor()：返回不大于指定表达式的最大整数  如：Select floor(100.8) from dual;   输出：100
   round()：指定的位数进对数值进行四舍五入  如：Select round(100.256,2) from dual;  输出：100.26

   trunc()：截取日期或数字，返回指定的值。 如：Select trunc(100.256,2) from dual;   输出：100.25
字符函数

  Initcap(char)：将首字母大写  如：select initcap('hello') from dual;
  Lower(char)：将大写转小写  如：select lower(‘FUN) from dual;
  upper('abc')： 将小写转大写   如：select upper('fun') from dual;
  Ltrim(char,set) :如果参数二中任意字符出现在参数一的左边，就去掉
  Rtrim(char,set) :如果参数二中任意字符出现在参数一的右边，就去掉
  Translate(char, from, to) :如果参数一中出现参数二，则用参数三代替
  Instr (char, m) : 获得参数二在参数一中的位置
  substr('abcdef','3','2')从第三个字符开始截取两个字符
  concat('abc','def')连接两个字符串 

  chr(97) 获得数字对应的字符
  ASCII('A') 获得字符对应的ASCII码
  Lpad('abc',10,'*') 如果abc不够10位，就在左边加上*
  Rpad('abc',10,'*') 如果abc不够10位，就在右边加上*
  length(char) 获得字符串的长度
  trim('abaca','a') 去掉参数一左右两边出现的参数二
转换函数

  to_char(date,'YYYY-MM-DD') 将日期类型转成字符类型
  to_date('2015-11-7','YYYY-MM-DD') 将字符串类型转成日期类型
  to_number('1234') 将字符串转成数字
其他函数

   nvl:将null值转化成指定的值 

     例：计算指定员工的月收入 =工资+奖金

      select sal,comm,sal+nvl(comm,0) from emp;
  nvl2：如果参数一为null就变成参数二，否则变成参数三

      例：如果有奖金，就在基础上+200，如果没有奖金，就发300奖金

     select ename,comm,nvl2(comm,comm+200,300) from emp;
  nullif：把存在的值变成一个null

      例：如果奖金为300，就把300变成null

      select ename,comm,nullif(comm,300) from emp;

--分析函数

  Row_number 返回连续的排名
  Rank    相同的值排位相同，随后跳跃
  Dense_Rank 相同的值排位相同，随后接着往下排

     例：取出工资最高的前5位
           select empno,ename,sal,row_number() over(order by sal desc) from emp;
           select empno,ename,sal,rank() over(order by sal desc) from emp;
           select empno,ename,sal,dense_rank() over(order by sal desc) from emp;

(3)使用操作符进行查询

算术操作符：加(+)减(-)乘(*)除(/)
比较操作符：= , ！= , > , < , >= , <=
                        between...and in any all like
逻辑操作符：and or not

连接操作符：

    union 合并两个查询结果集,去掉重复数据（并）

    union all 合并两个查询结果集,保留重复数据

    intersect 取两个结果集的交集（交）

    minus 取在第一个结果集中出现，但在第二个结果集中没有出现的数据（差）

(4)复杂查询

--子查询：在查询语句中包含另外的查询

例：查询工资最高的员工信息
select * from emp where sal=(select max(sal) from emp);

--多表查询

例：查询员工的姓名和所属部门名称
select emp.ename,dept.dname from emp ,dept where emp.deptno=dept.deptno;

例：求SMITH所在部门的位置：
select loc from dept where deptno=(select deptno from emp where ename='SMITH');

--连接查询

  内连接  INNER JOIN ON   只显示两个表中都有的数据

  左外连接  LEFT JOIN ON  以左表为主，会显示左表中的所有数据，如果在右表中没有出现这个数据，则用null代替

  右外连接  RIGHT JOIN ON  以右表为主，会显示右表中的所有数据，如果在左表中没有出现这个数据，则用null代替

  全连接  FULL JOIN ON 会显示两个表中所有的数据，如果在另外一张表中没有出现这个数据，则用null代替

五：数据库对象

包含  表/同义词/索引/序列/视图

1.同义词：指向其它数据库表的数据库指针  synonym

私有的同义词是在指定的模式中创建并且只有创建者使用的模式访问。公共同义词是由public 指定的模式访问，所有数据库模式（用户）都可以访问它。
       特点：简化SQL语句；隐藏用户名(模式名)和表名；提供对表的公共访问

定义一个私有同义词
 create synonym emp for scott.emp;

定义一个公有同义词
 create public synonym emp for scott.emp;

2.索引：使用索引可快速访问数据库表中的特定信息   index
        特点：索引是建立在某一个或者某几个字段上的；索引的作用就是为了提高查询性能
                   减少磁盘的IO；索引时由Oraclce自动维护的

                   索引并不是越多越好，只需要对经常检索的字段建立索引

创建索引 ：

   标准索引
      create index aa on emp(ename);[create index 索引名 on 表名 (字段名);]

   唯一性索引
      create unique index bb on students(sname);

   组合索引
      create index cc on students(sname,saddress);
   唯一性的组合索引(两个字段的值不能同时相同)
      create unique index cc on students(sname,saddress);

3.序列：可以产生唯一的，连续的数字的对象  sequence
 创建序列：

     create sequence seq_aa
     start with 1 -- 开始的数字
     increment by 1 --每次增量
     maxvalue 200 --最大值
     minvalue 1 --最小值
     nocycle  --当达到最大值的时候就不在生成
     cache 10  --每次缓存10

 如何使用序列
    查看该序列目前的值
     select seq_aa.currval from dual;
    将序列向后移动
     select seq_aa.nextval from dual;

   删除序列
     drop sequence seq_aa;

4.视图：相当于一张'虚拟表'，是用来显示一张表或者多张表的查询的数据的  view

        特点：简化操作，定制数据，合并分割数据，安全
创建视图：

    创建标准视图
      create view 视图名 as 查询的结果集;

    create or replace view abc as select *from emp where ename='SMITH'

    创建只读视图
      create view 视图名 as 查询的结果集 with read only;

     create or replace view abc as select *from emp where ename='SMITH'  with read only;