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

Mysql basic data type

Overview of common data types in mysql

! [1036857-20170801181433755-146301178] (D:\ notes\ mysql\ review\ 1036857-20170801181433755-146301178.png) 1. Number: integer: tinyinit int bigint decimal: float: inaccurate in the case of short digits double: inaccurate in the case of long digits 0.000001230123123123 saved: 0.000001230000 decimal: (if using decimals, it is recommended to use decimal) the precise internal principle is to save 2 in the form of a string. String: char (10): simple and rude, waste of space, fast access speed root as root000000 varchar: precision, space saving, slow access speed sql optimization: when creating a table, the type of fixed length is put forward, the longer one is put back, such as gender such as address or description information > 255characters, and then the file path is stored in the database. For example, pictures, videos, etc., find a file server, and only the path or url is stored in the database. 3. Time type: most commonly used: datetime 4. Enumerated types and collection types

Numerical type

[external link image transfer failed. The origin server may have hotlink protection mechanism. It is recommended to save the image and upload it directly (img-HAIxWM9Z-1585063057990) (D:\ notes\ mysql\ review\ 1036857-20170801181433755-146301178.png)]

1. Integer type

Integer type: TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT function: storage age, grade, id, various numbers and other integer type tests default is signed mysql > create table T1 (money tinyint); # Note: when creating a table, the last field cannot be followed by a comma, misspelled mysql > create table T1 (money tinyint,); mysql > insert into T1 values (11); Query OK, 1 row affected (0.28 sec) mysql > select * from T1 MySQL > insert into T1 values (- 10) Query OK, 1 row affected (0.11 sec) mysql > select * from T1 row affected MySQL > insert into T1 values; Query OK, 1 row affected, 1 warning (0.10 sec) mysql > select * from T1. If the data type is set, there will be a constraint effect. It can only represent the number within its own range to create unsigned numeric types: mysql > create table T2 (id tinyint unsigned)

2. Floating point type

Floating point type: float double function: store salary, height, temperature, weight, physique parameters and other tests mysql > create table T3 (id float (60 sec 30)); # 60 represents decimal + integer total Query OK, 0 rows affected (1.70 sec) # 30 means decimal place up to 30 mysql > create table T4 (id double (60Magazine 30)); Query OK, 0 rows affected (0.88 sec) mysql > create table T5 (id decimal (60 sec 30)) The reason why # decimal can store exact values is that it is stored internally as a string. Query OK, 0 rows affected (0.96 sec) mysql > insert into T3 values (1.111111111111111111); Query OK, 1 row affected (0.13 sec) mysql > insert into T4 values (1.1111111111111111); Query OK, 1 row affected (0.22 sec) mysql > insert into t5 values (1.111111111111111111); Query OK, 1 row affected (0.09 sec) mysql > select * from t3boot MySQL > select * from t4boot MySQL > select * from t5t1. Create the table create table T2 (F1 float (5 double 2), f2 float,f3 double (5 float,f3 double 2), f4 double); float (5L2) retains two decimal places and rounds double (5L2) 2. Write data insert into T2 values (5.2336, 5.2336, 5.336, 5.2336); 3. View the data in the table select * from t2bot 4. Specify the write data insert into T2 (f2Powerf4) values (5.1783682169875975); insert into table name (field 1, field 3) values (value 1, value 3); 1. Create the T3 table create table T3 (F1 float,d1 double,d2 decimal (30 decimal 20), d3 decimal); 2. View the table structure desc T3 position 3. Write data insert into T3 values (5.1783682169875975179, 5.1783682169875975179, 5.1783682169875975179, 5.1783682169875975179); int- has no constraint on length, up to 10 digits float (m) m-how many digits in total n-how many decimal places

Date Typ

