Basic SQL Queries in MariaDB

When working with databases, mastering SQL commands is essential for efficient data manipulation. Here’s a comprehensive guide on the basic SQL commands—SELECT, INSERT, UPDATE, and DELETE—that will empower you to interact with your MariaDB database seamlessly.

SELECT - Retrieving Data

The SELECT statement is fundamental in SQL as it allows you to retrieve data from one or more tables. The syntax for a basic SELECT query is:

SELECT column1, column2 FROM table_name;

Example:

Suppose you have a table named employees. To fetch the names and salaries of all employees, you could write:

SELECT name, salary FROM employees;

If you want to retrieve all columns from the employees table, use the asterisk (*) wildcard:

SELECT * FROM employees;

Filtering Data

To filter results and retrieve specific rows, you can use the WHERE clause:

SELECT * FROM employees WHERE department = 'Sales';

This query retrieves all employees who belong to the Sales department. You can use various operators (like =, >, <, etc.) and combine multiple conditions with AND and OR:

SELECT * FROM employees WHERE department = 'Sales' AND salary > 50000;

Sorting Results

To sort your result set, use the ORDER BY clause. By default, it sorts in ascending order:

SELECT name, salary FROM employees ORDER BY salary;

To sort in descending order, you can specify DESC:

SELECT name, salary FROM employees ORDER BY salary DESC;

Limiting Results

You can control the number of rows returned by using the LIMIT clause:

SELECT * FROM employees LIMIT 5;

This query will return only the first five rows from the employees table.

INSERT - Adding Data

The INSERT statement is used to add new records to a table. The syntax is:

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

Example:

To add a new employee to the employees table, you would write:

INSERT INTO employees (name, salary, department) VALUES ('John Doe', 50000, 'Sales');

Inserting Multiple Rows

Inserting multiple records in a single statement is possible, which can enhance performance:

INSERT INTO employees (name, salary, department) VALUES 
('Jane Smith', 60000, 'Marketing'), 
('Bob Brown', 70000, 'Engineering');

UPDATE - Modifying Data

The UPDATE statement allows you to modify existing records in a table. Its syntax is:

UPDATE table_name SET column1 = value1 WHERE condition;

Example:

If you need to update John Doe's salary in the employees table, you would execute:

UPDATE employees SET salary = 55000 WHERE name = 'John Doe';

Updating Multiple Columns

You can update multiple columns in a single query:

UPDATE employees 
SET salary = 65000, department = 'Management'
WHERE name = 'Jane Smith';

Caution: The Importance of WHERE

Always use the WHERE clause carefully to avoid unintended updates. For example, executing the following statement would increase the salary of all employees:

UPDATE employees SET salary = salary * 1.10; -- This gives a 10% raise to all employees.

DELETE - Removing Data

The DELETE statement is used to remove records from a table. The syntax is:

DELETE FROM table_name WHERE condition;

Example:

To delete John Doe from the employees table, you would use:

DELETE FROM employees WHERE name = 'John Doe';

Deleting All Rows

If you want to remove all records from the table and reset the auto-increment value, you can use:

DELETE FROM employees; -- Removes all records but keeps the table structure

However, if you wish to completely remove the structure as well, a DROP TABLE statement would be appropriate.

Caution: The Importance of WHERE

Like the UPDATE command, always include a WHERE clause with the DELETE command unless you intend to remove all records. Omitting the WHERE clause would cause all entries in the table to be deleted:

DELETE FROM employees; -- Dangerous if not intended

Combining SQL Commands

MariaDB allows combining multiple SQL commands to perform complex operations, such as using subqueries.

Example of Subquery with SELECT

You can use the result from one query as an input to another:

SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

Transaction Control

To ensure your operations are atomic, it’s crucial to utilize transactions when performing multiple data manipulation commands. In MariaDB, you can use:

START TRANSACTION;

UPDATE employees SET salary = salary * 1.1 WHERE department = 'Sales';
DELETE FROM employees WHERE name = 'John Doe';

COMMIT;  -- or ROLLBACK; to revert changes if needed

Conclusion

Mastering these basic SQL commands—SELECT, INSERT, UPDATE, and DELETE—is essential for efficient data management in MariaDB. Practice these commands to build your confidence and proficiency in handling database operations.

Remember, always support your SQL commands with careful filtering, as it helps maintain data integrity and prevents unwanted changes to your database. With these fundamental commands at your fingertips, you’re well on your way to becoming a proficient user of MariaDB! Happy querying!

Databases - MariaDB