MySQL5.7 database introduces table connections, subqueries, and foreign keys

Free learning recommendations: mysql video tutorial

Article Directory

• • • Table join
    • • Inner join
      • Left join
      • Right join
      • Subquery
    • Self-association
    • Foreign key
    • • Foreign key introduction
      • Creation Set foreign key constraints when table

Table connection

• When querying the column source of the result When there are multiple tables, you need to connect multiple tables into a large data set, and then select the appropriate columns to return to mysql
• At this time, you need to connect the tables

Inner join

• Inner join only selects records that match each other in the two tables

select * from 表1 inner join 表2 on 表1.列 = 表2.列-- 显示学生的所有信息，但只显示班级名称select s.*, c.name from students s inner join classes c on s.id=c.id;-- 将班级名称显示在第一列select c.name, s.* from students s inner join classes c on s.id=c.id;-- 查询 有能够对应班级的学生以及班级信息，按照班级进行排序select c.name, s.* from students s inner join classes c on s.cls_id = c.id order by c.name asc;-- 当同一个班级时，按照学生的id进行从小到大排序select c.name, s.* from students s inner join classes c on s.cls_id = c.id order by c.name asc, s.id asc;

Insert image description here

Left join

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

select * from 表1 left join 表2 on 表1.列=表2.列-- students表左连接classes表 并查看班级为null的数据select * from students s left join classes c on s.cls_id=c.id having s.cls_id is null;-- 左连接 并且 查询 s.cls_id=1 并且 s.name="small-j" 的数据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 join

The query result is the data matched by the two tables. The data held by the right table is used for the data that does not exist in the left table. null padding.

select * from 表1 right join 表2 on 表1.列 = 表2.列;

Subquery

In some cases, when querying, the required condition is the result of another select statement. In this case, it is necessary to use Subquery

select * from 表 where 表(子查询语句)-- 查询出students中身高最高的男生。显示名字和身高select s.name, s.high from students s where high=(select max(high) from students) and gender="男";-- 查询出高于平均身高的学生信息select * from students where high>(select avg(high) from students);-- 查询学生班级号cls_id能够对应的学生信息select * from students where cls_id in (select id from students);-- 查询最大年龄的女生的idselect * from students where id=(select max(id) from students where gender="女") and  gender="女";

Insert picture description here

Self-correlation

Simply understand it as a connection query between yourself and yourself

-- 查询广东省下的所有广东市select * from cities c inner join provinces p on c.provinceid=p.provinceid having p.province="广东省";-- 查询广东省下的所有广东市-- 自关联select * from areas a inner join areas b on a.id=b.pid having a.name="广东";

Foreign key

Introduction to foreign key

• MySQL’s foreign key (foreing key) is a table Special fields. For two tables with an associated relationship, the table where the primary key of the associated field is located is the main table (parent table), and the table where the foreign key is located is the secondary table (child table).
• Note: The primary key cannot contain null values, but null values ​​are allowed in foreign keys. That is, as long as each non-null value of the foreign key appears in the specified primary key, this The contents of the foreign key are correct.

Set foreign key constraints when creating a table

• When creating a foreign key, you must first delete the slave table before you can delete the master table.
• Create the slave table when the master table needs to exist.
• The foreign key association of the secondary table must be the primary key of the primary table, and the types of the primary key and the foreign key must be consistent.

[constraint 外键名] foreign key (字段名 [,字段名2, ...]) references  主键列1 [, 主键列2, ...]

-- 创建班级表create table classes(
    id int(4) not null primary key,
    name varchar(36));-- 创建学生表create table student(
    sid int(4) not null primary key,
    sname varchar(30),
    cid int(4) not null);-- 创建直接含有外键关系的学生表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));-- 通过alter来添加外键关系alter table student add constraint pk_id foreign key (cid) references classes(id);-- 删除外键约束alter table student drop foreign key pk_id;

##Related free learning recommendations: mysql database(Video)

The above is the detailed content of MySQL5.7 database introduces table connections, subqueries, and foreign keys. For more information, please follow other related articles on the PHP Chinese website!