DM_SQL

最新推荐文章于 2024-07-24 10:17:31 发布

凡哈哈

最新推荐文章于 2024-07-24 10:17:31 发布

阅读量988

点赞数 28

文章标签： sql oracle 数据库

本文链接：https://blog.csdn.net/l65656565/article/details/136719955

版权

DM_SQL 语言符合结构化查询语言 SQL 标准，是标准 SQL 的扩充。它集数据定义、数据查询、数据操纵和数据控制于一体，是一种统一的、综合的关系数据库语言。它功能强大，使用简单方便、容易为用户掌握。DM_SQL 语言具有如下特点：

1.功能一体化

DM_SQL 的功能一体化表现在以下两个方面：

DM_SQL 支持多媒体数据类型，用户在建表时可直接使用。DM 系统在处理常规数据与多媒体数据时达到了四个一体化：一体化定义、一体化存储、一体化检索、一体化处理，最大限度地提高了数据库管理系统处理多媒体的能力和速度；
DM_SQL 语言集数据库的定义、查询、更新、控制、维护、恢复、安全等一系列操作于一体，每一项操作都只需一种操作符表示，格式规范，风格一致，简单方便，很容易为用户所掌握。

2.两种用户接口使用统一语法结构的语言

DM_SQL 语言既是自含式语言，又是嵌入式语言。作为自含式语言，它能独立运行于联机交互方式。作为嵌入式语言，DM_SQL 语句能够嵌入到 C 和 C++ 语言程序中，将高级语言(也称主语言)灵活的表达能力、强大的计算功能与 DM_SQL 语言的数据处理功能相结合，完成各种复杂的事务处理。而在这两种不同的使用方式中，DM_SQL 语言的语法结构是一致的，从而为用户使用提供了极大的方便性和灵活性。

3.高度非过程化

DM_SQL 语言是一种非过程化语言。用户只需指出“做什么”，而不需指出“怎么做”，对数据存取路径的选择以及 DM_SQL 语句功能的实现均由系统自动完成，与用户编制的应用程序与具体的机器及关系 DBMS 的实现细节无关，从而方便了用户，提高了应用程序的开发效率，也增强了数据独立性和应用系统的可移植性。

4.面向集合的操作方式

DM_SQL 语言采用了集合操作方式。不仅查询结果可以是元组的集合，而且一次插入、删除、修改操作的对象也可以是元组的集合，相对于面向记录的数据库语言 (一次只能操作一条记录) 来说，DM_SQL 语言的使用简化了用户的处理，提高了应用程序的运行效率。

5.语言简洁，方便易学

DM_SQL 语言功能强大，格式规范，表达简洁，接近英语的语法结构，容易为用户所掌握。

DM_SQL 所支持的数据类型

字符数据类型

CHAR/CHARACTER

语法：CHAR[(长度)]

CHAR 数据类型指定定长字符串。定义 CHAR 类型的列时，可以指定一个不超过 32767 的正整数作为字节长度

例如：

CHAR(100)

如果未指定长度，缺省为 1

CHAR 类型列的最大存储长度由数据库页面大小决定，CHAR 数据类型最大存储长度和页面大小的对应关系请见下表。

但是，在表达式计算中，该类型的长度上限不受页面大小限制，为 32767。

数据库页面大小	实际最大长度
4 K	1900
8 K	3900
16 K	8000
32 K	8188

这个限制长度只针对基表中的列，在定义变量的时候，可以不受这个限制长度的限制。

另外，实际插入表中的列长度要受到记录长度的约束，每条记录总长度不能大于页面大小的一半。

VARCHAR/VARCHAR2

语法：VARCHAR[(长度)]

VARCHAR 数据类型指定变长字符串，用法类似 CHAR 数据类型，可以指定一个不超过 32767 的正整数作为字节或字符长度

例如：

VARCHAR(100)指定 100 字节长度；

VARCHAR(100 CHAR)指定 100 字符长度。

如果未指定长度，缺省为 8188 字节。

VARCHAR 类型在表达式计算中的长度上限不受页面大小限制，为 32767.

CHAR 同 VARCHAR 的区别在于前者长度不足时，系统自动填充空格，而后者只占用实际的字节空间。

