Mysql_select single table query

select * *Representing all

Query multiple fields

select Field 1, field 2, field 3

*Polymerization function count()**	Statistics	select count(*)
sum()	Summing up	select sum() Or select sum (CJ)
avg()	average value	select avg() Or select AVG (CJ)
max()	Maximum value	select max() Or select max (CJ)
min()	minimum value	select min() Or select min (CJ), min (CJ).

Examples:

#For the 001 students, the highest score, the lowest score, the total score, the average score and several subjects were inquired.

select max(cj),min(cj),sum(cj),avg(cj),count(xh)

from cjb

where xh=’001′

distinct

Duplicate removal

Single search to separate a result count (distinct condition).

#Which provinces and cities do the students in class 1024 refer to (distinct)?

select distinct jg as ‘Provinces and cities’

from xsb

where bj=’1024′

as field alias

form From… Table name

where +condition

1、Exact query: example: specify what to query. Where xm=’Zhang three’

2、Fuzzy query: like… Like%: matching any number of characters.

_：Match a character

select *

from xsb

where xm like ‘%Ice% ”

3、Logical operations (when applied to multiple conditions) and and or or

#For detailed information on Zhang three or Lee four, with an age greater than 20.

select *

from xsb

where (xm=’Zhang three’or xm=’Lee four’) and nl>’20’

4、Conditional operator >,<,>=,<=

#Enquiries for student information ranging from 20 to 23 (including 20 and 23)

select *

from xsb

where nl<=23 and nl>=20

5、Set operators in Innot in Be not in

#Query student information from cities outside Hebei, Henan and Shandong

select *

from xsb

where jg not in (‘Hebei, ‘Henan’, ‘Shandong’

6、Nonempty operation

is null Is empty

is not null Is not empty

#Query student ID with empty ID card number.

select *

from xsb

where sfzh is null

#Query student ID number is not empty.

select *

from xsb

where sfzh is not null

7、Interval operation

between…and…

Representing a closed interval

#Enquiries for student information ranging from 20 to 23 (including 20 and 23)

select *

from xsb

where nl between 20 and 23

group by +Conditional grouping field

Grouped according to the value of this field, the same value is a set.

Field 1, field 2

Grouping nested, first group by field 1.

Then each group is divided into two groups according to field 2.

#Statistics on the number of boys and girls in the student list

select count(*),xb

from xsb

group by xb

Conditions after grouping

having

Conditions after grouping

Applications must appear behind group by.

havingFollowed by aggregate function

#Student number for average score greater than 75

select avg(cj),xh

from cjb

group by xh

having avg(cj)>75

order byQuery results ascending and descending

order by Conditional desc descending order

order by Conditional ascending ASC does not write by default.

Applications must appear behind group by.

#3) Find all the books and unit prices of the higher education press. The results are sorted in descending order.

select sm,dj

from book

where cbdw=’Higher Education Press

order by dj desc

limit （Last)

Starting from record n+1, take M to record n and M.

#Inquire all the information of the score sheet, descending in descending order, (take the top 3).

select * select *

from cjb from cjb

order by cj desc order by cj desc

limit 0,3 limit 3

Main foreign key connection

references Relation

forgien key (Foreign key) references main table (primary key)

truncate Decimal decimal digits

truncate(max(dj),2)

2 decimal places removed.

Leave a Reply Cancel reply