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

Oracle 是什么

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

常规命令

客户端连接的命令为：

conn 用户/密码

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

用户登录步骤：

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

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

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

5.使用zs登录 conn zs/oracle

6.创建表格student:

7.插入数据

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

修改用户密码：

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

Select * from dba_tablespace;

基础表查询

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;

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

select sal from emp where job = ‘PRESIDENT’;

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

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

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

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

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

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

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

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

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

select * from emp where comm > sal;

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

select * from emp order by hiredate desc;

SQL基础，约束,表关系

varchar2和char的区别：

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

nchar:

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

日期类型date（年月日）:

二进制文件 BLOB CLOB

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

创建表格:

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

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

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

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

给uage字段添加约束1-120

实体完整性

域完整性

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

检查约束(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)

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妻子表 我们国家的关系一对一

多对多关系

SQL语言基础

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

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

系统的数据字典user_objects

Oracle简单查询语句

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

算术运算

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

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; 

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> 

最后

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