{"id":93242,"date":"2022-01-31T21:02:13","date_gmt":"2022-01-31T21:02:13","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=93242"},"modified":"2022-02-28T14:37:56","modified_gmt":"2022-02-28T14:37:56","slug":"getting-started-mysql","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/mysql\/getting-started-mysql\/","title":{"rendered":"Getting started with MySQL"},"content":{"rendered":"<p><strong>This article is part of Robert Sheldon's continuing series on Learning MySQL. To see all of the items in the series, <a href=\"https:\/\/www.red-gate.com\/simple-talk\/mysql-coding-basics\/\">click here<\/a>.<\/strong><\/p>\n\n<p>MySQL is a popular relational database management system developed and distributed by the Oracle Corporation. It provides a multi-user, multithreaded platform that can support a wide range of mission-critical workloads. As with other relational database management systems, such as SQL Server and PostgreSQL, MySQL is based on the Structured Query Language (SQL), a standardized programming language used to create database objects such as tables and views as well as build queries for accessing data through those objects. This article explains geting started with MySQL.<\/p>\n<p>First released in 1995, MySQL has its roots in the open-source community and continues to operate as an open-source project, despite now being owned by Oracle, which took over MySQL as part of its acquisition of Sun Microsystems in 2010. At the heart of the MySQL platform is MySQL Server, which provides the core database capabilities. You can access and download the source code for MySQL Server through <a href=\"https:\/\/github.com\/mysql\/mysql-server\">GitHub<\/a>.<\/p>\n<p>However, you don\u2019t need the source code to get started with MySQL. Oracle offers the MySQL Community edition as a free download available under the GNU General Public License (GPL). Oracle also offers commercial editions that include additional features, but they require licensing and are not free to use. Fortunately, the Community edition is all you need to learn about MySQL.<\/p>\n<p>In this article, I demonstrate how to set up the MySQL Community edition on a Windows computer and how to use MySQL Workbench to access the MySQL environment. I also show how to create a database, add a table to the database, insert data into the table, and then query that data. The examples I provide are fairly straightforward and should be easy to try out for yourself, even if you\u2019re relatively new to relational databases. That said, you should still have a basic understanding of how relational databases work and how they use SQL before you dive into MySQL.<\/p>\n<h2>Installing MySQL on Windows<\/h2>\n<p>You can install MySQL on many operating systems, including Windows, macOS, Oracle Solaris, and Linux distributions such as Ubuntu, Debian, Fedora, SUSE Linux Enterprise Server, and Red Hat Enterprise Linux.<\/p>\n<p>MySQL comes in several editions: Community, Standard, Enterprise, and Cluster Carrier Grade Edition. Because the Community edition is free and available under the GPL license, it\u2019s a great choice for learning about MySQL and creating test databases. For this article, I installed the MySQL Community edition on Windows 10 Pro, but you might choose to install it on one of the other supported platforms.<\/p>\n<p>The approach you take to installing MySQL will depend on the operating system and your preferences. The easiest way to install MySQL on Windows is to use MySQL Installer, a downloadable tool that walks you through the steps of installing and setting up a MySQL instance. I also installed MySQL on macOS to test it out. For this, I used Homebrew and the Bash command shell, which made it relatively straightforward to set up MySQL Community Server and start running SQL commands.<\/p>\n<p>To install the MySQL Community edition on Windows, take the following steps:<\/p>\n<ol>\n<li>Download MySQL Installer\u2019s .msi file from the <a href=\"https:\/\/dev.mysql.com\/downloads\/windows\/installer\/8.0.html\">MySQL site<\/a>. (At the time of this article, the most recent installer file was <em>mysql-installer-community-8.0.27.1.msi<\/em>.)<\/li>\n<li>Once downloaded, double-click the .msi file and follow the prompts to set up the installer.<\/li>\n<li>Launch the installer and follow the wizard\u2019s prompts as it steps you through the process of installing and configuring MySQL Server and any other components you want to install.<\/li>\n<\/ol>\n<p>When you run MySQL Installer, you\u2019ll need to choose which products to install. For this article, I selected the most recent version of each of the following:<\/p>\n<ul>\n<li><strong>MySQL Server.<\/strong> The primary MySQL Community instance.<\/li>\n<li><strong>MySQL Workbench.<\/strong> A user interface for connecting to and working with a MySQL Server instance. Workbench is similar to using SQL Server Management Studio to access SQL Server.<\/li>\n<li><strong>MySQL Documentation.<\/strong> A set of local help files that can be accessed locally through MySQL Workbench.<\/li>\n<\/ul>\n<p>You can install other components, but the three listed here are all you need to follow along with the examples in this article.<\/p>\n<p>When installing MySQL Server, you\u2019re also given the option to configure the server environment. When I configured the server on my instance, I used the following settings:<\/p>\n<ul>\n<li><strong>Type and Networking.<\/strong> I selected <em>Development<\/em> <em>Computer<\/em> from the <em>Config<\/em> <em>Type<\/em> drop-down list and left all other settings with their default values.<\/li>\n<li><strong>Authentication Method.<\/strong> I selected the <em>Use Strong Password Encryption for Authentication<\/em> option.<\/li>\n<li><strong>Accounts and Roles.<\/strong> I provided a password for the root account but defined no other accounts at this time. However, you can create additional accounts as needed and assign them the necessary roles, depending on the environment in which you work and your specific needs.<\/li>\n<li><strong>Windows Service.<\/strong> I selected the <em>Configure MySQL Server as a Windows Service<\/em> option and kept the default service name, <em>MySQL80<\/em>. I also stuck with the default settings for all other options.<\/li>\n<\/ul>\n<p>When you\u2019re finished installing and configuring MySQL, the installer will display the products you installed, as shown in Figure 1. You can then close the installer and start working with MySQL. However, you can relaunch the installer at any time to add, upgrade, or remove products, as well as modify product components and reconfigure MySQL Server.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-93268\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/01\/graphical-user-interface-description-automaticall-3.png\" alt=\"An image showing the MySQL installer.\" width=\"808\" height=\"615\" \/><\/p>\n<p><strong>Figure 1. Using MySQL Installer to set up the server and tools<\/strong><\/p>\n<p>After completing your installation, you can launch MySQL Workbench, which provides a graphical tool for working with MySQL servers and databases. You can use Workbench for SQL development, data modeling and design, server administration, and data migration. If you\u2019re running the MySQL Enterprise edition, you can also use Workbench to manage advanced features such as MySQL Enterprise Backup, MySQL Firewall, and MySQL Audit.<\/p>\n<p>When you launch MySQL Workbench, it opens to the home page, displaying the Welcome tab. From this page, you can also access the <em>Models<\/em> tab and the <em>Migration<\/em> wizard, which we\u2019ll cover later in this series.<\/p>\n<p>The <em>Welcome<\/em> tab should include a link to the local MySQL Server instance that you just created. On my system, the <em>MySQL<\/em> <em>Connections<\/em> section includes a link to <em>Local<\/em> <em>instance<\/em> <em>MySQL80<\/em>, as shown in Figure 2.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-93269\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/01\/graphical-user-interface-text-application-descr-15.png\" alt=\"An image showing the MySQL Workbench welcome page.\" width=\"988\" height=\"623\" \/><\/p>\n<p><strong>Figure 2. Launching MySQL Workbench<\/strong><\/p>\n<p>When you click the connection, Workbench opens a connection tab that displays information about the server instance. The tab also includes its own query tab, which you can use to issue SQL commands directly against the MySQL Server instance. Figure 3 shows the connection tab, which is labeled <em>Local<\/em> <em>instance<\/em> <em>MySQL80<\/em>, and the embedded query tab, which is labeled <em>Query<\/em><strong> 1<\/strong>. Additional query tabs are numbered incrementally.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-93270\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/01\/graphical-user-interface-text-application-descr-16.png\" alt=\"An image showing the MySQL workbench with the management tab open. There is also a query tab showing.\" width=\"1355\" height=\"750\" \/><\/p>\n<p><strong>Figure 3. Connecting to a MySQL instance<\/strong><\/p>\n<p>The connection tab contains three smaller panels that surround the query tab:<\/p>\n<ul>\n<li>The left panel provides access to management, instance, and performance information, as well as to schema, object, and session information.<\/li>\n<li>The right panel displays context help when building SQL statements. Nothing is currently displayed because no SQL elements are selected on the query tab. The panel also provides a mechanism for working with code snippets.<\/li>\n<li>The bottom panel displays the message output when running SQL statements.<\/li>\n<\/ul>\n<p>You can open or close any of these panels by clicking the toggles near the top right corner of the connection tab.<\/p>\n<p>There is much more to MySQL Workbench than I can cover here, and I recommend that you review the <a href=\"https:\/\/dev.mysql.com\/doc\/workbench\/en\/\">Workbench documentation<\/a> to get a better sense of the tool\u2019s capabilities and the many features it provides. In addition, MySQL Workbench, like MySQL Community Server, has a wide user base and is supported by an active community, so you can also find information on community forums.<\/p>\n<h2>Creating a MySQL database<\/h2>\n<p>Once you get MySQL Server and MySQL Workbench up and running, you\u2019ll likely do much of your work on the query tab, where you can run SQL statements against the target MySQL instance. Because MySQL is based on the SQL standard, most of the statements you run will be similar to those used for other relational database systems.<\/p>\n<p>For example, you can create a database by issuing a <code>CREATE<\/code> <code>DATABASE<\/code> statement that specifies the name of the new database, as shown in the following example:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">CREATE DATABASE IF NOT EXISTS travel;<\/pre>\n<p>In this case, I\u2019m creating a database named travel. I also included an <code>IF<\/code> <code>NOT<\/code> <code>EXISTS<\/code> clause to ensure that the statement runs only if the database does not already exist.<\/p>\n<p>After you create an SQL statement, you can then execute it against the target instance. The query tab toolbar includes two execute buttons (lightning bolts) for running SQL statements:<\/p>\n<ul>\n<li>The first execute button (the one on the left) runs the portion of the script that you\u2019ve selected. If no script is selected, Workbench executes all script on the query tab.<\/li>\n<li>The second execute button runs only the statement where the cursor is located. You have to be careful with this option because it will also execute the statement that directly precedes the cursor, even if the cursor is on an empty line.<\/li>\n<\/ul>\n<p>Because the query tab includes only one statement, you can click either of the two buttons. Figure 4 shows the <code>CREATE<\/code> <code>DATABASE<\/code> statement after I ran it in Workbench. The bottom panel displays a message about the statement\u2019s execution. The white-on-green checkmark to the left of the message indicates that the statement ran successfully. If the statement had instead generated an error, the bottom pane would display the error message, along with a white-on-red X.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-93271\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/01\/graphical-user-interface-text-application-descr-17.png\" alt=\"An image showing a create database statement.\" width=\"1350\" height=\"648\" \/><\/p>\n<p><strong>Figure 4. Creating a database in MySQL <\/strong><\/p>\n<p>You might have noticed that the right panel displayed information about the <code>CREATE<\/code> <code>DATABASE<\/code> statement as you started to write that statement. When your cursor is positioned over certain SQL keywords, such as <code>CREATE<\/code> <code>DATABASE<\/code>, Workbench automatically provides information about that language element, along with a link to additional information.<\/p>\n<p>After you create the database, you can view it on the <em>Schemas<\/em> tab in the left panel. You might have to click the refresh button near the top of the panel to get the new database to display. Workbench automatically adds four nodes to the database listing: <em>Tables<\/em>, <em>Views<\/em>, <em>Stored<\/em> <em>Procedures<\/em>, and <em>Functions<\/em>. However, because you have not created any of these types of objects, no objects will be listed under these nodes.<\/p>\n<p>It might seem odd that Workbench lists the travel database as a schema rather than a database. This is because MySQL, for all practical purposes, treats databases and schemas as one and the same, which is much different from other relational database systems. In fact, the MySQL documentation states that \u201c<code>CREATE<\/code> <code>SCHEMA<\/code> is a synonym for <code>CREATE<\/code> <code>DATABASE<\/code>.\u201d<\/p>\n<h2>Adding a table to a MySQL database<\/h2>\n<p>After you create a database, you can issue a <code>USE<\/code> statement to select the new database and then add a table to the database. For example, the following code switches to the <code>travel<\/code> database and then uses a <code>CREATE<\/code> <code>TABLE<\/code> statement to define the airplanes table:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">USE travel;\r\nCREATE TABLE IF NOT EXISTS airplanes (\r\n  plane_id INT UNSIGNED NOT NULL AUTO_INCREMENT,\r\n  manufacturer VARCHAR(50) NOT NULL,\r\n  model VARCHAR(50) NOT NULL,\r\n  engine_type VARCHAR(50) NOT NULL,\r\n  engine_count TINYINT NOT NULL,\r\n  wingspan DECIMAL(5,2) NOT NULL,\r\n  plane_length DECIMAL(5,2) NOT NULL,\r\n  max_weight MEDIUMINT UNSIGNED NOT NULL,\r\n  icao_code CHAR(4) NOT NULL,\r\n  create_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,\r\n  last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP \r\n    ON UPDATE CURRENT_TIMESTAMP,\r\n  PRIMARY KEY (plane_id) ) \r\nENGINE=InnoDB AUTO_INCREMENT=101;<\/pre>\n<p>If you\u2019re already familiar with SQL, much of this script will look familiar to you, although some of the elements might be new. For example, MySQL lets you specify whether an integer data type is signed or unsigned. If signed, a column\u2019s values can include negative numbers. If unsigned, the values cannot include negative numbers. Integer data types are signed by default.<\/p>\n<p>We\u2019ll be discussing the <code>CREATE<\/code> <code>TABLE<\/code> statement in more depth later in the series.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-93272\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/01\/graphical-user-interface-text-application-descr-18.png\" alt=\"An image showing the create table statement.\" width=\"1351\" height=\"645\" \/><\/p>\n<p><strong>Figure 5. Adding a table to a MySQL database<\/strong><\/p>\n<p>The <em>Schemas<\/em> tab in the left panel now shows the <em>airplanes<\/em> table. Because the table is selected on that tab, the <em>Object<\/em> <em>Info<\/em> tab at the bottom of the left panel displays information about the table.<\/p>\n<h2>Inserting data into a MySQL table<\/h2>\n<p>After you create a table, you can use the <code>INSERT<\/code> statement to add data to that table. As you\u2019ve seen with other database products, you need only specify the target columns and their values, as shown in the following example:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">INSERT INTO airplanes \r\n  (manufacturer, model, engine_type, engine_count, \r\n    wingspan, plane_length, max_weight, icao_code)\r\nVALUES \r\n  ('Airbus', 'A380-800', 'jet', '4', 261.65, 238.62, 1267658, 'A388');<\/pre>\n<p>The statement\u2019s format should be familiar to anyone who\u2019s worked with SQL, regardless of the database system. After the <code>INSERT<\/code> clause, the statement lists the target columns in parentheses. The statement then includes a <code>VALUES<\/code> clause, which lists the values to be inserted into the new row, also in parentheses.<\/p>\n<p>Retrieving data is also much the same as other relational databases. For instance, the following <code>SELECT<\/code> statement returns all of the table\u2019s data:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">SELECT * FROM airplanes;<\/pre>\n<p>In this case, the <code>SELECT<\/code> clause uses the asterisk (*) wildcard to indicate that all columns should be returned. Of course, you would not want to do this within a production environment, but for<\/p>\n<p>Figure 6 shows the <code>INSERT<\/code> and <code>SELECT<\/code> statements, along with the results returned by the <code>SELECT<\/code> statement. I closed the right and lower panels to reduce the clutter.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"978\" height=\"549\" class=\"wp-image-93273\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/01\/graphical-user-interface-application-description-3.png\" alt=\"Graphical user interface, application\n\nDescription automatically generated\" \/><\/p>\n<p><strong>Figure 6. Inserting data into a MySQL table<\/strong><\/p>\n<p>When you return all columns in a table, Workbench adds a row to the results that show a null value for each column. You can use this row to add data directly to the table, as long as the values conform to the configured data types. In addition, Workbench lets you edit the values in the returned results. We\u2019ll be covering the result grid in more detail in a later article in this series.<\/p>\n<p>As with other database systems, you can also use a single <code>INSERT<\/code> statement to add multiple rows, as shown in the following example:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">INSERT INTO airplanes \r\n  (manufacturer, model, engine_type, engine_count, \r\n    wingspan, plane_length, max_weight, icao_code)\r\nVALUES \r\n  ('Piper', 'J-3 Cub', 'piston', '1', 38.00, 22.42, 1220, 'J3'),\r\n  ('Beechcraft', '1900C', 'turboprop', '2', 54.50, 57.83, 16600, 'B190');<\/pre>\n<p>To add multiple rows, you need only separate the value sets with a comma. You can then run the same <code>SELECT<\/code> statement to return all the rows and columns:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">SELECT * FROM airplanes;<\/pre>\n<p>Figure 7 shows the <code>INSERT<\/code> and <code>SELECT<\/code> statements, along with the query results.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-93274\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/01\/graphical-user-interface-text-description-automa-3.png\" alt=\"An image showing the insert and select statements. The insert is inserting multiple rows.\" width=\"973\" height=\"545\" \/><\/p>\n<p><strong>Figure 7. Inserting multiple rows into a MySQL table<\/strong><\/p>\n<p>As you can see, the table includes all three rows, along with a row of null values for adding more data. You can, of course, be more specific in your queries, as in the following <code>SELECT<\/code> statement, which limits the columns and rows:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">SELECT model, engine_type, engine_count\r\nFROM Airplanes\r\nWHERE manufacturer = 'Airbus';<\/pre>\n<p>In this case, the <code>SELECT<\/code> clause specifies only three columns (<code>model<\/code>, <code>engine_type<\/code>, and <code>engine_count<\/code>) rather than all columns. In addition, the statement includes a <code>WHERE<\/code> clause that limits the results to rows with a <code>manufacturer<\/code> value of Airbus. Figure 8 shows the <code>SELECT<\/code> statement and its results, which include only one row with three columns.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-93275\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/01\/graphical-user-interface-text-application-descr-19.png\" alt=\"An image showing a query window with the INSERT and SELECT statements. The results of a query with a comma delimited list and filter.\" width=\"973\" height=\"548\" \/><\/p>\n<p><strong>Figure 8. Retrieving data from a MySQL table<\/strong><\/p>\n<p>There should be no surprises here. The main thing to note is that the result grid no longer includes a row of nulls for inserting new data, which is to be expected because you\u2019re viewing only a subset of the table\u2019s columns.<\/p>\n<h2>Getting started with MySQL<\/h2>\n<p>Once you know how to install MySQL and use MySQL Workbench, you can experiment with various features, create different types of database objects, and start building SQL queries. MySQL is a versatile database system that can accommodate a wide range of workloads. The better you understand how it works, the greater your ability to take advantage of its capabilities. Even if you\u2019re new to relational databases, MySQL Installer makes it easy to set up your environment, and Workbench makes it easy to interface with that environment and work with MySQL and its data. You can then build on this foundation to become more proficient at creating databases and querying the data so you can better support your data-driven applications.<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The first step when getting started with MySQL is to get it installed and running. In this article, Robert Sheldon explains how to install it on Windows and create the first database and table.&hellip;<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,145792],"tags":[95506],"coauthors":[6779],"class_list":["post-93242","post","type-post","status-publish","format-standard","hentry","category-featured","category-mysql","tag-automate"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93242","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=93242"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93242\/revisions"}],"predecessor-version":[{"id":93450,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93242\/revisions\/93450"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=93242"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=93242"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=93242"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=93242"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}