MySQL Single table query

1. Single-table query

2. Where constraints

3. Goup by

4. Having

5. ORDER BY order

6, Limit N

7. Regular

1. Single-table query

Single-table syntax: SELECT DISTINCT field 1, Field 2,... fromtable name where constraint group by grouping condition having filter condition order by sort field limit N;#Simple QuerySELECT * fromEMP; Select Id,name fromEMP;#Remove DuplicatesSELECT DISTINCT Post fromEMP;#arithmeticSelect Name,salary*12 as Annual_salary fromEMP; Select Name,salary*12 annual_salary fromEMP;#Defining display FormatsThe concat () function is used for connection strings select Concat ('Name:', Name,'Annual Salary:', salary*12) as Annual_salary fromemployee; Concat_ws () The first parameter is a delimiter select Concat_ws (':', name,salary*12) as Annual_salary fromemployee; Combine Case statement: Select ( case when name='Egon'Then name when name='Alex'Then concat (name,'_BIGSB')            Elseconcat (name,'_SB') end) as New_name fromEmp

2. Where

The where clause can be used:

? Comparison operators:> < >= <= <>!=between and values between ten and

? In (80,90,100) value is ten or

? Like ' egon% '
The pattern can be either % or _,
% means any number of characters
_ represents a character

Logical operators: logical operators and or not can be used directly in multiple conditions

#1: Single condition querySELECT name from employee WHERE Post='Sale'; #2: Multi-criteria QuerySELECT name,salary from employee WHERE Post='Teacher'and salary>10000;#3: keyword between andSELECT name,salary from employee WHERE salary between10000 and 20000; SELECT name,salary from employee WHERE salary not between10000 and 20000; #4: keyword is null (determines if a field is null cannot be used as an equals sign, need to be)SELECT name,post_comment from the employee WHERE post_comment is NULL;            SELECT name,post_comment from employee WHERE post_comment are not NULL; SELECT name,post_comment from employee WHERE post_comment="'; Attention"'is an empty string, NOT NULL PS: Perform update employee set post_comment="'where id=2; And then check it out, there's going to be a result.#5: Keyword in collection querySELECT name,salary from employee WHERE salary=3000 or salary=3500 or salary=4000 or salary=9000 ; SELECT name,salary from Employee WHERE salary in (3000,3500,4000,9000) ; SELECT name,salary from employee WHERE salary not in (3000,3500,4000,9000) ;#6: Keywords like fuzzy queryWildcard characters '%' SELECT*From employee WHERE name is like'eg%'; Wildcard ' _ ' SELECT*From employee WHERE name is like'al__';

3. Goup by

  Use the group BY keyword separately for select post from the employee GROUP by post; Note: We group by post field, then the field of the select query can only be post, want to get other related information within the group, need to use the function GROUP by keyword and group_concat () function with SELECT Post,group_ CONCAT (name) from the employee GROUP by post;  #   GROUP by post and view the member names in the group   select Post,group_concat (name) as Emp_members from the employee GROUP by post; GROUP by is used with the aggregation function select Post,count (ID) as Count  from  employee GROUP by post ; #   GROUP by post and see how many people are in each group  

# emphasis: Aggregation functions aggregate the contents of a group, and if there is no grouping, the default set of Example:    SELECT COUNT (*) from employee;    SELECT COUNT (*) from employee WHERE depart_id=1;    SELECT MAX (salary) from employee;    SELECT MIN (salary) from employee;    SELECT AVG (salary) from employee;    SELECT SUM (salary) from employee;    SELECT SUM (Salary) from employee WHERE depart_id= 3;

4. Having

##1. Where occurs before grouping group by, so there can be any field in the where, but the aggregate function must never be used.  #2. Having a group by, and having the ability to use grouped fields and not directly to other fields, you can use the aggregate function   from the EMP GROUP by post have AVG ( Salary) > 20000;

5.Order by order

SELECT * from#  default ascending, from small to large select * from# large to small # sorted by multiple columns : Sorts the first according to age, and if the old is the same, sort  by the ID from the EMP GROUP by post ORDER by AVG (salary);

6, Limit N

# default initial position is 0 SELECT * FROM employee ORDER by salary DESC LIMIT 3;                    # from the beginning of the No. 0, that is, the first query, and then contain the article 5 SELECT * from the employee ORDER by salary DESC LIMIT0,5# starting from the 5th, that is, the first query out the 6th, and then include this one, and then look back to article 5.

7. Regular

SELECT * FROM employee WHERE name REGEXP'^ale'; SELECT* FROM employee WHERE name REGEXP'on$'; SELECT* FROM employee WHERE name REGEXP'm{2}'; summary: How to match a string where name='Egon'; WHERE name like'yua%'; WHERE name REGEXP'on$';#View employee information for all employees whose name is Jin start, n or G resultsSELECT * fromEmployee WHERE name RegExp'^jin.*[gn]$';

MySQL Single table query