How to Create a Database and a User in MySQL

Updating data in databases is a critical task when working with MySQL. It involves modifying the values of existing records in a table. Updates can range from modifying fields in a group of rows (or even all rows in a table) to adjusting a specific field in a single row. Understanding the syntax for updating data is essential for effectively working with both local and cloud databases. The key command for modifying records in a MySQL database table is UPDATE. Updates occur sequentially, from the first row to the last. Depending on the type of update, there are two syntax options for the UPDATE statement in MySQL. Syntax for Updating a Single Table UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET assignment_list WHERE where_condition ORDER BY ... LIMIT row_count; Parameters: Required: SET assignment_list: Specifies which columns to modify and how (assignment_list is the list of columns and their new values). Optional: LOW_PRIORITY: If specified, the UPDATE is delayed until no other user is reading data from the table. IGNORE: Ensures the UPDATE continues even if errors occur. Rows with duplicate values in unique key columns are not updated. WHERE where_condition: Specifies the conditions for selecting rows to update. If omitted, all rows in the table will be updated. ORDER BY: Determines the order in which rows are updated. LIMIT row_count: Limits the number of rows updated (row_count specifies the number of rows). This count applies to rows matching the WHERE condition, regardless of whether they are actually modified. Syntax for Updating Multiple Tables UPDATE [LOW_PRIORITY] [IGNORE] table_references SET assignment_list WHERE where_condition; Parameters: table_references: Specifies the tables to update. Changes are applied as defined in assignment_list. ORDER BY and LIMIT are not allowed when updating multiple tables. Other optional parameters (LOW_PRIORITY, IGNORE, WHERE) behave the same as for a single-table update. Note that when updating multiple tables, there is no guarantee that updates will occur in a specific order. Creating a Test Database Let’s create a database for a bookstore that sells rare and antique books from around the world. The table will have four tables: author, genre, book, and sales. CREATE TABLE author ( id INT PRIMARY KEY AUTO_INCREMENT, author_name VARCHAR(50) NOT NULL ); INSERT INTO author (author_name) VALUES ('Leo Tolstoy'), ('Franz Kafka'), ('Nikolai Gogol'), ('William Shakespeare'), ('Homer'); CREATE TABLE genre ( id INT PRIMARY KEY AUTO_INCREMENT, genre_name VARCHAR(30) NOT NULL ); INSERT INTO genre (genre_name) VALUES ('Realist novel'), ('Dystopian novel'), ('Picaresque novel'), ('Epic poetry'); CREATE TABLE book ( book_id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(50), author_id INT NOT NULL, genre_id INT, price DECIMAL(8,2) NOT NULL, amount INT DEFAULT 0, FOREIGN KEY (author_id) REFERENCES author (id), FOREIGN KEY (genre_id) REFERENCES genre (id) ); INSERT INTO book (title, author_id, genre_id, price, amount) VALUES ('Anna Karenina', 1, 1, 650.00, 15), ('The Castle', 2, 2, 570.20, 6), ('Dead Souls', 3, 3, 480.00, 2), ('Iliad', 5, 4, 518.99, 4), ('Odyssey', 5, 4, 518.99, 7); CREATE TABLE sales ( id INT PRIMARY KEY AUTO_INCREMENT, book_id INT NOT NULL, count INT NOT NULL, cost DECIMAL(8,2) NOT NULL, FOREIGN KEY (book_id) REFERENCES book (book_id) ); We will get the following tables. Table: book Here, we have the columns: book_id: Unique book identifier. title: The book's title. author_id: Author identifier (foreign key to author). genre_id: Genre identifier (foreign key to genre). price: Price of the book per unit. amount: Number of books in stock. The genre table will have the following content:  id genre_name 1 Realist novel 2 Dystopian novel 3 Picaresque novel 4 Epic poetry And our author table will look like this: id author_name 1 Leo Tolstoy 2 Franz Kafka 3 Nikolai Gogol 4 William Shakespeare 5 Homer Table: sales The columns here are: id: Unique identifier for the transaction. book_id: Unique book identifier (foreign key to book). count: Number of books sold. cost: Total cost of the purchased books. Data Update Operations Now, having created a sample database, we will demonstrate the execution of various data update operations using the UPDATE statement and other commands in MySQL. 1. Updating All Rows If we omit the WHERE clause in an UPDATE statement, all rows in the table will be updated. For example, suppose there is a promotion in a bookstore where all books are priced at a fixed rate of 500. The query would look like this: UPDATE bookSET price = 500; Resulting Table: If we try to assign a value that is already in a column, MySQL will not update it. If we want to assign a NULL value to a column defined as NOT NULL will, the query will return an error:  Column 'name_column' cannot be null Using the IGNORE parameter forces the value to default: 0 for numeric types, "" for string types, default dates (e.g., 0000 for YEAR, 0000-00-00 00:00:00 for DATETIME). 2. Updating Rows with a Condition Updating all rows is rare; typically, updates are performed based on specific conditions. For instance, to apply a discount on books with fewer than 5 copies in stock: UPDATE book SET price = 300WHERE amount < 5; Resulting Table: 3. Updating Values Using Expressions Columns can be updated using expressions instead of static values. For example, we can apply a 15% discount on Russian classics (author IDs 1 and 3): UPDATE book SET price = price * 0.85WHERE author_id IN (1, 3); Resulting Table: Updates are executed from left to right. For example, the query below increments the amount by 1 and then doubles it: UPDATE book SET amount = amount + 1, amount = amount * 2; Resulting Table: 4. Updating with DEFAULT We can update column values to their default values (DEFAULT), which are defined during the creation or modification of the table. To find out the default values used in our table, we can execute the following query: DESC book; Table Structure: Next, we reset the values of the amount column to its default value. Since the default value for amount is 0, all rows will now have amount set to 0: UPDATE book SET amount = DEFAULT; Resulting Table: 5. Updating Multiple Columns We can update multiple columns in a single query. For example, let's change the price and amount values for rows where book_id < 4: UPDATE book SET price = price * 0.9, amount = amount - 1 WHERE book_id < 4; Resulting Table: 6. Using LIMIT The LIMIT clause allows us to restrict the number of rows to be updated. For instance, we update only the first row where genre_id = 4: UPDATE book SET price = 100 WHERE genre_id = 4 LIMIT 1; The table contains two rows with genre_id equal to 4, but since we specified LIMIT 1, only one will be updated. Resulting Table: Note: The LIMIT N parameter does not guarantee that exactly N rows will be updated—it processes the first N rows matching the WHERE condition, regardless of whether the rows are updated or not. 7. Updating Multiple Tables In MySQL, it is possible to update multiple tables simultaneously. For example, update the amount in the book table and set the author_name to "-" in the author table for specific conditions: UPDATE book, author SET amount = amount + 3, author.author_name = '-' WHERE book.book_id = 4 AND author.id = 4; Resulting book Table: While the author table will look like this: id author_name 1 Leo Tolstoy 2 Franz Kafka 3 Nikolai Gogol 4 William Shakespeare 5 Homer 8. Updating Tables with a Join (INNER JOIN) While performing updates, we can also join tables using the INNER JOIN command. UPDATE book b INNER JOIN author a ON b.author_id = a.idSET b.title = CONCAT(b.title, ' (', a.author_name,')'); Specifying INNER is not mandatory, as this type of join is used by default. We can rewrite the query as follows and get the same result: UPDATE book, author a SET b.title = CONCAT(b.title, ' (', a.author_name,')')WHERE b.author_id = a.id; 9. Updating Tables with a Join (LEFT JOIN) We can also use a LEFT JOIN. In this case, we must specify LEFT JOIN in the query.  For example, we can update the stock quantity of books after a purchase. Let's add two rows to the sales table: INSERT INTO sales (book_id, count, cost) VALUES (1, 3, (SELECT price FROM book WHERE book_id = 1)*3), (3, 1, (SELECT price FROM book WHERE book_id = 3)*1); The store sold 3 copies of 'Anna Karenina' and 1 copy of 'Dead Souls'. Let's execute the query: UPDATE book LEFT JOIN sales on book.book_id = sales.book_idSET amount = amount - countWHERE sales.book_id is not NULL; We can see that now we have less copies of these books: If we try not to use LEFT JOIN, we will encounter the error: Out of range value for column 'amount' at row 3 This happens because amount cannot be negative. Alternatively, if we add IGNORE, the result will be: As we can see, we reduced the quantity in all rows by three books, which is not what we wanted. 10. Updating with CASE, IF, IFNULL, COALESCE When updating a table, it is also possible to use conditional operators such as CASE, IF, and others.The CASE function evaluates a set of conditions and, depending on the result, returns one of the possible outcomes. The syntax for using CASE and WHEN in an UPDATE statement in MySQL is as follows: UPDATE book SET price = CASE genre_id WHEN 1 THEN 100 WHEN 2 THEN 150 ELSE price END; In this case, if the book has genre 1, we set the price to 100, and if the genre is 2, the price is set to 150. The IF function returns one of two values depending on the result of a conditional expression. If the book has genre 4, we decrease its price by 200; otherwise, we leave the price unchanged. UPDATE bookSET price = IF (genre_id = 4, price-200, price); The result: The IFNULL function checks the value of an expression – if it is NULL, a specified value is returned; otherwise, the expression itself is returned. Let's assume that one of the amount values is NULL: Let's check all the values in the amount column, and if any NULL values are found, we will replace them with 0: UPDATE bookSET amount = IFNULL(amount, 0); Resulting Table: The COALESCE function is quite similar to IFNULL. The main difference is that this function can accept multiple values (two or more). Like IFNULL, it returns the first value that is not NULL. To see how this works, let's create a table like this: id col1 col2 col3 col4 1   val12 val13 val14 2     val23 val24 3       val34 And run the query: UPDATE test_tableSET col4 = COALESCE(col1, col2, col3, 'no value'); We will get: id col1 col2 col3 col4 1   val12 val13 val12 2     val23 val23 3       no value 11. Updating with Sorting Sorting can help when updating a field with a unique key. If we want to shift our id values by 1, updating the first row would result in two rows having id = 2, which will cause an error. However, if we add ORDER BY and start updating from the end, the query will execute successfully: UPDATE bookSET book_id=book_id+1  We will get: 12. Updating Based on Data from Other Tables In MySQL, when working with UPDATE, it is possible to use nested SELECT and FROM commands in the WHERE condition. In the following example, we first retrieve the id of the 'Epic poetry' genre, then use the retrieved value to select the rows for updating the table. UPDATE book SET amount = 0 WHERE genre_id = ( SELECT id FROM genre Where genre_name = 'Epic poetry' ); Alternatively, we can select the values that need to be changed using the query: UPDATE book SET price = ( SELECT MIN (cost) FROM sales) WHERE amount < 5; We are updating the price values of all books whose stock quantity is less than 5, setting the price to the minimum selling amount. The minimum selling amount is 480: It is not possible to update the table by selecting values from the same table in a subquery. However, there is a trick we can use – we can join the table with itself: UPDATE book AS book_1 INNER JOIN( SELECT genre_id, MIN(amount) AS min_amount FROM book GROUP BY genre_id ) AS book_2 ON book_1.genre_id = book_2.genre_id SET book_1.amount = book_2.min_amount; In this case, the subquery creates a temporary table for the join and closes it before the UPDATE begins execution. The subquery finds the minimum quantity of books for each genre, which is then used to update the amount column. In our table, only genre 4 has more than one row. The values in both rows should be replaced with the minimum value for that genre, which is 4. We will get: Another option is using SELECT FROM SELECT: UPDATE book AS book_1 SET book_1.price = (SELECT MIN(price) AS min_price FROM ( SELECT price FROM book) as book_2); In this case, a temporary table is also created. However, only a single value is assigned to all rows. Conclusion We have covered the features of using the UPDATE statement in MySQL in as much detail as possible and covered simple scenarios with practical examples.

