preface

SQL query statement is the development of the most used is also the most important statement, our network life is now all in the query operation, such as: open wechat to see circle of friends, jingdong Taobao shopping goods, baidu to find some things, brush the headlines on the phone and so on. Query statement is more flexible, there are many kinds of usage, master them to our program development has an important role.

Basic query statements

The basic syntax of a query statement is:

Select * from table name;Copy the code

The field list can contain multiple fields separated by commas, for example:

Select * from table_name where table_name = 1; select * from table_name where table_name = 1;Copy the code

* can also be used to represent all fields, such as:

Select * from table_name;Copy the code

Code examples:

Select * from tb_student;Copy the code

Select stu_id,stu_name,stu_age from tb_student;Copy the code

The query uses the display alias

In query results, column names can be replaced with alias syntax:

Select field 1 alias, field 2 alias... From the name of the table;Copy the code

Code examples:

Select stu_id id,stu_name name,stu_age age from tb_student;Copy the code

Query keyword WHERE

Most of the time, when we do a query, we need to filter the results by some criteria. Conditional query:

Select * from table_name where table_name = 1;Copy the code

Code examples:

Select * from tb_student where stu_id = 2;Copy the code

Select * from tb_student where stu_age >= 20;Copy the code

Query IN for keyword

In (value 1, value 2…) Code examples:

Select * from tb_student where stu_address in ('北京',' 北京',' 北京');Copy the code

Query BETWEEN of the keyword

Field names in a range between values 1 and 2 examples of this code:

Select * from tb_student where stu_age between 20 and 25;Copy the code

Query keyword LIKE fuzzy query

Sometimes we need to query not so precise conditions, such as: student surnamed Zhang, student with a small name, etc. Field name like ‘wildcard string’; The wildcards include: % indicates any character of any length. _ indicates any character.

Select * from tb_student where stu_name like '%';Copy the code

Select * from tb_student where stu_telephone like '%3333';Copy the code

Select * from tb_student where stu_name like '% % %';Copy the code

Query keyword IS NULL conditional query

Select * from table where not null is [not] null

Select * from tb_student where stu_telephone is null;Copy the code

Query keyword AND multi – condition query

Use and to join two conditions. Both conditions must be true for the whole condition to be true.

Select * from tb_student where stu_age > 25 and stu_gender = 'female '; select * from tb_student where stu_age > 25 and stu_gender =' female ';Copy the code

Query keyword OR multi – condition query

Use OR to join two conditions. Only one of the conditions needs to be true, and the whole condition is true. Example of this code:

Select * from tb_student where stu_address = '北京' or stu_address = '北京';Copy the code

Query the DISTINCT keyword

In the query result, many fields have duplicate values. The distinct value can be removed. Select distinct from table… ; Code examples:

Select distinct stu_address from tb_student;Copy the code

Query keyword ORDER BY sort

The query we can prioritize, fields such as commodity price, sales, etc by the sort of grammar is: select * from table name order by field [asc | desc]; Order by (asC); order by (desc); Code examples:

Select * from tb_student order by stu_age;Copy the code

Select * from tb_student order by stu_age desc;Copy the code

Query keyword LIMIT page

Sometimes we have more data in the table, the query can be divided into multiple pages, so that the query speed will be improved, the user is also easier to operate. Syntax: select * from table name limit start position, length; Note: the start position is 0. The first line position is 0. The start position can be omitted. Length is the number of recorded lines on the page. Code examples:

Select * from tb_student limit 0,5; Select * from tb_student limit 5,5; Select * from tb_student limit 10,5;Copy the code





Select * from tb_student order by stu_age limit 1;Copy the code

conclusion

In this chapter, we learned basic query statements, including where query, in query multiple values, between query range, conditional join using AND and OR, order by sorting, limit paging, and so on. Later we will study advanced query methods such as inner join, outer join, and subquery. Please give a thumbs up if it helps you 🙂