Oracle数据库语句总结

简介说明

四大传统主流数据库：

**Oracle MySQL SqlServer DB2**

非关系型数据库：** Redis MongoDB**

主流数据库是关系型数据库：表与表之间是存在关联关系的

当我们说安装数据库的时候，指的是安装数据库服务

创建数据库的时候，指的是创建数据仓库

   可以给数据仓库分成若干块，每一块就叫做一个**表空间**；

    每个表空间都可以对应一个用户

初步使用数据库的时候，先创建一个用户，再给此用户指定一个表空间

如果不给用户提供创建表空间，就默认使用Oracle的默认表空间；为了安全期间，应该为每个用户都创建一个表空间

.DBF就是表空间文件的意思，表空间可以提高安全性。在表空间的用户只能访问存入当前表空间的内容

---

标准创建流程：

   ** 先创建表空间，创建用户让用户名与此表空间关联；**

** 再建表，这样做的话，新建的表就会创建在刚才的表空间中；**

** 最后再赋予登录，增删改权限即可**

创建表空间---创建用户（关联表空间）---赋予登录权限---赋予增删改权限

查询所有表空间（系统和自己的管理员操作）

一、创建表空间（管理员操作）

create tablespace 表空间名
datafile '存储的路径'
size 10M
autoextend on
next 10M

datafile 用于设置物理文件名称

size 用于设置表空间的初始大小

autoextend on 用于设置自动增长，如果存储量超过初始大小，则自动扩容

next 用于设置扩容的空间大小

怎么删除表空间（管理员操作）

    注意必须先用语句执行，再手动删除磁盘空间，否则Oracle会崩溃

drop tablespace 表空间名称

二、创建用户（管理员才可以创建用户）

--创建用户
create user 用户名
identified by 密码
default tablespace 表空间名称

--修改用户的表空间
alter user 用户名default tablespace 表空间名称

identified by 用于设置用户的密码

default tablespace 用于指定默认表空间名称

用户创建成功了，但是缺失某个权限，导致登录失败

login denied 登录拒绝；就是缺失登录权限

三、赋予登录权限

grant connect to 用户名;

当建表时，显示没有操作权限

四、赋予增删改查的权限

grant resource to 用户名

回收权限

    **注意：同级别不能“互相伤害”，就是删除回收平级用户**

--回收登录权限
revoke connect from 用户名；

--回收增删改权限
revoke resource from 用户名;

五、赋予收回管理员权限

--给管理员权限
grant dba to 用户名;

--收回管理员权限
revoke dba from 用户名;

---

其他流程操作语法

Oracle有几个默认用户

** Sys，system，scott**

** Sys，system**是管理员；Scott是普通用户

重置普通密码（管理员）

sys as sysdba        //作为系统管理员身份登录
alter user 用户名 identified by 密码
：

用户被锁定，解锁

--管理员执行：
alter user 用户名 account unlock;

删除用户（管理员）

    **删除用户时，如果有表则删除失败**，因为安全起见；先删除所有表，再删除用户

--删除用户
drop user 用户名 cascade；

---

数据定义语言DDL（DDL：Data Definition Language）

定义数据库对象语言，其语句包括动词create，drop等

Create创建

创建表空间

--创建表空间
create tablespace 表空间名
datafile '存储的路径'
size 10M
autoextend on
next 10M

创建用户

--创建用户
create user 用户名
identified by 密码
default table 表空间名称

创建表

default 向表中添加某个默认值

--创建表
create table 表名称(
字段名称 类型（长度）,
字段名称 类型（长度）default ''
);

复制一个表

--利用现有的表创建新表，复制一个表
create table 新表名称 as select * from 旧表名称;

复制一个表（不复制表的信息，只保留表的类型）

--只复制表的类型
--Where1=2的意思就是，不相等，复制表的时候就不复制表的信息，只复制表的类型
Create table 新表名 as select * from 旧表名 where 1=2;

复制一个表（剔除重复数据）

--利用现有的表创建新表，复制的表中数据不重复
create table 新表名称 as select distinct * from 旧表名称；

alter修改

新增约束

主键：创建表的时候，每个表都要有主键，主键起到一个唯一识别的作用

约束的作用：约束用于规定表中的数据规则，如果存在违反约束的数据行为，行为会被约束终止

• 主键约束：唯一性，非空性
• 唯一约束：唯一性，可以为空但只能有一个
• 检查约束：对该列数据的范围、格式的限制（年龄，性别）
• 非空约束：该列不允许包含空值

    PK主键（Primary Key）；UQ唯一（Unique ）约束
    CK检查（Check ）约束；FK外键(Foreign Key)约束

--约束语法（可以直接在追加表后面）
alter table xx
add constraint 约束名 约束类型 具体约束说明
    
例如：
alter table product add constraint PK_PNO primary key (pno);

--添加年龄约束15~40
alter table stuInfo
add constraint checkAge check(stuAge>15 and stuAge<40);

删除约束

--删除约束
alter table 表名
drop constraint 约束名

用户被锁定

alter user 用户名 account unlock

drop删除

删除表

--删除表
drop table 表名

---

数据操作语言（DML：Data Manipulation Language）

包括insert插入，update更新，delete删除

数据查询语言（DQL：Data Query Language）

也叫数据检索语句，用于从表中获取数据，确定数据怎样在应用程序给出。

关键字select是所有sql用的最多的动词；其他DQL常用关键字有where，order by，group by，having。这些关键字常与其他类型的SQL语句一起使用

Insert插入

插入数据

--向表中插入数据
insert into 表名 values('xxx',to_date('2020-02-02','yyyy-mm-dd hh24:mi:ss'));

--如果插入的是部分数据
insert into 表名（字段名称，字段名称……） values('xxx','xxx');

select选择

select语句的作用是从数据库中返回信息；主要有以下作用

• 列选择（投影操作）

能够使用select语句的列选择功能选择表中的列，这些列是我们想要用查询返回的；当我们查询时，可以选择的查询表中指定的列

• 行选择（选择操作）

能够使用select语句的行选择功能选择表中的行，这些行是我们想要用查询返回的，能够使用不同的标准限制所看见的行

• 连接（多表操作）

能够使用select语句的连接功能来集合数据，这些数据虽然被存储在不同的表中，但是我们可以通过连接查询到该数据

基本select语句

在最简单的形式中，select语句必须包含以下内容：

    一个**select**子句，指定被显示的列

    一个**from**子句，指定表，该表包含select子句中的字段列表

select语法解释

select是一个或多个字段的列表

• 选择所有的列

distinct 关键字表示禁止重复

column|expression 选择指定的字段或表达式

alias 给所选择的列不同的标题

from table 指定包含列的表

select语法基本结构

*select | {[distinct]，column|expression [alias]，....} from table；

select 确定哪些列

from 确定哪张表

select语句的执行顺序

from子句（哪张表）

where子句 （什么要求）

select子句 （怎么投影）

order by子句（怎么排序）

查询表中所有信息

--查询表中所有信息
select * from 表名

查询表结构

--查看表结构
select * from user_tab_columns where table_name='departments'

--命令行窗口下查看表结构
--desc 表名

查询指定的信息

--查询指定的信息
select * from 表名 where 主键='xx';

--查询名称King或者pat的员工的工作编号
select job_id from employees where last_name='King' or first_name='Pat'

选择指定的行列显示

--指定数据进行 行和列 显示
select xx,xx from 表名

筛选重复的行

--显示筛选重复的行
select distinct xx,xx from 表名

按照某个规则排序

--按照某个规则排序(年龄降序，学号升序)
select * from 表名 order by xxage DESC,xxno ASC;

--例如
--ascending 升序
select * from sc order by （要升序的字段名） asc;

--descending 降序
select * from sc order by 字段名 desc;

--默认显示是asc，可以为空

别名显示

--列表别名，就是自定义个名称
--'S'|| 学生编号前面有个S
select 'S'||stuNO 学号，stuName"姓 名" FROM 表名

例如，别名显示后降序排序

查询不包括某条件的字段

--not in 范围         ！=不等于具体的值
-- !=的效率比not in高，not in 会造成全盘扫描造成索引异常

--部门编号不等于10  !=
select * from emp where deptno!=10;
--not in 
select * from emp where  deptno not in('10','20');

查询某个时间等于某