另外，实际插入表中的列长度要受到记录长度的约束，每条记录总长度不能大于页面大小的一半。

数值数据类型

NUMERIC/DECIMAL/DEC/NUMBER 类型

语法：NUMERIC[(精度 [, 标度])]

NUMERIC 数据类型用于存储零、正负定点数。其中：精度是一个无符号整数，定义了总的数字数，精度范围是 1 至 38。标度定义了小数点右边的数字位数。

一个数的标度不应大于其精度，如果实际标度大于指定标度，那么超出标度的位数将会四舍五入省去。

例如：NUMERIC(4,1)定义了小数点前面 3 位和小数点后面 1 位，共 4 位的数字，范围在-999.9 到 999.9。

所有 NUMERIC 数据类型，如果其值超过精度，DM 会返回一个出错信息，如果超过标度，则多余的位会被截断。

如果不指定精度和标度，缺省精度为 38，标度无限定。

INTEGER/INT 类型

语法：INTEGER

用于存储有符号整数，精度为 10，标度为 0。取值范围为：-2147483648 (-2^31)～ +2147483647(2^31-1)。

BIGINT 类型

语法：BIGINT

用于存储有符号整数，精度为 19，标度为 0。取值范围为：-9223372036854775808(-2^63)～9223372036854775807(2^63-1)。

SMALLINT 类型

语法：SMALLINT

用于存储有符号整数，精度为 5，标度为 0。取值范围为：-32768(-2^15) ～ +32767(2^15-1)。

TINYINT/BYTE 类型

语法：TINYINT

用于存储有符号整数，精度为 3，标度为 0。取值范围为：-128 ～ +127。

FLOAT 类型

语法：FLOAT[(精度)]

FLOAT 是带二进制精度的浮点数，精度范围（1~126）。

当精度小于等于 24 时，DM 将其转换为标准 C 语言中的 REAL 类型；当精度大于 24 时，转换为标准 C 语言中的 DOUBLE 类型。

FLOAT 取值范围-1.710^308 ～ 1.710^308。

DOUBLE 类型

语法：DOUBLE[(精度)]

DOUBLE 是带二进制精度的浮点数。DOUBLE 类型的设置是为了移植的兼容性。精度与取值范围与 FLOAT 一样。

DOUBLE PRECISION 类型

语法：DOUBLE PRECISION[(精度)]

该类型指明双精度浮点数。DOUBLE PRECISION 类型的设置是为了移植的兼容性。精度与取值范围与 FLOAT 一样。

位串数据类型

BIT 类型

语法：BIT

BIT 类型用于存储整数数据 1、0 或 NULL，只有 0 才转换为假，其他非空、非 0 值都会自动转换为真，可以用来支持 ODBC 和 JDBC 的布尔数据类型。

DM 的 BIT 类型与 SQL SERVER2000 的 BIT 数据类型相似。

日期时间数据类型

DATE 类型

语法：DATE

DATE 类型包括年、月、日信息，定义了'-4712-01-01'和'9999-12-31'之间任何一个有效的格里高利日期。

CREATE TABLE T2(C1 DATE,C2 DATE,C3 DATE);

INSERT INTO T2 VALUES(DATE '1999-10-01','1999/10/01','1999.10.01');

TIME 类型

语法：TIME[(小数秒精度)]

TIME 类型包括时、分、秒信息，定义了一个在'00:00:00.000000'和'23:59:59.999999'之间的有效时间。

TIME 值的书写方式有两种：一是 TIME'时:分:秒'；二是'时:分:秒'。

CREATE TABLE T2(C1 TIME(2),C2 TIME,C3 TIME);

INSERT INTO T2 VALUES(TIME '09:10:21.20','09:10:21','9:10:21.49');

TIMESTAMP/DATETIME 类型

语法：TIMESTAMP[(小数秒精度)]

TIMESTAMP 类型包括年、月、日、时、分、秒信息，定义了一个在'-4712-01-01 00:00:00.000000000'和'9999-12-31 23:59:59.999999999'之间的日期时间。

