MySQL database query statement Select

1. Querying data for all fields in the table

Mysql> select * from student;

2. Specify columns to query, and control field names

Mysql> select name, score score from student;

3, cross-database query, the database name is added to the front of the table can

Mysql> select name, score score from Test.student;

4, the use of functions to query data, ifnull (Gender, "confidential") means the query Gender field, the content is output, if it is empty display "confidential"

Mysql> select name, score score, ifnull (Gender, "confidential") gender from Test.student;

5. Query conditions

Mysql> Select name,age,address from student where age=20; Query the Name,age,address field, conditional age equals 20

Mysql> Select name,age,address from student where age>20; Query name,age,address field, condition age greater than 20

Mysql> Select name,age,address from student where age<>20; Query name,age,address field, condition is age not equal to 20

6, Fuzzy Query, the main string

Mysql> SELECT * FROM student where Name is like "Li%"; % represents 0 or more strings

Mysql> SELECT * FROM student where Name is like "_ Li"; _ Denotes an arbitrary character

Mysql> SELECT * FROM student where Name isn't like "Li%"; The not-like representation of data other than XX

7, between, and interval query

Mysql> SELECT * from student where score between and 70; The query score is between 60-70 and can be queried using the following form

Mysql> SELECT * from student where score>=90 and score<=100;

Mysql> SELECT * FROM student where score<90 or score>100; In addition to data between 90-100, the following methods can also be

Mysql> SELECT * FROM student where score not between and 70;

8. query field NULL data

Mysql> SELECT * FROM student where Gender is null; Note: It is not possible to write gender= "null" here.

Mysql> SELECT * FROM student where Gender are NOT null; is not NULL query for data NOT NULL

9. Conditions in () and not in ()

Mysql> SELECT * FROM student the Where Address in ("Haidian District, Beijing"); Query the Address field for "Haidian District, Beijing", "Chaoyang District, Beijing" data

Mysql> SELECT * FROM student where Address isn't in ("Beijing Haidian District", "Beijing Chaoyang District"); Take counter

10. Query sort

Mysql> Select Name,score from student order by score Desc; Desc is descending, not specified by default ascending ASC

Mysql> Select Name,score,age from student ORDER by score Desc,gender desc; If the score field is duplicated, repeat the following criteria again.

11. Limit the number of query result bars

Mysql> SELECT * from student limit 2; Show only 2 rows

Mysql> Select Name,score,age from student order by score ASC limit 3;

Mysql> Select Name,score,age from student where gender= "male" ORDER BY score desc LIMIT 1;

Mysql> SELECT * FROM Student ORDER by rand () limit 2; Rand () is random, not recommended

12. Averaging

Mysql> Select AVG (score) results average from student; Averages can be averaged using the AVG () function

Think about it, if there is a classmate leave without taking the exam, then we should not give him the statistics of the average score? The answer is no, so use it reasonably when calculating the average, and do not participate in the AVG operation when the field is null.

13, Sum function sum ()

Mysql> select SUM (Score) total score from student;

14, statistics query out the number of data, such as statistics how many men

Mysql> Select COUNT (*) from student where gender= "male";

15. Find Max Max ()

Mysql> select Max (score) highest score from student;

16. Find min min ()

Mysql> Select min (score) highest score from student;

17, sub-query

Mysql> SELECT * FROM student where score= (select Max (score) from student); The following statement executes the Select MAX (score) from student first

18. Group Query

For example, how many people are there in statistics for boys, girls and non-gender-filled people?

Mysql> Select Ifnull (Gender, "confidentiality"), COUNT (*) Number of people from student group by Gender;

19. Eliminate duplicate data when querying

Mysql> SELECT DISTINCT dept from Stu; Distinct means de-duplication

MySQL database query statement select