Information Technology Reference
In-Depth Information
reports from them. On occasion, the researcher will find these tools too limiting,
and will want to retrieve the data directly from the database. When that situation
arises, the researcher will have to use the SQL language to access the database.
This is the most general and flexible method to retrieve data from a database.
We'll describe at some length the SQL language and explain how RDBMSs store
and retrieve data, so that the user may understand how to access data in that for-
mat. There are many books that describe the SQL language in detail, one good
example being Bowman, Emerson & Darnovsky (2001).
Example
Creating a database in MySQL with SQL
Relational databases consist of one or more tables, which in turn consist of
rows and columns (which are also called records and fields). The columns are
defined to be a particular data type: integers, floating point numbers, fixed
length text, or variable length text. For example, let us imagine a database
called FORUM. It consists of two tables, USERS and COMMENTS. The USERS
table is used to store some information about users: their first and last name,
their age, and an identifier which is used internally. The COMMENTS table is
used to store a piece of text communicated by the user, the ID of the user in
question, and the time of day of the comment. To create this database in
SQL, would take the following commands:
CREATE DATABASE FORUM;
USE FORUM;
CREATE TABLE USERS (ID int auto_increment,
FIRST varchar(255),
LAST varchar(255),
primary key (ID));
CREATE TABLE COMMENTS (USERID int,
TIME timestamp,
MESSAGE text);
The table creation command specifies the name of the field along with its
data type. In our cases the data types are integers, variable length charac-
ter strings, and time stamps. The “primary key” definition is an optimiza-
tion that tells the software to search using the first (the ID) column. The
database tables will be defined, but empty at this point. To review what was
just created one would enter:
SHOW TABLES;
+
——————————————
+
| Tables_in_project1
|
