Building tables

Create table tb and insert data:

CREATE TABLE `tb_clazz` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'class id',
  `grade` varchar(64) DEFAULT NULL COMMENT 'Class name',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;

INSERT INTO `tb_clazz` (`id`, `grade`) VALUES (1, 'first grade');
INSERT INTO `tb_clazz` (`id`, `grade`) VALUES (2, 'second grade');
INSERT INTO `tb_clazz` (`id`, `grade`) VALUES (3, 'Grade three');
INSERT INTO `tb_clazz` (`id`, `grade`) VALUES (4, 'fourth grade');
INSERT INTO `tb_clazz` (`id`, `grade`) VALUES (5, 'fifth grade');
INSERT INTO `tb_clazz` (`id`, `grade`) VALUES (6, 'Grade six');

Create table tb student and insert data:

CREATE TABLE `tb_student` (
  `id` int(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Student id',
  `name` varchar(128) NOT NULL COMMENT 'Full name',
  `id_clazz` int(11) NOT NULL COMMENT 'class id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;

INSERT INTO `tb_student` (`id`, `name`, `id_clazz`) VALUES (1, 'Zhang San', 1);
INSERT INTO `tb_student` (`id`, `name`, `id_clazz`) VALUES (2, 'Li Si', 2);
INSERT INTO `tb_student` (`id`, `name`, `id_clazz`) VALUES (3, 'Ye Fan', 3);
INSERT INTO `tb_student` (`id`, `name`, `id_clazz`) VALUES (4, 'salary', 4);
INSERT INTO `tb_student` (`id`, `name`, `id_clazz`) VALUES (5, 'john', 3);
INSERT INTO `tb_student` (`id`, `name`, `id_clazz`) VALUES (6, 'cat', 7);

sql statement

1,join

• Left join: (left join) returns records that include all records in the left table and the same join fields in the right table.

SELECT * FROM tb_student s LEFT JOIN tb_clazz c ON s.id_clazz = c.id;

• right join: returns records that include all records in the right table and the same join fields in the left table.

SELECT * FROM tb_student s RIGHT JOIN tb_clazz c ON s.id_clazz = c.id;

inner join: only rows with equal join fields in two tables are returned, which is abbreviated to join.

SELECT * FROM tb_student s JOIN tb_clazz c ON s.id_clazz = c.id;

-- SELECT * FROM tb_student s INNER JOIN tb_clazz c ON s.id_clazz = c.id;

2,UNION

The UNION operator is used to combine the result sets of two or more SELECT statements.

It is worth noting that the SELECT statement within the UNION must have the same number of columns. Columns must also have similar data types. At the same time, the columns in each SELECT statement must be in the same order.

Example 1: by default, the UNION operator selects different values to eliminate duplicate values

SELECT id FROM tb_clazz
UNION 
SELECT id FROM tb_student ;

If duplicate values are allowed, use UNION ALL.

SELECT id  FROM tb_clazz 
UNION ALL
SELECT id FROM tb_student ;

Example 2: the column name in the UNION result set is always equal to the column name in the first SELECT statement in the UNION.

SELECT id ,grade FROM tb_clazz 
UNION 
SELECT id , `name` FROM tb_student ;

SELECT id , `name` FROM tb_student
UNION
SELECT id ,grade FROM tb_clazz ;

insert inserts multiple pieces of data

For example, insert all the data with table id = 1 into the TB student table

INSERT INTO tb_student(NAME,id_clazz) SELECT grade,id  FROM tb_clazz WHERE id = 1;

It should be noted that the data type of the inserted field is similar to that of the inserted field, and the number of fields is equal.

Copy table

Can refer to MySQL replication table