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

Knowledge points related to SQLServer database

1. Why use a database?

Database technology is one of the core technologies of computer science. The use of database can store data efficiently and clearly, so that people can manage data more quickly and conveniently. The database has the following characteristics:

It can store a large amount of data and information structurally, which is convenient for users to retrieve and access effectively.

Can effectively maintain the consistency of data information. Integrity, reducing data redundancy

Can meet the sharing and security requirements of the application

2. The basic concept of database

⑴, what is data?

Data is a symbolic record that describes things, including numbers, text, graphics, sounds, images, etc.; data is stored in the database in the form of "records", and data of the same format and type will be stored together; in the database, each line of data is a "record".

⑵ what are databases and database tables?

Different records are organized together to be the "tables" of the database, that is to say, tables are used to store data, and the database is a collection of "tables".

⑶ what is a database management system?

Database management system (DBMS) is a system software that realizes the effective organization, management and access of database resources. With the support of the operating system, it supports all kinds of operations on the database. DBMS has the following main functions:

Database establishment and maintenance functions: including the establishment of database structure and data input and conversion, database dump and recovery, database reorganization and performance monitoring and other functions

Data definition function: including defining global data structure, local logical data structure, storage structure, security mode and data format and other functions. Ensure that the data stored in the database is correct, effective and compatible to prevent inappropriate error data from being input or output

Data manipulation function: including data query statistics and data update

Database operation management function: this is the core part of the database management system, including concurrency control, access control, database internal maintenance and other functions.

Communication function: communication between DBMS and other software

⑷ what is a database system?

The database system is an one-man-one-machine system, which is composed of hardware, operating system, database, DBMS, application software and database users.

⑸ Database Administrator (DBA)

Generally responsible for database update and backup, database system maintenance, user management, to ensure the normal operation of the database system.

3. The development process of database.

Primary stage-first generation database: at this stage, IMS, a hierarchical model database management system developed by IBM, came out.

Intermediate stage-the emergence of relational database: the advent of DB2, the emergence of SQL language

Advanced stage-advanced database: the emergence of various new databases, such as engineering database, multimedia database, graphic database, intelligent database, etc.

4. Three models of database

Mesh model: data relations many-to-many, many-to-one, more complex

Hierarchical model: similar to the relationship between superiors and subordinates of the company

Relationship model: entities (things that realize the world, such as × ×, bank accounts)-relationships

5. Today's mainstream database

SQLServer:Microsoft 's database products run on windows systems.

Oracle: Oracle products; representative of large databases, supporting linux and unix systems.

Degacot of DB2:IBM Company put forward the theory of relational model, and 13 years later IBM's DB2 came out.

MySQL: now acquired by Oracle. Runs on linux, with Apache and Nginx as Web servers, MySQL as background database, and PHP/Perl/Python as script interpreter to form a "LAMP" combination

6. Relational database

Basic structure of ⑴

The storage structure used in relational database is multiple two-dimensional tables, that is, the data description that reflects things and their relations is reflected in the form of flat tables. In each two-dimensional table, each row is called a record and is used to describe the information of an object: each column is called a field and is used to describe an attribute of the object. There are corresponding associations between the data table and the database, which are used to query the relevant data. A relational database consists of associations between data tables. Where:

A data table is usually a two-dimensional table composed of rows and columns, each of which describes the objects and their properties of a particular aspect or part of the database.

A row in a data table is usually called a record or tuple, which represents one of many objects with the same properties

Columns in a data table are often called fields or properties, which represent common properties of objects stored in the corresponding database

⑵ primary key and foreign key

Primary key: it uniquely identifies the row data in the table, with a primary key corresponding to a row of data; the primary key can be composed of one or more fields; the value of the primary key is unique and is not allowed to be null; only one primary key is allowed in each table.

Foreign key: a foreign key is a column or columns used to establish and strengthen a link between two table data; a relational database usually contains multiple tables that can be associated with foreign keys.

⑶ data integrity rules

Entity integrity rules: tuples in relationships are required to have no null on the attributes of the primary key

Domain integrity rules: specify whether a dataset is valid for a column or determine whether null is allowed

Referential integrity rules: if two tables are associated, referential integrity rules require that references to tuples that do not exist are not allowed

User-defined integrity rules

7. SQLServer system database

Master database: records system-level information, including all user information, system configuration, database file location, and other database information. If the database is damaged, the entire database will be paralyzed and unusable.

Model databases: database templates for

Msdb database: for SQLServer agents to schedule alerts and jobs

Tempdb database: temporary file storage location

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.