Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

T-SQL Advanced query

2025-01-18 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

T-SQL Advanced query

Function: a program segment that can be used repeatedly

Use call statements from other program segments to execute this program

Write a sentence in advance, take a name, and call this name when you use it.

System functions: converting data types and viewing system parameters

Convert (): data types are converted to strings

Select converet (varchar (5), 12345)

12345 numeric '12345' string

CAST (): convert data types (simple)

Select cast ('2019-01-18' as datetime) / / convert strings to time classes

2019-01-18 00:00:00

Current_user (): view the user name of the current login database

Select current_user

SYSTEM_USER (): view the login users of the system

Select system_user

The salary of select 'operation and maintenance engineer' + name +'is: + cast (basic salary as varchar (50)) + 'yuan' from employee where job = 'operation engineer'

String function

CharIndex ()

Used to find the starting position of a specified string in another string

SELECT CHARINDEX ('BDQN','www.bdqn.cn',1)

Return: 5

Len ()

Returns the length of the string passed to it

SELECT LEN ('SQL Server course')

Return: 12

Upper ()

Convert the string passed to it to uppercase

SELECT UPPER ('SQL Server course')

Return: SQL SERVER course

Ltrim ()

Clear the space to the left of the character

SELECT LTRIM ('BDQN')

Return: BDQN (the following spaces are reserved)

Rtrim ()

Clear the space to the right of the character

SELECT RTRIM ('BDQN')

Return: space before BDQN is reserved)

Right ()

Returns a specified number of characters from the right side of the string

SELECT RIGHT ('ABCDEFG',3)

Return: EFG

Replace ()

Replace a character in a string

SELECT REPLACE ('ABABAB','B','A')

Return: AAAAAA

Stuff ()

In a string, delete the string of the specified length at the specified location and insert a new string at that location

SELECT STUFF ('ABCDEFG',2,3,' my Music, my World')

Return: a my music, my world EFG

Date function:

Select dateadd (dd,3000,GETDATE ()) current number of days increased by 3000 days' ddd days mm month yy year'

Select datediff (dd,'1998-11-2Getdate ()) calculates the difference between two dates.

Select datename (DW,'2001-05-01') to find the week of the date

Select name, datediff (yy, date of birth, getdate ()) AS age from employee / / View employee age (first year)

Aggregate function:

SUM (): summation

AVG (): find the average

MAX (): find the maximum

MIN (): minimum

COUNT (): number of rows with non-null values

Select SUM (base salary) AS Total salary from employee

Select AVG (base salary) AS average wage, MAX (base salary) AS maximum, MIN (base salary) AS minimum from employee

-- count the number of post-90s people in the company--

Select count (date of birth) AS how many people from employee where '2000-01-01' > birth date and birth date > = '1990-01-01'

Grouping query

Select position, SUM (base salary) AS department salary from employee group by job

Mathematical function

Abs () takes the absolute value of a numeric expression

Ceiling () takes the smallest integer greater than or equal to the specified value and expression

Floor () takes the largest integer that is less than or equal to the specified expression

Power () takes the power of a numeric expression

Round () rounds the numeric expression to the specified precision

Sign () returns + 1 for positive numbers,-1 for negative numbers, and 0 for 0

Sqrt () takes the square root of a floating-point expression

Comprehensive exercise: how many days before the company's employees are 30 years old?

Select name + 'year' + cast (datediff (yy, date of birth, getdate ()) as varchar (10)) + 'year' + 'from 30 years old and' + cast (datediff (dd,getdate (), dateadd (yy,30, date of birth)) as varchar (10)) + 'day' from employee

Internal link:

Select A. Name, A. School, B. Name, B. Professional from An INNER JOIN B ON A. Name = B. Name

External connection:

The left outer connection shows all of the left table, and the right table only shows those that meet the conditions.

Select A. Name, A. School, B. Name, B. Professional from A LEFT JOIN B ON A. Name = B. Name

The right table shows all of the right outer connection, and the left table only shows those that meet the conditions.

Select A. Name, A. School, B. Name, B. Professional from A RIGHT JOIN B ON A. Name = B. Name

Full external connection

SELECT A. Name, A. School, B. Name, B. Professional from A FULL JOIN B on A. Name = B. Name

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 219

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report