Click this widget to change the font size. Click this widget to change contrast.

Guide to Object-oriented Programming With Java (Buffalo State University Version)

---

Chapter 14: Working With the MySQL Database

MySQL Statements

You can use phpMyAdmin on your localhost web server to manage your MySQL databases. Click on your database. Then click on the SQL tab. You can then enter SQL statements in the editor window.

The MySQL create table Statement

You can use the create table statement to create a new table on your localhost server.

CREATE TABLE booksTable (
bookID int(9) auto_increment primary key,
bookTitle varchar(100),
authorFname varchar(20),
authorLname varchar(20),
bookISBN varchar(14)
);

The MySQL describe table Statement

You can use the describe table statement to view the structure of your table on your localhost server.

DESCRIBE booksTable;

Field	Type	Null	Key	Default	Extra
bookID	int(9)	NO	PRI	NULL	auto_increment
bookTitle	varchar(100)	YES	NULL
authorFname	varchar(20)	YES	NULL
authorLname	varchar(20)	YES	NULL
bookISBN	varchar(14)	YES	NULL

The MySQL insert Statement

You can use the insert statement to add data record (row) to your table on your localhost server.

INSERT INTO booksTable (bookTitle, authorFname, authorLname, bookISBN)
VALUES ("Tarantula", "Bob", "Dylan", "9714-0743230414");
INSERT INTO booksTable (bookTitle, authorFname, authorLname, bookISBN)
VALUES ("19134", "George", "Orwell", "9714-0451524935");
INSERT INTO booksTable (bookTitle, authorFname, authorLname, bookISBN)
VALUES ("The Case-Book of Sherlock Holmes ", "Arthur Conan", "Doyle", "9714-0199555642");

The MySQL select ... where Statement

You can use the select statement to retreive a data record (or records) from your table on your localhost server.

SELECT * FROM booksTable;

bookID	bookTitle	authorFname	authorLname	bookISBN
1	Tarantula	Bob	Dylan	9714-0743230414
2	19134	George	Orwell	9714-0451524935
3	The Case-Book of Sherlock Holmes	Arthur Conan	Doyle	9714-0199555642

The MySQL where Statement

You can use the where keyword on your select statement to addd criteria that limits the retreived records(results set).

SELECT * FROM booksTable WHERE authorLname = "Dylan";

bookID	bookTitle	authorFname	authorLname	bookISBN
1	Tarantula	Bob	Dylan	9714-0743230414

Working With Multiple MySQL Tables

In most appllications you will need to work with more than one table.

The MySQL join Keyword

You can use the join keyword to relate two or more tables together.

SELECT firstName, lastName, DeptName, locationName
FROM employees
JOIN departments ON employees.dept = departments.departmentID
JOIN locations ON departments.location = locations.locationID;

firstName	lastName	DeptName	locationName
Bob	Dylan	HR	New York, NY
Joe	Cocker	HR	Paris, FR
John	Prine	HR	London, UK

MySQL Aliases

MySQL Column Aliases

You can use the as keyword to provide a more user-friendly name for a field (column). This is known AS a column alias. If the alias value has spaces or mixed case characters you need to enclose the value in quotes.

SELECT firstName AS "First Name", lastName AS "Last Name", DeptName AS "Department", locationName AS "Location"
FROM employees
JOIN departments ON employees.dept = departments.departmentID
JOIN locations ON departments.location = locations.locationID;

First Name	Last Name	Deptartment	Location
Bob	Dylan	HR	New York, NY
Joe	Cocker	HR	Paris, FR
John	Prine	HR	London, UK

MySQL Table Aliases

You can use the as keyword to provide a more user-friendly name for a table. This allows you to provide a shorter name when refering to that table.

SELECT firstName AS "First Name", lastName AS "Last Name", DeptName AS "Department", locationName AS "Location"
FROM employees AS e
JOIN departments AS d ON e.dept = d.departmentID
JOIN locations AS l ON d.location = l.locationID;

First Name	Last Name	Deptartment	Location
Bob	Dylan	HR	New York, NY
Joe	Cocker	HR	Paris, FR
John	Prine	HR	London, UK

Help contribute to my OER Resources. via my PayPal account.
This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License. Copyright © 2016-2024 Jim Gerland