MySQL Tutorial

MySQL Tutorial MySQL Features MySQL Database Introduction MySQL Environmental Setup MySQL Data Types MySQL variable MySQL Advance table Query MySQL database queries MySQL Entity-Relationship Model MySQL Table Query MySQL Operators MySQL logical conditions MySQL Queries MySQL Clauses Clustered vs Non-Clustered Index MySQL Full text index MySQL Descending Index MySQL Invisible Index MySQL Composite Index MySQL Prefix index MySQL Index MySQL Create index MySQL Drop Index MySQL Show index MySQL Unique index MySQL Table MySQL Variable MySQL View MySQL Constraints MySQL Command Line Client Basic Queries MySQL Stored Procedure MySQL IF Statement MySQL Subquery MySQL Triggers

MySQL Join

MySQL Join MySQL CROSS JOIN MySQL DELETE JOIN MySQL EQUI JOIN MySQL INNER JOIN MySQL Union MySQL NATURAL JOIN MySQL RIGHT JOIN MySQL SELF JOIN MySQL UPDATE JOIN

MySQL Function

MySQL Function MySQL AVG() Function MySQL SUM() Function MySQL String() Function MySQL Advance() Function MySQL Aggregate() Function MySQL COALESCE() Function MySQL Control Flow Function MySQL COUNT() Function MySQL Date And Time Function MySQL GREATEST() Function MySQL ISNULL() Function MySQL LEAST() Function MySQL Math() Function MySQL MAX() Function MySQL MIN() Function MySQL find_in_set() function MySQL ASIN() Function MySQL CEIL() function MySQL CEILING() function MySQL TAN() Function MySQL Truncate() Function MySQL FLOOR() function MySQL LN() function MySQL LOG2() function MySQL LOG10() function MySQL MOD() function MySQL PI() function MySQL POW() function MySQL RADIANS() function MySQL RAND() function MySQL ROUND() function MySQL Character Length Function MySQL Current Date Function MySQL Date Add Function MySQL Date Format Function MySQL Datediff Function MySQL Day Function MySQL Elt Function MySQL Export Set Function MySQL Field Function MySQL Format Function MySQL From Base64 Function MySQL Hex Function MySQL Insert Function MySQL Instr Function MySQL Length Function MySQL CONCAT() function MySQL FIND_IN_SET() function MySQL LIKE() function MySQL LOAD_FILE() function MySQL LOCATE() function MySQL LOG() function MySQL MONTHNAME() function MySQL NOW() function MySQL PERIOD_ADD() function MySQL PERIOD_DIFF() function MySQL POWER() function MySQL QUARTER() function MySQL REVERSE() function MySQL RIGHT() Function MySQL RPAD() function MySQL RTRIM() function MySQL SEC_TO_TIME() function MySQL SOUNDEX() function

Questions

Which Clause is Similar to Having Clause in MySQL

Misc

MySQL Error 1046 - No Database Selected Failed to Start MySQL Service Unit MySQL Service Unit not Found Import MySQL Connector Mudule not Found Error No Module Named MySQL Joins Available in MySQL MySQL Docs MySQL Download For Windows 7 64 Bit MySQL Error Code 1064 MySQL Export MySQL History MySQL Host MySQL Import MySQL Drop All Tables MySQL Drop MySQL Error Code 1175 MySQL Events MySQL Except MYSQL Foreign Key Constraint MySQL If Exists MySQL IndexOf MySQL List All Tables json_extract in MySQL

MySQL variable

The MySQL variable is essential for storing data in the table. The variable declares data with a specific name or label to avoid confusion. This data label is used in the execution to get proper value from a table.  You can share table values from one SQL query to another query.

The variable uses only the execution time to work on data. The one variable used in the entire program for query execution and modification table information. MySQL variable works in three different ways. This method declares as per variable use and limitations.

The MySQL variable works on the three methods below. You pass values from one query to another query using the below variable types.

  1. User-defined variable
  2. System variable
  3. Local variable

