Simple queries in the database

1. Simple query

SELECT * FROM Info--Check all data
Select Code,name from Info--check the data for the specified column
Select Code as ' code name ', name as ' from Info '--Assign alias to column

2. Conditional query

SELECT * from Info where code= ' p001 '
SELECT * from Info where sex= ' true ' and nation= ' n001 '--multi-conditional and relational
SELECT * from Info where sex= ' true ' or nation= ' n001 '--multi-condition or relationship

3. Scope Query

SELECT * from Car where price>40 and price<50
SELECT * from Car where price between and 50

4. Discrete query

SELECT * from Car where Code in (' c001 ', ' c005 ', ' c010 ', ' c015 ')
SELECT * from Car where Code not in (' c001 ', ' c005 ', ' c010 ', ' c015 ')

5. Fuzzy query

SELECT * from Car where Name like '% BMW% '--check contains BMW's
SELECT * from Car where Name like ' BMW% '--look at the beginning of the BMW
SELECT * from Car where Name like '% BMW '--look at the end of BMW
SELECT * from Car where Name like ' BMW '-check equals BMW's

SELECT * from Car where Name like ' __e% '--check the third character is E's

% represents any number of characters

_ Represents a character

6. Sort queries

SELECT * from Car ORDER BY price ASC-in ascending order of prices
SELECT * from Car ORDER BY price desc--In descending order of prices
SELECT * from Car ORDER BY oil desc,price ASC-sorted in two fields, preceded by a secondary condition after the main condition

7. Paging Query

Select Top 5 * from Car
Select Top 5 * from car where Code not in (select Top 5 Code from car)

Current: page = 2; Display per page: row = 10;

Select Top row * from car where Code not in (select Top (page-1) *row Code from car)

8. Go to re-query

Select distinct Brand from Car

9. Group queries

Select brand from Car GROUP by Brand has count (*) >2

10. Aggregation function (statistical query)

Select COUNT (*) from Car--Query all data bars
Select COUNT (Code) from Car--Query all data bars

Select SUM (Price) from Car--sum
Select AVG (price) from Car--averaging
Select Max from Car--Maximum value
Select min (Price) from Car--minimum value

Simple queries in the database