Oracle 数据库 简单操作命令（新手看 可以收藏哟）

Oracle 数据库 简单操作命令（新手看 可以收藏哟）

（💖下面的案例是Oracle 10g ，可以看下句型作参考）

一、认识 Oracle

1、简介

oracle 创始人拉里·埃里森，Oracle也叫“甲骨文公司”，是一个公司的名称，Oracle是一个关系型（表）数据库，关系可以看成由行和列交叉组成的二维表格

2、需要了解的知识点

① 数据（date）：数字、文字、图形、视频等

② 数据库（datebase）：管理数据的仓库

③ 数据库管理系统（DBMS）:Oracle、MySQL

④ 表中的列称为属性或字段

⑤ 域-值域：属性的取值范围

⑥ 表中的行称为元组或记录

⑦ 主键：能够唯一的标识一条记录的属性或属性组（联合主键）

⑧ 外键：在A表中不是主键，但是在B表中是主键，这一列就叫做A表的外键

⑨ 表空间（Tablespace）：存储表的文件，相当于MySQL一个数据库

⑩ Oracle版本：Oracle 9i–Internet、Oracle 10g–grid、Oracle 11g、Oracle 12c–cloud

3、命令类型

二、SQL 查询

1、了解表及表的结构

• 下面的案例需要先了解表的结构
• emp表、dept表
  
• student表、score表、course表

SQL> desc student;
Name Type Nullable Default Comments
SID CHAR(6)
SNAME VARCHAR2(10) Y
SSEX CHAR(1) Y
SAGE NUMBER(38) Y
SPHONE VARCHAR2(12) Y

SQL> desc score;
Name Type Nullable Default Comments
SCID NUMBER(38)
SID CHAR(6) Y
CID CHAR(6) Y
GRADE NUMBER(38) Y

SQL> desc course;
Name Type Nullable Default Comments
CID CHAR(6)
CNAME VARCHAR2(20) Y
CHOUR NUMBER(38) Y
TNAME VARCHAR2(10) Y

2、SQL语句特点

1）SQL关键字不区分大小写

2）表名和列名不区分大小写

3）SQL语句可以换行，但是关键字不能换行

4）SQL中注释使用两个中划线（–）

3、简单的查询

（💖参考上面的表结构）

• 查询用户下有哪些表

select * from user_tables;

• 查询员工的工号、姓名、职位

select empno,ename,job from emp;

• 查询所有列 *表示所有列
  查询员工信息

select * from emp;

• 列别名，重新给查询出来的列起个名字
  列名 as 别名；as可以省略；当在别名中有特殊字符如空格或者需要区分大小，需要使用双引号

  –查询员工姓名、年薪（不包含奖金）

  select ename as 姓名,sal as 年薪 from emp;
  select ename 姓名,sal 年薪 from emp;
  select ename 姓名,sal “年 薪” from emp; --有空格
  select ename,sal “sumsal” from emp; --区分大小写
  

4、基本运算符（+ - * /）学习

• 可以对某些列（NUMBER类型、DATE型）进行算术运算
  查询员工姓名、工资，以及加了500以后的工资

select ename,sal,sal+500 from emp;

• 查询员工姓名及日薪（不加奖金）

select ename,sal/24 from emp;

• 查询员工姓名及年薪（不算奖金）

select ename,sal*12 from emp;

5、时间函数

• 当前时间函数 sysdate
• dual 是一张虚表，为了保证SQL语法完整性而存在
• 工作年限 (sysdate-hiredate)/365

select sysdate from dual;

–查询员工姓名以及工作年限（截止当前时间）

select ename,(sysdate-hiredate)/365 from emp;

• emp表，插入一条数据，其中员工编号是1122，入职日期是1997年7月1号
• 第一种方式 to_date(日期,格式) 推荐使用

insert into emp(empno,hiredate) values(1123,to_date(‘1997-07-01’,‘YYYY-MM-DD’));

• 第二种方式 Oracle认为满足DD-MON-YY这种格式的字符串是时间，其他字符串不是时间
• DD-MON-YY是可以修改的 不推荐使用

insert into emp(empno,hiredate) values(1122,‘01-7月-97’);

