The MySQL Workshop

Thomas Pettit, Scott Cosentino

• Publisher: Packt Publishing
• Year: 2022
• ISBN:
  9781839215476
• Edition: 1
• Topic: Informatik
• Subtopic: Open-Source-Programmierung

Learning MySQL just got a whole lot easier, thanks to this hands-on workshop, complete with simple explanations, engaging examples, and realistic exercises that focus on helping you to build and maintain databases effectivelyKey Features• Learn how to set up and maintain a MySQL database• Run SQL queries to create, retrieve, and manipulate data• Use MySQL effectively with common business applications such as Excel and MS AccessBook DescriptionDo you want to learn how to create and maintain databases effectively? Are you looking for simple answers to basic MySQL questions as well as straightforward examples that you can use at work? If so, this workshop is the right choice for you.Designed to build your confidence through hands-on practice, this book uses a simple approach that focuses on the practical, so you can get straight down to business without having to wade through pages and pages of dull, dry theory.As you work through bite-sized exercises and activities, you'll learn how to use different MySQL tools to create a database and manage the data within it. You'll see how to transfer data between a MySQL database and other sources, and use real-world datasets to gain valuable experience of manipulating and gaining insights from data. As you progress, you'll discover how to protect your database by managing user permissions and performing logical backups and restores.If you've already tried to teach yourself SQL, but haven't been able to make the leap from understanding simple queries to working on live projects with a real database management system, The MySQL Workshop will get you on the right track.By the end of this MySQL book, you'll have the knowledge, skills, and confidence to advance your career and tackle your own ambitious projects with MySQL.What you will learn• Understand the concepts of relational databases and document stores• Use SQL queries, stored procedures, views, functions, and transactions• Connect to and manipulate data using MS Access, MS Excel, and Visual Basic for Applications (VBA)• Read and write data in the CSV or JSON format using MySQL• Manage data while running MySQL Shell in JavaScript mode• Use X DevAPI to access a NoSQL interface for MySQL• Manage user roles, credentials, and privileges to keep data secure• Perform a logical database backup with mysqldump and mysqlpumpWho this book is forThis book is for anyone who wants to learn how to use MySQL in a productive, efficient way. If you're totally new to MySQL, it'll help you get started or if you've used MySQL before, it'll fill in any gaps, consolidate key concepts, and offer valuable hands-on practice. Prior knowledge of simple SQL or basic programming techniques will help you in quickly grasping the concepts covered, but is not necessary.

Section 1: Creating Your Database

This section covers the basics of MySQL, relational databases, and database management systems. We will discuss the ways you can create databases and insert, modify, query, and delete data contained within them.

This section consists of the following chapters:

• Chapter 1, Background Concepts
• Chapter 2, Creating a Database
• Chapter 3, Using SQL to Work with a Database
• Chapter 4, Selecting, Aggregating, and Applying Functions

Chapter 1: Background Concepts

In this chapter, you will gain an understanding of the basic types of databases and how people tend to use them. You will learn how MySQL implements specific concepts such as database structures, layers, organization, and what its architecture looks like. You will explore what a relational database management system such as MySQL is, and how it differs from a standard database management system. You will also learn about data normalization and data modeling.

By the end of this chapter, you will have a good overview of what a database is and its different components. You will also learn what makes MySQL special and how it fits into this ecosystem.

This chapter covers the following topics:

• Introducing databases
• Exploring MySQL
• Exercise 1.01: Organizing data in a relational format
• Exploring MySQL architecture
• Storage engines (InnoDB and MyRocks)
• Data modeling
• Normalization
• Activity 1.01: Creating an optimized table for an employee project

Introducing databases

Information is abundant, an ever-growing pile of little bits of data that drives every aspect of your life, and the bigger that pile of data grows, the more valuable it becomes to yourself or others. For example, consider a situation where you need to search the internet for a specific piece of information, such as how to create a MySQL database. To do this, you would send a query to a search engine, which then parses large sets of data to find the relevant results. Putting all that data into some form of useful context manually, such as inputting it into spreadsheet software, is time-consuming.

Using databases, it is easier to automate the input and processing of data. Now you can store all that data into ever-growing databases and push, pull, squeeze, and tug on the data to get information from it that you could never dream of getting before, and in the blink of an eye. A database is an organized collection of structured data. The data becomes information once it is processed. For example, you have a database to store servers and their information, such as processor count, memory, storage, and location. Alone, this data is not immediately useable for business decisions and analysis. However, detailed reports about the utilization of servers at specific locations contain the information that can be fetched from the database.

To ensure fast and accurate access and to protect all the valuable data, the database is usually housed in an external application specifically designed to efficiently store and manage large volumes of data. MySQL is one such application. In almost all cases, the database management system or database server is installed on a dedicated computer. This way, many users can connect to a centralized database server at the same time. Irrespective of the number of users, both the data and the database are important—as sensitive data and useful insights are stored in it—and must be suitably protected and efficiently used. For example, a database can be used to store log information or the revenue of a company.

In this book, you will build up your knowledge to manage your database. You will also learn how to deploy, manage, and query the database as you progress in the book.

The following section will describe databases in greater depth.

Database architecture

A database is a collection of related data that has been gathered and stored for easy access and management. Each discrete item of data in a database is, in itself, not very useful or valuable, but the entire collection of data as a whole (when coupled with ease of use and fast access) provides an exceptionally powerful tool for business and personal use. For example, if you have a set of data that shows how much time a user spends on a specific page, you can track user experience on your application. As the volume of data grows and its historical content stretches further back in time, the data becomes more useful in identifying and predicting past and future trends, and the value of the data to its owner increases. Databases allow the user to logically separate data and store it in a well-structured format that allows them to create reports and identify trends.

To understand the advantage of databases, consider a telephone book that is used to store people's names, addresses, and phone numbers. A phone book is a good example of a manual data store, in which data is organized alphabetically to find the information easily (albeit, manually). With a phone book, storing large sets of data creates a bulky physical object, which must be manually searched to find the data we want. The process of searching the data is time-consuming, and we can only search the data by name since this is how it is organized.

To help improve this process, you can utilize computer-based information systems to store the data either in tables or flat files. Flat files store data in a plain text format. Files with the extensions .csv or .txt are usually flat files.

Figure 1.1 – An example of a flat file

Tables store data in rows and columns, allowing you to logically separate data and store them.

Figure 1.2 – An example of a table

You use databases in almost everything you do in your life. Whenever you connect to a website, the screen layout and the information displayed in front of the screen are fetched from the database. The cell phone you use in your day-to-day life stores the contact numbers in a database. When you watch a show on a streaming service, your login details, the information about the show, and the show itself are stored in a database.

There are many different types of database systems out there. Most are quite similar in some ways, though quite different in others. Some are geared toward a specific type of activity, and others are more general in their application. You will look at the two most common database management systems used by businesses today, DBMS and RDBMS, in the upcoming sections.

A centralized database is one that is located, stored, and maintained at a single site. The simplest example of a centralized database is an MS Access file stored on SharePoint that is used by multiple people. A distributed database is more complex as the data is not stored in a single place, but rather at multiple locations. A distributed database helps users to fetch the information quickly as the data is stored closer to the end users. ...