MySQL Essentials - nikos dimitrakas



MySQL Essentials

[pic]

July 2018

version 2.0



nikos dimitrakas

[pic]

Table of contents

1 Introduction 3

1.1 MySQL 3

2 Installation and Configuration 3

3 User Interface 6

3.1 Working with SQL 9

3.2 Working with the graphical interface 9

3.2.1 Creating a database 9

3.2.2 Creating tables 10

3.2.3 Creating Foreign Keys 12

3.2.4 Editing table data 13

3.2.5 Editing Results 15

3.3 Command line 16

3.3.1 mysql 16

3.3.2 mysqldump 18

3.3.3 Other Commands 19

3.4 Other facilities 19

4 Embedded SQL 19

4.1 Linking MySQL to Access 20

5 Epilogue 20

Introduction

The goal of this compendium is to give a short introduction of the database manager MySQL and some of its most commonly used features. It is assumed that the reader already possesses some basic understanding of relational databases and SQL. This document consists of the following parts:

1. A short introduction to what MySQL is.

2. A description of how to set up the MySQL environment.

3. A presentation of the MySQL user interface, including the MySQL Workbench and some command line options.

4. A short description of how to connect a program to a MySQL database.

This introduction is based on version 8 of MySQL on Windows 10, but it should be possible to get something out of it even if you are working with other versions.

1 MySQL

MySQL is an open source database manager. Even though MySQL does not have as wide support of SQL as other database managers, it is still a popular choice. There is a lot of information about the MySQL product family at .

MySQL consists of several components. In this introduction we will only look at the following:

1. MySQL Database Server (version 8.0.12)

This is the actual database engine, and it also includes a few command line tools for performing most database tasks.

2. MySQL Workbench (version 8.0.12)

This is a graphical tool for having an overview of most aspects of any MySQL server. It is also a graphical tool for working with SQL statements and data in any MySQL database.

Apart for the components mentioned above, MySQL offers tools for database migration, SAP/R3 support, as well as several application programming interfaces (APIs).

Installation and Configuration

In this chapter we take a quick look at the installation and configuration of the MySQL components mentioned earlier. This chapter provides some generic instructions for installing and configuring MySQL on any Windows platform.

In order to install the previously described environment on any windows computer, you can use the MySQL Installer for Windows, which can be downloaded from

MySQL Installer can be used to select features to install. When installing each component you may be asked several questions by the wizard. For the most part, the default settings will be fine. But here are some things that you should pay attention to during the installation:

In the first step select “Custom” in order to select specific features in the next step:

[pic]

Now select the relevant products/features (Server, Workbench, Connector/J, Connector/ODBC):

[pic]

After that, the Installer will check the requirements and may install some prerequisites. Finally, the selected features will be downloaded and installed. After that, you will get the option to configure the installed server. Most of the defaults are fine. Here are some settings to pay attention to:

MySQL can be configured as a Windows service that starts automatically on system startup. You can choose to not start MySQL automatically, but then you will need to start it manually when you need it.

[pic]

In the final step, the configuration will by applied to the system. This step may take a while.

Once the configuration of MySQL is complete, a Windows service should have been created. In order to see if the service has been created (and to check if it is running, start it or stop it) you may run (Win+R) the utility services.msc:

[pic]

In the Services window you can see the MySQL service:

[pic]

The service may be already running if you chose to start it automatically.

User Interface

Working with MySQL implies using the MySQL Workbench and some command line commands. The first one is a graphical tool that makes certain commonly performed functionality (like creating tables and editing data) easier.

Before using the Workbench or trying to execute commands, make sure that the MySQL service is running. Once the service is running, you can work with your server. In the main window of the Workbench, a connection to the local server should have been created (based on the configuration specified during the installation):

[pic]

If the connection is missing, you can create it by specifying the host, port and username and a name for the connection:

[pic]

In order to connect, you need to specify the root password (that you specified during the configuration of the server):

[pic]

Once a connection has been established successfully, you will get one tab for the connection:

[pic]

On the left of the new tab, there is a house icon that can be used to go back to the view with the connections.

