SQL Server Transact-SQL advanced Query

Advanced queries are most frequently used in databases and are also the most widely used.

Ø basic common Query

-- Selectselect * from student; -- all queries all select all sex from student; -- distinct filters duplicate 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; -- rename the select ID as number, name 'name', sex gender from student; -- rename the select ID, name, S. ID, S. name from stude Nt s; -- 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 select * from student where ID> 2 and sex = 1; -- or select * from student where id = 2 or sex = 1; --... and... 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 student where name not like '% A %'; select * from student where name like 'ja % '; select * from student where name not like' % [J, n] % '; select * from student where name like' % [J, n, a] % '; select * from student where name like' % [^ Ja,, on] % '; select * from student where name like' % [ja_on] % '; -- In subquery select * from student where ID in (1, 2 ); -- Not in is not where select * from student where id not in (1, 2); -- is null select * from student where age is null; -- is not null not empty select * from student where age 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 grouping select count (AGE), age from student group by age; select count (*) and sex from student group by sex are grouped by age and gender, and select count (*) is sorted (*), sex from student group by Sex, Age order by age; Grouping by gender, records with IDs greater than 2 are finally sorted by gender select count (*), sex from student where ID> 2 group by sex order by sex; query the data whose ID is greater than 2, and group and sort the results after the computation. Select count (*), (sex * ID) New from student where ID> 2 group by sex * ID order by sex * ID; -- group by all groups group by age, select count (*) for all ages, age from student group by all age; -- having grouping filtering conditions filter data with an empty age by age, also, select count (*) for counting the number of groups and actual age information, age from student group by age having age is not null; Grouping by age and CID combination, select count (*), CID, sex from student group by CID, sex having CID> 1; group by age, the filtering condition is that the number of records after the group is greater than or equal to 2 select count (*), age from student group by age having count (AGE)> = 2; group by CID and gender combination, the filter conditions are as follows: Cid is greater than 1, CID is greater than 2 select count (*), CID, sex from student group by CID, sex having CID> 1 and max (CID)> 2;

Ø nested subquery

A subquery is a query nested in a select, insert, update, or delete statement, or other subqueries. Subqueries can be used wherever expressions are allowed. A subquery is also called an internal query or an internal choice, and a statement containing a subquery is also an external query or an external choice.

# From (select... Table) Example

Use the query result of a table as a new table to query select * from (select ID, name from student where sex = 1) t where T. ID> 2;

The statement in the brackets above is the subquery Statement (internal query ). An external query is used. An external query can contain the following statements:

1. General SELECT query containing the general selection list component

2. A regular from statement that contains one or more table or view names

3. Optional WHERE clause

4. Optional group by clauses

5. Optional having clause

# Example

Query the class information and count the select *, (select count (*) from student where cid = classes. ID) as num from classes order by num;

# In, not in Clause query example

Query the student information of these classes whose class ID is greater than or equal to select * from student where CID in (select ID from classes where ID> 2 and ID <4 ); select * from student where CID not in (select ID from classes where name = '2 class ')

The results returned by the clause after in and not in must be one column, and the results of this column will be used as the query condition corresponding to the previous condition. For example, the ID of the CID clause;

# Exists and not exists clause query examples

Query the student information with the class ID: Select * from student where exists (select * from classes where id = student. CID and ID = 3); select * from student where not exists (select * from classes where id = student. CID );

Exists and not exists queries require an association condition between the internal query and the external query. If this condition is not found, all information is queried. For example, Id equals to student. ID;

# Some, any, all clause query example

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. Remove duplicate data from distinct

select distinct sex from student;select count(sex), count(distinct sex) from student;

2. Summary query of compute and compute

Select age from student where age> 20 order by age compute sum (AGE) by age for age greater than select ID, sex, Age from student where age> 20 order by Sex, Age compute sum (AGE) by sex; select age from student where age> 20 order by age, id compute sum (AGE); Grouping by age, age summary, find the maximum select ID for ID, age from student where age> 20 order by age compute sum (AGE ), max (ID );

Compute summarizes the query results, and the following result set is the summary information. You can add multiple aggregate expressions in the compute clause. The information you can add is as follows:

A. Optional by keyword. It calculates the specified row aggregation for each column.

B. Name of the row aggregate function. Including sum, AVG, Min, Max, Count, etc.

C. columns for which the aggregate function is to be executed

Compute by is suitable for businesses that are grouped first and then summarized. The columns after compute by must be columns in order.

3. cube Summary

The summary of cube is similar to that of compute, but the syntax is concise and a result set is returned.

select count(*), sex from student group by sex with cube;select count(*), age, sum(age) from student where age is not null group by age with cube;

