MySQL.Crash.Course.2023.3.epub

【实例简介】MySQL.Crash.Course.2023.3.epub

：crash 系列编程书籍都是经典中的经典教程

【实例截图】

【核心代码】

Contents In Detail
PRAISE FOR MySQL Crash Course
Title Page
Copyright
Dedication
About the Author
Acknowledgments
Introduction
About This Book
Who Is This Book For?
SQL in MySQL vs. SQL in Other Database Systems
Using the Online Resources
Part I: Getting Started
Chapter 1: Installing MySQL and Tools
The MySQL Architecture
Installing MySQL
Summary
Chapter 2: Creating Databases and Tables
Using MySQL Workbench
Creating a New Database
Creating a New Table
Constraints
Indexes
Dropping and Altering Tables
Summary
Part II: Selecting Data from a MySQL Database
Chapter 3: Introduction to SQL
Querying Data from a Table
Using the Wildcard Character
Ordering Rows
Formatting SQL Code
Uppercase Keywords
Backticks
Code Comments
Null Values
Summary
Chapter 4: MySQL Data Types
String Data Types
char
varchar
enum
set
tinytext, text, mediumtext, and longtext
Binary Data Types
tinyblob, blob, mediumblob, and longblob
binary
varbinary
bit
Numeric Data Types
tinyint, smallint, mediumint, int, and bigint
Boolean
Decimal Data Types
Date and Time Data Types
The json Data Type
Spatial Data Types
Summary
Chapter 5: Joining Database Tables
Selecting Data from Multiple Tables
Table Aliasing
Types of Joins
Inner Joins
Outer Joins
Natural Joins
Cross Joins
Self Joins
Variations on Join Syntax
Parentheses
Old-School Inner Joins
Column Aliasing
Joining Tables in Different Databases
Summary
Chapter 6: Performing Complex Joins with Multiple Tables
Writing One Query with Two Join Types
Joining Many Tables
Associative Tables
Managing the Data in Your Result Set
The limit Keyword
The union Keyword
Temporary Tables
Common Table Expressions
Recursive Common Table Expressions
Derived Tables
Subqueries
Subqueries That Return More Than One Row
Correlated Subqueries
Summary
Chapter 7: Comparing Values
Comparison Operators
Equal
Not Equal
Greater Than
Greater Than or Equal To
Less Than
Less Than or Equal To
is null
is not null
in
not in
between
not between
like
not like
exists
Checking Booleans
or Conditions
Summary
Chapter 8: Calling Built-in MySQL Functions
What Is a Function?
Passing Arguments to a Function
Optional Arguments
Calling Functions Within Functions
Calling Functions from Different Parts of Your Query
Aggregate Functions
count()
max()
min()
sum()
avg()
group by
String Functions
concat()
format()
left()
right()
lower()
upper()
substring()
trim()
ltrim()
rtrim()
Date and Time Functions
curdate()
curtime()
now()
date_add()
date_sub()
extract()
datediff()
date_format()
str_to_date()
time_format()
Mathematical Operators and Functions
Mathematical Operators
Mathematical Functions
Other Handy Functions
cast()
coalesce()
distinct()
database()
if()
version()
Summary
Chapter 9: Inserting, Updating, and Deleting Data
Inserting Data
Inserting Null Values
Inserting Multiple Rows at Once
Inserting Without Listing Column Names
Inserting Sequences of Numbers
Inserting Data Using a Query
Using a Query to Create and Populate a New Table
Updating Data
Updating Multiple Rows
Updating Multiple Columns
Deleting Data
Truncating and Dropping a Table
Summary
Part III: Database Objects
Chapter 10: Creating Views
Creating a New View
Using Views to Hide Column Values
Inserting, Updating, and Deleting from Views
Dropping a View
Indexes and Views
Summary
Chapter 11: Creating Functions and Procedures
Functions vs. Procedures
Creating Functions
Redefining the Delimiter
Adding Parameters and Returning a Value
Specifying Characteristics
Defining the Function Body
Creating Procedures
Using select to Display Values
Defining Local Variables and User Variables
Using Logic in Procedures
Displaying Procedure Results with select
Using a Cursor
Declaring Output Parameters
Writing Procedures That Call Other Procedures
Listing the Stored Routines in a Database
Summary
Chapter 12: Creating Triggers
Triggers That Audit Data
After Insert Triggers
After Delete Triggers
After Update Triggers
Triggers That Affect Data
Before Insert Triggers
Before Update Triggers
Before Delete Triggers
Summary
Chapter 13: Creating Events
The Event Scheduler
Creating Events with No End Date
Creating Events with an End Date
Checking for Errors
Summary
Part IV: Advanced Topics
Chapter 14: Tips and Tricks
Common Mistakes
Working in the Wrong Database
Using the Wrong Server
Leaving where Clauses Incomplete
Running Partial SQL Statements
Transactions
Supporting an Existing System
Using the MySQL Command Line Client
Loading Data from a File
Loading Data to a File
MySQL Shell
Summary
Chapter 15: Calling MySQL from Programming Languages
PHP
PDO
Object-Oriented MySQLi
Procedural MySQLi
Python
Selecting from a Table
Inserting a Row into a Table
Calling a Stored Procedure
Java
Selecting from a Table
Inserting a Row into a Table
Calling a Stored Procedure
Summary
Part V: Projects
Chapter 16: Building a Weather Database
Technologies You’ll Use
cron
Bash
SQL Scripts
Project Overview
The Data File
Creating the Weather Tables
Data Types
Constraints
Loading the Data File
Copying the Data to Your Final Table
Scheduling the Bash Script on cron
Alternative Approaches
Summary
Chapter 17: Tracking Changes to Voter Data with Triggers
Setting Up the Database
Creating the Tables
The voter Table
The ballot Table
The race Table
The candidate Table
The ballot_candidate Table
Adding Triggers
Before Triggers
After Triggers
Alternative Approaches
Audit Tables
Triggers vs. Privileges
Replacing check Constraints with New Tables
Summary
Chapter 18: Protecting Salary Data with Views
Creating the employee Table
Creating the View
Controlling Permissions
Using MySQL Workbench to Test User Access
An Alternative Approach
Summary
Afterword
Index
List of Tables
Table 7-1: MySQL Comparison Operators
Table 8-1: Common Specifiers
Table 14-1: Lightning Bolt Icons in MySQL Workbench
Table 16-1: Fields in the CSV Data File
Table 17-1: After Trigger Names
List of Illustrations
Figure 1-1: The client/server architecture
Figure 1-3: Downloading MySQL without creating an account
Figure 2-1: Showing databases with MySQL Workbench
Figure 2-2: Primary keys and foreign keys
Figure 2-3: A table can have only one primary key, but it can have multiple foreign keys.
Figure 2-4: Indexes automatically created by MySQL for the dogtable
Figure 4-1: Describing the foodtable in MySQL Workbench
Figure 6-1: Three tables within the policedatabase
Figure 6-2: The portfoliotable is an associative table.
Figure 11-1: MySQL Workbench showing errors on lines 12 and 19
Figure 11-2: Running an endless loop in MySQL Workbench
Figure 11-3: Steps for using a cursor
Figure 13-1: The show eventscommand as seen in MySQL Workbench
Figure 13-2: Displaying event errors in MySQL Workbench
Figure 14-1: You used MySQL Workbench to create anemployeetable in the distributiondatabase . . . didn’t you?
Figure 14-2: Naming the MySQL Workbench tabs Development and Production
Figure 14-3: Mistakenly deleting all rows in the table using MySQL Workbench
Figure 14-4: Exploring an existing MySQL database
Figure 15-1: The results of display_mountains_pdo.php
Figure 15-2: The results of find_mountains_by_loc_pdo.php
Figure 15-3: The results of display_mountains_mysqli_oo.php
Figure 15-4: The results of find_mountains_by_loc_mysqli_oo.php
Figure 15-5: The results of display_mountains_mysqli_procedural.php
Figure 15-6: The results of find_mountains_by_loc_mysqli_procedural.php
Figure 16-1: An overview of your weather project
Figure 16-2: The weather.csvdata file
Figure 16-3: The current_weather_loadtable
Figure 16-4: Scheduling weather.shon cron to run every 5 minutes
Figure 17-1: The ballot for your election
Figure 17-2: The tables in your votingdatabase
Figure 17-3: Creating a ballot_typetable to store ballot types
Figure 18-1: Creating a MySQL Workbench connection
Figure 18-2: Creating a MySQL Workbench connection for root
Figure 18-3: Creating tables, view, and users and granting access using MySQL Workbench
Figure 18-4: Creating a MySQL Workbench connection for hr_user
Figure 18-5: MySQL Workbench connections for root, hr_user, and accounting_user
Figure 18-6: You can have multiple connections open in MySQL Workbench.
Figure 18-7: Querying the employeetable as hr_user
Figure 18-8: The accounting_usercannot view the employeetable.
Figure 18-9: The accounting_useris able to query the v_employeeview.
List of Listings
Listing 2-1: Creating a primary key
Listing 2-2: Creating multiple primary key columns
Listing 3-1: Using selectto display data from the continenttable
Listing 3-2: Results of running the selectstatement
Listing 4-1: Creating a table on planet statistics
Listing 5-1: Joining the subway_systemand countrytables
Listing 5-2: Performing a right outer join
Listing 5-3: Self join of the music_preferencetable
Listing 6-1: A query to list US wineries with in-season Merlot
Listing 6-2: Creating a temporary table
Listing 6-3: Naming and then querying a CTE
Listing 8-1: A query to sum amounts by customer
Listing 8-2: A query to sum amounts by salesperson
Listing 8-3: A query to count rows for each salesperson
Listing 8-4: A query to get the average amount sold by each salesperson
Listing 8-5: A query to display the salesperson’s name and their average amount sold
Listing 11-1: Creating the f_get_state_population()function
Listing 11-2: Creating the p_set_state_population()procedure
Listing 11-3: Creating the p_set_and_show_state_population()procedure
Listing 11-4: The p_compare_population()procedure
Listing 11-5: The p_population_group()procedure
Listing 11-6: Creating the p_split_big_ny_counties()procedure
Listing 12-1: Creating an after insert trigger
Listing 12-2: Creating an after delete trigger
Listing 12-3: Creating an after update trigger
Listing 12-4: Creating a before insert trigger
Listing 12-5: Creating a before update trigger
Listing 12-6: Creating a before delete trigger
Listing 13-1: Creating a monthly event
Listing 14-1: An updatestatement with incomplete criteria in the whereclause
Listing 15-1: Using PDO to display data from the mountaintable (display_mountains_pdo.php)
Listing 15-2: Using PDO to insert a row into the mountaintable (add_mountain_pdo.php)
Listing 15-3: Using PDO to call a stored MySQL procedure (find_mountains_by_loc_pdo.php)
Listing 15-4: Using object-oriented MySQLi to display data from the mountaintable (display_mountains_mysqli_oo.php)
Listing 15-5: Using object-oriented MySQLi to insert a row into the mountaintable (add_mountain_mysqli_oo.php)
Listing 15-6: Using object-oriented MySQLi to call a stored MySQL procedure (find_mountains_by_loc_mysqli_oo.php)
Listing 15-7: Using procedural MySQLi to display data from the mountaintable (display_mountains_mysqli_procedural.php)
Listing 15-8: Using procedural MySQLi to insert a row into the mountaintable (add_mountain_mysqli_procedural.php)
Listing 15-9: Using procedural MySQLi to call a stored MySQL procedure (find_mountains_by_loc_mysqli_procedural.php)
Listing 15-10: Using Python to display data from the mountaintable (display_mountains.py)
Listing 15-11: Using Python to insert a row into the mountaintable (add_mountain.py)
Listing 15-12: Using Python to call a stored procedure (find_mountains_by_loc.py)
Listing 15-13: Using Java to display data from themountaintable (MountainList.java)
Listing 15-14: Using Java to insert a row into themountaintable (MountainNew.java)
Listing 15-15: Using Java to call a MySQL stored procedure (MountainAsia.java)
Listing 16-1: Creating the current_weather_loadtable
Listing 16-2: The load_weather.sqlscript
Listing 16-3: The copy_weather.sqlscript
Listing 16-4: The weather.shscript
Listing 17-1: Defining the tr_voter_bitrigger
Listing 17-2: Defining the tr_ballot_candidate_bitrigger
Listing 17-3: Defining the tr_voter_butrigger
Listing 17-4: Defining the tr_voter_bdtrigger
Listing 17-5: Creating audit tables before defining your after triggers
Listing 17-6: Defining the tr_voter_aitrigger
Listing 17-7: Defining the tr_voter_adtrigger
Listing 17-8: Defining the tr_voter_autrigger
Guide
Cover
Front Matter
Dedication
Introduction
Part I: Getting Started
Chapter 1: Installing MySQL and Tools
Start Reading
Chapter 2: Creating Databases and Tables
Part II: Selecting Data from a MySQL Database
Chapter 3: Introduction to SQL
Chapter 4: MySQL Data Types
Chapter 5: Joining Database Tables
Chapter 6: Performing Complex Joins with Multiple Tables
Chapter 7: Comparing Values
Chapter 8: Calling Built-in MySQL Functions
Chapter 9: Inserting, Updating, and Deleting Data
Part III: Database Objects
Chapter 10: Creating Views
Chapter 11: Creating Functions and Procedures
Chapter 12: Creating Triggers
Chapter 13: Creating Events
Part IV: Advanced Topics
Chapter 14: Tips and Tricks
Chapter 15: Calling MySQL from Programming Languages
Part V: Projects
Chapter 16: Building a Weather Database
Chapter 17: Tracking Changes to Voter Data with Triggers
Chapter 18: Protecting Salary Data with Views