Detailed explanation of MySql data type tutorial examples

1. Brief Overview

Why should we open the MySQL learning section? Because this is a necessary skill for a data analyst. What is the most important thing in analyzing data? Of course it’s the data, in that case! In an age of explosive data growth, how can we not learn about databases? In fact, this is what many readers want to see, and it is they who suggested that I write it.

It is difficult for those who don’t know how to do it, and it is not difficult for those who know how to do it! In fact, there are many MySQL summary articles on the Internet. It is undeniable that the knowledge points are very comprehensive, but! This is just a query manual for friends who know how to use the MySQL database. Those who don’t know how to use it still won’t know how to use it (there is no detailed introduction). So I started writing this section, hoping to help those friends who want to change careers and learn MySQL. Of course, I would like to add that these sections are not aimed at those who are developers or want to be DBAs, but at those who want to switch to data analysis and need to learn MySQL database.

We have already shown you how to install the MySQL database. I also believe that this installation tutorial I wrote should be the most detailed tutorial on the entire Internet. If you don’t believe me, take a look.

MySQL Installation Tutorial

Of course, if you are worried about the second installation of MySQL (the first installation failed) at this time, then this MySQL uninstallation article should be of great help to you.

MySQL Uninstallation Tutorial

Although we are not DBAs, it is necessary for us to understand some knowledge other than MySQL addition, deletion, modification and query. For example, why is charset=utf8 added to the table creation statement? What kind of process do we go through from entering an SQL statement to the server returning data to us? You can actually read the following article.

Do you really understand the charset=utf8 encoding problem? 》

2. Detailed explanation of MySQL data types

Anyone who has learned a programming language knows how to learn a programming language? First we need to start learning about data types. Although the database was created by others and the table was designed by others, these seem to have nothing to do with you, but mastering such a knowledge point will definitely help you better understand and learn MySQL.

1) String type

① char(m): fixed-length string.

② varchar(m): a character string of variable length.

The above knowledge can be understood by referring to the following figure.

③ Comparison of space utilization of char and varchar storage.

From the table above we can see that:

④ tinytext, text, longtext

Note: As long as the string type is stored, you must pay attention to the encoding problem. Generally, utf8 encoding is used.

2) Integer Type

Detailed explanation of signed and unsigned bits: https://www.jb51.net/article/178768.htm

① Parameter issues when declaring integer data types

② unsigned parameter.

-- Create table create table person(
    pname varchar(20),
    page tinyint unsigned,
    psex bit(1)
) charset=utf8;

-- Insert two records insert into person
(pname,page,psex)
values
("Zhang San",18,0),
("Li Si",22,0);

Observe the following figure:

As can be seen from the above figure:

③ The zerofill parameter must be used in conjunction with the M parameter to be meaningful.

-- An explanation of the student number field:
-- 1: The student number cannot be negative;
-- 2: Student ID numbers usually have the same number of digits. Even if they are different, they will be padded with 0.
-- eg: 00001, 00013, 00128, 01280.
-- Create table create table student(
    sid smallint(5) zerofill not null default 0,
    sname varchar(20),
    sage tinyint unsigned,
   ssex bit(1) default 0
) charset=utf8;

-- Insert two records insert into student(sname,sid)
values ​​("Zhang Fei",5),("Lü Bu",1);

Observe the following figure:

As can be seen from the above figure:

3) Floating point type

For example:

-- swage represents salary; sbonus represents allowance, and allowance cannot be a negative number.
-- Create table create table salary(
    sname varchar(20),
    swage float(6,2),
    sbonus float(5,2) unsigned not null default 0
) charset=utf8;

-- Insert two records insert into salary
(sname,swage,sbonus)
values
("Ji Xiaolan",9999.99,111.11),
("He Shen",-9999.99,444.44);

Observe the following figure:

From the above figure, we can see that:

① Comparison of float/double and decimal precision

-- Create table create table bank(
    id varchar(20),
    acc1 float(9,2),
    acc2 decimal(9,2)
) charset=utf8;

-- Insert two records insert into bank(id,acc1,acc2)
values
(1,1234567.45,1234567.45),
(2,1234567.678,1234567.678);

Observe the following table:

From the table above we can see that:

4) Date/Time Type

① What are date types and time types?

1) Date type: refers to year, month, and day, similar to 2019-11-16 (November 16, 2019)

2) Time type: refers to hours, minutes, and seconds, similar to 10:45:30 (10:45:30)

② Date/time type

The above is the detailed content of the MySql data type tutorial example. For more information about MySql data types, please pay attention to other related articles on 123WORDPRESS.COM!