Add, delete, modify and check MySQL tables (advanced)

Article Directory

*Add

Insert query result
Syntax:

INSERT INTO table_name [(column [, column ...])] SELECT

Case: Create a user table with fields for name, email, sex, and mobile phone number. The existing
student data needs to be copied in. The fields that can be copied are name, qq_mail

//创建用户表
drop table if exists test_user;
create table test_user(
   id int primary key auto_increment,
   name varchar(20) comment'姓名',
   email varchar(20) comment'邮箱',
   sex varchar(1) comment'性别',
   mobile varchar(20) comment'手机号'
);
//将学生表的所有数据复制到用户表
insert into test_user(name,qq_mail) select name,qq_mail from student;

*Query (aggregate query)

* Aggregate functions: Do not appear after where, aggregate functions.

COUNT: Returns the number of data queried

Example: Count the number of qq_mail collected by the class, and the data whose qq_mail is NULL will not be included in the result

select count(qq_mail)from student;

You can de-duplicate the counted columns:

select count(distinct id) from exam_result;
SUM: Returns the sum of the queried data, it is not a number and it is meaningless

-Total score of statistical mathematics

select sum(math) from exam_result;

-Failed <60 total score, no result, return NULL

select sum(math) from exam_result where math<60;
AVG: Returns the average value of the queried data, it is not a number without meaning

-Statistical average total score

select avg(math+english+chinese) 平均总分 from exam_result;
MAX: Returns the maximum value of the data queried, it is not a number and it is meaningless

– Return to the highest score in English

select max(english) from exam_result;
MIN: returns the minimum value of the data queried, it is not a number and it is meaningless

– Return> Minimum score of 70 points or more in mathematics

slecet min(math) from exam_result where math>70;

*Grouping: GROUP BY

The GROUP BY clause can be used in the SELECT to perform grouping queries on the specified columns. Need to meet: When using GROUP BY for grouping query, the field specified by SELECT must be the "group by field", and other fields must be included in the aggregate function if they want to appear in the SELECT.
Prepare data:

drop table if exists emp; 
create table emp(
       id int primary key auto_increment, 
       name varchar(20) not null, 
       role varchar(20) not null, 
       salary numeric(11,2) 
       );
       insert into emp(name, role, salary) values 
       ('A','教授', 10000.20), 
       ('B','讲师', 4000.99), 
       ('C','讲师', 999.11), 
       ('D','辅导员', 333.5), 
       ('E','辅导员', 700.33), 
       ('F','副教授', 12000.66);
Insert picture description here


-Query the maximum wage, minimum wage and average wage of each role

 select role, max(salary),min(salary),avg(salary)from emp group by role;
Insert picture description here

*having: filter condition

The role of having is the same as where, but where cannot be used for aggregate functions

Shows the roles whose average salary is less than 1500 and their average salary

select role,avg(salary) from emp group by role having avg(salary)<1500;
Insert picture description here