SQL Server SQL advanced Query Statement summary

--select select * from student; --all query All the Select all sex from student; --DISTINCT filtering repeats select distinct sex from student; --count Statistics SELECT COUNT (*) from student; Select count (Sex) from student; Select count (Distinct sex) from student; --top fetch top N Records select Top 3 * from student; --alias Column name rename named Select ID as number, name ' names ', sex gender from student; --alias Table Name table rename Select ID, name, s.id, s.name from student s; --column column Operation Select (age + ID) col from student; Select S.name + '-' + c.name from classes C, student s where s.cid = C.id; --where Condition 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 not between 2 and 5; --like Fuzzy Query select * FROM student where name like '%a% '; SELECT * from student where name is like '%[a][o]% '; SELECT * from student where name is not like '%a% '; SELECT * from student where name is like ' ja% '; SELECT * from student where name is not like '%[j,n]% '; SELECT * from student where name is like '%[j,n,a]% '; SELECT * from student where name is like '%[^ja,as,on]% '; SELECT * from student where name is like '%[ja_on]% '; --in subquery SELECT * FROM student where ID in (1, 2); --not in is not where the select * from student where ID is not in (1, 2); --is NULL is a null SELECT * from student where the age is null; --is NOT NULL for a SELECT * from student where the age was 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 grouping Select count (age) in groups of ages from student group by; Grouping statistics by Gender select COUNT (*), sex froM student group by sex; Group statistics according to age and gender combination and sort select count (*), sex from student group by sex, aged order by age; Grouped by sex, and is the record with ID greater than 2 finally sorted by gender select COUNT (*), sex from student where ID > 2 GROUP by sex Order by sex; The query ID is greater than 2 data, and the results are grouped and sorted after the operation select COUNT (*), (Sex * ID) New from student where ID > 2 GROUP BY sex * ID ORDER BY sex * Id --group by all groups according to age group, is all ages select count (*), age from student group by all; --having group filtering conditions are grouped according to age, filtering age-free data, and counting the number of bars and actual age information for a group of SELECT COUNT (*), and the ages from student the group by ages having a is not null; Grouped by age and CID combination, filter condition is CID greater than 1 records select COUNT (*), CID, sex from student group by CID, sex have cid > 1; According to age group, the filter condition is that the number of records after grouping is greater than or equal to 2 select COUNT (*), and the ages from student group By is the Count (age) >= 2; Grouped by CID and gender combination, the filter condition is CID greater than 1,cid maximum value greater than 2 select COUNT (*), CID, sex from student group by CID, sex have cid > 1 and MAX (CID ) > 2; The nested subquery subquery is a query that is nested within a SELECT, insert, UPDATE, or DELETE statement or other subquery. Subqueries can be used anywhere that an expression is allowed to be used. A subquery is also called an internal query or an internal selection, and a statement that contains a subquery becomes an external query or an external selection. #The From (SELECT ... table) example queries the query result 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 the parentheses above is the subquery statement (internal query). Outside the query, where the outer query can contain the following statements: 1, General select query 2 containing the General selection list component, general from statement 3 with one or more table or view names, optional WHERE clause 4, optional GROUP BY clause 5, optional having child Sentence # example query class information, statistics class student life SELECT *, (SELECT COUNT (*) from student where cid = classes.id) as num from the classes ORDER by Num;  # in, no in clause query sample query class ID greater than less than the student information for these classes select * from student where CID in (select ID from classes where ID > 2 and ID < 4); The query is not the student information of the class select * from student where the CID not in (select ID from classes WHERE name = ' 2 class ') in, not after clauses return the result must be a column, which The result of a column corresponds to the previous condition as a query condition. As the ID of the CID corresponding clause; # exists and NOT EXISTS clause query sample query existence student information for class ID select * from student where exists (SELECT * from classes where ID = Student.cid and id = 3); Query does not assign student information for 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, and if none of this condition will be all the information that is queried. such as: ID equals student.id; # Some, any, all clause query sample query The age of the student of the class older than the class select * FROM student WHERE CID = 5 and ages > All (select aged from Studen t WHERE cid = 3); SELECT * FROM student WHERE cid = 5, and age > No (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 queries are aggregated for older than the Select age from student where ages > ORDER BY age Compute sum (aged) by; Aggregated age information by sex for older than by gender select ID, sex, ages from student where > order by sex, aged compute sum (by sex); Summary of the age group in the select from student where ages > order BY, ID compute sum (aged); According to age group, age rollup, id find maximum of select ID, ages from student where ages > ORDER BY age Compute SUM, max (ID); Compute is the result of the query, and the next result set is the aggregated information. You can add more than one summary expression in the COMPUTE clause, and you can add the following information: A, optional by keyword. It is each column that calculates the specified row aggregation B, row aggregate function name. including SUM, AVG, MIN, max, COUNT, etc. c, The column to which the aggregate function is to be performed compute by IS suitable for the business to be aggregated after the first grouping. The column after compute by must be the column that appears in the order by. 3, Cube Summary cube Summary and compute effect is similar, but the syntax is more concise, and returns a result set. Select COUNT (*), sex from student group by sex with Cube; Select COUNT (*), Age, sum (age) from student where age is not a null group by age with Cube; Cube to complete the grouping summary with GROUP BY statement Ø sorting function sorting is needed in many places, and the query results need to be sorted and given the ordinal number. For example: 1, the Order of a table, the sequence number needs to be incremented by 2, the grade of the students, the rank can be tied, but the rank of the serial number is continuously increasing 3, in some sort of case, the need to skip the empty sequence number, although it is a parallel basic grammar sort function over ([group statement] SORT clause [  DESC][ASC]) sort clause order BY column name, column name group clause partition by Group column, Group column # Row_number function increments the sequential ordinal by name by the order of the sort clause increment 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; The Rank function function gives the ascending ordinal number according to the sort clause, but there is a side-by-side and the skip-empty order increments the 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 () over (order by C.name) as rank from student s, classes c where cid = C.id; # The Dense_rank function gives incrementing ordinal numbers based on a sort clause, but there is no skip, and the direct increment of 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 grouping clauses can be done to increase the sorting of grouped data, partition by can be used in conjunction with the above three functions. Use partition by Group by class name, Student ID Sort select s.id, S.name, CID, C.name, Row_number () over (partition by c.name ORDER by S.id) as Ra NK from student S, classes c where cid = C.id; Select S.id, S.name, CID, C.name, rank () over (partition by c.name ORDER by S.id) as rank from student s, classes C where C id = c.id; Select S.id, S.name, CID, C.name, Dense_rank () over (partition by c.name ORDER by S.id) as rank from student s, classes C W Here cid = c.id; # Ntile The average sort function divides the sorted data and then sorts them by the same order. The parameters in the ntile represent how many halves 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 operations two sets of query results, the intersection, Union, Subtraction set operation 1, unions and union all to do--union and set operation, do not repeat the Select ID, name from student where name like ' ja% ' un Ion Select ID, name from student where id = 4; --Union, repeat select * from student where name like ' ja% ' UNION ALL SELECT * from student; 2, intersect 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 the same part) SELECT * from student where name like ' ja% ' except select * from student where name like ' jas% ‘; Ø when the formula table Expression query table, sometimes the intermediate table needs to be reused, these subqueries are repeated query calls, not only inefficient, and low readability, not conducive to understanding. Then the formula table expression can solve 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. --expression with statnum (ID, num) as (select CID, COUNT (*) from student where ID > 0 GROUP BY CID) SELECT ID, num from stat Num 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, simplified connection query-simplified join query Select S.id, S.name, C.id, c.name from student s, classes C where s.cid = C.id; 2. Left join--left JOIN connect select S.id, S.name, C.id, c.name from student s to 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 R join classes c on s.cid = C.id; 4, INNER join inside connection--Internal Connection Select S.id, S.name, C.id, c.name from student s inner JOIN classes C on s.cid = C.id; --inner can omit select S.id, S.name, C.id, c.name from student S joins classes c on s.cid = C.id; 5, Cross join intersection--Cross joins the query, the result is a flute Descartes product select S.id, S.name, C.id, c.name from student s crosses join classes C--where s.cid = C.id; 6, self-connection (the same table for connection query)--Self-connection 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 Min, count statistic, avg average, sum sum, var find variance select Max (age) Max_age, Min (age) Min_age, Count (age) Count_age, AV G (age) Avg_age, sum (age) Sum_age, VAR (age) Var_age from student; 2. Date Time function Select DateAdd (day, 3, getDate ());--Plus days select DATEADD (year, 3, GetDate ());--plus 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 ()); --Difference in seconds Select DateDiff (second, ' 2011-06-22 11:00:00 ', getDate ()); --Difference 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 number of days Select month (GetDate ());--Returns the current date month of Select month (' 2011-11-10 '); Select year (GetDate ());--Returns the current date years of select (' 2010-11-10 '); Select GetDate ();--Current system date Select getUTCDate ();--UTC date 3, math function Select Pi (),--pi function Select rand (+), rand (+), rand (), Rand ( );--Random number Select round (rand (), 3), round (rand (100), 5);--Exact decimal digits--exact number of digits, negative means 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 '); --Return column classType length Select ColumnProperty (object_id (' Student '), ' name ', ' PRECISION '); --Returns the index of the column where 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 convert character Select Unicode (' A '), Unicode (' Medium ');--Return Unicode encoded value Select SOUNDEX (' Hello '), Soundex (' World '), soundex (' word '); Select Patindex ('%a ', ' ta '), Patindex ('%ac% ', ' Jack '), Patindex (' dex% ', ' dexjack ');--Match character index select ' a ' + space (2) + ' B ', ' C ' + space (5) + ' d ';--Output Space Select CharIndex (' o ', ' Hello World ');--Find Index Select CharIndex (' o ', ' Hello World ', 6);--Find index Sele CT quoteName (' abc[]def '), QuoteName (' 123]45 '); --Accurate Digital 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 identical to select difference (' Hello ', ' world '); Select difference (' Hello ', ' Llo '); SElect difference (' Hello ', ' hel '); Select difference (' Hello ', ' hello ');  Select replace (' Abcedef ', ' e ', ' e ');--replace 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 ');--Invert string Select left (' leftstring ', 4); Select Right (' leftstring ', 6);--Take the left-hand string, select "(' Leftstring ', 3); Select LOWER (' abc '), Lower (' abc ');--Lowercase select upper (' abc '), Upper (' abc ');--uppercase--Remove 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 ();--the application name of the current session select CAST (as DateTime), cast (' as Money '), cast (' 0 ' as varbinary);--type conversion Select CONVERT (DateTime, ' 2011 ');--type conversion 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 timestamp 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-increment value Select identity (int, 1, 1) As ID into tab from student;--will Studeng table martyred, create a tab select with/1 self-increment form From Tab; SELECT @ @rowcount;--Affects row count select @ @cursor_rows;--Returns the number of currently qualified rows for the cursor opened on the connection select @ @error;--t-sql Error number select @ @procid; 8, configuration Function set Datefirst 7;--set the first day of the week, indicating Sunday select @ @datefirst as ' the first day of the Week ', DATEPART (DW, getDate ()) as ' Today is the week '; SELECT @ @dbts;--Returns the current database unique timestamp set language ' Italian '; SELECT @ @langId as ' Language ID ';--Returns the language ID select @ @language as ' Language name ';--Returns the current language name SELECT @ @lock_timeout;--Returns the current session's Current lock timeout setting (milliseconds) select @ @max_connections;--Returns the maximum number of user connections that the instance of SQL Server allows at the same time select @ @MAX_PRECISION as ' Max PRECISION ';-- Returns the precision level used by the decimal and numeric data types SELECT @ @SERVERNAME; the name of the local server for--sql server SELECT @ @SERVICENAME;--Service Name SELECT @ @SPID;-- Current session Process ID SELECT @ @textSize; SELECT @ @version;--Current database version information 9, System statistics function SELECT @ @CONNECTIONS;--evenThe number of pick 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;--The number of network packet errors that occurred select @ @TOTAL_WRITE;--sqlserver 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 function # View all functions of the current database--query all created functions select definition,* from sys.sql_modules m join sys.objects o m.object_id = o.obj ect_id and type in (' fn ', ' if ', ' TF '); # Create function 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 connection if (object_id (' fun_append ', ' Fn ') is not null) drop function Fun_append go create function fun_append (@args nvarchar (1024x768), @args2 nvarchar (1024x768)) retur NS nvarchar (2048) as begin return @args + @args2; End Go Select dbo.fun_append (name, ' abc ') from student; # Modify functions Alter function fun_append (@args nvarchar (1024x768), @args2 nvarchar (1024x768)) returns nvarchar (1024x768) as BEGIN declare @res Ult varchar (1024); --COALESCE returns the first non-null value set @args = COALESCE (@args, '); Set @args2 = COALESCE (@args2, ');; Set @result = @args + @args2; return @result; End Go Select dbo.fun_append (name, ' #abc ') from student; # return table type function--Return Table object function select Name, object_id, type from sys.objects where type in (' fn ', ' if ', ' tf ') or type '% ' f% '; if (exists (SELECT * from sys.objects where type in (' 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);

SQL Server SQL Advanced Query Statement summary (GO)