• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL SQL

← Previous Next →

SQL stands for Structured Query Language. It’s the standard language used to communicate with databases. Every time you create, read, update, or delete data in MySQL, you’re actually using SQL. It’s not a programming language like Python or Java — instead, it’s a query language that tells the database what to do.

SQL is the foundation of MySQL. Without SQL, you can’t interact with your database. Whether you want to create tables, insert records, or retrieve information, every operation in MySQL runs through SQL statements.

What is SQL?

SQL is a language used to store, manage, and manipulate data in a relational database. It works on the concept of tables, which contain rows and columns of data. You can think of each table as a spreadsheet, and SQL as the set of instructions you use to interact with that spreadsheet.

Here are some of the things SQL lets you do:

  • Create new databases and tables

  • Insert new records

  • Update existing data

  • Retrieve specific information using filters

  • Delete unnecessary data

  • Control user access and privileges

For example, this simple SQL statement retrieves all rows from a table named students:

SELECT * FROM students;

It means: “Select all columns from the table named students.”

Why SQL is Important in MySQL

MySQL itself is a Relational Database Management System (RDBMS). But SQL is the language MySQL understands to perform operations. Think of MySQL as the engine and SQL as the instructions that drive it.

Without SQL:

  • You couldn’t create or manage tables.

  • You couldn’t insert or retrieve data.

  • You couldn’t perform analysis or reporting.

So, learning SQL is the first step in mastering MySQL.

Types of SQL Statements

SQL commands in MySQL are grouped into different categories based on what they do. Each type has a specific role.

1. Data Definition Language (DDL)

DDL commands define the structure of the database. They help you create, modify, or delete tables and databases.

Command Description
CREATE Creates a new table or database
ALTER Modifies an existing table (like adding a column)
DROP Deletes a table or database
TRUNCATE Removes all data from a table but keeps its structure

Example:

CREATE TABLE courses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL
);

2. Data Manipulation Language (DML)

DML commands deal with data inside tables — adding, changing, or removing records.

Command Description
INSERT Adds new rows to a table
UPDATE Changes data in existing rows
DELETE Removes rows from a table

Example:

INSERT INTO courses (course_name) VALUES ('Mathematics');

3. Data Query Language (DQL)

DQL commands are used to fetch data from the database.
There’s mainly one command: SELECT.

Example:

SELECT course_name FROM courses;

The SELECT statement is one of the most powerful features of SQL. You can use it to filter, sort, and combine data from multiple tables.

4. Data Control Language (DCL)

DCL commands handle user permissions — who can do what inside the database.

Command Description
GRANT Gives privileges to a user
REVOKE Removes privileges from a user

Example:

GRANT ALL PRIVILEGES ON school.* TO 'user1'@'localhost';

5. Transaction Control Language (TCL)

TCL commands manage database transactions. A transaction is a set of operations that should either all happen or none at all.

Command Description
COMMIT Saves changes permanently
ROLLBACK Cancels uncommitted changes
SAVEPOINT Sets a temporary point to roll back to later

Example:

START TRANSACTION;
UPDATE students SET age = 20 WHERE id = 5;
COMMIT;

Basic SQL Syntax Rules

Before writing SQL queries, it’s important to understand a few basic rules:

  1. SQL keywords are not case-sensitive.
    SELECT and select mean the same thing. However, writing keywords in uppercase makes queries easier to read.

  2. Each SQL statement ends with a semicolon ( ; ).

  3. String values are enclosed in single quotes ('), not double quotes.
    Example:

    INSERT INTO students (name) VALUES ('Ananya');

  4. Comments can be added for better readability:

    -- This is a single-line comment
/* This is a
   multi-line comment */

  5. White spaces and line breaks are ignored by SQL, so formatting doesn’t affect execution.

How SQL Works in MySQL

When you run an SQL statement in MySQL, it goes through a series of steps:

  1. Parsing: MySQL checks if the syntax is valid.

  2. Optimization: The SQL optimizer figures out the most efficient way to execute the command.

  3. Execution: MySQL performs the operation and returns the result.

For example:

SELECT name, age FROM students WHERE age > 18;

Here’s what happens:

  • MySQL locates the students table.

  • It checks each row where age > 18.

  • It retrieves the name and age columns of matching records.

Common SQL Commands in MySQL

Here are some frequently used SQL commands with examples.

Create a Database

CREATE DATABASE school;

Use a Database

USE school;

Create a Table

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    city VARCHAR(50)
);

Insert Data

INSERT INTO students (name, age, city)
VALUES ('Riya', 19, 'Mumbai');

Retrieve Data

SELECT * FROM students;

Update Data

UPDATE students SET city = 'Delhi' WHERE id = 1;

Delete Data

DELETE FROM students WHERE id = 3;

Delete a Table

DROP TABLE students;

Advantages of SQL in MySQL

  1. Simple and Easy to Learn:
    SQL syntax is close to plain English. You can learn the basics quickly.

  2. Powerful and Flexible:
    With a few commands, you can handle large amounts of data efficiently.

  3. Portable:
    The same SQL queries work on most RDBMS systems (like MySQL, Oracle, or PostgreSQL).

  4. Supports Data Integrity:
    SQL ensures that the data entered into the database follows the rules you set (like no duplicates or nulls).

  5. Compatible with Many Languages:
    SQL works smoothly with programming languages like PHP, Java, and Python.

Example: SQL in Action

Let’s say you’re managing a school database. You create two tables — students and courses — and want to connect them.

CREATE TABLE courses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(100)
);

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    course_id INT,
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

Now, you can insert data:

INSERT INTO courses (course_name)
VALUES ('Science'), ('Maths'), ('English');

INSERT INTO students (name, age, course_id)
VALUES ('Meera', 18, 2), ('Pooja', 19, 1), ('Aditi', 20, 3);

To view each student’s course:

SELECT students.name, courses.course_name
FROM students
JOIN courses ON students.course_id = courses.id;

This shows how SQL connects data between tables and fetches meaningful results.

Summary of the Tutorial

  • SQL (Structured Query Language) is the backbone of MySQL.

  • It allows you to create, manage, and manipulate data.

  • SQL statements are divided into five categories: DDL, DML, DQL, DCL, and TCL.

  • MySQL processes SQL through parsing, optimization, and execution.

  • SQL’s simplicity and power make it essential for every database developer.

Practice Questions

  1. Write a SQL statement to create a database named school.

  2. Create a table students with columns id, name, age, and city. Make id the primary key and auto-incremented.

  3. Insert three sample student records into the students table.

  4. Write a SQL query to retrieve all columns and rows from the students table.

  5. Update the city of the student whose id is 2 to Delhi.

  6. Delete the record of the student whose name is 'Riya'.

  7. Create a table courses with columns id (primary key) and course_name.

  8. Insert at least three courses into the courses table.

  9. Write a query to fetch the names of all students along with the courses they are enrolled in (assume a course_id column in students).

  10. Drop the courses table from the database.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top