This article is mainly aimed at the addition, deletion, modification and query of MySQL and upgrading again. If you want to see the basic additions, deletions and changes, you can see the previous article of the blogger. Here, the blogger posts a link for you: https://blog.csdn.net/weixin_53033446/article/details/119005596?spm=1001.2014.3001.5501

When you see this article, you must have understood the basic usage of MySQL. SQL mainly plays with data. Let's take a look at the advanced version of SQL addition, deletion, modification and query!!!
In MySQL, if we want to make a data null, or make its number grow by itself, we need to restrict it when building SQL. Let's look at common constraints

1, Database Constraints

Database constraints, we need to master some common constraints

1. not null means that a column cannot be empty
2. Unique means that each row of a column must have a unique value
3. Default is the default value when no value is assigned to the column
4. primary Key is a combination of not null and unique. It refers to ensuring that a column (or a combination of two columns and multiple columns) has a unique identification, which is conducive to finding specific records in the table more quickly
5. poreign Key refers to the referential integrity that the data in one table matches the values in another table

1.1 code display

1.1.1 not null

create table if exists student;
create table student(
    id int not null,
    age int,
    name varchar(10),
    ...
)

1.1.2 unique

create table if exists student;
create table student(
    id int not null,
    age int unique,
    name varchar(10),
    ...
)

1.1.3 default

create table if exists student;
create table student(
    id int not null,
    age int unique,
    name varchar(10) default 'XXX',
    ...
)

1.1.4 primary Key

Insert the code slice here create table if exists student;
create table student(
    id int not null primary KEY,
    age int unique,
    name varchar(10) default 'XXX',
    ...
)

For integer type primary keys, they often match atuo_increment To represent self growth

1.1.5poreign Key

create table if exists class;
create table class (
    id int primary key auto_increment,
    classId int,
    name varchar (20),
    foreign key (classId) references student(id)
);

The above code means, id Is the primary key, classid Foreign key, Association student In table id

2, Add

Next, let's look at the high-end operation of new data.

Chestnut: create a user table with name Name age Age, sex Gender mobile Mobile number field. The existing student data needs to be copied in. The fields that can be copied are name,age

//User table
create table if exists user;
create table user (
    name varchar (10),
    age int,
    sex varchar (2),
    mobile varchar (11)
);

insert into user (name , age) select name ,age from student;

Three search

3.1 aggregate search

3.1.1 aggregate function

Aggregate function query is actually very easy to understand. It is equivalent to the common functions in our Excel table. It is very easy to operate. Here we list the common function queries.

1. count() returns the number of queried data
2. sum() returns the sum of the queried data
3. avg() returns the average value of the queried data
4. max() returns the maximum value of the queried data
5. min() returns the minimum value of the queried data

Code display:

select count(*) from student;
select sum(math) from exam where math < 60;
select avg(math + english + chinese) Average total score from exam
select max(chinese) from exam;
select min(english) from exam where english > 90;

The above codes are easy to understand and clear at a glance. All routines are the same. They can be used together or add additional conditions for efficient work

3.1.2 group by

The group by field refers to the grouping query on the specified columns, but there is a precondition: when using group by query, the select field must be the grouping basis field, and other fields must be included in the aggregate function if they want to appear in the select field. This explanation is very abstract. Let's show it in a simple syntax:

select test1, sum(test2)  from table group by test1,test3;

For example, chestnuts:
There are many positions in a company. If we want to find the maximum wage, minimum wage and average wage of each position
Role - > position salary - > salary EMP - > information table

select role , max(salary) ,min(salary) , avg(salary) from emp group by role;

This method is also very easy to understand. Practice more and keep it in mind

3.1.3 having

As you all know, when adding conditional queries, we will use the where statement to execute. However, after group by performs group queries, if conditional filtering is required, we can't use where for conditional filtering. At this time, we have to use the having keyword

Take chestnuts: let's continue the chestnuts in the above code
Conditions: query the roles whose average salary is lower than 1500 and their average salary

select role , max(salary) ,min(salary) , avg(salary) from emp group by role having avg(salary) < 1500;

3.2 joint query

Joint query is not only a difficulty in our query, but also the most frequently used method in our work.
The query methods we mentioned earlier are all completed in another table. Joint query is also called multi table query. As the name suggests, it is to query two or more tables. Next, let's take a look at the methods used in joint query!
Before that, let's explain the tables and data used

