SQL Server 2008 CREATE database

1. Basic concepts of data, database and data management systems:

Data: Symbolic representation of human useful information.

database: A warehouse that organizes, stores, and manages data in accordance with its data structure.

Database management System ( DBMS ): Software Systems ( software + database + data Administrators ) that can maintain, store, and provide data to the application system ）。

Things that can be objectively described are called " entities ", and different data represent different entities. Data for each row (row) corresponds to an entity, known as " record ", each entry is called " column ", such as number, name, number, etc. The data " table " is a collection of entities that are used to store specific data. " Database " is a " Table " the collection. Relationships are also an important part of the database (from relational expressions to abstract concepts like keys, types, rules, permissions, constraints, triggers, and so on).

Database history:

• Embryonic stage-File system: Provides simple data sharing and management, similar to WinXP's resource manager.

• Primary stage-first generation database: Hierarchical model and Mesh model database system (e.g. IMS information Management System)

• Intermediate stage-second generation database: relational databases (such as IBM's Db2,oracle), using Structured Query language SQL as database definition language DDL and Database operation language DML. (It is still the world of business applications)

• Advanced stage-A new generation of databases: such as relational-object-based database systems, but changes in different directions to suit the needs of the database vendors.

2. Common database types:

• Hierarchy Model: IMS

• Mesh Structure Model: DBTG (Data Base Task Group)

• Relational Structure model: two-dimensional tabular form (field Records)

3. Common Database Products

SQL Server(Microsoft),Oracle(Oracle Oracle),DB2(IBM),MySQL(Sun),Access (MS Office).

Current Intenet Popular web site architecture: LAMP (Linux + Apache + MySQL + PHP). (MySQL open source software, low cost)

Here we first understand and master the application of SQL Sever database, so use the most popular SQL Sever 2005 database as the research object

SQL Server 2005 What are the major versions:

SQL Sever 2005 Enterprise Edition Corporate (Mega Enterprise), SQL Sever 2005 Standard Edition (Small and Medium Business), SQL Server 2005 Workgroup Edition Working Group Edition (Small Business for production services), SQL Server 2005 Development Edition Development (small Business for development testing), SQL Sever 2005 Express Edition Lite

4.SQL Server 2005 Express based on WinXP installation of:

(1) Installing Windows Installer 3.0

(2) Installing the Microsoft. NET Framework 2.0
(3) Installing the Microsoft. NET 2.0 Language Pack Language Pack

(4) Installing SQL Server 2005 Expressedition (sqlexpr32_sp2_chs_20070218)

(5) Installing Management studioexpress (sqlserver2005_ssmsee_sp2_20070217)

(6) Installing SQL Sever 2005 Books Online (Sqlserver2k5_bol_dec2008_chs)
"Note" If you have installed Visual Studio 2005/2008 development software, you only need to install section (5).

5.SQL Server 2005 To create a database and data table:

SQL Server 2005 databases contain physical files: Data files + log files

Primary data file (. mdf)-Contains database startup information and points to other files in the database (only one).

Secondary data file (. ndf)-user-defined and stored user data (optional) that can be dispersed across multiple disks (extensible).

Transaction log file (. ldf)--Saves log information (at least one) that is used to recover the database.

"Note" For ease of administration and allocation, each database has one primary filegroup (Primary): All minor files that contain the primary data files and are scattered across the disks but are not grouped.

SQL Server 2005 System databases (5):master,msdb,model,Resource,tempdb

system database	for     with
master	records all database system-level information (endpoint, login account, system configuration, etc.) Records the presence, file location, and initialization information of all other databases.
msdb	as SQL Ser The ver agent is used to schedule alerts and jobs, or to complete other functions.
model	used to create on instance Template for all databases (must exist).
, Resource	contains all numbers Database system objects (read only), i.e. all system resource databases (prefix sys)
  tempdb	P align= "left" >  temporary workspace for saving temporary objects or intermediate result sets, and does not allow backup and restore operations to tempdb.

• Create a user database:

A. Connect the server first: (server name (native):. SQLEXPRESS; authentication: Windows authentication)

Note the differences between Windows authentication and SQL Server authentication:

Windows authentication identifies the client for authentication (that is, the user account on behalf of each computer) by the user account specified by Windows

SQL Server authentication authenticates the client by comparing the user name and password provided by the client with a list of valid usernames and passwords maintained in SQL Server.

B. Create a new database:

C. The options panel sets the relevant options for the database. Make sure that you complete the creation of the database.

Management and maintenance of the database:

• Migration of databases:

Detach : detaches the database file from the current instance of SQL Server (the database is removed from a computer)

Attach : Attach a database file to another instance of SQL Server (the DB instance is moved to another DB instance)

Although the database is detached, the detached students database physical file still exists in the Data folder under the SQL Server installation directory (address below), where the database can be replicated and moved in such a migration operation without being affected by the server.

So how do you get back to the database for re-operation?

Click "Add" to locate the path to the previous database file:

When the point "OK" is added successfully, the database is back in our sight (it is again associated with this server instance and cannot be migrated).

• Backup and restore of the database:

backup : A copy of the data that is used to restore and recover data after a system failure.

Restore : The process of loading a backup of a database into the system.

Click "Add" to save the backup file in a different directory.

"Note" cannot be backed up to a separate drive letter path (errors in the above operation) and must be backed up to the specified folder directory.

For example: I store the backup file in the D:temp directory,

Let us perform the restore operation, first assuming that the students database physical file has been accidentally deleted due to a system failure (here for easy operation, we deliberately delete it), then you can call D: The Students.bak in temp makes a database restore so you don't get caught up in a situation like this.

Select the restored source file: that is, the path to the backup file

When you click OK, the following dialog box appears (if the error cannot be restored, it is likely that you did not delete the two physical files in the database.) ）

In the future we can take our backup files to different server operations without having to carry so many database physical files.

• Create user login account

Select the General tab:

Select the User Mappings tab:

Then disconnect from the original server (you can also click on the left side of the "open connection" without shutting down the original) and reconnect with the SQL Server account:

After successful login, there are two user names with the login name SA and Sun.

See if you can log in successfully, and if not, add the following server roles:

A special reminder here is that SA is the default highest privilege for SQL Server logged-on users.

SQL Server 2008 CREATE database