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

      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.

      SHOW Databases

      MySQL SHOW Databases Statement

      To display the list of all databases present in a MySQL server, we need to use the SHOW DATABASES statement. It returns the result in a tabular form with one column. The databases in the result set will be sorted in alphabetical order.

      Syntax

      Following is the syntax to list all databases in MySQL sever −
      SHOW DATABASES [LIKE 'pattern' | WHERE expr]

      Example

      First of all, let us create a database with a name TUTORIALS using the following query 
      CREATE DATABASE TUTORIALS;
      Make sure you have the admin privilege before creating any database. Once a database is created, you can check it in the list of databases by using the following query 
      SHOW DATABASES;

      Output

      The above query displayed all the databases present in the current MySQL server.
      Database
      information_schema
      mysql
      performance_schema
      tutorials

      MySQL SHOW SCHEMAS Statement

      We can also use the SHOW SCHEMAS statement to list out the databases in MySQL.

      Syntax

      Following is the syntax of the MySQL SHOW SCHEMAS statement 
      SHOW SCHEMAS [LIKE 'pattern' | WHERE expr]

      Example

      Lets try to verify the list of databases once again you can using the following query 
      SHOW SCHEMAS;

      Output

      The output of the above query will be as shown below
      Database
      information_schema
      mysql
      performance_schema
      tutorials

      Showing Databases Using a Client Program

      Besides fetching the list of all databases in current MySQL server with a MySQL query, we can also use a client program to perform the SHOW DATABASES operation.

      Syntax

      Following are the syntaxes of this operation in various programming languages
      PHPNodeJSJavaPython
      To show the list of all databases present in current MySQL through PHP program, we need to execute the SHOW DATABASES statement using the mysqli function query() as follows 
      $sql = "SHOW Database_name";
      $mysqli->query($sql);

      Example

      Following are the programs
      PHPNodeJSJavaPython
      $dbhost = 'localhost';
      $dbuser = 'root';
      $dbpass = 'password';
      $mysqli = new mysqli($dbhost, $dbuser, $dbpass);
      
      if($mysqli->connect_errno ) {
         printf("Connect failed: %s", $mysqli->connect_error);
         exit();
      }
      //printf('Connected successfully.');
      if ($result = $mysqli->query("SHOW DATABASES")) {
         printf("Show Database executed successfully..!");
         echo "Database list are: ";
         while($row = mysqli_fetch_array($result)){
             print_r($row);
         }
      }
      if ($mysqli->errno) {
          printf("Could not find database: %s", $mysqli->error);
      }
      $mysqli->close();

      Output

      The output obtained is as follows 
      Show Database executed successfully..!Database list are:
      Array
      (
          [0] => bank
          [Database] => bank
      )
      Array
      (
          [0] => company
          [Database] => company
      )
      Array
      (
          [0] => information_schema
          [Database] => information_schema
      )
      Array
      (
          [0] => mydb
          [Database] => mydb
      )
      Array
      (
          [0] => mysql
          [Database] => mysql
      )
      Array
      (
          [0] => performance_schema
          [Database] => performance_schema
      )
      Array
      (
          [0] => sys
          [Database] => sys
      )
      Array
      (
          [0] => testdb
          [Database] => testdb
      )
      Array
      (
          [0] => tutorials
          [Database] => tutorials
      )
      Array
      (
          [0] => usersdb
          [Database] => usersdb
      )