Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Save more on your purchases! discount-offer-chevron-icon
Savings automatically calculated. No voucher code required.
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Newsletter Hub
Free Learning
Arrow right icon
timer SALE ENDS IN
0 Days
:
00 Hours
:
00 Minutes
:
00 Seconds
MySQL 8 Cookbook
MySQL 8 Cookbook

MySQL 8 Cookbook: Over 150 recipes for high-performance database querying and administration

eBook
₹799.99 ₹3276.99
Paperback
₹4096.99
Subscription
Free Trial
Renews at ₹800p/m

What do you get with eBook?

Product feature icon Instant access to your Digital eBook purchase
Product feature icon Download this book in EPUB and PDF formats
Product feature icon Access this title in our online reader with advanced features
Product feature icon DRM FREE - Read whenever, wherever and however you want
OR
Modal Close icon
Payment Processing...
tick Completed

Billing Address

Table of content icon View table of contents Preview book icon Preview Book

MySQL 8 Cookbook

Using MySQL

In this chapter, we will cover the following recipes:

  • Connecting to MySQL using the command-line client
  • Creating databases
  • Creating tables
  • Inserting, updating, and deleting rows
  • Loading sample data
  • Selecting data
  • Sorting results
  • Grouping results (aggregate functions)
  • Creating users
  • Granting and revoking access to users
  • Selecting data into a file and table
  • Loading data into a table
  • Joining tables
  • Stored procedures
  • Functions
  • Triggers
  • Views
  • Events 
  • Getting information about databases and tables

Introduction

We are going to learn a lot of things in the following recipes. Let's take a look at each one in detail.

Connecting to MySQL using the command-line client

So far, you have learned how to install MySQL 8.0 on various platforms. Along with the installation, you will get the command-line client utility called mysql, which we use to connect to any MySQL server.

Getting ready

First you need to know to which server you need to connect. If you have the MySQL server installed on one host and you are trying to connect to the server from a different host (usually called client), you should specify the hostname or IP address of the server and the mysql-client package should be installed on the client. In the previous chapter, you installed both MySQL server and client packages. If you are already on the server (through SSH), you can...

Creating databases

Well, you have installed MySQL 8.0 and connected to it. Now it is time to store some data in it, that's what the database is meant for, after all. In any relational database management system (RDBMS), data is stored in rows, which is the basic building block of the database. Rows contain columns in which we can store several set of values.

For example, if you want to store information about your customers in a database.

Here is the dataset:

customer id=1, first_name=Mike, last_name=Christensen country=USA
customer id=2, first_name=Andy, last_name=Hollands, country=Australia
customer id=3, first_name=Ravi, last_name=Vedantam, country=India
customer id=4, first_name= Rajiv, last_name=Perera, country=Sri Lanka

