Oracle数据库学习笔记(一)

Oracle 数据库

Oracle是一个强大的关系型数据库产品，功能丰富，产品安全性有保障，是Oracle公司的主打产品

本系列笔记可能会遇到的专业词汇有：

• DB, DataBase, 数据库
• DBMS,DataBase Management System, 数据库管理系统
• RDBMS,Relationship DataBase Management System, 关系型数据库管理系统
• SQL, Standard Query Language, 标准化查询语言

本系列学习笔记包含如下的课程内容：

• 数据库的基本概念
• DDL 命令
  • Create
  • ALTER
  • DROP
• DML 命令
  • insert
  • update
  • delete
• DQL 命令
  • select 命令
  • 关联查询
  • 分组查询
  • 子查询
• DCL 命令
• DTL 命令
  • commit
  • rollback
  • savepoint
• 数据字典
• 数据库对象
  • 序列
  • 索引
  • 视图
  • 同义词

---

数据库的一些基本概念

• 数据库(DB, Database):按照数据结构来组织、存储和管理数据的仓库
• 数据库管理系统(DBMS, Database Management System):
  是一种管理类软件系统，专门用来做数据存储、管理。
• 数据库分类:

1. 网状型数据库 2.树形数据库 3. 关系型数据库 4. 对象数据库

• Oracle数据库，它是一个比较大型的关系型数据库管理系统
• Larry Ellison是oracle公司的创始人
• 关系型数据库管理系统有哪些产品？

1. Oracle(用的最多/功能最强大/收费最高(60到70万每年))
2. DB2(IBM公司的)
3. SQL SERVER(微软的)
4. MySQL(之前是sun公司的产品现在一并归入到了ORALE)
5. Sqlite(手机上的小型数据库)
   ……

• SQL (Structure Query Language, 结构化查询语言)

  1974年由IBM公司提出,1976年ANSI把SQL标准进行了规范化并且在1986和1992年对该标准进行了改进.2003年又进行了比较大的改进.
  (2003年的标准为现阶段的主流标准,其写法又称为03写法)

  它是用来和RDBMS产品进行交互的语言，是随着RDBMS产品一起发布的，
  作为操作数据库管理系统的一个标准

---

• SQL的5种命令

1. DQL, Data Query Language【数据查询语句】
   关键字: SELECT

2. DCL, Data Control Language【数据控制语句】
   关键字: GRANT 、 REVOKE

3. DML, Data Manipulating Language【数据操作语句】
   关键字: INSERT、 UPDATE、 DELETE

4. DTL 命令，Data Transaction Language【数据事务语句】
   关键字: COMMIT、 ROLLBACK、 SAVEPOINT

5. DDL 命令，Data Definition Language【数据定义语句】
   关键字: CREATE、 DROP、 ALTER、 COMMENT ON、 TRUNATE、 RENAME TO….

---

• 安装与配置Oracle数据库。
• Oracle的版本:
  …..8i -> 9i -> 10g -> 11g -> 12c
  i 代表internet(互联网)
  g 代表grid 网格运算和分布式运算
  c 代表cloud 云计算
• oracle安装完成之后会在”服务”新增5个服务,其中最重要的两个服务为:

1. OracleServiceXE 它是ORACLE主引擎,该服务的关闭会导致整个ORACLE系统不能使用

2. OracleXETNSListener: 它是第三方服务的监听器,它负责把其他第三方的连接进行传入和传出,该服务的关闭将导致第三方客户端的不可使用

• oracle 数据库由服务端和客户端组成：

• 服务端：由一堆的数据库后台进程和监听器以及数据库所组成,安装完成后新增的5个服务就属于后台进程和监听器的范畴

• 客户端有多种:

1. SqlPlus(命令行形式的客户端,它是官方自带的轻量的客户端)
2. PL/SQL(第三方收费客户端/功能最强大的ORACLE客户端,图形界面)
3. SQL developer(ORACLE自己的开发的,基于JAVA的客户端软件)
4. Navigator(之前是专门提供给MYSQL的客户端,SUN公司被收购后,归ORACLE公司所有,现在同样支持ORACLE的连接和使用)

5. Navicat(香港卓软科技有限公司)

   从9i~10g，提供了基于浏览器访问的服务 isqlplus也就是说，不需要专门的客户端软件，通过浏览器就能打开。
   如：打开浏览器，输入 http://127.0.0.1:8080/apex

