1. SQL Server's data type data category is an attribute of data, indicating the type of information indicated by the data. Any computer language defines its own data type. Of course, different programming languages have different characteristics, and all kinds of various types and names of the defined data types are more or less. SQL Server provides 25 data types:
· Binary [(n)]
Varbinary [(n)]
· Char [(n)]
VARCHAR [(N)]
· Nchar [(n)]
· Nvarchar [(n)]
· DateTime
· SmallDateTime
· Decimal [(p [, s])]
· Numeric [(p [, s])]]
· Float [(n)]
Real
Int
· Smallint
Tinyint
· Money
· Smallmoney
· Bit
Cursor
Sysname
· TimeStamp
· UNIQUEIDENTIFIER
· Text
Image
· NTEXT
(1) Binary data type binary data includes
Binary, Varbinary and Image
The Binary data type can be only a fixed length (Binary) or a length of length.
Binary [(n)] is the N-bit fixed binary data. Wherein N value ranges from 1 to 8000. The size of its storage is N 4 bytes.
Varbinary [(N)] is binary data for n-bit length. Wherein N value ranges from 1 to 8000. The size of the storage is N 4 bytes, not n bytes.
The data stored in the image data type is stored in a bit string, not interpreted by SQL Server, must be explained by the application. For example, the application can store data in the Image data type using BMP, TIEF, GIF, and JPEG format.
(2) The type of character data type character data includes
Char, varchar and text
Character data is data made from any letters, symbols, and numbers.
VARCHAR is a bell-length character data, which does not exceed 8KB. Char is a fixed length character data with a maximum length of up to 8KB. More than 8KB ASCII data can be stored using the Text data type. For example, because the HTML document is all ASCII characters, and in general, the length is more than 8KB, so these documents can be stored in the SQL Server in the TEXT data type.
(3) Unicode data type Unicode data types include
Nchar, nvarchar and ntext
In Microsoft SQL Server, traditional non-Unicode data types allow using characters defined by a particular character set. Allow a character set during the SQL Server installation. Use the Unicode data type, you can store any characters defined by the Unicode standard. In the Unicode standard, all characters defined in various character sets are included. Using the Unicode data type, the victory is twice as large as the non-Unicode data type.
In SQL Server, UNICODE data is stored in Nchar, NVARCHAR, and NTEXT data types. Columns using this type of character type can store characters in multiple characters. When the length of the column changes, the NVARCHAR character type should be used, and up to 4000 characters can be stored. When the length of the column is fixed, the NCHAR character type should be used, and the same, at this time, up to 4000 characters can be stored. When using the NTEXT data type, the column can store more than 4000 characters.
(4) Date and time data type date and time data types include
DateTime and SmallDatetime Two types of date and time data type consist of valid date and time. For example, effective date and time data include "4/01/98 12: 15: 00: 00: 00 PM" and "1: 28: 29: 15: 01AM 8/17/98". The previous data type is the date before, the time in the latter data is the time before, the date is behind. In Microsoft SQL Server, the date and time data type includes both types of DateTime and SmallDateTime, the stored date range begins on January 1, 1753, ending from December 31, 9999 (8 each value requires 8 Storage bytes). When using the SmallDateTime data type, the stored date range begins on January 1, 1900, and ends on December 31, 2079 (each value requires 4 storage bytes).
The date of the date can be set. Set the order in the date format as follows:
Set DateFormat {Format | @format _var |
Among them, Format | @format_var is the order of the date. Effective parameters include MDY, DMY, YMD, YDM, MYD, and DYM. By default, the date format is MDY.
For example, after the SET DATEFORMAT YMD is executed, the date is formatted in the form of an annual month; after the SET DATEFORMAT DMY is executed, the date format is a year in the day.
(5) Digital data type digital data only contains numbers. Digital data types include positive and negative numbers, decimal (floating point) and integers
Integers consisting of positive integers and negative integers, such as 39, 25, 0-2, and 33967. In MicrSoft SQL Server, the data type of integer storage is
INT, Smallint and Tinyint. The INT data type storage data is greater than the scope of the Smallint data type stored data, and the scope of the SmallINT type storage data is greater than the range of Tinyint data types. The range of INT data dogs stored data is from -2 147 483 648 to 2 147 483 647 (4 bytes of storage space each value). When using the Smallint data type, storage data ranges from -32 768 to 32 767 (2 bytes storage space each value). When using the Tinyint data type, the storage data ranges from 0 to 255 (one byte storage space for each value).
Accurate smuggling data in SQL Server is Decimal and NuMeric. The storage space occupied by this data is determined based on the number of bits after the data of the data.
In SQL Server, the data type of approximate decimal data is Float and Real. For example, one-third of this score is recorded. 333333, when using approximate data types, it can be accurately expressed. Therefore, the data retrieved from the system may not be exactly the same as the data stored in the column.
(6) The currency data represents the number of positive or negative currencies. In Microsoft SQL Server, the data type of currency data is
Money and Smallmoney
The Money data type requires 8 stored bytes, and the SmallMoney data type requires 4 stored bytes.
(7) Special data type Special data types include data types that have not been entered in front. There are three types of special data types, namely
TimeStamp, Bit and UniqueIdentifier.
TimeSTAMP is used to represent the order of SQL Server activities, expressed in the format of binary projection. TimeStamp data does not matter to insert data or date and time. Bit consists of 1 or 0. When you represent true or false, ON or OFF, use the BIT data type. For example, asking if it is a client request to be accessed, it can be stored in the column of this data type.
UniqueIdentifier consists of 16-bytes of hexadecimal numbers, indicating a global unique. The GUID is very useful when the recording line of the table requires the only time. For example, using this data type in the customer identification number can distinguish between different customers.
2. User-defined data type User-defined data types are based on data types provided in Microsoft SQL Server. When the same data type must be stored in several tables, and to ensure that these columns have the same data type, length, and blanket, the user-defined data type can be used. For example, a data type called Postal_Code can be defined, which is based on a CHAR data type.
When creating a user-defined data type, three numbers must be provided: the name of the data type, the system data type and the data type of data type.
(1) Creating a user-defined data type Creating a user-defined data type You can use the Transact-SQL statement. System stored procedures sp_addtype can create user-defined data types. Its grammar form 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 data type provided by the system, such as Decimal, Int, Char, etc. NULL_TYPE indicates how the data type handles null values, and you must use single quotes, such as 'null', 'not null', or 'Nonull'.
example:
Uses USE CUST
EXEC SP_ADDTYPE SSN, 'VARCHAR (11)', "Not null"
Creating a user-defined data type SSN, whose system data type is characterized by a character that becomes 11, and is not allowed.
example:
Uses USE CUST
EXEC SP_ADDTYPE BIRTHDAY, datetime, 'null'
Create a user-defined data type Birthday, whose system data type is DateTime, allowed empty.
example:
Use master
EXEC SP_ADDTYPE TELEPHONE, 'VARCHAR (24),' NOT NULL '
EEXC SP_ADDTYPE FAX, 'VARCHAR (24)', 'NULL'
Create two data types, TELEPHONE and FAX
(2) Deleting a user-defined data type When the user-defined data type is not required, it can be deleted. Deleting a user-defined data type command is sp_droptype {'type'}.
example:
Use master
EXEC SP_DROPTYPE 'SSN'
Note: This user-defined data type cannot be deleted when the columns in the table are also using user-defined data types, or when they are also bound to have default or rules.
SQL Server field type description
The following is a field type of SQL Server 7.0 or higher. Field Type Description of SQL Server6.5 Please refer to the instructions provided by SQL Server.
Field Type Description Bit 0 or 1 Integer Digital INT from -2 ^ 31 (-2, 147, 483, 648) to 2 ^ 31 (2, 147, 483, 647) integer digital Smallint from -2 ^ 15 (-32, 768) to 2 ^ 15 (32, 767) Integer Digital Tinyint from 0 to 255 Digital Decimal from -10 ^ 38 to 10 ^ 38-1 Numeric Numeric Decimal synonym Money from -2 ^ 63 (-922, 337, 203, 685, 477.5808) to 2 ^ 63-1 (922, 337, 203, 685, 477.5807), the minimum currency unit, thousands of tens of SMallMoney from -214, 748.3648 to 214, 748.3647, minimum currency unit, 10,000 tenth float from -1.79e 308 to 1.79e 308 variable accuracy Digital REAL from -3.04E 38 to 3.04E 38 variable precision Digital DateTime from January 1, 1753 to 31 and time data of 31, 9999, minimum time unit is 3 modium or 3.33 milliseconds SmallDatetime From January 1, 1900, the date and time data of June 6, 2079, the minimum time unit is a minute TimeStamp timestamp, a database width unique representifier global unique identifier Guid Char fixed length non-Unicode character type Data, the maximum length of 8000 VARCHAR is extremely non-Unicode characteristic data, the maximum length of 8000 text is long-length non-Unicode type data, the maximum length of 2 ^ 31-1 (2G) Nchar fixed length Unicode character data, The maximum length of 8000 nVARCHAR becomes the characteristic data of Unicode, the maximum length of 8000 nText becomes the characteristic data of Unicode, the maximum length is 2 ^ 31-1 (2G) binary fixed length binary data, the maximum length is 8000 varbinary Binary data, the maximum length is 8000 image, the length binary data, the maximum length is 2 ^ 31-1 (2G) DB2 provides a query language SQL (Structure Language) of the off-site database, is a very spoken, easy to learn Also understand the grammar. This language is almost almost every library system, which is used to indicate a connection-like *, including the definition (DDL) of the data (DDL), and processing (DML). SQL originally metrics, the prototype of this language is completed in the IBM San Jose laboratory with the name "System R". It is quite satisfactory, and the result is quite satisfactory and determines in the system. R The technical foundation of R is developed from IBM products. Moreover, the National Standard Society (ANSI) and the International Standardization Organization (ISO) follows an almost IBM SQL-based standard-based standard-off language definition in 1987.
I. Data definition DDL (Data Definition language) DDL is a language defined in the format and morphology of the information. He is the first to face when each database is to be established, and the data is divided into which form relationships, forms The relationship between the primary key, the table and the table, etc., all must be planned at the beginning.
1. Built: Create Table Table_Name (Column1 Datatype [NOT NULL] [Not Null Primary Key], Column2 DataType [Not Null], ...) Description: DataType - is the format of the data, see the table. NUT NULL - You can not allow information to be available (not yet filled in). Primary Key - is the primary key of this table. 2, change the form ALTER TABLE TABLE_NAME Add Column Column_Name DataType Description: Add a field (no grammar to delete a certain field). ALTER TABLE TABLE_NAME ADD Primary Key Description: Change the definition of an item sets a certain field as the primary key. ALTER TABLE TABLE_NAME DROP Primary Key Description: Delete the definition of the primary key.
3. Establish index create index index_name on table_name (column_name) Description: Establish an index for a table to increase the speed of the query.
4, delete DROP TABLE_NAME DROP INDEX_NAME
Second, the data form is an integer of DataTypes Smallint 16-bit yuan. Integer 32-bit integers. Decimal (p, s) p accuracy value and the size of the size of the s size, the precise value P refers to all of the number (DIGITS) large values, S is a few digits after the decimal point. If there is no special designation, the system is set to P = 5; s = 0. Float 32-bit real number. Double 64-bit real. CHAR (N) N length string, n cannot exceed 254. VARCHAR (N) length is not fixed and its maximum length is a string of N, N cannot exceed 4000. Grawhic (n) and char (n), but its unit is two characters Double-Bytes, n cannot exceed 127. This form is a font that supports two character lengths, such as Chinese characters. Vargraphic (n) variable length and its maximum length is a double-character string of N, N cannot exceed 2000. Date contains years, month, date. Time contains hours, minutes, seconds. Timestamp contains years, months, days, time, minute, seconds, thousands of seconds.
Combined query combined query refers to the source of the data that is not only a single table, but a combination of more than one table can be obtained. Select * from table1, table2 where table1.colum1 = table2.column1 Description: 1. Query data in both tables in the two tables. 2. Of course, the two forms are compared to each other, and their data must be the same. 3. A complex query that it can be used to use may be many.
Integrated query: select count (*) from table_name where colorn_name = xxx Description: Querying the qualified information has a total of several. SELECT SUM (Column1) from table_name Description: 1. Calculate the sum, the selected field must be a number of digital forms. 2. Avg () is the integrated query for calculating average, max () and min () calculate the maximum minimum. Select Column1, AVG (Column2) from Table_name Group By Column1 Having AVG (Column2)> xxx Description: 1.Group By: Use Column1 to calculate the average of the COLUMN2 must be used with the keywords of AVG, SUM and other integrated queries . 2.Having: It must be used as a constraint for integration with Group By. Complency Query Select * from Table_name1 WHERE EXISTS (Select * from table_name2 where conditions) Description: 1.where's conditions can be another Query. 2.Exists herein refers to whether there is. Select * from table_name1 where column
1 in
(Select Column1 From Table_name2 WHERE CONDitions) Description: 1. In the back is a collection, indicating that the column1 exists in a collection. 2. SELECT's data must meet Column1.
Other Query Select * from table_name1 where column1 like 'x%' Description: LIKE must be and the back 'X%' Echo indicates the string starting with X. Select * from table_name1 where column
1 in
('Xxx', 'yyy', ..) Description: The later is a collection, indicating that the column1 exists in a collection. Select * from table_name1 where column1 betWeen XX and yy Description: Between indicates that the value of Column1 is between XX and YY.
3, change information: update table_name set column1 = 'xxx' where conditionoins Description: 1. Change a certain field setting It is 'xxx'. 2. Conditions is the condition you have to match. If there is no WHERE, all TABLE will be all changed.
4, delete information: delete from table_name where conditions Description: Delete eligible information.
Note: About WHERE conditions If there is a date, different databases have different expressions. The specific is as follows: (1) If it is an Access database, it is: where mydate> #
2000-01-01
# (2) If it is an Oracle database, it is: where mydate> cast ('
2000-01-01
'As date) or: where mydate> to_date ('
2000-01-01
',' YYYY-MM-DD ') Writing in Delphi: thisdate ='
2000-01-01
'; query1.sql.add (' select * from abc where mydate> Cast (' ' '' '); "If the datetime type is: where mydatetime> TO_DATE ('2000-01-01 10:00:
01 '
, 'YYYY-MM-DD HH24: MI: SS')
