Shulou(Shulou.com)05/31 Report--

Editor to share with you the example analysis of table joins, subqueries and foreign keys in the MySQL5.7 database. I hope you will get something after reading this article. Let's discuss it together.

Article catalogue

Table join

Self-correlation

Foreign key

Internal connection

Left connection

Right connection

Subquery

Introduction of foreign keys

Set foreign key constraints when creating a table

Table join

When the columns of the query result come from multiple tables, you need to join the tables into a large dataset, and then select the appropriate column to return mysql.

At this time, the table is needed to connect.

Internal connection

The inner join selects only the records that match each other in the two tables

Select * from Table 1 inner join Table 2 on Table 1. Column = Table 2. Column-shows all the information about the student, but only shows the class name select s. Class, c.name from students s inner join classes c on s. Class C. idhands, which shows the class name in the first column select c.name, s.* from students s inner join classes c on s.id=c.id -- query the students who can correspond to the class and the class information, and sort select c.name by class, s. * from students s inner join classes c on s.cls_id = c.id order by c.name asc;--. When the same class is in the same class, select c.name is sorted according to the students'id, s. * from students s inner join classes c on s.cls_id = c.id order by c.name asc, s.id asc.

Insert a picture description here

Left connection

The result of the query is the data matched by the two tables, the data held by the left table, and populated with null for the data not stored in the right table.

Select * from Table 1 left join Table 2 on Table 1. Column = Table 2. Column-students table left join classes table and view data for class null select * from students s left join classes c on s.cls_id=c.id having s.cls_id is null;-- left join and query s.cls_id=1 and s.name = "small-j" data select * from students s left join classes c on s.cls_id=c.id having s.cls_id=1 and s.name = "small-j"

Right connection

The query result is the data matched by the two tables, the data held by the right table, and populated with null for the data that does not exist in the left table.

Select * from Table 1 right join Table 2 on Table 1. Column = Table 2. Column; subquery

In some cases, when a query is made, the condition required is the result of another select statement, in which case a subquery is used

Select * from table where (sub-query sentence)-find out the tallest boy in students. Display name and height select s.name, s.high from students s where high= (select max (high) from students) and gender= "male";-- query the above-average height student information select * from students where high > (select avg (high) from students);-- query the student information select * from students where cls_id in (select id from students) corresponding to the student class number cls_id. -- query the idselect * from students where id= (select max (id) from students where gender= "female") and gender= "female" of the oldest girl

Insert a picture description here

Self-correlation

Simply understand that you make a connection query with yourself.

-- query all Guangdong cities under Guangdong Province select * from cities c inner join provinces p on c.provinceid=p.provinceid having p.province = "Guangdong";-- query all Guangdong cities under Guangdong Province-- self-associate select * from areas an inner join areas b on a.id=b.pid having a.name = "Guangdong"

Introduction to foreign keys

The foreign key (foreing key) of MySQL is a special field of the table. For two associated tables, the primary key table of the associated field is the master table (parent table), and the table where the foreign key is located is the slave table (child table).

Note: the primary key cannot contain null values, but null values are allowed in the foreign key, that is, as long as each non-null value of the foreign key appears in the specified primary key, the content of the foreign key is correct.

Set foreign key constraints when creating a table

When creating a foreign key, you must delete the slave table before you can delete the master table.

Create a slave table when the master table needs to exist.

The foreign key association of the slave table must be the primary key of the primary table, and the type of primary key and foreign key must be the same.

[constraint foreign key name] foreign key (field name [, field name 2,...]) References primary key column 1 [, primary key column 2,...]-- create class table create table classes (id int (4) not null primary key, name varchar (36));-- create student table create table student (sid int (4) not null primary key, sname varchar (30), cid int (4) not null) -- create student tables create table student (sid int (4) not null primary key, sname varchar (30), cid int (4) not null, constraint pk_id foreign key (cid) references classes (id)) that directly contain foreign key relationships;-- add foreign key relationships alter table student add constraint pk_id foreign key (cid) references classes (id) through alter;-- delete foreign key constraints alter table student drop foreign key pk_id

After reading this article, I believe you have a certain understanding of "sample analysis of table joins, subqueries and foreign keys in MySQL5.7 database". If you want to know more about it, please follow the industry information channel and thank you for your reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.