preface

Hey, everybody, I’m A yard farm On Saturday!

Last time I said something about or,! =,in/not in between,like,limit,order by,group by

But that’s not all. Let’s move on!

The query

Here’s our data.

It turns out that class_id is a number because we designed the student table separately from the class table.

But what if we want to find out what class this person is in?

Even the table query

Method 1,where join table

grammar

SELECT * from table1, table2 WHERE table1 = table2 Foreign key column; SELECT the table 1. Column 1, table 1. Column 2, table 2. The column 1,... From table1, table2 WHERE table1 = table2, table2 = table2;Copy the code

A join table displays all columns

SELECT * from student,class WHERE student.class_id=class.id;
Copy the code

The execution result

Now you know which student is in which class!

A linked table displays the specified column

SELECT student.id,student.`name`,class.title from student,class WHERE student.class_id=class.id;
Copy the code

The execution result

Method 2,left join table

Joining tables through where is simple, but has obvious drawbacks.

Because our where is followed by a judgment condition, using where to join a table is a bit of a logical mess.

In general join table operations, left is also used to join tables.

grammar

SELECT * from table 1 LEFT JOIN table 2 on table 1 Foreign key field = Table 2. SELECT the table 1. Column 1, table 1. Column 2, table 2. The column 1,... From table 1 LEFT JOIN table 2 on table 1. Foreign key field;Copy the code

A join table displays all columns

SELECT * from student LEFT JOIN class on student.class_id=class.id;
Copy the code

The execution result

Select the specified column query

SELECT student.id,student.`name`,class.title from student LEFT JOIN class on student.class_id=class.id;
Copy the code

The execution result

In fact, this is the same as where table.

Method three,inner table

Inner and left are the same except that left is forward concatenation and inner is backward concatenation.

It’s like student schedules and class schedules.

If it is through the student table and the class table is in the forward direction, use left.

If it is from the class table to the student table, it is the reverse, use inner.

If you use “left” in the reverse order, some null values will occur.

Inner has the same syntax as left, except that the table is in a different order.

Other operating

All of the above operations are query operations, basically the entry is ok, let’s look at the rest of the add, delete, change operations.

Increasing (insert)

A single insert

grammar

INSERT INTO table (1, 2,...) Values (1, 2...) ;Copy the code

Add a student information

INSERT into student(name,age,gender,class_id) VALUES(" 李 阳 ",22,"男",1);Copy the code

The execution result

Table of contents

Multiple insert

grammar

INSERT INTO table (1, 2,...) Values (1, 2...) ,(value 1, value 2...) ;Copy the code

Add student information in batches

INSERT INTO student (NAME, age, gender, class_id) VALUES (" 新 年 ", 18, "新 年 ", 2), (" 新 年 ", 24," 新 年 ", 3);Copy the code

The execution result

Table of contents

Change (update)

grammar

UPDATE < table > set column = value where < condition >;Copy the code

Change Li Si’s age to 88

UPDATE student set age = 99 where student set age = 99Copy the code

The execution result

Delete (delete)

grammar

Delete from < table name > delete from < table name > whereCopy the code

Delete zhang SAN

DELETE from student where name="张三"
Copy the code

The execution result

conclusion

This chapter is a bit of a closing chapter, with the addition of the join table query, followed by the addition of Mysql add, delete, change query.

Table concatenation is left and inner.

Insert Data You can insert single data or multiple data and multiple parameters.

If you have any problems during the operation, please leave a comment below and we will solve them as soon as we see them.

The harder you work, the luckier you get.

I am a code non Tuesday, if you think it is good, remember to like it.

Thank you for watching.