Creating
Databases and Tables
The creation of a new database is
relatively simple:
CREATE DATABASE
<dbname>
CREATE SCHEMA
<dbname>
These two forms are equivalent to
each other—<dbname> is the name of the new database you want to create.
As you can see, there is no mechanism for providing security or access
control—this is, indeed, a shortcoming of SQL that is solved by each database
system in its own way.
The creation of a table is somewhat
more complex, given that you need to declare its structure as well:
CREATE TABLE
<tablename> (
<col1name>
<col1type> [<col1attributes>],
[...
<colnname>
<colntype> [<colnattributes>]]
)
As you can see, it is necessary to
declare a column’s data type—as you probably have guessed, most database
systems are very strict about data typing, unlike PHP. Here’s the declaration
for a simple table that we’ll use throughout the remainder of this blog:
CREATE TABLE book (
id INT NOT NULL PRIMARY KEY,
isbn VARCHAR(13),
title VARCHAR(255),
author VARCHAR(255),
publisher VARCHAR(255)
)
Here, we start by declaring a column
of type INT that cannot contain NULL values and
is the primary key of our table. The
other columns are all VARCHARs of varying length
(note how we are using 255 as the
maximum allowable le ngth; this is a “safe bet” and
it is true in many, but not all,
database systems).
Creating
Indices and Relationships
Indices can be created (as was the
example with the primary ke y above) while you
are creating a table; al
ternatively, you can create them separately at a later point in
time:
CREATE INDEX
<indexname>
ON <tablename>
(<column1>[, ..., <columnn>])
For example, suppose we wanted to
create a unique index on the isbn column of the book table we created earlier:
CREATE INDEX book_isbn
ON book (isbn)
The name of the index is, of course,
entirely arbitrary and only has a meaning when deleting the latter; however, it
must still be unique and abide by the naming rules we described above.
Foreign-key relationships are
created either when a table is created, or at a later date with an altering
statement. For example, suppose we wanted to add a table that contains a list
of all of the blog titles for every book:
CREATE TABLE book_blog
(
isbn VARCHAR(13) REFERENCES book (id),
blog_number INT NOT NULL,
blog_title VARCHAR(255)
)
This code creates a one-to-many
relationship between the parent table book and the child table book_blog based
on the isbn field. Once this table is created, you can only add a row to it if
the ISBN you specify exists in book.
Dropping
Objects
The act of deleting an object from a
schema—be it a table, an index, or even the schema itself—is called dropping.
It is performed by a variant of the DROP statement:
DROP TABLE book_blog
A good database system that supports
referential integrity will not allow you to drop a table if doing so would
break the consistency of your data. Thus, deleting the book table cannot take
place until book_blog is dropped first.
The same technique can be used to
drop an entire schema:
DROP SCHEMA my_book_database
Adding
and Manipulating Data
While most of the time you will be
retrieving data from a database, being able to insert it is essential to using
it, later.
This is done by means of the INSERT
statement, which takes on two for ms:
INSERT INTO
<tablename> VALUES (<field1value>[, ..., <fieldnvalue>])
INSERT INTO
<tablename>
(<field1>[, ...,
<fieldn>])
VALUES
(<field1value>[,
..., <fieldnvalue>])
The first form of the INSERT
statement is used when you want to provide values for every column in your
table—in this case, the column values must be specified in the same order in
which they appear in the table declaration.
This form is almost never ideal; for
one thing, you may not even be able to specify a value for each column—for
example, some of the columns may be calculated automatically by the system, and
forcing a value onto them may actually cause an error to be thrown. In
addition, using this form implies that you expect the order of the columns to
never change—this is never a good idea if you plan for your application to run
for more than a month!
In its second form, the INSERT
statement consists of three main parts. The first part tells the database engine
into which table to insert the data. The second part indicates the columns for
which we’re providing a value; finally, the third part contains the actual data
to insert. Here’s an example:
INSERT INTO book
(isbn, title, author)
VALUES (’0812550706’,
’Ender\’s Game’, ’Orson Scott Card’);
Adding records to the database is,
of course, not very useful without the ability to modify them. To update
records, you can use the UPDATE statement, which can either alter the value of
one or more columns for all rows, or for a specific subset thereof by means of a
WHERE clause. For example, the following UPDATE statement updates the publisher
for all records in the book table to a value of ’Tor Science Fiction.’ UPDATE
book SET publisher = ’Tor Science Fiction’;
Since it is not li kely that all
books in the table will have the same publisher (and, if they did, you wouldn’t
need a database column to tell you), you can further restrict the range of
records over which the UPDATE statement operates:
UPDATE book
SET publisher = ’Tor
Science Fiction’, author = ’Orson S. Card’
WHERE isbn =
’0812550706’;
This UPDATE statement will update
only the record (or records) where isbn is equal to the value ’0812550706’.
Notice also that this statement illustrates another feature of the UPDATE
statement: it is possible to update multiple columns at a time using the same
statement.
Removing
Data
In a dynamic application, data never
remains constant. It always changes—and, sometimes, it becomes superfluous and
needs to be deleted. SQL database engines implement the DELETE statement for
this purpose:
DELETE FROM book;
This simple statement will remove
all records from the book table, leaving behind an empty table. At times, it is
necessary to remove all records from tables, but most of the time, you will
want to provide parameters limiting the deletion to specific records. Again, a
WHERE clause achieves this:
DELETE FROM book WHERE
isbn = ’0812550706’;
Retrieving
Data
As we mentioned earlier, relational
database are biased toward read operations; therefore, it follows that the most
common SQL statement is designed to extract data from a database.
To retrieve data from any SQL
database engine, you must use a SELECT statement; SELECT statements range from
very simple to incredibly complex, depending on your needs. Its most basic
form, however, is simple and easy to use:
SELECT * FROM book;
The statement begins with the verb
or action keyword SELECT, followed by a comma separated list of columns to
include in the dataset retrieved. In this case, we use the special identifier *,
which is equivalent to extracting all of the columns available in the dataset.
Following the list of columns is the keyword FROM, which is itself followed by
a comma-separated list of tables. This statement retrieves data from only one table,
the book table.
You will rarely need to gain access
to all of the records in a table—after all, relational databases are all about
organizing data and making it easily searchable. Therefore, you will most often
find yourself limiting the rows returned by a SELECT statement using a WHERE
clause. For example, for the book table, you may wish to retrieve all books wri
tten by a specific author. This is possible using WHERE.
SELECT * FROM book
WHERE author = ’Ray Bradbury’;
The recordset returned by this
SELECT statement will contain all books written by the author specified in the
WHERE clause (assuming, of course, that your naming convention is consistent).
You may al so list more than one parameter in a WHERE clause to further limit
or broaden the results, using a number of logical conjunctions:
SELECT * FROM book
WHERE author = ’Ray
Bradbury’ OR author = ’George Orwell’;
SELECT * FROM book
WHERE author = ’Ray
Bradbury’ AND publisher LIKE ’%Del Ray’;
The first example statement contains
an OR clause and, thus, broadens the results to return all books by each
author, while the second statement further restricts the results with an AND
clause to all books by the author that were also published by a specific
publisher. Note, here, the use of the LIKE operator, which provides a case-insensitive
match and allows the use of the % wild character to indicate an arbitrary
number of characters. Thus, the expression AND publisher LIKE ’%Del Ray’ will
match any publisher that ends in the string del ray, regardless of case.
SQL
Joins
As the name implies, joins combine
data from multiple tables to create a single recordset. Many applications use
extremely complex joins to return recordsets of data spanning across many
different tables. Some of these joins use subqueries that contain even more
joins nested within them. Since joins often comprise very complex que ries,
they are regarded as an advanced SQL concept and many inexperienced developers
try to avoid them—for better or worse, however, they are not quite as
complicated as they are made out to be.
There are two basic types of joins:
inner joins and outer joins. In both cases, joins create a link between two
tables based on a common set of columns (keys). An inner join returns rows from
both tables only if keys from both tables can be found that satisfies the join
conditions. For example:
SELECT *
FROM book INNER JOIN
book_blog
ON book.isbn = book_blog.isbn;
As you can see, we declare an inner
join that creates a link between book and book_blog; rows are returned only if
a common value for the isbn column can be found for both tables. Note that
inner joins only work well with assertive conditions—negative conditions often
return bizarre-looking results:
SELECT * FROM book
INNER JOIN book _blog ON book.isbn <> book_blog.isbn;
You would probably expect this query
to return a list of all the records in the book
table that do not have a
corresponding set of records in book_blog—however, the database engine returns
a data set that contains an entry for each record in book_blog that does not
match each record in book; the end result is, in fact, a dataset that contains
every line in book_blog repeated many times over (the actual size of the set
depending on the number of rows between the two tables that do have matching
values for their respective isbn columns).
Outer
Joins
Where inner joins restrict the
results returned to those that match records in both tables, outer joins return
all records from one table, while restricting the other table to matching records,
which means that some of the columns in the results will contain NULL values.
This is a powerful, yet sometimes confusing, feature of SQL database engines.
Left joins are a type of outer join
in which every record in the left table that matches the WHERE clause (if there
is one) will be returned regardless of a match made in the ON clause of the
right table.
For example, consider the following
SQL statement with a LEFT JOIN clause.
SELECT book.title,
author.last_name
FROM author
LEFT JOIN book ON book.author_id
= author.id;
The table on the left is the author
table because it is the table included as the primary table for the statement
in the FROM clause. The table on the right is the book table because it is
included in the JOIN clause. Since this is a LEFT JOIN and there is no further
WHERE clause limiting the results, all records from the author table will be in
the returned results. However, only those records from the book table that
match the ON clause where book.author_id = author.id will be among the results.
Right joins are analogous to left
joins—only reversed: instead of returning all results from the “left” side, the
right join returns all results from the “r i ght” side, restricting results
from the “left” side to matches of the ON clause.
The following SQL statement performs
a task similar to that shown in the left join example. However, the LEFT JOIN
clause has been replaced with a RIGHT JOIN clause. In addition, you’ll notice
another LEFT JOIN clause added to the statement to show that multiple joins may
be used in a single statement. Beware, however, that the type of join used will
impact the data returned, so be sure to use the correct type of join for the
job.
SELECT book.title,
author.last_name, publisher.name
FROM author
RIGHT JOIN book ON
book.author_id = author.id
Here, the table on the left is still
the author table, and the right table is still the book table, but, this time,
the results returned will include all records from the book table and only
those from the author table that match the ON clause where book.author_id=
author.id.
0 Comment to " PHP - Creating Databases and Tables - retrieve - remove - updates - joins "
Post a Comment