Go: How to learn SQL (Part III: SQL data types and three-valued logic)

Transferred from: Http://blog.163.com/[email protected]/blog/static/285720652010950921286/

7. Data type

In database theory, the two parts of relational model and data type are orthogonal (see the 8th section of "The path of Programmer's cultivation" for the discussion of "orthogonality"), which is independent of each other. In other words, the relational model does not care what the data type of each table's field is, whether it is a basic type such as integers, strings, or a custom type, such as a combination type, a class, or a relational model that requires only that each field be atomic.

In database theory, the data type is also called a domain, but the domain is a more restrictive definition. For example, the number of students in a class and the average student may be int type, but this is two different fields, "student number * Student Average score = Student Total score", but "student number + student average" is meaningless. The current mainstream DBMS does not seem to have good support for the domain, but the future situation may change, and the design of custom types also requires a full understanding of the problem. See the 2nd Chapter of the Deep Exploration relational database.

For database and SQL applications, in addition to mastering the principle of relational model, it is necessary to understand the data types supported by DBMS and their transformation rules.

1. Basic data types

A DBMS typically supports the following kinds of basic data types (for example, SQL Server):
-Exact number: integer (bigint/int/smallint/tinyint/bit), fixed-point decimal (decimal), currency (Money/smallmoney)
-Approximate number: floating point (float)
-Date and time: Datetime/smalldatetime; Date, time, DateTime2, DateTimeOffset (after 4 new types of SQL Server 2008)
-String and Unicode strings: Varchar/nvarchar, Char/nchar (Text/ntext is no longer recommended, use varchar (max)/nvarchar (max) instead)
-binary string (that is, byte stream): varbinary, binary (image is no longer recommended, use varbinary (max) instead)
-Other data types: types with special functions (sql_variant, timestamp, uniqueidentifier, XML), cannot be used for special types of tables (cursor, table)

2. AboutData TypeIssues to be aware of

A. Class two special data types
-Data storage for date and time types and range of available values, related calculations, comparisons, displays (strings converted to specified formats) are more complex and involve a set of datetime functions. See DateTime type Analysis for a thread.
-The string type involves character encoding and collation, and the comparison operation also contains a like match (which may also support regular expression matching in the future), which is very important to note. See Understanding character encoding and SQL Server Chinese processing two posts.

B. If the two strings of different collations are evaluated or compared, they will be based on aCollation Precedence OrderTo determine the collation or comparison method of the result of the calculation.

C. If a calculation or comparison of two values of different types is performed, theData Type PriorityTo make an implicit conversion. The basic rules for data type precedence are as follows:
-Large > Small (> Priority above, below): such as bigint > int > smallint > Tinyint > Bit,varchar (> VarChar), datetime > smalldatetime, wait.
-variable > fixed: such as float > Decimal,varchar > Char,nvarchar > Nchar,varbinary > Binary.
-Priority for various types of classes: datetime > Float > Decimal > Integer > unicode string > ANSI string > Binary.
-Priority and conversion rules for special data types require special consideration, see Books Online.

D. If two values of different sizes are calculated, theprecision, number of decimal digits and lengthRules to produce a new type size.

E.ConstantsData type (can be observed by select Col = constant value into Testdt and then view the data type of the Testdt Table col field)
-If you do not explicitly specify and implicitly convert, NULL is handled by the int type.
-', N ', 1, 0x01, 1.0, 1E0, $ each corresponds to varchar, nvarchar, int, varbinary, decimal, float, money type, and the length is the minimum length required to store the corresponding value.

F. In the field of software development It is well known that "implicit conversion is the source of bugs". Therefore, there are two recommendations:
-When using constants, it is best to use constants of the corresponding type. For example, if Table.col is a varchar type, then a query of where Table.col = 10 will not be able to use the index, and an error will occur when it encounters a value in Col that cannot be converted to a number.
-unless the implicit conversion of the corresponding value is very intuitive, it is preferred to specify explicit explicit conversions with cast ()/convert ().

In the above content, the underlined bold is the title of Books Online. For a detailed analysis, see the 1th chapter of Microsoft SQL Server 2005 Technology Insider: T-SQL programming.


8. Null and three-valued logic

The three-valued logic (3VL, three-valued logic) is definitely a critical level in SQL practice and deserves special attention and retreat meditation. After this level is breached, NULL and NOT NULL in SQL will be indistinguishable.

As to whether SQL should allow Nulls, there is a near-belief debate in the database field. E.f.codd thinks null is necessary, but his friend C.j.date thinks Null can be completely canceled. The end result is that the SQL standard supports NULL.

A theoretical argument and no matter what. In practice, however, it is important to know that Null's three-valued logic can cause a lot of problems.
A. Reasons for not using null:
-Null introduces complex three-valued logic.
-NULL behavior in query conditions, FOREIGN key and CHECK constraints, unique constraints, GROUP by, and ORDER by is inconsistent.
B. Reasons for using null:
-NULL is more natural when it is necessary to represent an unknown, indeterminate value. For example, a current staff turnover time, top staff (boss), and so on.
-The external connection is often introduced as NULL, even if all table fields are defined as NOT NULL.

First, if possible, try to make all fields declared as not NULL. Unless it is a better place to use NULL (from business).

Second, when using NULL, be sure to understand the three-valued logic and the database engine's handling of NULL:
(SQL Server has an option set ANSI_DEFAULTS, which defaults to on, which is consistent with the SQL standard.) The effect set to off is detailed in Books Online. ）

1. Null with other values such as +-*/(including NULL in most functions), the result is null (scalar expression). Null is unknown (asserted) after comparing operations with other values =, >, <, and so on.
Unknown related logical operations:
[Code=sql]
Not Unknown-Unknown
Unknown and/or Unknown-Unknown
Unknown OR True----true
Unknown and TRUE-and Unknown
Unknown OR FALSE--Unknown
Unknown and False--and false
[/code]
The Truth-Table of three-value logic can be checked.

2. In where/on/having and If/case when, only true causes the condition to be set (that is, unknown is treated as false). Like what:
where column = value: Rows in the table that are null in column will never be returned, even if value is null;
Case value is null then XXX when ... end:xxx never executes, even if value is null;
If <Unknown> XXX else YYY end or case when <Unknown> then XXX else YYY end: In both cases, YYY executes.

3. Fields that contain foreign KEY constraints and CHECK constraints allow Nulls (that is, the constraint only fails if the condition is false, unknown is not the case).
4. A field that contains a unique constraint (unique index) allows only one null row, and then inserts or updates the field that is null for the duplicate error.
5. When group BY, all NULL is treated as a group.
6. When ORDER by, all null is queued, but NULL is preceded by a non-null value (such as SQL Server) or later (such as Oracle), the SQL standard is not specified.
7. The aggregation function (Sum/avg/max/min/count) ignores null rows.
8. A variable of declare, which is null before it is assigned.
9. Operators and functions related to NULL handling:
-is null/is NOT NULL: Use these two operators to determine whether a value is null instead of = or <>.
-Isnull/coalesce: Take the first non-null value (note that the data type conversion rules for the two functions are different).
-Nullif (b): equivalent to case when a = B then NULL ELSE a END.

Trackback:http://topic.csdn.net/u/20100826/18/7b81012a-b5c4-48b1-b5d1-40a92f3e0388.html

Go: How to learn SQL (Part III: SQL data types and three-valued logic)