The new tab is divided into several parts. On the top left we have the Navigator. The Navigator has some server management options as well as the schemas (databases) available on this server. On the bottom right there is information about executed commands. On the top right (in the panel “SQL Additions”), syntax help can be displayed. The main panel is in the middle and shows query/script tabs and also other tabs for database management and table design. The left, right and bottom panel can be hidden in order to maximize the main panel. Use the buttons on the top right or the View > Panels menu to show/hide different panels. When a query tab in the main panel is used, it will split into two parts. The top part contains the SQL query, while the bottom part will show the result once the query has been executed (if the query has a result).

The Workbench can for example look like this after hiding a few panels and after splitting the Administration and the Schemas into two separate tabs in the Navigator:

[pic]

1 Working with SQL

Most things (creating databases, tables, views, manipulating and viewing data, etc.) can be achieved with SQL. In MySQL Workbench, SQL can be executed in a query tab. SQL can be executed as a script or as a single statement. To execute a statement or script, use the lightning buttons or the options under the Query menu (or the associated keyboard shortcuts).

Note: When executing a query, it is necessary that MySQL knows which database the tables in the query come from. One way is to right click on the database that you want to use and select the option "Set as Default Schema". The same can also be achieved with the USE command (for example “USE testdb”). Then any unqualified tables you use in your query will be assumed to belong to the selected schema/database. Another way is to qualify any tables/views/etc. in your query with the schema name, so instead of writing “person”, you could write “testdb.person”.

2 Working with the graphical interface

All the common tasks (creating databases, tables, keys, indexes, etc, as well as editing the data of tables) can also be performed without writing SQL statements. The following section show how to use the graphical interface for some of these common tasks.

1 Creating a database

We can start by creating a new database. In MySQL, databases are also called schemas (and even catalogs).

Right-click in the schemas area in the Navigator panel and select “Create schema…”:

[pic]

A new tab will open where the database name (schema name) can be specified. Give it a name (for example testdb) and press Apply to create the database!

[pic]

A very nice feature in MySQL Workbench is that the SQL statement corresponding to any change done graphically is displayed prior to being executed.

[pic]

Press Apply once more! A new database has now been created and added to the list of schemas in the Navigator.

2 Creating tables

To create a new table, click on the button "Create Table" (after selecting the correct database as default) or by right-clicking on “Tables” under the correct database in the Navigator. The Table Editor comes up, where you can define columns and keys:

[pic]

Let's create the following two tables:

Person (pid, name)

Car (licensenumber, owner)

Car.owner Run or by opening a Command prompt from the start menu).

Unfortunately, the latest version of MySQL does not configure the system PATH to include the MySQL folder. That means that the commands are not recognized unless either you are in the MySQL folder, or you give the complete path to the commands. The most practical solution is to fix the environment variable PATH to include the bin folder in the MySQL folder (probably “C:\Program Files\MySQL\MySQL Server 8.0\bin”). To fix the PATH variable, select “Edit the system environment variables” from the start menu or from the control panel. Then add the path to the bin folder to the PATH variable. In the following section it is assumed that the PATH is fixed to include the MySQL bin folder.

1 mysql

The command mysql has many functions. One of its functions is to provide an interactive command line environment where you can execute sql statements. If you type

mysql ––help in a command prompt, you will see a long list of parameters. The most interesting parameters are:

• -u username

Allows you to specify the user name to use when connecting to the database server

• -ppassword (or just –p)

Allows you to specify the password to use (or specifies that you should be asked for a password)

• -h host

Allows you to specify the address of the database server to connect to. Default is localhost (the computer you are using)

• -D database

Allows you to specify the name of the database to use.

• -e command

Allows you to execute one command without entering the command line environment.

• -vvv

Forces all commands entered to be echoed. This is especially useful if you are planning to execute a script and you would like to see each command as it is being run.

So you could for example write the following command to connect as root to the localhost database server, to use the database testdb and to specify that you should be asked for a password:

mysql -h localhost -u root -p -D testdb

Once you have supplied the password the command prompt will change to mysql> to indicate that you are inside the mysql command line environment. Here you can run any SQL statement you want and also get the result directly. Make sure to finish all SQL statements with semicolon. Here is a simple example:

[pic]

