MySQL four-1: single-table query

The syntax of a single-table query

SELECT Field 1, Field 2 ... From table name

WHERE condition

GROUP by Field

Having screening

ORDER by Field

Limit number of bars

Ii. priority of implementation of keywords (emphasis)

Key priorities in the execution priority focus!!!

From--->where--->group by--->having--->select--->distinct--->order by--->limit

1. Find the table: from

2. Take the where specified constraints, go to the File/table to remove a record

3. Group BY, if no group by, is grouped by the records taken out.

4. To filter the results of a group

5. Execute Select

6. Go to the heavy

7. Sort the results conditionally: ORDER BY

8. Limit the results of the display bar

Three, simple query

1, watchmaking

Create a table

Mysql> CREATE TABLE Employee (

ID int not NULL unique auto_increment,

Name varchar () is not NULL,

Sex enum (' Male ', ' female ') not null default ' male ', #大部分是男的

Age int (3) unsigned NOT null default 28,

Hire_date date NOT NULL,

Post varchar (50),

Post_comment varchar (100),

Salary double (15,2),

Office int, #一个部门一个屋子

depart_id int

);

View table Structure

Mysql> DESC employee;

Insert a record (three departments: teaching, Sales, operations)

Mysql> INSERT into employee (NAME,SEX,AGE,HIRE_DATE,POST,SALARY,OFFICE,DEPART_ID) values

(' Egon ', ' Male ', 18, ' 20170301 ', ' old boy ' diplomatic ambassador to Shahe Office ', 7300.33,401,1), #以下是教学部

(' Alex ', ' Male ', ' 20150302 ', ' teacher ', ' 1000000.31,401,1 '),

(' Wupeiqi ', ' Male ', Bayi, ' 20130305 ', ' teacher ', 8300,401, 1),

(' Yuanhao ', ' Male ', ' 20140701 ', ' teacher ', 3500,401, 1),

(' Liwenzhou ', ' Male ', ', ' 20121101 ', ' teacher ', 2100,401, 1),

(' Jingliyang ', ' female ', ' 20110211 ', ' teacher ', 9000,401, 1),

(' jinxin ', ' Male ', ' 19000301 ', ' teacher ', 30000,401, 1),

(' Dragon ', ' Male ', ' 20101111 ', ' teacher ', 10000,401, 1),

(' Crooked ', ' female ', ' 20150311 ', ' sale ', ' 3000.13,402,2 '), #以下是销售部门

(' Ya Ya ', ' female ', ' 20101101 ', ' sale ', ' 2000.35,402,2 '),

(' ding ', ' female ', ' 20110312 ', ' sale ', 1000.37,402,2),

(' stars ', ' female ', ' 20160513 ', ' sale ', 3000.29,402,2),

(' Princess ', ' female ', ' 20170127 ', ' sale ', ' 4000.33,402,2 '),

(' Chang ye ', ' Male ', ' 20160311 ', ' operation ', 10000.13,403,3), #以下是运营部门

(' Cheng Bite gold ', ' Male ', ' 19970312 ', ' Operation ', 20000,403, 3),

(' Cheng bites silver ', ' female ', ' 20130311 ', ' Operation ', 19000,403, 3),

(' Process bite copper ', ' male ', ' 20150411 ', ' Operation ', 18000,403, 3),

(' Cheng Bite iron ', ' female ', ' 20140512 ', ' Operation ', 17000,403, 3)

;

2. Enquiry

Simple query

SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id from employee;

SELECT * from employee;

SELECT name,salary from employee;

Avoid repeating distinct

SELECT DISTINCT post from employee; #distinct意思是输出后面 column to the content of the heavy;

Query by arithmetic

SELECT name, salary*12 from employee;

SELECT name, salary*12 as annual_salary from employee;

SELECT name, salary*12 annual_salary from employee;

Defining display formats

#CONCAT () function for connection strings

SELECT CONCAT (' name: ', name, ' Annual salary: ', salary*12) as annual_salary from employee;

#CONCAT_WS () The first argument is a delimiter

SELECT concat_ws (': ', name,salary*12) as annual_salary from employee;

Combine case statements:

SELECT

(

Case

When NAME = "Egon" Then

NAME

When NAME = ' Alex ' Then

CONCAT (name, ' _BIGSB ')

ELSE

Concat (NAME, ' SB ')

END

) as New_name

from employee;

3. Small Exercises

1) Find out the name, salary, and format of all employees

< name:egon> < payroll:3000>

SELECT CONCAT (' < name: ', Name, ' > ', ' < yearly salary: ', salary*12, ' > ') as Annual_year from employee;

2) Identify all positions (remove duplicates)

