--------------------------------------------Basic Common Query--------------------------------------I made a watch with a simple practice. Today I looked at the SQL Server T-hoojo High-level query of the great God, I can only understand the basis of the current level, so I share the part of my contact and understanding. --Query All
SELECT * FROM [dbo]. [Table]
--Find out all names
Select all name from [dbo]. [Table]
--Filter out repetitive sex
SELECT distinct sex from [dbo]. [Table]
--Statistics on how many messages are in the table
Select COUNT (*) from [dbo]. [Table]
--count the number of messages for all names in the table
Select count (name) from [dbo]. [Table]
--Statistics on how many messages are filtered out of duplicate sex
Select COUNT (Distinct sex) from [dbo]. [Table]
-The first 5 names
Select Top 5 name from [dbo]. [Table]
--If you want to know all the information, replace name with *----------------------
--Rename naming (three types of spaces, as, single quotes)--often spaces
Select ID number, name as name, sex ' gender ' from [dbo]. [Table]
--Table renaming (two way spaces, as)--often spaces
SELECT * FROM [dbo]. [Table] as T
SELECT * FROM [dbo]. [Table] t
--Column operations (two columns added)
Total Select (id+age) from [Table]
--Combine two columns to display in a column-separated
Select name + '-' + age personal information from [Table]
--where condition (followed by column name operator value)
Select ID number, name name from [Table] where ID =2
SELECT * FROM [Table] where ID <=8
SELECT * FROM [Table] where ID >=2
SELECT * FROM [Table] where ID!=3
SELECT * FROM [Table] where ID!<7
SELECT * FROM [Table] where ID!>8
--The information that contains the name Qingshou is not displayed
SELECT * FROM [Table] where name<> ' Qingshou '
--and and OR usage
The usage of--and is and (satisfies all conditions)
SELECT * FROM [Table] where id=3 and age=14
The use of--or is either (satisfying one of these conditions)
SELECT * FROM [Table] where age=13 or age=14
--between ... Usage is between two people (show all information between two people in the condition)
SELECT * FROM [Table] where ID between 2 and 8
--Fuzzy query (% replace all input characters, _ replace one input character) keyword like substitution operator
SELECT * FROM [Table] where name like '% '
SELECT * FROM [Table] where name like '% electricity '
SELECT * FROM [Table] where name like ' _ Rain '
--subquery keyword in (under conditions)
SELECT * FROM [Table] where ID in (2,3,4,7)
SELECT * FROM [Table] where name in (' Money rain ', ' li-Electric ', ' Qingshou ')
--not in (not in condition)
SELECT * FROM [Table] where age not in (17,18,19)
SELECT * FROM [Table] where name is not in (' Money rain ', ' li-Electric ', ' Qingshou ')
--is Null (displays information that has a null value in the condition)
SELECT * FROM [Table] where-is null
--is NOT NULL (displays information that the value in the condition is not null)
SELECT * FROM [Table] where-is not null
--order by sort (desc descending, ASC Ascending)
SELECT * FROM [Table] ORDER by age
SELECT * FROM [Table] ORDER BY age DESC
SELECT * FROM [Table] ORDER by age ASC
--group by group
--(a column or n columns are queried and displayed separately when queried, and the query criteria correspond to the following group by)
Select age from [Table] GROUP by age
Select ID, name, age from [Table] GROUP by Id,name, age
--group statistics according to age
Select COUNT (*) age from [Table] GROUP by age
--group statistics according to gender
Select COUNT (*) sex from [Table] GROUP by sex
--grouping statistics by age and gender combination, and sorting
Select COUNT (*) age,sex from [Table] GROUP by Age,sex order by age
--Grouped by sex, and the records with IDs greater than 2 are finally sorted by sex
Select COUNT (*) ID greater than 2 people, sex from [Table] where id>2 GROUP by sex order by sex
--Query the data with IDs greater than 2, and then group and sort the results after the operation is completed
Select COUNT (*) ID greater than 2 people, (Age+id) total from [Table] where id>2 GROUP BY (Age+id) Order by (Age+id)
--group by all
--by age group, is all age
Select age from [Table] GROUP BY all age
--having Grouping Filter conditions
--to filter age-based data by age group, and to count the number of groups and actual age information
Select COUNT (*) Number of bars grouped, age from [Table] GROUP by age has an age was not null
--grouped by age and ID combination, filters are records with IDs greater than 1
Select Id,age from [Table] GROUP by Id,age have ID >1
--by age group, filter conditions are grouped after the number of records is greater than or equal to 1
The number of record bars after select COUNT (*) is grouped, age from [Table] group BY, and Count (*) >=1
--Grouped by ID and gender combination, the filter condition is that the maximum value of ID greater than 1,id is greater than 2
Select Id,sex from [Table] GROUP by Id,sex have id>1 and Max (ID) >2--------------------------------------------nested subqueries--------------------------------------
--Subqueries are internal queries or internal choices, and external queries or external choices are outside the subquery.
--This is the basic theory, I personally think that sub-query is the center, external query is the periphery, the central query statement is the base statement evolved, external query is a combination of subqueries query results
--The following is a simple subquery format
SELECT * FROM/* This is an external query * /
(select ID, name, age from [Table] where ID >1) t/* Internal query */
where T.age >15/* This is an external query * /
--the statement used in internal query is the basic common query statement
--External queries can contain basic common query statements
--for example where,group by,having,count,select query, multiple tables or view from statementsThe way of learning is endless, not ambitious, not to belittle oneself, to maintain the heart, can obtain more knowledge. --w Code Source
Basic query statements for SQL