• 日期默认格式由参数NLS_DATE_FORMAT控制

select * from nls_session_parameters;

• to_char() 将日期和数值类型转换为字符串
• 查询入职时间是1980-12-17的员工，使用to_date/to_char函数

select * from emp where hiredate = to_date(‘1980-12-17’,‘YYYY-MM-DD’);
select * from emp where to_char(hiredate,‘YYYY-MM-DD’) = ‘1980-12-17’;

6、null 空值

• null 意为空值、不存在、不知道的值
• 空值参与计算结果也是空值
• 查询员工姓名和年薪（加上奖金）

select ename,sal*12+comm from emp;

7、where 语句

• where子句，对结果进行限定
• where子句中是一个或者多个条件表达式，某一行是否被查询出来，取决于表达式结果
  是真还是假
• 条件永远为真，会查询出所有记录
• 查询姓名是SMITH的员工信息

select * from emp where ename=‘SMITH’; --字符串是区分大小写

8、比较运算符

• 大于(>) 、大于等于(>=) 、小于(<) 、小于等于(<=) 、等于(=) 、不等于(<> !=)
• 其他比较运算符：
  between A and B 在两者之间 大于等于A且小于等于B
  in(A,B) 匹配列表中每一项 等于A或等于B
• 查询工资在1500和3000之间的员工信息

select * from emp where sal between 1500 and 3000;

• 查询工资为1500和3000的员工信息

select * from emp where sal in (1500,3000);

9、like运算符 （_ %）

• % ： 可表示零或多个字符 例：张% 表示 张 张三 张亮 张三丰 张三丰子
• ： 可表示一个字符 例：张 表示 张三 张亮
• 查询姓名第二个字符是A的员工

select * from emp where ename like ‘_A%’;

• 查询姓名以S开头的员工

select * from emp where ename like ‘S%’;

10、逻辑运算符

• NOT 多个条件都要满足，OR 多个条件只要满足其中1个就会被查询出来，NOT 对条件取反；
• 查询工资大于1500或者部门号是20的员工信息

select * from emp where sal>1500 or deptno=20;

• 运算优先级 NOT>AND>OR
• 查询工资大于1500的PRESIDENT和工资大于1500的SALESMAN的员工

select * from emp where (job = ‘SALESMAN’ or job=‘PRESIDENT’) and sal>1500;

11、order ⼦句

• 使⽤ORDER BY ⼦句将记录排序 ASC: 升序,缺省 DESC: 降序
• 查询部门20的员工，按照工资升序排列

select * from emp where deptno=20 order by sal asc; --asc 省略

• 查询员工信息，按照工资降序排

select * from emp order by sal desc;

12、转义字符 （ \ ）

• 查询名字最后1个字是%的同学

select * from student where sname like ‘%%’ escape ‘’;

13、字符函数

13、数字函数

三、常用函数

1、单行函数

• 字符串函数：对字符串操作
• 数字函数：对数字进行计算，返回一个数字
• 日期函数：对日期和时间进行处理
• 转换函数：可以将一种数据类型转换为另外一种数据类型

2、多行函数（聚合函数）

• AVG ([DISTINCT|ALL]n) 求平均数
• SUM ([DISTINCT|ALL]n) 求和
• COUNT ({ *|[DISTINCT|ALL]expr}) 计数
• MAX ([DISTINCT|ALL]expr) 求最⼤值
• MIN ([DISTINCT|ALL]expr) 最⼩值

四、分组查询

1、 group by⼦句

• select后⾯的列必须要出现在group by中，但是出现在group by后⾯列不⼀定要出现select中
• 查询每个部门的平均工资

select deptno,avg(sal) from emp group by deptno;

• 查询每个年龄段的人数

select sage,count(*) from student group by sage order by sage;

2、分组过滤 having

• having 对分组进行过滤
• where 对行进行过滤
• 查询每个部门平均工资，只显示平均工资大于2000的

select deptno,avg(sal) from emp group by deptno having avg(sal) >2000;

3、多表连接查询

