Shulou(Shulou.com)06/01 Report--

This article "SQL Server database basic concepts, composition, commonly used objects and constraints are what" most people do not understand the knowledge points of the article, so Xiaobian summarized the following content for everyone, detailed content, clear steps, with a certain reference value, I hope you can read this article to gain something, let's take a look at this article "SQL Server database basic concepts, composition, commonly used objects and constraints are what" article bar.

Basic concepts 1. Database

Database (DB): The abbreviation of DataBase is a warehouse that organizes, stores and manages data according to a certain data structure. A collection of related data stored together. Think of a database as a file cabinet containing multiple folders, which in turn contain multiple files.

The database can be divided into two types according to its different functions:

Relational database. It is a database based on relational model, which deals with the data of database by means of collection, mathematics and other concepts and methods. SQL Server, MySQL, Access, etc.

Non-relational database. Non-relational databases are also known as NoSQL databases, which means "Not Only SQL" and refers to non-relational databases, not "NO SQL." The non-relational database is not a complete negation of relational database, but an effective supplement to traditional database.

2. Database Management System

Database Management System (DBMS): Abbreviation for DataBase Management System, a computer software system designed to manage databases. Is used to operate and manage large database software, used to build, use and maintain databases. It manages and controls the database uniformly to ensure the security and integrity of the database. Users can access data in the database through DBMS, and database administrators can also maintain the database through DBMS. It can support multiple applications and users to create, modify and query databases in different ways at the same time or at different times. Most DBMSs provide DDL (Data Definition Language) and DML (Data Manipulation Language) for users to define the schema structure and permission constraints of the database and to implement operations such as adding and deleting data.

3. Database system

Database system is composed of database and database management system.

II. Composition of the database

Database exists in the form of files and consists of files and file groups.

1. Documents

Database files are classified into the following types:

Main data file: The main data file contains data and some initialization information of the database. Each database has only one primary data file. The primary data file has a.mdf extension.

Secondary data file: The secondary data file contains all data except the primary data file. Secondary data files are not required and can be omitted. If there are secondary data files, there can be one or more. If the database data is very large, this time you can put some data into the secondary data file inside. Secondary data files have a.ndf extension.

Transaction log file: The transaction log file stores all log information about operations performed on the database. Each database must have at least one log file, and there can be multiple log files. Transaction log files have the.ldf extension.

2. File group

File group is a logical management unit of database files, which divides database files into different file groups, which is convenient for us to allocate and manage files. File groups are divided into two types:

Primary: Contains primarily primary data files and files that are not explicitly assigned to other filegroups.

User-defined filegroups: We use the fileGroup keyword to specify filegroups when creating or modifying databases using scripts.

A file can belong to only one file group.

Design principles for file groups:

A file can only be a member of one filegroup.

A file or file group can belong to only one database, and cannot belong to multiple databases at the same time.

Data and log information cannot belong to the same file or filegroup.

Log files cannot be part of a filegroup. Because data and logs are managed separately, we can backup only data files or log files when backing up, and restore only data files or log files without backing up the entire database.

III. Common Database Objects

This article focuses on common objects in databases.

1. Table

A table is an object that contains all the data in a database, consists of rows and columns, and is used to organize and store data.

2. Field

Fields are columns in a table. A table can have multiple columns or only one.

Fields also have their own attributes: column name, data type (which determines what type of data the field stores).

3. View

There can be multiple tables in the database, and there can be multiple fields in the table. There may also be relationships between tables. A view can also be thought of as a table, but the view is not a real table, but a virtual table. It can query from a table or multiple tables, extract the required fields, and these fields will be combined into a virtual table. It is a way for users to view data. Both structure and data are built on queries to tables.

4. Index

Indexing is built to provide users with a way to quickly access data. Indexing is table dependent. We create indexes on tables. After creating the index, we can traverse the data without a comprehensive query of the entire table, but can quickly locate the query through the index. It can be understood as a directory inside a Word document.

5. Storage process

Stored procedure is a set of SQL statements to complete a specific function. Stored procedure can have only one query statement or multiple query statements. It can be a batch insertion operation. It can also have insert, modify, query and other statements. After the stored procedure is established, it needs to be generated and compiled, and after compilation, it will be stored in the database. When it is used later, it can be called directly. Stored procedures only need to be compiled once.

6. Trigger

A user-defined collection of SQL transaction commands that perform a variety of operations. The database is table specific. Triggers are automatically executed when an add, delete, or change operation is performed on a table.

7. Restrictions

A constraint can be understood as a restriction on the column values in a data table. This can be a format constraint or a value range constraint. When we define this restriction on a column of a data table, if we insert data into the table, the inserted value does not meet this restriction and prevents the insertion of data. Columns in tables could be better normalized.

8. Default values

Default values are equivalent to assigning a default value to a column in a table. When inserting data, if no value is inserted for this column, it is automatically populated with preset default values.

IV. Database constraints I. Definitions

Constraints: Rules that specify data in a table. If there is data behavior that violates the constraint, the behavior is blocked.

If you are using DBMS tools to create tables, constraints must be created after the tables are created. If you are using scripts to create tables, you can create constraints right in the script.

II. Classification 1. Primary key constraint

Primary key constraint: primary key constraint, unique, non-empty, and cannot be modified.

2. Foreign key constraints

Foreign key constraint: used to strengthen the connection between one or more columns of data in two tables. First, you need to establish the primary key in the primary table, and then define the foreign key in the foreign key table.

Note: Only primary key columns in the primary table can be used as foreign keys by the secondary table; other columns cannot be used as foreign keys. The value of the foreign key in the slave table must be the value of the primary key in the master table, which restricts updates and inserts from the slave table. When deleting a piece of data in the primary table, if the primary key of the piece of data has a reference in the secondary table, the reference data in the secondary table must be deleted first before the data in the primary table can be deleted. If the data is not referenced in the slave table, it can be deleted directly.

3. Unique constraints

Unique: Unique constraints. Make sure that no column of data in the table has the same value. Similar to, but different from, primary key constraints. A table can have only one primary key constraint, and uniqueness constraints can have multiple or none. And the uniqueness constraint is not a combination of one or more columns of the primary key. Unique keys can be null, but primary keys cannot.

4. Check constraints

Check constraints: We can use logical expressions to determine the validity of data, which are used to limit the range of values entered in one or more columns.

5. Default constraint

Default constraint: Default constraint. When the user inserts a new row of data, if the row does not specify data, the system assigns default values to the column. If no default value is set, the system sets the column to null.

The above is about "SQL Server database basic concepts, composition, common objects and constraints are what" this article content, I believe everyone has a certain understanding, I hope the content shared by Xiaobian is helpful to everyone, if you want to know more related knowledge content, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.