MSSQL 2 manage databases and tables-mysql tutorial

As a database developer, you are responsible for creating and managing databases and tables. When creating a table, maintaining data integrity is important to you. To ensure that the data in the table is accurate, consistent, and reliable, SQLServer provides a variety of checks that you can apply to the table to enhance data integrity. SQLServer contains various system databases. This chapter does not

As a database developer, you are responsible for creating and managing databases and tables. When creating a table, maintaining data integrity is important to you. To ensure that the data in the table is accurate, consistent, and reliable, SQL Server provides a variety of checks that you can apply to the table to enhance data integrity. SQL Server contains various system databases. This chapter does not

As a database developer, you are responsible for creating and managing databases and tables. When creating a table, maintaining data integrity is important to you. To ensure that the data in the table is accurate, consistent, and reliable, SQL Server provides a variety of checks that you can apply to the table to enhance data integrity. SQL Server contains various system databases. This chapter describes different types of system databases and explains how to manage user-defined databases and user-defined tables. Key points ? Manage databases ? Manage tables ? Data types in SQL Server 2008 Preview lessons ? Create a database ? Modify database ? Delete database ? Create a table ? Modify table ? Delete table

Identifying system databases in SQL Server 2008

SQL Server 2008 has four system databases: Master, Model, Msdb, and Tempdb.

(1) the Master database is the most important database in the SQL Server System. it records all system information of the SQL Server system. The system information includes all logon information, system settings, SQL Server initialization information, and other system and user database information. Therefore, if the master database is unavailable, SQL Server cannot be started. In SQL Server 2008, system objects are not stored in the master database, but in the Resource database.

(2) the model database provides templates for all databases created on the SQL Server instance. Because tempdb is created every time SQLServer is started, the model database must always exist in the SQL Server system. When the create database statement is issued, the first part of the DATABASE is created by copying the content of the model DATABASE, and then the remaining part of the new DATABASE is filled with blank pages. If you modify the model database, all created databases will inherit these modifications. For example, you can set permissions or database options or add objects, such as tables, functions, or stored procedures.

(3) Msdb is a proxy service database that provides storage space for alarm, task scheduling, and operator operation recording.

(4) Tempdb is a temporary database that provides storage space for all temporary tables, stored procedures, and other temporary operations. The Tempdb database is used by all databases of the entire system. no matter which database the user uses, all the temporary tables and stored procedures they create are stored in tempdb. When SQL Server is started, the tempdb database is re-established. When a user is disconnected from SQL Server, the temporary table and stored procedure are automatically deleted.

Database storage structure

The storage structure of a database is divided into two types: logical storage structure and physical storage structure.

1. the logical storage structure of a database refers to the information of the database. the database of SQL Server is not only the storage of data, all information related to data processing operations is stored in the database. In fact, SQL Server databases are composed of different database objects, such as tables, views, and indexes. they are used to store specific information and support specific functions respectively, the logical storage structure of the database.

2. the physical storage structure of the database is to discuss how database files are stored on disks. A database is stored on a disk in files. it consists of database files and transaction log files. a database should contain at least one data file and one transaction log file, there can be multiple second data files. In fact, this sentence can be understood as follows: "There is only one primary data file in the database and at least one transaction log file (that is, there can be multiple but must be ), there can be multiple second data files (not available). What is "primary database file", "Second Data File", and "transaction log file"

Files and file groups in the database

Data files can be divided into primary data files and secondary data files.

1. Primary Database File)

A primary data file is the starting point of a database. each database has only one primary data file. The default suffix of the primary data file name is mdf. Secondary data files are optional. they can store all data and objects that are not in the primary data file.

2. second Database File (Secondary Database File)

The second database file is also called a secondary database file. the database may neither have secondary data files nor have multiple secondary data files. The default suffix of the data file name is ndf.

3. transaction log file

A transaction is the work of a unit. the work of this unit is either completed or not completed. The Microsoft SQL Server System provides transaction functions to ensure consistency and integrity of database operations.

Microsoft SQLServer system uses the transaction log of the database to implement the transaction function. The transaction log records all the modifications to the database. Logs Record the starting of each transaction, changing data, canceling modification, and other operations. Logs continuously increase with database operations. For some large operations, such as creating indexes, logs only record the facts of the operation, rather than the data generated. The transaction log also records the distribution and release of data pages, as well as the commit and rollback of each transaction. In this way, the SQL Server system is allowed to either recover the transaction or cancel the transaction. Transaction logs exist as operating system files and are called log files in databases.

Each database has at least one log file. The default suffix of the log file name is ldf.

4. file Group

To facilitate allocation and management, SQL Server allows you to classify multiple files into the same group and assign the group a name. this is the file group.

A file Group is a logical set of files. To facilitate data management and allocation, file groups can combine specified files. For example, in a database, three files (data1.ndf, data2.ndf, and data3.ndf) are created in three different disk drives respectively, and then a file group fgroup1 is specified for them. Later, the created table can be explicitly specified on the file group fgroup1. Data in the table is queried on these three disks. Therefore, you can execute parallel access to improve query performance.

When creating a table, you cannot specify to put the table on a file. you can only specify to put the table on a file group. Therefore, if you want to place a table on a specific file, you must create a file group.

Define database

Defining a database means creating a database from scratch and setting database options.

This section defines the database from three aspects: creating a database, setting database options, and viewing database information.

Creating a database is the process of determining the database name, file name, data file size, database character set, automatic growth, and automatic growth.

You can create up to 32767 databases in a Microsoft SQL Server instance. The database name must comply with the system identifier rules. When naming a database, make sure that the database name is brief and has a certain meaning.

