SQL Server SQL advanced Query Statement summary _mssql

Ø Basic Common Query
--select
SELECT * from student;
--all Query All
Select all sex from student;
--DISTINCT Filter Repeat
Select distinct sex from student;
--count Statistics
Select COUNT (*) from student;
Select count (Sex) from student;
Select count (Distinct sex) from student;
--top take the first N records
Select Top 3 * from student;
--alias Column Name Rename name
Select ID as number, name ' name ', sex sex from student;
--alias Table name Tables Rename
Select ID, name, s.id, s.name from student s;
--column column Operations
Select (age + ID) col from student;
Select S.name + '-' + c.name from classes C, student s where s.cid = C.id;
--where conditions
SELECT * FROM student where id = 2;
SELECT * FROM student where ID > 7;
SELECT * FROM student where ID < 3;
SELECT * FROM student where ID <> 3;
SELECT * FROM student where ID >= 3;
SELECT * FROM student where ID <= 5;
SELECT * FROM student where ID!> 3;
SELECT * FROM student where ID!< 5;
--and and
SELECT * FROM student where ID > 2 and sex = 1;
--or or
SELECT * FROM student where id = 2 or sex = 1;
--between ... and ... Equivalent and
SELECT * from student where ID between 2 and 5;
SELECT * FROM student where ID is not between 2 and 5;
--like Fuzzy Query
SELECT * FROM student where name like '%a% ';
SELECT * FROM student where name like '%[a][o]% ';
SELECT * FROM student where name isn't like '%a% ';
SELECT * FROM student where name like ' ja% ';
SELECT * FROM student where name isn't like '%[j,n]% ';
SELECT * FROM student where name like '%[j,n,a]% ';
SELECT * FROM student where name like '%[^ja,as,on]% ';
SELECT * FROM student where name like '%[ja_on]% ';
--in subquery
SELECT * from student where ID in (1, 2);
--not in is not therein
SELECT * FROM student where ID is not in (1, 2);
--is NULL is NULL
SELECT * FROM student where ' is null;
--is NOT NULL is NOT NULL
SELECT * FROM student where-is-is-not null;
--order by sort
SELECT * FROM student order by name;
SELECT * FROM student order by name Desc;
SELECT * FROM Student order by name ASC;
--group by group
Group statistics by age
Select count (age), student group by age;
Group statistics by sex
Select COUNT (*), sex from student group by sex;
Group statistics by age and sex, and sort
Select COUNT (*), sex from student group by sex;
Grouped by sex, and the records with IDs greater than 2 are sorted by sex at the end
Select COUNT (*), sex from student where ID > 2 GROUP by sex Order by sex;
Query ID is greater than 2 data, and the results after the operation are grouped and sorted
Select COUNT (*), (Sex * ID) New from student where ID > 2 GROUP BY sex * ID ORDER by sex * ID;
--group by all Group
Grouped by age, is all the age
Select COUNT (*), age from student group by all age;
--having Packet Filter Conditions
According to age group, filter age is empty data, and statistical grouping of the number and the actual age information
Select COUNT (*), the age from student group by age has the ' is ' not null;
Grouped by age and CID combination, the filtration condition is the CID greater than 1 records
Select COUNT (*), CID, sex from student group by CID, sex have cid > 1;
Grouped by age, the filter condition is that the number of record bars after grouping is greater than or equal to 2
Select COUNT (*), student group by Age has count (age) >= 2;
According to the CID and gender combination, the filtration condition is that the maximum value of CID greater than 1,cid is greater than 2
Select COUNT (*), CID, sex from student group by CID, sex have cid > 1 and Max (CID) > 2;
Ø nested subqueries
A subquery is a query that is nested within a SELECT, insert, UPDATE, or DELETE statement or other subquery. Subqueries can be used wherever an expression is allowed. Subqueries are also referred to as internal or internal selections, and statements that contain subqueries become external queries or external selections.
# from (select.. table) sample
Query the results of a table as a new table
SELECT * FROM (
Select ID, name from student where sex = 1
t where t.id > 2;
The statement in parentheses above is a subquery statement (an internal query). Outside is an external query, where the outer query can contain the following statement:
1. General select query containing general selection list components
2, a general from statement containing one or more table or view names
3. Optional WHERE clause
4, optional GROUP BY clause
5. Optional HAVING clause
# example
Query class information, statistics class student life
SELECT *, (SELECT COUNT (*) from student where cid = classes.id) as num
From classes order by Num;
# in, don't in clause query example
Query student information for these classes with a class ID greater than
SELECT * FROM student where CID (
Select ID from classes where ID > 2 and ID < 4
);
The query is not the student information of the class
SELECT * FROM student where CID (
Select ID from classes WHERE name = ' 2 class '
)
The result of the clause in or not in must be a column, and the result of this column will correspond to the preceding condition as a query condition. such as the ID of the CID corresponding clause;
# exists and NOT EXISTS clause query example
Query for student information with class ID
SELECT * FROM student where exists (
SELECT * FROM classes WHERE id = student.cid and id = 3
);
Query for student information not assigned to class
SELECT * from student where NOT exists (
SELECT * FROM classes where id = student.cid
);
exists and not EXISTS queries require an associated condition for an internal query and an external query, without which all information is queried. such as: ID equals student.id;
# Some, any, all clause query example
Information on the age of students in the class older than the class
SELECT * FROM student WHERE cid = 5 and age > All (
Select age from student WHERE cid = 3
);
SELECT * FROM student WHERE cid = 5 and age > any (
Select age from student WHERE cid = 3
);
SELECT * FROM student WHERE cid = 5 and age > Some (
Select age from student WHERE cid = 3
);
Ø Aggregate Query
1, distinct remove duplicate data
Select distinct sex from student;
Select count (Sex), count (Distinct sex) from student;
2, compute and COMPUTE by rollup query
Summary of age greater than
Select age from Student
Where Age > ORDER BY age Compute sum (age) by age;
Age information grouped by sex
Select ID, sex, age from student
Where age > order by sex, the Age compute sum (age) by sex;
Aggregated by age group
Select age from Student
Where Age > order by age, ID compute sum (age);
According to age group, age summary, ID find the maximum value
Select ID, age from student
Where Age > ORDER BY age Compute sum (age), Max (ID);
Compute is summarized before the result of the query, and the subsequent result set is the summarized information. You can add more than one rollup expression in the COMPUTE clause, and the information you can add is as follows:
A, optional by keyword. It is the specified row aggregation for each column calculation
b, row aggregate function name. Include Sum, AVG, MIN, max, COUNT, etc.
C, the column to which the aggregate function is to be executed
Compute by IS suitable for the first group after the summary of the business. The column following compute by must be the column that appears in the order by.
3, Cube Summary
Cube rollup and compute effects are similar, but the syntax is simpler and returns a result set.
Select COUNT (*), sex from student group by sex with Cube;
Select COUNT (*), Age, sum (age) from student where the-is-not-null group by age with Cube;
Cube to complete grouped totals with the GROUP BY statement
Ø sort function
The sort needs to be used in many places, and the query results need to be sorted and given a sequence number. Like what:
1, a table to sort, the number needs to increment the repeat
2, the students of the ranking, to get the position, ranking can be tied, but the number of the ranking is continuously increasing
3, in some sort of case, the need to jump the number of empty, although it is tied
Basic syntax
Sort function over ([group statement] sort clause [DESC][ASC])
Sort clause order BY column name, column name
Group clauses partition by group columns, grouped columns
# Row_number function
Increment sequential serial Number according to the sort clause
Ascending by name sort order
Select S.id, S.name, CID, C.name, Row_number () over (order by c.name) as number
From student S, classes c where cid = C.id;
# Rank function function
An incremented ordinal number is given according to the sort clause, but there is a juxtaposition and a skip
Sequential increments
Select ID, name, rank () over (order by CID) as rank from student;
Skip the same increment
Select S.id, S.name, CID, C.name, rank (order by c.name) as rank
From student S, classes c where cid = C.id;
# Dense_rank function
An incremented ordinal number is given according to the sort clause, but there is no jump
No skip, direct increment
Select S.id, S.name, CID, C.name, Dense_rank () over (order by c.name) as dense
From student S, classes c where cid = C.id;
# Partition by group clause
You can do an incremental sort of grouped data, and partition by can be used in conjunction with the above three functions.
Using partition by Group by class name, student ID sort
Select S.id, S.name, CID, C.name, Row_number () over (partition to C.name order by s.id) as rank
From student S, classes c where cid = C.id;
Select S.id, S.name, CID, c.name, rank (partition by C.name ORDER by S.id) as rank
From student S, classes c where cid = C.id;
Select S.id, S.name, CID, C.name, Dense_rank () over (partition to C.name order by s.id) as rank
From student S, classes c where cid = C.id;
# Ntile Average sort function
The data to be sorted is divided equally, and then sorted by division. The parameters in the ntile represent how many equal parts are divided.
Select S.id, S.name, CID, C.name,
Ntile (5) over (order by c.name) as Ntile
From student S, classes c where cid = C.id;
Ø Set operation
Operation of two groups of query results, intersection, and set, reduce set operation
1, Union and union all carry out the operation of the set
--union and no repetition
Select ID, name from student where name like ' ja% '
Union
Select ID, name from student where id = 4;
--Set and repeat
SELECT * FROM student where name like ' ja% '
UNION ALL
SELECT * from student;
2. Intersect for intersection operation
--intersection (same part)
SELECT * FROM student where name like ' ja% '
Intersect
SELECT * from student;
3, except to reduce the set operation
--minus set (except for the same part)
SELECT * FROM student where name like ' ja% '
Except
SELECT * FROM student where name like ' jas% ';
Ø Formula Table Expression
When querying tables, sometimes the intermediate tables need to be reused, and these subqueries are repeated query calls, not only inefficient, but also less readable and not conducive to understanding. Then the formula table expression solves this problem.
We can treat a formula table expression (CET) as a temporary result set, defined within the execution scope of a SELECT, insert, UPDATE, delete, or CREATE VIEW statement.
--An expression
With StatNum (ID, num) as
(
Select CID, COUNT (*)
From student
Where ID > 0
GROUP BY CID
)
Select ID, num from statnum an order by ID;
With StatNum (ID, num) as
(
Select CID, COUNT (*)
From student
Where ID > 0
GROUP BY CID
)
Select Max (ID), AVG (num) from StatNum;
Ø Connection Query
1. Simplify connection Query
--Simplify Join queries
Select S.id, S.name, C.id, c.name from student s, classes C where s.cid = C.id;
2, left Join
--Left connection
Select S.id, S.name, C.id, c.name from student s left join classes c on s.cid = C.id;
3. Right Join
--Right connection
Select S.id, S.name, C.id, c.name from student s right join classes C on s.cid = C.id;
4. INNER JOIN INNER JOIN
--INNER JOIN
Select S.id, S.name, C.id, c.name from student s inner JOIN classes C on s.cid = C.id;
--inner can be omitted
Select S.id, S.name, C.id, c.name from student s join classes c on s.cid = C.id;
5. Cross Join Cross Connection
--Cross Join query, result is a flute Descartes product
Select S.id, S.name, C.id, c.name from student s cross join classes C
--where s.cid = c.id;
6, from the connection (the same table to connect query)
--Self-connected
Select distinct s.* from student s, student S1 where S.id <> s1.id and s.sex = S1.sex;
Ø function
1. Aggregate function
Max Max, min minimum, count statistic, avg mean, sum sum, var variance
Select
Max (age) Max_age,
Min (age) Min_age,
Count (age) Count_age,
AVG (age) Avg_age,
SUM (age) Sum_age,
VAR (age) Var_age
from student;
2, Date Time function
Select DATEADD (Day, 3, getDate ());--Gatian
Select DATEADD (year, 3, GetDate ());--Gagnin
Select DATEADD (Hour, 3, getDate ());--plus hour
--Returns the number of date boundaries and time boundaries across two specified dates
Select DateDiff (Day, ' 2011-06-20 ', getDate ());
--the difference of seconds
Select DateDiff (Second, ' 2011-06-22 11:00:00 ', getDate ());
--the difference of hours
Select DateDiff (Hour, ' 2011-06-22 10:00:00 ', getDate ());
Select Datename (Month, getDate ());--Current month
Select Datename (minute, getDate ());--Current minute
Select Datename (Weekday, getDate ());--Current week
Select DatePart (Month, getDate ());--Current month
Select DatePart (Weekday, getDate ());--Current week
Select DatePart (Second, getDate ());--current number of seconds
Select Day (GetDate ());--Returns the current date days
Select Day (' 2011-06-30 ');--Returns the current date days
Select Month (GetDate ());--Return the current date month
Select month (' 2011-11-10 ');
Select year (GetDate ());--Returns the current date years
Select year (' 2010-11-10 ');
Select GetDate ();-Current system date
Select getUTCDate ();--UTC Date
3. Mathematical function
Select Pi ();--pi function
Select Rand, Rand (a), Rand (), Rand ();--Random number
Select Round (rand (), 3), round (rand (100), 5);--Exact decimal digits
--exact digits, negative numbers indicate before the decimal point
Select Round (123.456, 2), round (254.124,-2);
Select Round (123.4567, 1, 2);
4, Meta data
Select Col_name (object_id (' Student '), 1);--Return column name
Select Col_name (object_id (' Student '), 2);
--The column data type length
Select Col_length (' Student ', Col_name (object_id (' Student '), 2));
--The column data type length
Select Col_length (' Student ', Col_name (object_id (' Student '), 1));
--return type name, type ID
Select Type_name (type_id (' varchar ')), type_id (' varchar ');
--Returns the column type length
Select ColumnProperty (object_id (' Student '), ' name ', ' PRECISION ');
--Returns the index where the column is located
Select ColumnProperty (object_id (' Student '), ' sex ', ' ColumnId ');
5. String function
Select ASCII (' a ');--Character conversion ASCII value
Select ASCII (' A ');
Select char;--ascii value conversion character
Select char (65);
Select NCHAR (65);
Select NCHAR (45231);
Select NCHAR (32993);--unicode conversion characters
Select Unicode (' A '), Unicode (' in ');--Return Unicode encoding value
Select Soundex (' Hello '), Soundex (' World '), soundex (' word ');
Select Patindex ('%a ', ' ta '), Patindex ('%ac% ', ' Jack '), Patindex (' dex% ', ' dexjack ');--matching character index
Select ' A ' + space (2) + ' B ', ' C ' + spaces (5) + ' d ';--output blanks
Select CharIndex (' o ', ' Hello World ');--Find Index
Select CharIndex (' o ', ' Hello World ', 6);--Find Index
Select QuoteName (' Abc[]def '), QuoteName (' 123]45 ');
--Exact numbers
Select STR (123.456, 2), str (123.456, 3), str (123.456, 4);
Select STR (123.456, 9, 2), str (123.456, 9, 3), str (123.456, 6, 1), str (123.456, 9, 6);
Select difference (' Hello ', ' HelloWorld ');--compare strings the same
Select difference (' Hello ', ' world ');
Select difference (' Hello ', ' Llo ');
Select difference (' Hello ', ' hel ');
Select difference (' Hello ', ' hello ');
Select Replace (' Abcedef, ' e ', ' e ');--Replacement string
Select Stuff (' Hello World ', 3, 4, ' ABC ');--Specify position replacement string
Select Replicate (' abc# ', 3);--repeating string
Select SubString (' abc ', 1, 1), subString (' abc ', 1, 2), subString (' Hello Wrold ', 7, 5);--intercept string
Select Len (' abc ');--Return length
Select reverse (' SQL Server ');--Reverse string
Select Left (' leftstring ', 4);--Take the left-hand string
Select Left (' leftstring ', 7);
Select Right (' leftstring ', 6);--Take the right-hand string
Select Right (' leftstring ', 3);
Select LOWER (' abc '), Lower (' abc ');--lowercase
Select UPPER (' abc '), Upper (' abc ');--Uppercase
--Remove the left space
Select LTrim (' abc '), LTrim (' # abc# '), LTrim (' abc ');
--Remove the right space
Select RTrim (' abc '), RTrim (' # abc# '), RTrim (' abc ');
6. Security function
Select Current_User;
Select User;
Select USER_ID (), user_id (' dbo '), user_id (' Public '), user_id (' guest ');
Select USER_NAME (), user_name (1), user_name (0), user_name (2);
Select Session_user;
Select suser_id (' sa ');
Select Suser_sid (), Suser_sid (' sa '), suser_sid (' sysadmin '), Suser_sid (' serveradmin ');
Select Is_member (' dbo '), Is_member (' Public ');
Select Suser_name (), Suser_name (1), Suser_name (2), suser_name (3);
Select SUSER_SNAME (), SUSER_SNAME (0x01), SUSER_SNAME (0x02), SUSER_SNAME (0x03);
Select Is_srvrolemember (' sysadmin '), Is_srvrolemember (' serveradmin ');
Select Permissions (object_id (' student '));
Select System_user;
Select schema_id (), schema_id (' dbo '), schema_id (' guest ');
Select Schema_name (), schema_name (1), schema_name (2), schema_name (3);
7. System function
Select App_name ();--application name of the current session
Select CAST (as DateTime), cast (' as Money '), cast (' 0 ' as varbinary);--Type conversions
Select CONVERT (DateTime, ' 2011 ');--Type conversions
Select COALESCE (null, ' a '), coalesce (' 123 ', ' a ');--Returns the first non-empty expression in its argument
Select Collationproperty (' Traditional_spanish_cs_as_ks_ws ', ' CodePage ');
Select current_timestamp;--Current Time stamp
Select Current_User;
Select IsDate (GetDate ()), IsDate (' abc '), IsNumeric (1), IsNumeric (' a ');
Select Datalength (' abc ');
Select host_id ();
Select HOST_NAME ();
Select Db_name ();
Select Ident_current (' Student '), Ident_current (' classes ');--Returns the maximum value of the primary key ID
Select IDENT_INCR (' Student '), IDENT_INCR (' classes '),--id increment value
Select Ident_seed (' Student '), Ident_seed (' classes ');
SELECT @ @identity;--last self-added value
Select identity (int, 1, 1) as ID into the tab from student;--to create a tab in/1 Studeng the martyred of the table
SELECT * from tab;
SELECT @ @rowcount;--affect number of rows
SELECT @ @cursor_rows;--Returns the number of current qualifying lines for the cursor open on the connection
SELECT @ @error;--t-sql Error number
SELECT @ @procid;
8. Configuration function
Set Datefirst 7;--Sets the first day of the week, indicating Sunday
SELECT @ @datefirst as ' first day of the Week ', DATEPART (DW, getDate ()) as ' Today is Week ';
SELECT @ @dbts;--Returns the current database unique timestamp
Set language ' Italian ';
SELECT @ @langId as ' Language ID ';--return language ID
SELECT @ @language as ' language name ';--Returns the current language name
SELECT @ @lock_timeout;--Returns the current lock timeout setting for the current session (milliseconds)
SELECT @ @max_connections;--Returns the maximum number of simultaneous user connections that SQL Server instance allows
SELECT @ @MAX_PRECISION as ' MAX PRECISION ';--Returns the level of precision used for the decimal and numeric data types
The name of the local server for the SELECT @ @SERVERNAME--sql Server
SELECT @ @SERVICENAME;--Service name
SELECT @ @SPID;--current session process ID
SELECT @ @textSize;
SELECT @ @version;--Current database version information
9. System Statistic function
SELECT @ @CONNECTIONS;--Number of connections
SELECT @ @PACK_RECEIVED;
SELECT @ @CPU_BUSY;
SELECT @ @PACK_SENT;
SELECT @ @TIMETICKS;
SELECT @ @IDLE;
SELECT @ @TOTAL_ERRORS;
SELECT @ @IO_BUSY;
SELECT @ @TOTAL_READ;--Read disk count
SELECT @ @PACKET_ERRORS;--Number of network packet errors that occurred
SELECT @ @TOTAL_WRITE;--sqlserver the number of disk writes performed
Select PatIndex ('%soft% ', ' Microsoft SQL Server ');
Select PatIndex (' soft% ', ' Software SQL Server ');
Select PatIndex ('%soft ', ' SQL Server Microsoft ');
Select PatIndex ('%so_gr% ', ' Jsonisprogram ');
10. User-defined Functions
# View all functions of the current database
--Query all created functions
Select definition,* from sys.sql_modules m join sys.objects o on m.object_id = o.object_id
and type in (' fn ', ' if ', ' TF ');
# Create functions
if (object_id (' fun_add ', ' fn ') is not null)
Drop function Fun_add
Go
Create function Fun_add (@num1 int, @num2 int)
returns int
With EXECUTE AS caller
As
Begin
declare @result int;
if (@num1 is null)
Set @num1 = 0;
if (@num2 is null)
Set @num2 = 0;
Set @result = @num1 + @num2;
return @result;
End
Go
Call function
Select Dbo.fun_add (ID, age) from student;
--Custom function, string concatenation
if (object_id (' fun_append ', ' fn ') is not null)
Drop function Fun_append
Go
Create function Fun_append (@args nvarchar (1024), @args2 nvarchar (1024))
Returns nvarchar (2048)
As
Begin
return @args + @args2;
End
Go
Select Dbo.fun_append (name, ' abc ') from student;
# Modify function
Alter function fun_append (@args nvarchar (1024), @args2 nvarchar (1024))
Returns nvarchar (1024)
As
Begin
DECLARE @result varchar (1024);
--COALESCE returns the first value that is not NULL
Set @args = COALESCE (@args, ');
Set @args2 = COALESCE (@args2, ');
Set @result = @args + @args2;
return @result;
End
Go
Select Dbo.fun_append (name, ' #abc ') from student;
# Returns the table type function
--Returns the Table object function
Select Name, object_id, type from sys.objects where type of (' fn ', ' if ', ' tf ') or type like '%f% ';
if (exists (SELECT * from sys.objects where type (' fn ', ' if ', ' TF ') and name = ' Fun_find_sturecord '))
Drop function Fun_find_sturecord
Go
Create function Fun_find_sturecord (@id int)
Returns table
As
Return (SELECT * from student where id = @id);
Go
SELECT * from Dbo.fun_find_sturecord (2);