Description of the SQLServer field type

Bit The bit data type is an integer. The value can be 0, 1, or null. This data type is used to store data with only two possible values, such as Yes or No, True or False, and On or Off. Note: this is a very space-efficient data type and should be used as much as possible if it meets your needs.

Tinyint Integer The tinyint data type can store integers from 0 to 255. It is useful when you only intend to store a finite number of values. This data type occupies 1 byte in the database. Note: If the bit type is too boring for your needs, you may want to use tinyint because this type is also relatively safe and does not accept the embedding of malicious script content.

Smallint The Smallint data type can store integers between the 15th power of -2 (-32768) and the 15th power of 2 (32767). This data type is useful for storing numeric data that is often limited to a specific scope. This data type takes up 2 bytes of space in the database. Note: If tinyint is too monotonous for your needs, you can consider using Smallint because it is also relatively safe and does not accept the embedding of malicious script content.

Int The int data type can store integers from the 31st power of -2 (-2147483648) to the 31st power of 2 (2147483 647). Almost any numeric data stored in a database can use this data type. This data type occupies 4 bytes in the database. Note: If Smallint is not enough for your needs, you can consider using a larger int.

Decimal Exact numeric type Decimal data types can be used to store numeric data with fixed precision and range from the 38th power of -10 to the 38th power of 10. When using this data type, you must specify the range and precision. The range is the total number of digits that can be stored around the decimal point. The precision is the number of digits of the number stored to the right of the decimal point. Numeric The numeric numeric data type is similar to Decimal.

Smallmoney The Smallmoney data type is used to represent money and currency values. This data type can store data between -214748.3648 and 214748.3647, accurate to one thousandth of a monetary unit. Money The money data type is used to represent money and the value of a currency. This data type can store data ranging from -922 billion to 922 billion, accurate to one ten-thousandth of a monetary unit

A float data type is an approximate numeric type used for floating point numbers. Floating-point numbers are approximate because not all numbers in their range can be represented exactly. Floating point numbers can be anything from -1.79e +308 to 1.79E+308

Real Approximate numeric type The real data type, like floating point, is an approximate numeric type. Smalldatetime The Smalldatetime data type is used to represent the date and time from January 1, 1900 to June 6, 2079, accurate to one minute

Datetime Datetime data type is used to represent the date and time. This data type stores all date and time data from January 1, 1753, to December 31, 9999, accurate to one hundredth of a second, or 3.33 milliseconds.

Cursor Special data type A CURSOR data type is a special data type that contains a reference to a cursor. This data type is used in stored procedures and cannot be used when creating tables

Timestamp Special data type THE TIMESTAMP data type is a special data type used to create a database-wide unique digit. There can only be one TIMESTAMP column in a table. The value of the TIMESTAMP column changes each time a row is inserted or modified. Despite the “time” in its name, the TIMESTAMP column is not a date that people recognize. In a database, the timestamp value is unique

Uniqueidentifier special data type the Uniqueidentifier data type is used to store a globally Uniqueidentifier, the GUID. Guids are indeed globally unique. This number has little chance of being reconstructed in another system. Columns with unique identifiers can be initialized using the NEWID function or by converting a string to a unique identifier.

Char The CHAR data type is used to store data of a specified length in a non-uniform encoding. When defining a column of this type, you must specify the column length. This data type is useful when you always know the length of the data to store. For example, when you store data by zip code plus four characters, you know that 10 characters are always needed. The column width of this data type is up to 8000 characters.

Varchar Character type VARCHAR data type. Like char, it is used to store character data of a non-uniform encoding. Unlike char, this data type is variable-length. When defining a column of the data type, you specify the maximum length of the column. The biggest difference from the CHAR data type is that the stored length is not the column length, but the length of the data.

Text Character type The text data type is used to store large amounts of non-uniform encoding character data. This data type can have up to 231-1 or 2 billion characters.

Nchar The nCHAR data type is used to store the data of the unified encoding character of fixed length. Uniform encodings store each character in a two-byte structure instead of a single byte (as is the case in plain text). It allows for a large number of extended characters. This data type can store 4000 characters, doubling the amount of byte space used.

Nvarchar Unified encoding character The NVARCHAR data type is used for variable length unified encoding character data. This data type can store 4000 characters, doubling the amount of byte space used.

Ntext Unicode character type The NTEXT data type is used to store a large amount of unicode character data. This data type can store 230 to 1, or nearly 1 billion characters, and doubles the amount of byte space used

Binary The binary data type is used to store binary data of fixed length up to 8000 bytes. You should use this data type when the contents of the input table are close to the same length.

Varbinary The varbinary data type is used to store variable binary data up to 8000 bytes long. You should use this data type when the contents of the input table are of variable size

Image Binary data type The image data type is used to store variable-length binary data, up to 231-1 or about 2 billion bytes

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — – (1) binary data types Binary data including Binary, Varbinary and Image Binary The data type can be either fixed length (Binary) or variable length. Binary[(n)] is fixed n bits of Binary data. Where n ranges from 1 to 8000. Its memory space is n + 4 bytes in size. Varbinary[(n)] is n bits of variable-length binary data. Where n ranges from 1 to 8000. Its memory space is n + 4 bytes, not N bytes. Data stored in the Image data type is stored as a bit string, not interpreted by SQL Server, and must be interpreted by the application. For example, an application can store data in the Image data type using BMP, TIEF, GIF, and JPEG formats.

(2) Character data type

The types of character data include Char, Varchar, and Text character data is any arbitrary combination of letters, symbols, and numbers. Varchar is variable length character data, the length of which does not exceed 8KB. Char is fixed-length character data with a maximum length of 8KB. ASCII data larger than 8KB can be stored using the Text data type. For example, because Html documents are all ASCII characters and are generally more than 8KB in length, they can be stored as Text data in SQL Server.

(3)Unicode data types

Unicode data types include Nchar,Nvarchar, and Ntext. In Microsoft SQL Server, traditional non-Unicode data types allow the use of characters defined by a particular character set. You can select a character set during SQL Server installation. Using Unicode data types, any character defined by the Unicode standard can be stored in a column. In the Unicode standard, all characters defined in various character sets are included. Using Unicode data types defeats twice as much space as using non-Unicode data types. In SQL Server, Unicode data is stored as Nchar, Nvarchar, and Ntext data types. Columns stored using this character type can store characters from multiple character sets. When the length of the column changes, the Nvarchar character type should be used, which can store up to 4000 characters. The Nchar character type should be used when the length of the column is fixed, again, up to 4000 characters can be stored. When using the Ntext data type, this column can store more than 4000 characters.