Duplicate entries may inadvertently accumulate in databases, which are crucial for storing vast amounts of structured data. These duplicates could show up for a number of reasons, including system errors, data migration mistakes, or repeated user submissions. A database with duplicate entries may experience irregularities, sluggish performance, and erroneous reporting. Using the GROUP BY and HAVING clauses, as well as a different strategy that makes use of temporary tables, we will discuss two efficient methods for locating and removing duplicate rows in MySQL. With these techniques, you can be sure that your data will always be accurate, clean, and well-organized. Database duplication in MySQL tables can clog your data, resulting in inaccurate analytics and needless storage. Locating and eliminating them is a crucial database upkeep task. This is a detailed guide on how to identify and remove duplicate rows. If two or more columns in a row have identical values, it is called a duplicate row. For instance, rows that have the same values in both the userName and userEmail columns of a userDetails table may be considered duplicates. Benefits of Removing Duplicate Data The advantage of eliminating duplicate data is that duplicate entries can slow down query performance, take up extra storage space, and produce misleading results in reports and analytics. The accuracy and speed of data processing are improved by keeping databases clean, which is particularly crucial for databases that are used for critical applications or are expanding. Requirements Prior to starting, make sure you have access to a MySQL database or have MySQL installed on your computer. The fundamentals of general database concepts and SQL queries. One can execute SQL commands by having access to a MySQL client or command-line interface. To gain practical experience, you can create a sample database and table that contains duplicate records so that you can test and comprehend the techniques for eliminating them. Creating a Test Database Launch the MySQL command-line tool to create a Test Database. mysql -u your_username -p Create a new database called test_dev_db after entering your MySQL credentials. CREATE DATABASE test_dev_db; Then, switch to this newly created database:. USE test_dev_db; Add several rows, including duplicates, to the userDetails table after creating it with the CREATE TABLE query and INSERT query below. CREATE TABLE userDetails ( userId INT AUTO_INCREMENT PRIMARY KEY, userName VARCHAR(100), userEmail VARCHAR(100) ); INSERT INTO userDetails (userName, userEmail) VALUES (‘Alisha’, ‘[email protected]’), (‘Bobita, ‘[email protected]’), (‘Alisha’, ‘[email protected]’), (‘Alisha’, ‘[email protected]’); Using GROUP BY and HAVING to Locate Duplicates Grouping rows according to duplicate-defining columns and using HAVING to filter groups with more than one record is the simplest method for finding duplicates. Now that you have duplicate data, you can use SQL to determine which rows contain duplicate entries. MySQL's GROUP BY and HAVING clauses make this process easier by enabling you to count instances of each distinct value. An example of a table structure is the userDetails table, which contains the columns userId, userName, and userEmail. The GROUP BY clause is useful for counting occurrences and identifying duplicates because it groups records according to specified column values. The HAVING clause  allows duplicate entries in groups formed by GROUP BY to be found by combining groups based on specific criteria. Table userDetails Structure userId userName userEmail 1 Alisha  [email protected] 2 Bobita  [email protected] 3 Alisha  [email protected] 4 Alisha  [email protected] In the above table userDetails, records with identical userName and userEmail values are considered duplicates. Finding Duplicates Query for find the duplicate entries: SELECT userName, userEmail, COUNT(*) as count FROM userDetails GROUP BY userName, userEmail HAVING count > 1; Rows are grouped by userName and userEmail in the aforementioned query, which also counts entries within the group and eliminates groups with a single entry (no duplicates). Explanation: SELECT userName, userEmail, COUNT(*) as count: Retrieves the count of each combination of username and userEmail, as well as their unique values. GROUP BY userName, userEmail: Records are grouped by username and user email using the GROUP BY userName, userEmail function COUNT (*): Tallies the rows in each set. HAVING occurrences > 1: Recurring entries are identified by displaying only groups with more than one record. This query will return groups of duplicate records based on the selected columns. userName userEmail count Alisha [email protected] 3 Eliminating Duplicate Rows After finding duplicates, you may need to eliminate some records while keeping the unique ones. Joining the table to itself and removing rows with higher userId values is one effective method that preserves the lowest userId for every duplicate. Use the SQL query to remove duplicate rows while keeping the lowest userId entry. DELETE u1 FROM userDetails u1 JOIN userDetails u2 ON u1. userName = u2. userName AND u1. userEmail = u2. userEmail AND u1. userId > u2. userId ; Explanation: u1 & u2: Aliases for the userDetails table to ease a self-join. ON u1. userName = u2. userName AND u1. userEmail = u2. userEmail: Matches rows with identical userName, userEmail. AND u1. userId > u2. userId: Removes rows with higher userId values, keeping only the row with the smallest userId. Because this action cannot be undone, it is advised that you backup your data before beginning the deletion procedure. Confirming Duplicate Removal To confirm that all duplicates have been removed, repeat the Step 1 identification query. SELECT userName, userEmail, COUNT(*) as count FROM userDetails GROUP BY userName, userEmail HAVING count > 1; All duplicates have been successfully eliminated if this query yields no rows. Benefits of Employing GROUP BY and HAVING The GROUP BY and HAVING clauses serve as vital instruments for the aggregation of data and the filtration of grouped outcomes. These functionalities are especially useful for detecting and handling duplicate entries or for condensing extensive datasets. Below are the primary benefits of employing these clauses. Efficient Identification of Duplicates Data Aggregation and Summarization Filtering Aggregated Results with Precision Versatility Across Multiple Scenarios Compatibility and Simplicity Enhanced Query Readability Support for Complex Aggregations The GROUP BY and HAVING clauses serve as essential instruments for data aggregation, identifying duplicates, and filtering results. Their effectiveness, ease of use, and adaptability render them crucial for database management and data analysis activities, allowing users to derive insights and handle data proficiently across a variety of applications. Identifying Duplicates Using a Temporary Table When dealing with large datasets, it can be easier and more efficient to separate duplicates using a temporary table before deleting them. Creating the Table Make a temporary table to store duplicate groups according to predetermined standards (e.g. A. username, along with userEmail. CREATE TEMPORARY TABLE temp_view_duplicates AS SELECT username, userEmail, MIN (userId) AS minuid FROM userDetails GROUP BY username, userEmail, HAVING COUNT(*) > 1; Explanation: CREATE TEMPORARY TABLE temp_view_duplicates AS: Creates a temporary table named temp_view_duplicates. SELECT userName, userEmail, MIN(userId) AS minuid: Groups duplicates by userName and userEmail, keeping only the row with the smallest userId. GROUP BY userName, userEmail: Groups rows by userName, userEmail. HAVING COUNT(*) > 1: Filters only groups with more than one row, identifying duplicates. This temporary table will now contain one representative row per duplicate group (the row with the smallest id). Deleting Duplicates from the Main Table Now that we have a list of unique rows with duplicates in the temp_view_duplicates table, we can use the temporary table to remove duplicates while keeping only the rows with the smallest userId. Use the following DELETE command: DELETE FROM userDetails WHERE (username, userEmail) IN ( SELECT username, userEmail FROM temp_view_duplicates ) AND userId NOT IN ( SELECT minuid FROM temp_view_duplicates ); Explanation: WHERE (username, userEmail,) IN: Targets only duplicate groups identified in temp_view_duplicates. AND userId NOT IN (SELECT minuid FROM temp_view_duplicates): Ensures that only duplicate rows (those with higher userId values) are deleted. Verifying Results To confirm that duplicates have been removed, query the userDetails table: SELECT * FROM userDetails; Only unique rows should remain. Temporary tables (CREATE TEMPORARY TABLE) are automatically dropped when the session ends, so they don’t persist beyond the current session. When making extensive deletions, think about utilizing a transaction to safely commit or undo changes as necessary. Key Advantages of Using a Temporary Table Lower Complexity: By isolating duplicates, the removal process is simpler and clearer. Enhanced Efficiency: It's faster for large datasets, as it avoids repeated joins. Improved Readability: Using a temporary table makes the process more modular and easier to understand. Conclusion Eliminating duplicate records is essential for maintaining a well-organized database, improving performance, and ensuring accurate reporting. This guide presented two approaches: Direct Method with GROUP BY and HAVING Clauses: Ideal for small datasets, using self-joins to delete duplicates. Temporary Table Approach: More efficient for larger datasets, leveraging temporary storage to streamline deletion. Choose the method that best fits your data size and complexity to keep your database clean and efficient.

