Oracle数据库入门教程（作者原创）

文章目录

---

Oracle 是什么

Oracle系统，即是以Oracle关系数据库为数据存储和管理作为构架基础，构建出的数据库管理系统。 Oracle，世界第一个支持SQL语言的商业数据库，定位于高端工作站，以及作为服务器的小型计算机，如IBM P系列服务器、HP的Integraty服务器、Sun Fire服务器。Oracle公司的整个产品线包括数据库服务器、企业商务应用套件、应用开发和决策支持工具。

常规命令

客户端连接的命令为：

conn 用户/密码

Show user —显示当前登录的用户

用户登录步骤：

创建用户，一定要先使用管理员，管理员才有创建用户的权限
1.创建用户的表空间 tablespace,表空间要分配给指定的用户，用户登录后的创建的表格和数据，就放到指定的表空间中，用户的数据应该放到用户的表空间

create tablespace tablespace_zs datafile ‘D:\oraclexe\app\oracle\oradata\XE\zsdata.dbf’ size 10m;
tablespace_zs 逻辑名称 zsdata.dbf物理文件，在磁盘上方数据的文件

2.创建用户，指定表空间

create user zs identified by oracle default tablespace tablespace_zs;
zs 为账号 oracle为密码 tablespace_zs为指定的表空间

3.现在好不能使用conn zs/oracle 缺少权限

4.重新使用sys登录 conn sys/oracle as sysdba ,给zs分配连接的权限和创建表格等的权限

grant connect,resource to zs; connect (create session)为连接权限
resource为创建资源的权限（比如创建表格）
grant create session,resource to zs;

5.使用zs登录 conn zs/oracle

6.创建表格student:

create table student(id int primary key,sno varchar2(10),sname
varchar2(50));

7.插入数据

oracle默认不提交数据，数据翻到内存中，需要程序员手工使用commit提交数据，才能将数据写入磁盘的文件，mysql默认提交，不需要程序员写commit提交

修改用户密码：

查看系统的表空间的方法：

Select * from dba_tablespace;

注意：如果在创建用户是没有指定表空间，那么使用users表空间
在创建了用户后一般在创建了数据后，不删除，一般为停用该用户，停用后可以再启用该用户：（sys登录才能做用户停用和启用）
启用用户（sys登录）

基础表查询

01.查询emp表的所有数据

select * from emp;

02.查询emp表中ename,job,sal几列,其中sal列在结果集中以别名salary查询

select ename,job,sal salary from emp;

03.查询emp表中deptno,ename,年工资,并将年工资命名别名total salary

select deptno,ename,nvl(sal,0)*12 as “total salary” from emp;

04.查看emp表的结构

desc emp;

05.查询emp表中雇员号,雇员的名字,工作岗位,入职日期

select empno,ename,job,hiredate from emp;

06.查询员工表中部门编号,重复的部门编号只查询一次

select distinct deptno as ”部门编号” from emp;

07.在结果集中雇员名和工作岗位相连用"空格逗号空格"分隔,并将列头查询为Employee and Title。

select ename || ’ , ’ || job as “Employee and Title” from emp;

1. 查询职位(JOB)为’PRESIDENT’的员工的工资

select sal from emp where job = ‘PRESIDENT’;

1. 查询佣金(COMM)为0 或为NULL 的员工信息

select * from emp where comm = 0 or comm is null;

1. 查询入职日期在1981-5-1 到1981-12-31 之间的所有员工信息

select * from emp where hiredate between
to_date(‘1981/05/01’,‘yyyy/mm/DD’) and
to_date(‘1981/12/31’,‘yyyy/mm/DD’);

1. 显示10 号部门的所有经理(‘MANAGER’)和20 号部门的所有职员(‘CLERK’)的详细信息

select * from emp where (deptno = 10 and job = ‘MANAGER’) or (deptno =
20 and job = ‘CLERK’);

1. 显示姓名中没有’L’字的员工的详细信息或含有’SM’字的员工信息

select * from emp where ename not like ‘%L%’ or ename like ‘%SM%’;

1. 显示各个部门经理(‘MANAGER’)的工资

select sal as 工资 , deptno as 部门 from emp where job = ‘MANAGER’;

1. 显示佣金(COMM)收入比工资(SAL)高的员工的详细信息

select * from emp where comm > sal;

1. 按照入职日期由新到旧排列员工信息

select * from emp order by hiredate desc;

SQL基础，约束,表关系

注意：字符类型varchar2或者varchar(varchar2为oracle特有的类型，mysql没有该类型，mysql为varchar)，使用该类型必须给定长度
sname varchar2(50)