(4) Date and time data types

Date and time Data types include Datetime and Smalldatetime date and time A data type consists of a valid date and time. For example, valid date and time data include “4/01/98 12:15:00.00:00:00 PM” and “1:28:29:15:01AM 8/17/98”. The first datatype is date first, and the second datatype is flash time first, and the second datatype is date first. In Microsoft SQL Server, the date and time data types include Datetime and Smalldatetime, and the date range is from January 1, 1753. Ends on December 31, 9999 (8 bytes per value required). When using the Smalldatetime data type, the range of stored dates starts on January 1, 1900 and ends on December 31, 2079 (each value requires 4 bytes of storage). The date format can be set. Set the date format command is as follows: Set the DateFormat {format | @ format _var | among them, the format is | @ format_var date order. Valid parameters include MDY, DMY, YMD, YDM, MYD and DYM. By default, the date format is MDY. For example, after the Set DateFormat YMD command is executed, the DateFormat is year, month and day. When Set DateFormat DMY is executed, the DateFormat is day, month and year

(5) Digital data type

Numeric data contains only numbers. Numeric data types include positive and negative numbers, decimals (floating point numbers), and integers Integers consist of positive and negative integers, such as 39, 25, 0-2, and 33967. In Micrsoft SQL Server, integers store data of the types Int, Smallint, and Tinyint. The range of data stored by the Int data type is larger than that stored by the Smallint data type, while the Smallint data type is larger than that stored by the Tinyint data type. Data stored with Int data can range from -2 147 483 648 to 2 147 483 647 (each value requires 4 bytes of storage space). When the Smallint data type is used, data can be stored in a range from -32 768 to 32 767 (each value requires 2 bytes of storage). When using the Tinyint data type, data is stored from 0 to 255 (each value requires 1 byte of storage). Precise small data in SQL Server are of the data types Decimal and Numeric. The storage space occupied by such data is determined by the number of bits following the number of bits of the data. In SQL Server, the data types for nearly fractional data are Float and Real. So for example, let’s call this fraction 1/3. 3333333, accurately represented when using approximate data types. Therefore, the data retrieved from the system may not be exactly the same as the data stored in the column.

(6) Monetary data represents the positive or negative amount of money.

In Microsoft SQL Server, the data types of currency data are Money and Smallmoney. The Money data type requires 8 bytes of storage, and the Smallmoney data type requires 4 bytes of storage.

(7) Special data types

Special data types include data types not mentioned earlier. There are three special data types, namely Timestamp, Bit, and Uniqueidentifier. Timestamp is used to indicate the sequence of SQL Server activities in binary projection format. Timestamp data has no relation to insert data or date and time. Bits consist of 1’s or 0’s. The Bit data type is used when representing true or false, ON or OFF. For example, asking if a client request for each visit could be stored in a column of this data type. The Uniqueidentifier is a 16-byte hexadecimal number that represents a globally unique. Guids are useful when the record rows of a table are required to be unique. For example, using this data type in a customer id column can distinguish between different customers.

2. User-defined data types

User-defined data types are based on the data types provided in Microsoft SQL Server. User-defined data types can be used when the same data type must be stored in several tables and to ensure that the columns have the same data type, length, and nullability. For example, you can define a data type called Postal_code, which is based on the Char data type. When creating a user-defined data type, you must provide three numbers: the name of the data type, the system data type on which it is based, and the nullability of the data type.

(1) Create user-defined data types

User-defined data types can be created using Transact-SQL statements. The system stored procedure sp_addType can be used to create user-defined data types. The syntax is as follows: sp_addType {type},[,system_data_bype][, ‘null_type’] Where type is the name of the user-defined data type. System_data_type is the system-provided data type, such as Decimal, Int, Char, and so on. Null_type indicates how the data type handles NULL values. It must be enclosed by single quotes, such as’ NULL ‘, ‘NOT NULL’, or ‘NONULL’. Example: Use cust Exec SP_addType SSN, ‘Varchar(11)’,”Not Null “Creates a user-defined data type SSN based on a system data type of 11 characters. Null is Not allowed. Example: Use cust Exec sp_addtype birthday,datetime, ‘Null’ to create a user-defined data type birthday based on the system data type datetime. Null is allowed. Example: Eexc sp_addType fax, ‘varchar(24) ‘,’ Null ‘create two data types, Namely, telephone and fax

(2) Delete user-defined data types

If a user-defined data type is not needed, you can delete it. The command to drop a user-defined data type is sp_droptype {‘ type ‘}. Example: Use master Exec SP_droptype ‘SSN’ Note: A user-defined data type cannot be deleted when a column in a table is using a user-defined data type, or when a default or rule is bound to it.

Field types of the SQL SERVER

The following describes the field types of SQL SERVER7.0 or later. For details about the field types of SQL SERVER6.5, see the description provided by SQL SERVER.

Field Type Description bit Integer number 0 or 1 int Integer number from -231(-2,147,483,648) to 231(2,147,483,647) SmallInt Integer number from -215(-32,768) to 215(32,767) tinyint An integer number from 0 to 255

The precision of decimal from -1038 to 1038-1 is a synonym for the number numeric Decimal of significant digits

Money from -263(-922,337,203,685,477.5808) to 263-1(922,337,203,685,477.5807), Smallmoney Specifies the currency data between -214,748.3648 and 214,748.3647. Smallmoney Specifies the currency data between -214,748.3648 and 214,748.3647

Float from -1.79e +308 to 1.79E+308 variable precision numbers real from -3.04e +38 to 3.04e variable precision numbers

Datetime Date and time data from January 1, 1753 to December 31, 9999, in three hundredths of a second or 3.33 milliseconds Smalldatetime Date and time data from January 1, 1900 to June 6, 2079, in minutes

Timestamp timestamp, a database width unique number uniqueidentifier global uniqueidentifier GUID

Char Fixed-length non-Unicode character data, the maximum length is 8000 VARCHAR Variable length non-Unicode character data, the maximum length is 8000 TEXT Variable length non-Unicode character data, the maximum length is 2^31-1(2G).

Nchar Fixed-length Unicode character data with a maximum length of 8000 nvARCHar Variable length Unicode character data with a maximum length of 8000 ntext Variable length Unicode character data with a maximum length of 2^31-1(2G)

Variable-length binary data with a maximum length of 8000 varbinary data with a maximum length of 2^31-1(2G)

— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — – 1, a value of 0 or 1 Int Integer 4 bytes, a value of 231-231-1 Smallint 2 bytes, the value of Tinyint 1-215-215-1 byte, Values 0 to 255 Decimal (P, S) Numeric data, fixed precision P, width s Numeric Money 8 bytes, store the currency type, values -263 to 263-1 Small Money 4 bytes, store the currency type, Value -214748.3648 to +214748.3647 Approximate value data type Float (n) n between 1 and 24, 4 bytes, 7 bits precision n =1 to 7 real N between 25 to 53, 8 bytes, 15 bits precision =8 to 15 Float Datetime 8 bytes, Smalldatetime specifies the date and time of a day with an accuracy of 1/300 of a second. Smalldatetime specifies the date and time of a day with an accuracy of 1/300 of a second. Uniqueidentifier (16 bytes) used to store the unique data in the database Uniqueidentifier (16 bytes) used to store the global Uniqueidentifier (GUID) Char (n) fixed length of a non-unicode string, n=1 to 8000 Character (n) Varchar (n) variable length, Non-unicode character string N =1 to 8000 Char VARYING (N) Text Varying length non-Unicode data in the server code page. Nchar Unicode character string of fixed length n=1 to 4000 National character (n), National CHAR (n) Nvarchar Unicode string of fixed length N =1 to 4000 National CHARACTER VARYING (N) Ntext Varying length Unicode data. The maximum length of fixed-length Binary data is 230-1. The storage space is between 1 and 8000 bytes. The storage space is N +4 bytes. N =1 to 8000 Binary VARYING (N) Tmage Variable-length Binary data. The size ranges from 0 to 231-1. 1) For numeric data types, scale refers to the number of digits stored after the decimal point, and precision refers to the number of digits that can be stored, including the decimal point. 2) The storage width of money and small money is 4. 3) The timestamp column value is automatically updated when each row is updated. The timestamp column cannot be a keyword or a part of the keyword. 4) Arithmetic operators (such as +, -, etc.) cannot be used to uniquely identify data types, which can only use equality comparison operations. Unicode is the standard for storing data consistently across all character sets. It uses twice as much storage space as a non-Unicode data store.

