INTRODUCTION TO DATABASE

BASICS

BY

CHIKEZIE SUSAN KELECHI

1
DATABASE OUTLINE
CHAPTER ONE: INTRODUCTION
 Definition of a database
 Types of Database
 Information, Data and Data Integrity
CHAPTER TWO: COMPONENTS OF A DATABASE
 Tables
o Definition
o Fields
o Rows
 Forms
 Queries
 Reports
 Datatypes
o Definition
o Types of Datatypes
o Types of Simple and Complex Datatypes
 Constraints
o Definition and its use
o Types of Constraints
 Relationships
o Definition and its significance
o Entity Relationship Diagrams (ERDs)
o Types of Relationships

2
  Understanding Keys
o Primary key
o Unique key
o Foreign key
o Understanding Referential Integrity
 Views
o Definition and its use
o Materialized views
 Other Specialized Objects
o Clusters
o Partitioning and Non-Partitioning
o Sequences and Auto-Counters

CHAPTER THREE: BASIC MYSQL COMMANDS

o What are Transactions?
o Creating a Database
o Basic MySql Datatypes
o Creating a Table
o Insert Command
o Select Command
o WHERE clause
o Update Command
o Delete Command

3
CHAPTER ONE: INTRODUCTION TO DATABASE
 Definition
A database is a collection of information preferably related information and
preferably organized. A database consists of the physical files you set up on a
computer when installing the database software.
By definition, a database is a structured object. It can be a pile of papers, but most
likely in the modern world it exists on a computer system. That structured object
consists of data and metadata, with metadata being the structured part. Data in a
database is the actual stored descriptive information, such as all the names and
addresses of your customers. Metadata describes the structure applied by the
database to the customer data. In other words, the metadata is the customer table
definition. The customer table definition contains the fields for the names and
addresses, the lengths of each of those fields, and datatypes. (A datatype restricts
values in fields, such as allowing only a date, or a number). Metadata applies
structure and organization to raw data.

TYPES OF DATABASE

Databases functionally fall into three general categories:

o Transactional
o Decision support system (DSS)
o Hybrid

o TRANSACTIONAL DATABASE
A transactional database is a database based on small changes to the
database (that is, small transactions). The database is transaction-driven. In
other words, the primary function of the database is to add new data, change
existing data, delete existing data, all done in usually very small chunks,
such as individual records.
o DECISION SUPPORT SYSTEM (DSS) DATABASE
Decision support systems are commonly known as DSS databases, and they
do just that they support decisions, generally more management-level and
even executive-level decision-type of objectives.
o HYBRID DATABASE
It is a mixture of the transactional database and the hybrid database.

4
INFORMATION, DATA AND DATA INTEGRITY
Information refers to knowledge or the way in which knowledge is communicated.
Values in a database are made up of data, which is essentially information. Validity
is determined by the integrity of data. The integrity of data is the correct form of
data. The following list leads off the definitions of basic terms and concepts:

o The concept of information: Information is knowledge or the

communication of knowledge. Knowledge is accumulated and derived by
processes including those of experience, events, or static information (such
as a set of statistical values). In computer jargon, information is data that is
stored in a database, processed by programs, or even transmitted over a
network such as the Internet (between multiple users).

o The concept of data: Data is composed of unique, specifically formatted

items of information. Unique data item values are stored in slots in a
database, processed as individual values by coded programs, and transmitted
across networks of wires, or even communicated with electromagnetic
signals to and from satellites (all over the world, and beyond).
o The concept of a computer program: Programs are sets of precise
instructions, used to manipulate and process changes to a database.

o The concept of a datatype: Datatypes comprise the forms data can take,
such as numbers, dates, strings, and others.

o The concept of data integrity: The integrity of data is the validity of data.
Possible compromises to data integrity include human error at data entry,
network transmission errors, software bugs and virus infections, hardware
malfunction, disk errors, and natural disasters. Countering compromises to
data integrity is mostly a pre-emptive process, rather than a re-active
process. In other words, the best solution is to attempt to prevent data
integrity loss. The most significant prevention mechanisms are database
backups (regularly), computer security (in all forms), and properly designed
interfaces restricting how data is entered by data entry users. Solving the
problem after the fact often utilizes something called a parity check (such as
when transmitting over a network), which is simply a check of something, of
itself.
5
CHAPTER TWO: COMPONENTS OF A DATABASE

