SQL Action Statements

Advanced queries for SQL Server T-SQL advanced queries are most frequently used in databases and are the most widely used. Ø Basic Common Query--selectselect * 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;s Elect * from student where ID <> 3;select * from student where ID >= 3;select * from student where ID <= 5;sel ECT * 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 ... Equivalent to 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 like '%[a][o]% '; select * FROM St Udent where name not as '%a% '; select * from student where name is like ' ja% '; select * from student where name does like '%[ j,n]% '; select * from student where name like '%[j,n,a]% ', select * from student where name like '%[^ja,as,on]% '; select * FR Om student where name 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 n AME ASC; --group by grouping Select COUNT (age) as a group of ages from student group by; Group statistics by gender SelectCount (*), sex from student group by sex, grouping statistics according to age and gender combination and sorting select COUNT (*), sex from student group by sex, ages order by; 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; query ID greater than 2 and complete the result of the operation Group and Sort 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 the age group, the filter condition is grouped after the number of records is greater than or equal to 2select count (*), the ages from student group by age has count (age) >= 2;  Grouped by CID and gender combination, the filter condition is CID greater than 1,cid maximum value greater than 2select 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 outerSelected by the department. # The From (SELECT ... table) example queries a table query result as a new table for a SELECT * FROM (select ID, name from student where sex = 1) t where T. ID > 2; The statement in parentheses above is a 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 containing one or more table or view names, 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 fro  m 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 exists 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 query Requires an association between an internal query and an external queryThe condition, if not, 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 class students older than the class select * FROM student WHERE CID = 5 and ages > All (S Elect age from student 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 the age group, the age summary, the ID to find the maximum of the select ID, the ages from student where Age > The ORDER by compute sum (age), Max (ID), compute is summarized before the results of the query , followed by a 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 everyColumn evaluates the specified row aggregation B, row aggregate function name. Includes sum, AVG, MIN, Max, Count, and so on, the column to which the aggregate function is to be performed compute by is appropriate 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-is not null Group by age with Cube;cube to complete the grouping summary with the GROUP BY Statement Ø sort function sorting is needed in many places, and the query results need to be sorted and numbered.  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 syntax sorting function over ([grouping statements] 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 Select s.id by the order of the sort clauses, and the ascending sequential sequence number is ordered by name, S.N  Ame, 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 the increment based on the sort clauseSerial number, but there is a side-by-side not skipping, direct incrementing 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 Ran K 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, do not repeat the Select ID, name from student where the name like ' ja% ' Unio Nselect ID, name from student where id = 4; --Set and repeat Select * FROM student where name like ' ja% ' union allselect * from student;  2, intersect the intersection operation--intersection (the same part) SELECT * from student where the name like ' ja% ' intersectselect * from student;  3, except to reduce the set operation-minus the set (except the same part) SELECT * from student where the name like ' ja% ' exceptselect * 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, nu M from StatNum order by ID; With StatNum (ID, num) as (select CID, COUNT (*) from student where ID > 0 GROUP by CID) select Max (id), AV  g (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 right joins classes C on s.cid = C.id; 4. Inner join within joins--internal connection Select S.id, S.name, C.id, c.name from student s inner joins 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 joins-cross join 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) C  Ount_age, AVG (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 ());--Small differenceHours 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 ());--Returns the current date month Select month (' 2011-11-10 '); Select year (GetDate ());- -Returns the current date year of select (' 2010-11-10 '); select GetDate ();--Current system date Select getUTCDate ();--utc date 3, mathematical 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 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 Idselect type_name (type_id (' varchar ')), type_id (' varchar ');--Return 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 convert character select char (n); Select NCHAR (65) ; Select nchar (45231); select nchar (32993);--unicode convert character Select Unicode (' A '), Unicode (' Medium ');--Return Unicode encoding 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 Select QuoteName (' Abc[]def '), QuoteName (' 123]45 ');--Exact number 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.4 9, 6); Select difference (' Hello ', ' HelloWorld ');--Compare strings same 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); Leftstring ', 7); 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 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 SE Lect 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 (); selectIdent_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;--the martyred of the Studeng table, create a tabselect * from Tab;select @ @rowcount in the form of/1;--affects the number of rows 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 ';--return language idselect @ @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 user connections that the SQL Server instance allows concurrently 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 Idselect @ @textSize; select @ @version;--Current database version information 9, System statistics function SELECT @ @CONNECTIons;--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 times select @ @PACKET_ERRORS;--The number of network packet errors that occurred select @ @TOTAL_WRITE;-- Number of disk writes performed by SQL Server 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.obje  Ct_idand type in (' fn ', ' if ', ' TF ');     # Create function if (object_id (' fun_add ', ' fn ') is not null) Drop function Fun_addgocreate function fun_add (@num1 int, @num2 int)        Returns Intwith EXECUTE AS Calleras begin declare @result int;        if (@num1 is null) set @num1 = 0;        if (@num2 is null) set @num2 = 0;        Set @result = @num1 + @num2;    return @result; Endgo 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_appendgocreate function fun_append (@args    nvarchar (1024x768), @args2 nvarchar (1024x768)) returns nvarchar (2048) as begin return @args + @args2;  Endgo 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 @result varchar (1024);        --COALESCE returns the first non-null value set @args = COALESCE (@args, ');        Set @args2 = COALESCE (@args2, ');;        Set @result = @args + @args2;    return @result;  Endgo 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_sturecordgocreate function Fun_find_sturecord (@idint) returns TABLEAS return (SELECT * from student where id = @id), go select * from Dbo.fun_find_sturecord (2); 

SQL Action Statements