文章
· 三月 1, 2021 阅读大约需 7 分钟

第二章 InterSystems SQL基础

第二章 InterSystems SQL基础

本章概述了InterSystems SQL的特性,特别是那些SQL标准未涵盖的特性,或者与InterSystems IRIS®数据平台统一数据架构相关的特性。
本教程假定读者具备SQL知识,并不是为介绍SQL概念或语法而设计的。

本章讨论以下主题:
- 表
- 查询
- 权限
- 数据显示选项
- 数据排序类型
- 执行SQL

在InterSystems SQL中,数据显示在表中。每个表都包含许多列。一个表可以包含零个或多个数据值行。以下术语大体上等效:

数据术语 关系数据库术语 InterSystems IRIS术语
数据库 架构
数据库 persistent class(持久类)
字段 属性
记录

表有两种基本类型:基表(包含数据,通常简称为表)和视图(基于一个或多个表提供逻辑视图)。

模式与架构

SQL模式提供了一种将相关表,视图,存储过程和缓存查询的集合进行分组的方法。模式的使用有助于防止表级别的命名冲突,因为表,视图或存储过程的名称在其模式内必须唯一。应用程序可以在多个架构中指定表。

SQL模式与持久性类包相对应。通常,模式与其相应的程序包具有相同的名称,但是由于不同的模式命名约定或故意指定了不同的名称,因此这些名称可能有所不同。模式到程序包的映射在SQL到类名的转换中有进一步描述。

模式是在特定的名称空间中定义的。模式名称在其名称空间内必须是唯一的。将第一个项目分配给它时,会自动创建一个模式(及其对应的程序包),从中删除最后一个项目时,会自动将其删除。

可以指定一个限定或不限定的SQL名称,限定名称指定模式:schema.name。
非限定名不指定模式名。
如果不指定模式,InterSystems IRIS将提供如下模式:

  • 对于DDL操作,InterSystems IRIS使用系统范围的默认架构名称。此默认值可配置。它适用于所有名称空间。
  • 对于DML操作,InterSystems IRIS可以使用用户提供的模式搜索路径或系统范围内的默认模式名称。在动态SQL,嵌入式SQL和SQL Shell中,使用了不同的技术来提供模式搜索路径。

DML(data manipulation language):
它们是SELECT、UPDATE、INSERT、DELETE,就象它的名字一样,这4条命令是用来对数据库里的数据进行操作的语言
DDL(data definition language):
DDL比DML要多,主要的命令有CREATE、ALTER、DROP等,DDL主要是用在定义或改变表(TABLE)的结构,数据类型,表之间的链接和约束等初始化工作上,他们大多在建立表时使用
DCL(Data Control Language):
是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句,包括(grant,deny,revoke等)语句。在默认状态下,只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL

要查看名称空间内的所有现有模式,请执行以下操作:

  1. 在管理门户中,选择“系统资源管理器”,然后选择“ SQL”。使用页面顶部的Switch选项选择一个名称空间;这将显示可用名称空间的列表。选择一个名称空间。

image

image

  1. 选择屏幕左侧的Schema下拉列表。这将显示当前名称空间中的架构列表。从该列表中选择一个模式;所选名称将出现在“模式”框中。

image

  1. 如果有数据下拉列表允许选择表,视图,过程或缓存的查询,或所有属于模式的所有这些。设置此选项后,单击三角形以查看项目列表。如果没有项目,则单击三角形无效。

image

查询

在InterSystems SQL中,可以通过查询查看和修改表中的数据。粗略地说,查询有两种形式:查询数据(SELECT语句)和修改数据(INSERT,UPDATE和DELETE语句)。

可以通过多种方式使用SQL查询:
- 在ObjectScript中使用嵌入式SQL。
- 在ObjectScript中使用动态SQL。
- 调用使用CREATE PROCEDURE或CREATE QUERY创建的存储过程。
- 使用类查询。
- 使用来自各种其他环境的ODBC或JDBC接口。

权限

InterSystems SQL提供了一种通过权限来限制对表、视图等的访问的方法。

数据显示选项

InterSystems SQL使用SelectMode选项来指定如何显示或存储数据。
可用的选项有Logical、Display和ODBC。
数据在内部以逻辑模式存储,并且可以在这些模式中的任何一种中显示。
通过使用LogicalToDisplay()LogicalToODBC()DisplayToLogical()odbcological()方法,每个数据类型类都可以在内部逻辑格式和显示格式或ODBC格式之间进行转换。
当显示SQL SelectMode时,将应用LogicalToDisplay转换,并对返回值进行格式化以便显示。
默认的SQL SelectMode是逻辑的;
因此,默认情况下返回值以存储格式显示。