This chapter describes all the pieces that the database consists of. All of the
constituents of the database help to create an organized logical structure for
managing data in a database.

 Tables
A table is a bucket into which data is poured. A table consists of fields (columns)
and records (rows). There is really nothing to understand other than that a table can
have multiple fields, whereas that set of fields can have many records created in
that table, and data can subsequently be accessed according to the field structure of
the table, record by record.

o Fields
The terms field, column, and attribute all mean the same thing. They are all terms
used to describe a field in a table. Afield applies structure and definition to a chunk
of data within each repeated record. Fields are the vertical entity of a table.

o Records
The terms record, row, and tuple all mean the same thing. They are terms used to
describe a record in a table. Records are the horizontal entity of a table.

 Forms
A form is a database object that you can use to create a user interface for a
database application. Example are the forms you fill on websites.

 Queries
A query is a request for information from a database. It is usually a code. They are
primary mechanisms for retrieving information from a database.

 Reports
A report presents information from a database. Information is displayed simply and
efficiently in tabular form.

6
DATATYPES

Datatype can be defined as the form a data can take. It refers the form or type a
data is to be stored as. There are many different types of datatypes, which vary
often more in name than anything else with respect to different database engines.
This section describes all different variations of datatypes.

 Types of Datatypes

Datatypes can be divided into three separate sections:

o Simple datatypes: These are datatypes applying a pattern or value
limitation on a single value such as a number.
o Complex datatypes: These include any datatypes bridging the gap between
object and relational databases, including items such as binary objects and
collection arrays. Specifics on complex datatypes are not strictly necessary
for this book as they are more object-oriented than relational in nature.
o Specialized datatypes: These are present in more advanced relational
databases catering to inherently structured data such as XML documents,
spatial data, multimedia objects and even dynamically definable datatypes.

 Simple Datatypes
Simple datatypes include basic validation and formatting requirements placed on to
individual values. This includes the following:

o Strings: A string is a sequence of one or more characters. Strings can be

fixed-length strings or variable-length strings:
 Fixed-length strings: A fixed-length string will always store the
specified length declared for the datatype. The value is padded with
spaces even when the actual string value is less than the length of the
datatype length. For example, the value NY in a CHAR (3) variable
would be stored as NY plus a space character.

 Variable-length strings: A variable-length string allows storage into a

datatype as the actual length of the string, as long as a maximum limit is
not exceeded. The length of the string is variable because when storing a
string of length less than the width specified by the datatype, the string is
7
 not padded (as is the case for fixed-length strings). Only the actual string
value is stored. Storing the string XXX into a variable length string
datatype of ten characters in length stores the three characters only, and
not three characters padded by seven spaces. Different databases use
different naming conventions for variable-length string datatypes.
VARCHAR (n) or TEXT (n) are common naming formats for variable-
length strings.

o Numbers: Numeric datatypes are often the most numerous field datatypes in
many database tables. The following different numeric datatype formats are
common:
 Integers: An integer is a whole number such that no decimal digits
are included. Some databases allow more detailed specification using
small integers and long integers, as well and standard-sized integer
datatypes.
 Fixed-length decimals: A fixed-length decimal is a number,
including a decimal point, where the digits on the right side of the
decimal are restricted to a specified number of digits. For example, a
DECIMAL (5,2) datatype will allow the values 4.1 and 4.12, but not
4.123.
 Floating points: A floating-point number is just as the name implies,
where the decimal point “floats freely” anywhere within the number.
In other words, the decimal point can appear anywhere in the number.
Floating-point values can have any number of digits both before and
after the decimal point, even none on either side. Values such as 1000,
1000.12345, and 0.8843343223 are valid floating-point numbers.

o Dates and times: Dates can be stored as simple dates or dates including
timestamp information. In actuality, simple dates are often stored as a Julian
date or some other similar numbering system. A Julian date is a time in
seconds from a specified start date (such as January 1, 1960). When simple
date values are set or retrieved in the database, they are subjected to a default
formatting process spitting out to, for example, a dd/mm/yyyy format
excluding seconds (depending on default database formatting settings, of
course). A timestamp datatype displays both date and time information

8
 regardless of any default date formatting executing in the database
(sometimes stored as a special timestamp datatype).

 Complex Datatypes