varchar2和char的区别：

varchar2或者varchar为可变长度的字符类型，比如varchar2(10),实际保存了5个字符abcde,剩下的5个长度的空间不会去补充空格占满整个10个长度，而是自动缩小到五个长度，所以叫可变长度的字符类型，好处为节约空间。

字符类型char，定义是要指定长度，比如char(10)， 不可以变的长度的字符类型，比如保存了2个字符ab,剩下的8个长度补空格填满，浪费空间，一般用于长度比较小的字段，比如性别char(3)
保存数据男或女

nchar:

nchar(n-native 本地，定义的数据的长度和本地的计算机系统的环境有关系)和nvarchar2和char及nvarchar2的区别，nchar和nvarchar2当前的电脑的语言环境有关系，window安装的中文的版本，使用中文的环境，那么使用nchar(2)，保存2个中文，使用英文的环境，那么使用nchar(2)，保存2个英文;

日期类型date（年月日）:

二进制文件 BLOB CLOB

可以保存4G的二进制文件（比如：图片），实际应用不推荐将图片文件直接保存到数据库（读写的效率低），而是在数据库中只保存图片的名称，图片的文件保存到文件服务器上（七牛云、阿里云oss、Fastdfs分布式文件系统、本地磁盘–不推荐，将文件保存到MongoDB数据库中-非关系型数据库

to_timestamp()支持毫秒
to_date()不支持毫秒

