Shulou(Shulou.com)06/01 Report--

The concept of data types in? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

First, what is the data?

Data is a collection of information that is stored in a database as a certain data type. The data includes names, numbers, currencies, text, images, decimals, calculations, statistics, etc., covering almost anything imaginable. Data customers save as uppercase, lowercase, or mixed case, the data can be manipulated or modified, and most of the data will not remain unchanged during its lifetime.

The data type is used to specify the rules for the data contained in the column, which determines how the data is stored in the column, including the width assigned to the column, and whether the value can be letter, number, date, time, and so on. Any data or combination of data has corresponding data types, which are used to store things like letters, numbers, dates and times, images, binary data, and so on. In more detail, the data can include names, descriptions, numbers, calculations, images, image descriptions, documents, and so on.

Data is the meaning of the database and must be protected. The protection of data is the database administrator (DBA), but every database user also has the responsibility to take the necessary measures to protect the data.

II. Basic data types

The data type is a characteristic of the data itself, and its properties are set to the fields in the table. For example, we can specify that a field must contain a numeric value, and we are not allowed to enter a string of numbers or letters, nor do we want to enter letters in the field that holds the currency value. Defining a data type for each field in the database can greatly reduce the error data in the database due to errors. A field definition (data type definition) is a data check that notifies the data that can be entered in each field.

1. Fixed-length string.

Fixed-length strings usually have the same length and are saved using fixed-length data types. The following is the standard for Sql fixed-length strings:

CHARACTER (n)

N is a number that defines the maximum number of characters that can be saved in a field.

Some Sql implementations use the CHAR data type to hold fixed-length data. Letters can be saved to this data type.

In fixed-length data types, spaces are usually used to supplement an insufficient number of characters. If the character length is 10 and the input data is only 5 digits, then the remaining 5 digits will be recorded as spaces. Filling in the blanks ensures that each value in the field has the same length.

PS: do not use fixed-length data types to save data of varying length, such as names. Improper use of fixed-length data types can lead to a waste of available space and affect accurate comparisons of different data. Variable-length data types should be used to save variable-length strings, thereby saving database space.

two。 Variable length string.

Sql supports variable-length strings, that is, strings of variable length. Here are the criteria for Sql variable-length strings:

CHARACTER VARYING (n)

N is a number that represents the maximum number of characters that can be saved in the field.

Common variable-length string data types are VARCHAR, VARINARY, and VARCHAR2. VARCHAR is the ANSI standard, and Microsoft Sql Server and MySql also use it; VARINARY and VARCHAR2 are both used by Oracle. Numbers and letters can be stored in fields defined as characters, which means that the data may contain numeric characters. VARBINARY is similar to VARCHAR and VARCHAR2, except that it contains bytes of variable length. This data type is often used to hold digital data, such as image files.

Fixed-length data types use spaces to fill blanks in fields, but variable-length strings do not. For example, if the length of a variable-length field is defined as 10 and the string length entered is 5, then the total length of the value is 5, and spaces are not used to fill in the blanks in the field.

3. Large object type.

Some variable-length data types require longer data than is normally reserved for VARCHAR fields, such as the BLOB and TEXT data types that are common today. These data types are designed to hold large datasets. BLOB is a binary large object, and its data is a very long binary string. BLOB is suitable for storing binary media files such as images and MP3 in a database.

The TEXT data type is a long string type that can be thought of as a large VARCHAR field and is usually used to hold a large character set in a database, such as HTML input from a blog site. Saving this type of data in the database can realize the dynamic update of the site.

4. Numeric type.

Numeric values are stored in fields defined as certain numeric types, such as NUMBER, INTERGER, REAL/DECIMAL, and so on.

Dried shrimp is the standard of Sql value:

(1) .bit (n)

(2). BIT VARYING

(3) .DECIMAL (precom s)

(4) INTEGER

(5) SMALLINT

(6). SIGINT

(7) .FLOAT (ppencil s)

(8). DOUBLE PRECISION (PPJ S)

(9) .real (S)

P represents the maximum length of the field. S represents the number of digits after the decimal point.

A common numerical type in the Sql implementation is NUMERIC, which conforms to the ANSI standard. Values can be 0, positive, fixed-point, and floating-point numbers. Here is an example of using NUMERIC:

NUMERIC (s), which limits the maximum value a field can accept to 99, 999. 0. In all the database implementations involved in the examples in this book, NUMERIC is implemented in the DECIMAL type.

5. Decimal type.

The decimal type is the overall length of the number. For example, in the numerical definition ZDECIMZAL (4Power2), the number of significant digits is 4, which means the total number of digits is 4. The scale is the number of digits after the decimal point, which in the previous example is 2. If the number of decimal places of the actual value exceeds the defined number of digits, the value is rounded, for example, when 34.33 is written to a field defined as DECIMAL (3Power1).

If a numeric value is defined as follows, its maximum value is 99.99:

DECIMAL (4Jing 2)

The significant bit is 4, which indicates that the overall length of the value is 4, and the scale is 2, which means that 2 digits are reserved after the decimal point. The decimal point itself does not count as a character.

The arrays that are allowed to be entered in the fields defined as DECIMAL (4P2) include:

(1) .12

(2). 12.4

(3). 12.44

(4). 12.449

The last value, 12.449, is rounded to 12.45 when saved to the field. In this definition, any value between 12.45 and 12.499 is rounded to 12.45.

6. Round number.

Integers are numeric values that do not contain decimal points (including positive and negative numbers).

Here are some valid integers:

(1) .1

(2) .0

(3)-1

(4) .99

(5)-99

(6) .199

7. Floating point number.