TIMESTAMP 值的书写方式有两种：一是 TIMESTAMP'<DATE 值 > <TIME 值 >'；二是'<DATE 值 > <TIME 值 >'。语法中，TIMESTAMP 也可以写为 DATETIME。

CREATE TABLE T2(C1 TIMESTAMP,C2 DATETIME,C3 TIMESTAMP,C4 DATETIME,C5 TIMESTAMP);

INSERT INTO T2 VALUES(TIMESTAMP '2002-12-12 09:10:21',TIMESTAMP '2002-12-12 09:10:21','2002/12/12 09:10:21','2002.12.12 09:10:21',DATETIME'2002-12-12 09:10:21' );

多媒体数据类型

多媒体数据类型的字值有两种格式：一是字符串，例如：’ABCD’，二是 BINARY，例如：0x61626364。

TEXT、LONG、LONGVARCHAR、CLOB 只支持字符串。

BFILE 不适用上面两种格式。BFILE 指明的文件只能只读访问。

BINARY 格式的多媒体数据类型以及 BFILE 数据类型不支持比较。

不支持为多媒体数据类型的字段指定精度。

BINARY 类型

语法：BINARY[(长度)]

BINARY 数据类型用来存储定长二进制数据。在基表中，定义 BINARY 类型的列时，其最大存储长度由数据库页面大小决定，可以指定一个不超过其最大存储长度的正整数作为列长度，缺省长度为 1 个字节。

BINARY 类型在表达式计算中的长度上限为 32767。

BINARY 常量以 0x 开始，后面跟着数据的十六进制表示，例如：0x2A3B4058。

VARBINARY/RAW 类型

语法：VARBINARY[(长度)]

VARBINARY 数据类型用来存储变长二进制数据，用法类似 BINARY 数据类型，可以指定一个不超过 32767 的正整数作为数据长度。缺省长度为 8188 个字节。VARBINARY 数据类型的实际最大存储长度由数据库页面大小决定，具体最大长度算法与 VARCHAR 类型的相同，其在表达式计算中的长度上限也与 VARCHAR 类型相同，为 32767。

TEXT/LONG/LONGVARCHAR/CLOB 类型

语法：TEXT

TEXT 为变长字符串类型。其字符串的长度最大为 100G-1 字节。DM 利用它存储长的文本串。

IMAGE/LONGVARBINARY 类型

语法：IMAGE

IMAGE 用于指明多媒体信息中的图像类型。图像由不定长的象素点阵组成，长度最大为 100G-1 字节。该类型除了存储图像数据之外，还可用于存储任何其它二进制数据。

BLOB 类型

语法：BLOB

BLOB 类型用于指明变长的二进制大对象，长度最大为 100G-1 字节。

BFILE 类型

语法：BFILE

BFILE 用于指明存储在操作系统中的二进制文件，文件存储在操作系统而非数据库中，仅能进行只读访问。

DM_SQL 语言支持的表达式

DM 支持多种类型的表达式，包括数值表达式、字符串表达式、时间值表达式、时间间隔值表达式等。

数值表达式

一元算符 + 和 -

语法：+exp 、-exp

当单独使用时，+ 和 – 代表表达式的正负号。

select -(-5), +NOWPRICE from PRODUCTION.PRODUCT WHERE NOWPRICE<10;

查询结果为：5 6.1000

在 SQL 中由于两短横即“--”，表示“注释开始”，则双负号必须是-(-5)，而不是--5。

一元算符 ~

语法：~exp

按位非算符，要求参与运算的操作数都为整数数据类型。

select ~10 from PRODUCTION.PRODUCT WHERE NOWPRICE<10;

查询结果为：-11

只有精确数值数据类型的运算

两个相同类型的整数运算的结果类型不变，两个不同类型的整数运算的结果类型转换为范围较大的那个整数类型。

整数与 NUMERIC，DEC 等类型运算时，SMALLINT 类型的精度固定为 5，标度为 0；

INTEGER 类型的精度固定为 10，标度为 0；BIGINT 类型的精度固定为 19，标度为 0。

exp1+exp2 结果的精度为二者整数部分长度(即精度－标度)的最大值与二者标度的最大值之和，标度是二者标度的最大值；

