Mysql Single Table Record query, Mysql Single Table Record

Mysql Single Table Record query, Mysql Single Table Record
Data Record query: 1. simple data record query: select * from table_name; select allfield from table_name; select distinct (attribute name) from table_name; // avoid repeated query to implement four elements: select Operation; connection query (set display format data query): select concat (attribute Field 1, "Description", attribute Field 2) from table_name; example: select concat (ename, "num is ", empno) from t_employee; 2. query condition data records: select * from table_name where Condition 1 and | or condition 2 .......; // you can have multiple conditions: select * from table_name where attribute field, the lower limit of the between range, and the upper limit of the range. // his meaning This is the value of the attribute field> = the value of the lower range and attribute field <= the upper range; select * from table_name where attribute field is null; // query the value of the attribute field being NULL, null is not equal to ""; select * from table_name where attribute FIELD in (range); // range query, in can also be replaced by not in. (If the keyword IN is used, if NULL exists IN the query set, the query will NOT be affected. If not in is used, there will be no query results IN the query set.) Example: select * from t_dept where deptno in (10, 30, 50, 70); query with the LIKE Keyword: "_" wildcard, match a single character, "%" wildcard, match any length of string, it can be 0 characters, one character, or many characters. view variable: show variables like '% variable name % ', Indicates the variable containing the variable name,' % variable name', indicates the variable ending with the variable name, and 'variable name' indicates the variable starting with the variable name, for example: show variables '% max %'; select * from table_name where attribute field like '%'; // a % and two % indicate all results. select * from table_name where attribute field like '% variable'; // indicates the result set ending with a variable. select * from table_name where attribute field like 'variable % '; // indicates the result set starting with a variable. select * from table_name where attribute field like '% variable %'; // indicates the result set containing the variable. what if I want to query the result set with %? Escape characters, escape % to \ %, and the rest remain unchanged. 3. query the order by data records: select * from table_name order by attribute field ASC | DESC; // ASC in ascending order, DESC select * from table_name order by attribute Field 1 ASC | DESC, attribute Field 2 ASC | DESC; sort by attribute Field 1 first, the same field 1 is arranged with attribute Field 2. in sorting, the NULL value is the minimum value. 4. limit: num must be a constant integer selecct * from table_name limit num; // num, which indicates querying the first row of num In the table selecct * from table_name limit num1, num2; // indicates the num2 data records after the num1 row. query of statistical functions and group data records: COUNT () function: number of records in the statistical table sel Ect count (attribute field) from table_name; // The attribute field can be *, and the total number of records is queried. If the field contains null, null results are not counted as the sum, but the null value "" is counted. AVG () function: calculate the average value of a field. select avg (attribute field) from table_name; SUM () function: calculate the total value of a field. select sum (attribute field) from table_name; MAX () function: maximum value of the queried field value select max (attribute field) from table_name; MIN () function: minimum value of the queried field value select min (attribute field) from table_name; select attribute field from table_name group by attribute field; // It can be composed of multiple conditions (multiple conditions are required for grouping multiple columns; otherwise, errors may occur) Example: select deptno, job, count (1) from T_employee group by deptno, job; function grouping query: select attribute Field 1, group_concat (distinct (attribute Field 2) from table_name group by attribute Field 1; example: select deptno, group_concat (distinct (job) as job from t_employee group by deptno; // how to understand? In my opinion, the deptno group is used, and then the result of select distinct (job) from t_emplyee is added to the table. In simple words, it is a pair line. use images to understand (only the third and fourth columns need to be viewed): multi-function grouping query: I understand that grouping is performed using two grouping conditions, and count the number or type of the other content in each group. example (not easy to understand): using the having clause to limit a group query is equivalent to adding a condition example when grouping: select deptno, job, group_concat (ename) as p, count (1) as c from t_employee group by deptno, job having c> 3;