--查询emp表2000-1-13入职的员工
select * from employees where hire_date=to_date('2000/1/13','YYYY-MM-DD')

delete删除

删除表中所有信息

--删除表中所有字段信息
delete from 表名

删除表中的某个信息

-- 删除表中的某个信息
delete from 表名 where 主键='xx'

--删除某个信息,多个信息可以and连接
delete from 表名 where xx=xx and xx=xx

update更新

修改表中的某个信息

--修改表中的某个信息
--一般都根据主键来修改数据
update 表名 set xx=xx,xx=xx where 主键=''

例如：
update 表名 set sname='james' where sid=2023;

---

事务控制语言（TCL：Transaction Control Language）

它的语句能确保被DML语句影响的表的所有行得以及时更新

Commit提交

事务提交

savepoint保存

设置回滚点

pollback回拨

事务回滚

---

数据控制语言DCL（DCL：Data Control Language）

它的语句通过grant和revoke获取许可，确定单个用户和用户组对数据库对象的访问

Grant授予

赋予登录权限

grant connet to 用户名

赋予增删改权限

--赋予增删改权限
grant resource to 用户名

赋予管理员权限

--赋予管理员权限
grant dba to 用户名

revoke撤销

回收管理员权限

--回收管理员权限
revoke dba from 用户名

回收登录权限

--回收登录权限
revoke connect from 用户名

回收增删改权限

--回收增删改权限
revoke resource from 用户名

---

聚合函数

注：查询的emp表在scott用户里

AVG 求平均值

--avg()平均值

---求所有人平均工资
select avg(sal) from emp;

--求20号部门的平均工资
select avg(sal) 平均工资 from emp where deptno=20;

--求20号部门的平均工资 和 部门编号
select deptno,avg(sal) 平均工资 from emp where deptno=20 group by deptno;

--按照部门编号进行分组，求每组的平均工资
select deptno,avg(sal) from emp group by deptno;

MIN求最小值

--min()最小值

--求本公司薪资最低的人的工资
select min(sal) from emp;

--求20号部门工资最低的人
select min(sal) from emp where deptno=20;

--求本公司每个部门的最低工资
select deptno,min(sal) from emp group by deptno;

--分组，就添加group by

MAX求最大值

--max()最大值

--求本公司薪资最高的人的工资
select max(sal)最高工资 from emp;

--求30号部门工资最高的人
select max(sal)部门最高工资 from emp where deptno=30;

--求本公司每个部门的高工资
select deptno,max(sal) from emp group by deptno;

SUN求和

--sun()求和

--求公司每月要发的总工资
select sum(sal)总工资 from emp;

--求每个月给30号部分发的总工资
select sum(sal) from emp where deptno=30;

--求本公司每个部门的总工资
select deptno 部门编号,sum(sal)总工资 from emp group by deptno;

count累加

--count()累加

--求公司总人数
select count(*) from emp;

--求20号部门的人数
select count(*) from emp where deptno=20;

--最好不要用count（*）；*代表查找所有字段
select deptno,count(*) from emp group by deptno;

--求每个部门的总人数
select deptno,count(empno) from emp group by deptno;

--每个部门的。。。值，只要2000以上的
select deptno 部门编号,max(sal) 最大值,AVG(sal) 平均值,min(sal) 最小值
from emp
group by deptno
having avg(sal)>2000;

group by子句

用于将信息划分为更小的组；每一组行返回针对该组的单个结果（分组，就添加group by）

--group by子句

SELECT deptno,MAX(sal) maxSal,
AVG(sal) avgSal,
MIN(sal) minSal
 FROM emp
GROUP BY deptno;

order by子句

一般是用来，依照查询结果的某一列（或多列）属性，进行排序（升序：ASC；降序：DESC；默认为升序；order by 子句在select语句的最后）

在排序中也可以使用没有包括在select子句定义的列排序，就是没有做投影

当排列为空值时，升序排序显示在最后，降序排序显示在最前面

选择多个列属性进行排序，然后排序的顺序是，从左到右，依次排序。

如果前面列属性有些是一样的话，再按后面的列属性排序。（前提一定要满足前面的属性排序，因为在前面的优先级高）

--按照某个规则排序(年龄降序，学号升序)
select * from xxx order by xxage DECS,xxno ASC;

