I'm a newbie in studying SQL.
我是学SQL的新手。
I'm in doubt about a thing.
我对一件事感到怀疑。
Today I viewed some query examples, and I found some string comparisons in the WHERE condition.
今天我查看了一些查询示例，并在WHERE条件下找到了一些字符串比较。
The comparison was made using the greater than (>) and less than (<) symbols, is this a possible way to compare strings in SQL? and how does it act? a string less than another one comes before in dictionary order? For example, ball is less than water? and this comparison is case sensitive? for example BALL < water, the upcase character does affect these comparison?
使用大于(>)和小于(<)符号进行比较，这是比较SQL中的字符串的一种方法吗?它是如何运作的?一个比另一个小的字符串按字典顺序出现?例如，球比水小?这种比较是区分大小写的吗?例如球 <水，upcase字符会影响这些比较吗?< p>
I've googled for hours but I was not able to find nothing that can drive me out these doubt.
我用谷歌搜索了好几个小时，但我找不到任何能让我怀疑的东西。
2 个解决方案
#1
17
The comparison operators (including < and >) "work" with string values as well as numbers.
比较运算符(包括 <和> )使用字符串值和数字“work”。
For MySQL
MySQL
By default, string comparisons are not case sensitive and use the current character set. The default is
latin1(cp1252 West European), which also works well for English.默认情况下，字符串比较不区分大小写，并使用当前字符集。默认情况下，字符串比较是latin1 (cp1252 West europe)，这对英语也很适用。
String comparisons will be case sensitive when the characterset collation of the strings being compared is case sensitive, i.e. the name of the character set ends in _cs rather than _ci. There's really no point in repeating all of the information that's available in MySQL Reference Manual here.
当被比较的字符串的字符集排序是区分大小写的时，字符串比较将区分大小写，即字符集的名称以_cs而不是_ci结尾。重复MySQL参考手册中提供的所有信息是毫无意义的。
MySQL Comparison Operators Reference:
http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html
MySQL比较操作符引用:http://devmy.sql.com/doc/refman/5.5 /en/comparison-operators.html
More information about MySQL charactersets/collations:
http://dev.mysql.com/doc/refman/5.5/en/charset.html
更多关于MySQL字符集/排序的信息:http://dev.mysql.com/doc/refman/5.5/en/charset.html
To answer the specific questions you asked:
回答你提出的具体问题:
Q: is this a possible way to compare strings in SQL?
问:这是比较SQL中的字符串的一种可能方法吗?
A: Yes, in both MySQL and SQL Server
答:是的，在MySQL和SQL Server中都有
Q: and how does it act?
问:它是如何行动的?
A: A comparison operator returns a boolean, either TRUE, FALSE or NULL.
比较运算符返回一个布尔值，要么是真，要么是假，要么是空。
Q: a string less than another one comes before in dictionary order? For example, ball is less than water?
问:在字典顺序中，字符串比另一个要少?例如，球比水小?
A: Yes, because 'b' comes before 'w' in the characteset collation, the expression
A:是的，因为在人物排比中，“b”在“w”之前
'ball' < 'water'
will return TRUE. (This depends on the characterset and on the collation.
将返回TRUE。(这取决于你的性格和排序。)
Q: and this comparison is case sensitive?
问:这种比较是区分大小写的吗?
A: Whether a particular comparison is case sensitive or not depends on the database server; by default, both SQL Server and MySQL are case insensitive.
A:特定的比较是否区分大小写取决于数据库服务器;默认情况下，SQL Server和MySQL都不区分大小写。
In MySQL it is possible to make string comparisons by specifying a characterset collation that is case sensitive (the characterset name will end in _cs rather than _ci)
在MySQL中，可以通过指定区分大小写的字符集排序进行字符串比较(字符集名称以_cs而不是_ci结尾)
Q: For example BALL < water, the upper case character does affect these comparison?
问:比如球 <水，大写字母是否会影响这些比较?< p>
A: By default, in both SQL Server and MySQL, the expression
答:默认情况下，在SQL Server和MySQL中，都是表达式
'BALL' < 'water'
would return TRUE.
将返回TRUE。
#2
7
In Microsoft SQL Server, collation determines to dictionary rules for comparing and sorting character data with regards to:
在Microsoft SQL Server中，排序确定了用于比较和排序字符数据的字典规则:
- case sensitivity
- 区分大小写
- accent sensitivity
- 口音的敏感性
- width sensitivity
- 宽度的敏感性
- kana sensitivity
- 假名的敏感性
SQL Server also includes binary collations where comparison and sorting is done by binary code point rather than dictionary rules. Once can choose from many collations according to the desired sensitivity behavior. The default collation selected for Latin-based language locales during SQL installation is case insensitive and accent sensitive.
SQL Server还包含二进制排序，其中比较和排序是由二进制代码点而不是字典规则完成的。一次可以根据需要的灵敏度行为从多个排序中进行选择。在SQL安装期间，为基于拉丁的语言环境选择的默认排序是不区分大小写和重音敏感的。
Collation is specified at the instance (during installation), database, and column level. Instance collation determines the collation of Instance-level objects like logins and database names as well as identifiers for variables, GOTO labels and temporary tables. Database collation (same as instance collation by default), determines the collation of database identifiers like table and column names as well as literal expressions. Column collation (same as database collation by default) determines the collation of that column.
在实例(安装期间)、数据库和列级别指定排序规则。实例排序确定实例级对象的排序，如登录和数据库名称，以及变量的标识符、GOTO标签和临时表。数据库排序(默认情况下与实例排序相同)确定数据库标识符(如表和列名)的排序，以及文字表达式。列排序(默认情况下与数据库排序相同)确定该列的排序。
It is certainly possible compare strings using '<', '>', '<>', ,LIKE, BETWEEN, etc.
使用'<'、'>'、'<>'、LIKE、BETWEEN等进行比较当然是可能的。
#1
17
The comparison operators (including < and >) "work" with string values as well as numbers.
比较运算符(包括 <和> )使用字符串值和数字“work”。
For MySQL
MySQL
By default, string comparisons are not case sensitive and use the current character set. The default is
latin1(cp1252 West European), which also works well for English.默认情况下，字符串比较不区分大小写，并使用当前字符集。默认情况下，字符串比较是latin1 (cp1252 West europe)，这对英语也很适用。
String comparisons will be case sensitive when the characterset collation of the strings being compared is case sensitive, i.e. the name of the character set ends in _cs rather than _ci. There's really no point in repeating all of the information that's available in MySQL Reference Manual here.
当被比较的字符串的字符集排序是区分大小写的时，字符串比较将区分大小写，即字符集的名称以_cs而不是_ci结尾。重复MySQL参考手册中提供的所有信息是毫无意义的。
MySQL Comparison Operators Reference:
http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html
MySQL比较操作符引用:http://devmy.sql.com/doc/refman/5.5 /en/comparison-operators.html
More information about MySQL charactersets/collations:
http://dev.mysql.com/doc/refman/5.5/en/charset.html
更多关于MySQL字符集/排序的信息:http://dev.mysql.com/doc/refman/5.5/en/charset.html
To answer the specific questions you asked:
回答你提出的具体问题:
Q: is this a possible way to compare strings in SQL?
问:这是比较SQL中的字符串的一种可能方法吗?
A: Yes, in both MySQL and SQL Server
答:是的，在MySQL和SQL Server中都有
Q: and how does it act?
问:它是如何行动的?
A: A comparison operator returns a boolean, either TRUE, FALSE or NULL.
比较运算符返回一个布尔值，要么是真，要么是假，要么是空。
Q: a string less than another one comes before in dictionary order? For example, ball is less than water?
问:在字典顺序中，字符串比另一个要少?例如，球比水小?
A: Yes, because 'b' comes before 'w' in the characteset collation, the expression
A:是的，因为在人物排比中，“b”在“w”之前
'ball' < 'water'
will return TRUE. (This depends on the characterset and on the collation.
将返回TRUE。(这取决于你的性格和排序。)
Q: and this comparison is case sensitive?
问:这种比较是区分大小写的吗?
A: Whether a particular comparison is case sensitive or not depends on the database server; by default, both SQL Server and MySQL are case insensitive.
A:特定的比较是否区分大小写取决于数据库服务器;默认情况下，SQL Server和MySQL都不区分大小写。
In MySQL it is possible to make string comparisons by specifying a characterset collation that is case sensitive (the characterset name will end in _cs rather than _ci)
在MySQL中，可以通过指定区分大小写的字符集排序进行字符串比较(字符集名称以_cs而不是_ci结尾)
Q: For example BALL < water, the upper case character does affect these comparison?
问:比如球 <水，大写字母是否会影响这些比较?< p>
A: By default, in both SQL Server and MySQL, the expression
答:默认情况下，在SQL Server和MySQL中，都是表达式
'BALL' < 'water'
would return TRUE.
将返回TRUE。
#2
7
In Microsoft SQL Server, collation determines to dictionary rules for comparing and sorting character data with regards to:
在Microsoft SQL Server中，排序确定了用于比较和排序字符数据的字典规则:
- case sensitivity
- 区分大小写
- accent sensitivity
- 口音的敏感性
- width sensitivity
- 宽度的敏感性
- kana sensitivity
- 假名的敏感性
SQL Server also includes binary collations where comparison and sorting is done by binary code point rather than dictionary rules. Once can choose from many collations according to the desired sensitivity behavior. The default collation selected for Latin-based language locales during SQL installation is case insensitive and accent sensitive.
SQL Server还包含二进制排序，其中比较和排序是由二进制代码点而不是字典规则完成的。一次可以根据需要的灵敏度行为从多个排序中进行选择。在SQL安装期间，为基于拉丁的语言环境选择的默认排序是不区分大小写和重音敏感的。
Collation is specified at the instance (during installation), database, and column level. Instance collation determines the collation of Instance-level objects like logins and database names as well as identifiers for variables, GOTO labels and temporary tables. Database collation (same as instance collation by default), determines the collation of database identifiers like table and column names as well as literal expressions. Column collation (same as database collation by default) determines the collation of that column.
在实例(安装期间)、数据库和列级别指定排序规则。实例排序确定实例级对象的排序，如登录和数据库名称，以及变量的标识符、GOTO标签和临时表。数据库排序(默认情况下与实例排序相同)确定数据库标识符(如表和列名)的排序，以及文字表达式。列排序(默认情况下与数据库排序相同)确定该列的排序。
It is certainly possible compare strings using '<', '>', '<>', ,LIKE, BETWEEN, etc.
使用'<'、'>'、'<>'、LIKE、BETWEEN等进行比较当然是可能的。