| Section 1 An introduction to MySQL |
|
|
Chapter 1 An introduction to relational databases |
|
|
|
An introduction to client/server systems |
|
|
4 | (6) |
|
The hardware components of a client/server system |
|
|
4 | (2) |
|
The software components of a client/server system |
|
|
6 | (2) |
|
Other client/server architectures |
|
|
8 | (2) |
|
An introduction to the relational database model |
|
|
10 | (8) |
|
|
|
10 | (2) |
|
|
|
12 | (2) |
|
|
|
14 | (2) |
|
How to read a database diagram |
|
|
16 | (2) |
|
An introduction to SQL and SQL-based systems |
|
|
18 | (4) |
|
|
|
18 | (2) |
|
A comparison of Oracle, DB2, Microsoft SQL Server, and MySQL |
|
|
20 | (2) |
|
|
|
22 | (12) |
|
An introduction to the SQL statements |
|
|
22 | (2) |
|
How to work with database objects |
|
|
24 | (2) |
|
How to query a single table |
|
|
26 | (2) |
|
How to join data from two or more tables |
|
|
28 | (2) |
|
How to add, update, and delete data in a table |
|
|
30 | (2) |
|
|
|
32 | (2) |
|
How to use SQL from an application program |
|
|
34 | (8) |
|
Common options for accessing MySQL data |
|
|
34 | (2) |
|
PHP code that retrieves data from MySQL |
|
|
36 | (2) |
|
Java code that retrieves data from MySQL |
|
|
38 | (4) |
|
Chapter 2 How to use MySQL Workbench and other development tools |
|
|
|
An introduction to MySQL Workbench |
|
|
42 | (12) |
|
The Home page of MySQL Workbench |
|
|
42 | (2) |
|
How to open a database connection |
|
|
44 | (2) |
|
How to start and stop the database server |
|
|
46 | (2) |
|
How to navigate through the database objects |
|
|
48 | (2) |
|
How to view and edit the data for a table |
|
|
50 | (2) |
|
How to view and edit the column definitions for a table |
|
|
52 | (2) |
|
How to use MySQL Workbench to run SQL statements |
|
|
54 | (10) |
|
How to enter and execute a SQL statement |
|
|
54 | (2) |
|
|
|
56 | (2) |
|
How to handle syntax errors |
|
|
58 | (2) |
|
How to open and save SQL scripts |
|
|
60 | (2) |
|
How to enter and execute SQL scripts |
|
|
62 | (2) |
|
How to use the MySQL Reference Manual |
|
|
64 | (2) |
|
|
|
64 | (1) |
|
How to look up information |
|
|
64 | (2) |
|
How to use the MySQL Command Line Client |
|
|
66 | (8) |
|
How to start and stop the MySQL Command Line Client |
|
|
66 | (2) |
|
How to use the MySQL Command Line Client to work with a database |
|
|
68 | (6) |
|
Chapter 3 How to retrieve data from a single table |
|
|
|
An introduction to the SELECT statement |
|
|
74 | (4) |
|
The basic syntax of the SELECT statement |
|
|
74 | (2) |
|
SELECT statement examples |
|
|
76 | (2) |
|
How to code the SELECT clause |
|
|
78 | (14) |
|
How to code column specifications |
|
|
78 | (2) |
|
How to name the columns in a result set using aliases |
|
|
80 | (2) |
|
How to code arithmetic expressions |
|
|
82 | (2) |
|
How to use the CONCAT function to join strings |
|
|
84 | (2) |
|
How to use functions with strings, dates, and numbers |
|
|
86 | (2) |
|
How to test expressions by coding statements without FROM clauses |
|
|
88 | (2) |
|
How to eliminate duplicate rows |
|
|
90 | (2) |
|
How to code the WHERE clause |
|
|
92 | (12) |
|
How to use the comparison operators |
|
|
92 | (2) |
|
How to use the AND, OR, and NOT logical operators |
|
|
94 | (2) |
|
How to use the IN operator |
|
|
96 | (2) |
|
How to use the BETWEEN operator |
|
|
98 | (2) |
|
How to use the LIKE and REGEXP operators |
|
|
100 | (2) |
|
How to use the IS NULL clause |
|
|
102 | (2) |
|
How to code the ORDER BY clause |
|
|
104 | (4) |
|
How to sort by a column name |
|
|
104 | (2) |
|
How to sort by an alias, expression, or column number |
|
|
106 | (2) |
|
How to code the LIMIT clause |
|
|
108 | (6) |
|
How to limit the number of rows |
|
|
108 | (1) |
|
How to return a range of rows |
|
|
108 | (6) |
|
Chapter 4 How to retrieve data from two or more tables |
|
|
|
How to work with inner joins |
|
|
114 | (14) |
|
How to code an inner join |
|
|
114 | (2) |
|
|
|
116 | (2) |
|
How to join to a table in another database |
|
|
118 | (2) |
|
How to use compound join conditions |
|
|
120 | (2) |
|
|
|
122 | (2) |
|
How to join more than two tables |
|
|
124 | (2) |
|
How to use the implicit inner join syntax |
|
|
126 | (2) |
|
How to work with outer joins |
|
|
128 | (6) |
|
How to code an outer join |
|
|
128 | (2) |
|
|
|
130 | (4) |
|
Other skills for working with joins |
|
|
134 | (6) |
|
How to join tables with the USING keyword |
|
|
134 | (2) |
|
How to join tables with the NATURAL keyword |
|
|
136 | (2) |
|
|
|
138 | (2) |
|
|
|
140 | (10) |
|
|
|
140 | (1) |
|
A union that combines result sets from different tables |
|
|
140 | (2) |
|
A union that combines result sets from the same tables |
|
|
142 | (2) |
|
A union that simulates a full outer join |
|
|
144 | (6) |
|
Chapter 5 How to insert, update, and delete data |
|
|
|
How to create test tables |
|
|
150 | (2) |
|
How to create the tables for this book |
|
|
150 | (1) |
|
How to create a copy of a table |
|
|
150 | (2) |
|
|
|
152 | (6) |
|
How to insert a single row |
|
|
152 | (1) |
|
How to insert multiple rows |
|
|
152 | (2) |
|
How to insert default values and null values |
|
|
154 | (2) |
|
How to use a subquery in an INSERT statement |
|
|
156 | (2) |
|
How to update existing rows |
|
|
158 | (4) |
|
|
|
158 | (2) |
|
How to use a subquery in an UPDATE statement |
|
|
160 | (2) |
|
How to delete existing rows |
|
|
162 | (8) |
|
|
|
162 | (1) |
|
How to use a subquery in a DELETE statement |
|
|
162 | (8) |
| Section 2 More SQL skills as you need them |
|
|
Chapter 6 How to code summary queries |
|
|
|
How to work with aggregate functions |
|
|
170 | (4) |
|
How to code aggregate functions |
|
|
170 | (2) |
|
Queries that use aggregate functions |
|
|
172 | (2) |
|
How to group and summarize data |
|
|
174 | (14) |
|
How to code the GROUP BY and HAVING clauses |
|
|
174 | (2) |
|
Queries that use the GROUP BY and HAVING clauses |
|
|
176 | (2) |
|
How the HAVING clause compares to the WHERE clause |
|
|
178 | (2) |
|
How to code compound search conditions |
|
|
180 | (2) |
|
How to use the WITH ROLLUP operator |
|
|
182 | (2) |
|
How to use the GROUPING function |
|
|
184 | (4) |
|
How to code aggregate window functions |
|
|
188 | (12) |
|
How the aggregate window functions work |
|
|
188 | (2) |
|
|
|
190 | (4) |
|
|
|
194 | (6) |
|
Chapter 7 How to code subqueries |
|
|
|
An introduction to subqueries |
|
|
200 | (4) |
|
|
|
200 | (2) |
|
|
|
202 | (2) |
|
How to code subqueries in the WHERE clause |
|
|
204 | (4) |
|
How to use the IN operator |
|
|
204 | (2) |
|
How to use the comparison operators |
|
|
206 | (2) |
|
How to use the ALL keyword |
|
|
208 | (8) |
|
How to use the ANY and SOME keywords |
|
|
210 | (2) |
|
How to code correlated subqueries |
|
|
212 | (2) |
|
How to use the EXISTS operator |
|
|
214 | (2) |
|
How to code subqueries in other clauses |
|
|
216 | (4) |
|
How to code subqueries in the HAVING clause |
|
|
216 | (1) |
|
How to code subqueries in the SELECT clause |
|
|
216 | (2) |
|
How to code subqueries in the FROM clause |
|
|
218 | (2) |
|
How to work with complex queries |
|
|
220 | (4) |
|
A complex query that uses subqueries |
|
|
220 | (2) |
|
A procedure for building complex queries |
|
|
222 | (2) |
|
How to work with common table expressions |
|
|
224 | (8) |
|
|
|
224 | (2) |
|
How to code a recursive CTE |
|
|
226 | (6) |
|
Chapter 8 How to work with data types |
|
|
|
|
|
232 | (16) |
|
|
|
232 | (2) |
|
|
|
234 | (2) |
|
|
|
236 | (2) |
|
The fixed-point and floating-point types |
|
|
238 | (2) |
|
|
|
240 | (4) |
|
|
|
244 | (2) |
|
|
|
246 | (2) |
|
|
|
248 | (10) |
|
How implicit data conversion works |
|
|
248 | (2) |
|
How to convert data using the CAST and CONVERT functions |
|
|
250 | (2) |
|
How to convert data using the FORMAT and CHAR functions |
|
|
252 | (6) |
|
Chapter 9 How to use functions |
|
|
|
How to work with string data |
|
|
258 | (8) |
|
A summary of the string functions |
|
|
258 | (2) |
|
Examples that use string functions |
|
|
260 | (2) |
|
How to sort by a string column that contains numbers |
|
|
262 | (2) |
|
|
|
264 | (2) |
|
How to work with numeric data |
|
|
266 | (4) |
|
How to use the numeric functions |
|
|
266 | (2) |
|
How to search for floating-point numbers |
|
|
268 | (2) |
|
How to work with date/time data |
|
|
270 | (14) |
|
How to get the current date and time |
|
|
270 | (2) |
|
How to parse dates and times with date/time functions |
|
|
272 | (2) |
|
How to parse dates and times with the EXTRACT function |
|
|
274 | (2) |
|
How to format dates and times |
|
|
276 | (2) |
|
How to perform calculations on dates and times |
|
|
278 | (2) |
|
|
|
280 | (2) |
|
|
|
282 | (2) |
|
Other functions you should know about |
|
|
284 | (22) |
|
How to use the CASE function |
|
|
284 | (2) |
|
How to use the IF, IFNULL, and COALESCE functions |
|
|
286 | (2) |
|
How to use the regular expression functions |
|
|
288 | (4) |
|
How to use the ranking functions |
|
|
292 | (4) |
|
How to use the analytic functions |
|
|
296 | (10) |
| Section 3 Database design and implementation |
|
|
Chapter 10 How to design a database |
|
|
|
How to design a data structure |
|
|
306 | (16) |
|
The basic steps for designing a data structure |
|
|
306 | (2) |
|
How to identify the data elements |
|
|
308 | (2) |
|
How to subdivide the data elements |
|
|
310 | (2) |
|
How to identify the tables and assign columns |
|
|
312 | (2) |
|
How to identify the primary and foreign keys |
|
|
314 | (2) |
|
How to enforce the relationships between tables |
|
|
316 | (2) |
|
|
|
318 | (2) |
|
How to identify the columns to be indexed |
|
|
320 | (2) |
|
How to normalize a data structure |
|
|
322 | (10) |
|
|
|
322 | (2) |
|
How to apply the first normal form |
|
|
324 | (2) |
|
How to apply the second normal form |
|
|
326 | (2) |
|
How to apply the third normal form |
|
|
328 | (2) |
|
When and how to denormalize a data structure |
|
|
330 | (2) |
|
How to use MySQL Workbench for database design |
|
|
332 | (10) |
|
How to open an existing EER model |
|
|
332 | (1) |
|
How to create a new EER model |
|
|
332 | (2) |
|
How to work with an EER model |
|
|
334 | (2) |
|
How to work with an EER diagram |
|
|
336 | (6) |
|
Chapter 11 How to create databases, tables, and indexes |
|
|
|
How to work with databases |
|
|
342 | (2) |
|
How to create and drop a database |
|
|
342 | (1) |
|
|
|
342 | (2) |
|
|
|
344 | (12) |
|
|
|
344 | (2) |
|
How to code a primary key constraint |
|
|
346 | (2) |
|
How to code a foreign key constraint |
|
|
348 | (2) |
|
How to alter the columns of a table |
|
|
350 | (2) |
|
How to alter the constraints of a table |
|
|
352 | (2) |
|
How to rename, truncate, and drop a table |
|
|
354 | (2) |
|
|
|
356 | (2) |
|
|
|
356 | (1) |
|
|
|
356 | (2) |
|
A script that creates a database |
|
|
358 | (4) |
|
How to use MySQL Workbench |
|
|
362 | (6) |
|
How to work with the columns of a table |
|
|
362 | (2) |
|
How to work with the indexes of a table |
|
|
364 | (2) |
|
How to work with the foreign keys of a table |
|
|
366 | (2) |
|
How to work with character sets and collations |
|
|
368 | (6) |
|
An introduction to character sets and collations |
|
|
368 | (2) |
|
How to view character sets and collations |
|
|
370 | (2) |
|
How to specify a character set and a collation |
|
|
372 | (2) |
|
How to work with storage engines |
|
|
374 | (8) |
|
An introduction to storage engines |
|
|
374 | (1) |
|
How to view storage engines |
|
|
374 | (2) |
|
How to specify a storage engine |
|
|
376 | (6) |
|
Chapter 12 How to create views |
|
|
|
|
|
382 | (4) |
|
|
|
382 | (2) |
|
|
|
384 | (2) |
|
|
|
386 | (16) |
|
|
|
386 | (4) |
|
How to create an updatable view |
|
|
390 | (2) |
|
How to use the WITH CHECK OPTION clause |
|
|
392 | (2) |
|
How to insert or delete rows through a view |
|
|
394 | (2) |
|
How to alter or drop a view |
|
|
396 | (6) |
| Section 4 Stored program development |
|
|
Chapter 13 Language skills for writing stored programs |
|
|
|
An introduction to stored programs |
|
|
402 | (4) |
|
Four types of stored programs |
|
|
402 | (1) |
|
A script that creates and calls a stored procedure |
|
|
402 | (2) |
|
A summary of statements for coding stored programs |
|
|
404 | (2) |
|
How to write procedural code |
|
|
406 | (24) |
|
|
|
406 | (2) |
|
How to declare and set variables |
|
|
408 | (2) |
|
How to code IF statements |
|
|
410 | (2) |
|
How to code CASE statements |
|
|
412 | (2) |
|
|
|
414 | (2) |
|
|
|
416 | (2) |
|
How to declare a condition handler |
|
|
418 | (2) |
|
How to use a condition handler |
|
|
420 | (4) |
|
How to use multiple condition handlers |
|
|
424 | (6) |
|
Chapter 14 How to use transactions and locking |
|
|
|
How to work with transactions |
|
|
430 | (4) |
|
How to commit and rollback transactions |
|
|
430 | (2) |
|
How to work with save points |
|
|
432 | (2) |
|
How to work with concurrency and locking |
|
|
434 | (12) |
|
How concurrency and locking are related |
|
|
434 | (2) |
|
The four concurrency problems that locks can prevent |
|
|
436 | (2) |
|
How to set the transaction isolation level |
|
|
438 | (2) |
|
How to lock selected rows |
|
|
440 | (2) |
|
|
|
442 | (4) |
|
Chapter 15 How to create stored procedures and functions |
|
|
|
How to code stored procedures |
|
|
446 | (18) |
|
How to create and call a stored procedure |
|
|
446 | (2) |
|
How to code input and output parameters |
|
|
448 | (2) |
|
How to set a default value for a parameter |
|
|
450 | (2) |
|
How to validate parameters and raise errors |
|
|
452 | (2) |
|
A stored procedure that inserts a row |
|
|
454 | (4) |
|
How to work with user variables |
|
|
458 | (2) |
|
How to work with dynamic SQL |
|
|
460 | (2) |
|
How to drop a stored procedure |
|
|
462 | (2) |
|
How to code stored functions |
|
|
464 | (8) |
|
How to create and call a function |
|
|
464 | (2) |
|
How to use function characteristics |
|
|
466 | (2) |
|
A function that calculates balance due |
|
|
468 | (2) |
|
|
|
470 | (2) |
|
How to use Workbench with procedures and functions |
|
|
472 | (6) |
|
How to view and edit stored routines |
|
|
472 | (1) |
|
How to create stored routines |
|
|
472 | (1) |
|
How to drop stored routines |
|
|
472 | (6) |
|
Chapter 16 How to create triggers and events |
|
|
|
How to work with triggers |
|
|
478 | (8) |
|
How to create a BEFORE trigger |
|
|
478 | (2) |
|
How to use a trigger to enforce data consistency |
|
|
480 | (2) |
|
How to create an AFTER trigger |
|
|
482 | (2) |
|
How to view or drop triggers |
|
|
484 | (2) |
|
|
|
486 | (8) |
|
How to turn the event scheduler on or off |
|
|
486 | (1) |
|
|
|
486 | (2) |
|
How to view, alter, or drop events |
|
|
488 | (6) |
| Section 5 Database administration |
|
|
Chapter 17 An introduction to database administration |
|
|
|
Database administration concepts |
|
|
494 | (4) |
|
Database administrator responsibilities |
|
|
494 | (2) |
|
|
|
496 | (1) |
|
|
|
496 | (2) |
|
How to monitor the server |
|
|
498 | (8) |
|
How to view the server status |
|
|
498 | (2) |
|
How to view and kill processes |
|
|
500 | (2) |
|
How to view the status variables |
|
|
502 | (2) |
|
How to view the system variables |
|
|
504 | (2) |
|
How to configure the server |
|
|
506 | (6) |
|
How to set system variables using MySQL Workbench |
|
|
506 | (2) |
|
How to set system variables using a text editor |
|
|
508 | (2) |
|
How to set system variables using the SET statement |
|
|
510 | (2) |
|
|
|
512 | (12) |
|
How to enable and disable logging |
|
|
512 | (2) |
|
|
|
514 | (2) |
|
How to view text-based logs |
|
|
516 | (2) |
|
|
|
518 | (6) |
|
Chapter 18 How to secure a database |
|
|
|
An introduction to user accounts |
|
|
524 | (8) |
|
An introduction to SQL statements for user accounts |
|
|
524 | (2) |
|
|
|
526 | (4) |
|
The four privilege levels |
|
|
530 | (1) |
|
The grant tables in the mysql database |
|
|
530 | (2) |
|
How to work with users and privileges |
|
|
532 | (14) |
|
How to create, rename, and drop users |
|
|
532 | (2) |
|
How to specify user account names |
|
|
534 | (2) |
|
|
|
536 | (2) |
|
|
|
538 | (2) |
|
|
|
540 | (2) |
|
|
|
542 | (2) |
|
A script that creates users |
|
|
544 | (2) |
|
|
|
546 | (6) |
|
How to create, manage, and drop roles |
|
|
546 | (4) |
|
A script that creates users and roles |
|
|
550 | (2) |
|
How to use MySQL Workbench |
|
|
552 | (12) |
|
How to work with users and privileges |
|
|
552 | (4) |
|
How to connect as a user for testing |
|
|
556 | (8) |
|
Chapter 19 How to backup and restore a database |
|
|
|
Strategies for backing up and restoring a database |
|
|
564 | (2) |
|
|
|
564 | (1) |
|
|
|
564 | (2) |
|
How to back up a database |
|
|
566 | (8) |
|
How to use mysqldump to back up a database |
|
|
566 | (2) |
|
A SQL script file for a database backup |
|
|
568 | (4) |
|
How to set advanced options for a database backup |
|
|
572 | (2) |
|
How to restore a database |
|
|
574 | (4) |
|
How to use a SQL script file to restore a full backup |
|
|
574 | (2) |
|
How to execute statements in the binary log |
|
|
576 | (2) |
|
How to import and export data |
|
|
578 | (4) |
|
How to export data to a file |
|
|
578 | (2) |
|
How to import data from a file |
|
|
580 | (2) |
|
How to check and repair tables |
|
|
582 | (12) |
|
How to use the CHECK TABLE statement |
|
|
582 | (2) |
|
How to repair a MyISAM table |
|
|
584 | (1) |
|
How to repair an InnoDB table |
|
|
584 | (2) |
|
How to use the mysqlcheck program |
|
|
586 | (2) |
|
How to use the myisamchk program |
|
|
588 | |
| Appendix A How to install the software for this book on Windows |
|
|
How to install the software from mysql.com |
|
|
594 | (2) |
|
How to install the MySQL Community Server |
|
|
594 | (1) |
|
How to install MySQL Workbench |
|
|
594 | (2) |
|
How to install the software from murach.com |
|
|
596 | (6) |
|
How to install the source files for this book |
|
|
596 | (2) |
|
How to create the databases for this book |
|
|
598 | (1) |
|
How to restore the databases |
|
|
598 | (4) |
| Appendix B How to install the software for this book on macOS |
|
|
How to install the software from mysql.com |
|
|
602 | (4) |
|
How to install the MySQL Community Server |
|
|
602 | (2) |
|
How to install MySQL Workbench |
|
|
604 | (2) |
|
How to install the software from murach.com |
|
|
606 | |
|
How to install the source files for this book |
|
|
606 | (2) |
|
How to create the databases for this book |
|
|
608 | (1) |
|
How to restore the databases |
|
|
608 | |
