We've been focused on the data manipulation aspect of SQL for the past few months, which I hope has improved your day-to-day development of database-backed applications. For the next few columns, we're going to take a slight left turn and talk about the data definition aspect of SQL -- how to use SQL to define the database and its component tables and other artifacts.
In the very first aboutSQL column, I covered the "standard" SQL, as codified by ANSI. Data manipulation across database platforms, as we've seen, conforms pretty well to the ANSI-SQL standard, which means SQL queries created for the open-source MySQL database will generally run in Microsoft SQL-Server, PostgreSQL, Sybase, Informix, Oracle, or any other database, as is. But when we move over to the data definition side of SQL, the database starts to diverge much more significantly in terms of exactly how things are implemented.
The differences between the details of defining a database among the database platforms should be no surprise -- the differentiating factors between databases are in the types of data elements, the operations allowed on the data and database elements, and even in the construction of the tables themselves. Regardless of the SQL implementation, databases support all of the following operations to some degree:
These topics will be the focus of the next few columns.
Corporate developers typically have database administrators (DBAs) to handle the details of defining database elements, but most developers are at least passingly familiar with using GUI-based database management tools, such as Microsoft Access, SQL-Server's Enterprise Manager, the phpMyAdmin Web interface, MySQL, or similar applications. These GUI tools are very effective, but I have found in practice that it is important for everyone working with databases to at least have an idea of the broad strokes of directly manipulating database structure manipulation, even if that knowledge is rarely used directly.
How much do you depend on GUI tools, and if you use them heavily, how interested are you in what goes on beneath the hood?
Also in aboutSQL:
Let's say, for example, you are a Windows-based programmer developing applications that run off a Linux-based MySQL server. You can use MyODBC to turn Microsoft Access into a GUI front-end for running queries against existing tables in the
MySQL database, but if you need to create a copy of the database for testing or simply add a table to the database, you're going to have trouble using the Access GUI to accomplish the task. You can however, log into the Linux server, start the
mysql environment, and directly manipulate the database using
Or, as a more common example, you can create the SQL for building a specific table or database, save it as a file, and use that file to create the database element in the future on any machine -- test, staging, production, etc. This method is far easier than creating a database by hand in the GUI environment.
There are two SQL keywords that apply to manipulating entire databases:
That's all there is to it -- if you want to create a database named "MusicCollection," the SQL command would be:
CREATE DATABASE MusicCollection
And when you're ready to get rid of that database completely, the SQL command is:
DROP DATABASE MusicCollection
Keep in mind that we're operating at the database level here, not the table level. The
CREATE command generates an empty database container called
MusicCollection, which can hold any tables you create for it. The
DROP command, conversely, deletes the entire database, tables, stored procedures, data,
and all. Be especially careful with the
DROP command, since SQL command line tools may not offer an "Are you sure you want to delete the entire database?" prompt.
This week's coverage of database-level manipulation is just an introduction; next week we'll start on manipulating databases at the table level using the same
DROP keywords. From there, we'll touch on changing existing database
tables, creating script files containing the database schema, and SQL datatypes of database columns. Until then, feel free to contact me with comments, questions, and criticisms about SQL.
As a followup to the several columns on SQL joins, a reader caught the one type of join I skipped -- the ANTI-JOIN. In a nutshell, the ANTI-JOIN provides a way to find records in one table that are not in another table. To my knowledge, Oracle is the only RDBMS that has actually implemented the ANTI-JOIN, so don't run out an try it without checking your DBMS documentation. Keep the comments coming!
John Paul Ashenfelter is president and CTO of TransitionPoint.com, a technology development and analysis group focusing on web database applications.
Read more aboutSQL columns.
Return to ONLamp.com.
Copyright © 2009 O'Reilly Media, Inc.