--男同学的年龄升序输出asc,降序desc
select sno 男同学编号,sage 男同学年龄 from student where ssex='男' order by sage desc

用列号排序

可以使用投影的列的序号指定排序列，但是不推荐

--对员工名称，薪资进行排序
--不建议使用列号排序，如果变动就失效了
select last_name,job_id,salary from employees order by 1 asc,3 desc

同列别名排序

可以使用列的列表名指定排序列

--使用列别名进行排序
select last_name as name,job_id,salary from employees order by name asc
select last_name as name,job_id as id,salary from employees order by id desc

多列排序

多列排序，可以用多列排序查询结果。在order by 子句中，多个指定的列名之间用逗号分开。如果想要对某个列倒序排序需则在该列名后面指定desc

--多列排序，注意逗号隔开
select last_name as name,job_id as id,salary from employees order by name asc,id asc

having子句

用于指定group by 子句检索行的条件

having和where的区别

只有满足HAVING短语指定条件的组才输出。

HAVING短语与WHERE子句的区别：作用对象不同。

    1》WHERE子句作用于**基表或视图**，从中选择满足条件的**元组**。

    2》HAVING短语作用于**组**，从中选择满足条件的**组**。

理解：要用having的话，前面的语句必须要有having的字段，否则会报错；而where不用

因为having是从前筛选的字段再筛选，而where是从数据表中的字段直接进行的筛选的。

--having子句
--条件可以用and追加

SELECT deptno,
MAX(sal) maxSal,
AVG(sal) avgSal,
MIN(sal) minSal
FROM emp
GROUP BY deptno
HAVING AVG(sal)>2000;

where子句

*select | {[distinct]，column|expression [alias]，....} from table [where condition(s)]；

where 子句跟着from子句

where 限制查询满足条件的行

condition 由列名、表达式、常数、比较操作组成

** 选择限制的行**

    可以用where子句限制从查询返回的行。一个where子句包含一个必须满足的条件，where子句紧跟着from子句。如果条件是true，返回满足条件的行。

    where子句能够比较列值、文字值、算术表达式或者函数，**where子句由三个元素组成：**

• 列名
• 比较条件
• 列名、常量或者值列表

Not in和！= 的区别

语句执行顺序

执行顺序: where -> group by -> select ... from ... -> order by..

SQL函数

    SQL函数分为**单行函数**、**聚合函数**、**分析函数**

单行函数

    单行函数对于从表中查询的每一行只返回一个值；可以出现在select子句中和where子句中；

    单行函数大致可以划分为：

• 日期函数
• 数字函数
• 字符函数
• 转换函数
• 其他函数

转换函数

    转换函数将值从一种数据类型转换成另一种数据类型

常用的转换函数有：

• TO_CHAR
• TO_DATE
• TO_NUMBER

--如下

SELECT TO_CHAR(sysdate,'YYYY"年"MM"月"DD"日" HH24:MI:SS')
FROM dual;

SELECT TO_CHAR (123456.03,'099,999.99') FROM dual;　

SELECT TO_DATE('2005-12-06' , 'yyyy-mm-dd')
FROM dual;

SELECT TO_NUMBER('100') FROM dual;

select语句中的算术表达式

用算术表达式创建数字和日期数据的表达式（+ - * /）

注意：如果对日期进行计算，我们只能对date和timestamp数据类型使用加和减操作

运算符的优先级（ / + -）*

• 乘法和除法比加法和减法的优先级高
• 相同优先级的运算符从左到右计算
• 圆括号用于强转优先计算，并且使语句更加清晰

--算数表达式练习

--计算employees表的员工年薪+100以后是多少
select employee_id,first_name,salary*12+100 from employees;

--然后进行对薪水进行降序排序
select employee_id 员工id,first_name 员工名称,salary*12+100 年薪 from employees order by salary desc;

--计算employees表的员工薪水+100以后的年薪是多少，升序排序
select employee_id,first_name,(salary+100)*12 from employees order by salary asc;

连字运算符

连字运算符：

• 连接列或者字符串到其他的列
• 用两个竖线表示（||）
• 构造一个字符表达式的合成列

