MySQL Basics-Data queries (1)

The concept of querying data: Querying data, filtering data, determining what format the data is exported in.

Second, the basic statement of data query

1. Basic Statement format: Select field List from table or view where query condition

2.1 Single-Table query content: Query all fields, query specified fields, query specified records, query null values, multi-criteria queries

Sorts the results of the query.

2.1.1 Queries all fields, which are implemented by * wildcard characters.

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

When the data is returned, it is displayed in the order in which the table is defined.

It is generally not recommended to use * to query all 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 Querying multiple fields:

SELECT f_name, f_price from fruits; (F_name,f_price is a column of fruits).

Separate field names with commas.

2.2 Filtering filtering of data by where to write conditions

SELECT field name 1...N from table name WHERE query condition

The 2.2.2 condition specifier is greater than (>), less than (<), greater than or equal to (>=), less than or equal (<=), not equal to (= = <>), between (between).

2.2.3 equals (used to compare strings and values)

SELECT f_name, f_price from fruits WHERE f_price = 10.2;

SELECT f_name, f_price from fruits WHERE f_name= ' apple ';

2.2.4 Less than

SELECT f_name,f_price from Fruits WHERE f_price <10.00;

The 2.2.5 with the IN keyword is used to query for records that meet conditions within a specified range, as long as a value within the criteria range is met.

SELECT s_id, f_name, f_price from Fruits WHERE s_id on (101,102) ORDER by F_name;

Not in:

SELECT s_id, f_name, f_price from Fruits WHERE s_id on (101,102) ORDER by F_name;

2.2.6 between ...

SELECT f_name, f_price from Fruits WHERE f_price between 2.00 and 10.20;

Not between ....

SELECT f_name, f_price from fruits WHERE f_price not between 2.00 and 10.20;

2.3.1 like the character matching query, with the wildcard character used by like is% and _.

2.3.2% matches any length of character, even including 0 characters,% can be placed in different positions.

SELECT f_id, f_name from fruits WHERE f_name like ' b% '; (Find fruit that starts with the B word)

SELECT f_id, f_name from fruits WHERE f_name like '%g% ' (find fruit with G in the name)

2.3.3 _ Underline wildcard characters, which can only match any one character at a time.

SELECT f_id, f_name from fruits WHERE f_name like ' ____y ';

2.4.1 Query NULL: The null value differs from 0 and is also different from an empty string, where null values generally indicate that the data is unknown, not applicable, or that data will be added later.

SELECT c_id, C_name, c_email from Customers WHERE C_email is NULL;

Is NULL:

SELECT C_id,c_name,c_email from Customers WHERE C_email is NULL;

The 2.5.1 and operator qualifies only records that satisfy all query criteria to be returned, you can use and to link multiple or even multiple query conditions, and separate multiple conditions with and.

SELECT f_id, F_price, f_name from fruits WHERE s_id = ' 101 ' and F_price >=5;

You can have multiple filters to increase the condition while adding a keyword and

SELECT f_id, F_price, f_name from fruits

WHERE s_id in (101,102) and F_price >=5 and f_name= ' Apple ';

2.6.1 or means that only records that meet one of the criteria are required to return data, or you can concatenate multiple or even multiple query conditions, and separate multiple conditional expressions using or.

SELECT s_id, f_name,f_price from fruits WHERE s_id =101 OR s_id = 102;

The in and or operators result in the same results, but using the in operator makes the retrieval statement more concise, and in executes faster than OR, using the in operator, you can perform more complex nested queries.

and has a higher precedence than OR.

2.7.1 Query results do not repeat distinct eliminate duplicate values

SELECT DISTINCT s_id from fruits;

2.8.1 to sort the results of a query

SELECT f_name from Fruits ORDER by f_name; The default should be ascending.

2.8.2 Multi-column sorting to sort multiple columns of data, separate the columns that need to be sorted by commas.

SELECT f_name, f_price from Fruits ORDER by f_name, F_price;

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

2.8.3 specifies the direction of the sort to be implemented by keyword DESC

SELECT f_name, f_price from Fruits ORDER by F_price DESC; Descending

ASC is ascending, and ASC is the default sorting method in the sort order, so it is possible to add 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 Group Query A group query is a grouping of data by 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 to group, and ' having (conditional expression) ' Specifies that the result that satisfies the expression qualification is displayed.

The 2.9.3 GROUP by keyword is typically used in the first phase of a collection function such as: MAX (), MIN (), COUNT (), SUM (), AVG ()

SELECT s_id, COUNT (*) as total from fruits GROUP by s_id;

SELECT S_id,group_concat (f_name) as Names from Fruits GROUP by s_id;

2.9.4 GROUP by can, together with having, limit the conditions that are required to display records, and only groups that meet the criteria will be displayed.

SELECT s_id, Group_concat (f_name) as Names

From fruits

GROUP by s_id have COUNT (f_name) >1;

2.9.5 the difference between having and where: having a filter to select a group after the data is grouped, where it is used to select records before grouping, and where excluded records are not included in the grouping.

2.9.6 GROUP by using the with ROLLUP

After using the with ROLLUP, a record is added after all the queries out of the group, which calculates the synthesis of all the records that are queried, that is, the number of statistics.

SELECT s_id, COUNT (*) as total from Fruits GROUP by s_id with ROLLUP;

2.9.7 Multi-field grouping experiment could not succeed.

2.9.8 GROUP by and ORDER by are used together

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; Show first 4 rows of query results

SELECT * from fruits limit4,3; displays three records after the start of line fifth.

4.1.1 using aggregate functions to query

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

AVG (the average of a column) count (the number of rows in a column) max (the maximum value of a column) min (the lowest value of a column) SUM (a column worth and)

4.1.2 COUNT () function

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

SELECT COUNT (*) as cust_num from customers;

Count (field name) computer Specifies the total number of rows under the column, ignoring null values when calculating a row

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 first grouped by the Oh 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 the count rows, sum is the sum of the columns worth.

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 computed, and if you want multiple averages for 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 determine the size of a letter and return the maximum 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 connectivity is a key feature of the database model. Mainly includes inner connection, outer connection and so on.

5.1.2 Internal Connection Query

Use the glue operator to perform a comparison of a column of data between tables, and list the rows of data in those tables that match the join criteria and combine them into new records.

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, respectively. The WHERE clause, as a filter, indicates that only the s_id field values in two tables match the conditions of the join query.

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 connection query, the two tables involved are the same table, and the query is called a self-join query. A self-join query is a special inner join, which refers to a table that is connected to each other physically, 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 ';

MySQL Basics-Data queries (1)