Two: sqlserver custom function use

Three: sqlserver custom function use

Cursors can process a select result set, or return a recordset result without processing all of it. 1. Cursors are actually a mechanism for extracting one record at a time from a result set of multiple data records. The cursor can be completed: the specific line # # allows positioning to the result set from the result set the current position to retrieve one or more lines of data # support to modify the result set the current position Because the cursor is lines will record set for operation, so it adds burden to the server, usually under the condition of the complex operation results, using a cursor. SQL Server 2005 has three types of cursors: T-SQL cursor, API cursor, and client cursor.

2, the basic operation of the cursor the basic operation of the cursor is to define the cursor, open the cursor, read the cursor cycle, close the cursor, delete the cursor. A, define the cursorCopy the code

Declare cursor_name – the name of the cursor cursor [local | global] – global, local [forward only | scroll] – cursor scroll way [read_only | scroll_locks | Optimistic] – read way for select_statements – the query [for update | of column_name…]. — Alter field

Parameters: the forward only | scroll: before a parameter, the cursor can only move backward; Read_only: read-only cursor scroll_LOCKS: cursor locks. The database locks the record when the cursor is read so that the cursor can complete manipulation of the record. Optimistic: this parameter does not lock the cursor. At this point, if records are read into the cursor, the cursor will not be updated or deleted more than B, open the cursor open cursor_name; Fetch cursor_name; fetch cursor_name; fetch cursor_name; The retrieval method is as follows: Fetch first; Fetch next; Fetch prior; Fetch last; Fetch absolute n; Fetch pelative n If n is a positive integer, read the NTH record if n is negative, fetch the NTH record if n is negative, fetch pelative n If n is, read the last read record D. Close the cursor close cursor_name. E, delete cursor deallocate cursor_name; 3. Examples of cursor operationCopy the code

Create a cursor declare cursor_STu cursor Scroll for select ID, name, age from student; – Open the cursor open cursor_stu; – Declare @ID int, @name nvARCHar (20), @age vARCHar (20); – Fetch first from cursor_stu into @id, @name, @age; Print ‘print’; – While (@@fetch_status = 0) begin print ‘Id:’ + convert(char(5), @id) + ‘, name: ‘+ @name +’, type: ‘+ @age; Fetch next from cursor_stu into @id, @name, @age; End — Close area_cursor;

– Delete a cursor. – deallocate area_CURSOR;

Four: SQLServer exception handling

The sampleCopy the code

# Example: Handle error messages with exceptionsCopy the code

# Example: Error messages that exceptions can handleCopy the code

Begin try select * * from student; end try begin catch exec proc_error_info; end catch go

– A simple try catch example, which does not process errors (non-existent table objects) begin try select * from st; end try begin catch exec proc_error_info; end catch go

Exceptions do not handle compile-time errors, such as syntax errors. And errors that occur when some name objects are not properly parsed due to recompilation. # Example: cannot commit transactionCopy the code

# Example: Handle exception log informationCopy the code

– Exception and error information table if (object_id(‘ errorLog ‘, ‘U’) is not null) drop table errorLog go create table errorLog(errorLogID int primary key identity(100, 1), — Primary key of the ErrorLog line. ErrorTime dateTime default getDate(), — The date and time when the error occurred. UserName sysname default current_user, — The user that performs the batch processing for which the error occurred. ErrorNumber int, — the errorNumber of the error that occurred. ErrorSeverity int, — The severity of the error that occurred. ErrorState int, — The status number of the error that occurred. ErrorProcedure nvARCHar (126), — The name of the stored procedure or trigger in which the error occurred. ErrorLine int, — the line number on which the error occurred. errorMessage nvarchar(4000) ) go

