SQL Query (part 1)
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;
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;
* can also be used to represent all fields, such as:
Select * from table_name;
Code examples:
— Query all fields
select * from tb_student;
— Query some fields
select stu_id,stu_name,stu_age from tb_student;
The query uses the display alias
In query results, column names can be replaced with aliases
Grammar:
Select field 1 alias, field 2 alias… From the name of the table;
Code examples:
Alias is used when querying
Select stu_id,stu_name,stu_age from tb_student;
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;
Code examples:
Select * from student where id = 2
select * from tb_student where stu_id = 2;
Select * from student where age > 20
select * from tb_student where stu_age >= 20;
Query IN for keyword
Indicates that the query field is any of multiple values
Field name in (value 1, value 2….)
Code examples:
Select * from student where hometown is Wuhan or Beijing or Chengdu
Select * from tb_student where stu_address in (‘ Beijing ‘,’ wuhan ‘,’ Chengdu ‘);
Query BETWEEN of the keyword
Indicates that the value of a field is in a range
Field name between Values 1 and 2
Code examples:
Select * from student where age between 20 and 25
select * from tb_student where stu_age between 20 and 25;
Query keyword LIKE fuzzy query
Sometimes we need to query for less precise conditions, such as: students with the surname Zhang, students with the first name and so on
Field name like ‘wildcard string ‘;
Wildcards include:
% represents a character of any length
_ stands for any character
Code examples:
Select * from student where name = ‘zhang’
Select * from tb_student where stu_name like ‘%’;
Select * from student where phone number ending in 3333
select * from tb_student where stu_telephone like ‘%3333’;
Select * from student where name < middle school
Select * from tb_student where stu_name like ‘% % %’;
Query keyword IS NULL conditional query
Query for records whose fields are empty, or add NOT if the fields are not empty
Field is [not] null
Select student whose mobile phone number is not specified
select * from tb_student where stu_telephone is null;
Query keyword AND multi – condition query
Use and to join two conditions. Both conditions must be true for the whole condition to be true
Condition 1 and condition 2
Code examples:
Select * from female student where age > 25
Select * from tb_student where stu_age > 25 and stu_gender = ‘female ‘;
Query keyword OR multi – condition query
Use OR to connect two conditions. Only one condition needs to be true for the whole condition to be true
Condition 1 or condition 2
Code examples:
Select * from male student whose native place is Beijing or Wuhan
Select * from tb_student where stu_address = ‘Beijing’ or stu_address = ‘Wuhan ‘;
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:
— Check where all students are from
select distinct stu_address from tb_student;
Query keyword ORDER BY sort
When we query, we can sort the fields, such as goods by price, sales and so on
The syntax for sorting is:
Select * from table name order by field [asc | desc];
Order by (asC); order by (desc);
Code examples:
— Students are sorted in ascending order of age
select * from tb_student order by stu_age;
— Students are sorted in descending order of age
select * from tb_student order by stu_age desc;
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.
Grammar:
Select * from table_name limit 1;
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:
— Query students, each 5 acts one page, divided into multiple pages query
Select * from tb_student limit 0,5;
Select * from tb_student limit 5,5;
Select * from tb_student limit 10,5;
Select * from youngest student
select * from tb_student order by stu_age limit 1;
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.