我们能够用连字运算符进行列与列之间、列与算术表达式之间或者列与常数值之间的连接，来创建一个字符表达式，连字运算符两边的列被合并成一个单个的输出列

--连字运算符 ||
--连接e表中的员工名称

select first_name||last_name 员工名称 from employees;

文字字符串

• 文字字符串是包含在select列表中的一个字符串，一个数字或者一个日期
• 日期和字符的文字字符串值必须用单引号括起来
• 每个文字字符串在每行输出一次

文字字符串不是列名或者别名，对每个返回行打印一次。任意格式的文本文字字符串能够被包含在查询结果中，并且作为select列表中的列处理；日期和字符文字必须放在单引号中，数字不需要

--文字字符串，文字需要加'' 数值不需要

select first_name||' is a '||job_id as 员工职务 from employees;

select first_name||0744||job_id as 员工职务 from employees;

distinct去除重复行

在select语句中用distinct关键字除去相同的行。

为了在结果中除去相同的行，在select子句中的select关键字后面紧跟distinct关键字

--去除e表中的department_id重复数据
select distinct department_id from employees order by department_id desc;

--注意 distinct 剔除的数据后面还有数据的话，这两个结果将构成一个条件来执行distinct关键字
select distinct department_id,first_name from employees order by department_id desc;

注意：两个结果将构成一个条件来执行distinct关键字，所以还会重复

字符串和日期

• 字符串和日期的值放在单引号中
• 字符串区分大小写，日期值是格式敏感的
• 日期的默认格式是 YYYY-MM-DD

用的很少；前端拿到的数据是string类型，保存在数据库的话要转换成 日期类型；

从数据库拿数据展示到页面，就需要把日期类型的数据转换成字符串；

比较条件

条件运算符

**运算 **含义=等于>大于>=大于等于<小于
<=
小于等于<>不等于（也可以使用！= 和 ^= 来表示）

--查询薪水小于等于3000的员工
select first_name 员工名称,salary 薪水 
from employees where salary<=3000 order by salary desc

其他比较条件

操作含义between...and...在两个值之间（包含）is（set）匹配一个任意值列表like匹配一个字符模板is null是一个空值

使用between条件

between条件：可以用between范围条件显示基于一个值的范围的行。指定的范围包含一个上限和一个下限。between...and...实际上是由Oracle服务器转变为and条件：（a>=上限）and（a<=下限），所以使用between...and...并没有性能的提高，只是逻辑上简单

--between...and..下限,上限
--查询薪水2500~3000的员工
select first_name 员工名称,salary 薪水 
from employees where salary between 2500 and 3500 order by salary desc

--between只是对条件进行简化处理，实际上还是会被解析成and....
select first_name 员工名称,salary 薪水 
from employees where salary>=2500 and salary<=3500 order by salary asc

使用in条件

in条件：用in条件在指定的一组值中进行选择；in（...）实际上是由Oracle服务器转变为一组or条件：a=valuel OR a=value2 OR a=value3，所以使用in（...）并没有得到性能的提高，只是逻辑上简单

注意：in只能做相等的判断；多条件的值最终会被转换成or

--int(...)多个选择
--查询所有经理号为100,101,102的雇员的信息
select manager_id,last_name,salary from employees 
where manager_id in (100,101,102,103)

--or用法
select manager_id,last_name,salary from employees 
where manager_id=101 or manager_id=102 or manager_id=103

使用like条件

使用like条件执行有效搜索串值的通配符搜索

搜索条件既可以包含文字也可以包含数字：

• %表示零个或多个字符

• _表示一个占位符（字符）

    like条件：我们也许不能总知道要搜索的确切的值，但能够选择那些用like条件匹配一个字符模板的行。字符模板匹配运算涉及通配符查询。有两个符号**%和 _** 可以用来构造搜索串
  

--like 查询名称带a的
--% 表示零个多个字符，-表示一个字符
select first_name from employees where first_name like '%a%';

--名称首字母是a的,注意对大小写敏感
select first_name from employees where first_name like 'C%';

--名称第二个字母是d
select first_name from employees where first_name like '_d%'

escape标识符：可以用escape标识符搜索实际的 % 和 _ 符号。使用escape选项，该选项指定换码符是什么。如果你想要搜索包含‘sa_’的字符串可以使用escape对\表示该符号为转义符号

