Shulou(Shulou.com)05/31 Report--

This article mainly introduces "basic query sql sentence arrangement". In daily operation, I believe many people have doubts about basic query sql statement arrangement. The editor has consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts of "basic query sql statement arrangement". Next, please follow the editor to study!

Basic query sql statement

I. Overview:

Learning MySQL database must learn the query function of sql, to put it bluntly, using the database is a process of storing data, querying data, and using these data. Only by looking at it can we know whether we have successfully stored the data and whether we can use the data directly.

Second, specific sql

1. The basic syntax of querying the database:

The field name and content that select needs to query from specify the relevant table name conditions required for where query group by how to group the results order by how to sort the result bottle the second condition met by the having query limit qualifies the query output

(1): single table query

1. Query all fields

Select * from table name

2. Query the specified fields

Select field name from table name

3. Query specified data

Select * from table name where condition

4. Query with "in" keyword

Select * from table name where condition [not] IN. Element n); not: is an optional parameter, plus not indicates that the condition is not met within the set element: indicates the elements in the set, separated by commas

5. Scope query with "Between And"

Select * from table name where condition [not] between value 1 and value 2; translation: not: it is an optional parameter indicating that the condition is not satisfied within the specified range. Value 1: indicates the starting value of the range 2: indicates the end value of the range.

6. Character matching query with "like" [implement fuzzy query]

Like characters to achieve fuzzy query, generally in the search, retrieval and other parts of our web page to use the two kinds of wildcards used by fuzzy query: "%" and "_". "%": can match one or more characters, can represent a string of any length "_": matches only one character, such as m; 3 characters starting with m and ending with n select * from table name where condition like'% asides; query all data in this table that contains the character a.

7. Multi-conditional query with "And"

Select * from table name where condition 1 and condition 2; join multiple conditions using the and keyword

8. Multi-conditional query with "Or"

Select * from table name where condition 1 or condition 2; as long as one of the conditions is met, it will be queried

9. Use the "distinct" keyword to cancel duplicated data

Select distinct field name from table name; cancel duplicate record rows in query results

10. Sort using the "order by" keyword

Select * from table name order by [asc] [desc]; translation: order by: refers to the way to sort asc: refers to "ascending" sort desc: refers to "descending" sort

11. Use the "group by" keyword to group

Select field 1, field 2. From table name group by a field 1 or field 2 can be looked up in groups; at the same time, multiple fields can be grouped

12. Use "limit" to limit the number of query results

Select * from order by asc limit 3; the result of the query shows the first three records

(2) aggregate function query

1. Use "count ()" function

Select count (*) from table name; translation, all records in the statistical table

2. Use the "sum ()" function

Select sum (row) from table name; find out the total number of rows in the table.

3. Use the "avg ()" function

Select avg (row) from table name; find an average of the rows in the table

4. Use the "max ()" function

Select max (row) from table name; translation: the maximum value of the row field in the query table

5. Use the "min ()" function

Select min (row) from table name; translation: the minimum value of the row field in the query table

(3) join query

1. Internal join query

Select field 1, field 2, field 3 from table name 1, table name 2 where table name 1. Field 1 = table name 2. Field 2; to implement a join query between tables is to make sure that both tables have the same field name.

2. External join query

The outer join is different from the inner join, which refers to the use of the "outer join" keyword to join two tables.

It can be divided into three types: left outer connection "left join", right outer connection "right join" and full connection.

Syntax:

Select field name from table name 1 left/right join table name 2 on table name 1. Field 1 = table name 2. Field 2

2.1.The left outer link "left join"

Left outer join: refers to the connection combination of all the data in the left table on each piece of data in the right table, and the returned results include not only the data in the inner join, but also the data that does not match in the left table, and add null values to the corresponding columns in the right table.

Select field name from table name 1 left join table name 2 on table name 1. Field 1 = table name 2. Field 2

2.2.2.The right outer link "right join"

The right outer join refers to the connection combination of all the data in the right table on each piece of data in the left table, and the returned results include not only the data in the inner join, but also the data that do not meet the conditions in the right table, and add null values to the corresponding columns in the left table.

Select field name from table name 1 right join table name 2 on table name 1. Field 1 = table name 2. Field 2

2.3.Cross-connect "cross join": Cartesian product

Select * from Table 1 cross join Table 2

(4) Sub-query

Subquery: that is, the select query is an attachment to another query. What is dependency, that is, the result of a query statement is a condition or a basis, premise, etc., for the second query, and only when it is queried can the second result be queried.

1. Subquery with "in" keyword

Using the in operator, you can detect whether a particular value exists in the result set, and execute an external query if the test is successful.

Select * from table name 1 where field 1 in (select field 1 from table name 2); query all records in table 1, but field 1 must appear in table 2.

2. Subquery with comparison operator

It is common for subqueries to use operators, including "=,! =, >,

=,

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.