A floating point number is a decimal value in which the significant digits and scales are variable and unlimited. Any significant digits and scales are acceptable. The data type REAL represents a single-precision floating-point value, while DOUBLE PRECISION represents a double-precision floating-point value. The significant digits of a single-precision floating-point value are 1-21 inclusive, and the significant digits of a double-precision floating-point value are 22-53 inclusive. Here are some examples of FLOAT data types:

(1) FLOAT

(2) .FLOAT (15)

(3) .float (50)

8. Date and time type.

Date and time data types are obviously used to save date and time information. Standard Sql supports the DATETIME data type, which includes the following types:

(1) DATE

(2) TIME

(3) DATETIME

(4) TIMESTAMP

Elements of the DATETIME data type include:

(1) YEAR

(2) MONTH

(3) DAY

(4) HOUR

(5) SECOND

The ps:SECOND element can also be broken down into fractions of a second, with a range of 00.000 seconds 61.999, but not all Sql implementations support this range. The extra 1.999 seconds is used to achieve leap seconds.

Each Sql implementation may have a custom data type to hold the date and time. The data types and elements described earlier are standards that every Sql vendor should follow, but most implementations have their own data types to hold date values, which are different from the actual way they are stored.

Date data generally does not specify a length. Later we will describe the date types in more detail, including how date information is saved in some implementations, how to use conversion functions to manipulate dates and times, and use examples to show how dates and times are used in real work.

9. Literal string.

An literal string is a series of characters, such as a name or phone number, that are explicitly specified by the user or program. The literal string contains data that has the same properties as the data type described earlier, but the value of the string is known. The value of the column itself is usually uncertain because each column usually contains different values of the field in all records.

You don't really need to specify the field as an literal string data type, but rather to specify a string. An example of an literal string is as follows:

(1). Hello'

(2). 45000

(3). '45000'

(4) .3.14

(5). 'November 1,1997'

The character string is surrounded by single quotes, the value 45000 is not surrounded by single quotes, and the second 45000 is enclosed in double quotes. In general, character strings require single quotation marks, while numeric strings do not.

Implicit conversion of a process term that converts a data type to a numeric type. During this process, the database automatically determines which data type should be used. So, if a data is not enclosed in single quotation marks, then the Sql program will identify it as a numeric type. Therefore, special attention must be paid to the form of the data. Otherwise, the stored results may deviate or report an error. I'll show you how to use literal strings in database queries later.

10.NULL data type.

A null value indicates that there is no value. NULL values are widely used in Sql, including table creation, query search conditions, and even literal strings.

When using the NULL data type, you need to make it clear that the corresponding field is not required to enter data. If a field must contain data, set it to NOT NULL, and as long as it is possible that the field does not contain data, it is best to set it to NULL.

11. Boolean value.

Boolean values range from TRUE, FlASE, and NULL for data comparison. For example, when you set a condition in a query, each condition is evaluated to get TRUE, FLASE, or NULL. If all conditions in the query have values of TRUE, the data will be returned; if the value of a condition is FLASE or NULL, the data will not be returned. For example, the following example:

WHERE NAME = 'SMITH'

This may be a condition in the query according to which each row of data in the target table is evaluated. If the NAME field value of a row in the table is SMITH, the condition value is TRUE, and the corresponding record is returned.

Most database implementations do not have a strict BOOLEAN type, but instead have different implementation methods. MySql is used for BOOLEAN types, but is essentially the same as its existing TINYINT type. Oracle tends to let users use a CHAR (1) value instead of a Boolean value, while Sql Server uses BIT instead.

twelve。 Custom type.

Custom types are user-defined types, which allow users to customize their own data types according to existing data types to meet the needs of data storage. Custom types greatly enrich the possibility of data storage and make developers have more flexibility in the process of database program development. The statement CREATE TYPE is used to create a custom type.

For example, in MySql and Oracle, you can create a type like this:

CREATE TYPE PERSON ASOBJECT

(NAME VARCHER (30)

SSN VARCHAR (9)

)

You can then reference the custom type as follows:

CREATE TABLE EMP_PAY

(EMPLOYEE PERSON

SALARY DECIMAL (10Phone2)

HIRE_DATE DATE)

The type of the first column EMPLOYEE of the table EMP_PAY is PERSON, which is the custom type you created earlier.

13. Domain.

A domain is a collection of valid data types that can be used. The domain is associated with data, so only specific data is accepted. After the domain is created, we can add constraints to the domain. Constraints work together with data types to further limit the data that fields can accept. The use of fields is similar to custom types.

You can create a domain like this:

CREATE DOMAIN MONEY_D AS NUMBER (0Pol 2)

Add constraints to the domain as follows:

ALTER DOMAIN MONEy_D

ADD CONSTRAINT MONEY_CON

CHECK (VALUE > 5)

Then refer to the domain as follows:

CREATE TABLE EMP_PAY

(EMP_ID NUMBER (9)

EMP_NAME VARCHER2 (30)

PAY_RATE MONEY_D)

III. Summary

Sql has a variety of data types, which are not unfamiliar to people who have used other programming languages. Data types allow different types of data to be saved to the database, such as individual characters, decimals, dates, and times. Whether you are using a third-generation programming language like c or using a relational database to implement Sql coding, the concept of data types is the same. Of course, you have different name pits for data types in different implementations, but they work basically the same way. In addition, a relational database management system does not have to be implemented, and all the data types specified in the ANSI standard will be considered to be compatible with ANSI, so it is best to look at the specific documentation to understand the data types used by ANSI.

When considering data type, length, scale, and precision, you must carefully plan for both the short term and the long term. In addition, the company policy and how you want users to access data are also factors to be considered. Developers should understand the nature of data. And how the data are related to each other in the database to use the appropriate data type.

Thank you for reading! After reading the above, do you have a general understanding of the concept of data types in? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.