Complex datatypes encompass object datatypes. Available object datatypes vary
for different relational databases. Some relational databases provide more object-
relational attributes and functionality than others. Complex datatypes include any
datatypes breaching the object-relational database divide including items such as
binary objects, reference pointers, collection arrays and even the capacity to create
user defined types. Following are some complex datatypes:

o Binary objects: Purely binary objects were created in relational databases to

help separate binary type data from regular relational database table record
structures. A large object such as a graphic is so very much larger than the
length of an average table record containing all strings and numbers. Storage
issues can become problematic. Relational databases use many different
types of underlying disk storage techniques to make the management of
records in tables more efficient. Atypical record in a table may occupy at
most 2 KB (sometimes known as a page or block), and often much less.
Even the smallest of graphic objects used in Web site applications easily
exceeds the size of a record and each record in a table could have a unique
graphic object. Therefore, storing a graphic object with each record in the
underlying operating system block structure completely ruins any kind of
specialized storage structure performance tuned for simple table record
strings and numbers storage. Binary objects were created to physically
separate binary values from traditional table record values. The obvious
extension to this concept was creation of binary objects to store anything in
binary format, reducing storage, even items such as large strings, sound files,
video, XML documents . . . the list goes on.
o Collection arrays: Some relational databases allow creation of what an
object database would call a collection. A collection is a set of values
repeated structurally (values are not necessarily the same) where the array is
contained within another object, and can only be referenced from that object.
In the case of a relational database, the containment factor is the collection
being a field in the table. Collection arrays can have storage structures
defined in alternative locations to table fields as for binary objects, but do
9
 not have to be as such. Collection arrays, much like program arrays, can be
either fixed length or dynamic. A dynamic array is a variable-length array,
and is actually a pointer. When using a fixed-length array, the programmer
must specify the length of the array before using it.

CONSTRAINTS
In general, constraints are used to restrict values in tables, make validation checks
on one or more fields in a table, or even check values between fields in different
tables. They are put on fields not the tables themselves. Following are some
examples of constraints:
o Not Null: This is the simplest of field level constraints, making sure that a
value must always be entered into a field when a record is added or changed.
o Validation check: Similar to a NOT NULL constraint, a validation
checking type of constraint restricts values in fields when a record is added
or changed in a table. A check validation constraint can be as simple as
making sure a field allowing only M for Male or F for Female, will only
ever contain those two possible values.
o Keys: Key constraints include primary keys, foreign keys, and unique keys.
All these key types are discussed briefly later on in this chapter and further
in later chapters in this book. Key constraints allow the checking and
validation of values between fields in different tables. Primary and foreign
keys are essentially the implementation of relationships between parent and
child tables. Those relationships or relations are the source of the term
relational database. We discuss keys further later in this chapter.

RELATIONSHIPS
Tables are connected to each other with relationships. The link or connection
between tables is called a Relationship. Relationships are important because they
help in making a table less bulky, you can store data relating to the same record in
different tables, all you have to do is link them.

10
ENTITY RELATIONSHIP DIAGRAMS (ERDs)
The different types of inter-table relation- ships that can be formed between
different tables can be best described as displayed in Entity Relationship Diagrams
(ERDs). An ERD displays tables and relationships between those tables.
TYPES OF RELATIONSHIPS
There are basically three types of relationships that can exist between tables. They
are:
 ONE-TO-ONE:
A one-to-one relationship implies exactly one entry in both tables. Meaning
one record in the parent table can be linked to just one record in the child.

11
 ONE-TO-MANY:
One-to-many implies one entry to many entries between two tables. In this
type of relationship, one record in the parent table can be linked to multiple
records in the child table. For example, we have two tables, one called
Authors that keeps record of authors and their details while the second called
Books keeps a record of the books they have published. In this relationship,
an author’s record in the Authors table can be linked to multiple records of
several books that author has published. This relationship allows a record in
the parent table (Authors) to have multiple records in the child table
(Books).

12
  MANY-TO-MANY:
A many-to-many relationship means that for every one record in one table
there are many possible records in another related table, and vice versa (for
both tables). The classic example of a many-to-many relationship is many
students enrolled in many courses at a university. The implication is that
every student is registered for many courses and every course has many
students registered. The result is a many-to- many relationship between
students and courses.

