Shulou(Shulou.com)06/01 Report--

First, the concept of querying data: querying data, filtering data, and determining the format in which the data is output.

Second, the basic sentences of data query

1. Basic statement format: select field list FROM table or view where query condition

2.1 contents of single table query: query all fields, query specified fields, query specified records, query null values, query multi-condition queries

Sort the query results.

2.1.1 query all fields through * wildcards.

SELECT * FROM fruits; (* represents all fields in the table, fruits is the table name)

The data is returned in the order in which the table is defined.

It is generally not recommended to use * to query all the data, otherwise it will reduce the efficiency of the query and the application used.

2.1.2 query specifies a single field:

SELECT f_name FROM fruits (f_name is a column in the fruits table)

2.1.3 query multiple fields:

SELECT _ name _ direction _ price FROM fruits; (both of them are a column of fruits).

Different field names should be separated by commas.

2.2 use where to write conditions for data filtering

SELECT field name 1... n FROM table name WHERE query condition

2.2.2 the conditional determiner is greater than (>), less than (=), less than or equal to (= 5 AND fanciname = 'apple')

2.6.1 OR means that only records that meet one of these conditions can return data, OR can join multiple or even multiple query conditions, and multiple conditional expressions are separated by OR.

SELECT s_id, fanciname _ OR s_id _ price FROM fruits WHERE s_id = 101price

The results of IN and OR operators are the same, but the use of the IN operator makes the retrieval statement more concise, and IN executes faster than OR, and with the IN operator, more complex nested queries can be executed.

AND takes precedence over OR.

2.7.1 query results do not repeat distinct eliminates duplicate values

SELECT DISTINCT s_id FROM fruits

2.8.1 sort query results

SELECT f_name FROM fruits ORDER BY frename; the default should be ascending order.

2.8.2 Multi-column sorting sorts multi-column data, separating the columns that need to be sorted with commas.

SELECT f_name, f_price FROM fruits ORDER BY f_name, f_price

When sorting multiple columns, the first column sorted first must have the same column value before the second column is sorted. If all values in the first column of data are unique, the second column is no longer sorted.

2.8.3 specify the direction of sorting through the keyword DESC

SELECT f_name, f_price FROM fruits ORDER BY f_price DESC; descending order

ASC is an ascending order, and ASC is the default sorting method when sorting, so you can add it or not.

To sort multiple columns in descending order, you must add the DESC keyword after the column name of each column

SELECT f_price, f_name FROM fruits ORDER BY f_price DESC, f_name DESC

2.9.1 grouping query A grouping query is to group data according to one or more fields. Use the GROUP BY keyword to group data.

2.9.2 basic syntax: GROUP BY field HAVING conditional expression

The field value is the name of the column on which the grouping is based; 'HAVING (conditional expression)' specifies that the result that meets the qualification of the expression will be displayed.

2.9.3 the GROUP BY keyword is usually used in the first phase of aggregate functions such as MAX (), MIN (), COUNT (), SUM (), AVG ()

SELECT s_id, COUNT (*) AS total FROM fruits GROUP BY s_id

SELECT scuttlehead GROUPPRECTOCONcat (f_name) AS Names FROM fruits GROUP BY s_id

2.9.4 GROUP BY can work with HAVING to define the conditions required to display records, and only groups that meet the conditions can be displayed.

SELECT s_id, GROUP_CONCAT (f_name) AS Names

FROM fruits

GROUP BY s_id HAVING COUNT (f_name) > 1

2.9.5 the difference between HAVING and WHERE: HAVING filters after a data packet to select a packet, while WHERE is used to select records before a packet, and records excluded in WHERE are not included in the packet.

2.9.6 use WITH ROLLUP in GROUP BY

After using WITH ROLLUP, a record is added after all the queried groups, and the record calculates the sum of all the queried records, that is, the statistical number.

SELECT s_id, COUNT (*) AS Total FROM fruits GROUP BY s_id WITH ROLLUP

2.9.7 the multi-field grouping experiment was not successful.

2.9.8 GROUP BY is used with ORDER BY

SELECT o_num, SUM (quantity* item_price) AS orderTotal

FROM orderitems

GROUP BY o_num

HAVING SUM (quantity*item_price) > = 100

3.1.1 LIMIT limits the number of query results

SELECT * FROM fruits LIMIT 4; displays the first four lines of the query result

SELECT * FROM fruits LIMIT4,3; displays three records starting from the fifth line.

4.1.1 query using aggregate function

Function: calculate the total number of rows recorded in the data table, calculate the sum of the data under a field column, and calculate the maximum, minimum, or average value under a field in the table.

AVG (average of a column) COUNT (number of rows of a column) MAX (maximum of a column) MIN (minimum of a column) SUM (a column worth summing)

4.1.2 COUNT () function

COUNT (*) calculates the total number of rows in the table, regardless of whether a column has a numeric value or is null.

SELECT COUNT (*) AS cust_num FROM customers

The COUNT (field name) computer specifies the total number of rows under the column, ignoring rows with null values in the calculation

SELECT COUNT (o_email) AS email_num FROM customers

4.1.2 COUNT GROUP BY

SELECT o_num, COUNT (f_id)

FROM orderitems

GROUP BY o_num

The GROUP BY keyword is grouped according to the order number, and then the total number of records in each group is calculated.

4.1.3 SUM

SELECT SUM (quantity) AS items_total

FROM orderitems

WHERE o_num = 30005

SELECT o_num, SUM (quantity) AS items_total

FROM orderitems

GROUP BY o_num

The sum of count rows, and sum is the sum of the columns worth it.

4.1.4 AVG

SELECT AVG (f_price) AS avg_price

FROM fruits

WHERE s_id=103

SELECT s_id, AVG (f_price) AS avg_price

FROM fruits

GROUP BY s_id

When the AVG () function is used, its argument is the name of the column to be calculated. If you want to get multiple averages of multiple columns, you need to use the AVG () function on each column.

4.1.5 MAX () function

SELECT MAX (f_price) AS max_price FROM fruits

SELECT s_id, MAX (f_price) AS max_price FROM fruits GROUP BY s_id

SELECT MAX (f_name) FROM fruits

MAX can size letters and return the largest character or string value.

4.1.6 MIN () function

SELECT MIN (f_price) AS min_price FROM fruits

SELECT s_id, MIN (f_price) AS min_price

FROM fruits

GROUP BY s_id

The GROUP BY keyword groups records according to the s_id field, and then calculates the minimum value in each group.

5.1.1 connections are the main features of the database model. It mainly includes inner connection, outer connection and so on.

5.1.2 join query within

Use the glue operator to compare a column of data between tables and list the data rows in these tables that match the join conditions to form a new record.

SELECT suppliers.s_id, s_name,f_name, f_price

FROM fruits, suppliers

WHERE fruits.s_id = suppliers.s_id

The columns specified after the SELECT belong to two different tables. The WHERE clause, as a filter condition, indicates that join queries are eligible only if the values of the s_id fields in the two tables are equal.

SELECT suppliers.s_id,s_name, f_name, f_price

FROM fruits INNER JOIN suppliers

ON fruits.s_id = suppliers.s_id

INNER JOIN is a standard specification

5.1.3 in a join query, both tables involved are the same table, which is called a self-join query. Self-join query is a special inner join, which means that the joined tables are physically the same table, but logically divided into two tables.

SEELCT f1.f_id, f1.f_name

FROM fruits AS f1, fruits AS f2

WHERE f1.s_id = f2.s_id AND f2.f_id = 'a1'

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.