Computer Experiment on database (i)

This semester has studied the database this course, altogether four times on the machine, 8 on the machine test, are relatively basic, and now a few times on the machine test and individual to do the results of writing, hoping to just learn to help students, textbooks, "Database system Introduction" (fourth Edition) Higher Education Press, can refer to this book. There should be mistakes in it, I hope we can point out together and learn together. All the complete code is stored in the code base of my space and can be run directly.

on the computer experiment one understand SQLServer2000

I. Purpose of the experiment

Learn to run SQL Server2000, understand the functions of the components, and be familiar with the usage environment.

II. Preparation of the experiment

1. Composition of SQL Server

After you install the SQL Server2000 correctly, you can see the Microsoft SQL Server Group in the Windows Start menu.

Some of the tools used in the experiment are described below.

⑴ Services Manager (Servermanager)

Server Manager is used to start, pause, stop the MS SQL Server service, SQL Agent service, MS DTC service, and Microsoft Search service, which is an essential part of SQL Server. These services are typically run as applications in the background of the server. Only the MS SQL Server service is used in the experiment.

The server has three different states:

Y begins/continues, indicating that the server is running. When the MSSQLServer service is in this state, the server can accept the user to connect and process the data.

Y stops, any client connections to the server are disconnected, and all of their processes are terminated. Other services and processes in this state will be interrupted.

Y paused, only valid for MS SQL Server service. When MS SQL Server is paused, users connected to the server will be allowed to remain connected, but new user connections will be denied.

In the experiment, the MS SQL Server service, which is actually the database servers, must be started when the user wants to access the database (the Service Manager is run automatically at the default installation, and the service manager is started when the tray icon in the lower right corner of the screen is green triangle).

⑵ Enterprise Manager (Enterprisemanager)

It is one of the most common tools used by users in SQL Server and the user management interface provided by SQL Server. Users can manage all database servers that can be accessed graphically, including databases, Data Transformation Services DTS, management, security, and so on. When you manipulate an object in Enterprise Manager, you can perform most of the functions by right-clicking on the appropriate object.

⑶ Query Analyzer (queryanalyzer)

SQL Server Query Analyzer is a tool used to manage servers. Unlike Enterprise Manager, you must type SQL commands and SQL scripts when you use the tool. But it allows users to have more control over the DBMS than the Enterprise Manager. All of our SQL statements will be typed here and used in its graphical section to view the execution.

2. Running SQL Server

⑴ starts the MS SQL Server service.

Action: Start àms SQL serverà service Manager à start/continue.

⑵ Closes the Service Manager window and opens Enterprise Manager.

Action: Start àms SQL serverà Enterprise Manager.

⑶ Right click on each object, select a certain menu item view, familiar with the graphics management environment.

3. Connect to SQL Server

⑴ Closes the Enterprise Manager window and opens Query Analyzer. Action: Start àms SQL serverà Query Analyzer. In the Connect to SQL Server dialog box, enter the name of the database server in the SQL Server box. To select a local server, select (local). To specify another server or another instance of the server, enter the server name in the SQL Server box. Click Browse (...) button to display a list of active servers. The server is listed using the ServerName\InstanceName format.

⑵ Click Windows NT Authentication to connect using Windows NT Authentication. or click SQL Server Authentication to connect using SQL Server Authentication.

experiment two on the computer create SQL Server database and tables

I. Purpose of the experiment

Through the experiment, understand SQL Server's system database and user database, master SQL Server database creation method and database object, understand SQL Server2000 data type, master the method of creating SQL Server table.

II. Preparation of the experiment

1. Understanding SQL Server Database structure

In SQL Server, all information is stored in the database. Each database is made up of both physical and logical parts.

Logical components are database objects, such as database names, table names, view names, stored procedure names, and database objects that the user sees in the SQL Server graphical interface, or accesses in the program.

The physical components of the database-files, transparent to the user, typically only system administrators operate directly on the database files.

Each database file consists of the following three:

⑴ master data file. mdf, which is the base of the database, with only one primary file data per database.

⑵ data file. NDF, which contains content that is not included in the master data file. It is not necessary for every database, and a complex database can have multiple files.

⑶ log files. ldf, log files record the transaction log information, each database must have at least one log file.

2. Understanding SQL Server's four system databases and their role

Microsoft SQL Server 2000 systems have four system databases: master database

The master database records all system-level information for the SQL Server system. It records all login accounts and system configuration settings, all other databases, including the location of the database files, SQL Server initialization information. It always has a backup of the latest master database available. tempdb database

The tempdb database holds all temporary tables and temporary stored procedures. It also meets any other temporary storage requirements, such as storing worksheets generated by SQL Server. The tempdb database is a global resource, and all temporary tables and stored procedures are stored in the database for all users connected to the system. The tempdb database is re-created every time SQL Server is started, so the database is always clean when the system starts. Temporary tables and stored procedures are automatically dropped when a connection is disconnected, and no connections are active when the system shuts down, so nothing in the tempdb database is saved from one session of SQL Server to another session. Model database