UNDERSTANDING KEYS
A key is also a key, its namesake, because it creates a special tag for a field,
allowing that field to be used as a table relationship field, linking tables together
into relations. Asides linking tables, keys make search for a record in a table easy.
A key makes the field it is set on to be unique, i.e. no two records can have the
same value. For instance, the matriculation number of a student is unique to a
student; this can be a key in a table. There are three types of keys: a primary key, a
unique key, and a foreign key.
o Primary Keys
A primary key is used to uniquely identify a record in a table. Unique
identification for each record is required because there is no other way to
find a record without the possibility of finding more than one record, if the
unique identifier is not used. A good example is the matriculation number
mentioned above.

13
 o Unique Keys
Like a primary key, a unique key is created on a field containing only unique
values throughout an entire table.
So, why create unique keys that are not primary keys? A unique key ensures
uniqueness across a table. A primary key is always unique, or at least a
unique key; however, a primary key is also used to define relationships
between tables. Unique keys are not used to define relationships between
tables.
o Foreign Keys
Foreign keys are the copies of primary keys created into child tables to form
the opposite side of the link in an inter-table relationship—establishing a
relational database relation. A foreign key defines the reference for each
record in the child table, referencing back to the primary key in the parent
table.

UNDERSTANDING REFERENTIAL INTEGRITY

Referential Integrity functions just as its name states: It ensures the integrity of
referential relationships between tables as defined by primary and foreign keys. In
a relation between two tables, one table has a primary key and the other a foreign
key. The primary key uniquely identifies each record in the first table. In other
words, there can be only one record in the first table with the same primary key
value. The foreign key is placed into the second table in the relationship such that
the foreign key contains a copy of the primary key value from the record in the
related table.
Remember, a constraint is a piece of metadata defined for a table defining
restrictions on values. A primary key constraint forces the primary key field to be
unique. A primary key constraint is also forced to make checks against any foreign
key constraints referenced back to that primary key constraint. Referencing (or
referential) foreign key constraints can be in any table, including the same table as
the primary key constrained field referenced by the foreign key (a self-join). A
foreign key constraint uses its reference to refer back to a referenced table,

14
containing the primary key constraint, to ensure that the two values in the primary
key field and foreign key field match.
A primary key table is assumed to be a parent table and a foreign key table a child
table. Foreign key fields can contain NULL values. Primary key field values can
never contain NULL values as they are required to be unique.
Simply put, primary and foreign keys automatically verify against each other.
There are some specific circumstances to consider in terms of how Referential
Integrity is generally enforced:
 When adding a new record to a child table, if a foreign key value is entered,
it must exist in the related primary key field of the parent table.
 When changing a record in a parent table if the primary key is changed, the
change must be cascaded to all foreign key valued records in any related
child tables. Otherwise, the change to the parent table must be prohibited.
The term “cascade” implies that changes to data in parent tables are
propagated to all child tables containing foreign key field copies of a
primary key from a parent table.
 When changing a record in a child table, a change to a foreign key requires
that a related primary key must be checked for existence, or changed first. If
a foreign key is changed to NULL, no primary key is required. If the foreign
key is changed to a non-NULL value, the foreign key value must exist as a
primary key value in the related parent table.
 When deleting a parent table record then related foreign key records in child
tables must either be cascade deleted or deleted from child tables first.
VIEWS
A view is essentially a query definition and does not contain any data. A view is
not a physical copy of data and does not contain any data itself. A view is merely a
logical overlay of existing tables. Every execution against a view executes the
query contained within the view against all underlying tables.
A view simply put is a table-on-the-fly. A view displays data from a table, it does
not have data of its own. Views are typically useful for speeding up the
development process but in the long run can completely kill database performance.
MATERIALIZED VIEWS
15
A materialized view materializes underlying physical data by making a physical
copy of data from tables. So, unlike a view as described previously, when a query
is executed against a materialized view, the materialized view is physically
accessed rather than the underlying tables. The objective is to free the underlying
tables for other uses, effectively creating two separate physical copies.
Materialized views are often used to aggregate large data sets down to smaller
sized data sets, in data warehouses and data marts. The biggest potential problem
with materialized views is how often they are refreshed and brought up to date
with any changes to their underlying tables. Another attribute of materialized
views is the ability of some database engines to allow a query directed at an
underlying table to be automatically redirected to a physically much smaller
materialized view, sometimes called automated query rewrite.

OTHER SPECIALIZED OBJECTS

Other than views, there are further possibilities within relational databases that
some database engines allow and some do not. It is important to know that
specialized objects exist as options for expansion to a relational database model, as
extensions to both the underlying physical structure of a database and the overlying
logical structure (the tables and indexes). They include:
 CLUSTERS