insert into t1 values(7,to_date(‘2022-1-6 10:11:12.888’,‘yyyy-mm-dd
hh:mi:ss’)

insert into t1 values(7,to_timestamp(‘2022-1-6
10:11:12.888’,‘yyyy-mm-dd hh:mi:ss.ff’));

insert into t1 values(7,to_timestamp(‘2022-1-6
10:11:12.888’,‘yyyy-mm-dd hh24:mi:ss.ff’)); java中书写的格式new
SimpleDateFormat(“yyyy-MM-dd HH:mm:ss.SSS”);

oracle使用sysdate获取当前的时间：

创建表格:

创建用户表(uuid uname upwd usex uaddress)
create table users( uuid
number(12),
uname nvarchar2(20) not null,
upwd varchar2(20) not null,
usex char(2), uaddress varchar2(20));

在用户表基础上添加uage字段

alter table users add(uage number(8));

给usex字段添加约束为男或者女

alter table users add constraint ck_sex check(usex in(‘男’,‘女’));

给uage字段添加约束1-120

alter table users add constraint ck_uage check(uage between 1 and 120
);

实体完整性

约束：主键约束 primary key 能否使用多个字段，同时成为主键？可以 student_score学生成绩表 student(sno主键,sname) course(cno主键,cname) student_score(sno,cno,score)
sno和cno同时作为主键—复合主键

域完整性

保证每一列的数据的完整性和合法性。 约束 ： 数据类型: number int varchar2 char
nvarchar2 nchar

非空约束(not null):

默认值约束(default):

注意：唯一约束的字段能否为空？可以为空(和主键的区别，主键唯一不能为空，唯一约束 唯一能为空)

检查约束(check)：(注意: mysql能创建检查约束，语法可以通过，但是不生效)

SQL> create table s3(id number(2) primary key,name varchar2(10) not null, sex nchar(1) default ‘男’);

Table created

SQL> alter table s3 add constraint ch_sex check(sex in(‘男’,‘女’));

Table altered

SQL> insert into s3 values(1,‘zs’,‘性’);

insert into s3 values(1,‘zs’,‘性’)

ORA-02290: 违反检查约束条件 (ZS.CH_SEX)

SQL> insert into s3 values(1,‘zs’,‘女’);

1 row inserted

SQL> commit;

Commit complete

SQL> alter table s3 add constraint ch_sex check(sex=‘男’ or ‘女’);

alter table s3 add constraint ch_sex check(sex=‘男’ or ‘女’)

ORA-00920: 无效的关系运算符

SQL> alter table s3 add constraint ch_sex check(sex=‘男’ or sex=‘女’);—规定输入的值只能是男或者女，输入其他的值违反约束

Table altered

SQL> insert into s3 values(3,‘ww’,‘女’);—合法的值

1 row inserted

SQL> insert into s3 values(3,‘ww’,‘a’);—非合法的值，违反约束

insert into s3 values(3,‘ww’,‘a’)

ORA-02290: 违反检查约束条件 (ZS.CH_SEX)

SQL>
SQL> alter table s3 add(age number(3));

Table altered

SQL> alter table s3 add constraint ch_age check(age >= 0 and age <= 200);—规定年龄的范围为0-200之间

Table altered

SQL> insert into s3 values(5,‘lw’,‘女’,50);—合法的值

1 row inserted

SQL> insert into s3 values(7,‘kw’,‘女’,201);—非合法的值，违法约束了，不能插入该值

insert into s3 values(7,‘kw’,‘女’,201)

ORA-02290: 违反检查约束条件 (ZS.CH_AGE)

SQL>

约束：外键约束 foreign key

创建外键约束：

注意：在实际的工作中，如果两个表的数据量大，在做查询时会做外键约束的，就会影响查询的性能，所以在实际的工作中，常常是不创建外键约束，只是程序员的意识中知道存在这个约束

级联关系： 级联删除：删除主表dept的数据，同时删除关联的主表emp中的员工数据。

自定义完整性
书写存储过程或者触发器（不在使用了）实现自定义的约束，比如针对工资自定义的约束，工资只能增加，不能减少

删除约束

表之间的关系

1对1 Husband丈夫表 wife妻子表 我们国家的关系一对一

注意:一对一是一对多的特例，下面的表格的数据为一对多（一个妻子，两个丈夫）

一对多
人和手机（一个人有多个手机） 学校和食堂（一个学校有多个食堂） 父亲和孩子（一个父亲有多个孩子）

多对多
老师和学生（一个老师和教多个学生，一个学生可以上多个老师的课） 学生和课程（一个学生可以选多门课程，一个课程可以被多个学生选择）

多对多关系

老师和学生（一个老师和教多个学生，一个学生可以上多个老师的课）
商品和订单（一个的订单可以有多个商品，一个商品可以出现到多个订单） 表1：
Teacher (tid tname) 主键为tid 1 张老师 2 王老师 表2： Student(sid tname) 主键为sid 1
张三 2 李四 表3：中间表 Teacher_student(tid sid) 主键为tid 和sid 成为联合主键
tid和sid不能同时重复 tid sid 1 1 1 2 2 1 2 2

SQL语言基础

rollback的使用，回滚，相对于提交，如果添加数据insert into，没有commit,数据在内存中，没有进入磁盘的文件xxx.dbf,在没有提交之前，可以进行数据的回滚，撤销刚才的插入数据的操作，一旦提交就不能回滚，所以回滚一定要在提交之前做

序列
create sequence seq_gid; seq_gid为序列的名称，为自定义

使用序列，填充主键，实现了主键的自动增加

系统的数据字典user_objects

删除delete

update 修改

Oracle简单查询语句

1.执行备份的sql,将sql复制到客户端中的编辑器界面使用f8执行，创建三个表emp,dept,salgrade

查询语句的书写有顺序遵循下面的顺序

select 字段列表
from 表名
where 查询条件
group by子句
order by 排序字段

投影查询（查询部分的列）

算术运算

在查询列表中,可以对NUMBER型的字段使用算数原算符号(+,-,*,/)
注意：如果该列的值为null，要使用nvl()函数，进行处理才能进行运算

字符类型的连接 拼接运算符 ||: 表达字符（串）的拼接，可以将某几列或某列与字符串拼接在一起

SQL> select '员工编号为'||empno||'员工的姓名为'||ename||'员工的年薪为'||nvl(sal,0)*12  from emp;
 
'员工编号为'||EMPNO||'员工的姓
--------------------------------------------------------------------------------
员工编号为7369员工的姓名为SMITH员工的年薪为0
员工编号为7499员工的姓名为ALLEN员工的年薪为22440
员工编号为7521员工的姓名为WARD员工的年薪为17820
员工编号为7566员工的姓名为JONES员工的年薪为40590
员工编号为7654员工的姓名为MARTIN员工的年薪为17820
员工编号为7698员工的姓名为BLAKE员工的年薪为38940
员工编号为7782员工的姓名为CLARK员工的年薪为33660
员工编号为7788员工的姓名为SCOTT员工的年薪为40920
员工编号为7844员工的姓名为TURNER员工的年薪为21120
员工编号为7876员工的姓名为ADAMS员工的年薪为15840
员工编号为7900员工的姓名为JAMES员工的年薪为13860
员工编号为7902员工的姓名为FORD员工的年薪为40920
员工编号为7934员工的姓名为MILLER员工的年薪为18480
员工编号为7839员工的姓名为KING员工的年薪为67320
 
14 rows selected
 
SQL> 

查询的列给个别名

SQL> select empno as “员工编号” , ename as “员工姓名” from emp;

使用distinct实现对某个字段的值去掉重复

SQL> select distinct deptno from emp;
 
DEPTNO
------
    30
    20
    10

条件查询Where子句

SQL> select * from emp where 1=1;

<>或者!=

SQL> select * from emp where empno != 7369;

<, >,>=,<=

SQL> select * from emp where empno > 7369;

between … and… 等同于>= and <=

SQL> select * from emp where empno >= 7369 and empno <=7566;

is null 或者是 is not null 判断该列的值为null

SQL> select * from emp where sal is null; 

and并且 ，or或者 ,not不 is null,is not null, like,not like, in not in,NOT BETWEEN AND

函数及分组

函数分类

单行函数：字符函数，数值函数，日期函数，转换函数 概念：对于每一行记录，返回一个结果
字符函数：字符函数接受字符参数，这些参数可以是表中的列，也可以是一个字符串表达式

函数说明

CONCAT(X,Y)    连接字符串X和Y
INSTR(X,STR[,START][,N)    从X中查找str，可以指定从start开始，也可以指定从n开始
LENGTH(X)    返回X的长度
LOWER(X)    X转换成小写
UPPER(X)    X转换成大写
LTRIM(X[,TRIM_STR])    把X的左边截去trim_str字符串，缺省截去空格
RTRIM(X[,TRIM_STR])    把X的右边截去trim_str字符串，缺省截去空格
TRIM([TRIM_STR  FROM]X)    把X的两边截去trim_str字符串，缺省截去空格
REPLACE(X,old,new)    在X中查找old，并替换成new
SUBSTR(X,start[,length])    返回X的字串，从start处开始，截取length个字符，缺省length，默认到结尾
NVL(X,Y)    X为null,返回y，不为null返回x
NVL2(X,Y1,Y2)    X不为null，返回y1,否则返回y2

转换函数 ：

TO_CHAR(),TO_NUMBER(),TO_DATE()，to_timestamp()
TO_CHAR(d|n[,fmt]):把日期和数字转换为制定格式的字符串

多行函数：

聚合函数 
count() 统计表格的行数，个数 注意:该行的值为null，将无法统计
提示：这里对文章进行总结：
例如：以上就是今天要讲的内容，本文仅仅简单介绍了pandas的使用，而pandas提供了大量能使我们快速便捷地处理数据的函数和方法。

分组语句group by,该语句写到order by 之前

SQL> ---统计出各个部门的薪水的平均值
SQL> select avg(sal) from emp;---所有的部门
 
  AVG(SAL)
----------
   2391.25
 
SQL> select avg(sal) from emp group by deptno;---按照部门进行了分组，求各个组的平均值
 
  AVG(SAL)
----------
1833.33333
  2880.625
   2738.75
 
SQL> select deptno,avg(sal) from emp group by deptno;---按照部门进行了分组，求各个组的平均值
 
DEPTNO   AVG(SAL)
------ ----------
    30 1833.33333
    20   2880.625
    10    2738.75
 
SQL> select deptno,avg(sal) from emp group by deptno order by deptno;---按照部门进行了分组，求各个组的平均值
 
DEPTNO   AVG(SAL)
------ ----------
    10    2738.75
    20   2880.625
    30 1833.33333
 
SQL> select deptno,sum(sal) as 各个部门的总工资 from emp group by deptno order by deptno;---按照部门进行了分组，各个部门的总工资
 
DEPTNO         各个部门的总工资
------ ----------------
    10            10955
    20          11522.5
    30            11000
 
SQL> select deptno,max(sal) as 部门最高工资,min(sal) 部门最低工资 from emp group by deptno order by deptno;
 
DEPTNO       部门最高工资       部门最低工资
------ ------------ ------------
    10         5610         1000
    20         3410         1320
    30         3245         1155
 
SQL> select deptno,count(1) 部门的人数 from emp group by deptno order by deptno;
 
DEPTNO      部门的人数
------ ----------
    10          4
    20          4
    30          6
 
SQL> select deptno 部门名称,count(1) 部门的人数 from emp group by deptno order by deptno;
 
    部门名称      部门的人数
-------- ----------
      10          4
      20          4
      30          6
 
SQL> 
SQL> ---按照工种进行分组 可以统计各个工种下的人数
SQL> select job,count(1) from emp group by job;
 
JOB         COUNT(1)
--------- ----------
CLERK              4
SALESMAN           4
PRESIDENT          1
MANAGER            3
ANALYST            2
 
SQL> 

SQL> select deptno,avg(sal) from emp group by deptno order by deptno;---按照部门进行了分组，求各个组的平均值
 
DEPTNO   AVG(SAL)
------ ----------
    10    2738.75
    20   2880.625
    30 1833.33333
 
SQL> select deptno,avg(sal) from emp group by deptno order by avg(sal);---按照部门进行了分组，求各个组的平均值
 
DEPTNO   AVG(SAL)
------ ----------
    30 1833.33333
    10    2738.75
    20   2880.625
 
SQL> 

Oracle高级查询语句

1.等值连接

SQL> ---查询出员工和员工的部门的名称是什么（涉及两个表emp,dept的联合查询，找等值条件  emp.deptno = dept.deptno）
SQL> ---1.等值查询
SQL> select e.*,d.dname from emp e,dept d where e.deptno = d.deptno;

笛卡尔积，笛卡尔积(没有等值条件) emp 14条 dept 4条 结果 14*4 = 56条记录

SQL> select e.empno,e.ename,d.dname from emp e,dept d;

内连接（和等值查询的结果一样） inner join

SQL> select e.empno,e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno;

外连接 left [outer] join 左外联，right [outer] join 右外联，full join完全连接
left [outer] join 左外联：
rows selected

SQL> ----3.left [outer] join 左外联，以左表为准 emp e left join dept d,left join左边的emp为左表，left join右边 dept为右表
SQL> 左外联，以左表emp为准,不管关联的字段deptno是否在右表dept中是否有对应的数据，左表emp的数据全部要显示(注意：查询结果合理，实战中推荐使用左外联) 
左外联，以左表emp为准,不管关联的字段deptno是否在右表dept中是否有对应的数据，左表emp的数据全部要显示 
SQL> select e.empno,e.ename,d.dname from emp e left join dept d on e.deptno = d.deptno;

right [outer] join 右外联,右外联，以右表dept为准,不管关联的字段deptno是否在左表emp中是否有对应的数据，右表dept的数据全部要显示,坐标emp中关联的deptno为null的记录不显示

full [outer] join 完全连接 都要显示左表和右表的数据，不管左表emp的关联字段deptno为null,还是右表dept的数据deptno=40被左表emp引用(注意：mysql不支持)

联合查询中的三表联合查询

SQL> select e.empno,e.ename,e.job,e.sal,d.dname,g.grade
  2  from emp e
  3  left join dept d on e.deptno = d.deptno
  4  left join salgrade g on e.sal between g.losal and g.hisal
  5  order by e.empno
  6  ;

如何使用(+)进行多表查询（注意：该语法在别的数据库不支持，所以不通用，不推荐使用）

SQL> select e.empno, e.ename, d.deptno, d.dname
  2  from emp e, dept d
  3  where e.deptno(+) = d.deptno;
 
EMPNO ENAME      DEPTNO DNAME
----- ---------- ------ --------------
 7499 ALLEN          30 SALES
 7521 WARD           30 SALES
 7566 JONES          20 RESEARCH
 7654 MARTIN         30 SALES
 7698 BLAKE          30 SALES
 7782 CLARK          10 ACCOUNTING
 7788 SCOTT          20 RESEARCH
 7844 TURNER         30 SALES
 7876 ADAMS          20 RESEARCH
 7900 JAMES          30 SALES
 7902 FORD           20 RESEARCH
 7934 MILLER         10 ACCOUNTING
 7839 KING           10 ACCOUNTING
                     40 OPERATIONS
 
14 rows selected

使用using来实现联合查询

SQL> select e.empno,e.ename,d.deptno,d.dname from emp e inner join  dept d using(deptno);
 
select e.empno,e.ename,d.deptno,d.dname from emp e inner join  dept d using(deptno)
 
ORA-25154: USING 子句的列部分不能有限定词
 
SQL> select e.empno,e.ename,deptno,d.dname from emp e inner join  dept d using(deptno);
 
EMPNO ENAME      DEPTNO DNAME
----- ---------- ------ --------------
 7499 ALLEN          30 SALES
 7521 WARD           30 SALES
 7566 JONES          20 RESEARCH
 7654 MARTIN         30 SALES
 7698 BLAKE          30 SALES
 7782 CLARK          10 ACCOUNTING
 7788 SCOTT          20 RESEARCH
 7844 TURNER         30 SALES
 7876 ADAMS          20 RESEARCH
 7900 JAMES          30 SALES
 7902 FORD           20 RESEARCH
 7934 MILLER         10 ACCOUNTING
 7839 KING           10 ACCOUNTING
 
13 rows selected
 
SQL> 

最后

事务相关的专门出一期专题，谢谢！