exp1-exp2 结果的精度为二者整数部分长度的最大值与二者标度的最大值之和，标度是二者标度的最大值；

exp1*exp2 结果的精度为二者的精度之和，标度也是二者的标度之和；

exp1/exp2 结果为 DEC 类型（缺省的精度为 38，标度未限定）；

exp1%exp2 结果为 DEC 类型（缺省的精度为 38，标度未限定）。

使用 +、-运算

select NOWPRICE+1, NOWPRICE-1 from PRODUCTION.PRODUCT WHERE NOWPRICE<10;

查询结果为：7.1 5.1

使用*、/运算

select NOWPRICE*10, NOWPRICE/10 from PRODUCTION.PRODUCT WHERE NOWPRICE<10;

查询结果为：61 0.61

使用 % 运算

select NOWPRICE%4 from PRODUCTION.PRODUCT WHERE NOWPRICE<10;

查询结果为：2.1

有近似数值数据类型的运算

对于 exp1+exp2 、exp1- exp2 、exp1*exp2 、exp1/exp2 、exp1%exp2 中 exp1 和 exp2 只要有一个为近似数值数据类型，则结果为近似数值数据类型。

drop table TEST;
create table TEST(C1 int, C2 double);
insert into TEST values(5, 3.3);
select C1-C2, C1/C2 from TEST;

查询结果为：1.700000000000000E+00 1.515151515151515E+00

二元算符 &

语法：exp1 & exp2

按位与算符，要求参与运算的操作数都为整数数据类型。

select 20 & 10 from PRODUCTION.PRODUCT WHERE NOWPRICE<10;

查询结果为：0

二元算符 |

语法：exp1 | exp2

按位或算符，要求参与运算的操作数都为整数数据类型。

select 20 | 10 from PRODUCTION.PRODUCT WHERE NOWPRICE<10;

结果是：30

二元算符 ^

语法：exp1 ^ exp2

按位异或算符，要求参与运算的操作数都为整数数据类型。

select 20 ^ 10 from PRODUCTION.PRODUCT WHERE NOWPRICE<10;

查询结果为：30

二元算符<<、>>

语法：exp1 << exp2
exp1 >> exp2

左移、右移运算符，要求参与运算的操作数只能为整数数据类型、精确数据类型。

左移运算符的使用

select 1 << 3 from PRODUCTION.PRODUCT WHERE NOWPRICE<10;

查询结果为：8

右移运算符的使用

select 8 >> 3 from PRODUCTION.PRODUCT WHERE NOWPRICE<10;

查询结果为：1

当在表达式之间使用 <<、>> 时，对于表达式 exp1、exp2 的类型规则如下：

只有整数数据类型的运算

左右操作数都为 smallint、tinyint 时操作结果为 int 类型；

左右操作数都为 int 类型时操作结果为 int 类型；

左右操作数都为 bigint 类型时操作结果为 bigint 类型。

左操作数是 int、smallint、tinyint 类型，右操作数为 int、smallint、tinyint 类型时，操作结果为范围较大的类型。

当左操作数或右操作数有一个是 bigint 类型时，操作结果为 bigint 类型。

有精确数据类型的运算：

当左操作数、右操作数都是精确数据类型时，分别四舍五入转化成 bigint 类型后运算，结果为 bigint 类型；

当整数与精确数据类型运算时，将精确数据类型四舍五入转化成 bigint 类型后运算，结果为 bigint 类型。

有字符串数据类型的运算

字符串指数字字符串，不支持字符串与字符串运算；

当整数与数字字符串数据类型运算时，结果为整数数据类型；

当精确数据类型与字符串数据类型运算时，结果为 bigint 类型。

字符串表达式

连接 ||

语法：STR1 || STR2

(STR1 代表字符串 1，STR2 代表字符串 2)

连接操作符对两个运算数进行运算，其中每一个都是对属于同一字符集的字符串的求值。它以给定的顺序将字符串连接在一起，并返回一个字符串。其长度等于两个运算数长度之和。如果两个运算数中有一个是 NULL，则 NULL 等价为空串。

select '武汉' || ADDRESS1 from PERSON.ADDRESS WHERE ADDRESSID=3;

