Lowe D. Networking For Dummies
Подождите немного. Документ загружается.
Book VIII
Chapter 3
Using SQL
Server 2008
637
From a practical point of view, the third definition is the most important:
A relational database is a database that you can access via SQL. Thus, SQL
Server is used for relational databases. The next section dives a little more
deeply into what SQL is.
What Is SQL?
SQL, which stands for Structured Query Language, is a language designed to
extract, organize, and update information in relational databases. Originally,
SQL was envisioned as an English-like query language that untrained end
users could use to access and update relational database data. But in real-
ity, SQL is nothing like English, and it’s far too complicated and esoteric for
untrained users. But it has become the overwhelming favorite among pro-
grammers who develop applications that access relational databases.
SQL dialects
Like most computer languages, SQL has several different dialects. In fact,
each major brand of SQL database server has its own dialect of SQL. These
dialects are 95 percent the same, so a basic SQL statement is likely to work
the same regardless of the database server you use it with. But there are
many variations in how the more advanced features of SQL work.
The version of SQL used by Microsoft’s SQL Server is known as T-SQL.
SQL statements
Like other programming languages, SQL uses statements to get its work
done. Table 3-1 lists the most commonly used statements.
Table 3-1 Common SQL Statements
SQL Statement What It Does
use
Identifies the name of the database that subsequent SQL
statements apply to.
select
Retrieves data from one or more tables. This is the SQL state-
ment that’s used the most.
insert
Inserts one or more rows into a table.
delete
Deletes one or more rows from a table.
update
Updates existing rows in a table.
create
Creates tables and other database objects.
alter
Alters the definition of a table or other database object.
drop
Deletes a table or other database object.
What Is SQL?
60_625873-bk08ch03.indd 63760_625873-bk08ch03.indd 637 9/21/10 10:43 PM9/21/10 10:43 PM
638
Using the select statement
As the name Structured Query Language suggests, queries are what SQL is all
about. Thus, the select statement is the most important of the SQL state-
ments. A select statement extracts data from one or more tables in a data-
base and creates a result set containing the selected rows and columns.
In a select statement, you list the table or tables from which you want
to retrieve the data, the specific table columns you want retrieved (you
might not be interested in everything that’s in the table), and other clauses
that indicate which specific rows should be retrieved, what order the rows
should be presented in, and so on.
Here’s a simple select statement that retrieves data from a table named
movie, which contains information about your favorite movies:
select title, year
from movie
order by year
Now, take this statement apart piece by piece:
✦ select title, year names the columns you want included in the
query result (title and year).
✦ from movie names the table you want the rows retrieved from
(movie).
✦ order by year indicates that the result should be sorted into
sequence by the year column so the oldest movie appears first.
Here’s something you’ve probably wondered
ever since you first saw the letters SQL: How
do you pronounce it? Two schools of thought
exist on the subject:
✓ Spell out the letters: S-Q-L.
✓ Pronounce it like the word sequel.
Either way is acceptable, but sequel is hipper.
You can always tell how a writer pronounces
SQL by checking to see if the author writes “a
SQL query” or “an SQL query.”
You can impress even the staunchest SQL
expert by pointing out that originally, the lan-
guage was spelled SEQUEL by the IBM engi-
neers who created the first version way back
in the 1970s. SEQUEL stood for Structured
English Query Language. Someone must have
correctly pointed out that aside from borrow-
ing a few words from English such as select
and CREATE, SEQUEL actually bore no resem-
blance whatsoever to English. So English was
dropped from the name and the acronym was
shortened from SEQUEL to just SQL.
How do you pronounce SQL?
What Is SQL?
60_625873-bk08ch03.indd 63860_625873-bk08ch03.indd 638 9/21/10 10:43 PM9/21/10 10:43 PM
Book VIII
Chapter 3
Using SQL
Server 2008
639
In other words, this select statement retrieves the title and date for all the
rows in the movie table and sorts them into year sequence.
If you want the query to retrieve all the columns in each row, you can use an
asterisk instead of naming the individual columns:
select * from movie order by year;
Both examples so far include an order by clause. In a SQL database, the
rows stored in a table are not assumed to be in any particular sequence. As
a result, if you want to display the results of a query in sequence, you must
include an order by clause in the select statement.
Suppose you want to find information about one particular video title. To
select certain rows from a table, use the where clause in a select state-
ment. For example:
select title, year from movie
where year <= 1980
order by year
Here, the select statement selects all rows in which the year column is
less than or equal to 1980. The results are ordered by the year column.
Perhaps you want to retrieve all rows except those that match certain cri-
teria. For example, here’s a query that ignores movies made in the 1970s
(which is probably a good idea, with a few exceptions like The Godfather and
Young Frankenstein):
select title, year from movie
where year < 1970 or year > 1979
order by year;
There is, of course, much more you can do with select statements. But this
chapter isn’t about SQL itself; it’s about installing and using SQL Server. So
get ready to move on to the good stuff.
Installing SQL Server 2008
Like Exchange Server, SQL Server is licensed separately from Microsoft; it
isn’t included with any version of Windows Server 2008. As a result, you
have to install it on a Windows Server computer before you can use it to
create and access SQL databases.
You’ll probably want to run this installation procedure at least twice. First,
you’ll want to run it on a server computer to install the SQL Server 2008 server
components, thus creating a SQL Server instance. Then, you’ll want to run the
setup procedure on a client workstation computer to install the management
Installing SQL Server 2008
60_625873-bk08ch03.indd 63960_625873-bk08ch03.indd 639 9/21/10 10:43 PM9/21/10 10:43 PM
640
components that you run from a local workstation to manage the server.
Depending on the needs of your organization, you may also need to run the
setup program additional times to install other components.
The following steps summarize the procedure for installing SQL Server 2008:
1. Insert the SQL Server 2008 distribution disk in the server computer’s
CD/DVD drive.
The installation program should auto-start. If it doesn’t, browse to the
disk, open the Servers folder, and double-click the Splash.hta file. The
screen shown in Figure 3-1 appears.
2. Under the Install heading, click the Server Components, Tools, Books
Online, and Samples link.
This starts the SQL Server 2008 Setup program, which begins by display-
ing a license agreement page.
3. Select the I Accept check box and then click Next.
4. Be patient while the Setup program installs any prerequisites that are
missing.
Typically, this takes just a minute while the Setup program installs sup-
port files that are necessary for the installation to proceed.
5. When the prerequisites have been installed, click Next.
The Setup program next displays the Microsoft SQL Server 2008 Setup
welcome screen, as shown in Figure 3-2.
Figure 3-1:
Installing
SQL Server
2008.
Installing SQL Server 2008
60_625873-bk08ch03.indd 64060_625873-bk08ch03.indd 640 9/21/10 10:43 PM9/21/10 10:43 PM
Book VIII
Chapter 3
Using SQL
Server 2008
641
Figure 3-2:
Welcome to
SQL Server
2008 setup.
6. Click Next.
The Setup program checks to make sure all the requirements are in
place for a successful installation of SQL Server 2008. This may take a
few minutes. When all the tests are complete, you see a screen such as
the one shown in Figure 3-3. If any errors or warnings are detected, be
sure to correct them before continuing.
7. Click Next.
The Setup program prompts you to enter your name and your company
name to personalize the installation.
Figure 3-3:
Checking
the system
require-
ments.
Installing SQL Server 2008
60_625873-bk08ch03.indd 64160_625873-bk08ch03.indd 641 9/21/10 10:43 PM9/21/10 10:43 PM
642
8. Enter your name (required) and company name (optional) and then
click Next.
The next screen, shown in Figure 3-4, lets you select which components
of SQL Server 2008 you want to install.
9. Select the components you want to install.
At the minimum, you should select the first component, SQL Server
Database Services.
You can select specific elements of any of the SQL Server components
by clicking the Advanced button.
You should also install the Workstation components. However, you
won’t typically want to install them on the same computer that you
install the SQL Database Services components on. Instead, you’ll want
to repeat this entire procedure, installing the Workstation components
on a client computer. You can then use these components to manage
the SQL Server from a client computer, without having to log on to the
server.
The remainder of this procedure assumes you have selected the SQL
Server Database Services components to install a new SQL Server
instance. If you’re installing the Workstation components, the prompts
and steps will vary slightly.
10. Click Next.
The Setup program next asks for the name of the instance you want to
create, as shown in Figure 3-5. You can leave this option set to Default
Instance, which uses the name of the server as the instance name, or
you can enter your own instance name if you prefer.
Figure 3-4:
Determining
which
components
of SQL
Server 2008
to install.
Installing SQL Server 2008
60_625873-bk08ch03.indd 64260_625873-bk08ch03.indd 642 9/21/10 10:43 PM9/21/10 10:43 PM
Book VIII
Chapter 3
Using SQL
Server 2008
643
Figure 3-5:
Specifying
an instance
name.
11. Choose the instance name and then click Next.
The Service Account screen appears, as shown in Figure 3-6. This screen
lets you specify the Windows account that will be used to run the SQL
Server services on the server computer. You can leave this set to the
built-in system account, or, if you prefer, you can choose a domain
account to run the services. Note that if you choose to use a domain
account, you must first use Active Directory Users and Computers to
create the account, and you must make sure the account has adequate
privileges.
Figure 3-6:
Specifying
a service
account.
Installing SQL Server 2008
60_625873-bk08ch03.indd 64360_625873-bk08ch03.indd 643 9/21/10 10:43 PM9/21/10 10:43 PM
644
12. Select the service account and then click Next.
The Authentication Mode screen appears, as shown in Figure 3-7. This
screen lets you choose one of two methods for authenticating users:
Windows Authentication Mode, which relies entirely on Windows
domain accounts, and Mixed Mode, which uses Windows domain
accounts and accounts that are created within SQL Server. Unless you
have a specific reason to use Mixed Mode (for example, you have older
applications that depend on SQL Server accounts), I recommend that
you select Windows Authentication Mode.
13. Select the authentication mode and then click Next.
A screen called Collation Settings appears. This screen lets you change
the way SQL Server sorts data. In most cases, you should leave this
option set at its default.
14. Click Next.
A screen titled Error and Usage Reporting Settings appears. This screen
lets you choose whether the server should send diagnostic information
to Microsoft when errors occur and whether the server should send
usage data to let Microsoft know which features of SQL Server you use
the most. You can enable these features if you don’t care about the
privacy of this information or the small performance penalty the com-
munication incurs. If you want to keep this information private or are
obsessively concerned about performance, you can disable these
features.
15. Enable or disable the reporting settings and then click Next.
The next screen displays a summary of what components of SQL Server
will be installed, as shown in Figure 3-8.
Figure 3-7:
Specifying
the authenti-
cation
mode.
Installing SQL Server 2008
60_625873-bk08ch03.indd 64460_625873-bk08ch03.indd 644 9/21/10 10:43 PM9/21/10 10:43 PM
Book VIII
Chapter 3
Using SQL
Server 2008
645
16. Click Install to begin the installation.
The Setup program proceeds with the installation, which can take a few
minutes. A progress screen appears to indicate the installation’s prog-
ress. When all components have been installed, the screen shown in
Figure 3-9 appears.
17. Click Next to continue.
One final completion screen appears to congratulate you on a job well done.
18. Click Finish.
You’re done!
Figure 3-8:
Summarizing
the
installation.
Figure 3-9:
Success!
Everything
has been
installed.
Installing SQL Server 2008
60_625873-bk08ch03.indd 64560_625873-bk08ch03.indd 645 9/21/10 10:43 PM9/21/10 10:43 PM
646
Using the SQL Server 2008 Management Studio
The SQL Server Management Studio is a component of SQL Server 2008
that runs on workstation computers and lets you manage any or all of the
SQL Server instances on your network. You run it by choosing SQL Server
Management Studio from the Start menu on a workstation computer. The
Management Studio begins by displaying a Connect to Server dialog box,
as shown in Figure 3-10. You can use this dialog box to connect to any SQL
Server instance on your network.
To connect to a SQL Server instance, specify the instance name and your
logon credentials, and then click Connect. After you have connected, the
Management Studio displays the screen shown in Figure 3-11.
Figure 3-10:
Connecting
to a SQL
Server
instance.
Figure 3-11:
SQL Server
Management
Studio has
connected
to an
instance!
Using the SQL Server 2008 Management Studio
60_625873-bk08ch03.indd 64660_625873-bk08ch03.indd 646 9/21/10 10:43 PM9/21/10 10:43 PM