Home Database Mysql Tutorial SQL语法 分隔符理解小结

SQL语法 分隔符理解小结

Jun 07, 2016 pm 05:59 PM
sql syntax delimiter

单引号和双引号之间的区别最早在SQL92标准中引入的。对于标识符,这个标准区分了常规标识符和分隔的标识符。

两者主要的区别在于:分隔的标识符被括在双引号中(Transact-SQL也支持方括号的使用:[标识符])并且是区分大小写的。单引号只用于字符串的定界。总的来说,引入分隔的标识符是为了对标识符进行规范,否则就会与保留字相同了。特别要提到的是,分隔的标识符能够使你在命名(标识符或变量的名字)的时候,免于使用在将来的SQL标准中可能出现的保留字。另外,分隔的标识符能够包含一些在通常的标识符名称中被视为不合法的字符,如空格。
在SQL SERVER中,双引号的使用由SET 语句中的QUOTED_IDENTIFIER选项来定义。如果这个选项被设为ON,则双引号中的标识符将被定义为一个分隔的标识符。在这种情况下,双引号不能被用于定界字符串。”
记住以下几句话就能把分隔符理解了
以上这段话是书上的原话,我对分隔的标识符的理解是:例如"hu","h u",[hu],
[ h u]这几个标识符都是不同的,在使用时就像使用a,b等标识符一样,只不过必须加上双引号或中括号,不知这样理解对不对。
我自己对分隔符的理解:分隔符就是为了起到分隔作用。 SELECT * FROM "My Table"
WHERE "Last Name" = 'O''Brien'
自我分析:SELECT,FROM,where是关键字。"My Table"就是标志符,而””就是分隔符
请大虾们指教。
其实你理解的挺对的
但是别忘了它的作用是为了定义标识符
符合所有标识符格式规则的标识符可以使用分隔符,也可以不使用分隔符。
不符合标识符格式规则的标识符必须使用分隔符。
分隔标识符在下列情况下使用:
当在对象名称或对象名称的组成部分中使用保留字时。
推荐不要使用保留关键字作为对象名称。从 Microsoft® SQL Server™ 早期版本升级的数据库可能含有标识符,这些标识符包括早期版本中未保留而在 SQL Server 2000 中保留的字。可用分隔标识符引用对象直到可改变其名称。
当使用未被列为合法标识符的字符时。
SQL Server 允许在分隔标识符中使用当前代码页中的任何字符。但是,不加选择地在对象名称中使用特殊字符将使 SQL 语句和脚本难以阅读和维护。
Transact-SQL 所使用的分隔符类型:
说明 分隔符仅用于标识符。分隔符不能用于关键字,不论它们在 SQL Server 中是否被标记为保留字。
被引用的标识符用双引号 (") 分隔开:
SELECT * FROM "Blanks in Table Name"
括在括号中的标识符用方括号 ([ ]) 分隔:
SELECT * FROM [Blanks In Table Name]
仅当 QUOTED_IDENTIFIER 选项设置为 ON 时,被引用的标识符才有效。默认情况下,当用于 SQL Server 的 Microsoft OLE DB 提供程序和 SQL Server ODBC 驱动程序连接时,将 QUOTED_IDENTIFIER 设置为 ON。默认情况下,DB-Library 不将 QUOTED_IDENTIFIER 设置为 ON。不管使用何种接口,个别应用程序或用户可随时更改设置。SQL Server 提供了多种方法来指定该选项。例如,在 SQL Server 企业管理器和 SQL 查询分析器中,该选项可在对话中设置。在 Transact-SQL 中,可以使用 SET QUOTED_IDENTIFIER、sp_dboption 的 quoted identifier 选项或 sp_configure 的 user options 选项将此选项设为多种级别。
当 QUOTED_IDENTIFIER 为 ON 时,对于 SQL 语句中的双引号和单引号 (') 的使用,SQL Server 遵循 SQL-92 规则:
双引号只能用于分隔标识符,不能用于分隔字符串。
为保持与现有应用程序的兼容性,SQL Server 并不完全强制该规则。如果字符串没有超过标识符的长度,则该字符串可包含在双引号内。但不建议这样做。
单引号必须用来包含字符串,不能用于分隔标识符。
如果字符串包含单引号,则需要在单引号前再增加一个单引号:
SELECT * FROM "My Table"
WHERE "Last Name" = 'O''Brien'
当 QUOTED_IDENTIFIER 为 OFF 时,对于双引号和单引号的使用,SQL Server 遵循如下规则:
引号不能用于分隔标识符,而是用括号作为分隔符。
单引号或双引号可用于包含字符串。
如果使用双引号,嵌入的单引号不需要用两个单引号来表示:
SELECT * FROM [My Table]
WHERE [Last Name] = "O'Brien"
无论 QUOTED_IDENTIFIER 的设置如何,都可以在括号中使用分隔符。
分隔标识符规则
分隔标识符的格式规则是:
分隔标识符可以包含与常规标识符相同的字符数(1 到 128 个,不包括分隔符字符)。本地临时表标识符最多可以包含 116 个字符。
标识符的主体可以包含当前代码页内字母(分隔符本身除外)的任意组合。例如,分隔符标识符可以包含空格、对常规标识符有效的任何字符以及下列任何字符: 代字号 (~) 连字符 (-)
惊叹号 (!) 左括号 ({)
百分号 (%) 右括号 (})
插入号 (^) 撇号 (')
and 号 (&) 句号 (.)
左圆括号 (() 反斜杠 (\)
右圆括号 ()) 重音符号 (`)
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to change number format on iPhone How to change number format on iPhone Apr 13, 2023 pm 06:16 PM

Which number formats you can choose on iOS 16 With changes to iOS 16.4 (beta 2), you can choose from three different number formats for your iPhone. These formats use spaces, commas, and periods as symbols that separate thousands digits in numbers or as decimal points. The decimal point is the character used to separate the integer part of a value from its fractional part, usually assigned by a period (.) or a comma (,). The thousand separator is used to separate multi-digit numbers into three groups, usually specified by a period (.), a comma (,), or a space ( ). On the latest version of iOS, you'll be able to apply any of the following number formats as your iPhone's preferred option: 1,23

What are the special symbols in C language? What are the special symbols in C language? Aug 26, 2023 pm 01:41 PM

In the C programming language, typically, special symbols have special meanings and cannot be used for other purposes. Some special symbols used in C programming are as follows −[](){},;*=# Let us understand their definitions as follows: Square brackets []-The opening and closing of square brackets are used for array element references, indicating Single and multidimensional subscripts. Parentheses () - These special symbols are used for function calls and function parameters. Curly braces {} - Opening and closing curly braces indicate the beginning and end of a block of code that contains multiple executable statements. Comma (,) - used to separate multiple statements, such as parameter separation in function calls. Colon (:) - This is an operator that actually calls something called an initialization list. Semicolon (;) - it is called a slang

The limitations of MySQL technology: Why is it not enough to compete with Oracle? The limitations of MySQL technology: Why is it not enough to compete with Oracle? Sep 08, 2023 pm 04:01 PM

The limitations of MySQL technology: Why is it not enough to compete with Oracle? Introduction: MySQL and Oracle are one of the most popular relational database management systems (RDBMS) in the world today. While MySQL is very popular in web application development and small businesses, Oracle has always dominated the world of large enterprises and complex data processing. This article will explore the limitations of MySQL technology and explain why it is not enough to compete with Oracle. 1. Performance and scalability limitations: MySQL is

How to change the decimal separator from comma to dot in MacOS Ventura How to change the decimal separator from comma to dot in MacOS Ventura Apr 15, 2023 pm 12:43 PM

Change the decimal separator and number format in MacOS Ventura Go to the  Apple menu and select "System Settings" Go to "General" Select "Language & Region" Find "Number Format" and pull down the submenu next to it to access the various number format options , allowing you to change the decimal separator to a comma, dot, or space to choose your desired number format, and the setting will instantly apply throughout MacOS. "Number format" missing in MacOS Ventura system settings? Some Mac users using different locales have found that the number formatting section is missing from their MacOS Ventura system settings, whether this is a serious oversight or a cute bug. Anyway, if you find yourself missing a number grid

How to split a string based on a certain delimiter using the split() method of String class How to split a string based on a certain delimiter using the split() method of String class Jul 24, 2023 pm 09:32 PM

How to use the split() method of the String class to split a string based on a certain delimiter Overview: In the Java programming language, the String class is a very important and commonly used class. The String class provides many practical methods, among which the split() method is used to split strings. The split() method splits a string into multiple substrings based on the specified delimiter and stores these substrings in a string array. This article will introduce how to use split of String class

What are the delimiters for php server scripts? What are the delimiters for php server scripts? Sep 18, 2023 pm 01:58 PM

PHP server script delimiters include "<?php", "<?", "?>", "<!--" and "//-->". Detailed introduction: 1. "<?php", which is the most common start tag in PHP, is used to identify the beginning of the PHP code block. After this tag, any PHP code can be written; 2. "<?" The short tag is another type of start tag in PHP. It is relatively concise and can be modified through settings in the PHP configuration file. Since the short tag may conflict with tags in other languages, its use is not recommended and so on.

Extract substrings between any pair of delimiters Extract substrings between any pair of delimiters Aug 26, 2023 pm 02:21 PM

A delimiter is a character that separates a string from other characters, for example in sentences in our daily reading activities we separate different words by spaces. In mathematics and regular expressions, we use () brackets as the main delimiter. The concept of substrings and their operations is very important in programming, especially in the C language, which is used to write compilers and assemblers. Identifies delimiters in a string and copies the characters after the starting delimiter into another variable, up to the ending delimiter. The == and != operators can be used to compare characters in a string to a user-specified delimiter character. Use the scanf() function to accept a string from the user, so spaces cannot be part of the string. If you use puts() or other functions or methods, you can

In-depth understanding of PHP server script delimiters: comprehensively master the usage and characteristics of various delimiters In-depth understanding of PHP server script delimiters: comprehensively master the usage and characteristics of various delimiters Jan 11, 2024 pm 05:16 PM

Complete collection of PHP server script delimiters: To fully understand the usage and characteristics of various delimiters, specific code examples are required. 1. Introduction In the process of writing PHP server scripts, in order to implement different functions and logic, we need to use various delimiters. Delimiters play a very important role in PHP code and can be used to separate characters, strings or code blocks, etc. This article will introduce common delimiters in PHP server scripts, explain their usage and characteristics in detail, and provide relevant code examples so that readers can better understand and apply them. two,

See all articles