---

• 准备工作:
  创建用户的2个步骤:

• 1. 利用管理员身份创建一个普通用户。
  语法：

  create user 用户名 identified by 密码
  [default tablespace 表空间名字];

  默认是使用USER的表空间，密码的设定不能以数字开头

• 练习：创建一个用户名为jsd1611密码为jsd1611的用户

  create user jsd1611 identified by jsd1611;

• 2. 授权给一个用户（即设置角色） , 语法：

  grant 权限, 权限…… to 用户名;

  下表为oracle中内置的3个常用角色, 分别为

  角色名|操作权限
  -|-
  dba|系统管理权限
  connect|创建会话连接权限
  resource|操作基本数据库对象的权限

• 练习:把connect以及resource两个角色授予用户

• 切换用户语法:

  conn 用户名/密码;

• 修改密码语法:

  alter user 用户名 identified by 密码;

  注意: 用户拥有修改自己当前用户的密码的权限，创建当前用户的高权限用户同样拥有修改权限，
  当前连接着的用户不能够被管理员删除

• 删除用户语法:

  drop user 用户名 [cascade];

  注意:只有创建该用户或者更高权限的账户才拥有删除该用户的权限

• 补充:

  • 设置用户密码过期: alter user 用户名 password expire; (该操作同样要求管理员权限)
  • 锁定用户: alter user 用户名 account lock;
  • 账户解锁: alter user 用户名 account unlock;
• 练习

  -- 创建一个用户: 账号名为 test 密码为test
  create user test identified by test;
  -- 给这个账号授权 connect 和 resource
  grant connect, resource to test;
  -- 然后再创建一个用户test001/a123456
  create user test001 identified by a123456;
  -- 连接到test用户 把密码修改为test02
  conn test/test;
  -- 修改成功
  alter user test identified by test02;
  -- 并尝试修改test001用户的密码(修改不成功)
  alter user test001 identified by test02;
  -- 最后使用管理员账户system
  conn system/密码; 
  -- 删除用户test和test001
  drop user test;
  drop user test001;

  注意:SQL中使用’–’或者/…/来表示注释内容

---

• SQLPLUS 是ORACLE数据库的专用客户端软件，它是随着ORACLE产品
  一起发布的，也就说，你安装好了ORACLE软件，也就自动安装了SQLPLUS
  客户端。

  注：sqlplus是一款基于命令行的软件，它不是IDE工具。

• SQL*PLUS中的常用命令:

1. conn[ect] 用户名/密码
   表示切换用户

2. start或者@ [空格] *.sql(SQL脚本的路径)
   执行数据库脚本

   数据库脚本：用于创建数据库对象语句集合
   目的：在数据库中创建存储数据的表格并且在表格中初始化数据

3. show user
   显示当前的命令用户
4. desc 表名
   查看表的结构
5. clear screen
   清屏
6. 回忆记录
   方向键上下 (常用)

7. edit 文件名
   编辑多行sql(了解)

   我们可以使用 help 命令来查看任何一个SQL*PLUS命令的帮助文档。如:

   help conn
   help index;

   注：SQL*PLUS命令与SQL命令是两码事。

---

• 表[table]

  表是数据库中最常见的对象, 它利用 “行与列” 来存储数据, 我们的数据也是放在表中的。

• Navicat快捷键

  快捷键|作用
  -|-
  Ctrl + q|打开一个新的查询窗口
  Ctrl + r|执行所有SQL语句
  Ctrl + Shift + r|执行选中的SQL语句
  Ctrl + /|注释
  Ctrl + shift + /|解除注释
  Ctrl + l|删除行
  F7|执行鼠标光标后的一行SQL语句

---

初级查询

单表无条件查询

• 查询语句DQL

  查询的本质是做两件事: 1. 找什么 【定位 列名】 2. 去哪里找 【定位 表名】

  语法:

  SELECT [DISTINCT] [alias.]列名 [as] 列别名[,[alias.]列名 [as] 列别名]…
  FROM 表名 [as] alias

  DISTINCT 关键字表示用来排除重复记录。