– Stored procedures: Add exception log information if (object_id(‘ proc_add_EXCEPtion_log ‘, ‘P’) is not null) DROP PROC proc_ADD_EXCEPtion_log Go Create proc proc_add_Exception_log (@logid int = 0 output) as begin set nocount on; set @logId = 0; begin try if (error_number() is null) return;

If (xact_state() = -1) BEGIN print 'The session has an active transaction, but an error occurs that causes the transaction to be classified as an uncommitted transaction. The '+' session cannot commit a transaction or roll back to a savepoint; It can only request a full rollback of the transaction. The '+' session cannot perform any write operations until the transaction is rolled back. The session can only perform read operations before rolling back the transaction. After the '+' transaction is rolled back, the session can perform read and write operations and can start a new transaction. '; End else if (xact_state() = 0) begin print 'The session has no active transactions. '; End else if (xact_state() = 1) BEGIN print 'The session has active transactions. The session can perform any operation, including writing data and committing transactions. '; Insert into errorLog values(getDate(), current_USER, error_number(), error_severity(), error_state(), error_procedure(), error_line(), error_message()); Select @logid = @@identity; End try begin catch print 'Error adding exception log information '; exec proc_error_info; -- Displays error message return-1; end catchCopy the code

end go

Five: The use of sqlServer view

View 1. What is a View A view is a virtual table in which data records are the result of a query statement.

When creating a view, consider the following guidelines: # The name of a view must follow the rules for identifiers. The name must not be the same as the name of a table in the schema. Nesting of views is allowed, but not more than 32 layers. Views can have a maximum of 1024 fields # cannot associate rules and default with views # Queries of views cannot contain a compute clause, compute by clause, or into keyword # Queries defining views cannot contain an ORDER by clause, The name of each column in the view must be specified unless there is a top clause in the select list: # in the view of how the columns from the arithmetic expression is derived, built-in function, or constant # view there are two or more columns with the same name (usually because the view definition contains connection, so from two or more different columns with the same name) # column specifies a instead of the original column in the hope to view a different name (can rename columns in the view). The view column inherits the data type of the original column, whether or not it is renamedCopy the code

If (exists (select * from sys.objects where name = ‘v_stu’)) drop view v_stu go create view v_stu as select ID, name, age, sex from student;

4. Modify the viewCopy the code

alter view v_stu

as

select id, name, sex from student;

Alter view v_stu as select ID, name, sex from student go select * from v_stu;

select * from information_schema.views;

5. Encrypt viewsCopy the code