SELECT DISTINCT depart_id from employee;

3) Find out the names of all employees, and their annual salary, the field name is Annual_year

SELECT name, salary*12 as annual_year from employee;

Iv. where constraints

The WHERE clause can be used:

1. Comparison operators:> < >= <= <>! =

2. Between and 100 values between 10 and 20

3. In (80,90,100) value is 10 or 20 or 30

4. Like ' egon% '

Pattern can be% or _ (% means any number of characters, _ denotes one character)

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

Single-Condition query:

SELECT name from employee WHERE post= ' sale ';

Multi-Criteria Query:

SELECT name,salary from employee WHERE post= ' teacher ' and salary>10000;

Keyword between and:

SELECT name,salary from employee WHERE salary between 10000 and 20000;

SELECT Name,salary from the employee WHERE salary not between 10000 and 20000;

The keyword is null (determining whether a field is null cannot be used as an equals sign, which is required):

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= '; Note ' is an empty string, NOT NULL

Ps:

Execute update employee set post_comment= ' where id=2; and then, with the last bar, there will be results.

Keyword in collection query:

SELECT name,salary from employee WHERE salary=3000 or salary=3500 or salary=4000 or salary=9000;

SELECT Name,salary from the employee WHERE salary in (3000,3500,4000,9000);

SELECT Name,salary from the employee WHERE salary not in (3000,3500,4000,9000);

Keyword like fuzzy query:

Wildcard '% ': SELECT * from the employee WHERE name like ' eg% ';

Wildcard ' _ ': SELECT * from the employee WHERE name like ' al__ ';

Small exercise:

1. Check position is Teacher's employee name, age

SELECT name,age from employee WHERE post= ' teacher ';

2. Check the name and age of employees who are teacher and older than 30 years old

SELECT name,age from employee WHERE post= ' teacher ' and age>30;

3. View the employee's name, age, and salary in the teacher and salary range within 9000-1000

SELECT name,age,salary from employee WHERE post= ' teacher ' and salary between 9000 and 10000;

4. View employee information that the job description is not NULL

SELECT * FROM employee where post_comment are not null;

5. View the employee's name, age, salary, teacher and salary 10000 or 9000 or 30000

SELECT Name,age,salary from the employee WHERE post= ' teacher ' and salary in (10000,9000,30000);

6. View the employee's name, age, salary, teacher and salary not 10000 or 9000 or 30000

SELECT Name,age,salary from the employee WHERE post= ' teacher ' and salary not in (10000,9000,30000);

7. View the position is teacher and the name is Jin beginning employee name, annual salary

SELECT Name,salary from the employee WHERE post= ' teacher ' and age like ' jin% ';

Five, group query: GROUP BY

1. What is a group? Why are they grouped?

1) First make it clear: The grouping occurs after the where, that is, the grouping is based on the records obtained after the Where

2) grouping refers to the grouping of all records in one of the same fields, such as a group of positions for the employee Information table, or grouping by gender

3) Why are they grouped?

The highest wage per department

Number of employees in each department

Number of males and women

Tip: The field behind the word ' every ' is the basis for our group

4) Major premise:

Can be grouped by any field, but after grouping, such as group by post, can only view the post field, if you want to view the information in the group, need to use the aggregation function

2, Only_full_group_by

View MySQL 5.7 By default Sql_mode as follows:

Mysql> SELECT @ @global. sql_mode;

Only_full_group_by,strict_trans_tables,no_zero_in_date,no_zero_date,error_for_division_by_zero,no_auto_create_ User,no_engine_substitution

！！！ Attention

The semantics of only_full_group_by is to determine that the values of all the columns in the Select target list are explicit semantics, and simply, in only_full_group_by mode, the values in the target list are either the result of the aggregation function , or the value of an expression from the group by list.

Set Sql_mole as follows (we can remove only_full_group_by mode):

mysql> set global sql_mode= ' Strict_trans_tables,no_zero_in_date,no_zero_date,error_for_division_by_zero,no_ Auto_create_user,no_engine_substitution ';

3. GROUP by

Use GROUP BY keyword grouping alone

SELECT post from the employee GROUP by post;

