SQL Server database-related knowledge points
1. Why should I use a database?
Database technology is one of the core technologies of computer science. Using a database allows you to store data efficiently and coherently, enabling people to manage data more quickly and easily. The database has the following characteristics:
• Can be structured to store large amounts of data information to facilitate effective user search and access
• Can effectively maintain the consistency of data information. Integrity, reduce data redundancy
• Can meet the sharing and security requirements of the application
2, the basic concept of the database
⑴ What is data?
Data is a symbolic record describing things, data including numbers, text, graphics, sounds, images, etc.; data is stored as "records" in the database, and data of the same format and type are stored together; In a database, each row of data is a "record."
⑵ What are database and database tables?
Different records are organized together as the "tables" of the database, and the She used table is where the data is stored, and the database is the collection of "tables".
⑶ What is a database management system?
Database management System (DBMS) is a system software that realizes the organization, management and access of database resources effectively. It supports various operations of the database with the support of the operating system. The DBMS mainly has the following functions:
• Database establishment and maintenance functions: including the establishment of database structure and data input and conversion, database dump and restore, database reorganization and performance monitoring functions
• Data definition capabilities: Includes defining global data structures, local logical data structures, storage structures, security patterns, and data formats. Ensure that the data stored in the database is correct, valid, and compatible to prevent non-semantic error data from being entered or output,
• Data manipulation features: Two aspects including data query statistics and Data update
• Database operation management function: This is the core of database management system, including concurrency control, access control, database internal maintenance and other functions
• Communication functions: Communication between the DBMS and other software
⑷ What is a database system?
Database system is a one-person one-machine system, which consists of hardware, operating system, database, DBMS, application software and database user.
⑸ 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 the database
• Primary stage-first generation database: At this stage IBM developed a hierarchical model of the database management system-ims the advent of
• Intermediate stage-The emergence of relational databases: the advent of DB2, the generation of SQL language
• Advanced Stage-Advanced database: The generation of new types of 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 company-subordinate relationship
• Relational Models: entities (things to achieve the world, such as XXX, bank accounts)-relationships
5. Current Mainstream database
· Sqlserver:microsoft the company's database products, running on Windows systems.
· Oracle: Oracle's products, representative of large databases, support for Linux, Unix systems.
· DB2:IBM Company's Dega Cauter put forward the relationship model theory, 13 years later IBM's DB2 came out
· MySQL: Now acquired by Oracle Corporation. Run on Linux, Apache and Nginx as a Web server, MySQL as a background database, Php/perl/python as a script interpreter to form a "LAMP" combination
6. Relational database
⑴ Basic Structure
The storage structure used by the relational database is a two-dimensional table, that is, the data description reflecting the thing and its relation is embodied in the form of a flat table. In each of the two-dimensional tables, each row is called a record that describes the information for an object: Each column is called a field that describes a property of the object. There is a corresponding association between the data table and the database, which is used to query the relevant data. Relational databases are made up of associations between data tables. which
• A data table is typically a two-dimensional table of rows and columns, each of which describes a particular aspect or part of an object in a database and its properties
• Rows in a data table are often called records or tuples, which represent one of many objects with the same attributes
• A column in a data table is often called a field or property, which represents the shared properties of the object stored in the corresponding database
⑵ primary KEY and foreign key
Primary key: Is the row data that uniquely identifies the table, one row of data for a primary key, one or more fields for the primary key, uniqueness for the primary key, not allowed for control (null), and only one primary key per table allowed.
FOREIGN key: A foreign key is a column or columns used to establish and strengthen a link between two table data; A relational database typically contains multiple tables, and foreign keys can correlate those tables.
⑶ Data integrity rules
• Entity Integrity rules: requires that tuples in a relationship cannot have null on the primary Key's properties
• Domain Integrity rules: Specifies whether a dataset is valid for a column or determines whether NULL is allowed
• Referential integrity rules: If two tables are associated, referential integrity rules require that references to non-existent tuples are not allowed
• User-defined integrity rules
7. SQL Server 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 disabled.
Model database: Database template
MSDB database: For SQL Server Agent scheduled alerts and jobs
tempdb database: Temporary file storage location
Getting Started with SQL Server database
