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
      Java Syntax

      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.

       Java Syntax

      To communicate with databases Java provides a library known as JDBC (Java Database Connectivity). JDBC provides a set of classes and methods specifically designed for database connectivity, enabling Java developers to perform tasks such as establishing connections, executing queries, and managing data in MySQL databases.

      JDBC Installation

      To use MySQL with Java, you need to use a JDBC (Java Database Connectivity) driver to connect your Java application to a MySQL database. Below are the general steps for installing and using the MySQL Connector/J, which is the official MySQL JDBC driver for Java
      Step 1: Download MySQL Connector/J
      Visit the official MySQL Connector/J download page: MySQL Connector/J Downloads.
      Step 2: Select the Appropriate Version
      Choose the appropriate version based on your MySQL server version and Java version. Download the ZIP or TAR archive containing the JDBC driver.
      Step 3: Extract the Archive
      Extract the contents of the downloaded archive to a location on your computer.
      Step 4: Add Connector/J to Your Java Project
      In your Java project, add the Connector/J JAR file to your classpath. You can do this in your IDE or by manually copying the JAR file into your project.
      Step 5: Connect to MySQL Database in Java
      In your Java code, use the JDBC API to connect to the MySQL database.

      Java Functions to Access MySQL

      Following are the major functions involved in accessing MySQL from Java
      S.No
      Function & Description
      1
      DriverManager.getConnection(String url, String user, String password)
      Establishes a connection to the database using the specified URL, username, and password.
      2
      createStatement()
      Creates a Statement object for executing SQL queries.
      3
      executeQuery(String sql)
      Executes a SQL SELECT query and returns a ResultSet object containing the result set.
      4
      executeUpdate(String sql)
      Executes a SQL INSERT, UPDATE, DELETE, or other non-query statement.
      5
      next()
      Moves the cursor to the next row in the result set. Returns true if there is a next row, false otherwise.
      6
      getInt(String columnLabel)
      Retrieves the value of the specified column in the current row of the result set.
      7
      prepareStatement(String sql)
      Creates a PreparedStatement object for executing parameterized SQL queries.
      8
      setXXX(int parameterIndex, XXX value)
      Sets the value of a specified parameter in the prepared statement.
      9
      executeQuery(), executeUpdate()
      Execute the prepared statement as a query or update.
      10
      setAutoCommit(boolean autoCommit)
      Enables or disables auto-commit mode.
      11
      commit()
      Commits the current transaction.
      12
      rollback()
      Rolls back the current transaction.

      Basic Example

      To connect and communicate with a MySQL database using Java, you can follow these steps
      • Load the JDBC driver specific to your database.
      • Create a connection to the database using "DriverManager.getConnection()".
      • Create a "Statement" or "PreparedStatement" for executing SQL queries.
      • Use "executeQuery()" for SELECT queries, or "executeUpdate()" for other statements.
      • Iterate through the "ResultSet" to process the retrieved data.
      • Close "ResultSet", "Statement", and "Connection" to release resources.
      • Wrap database code in try-catch blocks to handle exceptions.
      • Use transactions if performing multiple operations as a single unit.
      The following example shows a generic syntax of a Java program to call any MySQL query 
      import java.sql.Connection;
      import java.sql.DriverManager;
      import java.sql.ResultSet;
      import java.sql.SQLException;
      import java.sql.Statement;
      
      public class DatabaseInteractionExample {
      
          public static void main(String[] args) {
              try {
                  // Load JDBC Driver
                  Class.forName("com.mysql.cj.jdbc.Driver");
      
                  // Connect to Database
                  Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/your_database", "your_username", "your_password");
      
                  // Execute Query
                  Statement statement = connection.createStatement();
                  ResultSet resultSet = statement.executeQuery("Your SQL Query");
      
                  // Process Results
                  while (resultSet.next()) {
                      // Process data
                  }
      
                  // Close Resources
                  resultSet.close();
                  statement.close();
                  connection.close();
      
                  // Handle Exceptions
              } catch (ClassNotFoundException | SQLException e) {
                  e.printStackTrace();
              }
          }
      }