1, Relationship between tables

1.1 one to one

1.2 one to many

A class can have multiple students, which is called one to many relationship

1.3 many to many

A teacher can lead multiple students, and a student can be taught by multiple teachers, so the relationship between the two tables is many to many.

2, Join table query

2.1 concept

If the data fields to be queried are in two tables, you need to query by connecting tables

2.2 Cartesian product

select * from Table name 1 inner join Table name 2;

If the student table is as follows:

The list of teachers is as follows:

If the above writing is unconditional, that is, each piece of data in the student table is used to match the data in the teacher table, that is, 8 * 5 = 40, a huge query result will be generated, which is called Cartesian product.
However, many of the above data are invalid, so in order to avoid Cartesian product, we need to add conditions.

2.3 internal connection

select * from Table name 1 inner join Table name 2 on condition;

The query results are as follows:

We can also alias the table. The syntax is the real table name as alias

2.4 external connection

2.4.1 left outer connection

select field from Left table name left outer join Right table name on condition;

The left outer connection is based on the left table. All data in the left table will be displayed. For the right table, the matching data will be displayed, and if there is no matching, null will be displayed
You can also omit outer

2.4.2 right outer connection

select field from Left table name right outer join Right table name on condition;

The right outer connection is based on the right table. All data in the right table will be displayed. For the left table, the matching data will be displayed, and if there is no matching, null will be displayed

2.4.3 full connection

In this way, we can query all data and display the results
Left outer join statement UNION right outer join statement;

select sname,age,tname from student left join teacher on student.tid=teacher.tid UNION select sname,age,tname from student right join teacher on student.tid=teacher.tid;

2.4.4 import and export Navicat into sql statement

We import the sql statements given by others into the following operations:

Note that the table will not be displayed until you right-click to refresh it after the import is successful.

If we want to export our tables into sql files, we can do the following:

2.4.5 practice

Suppose the dept table is as follows:

The assumed employee table is as follows:

(1) Query the specific information (name, salary and bonus Department) of employees in Sales Department

(2) The last person to query the name is the name of R's employee, work bonus and department

3, Subquery

3.1 concept

The result of one query statement is regarded as the scope or condition of another query statement.

3.2 practice

3.2.1 query all information of the highest paid employees

Step 1: find out the maximum wage first

select max(sal) from emp;The result is 5000

Step 2: query all the information of the person whose salary = 5000

select * from emp where sal=5000;

Step 3: replace with subquery

select * from emp where sal=(select max(sal) from emp);

3.2.2 query the details of people whose salary is higher than 7654 and 7788 who do the same work?

Step 1: determine the main frame

select * from emp where wages>？ and job=?

Step 2: first implement condition 1

select sal from emp where empno=7654 The result is 1250

Step 3: implement condition 2

select job from emp where empno=7788;The result is ANALYST

Step 4: replace with subquery

select * from emp where sal>( select sal from emp where empno=7654) and job=( select job from emp where empno=7788);

The final results are as follows:

5, Practice

5.1 query the information that the salary is higher than that of JONES employees

Step 1: query the salary of JONES first

select sal from emp where ename='JONES'; The result is 2975

Step 2: replace with sub query

select * from emp where sal>( select sal from emp where ename='JONES');

5.2 calculate the average wage of each department higher than the maximum wage of 30 departments

Step 1: write the main frame first

select  deptno, avg(sal)  from emp group by deptno having avg(sal)>?

Step 2: seek the maximum salary of department 30

select max(sal) from emp where deptno=30; The result is 2850

Step 3: replace with subquery

select  deptno, avg(sal)  from emp group by deptno having avg(sal)>(select max(sal) from emp where deptno=30);

5.3 query the top 6 to 10 wages

select sal from emp order by sal desc limit 5,5;

5.4 query information about the same work as SCOTT

Step 1: query SCOTT's work first

select job from emp where ename='SCOTT';The result is the chemist

Step 2: replace with sub query

select * from emp where job=( select job from emp where ename='SCOTT');

5.5 query employee information with bonus

select * from emp where comm is not null;

5.6 query the information of employees whose department number is 20 and whose salary is between 2000 and 3000

select * from emp where deptno=20 and sal between 2000 and 3000;

5.7 query the information of employees working as MANAGER or CLERK.

select * from emp where job='MANAGER' or job='CLERK';

perhaps

select * from emp where job in ('MANAGER','CLERK');

5.8 query the information of the employee whose name is the second positive number and whose name is R.

select * from emp where ename like '_R%';

6, Supplement

mysql has two storage engines, innodb and MYISAM. However, if you want to set foreign keys, the storage engine must be innodb
In fact, create the complete format of the data table:

create table xx(
	Field name type field modifier,
	.....

)engine=innodb default charset utf8;

For the detailed usage of navicat, please refer to Part 03 of today's recording and broadcasting database