MySQL is an open-source relational database management system we can interact with using Structured Query Language (SQL). A query language is a simple programming language that can help you access, manipulate and manage data in a relational database. We use MySQL to store all the information you need to efficiently run large, complex websites.
MySQL represents the M in LAMP (Linux, Apache, MySQL, PHP/Perl/Python), which is a collection of tools used to build web applications. In order to build a web application, we need an operating system, a web server to hold our web application, a database that stores and allows us to access the data and a programming language to develop our application.
MySQL and SQL: What’s the Difference?
MySQL is a database management system; it’s a way we can store and retrieve data in a structured way so we can use that data in web applications.
The way we can communicate with a database management system is through SQL, Structured Query Language. SQL helps us ask a database management system for specific data through queries, which are basically a set of conditions stating what data we need to retrieve from the database.
Is MySQL Open Source?
How Does MySQL Work?
MySQL is fundamentally based on two main concepts: the relational database and the client-server model.
1. Relational Database
In a relational database, you store data in separate storage areas, which we call tables. These tables are often related to each other. For example, suppose I have two tables for an airline company, one for the pilots and one for the flights. In that case, they can be connected by a relation, a column in both tables, as in the image below.
In this case, the representation of the relation between the two tables will be the PilotID; in database language we call that a primary key. A primary key is the main connection between two tables in a relational database.
2. Client-Server Model
MySQL is a management system built on the client-server model. Simply put, the server is where the data is stored and the client is the tool (or approach) you’ll need to use in order to access and retrieve this data.
1. Display All the Data in the Pilots Table
SELECT * FROM Pilots;
2. Display a Specific Column in the Pilots Table
SELECT Name FROM Pilots;
3. Display a Specific Pilot Based on Their ID
SELECT * FROM Pilots WHERE PilotID = 10 ;
4. Make a New Table for Meals on a Flight
Where the FlightID is an integer, the name of the meal is within 25 characters and the quantity of each meal is also an integer.
CREATE TABLE MealsInfo( FlightID INT, MealName VARCHAR(25), Quantity INT, );
5. Add Meals to the MealsInfo Table
INSERT INTO MealsInfo (FlightID,MealName, Quantity) VALUES (1002, Chicken, 46), (1003, Beef , 28), (1004, Veg , 39);