– Encrypted view if (exists (select * from sys.objects where name = ‘v_student_info’) drop view v_student_info go create view V_student_info with encryption — encrypt as select ID, name, Age from student go — view_definition is null select * from information_schema. Views where table_name like ‘v_stu’;

Six: the use of sqlserver transactions

Transactions have 4 properties: Atomicity, Consistency, Isolation and Durability also known as the ACID property of transactions. Atomicity: All work within a transaction is either complete or not complete, and there is no partial completion. Consistency: No subsequent operations within a transaction must violate the database subsequent constraints or rules, and the internal data structure must be correct at transaction completion. Isolation: Transactions are directly isolated from each other if there are two transactions performing operations on the same database, such as reading table data. All content seen by any one transaction is either the state before or after other transactions have completed. It is not possible for one transaction to encounter an intermediate state of another transaction. Persistence: After a transaction has completed, its impact on the database system is persistent, even if the system is faulty, the results of the transaction will remain after the system is restarted. Begin TRANSACTION, commit transaction, or rollback transaction Automatic Commit transaction An automatic commit transaction is a transaction that can be automatically executed and automatically rolled back. This mode is the default transaction mode of T-SQL. For example, when deleting a table record, if the record has a primary foreign key relationship, the delete is affected by the primary foreign key constraint and the delete is cancelled. You can set the transaction to enter implicitly: set IMPLICIT_TRANSACTION on; C, Implicit transaction An implicit transaction means that SQL Server automatically starts a transaction after the transaction is committed or rolled back. Therefore, an implicit transaction does not need to be started using begin Transaction, just a T-SQL statement that either commits the transaction or rolls back the transaction. To use it, set the set IMPLICIT_TRANSACTION on statement to turn implicit transaction mode on, and the next statement will start a new transaction, and the next statement will start a new transaction. 3, transaction processing commonly used T-SQL transaction statements: The @@tranCount global variable is used to record the number of transactions. The value of the @@error global variable is used to record the error information during the execution of the transaction. If there is no error, the transaction can be directly committed, and the error can be rolled back. B. Commit transaction rollback, indicating the end of an implicit or explicit transaction, and the changes made to the database. And subtract the value of @@trancount by 1; C, rollback TRANSACTION; rollback TRAN; Data will be rolled back to the begin tran state 4, transaction example - * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * transaction SQL * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *Copy the code

Sqlserver various query syntax

Select * from student; select * from student;

— all select all sex from student;

— select distinct sex from student;

Select count(*) from student; select count(sex) from student; select count(distinct sex) from student;

Select top 3 * from student;

– alias column name select id as id, name ‘id’, sex sex from student;

Select id, name, s.id, s.name from student s;

Select (age + id) col from student; Select s.name + ‘-‘ + c.name from classes c, student s where s.id = c.id;

Select * from student where id = 2; select * from student where id > 7; select * from student where id < 3; select * from student where id <> 3; select * from student where id >= 3; select * from student where id <= 5; select * from student where id ! > 3; select * from student where id ! < 5;

— And select * from student where id > 2 and sex = 1;

Select * from student where id = 2 or sex = 1;

– between… The and… Select * from student where id between 2 and 5; select * from student where id not between 2 and 5;

Select * from student where name = ‘%a%’; Select * from student where name = ‘%[a][o]%’; Select * from student where name not like ‘%a%’; Select * from student where name like ‘ja%’; Select * from student where name not like ‘%[j,n]%’; Select * from student where name like ‘%[j,n,a]%’; Select * from student where name like ‘%[^ja,as,on]%’; Select * from student where name like ‘%[ja_on]%’;

Select * from student where id in (1, 2);

Select * from student where id not in (1, 2);

Select * from student where age is null;

Select * from student where age is not null;

Select * from student order by name; select * from student order by name desc; select * from student order by name asc;

Select count(age), age from student group by age; select count(age) from student group by age; Select count(), sex from student group by sex; Select count(), sex from student group by sex, age order by age; Select count(), sex from student where id > 2 group by sex order by sex; Select * from data where id > 2; Select count(), (sex * id) new from student where id > 2 group by sex * id order by sex * id;

Select count(*), age from student group by all age;

Select count(*), age from student group by age having age is not null select count(*), age from student group by age having age is not null

Select count(*), sex from student group by CID, sex having CID > 1; select count(*), sex from student group by CID, sex having CID > 1;

Select count(*), age from student group by age having count(age) >= 2; select count(*) from student group by age having count(age) >= 2;

Group by CID and gender group. Filter if CID is greater than 1. Select count(*), cid, sex from student group by CID, sex having CID > 1 and Max (cid) > 2; Nested subquery A subquery is a query nested within a SELECT, INSERT, UPDATE, or DELETE statement or other subquery. Subqueries can be used anywhere expressions are allowed. Subqueries are also called internal queries or internal selections, and statements containing subqueries are also called external queries or external selections.

The from (select… Table) sample

The sample

Select count() from student where cid = classes.id as num from classes order by num;

In, not in clause query examples

Exists and not EXISTS clause query examples

Select * from student where exists (select * from classes where id = student.cid and id = 3);

Select * from student where id = student.cid; select * from student where id = student.cid; Exists and not EXISTS queries require a condition for the association between internal and external queries. If there is no condition, all information queried will be contained. Id = student.id;

Some, any, and all clause query examples

Row_number function

Select s.id, s.name, cid, c.name, row_number() over(order by c.name) as number from student s, classes c where cid = c.id;

Rank function

Dense_rank function

Given the increasing ordinal number according to the sorting clause, but there is no juxtaposition without skipping, Select s.id, S.name, CID, C. name, dense_rank() over(order by C. name) as dense from student S. classes c where cid = c.id;

Partition by grouping clause

Ntile average sorting function

The data to be sorted is bisected and then sorted equally. The parameters in ntile represent how many equal divisions. select s.id, s.name, cid, c.name, ntile(5) over(order by c.name) as ntile from student s, classes c where cid = c.id;

ø Set operation operates on two sets of query results, Select id, name from student where name like ‘ja%’ union select id, name from student where name like ‘ja%’ name from student where id = 4;

Select * from student where name like ‘ja%’ union all select * from student;

Select * from student where name like ‘ja%’ SELECT * from student;

Select * from student where name = ‘ja%’ select * from student where name = ‘ja%’ select * from student where name = ‘ja% ‘jas %’;

When the formula table expression queries the table, sometimes the intermediate table needs to be reused. These sub-queries are called by repeated queries, which is not only inefficient, but also unreadable and not easy to understand. So the formula table expression can solve this problem. We can think of formula table expressions (CET) as temporary result sets defined within the execution scope of SELECT, INSERT, UPDATE, DELETE, or CREATE View statements. — expression with statNum(id, num) as (select cid, count(*) from student where id > 0 group by cid) select id, num from statNum order by id;

with statNum(id, num) as

(

select cid, count(*)

from student

where id > 0

group by cid

)

select max(id), avg(num) from statNum;

Select s.id, s.name, c.id from student s, classes c where s.id = c.id; select s.id, s.name, c.id from student s, classes c where s.id = C.id;

Select s.id, s.name, c.id, c.name from student s left join classes c on s.id = c.id;

Select s.id, s.name, c.id from student s right join classes c on s.id = c.id; select s.id from student s right join classes C on s.id = c.id;

Select s.id, s.name, c.id, c.name from student s inner join classes c on s.id = c.id;

Select s.id, s.name, c.id, c.name from student s join classes c on s.id = c.id;

Cross join — cross join query, Select s.id, s.name, c.id, c.name from student s cross join classes c — where s.id = C.id;

* from student s, student s1 where s.id <> s1.id and s.ex = s1.sex; select * from student s, student s1 where s.id <> s1.id and s.ex = s1.sex;

Select count(age), count(age), count(age), count(age), count(age), count(age), count(age), count(age), count(age), count(age), count(age) avg(age) avg_age, sum(age) sum_age, var(age) var_age from student;

Select dateAdd(day, 3, getDate()); — select dateAdd(year, 3, getDate()); – select dateAdd(hour, 3, getDate()); Select dateDiff(day, ‘2011-06-20’, getDate()); select dateDiff(day, ‘2011-06-20’, getDate()); Select dateDiff(second, ‘2011-06-22 11:00:00’, getDate()); – Select dateDiff(hour, ‘2011-06-22 10:00:00’, getDate()); select dateName(month, getDate()); – Current month select dateName(minute, getDate()); – Select dateName(weekday, getDate()); – Select datePart(month, getDate()); – Select datePart(weekday, getDate()); — Select datePart(second, getDate()); – Current seconds select day(getDate()); – Select day(‘ 2011-06-30 ‘); – Select month(getDate()); – Return the current month select month(‘ 2011-11-10 ‘); select year(getDate()); Select year(‘ 2010-11-10 ‘); select getDate(); – Current system date select getUTCDate(); – the utc date

Select PI (); — PI function select rand(100), rand(50), rand(), rand(); – Random number select round(rand(), 3), round(rand(100), 5); Select round(123.456, 2), round(254.124, -2); Select round (123.4567, 1, 2);

Object_id (‘ student ‘), 1); Select col_name(object_id(‘ student ‘), 2); – Select col_length(‘ student ‘, col_name(object_id(‘ student ‘), 2)); – Select col_length(‘ student ‘, col_name(object_id(‘ student ‘), 1)); – Return type name, type ID SELECT type_name(type_id(‘ vARCHAR ‘)), type_id(‘ varchAR ‘); Select columnProperty(object_id(‘ student ‘), ‘name’, ‘PRECISION’); Select columnProperty(object_id(‘ student ‘), ‘sex’, ‘ColumnId’);

