Blog

MySQL is an open-source relational database management system that uses Structured Query Language (SQL) for managing and manipulating data.

It differs from other database systems like Oracle or SQL Server primarily in terms of its licensing (being open-source), performance, and scalability. Unlike Oracle, MySQL is free to use and is preferred for its simplicity, ease of use, and wide community support.

Normalization is the process of organizing data in a database efficiently. It involves breaking down a database into smaller, more manageable tables and defining relationships between them. The primary goals of normalization are to minimize data redundancy, improve data integrity, and reduce the likelihood of anomalies during data manipulation.

MySQL server is using 3306 as default port..

Indexes are data structures that provide quick access to rows in a database table based on the values of one or more columns. They work similarly to the index of a book, allowing MySQL to locate rows efficiently without having to scan the entire table. By using indexes, query performance can be significantly improved, especially for tables with a large number of rows.

MySQL uses the ACID (Atomicity, Consistency, Isolation, Durability) properties to ensure data integrity and reliability within transactions.

• Atomicity: Transactions are atomic, meaning they are either executed in full or not at all. If a transaction fails midway, MySQL rolls back the changes to maintain consistency.
• Consistency: Transactions transition the database from one consistent state to another consistent state, preserving data integrity and enforcing constraints.
• Isolation: Transactions are isolated from each other, preventing interference and ensuring that each transaction sees a consistent view of the database.
• Durability: Once a transaction is committed, its changes are permanently saved to the database and survive system failures.

MyISAM and InnoDB are two of the most commonly used storage engines in MySQL.

• MyISAM: It offers fast read operations, making it suitable for read-heavy applications. However, it lacks support for transactions and foreign keys, which are essential for maintaining data integrity in complex applications.
• InnoDB: InnoDB provides features such as transaction support, row-level locking, and foreign key constraints, making it suitable for transactional databases where data integrity is crucial. It excels in scenarios with a high volume of write operations.

• CHAR: CHAR is a fixed-length character data type that stores strings with a fixed length. When you define a CHAR column, you must specify the maximum length of the string it can store. CHAR pads shorter strings with spaces to fill the fixed length.
• VARCHAR: VARCHAR is a variable-length character data type that stores strings with a variable length. Unlike CHAR, VARCHAR only uses as much storage space as necessary to store the actual string, plus one or two bytes to store the length of the string.

BLOB stands for a binary large object. It is used to hold a variable amount of data and holds up to 65,535 bytes of data. The following are the four types of BLOB.

• TINYBLOB
• BLOB
• MEDIUMBLOB
• LONGBLOB

• Primary Key: A primary key uniquely identifies each record in a table and ensures that there are no duplicate records. There can only be one primary key in a table.
• Foreign Key: A foreign key establishes a relationship between two tables by linking a column or columns in one table to the primary key or unique key in another table.

CASCADE DELETE is a referential action that automatically deletes related records in child tables when a record in the parent table is deleted. It ensures data consistency by removing dependent records to maintain referential integrity.

A stored procedure is a precompiled collection of SQL statements that are stored in the database and can be executed multiple times. It allows for encapsulating and reusing complex SQL logic within the database server. Stored procedures can accept parameters, perform calculations, and return result sets or output parameters.

Triggers are special types of stored procedures that are automatically executed (or fired) in response to specific events, such as INSERT, UPDATE, or DELETE operations on a table. They allow for enforcing business rules, maintaining referential integrity, and auditing changes to data.

• DELETE: Deletes rows from a table based on a specified condition. It’s a DML (Data Manipulation Language) statement and can be rolled back using a transaction.
• TRUNCATE: Removes all rows from a table without specifying any conditions. It’s a DDL (Data Definition Language) statement and cannot be rolled back. It’s faster than DELETE but doesn’t invoke triggers.

• WHERE: Used to filter rows before any groupings are made in a query. It’s applied to individual rows.
• HAVING: Used to filter groups after the GROUP BY clause has been applied. It’s applied to aggregated groups.

• LEFT JOIN: Returns all rows from the left table and matching rows from the right table. If there are no matches in the right table, NULL values are returned for the columns from the right table.
• RIGHT JOIN: Returns all rows from the right table and matching rows from the left table. If there are no matches in the left table, NULL values are returned for the columns from the left table.

• Use the DISTINCT keyword in SELECT queries to remove duplicate rows from the result set.
• Use the GROUP BY clause to group rows with common attributes and apply aggregate functions to eliminate duplicates.
• Use the UNIQUE constraint to enforce uniqueness at the database schema level.

• To start a transaction, you can use the START TRANSACTION or BEGIN statement.
• To commit a transaction and make the changes permanent, you use the COMMIT statement.
• To roll back a transaction and discard the changes, you use the ROLLBACK statement.

A view in MySQL is a virtual table that is based on the result of a SELECT query. It allows you to simplify complex queries, encapsulate logic, and present data in a customized way without storing the data physically.

The LIKE command is used in a WHERE clause to search for a specified pattern in a column.

• %: The percent sign represents zero, one, or multiple characters. It can match any sequence of characters, including zero characters.For example, if you use the pattern 'abc%', it will match any string that starts with 'abc', followed by zero or more characters.
• _: The underscore represents a single character. It matches any single character in the specified position.For example, if you use the pattern 'a_c', it will match strings such as 'abc', 'adc', and 'axc', where the middle character can be any single character.

You can find the second highest salary using a subquery or by ordering the salaries in descending order and selecting the second row. Here’s an example using a subquery:

SELECT MAX(Salary) AS SecondHighestSalary 
FROM Employees 
WHERE Salary < (SELECT MAX(Salary) FROM Employees);

• GROUP BY: Groups the result set by one or more columns and applies aggregate functions to each group. It is used in conjunction with aggregate functions like COUNT, SUM, AVG, etc., to perform summary operations on groups of rows.
• ORDER BY: Sorts the result set by one or more columns in ascending or descending order. It does not perform grouping or summarization; it only orders the rows in the result set.