• 练习：

  -- 从s_emp表中查询出员工的名字，薪水和职位
  SELECT first_name, salary, title FROM s_emp;
  -- 从s_emp表中查出员工的姓氏，名字
  SELECT last_name "姓 氏", first_name AS 名字 FROM s_emp;
  -- 找出员工的所有信息
  SELECT * FROM s_emp;
  --- 找出本公司员工的所有职位（title）
  SELECT DISTINCT title FROM s_emp;
  -- 找出所有员工的全名、工资和入职日期
  SELECT first_name || ' ' || last_name 全名, salary, start_date FROM s_emp;
  -- 找出所有的客户名及他的电话号码
  SELECT name, phone FROM s_customer;
  -- 找出每个订单的费用、支付方式、出货日期
  SELECT total, payment_type, date_shipped FROM s_ord;

  注意: SQL中可以使用运算符对列值等进行逻辑运算和算术运算等

Oracle的常用数据类型

• 数字型（可以做所有的数学运算）

  类型|表示含义
  -|-
  number|代表整数，默认长度为38 表示范围 -99…99(38位数)~ 99…99(38位)
  number(p)|代表整数，长度为p
  number(5) |代表整数字段有效位数为5的数 范围是-99999~ 99999
  number(p,s)|代表浮点数，p表示有效位，s代表精度(精确到几位)

• 有效位数指的是从左边第一个不为 0 的数算起，到末位数字为止的所有数字，小数点和负号不计入有效位数。

  number(4, 2)    表示有效位是4位, 精确到小数点后面是2位。
  若存储数值为99.99          正确存储
  若存储数值为99.128         存储为99.13
  number(2, 4)    表示有效位是2位, 精确到小数点后面4位。
  若存储数值为0.0099         正确存储
  若存储数值为0.00931        存储为0.0093
  若存储数值为0.9932         报错
  number(2, -1)   表示有效位数为2位精确到十位 
  若存储数值为93             存储为90

• 字符型（可以做拼接运算，oracle用||做拼接符）

  类型|表示含义
  -|-
  varchar2(n)|变长字符串,最大长度为4000 bytes,为SQL标准
  varchar(n)|同上,区别在于varchar2为oracle特有,oracle会保证varchar2在今后的兼容性

• varchar2又有两种表示写法:

1. varchar2(n byte) 表示该字段能最长存储n比特的字符串,强调空间的概念,默认bytes表示

2. varchar2(n char) 表示该字段能最长存储n个字符的字符串,强调数量的概念

   其中, 中文根据不同字符的编码占不同的大小, UTF-8编码一个汉字占3个byte, GBK编码一个汉字占2个byte,
   英文占1个byte

• 补充
  nvarchar2(n), 它没有byte和char之分,并且汉字都占2个byte,最大长度为4000 bytes
  它的字符集设定只能使用AL16UTF16或者UTF8,它在数据库创建时就会根据国家字符集合进行规定

  varchar2(20) 代表字符串最长为20bytes;
  若存储数据为"abcd"  长度为4bytes
  若存储数据为"张三"  长度为4bytes(GBK编码)

• char(n) 表示定长字符, n最大为2000 bytes

• nchar(n) 表示定长n个字符, 最大长度2000 bytes

      若使用char(20)存储数据"abcd"    存储为abcd + (16空格),长度为20 
      ```    
  
  - 效率的话char大于varchar2
  
  - 同varchar相似,char也有两种表示写法
  1. char(20) 代表定长20 bytes,等价于char(20 byte)  
  2. char(20 char) 定长20 字符
  
  - **日期型，可以做加减运算**
  
      类型|表示含义
      -|-
      date|代表日期，默认有年、月、日(年月日时分秒)
      time|代表时间（时分秒）
      timestamp|代表日期与时间（年月日时分秒，还有小数位：3.2秒）
  
  - **大对象(4G)，不支持查看结果**
  1. blob,  binary large object , 字节大对象,用来存放二进制数据。  
      如：mp3, 图片, avi, ... 
  2. clob,  character large object, 字符大对象，用来存放大的字符数据。  
      如:大文本信息
  
  - 注意:不同的类型可以参与的运算是不一样的，目前我们主要讲解数字与字符。  
  <font color="#FF0000">oracle 里任意值若与null进行运算, 结果都是null</font>
  
  ---
  
  - <font color="#0000FF">练习：</font>

  – 查询出每个员工的姓名以年薪 以月薪*12
  SELECT first_name, salary 12 年薪 FROM s_emp;
  – 查询出每个员工的姓名以年薪 [以月薪12](考虑提成率)
  SELECT first_name, salary (1 + nvl(commission_pct, 0) / 100) 12 年薪 FROM s_emp;
  – 从s_emp表中查出员工的全名
  SELECT first_name || ‘ ‘ || last_name 全名 FROM s_emp;
  – 字符串拼接（*是入职的，工资是，职位是）
  SELECT first_name || ‘是’ || start_date || ‘入职的，工资是’ || salary || ‘，职位是’ || title FROM s_emp;

  - 补充: 空值置换函数  
      + 对空值[null]值的处理：
      oracle提供的空值处理函数 NVL 来转换, 语法：
  > ***NVL(列值, 替代值)  当列值为null，返回替代值。***
      + 如:nvl(commission_pct, 0) 表示如果commisson_pct不为null,返回commission_pct;反之，返回0.
      + 注：nvl()中的两个值要为同一数据类型
  
  ---
  
  ## 条件查询  
  
  - 在基本查询的基础上，添加了行记录的过滤。语法：
  >***SELECT [DISTINCT] [alias.]列名 [as] 列别名[,[alias.]列名 [as] 列别名]...    
  FROM 表名 [as] alias  
  [WHERE 子句]  
  [order by 字段一 asc/desc, 字段二 asc/desc]***  
  
  - 注意：  
      where 子句可以指定条件来过滤记录,它对字面量大小写是敏感的  
      order by子句可以指定排序条件进行记录的排序
  
  - 示例: **找出在41部门ID(对应字段为dept_id)的员工名、工资、职称**  
      **1.查询全部**   
      elect first_name, salary, title from s_emp;  
      **2.对查询结果进行条件筛选**  
      where dept_id = 41;  
      **3.得到最终结果语句**  
      select first_name, salary, title, dept_id from s_emp
      where dept_id = 41;
  
  - <font color="#0000FF">练习：</font>

  – 找出职称是Sales Representative的员工名、工资
  SELECT first_name, salary FROM s_emp WHERE title = ‘Sales Representative’;
  – 找出manager_id为1的员工
  SELECT * FROM s_emp WHERE manager_id = 1;

  -------
  
  - **where语句中的运算符**
  1. 算术运算符  
     \+ \- \* /   
  2. 比较运算符   
     >, <, >=, <=, =, !=, <>, ^=(后面三个表示的都是不等于)
  
  3. 逻辑运算符  
     AND  
     OR  
     NOT  
     BETWEEN AND(包含边界条件)  
  
  - **salary >= 1500 and salary <= 2000 等价于
      salary between 1500 and 2000**
  
  - <font color="#0000FF">练习：</font>

  – 找出在41部门且工资超过1200元的员工
  SELECT FROM s_emp WHERE dept_id = 41 AND salary > 1200;
  – 找出工资在1200至1400元之间的员工(包含)
  – 注意BETWEEN AND运算符使用时小的数值在前大的数值在后
  SELECT FROM s_emp WHERE salary BETWEEN 1200 AND 1400;
  SELECT * FROM s_emp WHERE salary >= 1200 AND salary <= 1400;

  - 特殊的运算符  
      in(list)     判断是否在指定的list列表中,值跟值之间用逗号分开

  salary in (1300, 1200, 1500) 等价于
  salary = 1300 or salary = 1200 or salary = 1500

  not in(list) 与上面相反

  salary not in (1300, 1200, 1500) 等价于
  salary <> 1300 and salary <> 1200 and salary <> 1500

      is null      是否为null  (= null) 不能等于null  
      is not null  是否不为null  
      like         模糊匹配 like '%a%'; 'a___'  
      '%' 表示匹配所有字符  
      as% 可以匹配 'as', 'assssss', 'asa','33333as77777' 但是不能匹配'aaaaa'  
      '_' 表示匹配单个字符  
      a_ 能匹配'aa', 'as'  
      不能匹配'ass', 'a'  
  
  - 示例：
      --- 找出在41或42或50部门的员工  
      select * from s_emp where dept_id in (41,42,50);
  
      select * from s_emp where dept_id = 41 or dept_id = 42 
      or dept_id = 50;  
      -- 找出没有提成率的员工  
      select * from s_emp where commission_pct is null;
  
  - 模糊匹配运算符 like, 它支持两个匹配模式：
      %   可以匹配任意多个字符
      _    可以匹配任意单个字符
  
  - <font color="#0000FF">练习：</font>

  – 找出职称中含有VP的员工
  SELECT FROM s_emp WHERE title LIKE ‘%VP%’;
  – 找出first_name中第2个字母是i的员工
  SELECT FROM s_emp WHERE first_name LIKE ‘_i%’;
  – 第二个字母是i并且长度只有2
  SELECT FROM s_emp WHERE first_name LIKE ‘_i’;
  – 找出last_name中倒数第3个字母是o的员工
  SELECT FROM s_emp WHERE last_name LIKE ‘%o__’;

  - 字符的转义
      利用 escape 关键字来转义如：  
      -- 找出first_name中以_开头的员工    
      select * from s_emp where like '\_%' escape '\';  
     -- 找出first_name以'A_'开头的员工  
     select * from s_emp where like 'AAA_%' escape 'A';
  
  - <font color="#0000FF">练习：</font>

  –找出firstname中第二字符为’‘的员工且不以’%’结尾的员工
  – first_name中不包含’\’且第二个字符为’\’的员工
  SELECT FROM s_emp WHERE
  first_name NOT LIKE ‘%\%’ AND
  firstname LIKE ‘_%’ ESCAPE ‘\’;
  – first_name中第二个字符为’\’且长度至少为3且不以’%’结尾的员工
  SELECT FROM s_emp WHERE
  firstname LIKE ‘_%’ AND
  first_name NOT LIKE ‘%\%’ ESCAPE ‘\’;
  – firstname中第二字符为’‘的员工且不以’%’结尾的员工
  SELECT * FROM s_emp WHERE
  first_name NOT LIKE ‘%\%’ ESCAPE ‘\’
  AND firstname LIKE ‘_%’ ESCAPE ‘\’;
  注意:escape影响的范围是单个like条件

  ---
  
  - 排序：
      在语句的后面带上 ORDER BY 子句即可,且只能是在所有字句的最后。  
     支持两种排序：  
      1.asc  升序, 默认就是升序  
      2.desc 降序
  
      语法：
  > ***....
  order by 列名|表达式|列的序号|别名 [asc|desc]   [,列名|表达式|...]***
  
  - 示例：  
      --- 找出员工名及所在部门、工资，并按部门号的升序排序，如果相同,再按工资的降序排序。  
      select first_name,dept_id,salary from s_emp order by dept_id, salary desc;  
  
      select first_name,dept_id,salary from s_emp order by 2, 3 desc;  
  
      select * from s_emp order by 2, 3 desc; // 也可以使用,将按照数据表里的原先列排序  
  
      select first_name,dept_id,salary "薪资" from s_emp order by 2, "薪资" desc;  
  
  - <font color="#0000FF">练习：</font>

  – 找出有提成率的员工
  select from s_emp where commission_pct is not null;
  – 找出费用超过10000元的订单编号以及支付方式
  select id,payment_type,total from s_ord where total > 10000;
  – 找出工资在950（含）至1200（含）元的员工姓名和职称
  select first_name, title, salary from s_emp where
  salary between 950 and 1200;
  – 找出名字中含有字母a的员工
  select from s_emp where first_name like ‘%a%’;
  – 找出名字中第二个字母是a的员工
  select from s_emp where first_name like ‘_a%’;
  – 查询出职称是Stock Clerk的员工全名、工资，并按工资的降序排列
  select first_name || ‘ ‘ || last_name 全名,salary from s_emp where title = ‘Stock Clerk’ order by salary desc;
  — 查询出年薪低于25000的员工名、职称、年薪，并按年薪降序排列（考虑提成率）
  select first_name, title,
  salary (1 + nvl(commission_pct, 0) / 100) 12 年薪 from
  s_emp where salary (1 + nvl(commission_pct, 0) / 100) 12 < 25000 order by 3 desc;
  select first_name, title,
  salary (1 + nvl(commission_pct, 0) / 100) 12 年薪 from
  s_emp where salary (1 + nvl(commission_pct, 0) / 100) 12 < 25000 order by 年薪 desc;
  – 查询出部门号为41或者42且工资大于1200少于1500(包含)且名字中不包含a的员工名,并按照年薪降序排列
  select first_name from s_emp where
  dept_id in (41, 42) and salary > 1200 and salary <= 1500
  and first_name not like ‘%a%’ order by salary (1 + nvl(commission_pct, 0) / 100) * 12 desc;
  ```