SYNTAX

The basic syntax of the variable is below. It helps to assign value. The variable works to declare and update the respective value.

@variable_name;

Most variable names come with @ ("at") symbol. This symbol is placed before a variable name.

@variable_name = value;

You assign variables to the table data. If you want to work on the respective data, then use the given variable.

DECLARE variable_name data type (size);

The local variable does not need to use @ symbol before the variable name. You use the "declare" keyword for the local variable.

User-defined variable

The user-defined variable is used to pass table values from the initial query to another query. The initial query stores value in the table. The other MySQL query refers to the value from MySQL initial query.  The user-defined variable does not see from one user to another user.

The user-defined variable stores all kinds of data. The variable assigns integer, float, decimal, string, and null values. This variable declares a name using any sentence case such as an uppercase and a lowercase.

@VARIABLE_NAME and @variable_name have the same meaning because of not case-sensitive properties. The user-defined variable contains 64 characters in any format.

MySQL user-defined variable works with SET and SELECT statements. This statement is used to initialize the value and declares the required value. The user-defined variable comes before @ symbol to assign the variable name.

SYNTAX

The user-defined variable uses the SET statement. This variable helps to initialize the value. The variable uses either the "=" or ":=" symbol to assign value.

If the variable uses the "=" symbol, then syntax is below.

SET @variable_name = value;

If the variable uses the ":=" symbol, then syntax is below.

SET @variable_name := value;

The user-defined variable uses the SELECT statement. This variable helps to declare value. The syntax is below.

SELECT @variable_name;

Examples of the user-defined variable

1) Example: the user-defined variable with a SET statement.

Execute below query to set user define variable.

 mysql> set @mark = 54;
 Query OK, 0 rows affected (0.13 sec) 

OUTPUT

Execute below query to get output.

mysql> select @mark;
MySQL variable

You see the mark variable and its value in the output.

2) Example: the user-defined variable with SELECT statement.

Execute the below query to get the user to define a variable.

mysql> select @mark;

OUTPUT

You see the mark variable and its value in the output.

MySQL variable

The variable declared value in the output. This local variable value is set from the user and declared as an output.

3) Example: the user defined a variable with SELECT statement and value.

mysql> select @mark := 54;

OUTPUT

You see the mark variable and its value in the output.

MySQL variable

If a value is available, then output shows the Boolean value "1." You must initialize the variable with the required value.

4) Example: the user defined a variable with SELECT statement and value.

mysql> select @mark := 54;

OUTPUT

You see the mark variable and its value in the output.

MySQL variable

If the value does not include available, then output shows a null value. If the variable does not initialize, then output displays a null value.

The user-defined variable is the most usable in the MySQL data. This variable does not need to declare any table. It is a temporary variable to execute a program and manage data.

System variable

The system variable is predefined in the MySQL data management system. The system variable stores several variables with a default value. If you want to initialize other values, then use the SET statement.

The system variable contains global, session, and mix types variables. The global variable works the entire lifecycle of the MySQL server. The session variable works for a specific session of the MySQL server.

SYNTAX

Use the below query to get many stored variables in the MySQL system.

SHOW VARIABLES;

If you use the above query, then the MySQL command-line client executes continuously. If you want to show output, then apply any condition.

Show variable LIKE pattern;

The MySQL system variable uses the "LIKE" operator with the required pattern.

Examples of the system variable.

MySQL system variable shows using "WHERE" and "LIKE" with the necessary condition.

1) Example: the system variable with the "LIKE" operator.

You use the "LIKE" operator with a pattern to get a particular variable from the MySQL system. Execute the below query to get the required system variable.

mysql> show variables LIKE '%out';

OUTPUT

MySQL variable

You see the system variable and its value using a pattern.  You see, the system variable includes the "timeout" keyword. You get the default size of the MySQL system variables.

2) Example: the system variable with the "WHERE" operator.

