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

MySQL Learning Notes-data types and operational datasheets

Data type: 1. Character type 2. Integer 3. Floating point type 4. Date and time type

Data table operation: 1. Insert record 2. Find a record

Record operation: 1. Create data Table 2. Use of constraints

1. Data type

[1] Integer:

Data type storage range bytes

TINYINT has symbolic type:-128 ^ 127 (- 2 ^ 7 ~ 2 ^ 7-1), unsigned type 0# 255 (0 ~ 2 ^ 8-1) 1

SMALLINT has symbolic type:-2 ^ 15 ~ 2 ^ 15-1, unsigned type 0 ~ 2 ^ 16-12

MEDIUMINT has symbolic type:-2 ^ 23 ~ 2 ^ 23-1, unsigned type 0 ~ 2 ^ 24-13

INT has symbolic type:-2 ^ 31 ~ 2 ^ 31-1, unsigned type 0 ~ 2 ^ 32-14

BIGINT has symbolic type:-2 ^ 63 ~ 2 ^ 63-1, unsigned type 0 ~ 2 ^ 64-18

[2] floating point

Data type storage range bytes

FLOAT [(MMagar D)]-3.402823466E+38~-1.175494351E-38, 0 and 1.175494351E-38 ~ 3.402823466E+384

DOUBLE [(MMagar D)] 2.2250738585072014Emuri 308 ~ 1.7976931348623157E+3088

[note] M is the total number of digits and D is the number of digits after the decimal point. If M and D are omitted, the value is preserved according to the limits allowed by the hardware. Single-precision floating-point values are accurate to about 7 decimal places. EBay 38 means 10 ^ 38.

[3] date and time type

Column type byte storage range

YEAR1YEAR [(2 | 4)] defaults to 4-bit format, with values ranging from 1901 to 2155.

TIME3-838 VOL59 VOL59

DATE31000-1-1 999-12-31

DATETIME8 date time, 1000-1-100: 00 DATETIME8 0099-12-31 23:59:59

TIMESTAMP4 timestamp, 1970-1-1 0015, sometime in 2000-2037

[note] in actual development, "date-time type" is rarely used, because considering the problem of cross-time zone, it is generally replaced by numeric type.

MySQL itself is Y2K safe, but the input value submitted to MySQL may not be. Any input that contains a 2-digit annual value is ambiguous because the century is unknown. Such a value must be interpreted as a 4-bit form, because 4-bit years are stored internally in MySQL.

For DATETIME, DATE, TIMESTAMP, and YEAR types, MySQL uses the following rules to interpret ambiguous annual values:

-the annual value in the range 00-69 is converted to 2000-2069.

-the annual value in the range 70-99 is converted to 1970-1999.

Remember that these rules only provide reasonable guesses about the meaning of your data. If the heuristic rules used by MySQL do not produce the correct value, you should provide an unambiguous input containing a 4-digit annual value.

[4] character type

Column type storage requirements

CHAR (M) (fixed length, automatic completion of insufficient spaces) M bytes, 0 SHOW TABLES

View the list of data tables of other databases, for example, view mysql database

Mysql > SHOW TABLES FROM mysql

View the structure of the data table

Mysql > SHOW COLUMNS FROM T1

Insertion and search of 3.MySQL record

Record insertion:

INSERT [INTO] tbl_name [(col_name,...0] VALUES (val,...))

Example:

Mysql > INSERT T1 VALUES ('Tom',25,7863.25)

Record lookup:

SELECT expr,... FROM tbl_name

Example:

SELECT * FROM T1

4.MySQL null value and non-null value

* NULL. Field value can be empty.

* NOT NULL. The field value cannot be empty.

Example:

Mysql > USE T2 * MySQL > CREATE TABLE IF NOT EXISTS tb2 (- > username VARCHAR (20) NOT NULL,-> age TINYINT UNSIGNED NULL,-> salary FLOAT (8pm 2) UNSIGNED->)

5.MySQL primary key constraint

* Primary key constraint

* only one primary key can exist per data table

* the primary key ensures the uniqueness of the record

* the primary key is automatically NOT NULL

6.MySQL automatic numbering AUTO_INCREMENT

* automatically numbered and must be used in combination with the primary key

* by default, the starting value is 1 and the increment is 1 at a time

For example, create an automatic numbering table:

Mysql > CREATE TABLE tb3 (- > id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,-> username VARCHAR (30) NOT NULL->); mysql > SHOW COLUMNS FROM tb3 +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | smallint (5) unsigned | NO | PRI | NULL | auto_increment | | username | varchar (30) | NO | | NULL | | + -+

For example, insert records and view tables:

Mysql > INSERT tb3 (username) VALUES ('Tom') mysql > INSERT tb3 (username) VALUES (' John') mysql > INSERT tb3 (username) VALUES ('Rose') mysql > INSERT tb3 (username) VALUES (' Dimitar') mysql > SELECT * FROM tb3;+----+-+ | id | username | +-+ | 1 | Tom | | 2 | John | 3 | Rose | 4 | Dimitar | +-+-- + |

7.MySQL unique constraint UNIQUE KEY

* unique constraint

* unique constraints can guarantee the uniqueness of records.

* unique constrained fields can be null (NULL)

* multiple unique constraints can exist in each data table

Example:

For example, create a data table tb4:

Mysql > CREATE TABLE tb4 (- > id SMALLINT AUTO_INCREMENT PRIMARY KEY,-> username VARCHAR (20) NOT NULL UNIQUE KEY,-> age TINYINT UNSIGNED->); mysql > SHOW COLUMNS FROM tb4 +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | smallint (6) | NO | PRI | NULL | auto_increment | | username | varchar (20) | NO | UNI | NULL | | age | tinyint (3) unsigned | YES | | NULL | | +-+-+

For example, insert a record:

Mysql > INSERT tb4 (username,age) VALUES ('Tom',25); Query OK, 1 row affected (0.00 sec)

For example, insert a record of username and 'Tom' at the same time, which prompts you to repeat:

Mysql > INSERT tb4 (username,age) VALUES ('Tom',23); ERROR 1062 (23000): Duplicate entry' Tom' for key 'username'

8.MySQL default constraint

* * default value

* when inserting a record, if the field is not explicitly assigned a value, the default value is automatically assigned

Example:

For example, create the data table tb6 and set the default value of the sex column to 3:

Mysql > CREATE TABLE tb6 (- > id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,-> username VARCHAR (20) NOT NULL UNIQUE KEY,-> sex ENUM) DEFAULT'3'->; mysql > SHOW COLUMNS FROM tb6 +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | smallint (5) unsigned | NO | PRI | NULL | auto_increment | | username | varchar (20) | NO | UNI | NULL | | sex | enum ('1') 3') | YES | | 3 | | +-+-+

For example, insert a record without assigning a value to sex:

Mysql > INSERT tb6 (username) VALUES ('Tom'); mysql > INSERT tb6 (username) VALUES (' John'); mysql > INSERT tb6 (username) VALUES ('Mimiter')

For example, to view the data table, sex is automatically set to the default value of 3:

Mysql > SELECT * FROM tb6; +-+ | id | username | sex | +-- + | 1 | Tom | 3 | 2 | John | 3 | | 3 | Mimiter | 3 | +-+

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.