
The MySQL Workshop
Do 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.
- The MySQL Workshop
- Contributors
- About the authors
- About the reviewer
- Preface
- Who this book is for
- What this book covers
- To get the most out of this book
- Download the example code files
- Download the color images
- Conventions used
- Get in touch
- Share Your Thoughts
- Section 1: Creating Your Database
- Chapter 1: Background Concepts
- Introducing databases
- Database architecture
- MS Access as a database
- Database management system
- RDBMS
- Exploring MySQL
- Data types
- Exercise 1.01: Organizing data in a relational format
- Exploring MySQL architecture
- The MySQL layers
- Storage engines (InnoDB and MyRocks)
- ACID compliance
- Data modeling
- Normalization
- Activity 1.01: Creating an optimized table for an employee project
- Summary
- Introducing databases
- Chapter 2: Creating a Database
- Developing databases
- The MySQL Workbench GUI
- Connecting the Workbench GUI to MySQL
- Exercise 2.01 creating a connection with the MySQL Workbench GUI
- Accessing MySQL through the command-line interface
- Creating a database
- Exercise 2.02 creating the autoclub database
- Using Workbench to add a table
- Importing objects from a SQL script file
- Exercise 2.03 importing tables from an SQL script file
- MySQL table indexes and foreign keys
- Indexes
- Exercise 2.04 creating an index
- Indexes on multiple columns
- Foreign keys
- Exercise 2.05 creating a foreign key
- Reverse engineering a database
- Exercise 2.06 creating an EER model from the autoclub database
- Exercise 2.07 using the EER diagram and forward engineering to manage the database model
- Exercise 2.08 committing model changes to the production database with Synchronize Model
- Activity 2.01 modifying the EER diagram, the model, and the database
- Summary
- Chapter 3: Using SQL to Work with a Database
- An introduction to working with databases using SQL
- Working with data
- Types of SQL statements
- Backing up databases
- Exercise 3.01 Backing up the autoclub database
- Restoring databases
- Exercise 3.02 restoring the autoclub database
- Working with SQL code to maintain a database
- Creating a new database
- Exercise 3.03 creating a new database
- Creating and modifying tables
- Exercise 3.04 creating a new table
- SQL queries to create indexes and foreign keys
- Exercise 3.05 creating tables with indexes and foreign keys
- Activity 3.1 creating a table with indexes and foreign keys
- Altering table queries
- Exercise 3.06 modifying an existing table
- Adding data to a table
- Exercise 3.07 adding a single record to a members table
- Updating data in a record
- Exercise 3.08 updating a record
- Deleting data from tables
- Drop queries
- Blobs, files, and file paths
- Exercise 3.09 files and blobs
- Files and file paths
- Activity 3.2 adding image file paths to the database
- Summary
- Chapter 4: Selecting, Aggregating, and Applying Functions
- An introduction to querying data
- Querying tables in MySQL
- Exercise 4.01 working with simple queries
- Filtering results
- Exercise 4.02 filtering results
- Using functions on data
- Math functions
- String functions
- Date and time functions
- Exercise 4.03 using functions
- Aggregating data
- Exercise 4.04 aggregating data
- Case statements
- Exercise 4.05 writing case statements
- Activity 4.01 collecting information for a travel article
- Summary
- Section 2: Managing Your Database
- Chapter 5: Correlating Data across Tables
- Introduction to processing data across tables
- Joining two tables
- Accidental cross joins
- LEFT JOIN versus INNER JOIN
- Exercise 5.01: Joining two tables
- Analyzing subqueries
- Dependent subqueries
- Exercise 5.02: Using a subquery
- Common table expressions
- Recursive CTE
- Exercise 5.03: Using a CTE
- Analyzing query performance with EXPLAIN
- Exercise 5.04: Using EXPLAIN
- Activity 5.01: The Sakila video store
- Activity 5.02: Generating a list of years
- Summary
- Chapter 6: Stored Procedures and Other Objects
- Introduction to database objects
- Exploring various database objects
- Working with views
- Exercise 6.01 creating a mailing list with a view
- Updatable views
- Activity 6.01 updating the data in a view
- Working with user-defined functions
- Exercise 6.02 creating a function
- Working with stored procedures
- Exercise 6.03 creating a stored procedure
- Exercise 6.04 stored procedures and parameters
- Working with IN, OUT, and INOUT
- Exercise 6.05 IN and INOUT
- Exploring triggers
- Advantages of triggers
- Disadvantages of triggers
- Restrictions with triggers
- Exercise 6.06 triggers to enforce business rules
- Using transactions
- Exercise 6.07 implementing a transaction
- Summary
- Chapter 7: Creating Database Clients in Node.js
- Introduction to database management with Node.js
- Best practices for SQL client development
- Installing a development MySQL server
- Creating a development MySQL server
- Backing up before making changes
- Restoring a database
- Recovering from accidental data deletion
- Exercise 7.01 safely deleting records
- JavaScript using Node.js
- Setting up Node.js
- Getting started with Node.js
- Basics of Node.js
- Exercise 7.02 basic output in the console
- Exercise 7.03 testing outputs in a browser
- Writing outputs to files
- Exercise 7.04 writing to a disk file
- Connecting to MySQL
- Exercise 7.05 connecting to the MySQL server
- Troubleshooting connection errors
- Modularizing the MySQL connection
- Exercise 7.06 modularizing the MySQL connection
- Creating databases in Node.js
- Exercise 7.07 creating a new database
- Creating tables in Node.js
- Exercise 7.08 creating a table in a database
- Activity 7.01 building a database application with Node.js
- Summary
- Chapter 8: Working with Data Using Node.js
- Interacting with databases
- Inserting records in Node.js
- Exercise 8.01 inserting a record into a table
- Inserting multiple records
- Exercise 8.02 inserting multiple records into a table
- Inserting with multiple fields
- Exercise 8.03 populating records from the existing tables
- Updating the records of a table
- Exercise 8.04 updating a single record
- Activity 8.01 multiple updates
- Displaying data in browsers
- Exercise 8.05 formatting data to the web browser
- ODBC connections
- Types of DSNs
- Determining whether ODBC drivers have been installed
- Local, LAN, and remote ODBC connections
- Exercise 8.06 creating a LAN or remote DSN/ODBC connection to the world_statistics database
- Creating file DSN/ODBC connections
- Activity 8.02 designing a customer database
- Summary
- Section 3: Querying Your Database
- Chapter 9: Microsoft Access Part 1
- Introduction to MS Access
- MS Access database application configurations
- Upsizing an MS Access database to MySQL
- Exercise 9.01 preparing your MySQL database and ODBC
- Manually exporting MS Access tables
- Exercise 9.02 manually upsizing a table
- Adjusting field properties
- Exercise 9.03 manually migrating tables and adjusting their field properties
- Migrating with wizards
- Exercise 9.04 using the Workbench Migration Wizard to upsize the table
- Linking to your tables and views
- Exercise 9.05 linking a good MySQL table to Access
- Exercise 9.07 linking a problematic MySQL table to Access
- Refreshing linked MySQL tables
- Activity 9.01 linking the remaining MySQL tables to your MS Access database
- Summary
- Chapter 10: Microsoft Access Part 2
- Introduction to MS Access
- Migrating an MS Access application to MySQL
- Passthrough queries
- Exercise 10.01 Passthrough (simple SQL conversion)
- Activity 10.01 Converting gender and job statistics
- Calling MySQL functions
- Exercise 10.02 Passthrough (calling MySQL functions)
- Activity 10.02 Creating a function and calling it
- Calling MySQL stored procedures
- Exercise 10.03 Calling a MySQL stored procedure
- Activity 10.03 Creating MySQL stored procedures and using them in VBA
- Using parameters
- Parameterized stored procedures
- Exercise 10.04 Parameterized stored procedure (series list)
- Activity 10.04 Parameterized stored procedure (series list)
- Exercise 10.05 Multiple parameters stored procedure (country list)
- Activity 10.05 Multiple parameters stored procedure (date list)
- Exercise 10.06 Multiple parameters stored procedure (crosstab queries)
- The Bad Bits form
- Exercise 10.07 Bad Bits demonstration
- Unbound forms
- Another way to unbind a form from a linked table
- Exercise 10.08 Removing all linked tables
- Summary
- Chapter 11: MS Excel VBA and MySQL Part 1
- Introduction to Excel
- Exercise 11.01 Setting up a sample MySQL database
- Exploring the ODBC connection
- The Developer menu
- Exercise 11.02 Activating the Developer tab and the VBA IDE
- Exploring the Excel VBA structure
- Preparing your Excel project
- Exercise 11.03 Creating a code module
- Learning about VBA libraries
- Exercise 11.04 Referencing a library
- Exercise 11.05 Inserting worksheets
- Connecting to the MySQL database using VBA
- Setting the scene
- Exercise 11.06 The connection variable
- Connection functions in VBA
- Exercise 11.07 Creating a connection function
- Reading data from MySQL using VBA
- Exercise 11.08 ReadGenreSales
- Exercise 11.09 Genre dropdown
- Auto-running functions when opening a workbook
- Exercise 11.10 Auto-running functions when opening a workbook
- Populating charts
- Populating a chart Genre sales
- Exercise 11.11 Loading Genre Sales chart data
- Running code on changes to a document
- Exercise 11.12 Detecting and working with worksheet changes
- Activity 11.01 Creating a chart (artist track sales)
- Summary
- Introduction to Excel
- Chapter 12: Working With Microsoft Excel VBA Part 2
- An introduction to MySQL connections
- Connecting to the MySQL database using ODBC
- Exercise 12.01 creating a DSN connection function
- Exploring generic data read functions
- Exercise 12.02 a generic data reader
- Creating connections to MySQL in Excel
- Exercise 12.03 creating a connection to MySQL
- Inserting data using MySQL for Excel
- Exercise 12.04 inserting the top 25 selling artists
- Updating data using MySQL for Excel
- Exercise 12.05 updating MySQL data employees
- Pushing data from Excel
- Exercise 12.06 pushing data from Excel to a new MySQL table
- Pivot tables
- Exercise 12.07 album sales
- Activity 12.01 building a MySQL-based Excel document
- Summary
- Section 4: Protecting Your Database
- Chapter 13: Getting Data into MySQL
- An introduction to data preparation
- Working with the X DevAPI
- An example of the X DevAPI
- Using MySQL Shell with the X DevAPI
- Exercise 13.01 inserting values with MySQL Shell in JS mode
- Inserting documents
- Exercise 13.02 inserting documents into a table
- Loading data from a SQL file
- Exercise 13.03 loading data from a SQL file and viewing tables
- Exercise 13.04 importing a SQL file using MySQL Workbench
- Loading data from a CSV file
- The SELECTINTO OUTFILE Format
- The LOAD DATA INFILEINTO format
- Exercise 13.05 loading data from a CSV file
- Loading data from a JSON file
- Exercise 13.06 loading data from a JSON file
- Using the CSV storage engine to export data
- Exercise 13.07 utilizing the CSV storage engine to export data
- Using the CSV storage engine to import data
- Exercise 13.08 utilizing the CSV storage engine to import data
- Searching and filtering JSON documents
- Exercise 13.09 Searching collections and filtering documents
- Using JSON functions and operators to query JSON columns
- Exercise 13.10 querying JSON data with SQL
- Using generated columns to query and index JSON data
- Activity 13.01 Exporting report data to CSV for Excel
- Summary
- Chapter 14: Manipulating User Permissions
- Introduction to user permissions
- Exploring users and accounts
- How to connect to MySQL with a set of credentials
- Creating, modifying, and dropping a user
- Granting permissions
- Inspecting users
- Exercise 14.01 creating users and granting permissions
- Changing users
- Flush privileges
- Changing permissions
- Exercise 14.02 modifying users and revoking permissions
- Using roles
- Exercise 14.03 using roles to manage permissions
- Troubleshooting access problems
- Activity 14.01 creating users for managing the world schema
- Summary
- Chapter 15: Logical Backups
- An introduction to backups
- Understanding the basics of backups
- Logical and physical backup
- Types of restore
- Performing backups
- Using mysqldump
- Exercise 15.01 backup using mysqldump
- Using mysqlpump
- Exercise 15.02 backing up using mysqlpump
- Scheduling backups
- Full restore
- Partial restore
- Exercise 13.03 restore a single schema backup
- Using point-in-time recovery with binlog files
- GTID format
- Using mysqlbinlog to inspect binlog contents
- Activity 15.01 backing up and restoring a single schema
- Activity 15.02 performing a point-in-time restore
- Summary
- Appendix
- Solution to Activity 1.1
- Solution to Activity 2.1
- Solution to Activity 3.1
- Solution to Activity 3.2
- Solution to Activity 4.1
- Solution to Activity 5.1
- Solution to Activity 5.2
- Solution to Activity 6.1
- Solution to Activity 7.1
- Solution to Activity 8.1
- Solution to Activity 8.2
- Solution to Activity 9.1
- Solution to Activity 10.1
- Solution to Activity 10.2
- Solution to Activity 10.3
- Solution to Activity 10.4
- Solution to Activity 10.5
- Solution to Activity 11.1
- Solution to Activity 12.1
- Solution to Activity 13.1
- Solution to Activity 14.1
- Solution to Activity 15.1
- Solution to Activity 15.2
- Why subscribe?
- Other Books You May Enjoy
- Packt is searching for authors like you
- Share Your Thoughts
- Tytuły: The MySQL Workshop
- Autor: Thomas Pettit, Scott Cosentino
- Tytuł oryginału: The MySQL Workshop
- ISBN Ebooka: 9781839215476, 9781839215476
- Data wydania: 2022-04-29
- Identyfikator pozycji: e_2t3k
-
Kategorie: