Guide to Object-oriented Programming With Java (Computer Science 4 All 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.

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

Figure 14-1: MySQL CREATE TABLE Code

The MySQL describe table Statement

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

JavaMyAdmin Sql Entry Prompt
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

Figure 14-2: MySQL DESCRIBE Code

The MySQL insert Statement

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

JavaMyAdmin Sql Entry Prompt
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");

Figure 14-3: MySQL INSERT Code

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.

JavaMyAdmin Sql Entry Prompt
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

Figure 14-4: MySQL SELECT Code

The MySQL where Statement

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

JavaMyAdmin Sql Entry Prompt
SELECT * FROM booksTable WHERE authorLname = "Dylan";
bookID bookTitle authorFname authorLname bookISBN
1 Tarantula Bob Dylan 9714-0743230414

Figure 14-5: MySQL SELECT ... WHERE Code

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.

JavaMyAdmin Sql Entry Prompt
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

Figure 14-6: MySQL join Code

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.

JavaMyAdmin Sql Entry Prompt
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

Figure 14-7: MySQL Column Aliases Code

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.

JavaMyAdmin Sql Entry Prompt
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

Figure 14-13: MySQL Table Alias (as) Code
Creative Commons License This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License. Copyright © 2016-2020 Jim Gerland