Clusters are used in very few databases and have been somewhat superseded
by materialized views. In the past, clusters were used to pre-create physical
copies of entire field level sections of heavily accessed tables, especially in
SQL joins. Unlike materialized views, clusters do not allow for automatic
refresh and are normally manually maintained.
 SEQUENCES AND AUTO-COUNTER
An auto counter field is a special datatype, sometimes called a non-static
internal function, allowing automated generation of sequential number
values (thus the term “sequence”). Typically, auto counters are used for
primary key surrogate key generation on insertion of new records into a
table.
 PARTITIONING AND PARALLEL PROCESSING

16
Some databases allow physical splitting of tables into separate partitions,
including parallel processing on multiple partitions and individual operations
on individual partitions. One particularly efficient aspect of partitioning is
the capability when querying a table to read fewer than all the partitions
making up a table, perhaps even a single partition.

17
 CHAPTER THREE: BASIC MYSQL COMMANDS
In this chapter, we would be discussing the basic mysql commands, some of the
commands you are to know.
WHAT ARE TRANSACTIONS?
Database transactions are sets of queries that must execute in such a way so
that if one query fails to execute completely they all fail. For instance,
suppose that you have a set of three queries, the second dependent on the
results of the first, and the third dependent on the results of the second. If the
second query fails, you need to have a way to negate the results of the first
query; similarly, if the third query fails, you need to negate the results of the
first and second queries, as well.
CREATING A DATABASE
The basic syntax to create a database in MySql is:
CREATE DATABASE db_name;
There are other attributes and settings depending on the database platform but you
can just use this basic syntax then the default settings are applied.
MYSQL DATATYPES
MySQL uses many different data types, broken into three categories: numeric, date
and time, and string types. Pay close attention because properly defining the data
type is more important than any other part of the table creation process.
 NUMERIC DATATYPES
MySQL uses all the standard ANSI SQL numeric data types. Below is a list
of numeric datatypes in MySql;
The terms signed and unsigned are used in the list of numeric data types. If
you remember your basic algebra, you’ll recall that a signed integer can be
a positive or negative integer, whereas an unsigned integer is always a non-
negative integer.
o INTEGER: There are different types of integers and they include:
 INT: A normal-sized integer that can be signed or unsigned. If signed, the
allowable range is from –2147483648 to 2147483647. If unsigned, the
18
 allowable range is from 0 to 4294967295. You can specify a width of up to
11 digits.
 TINYINT: A small integer that can be signed or unsigned. If signed, the
allowable range is from –128 to 127. If unsigned, the allowable range is
from 0 to 255. You can specify a width of up to 4 digits.
 SMALLINT: A small integer that can be signed or unsigned. If signed, the
allowable range is from –32768 to 32767. If unsigned, the allowable range is
from 0 to 65535. You can specify a width of up to 5 digits.
 MEDIUMINT: A medium-sized integer that can be signed or unsigned. If
signed, the allowable range is from –8388608 to 8388607. If unsigned, the
allowable range is from 0 to 16777215. You can specify a width of up to 9
digits.
 BIGINT: A large integer that can be signed or unsigned. If signed, the
allow-able range is from –9223372036854775808 to
9223372036854775807. If unsigned, the allowable range is from 0 to
18446744073709551615. You can specify a width of up to 11 digits.
o FLOAT(M,D): A floating-point number that cannot be unsigned. You can
define the display length (M) and the number of decimals (D). This is not
required and defaults to 10,2, where 2 is the number of decimals and 10 is
the total number of digits (including decimals). Decimal precision can go to
24 places for a FLOAT.
o DOUBLE(M,D): A double-precision floating-point number that cannot be
unsigned. You can define the display length (M) and the number of decimals
(D). This is not required and will default to 16,4, where 4 is the number of
decimals. Decimal precision can go to 53 places for a DOUBLE. REAL is a
synonym for DOUBLE.

 DATE AND TIME

MySQL has several data types available for storing dates and times, and these data
types are flexible in their input. In other words, you can enter dates that are not
really days, such as February 30—February has only 28 or 29 days, never 30. Also,
you can store dates with missing information. For example, if you know that some-