Note: We group by post field, then the field of select query can only be post, want to get other related information in the group, need to use the function

The GROUP by keyword is used with the Group_concat () function

SELECT Post,group_concat (name) from the employee 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 aggregate functions

Select Post,count (ID) as count from employee group by post; #按照岗位分组 and see how many people are in each group

4. Aggregation function

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;

5, small exercise:

1) search for job title and all employee names included in the position

SELECT Post,group_concat (name) from the employee GROUP by post;

2) Check the job name and the number of employees in each position

SELECT Post,count (ID) from the employee GROUP by post;

3) Check the number of male and female employees in the company

SELECT Sex,count (ID) from the employee GROUP by sex;

4) Check the job name and the average salary of each position

SELECT Post,avg (Salary) from the employee GROUP by post;

5) Check the job name and the highest salary for each position

SELECT Post,max (Salary) from the employee GROUP by post;

6) Check job name and minimum salary for each position

SELECT Post,min (Salary) from the employee GROUP by post;

7) Average salary for male and male employees, average salary for female and female employees

SELECT Sex,avg (Salary) from the employee GROUP by sex;

VI. Having a filter

Having the same place as where is!!!!!!

！！！ High-to-low execution priority: where > Group by > have

1. Where occurs before the group by grouping by, there can be any field in the where, but the aggregate function must never be used.

2. The having occurs after the group by, so that the having can use grouped fields, cannot be directly taken to other fields, you can use the aggregate function

Small exercise:

1. Inquiries about the number of employees in each position is less than 2 of the job name, the position contains the employee name, the number

SELECT Post,group_concat (name), count (ID) from the employee group by Post have count (ID) <2;

3. Check the job name and average salary for each position with a payroll of more than 10000

SELECT Post,avg (Salary) from the employee GROUP by post have AVG (salary) >10000;

4. Check the job name and average salary of each position with the above 10000 and less than 20000

SELECT Post,avg (Salary) from the employee GROUP by post have avg (Salary) >10000 and AVG (Salary) <20000;

Vii. Query sort: ORDER BY

Sort by single column

SELECT * FROM employee ORDER by salary;

SELECT * FROM employee ORDER by salary ASC;

SELECT * FROM employee ORDER by salary DESC;

Sort by multiple columns: Sort by age first, and if you are old, sort by salary

SELECT * FROM Employee ORDER by Age,salary DESC;

Small exercise:

1. Query all employee information, sorted in ascending order of age, sorted by hire_date in descending order if age is the same

SELECT * FROM Employee ORDER by Age,hire_date DESC;

2. Check the job name and average salary of each post with an equal pay of more than 10000, and the result is in ascending order of average salary

SELECT Post,avg (Salary) from the employee GROUP by post has AVG (salary) >10000 ORDER by AVG (salary);

3. Check the job name and average salary of each position with the mean pay of more than 10000, and the result is sorted by average salary.

SELECT Post,avg (Salary) from the employee GROUP by Post have AVG (Salary) >10000 ORDER by AVG (salary) DESC;

Viii. limit the number of records that are queried: limit

Example:

SELECT * FROM employee ORDER by salary DESC LIMIT 3; #默认初始位置为0

SELECT * FROM employee ORDER by salary DESC LIMIT 0, 5; #从第0开始, that is, first query out, and then include this one, and then look back to 5 article

SELECT * FROM employee ORDER by salary DESC LIMIT 5, 5; #从第5开始, that is, the first query out 6th, and then include this one, and then look back 5 article

Small exercise:

1. Pagination display, 5 articles per page

SELECT * FROM employee ORDER by salary DESC LIMIT 0, 5;

SELECT * FROM employee ORDER by salary DESC LIMIT 5, 5;

SELECT * FROM employee ORDER by salary DESC LIMIT 10, 5;

Nine, using regular expression query

SELECT * FROM employee WHERE name REGEXP ' ^ale ';

SELECT * FROM employee WHERE name REGEXP ' on$ ';

SELECT * FROM employee WHERE name REGEXP ' m{2} ';

Summary: How strings are matched

WHERE name = ' Egon ';

WHERE name like ' yua% ';

WHERE name REGEXP ' on$ ';

Small exercise:

View employee information for all employees whose name is Jin start, n or G results

SELECT * FROM employee WHERE name REGEXP ' ^jin.*[gn] ';

MySQL four-1: single-table query