【第28天】学习Oracle（11G）的准备工作与SQL语句基本概述

• 0 一些准备工作

  • 0.1 如何在Windows系统上完全卸载Oracle

  • 0.2 Oracle的管理工具（开发环境）、管理组件

  • 0.3 忘记密码（sqlplus）

• 1 Oracle中常用的数据类型

  • 1.1 sqlplus临时/Windows永久修改date类型存储的日期格式
    

• 2 Oracle的操作—SQL

  • 2.1 DDL（Data Definition Language，数据定义语言）

    • 2.1.1 create（创建）

    • 2.1.2 alter（修改）

    • 2.1.3 drop （扔掉）

    • 2.1.4 truncate（截断）

  • 2.2 DML（Data Manipulation Language，数据定义语言）

    • 2.2.1 insert（插入）

    • 2.2.2 delete（删除）

    • 2.2.3 update（更新）

  • 2.3 DQL（Data Query Language，数据查询语言）

    • 2.3.1 select （查询）

  • 2.4 DCL（Data Control Language，数据控制语言）

    • 2.4.1 grant （授权）

    • 2.4.2 revoke （取消授权）

  • 2.5 TCL（Transaction Control Language，事务控制语言）

    • 2.5.1 commit （提交）

    • 2.5.2 rollback（回滚）和 savepoint（保存还原点）

0 一些准备工作

0.1 如何在Windows系统上完全卸载Oracle

Step1 win+R运行regedit，打开注册表删除所有关于oracle的相关服务，打开HKEY_LOCAL_MACHINE—>SYSTEM—>ControlSet001—>Services—>将命名为Oracle****的服务全部删掉。
Step2 重启电脑，所有的oracle服务已不再windows加载。
Step3 删除oracle对应的文件，包括app目录（oracle的主目录）、C:\Program Files\Oracle。

0.2 Oracle的管理工具（开发环境）、管理组件

• 管理组件：cmd中输入services.msc—>服务、regedit—>注册表，遇到Oracle软件方面的异常时，先检查相关服务（如OracleServiceORCL、Oracle的listener监听）是否开启。
• 管理工具（开发环境）：sqlplus（关闭对话框前若有对数据库的DML操作，一定要commit进行提交！后面解释原因）、Navicat、PL/SQL

0.3 忘记密码（sqlplus）

以修改本地系统管理员的账户密码为例：

1.运行（win＋R），执行sqlplus /nolog;，先使用无账户登录
2.进入sqlplus后，执行conn /as sysdba;，连接系统管理员"SYS"（“SYSTEM”为本地系统管理员账户，“SYS”为总管理员账户）。
3.执行alter user system identified by aaa123;，在管理员账户下修改密码。
4.执行conn system/aaa123;，在系统管理员角色下切换到本地系统管理员账户。（普通切换用户也这么用，conn 用户名/密码）

1 Oracle中常用的数据类型

• 字符型（Oracle中字符型加单引号’'而不是双引号）：

  • varchar2(20) ： 可变长度，范围在0-4000字节内。括号中为预定义分配的数据长度。
  • char(10)： 固定长度 ，范围在0-2000字节内。括号中为定义的长度，定义为1时，常用来表示性别。
  • long： 存储的是可变长字符串，最大长度限制是2GB。

• 数值型：

  • number(5,2)： 参数列表中第一位代表整数部分，第二位代表小数部分，当第二位为负数时，则代表整数从最小位数取几位整，例如number(5, -2)，存入12345，真正存在数据库中的数据为12300。

• 日期型：

  • date： 存入日期使用的数据类型，精确到秒。

       yyyy   年（阿拉伯数字）/year  年（英文）
       mm     月（阿拉伯数字）/month 月（显示“X月”）  
       ddd    一年的第几天
       dd     一个月中的第几天
       d      一周中的第几天
       hh     时（12小时制）
       hh24   时（24小时制）
       mi     分钟
       ss     秒
       xff    毫秒
       ff3    毫秒（保留三位）
    

  • timestamp： 时间戳（包含毫秒数）。

1.1 sqlplus临时/Windows永久修改date类型存储的日期格式

• sqlplus中临时修改日期格式： alter session set nls_date_format = ‘目的修改格式（例：yyyy-mm-dd hh24:mi:ss）’;
• Windows永久修改日期格式： 在系统环境变量中添加键“nls_date_format”，值“yyyy-mm-dd hh24:mi:ss”

2 Oracle的操作---SQL

        SQL（Structured Query Language，结构化查询语言）。当语句涉及到表内数据（增删改查表中数据） 时，注意语句大小写敏感，每个语句结束时加分号。

2.1 DDL（Data Definition Language，数据定义语言）

2.1.1 create（创建）

• 创建新用户：
  create user 用户名 identified by 密码;

查看当前用户：show user;

• 创建一张表（字段命名需要大写）：

create table 表名(
  ---字段名 数据类型, 最后一行字段名不需要写“,”
  name varchar2(20),
  birthday date,
  salary number(5,2)
);

查看已经存在的表结构：desc 表名;

同一个用户下，表名不能重复。

2.1.2 alter（修改）

