If you are interested to learn MySQL Data Model

What is SQL?

SQL is the standard language for dealing with Relational Databases. SQL is used to insert, search, update, and delete database records.

How to Use SQL

The following SQL statement selects all the records in the “Customers” table:

Example

SELECT * FROM Customers;

MySQL

MySQL is the most popular Open Source Relational SQL database management system. MySQL is one of the best RDBMS being used for developing web-based software applications. MySQL is an open-source relational database management system used throughout the biggest companies in modern tech. Since its creation, MySQL has established itself as the industry standard for relational database creation and manipulation.

MySQL is a relational database management system based on the Structured Query Language, which is the popular language for accessing and managing the records in the database. MySQL is open-source and free software under the GNU license. It is supported by Oracle Company.

Our MySQL tutorial includes all topics of MySQL database that provides for how to manage database and to manipulate data with the help of various SQL queries. These queries are: insert records, update records, delete records, select records, create tables, drop tables, etc. There are also given MySQL interview questions to help you better understand the MySQL database.

Audience

This reference has been prepared for the beginners to help them understand the basics to advanced concepts related to MySQL languages.

Prerequisites

Before you start doing practice with various types of examples given in this reference, I’m making an assumption that you are already aware about what is database, especially RDBMS and what is a computer programming language.

Keep in Mind That…

• SQL keywords are NOT case sensitive: select is the same as SELECT

Semicolon after SQL Statements?

Some database systems require a semicolon at the end of each SQL statement. Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server. In this tutorial, we will use semicolon at the end of each SQL statement.

Some of The Most Important SQL Commands

• SELECT – extracts data from a database
• UPDATE – updates data in a database
• DELETE – deletes data from a database
• INSERT INTO – inserts new data into a database
• CREATE DATABASE – creates a new database
• ALTER DATABASE – modifies a database
• CREATE TABLE – creates a new table
• ALTER TABLE – modifies a table
• DROP TABLE – deletes a table
• CREATE INDEX – creates an index (search key)
• DROP INDEX – deletes an index

MySQL Database

MySQL is a fast, easy-to-use RDBMS being used for many small and big businesses. MySQL 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.

MySQL SELECT Statement

The SELECT statement is used to select data from a database. The data returned is stored in a result table, called the result-set.

SELECT Syntax

SELECT column1, column2, ...
FROM table_name;

Here, column1, column2, … are the field names of the table you want to select data from. If you want to select all the fields available in the table, use the following syntax:

SELECT * FROM table_name;

Demo Database

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the “Customers” table in the Northwind sample database:

SELECT Columns Example

The following SQL statement selects the “CustomerName”, “City”, and “Country” columns from the “Customers” table:

Example

SELECT CustomerName, City, Country FROM Customers;

SELECT * Example

The following SQL statement selects ALL the columns from the “Customers” table:

Example

SELECT * FROM Customers;

The MySQL SELECT DISTINCT Statement

The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

SELECT DISTINCT Syntax

SELECT DISTINCT column1, column2, ...
FROM table_name;

SELECT Example Without DISTINCT

The following SQL statement selects all (including the duplicates) values from the “Country” column in the “Customers” table:

Example

SELECT Country FROM Customers;

Now, let us use the SELECT DISTINCT statement and see the result.

SELECT DISTINCT Examples

The following SQL statement selects only the DISTINCT values from the “Country” column in the “Customers” table:

Example

SELECT DISTINCT Country FROM Customers;

The following SQL statement counts and returns the number of different (distinct) countries in the “Customers” table:

Example

SELECT COUNT(DISTINCT Country) FROM Customers;