MySQL addition, deletion, modification and query (Advanced)

    We introduced it in the last post MySQL Compare the basic addition, deletion, modification and check , Here are some advanced usages of adding, deleting, checking and modifying , Advanced usage , actually , The most important advanced part is the query part , Adding is mainly to insert the query result as a value ; The query part contains aggregate queries 、 The joint query （ Multi-table query ）; It does not involve modification and deletion of advanced .

MySQL Add or delete check change （ Basics ）_Naion The blog of -CSDN Blog https://blog.csdn.net/Naion/article/details/125809324?spm=1001.2014.3001.5501 Continue to use idea Remotely connect to the database for instructions .idea How to connect remotely? Please click the link .

Database Constraints

    As shown in the figure , Now my idea is ： Can you act as if you didn't give id When you assign , Give him a default value ？ Or you can only insert one sheet three . To implement these functions , Database constraints are required .

Constraint type

（1）NOT NULL - Indicates that a column cannot store NULL value , When the inserted value is null When , Report errors ;

（2）UNIQUE - Ensure that each row of a column must have a unique value , There can be no duplicate values ;

（3）DEFAULT - Specifies the default value when the column is not assigned a value ;

（4）PRIMARY KEY - NOT NULL and UNIQUE The combination of , Make sure that a column （ Or a combination of two or more columns ） There's a unique logo , Helps to find a specific record in a table more easily and quickly .

（5）FOREIGN KEY - Ensure referential integrity of data in one table matching values in another table .

（6）CHECK - Ensure that the values in the column meet the specified conditions . however MySQL I won't support it check sentence .

such as ：

not null constraint

unique constraint

default Default constraint

primary key Primary key constraint

    The primary key is not null and unique The combination of , For integer type primary keys , Often with auto_increment Use , So you can just insert name,id It will automatically calculate for you , Every time you insert one , Number plus 1.

foreign key Foreign key constraints

foreign key ( Field name ) references Main table ( Column )

    Primary key or unique key used to associate other tables . If the association is not a primary key or a unique key, an error will be reported . such as , Students have their own classes , Zhang San and Li Si are friends 1 Class , Wang Wushi 2 Class .

create table class
(
    class_id int primary key auto_increment,
    class_name varchar(20) unique
);
insert into class(class_name)
values ('mysql1 class '),
       ('mysql2 class ');

create table student
(
    id int primary key auto_increment,
    name varchar(20) not null,
    class_id int, #  class id
    foreign key (class_id) references class(class_id)
);
insert into student(name, class_id)
values (' Zhang San ', 1),
       (' Li Si ', 1),
       (' Wang Wu ', 2);
select *
from student;

  If I plug in class three , You're going to report a mistake .

newly added

Insert the result of the query as a value .

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

such as , Created student2 surface , hold student Copy the contents of to student2 In the table .

Aggregate query

    Basic queries and conditional queries are queries between columns . To insert between rows , Use aggregate queries .

Aggregate functions

Such as query student Number of people in the table , Use count() function .

group by Clause

select column1, sum(column2), .. from table group by column1,column3;

    Use group by Clause to group the specified columns , Note the use of group by In group query ,select The specified field must be “ Group by field ”, If you want other fields to appear in select Must be included in the aggregate function . For example, how many people are there in the two classes .

    Of course you can group by Clause before where sentence , Achieve the effect of filtering first and then grouping , Don't allow where Put the sentence in group by After clause , Otherwise use having sentence .

having

    where Put it in group by Before , First filter in the Group ;group by Then you have to filter , Not allowed where, But use having.where Sub statements act on ungrouped tables and views ,having Statement works on grouped groups .where Statements cannot contain aggregate functions , and having Aggregate functions are often used .

For example, the number of queries after grouping is greater than 1 Class .

Of course, it can be used at the same time where and having stay group by clause .

The joint query （ Multi-table query ）

    In actual development , You need to use multiple tables for joint query , such as , Jointly query students' corresponding classes , This is not the corresponding query id, It's the class name of each student . Multi table query is to take Cartesian product of multiple table data .

    The cartesian product , Also called cross join, yes SQL A way to connect two tables in . If A The data in the table are m That's ok ,B The data in the table are n That's ok , that A and B Do Cartesian product , The result is m*n That's ok .

    Usually we have to be practical SQL Avoid using Cartesian product directly , Because it will make “ Data explosion ”, Especially when there is a large amount of data . But clever use , Instead, it helps us solve problems quickly .

Internal connection

select * from A,B;

select * from A [inner] join B on Connection condition and Other conditions ;

Jointly query students' corresponding classes , First, Cartesian product .

    We can see that there are many query results , This is the time , The connection conditions need to be determined , Filter invalid information . Observation can tell ,student In the table class_id It must be with class_id One to one correspondence of values in , stay student In the table ,1 Of course, classes correspond class In the table 1 class , Add this condition .

Now it seems that the data of this table is much better . What we need is the corresponding class of students , So under screening one .

The above steps can be completed at one go , But at first , You need to take your time .

use join The sentence of .

select class.class_name, student.name
from class
    join java_104.student
where class.class_id = student.class_id;
# on class.class_id = student.class_id; #  Or use on To connect 

Both grammars can achieve their goals ,join There are also special uses .

External connection

    The outer connection is divided into left outer connection and right outer connection . If the union query , The table on the left shows that it is a left outer join ; The table on the right shows that we call it right outer join .

-- The left outer join , surface 1 Completely show

select Field name  from Table name 1 left join Table name 2 on Connection condition ;

-- Right connection , surface 2 Completely show

select Field from Table name 1 right join Table name 2 on Connection condition ;

Now in class New classes in the table ,student New students in the table , But there is no class .

Then look at the difference between the left outer connection and the right outer connection , And the difference with inner connection .

insert into class(class_name)
values ('mysql3 class ');

insert into student(name)
values (' Wang Ma Zi ');

select class.class_name, student.name
from class
         left join student
                   on class.class_id = student.class_id;
select class.class_name, student.name
from class
         right join student
                   on class.class_id = student.class_id;

Their results can be clearly represented by graphs .

Self join

    Connect and query by yourself . This kind of query is a trick , Used to convert rows into columns for query . In the process of query, you need to alias the table , Otherwise, the report will be wrong . For example, this kind of query is needed for comparison in the same table . Only the usage is written here .

Subquery

Subqueries are embedded in other sql Statement select sentence , It is also called nested query , It is commonly known as dolls .

    A single row subquery returns only one row of records , Multi line subqueries use in Keyword return multiple queries . such as , Ask the students id Greater than 2 Of the students id And name , Conditional queries are not used here .

Merge query

    in application , In order to merge multiple select The results of the implementation of , You can use the set operator union,union all. Use UNION and UNION ALL when , Before and after query result set , Fields need to be consistent . This operator is used to get the union of two result sets . When using this operator , Duplicate lines in the result set will be automatically removed .

union The result of the query will automatically remove the duplicate rows in the result set , however union all Can't .

Inquire about id Less than 3 Or the student's name is " Zhang San " Result . Results and use or The condition query results are consistent .