The Series of MySQL -1- Basic of MySQL - EP 01_04

The Series of MySQL -1- Basic of MySQL - EP 01_04

HI Hi, Greetings!  

Happy December

Hi Everyone,

Today, I would like to share with you an article about the first episode of Series of MySQL, and the content is given below.

a.    Data Types of MySQL

b.   Variables of MySQL

a.    Data Types of MySQL

A Data Type defines a certain type of data, such as integers, floating points, Booleans, and so on. It also describes the type's available values, actions that can be performed on the type, and how the type's values are stored. Each database table in MySQL has a number of columns, each with its own data type.

The following features can be used to determine the data type in MySQL:

·       The type of values it represents (fixed or variable).

·       It depends on whether the values are fixed-length or variable-length in terms of storage capacity.

·       Its values may or may not be indexed.

·       MySQL's method for comparing values of a specific data type.

MySQL has many SQL standard data types in many different categories. It employs a variety of data types, which can be classified.

                                          i.        Numeric:

MySQL includes all of the necessary SQL numeric data types. These data types can be exact numeric data types (for example, integer, decimal, numeric, etc.) or approximate numeric data types (for example, integer, decimal, numeric, etc). (For example, float, real, and double precision). It also has a BIT data type that can be used to hold bit values. Except for bit data types, numeric data types in MySQL are divided into two categories: signed and unsigned.

The following table lists all of the numeric data types that MySQL supports:

                                         ii.        Date and time:

Date, time, DateTime, timestamp, and year are all examples of temporal values represented by this data type. There are values in each temporal type, including zero. When we insert an invalid value, MySQL is unable to represent it, therefore it defaults to a zero value.

The table below shows all of the date and time data types that MySQL supports:

                                        iii.        String types:

The string data type is used to store plain text and binary data, such as files, pictures, and other types of data. MySQL can search for and compare string values using pattern matching techniques like the LIKE operator and Regular Expressions.

The table below shows all of the string data types that MySQL supports:

                                          iv.        BLOB:

BLOB is a data type in MySQL that can carry a large quantity of data. They are divided into four types based on the maximum length of data that they can carry.

The table below lists all of the Binary Large Object data formats that MySQL supports:

                                          v.        JSON data types:

Since MySQL 5.7.8, the native JSON data type has been supported. This data type enables us to quickly store and retrieve the JSON document.

When compared to storing JSON-format strings in a string column, the JSON data type offers the following benefits:

·       It checks JSON documents for errors automatically. It would generate an error if we stored invalid documents in JSON columns.

·       It has a storage format that is ideal.

b.    Variables of MySQL:

Variables are used to store data or information while a program is running. It is a method of labeling data with a meaningful term that aids the reader's comprehension of the program. The variable's principal purpose is to keep data in memory so that it can be used throughout the program.

Variables can be used in three different ways in MySQL, as seen below:

·       User-Defined Variable:

We occasionally need to transmit values from one statement to another. We can utilize the user-defined variable to store a value in one statement and then refer to it in another. To declare and initialize a variable in MySQL, use the SET and SELECT statements. The @ sign precedes the name of the user-defined variable.

User-defined variables such as @name and @NAME are case-insensitive; both are the same. A user-defined variable that is declared by one person cannot be seen by another. The user-defined variable can be assigned to only a few data types: integer, float, decimal, string, or NULL. The length of the user-defined variable is limited to 64 characters.

·       Local Variable:

It's a variable that isn't preceded by the @ symbol. A strongly typed variable is the local variable. The local variable's scope is defined in the saved program block in which it is declared. The DECLARE keyword is used by MySQL to declare the local variable. A DEFAULT clause is also included in the DECLARE statement to specify a default value for a variable. If you don't include the DEFAULT clause, the default value is NULL. It's most commonly utilized in stored procedure programs.

·       System Variable:

All program units belong to a particular class called system variables, which contains preset variables. MySQL has a number of system variables that control how it works, and each of these variables has a default value. Using the SET statement at runtime, we can alter several system variables dynamically. It allows us to make changes to the server's functionality without having to stop and restart it. In addition, the system variable can be used in expressions.

The MySQL server provides a variety of system variables, including the GLOBAL, SESSION, and MIX types. The GLOBAL variable is visible throughout the server's lifecycle, but the SESSION variable is only active during a single session.  

You are welcome to like and leave feedback in the comment area. 

Link: 

Main:

EP 01_01:

The Series of MySQL -1- Basic of MySQL - EP 01_01

EP 01_02:

EP 01_03:

EP 01_05:

The Series of MySQL -1- Basic of MySQL - EP 01_05

Source:

·        https://www.w3schools.com/mySQl/default.asp

·        https://www.mysqltutorial.org/mysql-basics/

M M Zaheer Hussain

Stay Safe!