Structured Query Language, or SQL, stands as the backbone of relational database management systems (RDBMS). SQL commands are the tools that empower developers, data analysts, and database administrators to interact seamlessly with databases. In this blog post, we’ll explore the most commonly used SQL commands that for database manipulation and querying.
The following SQL Statements are included in this article:
1. SELECT – Retrieving Data
2. INSERT – Adding Data
3. UPDATE – Modifying Data
4. DELETE – Removing Data
5. CREATE – Creating Tables and Databases
6. ALTER – Modifying Database Structure
7. DROP – Deleting Tables and Databases
8. ALTER TABLE – Modifying Table Structure
9. CREATE INDEX – Enhancing Query Performance
10. GRANT and REVOKE – Managing Permissions
Top 10 Common SQL Commands List
1. SELECT – Retrieving Data
The SELECT statement is the core of SQL, enabling the retrieval of data from one or more tables. Users can specify desired columns and apply filtering conditions using the WHERE clause. JOIN operations can also be added to merge data from multiple tables.
-- Example SELECT query SELECT column1, column2 FROM table WHERE condition; -- Example SELECT with JOIN SELECT t1.column1, t2.column2 FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id WHERE condition;
2. INSERT – Adding Data
The INSERT SQL command adds new records to a table. Values can be inserted explicitly or selected from another table.
-- Example INSERT query INSERT INTO table (column1, column2) VALUES (value1, value2);
3. UPDATE – Modifying Data
When it comes to modifying existing records we use the UPDATE SQL command. It allows the alteration of values in specific columns based on specified conditions.
-- Example UPDATE query UPDATE table SET column1 = value1, column2 = value2 WHERE condition;
4. DELETE – Removing Data
The DELETE command is utilized to remove records from a table based on certain conditions. Caution should be exercised, as this command permanently deletes data.
-- Example DELETE query DELETE FROM table WHERE condition;
5. CREATE – Creating Tables and Databases:
The CREATE command is used to create new databases, tables, and other database objects. It allows the definition of the database structure, including columns, data types, and constraints.
-- Example CREATE TABLE query CREATE TABLE table_name ( column1 datatype, column2 datatype, ... );
6. ALTER – Modifying Database Structure:
The ALTER command is used to modify the structure of an existing database or table. It facilitates actions such as adding, deleting, or modifying columns, as well as making changes to data types and constraints.
-- Example ALTER TABLE query ALTER TABLE table_name ADD column_name datatype;
7. DROP – Deleting Tables and Databases
The DROP command is the counterpart of CREATE and is used to delete databases, tables, and other database objects. Caution is advised, as it permanently removes the specified object and its data.
-- Example DROP TABLE query DROP TABLE table_name;
8. ALTER TABLE – Modifying Table Structure
The ALTER TABLE command allows for a variety of structural changes to an existing table. You can add or drop columns, modify data types, and even rename columns.
-- Example ALTER TABLE query ALTER TABLE table_name ADD column_name datatype;
9. CREATE INDEX – Enhancing Query Performance
The CREATE INDEX command is essential for optimizing database query performance. By creating an index on one or more columns of a table, you enable the database engine to retrieve data more efficiently, reducing query response times.
-- Example CREATE INDEX query CREATE INDEX index_name ON table_name (column1, column2);
10. GRANT and REVOKE – Managing Permissions
The GRANT and REVOKE commands allow us to control database security and access. Database Administrators will use this to control access. While not universally applicable to all RDBMS, these commands are commonly supported in major systems like MySQL, PostgreSQL, and Microsoft SQL Server.
-- Example GRANT query GRANT SELECT, INSERT ON table_name TO user_name; -- Example REVOKE query REVOKE DELETE ON table_name FROM user_name;
Conclusion
I may add more SQL queries and information to this post in future. If you think I can update this page with particular info to improve the post please do add a comment below. Also, feel free to check out my SQL Queries tag on this site for more tips on running SQL commands from a SQL DBA.
Leave a Reply