The foundation of our digital universe is an ever-increasing accumulation of data. As a consequence, database management systems like MySQL are the core elements of the Internet and the interconnected world. These allow large amounts of data to be processed electronically, archived consistently and permanently stored. To do this, complex data is broken down into manageable quantities and, depending on need, are related to each other. In our MySQL manual for beginners we introduce you to the basic principles of database management and we show you through examples how you can optimize the data management of your web project with MySQL.
What is MySQL?
MySQL, along with Oracle and Microsoft SQL Server, is among the most popular relational database management systems worldwide (you can find a current list at https://db-engines.com/en/ranking ). The software developed in 1994 by the company MySQL AB is today sponsored by Oracle Corporation and is marketed under a dual licensing system. Also, in addition to the proprietary Enterprise edition, Oracle offers an open source version with a GPL license.
This dual license offers companies the possibility to develop their own applications based on MySQL without having to submit them to the open source license. In the open source community, however, Oracle's takeover of MySQL is subject to criticism.
MySQL is written in C and C ++ and also comes with a Yacc-based SQL parser with its own tokenizer (lexical scanner). Likewise, the database management system stands out for its broad operating system support.
Done
The abbreviation SQL comes from "Structured Query Language", a programming language used to manage database structures. Possible operations include querying, integrating, updating and deleting existing data.
MariaDB: a fork of MySQL with potential
In the field of web development, the integration of the MySQL project in the Oracle product catalog is the object of mistrust and criticism, mainly due to the constant increase in the differences between the MySQL version with the GPL license and the paid Enterprise product. The new functions of the database management system are more and more frequent, although only in the proprietary version. The databases of non - public errors and insufficient tests do pose to the community, under the wings of the Oracle software, the open source project does not enjoy equal conditions, which triggers a diminishing support from this.
As early as 2009, the core development team around Michael “Monty†Widenius, inventor of the MySQL database, turned his back on the popular database system and launched an open source fork with MariaDB. of MySQL. In late 2012, Linux distributions such as Fedora, OpenSUSE, Slackware, and Arch Linux were the first to replace MySQL with MariaDB as a standard installation. A large number of open source projects, well-known web platform and software companies followed suit, including Mozilla, Ubuntu, Google, Red Hat Enterprise Linux, Web of Trust, Team Speak, the Wikimedia Foundation, and XAMPP.
Even today it is clear that, compared to the open source version of MySQL, MariaDB continues to develop. Therefore, it is to be assumed that the fork will soon overtake its parent project.
tip
In the software sector, a fork can be spoken of as a development branch that arises from the fork of a project (mostly open source). A fork is built on top of the source code of the parent project and developed into a separate follow-up project.
Database systems
Today, electronic data management takes place mainly with database systems (SBS) , which are made up of two components, the database itself (DB) and the database management system (DBMS). :
The management system databases: by DBMS is understood all that software, such as MySQL, the system used to manage databases. Among the tasks of this management software is the structuring of the data according to a predefined database model. In addition, the SGBD controls read and write accesses, manages large amounts of data and parallel accesses, and ensures that the standards regarding the integrity of the information, as well as its protection and security, are met.
The database is a set of data with related content, such as customer information or CMS data. A DBMS can manage several databases simultaneously.
Relational database model
By its definition, MySQL is a relational DBMS. This means that all the data that MySQL uses is stored in tables related to each other by means of keys.
Let's see how this can be illustrated with a simple example. Below are the authors and works tables :
All tables in a relational database are made up of columns and rows, and each column is designed for a specific attribute . For example, the authors table contains the id, first name and last name attributes . The rows of a table are designated as rows and each of them contains a set of data, which is identified (numbered), usually by means of a primary key. The type of attribute set as the primary key is defined when the table is created. The requirement is that the primary key enables unique mapping, so it can only be used once in the column. For this, serial numbering via ID is recommended .
Table works , besides the presents key primary id_obras , id_autores as foreign key (foreign key). This creates a relationship between both tables and links the data sets of one table with those of the other. When a connection is established between two tables in a relational database, we can speak of a join. This connection could take place with the following query to the database: "load all works of author John Ronald Reuel Tolkien with the date of the first publication."
Tolkien has been classified in the authors table with the primary key author_id 1 . To consult all the works of this author, this is presented in the works table as a foreign key. In this way, all rows linked with the author_id 1 are requested .
In practice, all database operations in MySQL are carried out with standardized SQL commands such as SELECT, INSERT, UPDATE, and DELETE, but we will talk about them in later chapters of our MySQL manual.
admin php
Obviously, all the data referring to the authors and their works can also be stored in a single table. A data storage of these characteristics involves a database containing a number of redundant entries as, for example, the data in columns name and last name are specified individually for each work. Such redundancy not only puts a strain on memory, it also results in updates to different points in the database. That is why when working with relational databases its content is limited to one subject per table. In this case, we speak of a normalization of the data.
MySQL's main field of application is data storage in the context of dynamic web pages. The combination of MySQL with Apache web server software and PHP or Perl scripting languages ​​has established itself as a classic software framework in web development. The web stack can be implemented as LAMP (Linux), MAMP (macOS) or WAMP (Windows) with common server operating systems.
For those who are starting to work with MySQL, the XAMPP locales may be recommended to gather the first experiences with the database management system, which is based on MariaDB in the current version.
Database management system installation
Here are some practical examples to shed more light on the fundamentals of MySQL. Our tutorial for MySQL is based on the XAMPP test environment and the snippet codes and screenshots focus on the database operations that are carried out through PHP with the help of an Apache HTTP server in a Windows computer. Instead of the classic MySQL database, in this case, the MariaDB fork is applied. Currently, both database management systems are so compatible that all operations are exactly the same. As part of a tutorial for beginners, it does not matter whether you are working with MySQL or MariaDB.
To find out how you can install a test environment locally on your Windows computer, visit our XAMPP tutorial . If you want to learn to work with relational databases from the beginning, it is recommended that you focus directly on MariaDB. A free alternative environment that is based on MySQL is AMPPS .
You also have the possibility of integrating a custom web stack. MySQL and MariaDB can be combined, as required, with various operating systems, web servers, and scripting languages. The packages download free and GPL are available on page mysql.com and mariadb.com . You can get detailed installation instructions for different platforms in the English documentation for MySQL and MariaDB .
Database management with phpMyAdmin
For the management of MySQL we rely on the free phpMyAdmin web application , contained in the XAMPP installation package, but also offered separately on the project's official website as a download package
phpMyAdmin is considered as the standard software for managing MySQL databases on the World Wide Web. The web application written in PHP and JavaScript facilitates database operations through graphical user interfaces . In this way, the relational database tables can be created and managed comfortably in the web browser. In this sense, it is not necessary in principle to know the corresponding SQL commands.
Start phpMyAdmin
If you have installed XAMPP, start the database management system (MySQL or MariaDB) in the control panel, in the same way as the other components of the stack. To do this, use the "Start" button under "Actions". In order to access phpMyAdmin through the web browser, you need to start the Apache web server. Activated modules are highlighted in green in the XAMPP control panel. You can also know the current status of the XAMPP modules in the form of notification through the text box.
In local test mode, the web interface of the management software can be accessed via localhost / phpmyadmin / .
As long as a password has been defined for the root account in the MySQL installation , it will need to be entered in the login screen in phpMyAdmin. In case of using this tool in a web hosting product, the registration data will be provided by the corresponding provider. In this case, as a user, you don't usually have root access rights.
Once you have logged in, phpMyAdmin displays the application's home page , which allows you to make the basic settings to obtain the character set (collation) from the MySQL connection or to select the desired display mode (language, layout and font size). On the right is a summary of the reference data for your database server, the web server software used, as well as information about the current version of phpMyAdmin. The menu bar is, like the rest of the application's menu bars, designed in the form of tabs. To select one or the other, just click on Databases , SQL ,Status, User accounts, Export, Import, Settings, Replication, Variables y More.
unique key
mysql unique keys