[Daily update-mysql Basics] 6. Common data types-date and time

1. Date and time

Date and time types are also very important in the database, such as we have a new user in the database, we should store the user join time, convenient for future queries.

There are, for example, when we record the log, who operates where, we also need to have time to participate in order to know, who at what time the operation of what.

1.1 Date and time

Expression: Datetime/date/year/time/timestamp

1.2 Datetime/date/year/time

Date is the day of the month.

DateTime holds the day of the month and seconds

Year: Years saved

Time: Seconds to save

You can see that datetime is more space-intensive, and it occupies 8 bytes.

1.3 Timestamp time is wrong

Occupies 4 bytes.

Range in: 1970-2038.

1.4. The usual way to save time:

In a real project, the preservation of time we usually use an integer type to hold a number to represent the timestamp, which is a number in seconds starting January 1, 1970 0:0:0.

2. Example of creating a data table:

Create a user table, you need to have: User name, nickname, mobile phone number, mailbox, age, registration time and other fields.

Analysis:

The user name, nickname, mobile phone number, and mailbox should all be string types. The difference is that the mobile phone number in China is 11 bits, so the mobile number can be used char type.

Age should be a number type, and age should be less than 200. So use the tinyint type to be satisfied.

The registration time is used to represent a time that can be used in a timestamp manner.

CREATE table if not EXISTS ' user ' (

' id ' int (one) unsigned auto_increment,

' username ' varchar (15),

' Nickname ' varchar (10),

' Phone ' char (11),

' Age ' tinyint (3),

' Create_time ' int (one) unsigned,

Primary KEY (' ID ')

) Engine=innodb default Charset=utf8;

3. Practice

Create a timesheet for a company employee, and you need to create more than one table.

1. Employee table

2. Employee Punch-in record form

4. Appendix (type Summary)

Type	Range	Description	DEMO
tinyint	-128-127 Unsigned 0-255	A 1-byte integer	column_name tinyint (3)
smallint	-32768-32767 Unsigned 0-65535	A 2-byte integer	column_name smallint (5)
Mediumint	-8388608-8388607 Unsigned 0-16777215	A 3-byte integer	Colum_name Mediumint (7)
Int	-2^31 ~ 2^31-1 Unsigned 0~2^32	A 4-byte integer	column_name Int (10)
bigint	-2^63 ~ 2^63-1 Unsigned 0 ~ 2^64	A 8-byte integer	Col_name bigint (16)
Float (m,d)	-3.4E+38 ~ 3.4E+38 (approx)	4 bytes of decimals
Double (m,d)	-1.79E+308 ~ 1.79E+308 (approx)	8 bytes of decimals
Decimal (M,D)		Accurate fractional Storage
char (N)	N = 1 ~ 255	Fixed length
varchar (N)	N = 1 ~ 65535	Variable length (requires 1-2 bytes separately to store the true length)
Tinytext	Maximum of 255 characters
Text	Maximum of 65,535 characters
Mediumtext	Maximum of 16,777,215 characters
Longtext	Max 4284967295 and characters
Date	Date (YYYY-MM-DD)
Time	Time (HH:MM:SS)
Datetime	Date Time (Yyyy-mm-dd hh:mm:ss)
Timestamp	Yymmddhhmmss
Year	Year (yyyy)

[Daily update-mysql Basics] 6. Common data types-date and time