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 Users

      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 Users

      As you might have already known, MySQL is a multi-user database that allows multiple users to work on it simultaneously. But have you ever wondered who these users might be?
      MySQL provides an account to each user that is authenticated with a username and a password. And details of these accounts are stored in the "user" table in the database. This table contains details like username, the host this user is connected from, and other privileges the said user has etc.

      The MySQL SHOW Users

      MySQL does not provide any direct command to show (list out) all the users. However, the details of these user accounts is stored in the "user" table within the database. Hence, we can use the SELECT statement to list out the contents of this table.
      There is no limit for how many users can connect to a MySQL database but the default user is always "root". And it does not have any password, unless it is set manually.

      Syntax

      Following is the syntax to show users in a MySQL database 
      SELECT * FROM mysql.user;

      Example

      To see the structure of this "user" table, use the following query with the DESC command 
      DESC mysql.user;
      Now, in this example, we are listing out all the users in the MySQL database local to a system 
      SELECT Host, User, User_attributes, account_locked 
      FROM mysql.user;

      Output

      The output obtained is as shown below
      Host
      User
      User_attr
      account_locked
      localhost
      mysql.infoschema
      NULL
      Y
      localhost
      mysql.session
      NULL
      Y
      localhost
      mysql.sys
      NULL
      Y
      localhost
      root
      NULL
      N
      The actual user table contains a lot more columns/fields than what is displayed in this chapter. Here, however, only some information is displayed for simplicity.
      Note that list of these users are local to a system. Hence, not all systems would give the same output (apart from the default users).

      Show Current User

      Not only the list of all users, MySQL also has a provision to see the current user. This is done with the help of user() or current_user() functions.

      Syntax

      Following is the syntax to show the current user 
      SELECT user();
      or
      SELECT current_user();

      Example

      Using the following query, let us display the username of the currently logged in user in MySQL database using the user() function 
      SELECT user();

      Output

      Following is the output obtained
      user()
      root@localhost

      Example

      In here, we are using the current_user() function to show the current user 
      SELECT current_user();

      Output

      The output obtained is as follows
      current_user()
      root@localhost

      Show Currently Logged in Users

      The difference between current users and currently logged in users is that, current user is the user that is executing the queries; whereas, currently logged in user list includes all the active users that are connected to the MySQL server at the moment.
      This information can be extracted from the "information_schema.processlist" table using the SELECT statement.

      Example

      In the following query, we are retrieving the information of all the currently logged in users 
      DESC information_schema.processlist;

      Output

      Following is the output of the above code
      Field
      Type
      Null
      Key
      Default
      Extra
      ID
      bigint unsigned
      NO
      
      
      
      USER
      varchar(32)
      NO
      
      
      
      HOST
      varchar(261)
      NO
      
      
      
      DB
      varchar(64)
      YES
      
      
      
      COMMAND
      varchar(16)
      NO
      
      
      
      TIME
      int
      NO
      
      
      
      STATE
      varchar(64)
      YES
      
      
      
      INFO
      varchar(65535)
      YES
      
      
      

      Example

      In here, we are retrieving information of current users, host, database, and command from the information_schema 
      SELECT user, host, db, command 
      FROM information_schema.processlist;

      Output

      After executing the above code, we get the following output
      user
      host
      db
      command
      root
      localhost:49958
      customers
      Query
      event_scheduler
      localhost
      NULL
      Daemon

      Show Users Using a Client Program

      We can also display information about the MySQL users using a client program.

      Syntax

      Following are the syntaxes to display information regarding MySQL users in various programming languages
      PHPNodeJSJavaPython
      To display info regarding user(s) in a MySQL database using a PHP program, we need to execute the SELECT USER statement using the query() function of the PHP mysqli library as 
      $sql = "SELECT USER FROM MYSQL.user";
      $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.');
      $sql = "SELECT USER FROM MYSQL.user";
      if($result = $mysqli->query($sql)){
         printf("User found successfully...!");
         printf("Users list are: ");
         while($row = mysqli_fetch_array($result)){
             print_r($row);
         }
      }
      if($mysqli->error){
         printf("Failed..!" , $mysqli->error);
      }
      $mysqli->close();

      Output

      The output obtained is as follows 
      User found successfully...!Users list are: Array
      (
          [0] => Vivek Verma
          [USER] => Vivek Verma
      )
      Array
      (
          [0] => Revathi
          [USER] => Revathi
      )
      Array
      (
          [0] => Sarika
          [USER] => Sarika
      )
      Array
      (
          [0] => mysql.infoschema
          [USER] => mysql.infoschema
      )
      Array
      (
          [0] => mysql.session
          [USER] => mysql.session
      )
      Array
      (
          [0] => mysql.sys
          [USER] => mysql.sys
      )
      Array
      (
          [0] => root
          [USER] => root
      )