How Do I Connect to MySQL?

We have already seen a brief look at the new MySQL Shell for connecting to and working with MySQL servers, the AdminAPI to configure an InnoDB Cluster, and the X DevAPI to access with data. However, 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. It is interesting that 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.

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) because 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 will 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.

Using the mysql client on the same machine without the --host (or -h) and --port option does not use a network connection. If you want to connect using a network connection or want to connect using a different port, you must use the loopback address. For example, to connect to a server running on port 3307 on the same machine, use the command mysql -uroot -p –h127.0.0.1 --port=3307. Listing 2-1 shows examples of several SQL commands in action using the mysql client.

In this example, you see DDL in the form of the CREATE DATABASE and CREATE TABLE statements, 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.

Although this list is only a short introduction and not a complete syntax guide, there is an excellent online MySQL reference manual that explains every command (and much more) in greater detail. You should refer to the online MySQL 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 frequently.

If you think that there is a lot more to MySQL than a few simple commands, you are correct. Despite its ease of use and fast start-up 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 online MySQL 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. As of this writing, MySQL 8 was not a GA release and as such only offered as a development milestone release (DMR). DMRs are an excellent way for you to try out new versions and features before they are released as GA. Generally, non-GA releases are considered developmental or in the case of early release candidates such as MySQL 8.0.4, a release candidate. Thus, you should not install and use DMR releases on your production machines.

To download GA releases of MySQL 8, visit http://dev.mysql.com/downloads/ and click Community, then MySQL Community. You can also click on the link near the bottom of the downloads page named Community (GPL) Downloads, then click MySQL Community Server . This is the GPLv2 license of MySQL. The page will automatically detect your operating system. If you want to download for another platform, you can select it from the dropdown list.

The download page will list several files for download. Depending on your platform, you may see several options including compressed files, source code, and installation packages. Most will choose the installation package for installation on a laptop or desktop computer. Figure 2-1 shows an example of the various download options for macOS platforms.

One of the most popular platforms is Microsoft Windows. Oracle has provided a special installation packaging for Windows named the Windows Installer. This package includes all the MySQL products available under the community license including MySQL Server, Workbench, Utilities, and all of the available connectors (program libraries for connecting to MySQL). This makes installing on Windows a one-stop, one-installation affair. Figure 2-2 shows the download page for the Windows installer.

However, you should note that some of the more advanced features and some of the plugins that also are in a developer milestone release (DMR) state may not be included in the Windows Installer. Thus, you should consider installing by using the server package. We see these below the Windows Installer download link in Figure 2-2. 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.

Fortunately, as MySQL 8 matures, more packaging options will become available allowing you to use a semi-automated installation mechanism. Let’s see one of those in action. In this scenario, we will install MySQL 8 on a macOS Sierra machine. In this case, I have downloaded the file mysql-8.0.11-macos10.13-x86_64.dmg, which is a compressed file containing a package installation program named mysql-8.0.11-macos10.13-x86_64.pkg for macOS. Once I launch the installer, the first step is agreeing to the license. Figure 2-3 shows the license agreement panel of the installation dialog.

The license shown is the GPLv2 license for the community edition. You can read the license and when ready, click Continue. You will see an acceptance dialog open, which will give you another chance to read the license.¹ When you’re ready to accept the license, click Accept. Figure 2-4 shows the license acceptance dialog.

The next panel displays the setup or installation type. Early releases such as this version may not show any installation types to choose from. If you run the Windows Installer, you will see several options. For most platforms, the default installation type is all you will need to get started. Figure 2-5 shows the installation type panel. When ready, click Install.

The installation may ask you to authorize the installation and once done, it will proceed rather quickly installing MySQL in the /usr/local/mysql folder (e.g., on Sierra).

If this is the first time you’ve installed MySQL 8, you will see a dialog that displays the default password for the root account. This was a change made in MySQL 5.7, which eliminated anonymous accounts and made server installations more secure. You should take note of this password, as it is a general random collection of characters and symbols that you won’t be able to guess. Figure 2-6 shows one such example dialog.

Figure 2-7 shows how you can recover this dialog on macOS from the notification center if you, like me, tend to dismiss dialogs without fully reading them.²

Once complete, you will get a completion dialog, which you can safely dismiss. Finally, you will be asked whether you want to keep the installation file (the .dmg) or delete it. If you are experimenting with MySQL 8 or think you may want to install it some other place, do not delete the file.

As you may have surmised, you need to change the root password as your first action after installation. Doing so is easy. Just open the MySQL client (mysql) and issue the following SQL statement. Because we installed the server in the default location, we can start the client with only the user and password prompts like this: mysql -uroot -p. The client will prompt you for the password.

If you get a message that you cannot connect to the server, it may mean the server has not been started. You can start the server on macOS with the following command.

Okay, now that we have the MySQL 8 server installed, we can begin configuring the server for use. You could install the MySQL Shell at this point, but we will explore how to install the MySQL Shell in more detail in Chapter 4.

Configuring and Managing Access to MySQL

Now that you know how to install MySQL, 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 setup 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.

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.

You can override this by referencing the database name first. For example, SELECT * FROM db1.table1 will execute regardless of the default database set. However, leaving off the database name will cause the mysql client to use the default database. The following shows two commands to create and change the focus of the database:

Creating a table requires the, yes, CREATE TABLE command. This command has many options allowing you to specify not only the columns and their data types but also additional options such as indexes, foreign keys, and so on. An index also can be created using the CREATE INDEX command (see the following code). The following code shows how to create a simple table for storing plant sensor data such as what may be used for monitoring a personal greenhouse.⁴

Note 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 at the sensor or even aggregate node.

