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
      Database Info

      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.

      Database Info

      MySQL usually stores two types of data in it: actual data stored in the form of tables and views, and information about the structure of a database and its objects. Such information is known as Metadata.
      For instance, whenever a user forgets certain information of a database or its objects, MySQL provides specific commands to retrieve the said information. There are actually three types of information, which you can retrieve from a MySQL database. They are as follows −
      • Information about the result of queries − This includes the number of records affected by any SELECT, UPDATE or DELETE statement.
      • Information about the tables and databases − This includes information related to the structure of the tables and the databases.
      • Information about the MySQL server − This includes the status of the database server, version number, etc.
      It is very easy to get all this information at the MySQL prompt, but while using PERL or PHP APIs, we need to call various APIs explicitly to obtain all this information.

      Obtaining Database Info from MySQL Prompt

      While accessing a MySQL server from MySQL prompt, which is a Command Prompt in Windows and a Terminal in Linux etc., any information regarding a database using following commands.
      • SHOW DATABASES: This command is used to retrieve the list of all databases present in MySQL.
      • SHOW TABLES: This command is used to display the list of tables present in a database.
      • mysql -V: This command is used to provide the current version of MySQL installed in your system.
      • DESC or DESCRIBE: This command is used to retrieve the structure or definition of a database table.

      mysql -V Command

      If you want to check the version of MySQL Server installed in your system, use the following mysql -V on Command Prompt or Terminal.
      Note: You must remember that the command prompt must be run as an administrator in Windows.

      Example

      In here, we are checking the current version of MySQL server in command prompt 
      C:\Windows\System32> mysql -V

      Output

      As we can see the output below, the current MySQL server version is '8.0.33' 
      mysql  Ver 8.0.33 for Win64 on x86_64 (MySQL Community Server - GPL)

      SHOW DATABASES Command

      To list or retrieve the names of all the databases in MySQL, you can use the following SHOW DATABASES command after logging into MySQL server
      Note − This command will list both system databases and user-defined databases together. The user must identify their specific user-defined database all the data is stored in.

      Example

      In the following query, we are fetching list of databases that exists in the current MySQL server 
      SHOW DATABASES;
      Here, the first three rows are system databases and the last two rows are user-defined databases
      Database
      information_schema
      mysql
      performance_schema
      tutorials
      tutorials_copy

      SHOW TABLES Command

      To list all the tables in a MySQL database, you can use the SHOW TABLES command after selecting a database with USE command

      Example

      In the below query, we are selecting a database named 'Tutorials' 
      USE Tutorials;
      Now, let us use the SHOW TABLES to fetch all the names of tables present in the above database 
      Show Tables;

      Output

      Following is the list of tables exist in the 'Tutorials' database
      Tables_in_tutorials
      customers
      employees
      students

      DESC Command

      If we want to check the structure of a MySQL table, we need to use the DESC or DESCRIBE query. DESC is a shortcut for DESCRIBE query, but retrieves the same result.

      Example

      Here, we are fetching the structure of MySQL CUSTOMERS table 
      DESC CUSTOMERS;
      Following is the structure
      Field
      Type
      Null
      Key
      Default
      Extra
      ID
      int
      NO
      PRI
      NULL
      auto_increment
      NAME
      varchar(20)
      NO
      
      NULL
      
      AGE
      int
      NO
      
      NULL
      
      ADDRESS
      char(25)
      YES
      
      NULL
      
      SALARY
      decimal(18,2)
      YES
      
      NULL
      

      Obtaining the Number of Rows Affected by a Query

      Let is now see how to obtain this information.

      PERL Example

      In DBI scripts, the affected row count is returned by the do( ) or by the execute( ) command, depending on how you execute the query.
      # Method 1
      # execute $query using do( )
      my $count = $dbh->do ($query);
      # report 0 rows if an error occurred
      printf "%d rows were affected\n", (defined ($count) ? $count : 0);
      
      # Method 2
      # execute query using prepare( ) plus execute( )
      my $sth = $dbh->prepare ($query);
      my $count = $sth->execute ( );
      printf "%d rows were affected\n", (defined ($count) ? $count : 0);

      PHP Example

      In PHP, invoke the mysql_affected_rows( ) function to find out how many rows a query changed.
      $result_id = mysql_query ($query, $conn_id);
      # report 0 rows if the query failed
      $count = ($result_id ? mysql_affected_rows ($conn_id) : 0);
      print ("$count rows were affected\n");

      Listing Tables and Databases

      It is very easy to list down all the databases and the tables available with a database server. Your result may be null if you don't have the sufficient privileges.
      Apart from the method which is shown in the following code block, you can use SHOW TABLES or SHOW DATABASES queries to get the list of tables or databases either in PHP or in PERL.

      PERL Example

      # Get all the tables available in current database.
      my @tables = $dbh->tables ( );
      
      foreach $table (@tables ){
         print "Table Name $table\n";
      }

      PHP Example

      Try the following example to get database info
      Copy and paste the following example as mysql_example.php 
      <html>
         <head>
            <title>Getting MySQL Database Info</title>
         </head>
         <body>
            <?php
               $dbhost = 'localhost';
               $dbuser = 'root';
               $dbpass = 'root@123';
               $dbname = 'TUTORIALS';
               $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
               $tutorial_count = null;
      		 
               if($mysqli->connect_errno ) {
                  printf("Connect failed: %s<br />", $mysqli->connect_error);
                  exit();
               }
               printf('Connected successfully.<br />');
      		 
               if ($result = mysqli_query($mysqli, "SELECT DATABASE()")) {
                  $row = mysqli_fetch_row($result);
                  printf("Default database is %s<br />", $row[0]);
                  mysqli_free_result($result);
               }
               $mysqli->close();
            ?>
         </body>
      </html>

      Output

      Access the mysql_example.php deployed on apache web server and verify the output.
      Connected successfully.
      Default database is tutorials

      Getting Server Metadata

      There are a few important commands in MySQL which can be executed either at the MySQL prompt or by using any script like PHP to get various important information about the database server.
      Sr.No.
      Command & Description
      1
      SELECT VERSION( )
      Server version string
      2
      SELECT DATABASE( )
      Current database name (empty if none)
      3
      SELECT USER( )
      Current username
      4
      SHOW STATUS
      Server status indicators
      5
      SHOW VARIABLES
      Server configuration variables