查询结果为：武汉青山区青翠苑 1 号

时间值表达式

时间值表达式的结果为时间值类型，包括日期(DATE)类型，时间(TIME)类型和时间戳(TIMESTAMP)间隔类型。DM SQL 不是对于任何的日期时间和间隔运算数的组合都可以计算。如果任何一个运算数是 NULL，运算结果也是 NULL。下面列出了有效的可能性和结果的数据类型。

日期 + 间隔，日期-间隔和间隔 + 日期，得到日期

日期表达式的计算是根据有效格里高利历日期的规则。如果结果是一个无效的日期，表达式将出错。参与运算的间隔类型只能是 INTERVAL YEAR、INTERVAL MONTH、INTERVAL YEAR TO MONTH、INTERVAL DAY。

如果间隔运算数是年-月间隔，则没有从运算数的 DAY 字段的进位。

select PUBLISHTIME + INTERVAL '1' YEAR, PUBLISHTIME - INTERVAL '1' YEAR
from PRODUCTION.PRODUCT
where PRODUCTID=1;

查询结果为：2006-04-01 2004-04-01<44444>

时间 + 间隔，时间-间隔和间隔 + 时间，得到时间

时间表达式的计算是根据有效格里高利历日期的规则。如果结果是一个无效的时间，表达式将出错。参与运算的间隔类型只能是 INTERVAL DAY、INTERVAL HOUR、INTERVAL MINUTE、INTERVAL SECOND、INTERVAL DAY TO HOUR、INTERVAL DAY TO MINUTE、INTERVAL DAY TO SECOND、INTERVAL HOUR TO MINUTE、INTERVAL HOUR TO SECOND、INTERVAL MINUTE TO SECOND。

例对时间值进行 +、-小时间隔运算。

SELECT TIME '19:00:00'+INTERVAL '9' HOUR,TIME '19:00:00'-INTERVAL '9' HOUR;

查询结果为：04:00:00 10:00:00

时间戳记 + 间隔，时间戳记-间隔和间隔 + 时间戳记，得到时间戳记

时间戳记表达式的计算是根据有效格里高利历日期的规则。如果结果是一个无效的时间戳记，表达式将出错。参与运算的间隔类型只能是 INTERVAL YEAR、INTERVAL MONTH、INTERVAL YEAR TO MONTH、INTERVAL DAY、INTERVAL HOUR、INTERVAL MINUTE、INTERVAL SECOND、INTERVAL DAY TO HOUR、INTERVAL DAY TO MINUTE、INTERVAL DAY TO SECOND、INTERVAL HOUR TO MINUTE、INTERVAL HOUR TO SECOND、INTERVAL MINUTE TO SECOND。

与时间的计算不同，当结果的小时值大于等于 24 时，结果进位到天。

例

SELECT TIMESTAMP'2007-07-15 19:00:00'+INTERVAL'9'HOUR,TIMESTAMP'2007-07-15 19:00:00'-INTERVAL'9'HOUR;

查询结果为：2007-07-16 04:00:00 2007-07-15 10:00:00

注：在含有 SECOND 值的运算数之间的一个运算的结果具有等于运算数的小数秒精度的小数秒精度。

日期 + 数值，日期-数值和数值 + 日期，得到日期

日期与数值的运算，等价于日期与一个 INTERVAL ‘数值’ DAY 的时间间隔的运算。

例 1 未设置 + 或-的数值时返回操作发生的日期

SELECT CURDATE(); //假设该查询操作发生在2011年9月29日

查询结果为：2011-09-29

例 2 返回当前日期 + 数值后的结果

SELECT CURDATE() + 2;

查询结果为：2011-10-01

例 3 返回当前日期-数值的结果

SELECT CURDATE() - 100;

查询结果为：2011-06-21

时间间隔值表达式

日期-日期，得到间隔

由于得到的结果可能会是“年-月-日”间隔，而这是不支持的间隔类型，故要对结果强制使用语法：

(日期表达式-日期表达式)<时间间隔限定符>

结果由<时间间隔限定符>中最不重要的日期字段决定。

例日期值-日期值，得到间隔

