Learn Mysql:mysql with Mr. Wang date and time type of data type

Learn Mysql:mysql with Mr. Wang date and time type of data type
Teacher: Wang Shaohua QQ Group No.: 483773664 Learning Content

Characteristics and use of year type

Characteristics and use of time type

Characteristics and use of date type

Characteristics and use of datetime types

Characteristics and use of timestamp type

MySQL represents the date and time data types in the following ways:

1. Year Type

2. Time Type

3. Date type

4. DateTime type

5. Timestamp type

650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M02/83/A5/wKiom1d5vj3gwCZFAADvCneclyI911.jpg "alt = "Wkiom1d5vj3gwczfaadvcneclyi911.jpg"/>

I. Introduction to year type (i)

Year uses a string to represent years

The value of year type is displayed in MySQL in yyyy form

Value range: 1901-2155

(ii) Example 1. Create a table

1 2 3 4 5

createtablehistory(     id intprimary keyauto_increment,     title varchar(10),     y year );

650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M01/83/A4/wKioL1d5vj3R2B6NAAAWuyJS91s509.png "alt = "Wkiol1d5vj3r2b6naaawuyjs91s509.png"/>

2 normal value of inserting language

1	insertintohistory(title,y)values(‘中华人民共和国成立‘,‘1949‘);

650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M01/83/A5/wKiom1d5vj3xWwkZAAAU8wxaZ5E413.png "alt = "Wkiom1d5vj3xwwkzaaau8wxaz5e413.png"/>

3. Insert less than 1901 value

1	insertintohistory(title,y)values(‘史前文明‘,‘1900‘);

650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M00/83/A4/wKioL1d5vj3B1wq7AAAKkPJE58A746.png "alt = "Wkiol1d5vj3b1wq7aaakkpje58a746.png"/>

4. Insert a 2-digit value

1-69 conversion to 2001-2069

70-99 conversion to 1970-1999

0 conversion to 0000

1 2 3

insertintohistory(title,y)values(‘1-69‘,23); insertintohistory(title,y)values(‘70-99‘,85); insertintohistory(title,y)values(‘0‘,0);

650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M02/83/A4/wKioL1d5vj6wQEu8AAAlAyIuz7w604.png "alt = "Wkiol1d5vj6wqeu8aaalayiuz7w604.png"/>

The difference between 5, 0 and ' 0 '

0 means 0000

' 0 ' means 2000

1 2	insertintohistory(title,y)values(‘二个零的区别‘,0); insertintohistory(title,y)values(‘二个零的区别‘,‘0‘);

650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M02/83/A5/wKiom1d5vj7Bp0OtAAAlmMLN-3o612.png "alt = "Wkiom1d5vj7bp0otaaalmmln-3o612.png"/>

Ii. Introduction to date type (i)

Use 4 characters to represent a date

A value that represents a date in YYYY-MM-DD. YYYY represents the year, MM for the month, DD for the day

Representation range for Date type: 1000-01-01 to 9999-12-31

(ii) Example 1, History table add a column D date

1	altertable history addd date;

650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M00/83/A5/wKiom1d5vj6x1TN5AAAc26pdLT8736.png "alt = "Wkiom1d5vj6x1tn5aaac26pdlt8736.png"/>

2. Input format yyyy

can have a variety of:

Yyyy-mm-dd

YYYYMMDD

Yyyy/mm/dd

[Email protected] @DD

YYYY. Mm. Dd

1 2 3 4 5