The cube should be combined with the group by statement to complete group summary.

Ø sorting Function

Sorting is required in many places. You need to sort the query results and give the sequence number. For example:

1. Sort a table. The sequence numbers must be incremental and not repeated.

2. Sort the scores of the students to obtain the rankings. The rankings can be tied together, but the ranking numbers are continuously increasing.

3. In some sort cases, the sequence number must be skipped, although it is a parallel

Basic syntax

Sorting function over ([grouping statement] sorting clause [DESC] [ASC]) sorting clause order by column name, column name grouping clause partition by grouping column, grouping Column

# Row_number Function

Returns an incremental continuous sequence number based on the sort clause.

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

The ascending sequence number is given according to the sort clause, but there is a parallel and skip null

Select ID, name, rank () over (order by CID) as rank from student; skip the same incremental 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;

# Dense_rank Function

The ascending sequence number is given based on the sort clause, but there is a column that does not skip null.

Do not skip, directly 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 clause

The partition by function can be used together with the preceding three functions.

Use partition by to group by class name and sort student IDs by select S. ID, S. name, CID, C. name, row_number () over (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, rank () over (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 by C. name order by S. ID) as rank from student s, classes C where cid = C. ID;

# Ntile average sorting Function

The data to be sorted is evenly divided and then sorted by equals. The parameter in ntile indicates the number of equals points.

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 Operations

Perform intersection, union, and subtraction operations on the two sets of query results.

1. Union and Union all operations

-- Union Union, no repeated select ID, name from student where name like 'ja % 'unionselect ID, name from student where id = 4; -- Union, repeated select * from student where name like 'ja % 'Union allselect * from student;

2. Intersect intersection calculation

-- Intersection (same part) Select * from student where name like 'ja % 'intersectselect * from student;

3. Reduce set operation with distinct T

-- Subtract set (except for the same part) Select * from student where name like 'ja % 'using tselect * from student where name like 'jas % ';

Ø expression of a public table

When querying a table, sometimes the intermediate table needs to be used repeatedly. These subqueries are called by repeated queries, which is not only inefficient but also easy to understand. Then the expression of the Public table can solve this problem.

We can regard a public table expression (CET) as a temporary result set and define it within the execution range of select, insert, update, delete or create view statements.

-- Expression with statnum (ID, num) as (select CID, count (*) from student where ID> 0 group by CID) Select ID, num from statnum 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 cwhere S. cid = C. ID;

2. Left join

-- Left join 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 join

-- Right join 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 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 join classes C on S. cid = C. ID;

5. Cross join

-- Cross join query, the result is a flute product select S. ID, S. name, C. ID, C. name from student s cross join classes C -- where S. cid = C. ID;

6. Self-join (query connections for the same table)

-- Select distinct S. * from student s, student S1 where S. ID <> s1.id and S. Sex = s1.sex;

Ø Functions

1. Aggregate functions

Max maximum, Min minimum, Count statistics, AVG average, 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 and Time Functions

Select dateadd (day, 3, getdate (); -- add day select dateadd (year, 3, getdate (); -- add year select dateadd (hour, 3, getdate (); -- add hour -- return the number of date boundary and time boundary across two specified dates select datediff (day, '2017-06-20 ', getdate ()); -- select datediff (second, '2017-06-22 11:00:00 ', getdate (); -- select datediff (hour, '2017-06-22 10:00:00 ', getdate (); select datename (month, getdate (); -- select datename (minute, getdate () of the current month; -- select datename (weekday, getdate (); -- select datepart (month, getdate () of the current week; -- select datepart (weekday, getdate () of the current month; -- select datepart (second, getdate (); -- select Day (getdate () in the current number of seconds; -- returns select Day ('2017-06-30 ') of the current date '); -- returns the current date day select month (getdate (); -- returns the current date month select month ('2017-11-10 '); Select Year (getdate ()); -- return the current date year select year ('1970-11-10 '); select getdate (); -- current system date select getutcdate (); -- UTC date

3. mathematical functions

Select Pi (); -- pi function select rand (100), Rand (50), Rand (), Rand (); -- random number select round (RAND (), 3 ), round (RAND (100), 5); -- precise decimal places -- precise digits, negative number indicates select round (123.456, 2), round (254.124,-2) before the decimal point ); select round (123.4567, 1, 2 );

4. Metadata

Select col_name (object_id ('student '), 1); -- returns the column name select col_name (object_id ('student'), 2 ); -- select col_length ('student ', col_name (object_id ('student'), 2); -- select col_length ('student ', col_name (object_id ('student '), 1); -- return type name, type idselect type_name (type_id ('varchar'), type_id ('varchar '); -- return the column type length select columnproperty (object_id ('student '), 'name', 'precision'); -- return the column index position select columnproperty (object_id ('student '), 'Sex ', 'columnid ');

5. String Functions

Select ASCII ('A'); -- character conversion ASCII value select ASCII ('A'); select char (97); -- ASCII value conversion character select char (65 ); select nchar (65); select nchar (45231); select nchar (32993); -- Unicode conversion character select Unicode ('A'), Unicode ('中 '); -- returns the unicode encoded values 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 '); -- query the index select charindex ('O', 'Hello world', 6); -- query the index select quotename ('abc [] def '), quotename ('1970] 45'); -- exact number select STR (123, 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 the same string select difference ('hello', 'World'); select difference ('hello', 'llo'); select difference ('helloworld ', 'hel'); select difference ('hello', 'Hello'); select Replace ('abcedef ', 'E', 'E '); -- replace the string select stuff ('Hello world', 3, 4, 'abc'); -- specify the position to replace the string select replicate ('abc # ', 3 ); -- repeated string select substring ('abc', 1, 1), substring ('abc', 1, 2), substring ('Hello wrold ', 7, 5 ); -- truncates the string select Len ('abc'); -- returns the select reverse ('sqlserver '); -- reverses the string select left ('leftstring', 4 ); -- select left ('leftstring', 7); select right ('leftstring', 6); -- select right ('leftstring', 3 ); select lower ('abc'), lower ('abc'); -- lower case 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 Functions

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 functions

Select app_name (); -- select cast (2011 as datetime), cast ('10' as money), cast ('0' as varbinary ); -- type conversion select convert (datetime, '20140901'); -- type conversion select coalesce (null, 'A'), coalesce ('20140901', 'A '); -- return the first non-empty expression in the parameter select collationproperty ('traditional _ spanish_cs_as_ks_ws ', 'codepage'); select 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 ('class'); -- returns the maximum value of the primary key ID select ident_incr ('student '), ident_incr ('classe'); -- the incremental value of ID: Select ident_seed ('student '), ident_seed ('class'); select @ identity; -- The last auto-increment value Select Identity (INT, 1, 1) as ID into tab from student; -- convert the attribute of the studeng table, create a tabselect * From tab in the form of/1 auto increment; select @ rowcount; -- 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 code select @ procid;

8. Configure Functions

Set datefirst 7; -- set the first day of each week, which indicates that on Sunday select @ datefirst as 'Day of the Week', datepart (DW, getdate () as 'Today is Week '; select @ dbts; -- returns the current database's unique timestamp set language 'Italian '; select @ langid as 'language id '; -- return the language idselect @ language as 'language name'; -- returns the current language name select @ lock_timeout; -- returns the current lock timeout setting of the current session (in milliseconds) Select @ max_connections; -- return the maximum number of user connections allowed by the SQL server instance at the same time select @ max_precision as 'max precision '; -- return the precision level used by decimal and numeric Data Types Select @ servername; -- SQL Server's local server name: Select @ servicename; -- service name: Select @ spid; -- current session process idselect @ textsize; select @ version; -- current database version information

9. System statistical functions

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 count select @ packet_errors; -- select @ total_write; -- select patindex ('% Soft %', 'Microsoft sqlserver ') for the number of disk writes executed by sqlserver; select patindex ('soft %', 'Software sqlserver '); select patindex ('% soft', 'sqlserver 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_idand type in ('fn ', 'if', 'tf ');

# Create a 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 callerasbegindeclare @ result int; If (@ num1 is null) set @ num1 = 0; If (@ num2 is null) set @ num2 = 0; set @ result = @ num1 + @ num2; return @ result; endgo calls the select DBO function. 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 (1024), @ args2 nvarchar (1024) returns nvarchar (2048) asbeginreturn @ ARGs + @ args2; endgoselect DBO. fun_append (name, 'abc') from student;

# Modifying Functions

Alter function fun_append (@ ARGs nvarchar (1024), @ args2 nvarchar (1024) returns nvarchar (1024) asbegindeclare @ result varchar (1024 ); -- coalesce returns the first non-null value set @ ARGs = coalesce (@ ARGs, ''); Set @ args2 = coalesce (@ args2 ,'');; set @ result = @ ARGs + @ args2; return @ result; endgoselect DBO. fun_append (name, '# abc') from student;

# Return table functions

-- Returns the Select name, object_id, type from sys. objects where type in ('fn ', 'if', 'tf') or type like' % 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 (@ id int) returns tableasreturn (select * from student where id = @ ID); goselect * From DBO. fun_find_sturecord (2 );