You should save them as rows: (1, 'Mike', 'Christensen', 'USA'), (2, 'Andy', 'Hollands', &apos...

Creating tables

While defining columns in a table, you should mention the name of the column, datatype (integer, floating point, string, and so on), and default value (if any). MySQL supports various datatypes. Refer to the MySQL documentation for more details (https://dev.mysql.com/doc/refman/8.0/en/data-types.html). Here is an overview of all datatypes. The  JSON datatype is a new extension, which will be discussed in Chapter 3, Using MySQL (Advanced):

  1. Numeric: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, and BIT.
  2. Floating numbers: DECIMAL, FLOAT, and DOUBLE.
  3. Strings: CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET.
  4. Spatial datatypes are also supported. Refer to https://dev.mysql.com/doc/refman/8.0/en/spatial-extensions.html for more details.
  5. The JSON datatype - discussed in detail in the next chapter.

You can create many tables inside a database...

Inserting, updating, and deleting rows

The INSERT, UPDATE, DELETE, and SELECT operations are called Data Manipulation Language (DML) statements. INSERT, UPDATE, and DELETE are also called write operations, or simply write(s). SELECT is a read operation and is simply called read(s).

How to do it...

Let's look at each of them in detail. I am sure you will enjoy learning this. I would suggest that you try a few things on your own as well, later. By the end of this recipe, we will also have gotten to grips with truncating tables.

Inserting

The...

Loading sample data

You have created the schema (databases and tables) and some data (through INSERT, UPDATE, and DELETE). To explain the further chapters, more data is needed. MySQL has provided a sample employee database and a lot of data to play around with. In this chapter, we will discuss how to get that data and store it in our database.

How to do it...

  1. Download the zipped file:
shell> wget 'https://codeload.github.com/datacharmer/test_db/zip/master' -O master.zip
  1. Unzip the file:
shell> unzip master.zip
  1. Load the data:
shell> cd test_db-master

shell> mysql -u root -p < employees.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
INFO
CREATING DATABASE STRUCTURE...

Selecting data

You have inserted and updated data in the tables. Now it is time to learn how to retrieve information from the database. In this section, we will discuss how to retrieve data from the sample employee database that we have created.

There are many things that you can do with SELECT. The most common use cases will be discussed in this section. For more details on syntax and other use cases, refer to https://dev.mysql.com/doc/refman/8.0/en/select.html.

How to do it...

Select all data from the departments table of the employee database. You can use an asterisk (*) to select all columns from a table. It is not recommended to use it, you should always select only the data you need:

mysql> SELECT...
Left arrow icon Right arrow icon

Key benefits

  • Store, retrieve, and manipulate your data using the latest MySQL 8 features
  • Practical recipes on effective administration in MySQL, with a focus on security, performance tuning, troubleshooting, and more
  • Contains tips, tricks, and best practices for designing, developing, and administering your MySQL 8 database solution without any hassle

Description

MySQL is one of the most popular and widely used relational databases in the World today. The recently released MySQL 8 version promises to be better and more efficient than ever before. This book contains everything you need to know to be the go-to person in your organization when it comes to MySQL. Starting with a quick installation and configuration of your MySQL instance, the book quickly jumps into the querying aspects of MySQL. It shows you the newest improvements in MySQL 8 and gives you hands-on experience in managing high-transaction and real-time datasets. If you've already worked with MySQL before and are looking to migrate your application to MySQL 8, this book will also show you how to do that. The book also contains recipes on efficient MySQL administration, with tips on effective user management, data recovery, security, database monitoring, performance tuning, troubleshooting, and more. With quick solutions to common and not-so-common problems you might encounter while working with MySQL 8, the book contains practical tips and tricks to give you the edge over others in designing, developing, and administering your database effectively.

Who is this book for?

If you are a MySQL developer or administrator looking for quick, handy solutions to solve the most common and not-so-common problems in MySQL, this book is for you. MySQL DBAs looking to get up-to-speed with the latest MySQL 8 development and administration features will also find this book very useful. Prior knowledge of Linux and RDBMS is desirable.

What you will learn

  • Install and configure your MySQL 8 instance without any hassle
  • Get to grips with new features of MySQL 8 like CTE, Window functions and many more
  • Perform backup tasks, recover data and set up various replication topologies for your database
  • Maximize performance by using new features of MySQL 8 like descending indexes, controlling query optimizer and resource groups
  • Learn how to use general table space to suit the SaaS or multi-tenant applications
  • Analyze slow queries using performance schema, sys schema and third party tools
  • Manage and monitor your MySQL instance and implement efficient performance-tuning tasks

Product Details

Country selected
Publication date, Length, Edition, Language, ISBN-13
Publication date : Jan 25, 2018
Length: 446 pages
Edition : 1st
Language : English
ISBN-13 : 9781788398442
Category :
Tools :

What do you get with eBook?

Product feature icon Instant access to your Digital eBook purchase
Product feature icon Download this book in EPUB and PDF formats
Product feature icon Access this title in our online reader with advanced features
Product feature icon DRM FREE - Read whenever, wherever and however you want
OR
Modal Close icon
Payment Processing...
tick Completed

Billing Address

Product Details

Publication date : Jan 25, 2018
Length: 446 pages
Edition : 1st
Language : English
ISBN-13 : 9781788398442
Category :
Tools :

Packt Subscriptions

See our plans and pricing
Modal Close icon
₹800 billed monthly
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Simple pricing, no contract
₹4500 billed annually
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just ₹400 each
Feature tick icon Exclusive print discounts
₹5000 billed in 18 months
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just ₹400 each
Feature tick icon Exclusive print discounts

Frequently bought together


Stars icon
Total 10,650.97
MySQL 8 Cookbook
₹4096.99
Mastering PostgreSQL 10
₹3276.99
MySQL 8 Administrator???s Guide
₹3276.99
Total 10,650.97 Stars icon

Table of Contents

14 Chapters
MySQL 8 - Installing and Upgrading Chevron down icon Chevron up icon
Using MySQL Chevron down icon Chevron up icon
Using MySQL (Advanced) Chevron down icon Chevron up icon
Configuring MySQL Chevron down icon Chevron up icon
Transactions Chevron down icon Chevron up icon
Binary Logging Chevron down icon Chevron up icon
Backups Chevron down icon Chevron up icon
Restoring Data Chevron down icon Chevron up icon
Replication Chevron down icon Chevron up icon
Table Maintenance Chevron down icon Chevron up icon
Managing Tablespace Chevron down icon Chevron up icon
Managing Logs Chevron down icon Chevron up icon
Performance Tuning Chevron down icon Chevron up icon
Security Chevron down icon Chevron up icon

Customer reviews

Rating distribution
Full star icon Full star icon Full star icon Half star icon Empty star icon 3.5
(4 Ratings)
5 star 25%
4 star 50%
3 star 0%
2 star 0%
1 star 25%
K. Sai Kiran Jul 26, 2018
Full star icon Full star icon Full star icon Full star icon Full star icon 5
I really liked the performance tuning chapter. Apt data sets and queries are shown to illustrate the optimizations.
Amazon Verified review Amazon
Omega Jan 08, 2019
Full star icon Full star icon Full star icon Full star icon Empty star icon 4
a great book as a handy reference.
Amazon Verified review Amazon
Bo Green Oct 30, 2019
Full star icon Full star icon Full star icon Full star icon Empty star icon 4
would like to see more valueable content
Amazon Verified review Amazon
c shah Nov 17, 2018
Full star icon Empty star icon Empty star icon Empty star icon Empty star icon 1
I have purchased kindle version of this book. It is pathetic. The formatting of the chapter is not readable.
Amazon Verified review Amazon
Get free access to Packt library with over 7500+ books and video courses for 7 days!
Start Free Trial

FAQs

How do I buy and download an eBook? Chevron down icon Chevron up icon

Where there is an eBook version of a title available, you can buy it from the book details for that title. Add either the standalone eBook or the eBook and print book bundle to your shopping cart. Your eBook will show in your cart as a product on its own. After completing checkout and payment in the normal way, you will receive your receipt on the screen containing a link to a personalised PDF download file. This link will remain active for 30 days. You can download backup copies of the file by logging in to your account at any time.

If you already have Adobe reader installed, then clicking on the link will download and open the PDF file directly. If you don't, then save the PDF file on your machine and download the Reader to view it.

Please Note: Packt eBooks are non-returnable and non-refundable.

Packt eBook and Licensing When you buy an eBook from Packt Publishing, completing your purchase means you accept the terms of our licence agreement. Please read the full text of the agreement. In it we have tried to balance the need for the ebook to be usable for you the reader with our needs to protect the rights of us as Publishers and of our authors. In summary, the agreement says:

  • You may make copies of your eBook for your own use onto any machine
  • You may not pass copies of the eBook on to anyone else
How can I make a purchase on your website? Chevron down icon Chevron up icon

If you want to purchase a video course, eBook or Bundle (Print+eBook) please follow below steps:

  1. Register on our website using your email address and the password.
  2. Search for the title by name or ISBN using the search option.
  3. Select the title you want to purchase.
  4. Choose the format you wish to purchase the title in; if you order the Print Book, you get a free eBook copy of the same title. 
  5. Proceed with the checkout process (payment to be made using Credit Card, Debit Cart, or PayPal)
Where can I access support around an eBook? Chevron down icon Chevron up icon
  • If you experience a problem with using or installing Adobe Reader, the contact Adobe directly.
  • To view the errata for the book, see www.packtpub.com/support and view the pages for the title you have.
  • To view your account details or to download a new copy of the book go to www.packtpub.com/account
  • To contact us directly if a problem is not resolved, use www.packtpub.com/contact-us
What eBook formats do Packt support? Chevron down icon Chevron up icon

Our eBooks are currently available in a variety of formats such as PDF and ePubs. In the future, this may well change with trends and development in technology, but please note that our PDFs are not Adobe eBook Reader format, which has greater restrictions on security.

You will need to use Adobe Reader v9 or later in order to read Packt's PDF eBooks.

What are the benefits of eBooks? Chevron down icon Chevron up icon
  • You can get the information you need immediately
  • You can easily take them with you on a laptop
  • You can download them an unlimited number of times
  • You can print them out
  • They are copy-paste enabled
  • They are searchable
  • There is no password protection
  • They are lower price than print
  • They save resources and space
What is an eBook? Chevron down icon Chevron up icon

Packt eBooks are a complete electronic version of the print edition, available in PDF and ePub formats. Every piece of content down to the page numbering is the same. Because we save the costs of printing and shipping the book to you, we are able to offer eBooks at a lower cost than print editions.

When you have purchased an eBook, simply login to your account and click on the link in Your Download Area. We recommend you saving the file to your hard drive before opening it.

For optimal viewing of our eBooks, we recommend you download and install the free Adobe Reader version 9.