Introducing MySQL getting started, installation and client management tools

[Related learning recommendations: php programming (video)]

Relational database

As the scale of applications expands and the complexity increases, data storage and retrieval are a Big questions, such as how to store articles in a blog system? For social media systems, how are user relationships and dynamics stored? For an e-commerce system, how to store product and transaction information? And storage alone is not enough, data must be dynamically queried, updated and deleted very conveniently.

Revolving around this issue, as early as 40 years ago, IBM scientists began to study and proposed the concept of relational database management system. A database organizes, stores and organizes data through specific data structures. A warehouse that manages large amounts of data, and a relational database management system (RDBMS) refers to a database based on the relational model.

Relational database management system can be referred to as relational database, which has the following characteristics:

• Data is stored in data tables, such as article tables and user tables;
• The rows of the data table represent a record, such as an article or all the information of a user;
• The columns of the data table represent the same type of data, such as article titles, user names, etc.;
• The data table has primary key, foreign key, index and other structures. The primary key can uniquely identify a record, the foreign key can establish a relationship with the columns of other tables, and the index can be used to speed up the query of the data table records;
• Rows and columns A data table is constructed, and multiple data tables are aggregated into a database.

Note: Data can also be maintained and managed through memory storage such as arrays, but it is not conducive to massive data. After all, memory resources are limited, and more fatally, it cannot be persisted; through files Massive amounts of data can be stored persistently, but the IO overhead of storage and retrieval is too high, and the performance is not enough to support concurrent requests from a large number of users. Relational databases can solve these problems at the same time.

MySQL database

There are many relational databases, including MySQL, Oracle, SQL Server, SQLite, Postgres, etc., but the most popular and widely used one is undoubtedly MySQL. This is largely due to the fact that MySQL is open source and free, and it has been proven in the practice of large companies that it can fully shoulder the task of storing massive data. It is also performant enough to support high concurrent requests, is durable, and can be used at no cost. Money, that’s great (compared to the high cost of Oracle, not too good). In addition, MySQL and PHP are also good friends. PHP has very good native support for MySQL, which is the most popular web development language in the world. The world's most popular relational database is a perfect match, and many well-known applications have been born from it. The relationship between the two is so good that they can wear a pair of pants, so some people joked that "without MySQL, what else can PHP do?"

Note: At present, foreign Postgres databases are becoming increasingly popular, and they are also free and open source. This may be largely due to the fact that after MySQL was acquired by Oracle, there are many uncertainties in the future.

Related learning recommendations: mysql tutorial(video)

Installing MySQL

Before using MySQL, you need to first Install it, but when it comes to building a local PHP development environment, the integrated development tools we recommend include MySQL by default:

• PHP/Laravel Local development environment setup: Mac
• PHP /Laravel Local Development Environment Construction: Windows Chapter

Whether it is Laradock, Xampp, MAMP, Laragon or PhpStudy, they all have built-in support for MySQL (including client and server, and the server stores data Central warehouse, the client can interact with the server through SQL commands to add, delete, modify, check and manage), and it can be used out of the box. So I won’t introduce how to install MySQL locally here.

Note: This series of tutorials assumes that you have mastered basic SQL statement operations. If you don’t know much about it, you can read the W3School SQL tutorial to learn.

Command line interaction

Whether it is a Mac or a Windows system, there are a large number of MySQL client tools. The most original one is the command line interaction that comes with MySQL. Take Laradock as an example. , we can start the MySQL container by executing the following command in the laradock project directory (based on the Windows Terminal 1.0 command line environment demonstration, the command in the Mac system is exactly the same):

Then enter the container through docker-compose exec mysql bash:

You can run it on the clientmysql -h localhost -u root -p Connected to the database server (default password is root):

Next, we can run SQL statements to interact with the server, such as viewing all databases through show databases; (SQL statements end with a semicolon, (cannot be omitted):

You can perform all MySQL database DML/DDL operations through SQL statements in the command line. We will not list them one by one here. We will focus on them below. GUI tools to operate the database.

GUI Tool List

MySQL Workbench

First of all, MySQL officially provides MySQL Workbench for Windows and Mac systems. After the download and installation is completed, open the main interface and click "MySQL The small plus sign on the right side of "Connections" adds a new connection. Here we fill in the connection information corresponding to the local Laradock:

After completing the filling, click "Test Connection" in the lower right corner. If it prompts success, click "Ok" 》Save:

Then you can click laradock in the connection list to enter the local MySQL database management page:

Next, we can manage the local MySQL database through the MySQL Workbench graphical interface.

Note: MySQL Workbench is available for Windows and Mac systems.

Sequel Pro

The official tool is not easy to use. In Mac systems, Sequel Pro is the first choice as the MySQL client tool:

It is a free third-party MySQL client management tool that is very easy to use. After the first installation, open the application, click " " on the bottom left to add a new connection configuration, and then set the connection name to laradock, next, you can configure the local MySQL Docker container connection information:

After configuration, click "Connect" to enter laradock On the database management page, you can select the database you want to operate in the Select Database drop-down menu, or add a new database:

Then you can manage this database. You can explore the specific details on your own and will not give an in-depth introduction here.

Note: Sequel Pro is only available for Mac systems.

PhpStorm

Jetbrains also provides a specialized database management tool Introducing MySQL getting started, installation and client management tools:

However, this tool requires a fee. Daily simple database management work can also be completed through the database management plug-in integrated with PhpStorm. In the upper right corner of the main interface of PhpStorm, there is a "Database" toolbar by default. Click the toolbar and click " " in the upper left corner of the pop-up interface to select data. Source, here we choose "MySQL":

Configure MySQL connection information (Docker container) in the pop-up window. After the configuration is completed, don't forget to click "Download missing driver file" at the bottom of the page ”, otherwise the connection cannot be established:

After the download is completed, click "Test Connection" and if it prompts success, you can click " Click the "Apply" button to save the settings, and then click the "OK" button to close the window.

Then we can click on the connection in the data source list to manage the local database:

You can perform daily DDL/DML operations by right-clicking:

Note: PhpStorm data source management function is available in both Windows and Mac.

Navicat For MySQL

There is also a popular MySQL client graphical management tool Navicat For MySQL:

This tool is also available for Windows and Mac systems. The experience is better on Windows systems, but it requires a fee. If you are interested, you can download and use it yourself.

phpMyAdmin

Finally, there is the well-known phpMyAdmin project that allows us to manage MySQL databases in a web browser. The Laradock project also has built-in support for it. To use it, you need to start the container through the following Docker command:

docker-compose up -d phpmyadmin

After the startup is completed, you can browse Access phpMyAdmin through http://localhost:8080 in the server. We fill in the form information (server, user name, password):

Note What needs to be filled in here is the Docker container name mysql, because the corresponding MySQL container IP can be resolved through this name inside the container. Click "Execute" to enter the MySQL management interface:

#Obviously, since it runs in the browser, phpMyAdmin has nothing to do with the system it belongs to. In order to unify the Windows/Mac system in the future Demonstration style, MySQL database management operations will be performed based on phpMyAdmin.

This article comes from https://xueyuanjun.com/post/21654

For more related articles, please pay attention to php mysql Column!

The above is the detailed content of Introducing MySQL getting started, installation and client management tools. For more information, please follow other related articles on the PHP Chinese website!