In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
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.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.