Home

hogen

Follow Excellence. Success will chase you.

Home About Github Email

oracle 学习之基础篇(一):oracle 基本概念和基本操作

从这个系列开始是老早好久前学习数据库 oracle 的一些最最基本的知识点笔记总结,这个系列所用的表都是安装 oracle 后系统默认提供的 emp 、dept、salgrade 几张表,请知悉

关于 oracle 的一些基本概念

  • 数据:在数据库领域看来,数据是存储的基本单位,包含文本,图片,视频,音频
  • 数据库:就是数据仓库,存储数据的地方,特指计算机设备中的硬盘,以二进制压缩文本的形式存放
    该文件不能直接操作,必须由各数据库公司提供的工具方可操作,该文件的格式是每个数据库公司内部定义的,不是统一规则
  • 数据库对象:在Oracle中,例如:表,视图,索引,函数,过程,触发器。。。
  • 关系型数据库:简单的说,以行列结构的形式,将数据库中的信息表示出来的对象,即二维表常见流行的关系型数据库:Oracle&MySQL/Oracle–>DB2/IBM—>SQLServer/Microsoft—

oracle数据库服务器由二部份组成

  • 实例:理解为对象,看不见的
  • 数据库:理解为类,看得见的,E:\app\Administrator\oradata\orcl*.DBF

oracle服务器与orcl数据库的关系

一个 oracle 数据库服务器中包括多个数据库,例如:orcl,orm,oa,bbs,tax,erp 等等在 E:\oracleDB\oradata\ 目录下,有多少个文件夹,就有多少个数据库,例如:orcl文件夹=orcl数据库我们向数据库中存储的所有数据库,最终都会存放在对应库的*.DBF 文件中,以二进制压缩形式存放在oracle服务器中创建数据库

注意: 我们在安装 oracle 时,已经创建好了一个数据库,默认名叫 orcl,除非你当时改了数据库名字

sqlplus、sqldeveloper和pl-sql develop,orcl 实例,orcl 数据库之间的关系

sqlplus是oracle11g自带的一个客户端黑屏界面工具,该工具可以连接到某个数据库的实例上,从而操作数据库sqldeveloper是oracle11g自带的一个客户端彩屏界面工具,该工具可以连接到某个数据库的实例上,从而操作数据库如果你觉得这二款客户端工具不喜欢,可以上网下载第三方的客户端工具pl-sql develop

以sys超级用户名,dba 角色,即超级管理员身份解锁 scott 方案/用户,并为 scott 设置一个密码为tiger

解锁用户:alter user scott/hr account unlock

设置密码:alter user scott/hr identified by tiger/lion

使用客户端 sqlplus 工具进入与退出 orcl 数据库

以超级管管理员角色进入:c:/>sqlplus / as sysdba

退出:sql>exit

以普通用户进入:c:/>sqlplus scott/tiger

注意:一般的,我们开发以普通用户的身份进入

访问其它用户下的对象

查询当前用户是谁

show user;

查询 scott 自己表空间下的所有对象时,可加,或不加用户名

select * from emp;
或
select * from scott.emp;

以 sysdba 身份解锁 hr 普通帐户

alter user hr account unlock;

以 sysdba 身份设置hr普通帐户的密码

alter user hr identified by lion;

当 scott 查询 hr 表空间下的所有表时,必须得加用户名

select * from hr.jobs;

在默认情况下,每个用户只能查询自已空间下的对象的权限,不能查询其它用户空间下的对象

以 sysdba 身份角色,授予 scott 用户查询所有用户空间下的对象权限

grant select any table to scott;

以 sysdba身份,撤销 scott 用户查询所有用户空间下的对象权限

revoke select any table from scott;

scott 自已查看自己所拥有的权限

select * from user_sys_privs;

从 scott 用户空间导航到 sysdba 用户空间

conn / as sysdba;

从 sysdba 用户空间导航到 scott 用户空间

conn scott/tiger;

从 scott 用户空间导航到 hr 用户空间

conn hr/lion;

查询 hr 用户空间中的所有对象

select * from tab;

从 hr 用户空间导航到 scott 用户空间

conn scott/tiger;

在 scott 用户空间下,查询 hr 用户空间下的 jobs 表,必须加上 hr 用户空间名

select * from hr.jobs;

数据库增删查改——————————select

创建新表new_emp,复制emp表中的结构和数据到new_emp表中

create table new_emp 
as 
select * from emp;

设置显示的列宽(字符型varchar2、日期型date),10个宽度位,a表示字符型,大小写均可

column ename format a12;
column hiredate format a10;

设置显示的列宽(数值型number),9表示数字型,一个9表示一个数字位,四个9表示四个数字位,只能用9

column empno format 9999;
column mgr format 9999;
column sal format 9999;
column comm format 9999;
column deptno format 9999;

设置一页显示80个条记录的高度

set pagesize 80;

使用/杠,执行最近一次的SQL语句

/

清屏,属于 SQL*PLUS 工具中的命令

host cls;

查询 emp 表的结构

desc emp;

查询 emp 表的所有内容,号表示通配符,表示该表中的所有字段,*号不能和具体字段一起使用

select * from emp;
或
select empno,ename,sal,deptno from emp;

查询emp表的员工编号,姓名,工资,部门号,列名,大小写不敏感,但提倡大写

select empno "编号",ename "姓名",sal "工资",deptNO "部门号" FROM Emp;

查询emp表的不重复的工作(distinct)

select distinct job from emp;

查询员工的编号,姓名,月薪,年薪(月薪*12)

select empno,ename,sal,sal*12 "年薪" from emp;

查询员工的编号,姓名,入职时间,月薪,年薪,年收入(年薪+奖金)

