数据库常用指令总结

目录

查看当前用户，有哪些表：

导入sql文件命令： @table.sql

查看表结构：

 数据库对象

sql语句的分类：

别名

拼接

nvl

distinct

format

排序

条件查询

between and 操作符，表示在俩个值之间

in() ，表示值在一个指定的列表中

like ，模糊查询，在值不精确的时候使用

and 、 or ，逻辑操作符，当条件有多个的时候可以使用

1. 单行函数

2. 转换函数

3. 聚合函数

 数字函数

日期函数​编辑

 转换函数

to_char​编辑

 to_number​编辑

to_date ​编辑

 函数嵌套

多表查询

 不等值连接外连接​编辑

 左外连接​编辑

 右外连接​编辑

 全连接​编辑

 自连接​编辑

 操作结果集

union​编辑

 union all

 rownum​编辑

聚合函数

 执行顺序

 select语句执行顺序：

 group by

having

 子查询

分页查询

外键范式

 建表

 命名

 约束

 表级约束的建表示例：

 表级约束和列级约束对比：

 特殊

DML语句

 insert

 update

 delete

 delete on

 数据库事务

 隔离

 alter

 注释

 序列

 删除

 视图

 索引

---

查看当前用户，有哪些表：

show user;
select table_name from user_tables;

导入sql文件命令： @table.sql

查看表结构：
 

desc s_region

 数据库对象

数据库对象是数据库的组成部分，常常用 CREATE 命令进行创建，可以使用 ALTER 命令修改，用 DROP执行删除操作。常见的数据库对象有：
用户（user）
表（table）
视图（view）
索引（index）
触发器（trigger）
存储过程（procedure）
同义词（synonym）
序列（sequence）

sql语句的分类：

DQL (Data Query Language)，数据查询语言
用于检索数据库中的数据，主要是 SELECT 语句

DML (Data Manipulation Language)，数据操纵语言
用于改变数据库中的数据，主要是 INSERT , UPDATE , DELETE 语句

DDL（Data Define Langage)，数据定义语言
用来建立、修改、删除数据库对象，主要是 CREATE 、 ALTER 、 DROP 、 TRUNCATE 语句

TCL (Transaction Control Language)，事务控制语言

用于维护数据的一致性，主要是 COMMIT , ROLLBACK , SAVEPOINT 语句

DCL（Data Control Language），数据控制功能
用于执行权限授予和权限收回操作，主要是 GRANT , REVOKE 语句
注意，DML语句需要事务的支持（产生事务），DDL语句会自动提交事务

查看每个员工的员工id，名字和年薪

select id,last_name,salary*12
from s_emp;

别名

select old_column [as] new_column_name from tb_name;

注意，中括号里面的as是可选的

拼接

查看员工的员工id，全名

select id,first_name||last_name
from s_emp;

nvl

使用nvl函数可以将null进行替换
查看所有员工的员工id，名字和提成，如果提成为空，显示成0

select id,last_name,nvl(commission_pct,0) commission_pct
from s_emp;

distinct

该关键字可以将重复数据去除。
语法：

select distinct col_name,col_name...
from tb_name;

format

使用 format 可以将查询结果显示的宽度进行调整。

使用前

select id,last_name
from s_emp;

使用后
//表示last_name列的下方有15个“-”
column last_name format a15
//或者简写为
col last_name for a15;
select id,last_name
from s_emp;

ID LAST_NAME
---------- ---------------
1 Velasquez
2 Ngao
3 Nagayama
4 Quick-To-See
5 Ropeburn
6 Urguhart
7 Menchu
......

可以清除设置的格式：

clear column
//或者
clear col

注意，format只能设置字符类型的字段列

排序

查询数据的时候进行排序，就是根据某个字段的值，按照升序或者降序的情况将记录显示出来

select col_name,...
from tb_name
order by col_name [asc|desc]

注意1，order by语句，只对查询记录显示调整，并不改变查询结果，所以执行权最低，最后执行
注意2，排序的默认值是asc：表示升序，desc：表示降序
注意3，如果有多个列排序，后面的列排序的前提是前面的列排好序以后有重复（相同）的值。

例如，

select id,id2
from test
order by id asc,id2 desc;

条件查询

select col_name,...
from tb_name
where col_name 比较操作表达式

 例如，查看员工工资小于1000的员工id和名字

select id,last_name,salary
from s_emp
where salary < 1000;

between and 操作符，表示在俩个值之间

例如，查看员工工资在700 到 1500之间的员工id和名字

select id,last_name,salary
from s_emp
where salary between 700 and 1500;

in() ，表示值在一个指定的列表中

