Database and Table Manipulation Statements in SQL
There are several types of statements in SQL that are used to manipulate databases and the data they contain. Some of the most commonly used are:
CREATE:creates a new database or table
ALTER:modifies the structure of a database or table
TRUNCATE:empties all data from table but table and structure remains
DROP:deletes an entire table or database
Here is an example of each of these statements:
-- create a new table called "employees" CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(255), salary DECIMAL(10, 2) ); -- add a new column called "address" to the "employees" table ALTER TABLE employees ADD COLUMN address VARCHAR(255); -- insert a new row of data into the "employees" table INSERT INTO employees (id, name, salary, address) VALUES (1, 'John Doe', 50000, '123 Main St'); -- truncate the table employee TRUNCATE TABLE employees; -- drop the "employees" table DROP TABLE employees;
These are the basic statements of SQL, you can use multiple JOINS, subqueries, with clause and UNION etc. to perform complex manipulation on data in database.
Here are a few best practices for working with database manipulation statements in SQL:
Be very careful when executing queries that modify the structure of a table, as some queries can cause loss of data which can be non-reversible.
If possible, first execute the queries in a test environment, before committing to executing them on a production database.
If the timeframe, size and storage of the database allows for it, make sure to have recent backups before executing queries that have the potential to break the database.
Before considering altering, creating or dropping a table or a database, do research if that is the only solution to your use case. In some cases, there might be better solutions such as adding additional relationships between tables or only adding columns to existing tables.