The model database is used as a template for all databases created on the system. When you create a new database, the first part of the new database is created by copying the contents of the model database, and the remainder is populated by an empty page. msdb database

The msdb database is used by the SQL Server Agent to schedule alerts and jobs and to record operators.

3. Learn about the two sample databases for SQL Server

The pubs and Northwind sample databases are provided as learning tools. (The names of these databases are case-sensitive.) Most of the examples in SQL Server Books Online are based on these databases.

4. Understand common data types for SQL Server

The following describes the common data types for SQL Server.

① character data: Char,varchar,text. The specific type definition retrieves books Online.

Data type-sql Server:char, data type-sqlserver:varchar, data type-sql server:text.

② integer data type: Bigint,int,smallint,tinyint. The specific type definition retrieves books Online.

③ exact data type: Decimal,numeric. The specific type definition retrieves books Online.

④ floating-point data type: float,real. The specific type definition retrieves books Online.

⑤ Currency data type: Money,smallmoney. The specific type definition retrieves books Online.

⑥ Date-time data type: Datetime,smalldatetime. The specific type definition retrieves books Online.

Iii. contents of the experiment

⑴ starts the MS SQL Server service and opens Enterprise Manager.

⑵ Click Microsoft SQL Servers in the console root tree structure on the left side of Enterprise Manager, and then expand the servers under SQL Server group and the Service Group (local) Windows NT.

⑶ expands the database, and the user can see four system databases and two sample databases from SQL Server.

⑷ creates a database named Grademanager in Enterprise Manager.

Experiment steps:

① Select Database, right-click, and select New Database in the right-click menu.

② in the Name column of the New Database dialog box that appears, enter the database name: Grademanager, or you can change the storage path of the database file through the New Database dialog box (the default data file storage path is the SQL Server installation directory \MSSQL\Data \ below).

③ Expand the newly created database, you will find that the database already has the directory structure of the data object.

⑸ creates a table in the Grademanager database.

Experiment steps:

① Create a table in Enterprise Manager. Select Grademanager, right-click, and select New-> table in the right-click menu.

② enter parameters such as column name, data type, and length in the pop-up dialog box. In the blank column of data type click on the mouse, the corresponding blank bar on the right side of the box marked with the next triangle, click on the box pop-up type of Drop-down list, select it.

③ Click on the Save icon on the toolbar of the Enterprise Manager window to save the table you created.

④ Select the Grademanager database, press "F5" to refresh, expand the Grademanager database, and view the tables you created.

The table structure created is, table name: Worker,

Column name, type, length, constraints are:

WNO, char (4), primary key;   Wname, char (8), not null;  Sex,char (2), notnull; Birthday DateTime.

⑹ deletes the created Grademanager database in Enterprise Manager. Application Query Analyzer and SQL language to create the database, the relevant syntax in the online Help Index "CreateDatabase", the parameter requires the default can be used.

⑺ the SQL language in Query Analyzer to create TABLE worker in the Grademanager database, the related syntax Indexes "CREATE TABLE" in the online Help, and the parameter requires default.

Iv. contents of the experimental report

⑴ writes out the general steps of creating a database in Enterprise Manager;

⑵ writes a script that creates a database Grademanager in SQL language;

⑶ writes out the general steps for creating a table in Enterprise Manager;

⑷ writes script to create table worker in SQL language;

The test results are:

"Introduction to Database System" on the Computer Experiment report a

1. Write out the general steps of creating a database in Enterprise Manager;

1, click Microsoft SQL Servers in the console root tree structure, and then expand the server under SQL Server group and the service group (native to dell-205-066 (Windows NT)).

2. Select "Database", right-click, and select "New Database" in the right-click menu.

3, in the next occurrence of the New Database dialog box, in the Name column, enter the database name: Grademanager. Click OK.

2. Write script to create database Grademanager in SQL language;

CREATE DATABASE Grademanager

3. Write out the general steps of creating a table in Enterprise Manager;

1, select "Grademanager", click the right mouse button, in the right-click menu, select "New"-> "table".

2, in the pop-up dialog box input column name, data type and length and other parameters. Data type selection, in the data type blank bar Click on the right side of the corresponding blank bar appears labeled with the Next Triangle box, click the box pop-up type Drop-down list, select it.

3. Click the Save icon on the toolbar of the Enterprise Manager window to save the table you created.

4. Write script to create table worker in SQL language;

CREATE Tableworker (

Wno char (4) Primary key,

Wname Char (8) Not NULL,

Sex char (2) not NULL,

Birthday datetime