SQL Server database programming knowledge Summary

SQL Server is a relational database management system (DBMS) developed and promoted by Microsoft. It was initially developed by Microsoft, Sybase, and Ashton-Tate, the first OS/2 version was released in 1988. It is only one of many relational database management systems. Other relational database management systems include server, Oracle, DB/2, Sybase, Informix, and relational database: Access, Foxpro, and DBASE. Today, databases have become one of the core courses for learning software development. Almost all software involves databases, and many data must exist in databases. Therefore, in order to better master and operate the database, it is particularly important to encapsulate the database knowledge.

 

●Relational and non-relational databases

 

A relational database is a database created on the basis of a relational model. It consists of one or more two-dimensional relational data tables. It defines the structure of a database by establishing the relationship between tables. The most important thing in a relational database is a data table. A data table lists the associated data in rows and columns to describe a physical object. A two-dimensional table is called a link. Each link can contain multiple attributes.

Compared with relational databases, non-relational databases are especially suitable for Web 2.0 applications represented by SNS. These applications require high-speed concurrent read/write operations, but do not require high value consistency. Relational databases have paid a significant price to maintain transaction consistency, leading to a decline in their read/write capabilities. As the network SNS applications have extremely high requirements on concurrent read/write capabilities, relational databases cannot cope with such problems, A new structured data storage must be used instead of relational databases. Another feature of relational databases is that they have a fixed table structure, so their scalability is very poor. In SNS, system upgrades and feature increases often mean huge changes to the data structure, this is also difficult for relational databases and requires new structured data storage. As a result, nosql came into being. Non-relational databases usually do not have a fixed table structure. Strictly speaking, they are not a type of database. They should be a set of structured data storage methods.

 

●SQL Server database Basics

 

First, let's look at the relationship between them.

1. Comparison of T-SQL and SQL

SQL is fully called structured query language. It is a standard language for relational databases. It consists of three languages: Data Definition Language, data manipulation language, and data control language. T-SQL is an enhanced version of the SQL language.

That is to say, SQL is applicable to all relational databases, but it has its own limitations because it only provides commands such as adding, deleting, modifying, and querying database data, when developing database programs with complex structures, only the SQL language is far from enough, so the major companies have enhanced it on the basis of SQL. The T-SQL is MS and Sybase in
Based on the DDL and DML of SQL, extended functions, system pre-stored programs, and program design structures (such as if and
While) makes programming more flexible. The enhanced SQL language, similar to the T-SQL, also has Oracle's extended PL/SQL for SQL.

Ii. database objects

The database object defines the structure of the database content. They are included in database projects. database projects can also contain data generation plans and scripts. The fastest way to learn SQL Server is to learn database objects first. After studying the similarities and differences between them, I divided database objects into three types.

These three types of database objects may overlap functions.

1. Table-based Objects

Table. The data is stored in the table.

Table-based objects are classified into three types:

View is used to filter out the information you want. It is a virtual table that only encapsulates statements to ensure data security in the table;

An index is used to sort the values of one or more columns in a database table. A pointer is provided to point to the data values of the specified columns in the table, sort these pointers according to the specified sorting order. The indexing method used by databases is similar to the directory used by books;

Constraints, default values, rules, and triggers ensure data integrity.

2. Database Security: Login applications in the user, role, permission, and database security can restrict operations that can be performed by the login user to ensure database security.

3. Block: the stored procedure is similar to the function, but the stored procedure can return A recordset. The trigger is a special stored procedure.

Iii. DTS data conversion Service

Main functions of DTs: 1. Import and export data

2. Data Conversion (inspection, purification, and reorganization)

3. Convert database objects

Iv. Management and Maintenance

This part mainly protects and manages database files.

1. Database Files

 

Database files are files that store database data and database objects. A database can have one or more database files. A database file only belongs to one database. When there are multiple database files, one file is defined as the master database file with the extension. MDF, which is used to store the database startup information and part or all of the data. A database can only have one primary database file. Other database files are called secondary database files. The extension is. NDF, which is used to store other data that is not stored in the primary file.

Multiple database files are combined into one file group. The main file group is composed of the main files. This file group is composed of these files, but the transaction log files do not belong to any file group.

2. Transactions and locks

Transaction

A transaction is a unit of concurrency control and a sequence of user-defined operations. These operations are either done or not done. They are an inseparable unit of work. Through transactions, SQL Server can bind a set of logical operations to ensure data integrity on the server.

Transactions generally start with begin transaction and end with commit or rollback.
Commit indicates commit, that is, all operations to commit a transaction. Specifically, all the updates to the database in the transaction are written back to the physical database on the disk, and the transaction ends normally.
Rollback indicates rollback, that is, a fault occurs during the transaction operation, and the transaction cannot continue. The system revokes all the completed operations on the database in the transaction, roll back to the starting state of the transaction.