Insert to history (title,d) values ( ' Yyyy-mm-dd ' , ' 2016-6-22 ' ); Insert into history (title,d) values ( YYYYMMDD ' , ' 20160622 ' ); Insert into history (title,d) values ( Yyyy/mm/dd ' , ' 2016/06/22 ' ); Insert into history (title,d) values ( ' [ Email protected] @DD ' , ' [email protected]@22 ' ]; Insert into history (title,d) values ( YYYY. Mm. DD ' , ' 2016.06.22 ' );

650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M01/83/A4/wKioL1d5vj7ALbeyAABNTvnfPqw251.png "alt = "Wkiol1d5vj7albeyaabntvnfpqw251.png"/>

3. Input Format yy

Rule is the same as year type

1-69 2001-2069

70-991970-1999

1	insertintohistory(title,d)values(‘YY.MM.DD‘,‘15.06.22‘);

650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M01/83/A5/wKiom1d5vj6yaC5QAAAvjfQ4RKQ573.png "alt = "Wkiom1d5vj6yac5qaaavjfq4rkq573.png"/>

4 Get the current date

Current_date

Now ()

1 2	insertintohistory(title,d)values(‘当前日期:‘,current_date); insertintohistory(title,d)values(‘当前日期:‘,now());

650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M00/83/A4/wKioL1d5vj_jDmsUAABUJ-rEwPA743.png "alt = "Wkiol1d5vj_jdmsuaabuj-rewpa743.png"/>

Iii. Introduction to type of time (i)

The time type uses 3 bytes to represent

The value of the time type is displayed in MySQL as HH:MM:SS, where HH indicates that MM is divided and SS represents seconds

Indication Range: -838:59:59~838:59:59

(b) Assignment
1. Add a column t time

1	altertableHistory addt time;

650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M00/83/A5/wKiom1d5vj_RuXPpAAAIi-NAays706.png "alt = "Wkiom1d5vj_ruxppaaaii-naays706.png"/>

2, D HH:MM:SS

D represents the number of days, the value of the hour Equals (D*24+HH), e.g. ' 2 11:30:59 ' 59:30:59

1	insertintohistory(title,t)values(‘D HH:MM:SS‘,‘2 11:30:50‘);

650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M01/83/A4/wKioL1d5vj_gBMonAAAR8_fsw8k458.png "alt = "Wkiol1d5vj_gbmonaaar8_fsw8k458.png"/>

3 HHMMSS

1	insertintohistory(title,t)values(‘HHMMSS‘,‘131313‘);

650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M02/83/A4/wKioL1d5vj-S1Xn1AAAT-ECCBBY182.png "alt = "Wkiol1d5vj-s1xn1aaat-eccbby182.png"/>

4 If the minutes and seconds are greater than 59, an error will be

1	insertintohistory(title,t)values(‘大于59‘,‘126767‘);

650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M02/83/A5/wKiom1d5vj-jAxaJAAAJ7p-iPag589.png "alt = "Wkiom1d5vj-jaxajaaaj7p-ipag589.png"/>

5 0 and ' 0 '-00:00:00

1	insertintohistory(title,t)values(‘0‘,‘0‘);

650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M01/83/A5/wKiom1d5vkCBWctuAAAUichxzF0815.png "alt = "Wkiom1d5vkcbwctuaaauichxzf0815.png"/>

6 getting the current system time

Current_time

Now ()

1 2	insertintohistory(title,t)values(‘获得当前时间‘,current_time); insertintohistory(title,t)values(‘获得当前时间‘,now());

650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M00/83/A4/wKioL1d5vkCSiOeHAAAnGZ5Cm8U332.png "alt = "Wkiol1d5vkcsioehaaangz5cm8u332.png"/>

Iv. Description of the datetime type (i)

A datetime type that can be formally viewed as a combination of the date type and the time type

A datetime type uses 8 bytes to represent the date and time

The value of the datetime type is displayed in MySQL in Yyyy-mm-dd HH:MM:SS form

(ii) Input 1, add column

1	altertable history adddt datetime;

650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M02/83/A4/wKioL1d5vkDBPDCcAAAWSYb1VwQ236.png "alt = "Wkiol1d5vkdbpdccaaawsyb1vwq236.png"/>

2 Yyyy-mm-dd HH:MM:SS

You can also have no separators

How many forms the delimiter can be

1 2	insertintohistory(title,dt)values(‘无分隔符‘,‘20160622200422‘); insertintohistory(title,dt)values(‘@分隔符‘,‘[email protected]@[email protected]+04+22‘);

650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M02/83/A5/wKiom1d5vkCDq08LAAAzmbsrhp8543.png "alt = "Wkiom1d5vkcdq08laaazmbsrhp8543.png"/>

3 Yy-mm-dd HH:MM:SS

00-69-->2000-2069

70--99-->1970-1999

4, 0--->0000-00-00 00:00:00
5 Get current system date and time

1	insertintohistory(title,dt)values(‘日期和时间‘,now());

650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M00/83/A5/wKiom1d5vkDzffnAAAAsdGz5qdE678.png "alt = "Wkiom1d5vkdzffnaaaasdgz5qde678.png"/>

V. Timestamp type (i) Introduction

Timestamp type uses 4 bytes to represent a date and time

Range: 1970-01-01 08:00:01--2038-01-19 11:14:07

Display as input datetime

(ii) Differences between timestamp and datetime
1 Adding a column

1	altertable history addts timestamp;

650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M00/83/A4/wKioL1d5vkHyWOG6AAAdwkgR8go939.png "alt = "Wkiol1d5vkhywog6aaadwkgr8go939.png"/>

2 Get current system date and time

DateTime Now ()

Timestamp Current_timestamp

1	insertintohistory(title,dt,ts)values(‘获得当前日期与时间‘,now(),current_timestamp);

650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M01/83/A4/wKioL1d5vkHDr_nWAAAJx2frrcE194.png "alt = "Wkiol1d5vkhdr_nwaaajx2frrce194.png"/>

3 null-or no-input processing

datetime, enter NULL, store null;

Do not enter, store null

Timestamp, enter NULL to store the current system date and time

Do not enter, store the current system date and time

1 2	insertintohistory(title,dt,ts)values(‘null或无输入‘,null,null); insertintohistory(title)values(‘null或无输入‘);

650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M00/83/A5/wKiom1d5vkGjWkd5AAALusJv720420.png "alt = "Wkiom1d5vkgjwkd5aaalusjv720420.png"/>

4 Timestamp type range is small, no datetime type range is large

Therefore, it is safer to choose a datetime type if you need a larger time range

VI. Instructional Videos

Http://edu.51cto.com/course/course_id-6420.html

This article is from "Learn programming with Mr. Wang" blog, please be sure to keep this source http://teacherwang.blog.51cto.com/10946447/1795483

Learn Mysql:mysql with Mr. Wang date and time type of data type