Select ASCII (‘ a ‘); – Character conversion ASCII value select ASCII (‘ A ‘); select char(97); – ASCII conversion character select char(65); select nchar(65); select nchar(45231); select nchar(32993); — Unicode conversion character select Unicode (‘ A ‘), Unicode (‘ middle ‘); Select soundex(‘ hello ‘), soundex(‘ world ‘), soundex(‘ word ‘); Select patindex (a ‘%’, ‘ta’), patindex (ac ‘% %’, ‘jack’), patindex (‘ dex % ‘, ‘dexjack); Characters index – a select ‘a’ + space (2) + ‘b’, ‘c’ + space (5) + “d”; Select charIndex(‘ o ‘, ‘hello world’); Select charIndex(‘ o ‘, ‘hello world’, 6); Select quoteName(‘ ABC []def ‘), quoteName(‘ 123]45 ‘); — Exact number select STR (123.456, 2), STR (123.456, 3), STR (123.456, 4); Select STR (123.456, 9, 2), STR (123.456, 9, 3), STR (123.456, 6, 1), STR (123.456, 9, 6); Select difference (” hello “, “helloWorld”); Select difference(‘ hello ‘, ‘world’); The select difference (‘ hello ‘, ‘llo); The select difference (” hello “, “hel”); The select difference (” hello “, “hello”); Select the replace (‘ abcedef ‘, ‘e’, ‘e’); — Replace string select stuff(‘ hello world ‘, 3, 4, ‘ABC’); — specify a position to replace the string select replicate(‘ abc# ‘, 3); Select subString(‘ ABC ‘, 1, 1), subString(‘ ABC ‘, 1, 2), subString(‘ hello word ‘, 7, 5); Select len(‘ ABC ‘); – Return length select reverse(‘ sqlServer ‘); — Reverse string

Select the left (leftString, 4); Select left(‘ leftString ‘, 7); The select right (leftString, 6); Select right(‘ leftString ‘, 3); Select the lower (” aBc “), the lower (” aBc “); Select upper(‘ aBc ‘), upper(‘ aBc ‘); Select ltrim(‘ ABC ‘), ltrim(‘ # ABC # ‘), ltrim(‘ ABC ‘); Select rtrim(‘ ABC ‘), rtrim(‘ # ABC # ‘), rtrim(‘ ABC ‘);

6, security function SELECT current_user; select user; The select user_id (), user_id (” dbo “), user_id (” public “), user_id (” guest “); select user_name(), user_name(1), user_name(0), user_name(2); select session_user; The select suser_id (” sa “); Select suser_sid(), suser_sid(‘ sa ‘), suser_sid(‘ sysadmin ‘), suser_sid(‘ serveradmin ‘); The select is_member (” dbo “), is_member (” public “); select suser_name(), suser_name(1), suser_name(2), suser_name(3); select suser_sname(), suser_sname(0x01), suser_sname(0x02), suser_sname(0x03); The select is_srvRoleMember (‘ sysadmin), is_srvRoleMember (‘ serveradmin); Select permissions (object_id (” student “)); select system_user; The select schema_id (), schema_id (” dbo “), schema_id (” guest “); select schema_name(), schema_name(1), schema_name(2), schema_name(3);

7, system function select app_name(); Select cast(2011 as datetime), cast(‘ 10 ‘as money), cast(‘ 0’ as varbinary); Select convert(datetime, ‘2011’); – Type conversion Select COALesce (NULL, ‘A’), coalesce(‘ 123 ‘, ‘A’); Select collationProperty(‘ Traditional_Spanish_CS_AS_KS_WS ‘, ‘CodePage’); select current_timestamp; – Current timestamp select current_user; Select isDate(getDate()), isDate(‘ ABC ‘), isNumeric(1), isNumeric(‘ a ‘); The select dataLength (” ABC “); select host_id(); select host_name(); select db_name(); The select ident_current (” student “), ident_current (” classes “); Select ident_incr(‘ student ‘), ident_incr(‘ classes’); Select ident_seed(‘ student ‘), ident_seed(‘ classes’); select @@identity; Select identity(int, 1, 1) as id into TAB from student; Select * from TAB; select * from TAB; select * from TAB; select @@rowcount; – Select @@cursor_rows; — Returns the current number of qualified rows of open cursors on the connection select @@error; – T-sql error number select @@procid;

Set dateFirst (); Select @@dateFirst as’ first day of the week ‘, datePart (dw, getDate()) as’ today is Sunday ‘; select @@dbts; – Return the current database unique timestamp set language ‘Italian’; Select @@langid as’ Language ID ‘; Select @@language as’ language Name ‘; – Return the current language name select @@lock_timeout; – Returns the current lock timeout setting of the current session (ms) select @@max_connections; Select @@max_precision AS ‘Max Precision’; – Returns the precision level used by decimal and numeric data types select @@servername; – Name of the local Server of the SQL Server select @@servicename; – Service name select @@spid; – Current session process ID select @@textSize; select @@version; – The current database version

9, system statistics function select @@connections; – Number of connections select @@pack_received; select @@CPU_BUSY; select @@PACK_SENT; select @@TIMETICKS; select @@IDLE; select @@TOTAL_ERRORS; select @@IO_BUSY; select @@TOTAL_READ; – Number of disk reads select @@packet_errors; – Number of network packet errors select @@total_write; – Number of disk writes performed by SQLServer SELECT patIndex(‘ %soft% ‘, ‘Microsoft SQLServer’); Select patIndex (‘ soft ‘%’, ‘software used); Soft select patIndex (‘ % ‘, ‘essentially a Microsoft); Select patIndex (so_gr ‘% %’, ‘Jsonisprogram);

10. User-defined functions

View all functions of the current database

Create a function

If (object_id(‘ fun_add ‘, ‘fn’) is not null) drop function fun_add go create function fun_add(@num1 int, @num2 int) returns int with execute as caller as begin declare @result int; if (@num1 is null) set @num1 = 0; if (@num2 is null) set @num2 = 0; set @result = @num1 + @num2; return @result; Select dbo.fun_add(id, age) from student;

— Custom functions, If (object_id(‘ fun_append ‘, Fun_append go create function fun_append(@args nvarchar(1024), @args2 nvarchar(1024)) returns nvarchar(2048) as begin return @args + @args2; end go

Select dbo.fun_append(name, ‘ABC’) from student;

Modify the function

Returns a table function

Select name, object_id, type from sys.objects where type in (‘ fn ‘, ‘if’, ‘tf’) or type like ‘%f%’;

If (exists (select * from sys.objects where type in (‘ fn ‘, ‘if’, ‘tf’) and name = ‘fun_find_stuRecord’)) drop function fun_find_stuRecord go create function fun_find_stuRecord(@id int) returns table as return (select * from student where id = @id); go

select * from dbo.fun_find_stuRecord(2);

Use of sqlserver stored procedures

A. Stored procedures allow standard component programming stored procedures to be created and executed multiple times in the program without having to rewrite the SQL statement of the stored procedure. Moreover, database professionals can modify stored procedures at any time without affecting the application source code, which greatly improves program portability. B. Stored procedures can execute faster if an operation contains a large number of T-SQL statements that are executed multiple times, stored procedures can execute much faster than batch processes. Because stored procedures are precompiled, when a stored procedure is run for the first time, the query optimizer analyzes it, optimizes it, and gives the storage plan that ends up in the system tables. Batch T-SQL statements need to be precompiled and optimized every time they are run, so they are slower. C. Stored Procedure Reduces network traffic For the same operation on a database object, if the t-SQL statement involved in the operation is organized as a stored procedure, then when the stored procedure is called on the client, only the call statement is passed over the network. Otherwise, there will be multiple SQL statements. Thus, the network traffic is reduced and the network load is reduced. D. Stored procedures can be used as a security mechanism to make full use of the system administrator's ability to restrict access to certain stored procedures. In this way, access to certain data can be restricted to avoid unauthorized users' access to data and ensure data security.Copy the code

Example system stored procedure:Copy the code

If (object_id(‘ pro_page ‘, ‘P’) is not null) drop proc proc_cursor go create proc pro_page @startindex int, @endIndex int as select count(*) from product ; select * from ( select row_number() over(order by pid) as rowId, * from product) temp where temp.rowId between @startIndex and @endIndex go — drop proc pro_page exec PRO_page 1, 4

If (object_id(‘ pro_page ‘, ‘P’) is not null) drop proc pro_stu go create procedure pro_stu(@pageindex int, @pageSize int ) as declare @startRow int, @endRow int set @startRow = (@pageIndex – 1) * @pageSize +1 set @endRow = @startRow + @pageSize -1 select * from ( select *, row_number() over (order by id asc) as number from student ) t where t.number between @startRow and @endRow;

exec pro_stu 2, 2;

Raiserror Raiserror returns user-defined error information. The severity level can be specified and system variables can be set to record the error. Syntax is as follows: Raiserror ({msg_id | msg_str | @ local_variable} {, severity, state} [, argument,… [n]] [with option,… [n]])

Msg_id: user-defined error information specified in the Sysmessages system table

Msg_str: user-defined information with a maximum length of 2047 characters.

Severity: User-defined severity level associated with the message. When msg_id is used to raise a user-defined message created with sp_AddMessage, the severity specified on RAISError overrides the severity defined in sp_AddMessage.

Any user can specify a direct severity level from 0 to 18. Only users frequently used by sysadmin fixed server roles or who have ALTER Trace permissions can specify the severity level 19-25 directly. Security levels between 19 and 25 require the with log option.Copy the code

State: Any integer between 1 and 127. State The default value is 1.

Raiserror (‘ is error, 16, 1); select * from sys.messages; – Use the message raisError (33003, 16, 1) defined in sysmessages; raiserror(33006, 16, 1);

SQL:

if @OrderType = 1 BEGIN set @OrderBy = ' Order by ' + REPLACE(@OrderBy,',',' desc,') + ' desc ' END else BEGIN set @orderby =' Order by '+ REPLACE(@orderby,',',' ASC,') +' ASC 'END  From '+@TableName+' '+@Where execute sp_executesql @CountSql,N'@TotalRecord int out',@TotalRecord out SET If (@pageIndex > @totalPage) set @pageIndex = @totalPage set @pagesize = @totalPage set @currentPagesize =(@PageIndex-1)* @pagesize -- set @TotalRecordForPageIndex=@PageIndex* @pagesize exec ('SELECT * FROM '  (SELECT TOP '+@TotalRecordForPageIndex+' '+@ReturnFields+', ROW_NUMBER() OVER ('+@OrderBy+') AS ROWNUM FROM '+@TableName+ ' ' + @Where +' ) AS TempTable WHERE TempTable.ROWNUM > SELECT @totalPage as PageCount, @totalRecord as RecordCountCopy the code

Use of sqlserver triggers

A trigger is a special type of stored procedure that is different from the stored procedures described earlier. Triggers are automatically invoked primarily by events that trigger. Stored procedures can be called by the name of the stored procedure. What is a trigger A trigger is a special stored procedure that is automatically executed when inserting, updating, or deleting a table. Triggers are typically used for more complex constraints of check constraints. The difference between a trigger and a normal stored procedure is that a trigger operates on a table. When performing operations such as update, INSERT, and delete, the system automatically invokes the triggers corresponding to the table. SQL Server 2005 triggers can be divided into two types: DML triggers and DDL triggers. DDL triggers fire with the effect of multiple data definition language statements, such as CREATE, ALTER, and DROP statements.

DML triggers are divided into: 1, after trigger (after trigger) a, B, C, delete trigger 2, instead of trigger (before trigger) An After trigger requires that the trigger be triggered only after an INSERT, update, or delete operation is performed, and can only be defined on a table. Instead of a trigger, it does not perform its defined actions (INSERT, update, delete) but only the trigger itself. Instead of triggers can be defined on a table or on a view. Triggers have two special tables: insert table (Instered) and delete table (deleted). These are both logical tables and virtual tables. The system creates two tables in memory that are not stored in the database. And both tables are read-only, can only read data and can not modify the data. The result of both tables is always the same structure as the table to which the trigger is applied. When the trigger completes its work, both tables are deleted. Data in the Inserted table is data that has been Inserted or modified, while data in the DELETED table is data that was updated or deleted.Copy the code

Inserted logical table operations on a table Deleted Logical table Insert Stores added records None Delete none Deletes records Update stores updated records Records before updates are stored To Update data, delete a table record and then add a new one. Inserted and DELETED tables will have data that has been updated. Note that a trigger is itself a transaction, so you can perform some special checks on the modified data within the trigger. If not, you can use transaction rollback to undo the operation.

Create trigger tgr_name on table_name with encrypion as Transact-SQL

Create an INSERT triggerCopy the code

— Create insert trigger if (object_id(‘ tgr_classes_insert ‘, ‘tr’) is not null) drop trigger tgr_classes_insert go create trigger tgr_classes_insert on classes for insert — insert triggers as – Declare @id int, @name vARCHar (20), @temp int; Select @id = ID, @name = name from INSERTED; set @name = @name + convert(varchar, @id); set @temp = @id / 2; insert into student values(@name, 18 + @id, @temp, @id); Print ‘Add student successfully! “; Insert into classes values(‘ 5 classes’, getDate()); Select * from classes; select * from student order by id; Insert trigger, which adds a newly inserted record to the INSERTED table.

Create a delete triggerCopy the code

— delete delete type trigger if (object_id(‘ tgr_classes_delete ‘, ‘TR’) is not null) drop trigger tgr_classes_delete go create trigger tgr_classes_delete on classes for delete — delete trigger AS Print ‘backup data… “; If (object_id(‘ classesBackup ‘, ‘U’) is not null) Insert into classesBackup select name, createDate from deleted; Select * into classesBackup from deleted; Print ‘Backup data successfully! “; go

Create update triggerCopy the code

# update Updates column level triggersCopy the code

An instead of trigger does not perform its defined actions (INSERT, update, delete) but simply executes the contents of the trigger itself. Create a grammarCopy the code

Create an instead of triggerCopy the code

# Display the custom message raisErrorCopy the code

# change triggerCopy the code

Enable/disable triggersCopy the code

SQL > create trigger ()Copy the code

# example, verify insert dataCopy the code

# example, operation logCopy the code

Sqlserver table partition

Please look at the following link www.cnblogs.com/knowledgese…

11: the primary and secondary databases of sqlserver are separated from each other

Twelve: sqlserver database summary and optimization

Naming style: block abbreviations of uppercase letters, do not underline (avoid mapping), English words, initials of uppercase pinyin/Chinese characters completely against primary key only unique identification, primary key generally has no meaning to the user; The best primary key singleton, improve query efficiency; The composite index (mapping/ several foreign key combinations) is never updated and the data does not change; Int/bigINT, sqlServer default aggregation index, small space, can have business significance (such as greater than 1000W are new users) multi-library environment, different environment database ID conflict; Continuous orders, easy to let peers calculate your sales; GUID program generated, globally unique, insert database, error free join, unlimited, easy to import space, no own meaning, no aggregation index (create time to aggregate) foreign key description data relationship, standard data relationship foreign key do data verification, Cascade delete (in fact, now is false delete) have strict data relationship, the use of foreign key import trouble, add, delete and change the database more step operation, more is to complete through the program, virtual foreign key reasonable use: as little as possible the use of foreign keys; Large Internet project bottlenecks are generally in the database, as little as possible to let the database do things; If data rigor is high, use foreign keys; Other did not how to use, especially Internet project 1 database transactions and database locking 2, views, stored procedures, triggers, the cursor custom function 3 field type, field can be empty, statistics and logic delete database transaction multiple SQL database as a whole is submitted to the system, either all completes, or cancelled. Is an indivisible logical unit. Two SQL statements, one place the order successfully; Another cut inventory; Update [Company] SET Name=' CreatorId 'where ID=1; That's the business. Have you ever seen one that's only half new?" Begin TRAN --rollback/commit Enable transactions by default. Begin TRAN ACID Atomicity Either all success or all failure consistency When transaction is completed, data is correctly isolated Two transactions operate on A table at the same time, transaction B either completes before transaction A, or executes after transaction A (lock table) Persistent data is solidified after commit lock: Multiple users access the same data resource at the same time 1 Modification lost 2 Unrepeatable read 3 Dirty read/magic read Ensure that the same resource is accessed in sequence. Handle concurrency. Optimistic lock indicates that there is no concurrency. Read data -- update -- Save (no lock) update do a judgment ----- timestamp /Version/ detect update fields/check all fields "database adds a column TimeSpan, long; Every query out; Update +1,; To ensure that any operation is in accordance with this specification, so there is also a vulnerability, other channels update performance is high!! Pessimistic lock that at any time may be multithreaded concurrency, read data others just change based on the database lock mechanism to complete the shared lock S lock read lock, allowing other transactions to read, but not allowed to modify; Release after reading, lock data page; Exclusive lock X lock write lock that prepares to write data and does not allow read or write; Select * from table where id= 1 where id= 1 where id= 1 (Deadlocks are impossible to avoid under high concurrency, but can only be reduced.) 1. No deadlocks, no deadlocks, optimistic locks 2. Minimize the number of operations inside the lock. 4 Avoid waiting for user input in transactions. 5 Reduce database concurrency A stored Procedure is a set of SQL statements that are compiled and stored in a database to perform specific functions. Users can execute the statements by specifying the name and parameters of the stored Procedure. Stored procedures are not maintainable; Logic disperses; Difficult to manage; All database operations; A flip-flop is a special type of stored procedure, The trigger is mainly done through events trigger is automatically calls don't use vernier cursor is, in fact, a kind of can be extracted from multiple data records in the result set every time a record mechanism Almost no view view is a virtual data table, the data in the table data record is a query of the query result is no problem, Has the need to use (more than table into a temporary table, convenient query/access control) function is not recommended to use a custom function, because the calculation to the database, can't use the index the field type Look at the document Don't nullable fields nullable value type, in query not query will not result in, and generally give a default value As far as possible is not empty, Create time, creator, last updated, last updated, state, isEnable Soft delete Fake delete, logical delete, change the status to Delete, not physical deleteCopy the code

Three ways to optimize database performance

1. Load balancing: disadvantages: data will be delayed and data consistency cannot be guaranteed. This optimization method still depends on money to solve.
2. Read/write separation: a master library with multiple slave libraries, 2-8 rule. It is mainly realized by publishing and subscription mode. If the data volume is large, the main library can not carry it.
3. Database sub – database sub – table: the main vertical and horizontal segmentation