MySQL Tutorial

MySQL Tutorial MySQL Tutorial A Beginners Guide To Learn MySQL Back Home Categories Online Courses Mock Interviews Webinars NEW Community Write for Us Categories Artificial Intelligence AI vs Machine Learning vs Deep LearningMachine Learning AlgorithmsArtificial Intelligence TutorialWhat is Deep LearningDeep Learning TutorialInstall TensorFlowDeep Learning with PythonBackpropagationTensorFlow TutorialConvolutional Neural Network TutorialVIEW ALL BI and Visualization What is TableauTableau TutorialTableau Interview QuestionsWhat is InformaticaInformatica Interview QuestionsPower BI TutorialPower BI Interview QuestionsOLTP vs OLAPQlikView TutorialAdvanced Excel Formulas TutorialVIEW ALL Big Data What is HadoopHadoop ArchitectureHadoop TutorialHadoop Interview QuestionsHadoop EcosystemData Science vs Big Data vs Data AnalyticsWhat is Big DataMapReduce TutorialPig TutorialSpark TutorialSpark Interview QuestionsBig Data TutorialHive TutorialVIEW ALL Blockchain Blockchain TutorialWhat is BlockchainHyperledger FabricWhat Is EthereumEthereum TutorialB lockchain ApplicationsSolidity TutorialBlockchain ProgrammingHow Blockchain WorksVIEW ALL Cloud Computing What is AWSAWS TutorialAWS CertificationAzure Interview QuestionsAzure TutorialWhat Is Cloud ComputingWhat Is SalesforceIoT TutorialSalesforce TutorialSalesforce Interview QuestionsVIEW ALL Cyber Security Cloud SecurityWhat is CryptographyNmap TutorialSQL Injection AttacksHow To Install Kali LinuxHow to become an Ethical Hacker?Footprinting in Ethical HackingNetwork Scanning for Ethical HackingARP SpoofingApplication SecurityVIEW ALL Data Science Python Pandas TutorialWhat is Machine LearningMachine Learning TutorialMachine Learning ProjectsMachine Learning Interview QuestionsWhat Is Data ScienceSAS TutorialR TutorialData Science ProjectsHow to become a data scientistData Science Interview QuestionsData Scientist SalaryVIEW ALL Data Warehousing and ETL What is Data WarehouseDimension Table in Data WarehousingData Warehousing Interview QuestionsData warehouse architectureTalend T utorialTalend ETL ToolTalend Interview QuestionsFact Table and its TypesInformatica TransformationsInformatica TutorialVIEW ALL Databases What is MySQLMySQL Data TypesSQL JoinsSQL Data TypesWhat is MongoDBMongoDB Interview QuestionsMySQL TutorialSQL Interview QuestionsSQL CommandsMySQL Interview QuestionsVIEW ALL DevOps What is DevOpsDevOps vs AgileDevOps ToolsDevOps TutorialHow To Become A DevOps EngineerDevOps Interview QuestionsWhat Is DockerDocker TutorialDocker Interview QuestionsWhat Is ChefWhat Is KubernetesKubernetes TutorialVIEW ALL Front End Web Development What is JavaScript â€" All You Need To Know About JavaScriptJavaScript TutorialJavaScript Interview QuestionsJavaScript FrameworksAngular TutorialAngular Interview QuestionsWhat is REST API?React TutorialReact vs AngularjQuery TutorialNode TutorialReact Interview QuestionsVIEW ALL Mobile Development Android TutorialAndroid Interview QuestionsAndroid ArchitectureAndroid SQLite DatabaseProgramming A Begin... MySQL (55 B logs) Become a Certified Professional AWS Global Infrastructure Introduction to SQL What is a Database? Definition, Types and ComponentsWhat is SQL and how to get started with it?SQL Basics â€" One Stop Solution for BeginnersWhat are SQL Operators and how do they work?Understanding SQL Data Types â€" All You Need To Know About SQL Data TypesSQL Tutorial : One Stop Solution to Learn SQLDBMS Tutorial : A Complete Crash Course on DBMSCREATE TABLE in SQL â€" Everything You Need To Know About Creating Tables in SQLWhat is a Schema in SQL and how to create it?What is a Cursor in SQL and how to implement it?Top 10 Reasons Why You Should Learn SQLLearn how to use SQL SELECT with examplesSQL Functions: How to write a Function in SQL?What is SQL Regex and how to implement it?SQL UPDATE : Learn How To Update Values In A TableSQL Union â€" A Comprehensive Guide on the UNION OperatorWhat are Triggers in SQL and how to implement them?INSERT Query SQL â€" All You Need to Know about the INSERT statementHow To Use Alter Table Statement In SQL?What is Normalization in SQL and what are its types?How to perform IF statement in SQL?What are SQL constraints and its different types?Learn How To Use CASE Statement In SQLPrimary Key In SQL : Everything You Need To Know About Primary Key OperationsForeign Key SQL : Everything You Need To Know About Foreign Key OperationsSQL Commands - A Beginner's Guide To SQLHow To Rename a Column Name in SQL?How to retrieve a set of characters using SUBSTRING in SQL?What is the use of SQL GROUP BY statement?How To Use ORDER BY Clause In SQL?How to use Auto Increment in SQL?Everything You Need to Know About LIKE Operator in SQLWhat is an index in SQL?Understanding SQL Joins â€" All You Need To Know About SQL JoinsDifferences Between SQL A Beginners Guide To Learn MySQL Last updated on May 22,2019 20.4K Views Sahiti Kappagantula Bookmark 3 / 6 Blog from Introduction To MySQL Become a Certified Professional MySQL Tutorial is the second blog in this blog series. In the previous blogWhat is MySQL, I introduced you to all the basic terminologies that you needed to understand before you get started with this relational database. In this blog of MySQL, you will be learning all the operations and command that you need to explore your databases.The topics covered in this blog are mainly divided into 4 categories: DDL, DML, DCL TCL.The DDL (Data Definition Language) consists of those commands which are used to define the database. Example: CREATE,DROP, ALTER, TRUNCATE, COMMENT, RENAME.The DML (Data Manipulation Language) commands deal with the manipulation of data present in the database. Example: SELECT, INSERT, UPDATE, DELETE.The DCL (Data Control Language) commands deal with the rights, permissions and other controls of the database system. Example: GRANT, INVOKEThe TCL ( Transaction Control Language) consists of those commandswhich mainly deal with the transaction of the database.Apart from the commands, following are the other topics covered in the blog:Different Types Of Keys In DatabaseConstraints Used In DatabaseNested QueriesJoinsSet OperationsWe are going to cover each of these categories one by one.In this blog on MySQL Tutorial, I am going to consider the below database as an example, to show you how to write commands.StudentIDStudentNameParentNameAddressCityPostalCodeCountryFees01HaznitizEmizDellys RoadAfir35110Algeria4214502ShubhamNarayanMG RoadBangalore560001India4567203SalomaoValentimMayo RoadRio Claro27460Brazil6543204VishalRameshQueens QuayToronto416Canada2345505Park JiminKim Tai HyungGangnam StreetSeoul135081South Korea22353Table 1: Sample Database MySQL TutorialSo, lets get started now!Subscribe to our youtube channel to get new updates..! MySQL Tutorial: Data Definition (DDL) CommandsThis section consists of those commands, by which you can define your database. The commands are:CREATEALTERDROPTRUNCATERENAMENow, before I start with the commands, let me just tell you the way to men tion the comments in MySQL.CommentsLike any other programming language, there are mainly two types of comments.Single-Line Comments The single line comments start with . So, any text mentioned after till the end of the line will be ignored by the compiler.Example: --Select all: SELECT*FROMStudents; Multi-Line Comments The Multi-line comments start with /* and end with */. So, any text mentioned between /* and */ will be ignored by the compiler.Example: /*Select all the columns of all the records in the Students table:*/ SELECT*FROMStudents; Now, that you know how to mention comments in MySQL, lets continue with the DDL commands.CREATEThe create statement is used to either create a schema, tables or an index.The CREATE SCHEMA StatementThis statement is used to create a database.Syntax:CREATE SCHEMA Database_Name;Example: CREATE SCHEMA StudentsInfo; The CREATE TABLE StatementThis statement is used to create a new table in a database.Syntax:CREATETABLEtable_name( column1 datatype, column2 datatype, column3 datatype, .... );Example: CREATE TABLE Students ( StudentID int, StudentName varchar(255), ParentName varchar(255), Address varchar(255), PostalCode int, City varchar(255) ); The CREATE TABLE AS StatementThis statement is used to create a new table from an existing table. So, this table gets the same column definitions as that of the existing table.Syntax:CREATETABLEnew_table_nameAS SELECTcolumn1, column2,... FROMexisting_table_name WHERE....;Example: CREATETABLEExampleTableAS SELECT Studentname, Parentname FROM Students; ALTERThe ALTER command is used to add, modify or delete constraints or columns.The ALTER TABLE StatementThis statement is used to either add, modify or delete constraints and columns from a table.Syntax:ALTERTABLEtable_name ADDcolumn_name datatype;Example: ALTERTABLE Students ADDDateOfBirth date; DROPThe DROP command is used to delete the database, tables or columns.The DROP SCHEMA StatementThis statement is used to drop the complete schema.Syntax:DROP SCHEMA schema_name;Example: DROP SCHEMA StudentsInfo; The DROP TABLE StatementThis statement is used to drop the entire table with all its values.Syntax:DROP TABLE table_name;Example: DROP TABLE table_name; TRUNCATEThis statement is used to delete the data which is present inside a table, but the table doesnt get deleted.Syntax:TRUNCATETABLEtable_name;Example: TRUNCATETABLE Students; RENAMEThis statement is used to rename one or more tables.Syntax:RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2] ...Example: RENAME Students TO Infostudents; Now, before I move into the further sections, let me tell you the various types of Keys and Constraints that you need to mention while manipulating the databases.MySQL Tutorial: Different Types Of Keys In DatabaseThere are mainly 5 types of Keys, that can be mentioned in the database.Candidate Key The minimal set of attributes which can uniquely identify a tuple is known as a candidate key. A relation can hold more than a single candidate key, where the key is either a simple or composite key.Super Key The set of attributes which can uniquely identify a tuple is known as Super Key. So, a candidate key is a superkey, but vice-versa isnt true.Primary Key Aset of attributes that can be used to uniquely identify every tuple is also a primary key. So, if there are 3-4 candidate keys present in a relationship, then out those, one can be chosen as a primary key.Alternate Key The candidate key other than the primary key is called as an alternate key.Foreign Key An attribute that can only take the values present as the values of some other attribute, is theforeign key to the attribute to which it refers.MySQL Tutorial: Constraints Used In DatabaseRefer to the image below are the constraints used in the database.Figure 1: Constraints Used In Database MySQL TutorialNow, that you know the various types of keys and constraints, lets move on to the next section i.e Data Manipulation Commands. Want to be a certified Database Administrator? Register Now! MySQL Tutorial: Data Manipulation (DML) CommandsThis section consists of those commands, by which you can manipulate your database. The commands are:USEINSERTUPDATEDELETESELECTApart from these commands, there are also other manipulative operators/functions such as:LOGICAL OPERATORSARITHMETIC,BITWISE,COMPARISON COMPOUND OPERATORSAGGREGATE FUNCTIONSSPECIAL OPERATORSUSEThe USE statement is used to mention which database has to be used to perform all the operations.Syntax:USE Database_name;Example: USEStudentsInfo; INSERTThis statement is used to insert new records in a table.Syntax:The INSERT INTO statement can be written in the following two ways:INSERTINTOtable_name(column1,column2,column3, ...) VALUES(value1,value2,value3, ...); --You need not mention the column names INSERTINTOtable_name VALUES(value1,value2,value3, ...);Example: INSERTINTO Infostudents(StudentID, StudentName, ParentName, Address, City, PostalCode, Country) VALUES('06', 'Sanjana','Jagannath', 'Banjara Hills', 'Hyderabad', '500046', 'India'); INSERTINTO Infostudents VALUES('07', 'Shivantini','Praveen', 'Camel Street', 'Kolkata', '700096', 'India'); UPDATEThis statement is used to modify the existing records in a table.Syntax:UPDATEtable_name SETcolumn1=value1,column2=value2, ... WHEREcondition;Example: UPDATE Infostudents SETStudentName ='Alfred', City='Frankfurt' WHEREStudentID =1; DELETEThis statement is used to delete existing records in a table.Syntax:DELETEFROMtable_name WHEREcondition;Example: DELETEFROM Infostudents WHERE StudentName='Salomao'; SELECTThis statement is used to select data from a database and the data returned is stored in a result table, called the result-set.The following are the two ways of using this statement:Syntax:SELECTcolumn1,column2, ... FROMtable_name; --(*) is used to select all from the table SELECT*FROMtable_name;Example: SELECTStudentName, CityFROM Infostudents; SELECT*FROMInfostudents; Apart from the individual SELECT keyword, we will be also seeing the following statements, which are used with the SELECT keyword:DISTINCTORDER BYGROUP BYHAVING ClauseThe SELECT DISTINCT StatementThis statement is used to return only distinct or different values. So, if you have a table with duplicate values, then you can use this statement to list distinct values.Syntax:SELECTDISTINCTcolumn1,column2, ... FROMtable_name;Example: SELECTCountryFROMStudents; The ORDER BY StatementThis statement is used to sort the desired results in ascending or descending order. By default, the results would besorted in ascending order. If you want the records in the result-set in descending order, then use the DESC keyword.Syntax:SELECTcolumn1,column2, ... FROMtable_name ORDERBYcolumn1, column2, ...ASC|DESC;Example: SELECT*FROMInfostudents ORDERBYCountry; SELECT*FROMInfostudents ORDERBYCountry DESC; SELECT*FROM Infostudents ORDERBYCountry, StudentName; SELECT*FROM Infostudents ORDERBYCountryASC, StudentNameDESC; The GROUP BY StatementThis statement is used with the aggregate functions to group the result-set by one or more columns.Syntax:SELECTcolumn_name(s) FROMtable_name WHEREcondition GROUPBYcolumn_name(s) ORDERBYcolumn_name(s);Example: SELECT COUNT(StudentID), Country FROM Infostudents GROUP BY Country ORDERBYCOUNT(StudentID)DESC; The HAVING Clause StatementSince the WHERE keyword cannot be used with aggregate functions, the HAVING clause was introduced.Syntax:SELECTcolumn_name(s) FROMtable_name WHEREcondition GROUPBYcolumn_name(s) HAVINGcondition ORDERBYcolumn_name(s);Example: SELECTnbsp;COUNT(StudentID), City FROMnbsp;Infostudents GROUPnbsp;BYnbsp;City HAVINGnbsp;COUNT(Fees) 23000; LOGICAL OPERATORSThis set of operators consists of logical operators such as AND/OR/NOT.AND OPERATORThe AND operator is used to filter records that rely on more than one condition. This operator displays the records, which satisfy all the conditions separated by AND, and give the output TRUE.Syntax:SELECTcolumn1,column2, ... FROMtable_name WHEREcondition1ANDcondition2ANDcondition3 ...;Example: SELECT*FROMInfostudents WHERECountry='Brazil'ANDCity='Rio Claro'; OR OPERATORThe OR operator displays those records which satisfy any of the conditions separated by OR and gives the output TRUE.Syntax:SELECTcolumn1,column2, ... FROMtable_name WHEREcondition1ORcondition2ORcondition3 ...;Example: SELECT*FROMInfostudents WHERECity='Toronto'ORCity='Seoul'; NOT OPERATORThis operator displays a record when the condition (s) is NOT TRUE.Syntax:SELECTcolumn1,column2, ... FROMtable_name WHERENOTcondition;Example: SELECT*FROMInfostudents WHERENOTCountry='India'; --You can also combine all the above three operators and write a query like this: SELECT*FROMInfostudents WHERECountry='India'AND(City='Bangalore'ORCity='Canada'); Interested in cracking Interviews for Database Administrator? Learn Now! ARITHMETIC, BITWISE, COMPARISON COMPOUND OPERATORSRefer to the image below.Figure 2: Arithmetic, Bitwise, Comparison Compound Operators MySQL TutorialAGGREGATE FUNCTIONSThis section of the article include the following functions:MIN()MAX()COUNT()AVG()SUM()MIN() FunctionThis function returns the smallest value of the selected column in a table.Syntax: SELECTMIN(column_name) FROMtable_name WHEREcondition; Example: SELECTMIN(StudentID)ASSmallestID FROMInfostudents; MAX() FunctionThis function returns the largest value of the selected column in a table.Syntax:SELECTMAX(column_name) FROMtable_name WHEREcondition;Example: SELECTMAX(Fees)ASMaximumFees FROMInfostudents; COUNT() FunctionThis function returns the number of rows that match the specified criteria.Syntax:SELECTCOUNT(column_name) FROMtable_name WHEREcondition;Example: SELECTCOUNT(StudentID) FROMInfostudents; AVG() FunctionThis function returns the average value of a numeric column that you choose.Syntax:SELECTAVG(column_name) FROMtable_name WHEREcondition;Example: SELECTAVG(Fees) FROMInfostudents; SUM() FunctionThis function returns the total sum of a numeric column that you choose.Syntax:SELECTSUM(column_name) FROMtable_name WHEREcondition;Example: SELECT SUM(Fees) FROMInfostudents; SPECIAL OPERATORSThis section includes the following operators:BETWEENIS NULLLIKEINEXISTSALLANYBETWEEN OperatorThis operator is an inclusive operator which selects values(numbers, texts or dates) within a given range.Syntax:SELECTcolumn_name(s) FROMtable_name WHEREcolumn_nameBETWEENvalue1ANDvalue2;Example: SELECT*FROMInfostudents WHEREFeesBETWEEN20000AND40000; IS NULL OperatorSince it is not possible to test for the NULL values with the comparison operators(=, , ), we can use IS NULL and IS NOT NULL operators instead.Syntax:--Syntax for IS NULL SELECTcolumn_names FROMtable_name WHEREcolumn_nameISNULL; --Syntax for IS NOT NULL SELECTcolumn_names FROMtable_name WHEREcolumn_nameISNOTNULL;Example: SELECTStudentName, ParentName, AddressFROMInfostudents WHEREAddressISNULL; SELECTStudentName, ParentName, AddressFROMInfostudents WHEREAddressISNOTNULL; LIKE OperatorThis operator is used in a WHERE clause to search for a specified pattern in a column of a table.The mentioned below are the two wildcards that are used in conjunction with the LIKE operator:% The percent sign represents zero, one, or multiple characters_ The underscore represents a single characterSyntax:SELECTcolumn1, column2, ... FROMtable_name WHEREcolumnLIKEpattern;Refer to the following table for the various patterns that you can mention with LIKE operator.LIKE OperatorDescriptionWHERE CustomerName LIKE z%Finds any values that start with zWHERE CustomerName LIKE %zFinds any values that end with zWHERE CustomerName LIKE %and%Finds any values that have and in any positionWHERE CustomerName LIKE _s%Finds any values that have s in the second position.WHERE CustomerName LIKE d_%_%Finds any values that start with d and are at least 3 characters in lengthWHERE ContactName LIKE j%lFinds any values that start with j and ends with lTable 2: Patterns Mentioned With LIKE Operator MySQL TutorialExample: SELECT*FROMInfostudents WHEREStudentNameLIKE'S%'; IN OperatorThis is a shorthand operatorfor multiple OR conditions which allows you to specify multiple values in a WHERE clause.Syntax:SELECTcolumn_name(s) FROMtable_name WHEREcolumn_nameIN(value1,value2, ...);Example: SELECT*FROMInfostudents WHERECountryIN('Algeria','India','Brazil'); Note: You can also use IN while writing Nested Queries. Consider the below syntax:EXISTS OperatorThis operator is used to test if a record exists or not.Syntax:SELECTcolumn_name(s) FROMtable_name WHEREEXISTS (SELECTcolumn_nameFROMtable_nameWHEREcondition);Example: SELECTnbsp;StudentName FROMnbsp;Infostudents WHEREnbsp;EXISTSnbsp;(SELECTnbsp;ParentNamenbsp;FROMnbsp;Infostudentsnbsp;WHEREnbsp;StudentId = 05nbsp;ANDnbsp;Price nbsp;25000); ALL OperatorThis operator is used with a WHERE or HAVING clause and returns true if all of the subquery values meet the condition.Syntax:SELECTcolumn_name(s) FROMtable_name WHEREcolumn_name operatorALL (SELECTcolumn_nameFROMtable_nameWHEREcondition);Example: SELECTnbsp;StudentName FROMnbsp;Infostudents WHEREnbsp;StudentID =nbsp;ALLnbsp;(SELECTnbsp;StudentIDnbsp;FROMnbsp;Infostudentsnbsp;WHEREnbsp;Fees 20000); ANY OperatorSimilar to the ALL operator, the ANY operator is also usedwith a WHERE or HAVING clause and returns true if any of the subquery values meet the condition.Syntax:SELECTcolumn_name(s) FROMtable_name WHEREcolumn_name operatorANY (SELECTcolumn_nameFROMtable_nameWHEREcondition);Example: SELECTStudentName FROMInfostudents WHEREStudentID =ANY(SELECTSttudentIDFROMInfostudentsWHEREFees BETWEEN 22000 AND 23000); Now, that I have told you a lot about DML commands, let me just tell you in short about Nested Queries,Joins and Set Operations. Want to know how to set up a relational database in the cloud? Explore Amazon's RDS Now! MySQL Tutorial: Nested QueriesNested queries are those queries which have an outer query and inner subquery. So, basically, the subquery is a query which is nested within another query such as SELECT, INSERT, UPDATE or DELETE. Refer to the image below:Fig 3: Representation Of Nested Queries MySQL TutorialMySQL Tutorial: JoinsJOINS are used to combine rows from two or more tables, based on a related column between those tables. The following are the types of joins:INNER JOIN: This join returns those records which have matching values in both the tables.FULL JOIN: This join returns all those records which either have a match in the left or the right table.LEFT JOIN: This join returns records from the left table, and also those records which satisfy the condition from th e right table.RIGHT JOIN: This join returns records from the right table, and also those records which satisfy the condition from the left table.Refer to the image below.Fig 4: Representation Of Joins MySQL TutorialLets consider the below table apart from the Infostudents table, to understand the syntax of joins.CourseIDStudentIDCourseNameStartDate110DevOps09-09-2018211Blockchain07-04-2018312Python08-06-2018Table 3: Sample Database MySQL TutorialINNER JOINSyntax:SELECTcolumn_name(s) FROMtable1 INNERJOINtable2ONtable1.column_name=table2.column_name;Example: SELECTCourses.CourseID, Infostudents.StudentName FROMCourses INNERJOINInfostudentsON Courses.StudentID = Infostudents.StudentID; FULL JOINSyntax:SELECTcolumn_name(s) FROMtable1 FULLOUTERJOINtable2ONtable1.column_name=table2.column_name;Example: SELECT Infostudents.StudentName, Courses.CourseID FROM Infostudents FULLOUTERJOINOrdersON Infostudents.StudentID=Orders.StudentID ORDERBY Infostudents.StudentName; LEFT JOINSyntax:SELECTcolumn_name(s) FROMtable1 LEFTJOINtable2ONtable1.column_name=table2.column_name;Example: SELECT Infostudents.StudentName, Courses.CourseID FROM Infostudents LEFTJOINCourses ON Infostudents.CustomerID = Courses.StudentID ORDERBY Infostudents.StudentName; RIGHT JOINSyntax:SELECTcolumn_name(s) FROMtable1 RIGHTJOINtable2ONtable1.column_name=table2.column_name;Example: SELECT Courses.CourseID FROM Courses RIGHTJOIN InfostudentsON Courses.StudentID = Infostudents.StudentID ORDERBY Courses.CourseID; MySQL Tutorial: Set OperationsThere are mainly three set operations: UNION, INTERSECT, SET DIFFERENCE. You can refer to the image below to understand the set operations in SQL.Now, that you guys know the DML commadsn. Lets move onto our next section and see the DCL commands.MySQL Tutorial: Data Control (DCL) CommandsThis section consists of those commands which are used to control privileges in the database. The commands are:GRANTREVOKEGRANTThis command is used to provide user access privileges or other privileges for the database.Syntax:GRANT privileges ON object TO user;Example: GRANT CREATE ANY TABLE TO localhost; REVOKEThis command is used to withdraw users access privileges given by using the GRANT command.Syntax:REVOKE privileges ON object FROM user;Example: REVOKE INSERT ON *.* FROM Infostudents; Now, lets move on to the last section of this blog i.e. the TCL Commands.MySQL Tutorial: Transaction Control (TCL) CommandsThis section of commands mainly deals with the transaction of the database. The commands are:COMMITROLLBACKSAVEPOINTRELEASE SAVEPOINTSET TRANSACTIONCOMMITThis command saves all the transactions to the database since the last COMMIT or ROLLBACK command.Syntax:COMMIT;Example: DELETE FROM Infostudents WHERE Fees = 42145; COMMIT; ROLLBACKThis command is used to undo transactions since the last COMMIT or ROLLBACK command was issued.Syntax:ROLLBACK;Example: DELETE FROM Infostudents WHERE Fees = 42145; ROLLBACK; SAVEPOINTThis command creates points within the groups of transactions in which to ROLLBACK. So, with this command, you can simplyroll the transaction back to a certain point without rolling back the entire transaction.Syntax:SAVEPOINT SAVEPOINT_NAME; --Syntax for saving the SAVEPOINT ROLLBACK TO SAVEPOINT_NAME; --Syntax for rolling back to the Savepoint commandExample: SAVEPOINT SP1; DELETE FROM Infostudents WHERE Fees = 42145; SAVEPOINT SP2; RELEASE SAVEPOINTYou can use this command to remove a SAVEPOINT that you have created.Syntax:RELEASE SAVEPOINT SAVEPOINT_NAME;Example: RELEASE SAVEPOINT SP2; SET TRANSACTIONThis command gives a name to the transaction.Syntax:SET TRANSACTION [ READ WRITE | READ ONLY ];I hope you enjoyed reading this blog on MySQL Tutorial blog. We have seen the different commands that will help you write queries and play around with your databases. Interested in learning more about MySQL? View Batches Now! If you wish to learn more about MySQL and get to knowthis open source relational database, then check out ourMySQL DBA Certification Trainingwhichcomes with instructor-led live training and real-life project experience.This training will help you understand MySQL in depth and help you achieve mastery over the subject.Got a question for us? Please mention it in the comments section of MySQL Tutorial and I will get back to you.Recommended videos for you Build Application With MongoDB Watch Now Introduction to MongoDB Watch NowRecommended blogs for you Top 5 Reasons to Learn Cassandra Decoded! Read Article SQL Pivot â€" Know how to convert rows to columns Read Article What are SQL Operators and how do they work? Read Article SSIS Tutorial For Beginners: Why, What and How? Read Article SQL Functions: How to write a Function in SQL? Read Article SQL Tutorial : One Stop Solution to Learn SQL Read Article Introduction to Cassandra Architecture Read Article Top 50 Oracle Interview Questions You Should Master Read Article SQL Views: How to work with Views in SQL? Read Article Differences Between SQL NoSQL Databases MySQL MongoDB Comparison Read Article MySQL Tutorial A Beginners Guide To Learn MySQL Read Article Understanding SQL Data Types All You Need To Know About SQL Data Types Read Article MySQL Data Types An Overview Of The Data Types In MySQL Read Article MongoDB ® with Hadoop and related Big Data technologies Read Article SQL Union A Comprehensive Guide on the UNION Operator Read Article Top 30 SQL Query Interview Questions You Must Practice In 2019 Read Article What is the Average Salary of a SQL Developer? Read Article SQL Datetime: Everything you Need to Know Read Article Everything you Need to Know About MongoDB Client Read Article What is the use of DECODE function in SQL? Read Article Comments 0 Comments Trending Courses in Databases SQL Essentials Training Certification6k Enrolled LearnersWeekend/WeekdaySelf Paced Reviews 5 (2400)