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.
Some Basic SQL Commands
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: 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 intoexistence, 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.
Some Basic SQL Commands
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
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 intoexistence, 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.
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.
Some Basic SQL Commands
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: 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 intoexistence, 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.
Some Basic SQL Commands
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
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 intoexistence, 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.
