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
      Introduction

      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.

      Introduction

      What is a Database?

      A database is used to store a collection of data (which can either be structured or unstructured). Each database has one or more distinct APIs for creating, accessing, managing, searching and replicating the data it holds.
      Other kinds of data storages can also be used to manage data, such as files on the file system or large hash tables in memory, but data fetching and writing would not be so fast and easy with those type of systems.
      Nowadays, we use relational database management systems (RDBMS) to store and manage huge volume of data. In such a database, the data is stored in a structured way with the help of different tables. Relations are established among these tables using primary keys or other keys known as Foreign Keys.
      A Relational DataBase Management System (RDBMS) is a software that −
      • Enables you to implement a database with tables, columns and indexes.
      • Guarantees the Referential Integrity between rows of various tables.
      • Updates the indexes automatically.
      • Interprets an SQL query and combines information from various tables.

      RDBMS Terminology

      Before we proceed to explain the MySQL database system, let us revise a few definitions related to the database.
      • Database − A database is a collection of tables, with related data.
      • Table − A table is a matrix with data. A table in a database looks like a simple spreadsheet.
      • Column − One column (data element) contains data of one and the same kind, for example the column postcode.
      • Row − A row (= tuple, entry or record) is a group of related data, for example the data of one subscription.
      • Redundancy − Storing data twice, redundantly to make the system faster.
      • Primary Key − A primary key is unique. A key value can not occur twice in one table. With a key, you can only find one row.
      • Foreign Key − A foreign key is the linking pin between two tables.
      • Compound Key − A compound key (composite key) is a key that consists of multiple columns, because one column is not sufficiently unique.
      • Index − An index in a database resembles an index at the back of a book.
      • Referential Integrity − Referential Integrity makes sure that a foreign key value always points to an existing row.

      MySQL Database

      MySQL is a fast, easy-to-use RDBMS being used for many small and big businesses. It is developed, marketed and supported by MySQL AB, which is a Swedish company. MySQL is becoming so popular because of many good reasons −
      • MySQL is released under an open-source license. So you have nothing to pay to use it.
      • MySQL is a very powerful program in its own right. It handles a large subset of the functionality of the most expensive and powerful database packages.
      • MySQL uses a standard form of the well-known SQL data language.
      • MySQL works on many operating systems and with many languages including PHP, PERL, C, C++, JAVA, etc.
      • MySQL works very quickly and works well even with large data sets.
      • MySQL is very friendly to PHP, the most appreciated language for web development.
      • MySQL supports large databases, up to 50 million rows or more in a table. The default file size limit for a table is 4GB, but you can increase this (if your operating system can handle it) to a theoretical limit of 8 million terabytes (TB).
      • MySQL is customizable. The open-source GPL license allows programmers to modify the MySQL software to fit their own specific environments.

      History of MySQL

      • Development of MySQL by Michael Widenius & David Axmark beginning in 1994.
      • First internal release on 23rd May 1995.
      • Windows Version was released on the 8th January 1998 for Windows 95 and NT.
      • Version 3.23: beta from June 2000, production release January 2001.
      • Version 4.0: beta from August 2002, production release March 2003 (unions).
      • Version 4.1: beta from June 2004, production release October 2004.
      • Version 5.0: beta from March 2005, production release October 2005.
      • Sun Microsystems acquired MySQL AB on the 26th February 2008.
      • Version 5.1: production release 27th November 2008.
      • Oracle acquired Sun Microsystems on 27th January 2010.
      • Version 5.5: general availability on 3rd December 2010
      • Version 5.6: general availability on 5th February 2013
      • Version 5.7: general availability on 21st October 2015
      • Version 8.0: general availability on 19th April 2018

      Before You Begin

      Before you begin this tutorial, you should have a basic knowledge of the information covered in our PHP and HTML tutorials.
      This tutorial focuses heavily on using MySQL in a PHP environment. Many examples given in this tutorial will be useful for PHP Programmers.
      We recommend you check our PHP Tutorial for your reference.