Single table query syntax of Mysql

1. Complete syntax: (the order of syntax level keywords is as follows)

select distinct field 1, field 2, field 3 form library name Table name
where constraint
group by
having filter conditions
order by sorted fields
limit limits the number of items displayed
;
Priority of keyword execution

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

2. Single table query:

create table emp(
        id int primary key auto_increment,
        name varchar(20) not null,
        sex enum('male','female')not null default 'male',
        age int not null,
        h_date date not null,
        post varchar(50),
        port_comment varchar(100),
        salary double(15,2),
        office int,#One department, one room
        depart_id int #Department id
                 );

Insert data:

insert into emp(name,sex,age,h_date,post,salary,office,depart_id) values
('Xi Shi','male',18,'20170301','tuling',7300.33,401,1), #The following is the teaching department
('sea','male',78,'20150302','teacher',1000000.31,401,1),
('Charlotte','male',81,'20130305','teacher',8300,401,1),
('Gu an','male',73,'20140701','teacher',3500,401,1),
('Zhuge Liang','male',28,'20121101','teacher',2100,401,1),
('Zhou Yu','female',18,'20110211','teacher',9000,401,1),
('Liu Bang','male',18,'19000301','teacher',30000,401,1),
('Jackie Chan','male',48,'20101111','teacher',10000,401,1),

('to one's heart's content','female',48,'20150311','sale',3000,402,2),#The following is the sales department
('multiplication table','female',38,'20101101','sale',4000,402,2),
('Save save','female',18,'20110312','sale',5000,402,2),
('Sisi','female',18,'20160513','sale',6000,402,2),
('Dongdong','female',28,'20170127','sale',7000,402,2),

('duan yu','male',28,'20160311','operation',3000,403,3), #The following is the operation Department
('Qiao Feng','male',18,'19970312','operation',4000,403,3),
('East evil','female',18,'20130311','operation',5000,403,3),
('Western poison','male',18,'20150411','operation',6000,403,3),
('Northern beggar','female',18,'20140512','operation',7000,403,3);

Simple query:

select * from emp;

1. Query a single field:

select post from emp;

2. Remove the repetition of a field:

select distinct post from emp;

3. View employee's monthly salary:

select name,salary from emp;

4. Check the employee's annual salary and name it year_salary:

select name,salary*12 as year_salary form emp;

5. String splicing query:

select concat('name:',name) as new_name,concat('Age:',age)as new_age from emp;

6. Merge one column and splice:

select concat(name,':',age) as name_and_age for emp;

7. Merge multiple at one time:

select concat_ws(':',name,age,sex) as info from emp;

II where:
1. Employees with ID greater than 10 but less than 15:

select * from emp where id>10 and id<15;

2. Employees with ID greater than or equal to 10 and less than or equal to 15 are equivalent between

select * from emp where id>=10 and id <= 15;
select * from emp where id between 10 and 15;

3.or equivalent in

select * from emp where id =6 or id=9 or id=12;
select * from emp where id in(6,9,12);

4.not

select * from emp where id not in(6,9,12)

5.like _ Represents a single character,% represents any infinite characters

select * from emp where name like '__';

select * from emp where name like 'west%';

III group by group
What is grouping: classify according to the same part of all records. It must be a field with low discrimination.
Why grouping: when we want to make statistics by group, we must group. The purpose of grouping is to make statistics by group. Then consider that a single record is meaningless
group by is often used with aggregate functions
max Max
min Min
avg average
sum and
count number
1. Grouping syntax:
Error:

select * from emp group by post;
select name,age from emp group by post;

correct:

select post from emp group by post;

2.group_concat can show that the record of the field after grouping (generally not used in this way) is the group member information after grouping

select group_concat(name),group_count(age) from emp group by post;

3. How many people are there in each department, grouped by department

select post,count(id) from emp group by post;

4. Highest salary per department

select post,max(salary) from emp group by post;

5. Average salary of each department

select post,avg(salary) from emp group by post;

6. What is the total salary of each department in the current month

select post,sum(salary) from emp by post;

7. Gender grouping, how many men and women

select sex,count(id) from emp group by sex;

8. Calculate the average salary of employees over the age of 30 in each department
The first step is to filter out employees over the age of 30

select * from emp where age >=30;

The second part is the Group Department, which cooperates with the aggregation function

select post,avg(salary) from emp where age >=30 group by post;#where must precede group by. where cannot be used with an aggregate function

4: having filter conditions
where is the filtering before grouping, that is, an overall filtering is done before grouping
having is filtering after grouping, that is, further filtering of aggregated results after grouping
Example:
If the average salary of each department is:

select post avg(salary) from emp group by post;

Then:
1. Filter the average salary of departments whose average salary is greater than 10000

select post avg(salary) from emp group by post having avg(salary)>10000;

2. Filter the average salary of departments whose average salary is greater than 10000 and less than 200000

select post avg(salary) from emp group by post having avg(salary)>10000 and 
avg(salary) < 200000;

3. Query the position names and employees with less than 2 employees in each department
group_concat can display field names

select post group_concat(name),count(id) from emp group by post having              
count (id)<2;

V distinct de duplication
1. Query the Department with an average salary of 5000 and display a value
Let's write it step by step
1.1 query the average salary of each department first:

select post avg(salary) from emp group by post;

1.2 then query the Department with an average salary of 5000

select post avg(salary) from emp group by post having avg(salary) =5000;

1.3 final weight removal

select distinct avg(salary) from emp group by post having avg(salary)=5000;

Vi order by
1. Age ranking from small to large

select * from emp order by age asc;

2. Large to small

select * from emp order by age desc

3. Sort by age in ascending order first. If the ages are the same, sort by salary in descending order

select * from emp order by age asc,salary desc;

4. Sorting of department average salary, because it is the sorting after grouping

select post,avg(salary) from emp group by post order by avg(salary);

VII Limit limit the number of displays
1. Display 3 items as a whole

select * from limit 3;

2. Sort the details of the person with the highest salary

select * from emp order by salary desc limit 1;

3. # pagination display

select * from emp limit 0,5;#Check 5 from 0
select * from emp limit 5,5;
select * from emp limit 5,10;

Added by viko20 on Thu, 03 Feb 2022 04:58:44 +0200

Programming VIP

Single table query syntax of Mysql

Popular Keywords