19
one was born sometime in November 1980, you can use 1980-11-00, where 00
would have been for the day, if you knew it.
The flexibility of MySQL’s date and time types also means that the responsibility
for date checking falls on the application developer (that would be you). MySQL
checks only two elements for validity: that the month is between 0 and 12 and that
the day is between 0 and 31. MySQL does not automatically verify that the
thirtieth day of the second month (February 30) is a valid date. Therefore, any date
validation you want to include in your application should happen in your PHP code
before you even attempt to add a record with a bogus date into your database table.
The MySQL date and time data types are as follows:
o DATE: A date in YYYY-MM-DD format, between 1000-01-01 and 9999-
12-31. For example, December 30, 1973, is stored as 1973-12-30.
o DATETIME: A date and time combination in YYYY-MM-DD HH:MM:SS
format, between 1000-01-01 00:00:00 and 9999-12-31 23:59:59. For
example, 3:30 in the afternoon on December 30, 1973, is stored as 1973-12-
30 15:30:00
o TIMESTAMP: A timestamp between midnight, January 1, 1970, and
sometime in 2037. You can define multiple lengths to the TIMESTAMP
field, which directly correlates to what is stored in it. The default length for
TIMESTAMP is 14, which stores YYYYMMDDHHMMSS. This looks like
the previous DATETIME format, only without the hyphens between
numbers; 3:30 in the afternoon on December 30, 1973, is stored as
19731230153000. Other definitions of TIME- STAMP are 12
(YYMMDDHHMMSS), 8 (YYYYMMDD), and 6 (YYMMDD).
o TIME: Stores the time in HH:MM:SS format.
o YEAR(M): Stores a year in two-digit or four-digit format. If the length is
speci- fied as 2 (for example, YEAR(2)), YEAR can be 1970 to 2069 (70 to
69). If the length is specified as 4, YEAR can be 1901 to 2155. The default
length is 4.
You will likely use DATETIME or DATE more often than any other date- or time-
related data type.

20
 STRING DATATYPES
Although numeric and date types are fun, most data you’ll store will be in string
format. This list describes the common string data types in MySQL:
o CHAR(M): A fixed-length string between 1 and 255 characters in length
(for example, CHAR(5)), right-padded with spaces to the specified length
when stored. Defining a length is not required, but the default is 1.
o VARCHAR(M): A variable-length string between 1 and 255 characters in
length; for example, VARCHAR(25). You must define a length when
creating a VARCHAR field.
o BLOB or TEXT: A field with a maximum length of 65,535 characters.
BLOBs are Binary Large Objects and are used to store large amounts of
binary data, such as images or other types of files. Fields defined as TEXT
also hold large amounts of data; the difference between the two is that sorts
and comparisons on stored data are case sensitive on BLOBs and are not
case sensitive in TEXT fields. You do not specify a length with BLOB or
TEXT.
o TINYBLOB or TINYTEXT: A BLOB or TEXT column with a maximum
length of 255 characters. You do not specify a length with TINYBLOB or
TINYTEXT.
o MEDIUMBLOB or MEDIUMTEXT: A BLOB or TEXT column with a
maximum length of 16,777,215 characters. You do not specify a length with
MEDIUMBLOB or MEDIUMTEXT.
o LONGBLOB or LONGTEXT: A BLOB or TEXT column with a
maximum length of 4,294,967,295 characters. You do not specify a length
with LONGBLOB or LONG- TEXT.
o ENUM: An enumeration, which is a fancy term for list. When defining an
ENUM, you are creating a list of items from which the value must be
selected (or it can be NULL). For example, if you want your field to contain
A or B or C, you would define your ENUM as ENUM (‘A’, ‘B’, ‘C’), and
only those values (or NULL) could ever populate that field. ENUMs can
have 65,535 different values. ENUMs use an index for storing items.

21
CREATING A TABLE
The table-creation command requires:
 Name of the table
 Names of fields
 Definitions for each field
The generic table-creation syntax is:
CREATE TABLE table_name (column_name column_type);
The table name is up to you, of course, but should be a name that reflects the usage
of the table. For example, if you have a table that holds the inventory of a grocery
store, you would not name the table s. You would probably name it something like
grocery_inventory. Similarly, the field names you select should be as concise as
possible and relevant to the function they serve and the data they hold.
The following table-creation example creates a generic grocery_inventory table
with fields for ID, item name, item description, item price, and quantity. Each of
the fields are a different type; the ID and quantity fields hold integers, the item
name field holds up to 50 characters, the item description field holds up to 65,535
characters of text, and the item price field contains a float:
CREATE TABLE grocery_inventory (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
item_name VARCHAR (50) NOT NULL,
item_desc TEXT,
item_price FLOAT NOT NULL,
curr_qty INT NOT NULL
);

