InterSystems SQL 的使用 - 第二部分 - 基础介绍
本文概述了InterSystems SQL的特点,特别是那些没有被SQL标准所涵盖或与InterSystems IRIS 数据平台的统一数据架构有关的特点。假定你已有SQL的知识,本文不是SQL概念或语法的介绍。
表
在InterSystems SQL中,数据是在表内呈现的。每个表都被定义为包含若干列。一个表可以包含零个或多个数据值的行。以下术语大致上是等同的。
数据 | 关系型数据库术语 | InterSystems SQL术语 | InterSystems IRIS术语 |
---|---|---|---|
database | schema | schema | package |
database | table | table | persistent class |
field | column | column | property |
record | row | row |
schema
SQL schema提供了一种将相关表、视图、存储过程和缓存查询分组的方法。模式的使用有助于防止在表一级的命名冲突,因为一个表、视图或存储过程的名称必须只在其schema内是唯一的。一个应用程序可以在多个schema中指定表。
SQL schema对应于IRIS中持久化的类包。通常情况下,一个模式的名称与它对应的包的名称相同,但是这些名称可能会因为不同的模式命名惯例而不同,或者因为不同的名称被故意指定。
命名:schema.name。如果没有指定schema:例如只有name。InterSystems IRIS会按以下方式提供模式。
- 对于DDL操作,InterSystems IRIS使用全系统默认的schema名称SQLUser。这个默认是可配置的。它适用于所有命名空间。
- 对于DML操作,InterSystems IRIS可以使用用户提供的schema搜索路径或全系统默认的schema名称。在动态SQL、嵌入式SQL和SQL Shell中,使用不同的技术来提供schema搜索路径。
在管理门户中查看一个命名空间内的所有现有schema:
从管理门户中选择系统资源管理器,然后选择SQL。用页面顶部的切换选项选择一个命名空间;这将显示可用的命名空间的列表。选择一个命名空间。选择屏幕左边的模式下拉列表。这将显示当前命名空间中的schema列表。从这个列表中选择一个schema;
SQL 查询
查询有两种类型:检索数据的查询(SELECT语句)和修改数据的查询(INSERT、UPDATE和DELETE语句)。
你可以以多种方式使用SQL查询:
- 在ObjectScript中使用嵌入式SQL。
- 在ObjectScript中使用动态SQL。
- 调用使用CREATE PROCEDURE或CREATE QUERY创建的存储过程。
- 使用一个类查询。
- 使用来自其他环境的ODBC或JDBC接口。
SELECT查询在本指南的查询数据库一章中有描述。
查询是InterSystems IRIS对象或ObjectScript程序的一部分。
Privileges 特权
InterSystems SQL提供了一种方法,通过权限来限制对表、视图等的访问。你可以定义一组用户和角色,并授予他们各种权限(读、写等等)。
数据显示 SelectMode
InterSystems SQL使用SelectMode选项来指定数据的显示或存储方式。可用的选项有逻辑模式、显示模式和ODBC模式。
数据在内部以逻辑模式存储,并可以以任何模式显示。每个数据类型类都可以通过使用LogicalToDisplay(), LogicalToODBC(), DisplayToLogical(), ODBCTtoLogical()方法来定义内部逻辑格式和显示格式或ODBC格式之间的转换。
当SQL SelectMode是显示模式时,LogicalToDisplay转换被应用,返回的值被格式化以用于显示。默认的SQL选择模式是逻辑模式;因此默认返回的值是以其存储格式显示的。
模式会影响查询结果集数据的显示格式,也会影响数据值的提供格式,如果提供的数据值与SelectMode不匹配,可能会导致错误或错误的结果。
例如,例如在WHERE子句中,如果DOB是一个以$HOROLOG逻辑格式存储的日期,而WHERE子句指定WHERE DOB > 2000-01-01(ODBC模式),SelectMode = ODBC返回预期的结果。但是,如果SelectMode = 显示模式,则生成SQLCODE -146 无法将日期输入转换为有效的逻辑日期值。如果SelectMode = 逻辑模式,则试图将2000-01-01解析为一个逻辑日期值,并返回0行。
对于大多数数据类型,三种SelectMode模式返回相同的结果。以下数据类型受到SelectMode选项的影响。
- 日期、时间和时间戳数据类型。
- %List数据类型 - 在ODBC选择模式下,列表项之间用逗号分隔符显示。在显示选择模式下,列表项之间以空白分隔符显示。
- 指定VALUELIST和DISPLAYLIST的数据类型。对于必填字段,如果您在显示模式下,在字段有DISPLAYLIST的表中插入一个值,您输入的显示值必须与DISPLAYLIST中的一个项目完全匹配。对于非必填字段,不匹配的值被转换为NULL值。
- 空字符串,和空BLOB(流字段)。在逻辑模式下,空字符串和BLOBs由非显示字符$CHAR(0)表示。在显示模式下,它们由一个空字符串("")表示。
SQL的SelectMode可以按以下方式指定。
- 对于当前进程,使用SetOption("SelectMode")方法。
- 对于InterSystems SQL Shell会话,使用SET SELECTMODE命令。
- 对于来自管理门户 "执行查询 "用户界面(系统浏览器,SQL)的查询结果集,使用 "显示模式 "下拉列表。
- 对于一个动态SQL %SQL.Statement实例,使用%SelectMode属性。
- 对于嵌入式SQL,使用ObjectScript的 #SQLCompile Select 预处理器指令设置。这个指令允许第四个值,Runtime,它将选择模式设置为RuntimeMode属性的任何设置。RuntimeMode默认为逻辑模式。
#SQLCOMPILE SELECT=Logical
#SQLCOMPILE SELECT=Display
#SQLCOMPILE SELECT=ODBC
- 对于的SQL命令CREATE QUERY、CREATE METHOD、CREATE PROCEDURE和CREATE FUNCTION, 使用SELECTMODE关键字。
- 对于SQL查询中的单个列,使用%EXTERNAL、%INTERNAL和%ODBCOUT函数。
SELECT TOP 5 DOB,%EXTERNAL(DOB) AS ExtDOB
Data Collation
data collation规定了数值的排序和比较方式,它是InterSystems SQL和InterSystems IRIS对象的一部分。
你可以指定collation类型作为字段/属性定义的一部分。除非另有规定,否则字符串字段/属性默认为命名空间的默认排序。默认情况下,字符串的命名空间默认排序是SQLUPPER。SQLUPPER排序法将字符串转换为大写字母,以便进行排序和比较。因此,除非另有规定,否则字符串的排序和比较是不分大小写的。
你可以指定一个collation类型作为索引定义的一部分,或者使用被索引字段的collation类型。
一个SQL查询可以通过对字段名应用一个collation函数来覆盖已定义的collation类型的字段/属性。ORDER BY子句指定了一个查询的结果集顺序;如果一个指定的字符串字段被定义为SQLUPPER,查询结果顺序是不区分大小写的。
SQL 执行
编写和执行SQL代码的方法包括:
- 嵌入式SQL:在ObjectScript代码中嵌入SQL代码。
- 动态SQL:从ObjectScript中执行SQL代码,使用%SQL.Statement类。
- Execute()方法:使用%SYSTEM.SQL类的Execute()方法执行SQL代码。
- 包含SQL代码的存储过程:使用CREATE PROCEDURE或CREATE QUERY创建。
- SQL Shell:从终端界面执行的SQL语句。
- 从管理门户执行的SQL语句:系统资源管理器 -> SQL。
- 使用InterSystems IRIS对象(类和方法):
- 定义一个持久的类(一个SQL表)。
- 定义一个索引。
- 定义和使用一个类查询。