Perhaps you’ve never used a database system before or maybe you’ve used one as a user but have never had any need to set up one from scratch. Or perhaps you’ve decided to discover what all the fuss is about database systems in general.
In this chapter, I present a short introduction to MySQL in the general SQL interface sense (traditional MySQL). Not only will you see how MySQL 8 is used to create and use databases, but you will also be introduced to some of the basics of the SQL interface, which is necessary and indeed required to fully integrate a MySQL server in your infrastructure.
We will also take a brief look at the MySQL Shell and how you can use that to connect to and work with MySQL via both a SQL and NoSQL interface for both applications and interactive sessions.
We will conclude the chapter with a brief look at how to connect your applications to MySQL servers to work with the databases you create.
Let’s begin with a brief foray into what MySQL is and what it can do for us. We will start with installing MySQL on our local PC for experimentation.
While this book presents the MySQL Database Service in the Oracle Cloud Infrastructure, much of the information in this chapter can be used with any MySQL server.
Getting Started
MySQL is the world’s most popular open source database system for many excellent reasons. First, it is open source, which means anyone can use it for a wide variety of tasks for free. Best of all, MySQL is included in many platform repositories, making it easy to get and install. If your platform doesn’t include MySQL in the repository (such as aptitude), you can download it from the MySQL website (http://dev.mysql.com).
Oracle Corporation owns MySQL. Oracle obtained MySQL through an acquisition of Sun Microsystems, which acquired MySQL from its original owners, MySQL AB. Despite fears to the contrary, Oracle has shown excellent stewardship of MySQL by continuing to invest in the evolution and development of new features as well as faithfully maintaining its open source heritage. Although Oracle also offers commercial licenses of MySQL – just as its prior owners did in the past – MySQL is still open source and available to everyone.
Open source software grew from a conscious resistance to the corporate-property mindset. Richard Stallman is credited as the father of the free software movement who pioneered a licensing mechanism to help protect ownership of software and yet make the use of the software and so some degree its revision free to all. The goal was to reestablish a cooperating community of developers cooperating with a single imperative – to guarantee freedom rather than restrict it.
This ultimately led to the invention of some cleverly worded (read legally binding) licensing agreements that permit the code to be copied and modified without restriction, stating that derivative works (the modified copies) must be distributed under the same license as the original version without any additional restrictions. One such license (created by Stallman) is called the GNU Public License (GPL). This is the license that is used by Oracle to license MySQL and as such it is indeed free for anyone to use.
However, GPL and similar licenses are intended to guarantee freedom to use, modify, and distribute; most never intended “free” to mean “no cost” or “free to a good home.” To counter this misconception, the Open Source Initiative (OSI) formed and later adopted and promoted the phrase open source to describe the freedoms guaranteed by the GPL license. For more information about open source software and the GPL, visit www.opensource.org.
MySQL runs as a background process (or as a foreground process if you launch it from the command line) on your system. Like most database systems, MySQL supports Structured Query Language (SQL). You can use SQL to create databases and objects (using data definition language [DDL]), write or change data (using data manipulation language [DML]), and execute various commands for managing the server.
There are two mechanisms for connecting to MySQL. You can use an application like the MySQL Shell or MySQL Workbench, or you can use the older MySQL client. Let’s look at both options.
Connecting with MySQL Shell
The MySQL Shell is a new and exciting addition to the MySQL portfolio. The MySQL Shell represents the first modern and advanced client for connecting to and interacting with MySQL. The shell can be used as a scripting environment for developing new tools and applications for working with data. While it does support an SQL mode, its main purpose is to permit access to data with the JavaScript and Python languages. That’s right, you can write JavaScript and Python scripts and execute them within the shell interactively or as a batch. Cool!
I use the term “shell” to refer to features or objects supported by the MySQL Shell. I use “MySQL Shell” to refer to the product itself.
The MySQL Shell is designed to use the new X Protocol for communicating with the server via the X Plugin. However, the shell can also connect to the server using the older protocol albeit with limited features in the scripting modes. What this means is, the shell allows you to work with both relational (SQL), JSON documents (NoSQL), or both.
The NoSQL interface is based on an application programming interface called the X DevAPI (https://dev.mysql.com/doc/x-devapi-userguide/en/). Administrative operations are supported by another API called the AdminAPI. See the online documentation for more information about these APIs if you plan to use the shell for development work (https://dev.mysql.com/doc/mysql-shell/8.0/en/).
A screenshot of The M Y S Q L shell displays the copyright and its affiliates. Type M Y S Q L in the command line to launch the M Y S Q L Shell.
The MySQL Shell
The following sections present the major features of the shell at a high level. We will not explore every detail of every feature or option, rather, this section provides a broad overview so that you can get started quickly and, more importantly, learn enough about the shell so that you can follow along with the examples in this book.
For more information about the MySQL Shell, see the section entitled, “MySQL Shell User Guide” in the online MySQL reference manual.
Features
Logging: You can create a log of your session for later analysis or to keep a record of messages. You can set the level of detail with the --log-level option ranging from 1 (nothing logged) to 8 (max debug).
Output Formats: The shell supports three format options; table (--table), which is the traditional grid format you’re used to from the old client, tabbed, which presents information using tabs for spacing and is used for batch execution, and JSON (--json), which formats the JSON documents in an easier to read manner. These are command-line options you specify when launching the shell.
Interactive Code Execution: The default mode for using the shell is an interactive mode, which works like a traditional client where you enter a command and get a response.
Batch Code Execution: If you want to run your script without the interactive session, you can use the shell to run the script in batch mode. However, the output is limited to non-formatted output (but can be overridden with the --interactive option).
Scripting Languages: The shell supports both JavaScript and Python although you can use only one at a time.
Sessions: Sessions are essentially connections to servers. The shell allows you to store and remove sessions. We will see more about sessions in a later section.
Startup Scripts: You can define a script to execute when the shell starts. You can write the script in either JavaScript or Python.
Command History and Command Completion: The shell saves the commands you enter allowing you to recalling them using the up and down arrow keys. The shell also provides code completion for known keywords, API functions, and SQL keywords.
- Global Variables: The shell provides a few global variables you can access when in interactive mode. These include the following:
session: global session object if established
db: schema if established via a connection
dba: the AdminAPI object for working with the InnoDB Cluster
shell: general purpose functions for using the shell
util: utility functions for working with servers
Customize the Prompt: You can also change the default prompt by updating a configuration file named ~/.mysqlsh/prompt.json using a special format or by defining an environment variable named MYSQLSH_PROMPT_THEME. See the MySQL Shell Reference manual for more details about changing the prompt.
Auto Completion: Starting in 8.0.4, the shell permits users to press the TAB key to auto-complete keywords in SQL mode and the major classes and methods in JavaScript and Python modes.
Extensions: You can define extensions in the form of reports and extension objects. Reports and extension objects can be created using JavaScript or Python.
- Utilities: The shell also provides several utilities that you may find helpful including the following. You access them via an API call as demonstrated.
Upgrade Checker: Used to verify whether MySQL server instances are ready for upgrade (e.g., util.checkForServerUpgrade()).
JSON Import: Import JSON documents to a MySQL Server collection or table (e.g., util.importJSON()).
Parallel Table Import: Splits up a single data file and uses multiple threads to load the chunks into a MySQL table.
Shell Commands: Like the original MySQL client, there are some special commands that control the application itself rather than interact with data. To execute a shell command, issue the command with a slash (). For example, help prints the help for all of the shell commands.
Options: The shell can be launched using several startup options that control the mode, connection, behavior, and more. Common options include the mode (SQL, JavaScript, or Python) and connection parameters. For a complete list of options, execute the shell with the --help option with mysqlsh -- help.
As you can see, there is a long list of features for the MySQL Shell. See the online reference manual for a complete list of features.
Let’s look at the two most important to those getting started working with MySQL: modes and how to connect.
Modes Supported
sql: Switch to the SQL language
js: Switch to the JavaScript language (default mode)
py: Switch to the Python language
You can also issue these commands as startup by using -- instead of (--sql, --js, --py).
Making Connections
Making connections in the shell is one area that may take some getting used to doing differently than the original MySQL client (mysql). You can use a specially formatted URI string or connect to a server using individual options by name (like the old client). SSL connections are also supported. Connections can be made via startup options, shell commands, and in scripts. However, all connections are expected to use a password. Thus, unless you state otherwise, the shell will prompt for a password if one is not given.
If you want to use a connection without a password (not recommended), you must use the --password option or, if using an URI, include an extra colon to take the place of the password.
Rather than discuss all the available ways to connect and all the options to do so, the following presents one example of each method of making a connection in the following sections.
Using a URI
A URI in the case of a MySQL Shell connection is a special string coded using the following format: <dbuser>[:<dbpassword>]@host[:port][/schema/], where <> indicates string values for the various parameters. Notice the password, port, and schema are optional but the user and host are required. Schema in this case is the default schema (database) that you want to use when connecting.
The default port for the X Protocol is 33060.
Connecting with a URI
Notice I also specified the default schema (world_x) with the /schema option in the URI.
The world_x database is a sample database you can download from https://dev.mysql.com/doc/index-other.html.
Using Individual Options
Connecting Using Individual Options
Notice I changed the mode (language) to Python with the --py option.
Using SSL Connections
See the section “Using Encrypted Connections” in the MySQL Shell reference manual for more details about encrypted connections.
The MySQL Shell is a huge leap forward in technology for MySQL clients. Not only is it designed to work with SQL in MySQL in a smarter way, it is also designed to enable prototyping of JavaScript and Python. You can work with any language you want and switch between them easily without having to restart the application or drop the connection. How cool is that?
Now, let’s look at the older application for connecting to MySQL servers.
Connecting with the MySQL Client
While the MySQL Shell has been around for a few years and has many features including scripting and development capabilities, there is another client that has been around in MySQL for decades. It is an application named mysql, which enables you to connect to and run SQL commands on the server. Interestingly, this MySQL client was originally named the MySQL monitor but has long since been called simply the “MySQL client,” terminal monitor, or even the MySQL command window.
You can only get the mysql client if you install the MySQL server (on Windows or macOS) or one of the package components. For example, on Linux, you can install only the client package, which will give you the client as well. We will save installation of the client to a later section where we install MySQL on our local PC.
Prior to MySQL version 8.0.4, the default authentication mechanism used an authentication plugin called the mysql_native_password plugin, which used the SHA1 algorithm. This mechanism was fast and did not require an encrypted connection. However, since NIST has suggested organization stop using the SHA1 algorithm, Oracle has changed the default authentication plugin in MySQL version 8.0.4 to the cachin_sha2_password plugin.
If you would like to learn more about the changes including why Oracle made the change and the advantages for users, see https://mysqlserverteam.com/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password/.
To connect to the server using the MySQL client (mysql), you must specify a user account and the server to which you want to connect. If you are connecting to a server on the same machine, you can omit the server information (host and port) since they default to localhost on port 3306. The user is specified using the --user (or -u) option. You can specify the password for the user on the command, but the more secure practice is to specify --password (or -p), and the client with prompt you for the password. If you do specify the password on the command line, you will be prompted with a warning encouraging you to not use that practice.
Connecting with the mysql Client
Basic SQL Commands
Now that we’ve seen how to connect to MySQL, let’s take a brief look at some of the more popular SQL commands you will likely use by way of a short example. We will discuss the commands in more detail in a later section. Listing 3-4 shows examples of several SQL commands in action using the mysql client.
To see a list of the commands available in the client, type help; and press Enter at the prompt.
Example SQL Commands Using the mysql Client
In this example, you see data definition language1 (DDL) in the form of the CREATE DATABASE and CREATE TABLE statements, data manipulation language2 (DML) in the form of the INSERT and SELECT statements, and a simple administrative command to set a global server variable.
Next you see the creation of a database and a table to store the data, the addition of a row in the table, and finally the retrieval of the data in the table. Notice how I used capital letters for SQL command keywords. This is a common practice and helps make the SQL commands easier to read and easier to find user-supplied options or data.
You can exit the MySQL client by typing the command quit. On Linux and Unix systems, you can press Ctrl+D to exit the client.
CREATE DATABASE <database_name>: Creates a database
USE <database>: Sets the default database (not an SQL command)
CREATE TABLE <table_name> [...]: Creates a table or structure to store data
INSERT INTO <table_name> [...]: Adds data to a table
UPDATE [...]: Changes one or more values for a specific row
DELETE FROM <table_name> [...]: Removes data from a table
SELECT [...]: Retrieves data (rows) from the table
SHOW [...]: Shows a list of the objects
You must terminate each command with a semicolon (;) or G.
Although this list is only a short introduction and nothing like a complete syntax guide, there is an excellent online reference manual that explains every command (and much more) in greater detail. You should refer to the online reference manual whenever you have a question about anything in MySQL. You can find it at http://dev.mysql.com/doc/.
One of the more interesting commands shown allows you to see a list of objects. For example, you can see the databases with SHOW DATABASES, a list of tables (once you change to a database) with SHOW TABLES, and even the permissions for users with SHOW GRANTS. I find myself using these commands quite frequently.
If you are thinking that there is a lot more to MySQL than a few simple commands, you are correct. Despite its ease of use and fast startup time, MySQL is a full-fledged relational database management system (RDBMS). There is much more to it than you’ve seen here. For more information about MySQL, including all the advanced features, see the reference manual.
How to Get and Install MySQL
The MySQL server is available for a variety of platforms including most Linux and Unix platforms, Mac OS X, and Windows. It is available as a community download (open source) or as a licensed product (enterprise edition). You can download the community editions directly from Oracle or you can download enterprise editions through your Oracle account. For the purposes of this chapter, we will use the community edition.
To download community releases of MySQL 8, visit www.mysql.com/downloads/ and scroll down and click MySQL Community (GPL) Downloads, then click MySQL Community Server. The page will automatically detect your operating system. If you want to download for another platform, you can select it from the drop-down list.
A screenshot depicts the general availability release tab and displays the macOS 11 examples that can be downloaded. The version and file size are displayed. The download button is on the right side.
Download Page for macOS
A screenshot of the general availability release tab displays the My S Q L Installer for windows and also other downloads are available at the bottom.
Download Page for Windows Installer
We see these below the Windows Installer download link in the image. You can choose either the Windows Installer 32- or 64-bit installation. Note that the package may be nothing more than a .zip file containing the server code. In this case, you may need to either run the server from the unzipped folder or do a local, manual install.
If you want to follow along with the examples in this chapter, you should consider installing MySQL on your PC or another PC on your local network. While you will not have to do any installation or configuration when using MDS, we can learn a bit about MySQL by installing it locally so we can use it at our leisure without incurring costs.
Installing MySQL is very easy and takes only a few minutes once you’ve downloaded the correct packages for your platform. Simply install the server and any subcomponents as you would for any other software for your platform. If you need step-by-step instructions, you can refer to the online reference manual (https://dev.mysql.com/doc/refman/8.0/en/installing.html).
For our experimental purposes, you can install MySQL with the default settings. More specifically, as a standard installation. You can always reinstall it if you want to install it preconfigured for a different environment. During the install, you may be asked to provide a password for the root user. Be sure to use best practices for choosing a password.
Ok, now that we have the MySQL 8 server installed, we can begin configuring the server for use. You will not need to do any of these operations when using MDS, but it is always good to know more about how MySQL is configured and how access is granted to user accounts.
Configuring and Managing Access to MySQL
Now that you have MySQL installed locally, let’s briefly discuss how to configure MySQL and how to grant access to the server (and databases) to others as well as how to set up the X Plugin (the key component to enable the Document Store). We begin with a look at the configuration file used to define the behavior and configure options in MySQL.
Configuration Files
MySQL Configuration File Excerpt
Notice we have settings grouped by section defined using square brackets []. For example, we see a section named [client], which is used to define options for any MySQL client that reads the configuration file. Similarly, we see a section named [mysqld], which applies to the server process (because the executable is named mysqld). Notice also we see settings for basic options like port, socket, etc. However, we can also use the configuration file to set options for InnoDB, replication, and more.
I recommend locating and browsing the configuration file for your installation so that you can see the options and their values. If you encounter a situation where you need to change an option – say to test the effect or perhaps to experiment – you can use the SET command to change values either as a global setting (affects all connections) or a session setting (applies only to the current connection).
However, if you change a global setting that is also in the configuration file, the value (state) will remain only until the server is rebooted. Thus, if you want to keep global changes, you should consider placing them in the configuration file.
For more information about the configuration file and how to use it to configure MySQL 8 including using multiple option files and where the files exist on each platform, see the section entitled, “Using Option Files” in the online reference manual (http://dev.mysql.com/doc/refman/8.0/en/).
Creating Users and Granting Access
The first command creates the user named hvac_user1, but the name also has an @ followed by another string. This second string is the host name of the machine with which the user is associated. That is, each user in MySQL has both a username and a host name, in the form user@host, to uniquely identify them. That means the user and host [email protected] and the user and host [email protected] are not the same. However, the % symbol can be used as a wildcard to associate the user with any host. The IDENTIFIED BY clause sets the password for the user.
It is always a good idea to create a user for your application that does not have full access to the MySQL system. This is so you can minimize any accidental changes and also to prevent exploitation. For example, it is recommended that you create a user with access only to those databases where you store (or retrieve) data.
Also be careful about using the wildcard % for the host. Although it makes it easier to create a single user and let the user access the database server from any host, it also makes it much easier for someone bent on malice to access your server (once they discover the password).
The second command allows access to databases. There are many privileges that you can give a user. The example shows the most likely set that you would want to give a user of a sensor network database: read (SELECT), add data (INSERT), and change data (UPDATE). See the online reference manual for more about security and account access privileges.
The command also specifies a database and objects to which to grant the privilege. Thus, it is possible to give a user read (SELECT) privileges to some tables and write (INSERT, UPDATE) privileges to other tables. This example gives the user access to all objects (tables, views, and so on) in the room_temp database.
Basic SQL Commands
If you have never used a database system, learning and mastering the system requires training, experience, and a good deal of perseverance. Chief among the knowledge needed to become proficient is how to use the common SQL commands and concepts. This section completes the primer on MySQL by introducing the most common MySQL commands and concepts as a foundation for learning how to use the Document Store.
Rather than replicate the reference manual, this section introduces the commands and concepts at a high level. If you decide to use any of the commands or concepts, please refer to the online reference manual for additional details, complete command syntax, and additional examples.
This section reviews the most common SQL and MySQL-specific commands that you will need to know to get the most out of your MySQL server databases. While you have already seen some of these in action, this section provides additional information to help you use them.
One important rule to understand is user-supplied variable names are case sensitive and obey case sensitivity of the host platform. Check the online reference manual for your platform to see how case sensitivity affects user-supplied variables.
Most of the example queries in this section are taken from an Internet of Things (IoT) application where data is recorded from one or more sensors and devices. Regardless, the examples represent typical ways we interact with MySQL via SQL statements.
Creating Databases and Tables
The most basic commands you will need to learn, and master are the CREATE DATABASE and CREATE TABLE commands. Recall that database servers such as MySQL allow you to create any number of databases that you can add tables and store data in a logical manner.
To create a database, use CREATE DATABASE followed by a name for the database. If you are using the MySQL client, you must use the USE command to switch to a specific database. The client focus is the latest database specified either at startup (on the command line) or via the USE command.
If you want to see all the databases on the server, use the SHOW DATABASES command.
Notice here that I specified the table name (plants) and four columns (plant_name, sensor_value, sensor_event, and sensor_level). I used several data types. For plant_name, I used a character field with a maximum of 30 characters, a floating-point data type for sensor_value, a timestamp value for sensor_event, and another character field for sensor_level of five characters.
The TIMESTAMP data type is of particular use any time you want to record the date and time of an event or action. For example, it is often helpful to know when a sensor value is read. By adding a TIMESTAMP column to the table, you do not need to calculate, read, or otherwise format a date and time when the value is inserted into the database table.
Notice also that I specified that the plant_name column be defined as a key, which creates an index. In this case, it is also the primary key. The PRIMARY KEY phrase tells the server to ensure there exists one and only one row in the table that matches the value of the column. You can specify several columns to be used in the primary key by repeating the keyword. Note that all primary key columns must not permit nulls (NOT NULL).
If you cannot determine a set of columns that uniquely identify a row (and you want such a behavior – some favor tables without this restriction, but a good DBA would not), you can use an artificial data type option for integer fields called AUTO INCREMENT. When used on a column (must be the first column), the server automatically increases this value for each row inserted. In this way, it creates a default primary key. For more information about auto increment columns, see the online reference manual.
Best practices suggest using a primary key on a character field to be suboptimal in some situations such as tables with large values for each column or many unique values. This can make searching and indexing slower. In this case, you could use an auto increment field to artificially add a primary key that is smaller in size (but somewhat more cryptic).
There are far more data types available than those shown in the previous example. You should review the online reference manual for a complete list of data types. See the section “Data Types.” If you want to know the layout or “schema” of a table, use the SHOW CREATE TABLE command.
Like databases, you can also get a list of all the tables in the database with the SHOW TABLES command.
Searching for Data
The most used basic command you need to know is the command to return the data from the table (also called a result set or rows). To do this, you use the SELECT statement. This SQL statement is the workhorse for a database system. All queries for data will be executed with this command. As such, we will spend a bit more time looking at the various clauses (parts) that can be used starting with the column list.
While we examine SELECT statements first, if you want to try these out on your system, be sure to run the INSERT statements in the following section first.
The SELECT statement allows you to specify which columns you want to choose from the data. The list appears as the first part of the statement. The second part is the FROM clause, which specifies the table(s) you want to retrieve rows from.
The FROM clause can be used to join tables with the JOIN operator. You will see a simple example of a join in a later section.
Example SELECT Statements
Notice that the first two statements result in the same rows as well as the same columns in the same order, but the third statement, while it generates the same rows, displays the columns in a different order.
There are additional clauses you can use including the GROUP BY clause, which is used for grouping rows for aggregation or counting, and the ORDER BY clause, which is used to order the result set. Let’s take a quick look at each starting with aggregation.
GROUP BY Example
Notice I specified the average function, AVG(), in the column list and passed in the name of the column I wanted to average. There are many such functions available in MySQL to perform some powerful calculations. Clearly, this is another example of how much power exists in the database server that would require many more resources on a typical lightweight sensor or aggregator node in the network.
Notice also that I renamed the column with the average with the AS keyword. You can use this to rename any column specified, which changes the name in the result set, as you can see in the listing.
ORDER BY Examples
As I mentioned, there is a lot more to the SELECT statement than shown here, but what we have seen here will get you very far, especially when working with data typical of most small to medium-sized database solutions.
Creating Data
In this example, I am inserting data for one of my plants by specifying the name and value. What about the other columns, you wonder? In this case, the other columns include a timestamp column, which will be filled in by the database server. All other columns (just the one) will be set to NULL, which means no value is available, the value is missing, the value is not zero, or the value is empty.
Notice I specified the columns before the data for the row. This is necessary whenever you want to insert data for fewer columns than what the table contains. More specifically, leaving the column list off means you must supply data (or NULL) for all columns in the table. Also, the order of the columns listed can be different from the order they are defined in the table. Leaving the column list off will result in the ordering of the column data based on how they appear in the table.
Here I’ve inserted several rows with the same command. Note that this is just a shorthand mechanism and, except for automatic commits, no different than issuing separate commands.
Updating Data
There are times when you want to change or update data. You may have a case where you need to change the value of one or more columns, replace the values for several rows, or correct formatting or even scale of numerical data. To update data, we use the UPDATE command. You can update a particular column, update a set of columns, perform calculations on one or more columns, and more.
Notice the key operator here is the SET operator. This tells the database to assign a new value to the column(s) specified. You can list more than one set operation in the command.
Notice I used a WHERE clause here to restrict the UPDATE to a particular set of rows. This is the same WHERE clause as you saw in the SELECT statement, and it does the same thing; it allows you to specify conditions that restrict the rows affected. If you do not use the WHERE clause, the updates will apply to all rows.
Don’t forget the WHERE clause! Issuing an UPDATE command without a WHERE clause will affect all rows in the table!
Deleting Data
Sometimes you end up with data in a table that needs to be removed. Maybe you used test data and want to get rid of the fake rows, or perhaps you want to compact or purge your tables or want to eliminate rows that no longer apply. To remove rows, use the DELETE FROM command.
Don’t forget the WHERE clause! Issuing a DELETE FROM command without a WHERE clause will permanently delete all rows in the table!
Using Indexes
Tables are created without the use of any ordering. That is, tables are unordered. While it is true MySQL will return the data in the same order each time, there is no implied (or reliable) ordering unless you create an index. The ordering I am referring to here is not like you think when sorting (that’s possible with the ORDER BY clause in the SELECT statement).
Rather, indexes are mappings that the server uses to read the data when queries are executed. For example, if you had no index on a table and wanted to select all rows with a value greater than a certain value for a column, the server will have to read all rows to find all the matches. However, if we added an index on that column, the server would have to read only those rows that match the criteria.
I should note that there are several forms of indexes. What I am referring to here is a clustered index where the value for column in the index is stored in the index, allowing the server to read the index only and not the rows to do the test for the criteria.
Indexes created like this do not affect the uniqueness of the rows in the table, in other words, making sure there exists one and only one row that can be accessed by a specific value of a specific column (or columns). What I am referring to is the concept of a primary key (or primary index), which is a special option used in the creation of the table as described earlier.
Views
Views are not normally encountered in small or medium-sized database solutions, but I include them to make you aware of them in case you decide to do additional analysis and want to organize the data into smaller groups for easier reading.
Triggers
Another advanced concept (and associated SQL command) is the use of an event-driven mechanism that is “triggered” when data is changed. That is, you can create a short set of SQL commands (a procedure) that will execute when data is inserted or changed.
This demonstrates an interesting and powerful way you can create derived columns with the power of the database server and save the processing power and code in your applications. I encourage you to consider this and similar powerful concepts for leveraging the power of the database server.
Simple Joins
One of the most powerful concepts of database systems is the ability to make relationships (hence the name relational) among the data. That is, data in one table can reference data in another (or several tables). The most simplistic form of this is called a master-detail relationship where a row in one table references or is related to one or more rows in another.
A common (and classic) example of a master-detail relationship is from an order-tracking system where we have one table containing the data for an order and another table containing the line items for the order. Thus, we store the order information such as customer number and shipping information once and combine or “join” the tables when we retrieve the order proper.
Let’s look at an example from the sample database named world. You can find this database on the MySQL website (http://dev.mysql.com/doc/index-other.html). Feel free to download it and any other sample database. They all demonstrate various designs of database systems. You will also find it handy to practice querying the data as it contains more than a few, simple rows.
If you want to run the following examples, you need to install the world database as described in the documentation for the example (http://dev.mysql.com/doc/world-setup/en/world-setup-installation.html).
Simple JOIN Example
Here I used a JOIN clause that takes two tables specified such that the first table is joined to the second table using a specific column and its values (the ON specifies the match). What the database server does is read each row from the tables and returns only those rows where the value in the columns specified a match. Any rows in one table that are not in the other are not returned.
You can retrieve those rows with different joins. See the online reference manual at https://dev.mysql.com/doc/refman/8.0/en/join.html for more information about the types of joins possible including the inner and outer joins for more details.
Notice also that I included only a few columns. In this case, I specified the country name and continent from the Country table and the language column from the CountryLanguage table. If the column names were not unique (the same column appears in each table), I would have to specify them by table name such as Country.Name. In fact, it is considered good practice to always qualify the columns in this manner.
There is one interesting anomaly in this example that I feel important to point out. In fact, some would consider it a design flaw. Notice in the JOIN clause I specified the table and column for each table. This is normal and correct but notice the column name does not match in both tables. While this really doesn’t matter, and creates only a bit of extra typing, some DBAs would consider this erroneous and would have a desire to make the common column name the same in both tables.
Another use for a join is to retrieve common, archival, or lookup data. For example, suppose you had a table that stored details about things that do not change (or rarely change) such as cities associated with ZIP codes or names associated with identification numbers (e.g., SSN). You could store this information in a separate table and join the data on a common column (and values) whenever you needed. In this case, that common column can be used as a foreign key, which is another advanced concept.
Foreign keys are used to maintain data integrity (i.e., if you have data in one table that relates to another table, but the relationship needs to be consistent). For example, if you wanted to make sure when you delete the master row that all of the detail rows are also deleted, you could declare a foreign key in the master table to a column (or columns) to the detail table. See the online reference manual for more information about foreign keys.
This discussion on joins touches only the very basics. Indeed, joins are arguably one of the most difficult and often confused areas in database systems. If you find you want to use joins to combine several tables or extend data so that data is provided from several tables (outer joins), you should spend some time with an in-depth study of database concepts such as Clare Churcher’s book Beginning Database Design (Apress, 2012).
Stored Routines
There are many more concepts and commands available in MySQL, but two that may be of interest are PROCEDURE and FUNCTION, sometimes called stored routines. I introduce these concepts here so that if you want to explore them, you understand how they are used at a high level.
Suppose you need to run several commands to change data. That is, you need to do some complex changes based on calculations. For these types of operations, MySQL provides the concept of a stored procedure. The stored procedure allows you to execute a compound statement (a series of SQL commands) whenever the procedure is called. Stored procedures are sometimes considered an advanced technique used mainly for periodic maintenance, but they can be handy in even the more simplistic situations.
For example, suppose you want to develop your own database application that uses SQL, but since you are developing it, you need to periodically start over and want to clear out all the data first. If you had only one table, a stored procedure would not help much, but suppose you have several tables spread over several databases (not unusual for larger databases). In this case, a stored procedure may be helpful.
When entering commands with compound statements in the MySQL client, you need to change the delimiter (the semicolon) temporarily so that the semicolon at the end of the line does not terminate the command entry. For example, use DELIMITER // before writing the command with a compound statement, use // to end the command, and change the delimiter back with DELIMITER ;. This is only when using the client.
Since stored procedures can be quite complicated, if you decide to use them, read the “CREATE PROCEDURE and CREATE FUNCTION Syntax” section of the online reference manual before trying to develop your own. There is more to creating stored procedures than described in this section.
Now suppose you want to execute a compound statement and return a result – you want to use it as a function. You can use functions to fill in data by performing calculations, data transformation, or simple translations. Functions therefore can be used to provide values to populate column values, provide aggregation, provide date operations, and more.
You have already seen a couple of functions (COUNT, AVG). These are considered built-in functions, and there is an entire section devoted to them in the online reference manual. However, you can also create your own functions. For example, you may want to create a function to perform some data normalization on your data. More specifically, suppose you have a sensor that produces a value in a specific range, but depending on that value and another value from a different sensor or lookup table, you want to add, subtract, average, and so on, the value to correct it. You could write a function to do this and call it in a trigger to populate the value for a calculation column.
You may be wondering what you do when you need to modify a table, procedure, trigger, and so on. Rest easy, you do not have to start over from scratch! MySQL provides an ALTER command for each object. That is, there is an ALTER TABLE, ALTER PROCEDURE, and so on. See the online reference manual section entitled “Data Definition Statements” for more information about each ALTER command.
Now that we have learned what the basic SQL commands are and how to use them, let’s look at how we can connect our applications to MySQL.
Connecting Applications
You have already seen how to connect to the MySQL server with the MySQL client and MySQL Shell. These tools are interactive tools where we can execute queries, but it isn’t helpful for saving data from our applications or other users. What we need is something called a connector. A connector is a programming module designed to permit our scripts or programs to send data to the database server. Connectors also allow us to query the database server to get data from the server.
I will cover two primary connectors you are likely to encounter when developing your own applications. I present each as a tutorial that you can use to follow. I begin with a connector for use with Python scripts (Connector/Python) and then present a connector for use in writing simplified Java (Connector/J). But first, let’s see what connectors are available for our applications.
MySQL Database Connectors
MySQL Connectors
Connector | Description | Download URL |
|---|---|---|
C API (libmysqlclient) | A client library for C development | |
Connector/C++ | Standardized C++ applications | |
Connector/J | Java applications | |
Connector/Net | Windows .Net platforms | |
Connector/Node.js | Node.js applications | |
Connector/ODBC | Generalized ODBC applications | |
Connector/Python | Python applications | |
MySQL native driver for PHP (mysqlnd) | PHP 5.3 or newer connector |
As you can see, there is a connector for just about any programming language you are likely to encounter. You can find documentation for each of the connectors above at https://dev.mysql.com/doc/connectors/en/.
Sample Database
Now let’s take a look at two examples starting with Connector/Python.
Example Connector: Connector/Python
The connector for Python from Oracle is a full-featured connector that provides connectivity to the MySQL database server for Python applications and scripts. Connector/Python features support for all current MySQL server releases. It is written to provide automatic data type conversion between Python and MySQL, making building queries and deciphering results easy. It also has support for compression, permits connections via SSL, and supports all MySQL SQL commands.
Connector/Python must be installed on the PC where you will run your code in the same manner any Python library that you may use. Using Connector/Python in your Python scripts consists of importing the base module, initiating a connection, and executing queries with a cursor.
Before we jump into how we can use Connector/Python to write some MySQL database–enabled applications, let’s talk about how to get and install Connector/Python.
The Python programming language is a high-level language designed to be as close to like reading English as possible while being simple, easy to learn, and powerful. Pythonistas will tell you the designers have indeed met these goals.
Programming the Raspberry Pi by Simon Monk (McGraw-Hill, 2013)
Beginning Python from Novice to Professional, 2nd Edition, by Magnus Lie Hetland (Apress, 2008)
Python Cookbook by David Beazley and Brian K. Jones (O’Reilly Media, 2013)
Interestingly, Python was named after the British comedy troupe Monty Python and not the reptile. As you learn Python, you may encounter campy references to Monty Python episodes. Having a fondness for Monty Python, I find these references entertaining. Of course, your mileage may vary.
Installing Connector/Python
Downloading is the same process as you discovered for the server. You can download Connector/Python from Oracle’s MySQL website (http://dev.mysql.com/downloads/connector/python/). The page will automatically detect your platform and show the available downloads for your platform. You may see several choices. Be sure to choose the one that matches your configuration.
Notice the pip installer command. The command pip3 is used in this example because the PC has both Python 2.X and 3.X installed and we want to ensure we install it for the Python 3.X installation.
You should install Python 3.7 or later.
Also notice any prerequisites required for the package being installed are also automatically downloaded and installed. As you can see, using pip is much easier than downloading and installing packages separately.
See the online reference manual for specific notes about installing on some platforms (http://dev.mysql.com/doc/connector-python/en/connector-python-installation.html).
Checking the Installation
What you should see is the version of Connector/Python printed. If you see any errors about not finding the connector, be sure to check your installation to ensure it worked. Once you can successfully access Connector/Python, you’re ready to move on to some example scripts.
If you have and are familiar with a Python integrated development environment (IDE), you can use that instead of creating a file with a text editor and executing it via the command line. Examples of good Python IDEs include Thonny (https://thonny.org/) and PyCharm (www.jetbrains.com/pycharm/download). Both are available for Windows, macOS, and Linux.
Thonny is a free, basic IDE and therefore very easy to use but limited in features whereas PyCharm is available in a community and enterprise editions supporting a host of features for enterprise-wide Python development.
Example 1: Connecting to MySQL
Let’s start with a simple example where we connect to the MySQL server and get a list of databases. We will name this example mysql_connector.py.
In this case, we start by importing the Connector/Python connector class and then to keep things tidy, we use a dictionary to store the connection information. Once we have the dictionary for the connection, we call the connect() method to connect to the server.
Next, we open a new cursor and get an instance of the cursor class with the cursor() method of the connection class. With the cursor class, we can then call the execute() method passing in a SQL statement and once executed, fetch the rows returned and print out the first column in each row. Finally, we close both the cursor and connection to tidy things up. Listing 3-10 shows the complete code for this example. As you will see, it is very easy to follow.
Be sure to change the user and password to match your installation.
MySQL Connect and Query Example
Once you have to code entered, you can execute it to see the results as shown below:
Be sure your MySQL server is running, and you provide the correct password and hostname for the server in the dictionary.
Depending on what sample databases or other databases you have installed or created, your results may be different, but you should see the plant_monitoring, mysql, information_schema, and performance_schema at a minimum.
Now let’s look at how to insert data.
Example 2: Inserting Data
Now let’s see how we can insert some data in a table. We will name this example code mysql_insert.py.
In this case, we simply want to read data from a file and insert it into a table. We will use the same code as the previous example to connect to the server and use a cursor to execute a query. The difference is we will use a file to read in sample data in a comma-separated value format (.csv). It is a common format used in a variety of applications.
MySQL Insert Data Example
You can do much more with the connector than shown here. You should read the online reference manual (https://dev.mysql.com/doc/connector-python/en/) for more information and examples of how to use the connector to meet your application needs.
Now, let’s look at a Connector/J example. We will use the same examples only implemented in Java.
Example Connector: Connector/J
The connector for Java from Oracle is a full-featured connector that provides connectivity to the MySQL database server for Java applications. Connector/J features support for all current MySQL server releases. It is a full-featured connector with all of the necessary features for creating secure connections and supports all MySQL SQL commands.
Connector/J must be installed on the PC where you will run your code in the same manner any Java library that you may use. Using Connector/J in your applications consists of importing the base module, initiating a connection, and executing queries with a cursor.
To keep things simple, we will be using a simplistic form of Java programming to write and execute the examples. More specifically, we will use a simple text editor to create the code files and the Java Development Kit (JDK) to compile the code (javac) and execute (java). If you have experience with more robust Java IDEs, you can use those instead.
The Java Runtime Environment (JRE) is not the same as the JDK. You will have to install the JDK even if you have the JRE installed.
Naturally, to use Connector/J, you will need to ensure you have the latest version of the Java Runtime Environment installed on your PC. Most PCs have the JDK installed. However, if you want to check, simply issue javac --version in a terminal. If you get an error that the command cannot be found, visit www.oracle.com/java/technologies/downloads/ to learn how to download and install JDK on your PC.
You should use JDK version 8.0 or later.
Before we jump into how we can use Connector/J to write some MySQL database–enabled applications, let’s talk about how to get and install Connector/J.
Installing Connector/J
Downloading is the same process as you discovered for the server. You can download Connector/J from Oracle’s MySQL website (http://dev.mysql.com/downloads/connector/j/). The page will automatically detect your platform and show the available downloads for your platform. You may see several choices. Be sure to choose the one that matches your configuration. Note, however, there is no installation package for macOS. If you do not see your platform in the list, you can download using the Platform Independent operating system option.
In this demonstration, we will use the Platform Independent option. We do this to demonstrate how you can use this option and use classes from an installation directory (folder) rather than install it on the system. You may want to do this if you are working on a system used for Java development as to not disrupt your IDE or Java installation.
Simply choose the Platform Independent operating system option from the download page and download either the .zip or .tar.gz file. Once downloaded, copy the file to your project directory and unzip it. For example, if you had a folder named ../Ch03/java to store the examples in this section, you can unzip the file in that directory. This will result in a folder in the same path named ../Ch03/java/ mysql-connector-java-8.0.28 (or similar if you download a newer version of the connector).
See the online reference manual for specific notes about installing on some platforms (https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-installing.html).
Checking the Installation
Once Connector/J is installed, you can verify it is working with the following short example. We will create a new code file named MySQLTest.java with a class of the same name.
Connector/J works with the Java database connectivity (JDBC) libraries. As such, rather than supply individual parameters for the connection, we build a universal resource locator3 (URL) that corresponds to the JDBC standard.
host: the host name of the MySQL server. The default value is 127.0.0.1.
port: the port number of the MySQL server. The default value is 3306.
database: name of the default database for the connection.
failoverhost: hostname of a standby database server. MySQL Connector/J supports failover when a connection fails. See the online reference for more details.
propertyNameN = propertyValueN: one or more ampersand-separated list of properties (optional)
For a complete tutorial on JDBC, see Oracle’s JDBC tutorial website https://docs.oracle.com/javase/tutorial/jdbc/basics/index.html.
Notice we left off the user and password. To make the connection to the server, we will use the Java DriverManager class to get the connection. We supply those as additional parameters when we call the getConnection() method of the DriverManager class.
Now that we understand how to make the connection, let’s look at an example Java application that is designed to test if the Connector/J is installed. In this example, we attempt a connection using a user account that does not exist. We do this to ensure we will get the MySQL access denied error message. If it succeeds without that specific error, we know something else is wrong (it should not succeed). Conversely, if the connection generates a different error, we know that Connector/J is either not installed (not found on the CLASSPATH) or something else is wrong. Either way, we print the message for the exception so the user can determine a course of action to fix the issue.
Test Connector/J Example
Once you confirm Connector/J is installed and working, you are ready for the examples.
Example 1: Connecting to MySQL
Let’s start with a simple example where we connect to the MySQL server and get a list of databases. We will name this example MySQLConnect.java.
MySQL Connect and Query Example
Once you have to code entered, you can compile and execute it to see the results as shown below:
Be sure your MySQL server is running, and you provide the correct password and hostname for the server in the dictionary.
Depending on what sample databases or other databases you have installed or created, your results may be different, but you should see the plant_monitoring, mysql, information_schema, and performance_schema at a minimum.
Now let’s look at how to insert data.
Example 2: Inserting Data
Now let’s see how we can insert some data in a table. We will name this example code mysql_insert.py.
In this case, we simply want to read data from a file and insert it into a table. We will use the same code as the previous example to connect to the server and execute a query. The difference is we will use a file to read in sample data in a comma-separated value format (.csv). It is a common format used in a variety of applications.
MySQL Insert Data Example
Once again, these examples only give you the very basics of using the connector. You can do much more with the connector than shown here. You should read the online reference manual (https://dev.mysql.com/doc/connector-j/8.0/en/) for more information and examples of how to use the connector to meet your application needs.
Summary
The MySQL database server is a powerful tool. Given its unique placement in the market as the database server for the Internet, it is not surprising that web developers (as well as many startup and similar Internet properties) have chosen MySQL for their solutions. Not only is the server robust and easy to use, but it is also available as a free community license that you can use to keep your initial investment within budget.
In this chapter, you discovered some of the power of using the MySQL database server in its traditional role using the SQL interface; how to issue commands for creating databases and tables for storing data as well as commands for retrieving that data, and even how to connect your applications to MySQL for storing data. While this chapter presents only a small primer on MySQL, you learned how to get started by practicing with your own installation of MySQL, which will pay dividends when employing the MySQL Database Service in a production environment.
In the next chapter, we will take a deeper look into the MySQL Database Service including how to get started creating and using your first database system (called a dbSystem in MDS).