After the DATABASE is created, you can use the alter database statement to modify the DATABASE as needed. You can modify database names, expand databases, contract databases, modify database files, manage database file groups, and modify character arrangement rules.

? Modify database name

[Example 2-4] change the name of the LCBCom database to LGCom.

? Expand database

In Microsoft SQLServer, if the data size of the database continues to expand, you can expand the size of the database as needed.

There are three ways to expand the database. The first method is to set the database to auto-increment mode, which can be set when the database is created. The second method is to directly modify the size of database data files or log files. The third method is to add new secondary data files or log files to the database.

[Example 2-5] expand the LGCom database by expanding the file

? Modify database files

You can use the alter database statement to modify specified files in the DATABASE as needed. These modifications include adding data files, adding specified files to a specified file group, adding log files, and deleting specified files.

Operations such as adding a data file and modifying a specified file have already been discussed. the following uses some examples to describe other operations on database files.

[Example 2-6] add a file to the specified file Group

? Manage file groups

A file Group is a logical combination of database data files. it manages and distributes data files to improve the efficiency of concurrent use of database files.

The Transact-SQL language does not provide commands to manage file groups independently. you can only use the ALTER DATABASE statement to manage file groups.

Manage tables in a database

Designing a database is actually designing tables in a database. When designing tables in a database, the goal is to use as few tables as possible and as few columns as possible in each table to meet the design requirements. A reasonable table structure can greatly improve the efficiency of querying the entire database data.

? Create and modify tables

This section describes how to create and modify tables.

The content includes creating tables, adding and deleting columns, modifying column attributes, setting identifier columns, viewing table information, and deleting tables.

In Microsoft SQLServer 2008, you can use the create table statement to CREATE a TABLE or a visual SQL Server Management Studio graphical tool. The following describes how to use the create table statement to CREATE a TABLE. The create table statement is a frequently used method for creating tables. it is also the most flexible and powerful method for creating tables.

[Example 2-7] use the create table statement to CREATE a students TABLE

[Example 2-8] use the create table statement to CREATE an orders TABLE

After creating a TABLE, you can use the alter table statement to modify the TABLE structure as needed. Adding new columns to a table and deleting existing columns are common operations to modify the table structure.

When you add a new column to a table, Microsoft SQLServer inserts a data value for the column in the table in each row of existing data.

Therefore, when adding a new column to a table, it is best to define a default constraint for the new column so that the column has a default value. If the new column has no default constraints and other data already exists in the table, you must specify that the new column allows null values. Otherwise, an error message is generated.

[Example 2-10] use the alter table statement to add columns in the TABLE

In addition to adding and deleting columns, you can use the alter table statement to modify column attributes. This section describes how to change the data type of a column. The basic syntax for changing the data type of a column using the alter table statement is as follows:

ALTER TABLEtable_name alter column column_name new_type_name

In the preceding syntax, the table_name parameter specifies the name of the table to be changed. the alter column clause is used to specify the name of the COLUMN to be changed and the new data type name. The column_name parameter specifies the COLUMN name, the new_type_name parameter specifies the new data type name.

[Example 2-12] use the alter table statement to change the column data type

The identifier column uniquely identifies each row of data in the table.

In Microsoft SQL Server 2008, you can create two types of identifier columns: the IDENTITY column and the ROWGUIDCOL column.

The following describes how to create and modify the two identifiers.

The column using the IDENTITY attribute is the IDENTITY column. each table can have only one IDENTITY column.

When defining the IDENTITY attribute, you must specify two values: seed value and increment value. In this way, the value of the IDENTITY column in the first row of the table is the seed value, and the value of the IDENTITY column in other rows is obtained by adding an increment value based on the value in the previous row.

[Example 2-13] use the IDENTITY attribute

The scope of the IDENTITY attribute is table. That is to say, in a specified table, the value of the IDENTITY attribute column does not have duplicate values. However, different tables may have the same value in the IDENTITY attribute column. This phenomenon must be avoided when performing a copy operation to merge multiple tables. The ROWGUIDCOL attribute provided by Microsoft SQL Server for the identifier column can solve this problem. The ROWGUIDCOL column is a globally unique identifier column. You can create at most one ROWGUIDCOL column in each table.

[Example 2-14] use the ROWGUIDCOL column

? Delete table

Deleting a table permanently removes the data and table structure from the database. After a table is deleted, its definition cannot be restored.

You can use the DROPTABLE statement to delete a table. the syntax of this statement is as follows:

DROPTABLE table_name

You cannot use the DROPTABLE statement to delete tables that are referenced by foreign key constraints in other tables. To delete a table referenced by a foreign key constraint, you must first delete the foreign key constraint before deleting the table. The owner of a table can delete its own table. When you delete a table, the rules bound to the table are lost by default. The constraints or triggers that belong to the table are automatically deleted. If you re-create a table, you must re-bind the corresponding rules and default, re-create the trigger, and add necessary constraints.

Practical problems

1. what permissions can a user use the DROPDATABASE command to delete a database?

2. what commands can be used to modify the database name and size.

3. use commands to create a data table

4. use commands to modify a data table

5. how to delete a table

Summary

1. databases self-contained in SQL SERVER

The Master database records the specific configurations of all servers, including authenticated users, databases, system configuration settings, and remote servers.

The Tempdb database stores all temporary tables and temporary databases for stored procedures.

A Model database is a template or prototype for a new database.

Msdb supports SQL Server proxy, which includes the features of scheduled periodic activities of SQL Server

2. the Create Database statement is used to Create a Database. It also contains the Database name, Database size, and files used to store data.

3. the Drop Database statement is used to delete a Database.

4. the Create Table statement is used to Create a Table.

5. the Alter Tabel statement is used to modify a table.

6. the Drop Table statement is used to delete a Table.