Type: DATE,TIME,DATETIME, IMESTAMP,YEAR function: store user registration time, post release time, employee entry time, birth time, expiration time, etc. Mysql > create table T6 (D1 year, D2 date,d3 datetime); Query OK, 0 rows affected (1.75 sec) mysql > insert into T6 values (now (), now (), now ()); Query OK, 1 row affected, 1 warning (0.12 sec) mysql > select * from T6

String type

Char and varchar performance comparison: compared with char (5) and varchar (5), add me to save three names: sb,ssb1,ssbb2 char: advantages: simple and rough, no matter how long data you are, I will store according to the prescribed length, five five, three names will be similar to this kind of storage: sb ssb1 ssbb2, space completion in the middle, five five when fetching data Simple, rough, fast disadvantage: seems to waste space, and the length of the data we store in the future may be uneven varchar: varchar type variable length storage data, more concise and save space, for example, when saving the above three names is similar to this: sbssb1ssbb2, connected, if stored in this way, how do you still get these three names? Do you know how long it takes to take out the first one? When varchar stores data, it will add a header in front of each data. This header is the data of 1-2 bytes. This data refers to the length of the data that follows. 1bytes can represent the length of the data that follows. 1bytes can represent the number of 65536, and two bytes can represent the number of 0-65535. So when varchar stores, it is like this: 1bytes+sb+1bytes+ssb1+1bytes+ssbb2, so it will be troublesome to save, resulting in slower efficiency than char. It is also slow to take it, take the length first, and then take the data. Advantages: save some hard disk space, an acsii character can be represented by a bytes length, but not necessarily less than char, take a look at the official website to give a table to compare the data, when you store the data is exactly the length of your field, varchar instead takes up more space than char. Value CHAR (4) Storage Required VARCHAR (4) Storage Required'' 4 bytes''1 byte'ab' 'ab' 4 bytes'ab' 3 bytes'abcd' 'abcd' 4 bytes'abcd' 5 bytes'abcdefgh'' abcd' 4 bytes'abcd' 5 bytes disadvantage: slow access speed for InnoDB data tables The internal row storage format does not distinguish between fixed-length and variable-length columns (all data rows use header pointers to data column values), so in essence, using fixed-length CHAR columns is not necessarily better than using variable-length VARCHAR columns. Therefore, the main performance factor is the total amount of storage used by data rows. Since CHAR takes up more space than VARCHAR on average, it is better to use VARCHAR to minimize the total storage of rows of data and disk Ibino that need to be processed. Suitable for char: ID number, mobile phone number, QQ number, username, password, bank card number suitable for varchar: comments, moments, Weibo 1. Create table create table T6 (C1 char (1), v1 varchar (1), c2 char (8), v2 varchar (8)); 2. Write data insert into T6 values ('alexq','alexq','alexq','alexq'); 3. Query the data in the table select * from T6

Enumerations and collection types

Enum: radio selection behavior-enumerated types allow only a single value to be selected from a set of values, not more than one value at a time. Create table create table T8 (id int, name char (18), gender enum ('male','female')); 2. Write data insert into T8 values;-unknown unable to write 3. Query the data in the table select * from t8 T8. Write data insert into T8 values;-male can be written to 5. Query the data in the table select * from t8 position 2. Write data insert into T8 values;-female can write 3. Query table data select * from t8transferset: the multi-selection behavior can * * select any one or more elements in the set of allowed values to combine * *. Injection is not allowed for out-of-range content, and duplicate values are automatically deduplicated. 1. Create table create table T9 (id int,name char (18), hobby set ('smoking', 'drinking', 'washing feet', 'massage', 'perm'); 2. Write data insert into T9 values (1) 'too white', 'perm, smoking, drinking, massage'); 3. Query the data in the table select * from t8 T8. Write data insert into T9 values (1)'Da Zhuang', 'foot washing, massage, playing games'); 5. Query the data in the table select * from T8

Summary

This is the end of this article on the basic data types of mysql8.0.19-. For more information about the basic data types of mysql8.0.19-, please search for previous articles or continue to browse the relevant articles below. I hope you will support it in the future!

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.