insert into classes(name, `desc`) values
('Computer department class 1', 'Learned the principle of computer'),
('Class 3, Chinese Department','I studied Chinese traditional literature'),
('Automation class 5','Learned mechanical automation');
insert into student(sn, name, qq_mail, classes_id) values
('001','Zhang San','110@qq.com',1),
('002','Li Si',null,2),
('005','Wang Wu',null,1),
('089','Zhao Liu','xuxian@outlook.com',2),
('055','Sun Qi',null,1),
insert into course(name) values
('Mechanical automation'),('Chinese traditional culture'),('Computer principle'),('language'),('Higher order mathematics'),('english');
insert into score(score, student_id, course_id) values
-- Zhang San
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
-- Li Si
(60, 2, 1),(59.5, 2, 5),
-- Wang Wu
(33, 3, 1),(68, 3, 3),(99, 3, 5),
-- Zhao Liu
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
-- Sun Qi
(81, 5, 1),(37, 5, 5),

3.2.1 internal connection

Syntax:

select [field] from Table 1 Table 1 aliases join Table 2 alias on Connection conditions and Other conditions;
select [field] from Table 1 Table 1 alias table 2 alias where Connection conditions and Other conditions;

The above code is the basic format of internal connection query, and the conditions can be added. Next, let's take a chestnut to show you the "visualization". In this section, we have explained the basic data, and we can query directly

Example 1:

Conditions: query the results of "Sun Qi"

select sco.score from student stu join score sco on stu.id = sco,student_id and stu.name = 'Sun Qi';

Interpretation: in the above code, first of all, the field we want to query is the score, so we select the score column in the score table as the field we want to query. Secondly, there are two tables of students and scores. There is a common information in both tables, that is, the student id. according to the matched students, we will know which score it is, Finally, we add the additional condition "Sun Qi". You can come down by yourself and expand it with the second grammar

Example 2:

Conditions: query the total score of all students and print their personal information

select 
	stu.sn,stu.name , stu.qq_mail,sum(sco.score)
from
	student stu join score sco on stu.id = sco.student_id
group by
	sco.student_id;

Interpretation: in the above code, if we don't understand the fields, we first analyze several tables from the topic, first build the general structure, and then read the questions. We can get the printed personal information and total score. Then we refine the words required by the topic in the student table and the grade table. Here we use the function query. There are common fields in both tables, Student id finally, we get the final answer by grouping the student id

3.2.2 external connection

In fact, there is no general difference between external connection and internal connection, but external connection is divided into left connection and right connection. Among them, if the table on the left is fully displayed, we say it is a left connection. Similarly, if the table on the right is fully displayed, we say it is a right connection. Good understanding. Let's take a look at the basic grammar first!

Syntax:

select Field name from table1 left join table2 on Connection conditions;
select Field name from table1 right join table2 on Connection conditions;

give an example:

Conditions: query the scores and personal information of all students. If there is no score for students, it should also be displayed

select 
	stu.id ,
	stu.sn , 
	stu.name ,
	sut.qq_mail ,
	sco.score,
	sco.course_id
	cou.name
from 
	student stu
	left join score sco on stu.id = sco.sutdent_id
	left join course cou on sco.course_id = cou.id
group by 
	stu.id

Interpretation: this topic is a little complex. According to the previous one, we understand how to obtain the field. Secondly, this is a three table merge query. First, we need to obtain all the student information, so we define it as a full column, so we use left and left connections to represent it. Secondly, we need to match the id obtained from the transcript with the student's id, Get the subject id from the transcript and match it with the id in the subject table. Most of all, we output information through the student id column.

3.2.3 self connection

Self connection, as the name suggests, is completed in a table. Let's go directly to the example!

give an example

Display all information that the score of "computer principle" is higher than that of "mechanical automation"

select id, name from course where name = 'Mechanical automation' or name = 'Computer principle'

select 
	s1.*
from 
	score s1,
	score s2
where 
	s1.student_id = s2.student_id and s1.score < s2.score
	and s1.course_id = 1 and s2.course_id = 3;

3.2.4 sub query

Subqueries are also called embedded queries, which are select statements embedded in other sql statements

single-row subqueries

example:

Conditions: Zhao Liu and I are classmates

select * from student where classes_id = (select classes_id from student where name = 'Zhao Liu');

Multi row sub column query

example

Conditions: query the score information of "Chinese" and "Mathematics" courses

in keyword

select * from score where course_id in (select id from course where name='language' and name = 'mathematics');

exists keyword

select * from score sco where exists (select sco.id from course cou where (name='language' and name='mathematics') and cou.id = sco.course_id);

IV. summary

All the above are our common query methods. Of course, there are those not involved by bloggers. You are also welcome to discuss with bloggers. If you feel OK, don't be stingy with your praise. Give the blogger a key three times!!!