SELECT (PUBLISHTIME-DATE'1990-01-01')YEAR TO MONTH FROM PRODUCTION.PRODUCT WHERE PRODUCTID=1;

查询结果为：INTERVAL '15-3' YEAR(9) TO MONTH

时间－时间，得到间隔

要对结果强制使用语法：

(时间表达式-时间表达式)<时间间隔限定符>

结果由<时间间隔限定符>中最不重要的时间字段决定。

例时间值-时间值，得到间隔

SELECT (TIME'19:00:00'-TIME'10:00:00') HOUR;

查询结果为：INTERVAL '9' HOUR(9)

时间戳记 - 时间戳记，得到间隔

要对结果强制使用语法：

(时间戳记表达式-时间戳记表达式)<时间间隔限定符>

结果由<时间间隔限定符>中最不重要的日期时间字段决定。

例时间戳值-时间戳值，得到间隔

SELECT (TIMESTAMP '2007-07-15 19:00:00'- TIMESTAMP '2007-01-15 19:00:00') HOUR;

查询结果为：INTERVAL '4344' HOUR(9)

年月间隔 + 年月间隔和年月间隔 - 年月间隔，得到年月间隔

参加运算的两个间隔必须有相同的数据类型，若得出无效的间隔的表达式将出错。结果的子类型包括运算数子类型所有的域，关于结果的引导精度规定如下：

如果二者的子类型相同，则为二者引导精度的最大值；如果二者的子类型不同，则为与结果类型首字段相同的那个运算数的引导精度。

例对年月间隔进行 +、-运算，得到年月间隔

SELECT INTERVAL'2007-07'YEAR(4) TO MONTH + INTERVAL'7'MONTH,
INTERVAL'2007-07'YEAR(4) TO MONTH - INTERVAL'7'MONTH;

查询结果为：INTERVAL '2008-2' YEAR(9) TO MONTH INTERVAL '2007-0' YEAR(9) TO MONTH

日时间隔 + 日时间隔和日时间隔 - 日时间隔，得到日时间隔

参加运算的两个间隔必须有相同的数据类型，若得出无效的间隔表达式将出错。结果的子类型包含运算数子类型所有的域，结果的小数秒精度为两运算数的小数秒精度的最大值，关于结果的引导精度规定如下：

如果二者的子类型相同，则为二者引导精度的最大值；如果二者的子类型不同，则为与结果类型首字段相同的那个运算数的引导精度。

例对日时间隔进行 +、-运算，得到日时间隔

SELECT INTERVAL'7 15'DAY TO HOUR + INTERVAL'10:10'MINUTE TO SECOND,

INTERVAL'7 15'DAY TO HOUR - INTERVAL'10:10'MINUTE TO SECOND;

查询结果为：

INTERVAL '7 15:10:10.000000' DAY(9) TO SECOND(6)

INTERVAL '7 14:49:50.000000' DAY(9) TO SECOND(6)

运算符的优先级

当一个复杂的表达式有多个运算符时，运算符优先性决定执行运算的先后次序。运算符有下面这些优先等级(从高到低排列)。在较低等级的运算符之前先对较高等级的运算符进行求值。

( )
+(一元正)、-(一元负)、~(一元按位非)
*(乘)、/(除)、%(取余数)
+(加)、-(减)
|| (串联)
<<(左移)、>>(右移)
&(按位与)、^(按位异)、|(按位或)

DM_SQL 数据定义语言

用户数据库建立后，就可以定义基表来保存用户数据的结构。

DM 数据库的表可以分为两类，分别为数据库内部表和外部表，数据库内部表由数据库管理系统自行组织管理，而外部表在数据库的外部组织，是操作系统文件。

其中内部表包括：数据库基表、HUGE 表和水平分区表。

定义数据库基表

-- 创建employee表

CREATE TABLE employee
(
  employee_id INTEGER,
  employee_name VARCHAR2(20) NOT NULL,
  hire_date DATE,
  salary INTEGER,
  department_id INTEGER NOT NULL
);

-- 使用 CREATE TABLE 语句创建 department 表

CREATE TABLE department
(
  department_id INTEGER PRIMARY KEY,
  department_name VARCHAR(30) NOT NULL
);