THE INSERT COMMAND

After you have created some tables, you use the SQL command INSERT for
adding new records to these tables. The basic syntax of INSERT is:

INSERT INTO table_name (column list) VALUES (column values);

22
Within the parenthetical list of values, you must enclose strings within quotation
marks. The SQL standard is single quotes, but MySQL enables the usage of either
single or double quotes. Integers do not require quotation marks around them.

Besides the table name, the INSERT statement consists of two main parts: the
column list and the value list. Only the value list is actually required, but if you
omit the column list, you must specifically provide for each column in your value
list in the exact order.
Using the grocery_inventory table as an example, you have five fields: id,
item_name, item_desc, item_price, and curr_qty. To insert a complete record, you
could use either of these statements:

 A statement with all columns named:

INSERT INTO grocery_inventory (id, item_name, item_desc, item_price,
curr_qty) VALUES (‘1’, ‘Apples’, ‘Beautiful, ripe apples.’, ‘0.25’, 1000);

 A statement that uses all columns but does not explicitly name them:
INSERT INTO grocery_inventory VALUES (‘2’, ‘Bunches of Grapes’,
‘Seedless grapes.’, ‘2.99’, 500);

Now for some more interesting methods of using INSERT. Because id was defined
at creation time as an auto-incrementing integer in the grocery_inventory table,
you do not have to put it in your value list. However, if there’s a value you
specifically do not want to list (such as id), you then must list the remaining
columns in use. For example, the following statement does not list the columns and
does not give a value for id:

INSERT INTO grocery_inventory VALUES (‘Bottled Water (6-pack)’, ‘500ml

spring water’, 2.29, 250);

The preceding statement produces an error, such as this:

ERROR 1136: Column count doesn’t match value count at row 1

Because you did not list any columns in this query, MySQL expects all of them to
be in the value list; since you did not, the query results in an error. If your goal was
to let MySQL do the work for you by auto-incrementing the id field, you could use
either of these statements:
23
  A statement with all columns named except id:
INSERT INTO grocery_inventory (item_name, item_desc, item_price,
curr_qty) VALUES (‘Bottled Water (6-pack)’, ‘500ml spring water’, ‘2.29’,
250);

 A statement that uses all columns, but does not explicitly name them and
indicates a NULL entry for id (so one is filled in for you):
INSERT INTO grocery_inventory VALUES (‘NULL’, ‘Bottled Water (12-
pack)’, ‘500ml spring water’, 4.49, 500);

THE SELECT COMMAND

SELECT is the SQL command used to retrieve records from your tables. This
command syntax can be totally simple or very complicated, depending on which
fields you want to select, whether you want to select from multiple tables, and
what conditions you plan to impose. As you become more comfortable with
database programming, you will learn to enhance your SELECT statements,
ultimately making your database do as much work as possible and not overworking
your programming language.
The most basic SELECT syntax looks like this:
SELECT expressions_and_columns FROM table_name [WHERE
some_condition_is_true] [ORDER BY some_column [ASC | DESC]] [LIMIT
offset, rows]
Look at the first line:
SELECT expressions_and_columns FROM table_name;
One handy expression is the * symbol, which stands for everything. So, to select
everything (all rows, all columns) from the grocery_inventory table, your SQL
statement would be:
SELECT * FROM grocery_inventory;
If you want to select specific columns only, replace the * with the names of the
columns, separated by commas. The following statement selects just the id,
item_name, and curr_qty fields from the grocery_inventory table:
24
SELECT id, item_name, curr_qty FROM grocery_inventory;
Ordering SELECT Results
Results of SELECT queries are ordered as they were inserted into the table and
should not be relied on as a meaningful ordering system. If you want to order
results a specific way, such as by date, ID, name, and so on, specify your
requirements using the ORDER BY clause. In the following statement, the
intention is a result set ordered alphanumerically by item_name:
SELECT id, item_name, curr_qty FROM grocery_inventory ORDER BY
item_name;
The default sorting of ORDER BY results is ascending (ASC); strings sort from A
to Z, integers start at 0, and dates sort from oldest to newest. You can also specify
a descending sort, using DESC:
SELECT id, item_name, curr_qty FROM grocery_inventory ORDER BY
item_name DESC;
Limiting Your Results
You can use the LIMIT clause to return only a certain number of records from your
SELECT query result. Two requirements apply when using the LIMIT clause: the
off- set and the number of rows. The offset is the starting position, and the number
of rows should be self-explanatory.
Suppose that you have more than two or three records in the grocery_inventory
table, and you want to select the ID, name, and quantity of the first two, ordered by
curr_qty. In other words, you want to select the two items with the least inventory.
The following single-parameter limit starts at the 0 position and goes to the second
record:
SELECT id, item_name, curr_qty FROM grocery_inventory ORDER BY curr_qty
LIMIT 2;

