Working with Databases and MySQL

2PHP Programming with MySQL, 2nd Edition Objectives In this chapter, you will: • Study the basics of databases and MySQL • Work with MySQL databases • Define database tables • Modify user privileges • Work with database records • Work with phpMyAdmin

3PHP Programming with MySQL, 2nd Edition Introduction to Databases A database is an ordered collection of information Each row is called a record. A record in a database is a single complete set of related information Each column is called a field. Fields are the individual categories of information stored in a record

4PHP Programming with MySQL, 2nd Edition Introduction to Databases (continued) Figure 7-1 Employee directory database

5PHP Programming with MySQL, 2nd Edition Understanding Relational Databases Relational databases consist of one or more related tables A primary table is the main table in a relationship that is referenced by another table A related table (or “child table”) references a primary table in a relational database A primary key is a field that contains a unique identifier for each record in a primary table

6PHP Programming with MySQL, 2nd Edition Understanding Relational Databases (continued) A primary key is a type of index, which makes retrievals and sorting faster A foreign key is a field in a related table that refers to the primary key in a primary table Primary and foreign keys link records across multiple tables in a relational database

7PHP Programming with MySQL, 2nd Edition One-to-One Relationships A one-to-one relationship exists between two tables when a related table contains exactly one record for each record in the primary table Create one-to-one relationships to break information into multiple, logical sets Information in the tables in a one-to-one relationship can be placed within a single table

8PHP Programming with MySQL, 2nd Edition One-to-One Relationships (continued) Figure 7-2 One-to-one relationship

9PHP Programming with MySQL, 2nd Edition One-to-Many Relationship A one-to-many relationship exists in a relational database when one record in a primary table has many related records in a related table Breaking tables into multiple related tables to reduce redundant and duplicate information is called normalization Provides a more efficient and less redundant method of storing this information in a database

10PHP Programming with MySQL, 2nd Edition One-to-Many Relationship (continued) Figure 7-3 Table with redundant information

11PHP Programming with MySQL, 2nd Edition One-to-Many Relationship (continued) Figure 7-4 One-to-many relationship

12PHP Programming with MySQL, 2nd Edition Many-to-Many Relationship A many-to-many relationship exists in a relational database when many records in one table are related to many records in another table A junction table creates a one-to-many relationship for each of the two tables in a many-to-many relationship A junction table contains foreign keys from the two tables

13PHP Programming with MySQL, 2nd Edition Working with Database Management Systems A database management system (or DBMS) is an application or collection of applications used to access and manage a database A schema is the structure of a database including its tables, fields, and relationships A flat-file database management system is a system that stores data in a flat-file format A relational database management system (or RDBMS) is a system that stores data in a relational format

14PHP Programming with MySQL, 2nd Edition Working with Database Management Systems (continued) Figure 7-5 Many-to-many relationship

15PHP Programming with MySQL, 2nd Edition Working with Database Management Systems (continued) Important aspects of database management systems: The structuring and preservation of the database file Ensuring that data is stored correctly in a database’s tables, regardless of the database format Querying capability

16PHP Programming with MySQL, 2nd Edition Working with Database Management Systems (continued) A query is a structured set of instructions and criteria for retrieving, adding, modifying, and deleting database information Structured query language (or SQL) is a standard data manipulation language used among many database management systems Open database connectivity (or ODBC) allows ODBC- compliant applications to access any data source for which there is an ODBC driver

17PHP Programming with MySQL, 2nd Edition Understanding MySQL Identifiers An alias is an alternate name used to refer to a table or field in SQL statements MySQL stores each database in a directory of the same name as the database identifier Field and index identifiers are case insensitive on all platforms