Ø lock

The database uses the locking mechanism to solve the concurrency problem .. There are two types of locks: Shared locks and exclusive locks (also called exclusive locks ).

From the programmer's perspective, there are two types of locks:

Optimistic lock: Optimistic lock assumes that when processing data, you can lock the record directly without doing anything in the application code, that is, you can manage the lock completely by relying on the database. Generally, when a transaction is processed
The server automatically locks the table updated within the transaction processing range.

Pessimistic lock: Pessimistic locks do not catch a cold in Automatic Management of database systems. Programmers need to directly manage data or lock processing on objects, and obtains, shares, and waives any lock on the data being used.

 

●Application Instance

/* -- Create a database named company, create three tables priduct, project, and tblsales, and create default values, rules, triggers, and stored procedures, users, roles, and permissions are used to ensure database security. */If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [fk_priduct_project] ') and objectproperty (ID, n'isforeignkey') = 1) alter table [DBO]. [priduct] Drop constraint fk_priduct_projectgoif exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [getavgpbiaodi] ') and objectproperty (ID, n' isprocedure') = 1) Drop procedure [DBO]. [getavgpbiaodi] goif exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [pinfo5000] ') and objectproperty (ID, n' isprocedure') = 1) Drop procedure [DBO]. [pinfo5000] goif exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [pro] ') and objectproperty (ID, n' isprocedure') = 1) Drop procedure [DBO]. [pro] goif exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [sysconstraints] ') and objectproperty (ID, N 'isview') = 1) Drop view [DBO]. [sysconstraints] goif exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [priduct] ') and objectproperty (ID, N 'isusertable') = 1) Drop table [DBO]. [priduct] goif exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [Project] ') and objectproperty (ID, n' isusertable') = 1) Drop table [DBO]. [Project] goif exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [tblsales] ') and objectproperty (ID, N 'isusertable') = 1) Drop table [DBO]. [tblsales] goif exists (select * From DBO. policypes where name = n 'tele') exec sp_droptype n 'tele' goif exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [rule_position] ') and objectproperty (ID, N 'isrule') = 1) Drop rule [DBO]. [rule_position] goif exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [@ position] ') and objectproperty (ID, n' isdefault') = 1) Drop default [DBO]. [@ position] gocreate default [@ position] as 'others' 'gocreate rule [rule_position] As @ postion in ('Project Manager', 'directory', 'account ', 'employee ', 'others') gosetusergoexec sp_addtype n 'tele', n'smallint', n' not null' gosetusergocreate table [DBO]. [priduct] ([product ID] [int] Null, [product name] [char] (20) Collate chinese_prc_ci_as null) on [primary] gocreate table [DBO]. [Project] ([Project No.] [int] not null, [project name] [char] (10) Collate chinese_prc_ci_as not null, [start date] [datetime] Null, [expected date] [int] Null, [Customer ID] [int] Null, [owner ID] [int] Null, [project subject] [int] Null) on [primary] gocreate table [DBO]. [tblsales] ([number] [int] not null, [name] [varchar] (20) Collate chinese_prc_ci_as not null, [sex] [char] (2) Collate chinese_prc_ci_as null, [birthday] [datetime] Null, [salary] [money] Null) on [primary] gosetusergoexec sp_bindefault n' [DBO]. [@ position] ', n' [project]. [Project subject] 'goexec sp_bindrule n' [DBO]. [rule_position] ', n' [project]. [Project name] 'gosetusergoset quoted_identifier on goset ansi_nulls on go --/****** encrypted object is not transferable, and script can not be generated. * *****/goset quoted_identifier off goset ansi_nulls on goset quoted_identifier on goset ansi_nulls on gocreate procedure getavgpbiaodi @ name varchar (10 ), @ avgpbiaodi int outputas declare @ errorsave intset @ errorsave = 0 select @ avgpbiaodi = AVG (project subject) from project as P inner join pmanager as PM on p. owner ID = PM. owner idwhere PM. name = @ nameif (@ error <> 0) set @ errorsave =@@ errorreturn @ resolve employee off goset employee on goset quoted_identifier on goset employee on gocreate procedure pinfo5000as select * from project where project subject> = 5000 order by project subject descgoset quoted_identifier off goset ansi_nulls on goset quoted_identifier on goset ansi_nulls on gocreate procedure pro @ N1 int, @ N2 int, @ N3 int, @ avreage int outputas select @ avreage = (@ N1 + @ N2 + @ N3)/3 declare @ avgscore intexec pro1, 2, 3, @ avgscore outputselect 'The score is: ', @ avgscoregoset quoted_identifier off goset ansi_nulls on go

I can only summarize so much about the basic framework of SQL Server, and I will continue to learn more in the next 1.1 drops.