like'%sa_%'escape''; 意思就是把 '' 定义成转移符号

--escape标识符,转义符号,任意符号都可以设置
--查询employee表的工作id包含sa_的员工姓名

--'_'符号是占位符号，不是文本的；所有就要设置新的转义字符
select first_name,job_id from employees where job_id like'SA\_%' escape'\'

使用null条件

null条件：null条件中包括** is null** 条件和** is not null 条件。is null 条件用于空值测试。空值的意思是难以获得的、未指定的、未知的或者不适用的。因此，不能用=判断**，因为null不能等于或不等于任何值。is not null 测试不是空值

--查询有佣金的员工
select last_name,job_id,commission_pct from employees 
where commission_pct is not null

--查询没有佣金的员工
select last_name,job_id,commission_pct from employees 
where commission_pct is null

逻辑条件关系

运算含义and如果两个组成部分的条件都为真，则返回trueor如果两个组成部分中的任何一个条件为真，则返回truenot如果跟随的条件为假，则返回true
逻辑条件：逻辑条件组合两个比较条件的结果来产生一个基于这些条件的单个的结果，或者逆转一个单一条件的结果。当所有条件的结果为真时，返回行；

SQL的三个逻辑运算符是：and、or、not

and

and：and要求两个条件同时为真

--查询工作岗位包含‘MAN’ 并且收入大于等于1000,降序输出
select employee_id,job_id,last_name,salary from employees 
where job_id like'%MAN%' and salary>=10000 order by salary desc

or

or：or操作要求两者之一为真即可

--查询工作岗位包含‘MAN’ 或者收入大于等于1000,升序输出
select employee_id,job_id,last_name,salary from employees
where job_id like'%MAN%' or salary>=10000 order by salary asc

not

not：取反，not运算符也可以用于另一个SQL运算符。例如，between、like、null

--查询工作岗不是it_prog,st_clerk,sa_rep的雇员
select last_name,job_id from employees
where job_id!='IT_PROG' and job_id!='SA_REP' and job_id!='IT_PROG'

select last_name,job_id from employees
where job_id not in('IT_PROG','SA_REP','IT_PROG')

运算优先规则

求值顺序（数字越小优先级越高）说明1算术运算2连字运算3比较运算4is（not）null，like，（not）in5（not）between6not逻辑条件7and逻辑条件8or逻辑条件
优先规则：优先规则定义表达式求值和计算的顺序，表中列出了默认的优先顺序。可以用圆括号括住想要先计算的表达式来覆盖默认的优先顺序

--查询工作岗位是sa_rep,ad_pres并且它们的薪水大于15000的员工
select last_name,job_id,salary from employees 
where job_id in('SA_REP','AD_PRES') and salary>15000

--查询工作岗位是sa_rep，或者 工作岗位是ad_pres并且薪水大于15000的员工
--建议加上括号（） 可读性更好更严谨，预防数据查的到底对不对
select last_name,job_id,salary from employees 
where job_id='SA_REP' or (job_id='AD_PRES' and salary>15000)

SQL语句语法要求

• SQL语句对大小写不敏感
• SQL语句可以写成一行或者多行
• 关键字不能简写或者分开拆行
• 子句通常放在不同的行
• 缩进用于增强可读性

--遇到百分号怎么办
--显示所有佣金为20%的雇员，需要把百分号转换成小数
--三种写法
select last_name,commission_pct from employees
where commission_pct=.2
where commission_pct=0.2
where commission_pct=0.20

SQL空值说明

空值是一个未分配、未知的、或不适用的值

空值不是0，也不是空格

空值

如果一行中的某个列缺少数据值，则该值被置为空值；空值和0或空格不相同，0是一个数字，空格是一个字符。任何数据类型的列都可以包含空值。可是某些约束，如not null，primary key 可以防止在列中使用空值

算数表达式中的空值

如果算数表达式中，有个字段的类型为空值（如commission_pct；某些员工的佣金为空值），则计算结果一定为空

--计算e表中的员工年薪+年佣金

select employee_id,first_name,salary*12*commission_pct from employees order by salary desc,employee_id asc;

从此表可以看出，有些值为空值；因为佣金是空值，进行运算后也变为空值