-- 非空约束

ALTER TABLE employee MODIFY(hire_date not null);

-- 主键约束

ALTER TABLE employee ADD constraint pk_empid PRIMARY KEY(employee_id);

-- 外键约束

ALTER TABLE employee ADD constraint fk_dept FOREIGN KEY (department_id) REFERENCES department (department_id);

DM_SQL 数据操纵语言

SQL书写规范

A. 关键字不能被拆分、缩写，也不能分行； B. 大小写不敏感，除了’ ’、” ”以外； C. 可以写在一行或者多行，可以换行； D. 采用缩进可提高语句的可读性。

简单查询

A. SELECT 标识选择哪些列，FROM标识从哪个表中选择 B. 省略模式名：set schema 模式名 C. 查询结果出现乱码：客户端字符集和数据库冲突语法：select () from ();

--单列查询 
eg:查询员工表的姓名

SQL>select employee_name from dmhr.employee;


--多列查询 
eg:查询员工表的姓名，部门编号，工资

SQL> select employee_name,department_id,salary from dmhr.employee;


--查询所有列

SQL>Select * from dmhr.employee;


--对列起别名 
eg:查询员工姓名和工资

SQL>select employee_name,salary as gz from dmhr.employee; 
SQL>select employee_name,salary gz from dmhr.employee;


连接 || 
eg:查询“***的工资是：”

SQL>select employee_name|| '的工资是:'||salary from dmhr.employee;


去重复 distinct (隐藏排序-升序) 
eg:在员工表查询部门编号

SQL>select distinct department_id from dmhr.employee;


--表达式 
eg:为所有人工资加 2000

SQL>select employee_name,salary,salary+2000 from dmhr.employee;

过滤查询（带条件查询）

比较运算符、逻辑运算、in、is null/is not null、between and ，like 语法：Select () from () where ();

--比较运算符：= > >= < <= <> != 
eg:查询名为“马学铭”的工资

SQL>select employee_name,salary from dmhr.employee where employee_name='马学铭'; 
SQL>select employee_name,salary,department_id from dmhr.employee where salary>=30000;

--逻辑运算符：and or not
and:如果两个分量条件都为真，则返回true
or：如果其中一个分量条件为真，则返回true
not:如果下面的条件为假，则返回true
eg:查询部门编号为101且工资大于10000的员工

SQL>select employee_name,salary,department_id from dmhr.employee where department_id=101 and salary >=10000;

eg:查询部门编号为103或者工资大于30000的员工

SQL>select employee_name,salary,department_id from dmhr.employee where department_id=103 or salary >=30000;


--模糊查询：like % _ 
%匹配0个或多个字符，_匹配 1 个字符
eg:查询姓陈的员工姓名和部门编号

SQL>select employee_name,department_id from dmhr.employee where employee_name like '陈%';

eg:查询名字里带有“强”的员工姓名和部门编号

SQL>select employee_name,department_id from dmhr.employee where employee_name like '%强%';

eg:查询姓陈且姓名为两个字的员工姓名和部门编号

SQL>select employee_name,department_id from dmhr.employee where employee_name like '陈_';


--in 用来查询某列值属于指定集合的元组
eg:查询部门编号为101和102的员工

SQL>select employee_name,department_id from dmhr.employee where department_id in (101,102)

--is null/is not null null 为空，不等于 0 
eg:查询部门编号为空/不为空的员工

SQL>select employee_name,job_id,department_id from dmhr.employee where department_id is null; 
SQL>select employee_name,job_id,department_id from dmhr.employee where department_id is not null;


between *** and *** 表示范围
eg:工资在10000~20000之间的员工

SQL>select employee_name,department_id,salary from dmhr.employee where salary BETWEEN 10000 and 20000;

等价于：

SQL>select employee_name,salary from dmhr.employee where salary>= 10000 and salary <=20000;

分组函数

COUNT：总个数函数 MAX：最大值函数 MIN：最小值函数 AVG：平均值集数 SUM：总和函数语法：Select () from () where () group by () having (); 使用说明： AVG、SUM 的参数必须为数值类型； MAX、MIN 的结果数据类型与参数类型保持一致； group by(先分组,再聚合)：给查询的结果进行分组，除了聚合函数不用跟在 group by 后面，其他 select 列，必须要跟在 group by 后面，having是对 group by 的进一步过滤，having 不能单独使用。