Note 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 that 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 will not), you can use an artificial data type option for integer fields called AUTO INCREMENT . When used on a column (this 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 MySQL reference manual.

There are far more data types available than those shown in the previous example. You should review the online MySQL 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.

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.

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 order that you specify the columns determines the order shown in the result set. If you want all of the columns, use an asterisks (*) instead. Listing 2-3 demonstrates three statements that generate the same result sets. That is, the same rows will be displayed in the output of each. In fact, I am using a table with only four rows for simplicity.

Note that the first two statements result in the same rows as well as the same columns in the same order. However, the third statement although it generates the same rows, displays the columns in a different order.

You also can use functions in the column list to perform calculations and similar operations. One special example is using the COUNT() function to determine the number of rows in the result set, as shown here. See the online MySQL reference manual for more examples of functions supplied by MySQL.

The next clause in the SELECT statement is the WHERE clause. This is where you specify the conditions you want to use to restrict the number of rows in the result set. That is, only those rows that match the conditions. The conditions are based on the columns and can be quite complex. That is, you can specify conditions based on calculations, results from a join, and more. But most conditions will be simple equalities or inequalities on one or more columns to answer a question. For example, suppose you wanted to see the plants where the sensor value read is less than 0.40? In this case, we issue the following query and receive the results. Note that I specified only two columns: the plant name and the value read from sensor.

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.

Suppose you wanted to average the sensor values read in the table for each sensor. In this case, we have a table that contains sensor readings over time for a variety of sensors. Although the example contains only four rows (and thus may not be statistically informative), the example demonstrates the concept of aggregation quite plainly, as shown in Listing 2-4. Note that what we receive is simply the average of the four sensor values read.

Note that 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.

Also note that I renamed the column with the average using 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.

Another use of the GROUP BY clause is counting. In this case, we replaced AVG() with COUNT() and received the number of rows matching the WHERE clause. More specific, we want to know how many sensor values were stored for each plant.

Now let’s say we want to see the results of our result set ordered by sensor value. We use the same query that selected the rows for the fern on the deck, but we order the rows by sensor value in ascending and descending order using the ORDER BY clause. Listing 2-5 shows the results of each option.

As I mentioned, there is a lot more to the SELECT statement than shown here, but what we have seen will get you very far, especially when working with data typical of most small- to medium-sized database solutions.

Creating Data

Now that you have a database and tables created, you will want to load or insert data into the tables. You can do so using the INSERT INTO statement. Here we specify the table and the data for the row. The following shows a simple example:

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.

Note that 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. To be more specific, 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 the column data based on how they appear in the table.

You can also insert several rows using the same command by using a comma separated list of the row values, as shown here:

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.

What may be more likely is you or your users will want to rename an object in your database. For example, suppose we determine the plant on the deck is not actually a fern but was an exotic flowering plant. In this case, we want to change all rows that have a plant name of “fern on deck” to “flowers on deck.” The following command performs the change:

Note that 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.

Note 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.

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. Perhaps you want to compact or purge your tables or you want to eliminate rows that no longer apply. To remove rows, use the DELETE FROM command.

Let’s look at an example. Suppose you have a plant-monitoring solution under development and you’ve discovered that one of your sensors or sensor nodes are reading values that are too low because of a coding, wiring, or calibration error. In this case, we want to remove all rows with a sensor value less than 0.20. The following command does this:

Note that I used a WHERE clause here. That is, a conditional statement to limit the rows acted on. You can use whatever columns or conditions you want; just be sure you have the correct ones! I like to use the same WHERE clause in a SELECT statement first. For example, I would issue the following first to check that I am about to delete the rows I want and only those rows. Note that it is the same WHERE clause.

Using Indexes

Tables are created without the use of any ordering; that is, they are unordered. Although 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 what 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.

To create an index, you can either specify the index in the CREATE TABLE statement or issue a CREATE INDEX command. The following shows a simple example:

This command adds an index on the plant_name column. Observe how this affects the table.

Indexes created like this do not affect the uniqueness of the rows in the table. In other words, make sure that 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 logical mappings of results of one or more tables. They can be referenced as if they were tables in queries, making them a powerful tool for creating subsets of data to work with. You create a view with CREATE VIEW and give it a name similar to a table. The following shows a simple example where we create a test view to read values from a table. In this case, we limit the size of the view (number of rows), but you could use a wide variety of conditions for your views, including combining data from different tables.

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.

There are several events or conditions under which the trigger will execute. You can set up a trigger either before or after an update, insert, or delete action. A trigger is associated with a single table and has as its body a special construct that allows you to act on the rows affected. The following shows a simple example:

This trigger will execute before each insert into the table. As you can see in the compound statement (BEGIN . . . END), we set a column called sensor_level to LOW, OK, or HIGH depending on the value of the sensor_value. To see this in action, consider the following command. The FOR EACH ROW syntax allows the trigger to act on all rows in the transaction.

Because the value we supplied is less than the middle value (0.70), we expect the trigger to fill in the sensor_level column for us. The following shows this indeed is what happened when the trigger fired:

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 in which 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 web site ( 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 also will find it handy to practice querying the data as it contains more than a few, simple rows.

Listing 2-6 shows an example of a simple join. There is a lot going on here, so take a moment to examine the parts of the SELECT statement, especially how I specified the JOIN clause. You can ignore the LIMIT option because that simply limits the number of rows in the result set.

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.

Note 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. Note in the JOIN clause that I specified the table and column for each table. This is normal and correct, but note that the column name does not match in both tables. Although 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 MySQL 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 the 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 because 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.

Because stored procedures can be quite complicated, if you decide to use them, read the “CREATE PROCEDURE and CREATE FUNCTION Syntax” section of the online MySQL 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 MySQL reference manual. However, you also can create your own functions. For example, you may want to create a function to perform data normalization on your data. More specific, 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 a trigger to populate the value for a calculation column.