Learn Mysql:mysql with Mr. Wang character type of data type
Teacher: Wang Shaohua QQ Group: 483773664 Study Content:
Kinds of string types and their characteristics
The similarities and differences between char and varchar
The string type is the data type that stores the string in the database.
String types include
CHAR,
VARCHAR
Blob
TEXT
One, char and varchar
(a) define the syntax
Note:
String type: char or varchar
M: Specifies the maximum length of the string
(b) Differences between the two
The char type length is fixed, which is specified when the table is created, and the length can be 0--255
The varchar type length is variable, specifying the maximum length when the table is created, and the value can be 0-65535.
CHAR (5): The field value occupies a space of 5 characters, if the value does not have 5 bytes, it also assigns 5 characters, if the value exceeds 5 characters, will be an error
varchar (5): The space occupied by the field value is determined by the size of the value, if the value is 3 characters, then 3 characters are allocated, if the value is 30 characters, then the system assigns 30 characters to it, even if how much is allocated
(c) Example 1 creating a table
Create student Table (STU), there are two fields, name is char type, nickname (waihao) varchar
1 2 3 4 |
create table stu(
name char (8) not null default ‘‘ ,
waihao varchar (10) not null default ‘‘
);
|
650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M00/83/A4/wKioL1d5vUOhshW1AAAPu3uqqn4576.png "alt = "Wkiol1d5vuohshw1aaapu3uqqn4576.png"/>
650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M02/83/A5/wKiom1d5vUOjimxAAAAQ5bRh0Pw741.png "alt = "Wkiom1d5vuojimxaaaaq5brh0pw741.png"/>
2 Verify that more than Char will error
1 |
insert into stu(name,waihao)values( ‘zhangxiaosan‘ , ‘san‘ );
|
650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M00/83/A5/wKiom1d5vUPSwaSQAAAKwfgw2YM034.png "alt = "Wkiom1d5vupswasqaaakwfgw2ym034.png"/>
Change it, Zhangxiaosan--->zhangsan
650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M00/83/A4/wKioL1d5vUPDLCofAAAI14HO4Tw707.png "alt = "Wkiol1d5vupdlcofaaai14ho4tw707.png"/>
3 Verify that M is the number of characters
1 |
insert into stu( name ,waihao) values ( ‘张小三张小三‘ , ‘san‘ );
|
650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M00/83/A5/wKiom1d5vUPSk2XAAAAIBT6QBoQ536.png "alt = "Wkiom1d5vupsk2xaaaaibt6qboq536.png"/>
4. Char removes the space at the end of the string when the character is stored, and varchar does not remove the trailing space
1 |
insert into stu( name ,waihao) values ( ‘lisi ‘ , ‘lisi ‘ );
|
1 |
select concat( name , ‘*‘ ),concat(waihao, ‘*‘ ) from stu;
|
650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M01/83/A4/wKioL1d5vUTAuxEPAAAbDlhDR9E183.png "alt = "Wkiol1d5vutauxepaaabdlhdr9e183.png"/>
5, when storing characters, char, varchar will not remove the string of the front space
1 |
insert into stu( name ,waihao) values ( ‘ wangwu‘ , ‘ wangwu‘ );
|
650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M01/83/A4/wKioL1d5vUSwBic8AAAcAEhHKXk604.png "alt = "Wkiol1d5vuswbic8aaacaehhkxk604.png"/>
(iv) The principle of selection of varchar and char
The biggest difference between varchar and char two character data types is that the former is variable length, while the latter is fixed length.
Because char is fixed-length, its query speed is faster than varchar.
An example is given to illustrate the reason for the choice of the two.
Weibo user name: the length of the user name is not more than 10 words, and the user is very large, so we can use char (10), with space to change the time
Micro-Blog content: 140 words, with char is too wasted space, we can use varchar (140)
Ii. type of text
The text type is a special type of string,
Text can only character data, such as news content.
Text types include Tinytext, text, Mediumtext, Longtext
650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M01/83/A5/wKiom1d5vUTyAE5TAAAEwtxCqkQ705.png "alt = "Wkiom1d5vutyae5taaaewtxcqkq705.png"/>
650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M02/83/A4/wKioL1d5vUTiUNMmAAAItyF3FaI386.png "alt = "Wkiol1d5vutiunmmaaaityf3fai386.png"/>
650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M02/83/A5/wKiom1d5vUSxyuMZAAAI_EDCv9s434.png "alt = "Wkiom1d5vusxyumzaaai_edcv9s434.png"/>
650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M00/83/A4/wKioL1d5vUSD9TK8AAAMEXL7V9k795.png "alt = "Wkiol1d5vusd9tk8aaamexl7v9k795.png"/>
650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M00/83/A5/wKiom1d5vUSzL65lAAAMhJ2DCO8798.png "alt = "Wkiom1d5vuszl65laaamhj2dco8798.png"/>
As can be seen from the above table, these types of text types, the main difference is the different storage space. So choose to follow the implementation requirements.
Three, BLOB
BLOBs can be used to hold large data volumes of binary data, compared to tablets, etc.
Blob types include Tinyblob, blobs, Mediumblob, and Longblob. The difference is the maximum length of storage is different
Note:
Typically, files such as pictures are generally stored in the file system, and the paths to those files are stored in the database. This way of storing is simpler than storing directly in the database, but the access speed is slower than storing it in the database
Iv. Summary
650) this.width=650; "border=" 0 "src=" http://s3.51cto.com/wyfs02/M01/83/A5/wKiom1d5vUWhsojZAAB2e56hts4398.png "alt = "Wkiom1d5vuwhsojzaab2e56hts4398.png"/>
V. Teaching VIDEO
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/1795478
Learn Mysql:mysql with Mr. Wang character type of data type