SQL (Structured Query Language) is a standardized language used to communicate with relational databases. It is used for defining, manipulating, querying, and managing data. The SQL syntax refers to the set of rules that defines how SQL statements are constructed.

Below is an overview of the most common SQL commands, categorized by their functionality.

1. SQL Statement Structure

SQL statements follow a general structure. An SQL statement can be simple or complex, but it typically follows this pattern:

sql
SELECT column1, column2 FROM table_name WHERE condition;

Each part of the SQL statement plays a specific role:

  • SELECT: Specifies which columns of the table you want to retrieve.
  • FROM: Identifies the table where the data is stored.
  • WHERE: Filters the records to meet specific conditions.

2. SQL Commands

SQL commands are categorized into several types based on their functionality.

2.1. Data Query Language (DQL)

DQL is used to retrieve data from a database.

SELECT: The most commonly used command in SQL for retrieving data from a table.

sql
SELECT column1, column2 FROM table_name WHERE condition;

2.2. Data Definition Language (DDL)

DDL commands are used to define and modify the structure of the database, such as creating, altering, or deleting tables.

CREATE: Used to create a new table, database, or other database objects.

sql
CREATE TABLE table_name ( column1 datatype, column2 datatype, ... );

ALTER: Used to modify an existing database object, such as a table or column.

sql
ALTER TABLE table_name ADD column_name datatype;

DROP: Used to delete an existing database object, such as a table or database.

sql
DROP TABLE table_name;

2.3. Data Manipulation Language (DML)

DML commands are used to manipulate data within tables.

INSERT: Adds new records to a table.

sql
INSERT INTO table_name (column1, column2) VALUES (value1, value2);

UPDATE: Modifies existing records in a table.

sql
UPDATE table_name SET column1 = value1 WHERE condition;

DELETE: Removes records from a table.

sql
DELETE FROM table_name WHERE condition;

2.4. Data Control Language (DCL)

DCL commands are used to control access to data within the database.

GRANT: Provides users with access privileges to database objects.

sql
GRANT SELECT, INSERT ON table_name TO user;

REVOKE: Removes previously granted privileges.

sql
REVOKE SELECT, INSERT ON table_name FROM user;

2.5. Transaction Control Language (TCL)

TCL commands are used to manage database transactions.

COMMIT: Saves all changes made in the current transaction.

sql
COMMIT;

ROLLBACK: Undoes all changes made in the current transaction.

sql
ROLLBACK;

SAVEPOINT: Sets a point within a transaction to which a rollback can occur.

sql
SAVEPOINT savepoint_name;

3. SQL Clauses

SQL clauses are used to perform additional filtering, sorting, and grouping of the data returned by queries.

WHERE: Filters the result set based on specified conditions.

sql
SELECT column1, column2 FROM table_name WHERE condition;

ORDER BY: Sorts the result set by one or more columns in ascending or descending order.

sql
SELECT column1, column2 FROM table_name ORDER BY column1 ASC, column2 DESC;

GROUP BY: Groups rows that have the same values in specified columns.

sql
SELECT column1, COUNT(*) FROM table_name GROUP BY column1;

HAVING: Filters groups based on a condition, typically used with GROUP BY.

sql
SELECT column1, COUNT(*) FROM table_name GROUP BY column1 HAVING COUNT(*) > 1;

LIMIT: Restricts the number of records returned by the query (used in databases like MySQL, PostgreSQL).

sql
SELECT column1, column2 FROM table_name LIMIT 10;

4. SQL Operators

SQL includes various operators that can be used within queries to define conditions.

4.1. Comparison Operators

=: Equal to

<>: Not equal to

>: Greater than

<: Less than

>=: Greater than or equal to

<=: Less than or equal to

4.2. Logical Operators

AND: Combines two or more conditions. All conditions must be true.

sql
SELECT * FROM table_name WHERE column1 = value1 AND column2 = value2;

OR: Combines two or more conditions. At least one condition must be true.

sql
SELECT * FROM table_name WHERE column1 = value1 OR column2 = value2;

NOT: Negates a condition.

sql
SELECT * FROM table_name WHERE NOT column1 = value;

4.3. Other Operators

BETWEEN: Selects values within a range.

sql
SELECT * FROM table_name WHERE column1 BETWEEN value1 AND value2;

LIKE: Searches for a specified pattern.

sql
SELECT * FROM table_name WHERE column1 LIKE 'pattern%';

IN: Checks if a value matches any value in a list.

sql
SELECT * FROM table_name WHERE column1 IN (value1, value2, value3);

IS NULL: Checks for NULL values.

sql
SELECT * FROM table_name WHERE column1 IS NULL;

Conclusion

SQL syntax is essential to communicating effectively with relational databases. The commands and clauses allow users to create, manage, and manipulate database structures and data. Understanding SQL syntax and its components, such as DDL, DML, DCL, and TCL, is fundamental for working with databases efficiently. Whether retrieving data or modifying the structure of a database, mastering SQL syntax is crucial for database management and querying.

line

Copyrights © 2024 letsupdateskills All rights reserved