Welcome to our practical guide on data manipulation in MySQL and MariaDB! If you’re working with databases, knowing how to insert, update, and delete data is essential. In this tutorial, we’ll walk you through these fundamental operations, providing clear explanations and easy-to-follow examples.
Whether you’re a beginner just getting started with database management or an experienced developer looking to brush up on the basics, this guide will help you become proficient in manipulating data using MySQL and MariaDB.
Inserting Data into the Database
– Using the INSERT INTO Statement
When you want to add new data to a database table, you can use the INSERT INTO statement in MySQL or MariaDB. This statement allows you to specify the name of the table you want to add data to and the values you want to insert.
For example, if you have a table called `employees` with columns for `id`, `name`, and `salary`, you could use the following syntax to insert a new row:
INSERT INTO employees (name, salary) VALUES ('John Smith', 50000);This would insert a new row into the `employees` table with a unique identifier for the `id` column (assuming it is set up as an auto-incrementing primary key), a value of ‘John Smith’ for the `name` column, and a value of 50000 for the `salary` column.
You can also insert multiple rows at once by separating each set of values with commas:
INSERT INTO employees (name, salary) VALUES ('Jane Doe', 60000), ('Bob Johnson', 55000);This would insert two new rows into the `employees` table – one with a name of ‘Jane Doe’ and salary of 60000, and another with a name of ‘Bob Johnson’ and salary of 55000.
– Specifying Column Names in INSERT INTO Statement
When inserting data into a MySQL or MariaDB database, you can specify the column names along with their corresponding values in the INSERT INTO statement. This is useful when you want to insert data only into specific columns or in a specific order.
To specify column names, simply list them inside parentheses after the table name in the INSERT INTO statement. Then, separate each column name with a comma followed by its corresponding value. For example:
INSERT INTO my_table (column1, column2, column3) VALUES ('value1', 'value2', 'value3');In this example, we are inserting values ‘value1’, ‘value2’, and ‘value3’ into columns ‘column1’, ‘column2’, and ‘column3’ respectively.
It is important to note that if you don’t specify all the columns in the table in your INSERT INTO statement, the remaining columns will either contain default values or NULL values if no default value has been set for those columns.
– Inserting Multiple Rows with a Single INSERT INTO Statement
When working with MySQL or MariaDB databases, inserting multiple rows into a table can be done efficiently using a single INSERT INTO statement. This method helps save time and resources when inserting large amounts of data.
To insert multiple rows using a single INSERT INTO statement, you can specify the values for each row in separate sets of parentheses, separated by commas. For example:
INSERT INTO table_name (column1, column2)
VALUES
(value1_1, value1_2),
(value2_1, value2_2),
(value3_1, value3_2);In this example, `table_name` is the name of the table you want to insert data into, and `(column1, column2)` specifies the columns you want to insert data into. The `VALUES` keyword is followed by sets of parentheses containing the values for each row that you want to insert.
It’s important to ensure that the number of values specified in each set of parentheses matches the number of columns specified in the first set of parentheses. Otherwise, you will get an error.
By using this method, you can quickly and efficiently insert multiple rows into your MySQL or MariaDB database with just one INSERT INTO statement.
– Inserting Data into a Table using SELECT Statement
Inserting data into a MySQL or MariaDB database table using the SELECT statement can be a useful technique when working with large datasets. This feature allows you to quickly and easily copy data from one table to another or insert only specific columns or rows of data.
The syntax for inserting data into a table using a `SELECT` statement is as follows:
INSERT INTO table_name (column1, column2, column3)
SELECT column1, column2, column3
FROM another_table_name
WHERE condition;In this syntax, `table_name` is the name of the table where you want to insert the data, and `(column1, column2, column3)` are the names of the columns in that table where you want to insert the corresponding data.
The `SELECT` statement specifies which columns and rows of data to insert into `table_name`. The `FROM` clause indicates which table should be used as a source for this operation. The optional `WHERE` clause is used to specify any conditions that must be met in order for a row to be inserted into the target table.
For example, assume we have two tables called “products” and “new_products”. To copy all product information from “products” to “new_products”, we could use:
INSERT INTO new_products (product_id, product_name, price)
SELECT product_id, product_name, price
FROM products;This would copy all rows from the “products” table into the new “new_products” table. Note that we specified which columns we wanted to insert into using `(product_id, product_name, price)`, and matched them up with their respective source columns in the SELECT statement.
Updating Data in the Database
– Using UPDATE Statement with WHERE Clause
To update data in a MySQL or MariaDB database, you can use the UPDATE statement with a WHERE clause. The WHERE clause is used to specify which rows should be updated.
For example, if you want to update the price of a product with a specific ID, you could use the following SQL query:
UPDATE products SET price=20.99 WHERE id=123;This query will update the price of the product with an ID of 123 to 20.99.
You can also update multiple columns with a single UPDATE statement by separating them with commas. For example:
UPDATE products SET price=20.99, quantity=10 WHERE id=123;This query will update both the price and quantity columns of the product with an ID of 123.
In addition, you can update multiple rows with a single UPDATE statement by using the IN keyword and providing a list of values in parentheses. For example:
UPDATE products SET price=19.99 WHERE id IN (123, 456);This query will update the price of both products with IDs of 123 and 456 to 19.99.
– Updating Multiple Columns with a Single UPDATE Statement
To update multiple columns with a single UPDATE statement in MySQL or MariaDB, you can simply list the columns and their new values separated by commas after the SET keyword. For example, if you want to update the ‘name’ and ‘age’ columns of a table named ’employees’, you can use the following syntax:
UPDATE employees SET name = 'John Doe', age = 30 WHERE id = 1;This statement will update the ‘name’ column to ‘John Doe’ and the ‘age’ column to 30 for the row where the ‘id’ column is equal to 1.
Using this method, you can update as many columns as needed in a single query, which can save time and reduce complexity compared to updating each column individually. It’s important to remember to include a WHERE clause in your UPDATE statement to ensure that only the desired rows are updated.
– Updating multiple rows with a single UPDATE Statement
Updating multiple rows with a single UPDATE statement in a MySQL or MariaDB database is an efficient way to make bulk changes to your data. Instead of having to write individual update statements for each row you want to update, you can use a single statement that updates multiple rows at once.
To do this, you need to specify the conditions that identify the rows you want to update using the WHERE clause. For example, if you want to update all customers who have an email address ending in ‘@gmail.com’, you would use the following statement:
UPDATE customers
SET status = 'active'
WHERE email LIKE '%@gmail.com';This statement will update the ‘status’ column for all rows in the ‘customers’ table where the email address ends in ‘@gmail.com’. You can also update multiple columns with a single UPDATE statement by separating each column and its new value with a comma. Here’s an example:
UPDATE customers
SET status = 'active', last_login = NOW()
WHERE email LIKE '%@gmail.com';In this case, we’re updating both the ‘status’ and ‘last_login’ columns for all rows where the email address ends in ‘@gmail.com’. The NOW() function is used to set the ‘last_login’ column to the current date and time.
Using an UPDATE statement to update multiple rows at once can save time and improve efficiency when working with large datasets.
Deleting Data from the Database
– Using DELETE Statement with WHERE Clause
When working with databases, it is often necessary to remove data from tables. The DELETE statement is used to delete one or more rows from a table based on a specified condition or criteria using the WHERE clause. The basic syntax of the DELETE statement is as follows:
DELETE FROM table_name WHERE condition;Here, `table_name` is the name of the table from which you want to delete rows, and `condition` specifies the criteria that must be met for rows to be deleted. For example, if you want to delete all records from a table where the age column equals 30, you would use:
DELETE FROM users WHERE age = 30;It’s important to note that if you don’t specify a condition in the `WHERE` clause, all records in the specified table will be deleted. Therefore, it’s recommended to always include a condition when using `DELETE`.
Using the `DELETE` statement can also have an impact on related tables if foreign keys are defined between them. In such cases, you may need to use other statements like CASCADE or SET NULL to handle these relationships.
– Deleting All Rows from a Table using TRUNCATE TABLE statement
To delete all rows from a table in a MySQL or MariaDB database, you can use the TRUNCATE TABLE statement. This statement is used to remove all data from a table while keeping the structure of the table intact.
To use TRUNCATE TABLE, you need to specify the name of the table that you want to delete all data from. The syntax for this statement is as follows:
TRUNCATE TABLE table_name;Where `table_name` is the name of the table that you want to delete all data from.
It’s worth noting that when you use TRUNCATE TABLE, it’s an immediate operation and doesn’t require a rollback. This means that once you execute this command, there’s no undoing it, so be sure that you really want to delete all data from the specified table.
