TRAINING & REFERENCE murach’s MySQL 2np EpItion Joel Murach Mike Muracu & Associates, INc. 4340 N. Knoll Ave. # Fresno, CA 93722 ‘www.murach.com * murachbooks@murach.comJoel Murach Editor: Ray Halliday Production: Maria Spera Books for web developers Murach’s Dreamweaver CC 2014 Murach’s HTMLS and CSS3 Murach’s JavaScript and jQuery Murach’s JavaScript and DOM Scripting Murach's PHP and MySQL (2" Edition) Murach’s Java Servlets and JSP (3 Edition) Murach’s ASP.NET 4.5 Web Programming with VB 2012 Murach’s ASP.NET 4.5 Web Programming with C# 2012 Books on Java, Visual Basic, and C# Murach’s Android Programming Murach’s Java Programming (4" Edition) Murach’s Visual Basic 2012 Murach’s C# 2012 Books for database developers Murach’s Oracle SQL and PL/SQL for Developers (2 Edition) Murach’s SOL Server 2012 for Developers For more on Murach books, please visit us at www.murach.com © 2015, Mike Murach & Associates, Ine. All rights reserved, Printed in the United States of America 10987654321 ISBN: 978-1-890774-82-0Content Introduction Section 1 An introduction to MySQL Chapter 1 An introduction to relational databases and SQL Chapter 2 How to use MySQL Workbench and other development tools Chapter 3 How to retrieve data from a single table Chapter 4 How to retrieve data from two or more tables, Chapter 5 How to insert, update, and delete data Section 2 More SQL skills as you need them Chapter6 How to code summary queries Chapter? How to code subqueries Chapter 8 How to work with data types Chapter 9 How to use functions Section 3 Database design and implementa’ Chapter 10 How to design a database Chapter 11 How to create databases, tables, and indexes Chapter 12 How to create views Section 4 Stored program development Chapter 13 Language skills for writing stored programs Chapter 14 How to use transactions and locking Chapter 15 How to create stored procedures and functions Chapter 16 How to create triggers and events Section 5 Database administration Chapter 17 An introduction to database administration Chapter 18 How to secure a database Chapter 19 How to backup and restore a database Appendixes Appendix A How to install the software for this book on Windows Appendix B How to install the software for this book on Mac OS X xii 4 73 113 149, 169 187 215 241 217 313 353 373 401 ais 445 461 493 527Expanded contents Expanded contents Section 1 An Chapter 1 Chapter 2 troduction to MySQL An introduction to relational databases and SQL An introduction to client/server systems.. The hardware components ofa clientserver system. The software components ofa client/server system. Other client/server architectures An introduction to the relational database model How a table is organized, How tables are related How columns are defined How to read a database diagram. An introduction to SQL and SQL-based systems A brief history of SQL. A comparison of Oracle, DB2, Microsoft SQL Server, and MySQL. 20 The SQL statements An introduetion tothe SQL statements How to work with database objects How to query a single table, How to join data from two oF more tables How to add, update, and delete data in a table SQL coding guidelines How to use SQL from an application program Common options for accessing MySQL data PHP code that retrieves data from MySQL. Java code that retrieves data feom MySQL. How to use MySQL Workbench and other development tools An introduction to MySQL Workbenc! ‘The Home tab of MySQL Workbench, How to open a database connection. Hw to start and stop the database server How to navigate through the database objects, How to view and edit the data fora table How to view and edit the column definitions fora table. 3 How to use MySQL Workbench to run SQL statements. How to enter and execute a SQL statement: How to use snippets How to handle syntax errors How to open and save SQL scripts How to enter and execute SQL scripts. How to use the MySQL Reference Manual. How to view the manual How to look up information, How to use the MySQL Command Line Client, How to start and stop the MySQL Command Line Client. How to use the MySQL Command Line Client to work with a database vvi Expanded contents Chapter 3 Chapter 4 How to retrieve data from a single table An introduction to the SELECT statement. The basic syntax of the SELECT statement SELECT statement examples How to code the SELECT claus« How to code column specifications. How to name the columns in a result set using aliases How to code arithmetic expressions How to use the CONCAT funetion to join stings. How to use functions with strings, dates, and numbers How to test expressions by coding statements without FROM clauses 88 How to eliminate duplicate rows 90 How to code the WHERE clause. How to use the comparison operators. How to use the AND, OR, and NOT logical operators. 94 How to use the IN operator. How to use the BETWEEN operator. How to use the LIKE and REGEXP operators How to use the IS NULL clause. How to code the ORDER BY clause How to sort by a column name How to sort by an alias, expression, or column number, 106 How to code the LIMIT clause How to limit the number of rows. How to eetuen a range of sows, How to retrieve data from two or more tables How to work with inner joins... How to code an inner join How to use table aliases. How to join toa table in another database How to use compound join conditions. How to use a seléjoin How to join more than two tables Hw to-use the implicit inner join syntax How to work with outer joins... How to code an outer join Outer join examples. Other skills for working with join How to join tables with the USING keyword. How to join tables with the NATURAL keyword, How to use cross joins How to work with unions. How to code a union ‘A union that combines result sets from different tables, union that combines result sets from the same tables ‘A union that simulates a fll outer joinChapter 5 Section 2 Chapter 6 Chapter 7 Expanded contents How to insert, update, and delete data How to create test tables .. How to create the tables for this book 180 How to create a copy ofa table. How to insert new rows.. How to insert a single row How to insert multiple rows. How to insert default values and null values. ist Hw to use a subquery in an INSERT statement 136 How to update existing rows How to update rows How to usea subquery in an UPDATE statement 160 How to delete existing rows.. How to delete rows. How to usea subguery in a DELETE statement 162 More SQL skills as you need them How to code summary queries How to work with aggregate functions. How to code aggregate functions Queries that use aggregate functions, How to group and summarize data How to code the GROUP BY and HAVING clauses m4 Queries that use the GROUP BY and HAVING clauses. 176 How the HAVING clause compares to the WHERE clause V8 How to code compound search conditions. 180) How to use the WITH ROLLUP operator. 182 How to code subqueries An introduction to subqueries Where to code subqueries, When to use subqueries How to code subqueries in the WHERE clause. Hw to use the IN operator. How to use the comparison operators. How to use the ALL keyword How to use the ANY and SOME keywords. How to code correlated subqueries. How to use the EXISTS operator How to code subqueries in other clauses. How to code subqueries in the HAVING clause How to code subqueries in the SELECT clause. How to code subqueries in the FROM clause, How to work with complex queries A complex query that uses subqueries, A procedure for building complex queries viiviii Expanded contents Chapter 8 Chapter 9 Section 3 Chapter 10 How to work with data types The data types Overview The character types. The integer types. The fixed-point and floating-point types The date and time types. The ENUM and SET types. The large object types. How to convert data, How implicit data conversion works How to convert data using the CAST and CONVERT functions How to convert data using the FORMAT and CHAR functions How to use functions How to work with string data.. A summary ofthe string functions. Examples that use string functions How to sore by a string column that contains numbers 246 How to parse a string How to work with numeric data Hw to use the numeric functions How to search fr floating-point numbers How to work with date/time data. How to get the current date and time How to parse dates and times with date/time funetions How to parse dates and times with the EXTRACT function How to format dates and times iow to perform calculations on dates and times How to search fora date Hw to search for atime: Other functions you should know about. How to use the CASE function How to use the IF, IFNULL, and COALESCE functions, Database design and implementation How to design a database How to design a data structure The basic steps for designing a data structure How to identity the data elements How to subdivide the data elements How to identify the tables and assign columns 284 How to identify the primary and foreign keys. 286 How to enforce the relationships between tables 288 How normalization works, How to identify the columns to be indexed How to normalize a data structure ‘The seven normal forms How to apply the first normal form How to apply the second normal form,Chapter 11 Chapter 12 Expanded contents How to apply the third normal form 300 When and how to denormalize a data structure How to use MySQL Workbench for database design . How to open an existing EER model. How to create a new EER model How to work with an EER model How to work with an EER diagram, How to create databases, tables, and indexes How to work with databases. Hw to ereate and drop a database How to select a database How to work with tables How to create a table. How to code a primary key constraint 318 How to code a foreign key constraint 320 How to alter the columns ofa table. 3 How to alter the constraints of a table 324 How to rename, truncate, and drop a table 325 How to work with indexes How to create an index How to drop an index. A script that creates a database. How to use MySQL Workbench.. How to work wit the columns ofa table How to work with the indexes ofa table How to work withthe foreign keys ofa table. How to work with character sets and collations. An introduction to character sets and collation How to view character sets and collations. How to specify a character set and a collation How to work with storage engines. An introduction to storage engines How to view storage engines How to specify a storage engine How to create views An introduction to views. How views work Benefits of using views How to work with views.. How to create a view How to create an updatable view 362 How to use the WITH CHECK OPTION clause 364 Hw to insert or delete rows through a view 366 How to alter or drop a view 368 ixx Expanded contents Section 4 Chapter 13 Chapter 14 Chapter 15 Chapter 16 Stored program development Language skills for writing stored programs An introduction to stored programs Four types of stored programs A script that ereates and calls a stored procedure A summary of statements for coding stored programs. How to write procedural code How to display data How to declare and set variables How to code IF statements, How to code CASE statements. How to code loops, Hw to use a cursor. How to declare a condition handler How to use a condition handler 392 How to use multiple condition handlers 396 How to use transactions and locking How to work with transactions. How to commit and rollback transactions How to work with save points How to work with concurrency and locking. How concurrency and locking are related. ‘The four concurtency problems that locks ean prevent. How to set the transaction isolation level How to prevent deadlocks How to create stored procedures and functions How to code stored procedures. How to create and cal a stored procedure How to code input and output parameters. How to seta default value fora parameter How to validate parameters and rise rors. A stored procedure that inserts a row How to work with user variables How to work with dynamic SQL. How to drop a stored procedure How to code stored functions How to create and call a function Afunction that calculates balance due How to drop a function How to use Workbench with procedures and functions .. How to view and edit stored routines, How to create stored routines How to drop stored routines How to create triggers and events How to work with triggers How to create a BEFORE trigger How to use a trigger to enforce data consistency 448 How to create an AFTER trigger 450 How to view or drop triggers. 452Section 5 Chapter 17 Chapter 18 Chapter 19 Expanded contents How to work with events How to turn on the event scheduler 454 Hw to create an event 454 How to view, alter, or drop events 456 An introduction to database administration Database administration concepts Database administrator responsibilities, Types of database files “Types of log files. How to monitor the server How to view the server status How to view and kill processes How to view the status variables How to view the system variables, How to configure the server Hw to set system variables using MySQL Workbench, 474 How to set system variables using a text editor. 476 Hw to set system variables using the SET statement. 478, How to work with logging How to enable and disable logging. 480 How to configure logging 482 How to view text-based logs 484 How to view the binary lo, 486 How to manage logs. 488 How to secure a database An introduction to user accounts An introduction to SQL statements for user accounts, 494 ‘A summary of privileges The four privilege levels The grant tables in the mysql database How to work with users and privileges .. How to create, rename, and drop users. How to specify user account names How to grant privileges, How to view privileges How to revoke privileges How to change passwords A script that ereates users How to use MySQL Workbench. How to work with users and privileges. How to connect asa user for testing How to backup and restore a database Strategies for backing up and restoring a database. ‘A backup strategy A restore strategy How to back up a databas How use mysqldump to back up a datal xixii Expanded contents Appendix A Appendix B A SQL script ile for a database backup. 532 How to set advanced options for a database backup 536 How to restore a database How to use a SQL script ile to restore a full backup. 538 How to execute statements inthe binary log. 540 How to view and edit statements inthe binary log 342 How to import and export data. How to export data toa file How to import data froma fle How to check and repair tables. How to use the CHECK TABLE statement. How to use the REPAIR TABLE statement How to repair an InnoDB table. How to use the mysqlcheck program, How to use the myisamehk program How to install the software for this book on Windows How to install the software from mysql.com .. How to install the MySQL Community Server. How to install MySQL Workbench How to install the software from murach.com. How to install the source files for this book: How to create the databases for this book. Hw to restore the databases How to install the software for this book on Mac OS X How to install the software from mysql.com .. How to install the MySQL Community Server How to install MySQL Workbench How to install the software from murach.com, How to install the source files for this book Hw to create the databases for this book How to restore the databases Hw to update the password forthe root user.Introduction Since its release in 2000, MySQL has become the world’s most popular ‘open-source database. It has been used by everyone from hobbyists to the world’s largest companies to deliver cost-effective, high-performance, scalable database applications...the type of applications that the web is built on, In fact, MySQL has been used as the database for many high-profile websites, including Wikipedia, Facebook, and Twitter. So knowing MySQL is a plus for any developer today. Who this book is for This book is designed for developers who are new to MySQL, as well as developers who have been using MySQL for years but who still aren't getting the most from it, It shows how to code all the SQL statements that developers need for their applications, and it shows how to code these statements so they run efficiently This book is also a good choice for anyone who wants to learn standard SQL. Since SQL is a standard language for accessing database data, most of the SQL code in this book will work with any database management system. As a result, once you use this book to learn how to use SQL to work with a MySQL. database, you can transfer most of what you have learned to another database management system, such as Oracle, SQL Server, or DB2. This book is also the right first book for anyone who wants to become a database administrator. Although this book doesn’t present all of the advanced skills that are needed by a DBA, it will get you started. Then, when you complete this book, you'll be prepared for more advanced books on the subject. 5 reasons why you’ll learn faster with this book * Unlike most MySQL books, this one starts by showing you how to query an existing database rather than how to create a new database. Why? Because that’s what you're most likely to need to do first on the job. Once you masterxiv Introduction What you’ those skills, you can learn how to design and implement a database if you need to do that. Or, you can learn how to work with other database features like transactions or stored procedures if you need to do that. Unlike most MySQL books, this one shows you how to use MySQL Workbench to enter and run your SQL statements, MySQL Workbench is a graphical tool that’s an intuitive and user-friendly replacement for the MySQL Command Line Client, a command-line program that has been around since the beginning of MySQL. In our experience, using MySQL Workbench instead of the command line helps you learn more quickly. Like all of our books, this one includes hundreds of examples that range from the simple to the complex. That way, you can quickly get the idea of how a feature works from the simple examples, but you'll also see how the feature is used in the real world from the complex examples. Like most of our books, this one has exercises at the end of each chapter that give you hands-on experience by letting you practice what you've learned. These exercises also encourage you to experiment and to apply what you've learned in new ways. If you page through this book, you'll see that all of the information is presented in “paired pages,” with the essential syntax, examples, and ‘guidelines on the right page and the perspective and extra explanation on the left page. This helps you learn more with less reading, and it is the ideal reference format when you need to refresh your memory about how to do something, learn in this book In section 1, you'll learn the concepts and terms you need for working with any database, You'll learn how to use MySQL Workbench to work with a database and run SQL statements. You'll also learn all the SQL skills for retrieving data from a database and for adding, updating, and deleting that data, These skills are the critical SQL skills that you'll need to get started. In section 2, you can learn more SQL skills as you need them, You can learn how to summarize the data that you retrieve, You can learn how to code subqueries. You can learn about the types of data that MySQL supports. And you can learn how to use MySQL functions in your SQL statements, These advanced skills are sure to raise your expertise even if you already have SQL experience. In section 3, you'll learn how to design a database, This includes learning how to use MySQL Workbench to create an EER (enhanced entity- relationship) model for your database. Then, you'll learn how to implement that design by using the DDL (Data Definition Language) statements that are a part of SQL. When you're done, you'll be able to design and implement your own database. In addition, you'll gain valuable perspective that will make you a better SQL programmer, even if you never have to design a database,Introduction + Inssection 4, you'll learn how to use MySQL to create stored procedures, functions, triggers, and events. In addition, you'll learn how to manage transactions and locking. These features allow you to create stored programs made up of multiple SQL statements that can be stored in the database and accessed as needed, either to run on their own or to use in application programs...a great productivity booster! So once you master these features, ‘you'll have a powerful set of MySQL skills. * Inssection 5, you'll learn a starting set of skills for becoming a database administrator (DBA). These skills include how to secure a database, how to back up a database, and how to restore a database. What software you need for this book Although you should be able to use this book with most versions of MySQL. we recommend that you use: © MySQL Community Edition 5.6 or higher + MySQL Workbench 6.2 or higher Both of these products can be downloaded for free from MySQL'’s website. And appendixes A (Windows) and B (Mac OS X) provide complete instructions for installing them, Since the MySQL server is backwards compatible, all of the SQL statements presented in this book should also work with future versions of MySQL. In addition, most statements presented in this book work with earlier versions of MySQL. and we have done our best to identify any statements that don't If you use MySQL Workbench 6.2, all of the skills presented in this book should work exactly as described. However, MySQL Workbench is being actively developed, so its functionality is improving all the time. As a result, you may want to use a later version of MySQL Workbench. If you do, the skilis presented in this book may not work exactly as described, but they should work similarly. What you can download from our website You can download all the source code for this book from our website, That includes: © A script file that creates the three databases used by this book. * The source code for all examples in this book. © The solutions to the exercises that are at the end of each chapter. Again, appendixes A (Windows) and B (Mac OS X) provide complete instructions for installing these items on your computer. xvXVI Introduction Support materials for trainers and instructors If you're a corporate trainer or a college instructor who would like to use this book for a course, we've created a set of instructional materials that include: (D) a complete set of PowerPoint slides that you can use to review and reinforce the content of the book; (2) instructional objectives that describe the skills a student should have upon completion of each chapter; (3) test banks that measure mastery of those skills; (4) additional chapter exercises that aren’t in this book; and (5) solutions to those exercises. To learn more about these instructional materials, please visit our website at www.murach.com and follow the links for trainers and instructors. Or, if you prefer, you can call Kelly at 1-800-221-5528 or send an email to kelly@murach.com. Please let me know how this book works for you When I started this book, I had two goals, First, I wanted to get you started with MySQL as quickly and easily as possible. Second, I wanted to raise your database development skills to a professional level Now, I thank you for buying this book. I wish you all the best with your MySQL development. And if you have any comments about this book, I'd love to hear from you. Murnel, Joel Murach, Author joel@murach.comAn introduction to MySQL Before you begin to learn how to write SQL statements that work with MySQL, you need to understand some concepts and terms related to SQL and relational databases. That’s what you'll learn in chapter |. In addition, you'll need to learn about some of the tools you can use to work with a MySQL database. That's what you'll learn in chapter 2. ‘After that, you'll be ready to learn about the most important SQL statements. In chapter 3, you'll learn how to use the SELECT statement to retrieve data from a single table. In chapter 4, you'll learn how to use the SELECT statement to retrieve data from two or more tables. And in chapter 5, you'll earn how to use the INSERT, UPDATE, and DELETE statements to add, update, and delete rows. At that point, you'll have all of the background and skills that you need to work with the rest of this book.An introduction to relational databases This chapter presents the concepts and terms that you should understand before you begin learning how to work with a SQL database such as MySQL. Although this chapter doesn’t present the coding details, it does present an overview of the most important types of SQL statements that are presented in this book. An introduction to client/server systems. ‘The hardware components ofa client/server system ‘The software components of a client/server system 6 ther client/server architectures 8 An introduction to the relational database model How a table is organized How tables are related How columns are defined How to read a database diagram. An introduction to SQL and SQL-based systems.. A brief history of SQL. ‘A compatison of Oracle, DB2, Microsoft SQL Server, and MySQL. ....20 The SQL statements. ‘An introduction to the SQL statements How to work with database objects How t0 query a single table How to join data from two or more tables, How to add, update, and delete data ina table SQL coding guidclines How to use SQL from an application program. ‘Common options for accessing MySQL. data PHP code that retrieves data from MySQL. Java code that retrieves data from MySQL PerspectiveSection | An introduction to MySQL An introduction to client/server systems In case you aren’t familiar with client/server systems, the topics that follow introduce you to their essential hardware and software components. When you use SQL to access a MySQL database, that system is often a client/server system. The hardware components of a client/server system Figure 1-1 presents the three hardware components of a client/server system: the clients, the network, and the server. The clients are usually the PCs that are already available on the desktops throughout a company. Clients can also ‘be mobile devices like laptops, tablets, and smartphones. And the network is the cabling, communication lines, network interface cards, hubs, routers, and other ‘components that connect the clients and the server. The server, commonly referred to as a database server, is a computer that has enough processor speed, internal memory (RAM), and disk storage to store the files and databases of the system and provide services to the clients of the system, This computer can be a high-powered PC, a midrange system like an IBM System x or Unix system, or even a mainframe system, When a system. consists of networks, midrange systems, and mainframe systems, often spread throughout the country or world, itis commonly referred to as an enterprise system. To back up the files of a client/server system, a server usually has a backup disk drive or some other form of offline storage. It often has one or more printers or specialized devices that can be shared by the users of the system. And it can provide programs or services like e-mail that can be accessed by all the users of the system. In a simple client/server system, the clients and the server are part of a local area network (LAN). However, two or more LANS that reside at separate ‘geographical locations can be connected as part of a larger network such as a wide area network (WAN). In addition, individual systems or networks can be connected over the Internet,Chapter 1 An introduction to relational databases Asimple client/server system —— bi Database Server CI OI L_ L Client Client _l Client The three hardware components of a client/server system ‘© The clients are the PCs, Macs, or workstations of the system. They can also be mobile devices like laptops, tablets, and smartphones. © The server is a computer that stores the files and databases of the system and provides services to the clients. When it stores databases, it’s often referred to as a database server. + The nenvork consists of the cabling, communication lines, and other components that connect the clients and the servers of the system. Client/server system implementations ‘+ Ina simple client/server system like the one above, the server is typically a high-powered PC that communicates with the clients over a local area network (LAN), ‘© The server can also be a midrange system, like an IBM System x or a Unix system, or it can be a mainframe system. ‘© A clien/server system can also consist of one or more PC-based systems, one ‘or more midrange systems, and a mainframe system in dispersed geographical locations. This type of system is commonly referred to as an enterprise system. * Individual systems and LANs can be connected and share data over larger private networks, such as a wide area network (WAN), or a public network like the Internet. Figure 1-1 The hardware components of a client/server systemSection | An introduction to MySQL The software components of a client/server system Figure 1-2 presents the software components of a typical client/server system, Here, the server requires a database management system (DBMS) like MySQL or Microsoft SQL Server. This DBMS manages the databases that are stored on the server. In contrast to a server, each client requires application software to perform usefull work. This can be a purchased software package like a financial accounting package, or it can be custom software that’s developed for a specific application Although the application software is run on the client, it uses data that’s stored on the server. To do that, it uses a data access API (application program- ‘ming interface). Since the technique you use to work with an API depends on the programming language and API you're using, you won’t learn those techniques in this book. Instead, you'll learn about a standard language called SQL (Structured Query Language) that lets any application communicate with any DBMS. (In conversation, SQL is pronounced as either S-O-L or sequel.) ‘Once the software for both client and server is installed, the client commu- nicates with the server via SOL queries (or just queries) that are passed to the DBMS through the APL. After the client sends a query to the DBMS, the DBMS interprets the query and sends the results back to the client. Ina client/server system, the processing is divided between the clients and the server. In this figure, for example, the DBMS on the server processes the requests that are made by the application running on the client. Theoretically, at least, this balances the workload between the clients and the server so the system works more efficiently,Chapter 1 An introduction to relational databases Client software, server software, and and the SQL interface __sotaveres | ee A statement that adds a new column to a table ALTER TABLE invoices ADD balance due DECINAL(9,2) A statement that deletes the new column, ALTER TABLE invoic DROP COLUMN balance due A statement that creates an index on the table CREATE INDEX invoices vendor id index ON invoices (vendor_id) A statement that deletes the new index DROP INDEX invoices vendor id index ON invoices Figure 1-11 Typical statements for working with database objects ‘AUTO_INCREMENT, DEFAULT 0, DEFAULT 0,26 © Section 1 Am introduction to MySOL How to query a single table Figure 1-12 shows how to use a SELECT statement to query a single table in a database. To start, this figure shows some of the columns and rows of the Invoices table. Then, in the SELECT statement that follows, the SELECT clause names the columns to be retrieved, and the FROM clause names the table that contains the columns, called the base table. In this case, six columns will be retrieved from the Invoices table. Note that the last column, balance_due, is calculated from three other columns in the table. In other words, a column by the name of balance_due doesn’t actually exist in the database. This type of column is called a calculated value, and it exists only in the results of the query. In addition to the SELECT and FROM clauses, this SELECT statement includes a WHERE clause and an ORDER BY clause. The WHERE clause gives the criteria for the rows to be selected. In this case, a row is selected only if it has a balance due that’s greater than zero, Finally, the returned rows are sorted by the invoice_date column. This figure also shows the resu(t set (or result table) that’s returned by the SELECT statement. A result set is a logical table that’s created temporarily within the database. When an application requests data from a database, i receives a result setChapter 1 An introduction to relational databases 27 The Invoices base table A SELECT statement that retrieves and sorts selected columns and rows from the Invoices table SELECT invoice number, invoice date, invoice total, payment total, credit total, invoice total - payment total - credit total aS balance due FROM invoices WHERE invoice total - payment total - credit total > 0 ORDER BY invoice date The result set defined by the SELECT statement [inwa nate mec aie mock a _pamerieel ceitom bum ae yom meow) asa 000 oo est juss «mara S000 0 sae wasars maa 75000 oo 075 Concepts © You use the SELECT statement to retrieve selected columns and rows from a base table. The result of a SELECT statement is a result table, or result set, like the one shown above. © A result set can include calculated values that are calculated from columns in the table, * A SELECT statement is commonly referred to as a query. Figure 1-12 How to query a single table28 Section 1 An introduction to MySQL How to join data from two or more tables Figure 1-13 presents a SELECT statement that retrieves data from two tables. This type of operation is called a join because the data from the two tables is joined together into a single result set. For example, the SELECT state- ment in this figure joins data from the Invoices and Vendors tables. An inner join is the most common type of join. When you use an inner join, rows from the two tables in the join are included in the result table only if their related columns match. These matching columns are specified in the FROM clause of the SELECT statement. In the SELECT statement in this figure, for example, rows from the Invoices and Vendors tables are included only if the value of the vendor_id column in the Vendors table matches the value of the vendor_id column in one or more rows in the Invoices table. If there aren't any invoices for a particular vendor, that vendor won't be included in the result set. Although this figure shows only how to join data from two tables, you can extend this syntax to join data from three or more tables. If, for example, you want to include line item data from a table named Invoice_Line_Items in the results shown in this figure, you can code the FROM clause of the SELECT statement like this: FROM vendors INNER JOIN invoices ON vendors. vendor id = invoices.vendor id INWER JOIN invoice line items ON invoices.invoice id = invoice line items.invoice id ‘Then, in the SELECT clause, you can include any of the columns in the Invoice_Line_Items table. In addition to inner joins, most relational databases including MySQL. support other types of joins such as outer joins. An outer join lets you include all rows from a table even if the other table doesn’t have a matching row. You'll learn more about the different types of joins in chapter 4.Chapter 1 An introduction to relational databases 29 A SELECT statement that joins data from the Vendors and Invoices tables SELECT vendor_name, invoice number, invoice date, invoice total FROM vendors INNER JOIN invoices ON vendors.vendor id = invoic WHERE invoice total >= 500 ORDER BY vendor name, invoice total DESC endor_id The result set defined by the SELECT statement amisosos Concepts ‘* A join lets you combine data from two or more tables into a single result set. ‘+ The most common type of join is an inner join. This type of join returns rows from both tables only if their related columns match. ‘* An ower join returns rows from one table in the join even if the other table doesn’t contain a matching row. Figure 1-13 How to join data from two or more tables30 Section | An introduction to MySQL How to add, update, and delete data in a table Figure 1-14 shows how you can use the INSERT, UPDATE, and DELETE statements to modify the data in a table. In this figure, for example, the first statement is an INSERT statement that adds a row to the Invoices table. To do that, the INSERT clause names the columns whose values are supplied in the VALUES clause In chapter 7, you'll learn more about specifying column names and values. For now, just note that you have to specify a value for a column unless it’s a column that allows null values or a column that’s defined with a default value. The two UPDATE statements in this figure show how to change the data in one or more rows of a table, The first statement, for example, assigns a value of 35.89 to the credit_total column of the invoice in the Invoices table with invoice number 367447. The second statement adds 30 days to the invoice due date for each row in the Invoices table whose terms_id column has a value of 4, To delete rows froma table, you use the DELETE statement. For example, the first DELETE statement in this figure deletes the invoice with invoice number 4-342-8069 from the Invoices table, The second DELETE statement deletes all invoices with a balance due of zero, However, since the Invoices table has a foreign key that references the Invoice_Line_Items table, these DELETE statements won’t work unless the invoice doesn’t contain any line items. One way to get these DELETE statements to work is to delete the corresponding rows from the Invoice_Line_Items table first.Chapter 1 An introduction to relational databases 34 A statement that adds a row to the Invoices table INSERT INTO invoices (vendor_id, invoice number, invoice date, invoice total, terms id, invoice due date) VALUES (12, 13289175", 2014-07-18", 165, 3, 2014-08-17") A statement that changes the value of the credit_total column for a selected row in the Invoices table UPDATE invoices SET credit total = 35.09 WHERE invoice number = '367447' A statement that changes the values in the invoice_due_date column for all invoices with the specified terms_id UPDATE invoices SET invoice due date = DATE ADD(invoice due date, INTERVAL 30 DAY) WHERE terms_id = 4 A statement that deletes a selected invoice from the Invoices table DELETE FROM invoices WHERE invoice number = '4-242-8069" A statement that deletes all paid invoices from the Invoices table DELETE FROM invoices WHERE invoice total - payment total - credit total = 0 Concepts ‘* You use the INSERT statement to add rows to a table. + You use the UPDATE statement to change the values in one or more rows of a table based on the condition you specify. + You use the DELETE statement to delete one or more rows from a table based on the condition you specify. Warning If you're new to SQL statements, please don’t execute the statements above until you read chapter 5 and understand the effect that these statements can have on the database, Figure 1-14 How to add, update, and delete data in a table32 Section | An introduction to MySOL SQL coding guidelines SQL isa freeform language. That means that you can include line breaks, spaces, and indentation without affecting the way the database interprets the code. In addition, SQL isn’t case-sensitive like some languages. That means that you can use uppercase or lowercase letters or a combination of the two without affecting the way the database interprets the code. Although you can code SQL statements with a freeform style, we suggest that you follow the coding recommendations presented in figure 1-15. The examples in this figure illustrate the value of these coding recommendations. The first example presents an unformatted SELECT statement that’s difficult to read. In contrast, this statement is much easier to read after our coding recommenda- tions are applied as shown in the second example. The third example illustrates how to code a block comment. This type of comment is typically coded at the beginning of a group of statements and is used to document the entire group. Block comments can also be used within a state- ment to describe blocks of code, but that’s not common. ‘The fourth example in this figure includes a single-Line comment, This type of comment is typically used to document a single statement or line of code. A single-line comment can be coded on a separate line as shown in this example, or it can be coded at the end of a line of code. In either case, the comment is delimited by the end of the line. Although many programmers sprinkle their code with comments, that shouldn't be necessary if you write your code so it’s easy to read and understand. Instead, you should use comments only to clarify sections of code that are diffi- cult to understand, Then, if you change the code, you should be sure to change the comments too, Otherwise, the comments won't accurately represent what the code does, which will make the code even more difficult to understand.Chapter 1 An introduction to relational databases 88 A SELECT statement that’s difficult to read select invoice number, invoice date, invoice total, payment total, credit total, invoice total - payment_total - credit total as balance due from invoices where invoice total ~ payment total - credit total > 0 order by invoice date A SELECT statement that’s coded with a readable style SELECT invoice number, invoice date, invoic payment total, credit total, invoice total - payment total - credit total AS balance due FROM invoices WHERE invoice total - payment total - credit total > 0 ORDER BY invoice date y_total, A SELECT statement with a block comment ” Author: Joel murach Date: 8/22/2014 ” SELECT invoice number, invoice date, invoice total, invoice total - payment total - credit total AS balance due FROM invoices A SELECT statement with a single-line comment -- The fourth column calculates the balance due SELECT invoice number, invoice date, invoice total, invoice total - payment total - credit total AS balance due FROM invoices Coding recommendations ‘© Capitalize all keywords, and use lowercase for the other code in a SQL statement. ‘© Separate the words in names with underscores, as in invoice_number. ‘© Start each clause on a new line. ‘* Break long clauses into multiple lines and indent continued lines. ‘+ Use comments only for portions of code that are difficult to understand. Then, make sure that the comments are correct and up-to-date. How to code a comment © Tocode a block comment, type /* at the start of the block and */ at the end. © Tocode a single-line comment, type ~ followed by the comment. Description ‘© Line breaks, white space, indentation, and capitalization have no effect on the operation of a statement. ‘* Comments can be used to document what a statement does or what specific parts of the code do. They are not executed by the system. Figure 1-15 SQL coding guidelines34 Section | An introduction to MySQL How to use SQL from an application program This book teaches you how to use SQL from within the MySQL environ- ment. As you learned in the last chapter, though, SQL is commonly used from, application programs too. So in the topies that follow, you'll get a general idea of how that works. As you'll see, there’s a lot involved in accessing a MySQL database from an application program, That's why most application programmers use a framework that makes it easier to execute SQL statements against a database. In some cases, application programmers create their own framework by writing utility classes and data access classes. In other cases, application programmers use an existing framework that provides the classes they need. Common options for accessing MySQL data Figure 1-16 shows three ways to access a MySQL database when you use a programming language to write a custom application. The technique that’s, used varies depending on the language that's used to develop the application, However, most modern languages provide an API that allows you to connect to a MySQL database. An APL uses a piece of software known as a database driver to communicate with the database. For some languages, the database driver is built in. For others, ‘you need to download and install a database driver. To access a MySQL database from a PHP application, for example, you typically choose from two APIs. Some programmers prefer to use the mysqli (MySQL Improved Extension) API. Other programmers prefer to use the newer PDO (PHP Data Objects) API. Neither of these APIs requires a database driver, since that driver is typically included as part of the PHP language. On the other hand, to access a MySQL database from a Java application, you typically use the JDBC (Java Database Connectivity) API. This API requires a driver to communicate with MySQL. In most cases, you can use the Connector/I diver that’s available from the MySQL web site to connect a Java application to a MySQL database. Although it’s more common to use MySQL with non-Microsoft languages such as PHP and Java, it’s possible to use MySQL with Microsoft .NET languages such as C# and Visual Basic.NET. However, the NET platform doesn’t include a database driver by default, so you typically need to download and install the Connector/Net driver that’s available from the MySQL web site. Then, you can use the ADO.NET API to access a MySQL database.Chapter 1 An introduction to relational databases 35 Common options for accessing MySQL data application Et C# application or Decale = Two commonly used MySQL drivers Cr Peon Connector/s ‘Connects Java applications to a MySQL databas Connector/Net Connects NET applications to a MySQL database. Description + Towork with a MySQL database, an application uses a data access API. For example, PHP uses the mysqli API or the PDO API, Java uses the JDBC API, and NET languages like C# and Visual Basic. NET use the ADO.NET API. ‘+ Most modern programming languages provide an API that you can use to access MySQL. ‘© Some programming languages include a piece of software known as a database driver for the API that it uses to access MySQL. For example, PHP includes a ‘MySQL driver for both the mysqli and PDO APIs. As a result, you typically don’t need to install a database driver when you use PHP. ‘© Some programming languages don’t provide a database driver to communicate with a MySQL database. For example, Java doesn’t include a MySQL driver for the JDBC APL. As a result, you typically need to install a database driver such as the Connector/J driver before you can use Java to access MySQL. Figure 1-16 — Common options for accessing MySQL data36 Section | An introduction to MySQL PHP code that retrieves data from MySQL Figure 1-17 presents PHP code that uses the PDO API to execute a SQL statement against a MySQL database. This code displays information from the Vendors and Invoices tables. It creates the PDO objects used by the application and then uses them to display the data that’s retrieved. If you have some PHP programming experience, you shouldn't have much trouble understanding this code. If you don’t have PHP experience, that’s fine too. In that case, focus on how this code uses the PDO API to execute SQL against a MySQL database. If you want to learn more about using PHP to work with a database, we recommend Murach’s PHP and MySQL. ‘The code in this figure begins by defining a PHP script. Within this script, the first statement stores a SELECT statement in a variable named Squery. Then, the next three statements create variables that store the information that’s needed to connect to a MySQL database named AP that’s running on the same computer as the PHP application. That includes variables that specify a username of “ap_tester” and a password of “sesame”. Here, the same script that created the database also created the ap_tester. This user has limited privileges. In particular, it can only access the AP database, not other databases. In addition, it can only work with the data in the database, not modify the structure of the database. As a result, when writing code, it's more secure to connect to the database as the ap_tester than to connect as a global user such as the root user that has all privileges on all databases. After specifying the connection information, this code uses these variables to create a PDO object that represents a connection to the database. If this code isn’t able to create a PDO object, an error known as a PDOException occurs, and the application displays an error message and ends. Otherwise, this code uses the PDO object to execute the SELECT statement, and it stores the result set in a variable named Srows. At this point, the HTML tags begin displaying an HTML page. Within the tag, a PHP script loops through each row in the result set and displays that data on the HTML page. In particular, it displays the vendor_name, invoice_number, and invoice_total columns. Here, the PHP function named number_format is used to apply formatting to the invoice_total column, Although this code may seem complicated, there’s only one statement in this figure that uses SQL. That's the statement that specifies the SELECT statement to be executed. OF course, if an application updates data, it can execute INSERT, UPDATE, and DELETE statements as well, With the skills that you'll earn in this book, though, you won't have any trouble coding the SQL statements you need.Chapter 1 An introduction to relational databases 37 PHP code that retrieves data from MySQL getMessage(); sho Serror_message; exit 7 > $rows = §ab->query ($query); a