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
      Statements Reference

      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.

      Statements Reference

      Here is the list of all important MySQL statements. Each statement has been explained along with suitable example.

      Data Definition Statements

      • MySQL ALTER DATABASE Statement − This statement is used to change the characteristics of an existing database.
      • MySQL ALTER EVENT Statement − This statement is used to change the characteristics of an existing event.
      • MySQL ALTER FUNCTION Statement − This statement is used to change the characteristics of an existing function.
      • MySQL ALTER PROCEDURE Statement − This statement is used to change the characteristics of an existing procedure.
      • MySQL ALTER SERVER Statement − This statement is used to change the characteristics of an existing server.
      • MySQL ALTER TABLE Statement − This statement is used to change the characteristics of an existing table.
      • MySQL ALTER TABLESPACE Statement − This statement is used to change the characteristics of an existing tablespace.
      • MySQL ALTER VIEW Statement − This statement is used to change the characteristics of an existing view.
      • MySQL CREATE DATABASE Statement − This statement is used to create a new database.
      • MySQL CREATE EVENT Statement − This statement is used to create a new event.
      • MySQL CREATE FUNCTION Statement − This statement is used to create a new function.
      • MySQL CREATE INDEX Statement − This statement is used to create a new index.
      • MySQL CREATE PROCEDURE Statement − This statement is used to create a new procedure.
      • MySQL CREATE SERVER Statement − This statement is used to create a new server.
      • MySQL CREATE SPATIAL REFERENCE SYSTEM Statement − This statement is used to create a new spatial reference system.
      • MySQL CREATE TABLE Statement − This statement is used to create a new table.
      • MySQL CREATE TABLESPACE XXX Statement − This statement is used to create a new table space.
      • MySQL CREATE TRIGGER Statement − This statement is used to create a new trigger.
      • MySQL CREATE VIEW Statement − This statement is used to create a new view.
      • MySQL DROP DATABASE Statement − This statement is used to delete an existing database.
      • MySQL DROP EVENT Statement − This statement is used to delete an existing event.
      • MySQL DROP FUNCTION Statement − This statement is used to delete an existing function.
      • MySQL DROP INDEX Statement − This statement is used to delete an existing index.
      • MySQL DROP PROCEDURE Statement − This statement is used to delete an existing procedure.
      • MySQL DROP SERVER Statement − This statement is used to delete an existing server.
      • MySQL DROP SPATIAL REFERENCE SYSTEM Statement − This statement is used to delete an existing spacial reference system.
      • MySQL DROP TABLE Statement − This statement is used to delete an existing table.
      • MySQL DROP TABLESPACE Statement − This statement is used to delete an existing table space.
      • MySQL DROP TRIGGER Statement − This statement is used to delete an existing trigger.
      • MySQL DROP VIEW Statement − This statement is used to delete an existing view.
      • MySQL RENAME TABLE Statement − This statement is used to rename an existing table.
      • MySQL TRUNCATE TABLE Statement − This statement is used to remove all the records from a table.

      Data Manipulation Statements

      • MySQL CALL Statement − This statement is used to call the stored procedure.
      • MySQL DELETE Statement − This statement is use to remove records from a table.
      • MySQL DO Statement − This statement is used execute MySQL statement.
      • MySQL INSERT Statement − This statement is used to insert records into a table.
      • MySQL LOAD DATA Statement − This statement loads the contents of a file into a table.
      • MySQL LOAD XML Statement − This statement is used to load the contents of an XML file into a table.
      • MySQL REPLACE Statement − This statement inserts a new record into a table. if you try to insert a new column with duplicate value for the column with UNIQUE or PRIMARY KEY constraints the old record will be completely replaced by the new record.
      • MySQL SELECT Statement − This statement is used to retrieve rows from MySQL tables.
      • MySQL Subquries Statement − A subquery is a query with in a query.
      • MySQL TABLE Statement − This statement is used to retrieve the rows and columns of a specified table.
      • MySQL UPDATE Statement − This statement is used to update one or more records of an existing table.
      • MySQL VALUES Statement − This statement is used to return a set of rows as a table.
      • MySQL WITH Statement − This statement is used to create common table expressions.

      Transactional and Locking Statements

      • MySQL START TRANSACTION Statement − This statement is used to start a transaction.
      • MySQL COMMIT Statement − This statement saves the changes in the current transaction.
      • MySQL ROLLBACK Statement − This statement undoes all changes till last commit.
      • MySQL SAVEPOINT Statement − This statement is used to create a MySQL savepoint.
      • MySQL Set Auto-commit Statement − This statement is used to set turn the auto-commit option on or off.
      • MySQL ROLLBACK TO SAVEPOINT Statement − This statement undoes all the changes done by the current transaction to the last named save point.
      • MySQL RELEASE SAVEPOINT Statement − This statement is used to release/delete the specified savepoint.
      • MySQL LOCK INSTANCE FOR BACKUP Statement − This statement is used to create an instance level backup lock.
      • MySQL UNLOCK INSTANCE Statement − This statement is used to release all backup locks in the current instance.
      • MySQL LOCK TABLES Statement − This statement is used to acquire locks on MySQl tables.
      • MySQL UNLOCK TABLES Statement − This statement is used to release locks on MySQl tables.
      • MySQL SET TRANSACTION Statement − This statement is used to set values to the characteristics of the current transaction.

      Prepared Statements

      • MySQL PREPARE Statement − This is used to created a MySQL prepared statement.
      • MySQL EXECUTE Statement − This is used to execute a MySQL prepared statement.
      • MySQL DEALLOCATE PREPARE Statement − This is used to delete an existing MySQL prepared statement.

      Compound Statements

      • MySQL BEGIN ... END Compound Statement − The BEGIN ... END syntax is used to create a compound statement.
      • MySQL Statement Labels − This statement is used to create labels for start and end blocks of compound statements.
      • MySQL DECLARE Statement − This statement is used to create Local variables, conditions, handlers and, Cursors..
      • MySQL Variables in Stored Programs − This statement is used to create user-defined variables or system variables with in Procedures.

      Variables in Stored Programs

      • MySQL Local Variable DECLARE Statement − This statement is used to declare local variables with in a compound statement or a function.

      MySQL Flow Control Statements

      • MySQL CASE Statement − This statement is used to test a value for equality against a list of values/conditions.
      • MySQL IF Statement − This executes a set of statements as long as the given condition is satisfied.
      • MySQL ITERATE Statement − This statement is used to restart the LOOP, REPEAT or, WHILE statements.
      • MySQL LEAVE Statement − This statement is used to exit the LOOP, REPEAT, WHILE statements or, BEGIN…END statements.
      • MySQL LOOP Statement − This statement is used to execute a single or set of statements repeatedly.
      • MySQL REPEAT Statement − This statement is used to repeat the given set of statements (or statement) until the value of the given search condition is TRUE.
      • MySQL RETURN Statement − This statement is used to end the stored functions.
      • MySQL WHILE Statement − This statement is used to execute a single or set of statements repeatedly as long as the specified condition is TRUE.

      MySQL Cursors Statements

      • MySQL Cursor CLOSE Statement − This statement is used to close a cursor.
      • MySQL Cursor DECLARE Statement − This statement is used to declare a MySQL cursor.
      • MySQL Cursor FETCH Statement − This statement is used fetch the cursor declared earlier.
      • MySQL Cursor OPEN Statement − This statement is used open the cursor declared earlier.

      MySQL Condition Handling Statements

      • MySQL DECLARE ... CONDITION Statement − This statement is used to declare a MySQL condition.
      • MySQL DECLARE ... HANDLER Statement − This statement is used to declare a MySQL handler.
      • MySQL GET DIAGNOSTICS Statement − The diagnostic area holds information about the errors occurred and information about the statements generated them.
      • MySQL RESIGNAL Statement − This statement is used to raise a warning or error. This is used within an error handler.
      • MySQL SIGNAL Statement − The SIGNAL in MySQL is used provide error information to a handler, application or a client.
      • MySQL Scope Rules for Handlers
      • The MySQL Diagnostics Area
      • MySQL Condition Handling and OUT or INOUT Parameters

      MySQL Account Management Statements

      • MySQL ALTER USER Statement − This statement modify an existing MySQL user.
      • MySQL CREATE ROLE Statement − This statement is used to create a MySQL role.
      • MySQL CREATE USER Statement − This statement is used to create a MySQL user.
      • MySQL DROP ROLE Statement − This statement is used to drop an existing MySQL role.
      • MySQL DROP USER Statement − This statement is used to drop an existing MySQL user.
      • MySQL GRANT Statement − This statement is used to grant privileges to a MySQL role or user.
      • MySQL RENAME USER Statement − This statement is used to rename an existing user
      • MySQL REVOKE Statement − This statement is used to retrieve privileges previously granted to a MySQL role or user.
      • MySQL SET DEFAULT ROLE Statement − This statement is used set a role as default.
      • MySQL SET PASSWORD Statement − This statement is used to set a password to a MySQl user.
      • MySQL SET ROLE Statement − This statement is used to set a role to a MySQl user.

      MySQL Resource Group Management Statements

      • MySQL ALTER RESOURCE GROUP Statement − This statement is used to modify an existing resource group.
      • MySQL CREATE RESOURCE GROUP Statement − This statement is used to create a resource group.
      • MySQL DROP RESOURCE GROUP Statement − This statement is used to drop an existing a resource group.
      • MySQL SET RESOURCE GROUP Statement − This statement is used to set/assign a thread to an existing MySQL resource group.

      MySQL Table Maintenance Statements

      • MySQL ANALYZE TABLE Statement − This statement is used to analyzes the specified table.
      • MySQL CHECK TABLE Statement − This statement is used to check the integrity of database tables.
      • MySQL CHECKSUM TABLE Statement − This statement used to report checksum on the specified table.
      • MySQL OPTIMIZE TABLE Statement − This statement is used to optimize a specified table.
      • MySQL REPAIR TABLE Statement − This statement is used to repair a corrupted table.

      MySQL Miscellaneous Statements

      • MySQL CREATE FUNCTION Statement for User-Defined Functions − You can add user defined functions to MySQL server using this statement.
      • MySQL DROP FUNCTION Statement for User-Defined Functions − You can drop a loadable user defined function using this statement.
      • MySQL INSTALL COMPONENT Statement − This statement is used to install MySQL component.
      • MySQL INSTALL PLUGIN Statement − This statement is used to install MySQL plugin.
      • MySQL UNINSTALL COMPONENT Statement − This statement is used to uninstall an existing MySQL component.
      • MySQL UNINSTALL PLUGIN Statement − This statement is used to uninstall an existing MySQL plugin.
      • MySQL CLONE Statement − Using this statement you can clone from a remote server to a local directory.
      • MySQL SET Syntax for Variable Assignment − Using this statement you can set values to, user-defined variables, variables in procedures and, system variables.
      • MySQL SET CHARACTER SET Statement − This statement is used to set value to the CHARACTER SET attribute.
      • MySQL SET NAMES Statement − This statement Statement is used to set values to the character_set_client, character_set_connection, and character_set_results session system variables

      MySQL SHOW Statements

      • MySQL SHOW BINARY LOGS Statement − This statement displays the list of binary log files in the server.
      • MySQL SHOW BINLOG EVENTS Statement − This statement displays the events in the specified binary log. In case if you haven’t specified a log name the first log is displayed.
      • MySQL SHOW CHARACTER SET Statement − This statement displays the list of all the available character sets available in the current engine.
      • MySQL SHOW COLLATION Statement − This statement displays the list of collations supported by the server.
      • MySQL SHOW COLUMNS Statement − This statement of MySQL is used to retrieve/display the description of all the columns of a table.
      • MySQL SHOW CREATE DATABASE Statement − This query shows/displays the statement used to create the specified database.
      • MySQL SHOW CREATE EVENT Statement − This statement displays the query used to create the specified event.
      • MySQL SHOW CREATE FUNCTION Statement − This displays the statement used to create the specified function.
      • MySQL SHOW CREATE PROCEDURE Statement − This query displays the statement used to create the specified procedure.
      • MySQL SHOW CREATE TABLE Statement − This query shows/displays the statement used to create the specified table.
      • MySQL SHOW CREATE TRIGGER Statement − This statement displays the CREATE statement used to create the specified trigger.
      • MySQL SHOW CREATE USER Statement − This statement displays the CREATE statement used to create the specified user.
      • MySQL SHOW CREATE VIEW Statement − This statement displays the CREATE VIEW statement used to create the specified view.
      • MySQL SHOW DATABASES Statement − This statement displays the list of databases in MySQL.
      • MySQL SHOW SCHEMAS Statement − This statement is a synonym for the SHOW DATABASES statement so, you can also use this statement to list out databases.
      • MySQL SHOW ENGINE Statement − This statement displays the operation information about the specified engine.
      • MySQL SHOW ENGINES Statement − This statement returns the current status information of the storage engines of MySQL.
      • MySQL SHOW ERRORS Statement − This statement is used to retrieve the information about the error occurred during the execution of the previous MySQL statement in the current session.
      • MySQL SHOW EVENTS Statement − This statement displays the list of events created in the current database.
      • MySQL SHOW FUNCTION CODE Statement − This statement returns the code in the form of a result set where, each row in it represents an instruction in the function.
      • MySQL SHOW FUNCTION STATUS Statement − This statement displays the features of the specified stored function.
      • MySQL SHOW GRANTS Statement − This statement is used to display/ retrieve the privileges and roles assigned to a role or an account.
      • MySQL SHOW INDEX Statement − This statement is used to list out the information about table index.
      • MySQL SHOW OPEN TABLES Statement − This statement displays the lists the non-TEMPORARY tables which are currently open in the table cache.
      • MySQL SHOW PLUGINS Statement − This statement displays the list of plugins currently installed in the MySQL server.
      • MySQL SHOW PRIVILEGES Statement − This statement displays the list of privileges that are supported by the MYSQL server.
      • MySQL SHOW PROCEDURE CODE Statement − This statement returns the code in the form of a result set where, each row in it represents an instruction in the procedure.
      • MySQL SHOW PROCEDURE STATUS Statement − This statement displays the features of the stored procedures.
      • MySQL SHOW PROCESSLIST Statement − This statement displays the list of processes currently running on MySQL server.
      • MySQL SHOW STATUS Statement − This statement displays the name and values of variables that gives you information about the server status.
      • MySQL SHOW TABLE STATUS Statement − This statement displays the information about the non-TEMPORARY tables in a database.
      • MySQL SHOW TRIGGERS Statement − This statement is used to display information about all the triggers defined in the current database.
      • MySQL SHOW VARIABLES Statement − This statement is used to display names and values of MySQL system variables.
      • MySQL SHOW WARNINGS Statement − This statement is used to retrieve the information about the error, warnings, and notes occurred during the execution of the previous MySQL statement in the current session.

      MySQL Other Administrative Statements

      • MySQL CACHE INDEX Statement − Using this statement used to assign the indexes of a table to a key cache.
      • MySQL FLUSH Statement − Using this statement you can clear the caches of MySQL.
      • MySQL KILL Statement − Using this statement you can kill the specified thread.
      • MySQL LOAD INDEX INTO CACHE Statement − This statement is used to preload the index of a table into the default key cache or the cache assigned to it.
      • MySQL RESET Statement − This statement is used to reset various types of servers. Using this statement, you can reset a MySQL master, slave or, replica.
      • MySQL RESET PERSIST Statement − Using this statement.
      • MySQL RESTART Statement − This statement is used to stop and restart the MySQL server.
      • MySQL SHUTDOWN Statement − This statement stops the MySQL server.

      MySQL Utility Statements

      • MySQL DESCRIBE Statement − You can get the information about the table structure using this statement.
      • MySQL EXPLAIN Statement − Using this statement you can get the execution plan of the specified query.
      • MySQL HELP Statement − This statement is used to retrieve the information from the MySQL official documentation about the specified string.
      • MySQL USE Statement − You can select/use an existing database using this statement.