Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      Variables

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

      Variables

      In general, variables are the containers that store some information in a program. The value of a variable can be changed as many times as required. Each variable has a datatype specifying the type of data we can store in it such as integer, string, float etc.
      • In some programming languages such as Java, C, C++ etc., we need to declare the datatype of a variable before assigning values to it.
      • In languages like python the datatype of a variable is presumed based on the values assigned to it. There is no need of declaring the datatype separately.
      • In MySQL there is no need to declare the datatype we can simply define a variable with a value using the SET statement.

      Variables in MySQL

      The main purpose of a variable is to label a memory location(s) and store data in it so that it can be used throughout the program.
      The characters we use to declare and define a variables are called literals and a literal can be anything other than special characters, numbers and, reserved keywords.
      In MySQL, there are three types of variables. The same is described below
      • User-Defined Variable
      • Local Variable
      • System Variable

      User-Defined Variables

      The User-Defined variable allows us to store a value in one statement and subsequently refer to it in another. To do so, MySQL provides SET and SELECT commands to declare a variable. These variable names will have the symbol "@" as a prefix. We can use either = or := symbols depending on the situation. The user-defined data type can be any of the following: integer, decimal, Boolean, etc.

      Syntax

      Following is the syntax to declare a user-defined variable in MySQL using the SET statement 
      SELECT @variable_name = value

      Example

      In the following query, we are assigning a value to a variable using the SET statement as follows 
      SET @Name = 'Michael';
      Using the SELECT statement, we can display the value of @name variable 
      SELECT @Name;

      Output

      The output for the query above is produced as given below
      @Name
      Michael

      Example

      Here, we are assigning a value to a variable using the SELECT statement 
      SELECT @test := 10;

      Output

      On executing the given query, the output is displayed as follows
      @test := 10
      10

      Example

      Let us create table with the name CUSTOMERS using the following query 
      CREATE TABLE CUSTOMERS(
         ID INT AUTO_INCREMENT PRIMARY KEY,
         NAME VARCHAR(20) NOT NULL,
         AGE INT NOT NULL,
         ADDRESS CHAR (25),
         SALARY DECIMAL (18, 2)
      );
      Now, let us insert values into the above-created table using the INSERT INTO statement 
      INSERT INTO CUSTOMERS (NAME, AGE, ADDRESS, SALARY) VALUES 
      ('Ramesh', 32, 'Ahmedabad', 2000.00),
      ('Khilan', 25, 'Delhi', 1500.00),
      ('Kaushik', 23, 'Kota', 2000.00),
      ('Chaitali', 25, 'Mumbai', 6500.00),
      ('Hardik', 27, 'Bhopal', 8500.00),
      ('Komal', 22, 'Hyderabad', 4500.00),
      ('Muffy', 24, 'Indore', 10000.00);
      The CUSTOMERS table is created as follows
      ID
      NAME
      AGE
      ADDRESS
      SALARY
      1
      Ramesh
      32
      Ahmedabad
      2000.00
      2
      Khilan
      25
      Delhi
      1500.00
      3
      Kaushik
      23
      Kota
      2000.00
      4
      Chaitali
      25
      Mumbai
      6500.00
      5
      Hardik
      27
      Bhopal
      8500.00
      6
      Komal
      22
      Hyderabad
      4500.00
      7
      Muffy
      24
      Indore
      10000.00
      Now, let us declare a variable with the name @max_salary using the SELECT statement to display the maximum salary value from the CUSTOMERS table 
      SELECT @max_salary := MAX(salary) FROM CUSTOMERS;
      Then, we will select records from the table where the salary is equal to @max_salary variable 
      SELECT * FROM CUSTOMERS WHERE SALARY = @max_salary;

      Output

      The output for the query above is produced as given below
      ID
      NAME
      AGE
      ADDRESS
      SALARY
      7
      Muffy
      24
      Indore
      10000.00

      Local Variables

      The MySQL local variable can be declared using the DECLARE keyword. When we declare the local variable, the @ symbol is not used as prefix. This variable is a strongly typed variable, which means that we definitely need to declare a data type.
      The MySQL DEFAULT keyword can be used while declaring a variable to set the default value of the variable. This is an optional parameter, if we do not define this, the initial value will be NULL.

      Syntax

      Following is the syntax to declare a local variable in MySQL 
      DECLARE variable_name1, variabale_name2, ...
      
      data_type [DEFAULT default_value];

      Example

      In the following example, we are using the DECLARE statement in a stored procedure.
      DELIMITER //
      CREATE PROCEDURE salaries()
      BEGIN
         DECLARE Ramesh INT;
         DECLARE Khilan INT DEFAULT 30000;
         DECLARE Kaushik INT;
         DECLARE Chaitali INT;
         DECLARE Total INT;
         SET Ramesh = 20000;
         SET Kaushik = 25000;
         SET Chaitali = 29000;
         SET Total = Ramesh+Khilan+Kaushik+Chaitali;
         SELECT Total,Ramesh,Khilan,Kaushik,Chaitali;
      END //
      Now, let us call the stored procedure using the following query 
      CALL salaries() //;

      Output

      Following is the output
      Total
      Ramesh
      Khilan
      Kaushik
      Chaitali
      104000
      20000
      30000
      25000
      29000

      System Variables

      The system variables are predefined by the MySQL. These contains the data we need, to work with the database. Each MySQL system variable has a default value.
      The SET command in MySQL can be used at the runtime to dynamically change the values of the system variables.
      There are two variable scope modifiers available for the SHOW VARIABLES command. They are GLOBAL and SESSION.
      • The GLOBAL variables are active throughout the lifecycle.
      • The SESSION variables can be available only in the current session.
      Following is the command to display all the system variables in MySQL 
      SHOW [GLOBAL | SESSION] VARIABLES;

      Example

      In the following example, let us display the existing global system variables using the SHOW VARIABLES query 
      SHOW VARIABLES LIKE '%table%';
      
      The variables are displayed in the table format as follows
      Variable_name
      Value
      big_tables
      OFF
      default_table_encryption
      OFF
      innodb_file_per_table
      ON
      innodb_ft_aux_table
      
      innodb_ft_server_stopword_table
      
      innodb_ft_user_stopword_table
      
      innodb_table_locks
      ON
      innodb_temp_tablespaces_dir
      .\#innodb_temp\
      innodb_undo_tablespaces
      2
      innodb_validate_tablespace_paths
      ON
      lower_case_table_names
      1
      max_heap_table_size
      16777216
      old_alter_table
      OFF
      performance_schema_max_table_handles
      -1
      performance_schema_max_table_instances
      -1
      performance_schema_max_table_lock_stat
      -1
      show_create_table_skip_secondary_engine
      OFF
      show_create_table_verbosity
      OFF
      table_definition_cache
      2000
      table_encryption_privilege_check
      OFF
      table_open_cache
      4000
      table_open_cache_instances
      16
      tablespace_definition_cache
      256
      temptable_max_mmap
      1073741824
      temptable_max_ram
      1073741824
      temptable_use_mmap
      ON
      tmp_table_size
      99614720
      updatable_views_with_limit
      YES
      Now, using the query below, we will fetch the current value of the MySQL "key_buffer_size" variable 
      SELECT @@key_buffer_size;

      Output

      Following is the output of the above query
      @@key_buffer_size
      8388608