|
One obviously cannot write a meaningful JDBC or Mysql++ database program without reasonable familiarity with SQL. This section explains the syntax of some of the commonly used SQL commands. It is obviously not possible to list here all of the different ways in which a client can interact with a database server using SQL, or even all of the commands that are supported by MySQL. However, we will give a sufficient introduction to SQL so that the reader can start writing useful JDBC and Mysql++ programs.
In what follows, we will first describe some basic SQL commands and list some of the more commonly used data types in SQL. This will be followed by two interactive terminal sessions for demonstrating how one actually uses SQL. The interactive sessions will introduce additional commands and functions of SQL.
To get started with SQL, we will now explain the syntax of the following very commonly used SQL commands:
CREATE TABLE : creates a new table with given column headings INSERT : inserts a new row in a table SELECT : retrieves entries from single or multiple tables with or without constraints on the entries UPDATE : changes table entries
Although, in keeping with the convention, we have shown the SQL commands in upper case, their usage is case insensitive. Let's look at a typical CREATE TABLE statement:
CREATE TABLE BookTable ( Title CHAR(20), Author CHAR(20), ISBN CHAR(13) PRIMARY KEY NOT NULL, PublisherID INT, PublisherRepID INT, Year INT, NumCopies INT )
This will create a database table with seven attributes, each corresponding to a separate column in the table. It also declares the data type of each attribute. The first three are of type CHAR(n), where n is the maximum number of characters that the column will hold. The last four are of type INT. With regards to the data types allowed, the SQL 92 specification recognizes the following general types:
exact numerics
approximate numerics
character strings
bit strings
datetimes
intervals
Within each of these general types, there are subtypes:
exact numerics: INTEGER (or, INT), SMALLINT, NUMERIC, DECIMAL approximate numerics: REAL, DOUBLE PRECISION, FLOAT character strings: CHARACTER or CHAR, CHARACTER(n) or CHAR(n), CHARACTER VARYING(n)or VARCHAR(n), NATIONAL CHARACTER(n) bit strings: BIT, BIT(n), BIT VARYING(n) datetimes: DATE, TIME, TIMESTAMP, TIME WITH TIME ZONE, TIMES TAMP WITH TIME ZONE intervals: INTERVAL DAY (is the difference between two datetime values)
SQL does not specify the precision associated with the different data types—that's left to the implementations. However, SQL does place some constraints on the relative precision of the related types. For example, the precision of SMALLINT can be no larger than that of INT. However, commonly a SMALLINT is represented by two bytes, and an INT is represented by four. A NUMERIC type has a fractional component, in addition to its integer component. For this type, precision refers to the maximum number of digits to be used for both parts together and scale refers to the number of digits to be used for just the integer part. For example, if you know that all your numbers in a column are going to less than 999.99, you could specify the data type for that column as NUMERIC (5,2). If you don't specify the precision and scale, the system will use a default of 12 for precision and 6 for scale. The DECIMAL type is closely related to the NUMERIC type. While a NUMERIC type can be used to place a constraint on the largest value in a column, there is no such enforcement for a DECIMAL type. For a column type specified as DECIMAL (5,2), a value such as 99999.999 will be acceptable.
The difference between CHAR (n)and VARCHAR (n)is that for the former the system will pad a string with blanks if the number of characters in the data string is fewer than n. No padding is used for the latter.
Getting back to the CREATE TABLE statement shown earlier, note that we declared the ISBN column to be PRIMARY KEY NOT NULL. A KEY is an attribute (meaning, a column heading) that can be used to uniquely identify a row in a table. For a system to access a row, it must have some unique way to identify that row. An attribute declared as a KEY is supposed to serve that function. If you know that no two rows in a table will have the same value for a given attribute, use that attribute as the PRIMARY KEY for the table. Characterization of a key as PRIMARYis supposed to distinguish it from its declaration as a FOREIGN KEY. When an attribute is declared to be a foreign key in a table, it informs the table that the key in question is a primary key in another named table.
So, by definition, an attribute declared to be a PRIMARY KEYwill take unique values in the different rows of a table. Moreover, this attribute will never be allowed to be NULL. In that sense, the declaration PRIMARY KEY NOT NULLis redundant. Another way to declaring an attribute to be a primary key is by using the UNIQUE label, as in
CREATE TABLE BookTable( Title CHAR(20), Author CHAR(20), ISBN CHAR(13) PRIMARY KEY NOT NULL, PublisherID INT, PublisherRepID INT, Year INT, NumCopies INT, UNIQUE( ISBN ) )
Sometimes it is not possible to identify an attribute that will take unique values in a table. In these cases, multiple rows can be declared to be NOT NULL, and the system will use a composite key based on all those rows. Of course, you have to be sure that the entries for those attributes will never be null.
After you have declared a new table with the CREATE TABLE command, you can start inserting information into it with the SQL command INSERT, as demonstrated by
INSERT INTO BookTable VALUES ( 'Noxious Fumes', 'Sul Phuric', '3636-7737-10', 3, 101, 1935, 3 )
Each such invocation will enter a new row into the table BookTable. After a table is thus brought into existence, you can examine all its contents by the SELECT command, as in
SELECT * FROM BookTable
where * will cause all the columns of the table BookTable to be shown for every row. If you only wanted to see the table entries corresponding to the books published in year 2002, you'd say
SELECT * FROM BookTable WHERE Year = 2002
The following retrieval command shows how the information contained in two different tables of a database can be merged:
SELECT BookTable.Title, BookTable.Author, PublisherTable.PublisherName FROM BookTable, PublisherTable WHERE BookTable.PublisherID = PublisherTable.PublisherID
In the interactive sessions that follow, also note the use of the following SQL commands:
UPDATE ALTER TABLE DROP TABLE
for doing exactly what the command names imply.
We will now show an interactive session with a MySQL database that demonstrates how SQL can be used to create, query, and modify a database. (This is also a good way to practice SQL.) To start the interactive session, you'd need to invoke the MySQL terminal monitor that allows you to execute command-line SQL. If the database server is running on the same machine on which you will be executing command-line SQL, you may be able to start up the terminal monitor by simply entering in a terminal window the following[5]
mysql
If your request for a connection succeeds, you will see a welcome message on your terminal followed by prompt
mysql>
Now you are ready to type in your SQL statements.
In what follows, we will show the MySQL prompt mysql> flush with the left margin. A user's entries will follow such prompts. The information returned by the database will be shown with an offset from the left margin. All words that are all in uppercase are either SQL commands or SQL keywords. This is just a convention that makes it easy for a human to parse the SQL syntax, but, as mentioned before, SQL itself is case insensitive. Bear in mind that a requirement of MySQL is that each SQL statement be terminated in either a ‘ or ‘\g’. The interactive session that follows will create a rudimentary relational database for the library example of Section 20.1.
One of the first things you'd need to do after firing up the terminal monitor is to tell MySQL which database you'd be using. We will assume that you'll use the "test" database that is supplied with the MySQL installation for testing purposes. To tell the database server that you'd be using the "test" database, you'd need to enter
mysql> USE test;
Now enter the following SQL statement:
mysql> CREATE TABLE BookTable ( Title CHAR(20), Author CHAR(20), ISBN CHAR(13) PRIMARY KEY NOT NULL, PublisherID INT, PublisherRepID INT, Year INT, NumCopies INT );
MySQL allows a single SQL statement to be in multiple lines, but the portion of the statement in the last line must terminate in either a ‘;’ or ‘/g’. If you want to review the structure of the table that was just created, you could now enter
mysql> DESCRIBE BookTable;
MySQL will come back with
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
Title | char(20) | YES | NULL | ||
Author | char(20) | YES | NULL | ||
ISBN | char(13) | PRI | |||
PublisherID | int(11) | YES | NULL | ||
PublisherRepID | int(11) | YES | NULL | ||
Year | int(11) | YES | NULL | ||
NumCopies | int(11) | YES | NULL |
SQL statements such as the following would now insert rows of information into this table:
mysql> INSERT INTO BookTable VALUES( 'Noxious Fumes', 'Sul Phuric', '3636-7737-10', 3, 101, 1935, 3 ); mysql> INSERT INTO BookTable VALUES( 'Nasty Nabobs', 'Dem Ented', '3636-9983-10', 2, 101, 1943, 2 ); mysql> .....
As you are entering new rows into the table, at any time you can see the contents of the table by typing
mysql> SELECT * from BookTable;
Assuming that you entered the previously shown two INSERT statements and three more (now shown), MySQL will respond to the SELECT command by returning the following for the contents of the BookTable:
Title | Author | ISBN | PID | PRID | Year | NumCopies |
---|---|---|---|---|---|---|
Noxious Fumes | Sul Phuric | 3636-7737-10 | 3 | 101 | 1935 | 3 |
Nasty Nabobs | Dem Ented | 3636-9983-10 | 2 | 101 | 1943 | 2 |
Nosy Nostrils | Lus Hairs | 3687-9983-10 | 3 | 103 | 1998 | 1 |
Attila the Hun | Wus Nofun | 3687-9944-98 | 2 | 101 | 1989 | 10 |
Hairy Hoods | LoLifes | 4303-9944-98 | 3 | 101 | 1978 | 2 |
Dancing Dollies | Boggy Boogers | 4303-0000-98 | 1 | 121 | 1986 | 1 |
where we have used the abbreviation PID for "PublisherID" and PRID for "PublisherRepID".
To expand on the interactive session in progress, let's now create a new table, PublisherTable, to hold information about the different publishers.
mysql> CREATE TABLE PublisherTable ( PublisherID INT, PublisherName CHAR(20), PublisherLocation CHAR(20), PublisherURL CHAR(20) );
If you want to see all the tables currently in the database, you can do so by entering
SHOW TABLES;
The server will come back with
Tables_in_test |
---|
BookTable |
To insert an item into the new table, PublisherTable, we will use the INSERT command as before:
mysql> INSERT INTO PublisherTable VALUES ( 1, 'Warring Warriors', 'New York', 'www.warriors.com' );
If we use a couple of more INSERT commands for this table, in accordance with the second and the third rows shown below, the command
Select * from PublisherTable
will then yield
PublisherID | PublisherName | PublisherLocation | PublisherURL |
---|---|---|---|
1 | Warring Warriors | New York | http://www.warriors.com |
2 | WordSmiths | New Delhi | http://www.wordsmiths.com |
3 | WordMasters | New Brunswick | http://www.wordmasters.com |
We need one more table for our example, which we create with the command
mysql> CREATE TABLE PublisherRepTable ( PublisherRepID INT, RepName CHAR(30), RepPhone CHAR(12), RepEmail CHAR(20) );
Inserting items into this table as shown by the rows below, we get the following for its contents:
PublisherRepID | RepName | RepPhone | RepEmail |
---|---|---|---|
101 | Rip Offer | 2347626266 | rip@wol.com |
103 | Hory Details | 3527622776 | hory@wol.com |
121 | Nuts Galore | 1117232776 | nuts@wol.com |
With our three-table relational database in place, we are now all set to demonstrate complex queries to the database that require accessing more than one table. For example, the query
mysql> SELECT BookTable.Title, BookTable.Author, PublisherTable.PublisherName FROM BookTable, PublisherTable WHERE BookTable.PublisherID = PublisherTable.PublisherID;
returns
Title | Author | PublisherName |
---|---|---|
Dancing Dollies | Boggy Boogers | Warring Warriors |
Nasty Nabobs | Dem Ented | WordSmiths |
Attila The Hun | Wus Nofun | WordSmiths |
Noxious Fumes | Sul Phuric | WordMasters |
Nosy Nostrils | Lus Hairs | WordMasters |
Hairy Hoods | Lo Lifes | WordMasters |
We will next illustrate how you can use the UPDATE command to alter a table at any time. Often, any updates to a table would be conditional, meaning that a row would be updated only if it satisfied certain requirements. For example,
mysql> UPDATE BookTable SET Author = 'Long Hairs' WHERE Title = 'Nosy Nostrils';
Now if we enter again
mysql> SELECT * FROM BookTable;
we get
Title | Author | ISBN | PID | PRID | Year | NumCopies |
---|---|---|---|---|---|---|
Noxious Fumes | Sul Phuric | 3636-7737-10 | 3 | 101 | 1935 | 3 |
Nasty Nabobs | Dem Ented | 3636-9983-10 | 2 | 101 | 1943 | 2 |
Nosy Nostrils | Long Hairs | 3687-9983-10 | 3 | 103 | 1998 | 1 |
Attila the Hun | Wus Nofun | 3687-9944-98 | 2 | 101 | 1989 | 10 |
Hairy Hoods | Lo Lifes | 4303-9944-98 | 3 | 101 | 1978 | 2 |
Dancing Dollies | Boggy Boogers | 4303-0000-98 | 1 | 121 | 1986 | 1 |
where we have again used the abbreviation PID for "PublisherID" and PRID for "PublisherRepID". Here is another updating of BookTable that shows how rows can be updated selectively:
mysql> UPDATE BookTable SET NumCopies = NumCopies + 1 WHERE Year > 1980;
Displaying BookTable now yields:
Title | Author | ISBN | PID | PRID | Year | NumCopies |
---|---|---|---|---|---|---|
Noxious Fumes | Sul Phuric | 3636-7737-10 | 3 | 101 | 1935 | 3 |
Nasty Nabobs | Dem Ented | 3636-9983-10 | 2 | 101 | 1943 | 2 |
Nosy Nostrils | Lus Hairs | 3687-9983-10 | 3 | 103 | 1998 | 2 |
Attila the Hun | Wus Nofun | 3687-9944-98 | 2 | 101 | 1989 | 11 |
Hairy Hoods | Lo Lifes | 4303-9944-98 | 3 | 101 | 1978 | 2 |
Dancing Dollies | Boggy Boogers | 4303-0000-98 | 1 | 121 | 1986 | 2 |
where, once again, we have used the abbreviation PID for "PublisherID" and PRID for "PublisherRepID". To close the session, we say
mysql> QUIT
Our previous session showed the basic workings of SQL. We will now show SQL that demonstrates
Entering multiple rows simultaneously into a table using a feature provided by MySQL.
Using the command SELECT with the ORDER BY option to display an ordered version of a database table.
Invoking the SQL set functions to calculate the various statistics of the numerical values in the individual columns of a database table.
Modifying a table structurally with the ALTER command after it is created and populated with data.
We will use the database "test" for this session also. Since we have no further need for the tables we created in the previous session, we can drop them by
mysql> DROP TABLE BookTable; mysql> DROP TABLE PublisherTable; mysql> DROP TABLE PublisherRepTable;
That should basically empty out the database "test". For the purpose of this interactive session, let's now create a new table TestTable1 by
mysql> CREATE TABLE TestTablel (Id INT PRIMARY KEY NOT NULL, price DOUBLE(10,2) DEFAULT '0.00' NOT NULL );
If we now enter
mysql> DESCRIBE TestTable1;
to review the structure of the table, we get back
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
id | int(11) | PRI | 0 | ||
price | double(10,2) | 0.00 |
So TestTable1 is a table with two columns, id and price, the former of type INT and the latter of type DOUBLE. To insert a row of information into the table, we can say
mysql> INSERT INTO TestTable1 VALUES ( 127, 4.98 );
Let's now say that we have a large number of additional entries to make like those shown above. MySQL gives us a way to enter them all at one time in a single SQL statement. In order to demonstrate how that can be done, let's first delete the row just entered:
mysql> DELETE FROM TestTable1 WHERE id = 127;
If this table contained multiple rows and we wanted to delete them all with one command, we could just say
mysql> DELETE FROM TestTable1;
In either case, TestTable1 is now empty. The following command will now enter multiple rows simultaneously into the table:
mysql> INSERT INTO TestTable1 VALUES (127, 4.98), (222, 22.22), (111, 11.11);
The command
mysql> SELECT * FROM TestTable1;
will now return
id | price |
---|---|
127 | 4.98 |
222 | 22.22 |
111 | 11.11 |
If we want the displayed output to be ordered by price, we can say
mysql> SELECT * FROM TestTable1 ORDER BY price;
Now the displayed output would be
id | price |
---|---|
127 | 4.98 |
111 | 11.11 |
222 | 22.22 |
Many useful operations on databases consist of determining the various statistics for the entries in the individual columns of a table. For the example at hand, we may wish to find out the average price charged for all the items in the database, the max price, the min price, and so on. Shown below is a SELECT command that computes such values from the price column of the above table and that also returns the number of non-null items in the id column:
mysql> SELECT MAX( price ), MIN( price ), AVG( price ), SUM( price ), COUNT( Id ) FROM TestTable1;
This returns
MAX(price) | MIN(price) | AVG(price) | SUM(price) | COUNT( Id ) |
---|---|---|---|---|
22.22 | 4.98 | 12.770000 | 38.31 | 3 |
The functions such as MAX, MIN, and so on, are known as the set functions.
It is possible to modify the structure of a table after it has been created and populated with data. Let's say we wish to add a third column to TestTable1 to keep track of the quantity of each item in the database. We could use the ALTER command for this, as in
mysql> ALTER TABLE TestTable1 ADD quantity INT;
Now if we say
mysql> DESCRIBE TestTable1;
we will get back
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
id | int(11) | PRI | 0 | ||
price | double(10,2) | 0.00 | |||
quantity | int(11) | YES | NULL |
Examining the contents of the table after this structural modification
mysql>SELECT * FROM TestTable1 ORDER BY price;
we get
id | price | quantity |
---|---|---|
127 | 4.98 | NULL |
111 | 11.11 | NULL |
222 | 22.22 | NULL |
An updating of this table with
mysql> UPDATE TestTable1 SET quantity = 2 WHERE price > 10.00;
yields the following for TestTable1 in the database
id | price | quantity |
---|---|---|
127 | 4.98 | NULL |
111 | 11.11 | 2 |
222 | 22.22 | 2 |
[5]If this does not work, you may have to supply a user name and a password. In general, if the database server is running on a remote machine for which you have access privileges, you can start up the terminal monitor on your local machine by
mysql -h hostName -u userName -p
where hostName is the name of the remote machine and userName the name you can use to enter that machine. This more general command will prompt you for a password.
|