select empno "编号",ename"姓名",hiredate "入职时间",sal "月薪",sal*12 "年薪",sal*12+comm "年收入" from emp;

如果结果为 null,在 sqlplus 客户端工具中,是不显示null这个值的

解决 null 的问题,使用 NVL() 函数,NVL(a,b):如果a是NULL,用 b 替代;如果 a 是非 NULL,就不用b替代,直接返回a的值\

select NVL(null,10) from emp;结果有14行记录
select NVL(null,10) from dual;结果有1行记录
select empno "编号",ename"姓名",hiredate "入职时间",sal "月薪",sal*12 "年薪",sal*12+NVL(comm,0) "年收入" from emp;

注意:null 与具体数字运算时,结果为null

使用列别名,查询员工的编号,姓名,月薪,年薪,年收入(年薪+奖金),AS 大小写都可且可以省略AS,别名用双引号

select empno AS "编号",ename as "姓名",sal "月薪" from emp;
或
select empno AS 编号,ename as 姓名,sal 月薪 from emp;

select empno AS "编号",ename as 姓名,sal "月 薪" from emp不加双引号的别名不能有空格;加了双引号的别名可以有空格要加只能加双引号,不能加单引号,因为在oracle中单引号表示字符串类型或者是日期类型列名不能使用单引号,因为oracle认为单引号是字符串型或日期型

使用 dual 哑表或者伪表,使用字符串连接符号 ||,输出”hello world”,在 oracle 中from 是必须写的

select 'hello' || ' world' "结果" from dual

使用 sysdate,显示系统当前时间,在默认情况下,oracle 只显示日期,而不显示时间,格式:26-4月-15

select sysdate from dual;

使用字符串连接符号||,显示如下格式信息:****的薪水是****美元

select ename || '的薪水是' || sal || '美元' from emp; 

使用 spool 命令,保存SQL语句到硬盘文件 e:/oracle-test.sql,并创建sql文件

spool e:/oracle-test.sql;

使用 spool off 命令,保存 SQL语句到硬盘文件e:/oracle-test.sql,并创建sql文件,结束语句

spool off;

使用@命令,将硬盘文件 e:/crm.sql,读到orcl实例中,并执行文件中的sql语句

@ e:/crm.sql; 

数据库增删查改——————————where

查询emp表中20号部门的员工信息

select * from emp where deptno = 20;

查询姓名是SMITH的员工,字符串使用’’,内容大小写敏感

select * from emp where ename = 'SMITH';

查询1980年12月17日入职的员工,注意oracle默认日期格式(DD-MON-RR表示2位的年份)

select * from emp where hiredate = '17-12月-80';

查询工资大于1500的员工

select * from emp where sal > 1500;

查询工资不等于1500的员工【!=或<>】

select * from emp where sal <> 1500;

查询薪水在1300到1600之间的员工,包括1300和1600

select * from emp where (sal>=1300) and (sal<=1600);
或
select * from emp where sal between 1300 and 1600;

查询薪水不在1300到1600之间的员工,不包括1300和1600

select * from emp where sal NOT between 1300 and 1600;

查询入职时间在”1981-2月-20”到”1982-1月-23”之间的员工

select * from emp where hiredate between '20-2月-81' and '23-1月-82';

注意:

1)对于数值型,小数值在前,大数值在后

2)对于日期型,年长值在前,年小值在后

查询20号或30号部门的员工,例如:根据ID号,选中的员工,批量删除

select * from emp where (deptno=20) or (deptno=30);
或
select * from emp where deptno in (30,20);

查询不是20号或30号部门的员工

select * from emp where deptno NOT in (30,20);

查询姓名以大写字母S开头的员工,使用%表示0个,1个或多个字符

select * from emp where ename like 'S';
或
select * from emp where ename = 'S';
select * from emp where ename like 'S%';

注意:

凡是精确查询用=符号

凡是不精确查询用like符号,我们通常叫模糊查询

查询姓名以大写字母N结束的员工

select * from emp where ename like '%N';

查询姓名第一个字母是T,最后一个字母是R的员工

select * from emp where ename like 'T%R';

查询姓名是4个字符的员工,且第二个字符是I,使用_只能表示1个字符,不能表示0个或多个字符

select * from emp where ename like '_I__';

插入一条姓名为’T_IM’的员工,薪水1200

insert into emp(empno,ename) values(1111,'T_IM');

查询员工姓名中含有’‘的员工,使用\转义符,让其后的字符回归本来意思 【like ‘%\%’ escape ‘\’】

select * from emp where ename like '%\_%' escape '\';

插入一个姓名叫’的员工

insert into emp(empno,ename) values(2222,'''');

插入一个姓名叫’’的员工

insert into emp(empno,ename) values(2222,'''''');

查询所有员工信息,使用%或%%

select * from emp;
select * from emp where ename like '%';
select * from emp where ename like '%_%';

查询佣金为 null的员工

select * from emp where comm is null;

注意:null不能参数= 运算,null能参数number/date/varchar2 类型运算

查询佣金为非null的员工

select * from emp where comm is not null;

查询无佣金且工资大于1500的员工

select * 
from emp 
where (comm is null) and (sal>1500); 

查询工资是1500或3000或5000的员工

select * 
from emp 
where sal in (4000,10000,1500,3,300,3000,5000);

查询职位是”MANAGER”或职位不是”ANALYST”的员工(方式一,使用!=或<>)

select *
from emp
where (job='MANAGER') or (job<>'ANALYST');

查询职位是”MANAGER”或职位不是”ANALYST”的员工(方式二,使用not)

select *
from emp
where (job='MANAGER') or (not(job='ANALYST'));

hogen

2017-09-09
Home About Github Email