SQL Sever Learning, 2016,5,31, (Focus: 100 lines later, string manipulation.) ）

--Aliases and expressions
Select Orderdate,year (OrderDate) as order year
From orders;

--Select
Select Orderdate,year (OrderDate) as order year
From Orders
Where year (OrderDate) = 2006;

--in the case of grouping, the select sentence will have a limit, can only query the entire group statistics
Select year (OrderDate), COUNT (*), SUM (OrderDate)
From Orders
Group BY OrderDate;

Select COUNT (*), SUM (OrderDate)
From orders;
--null NULL, the result of a null operation, or NULL
--You must use NULL in a special way
Select OrderDate
From Orders
Where not OrderDate is null;
--the group function ignores null

--having filtering the data after statistics
Select ... COUNT (*)
From Orders
GROUP BY ...
Having COUNT (*) > 2;

--order by sort
Select Orderdate,year (OrderDate) as order year
From Orders
ORDER BY OrderDate Desc;
--top Query the first few
Select Top (2)--Query Top 2

--sql
Select CityName as City
From city

SELECT *
From distributors

Select TOP (3) *
From items

--The Select SQL statement itself is case insensitive

--Percentage
Select TOP (Percent) *
From distributors

--with ties represents an additional record that is attached to the last record equal
Select Top (5) with ties Bossname
From distributors
ORDER BY Bossname

The--over clause is called the open window function
Select Bossname
from distributors;

Select COUNT (*)
from distributors;

--over
Select Bossname, COUNT (*) over () as people total
From distributors

Select Item, Ordernum, Totalprice
, SUM (totalprice) over () as Total price
, SUM (totalprice) over (partition by Item)--partition by is equivalent to making an equal selection, choosing a value equal to
From items

--ranking
--row_number line number
--rank rank, indicating how many are ahead
--dense_rank Dense rankings
--ntile Grouping
Select Row_number () over (order by Citycode) as line number
, Rank () over (order by Citycode) as rank
, Dense_rank () over (order by Citycode) as dense ranking
, Ntile (3) over (order by Citycode) as group
, Bossname,citycode
From distributors

--The ranking of the combined part
Select
Row_number () over (order by Citycode),
Row_number () over (partition by Citycode ORDER by Citycode),--Group
Citycode, Bossname
From distributors

SELECT *
From distributors

--where conditions
SELECT *
From distributors
where Distrinum >=1004 and Distrinum <=1010

--between and, containing boundaries, note upper and lower bounds
SELECT *
From distributors
where Distrinum between 1004 and 1010

--in
SELECT *
From distributors
where Distrinum =1004 or distrinum =1009 or Distrinum =1010

SELECT *
From distributors
where Distrinum in (1004,1009,1010)

--like must master, string match,% any number of characters, _ any one character.
SELECT *
From distributors
Where Bossname like ' King% '

SELECT *
From distributors
where Bossname like ' _ is% '

SELECT *
From distributors
Where Bossname like '% true% '

--case simple expressions to compare equality
Select Bossname, Citycode,
Case Citycode
When the ' CY ' then ' Who's outside '
Else ' other '
End
From distributors

--case Search Expressions
Select Bossname, Citycode,
Case
When Citycode inch (' CY ', ' PG ') Then ' wwww '
When Citycode was null then ' unknown '
When bossname = ' King Absalom ' then ' it's him '
Else ' go to other districts '
End
As Hhe
From distributors

--*************************************************************
--sql character data type
--char Type a string of length
--char (8) 8 length strings fixed-length strings
--varchar (8) variable-length string up to 8 extra auto-bounce
--nchar internationalized Encoding, Unicode, character length, 2 bytes per character, fixed length
--nvarchar (8) variable-length internationalized string ************************
--nvarchar (4000) 4000 maximum, 8000 bytes
--nvarchar (max) varchar (max) Special length
--**************************************************************

--Defining variables
DECLARE @s varchar (10);
Set @s = ' Hello ';
Print @s;
Set @[email protected]+ ' world ';
Print @s;

--isnull function
DECLARE @s varchar (10);
Print @s;
Set @s= isnull (@s, ') + ' world ';
Print @s;

--Note: the subscript in SQL is starting from 1
--substring (bossname,3,4) Intercept string
--left means intercept left (bossname,4)
--right means intercept right (bossname,4)

DECLARE @s varchar (10);
Set @s = ' Hello ';
Print @s;
Set @[email protected]+ ' world ';
Print @s;
Print Len (@s); --Actual length
Print datalength (@s);--the length of the claim

--The template is in front, the source string is behind, * * appears in the string position, starting from high 1
DECLARE @s varchar (10);
Set @s= ' HelloWorld ';
Print @s;
Select @s, charindex (' W ', @s)--Find location
Select @s,substring (@s,charindex (' W ', @s) +1,10)

--replace replacement
DECLARE @s varchar (20)
Set @s= ' Hello World ';
Print @s;
Select @s,replace (@s, ' hello ', ' hello ')

--stuff replacement
DECLARE @s varchar (20)
Set @s= ' Hello World '
Print @s
Select @s,stuff (@s,charindex (", @s), 1, ': ')

--upper to uppercase, lower to lowercase

--rtrim () Remove Right, Lteim () remove left
--ltrim (RTrim ()) remove all characters from the left and right sides
DECLARE @s varchar (20)
Set @s= ' Hello wor ld '
Print @s;
Set @s=rtrim (@s);
Print @s;
Set @s=ltrim (@s);
Print @s;
Set @s=ltrim (RTrim (' o '));
Print @s;

SQL Sever Learning, 2016,5,31, (Focus: 100 lines later, string manipulation.) )