例如，查看员工号1,3,5,7,9员工的工资

select id,last_name,salary
from s_emp
where id in (1,3,5,7,9);

like ，模糊查询，在值不精确的时候使用

% ，通配0到多个字符
_ ，通配一个字符，并且是一定要有一个字符
\ ，转义字符，需要使用 escape 关键字指定，转义字符只能转义后面的一个字符
例如，查看员工名字以C字母开头的员工的id，工资

select id,last_name,salary
from s_emp
where last_name like 'C%';

注意，数据库中的字符串，需要使用单引号括起来
例如，查看员工名字长度不小于5，且第四个字母为n字母的员工id和工资

select id,last_name,salary
from s_emp
where last_name like '%\_%' escape '\';

例如，查看员工名字长度不小于5，且第四个字母为n字母的员工id和工资

select id,last_name,salary
from s_emp
where last_name like '___n_%';

is null ，判断值为null的时候使用，null值的判断不能使用等号
例如，查看员工提成为为空的员工的id和名字

select id,last_name,commission_pct
from s_emp
where commission_pct is null;

注意，类似的，还有 is not null

and 、 or ，逻辑操作符，当条件有多个的时候可以使用

注意，and比or的优先级要高
例如，查看员工部门id为41且职位名称为Stock Clerk（存库管理员）的员工id和名字

select id,last_name,dept_id,title
from s_emp
where
dept_id = 41
and
title = 'Stock Clerk';

例如，查看员工部门为41 或者 44号部门，且工资大于1000的员工id和名字

select id,last_name,dept_id,title
from s_emp
where
salary > 1000
and
( d
ept_id = 41
or
dept_id = 44
);

例如，查看员工部门为41且工资大于1000，或者44号部门的员工id和名字

select id,last_name,dept_id,title
from s_emp
where
salary > 1000
and
dept_id = 41
or
dept_id = 44;

注意，以上俩个sql语句，执行的结果是不一样的，它们的区别就是第一个sql在条件中多了个小括号

oracle数据库中，内置了很多常用的函数，整体分为：

1. 单行函数

        字符函数
        日期函数
        数字函数

2. 转换函数

3. 聚合函数

单行函数，也可以称为单值函数，每操作一行数据（某个字段值），都会返回一个结果

例如，使用单行函数，将上面的结果中每一个last_name转换为大写

select id,upper(last_name) as last_name,salary
from s_emp
where id<5;
//运行结果：
ID LAST_NAME SALARY
---------- --------------- ----------
1 VELASQUEZ 2500
2 NGAO 1450
3 NAGAYAMA 1400
4 QUICK-TO-SEE 1450

聚合函数，也可以称为多行函数、分组函数、组函数，它可以操作多行数据，并返回一个结果，一般会结合着group分组来使用，当然也可以单独使用，那么默认全部数据就是一个小组。

例如，查询id小于5的所有员工的平均工资

select avg(salary)
from s_emp
where id<5;
//运行结果：
AVG(SALARY)
-----------
1700

例如，查询每个部分的员工人数、以及该部门的平均工资，并且按照平均工资的降序排序
 

select dept_id,count(*),avg(salary) as avgSalary
from s_emp
group by dept_id
order by avgSalary desc;
//运行结果：
DEPT_ID COUNT(*) AVGSALARY
---------- ---------- ----------
50 2 2025
33 1 1515
32 1 1490
10 1 1450
35 1 1450
31 2 1400
41 4 1247.5
34 2 1160
45 3 1089
42 3 1081.66667
44 2 1050
DEPT_ID COUNT(1) AVGSALARY
---------- ---------- ----------
43 3 900

转换函数，可以将一个类型的数据转换为另一种类型的数据
例如，把一个字符转换为数字
 

select to_number('1000')
from dual;

字符函数
常用的字符函数有：

 

 

 数字函数

专门操作数字的函数，常用的数字函数有：

 

日期函数

 转换函数

概述
转换函数，可以将一个类型的数据，转换为另一种类型的数据。
转换函数主要有三种：
1. TO_CHAR ，把一个数字或日期数据转换为字符
2. TO_NUMBER ，把字符转换为数字
3. TO_DATE ，把字符转换为日期

to_char

 

 日期转为字符的常用格式：

 to_number

to_date
 

 函数嵌套

以上介绍的常用函数，在数据类型正确的情况下，是可以嵌套使用的。
例如，先把'hello'和'world'连接起来，再转换为全部字母大写，然后再从第4个字符开始，连着截取4个字符

select substr(upper(concat('hello','world')),4,4) as result
from dual;