You use the "WHERE" clause with the condition to get a particular variable from the MySQL system. Execute the below query to get the required system variable with a specific value.

mysql> show variables where value = 30;

OUTPUT

MySQL variable

You see the above output of the system variable with value = 30.

3) Example: the system variable displays size.

The double "at" (@) symbol is used to declare the size of the variable. Execute the below query to get the size of the system variable.

mysql> select @@wait_timeout;

OUTPUT

MySQL variable

You see the respective variable and its size in the output image. The "wait_timout" variable has 28800 values.

Local variable

The local variable works in the "store procedure" programs. This variable does not use @ symbol to assign a variable. The local variable stores value with a data type. The local variable declares data and executes operation using stored procedure.

The local variable uses the "DECLARE" keyword to specify the local variable. This variable uses the "DEFAULT" clause. The default clause gives a specific value to a local variable. If the default value is not assigned then, the variable makes the default value null.

SYNTAX  

The "DECLARE" keyword comes before the variable name. The data type is placed after the variable name.  The default value must be assigned for a variable. The local variable with default value syntax shows below.

 DECLARE variable_name data_type(size) DEFAULT default_value;

The local variable with default value syntax shows below.

DECLARE variable_name data_type(size);

The local variable with similar data type syntax shows below.

DECLARE variable1, variable2, variable3 data_type(size) DEFAULT default_value;

Examples of the local variable

1) Example: the local variable with similar data type example shows below.

Execute the below query to know the working procedure of the local variable.

 DELIMITER $$
 USE `tutorial`$$
 CREATE PROCEDURE `local_variable` ()
 BEGIN
         DECLARE vfirst INT DEFAULT 40; 
         DECLARE vsecond INT DEFAULT 30; 
         DECLARE vthird INT;  
         DECLARE vfourth INT; 
         SET vfirst = 30; 
         SET vthird = 40; 
         SET vfourth = vfirst + vthird ; 
         SELECT vfirst, vsecond, vthird, vfourth;
 END$$
 DELIMITER ;
 ; 

OUTPUT

Execute below query to get output.

mysql> call local_variable();
MySQL variable

You can see the above output with variable values. All variable values are set with numbers and operations.

2) Example: the local variable with similar data type example shows below.

Execute the below query to know the working procedure of the local variable.

 DELIMITER $$
 USE `tutorial`$$
 CREATE PROCEDURE `local_variable` ()
 BEGIN
        DECLARE vfirst INT DEFAULT 40; 
         DECLARE vsecond INT; 
         DECLARE vthird INT; 
         DECLARE vfourth INT;
         SET vthird = 40; 
         SET vfourth = vfirst + vsecond; 
         SELECT vfirst, vsecond, vthird, vfourth;
 END$$
 DELIMITER ;
 ; 

OUTPUT

Execute below query to get output.

mysql> call local_variable();
MySQL variable

The first variable declares the default value. The second variable declares the null value. The third variable shows a set value. If you set both values, then the fourth variable operates; otherwise, it becomes null.

3) Example: the local variable with different data type example shows below.

Execute the below query to know the working procedure of the local variable.

 DELIMITER $$
 USE `tutorial`$$
 CREATE PROCEDURE `local_variable` ()
 BEGIN
        DECLARE vfirst INT DEFAULT 40; 
         DECLARE vsecond float default 35.8; 
         DECLARE vthird char default "m"; 
         DECLARE vfourth decimal;
         SET vfirst = 5;
         SET vsecond = 22.6; 
         SET vfourth = vfirst + vsecond; 
         SELECT vfirst, vsecond, vthird, vfourth;
 END$$
 DELIMITER ;
 ; 

OUTPUT

Execute below query to get output.

mysql> call local_variable();
MySQL variable

The first variable declares the default integer value. The second variable declares the default "float" value. The third variable shows the default "char" value. If you set both values, then the fourth variable operates; otherwise, it becomes null.