• 当两表关联，生成笛卡尔乘积（有很多无效的记录）
• 使用连接条件将有效的记录过滤出来
• 使用表前缀，可以提高查询的效率
• 等值连接：连接条件使用=号
• 不等连接：连接条件使用除了=之外其他比较运算符 > >=
• 查询员工工号，姓名，部门号，工作地

select emp.empno,emp.ename,emp.deptno,dept.loc from emp,dept where emp.deptno = dept.deptno;
select e.empno,e.ename,e.deptno,d.loc from emp e,dept d where e.deptno = d.deptno;

4、内 外 左 右 连接

• 内连接 (inner join)
• 外连接 (outer join)
• 左连接（left join）
• 右连接 （right join）
• 查询每门功课的选择人数，将没有人选的功课也显示出来

select c.cname,count(sc.sid) from score sc right join course c on sc.cid = c.cid group by c.cname；

5、子查询

• ⼦查询的结果返回多⾏
• 大于最大值 >ALL
• <ALL 小于最小值
• <ANY 小于最大值
• 大于最小值 >ANY
• 查询工资高于部门20所有人的员工

select * from emp where sal >(select max(sal) from emp where deptno=20)；
select * from emp where sal >ALL(select sal from emp where deptno=20)；

• 从emp表中工资小于CLERK的最高工资的且职位不是CELRK的员工

select * from emp where sal < (select max(sal) from emp where job=‘CLERK’) and
job<>‘CLERK’;
select * from emp where sal < ANY(select sal from emp where job=‘CLERK’) and
job<>‘CLERK’;

五、SQL执行顺序

• 1.from子句
• 2.where子句
• 3.group by子句
• 4.having子句
• 5.select子句
• 6.order by子句

六、数据操纵语言 DML

1、insert into ：将数据插入到数据表中

–第一种方式 表名后面跟列名
insert into dept(deptno,dname,loc) values(50,‘TEST50’,‘NANJING’);
–如果列名不写，插入空值
insert into dept(deptno,dname) values(60,‘TEST60’);
–第二种方式，表名后不写列名，表示要插入所有的值
insert into dept values(70,‘TEST70’,‘SH’)
–插入空值，需要写NULL
insert into dept values(80,‘TEST80’,null)

2、update ：更新数据表中已存在的数据

• 将SMITH的工资修改比KING多500

update emp set sal = (select sal+500 from emp where ename=‘KING’) where
ename=‘SMITH’;

3、delete ：删除数据表中的数据

• 删除员工表中员工号为1122和1123的数据

delete emp where empno in(1122,1123);

七、约束(constraint)

• 约束是表级强制执⾏的规则
• 保护表中数据的完整性和⼀致性
• 约束类型

NOT NULL ⾮空约束 C
UNIQUE Key 唯⼀约束
PRIMARY KEY 主键约束 P
FOREIGN KEY 外键约束
CHECK 检查约束 ⽤户可以⾃⼰定义检查的内容

八、数据定义语言：DDL

• Oracle数据类型
  VARCHAR2(size) 可变长字符串
  CHAR(size) 定长字符串
  NUMBER(p,s) 数字值
  DATE ⽇期值
  INTEGER 整数
  FLOAT ⼩数

1、create ：创建表或其他对象的结构

• 创建一个学生表（学号、姓名、性别、出生日期）

create table student
(
sid varchar2(20) primary key,–主键
sname varchar2(10) not null,–非空
ssex char(1) default ‘M’,–默认值，缺省值
birthday date
)

• 备份emp表 改名为emp_bak

create table emp_bak as select * from emp;

2、alter ：修改表或其他对象的结构

• 可以修改数据类型，长度，默认值
• 修改的时候不能影响原有的数据
• 修改一列 phone 13位

alter table student modify (phone varchar2(13))

3、drop ：删除表或其他对象的结构

• 删除学生表中数据及表结构

drop table student;

4、truncate ：删除表数据，保留表结构

• 删除表中全部记录

truncate table student;

5、delete 和truncate的区别

• delete可以选择删除部分记录，truncate只能全部记录
• delete是DML语句，可以回滚，truncate是DDL语句，不能回滚
• truncate删除速度比delete快
• truncate会回收空间，delete不会