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
      Connection

      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.

      Connection

      While working with MySQL database, we use a client program to communicate with the database server. To do so, we must first establish a connection between them.
      To connect a client program with MySQL server, we must ensure all the connection parameters are properly used. These parameters work just like any other login parameters: consisting of a username and a password. Where, a username is the name of the host where the server is running and a password needs to be set according to the user.
      Generally, each connection parameter holds a default value, but we can override them either on the command line or in an option file.
      This tutorial only uses the mysql client program to demonstrate the connection, but these principles also apply to other clients such as mysqldump, mysqladmin, or mysqlshow.

      Set Password to MySQL Root

      Usually, during the installation of MySQL server, we will be asked to set an initial password to the root. Other than that, we can also set the initial password using the following command 
      mysql -u root password "new_password";
      Where, new_password is the password set initially.

      Reset Password

      We can also change the existing password using the SET PASSWORD statement. However, we can only do so after logging in to the user account using the existing password. Look at the query below 
      SET PASSWORD FOR 'root'@'localhost' = PASSWORD('password_name');
      FLUSH PRIVILEGES;
      Every time a connection is needed to be established, this password must be entered.

      MySQL Connection Using MySQL Binary

      We can establish the MySQL database using the mysql binary at the command prompt.

      Example

      Here is a simple example to connect to the MySQL server from the command prompt 
      [root@host]# mysql -u root -p
      Enter password:******
      This will give us the 'mysql>' command prompt where we will be able to execute any SQL query. Following is the result of above command
      The following code block shows the result of above code 
      Welcome to the MySQL monitor.  Commands end with ; or \g.
      Your MySQL connection id is 2854760 to server version: 5.0.9
      
      Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
      In the above example, we have used root as a user but we can use any other user as well. Any user will be able to perform all the SQL operations, which are allowed to that user.
      We can disconnect from the MySQL database any time using the exit command at mysql> prompt.
      mysql> exit
      Bye

      MySQL Connection Using PHP Script

      We can open/establish connection to MySQL database using the PHP mysqli() constructor or, mysqli_connect() function. This function takes six parameters and returns a MySQL link identifier on success or FALSE on failure.

      Syntax

      Following is the syntax to open a MySQL connection using the constructor mysqli() 
      $mysqli = new mysqli($host, $username, $passwd, $dbName, $port, $socket);

      Parameters

      Following are its parameters
      Sr.No.
      Parameter & Description
      1
      $host
      Optional − The host name running the database server. If not specified, then the default value will be localhost:3306.
      2
      $username
      Optional − The username accessing the database. If not specified, then the default will be the name of the user that owns the server process.
      3
      $passwd
      Optional − The password of the user accessing the database. If not specified, then the default will be an empty password.
      4
      $dbName
      Optional − database name on which query is to be performed.
      5
      $port
      Optional − the port number to attempt to connect to the MySQL server.
      6
      $socket
      Optional − socket or named pipe that should be used.

      Closing the Connection

      We can disconnect from the MySQL database anytime using another PHP function close(). Following is the syntax 
      $mysqli->close();

      Example

      Try the following example to connect to a MySQL server. Save the file as mysql_example.php 
      <html>
         <head>
            <title>Connecting MySQL Server</title>
         </head>
         <body>
            <?php
               $dbhost = 'localhost';
               $dbuser = 'root';
               $dbpass = 'root@123';
               $mysqli = new mysqli($dbhost, $dbuser, $dbpass);
               
               if($mysqli->connect_errno ) {
                  printf("Connect failed: %s<br />", $mysqli->connect_error);
                  exit();
               }
               printf('Connected successfully.<br />');
               $mysqli->close();
            ?>
         </body>
      </html>

      Output

      Access the mysql_example.php deployed on apache web server and verify the output.
      Connected successfully.