Shulou(Shulou.com)06/02 Report--

Today, the editor will take you to understand what SQL Server's T-SQL advanced query refers to. The knowledge points in the article are introduced in great detail. Friends who think it is helpful can browse the content of the article with the editor, hoping to help more friends who want to solve this problem to find the answer to the problem. Let's follow the editor to learn more about "what is SQL Server's T-SQL advanced query?"

Basic common query-selectselect * from student;--all query all select all sex from student;--distinct filtering 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 column rename select id as number, name 'name', sex gender from student -- alias table name table renames select id, name, s.id, s.name from student. Select (age + id) col from student;select s.name +'-'+ c.name from classes c, student s where s.cid = c.idmuri where condition select * from student where id = 2select * from student where id > 7th select * from student where id

< 3;select * from student where id 3;select * from student where id >

= 3bot select * from student where id 3x select * from student where id!

< 5;--and 并且select * from student where id >

2 and sex = 1 words or select * from student where id = 2 or sex = 1 words between. And... It is equivalent to and select * from student where id between 2 and 5 select * from student where id not between 2 and 5 poster color 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'%% select * from student where name like 'ja%';select * from student where name not like'% [jMagn]%'; select * from student where name like'% [jjjjjjre] 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 in it select * from student where id not in (1,2);-- is null is empty select * from student where age is null;--is not null is not empty select * from student where age is not null;--order by sorting select * from student order by name Select * from student order by name desc;select * from student order by name asc;--group by groups are grouped by age, select count (age), age from student group by age; by gender, select count (*), sex from student group by sex; by age and sex, and sorted by select count (*), sex from student group by sex, age order by age Grouped by gender, and the records whose id is greater than 2 are sorted according to gender select count (*), sex from student where id > 2 group by sex order by sex; query the data with id greater than 2, and the results are grouped and sorted by select count (*), (sex * id) new from student where id > 2 group by sex * id order by sex * id -- group by all all groups are grouped according to age, is all age select count (*), age from student group by all age;--having grouping filter condition groups according to age, filter data with empty age, and count the number of groups and actual age information select count (*), age from student group by age having age is not null Grouped according to age and cid combination, filtering condition is select count (*), cid, sex from student group by cid, sex having cid > 1 for records with cid greater than 1; grouping according to age, filtering condition is that the number of records after grouping is greater than or equal to 2select count (*), age from student group by age having count (age) > = 2 Grouped according to cid and gender combination, the filter condition is that the maximum value of cid > 1 sex from student group by cid cid is greater than that of 2select count (*), cid, sex from student group by cid, sex having cid > 1 and max (cid) > 2; nested subqueries

A subquery is a query nested within a select, insert, update, or delete statement or other subquery. Subqueries can be used wherever expressions are allowed. A subquery is also known as an internal query or internal selection, and statements that contain subqueries also become external queries or external choices.

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 parentheses above is the subquery statement (internal query). On the outside is an external query, where the external query can contain the following statements:

1. Regular select queries with general selection list components

2. A regular from statement that contains 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, not in clause query example query class id greater than less than these classes 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班') in、not in 后面的子句返回的结果必须是一列，这一列的结果将会作为查询条件对应前面的条件。如cid对应子句的id； exists和not exists子句查询示例查询存在班级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和not exists查询需要内部查询和外部查询进行一个关联的条件，如果没有这个条件将是查询到的所有信息。如：id等于student.id； some、any、all子句查询示例select * from student where cid = 5 and age >

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.