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.
SQL statements follow a general structure. An SQL statement can be simple or complex, but it typically follows this pattern:
sqlSELECT column1, column2 FROM table_name WHERE condition;
Each part of the SQL statement plays a specific role:
SQL commands are categorized into several types based on their functionality.
DQL is used to retrieve data from a database.
SELECT: The most commonly used command in SQL for retrieving data from a table.
sqlSELECT column1, column2 FROM table_name WHERE condition;
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.
sqlCREATE TABLE table_name ( column1 datatype, column2 datatype, ... );
ALTER: Used to modify an existing database object, such as a table or column.
sqlALTER TABLE table_name ADD column_name datatype;
DROP: Used to delete an existing database object, such as a table or database.
sqlDROP TABLE table_name;
DML commands are used to manipulate data within tables.
INSERT: Adds new records to a table.
sqlINSERT INTO table_name (column1, column2) VALUES (value1, value2);
UPDATE: Modifies existing records in a table.
sqlUPDATE table_name SET column1 = value1 WHERE condition;
DELETE: Removes records from a table.
sqlDELETE FROM table_name WHERE condition;
DCL commands are used to control access to data within the database.
GRANT: Provides users with access privileges to database objects.
sqlGRANT SELECT, INSERT ON table_name TO user;
REVOKE: Removes previously granted privileges.
sqlREVOKE SELECT, INSERT ON table_name FROM user;
TCL commands are used to manage database transactions.
COMMIT: Saves all changes made in the current transaction.
sqlCOMMIT;
ROLLBACK: Undoes all changes made in the current transaction.
sqlROLLBACK;
SAVEPOINT: Sets a point within a transaction to which a rollback can occur.
sqlSAVEPOINT savepoint_name;
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.
sqlSELECT column1, column2 FROM table_name WHERE condition;
ORDER BY: Sorts the result set by one or more columns in ascending or descending order.
sqlSELECT column1, column2 FROM table_name ORDER BY column1 ASC, column2 DESC;
GROUP BY: Groups rows that have the same values in specified columns.
sqlSELECT column1, COUNT(*) FROM table_name GROUP BY column1;
HAVING: Filters groups based on a condition, typically used with GROUP BY.
sqlSELECT 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).
sqlSELECT column1, column2 FROM table_name LIMIT 10;
SQL includes various operators that can be used within queries to define conditions.
=: Equal to
<>: Not equal to
>: Greater than
<: Less than
>=: Greater than or equal to
<=: Less than or equal to
AND: Combines two or more conditions. All conditions must be true.
sqlSELECT * FROM table_name WHERE column1 = value1 AND column2 = value2;
OR: Combines two or more conditions. At least one condition must be true.
sqlSELECT * FROM table_name WHERE column1 = value1 OR column2 = value2;
NOT: Negates a condition.
sqlSELECT * FROM table_name WHERE NOT column1 = value;
BETWEEN: Selects values within a range.
sqlSELECT * FROM table_name WHERE column1 BETWEEN value1 AND value2;
LIKE: Searches for a specified pattern.
sqlSELECT * FROM table_name WHERE column1 LIKE 'pattern%';
IN: Checks if a value matches any value in a list.
sqlSELECT * FROM table_name WHERE column1 IN (value1, value2, value3);
IS NULL: Checks for NULL values.
sqlSELECT * FROM table_name WHERE column1 IS NULL;
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.
Copyrights © 2024 letsupdateskills All rights reserved