Mysql Data Type 1

Mysql Data Type 1. mysql Data Type
Mysql has the following data types:
(1) The numeric value www.2cto.com is a value such as 32 or 153.4. Mysql supports scientific notation, which is expressed by an integer or floating point followed by "e" or "e", a symbol ("+" or "-"), and an integer index. Both 1.24e + 12 and 23.47e-1 are valid scientific notation. 1.24e12 is not legal because the symbols before the index are not given. A floating point number consists of an integer, a decimal point, and a decimal point. The integer and decimal parts can be empty, but cannot be empty at the same time. You can place a negative value "-" before a value to indicate a negative value.
(2) character (string) string (also called string) is like "hello, world !" Or a value like "blood case caused by a steamed bread" or a value like phone number 87398413. You can enclose string values either in single or double quotes. Beginners often cannot tell the difference between a value of 87398143 and a string of 87398143. They are all numbers. How can I use a numeric type or a numeric type? The key lies in that the numeric 87398143 is involved in the calculation, for example, it is a total payment amount in the financial sector, while the numeric 87398143 is not involved in the calculation, only indicates the phone number, street numbers and house number are not involved in calculation. (3) Date and time are values such as "" or "12:30:43. Mysql also supports combination of dates and times, for example, "12:30:43 ".
(4) null indicates an unknown value. For example, if the mailing address in the form is unclear, leave it blank and leave it blank. This is the null value. We use the createtable statement to create a table (see the previous chapter), which contains the column definitions. For example, we have created a joke table, which has two columns: create table (content varchar (20) not null, writer varchar (10) null) syntax for defining a column: col_name col_typy [col_attributes] [general_attributes] www.2cto.com where the column name is given by col_name. A column name can contain a maximum of 64 characters, including letters, numbers, underscores, and dollar signs. A column name can start with any valid symbol (including numbers. However, the column name cannot be composed of digits, because it may be inseparable from the data. Mysql retains words such as select, delete, and create. These words cannot be used as column names, but function names (such as pos and min) can be used.
Col_type indicates the specific values that can be stored in a column. The column type specifier can also represent the maximum length of values stored in the column. For some types, a value can be used to explicitly describe the length. The length of other values is defined by the type name. For example, char (10) explicitly specifies the length of 10 characters, while the maximum implicit length of tinyblob value is 255 characters. Some type specifiers allow you to specify the maximum display width (that is, the number of characters used for displaying the value ). The floating point type allows you to specify the number of decimal places, so you can control the precision of the floating point.
You can specify optional type description attributes and more common attributes after the column type. Attributes are used to modify the type and modify the method of processing column values. attributes have the following types: (1) special attributes are used to specify columns. For example, the unsigned attribute is only for integer type, while the binary Attribute is only for char and varchar type. (2) general attributes can be used for any column except a few columns. You can specify null or not null to indicate whether a column can store null. You can also use default and def_value to indicate that when a new row is created but the value of the column is not explicitly given, the value def_value can be assigned to the column. Def_value must be a constant. It cannot be an expression or reference other columns. You cannot specify the default value for blob or text columns. If you want to give the special attributes of multiple columns, you can specify them in any order as long as they follow the column type and before the general attributes. Similarly, if you want to give multiple common attributes, you can also give them in any order, as long as they are placed after the column type and the column-specific attributes that may be given.
2. Each table in the mysql column (field) type database is composed of one or more columns (fields. When creating a table using the create table statement, you must specify a type for each column (field. The column (field) type is more refined than the data type. It accurately describes the types of values that a given column (field) may contain, such as whether to take decimal places or whether there are many texts. 2.1 value column type mysql has integer and floating point value column types, as shown in table 1. The integer column type can be signed or unsigned. There is a special attribute that allows the automatic generation of integer column values, which is very useful for application systems that require a unique sequence or ID number. Www.2cto.com type description tinyint very small integer smallint smaller integer mediumint medium size integer int standard integer bigint larger integer float single precision Floating Point double precision Floating Point decimal a string floating point table 1: the names and value ranges of each numeric type are shown in table 2. Type description value range: tinyint [(m)] signed value:-128 to 127 (-27 to 27-1) unsigned value: 0 to 255 (0 to 28-1) smallint [(m)] signed value:-32768 to 32767 (-215 to 215-1) unsigned value: 0 to 65535 (0 to 21 6-1) www.2cto.com mediumint [(m)] signed value:-8388608 to 8388607 (-22 3 to 22 3-1) unsigned value: 0 to 16777215 (0 to 22 4-1) int [(m)] signed value:-2147683648 to 2147683647 (-231 to 231-1) unsigned value: 0 to 4294967295 (0 to 232-1)
Bigint [(m)] signed value:-9223372036854775808 to 9223373036854775807 (-263 to 263-1) unsigned value: 0 to 18446744073709551615 (0 to 264-1) float [(m, d)] minimum non-zero value: ± 1. 175494351e-38 double [(m, d)] minimum non-zero value: ± 2. 225074255072014e-308 decimal (m, d) variable; its value range depends on m and d Table 2: The storage required for values of various types of values in the value column type is shown in table 3. Type description storage requirements tinyint [(m)] 1 byte smallint [(m)] 2 byte mediumint [(m)] 3 byte int [(m)] 4-byte bigint [(m)] 8-byte float [(m, d)] 4-byte double [(m, d)] 8-byte decimal (m, d) m bytes (mysql <3.23), m + 2 bytes (mysql> 3.23)
Table 3: Data column storage requirements www.2cto.com mysql provides five types of integers: tinyint, smallint, mediumint, int, and bigint. Int is the abbreviation of integer. These types are different in the value range that can be expressed. An integer column can be defined as unsigned to disable negative values. This enables the value range of the column to be greater than 0. Different types of storage requirements are also different. The storage required for a type with a large value range is large. Mysql provides three floating point types: float, double, and decimal. Different from integer types, the floating point type cannot be unsigned, and its value range is also different from that of integer types. The difference is that these types have the maximum value and the minimum non-zero value. The minimum value provides a metric of the corresponding type of precision, which is very important for recording scientific data (of course, there are also the maximum and minimum values of negative values ).
When you select a value type, you must consider the range of the value to be represented. You only need to select the minimum type that can cover the range of the value. Selecting a large type will result in a waste of space, making the table unnecessary to increase, and it is not as effective as selecting a small type. For integer values, if the data value range is small, such as the age of a person or the number of siblings, tinyint is the most suitable. Mediumint can represent millions of values and can be used for more types of values, but the storage cost is high. Bigint has the largest value range among all integer types, and the required storage space is twice that of the int type of the integer that represents the maximum range. Therefore, it is only used when it is actually needed. For floating point values, double occupies twice the space of float. Unless high precision or a large range of values are particularly required, the data should be represented by a float model that only uses half of the storage cost.
When defining integer columns, you can specify the optional display size m. In this case, m should be an integer ranging from 1 to 255. It indicates the number of characters used to display the column's median. For example, mediumint (4) specifies a mediumint column with a display width of 4 characters. If an integer column with no specific width is defined, it is automatically assigned a default width. The default value is the length of the "longest" value for each type. If the printable expression of a specific value requires more than m characters, the full value is displayed. The value is not truncated to fit m characters. For each floating point type, www.2cto.com can specify the maximum display size m and decimal places d. The m value should be 1 to 255. The value of d can be 0 to 3 0, but should not be greater than m-2 (if you are familiar with odbc terminology, m and d correspond to the "precision" and "number of decimal places" of odbc concepts "). Both m and d are optional for float and double, but are required for decimal. If m and d are omitted, the default value is used.
2.2 string column type mysql provides several string types for storing character data. The types are as follows: type Name Description char fixed-length string varchar variable-length string tinyblob very small blob (Binary Large Object) blob small blobmediumblob medium bloblongblob large blobtinytext very small text string text small text string mediumtext medium text string longtext large text string enum enumeration; column can be assigned to an enumeration member set; columns can be assigned to multiple set Member tables 4: String column types The following table lists the types of mysql-defined string value columns, as well as the maximum size and storage requirements for each type. For variable-length column types, the values of each row occupy different storage volumes, depending on the length of the values actually stored in the column. The length is represented by l in the table. Www.2cto.com type description maximum size storage requirement char (m) m byte varchar (m) m byte l + 1 byte tinyblob, tinytext 28-1 byte l + 1 byte blob, text 216-1 byte l + 2 byte mediumblob, mediumtext 224-1 byte l + 3 byte longblob, longtext 232-1 byte l + 4 byte enum ("value1 ", "value2 ",...) 65535 member 1 or 2 bytes set ("value1", "value2 ",...) 64 members: 1, 2, 3, 4, or 8 bytes
Table 5: the maximum size of the string column type and additional bytes other than storage requirements l are the number of bytes required to store the length of the value. Mysql processes variable-length values by storing the content and length of values. These additional bytes are unsigned integers. Note that the maximum length of a variable length type, the number of additional bytes required for this type, and the relationship between unsigned integers occupying the same number of bytes. For example, the mediumblob value may be 224-1 bytes long and three bytes are required to record its results. The maximum unsigned value of the Three-byte integer mediumint is 224-1. This is not accidental. Www.2cto.com 2.3 datetime column type mysql provides several time value column types: date, datetime, time, timestamp, and year. The following table lists the types that mysql provides to define the storage date and time values, and provides valid value ranges for each type.
Type Name Description date value in the format of "yyyy-mm-dd" time "hh: mm: ss" datetime "yyyy-mm-dd hh: mm: table 6: date and time column type name value range storage requirements date "1000-01-01" to "9999-12-31" 3-byte time "-838: 59: 59" to "838: 59: 59 "3-byte datetime" 1000-01-01 00:00:00 "to" 9999-12-31 23:59:59 "8-byte timestamp 19700101000000 to a time point in 2037 4 bytes year 1901 to 2155 1 byte www.2cto.com table 7: the following example shows the value range and storage requirements of the date column type: create table stu Dent (name varchar (20) not null, chinese tinyint (3), maths tinyint (3), english tinyint (3), birthday date) create a student table, this table contains the name field and character type column. null values are not allowed ). There are three integer columns: chinese, maths, and english. There is also a birthday date type column. Author: hunting-household-seat