Blog

Introduction to MySQL and relational databases

What is MySQL? So MySQL is an open-source relational database management system (RDBMS) software or database create, process, store software. MySQL uses Structured Query Language (SQL) to allow database users to manage databases and interact with database table records. MySQL software is widely used to store database data, retrieve databases, update and manage existing database table data. Where MySQL is a better software especially for its storage and retrieval speed, reliability and flexibility.

MySQL is a built-in part of the LAMP stack (Linux, Apache, MySQL, PHP/Python/Perl), where MySQL is commonly used in web development by database designers. MySQL is PHP, Python, Java and many other major programming language compatible software.

What is a Relational Database?

Relational databases in MySQL or other database software are the joining of the fields and columns of a database table, also known as database table relations. Database tables are organized in rows and columns. Relational databases connect similar database table columns. Where data is structured in such a way that relations can be created between different data set columns. Where in a database table, each table has columns (attributes) and rows (records). And database tables are established in one to one, one to many, and many to many table relationships.

Key concepts in relational databases.

Database Tables – A table in MySQL and other database software is a collection of data arranged in row and column order. Where each table represents a separate entity (for example, employee ID, employee name, age, salary, email) table field.

Example of a simple table in MySQL

ID Name Age Salary email

1 Ajit 40 9999 anit@domain.com

2 Harry 21 8888 harry@domain.com

• Rows (records) – Each individual row in a table represents a single table record or data. For example, a row in the employee table can represent a single employee table record.
• Columns (fields/attributes) – Columns indicate the type nature of the data stored in the table. Each column represents one of the attributes or properties of the record. For example, a name column can store an employee’s name, while an email column stores the employee’s email address.
• Primary Key – The table primary key is a unique identifier for each record in the table. It ensures that each row is distinct from each other, and can be referenced uniquely within the table. For example, an ID column is often used as a primary key.
• Foreign key – A foreign key is a column or group of columns in a table that references a primary key in another table. This is how relational databases create relationships between tables.

Database table relationships.

• One-to-one table relationship – Where each record in employee table x is related to exactly one record in table y. For example, each employee has a unique employee ID.
• One-to-many table relationship – Where one record in table x can be related to many records in table y. For example, a customer can have many orders.
• Many-to-many table relationship – Where many records in table x can be related to many records in table y. For example, student can join many courses, and each course can have many students. It is applied on table relation with junction table.

SQL (Structured Query Language) – SQL is a database design development language. Which is used to communicate with relational database. SQL allows you to create data from database, read table data, update table database and delete table data.

Basic SQL operations in MySQL.

CREATE TABLE – CREATE TABLE command is used to create a new table in MySQL database.

CREATE TABLE employee (

id INT AUTO_INCREMENT PRIMARY KEY,

emp_name VARCHAR(110),

emp_department VARCHAR(120),

email VARCHAR(80)

);

INSERT INTO – Used to insert new records in MySQL table.

INSERT INTO employee (emp_name, emp_department, email) VALUES (‘vinit’, ‘hr’, ‘vinit@domain.com’);

SELECT – used to query data from a MySQL table.

SELECT * FROM employee; — retrieves all records from the employee table

UPDATE – used to update existing records in a MySQL table.

UPDATE employee SET email = ‘amit@domain.com’ WHERE id = 102;

DELETE – used to delete records from a MySQL table.

DELETE FROM employee WHERE id = 101;

ALTER TABLE – used to modify an existing MySQL table structure. For example, adding columns to a table, changing table column data types, etc. are the options. ALTER TABLE employee ADD COLUMN contact INT;

JOIN – In MySQL, JOIN is used to combine rows of two or more database tables based on related columns.

SELECT orders.id, user.name FROM order

JOIN users ON order.user_id = user.id;

Relationships between tables in MySQL.

MySQL provides features to join data in tables by using special attributes in relational databases. These table relationships help you to model or structure data in the real-time world more efficiently.

One-to-many relationship – In this case, one record (such as a user) in one table can be linked to many records (such as orders) in another table.

One-to-many relationship example.

• Users table – Users table stores user information.
• Orders table – Orders table stores order information, where each order belongs to a single user.

Here in this example, user_id in the orders table is a foreign key that associates each order to a specific user.

Normalization in relational databases in MySQL.

Database normalization is the process of organizing data in a way that reduces database redundancy and database dependencies. The goal is to ensure that data is logically stored in such a way that it can be efficiently queried and updated.

There are several normal forms of databases in MySQL (1NF, 2NF, 3NF, etc.), but here are some steps to follow.

• First Normal Form (1NF) – Each table must have a primary key, and the values ​​in each column must not be any repeating groups or arrays.
• Second Normal Form (2NF) – Achieved by removing partial dependencies in database tables. For example, non-key columns must depend on the entire primary key, not just part of it.
• Third Normal Form (3NF) – Achieved by removing transitive dependencies in existing database tables. For example, non-key columns should not depend on other non-key columns.

Indexes in MySQL databases.

Indexes in MySQL databases are used to speed up table query performance. Indexing, typically on large datasets, is a data structure that allows immediate retrieval of rows based on values ​​in some columns. By default, MySQL creates indexes on primary keys, but you can manually create indexes on other columns.

Indexes in MySQL Example.

CREATE INDEX idx_user_email ON user (email);

MySQL Foreign Keys and Referential Integrity.

Foreign keys are used to enforce a relation between two database tables. This ensures that data is consistent by ensuring that foreign key values ​​in child tables must exist in parent tables.

Foreign Keys and Referential Integrity Example.

orders table containing user_id as a foreign key that references user.id.

ALTER TABLE order

ADD CONSTRAINT fk_user_id

FOREIGN KEY (user_id) REFERENCES user(id);

This ensures that each order must be linked to an existing user in the users table.

Transactions in MySQL database.

A transaction is a sequence of one or more SQL operations that execute as a unit. If any part of a transaction fails, the entire transaction can be rolled back to maintain consistency.

Transactions in MySQL Example.

START TRANSACTION;

INSERT INTO user (name, email) VALUES (‘Mohit’, ‘mohit@example.com’);

INSERT INTO order (order_date, user_id) VALUES (‘2027-04-25’, 2);

COMMIT; — If all queries succeed.

— If something goes wrong.

ROLLBACK;

MySQL Backup and Restore.

To prevent data losses in MySQL, it is important to regularly backup the database. MySQL provides you with various tools to create backups. Such as mysqldump utility.

Example for taking database backup in MySQL.

mysqldump -u username -p database_name > backup.sql

To restore a backup in MySQL.

mysql -u username -p database_name < backup.sql

MySQL Conclusion.

• MySQL is an efficient and widely used relational database management software.
• Relational databases in MySQL store data in table order, and relationships between tables are enforced using primary and foreign keys.
• MySQL allows you to interact with the database, including creating data, updating the database, and querying the database.
• Normalization in MySQL ensures that data is organized to reduce redundancy and improve efficiency in database tables.
• Database table transactions, indexing, and foreign keys in MySQL help maintain data integrity and improve performance. By understanding the key concepts of relational databases and MySQL, you can start building and managing databases that are efficient, secure, and scalable.