At last! You’ve fi nally ingested the most boring stuff. It doesn’t get any worse than basic objects and tools, does it? As it goes, you have to lay down a foundation before you can build a house. The nice thing is that the foundation is now down. Having used the clichéd example of building a house, I’m going to turn it all upside down by talking about the things that let you enjoy living in the house before you’ve even talked about the plumbing. You see, when working with databases, you have to get to know how data is going to be accessed before you can learn all that much about the best ways to store it. This chapter covers the most fundamental Transact-SQL (T-SQL) statements. T-SQL is SQL Server’s own dialect of Structured Query Language (SQL). The T-SQL statements that you learn about in this chapter are:
- SELECT
- INSERT
- UPDATE
- DELETE
These four statements are the bread and butter of T-SQL. You’ll learn plenty of other statements as
you go along, but these statements make up the basis of T-SQL’s Data Manipulation Language (DML). Because you’ll generally issue far more commands meant to manipulate (that is, read and modify) data than other types of commands (such as those to grant user rights or create a table), you’ll fi nd that these will become like old friends in no time at all. In addition, SQL provides many operators and keywords that help refi ne your queries. You’ll learn some of the most common of these in this chapter.
GETTING STARTED WITH A BASIC SELECT STATEMENT
If you haven’t used SQL before, or don’t feel like you’ve really understood it yet, pay attention here!
The SELECT statement and the structures used within it are the lion’s share of all the commands you
will perform with SQL Server. Let’s look at the basic syntax rules for a SELECT statement:
SELECT [ALL|DISTINCT] [TOP (<expression>) [PERCENT] [WITH TIES]]
<column list>
[FROM <source table(s)/view(s)>]
[WHERE <restrictive condition>]
[GROUP BY <column name or expression using a column in the SELECT list>]
[HAVING <restrictive condition based on the GROUP BY results>]
[ORDER BY <column list>]
[[FOR XML {RAW|AUTO|EXPLICIT|PATH [(<element>)]}[, XMLDATA]
[, ELEMENTS][, BINARY base 64]]
[OPTION (<query hint>, [, ...n])]
Wow — that’s a lot to decipher. Let’s look at the parts.
The SELECT Statement and FROM ClauseThe verb — in this case a SELECT — is the part of the overall statement that tells SQL Server what you are doing. A SELECT indicates that you are merely reading information, as opposed to modifying it. What you are selecting is identifi ed by an expression or column list immediately following the SELECT. You’ll see what I mean by this in a moment. Next, you add in more specifi cs, such as where SQL Server can fi nd this data. The FROM statement specifi es the name of the table or tables from which you want to get your data. With these, you have enough to create a basic SELECT statement. Fire up the SQL Server Management Studio and take a look at a simple SELECT statement:
SELECT * FROM INFORMATION_SCHEMA.TABLES;
Let’s look at what you’ve asked for here. You’ve asked to SELECT information; when you’re working in SQL Server Management Studio, you can also think of this as requesting to display information. The * may seem odd, but it actually works pretty much as * does everywhere: it’s a wildcard. When you write SELECT *, you’re telling T-SQL that you want to select every column from the table. Next, the FROM indicates that you’ve fi nished writing which items to output and that you’re about to indicate the source of the information — in this case, INFORMATION_SCHEMA.TABLES.
TRY IT OUT Using the SELECT Statement
Let’s play around with this some more. Change the current database to be the AdventureWorks
database. Recall that to do this, you need only select the AdventureWorks entry from the combo box in the toolbar at the top of the Query window in the Management Studio.
Now that you have the AdventureWorks database selected, let’s start looking at some real data from
your database. Try this query:
SELECT * FROM Sales.Customer;
After you have that in the Query window, just click Execute on the toolbar (the F5 key is a shortcut
for Execute and becomes a refl ex for you) and watch SQL Server give you your results. This query
lists every row of data in every column of the Sales.Customer table in the current database (in this case, AdventureWorks). If you didn’t alter any of the settings on your system or the data in the AdventureWorks database before you ran this query, you should see the following information if you
click the Messages tab:
(19820 row(s) affected)
For a SELECT statement, the number shown here is the number of rows that your query returned. You can also fi nd the same information on the right side of the status bar (found below the results
pane), with some other useful information, such as the login name of the user you’re logged in as, the
current database as of when the last query was run (this will persist, even if you change the database in the database dropdown box, until you run your next query in this query window), and the time it took for the query to execute.
How It WorksLet’s look at a few specifi cs of your SELECT statement. Notice that I capitalized SELECT and FROM. This is not a requirement of SQL Server — you could run them as SeLeCt and frOM and they would work just fi ne. I capitalized them purely for purposes of convention and readability. You’ll fi nd that many SQL coders use the convention of capitalizing all commands and keywords, and then use mixed case for table, column, and non-constant variable names. The standards you choose or have forced upon you may vary, but live by at least one rule: be consistent. The SELECT is telling the Query window what you are doing, and the * is saying what you want
(remember that * = every column). Then comes the FROM. A FROM clause does just what it says — that is, it defi nes the place from which your data should come. Immediately following the FROM is the names of one or more tables. In your query, all the data came from a table called Customer.
Now let’s try taking a little bit more specifi c information. Let’s say all you want is a list of all your
customers by last name:
SELECT LastName FROM Person.Person;
Your results should look something like:
Abbas
Abel
Abercrombie
...
Zukowski
Zwilling
Zwilling
Note that I’ve snipped rows out of the middle for brevity. You should have 19,972 rows. Because the
last name of each customer is all that you want, that’s all that you’ve selected.
Let’s try another simple query. How about:
SELECT Name FROM Production.Product;
Again, assuming that you haven’t modifi ed the data that came with the sample database, SQL Server
should respond by returning a list of 504 products that are available in the AdventureWorks database:
Name
----------------------------------------
Adjustable Race
Bearing Ball
BB Ball Bearing
...
...
Road-750 Black, 44
Road-750 Black, 48
Road-750 Black, 52
The columns that you have chosen right after your SELECT clause are known as the SELECT list. In
short, the SELECT list is made up of the columns that you have requested be output from your query.