注意，函数f1的返回类型，必须是函数f2的参数类型，那么它们之间才可以嵌套

多表查询

多表查询，又称表联合查询，即一条sql语句涉及到的表有多张，表中的数据通过特定的连接，进行联合显示
连接查询又可以大致分为：
1. 等值连接
2. 不等值连接
3. 外连接
左外连接
右外连接
全连接
4. 自连接

3.2 等值连接

 不等值连接
外连接

 

 左外连接

 右外连接

 全连接

 自连接

 操作结果集

4.1 概述
每一条sql语句，查询出的一个结果，都可以被称为结果集。
如果有俩条sql语句，它们分别查询出的结果集，都包含完全一致的字段名称和类型，那么我们可以使用
下面的关键字对俩个结果集进行操作：
union ，取俩个结果集的并集
union all ，把俩个结果集合在一起显示出来
minus ，第一个结果集除去第二个结果集和它相同的部分
intersect ，求俩个结果集的交集
前提条件是，俩个结果集中查询的列要完全一致（名称和类型）

union

 union all

例如，把俩个结果集合在一起显示出来

 

 rownum

 

 将来在实际的使用中，Oracle数据库中伪列rownum最核心的作用就是：完成分页查询。

聚合函数

1.1 概述
聚合函数，一般会操作多组数据，并对每一组中的某个列，执行计算并返回单一的值。
聚合函数经常与 SELECT 语句的 GROUP BY 子句一同使用，所以也把其它称之为分组函数。

 

 

 执行顺序

一个完整的select语句，是有自己的执行顺序的

 select语句执行顺序：

1. from子句，组装来自表的数据，有可能是多张表
2. where子句，基于指定的条件对记录行进行筛选
3. group by子句，将数据划分为多个分组
4. 使用聚合函数对没个小组中的数据进行计算
5. having子句，进行条件筛选，这里可以使用聚合函数的计算结果
6. 计算所有的运算表达式，主要是select部分
7. order by子句，对结果集进行排序

 

 group by

having

例如，查询s_emp表中部门的平均工资大于等于1400的部门

 子查询

2.1 概述
子查询，也称嵌套查询，即一个select语句中嵌套了另外的一个或者多个select语句
子查询的思路就是，把第一个sql语句的查询结果，在第二个sql语句中使用，这时候第一条sql语句的结
果，在第二条sql中就可以充当一个where条件中的一个值，或者充当一张虚拟的表。、

分页查询

外键
范式

设计关系数据库时，要遵从规范要求，才能设计出更加合理的表结构
这些不同的规范要求被称为不同的范式，各种范式依次递进，越高的范式数据库冗余越小。
目前关系数据库有六种范式：
第一范式(1NF)
第二范式(2NF)
第三范式(3NF)
巴斯-科德范式(BCNF)
第四范式(4NF)
第五范式(5NF，又称完美范式)
注意，满足最低要求的范式是第一范式(1NF)。在第一范式的基础上，进一步满足更多规范要求的
称为第二范式(2NF)，其余范式以次类推。
一般说来，数据库只需满足第三范式(3NF)就行了

 

  

 

 建表

 

 

 命名

对应表和列的名字：
1. 必须是字母开头
2. 必须是1-30个字符之间的长度
3. 表名中只能出现字母、数字、_、#
4. 不能和数据库中己有对象的名字重复
5. 不能是数据库中的关键字

 约束

 

 

 表级约束的建表示例：

 

 

 

 

 表级约束和列级约束对比：

1. 表级约束和列级约束所写的位置不一样
2. not null约束不能用表级约束来声明
3. 表级约束和列级约束声明语法稍有所不同
4. 如果要声明的约束为联合主键、联合外键、联合唯一的时候，就一定要用表级约束
 

在创建约束的时候（行级、表级都可以），还可以给约束起一个名字，这时候就要使用关键字constraint
constraint 关键字：
1. constraint是约束的意思
2. 建表的时候可以给约束起一个名字，这个名字起的规律一般会是：表名_列名_约束类型
3. 如果没有给约束起名字，那么系统也会给这个约束起一个默认的名字
4. 将来我们可以根据之前给约束起好的名字，而找到这个约束，然后进行修改获取其他操作

 

 特殊

DML语句

 

 insert

 update

 delete

 delete on

 

 

 数据库事务

 

 

 隔离

 

 alter

1.1 概述
在表创建好的情况下，可以使用 alter 关键字，来修改表的信息。
主要包括：

 注意， alter 属于DDL语句，会自动提交当前事务

 

 

 

 

 注释

 序列

 

 

 

 删除

删除序列语法：

 视图

 

 

 

 

 

 

 

 

 索引