MariaDB is an open-source relational database management system, which has made it a popular alternative to MySQL. It is often included in LAMP stacks (which consist of Linux, Apache, MySQL, and PHP, sometimes with Python and Perl compilers). This document provides a brief guide to setting up MariaDB. The setup involves three steps: updating the package index, installing the mariadb-server, and activating a security script restricting unauthorized access to the remote host.   The sequence of commands looks like this: sudo apt update sudo apt install mariadb-server sudo mysql_secure_installation For this tutorial, we will use a Hostman cloud server with Ubuntu 22.04 installed. Before diving into this guide, we recommend performing initial setup steps, such as creating a user account with sudo privileges and setting basic UFW firewall rules. Step 1: Installing MariaDB Repositories are regularly updated to include stable versions of utilities. Distributions, on the other hand, include outdated releases that were current at the time of the build, so they need to be updated to avoid compatibility issues. This procedure is executed with the following command: sudo apt update Now we can install the package we need: sudo apt install mariadb-server The installation proceeds without prompting for a password or making any other configuration changes. Using MariaDB in this form on the server is not advisable, as it will operate in an insecure mode. To rectify this situation, we will apply the mysql_secure_installation script that comes with the software. This script will restrict access to the server and eliminate unauthorized accounts. Step 2: Setting Up MariaDB The security script modifies the insecure options that are set by default. For instance, it creates test accounts and allows remote connections using the root account. This potentially poses a risk of hacking and unauthorized access to the information stored in the created database. To run the script, use the following command: sudo mysql_secure_installation This command will initiate a series of prompts that allow you to change the database's security parameters.  The first prompt relates to the root account, and the system will request the password for the active database. Press Enter. This indicates that there is currently no protection. Enter current password for root (enter for none): Switch to unix_socket authentication [Y/n] Enter n and press Enter Change the root password? [Y/n] Enter Y and press Enter. New password: Re-enter new password: Enter and re-enter the new root user password and press Enter. Answer Yes (Y) to all the following prompts. Remove anonymous users? [Y/n] Disallow root login remotely? [Y/n] Remove test database and access to it? [Y/n] Reload privilege tables now? [Y/n] The system will remove the test database and anonymous accounts, disable access through the root account, and load the updated rules.  The installation and configuration of the MariaDB package are complete, and you can now begin using it. Alternatively, you can proceed to an optional step to create an administrator account to enable password access. Step 3: Create an Administrator Account By default, on Ubuntu, MariaDB is installed using the unix_socket plugin, which means that password prompts do not apply. In most cases, this approach provides a high level of security. However, it also complicates administrative tasks, such as those done through phpMyAdmin. When starting or stopping the server or managing logs, the root account is used. That is why we did not change its data. However, during a package update, these settings may change on their own, so it's better to enable password authentication right away. As an example, we will create an account named admin and give it the same privileges as the root account. First, open the MariaDB command line: sudo mariadb Next, create the new user: GRANT ALL ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION; Replace admin and password with any preferred combinations.  After creating the account, flush the privileges while keeping the settings in the current session: FLUSH PRIVILEGES; Now you can close the shell: exit; Next, you should test MariaDB to ensure that the settings are correct. Step 4: Diagnostics When the MariaDB is installed from the official repository, it automatically configures the settings to ensure that the MariaDB module starts automatically. However, it's still a good practice to manually check its status: sudo systemctl status mariadb The output on the screen will look something like this: If the utility is not running, you will need to start it manually and also enable the service: sudo systemctl enable mariadb sudo systemctl start mariadb After forcibly starting the service, you can make a test connection to the database using mysqladmin. It allows you to interact with the database with administrative rights, execute commands, and change settings. Here’s an example of connecting and displaying the version number: sudo mysqladmin version The output on the screen will look like this: If access was configured using the administrator password, you can use the command: mysqladmin -u admin -p version The current version output confirms that the database is running and functioning, and that the user has access to its contents. Conclusions We have completed an overview of the installation and configuration for the MariaDB database management system. We discussed methods to protect against unauthorized access to the database and the creation of a new user who will have access to information equal to that of the root user.