SQL Server Training First Steps
Standard Query Language statements for relational database manipulation.
Aug 4, 2006
SQL, or Standard Query Language, is the de facto standard for relational database administration and manipulation. Despite the fact that various flavors exist on the open market, the core SQL language has remained stable for many years.
Individual vendors have taken it upon themselves to extend the SQL language by adding new SQL functions, and treat them as part of SQL itself, making for an interesting programming landscape. Nonetheless, understanding some basic concepts will make it much easier to understand SQL, and this basic SQL training tutorial article is designed as a starting point.
The SQL Server
At the center of any SQL relational database implementation is the SQL server. This is the software platform (usually multi-user) which manages the database itself. It has much in common with traditional server implementations in that the user model consists of an administrative 'super user', various user groups, and users; all with authentication.
Consequently, the SQL server should reside on a machine running a multi user server operating system (Windows Server, Linux, etc.), and the most efficient way to accomplish this is to access the SQL environment as a web service. Most SQL servers allow this style of access, as well as any web host offering an SQL server as part of the hosting package.
Arguably, Microsoft SQL Server and the Oracle family are the two best known commercial SQL implementations. However, there are some very stable Open Source SQL implementations such as MYSQL which offer functionality to rival the commercial offerings. However, they are not appropriate for large scale data warehousing as they suffer from performance issues.
As web based SQL implementations, for use with bulletin boards, site user authentication, affiliate tracking and content management, however, MYSQL or one of the other Open Source implementations are a more than capable solution. They also support a very standard SQL, with fewer 'vendor specific' functions added to the language.
One of the most popular interfaces to MYSQL happens to be MyPHP, a web interface and query builder allowing multiple SQL databases to be administered via a web browser. The advantage of the MyPHP implementation is that it offers ample in-tool help and various functions for building SQL queries and administering the database structure and data.
This includes facilities to download the database contents and backup and restore databases via the same interface. Other administration tools are available, and usually simply provide a more convenient interface to the underlying server functionality - in other words, anything that can be done with a front end tool, can also be done at the back end, using SQL statements.
Some Basic SQL Statements
The vast majority of the casual users' experience with SQL will be cutting and pasting SQL statements in web scripts (be it Perl, PHP, or ASP), and before mining the web for the best SQL scripts available, it is always useful to know a little more about what the statements mean. Essentially we can break them down into three categories:
- Table Creation
- Data Insertion/Update
- Data Retrieval
The table is the basic unit of storage in SQL, and consists of columns and rows. Each column has a name, a type of data that can be stored in it, and can be used (on its own, or in conjunction with other columns) as a unique key. In other words, if a table of users contains a username, email address and password, we might like to make the username unique, possibly in conjunction with the email address.
A table creation statement might look like:
CREATE TABLE users ( username VARCHAR UNIQUE, email_address CHAR(255), password TEXT )
This would create a table in which the username must be unique, and three columns per row are stored. We have used three types of character data here, and in reality, most work will be done with either text or numbers. It is rare to mix character types as in this example, but we do so to illustrate the richness of data types available.
Data insertion is done via the insert statement:
INSERT INTO users (username, email_address, password) VALUES ("user1", "someone@somewhere", "encrypted password")
There are variations in the above where the column names may be omitted, however it is the opinion of the author that it is good practice to quote the column names in the insert statement for clarity, and bug tracking. Should the insert statement contain a username that already exists, it will fail, and no data will be inserted.
Should user1 change their email address, we might update it using the update statement:
UPDATE users SET email_address="new_email_address" WHERE username="user1"
If we wished to send an email to everyone in the database, we could retrieve that information using the select statement:
SELECT email_address FROM users
This last would return a number of rows, which we would need to process in a sequential manner. Were we to include a where clause (as in the previous example) we might also select a minimum subset of the rows available - to retrieve all the users with a Yahoo! email address, for instance.
These examples, while simple, provide enough functionality for a very simple user management system, in conjunction with appropriate web scripting. Interfacing to SQL through a third language (PHP, ASP, Perl etc.) is important, as it is the only way to add interaction with the database from a web page.
Interfacing to SQL
Generally speaking, any scripted interface to an SQL server will pass through the following stages:
- Connect to the database with a username and password
- Prepare an SQL statement
- Execute the SQL statement, and handle any error returns
- Process any row data returned
- Close the connection to the database
Typically, the first step returns a 'handle' to the database, and the third, a handle to the result. This result set can be sequentially read, although only special kinds of query can allow movement in two directions through the result set.
It is also important to note that even if only a single SQL user (the anonymous web user) is accessing the database, this might represent many real users - it is not usual for each one to have their own SQL database access. Usually they all share one with limited rights.
A consequence of this is that some forms of table access (which lock the table) might result in access problems. There are ways around this, which are explained in other SQL tutorial articles.
Continuing the SQL tutorial, we shall be looking at how to create relational databases, and provide additional SQL training along the way to provide enough information in a condensed manner to learn SQL through the use of examples.