SelectMode影响查询结果集数据显示的格式,SelectMode还影响应该提供数据值的格式,例如在WHERE子句中。
InterSystems IRIS根据存储模式和指定的SelectMode选择合适的转换方法。
所提供的数据值与SelectMode之间的不匹配可能导致错误或错误的结果。
例如,如果DOB是一个以$HOROLOG逻辑格式存储的日期,并且WHERE子句指定DOB > 2000-01-01(ODBC格式),则SelectMode = ODBC返回预期的结果。
SelectMode = Display生成SQLCODE -146,无法将日期输入转换为有效的逻辑日期值。
SelectMode =Logic2000-01-01解析为逻辑日期值,并返回零行。

对于大多数数据类型,三种SelectMode模式返回相同的结果。
以下数据类型受SelectMode选项影响:

  • 日期,时间和时间戳数据类型。 InterSystems SQL支持多种日期,时间和时间戳数据类型(%Library.Date%Library.Time%Library.PosixTime%Library.TimeStamp%MV.Date)。除%Library.TimeStamp外,这些数据类型对逻辑,显示和ODBC模式使用不同的表示形式。在其中的几种数据类型中,InterSystems IRIS以$HOROLOG格式存储日期。此逻辑模式内部表示包括从任意起始日期(1840年12月31日)起的天数的整数,逗号分隔符以及从当天午夜开始的秒数的整数。 InterSystems IRIS将%PosixTime时间戳存储为编码的64位带符号整数。在“显示”模式下,日期和时间通常以数据类型的FORMAT参数指定的格式显示,或者当前语言环境的日期和时间格式默认为%SYS.NLS.Format。美国语言环境的默认值为DD / MM / YYYY hh:mm:ss。在ODBC模式下,日期和时间始终表示为YYYY-MM-DD hh:mm:ss.fff%Library.TimeStamp数据类型还将这种ODBC格式用于逻辑和显示模式。
  • %LIST数据类型。InterSystems IRIS逻辑模式使用两个非打印字符存储列表,这两个字符出现在列表中的第一个项目之前,并显示为列表项目之间的分隔符。在ODBC SelectMode中,列表项显示时列表项之间带有逗号分隔符。在Display SelectMode中,列表项显示时,列表项之间有空格分隔符。
  • 指定VALUELISTDISPLAYLIST的数据类型。如果处于显示模式,并且在字段具有DISPLAYLIST的表中插入一个值,则输入的显示值必须与DISPLAYLIST中的一项完全匹配。
  • 空字符串和空BLOB(流字段)。在逻辑模式下,空字符串和BLOB由非显示字符$CHAR(0)表示。在显示模式下,它们由空字符串(“”)表示。

SQL SelectMode可以指定如下:

  • 对于当前进程,请使用$SYSTEM.SQL.SetSelectMode()
  • 对于InterSystems SQL Shell会话,请使用SET SELECTMODE命令。
  • 使用“显示模式”下拉列表,从管理门户“执行查询”用户界面(系统资源管理器,SQL)获得查询结果集。
  • 对于动态SQL %SQL.Statement实例,请使用%SelectMode属性。
  • 对于嵌入式SQL,请使用ObjectScript #SQLCompile Select预处理器指令设置。该伪指令允许使用第四个值Runtime,它将选择模式设置为RuntimeMode属性设置为:逻辑,显示或ODBC。 RuntimeMode的默认值为Logical。
  • 对于使用SELECTMODE关键字的SQL命令CREATE QUERY,CREATE METHOD,CREATE PROCEDURE和CREATE FUNCTION。
  • 通过使用%EXTERNAL%INTERNAL%ODBCOUT函数在SQL查询中的单个列。

数据排序

Collation种类决定了值的排序和比较方式,它是InterSystems SQL和InterSystems IRIS对象的一部分。

可以指定排序规则类型作为字段/属性保护的一部分。除非另有说明,否则字符串字段/属性默认为命名空间默认排序规则。默认情况下,字符串的命名空间默认排序规则是SQLUPPER。
SQLUPPER排序规则将字符串转换为大写,以便排序和比较。因此,除非另有说明,字符串排序和比较不区分大小写。

可以指定排序规则类型作为索引保护的一部分,或者使用索引字段的排序规则类型。

通过将排序函数应用于字段名,SQL查询可以覆盖未保护的字段/属性排序规则类型。ORDER BY子句指定查询的结果集序列;如果指定的字符串字段被保护为SQLUPPER,查询结果顺序不区分大小写。

执行SQL

InterSystems IRIS支持多种方法来编写和执行SQL代码。其中包括:

  • 嵌入式SQL:嵌入在ObjectScript代码中的SQL代码。
  • 动态SQL:使用%SQL.Statement类从ObjectScript中执行的SQL代码。
  • Execute()方法:使用%SYSTEM.SQL类的Execute()方法执行SQL代码。
  • 包含SQL代码的存储过程,使用CREATE PROCEDURE或CREATE Query创建。
  • SQL Shell:从终端界面执行的SQL语句。
  • 执行查询界面:从管理门户执行的SQL语句。

可以使用InterSystems IRIS对象(类和方法)执行以下操作:

  • 持久性类(SQL表)。
  • 定义索引。
  • 定义并使用类查询。
讨论 (2)1
登录或注册以继续