MySQL SQL Syntax - codemaga

In this tutorial, we will introduce you to MySQL’s SQL syntax which helps you understand the details of SQL statements.

What is SQL?

SQL, or Structured Query Language, serves as the universal language for managing and manipulating relational databases.

It enables users to interact with databases by performing various operations such as querying, inserting, updating, and deleting data.

MySQL syntax vs SQL Standard

MySQL, while widely used, differs from the SQL (Structured Query Language) standard in several key aspects.

The SQL standard defines a universal set of rules for relational databases and fundamental database operations and commands.

MySQL implements a subset of the SQL standard but includes unique extensions & features like stored procedures and triggers.

Understanding these differences is crucial for developers to ensure compatibility and maintainability across database systems.

MySQL Syntax Basics: How to Use SQL?

MySQL, like many other relational database management systems, employs a declarative language, which closely resembles natural language.

Each SQL statement starts with a verb denoting the action, such as SELECT, INSERT, UPDATE, or DELETE, followed by the subject and predicate.

Here’s a SQL statement example:

SQL

SELECT 
	name, email 
FROM 
	users
WHERE
    YEAR(created_at) = 2000;

It reads like a regular sentence.

Retrieve the names of users created in 2000. The SELECT, FROM, and WHERE are clauses in the SQL statement. Some clauses, like SELECT and FROM, are mandatory, while others, like WHERE, are optional.

SQL consists of multiple commands, each typically ending with a semicolon.

Most common SQL Commands

Here are some of the essential and most common SQL commands and their functions.

Command	Function
SELECT	Retrieves data from a database.
UPDATE	Modifies existing data in a database.
DELETE	Removes data from a database.
INSERT INTO	Adds new data into a database.
CREATE DATABASE	Establishes a new database.
ALTER DATABASE	Adjusts the structure of a database.
CREATE TABLE	Generates a new table.
ALTER TABLE	Modifies an existing table’s structure.
DROP TABLE	Deletes a table from the database.
CREATE INDEX	Establishes an index to expedite searches.
DROP INDEX	Removes an index from the database.

Each command comprises tokens literals, keywords, identifiers, or expressions separated by spaces, tabs, or newlines.

Literals

Explicit values are known as constants. SQL provides string, numeric, and binary literals. For example:

SQL

'John'
'1990-01-01'
'50'

Numeric literals can be integers, decimals, or scientific notation:

SQL

200
-5
6.0221415E23

Keywords

SQL has reserved words like SELECT, INSERT, UPDATE, DELETE, and DROP. Identifiers:

ℹ️ SQL keywords are case-insensitive
Meaning select is the same as SELECT
But it’s always recommended to use uppercase for keywords for readability.

Identifiers

Refer to specific database objects like tables or columns. SQL is case-insensitive regarding keywords and identifiers.

To enhance readability, SQL commands will use uppercase for keywords and lowercase for identifiers throughout the tutorials.

SQL

'John'
'1990-01-01'
'50'

Comments

Used to document SQL statements. Database engines ignore characters in comments. Comments start with two hyphens (–) and for multi-line comments, use the C-style notation (/**/). Example:

SQL

/* get user_id and name */
SELECT 
    user_id, name
FROM
    users
WHERE
    role = 'subscriber'; -- Subscribers

Semicolons in SQL

The semicolon serves as the delimiter for SQL statements, marking the end of each command.

While it’s not always required, including a semicolon enhances readability and ensures compatibility across different database systems.

Summary

Introduction to MySQL’s SQL syntax for understanding SQL statements
SQL (Structured Query Language) as the universal language for managing relational databases
Differences between MySQL syntax and the SQL standard
- MySQL implements a subset of the SQL standard
- Unique extensions like stored procedures and triggers
MySQL syntax basics:
- Declarative language resembling natural language
- Structure of SQL statements: verb, subject, and predicate
- Example SQL statement for retrieving user names created in 2000
Key components of SQL:
- Common commands such as SELECT, UPDATE, INSERT
- Tokens like literals, keywords, and identifiers
Usage of uppercase for keywords and lowercase for identifiers for readability
Documentation of SQL statements using comments
- Single-line comments with two hyphens (–)
- Multi-line comments with C-style notation (/* */)
Semicolons as statement delimiters for readability and compatibility across database systems