5. SQL Server Database

SQL Server Database Fundamentals

First, install the SQL Server database

Setup.exe-> Installation--New SQL Server standalone installation or add features to an existing installation---Enter serial number---next to the default instance, mixed Mode--Enter SA password---Add the current account to the instance account.

If you want other machines to access this machine's SQL Server, you need to use inbound rules in Windows Firewall, port write 1433.

Ii. SQL Server Configuration Manager

Include each SQL service, when connecting to the database, it is important to ensure that the SQL Server service is turned on.

Third, the system database

1. master database

Primary system database, the library is missing and SQL Server will not start.

2. tempdb database

Used to create temporary objects, including temporary tables, stored procedures, table variables, global temporary tables, and cursors.

3. Model Database

Model of all databases created in a SQL Server instance.

4. Msdb database

Mainly used for the support background of SQL Server Agent.

5. Resource Database

Resource database, hidden system database. Primarily used to improve the process capabilities of SQL Server version upgrade.

6. Distribution database

The distribution database exists only if the instance is configured as a replicated distributor. All the metadata in the replication and the various types of history are stored in this database.

Iv. composition of the database

The database consists primarily of files and filegroups. All data and objects in the database (tables, stored procedures, triggers, and so on) are stored in the file.

1. Documents

Primary data file: The initialization information that holds the data and the database. There is only one primary data file per database, and the default extension is. mdf.
Secondary data file: All data files that are accidentally stored in addition to the primary data file. Some databases may not have secondary data files, or there may be multiple secondary data files, with the default extension of. ndf.
Thing log file: Holds all the log information used to recover the database. Each database has at least one thing log file, or it can have multiple things log files, the default extension is. ldf.

2. File groups
Primary filegroup: Contains the primary data file and any files that are not explicitly assigned to other filegroups. All pages of the system table are assigned to the primary filegroup.
User-defined filegroup: primarily in the CREATE DATABASE or ALTER DATABASE statement, the filegroup specified by the FILEGROUP keyword is used.

V. Create a database
When you create a database, include general, options, and filegroups that are general used to set the name size location of the database, options, and some options in the filegroup that define the database, display the statistics for files and filegroups, by default.
SQL Server creates a primary filegroup by default to hold several data files, but the log does not have a filegroup.
The data file for the SQL Server database is divided into logical names and house names. A logical name is the name used when referencing a file in an SQL statement, and the physical name is used to manipulate the management system.

Maintaining a SQL Server database

One, offline and online

Right-click Offline database, copy right-click online and finish

Ii. Separation and attachment

Detaching is not deleting the database but removing it from the server. exec sp_detach_db @dbname = ' webdb '

Database--Right-click Detach--when needed, then right-click on attach. Create Database WebDB on (filename= ' C:\webDB.mdf '), (c:\webDB.ldf) for attach

III. Import and Export

Database, right-click Import, select Data source, complete.

Database, right-click Export, enter the server name, account password, select Export method, complete.

Iv. Backup and Recovery

Complete backup, right-click Backup, Database.

Database--Right-click Recovery, select Bak file, complete overwrite.

V. Shrinking databases and files

Because the database space allocation is based on the existing allocation, after the use of the mechanism, so the database can be used in the process of unnecessary space. Shrinking the database feature allows you to shrink each file in the database.
Both the database and log files can be shrunk, and the amount can be shrunk automatically by hand.
Auto Shrink: Database--Right-click Properties--Auto shrink---finish.
Manual shrink: Database---right-click Task shrinking--Database/file--OK.

Vi. generating and executing SQL scripts

Generate SQL Script for database: Database--write script->create.
Generate a data table SQL script: Data table--script->create table-> complete.
Execute SQL Script: New query, Input script, execute.

Data Sheet

A database consists of multiple tables of data, each of which consists of rows and columns

First, the data type

1. Basic Data type

Exact numeric types: bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney.

Floating-point data type: Freal, float.

Character data types: text, ntext, char, nchar (n), varchar, nvarchar (n).

Binary data type:binary, varbinary, image.

datetime data type: Time, date, smalldatetime,datetime,datetime2, DateTimeOffset.

2. User-defined data type

2.1 Programmable type, user-defined data type, right-click New data type.

2.2 Exec sp_addtype Code_name, ' char (8) ', ' NOT NULL '

Ii. Data integrity of data tables

Each column in the table has a set of properties, such as name, data type, data length, and whether it is empty.

1. Null and non-null values (Null/not NULL): Defines whether NULL is allowed

2. Default value: Default value specified.

3. Identity attribute: Automatically creates successive incrementing numbers for inserting new rows into the table, because identity values are typically unique, so identity columns are typically defined as primary keys (integer data types, identity columns cannot be empty).

4. Constraints: Used to define how database integrity is automatically enforced, and constraints take precedence over the use of triggers, rules, and default values.

Non-null: The user must enter a value. There can be multiple non-null constraints.

Check: Used to specify a Boolean operation that restricts the values entered into the table.

Uniqueness: The user's application enters a unique value into the column, and the value cannot be duplicated, but can be empty.

Primary key: Entity integrity can be guaranteed, a table can have only one primary key, and the primary key cannot be empty.

FOREIGN key: Controls the data stored in the External key table. A table can have more than one foreign key.

III. Management Data sheet of Enterprise Manager

CREATE TABLE: Database, table, right-click New table, input column name, select data Type---save input table name.

Modify Table: Database---table--right-click Design.

Rename: Database, table->F2.

Delete Table: Database---table--right-click Delete.

Allow null---yes/No for column properties, NOT NULL constraints, and columns.

The primary key--------right-------is completed with the main key.

Unique constraints, columns, index/Key----complete.

CHECK constraints----right-click->check constraints----finish.

Default constraint, column, and column properties, default.

Select a FOREIGN key table column, such as the table and column specifications,, right-and-down relations, and columns, foreign KEY constraints.

Iv. creation and maintenance of the relationship

SQL Server is a relational database management system (relational datable Management System,rdbms), and when a database contains more than one table, you need to establish a relationship between the tables through the primary key.

Primary table user from table work Main Table wid right-link relationship-key work ID foreign key user wid.

V. Computed columns

Column properties, calculated column specification, formula.

5. SQL Server Database