18PHP Programming with MySQL, 2nd Edition Understanding MySQL Identifiers (continued) Identifiers that must be quoted using the backtick, or single quote, character (`)are An identifier that includes any character except standard alphanumeric characters, underscores (_) or dollar signs ($) Any identifier that contains one or more space characters An identifier that is a reserved word in MySQL An identifier made entirely of numeric digits An identifier that contains a backtick character

19PHP Programming with MySQL, 2nd Edition Getting Help with MySQL Commands

20PHP Programming with MySQL, 2nd Edition Creating Databases Use the CREATE DATABASE statement to create a new database: mysql> CREATE DATABASE vehicle_fleet;[ENTER] To use a new database, select it by executing the USE DATABASE statement

21PHP Programming with MySQL, 2nd Edition Selecting a Database Use the DATABASE() function to return the name of the currently active database mysql> SELECT DATABASE();[ENTER] View the available databases using the SHOW DATABASES statement mysql> SHOW databases;[ENTER] Use the DROP DATABASE statement to remove all tables and delete a database mysql> DROP DATABASE database;

22PHP Programming with MySQL, 2nd Edition Defining Database Tables Data types that are assigned to fields determine how much storage space the computer allocates for the data in the database Choose the smallest data type possible for each field

23PHP Programming with MySQL, 2nd Edition Defining Database Tables (continued)

Creating Tables Use the CREATE TABLE statement to create a new table and define the column names and data types for each column mysql> CREATE TABLE vehicles (license VARCHAR(10), make VARCHAR(25), model VARCHAR(50), miles FLOAT, assigned_to VARCHAR(40));[ENTER] 24PHP Programming with MySQL, 2nd Edition

Viewing Table Structure Use the DESCRIBE table_name statement to view the structure of the table 25PHP Programming with MySQL, 2nd Edition

Changing Table Field Names Use the ALTER TABLE to change the name of an existing field in a table using the following syntax ALTER TABLE table_name ADD [COLUMN] (column_name column_type [, column_name column_type ...]); In MySQL Monitor, enter the following: mysql> ALTER TABLE vehicles ADD COLUMN (model_year INT);[ENTER] 26PHP Programming with MySQL, 2nd Edition

Modifying Column Types Use the ALTER TABLE to rename columns of an existing field in a table using the following syntax ALTER TABLE table_name CHANGE [COLUMN] column_name new_name column_type; In MySQL Monitor, enter the following: mysql> ALTER TABLE vehicles CHANGE COLUMN miles mileage FLOAT;[ENTER] 27PHP Programming with MySQL, 2nd Edition

Renaming Columns Use the ALTER TABLE to rename columns using the following syntax ALTER TABLE table_name MODIFY [COLUMN] column_name column_type; In MySQL Monitor, enter the following: mysql> ALTER TABLE vehicles MODIFY COLUMN model_year SMALLINT;[ENTER] 28PHP Programming with MySQL, 2nd Edition

Renaming Tables Use the ALTER TABLE to change the name of an existing table using the following syntax ALTER TABLE table_name RENAME [TO] new_name; mysql> ALTER TABLE vehicles RENAME TO company_cars;[ENTER] 29PHP Programming with MySQL, 2nd Edition

Removing Columns Use the ALTER TABLE to remove an existing field from a table using the following syntax ALTER TABLE table_name DROP [COLUMN] column_name; mysql> ALTER TABLE company_cars DROP COLUMN assigned_to;[ENTER] 30PHP Programming with MySQL, 2nd Edition

Deleting Tables Execute the DROP TABLE statement to remove all data and the table definition from a database DROP TABLE table; In MySQL Monitor, enter the following at the mysql> prompt: mysql> DROP TABLE company_cars;[ENTER] You must be logged in as the root user or have DROP privileges to delete a table. 31PHP Programming with MySQL, 2nd Edition

Modifying User Privileges Privileges are actions and operations a user can perform with a table or a database For security purposes, user accounts should only be assigned the minimum necessary privileges to perform given tasks 32PHP Programming with MySQL, 2nd Edition

Modifying User Privileges (continued) 33PHP Programming with MySQL, 2nd Edition

34PHP Programming with MySQL, 2nd Edition Granting Privileges The syntax for the GRANT statement is: GRANT privilege [(column)] [, privilege [(columns)]] ... ON {table | * | *.* | database.*} TO user [IDENTIFIED BY 'password']; The GRANT statement creates the user account if it does not exist and assigns the specified privileges If the user account already exists, the GRANT statement just updates the privileges

35PHP Programming with MySQL, 2nd Edition Revoking Privileges The syntax for the REVOKE statement is: REVOKE privilege [(column)] [, privilege [(columns)]] ... ON {table | * | *.* | database.*} FROM user; The REVOKE ALL PRIVILEGES statement removes all privileges from a user account for a specified table or database You must be logged in with the root account or have sufficient privileges to revoke privileges from another user account

36PHP Programming with MySQL, 2nd Edition Adding Records Use the INSERT statement to add individual records to a table The syntax for the INSERT statement is: INSERT INTO table_name (column1, column2, …) VALUES(value1, value2, ...); The values entered in the VALUES list must be in the same order in which you defined the table fields Specify NULL in any fields for which you do not have a value

37PHP Programming with MySQL, 2nd Edition Adding Records (continued) mysql> INSERT INTO company_cars(license, model_year, make, model, mileage) VALUES('CK-2987', 2009, 'Toyota', 'Corolla', 3508.4);[ENTER]

Adding Records (continued) The LOAD DATA statement, with the full path and name of a local text file, is used to add multiple records to a table LOAD DATA INFILE 'file_path' INTO TABLE table_name (column1, column2, …); Each record in the text file must be placed on a separate line with a tab delimiter between each field 38PHP Programming with MySQL, 2nd Edition

Adding Records (continued) If the column list is omitted, the values on each line must be in the same order you defined the table fields Use consecutive tabs with nothing between them to designate a column with no value mysql> LOAD DATA INFILE 'company_cars.txt' INTO TABLE company_cars;[ENTER] 39PHP Programming with MySQL, 2nd Edition

Adding Records (continued) The optional FIELDS TERMINATED BY clause of the LOAD DATA statement allows you to change the field separator to a character such as (~ or ,) instead of the default tab character mysql> LOAD DATA INFILE 'company_cars.txt‘ INTO TABLE company_cars;[ENTER] 40PHP Programming with MySQL, 2nd Edition

41PHP Programming with MySQL, 2nd Edition Retrieving Records Use the SELECT statement to retrieve records from a table: SELECT criteria FROM table_name; Use the asterisk (*) wildcard with the SELECT statement to retrieve all fields from a table To return multiple fields, separate field names with a comma

42PHP Programming with MySQL, 2nd Edition Retrieving Records (continued) mysql> SELECT model, mileage FROM company_cars;[ENTER]

Using Aggregate Functions Aggregate functions summarize data in record sets rather than display the individual records The COUNT() function is unique in that The wildcard (*) can be used as a function argument instead of a field name The keyword DISTINCT can be used after the opening parentheses The DISTINCT keyword can also be used with the SELECT statement to retrieve records with a unique value in the WHERE clause 43PHP Programming with MySQL, 2nd Edition

Using Aggregate Functions (continued) To retrieve aggregate values for groups of records, use the GROUP BY clause and include the fields that you use to group the records as part of the query mysql> SELECT model_year, AVG(mileage) FROM company_cars GROUP BY model_year;[ENTER] 44PHP Programming with MySQL, 2nd Edition

45PHP Programming with MySQL, 2nd Edition Sorting Query Results Use the ORDER BY keyword with the SELECT statement to perform an alphanumeric sort of the results returned from a query mysql> SELECT make, model FROM inventory ORDER BY make, model;[ENTER]

46PHP Programming with MySQL, 2nd Edition Sorting Query Results (continued) To perform a reverse sort, add the DESC keyword after the name of the field by which you want to perform the sort mysql> SELECT make, model FROM company_cars ORDER BY make DESC, model;[ENTER]

47PHP Programming with MySQL, 2nd Edition Filtering Query Results The criteria portion of the SELECT statement determines which fields to retrieve from a table You can also specify which records to return by using the WHERE keyword mysql> SELECT * FROM inventory WHERE make='Martin‘;[ENTER]

48PHP Programming with MySQL, 2nd Edition Filtering Query Results (continued) Use the keywords AND and OR to specify more detailed conditions about the records you want to return mysql> SELECT * FROM company_cars WHERE model_year=2007 AND mileage<60000;[ENTER]

49PHP Programming with MySQL, 2nd Edition Filtering Query Results (continued) mysql> SELECT * FROM company_cars WHERE make='Toyota’ OR make='Honda‘ ORDER BY mileage ;[ENTER]

50PHP Programming with MySQL, 2nd Edition Updating Records To update records in a table, use the UPDATE statement The syntax for the UPDATE statement is: UPDATE table_name SET column_name=value WHERE condition; The UPDATE keyword specifies the name of the table to update The SET keyword specifies the value to assign to the fields in the records that match the condition in the WHERE keyword

51PHP Programming with MySQL, 2nd Edition Updating Records (continued) mysql> UPDATE company_cars SET mileage=368.2 WHERE make='Ford’ AND model='Fusion'; [ENTER]

52PHP Programming with MySQL, 2nd Edition Deleting Records Use the DELETE statement to delete records in a table The syntax for the DELETE statement is: DELETE FROM table_name WHERE condition; The DELETE statement deletes all records that match the condition To delete all the records in a table, leave off the WHERE keyword

53PHP Programming with MySQL, 2nd Edition Deleting Records (continued) mysql> DELETE FROM company_cars WHERE model_year=2006 AND make='Honda' AND model='Accord';[ENTER] To delete all records from a table, omit the WHERE clause

Working with Databases and MySQL

More Related Content

What's hot

Similar to Working with Databases and MySQL

More from Nicole Ryan

Recently uploaded

Working with Databases and MySQL