دانلود کتاب The MySQL Workshop: A practical guide to working with data and managing databases with MySQL

فهرست مطالب :

Cover Title page Copyright and credits Contributors About the reviewer Table of Contents Preface 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 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 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 SELECT…INTO OUTFILE Format The LOAD DATA INFILE…INTO 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 Index