• 解锁一个用户：
  alter user 用户名 account unlock;
• 锁定一个用户：
  alter user 用户名 account lock;

若访问被锁定的用户，会使当前连接的用户断开，此时不再连接到oracle，没有任何用户 ，不能执行操作，需要重新连接一个用户。

• 修改用户密码：
  alter user 用户名 identified by 密码;

• 修改已经存在的表名：
  alter table 旧表名 rename to 新表名;

• 建表后，修改字段名：
  alter table 表名 rename column 旧字段名 to 新字段名;

• 建表后 ，增加字段：
  alter table 表名 add 字段名 数据类型;

• 建表后，修改数据类型：
  alter table 表名 modify 字段名 数据类型;

• 建表后，删除字段：
  alter table 表名 drop column 字段名;

建表并插入了数据后最好不要修改数据类型，建议删掉字段重新再加一个，因为表中已存在的数据会对字段修改数据类型造成限制。

2.1.3 drop （扔掉）

• 删除用户：
  drop user 用户名;

• 删除表：
  drop table 表名;

• 完全删除表，不存入回收站：
  drop table 表名 purge;

2.1.4 truncate（截断）

• 删除表
  truncate table 表名;

• 同样是删除表，truncate和drop有什么区别？

  • truncate是删除表的数据和表空间，而表结构不会被删除，被删除的数据不会被存放至回收站。
  • drop是将表结构和数据全部删除 ，被删除的表会存放至回收站（加purge不存放），所以表空间不会被删除。

（表空间：创建用户时将自己所占一部分空间分配给这个新用户一些）

2.2 DML（Data Manipulation Language，数据定义语言）

2.2.1 insert（插入）

• 若加入的数据与字段名、名字的个数及顺序完全一致，表名后面可以不加列名
  insert into 表名(字段名) values(字段名对应的值，不写字段名);
  insert into 表名 values(按表中字段顺序写入对应的值，不写字段名);

2.2.2 delete（删除）

delete from 表名 [where];
如果不加where，对这个字段所有值都会被删除

2.2.3 update（更新）

update 表名 set 字段名 = 字段值 [where];
如果不加where，对这个字段所有值都会修改

2.3 DQL（Data Query Language，数据查询语言）

2.3.1 select （查询）

select 列名（如果需要表中所有信息，可以用“*”，但考虑到查询效率，尽量不使用） from 表名[可加where条件过滤出查询结果或group by将查询结果数据分组，order by 将查询结果按字段排序];

查询当前用户已经创建多少表
select table_name from user_tables;

• like：模糊查询
  %:代表任意位的任意字符
  _ :代表一位上的任意字符
  select * from 表名 where name like ‘aaa_’;（匹配表中以“aaa”开头加任意一位字符）
  select * from 表名 where name like ‘aaa%’;（匹配表中以“aaa”开头的所有字符）
  select * from 表名 where name like ‘%aaa%’;（匹配表中包含“aaa”所有字符）

• escape：逃离符
  通过指定一个字符位进行逃离，来保证like之后出现的字符串中的字符看作是普通字符
  select * from 表名 where name like 'aaa,_’ escape ‘,’;（匹配表中以“aaa”开头加任意一位字符）
  select * from 表名 where name like ‘aaa.__.__’ escape ‘.’;（匹配表中以“aaa_”开头加任意一位字符再加一个“_”再加任意一位字符）

2.4 DCL（Data Control Language，数据控制语言）

系统权限： 系统规定用户使用数据库的权限（对用户而言)。

实体权限： 某种权限用户对其它用户的表或视图的存取权限（是针对表或视图而言的）。

DBA： 拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。

RESOURCE： 拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。

CONNECT： 拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。

对于普通用户： 授予connect, resource权限。
对于DBA管理用户： 授予connect，resource, dba权限。

系统权限只能由DBA用户授出：sys, system(最开始只能是这两个用户)。普通用户通过授权可以具有与system相同的用户权限，但永远不能达到与sys用户相同的权限，system用户的权限也可以被回收。

（以上内容摘自junmail@优快云大神的整理。）

2.4.1 grant （授权）

• 给用户授权：
  grant dba to 用户名;

2.4.2 revoke （取消授权）

• 取消用户授权：
  revoke dba from 用户名;

2.5 TCL（Transaction Control Language，事务控制语言）

2.5.1 commit （提交）

• DDL的每一条语句前后，Oracle都将提交当前的事务。若用户使用insert命令将记录插入到数据库后，又执行了一条DDL语句，此时来自insert命令的数据将被提交到数据库。当DDL语句执行完成时，前面的语句将都会被自动提交，不用回滚。

• DML命令如果没有提交，将不会被其他会话看到。除非在DML命令之后执行了DDL命令或DCL命令，或用户退出会话，或终止实例，此时系统会自动发出commit命令，使未提交的DML命令提交。

2.5.2 rollback（回滚）和 savepoint（保存还原点）

savepoint t1;
rollback to savepoint t1;

在执行commit之前保存的savepoint环境，可在非DDL语句（其中包含commit语句）和commit语句执行前使用rollback返回到savepoint保存的环境。如果执行commit，当前事务的所有保存点被删除。