The following is an introduction to MySQL with some resources to get started. This article assumes that the reader has some knowledge about Structured Query Languages (SQL). For more information about SQL in general, you can check out this link. A relational database management system (RDBMS) which is used to implement databases for any general application. MySQL is one of the most popular DBMSs because it is flexible, secure, and has high performance. For more details, click here.
When choosing a database management system, there may be a lot of options. here are some comparisons between the most popular ones:
- Vs. PostgreSQL
- Postgres is more geared towards applications that require complex queries and large amounts of data since it boasts many features that are not present in MySQL. Some of these features include table inheritance and function overloading. For a more detailed comparison you can check out this link.
- Vs. MongoDB
- MongoDB is a NoSQL database, meaning that it does not follow SQL rules and schemas and instead uses JSON rules to store data. Therefore it is more flexible. It is also geared towards write performance and is better for real time applications
The following is a summary from this guide which has many details, but here are the following steps:
- Download the client
i) Linux
Follow the instructions on this this link. For users using Debian or Ubuntu, use the APT documentation here and follow the 3 steps.
ii) Windows
Follow the instructions on this link which provides 3 steps in order to complete this step using an installer.
iii) macOS
Follow steps 1-8 from this link which shows you how to nagivate the Installer Wizard. - Connect to the MySQL server using the mysql client
i) Linux Based Systems
Enter the following in the command line terminal
$> mysql -u root -p
ii) Windows
Go to Start, All Programs, MySQL, MySQL (ver#) Command Line Client - Run SQL statements to create schemas and run operations. Here are some examples
- Creating a new database. Use a CREATE DATABASE statement
- Create a table with a CREATE TABLE statement
- Adding records into a table using INSERT...VALUES statement
- Use a DELETE statement to delete a record from a table
There are many environments that can integrate a MySQL database. Here is how to do it in Node.js and Python
Assuming that npm and node is installed (click here for npm instructions, use the following commands in the terminal
npm init -ynpm install mysql- In your mysql client use the database command
CREATE DATABASE databaseName;to create a database to connect to - In your .js file, use
import 'mysql'; - Use the form
let connection = mysql.createConnection({host: 'localhost', user: 'root', password: '', database: 'databaseName'});in the .js file to connect to the database server. The host, user, and password parameters can be changed to your specific usage. - Use the form
connection.connect(function(err) {...});to connect to the database, where the ... represents your error checking method incase the connection fails. - If you want to query the database, use the form
connection.query(queryName, function(err, results, fields) {...}where queryName represents your SQL query in string format, and ... is your resolve function from the resulting query call. - To close the connection, use the form
connection.end(function(err) {...});where ... represents the error checking method of your choice.
For more detailed steps, check out this link
In order to connect to the database using Python, you need to use a database driver. Assuming that a recent version Python and pip is installed (click here for details installing pip), use the following commands:
- In the shell use
pip install mysql-connector-python - In your .py file, use
import mysql.connector - Use the form
connector = connect(host="localhost", user=..., password=...,)to connect to the database server, where ... represents your own user and password methods. - To create a database through python, use the cursor method which allows you to use SQL queries. Use the form
connector.cursor().execute('CREATE DATABASE databaseName'), where databaseName can be changed for your own usage. - If you want to connect to an existing database, use the form
connector = connect(host="localhost", user=..., password=..., database='databaseName',) - If you want to query the database, use the form
connector.cursor().execute('...') - To close the connection, use
connector.close()
For more detailed steps, check out this link
- To use MySQL with PHP, refer to this tutorial by MySQLTUTORIAL
- To see examples of using the MySQL client, refer to this link
- To troubleshoot the client, refer to this link, where it gives instructions on how to find problem and also lists common errors