"Notes" Database Fundamentals 03

Basic syntax for SQL database

Some basic statement usage, the base of the database.

The use of Unoin

The number of columns per result set must have the same result, and the type must be the same.

eg

Select Name from Student

Union

Select Age from Student

< add all after union, the number of repetitions is not removed >

Operations in total

eg

Select Name from Student

Union

Select ' Total totals ', sum{age} from Student

numeric functions

ABS () to find absolute value

CEILING () rounded to the maximum integer

Floor () rounded to the smallest integer

Found () round rounded to "nearest number to my radius"

LEN () calculates the string length

Select LEN (age) from Student

Date function

GETDATE (): Go to the current date

DATEADD (Datepart,number,date), calculates the date of the increment, the date of the parameter is the day to be calculated, the parameter number is increment, and the DatePart is the unit of measure;

There must be three parameters in select DATEADD (Day,0,getdate ()) <dateadd (). > represents the current time.

DATEADD (datepart,startdate.enddate): Calculates the difference between two dates. DatePart is the unit of measure.

Type conversion function

CAST (Expreession,as)

eg

Select CAST (' 123 ' as int), cast (' 2008-08-08 ' as DateTime),
Convert (datetime, ' 2009-09-09 '), CONVERT (varchar
(50), 123)

Select DATEPART (Year,cast (' 2023-12-11 ' as DateTime))

Null-value processing function

Execute the code in the note

ISNULL (Expression,value): Returns expression If expression is not empty, otherwise returns value.

eg

Select IsNull (FName, ' Zhanghui ') as name from Student

Use of the case function

Select Name

{

Case Age

When the ' normal user '

When the ' member user '

When the "VIP user"

Else ' Unknown user type '

End

} as Client side

EG1:

Select FName,
(
Case Fnumber
When 1 Then ' normal user '
When 2 Then ' member users '
When 3 Then ' VIP user '
Else ' Heihu '
End
)
As Customer type
From Customer

EG2:

Select Name,
(
Case
When math<60 then ' failed '
When math>=60 and math<=85 then ' pass '
When math>85 and then ' excellent '
Else ' input is incorrect, please re-enter '
End
) as ' performance status '
From Student

SQL data type "go from Network" data type for 1.SQL server

A data type is a property of the data that represents the type of information represented by the data. Any computer language defines its own data type. Of course, different programming languages have different characteristics, and the type and name of the data types defined are more or less different. SQL Server provides 25 types of data:
· 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 
The binary data includesBinary, Varbinary, and Image 
Binary data types can be either fixed-length (binary) or variable-length.
binary[(N)] is a fixed n-bit binary data. where n is the range of values from 1 to 8000. The size of its storage scenting is n + 4 bytes.
varbinary[(N)] is a binary data of n-bit variable length. where n is the range of values from 1 to 8000. The size of its storage scenting is n + 4 bytes, not n bytes.
The 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, applications can store data in the Image data type using BMP, Tief, GIF, and JPEG formats.
 　　 (2) character data type 
The types of character data includeChar,varchar and Text 
Character data is data that is any combination of letters, symbols, and numbers.
Varchar is a variable-length character data whose length does not exceed 8KB. Char is a fixed-length character data with a length of up to 8KB. ASCII data over 8KB can be stored using the text data type. For example, because Html documents are all ASCII characters and are typically longer than 8KB in length, these documents can be stored in SQL Server with the Text data type.
 　　 (3) Unicode data type 
Unicode data types includeNchar,nvarchar and ntext 
In Microsoft SQL Server, traditional non-Unicode data types allow the use of characters defined by a specific character set. During SQL Server Setup, a character set is allowed to be selected. With 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. With the Unicode data type, the prevailing scenting is twice times the size of the scenting used by non-Unicode data types.
In SQL Server, Unicode data is stored in Nchar, Nvarchar, and Ntext data types. Columns stored with this type of character can store characters in multiple character sets. When the length of a column changes, you should use the nvarchar character type, where you can store up to 4,000 characters. When the length of a column is fixed, you should use the Nchar character type, and you can store up to 4,000 characters at this time. When you use the Ntext data type, the column can store more than 4,000 characters.
 　　 (4) Date and time data type 
Date and time data types includeDatetime and smalldatetimeTwo types of
Date and time data types are made up of valid dates and times. For example, valid date and time data includes "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 last data type is a moment before, and the date in the back. In Microsoft SQL Server, date and time data types include datetime and smalldatetime two types when the date range is stored starting January 1, 1753 and ending December 31, 9999 (each value requires 8 Storage bytes). When using the smalldatetime data type, the stored date range starts January 1, 1900 and ends on December 31, 2079 (each value requires 4 bytes of storage).
The format of the date can be set. The commands for formatting dates are as follows:
Set DateFormat {format | @format _var|
Where, Format | @format_var is the order of the dates. Valid parameters include MDY, DMY, YMD, YDM, MYD, and DYM. By default, the date format is MDY.
For example, when the set DateFormat YMD is executed, the date is formatted as a month and day form; When the set DateFormat DMY is executed, the date is in the form of sun and moon.
 　　 (5) numeric data types 
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, the data type stored by an integer isInt,smallint and Tinyint。 The INT data type stores data in a range that is larger than the Smallint data type stores the data, while the Smallint data type stores the range greater than the tinyint data type stores the data. The range of data stored using the INT data type is from 2 147 483 648 to 2 147 483 647 (each value requires 4 bytes of storage space). When using the Smallint data type, the range of stored data ranges from 32 768 to 32 767 (each value requires 2 bytes of storage). When you use the tinyint data type, the range of stored data is from 0 to 255 (each value requires 1 bytes of storage space).
The exact decimal data type of data in SQL Server is decimal and Numeric. The amount of storage space that this data occupies is determined by the number of bits in the data.
In SQL Server, the data type of the approximate decimal data is Float and Real. For example, One-third of this score is recorded. 3333333, which can be expressed accurately 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) Currency data indicates the number of positive or negative currencies.  
In Microsoft SQL Server, the data type of the currency data isMoney and SmallMoney 
The Money data type requires 8 storage bytes, and the SmallMoney data type requires 4 bytes of storage.
 　　 (7) Special data types 
Special data types include data types that were not previously mentioned. There are 3 unique types of data, namelyTimestamp, Bit and uniqueidentifier。
The Timestamp is used to represent the sequence of SQL Server activities in a binary projection format. Timestamp data is not related to inserting data or date and time.
The Bit is made up of 1 or 0. When representing true or false, on, or off, use the Bit data type. For example, ask if a client request for each visit can be stored in a column of this data type.
The uniqueidentifier consists of 16-byte hexadecimal digits that represent a globally unique. GUIDs are useful when the table's record rows require uniqueness. For example, using this data type in the customer Identification Number column can differentiate between different customers

"Notes" Database Fundamentals 03