Data indexes and functions

I. Index

(1) Cluster Index

Index created during primary key Creation

(2) Non-Cluster Index

You can set up to 249

Index creation command format:

Create [unique] [clusered] [nonclustered] index index_name -- Unique unique index clusered Cluster Index nonclustered Common Index

On Table (column [ASC | DESC])

Ii. View

Operation:

View --> right-click to create --> enter SQL statement execution in the bottom box --> Save

You can use and operate a view like a table.

Example: 1. Select Xinxi. code, Xinxi. name, Minzu. name, Xinxi. sex, Xinxi. birthday from Xinxi join Minzu on Xinxi. code = Minzu. the Code statement is executed in the view.

 

 

2. Save and enter a new name, Xuesheng.

 

 

 

3. Then, Xuesheng is equivalent to a new table and can be queried. It can perform the same operations as other tables.

 

 

 

Iii. Functions

1. Len (judge the length of the string)

Format:

Len (string expression)

2. Left and Right (truncation)

Left: left string Function

Format: Left (column name, number) from Table Name

Right: Right string Function

Format: Right (column name, number) from Table Name

3. Lower and Upper

Format:

Lower (character_expression): converts all letters to lowercase letters

Upper: converts all letters to uppercase letters.

4. datediff

Function: calculate the difference between two dates.

Format: datediff (datepart, startdape, enddate) -- equivalent to enddate-startdape

Example: Search student age

Select datediff (year, csrq, getdate () as age, csrq from student

5, day, month, year

Format: Day (data)

To the day of the specified date

Format: Month (data)

Month of the specified date

Format: Year (data)

Specify the year of the date

Example: Search student age

Select Year (getdate ()-year (csrq) as age, csrq from student

6. Numeric Functions

(1) ceiling (return the smallest integer greater than or equal to the given number)

Floor number (returns the maximum integer smaller than or equal to the given number)

(2), Rand

Returns the random float number between 0 and 1.

(3) cast... as function (type conversion) -- you can also use convert

Format: Cast (expression as data_type)

For example, Birthday = 1997-8-28

Cast (Year (birthday) as varchar (10) + 'Year' + mouth (birthday) as varchar (10) + 'month' + Day (birthday) as varchar (10) + 'day ')

After execution, the date is changed to: January 1, August 28, 1997.

 

Data indexes and functions