Oracle数据库入门教程（作者原创）_oracle数据库
| 阿里云国内75折 回扣 微信号：monov8 |
| 阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6 |
文章目录
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;
- 查询职位(JOB)为’PRESIDENT’的员工的工资
select sal from emp where job = ‘PRESIDENT’;
- 查询佣金(COMM)为0 或为NULL 的员工信息
select * from emp where comm = 0 or comm is null;
- 查询入职日期在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’);
- 显示10 号部门的所有经理(‘MANAGER’)和20 号部门的所有职员(‘CLERK’)的详细信息
select * from emp where (deptno = 10 and job = ‘MANAGER’) or (deptno =
20 and job = ‘CLERK’);
- 显示姓名中没有’L’字的员工的详细信息或含有’SM’字的员工信息
select * from emp where ename not like ‘%L%’ or ename like ‘%SM%’;
- 显示各个部门经理(‘MANAGER’)的工资
select sal as 工资 , deptno as 部门 from emp where job = ‘MANAGER’;
- 显示佣金(COMM)收入比工资(SAL)高的员工的详细信息
select * from emp where comm > sal;
- 按照入职日期由新到旧排列员工信息
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>
最后
事务相关的专门出一期专题谢谢