If you type help while inside the mysql command line environment, you will get a list of available commands. A couple of useful commands are:

• source filename

Allows you to execute commands stored in a text file. This would be equivalent to writing the commands one by one.

• use database

Allows you to change database.

• exit or quit

Allows you to return to the windows command prompt.

So if we have a file script.txt with a few commands, we could execute it with any of the following ways:

1. First enter the mysql command line environment and then type source script.txt

2. Add the argument –e "source script.txt" to the mysql command

3. Pipe the file as input to the mysql command with the windows input pipe character "" and specify the filename:

mysqldump -u root -p -h localhost testdb >newscript.txt

3 Other Commands

MySQL offers some more command line commands. They will not be covered here, but we can mention a couple of them:

• mysqlcheck

This program can be used to CHECK, REPAIR, ANALYZE or OPTIMIZE tables.

• mysqlimport

Loads tables with data from text files.

• mysqlshow

Shows the structure of a databases, tables and columns.

4 Other facilities

MySQL Workbench offers more functionality, not described in the previous sections. One useful feature is working with models. A model is a collection of database objects (like tables and views). A model can also have one or more diagrams. Unlike a database created on a specific connection (a specific database server), a model is not a database. A model can be used to design a database in a graphical environment using diagrams, but it can also be used to generate SQL statements for actually creating the database on a specific database server. A model can be synchronized with a database on a specific database server and such synchronization can be done in both directions (either to update the model based on the state of the database, or to update the database based on the state of the model). Even though this is a powerful feature, the diagrams in a MySQL model (as rendered in MySQL Workbench) can be confusing, especially in the way primary keys and foreign keys are visualized. Use with care!

Embedded SQL

In many cases it is relevant to connect a program to your database. In order to do this, your program must know how to communicate with the database. This is done using so called database drivers. For each language and each database manager there are specific drivers. There are MySQL drivers for ODBC, Java, PHP, .NET, and many more languages. The necessary drivers can be downloaded from here:



In order to use the drivers you will need to make sure they are available to your program (and perhaps to the compiler). This procedure is different for each programming language or environment.

For example in Java the driver is called com.mysql.jdbc.Driver and to connect to a MySQL server you must use the following connection URL syntax:

jdbc:mysql://:/

For example:

jdbc:mysql://localhost:3306/testdb

Then the file mysql-connector-java-8.0.12.jar (or another version) must be available to the Java execution engine / Java Runtime Environment / Java Virtual Machine. The JAR file is available in “C:\Program Files (x86)\MySQL\Connector J 8.0” if you installed the connector (from the MySQL Installer) and it is also available in the file mysql-connector-java-8.0.12.zip that can be downloaded from the MySQL site.

1 Linking MySQL to Access

If you want to link you MySQL tables to Microsoft Access, then you will need to create an ODBC alias for your MySQL database. This requires an ODBC driver for MySQL. The MySQL ODBC driver can be installed from the MySQL Installer, or downloaded from:



The latest version of the MySQL ODBC driver at the time this compendium was written is 8.0.12. After installing the driver (mysql-connector-odbc-8.0.12-win32.msi or mysql-connector-odbc-8.0.12-winx64.msi for 64-bit systems) you will be able to create an ODBC alias for your MySQL database. You can read more about this process in the compendium entitled "Introduction to MS Access" found at:



Epilogue

We have reviewed in the previous chapters some of the most common tools and functionality offered by MySQL. We have looked at some graphical tools and some command line utilities. With all this, you should have enough information to set up a database with MySQL.

The MySQL Workbench can be compared to similar tools offered by other database management systems like DB2's Data Studio, Oracle's SQL Developer, and SQL Server's Management Studio. MySQL is of course not as powerful and complex as database management systems such as DB2 or SQL Server, but it can be a fine choice for a prototype or a smaller system.

I hope you have found this introduction educational and enjoyable. Please, don't hesitate to send me any comments that could help improve this compendium.

The Author

nikos dimitrakas[pic]

-----------------------

[1] For a complete list of the data types supported by MySQL take a look at

[2] Note that you can only refer to tables that have already been created. In certain situations you may be forced to first create a table and later edit it in order to add a foreign key.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download