Learning MySQL

Table of contents

Introduction

MySQL vs other DBMSs

MySQL client installation and basic operations

MySQL integration

Additional Resources

Introduction

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.

MySQL vs other DBMSs

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

MySQL client installation and basic operations

The following is a summary from this guide which has many details, but here are the following steps:

1. 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.
2. 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
3. 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

MySQL integration

There are many environments that can integrate a MySQL database. Here is how to do it in Node.js and Python

MySQL database connection to Node.js

Assuming that npm and node is installed (click here for npm instructions, use the following commands in the terminal

• npm init -y
• npm 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

MySQL database connection to Python

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

Additional Resources

• 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