Mysql query questions, not only has a high exit rating, but also has a great impact on salary negotiation, so it plays an important role. 1. Almost all software test questions will have Mysql query exercises. Large companies with high salaries will select more excellent testers because of the number of applicants, and often set one or two particularly difficult Mysql query questions. 2. If we answer all the other questions well, but we don't answer the Mysql query problem well, we may be PK down in the interview competition, which leads to our losing with the company we like. Even if the competitors are not strong enough to get away with the victory, we will suffer in the salary negotiation later. 3. On the contrary, if Mysql query problem-solving practice is not only correct, but also fast, it will leave a good impression of strong technology to the employer, so as to immediately get rid of other competitors. It will not only occupy a favorable position in salary negotiation, but also be conducive to the future development of the company.

But it's embarrassing that many new testers often don't know how to solve the problem after they get the problem, let alone the Mysql query problem. In order to let you better master the skills of database query, I write a technical article to introduce to you. This paper is divided into the following steps:

1. Step 1: prepare the table statement
2. The second step is to input the table creation statement into navicat to create three tables
3. The third step is to understand the three steps of question making
4. Step 4: test the questions
5. Step 5: summary

1, Step 1: prepare the table statement

1. Prepare sql data

-- Employee list employees
-- Payroll salary
-- Departmental table departments
create table departments (
deptid int(10) primary key, 
deptname varchar(20) not null -- Department name
);
insert into departments values ('1001', 'Marketing Department');
insert into departments values ('1002', 'Test department');
insert into departments values ('1003', 'Development Department');

create table employees (
empid int(10) primary key,
empname varchar(20) not null, -- Full name
sex varchar(4) default null, -- Gender
deptid int(20) default null, -- Department number
jobs varchar(20) default null, -- post
politicalstatus varchar(20) default null, -- Political affiliation
leader int(10) default null
);

insert into employees values ('1', 'Wang Zhaojun', 'female', '1003', 'Development', 'Masses', '9');
insert into employees values ('2', 'Zhu Geliang', 'male', '1003', 'Development Manager', 'Masses', null);
insert into employees values ('3', 'Zhang Fei', 'male', '1002', 'test', 'League member', '4');
insert into employees values ('4', 'White', 'male', '1002', 'Test Manager', 'Party member', null);
insert into employees values ('5', 'Big Joe', 'female', '1002', 'test', 'Party member', '4');
insert into employees values ('6', 'Sun Shang Xiang', 'female', '1001', 'market', 'Party member', '12');
insert into employees values ('7', 'Bai Li Xuan strategy', 'male', '1001', 'market', 'League member', '12');
insert into employees values ('8', 'Little Joe', 'female', '1002', 'test', 'Masses', '4');
insert into employees values ('9', 'Baili Convention', 'male', '1003', 'Development', 'Party member', '9');
insert into employees values ('10', 'Da Ji', 'female', '1003', 'Development', 'League member', '9');
insert into employees values ('11', 'Li Bai', 'male', '1002', 'test', 'League member', '4');
insert into employees values ('12', 'Sun Bin', 'male', '1001', 'Marketing Manager', 'Party member', null);

create table salary (
sid int(10) primary key,
empid int(10) not null,
salary int(10) not null -- wages
);

insert into salary values ('1', '7', '2100');
insert into salary values ('2', '6', '2000');
insert into salary values ('3', '12', '5000');
insert into salary values ('4', '9', '1999');
insert into salary values ('5', '10', '1900');
insert into salary values ('6', '1', '3000');
insert into salary values ('7', '2', '5500');
insert into salary values ('8', '5', '2000');
insert into salary values ('9', '3', '1500');
insert into salary values ('10', '8', '4000');
insert into salary values ('11', '11', '2600');
insert into salary values ('12', '4', '5300');

2, Step 2: input the table creation statement into navicat to create three tables

1) New database test

2) Enter the sql statement into the query editor and run

3) Create data table and paste it into excel

3, Step 3: understand the three steps of query questions

--1. See which tables the title field comes from. If it's multiple tables, use inner join
 --(do not use subquery temporarily, unless you can explicitly use subquery or ask for subquery)

--2. According to the meaning of the question: flexible selection of query commands. The order of exclusion of multiple query commands is
 --where filtering
 --Grouping (1) explicit: each, 2) implicit: filter total [such as number of people or other aggregate functions]
--Having (of)
--Sort [highest]
--Value [top few]

--3. select the field according to the meaning of the question

4, Step 4 - test step

Question 1: name and political profile of all female employees in marketing department

1) Problem solving steps

2) Get the answers

Select deptname, count (*) from departments as d inner join employees as E on d.deptd = e.deptd where political status = 'Party member' group by deptname

2. Display the employee number and name of the top three employees with the highest salary

1) Problem solving steps

2) Get the answer to the question

select e.empid,empname,salary from salary as s inner join employees as e on s.empid=e.empid order by salary desc limit 3

Five, summary

--1. See which tables the title field comes from. If it's multiple tables, use inner join
 --(do not use subquery temporarily, unless you can explicitly use subquery or ask for subquery)

--2. According to the meaning of the question: flexible selection of query commands. The order of exclusion of multiple query commands is
 --where filtering
 --Grouping (1) explicit: each, 2) implicit: filter total [such as number of people or other aggregate functions]
--Having (of)
--Sort [highest]
--Value [top few]

--3. select the field according to the meaning of the question