Автор: Mitchell Harper
From: http://www.tdan.com/view-articles/5010/Try surfing around the Internet and find a popular site that doesn't use some sort of database to store its content. I bet you'll have a tough time finding just one popular site (i.e. receiving over 5,000 unique visitors a day) that doesn't use some sort of DBMS, such as SQL Server 2000, MySQL, or Oracle.
As I'm sure you're aware, it's extremely difficult to build a dynamic Website without a way to access and manipulate your content dynamically. Back in 1995 when the majority of Websites were still serving static content (i.e. plain HTML files), database driven sites were nowhere near as popular as they are today. Why do you think this is? Are databases easier to setup today? Is Web server software more usable? Or was it just a natural progression that we as Web developers went through to "evolve" from the way we developed "back then"?
I like to think it's all of these things. Today we have databases such as MySQL and SQL Server that can be set up on a development machine with very little expertise: you simply activate the setup program, change a couple of options and you're up and running in 20 minutes or so. It also seems that databases are "the way to go" these days, which makes sense when you think about it: they're readily available (some such as MySQL are free), some are supported cross-platform, and others are supported with native providers or pure source code libraries.
In this article we're going to take a beginner's look at Microsoft SQL Server 2000. We'll look at how to create our first database, and also how to manipulate this database through both Enterprise Manager and Query Analyzer. If you've never worked with SQL Server 2000 before but have always been curious, then this article's guaranteed to get you up and running in no time.
Before we proceed, however, you should have SQL Server 2000 installed on your machine. I'm running Windows 2000 Advanced Server, but SQL Server 2000 can also be installed on Windows 2000 Server, Windows 2000 Datacenter Server, and Windows NT 4.0 with service pack 5. If you'd just like to fiddle around with SQL Server 2000 for the span of this article, then you'll be glad to know that you can download a free 120-day trial version of it by clicking here [1].
If you have any trouble with your installation then check MSDN [2] or consult the reference material that was included with your copy of SQL Server 2000.Before we create our first SQL Server 2000 database, let me fill you in on a couple of things. Firstly, SQL Server 2000 is a RDBMS (Relational Database Management System), which means that it allows you to create, update and administer relational databases. A relational database is a collection of data items that can be accessed and manipulated in many ways without actually changing the organization of the tables within that database.
Secondly, SQL Server 2000 supports its own dialect of SQL (Structured Query Language), which is known as Transact-SQL, or TSQL for short. TSQL is an extremely powerful and advanced set of commands that can be used to create, modify, query and delete databases, tables, triggers, constraints, etc. It's based on the ANSI SQL 92 standard but takes this standard into its own hands so to speak, adding that extra touch of flexibility and robustness to the commands that it supports.
Thirdly, as with many RDBMSs, there's always more than one way to accomplish a particular task. For example, Enterprise Manager lets you create tables using a familiar point and click interface similar to basic desktop databases like MS Access. Query Analyzer, on the other hand, acts more like command-driven databases such as MySQL, allowing you to get right down to the bare metal and code everything yourself. Throughout this article we will accomplish many things firstly with Enterprise Manager, and then with plain TSQL code in Query Analyzer.
The two programs that we're interested in working with are Enterprise Manager and Query Analyzer, so click on the Enterprise Manager item to load it up. When it loads, you'll notice two panels: the first is a tree view and the second is a list of items contained within the selected node of that tree.Expand the "Microsoft SQL Servers" branch in the left panel and also expand the "SQL Server Group" branch.
My computer's Network name is SERVER, and as you can see in the screenshot above, SQL Server represents my computer as a node called SERVER. Obviously this node will appear with your computer's Network name next to it. Expand this node and you'll see a number of folders appear in the right pane: databases, data transformation services, management, replication, security, support services and meta data services. In this article we're only concerned with databases, so expand the databases tab.
Each installation of SQL Server 2000 also includes a master database. One of the most important tables in the master database is the sysdatabases table, which contains a list of databases that reside on your SQL Server. To view the contents of the sysdatabases table in your master database:
If you can't find the sysdatabases table then click on the right pane and start to type the first few letters of sysdatabases. Enterprise Manager will highlight the table for you. Once you've found it, right click on it and choose the Open Table -> Return all rows option. Take a look at the various fields and values in the table, and then close it by pressing Ctrl+F4.
If you take a look at the list of databases under the databases node in the left pane, then you'll also notice the Northwind and pubs database, which come bundled with the SQL Server 2000 installation. These databases are not tied to the workings of SQL Server 2000 in any way, and you're free to do whatever you want with them.
However, instead of playing around with the Northwind or pubs databases, let's create our own. Right click on the databases node in the left pane and choose the New Database option.
The dialog contains three tabs; however, all we really need to do to create a new database is give it a name, so enter MyDatabase1 in the name field and click OK. If you're feeling confident then you can set the location of the database file and its growth settings by clicking on the Data Files tab. By default our new database will be created as C:\Program Files\Microsoft SQL Server\MSSQL\data\MyDatabase_Data.MDF, but you can change this to any location that you like. You can also change the location and growth settings of the transaction log for our database by clicking on the Transactions Log tab, but we won't worry about that for now.
If you take a look at the databases node in the left pane then you'll see that our MyDatabase1 database now exists in the list. Expand the MyDatabase1 node in the left panel. We're now going to create a table within it. Right click on the tables node and choose the New Table option. This will load the table designer, which allows us to add, edit and delete fields both to and from new and existing tables.
Each row in the designer represents one column in the table. Let's create a table that will hold the details of a set of widgets. Click on the first Column Name field and enter the value widgetId. The column name field is the name by which we refer to each particular column in the table, which we'll see shortly. Click on the Data Type field for widgetId and choose the int option, which represents an integer, or whole number. Notice that by selecting a data type, the length and allow null fields are automatically completed for us. However, we want the widgetId to always contain a value, so uncheck the Allow Nulls checkbox.
To make sure that each widget in our table is unique, we'll make the widgetId field a unique primary key. Right-click anywhere in this first row of the grid and choose 'set primary key'. You'll see a little key icon appear next to the column to indicate that it's now the primary key for the table.
Notice how I've set the identity value to Yes and also set the seed (the number that the column will have initially) and increment (the number of values by which the field will increase when a new record is added) values to 1.
Now that we've created a unique primary key column for our widgets, click on the next Column Name field and enter widgetName. Make this column a varchar (variable length character) and set its length to 20. Again, this column must contain a value, so uncheck the Allow Nulls table.
To keep our table simple, we will create just one last field. Click on the next Column Name field and enter the value widgetPrice. Make this column a money value that can't be null. Using the column properties tab at the bottom of the table designer window, change the Default Value of the column to 0.
Press Ctrl+F4 to close the table designer and choose Yes when asked if you'd like to save the changes to Table1. When prompted for the table's name, enter Widgets and click OK. If you select the Tables node under MyDatabase1 in the tree listing and take a look in the right-hand pane of Enterprise Manager, you'll see our new table, Widgets.
It's good to have a new table, but what use is this table if it doesn't contain any data? Right click on our Widgets table in the right pane and choose the Open Table -> Return all rows option. Normally you'd be presented with a complete list of all records in the table at this point, but because our table is empty, all we see are the column names and one empty record.
This empty record is the key to populating our database with records from within Enterprise Manager. Click on the empty widgetName column and enter the value "Red Widget". Click on the widgetPrice column and enter 9.95. Press enter when you're done and you've just added a new record to our Widgets table!
Notice how I've left some widgetPrice values blank? When you don't enter a value for the widgetPrice field, SQL Server sees that you've set a Default Value of 0 for that column and puts in this value automatically. Close the table by pressing Ctrl+F4.
Believe it or not, we've created our first database with Enterprise Manager... see how easy it was? We didn't have to use one bit of TSQL because Enterprise Manager handled everything behind the scenes for us.
I don't know about you, but I'm more of an old-style programmer and I like to create my code by hand. In the next section we're going to do exactly that with the use of Query Analyzer.
Ah! We've arrived at the fun part of the tutorial: coding a database and its tables by hand. Don't be scared: the manual creation of databases is not difficult at all and it's a great way to learn the TSQL syntax.
We're going to use Query Analyzer to create exactly the same database that we did with Enterprise Manager, so point to Start menu -> Programs -> Microsoft SQL Server and run Query Analyzer. When it loads, you'll be presented with the login screen. I'm assuming that you've just installed a fresh copy of SQL Server 2000, so the default username of sa and no password is fine for now. Click OK to login to your database and start working with Query Analyzer.
First off, let's delete the MyDatabase1 database that we created earlier with Enterprise Manager. The sysdatabases table in the master database contains entries for each SQL Server 2000 database that exists on our PC. When we created the MyDatabase1 database, a new record was added to the sysdatabases table.
Using some simple TSQL statements, we can check if the MyDatabase1 database exists in the sysdatabases table of the master database. If it does, then we'll delete it. In Enterprise Manager we used the tree view in the left pane to choose which database we wanted to work with. In Query Analyzer, we specify the USE [database name] statement to tell SQL Server 2000 which database we'd like to work with. The GO command executes all code up until that specific point of the script, so after the first GO command, SQL Server knows that any commands it encounters should be executed against the master database.
Now let's play around with a couple of TSQL commands and modify our database. The details of the commands you're about to see are available in the SQL Server 2000 help file, which you can access by pressing F1 in Query Analyzer. Adding a column to our widgets table Let's pretend that the boss of our widgets corporation wants to be able to set whether or not a particular widget is in stock. Most widgets are available; however, a select few have to be imported from Widgetville first.
In this article we've created an SQL Server 2000 database from scratch. We used Enterprise Manager and Query Analyzer to accomplish the same goals, and we finished off by looking at some TSQL commands we can use to manipulate our database. If you've never worked with SQL Server 2000 before then hopefully this article has given you the information you need to get started!