Database---T-SQL statement: Query Statement (ii)

> Enquiry:

I. Querying ALL data:

SELECT * FROM Info---query all data (rows)

Select name from Info---query specific column (Name column)

Select Name,code from Info---query a specific two columns (Name and Code column)

Two. According to the conditions to check

SELECT * from Info where code= ' p001 ' a conditional query (traverse every data check out)

SELECT * from Info where code= ' p001 ' and nation= ' n003 ' multi-condition and relational query

SELECT * from Info where name= ' hu June ' or nation= ' n001 ' multi-condition or relational query

SELECT * from Car where price >=50 and <=60 range query (can be used, not recommended)

SELECT * from Car where price between 60 and range query (recommended)

Three. Fuzzy query (also belongs to conditional query, fuzzy query is for string query)

SELECT * from Car where Name '% Audi '----% is a wildcard, representing any n characters

SELECT * from Car where name like '% Audi% '---represents a string in Name as long as there is an Audi that can have n strings before and after

SELECT * from Car where Name like ' _ Audi ' _ Wildcard: represents any one character

Four. Sorting

SELECT * from Car ORDER BY Price (ASC) in ascending order (default ascending order)

SELECT * from Car ORDER BY price desc in descending order of prices

SELECT * FROM-Car ORDER BY-price desc, oil desc (who writes in front of first platoon) is sorted by two columns, preceded by the main

Five. Statistical functions (Aggregation functions)

Select COUNT (Code) from Car query table how many data

If the parentheses are *, then iterate through each column of data, if the parentheses are code, then just look at the data in code, if there is data, even one; in order to perform faster, the primary key is usually used

Maximum value of select Max from Car fetch price

Select min (price) the minimum value from Car pickup

Select SUM (Price) The sum of the prices from Car fetch

Average of Select AVG (price) from Car fetch prices

Six. Group queries

SELECT * FROM Car GROUP by brand → Select brand from Car GROUP by brand → select count (Bran d) from Car Group by Brand

Select brand from Car GROUP by Brand has Conut (*) >2 query for all series with a quantity greater than 2

Seven. Paging Query

SELECT * from Car limit 5, 5 skip several (previous parameters) data fetch several data (later parameters)

Select Top5 from Car (represents the first five data in SQL Server, but is not available in MySQL)

Eight. Go to re-query

Select distinct from Brand from Car (only the first one)

Database---T-SQL statement: Query Statement (ii)