What is MySQL? Tables, Relationships and Views

An Introduction to MySQL

MySQL is a pretty strong software in and of itself. It supports a significant portion of the capabilities of the most costly and sophisticated database solutions. MySQL makes use of a standard version of the well-known SQL data language. MySQL is compatible with a wide range of operating systems and languages, including PHP, PERL, C, C++, Java, and others.

MySQL operates rapidly and efficiently, even with enormous data volumes.

MySQL is particularly friendly to PHP, the most popular web development language.

MySQL can handle big databases with up to 50 million rows or more in a single table. The default file size limit for a table is 4 GB, but you may extend this to a theoretical maximum of 8 million gigabytes (assuming your operating system supports it) (TB).

MySQL is adaptable. The MySQL software may be modified to meet unique contexts thanks to the open-source GPL license.

Tables in the MySQL Database

1. Table mysql.column stats… mysql.columns priv Column-level privileges in a table
2. MySQL Database Table.
3. mysql. event Table.
4. Table MySQL.func Information on user-defined functions.
5. MySQL.general log table If written to a table, the contents of the general query log.
6. global priv table in MySQL.
7. gtid slave pos table in MySQL.

Relationships in MySQL

The term “database relationship” refers to how data in one table is connected to data in another table. In relational databases (Relational Database Management System). The word “relational” refers to tables that include relations. Keys are used to establish relationships between two tables. A key in one table will usually correspond to a key in another. Two tables in a database may be unconnected as well. Database connections are classified into three types: 

1. A One-to-One (1:1) Connection: A one-to-one (1:1) relationship exists when just one piece of data in one table corresponds to only one piece of data in another table.
2. A One-to-Many (1:M) Connection: A one-to-many (1:M) relationship exists when just one data point in one table links to several data points in another table.
3. Many-to-many (M: M) Relationship: A many-to-many (M: M) relationship exists when multiple variables in one table relate to multiple variables in another table.

MySQL has a one-to-one (1:1) database relationship: Only one piece of data in one table connects to only one piece of data in another table in a one-to-one (1:1) relationship. Examine the example tables. Customers and addresses are two database tables. The first table contains the customer id, customer name, and address.

Database Relationship One-to-Many (1:M): One-to-Many (1:M) relationships exist when one entry in one table corresponds to several records in another table. The most prevalent type of relationship in relational database management systems is the one-to-many (1:M) relationship.

Let’s look at an example of a one-to-many connection. Customers and Orders are two database tables. Customers and customer names are stored in the customer database.

Database Relationship of Many-to-Many (M: M): Numerous records in one table relate to multiple records in another table in a Many-To-Many (M: M) Database Relationship. Take a look at the example tables below. There are three database tables. The first table, called “customers,” holds the customer’s ID, name, and phone number.

Views in MySQL

What are MySQL Views? VIEWS are virtual tables that do not store their own data but display data from other tables. VIEWS, in other words, are nothing more than SQL queries. A view can include all or a subset of a table’s rows.

Create a view statement in MySQL

1. A view is a virtual table in SQL that is based on the result set of a SQL operation.
2. A view, like a true table, has rows and columns. A view’s fields are taken from one or more actual tables in the database.
3. You may add SQL statements and functions to a view to portray data as though it came from a single table.

The CREATE VIEW statement creates a view.