MySQl 4-2-database query 2

In MySQL, records of pure values can be enclosed by no single quotation marks in the program. If only a number is included in a string type record, no single quotation marks can be added. The records after the as statement are the same as those without single quotation marks. 1. syntax format: select [all | distinct | distinctrow] [high_priority] limit [straight_join] [limit] [SQL _big_result] [limit] [SQL _cache | SQL _NO_CACHE] [distinct] select_expr ,... [into outfile 'name' export_options | INTO dumpfile 'name'] [from table_reference [, table_reference]…] /* FROM clause */[wherewhere_definition]/* WHERE clause */[group by {col_name | expr | position} [asc | desc],... [with rollup]/* group by clause */[having where_definition]/* HAVING clause */[order by {col_name | expr | position} [ASC | DESC],... .] /* order by clause */[limt {[offset,] row_count | row_count OFFSET offset}]/* LIMIT clause */Note: www.2cto.com can be seen from this basic syntax, the simplest SELECT statement is SELECTselect_expr. With this simplest SELECT statement, MySQL supports Any operation, such as SELECT 1 + 1, returns 2. Many options can be used after the SELECT Keyword: ● ALL | distinct | DISTINCTROW: these options specify whether repeated rows should be returned. If these options are not specified, the default value is ALL (ALL matching rows are returned ). DISTINCT and DISTINCTROW are synonyms used to remove duplicate rows in the result set. ● HIGH_PRIORITY, STRAIGHT_JOIN, and options starting with SQL _ are extensions of MySQL compared with standard SQL. These options are optional in most cases. ● High_priority: Give the SELECT statement a higher priority so that the query can be executed immediately to speed up the query. ● Straight_join: Used to urge the MySQL optimizer to join tables to speed up query. ● SQL _small_result: it can be used together with GROUP BY or DISTINCT to inform MySQL optimizer that the result set is small. In this case, MySQL uses a quick temporary table to store the generated table, instead of a category. ● SQL _buffer_RESULT: pushes the result into a temporary table. This can help MySQL unlock the table in advance and send the result set to the client if it takes a long time. ● SQL _BIG_RESULT: it can be used together with GROUPBY or DISTINCT to inform MySQL optimizer that there are many rows in the result set. In this case, MySQL will give priority to classification, instead of using temporary tables. Www.2cto.com ● SQL _cache: tells MySQL to store the query results in the query cache. For queries or subqueries that use UNION, this option affects all the SELECT statements in the query. ● SQL _NO_CACHE: Tell MySQL not to store query results in the query cache. ● SQL _CALC_FOUND_ROWS: informs MySQL to calculate the number of rows in the result set without considering any LIMIT clause. ● SELECT... Into outfile 'name': This statement can export the rows in the table to a file. This file is created on the server host and name is the file name. All used clauses must be strictly ordered in the order shown in the syntax description. For example, a HAVING clause must be placed after the group by clause and before the order by clause. 2. Duplicate rows may appear when only some of its columns are selected for the table in the result set. For example, if you select only the professional name and total credits for the XSCJ database XS table, multiple rows are repeated. You can use the distinct or distinctrow keyword to remove duplicate rows in the result set. The format is: select distinct | distinctrow field name 1 [, field name 2…] The meaning is to select only one duplicate row in the result set to ensure the uniqueness of the row. For example, select only the professional name and total credits for the xs table of the xscj database to eliminate duplicate rows in the result set. Select distinct professional name, total credits from xs; 2. Aggregate Function (aggregationfunction) (group: Category summary) The expression of the SELECT clause can also contain the so-called aggregate function. Aggregate functions are often used to calculate a group of values and then return a single value. Except the COUNT function, All Aggregate functions ignore null values. Aggregate functions are usually used together with the group by clause, but cannot be used together with the where statement. If the SELECT statement has a group by clause, the aggregate function takes effect on all columns. If no, the SELECT statement generates only one row as the result. Www.2cto.com Function Name Description COUNT: calculate the number of items in the group, returns the int type Integer max min sum returns the average value of all values in the expression and the AVG average value of the group STD or STDDEV returns the standard deviation of all values in the given expression VARIANCE returns all of the values in the given expression the variance GROUP_CONCAT www.2cto.com of values returns the result BIT_AND logic or BIT_OR logic, which is composed of a combination of column values. The result is different from the BIT_XOR logic (1) the COUNT () function is the most frequently used aggregate function. It is used to COUNT the number of rows or the total number of rows that meet the conditions in a group and returns the number of non-NULL values in the rows retrieved by the SELECT statement, if no matching row is found, 0 is returned. Syntax format: COUNT ({[ALL | DISTINCT] expression} | *) Where expression is an expression and its data type is any type except BLOB or TEXT. ALL indicates that ALL values are operated, and DISTINCT indicates that duplicate values are removed. The default value is ALL. When COUNT (*) is used, the total number of retrieved rows is returned, whether or not it contains NULL values. Count (*): number of statistical records (including null); count (field name): number of records with values in the statistical field (excluding null); count (distinct field name ): remove duplicates and then count the number of records with values in the field (null is not included). Example 1: calculate the total number of students. Select count (*) AS 'total students 'FROMXS; example 2. COUNT the number of students whose remarks are not empty. Select count (Remarks) AS 'number of students whose remarks are not empty 'from xs; note: the rows whose remarks are NULL in COUNT (Remarks) calculation are ignored. Example 3: count the number of people whose total credits are above 50. Select count (total credits) AS 'total credits 50 + persons 'from xs where total credits> 50; (2) MAX and MIN are used to calculate the maximum and minimum values of ALL values in the expression, respectively. Syntax format: MAX/MIN ([ALL | DISTINCT] expression) where, expression is a constant, column, function, or expression. Its data type can be a number, character, or time or date. For example, www.2cto.com: Calculate the highest score and the lowest score for the students who take the 101 course. Select max (score), MIN (score) FROM XS_KC WHERE course number = '000000'; Note: when a given column is null or the intermediate result retrieved is null, the values of MAX and MIN functions are also empty. (3) SUM and AVG functions SUM and AVG are used to calculate the SUM and average values of ALL value items in the expression, respectively. Syntax format: SUM/AVG ([ALL | DISTINCT] expression) expression is a constant, column, function, or expression. Its data type can only be numeric data. For example, the total score of the course students whose number is 081101. Select sum (score) AS 'total course score 'FROM XS_KC WHERE student ID = '000000'; (4) variance and stddev (STD) the variance and stddev functions are used to calculate the variance and standard deviation of all values in a specific expression. Syntax format: VARIANCE/STDDEV ([ALL | DISTINCT] expression) Description: perform the following steps to calculate the VARIANCE. ① Calculate the average value of the relevant column; ② calculate the difference between each value and the average value in the column; ③ calculate the sum of the square of the difference; ④ divide the sum by (in the column) The result worth the result. The STDDEV function is used to calculate the standard deviation. The standard deviation is equal to the mean root of the variance. So, stddev (...) And sqrt (VARIANCE (...)) The two expressions are equal. STDDEV can be abbreviated to STD, which has no effect on the result. (5) The group_concat function MySQL supports a special aggregate function group_concat. This function returns all non-NULL values from a specified column in a group. These values are placed one by one, separated by commas (,), and expressed as a long string. The length of this string is limited. The standard value is 1024. Syntax format: GROUP_CONCAT ({[ALL | DISTINCT] expression} | *) (6) BIT_AND, BIT_OR and BIT_XOR and binary Operators | (OR), & (and) and ^ (exclusive or) correspond to the aggregate function also exists, namely BIT_OR, BIT_AND, BIT_XOR. For example, the BIT_OR function executes a binary OR on all values in a column. Syntax format: BIT_AND | BIT_OR | BIT_XOR ({[ALL | DISTINCT] expression} | *) Example: there is a table BITS with three integer values in one column of bin_value: 1, 3, 7, get the result of executing BIT_OR on this column, use the following statement: www.2cto.com select bin (BIT_OR (bin_value) from bits; description: mySQL executes the following expression in the background: (001 | 011) | 111, and the result is 111. The BIN function is used to convert the result to a binary bit. 3. There are two keywords for range comparison: between and in. When the condition to be queried is the range of a value, you can use the between keyword. The BETWEEN keyword indicates the query range. The format is expression [NOT] between expression1 andexpression2. If NOT is NOT used, if the expression value is BETWEEN expression1 and expression2 (including the two values, on the contrary, if not is used, TRUE is returned. Otherwise, FALSE is returned. If NOT is used, the return value is the opposite. Note: The value of expression1 cannot be greater than that of expression2. You can use the IN keyword to specify a value table. The value table lists all possible values. If it matches any of the values, TRUE is returned. Otherwise, FALSE is returned. Use the IN keyword to specify the value table IN the format of expression IN (expression [,... N]) Example: The third practice of the student whose name was born on January 1, 1900; SELECT student ID, name, professional name, birthdate from xs where birthdate not between '2017-01-01 'and '2017-12-31'; example: query the status of students whose major is "computer", "Communication Engineering", or "radio" in the XS table. SELECT * from xs where professional name IN ('computer ', 'communication Project', 'Radio'); this statement is equivalent to the following statement: SELECT * from xs where professional name = 'computer 'OR professional name = 'communication project' OR professional name = 'Radio'; note: the main role of the IN keyword is to express subqueries. 4. NULL Value Comparison when you need to determine whether the value of an expression is null, use the is null keyword, format: expression IS [NOT] NULL when NOT, if expression is null, TRUE is returned. Otherwise, FALSE is returned. If NOT is used, the result is the opposite. For example, query the total credits of students in the XSCJ database. Www.2cto.com SELECT * from xs where total credits is null. In this example, the students whose total credits are NULL are found and the result is null. 5. The group by clause is mainly used to group rows based on fields. For example, all rows in the XS table are grouped according to the major students have learned. The result is that each major student becomes a group. The syntax format of the group by clause is as follows: group by {col_name | expr | position} [ASC | DESC],... [with rollup] Description: A group by clause usually contains a column name or expression. MySQL extends the group by clause. You can specify ASC (ascending) or DESC (descending) after the column ). Group by can be grouped BY one or more columns or expressions, which is often used together with Aggregate functions. Example 1: output the professional names in the XSCJ database. SELECT major name from xs group by major name; Example 2: Number of students in each major in XSCJ database. SELECT major name, COUNT (*) AS 'student number' from xs group by major name; use the group by clause with the rollup OPERATOR: specify that the result set contains not only normal rows provided by group by, but also summary rows. For example, a result set is generated on the XSCJ database, including the number of boys, girls, total students, and total students in each major. SELECT major name, gender, COUNT (*) AS 'number' from xs group by major name, gender with rollup; after using the ROLLUP operator, A summary row is generated for each column specified in the group by clause. The rule is that the rows are summarized in reverse order BY column arrangement. In this example, the XS table is divided into four groups based on the professional name and gender. After ROLLUP is used, a summary row is generated for the gender field (for rows with the same professional name ), then, a summary line is generated for values with different professional names and genders. The field values corresponding to different column values in the generated summary row are set to NULL. 6. having clause (select After grouping) uses having clause for a similar purpose as the where clause. having and where are different: WHERE clause is used to select rows after the FROM clause, the where clause selects the original records, while the HAVING clause selects rows after the group by clause, and selects records after the GROUP. Syntax format of www.2cto.com: having selection condition. The condition definition is similar to that in the WHERE clause. However, conditions in the HAVING clause can contain aggregate functions, but not in the WHERE clause. SQL standard requires HAVING to reference columns in the GROUP BY clause or columns used in aggregate functions. However, MySQL supports extension of this working nature and allows HAVING to reference columns in the SELECT list and columns in external subqueries. For example, find the student ID and average score of the XSCJ database with an average score of more than 85. SELECT student ID, AVG (score) AS 'average score 'FROM XS_KC group by student id having avg (score)> = 85; 7. in a SELECT statement, if the order by clause is not used, the order of the rows in the result is unpredictable. After the order by clause is used, the rows in the result are arranged in certain ORDER. Syntax format: order by {col_name | expr | position} [ASC | DESC],... note: after the order by clause, it can be a column, an expression, or a positive integer. A positive integer indicates sorting by columns at the position in the result table. The keyword asc indicates ascending order, and desc indicates descending order. The default value is ASC. For example, students of communication engineering are sorted by birth date. SELECT student ID, name, Major name, date of birth from xs where professional name = 'communication project' order by date of birth; Note: When sorting null values, the order by clause treats null values as the minimum values. If the values are sorted in ascending ORDER, the null values are placed at the top and the descending ORDER is placed at the bottom. If it is sorted by multiple fields, it is first ranked by the first field. When the value of the first field is the same, it is then ranked by the second row. If the first field is different, the second field is not needed; the order by statement is before the limit statement. 8. The limit clause is the last clause of the SELECT statement. It is mainly used to LIMIT the number of rows returned by the SELECT statement. Syntax format: LIMIT {[offset,] row_count | row_count OFFSET} Description: if there are many records, you can use the limit statement to paging! In the syntax format, both the offset and row_count must be non-negative integer constants. The offset specifies the offset of the first row returned. row_count indicates the number of rows returned. For example, "LIMIT 5" indicates the first five rows in the returned SELECT statement result set, while "LIMIT3, 5" indicates that 5 rows are returned starting from row 4th. Www.2cto.com it is worth noting that the offset of the initial row is 0 rather than 1. For example, query the information of the top five students in the XS table. SELECT student ID, name, Major name, gender, date of birth, total credits from xs order by student id limit 5; to be compatible with PostgreSQL, MySQL also supports the LIMIT row_count OFFSET syntax. Therefore, replace the LIMIT clause in the preceding example with "limit 5 offset 3" and the result is the same. Author tianyazaiheruan