数据库重点总结

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

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最核心的作用就是：完成分页查询。