eg:统计工资大于30000的员工数

SQL>select count(1)from dmhr.employee where salary>=30000;

eg:求部门工资和大于 10 万的部门

SQL>select department_id,sum (salary) from dmhr.employee group by department_id having sum (salary) >100000;

eg：求部门编号为101的员工工资最大值

SQL>select department_id,max(salary)from dmhr.employee group by department_id having department_id=101;

eg：求部门编号为103的员工工资最小值

SQL>select department_id,min(salary)from dmhr.employee group by department_id having department_id=103;

eg：求部门编号为104的员工工资平均值

SQL>select department_id,avg(salary)from dmhr.employee group by department_id having department_id=104;

连接查询

内连接（inner)

结果集只显示满足条件的记录 
--自然连接 - 
查询不需要指定连接列，列名自己过滤，两个表的连接列的列名和数据类型要一致。 
eg:连接列department_id

SQL>select employee_name,department_id,department_name from dmhr.employee natural join dmhr.department;

--交叉连接cross join（笛卡集）---尽量避免，容易拖死数据库

SQL>select count(*) from dmhr.employee; （emoployee表的行集）
SQL>select count(*) from dmhr.department; （departmen表的行集）
SQL>select count(*) from dmhr.employee cross join dmhr.department; （两表行集乘积）


--join ... using 子句 
在满足多个连接列时，可以指定一个列做连接，连接列的前面不能加表名或者是前缀；join 关键字指定连接的两张表，using 指明连接列；要求 using 中的列存在于两张连接表中。
eg:指定连接列 department_id

SQL>select employee_name,department_name,department_id 
from dmhr.employee 
join dmhr.department 
using (department_id);


--join ... on子句 
join 关键字指定连接的两张表，on 子句指定连接条件表达式，其中不允许出现 rownum，可为表设置简单代称。
eg：查询员工入职时间

SQL>select e1.employee_name,j1.start_date 
from dmhr.employee e1 
join dmhr.job_history j1
on e1.employee_id=j1.employee_id;


--hash join 
可以通过 hint 强制走 hash join 
优化器 CBO 根据小表的连接列生成 hash 值，连接大表，扫描大表 
eg：查询员工编号和所属部门

SQL>select e.EMPLOYEE_ID,e.EMPLOYEE_NAME,d.DEPARTMENT_NAME from dmhr.employee e inner 
hash join dmhr.department d 
on e.department_id = d.department_id;

外连接（outer) 结果集除了显示满足条件的记录，不满足的也显示，用 null 代替

左外连接 
把 left join 左边的全部显示出来，右边只显示满足条件的记录，不满足条件的用 null 代替 
eg：查询员工电话号码和所属部门

SQL>select e.employee_name,e.phone_num,d.department_name
from dmhr.employee e
left join dmhr.department d
on d.department_id=e.department_id;


右外连接 
把 right join 右边的全部显示出来，右边只显示满足条件的记录，不满足条件的用 null 代替

SQL>select e.employee_name,e.phone_num,d.department_name
from dmhr.employee e
right join dmhr.department d
on d.department_id=e.department_id;


全外连接 
左外连接+右外连接

SQL>select e.employee_name,e.phone_num,d.department_name
from dmhr.employee e
full join dmhr.department d
on d.department_id=e.department_id;

子查询

当一个查询的结果是另一个查询的条件时，就叫子查询。子查询先运行，子查询的结果是主查询的条件。子查询可以嵌套子查询，子查询可以做临时表，字段。

单行子查询：一次只返回一条记录 
eg：查询马学铭的工资和部门编号
SQL>select employee_name,salary,department_id 
from dmhr.employee 
where employee_id = (select employee_id from dmhr.employee where employee_name='马学铭'); 

多行子查询：一次返回多条记录 
eg：查询比102部门所有人工资都高的员工
SQL>select employee_name,salary,department_id 
from dmhr.employee
where salary > all (select salary from dmhr.employee where department_id=102);