THE WHERE CLAUSE

You have learned numerous ways to retrieve particular columns from your tables
but not specific rows. This is when the WHERE clause comes in to play. From the
25
example SELECT syntax, you see that WHERE is used to specify a particular
condition:
SELECT expressions_and_columns FROM table_name [WHERE
some_condition_is_true]
An example is to retrieve all the records for items with a quantity of 500:
SELECT * FROM grocery_inventory WHERE curr_qty = 500;
As shown previously, if you use an integer as part of your WHERE clause,
quotation marks are not required. Quotation marks are required around strings,
however, and the same rules apply with regard to escaping characters as you
learned in the section on INSERT.
Using Operators in WHERE Clauses
You’ve used the equal sign (=) in your WHERE clauses to determine the truth of a
condition that is, whether one thing is equal to another. You can use many types of
operators, with comparison operators and logical operators being the most popular
types. The table below lists the comparison operators and their meanings.
OPERATORS MEANING
= Equal To
!= Not Equal To
< Less Than
> Greater Than
<= Less Than or Equal To
>= Greater Than or Equal To

There’s also a handy operator called BETWEEN, which is useful with integer or
date comparisons because it searches for results between a minimum and
maximum value. For example:
SELECT * FROM grocery_inventory WHERE item_price BETWEEN 1.50 AND
3.00;

26
Other operators include logical operators, which enable you to use multiple
comparisons within your WHERE clause. The basic logical operators are AND and
OR. When using AND, all comparisons in the clause must be true to retrieve
results, whereas using OR allows a minimum of one comparison to be true. Also,
you can use the IN operator to specify a list of items that you want to match.
THE UPDATE CLAUSE
UPDATE is the SQL command used to modify the contents of one or more
columns in an existing record or set of records. The most basic UPDATE syntax
looks like this:
UPDATE table_name SET column1=’new value’, column2=’new value2’
[WHERE some_condition_is_true]
The guidelines for updating a record are similar to those used when inserting a
record: The data you’re entering must be appropriate to the data type of the field,
and you must enclose your strings in single or double quotes, escaping where
necessary.
For example, assume that you have a table called fruit containing an ID, a fruit
name, and the status of the fruit (ripe or rotten).
To update the status of the fruit to ripe, use:
UPDATE fruit SET status = ‘ripe’;
Conditional Updates
Making a conditional UPDATE means that you are using WHERE clauses to
match specific records. Using a WHERE clause in an UPDATE statement is just
like using a WHERE clause in a SELECT statement. All the same comparison and
logical operators can be used, such as equal to, greater than, OR, and AND.
Assume that your fruit table has not been completely filled with grapes but instead
contains four records, one with a spelling mistake (grappe instead of grape). The
UPDATE statement to fix the spelling mistake is as follows:
UPDATE fruit SET fruit_name = ‘grape’ WHERE fruit_name = ‘grappe’;
THE DELETE COMMAND
The basic DELETE syntax is as follows:

27
DELETE FROM table_name [WHERE some_condition_is_true] [LIMIT rows]
When you use DELETE without specifying restriction or condition, the entire
record is removed. When updating a table without specifying a condition caused an
update of all records. You must be similarly careful when using DELETE.

The following statement removes all records in the table:

DELETE FROM fruit;
CONDITIONAL DELETE
A conditional DELETE statement, just like a conditional SELECT or UPDATE
statement, means you are using WHERE clauses to match specific records. You
have the full range of comparison and logical operators available to you, so you
can pick and choose which records you want to delete.
A prime example is to remove all records for rotten fruit from the fruit table:
DELETE FROM fruit WHERE status = ‘rotten’;

00JZR3-1B0JUD-U39REE-85C9CX

28
29