Shulou(Shulou.com)06/01 Report--

The following mainly brings you the concept of MySQL multi-table query and instance. I hope that the concept of MySQL multi-table query and instance can bring you practical use, which is also the main purpose of my editing this article. All right, don't talk too much nonsense, let's just read the following.

1. The relationship between tables and tables

One-to-one: user table and identity table, user table is the master table

For example: men's watch, women's watch

Create table man (mid int primary key auto_increment, mname varchar (32), wid int unique); create table woman (wid int primary key auto_increment, wname varchar (32))

One-to-many: the most common table relationships, user tables and order tables

For example: employee table, department table

Create table emp (empno int primary key auto_increment, ename varchar (32), deptno int); create table dept (deptno int primary key auto_increment, dname varchar (32))

Many-to-many: for example, student schedules and curriculum schedules usually split a many-to-many relationship into an one-to-many or many-to-one relationship.

Create table student (sid int primary key auto_increment, sname varchar (32)); insert into student (sname) values ('Dana'); insert into student (sname) values ('Tang Yan'); insert into student (sname) values ('Wang Jianlin'); create table course (cid int primary key auto_increment, cname varchar (32)); insert into course (cname) values ('Chinese') Insert into course (cname) values ('mathematics'); insert into course (cname) values ('English'); insert into course (cname) values ('chemistry'); create table scutellc (cid int, sid int); insert into scutc (sid,cid) values (1L1); insert into scutc (sid,cid) values (1jol 2); insert into scutc (sid,cid) values (1je 3) Insert into scutellc (sid,cid) values (1Magol 4); insert into scutellc (sid,cid) values (2Mague 2); insert into scutellc (sid,cid) values (2Mague 4); insert into scutellc (sid,cid) values (3Magol 1); insert into scutellc (sid,cid) values (3Magee 3)

two。 Why use multiple tables?

Avoid a large amount of data redundancy.

It is not that the more tables are split, the better. Split them according to the actual situation.

3. Concept

Query multiple tables at the same time

4. classification

Merge query

Union, union all

To merge a result set is to merge the query results of two select statements together. (equivalent to union)

The two results of the merge, the number of columns and the order of columns, the class needs to be consistent.

Create table emp (empno int primary key auto_increment, ename varchar (32)); create table dept (deptno int primary key auto_increment, dname varchar (32)); select * from emp union select * from dept; select * from emp union all select * from dept

Join query

Employee table

Create table emp (empno int primary key auto_increment, # employee number ename varchar (32), # employee name job varchar (32), # employee position mgr int, # superior number hiredate date, # entry time sal double # salary comm double, # bonus deptno int # Department)

Department table

Create table dept (deptno int primary key auto_increment, # department number dname varchar (32), # department name loc varchar (32) # department address)

Internal connection: inner join....on, join,

Inner join is a comparison operator that returns only rows that meet the criteria

For example:

Select * from emp inner join dept on emp.deptno=dept.deptno; select * from emp e, dept d where e.deptno = d.deptno; select * from emp e join dept d where e.deptno = d.deptno

External connection:

Left outer connection: LEFT OUTER JOIN | left join. On

Represents the query, all of the left row, and null without it on the right

Select * from emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno

Right outer connection: right join. On or right outer join.... On

Select * from emp e right OUTER JOIN dept d ON e.deptno = d.deptno

Self-connect:

Think of a table as two tables, an employee table and a leadership table, both of which are emp tables

Select e.ename.el.ename from emp e left join emp el on e.mgr = el.empno

Natural connection: natural join (join) | natural left join (same as left join) | natural right join (same as right join)

The natural join automatically determines that the query result is returned with the same field in both tables as the join condition.

Select * from emp natural join dept; select * from emp NATURAL left join dept; select * from emp NATURAL right join dept

Note: the result of Cartesian product will occur if the inner connection does not write the connection condition, which should be avoided, while the outer connection without writing the connection condition will report an error.

Subquery (any subquery, in subquery, SOME subquery, all subquery)

Problems solved by subqueries:

Whose salary is higher than Cong Hao?

Select * from emp where sal > (select sal from emp where ename=' Conghao')

Definition: subqueries allow one query to be nested within another

A subquery is also called an internal query, which is equivalent to an internal query. Those that contain internal queries are called external queries. The results of the subquery are used by the main query.

Questions to be noted:

1. Parenthesis

two。 You can use subqueries after the where select having from of the main query.

3. You cannot use subqueries after group by.

4. The main query and the subquery may not be the same table; only the values returned by the subquery can be used by the main query.

Demand: query the employee information whose department name is manpower.

The first way: using subquery

Select * from emp where deptno= (select deptno from dept where dname=' Human Resources Department')

The second way: using association query

Select * from emp eForce dept d where e.deptno = d.deptno and d.dnameplate 'Ministry of Human Resources'

SQL optimization: use multiple table queries as much as possible

When most of the subqueries are finally executed, they are transformed into a multi-table query. This can be seen through the SQL execution plan.

If you execute the plan through SQL, you will find that it is the same in both ways.

Subquery after 5.from

Demand:

Inquire about employee number, name, monthly salary

Select empno,ename,sal from emp

6. It is generally not sorted in subqueries.

7. Generally, the subquery is executed first, then the main query.

ANY keyword

Suppose the number of results returned by the query inside any is three, such as: result1,result2,result3, then

Select.... From.. Where a > any (...);-> select. From... Where a > result1 or a > result2 or a > result3

Demand:

Inquire about the information that the salary is higher than any employee in Department 1.

Select * from emp where sal > any (select sal from emp where deptno = 1)

ALL keyword

The ALL keyword is similar to the any keyword, except that the above or is changed to and:

Select.... From.. Where a > all (...);-> select. From... Where a > result1 and a > result2 and a > result3

Demand:

Query the employee information of all employee numbers in department 1

Select * from emp where sal > all (select sal from emp where deptno = 1)

SOME keyword

The some keyword is the same as the any keyword. So:

Select.... From.. Where a > any (...);-> select. From... Where a > result1 or a > result2 or a > result3

IN keyword

The IN operator is used in where expressions to support multiple selections in the form of list directions. The syntax is as follows:

Where column in (v1memev2recoverv3rem.); where column not in (v1pencev2recoveryv3recorder.)

When in is preceded by the not operator, it means the opposite of in and is not selected in the write list item.

Case study:

The name of the inquiry department is manpower and R & D staff.

Select * from emp where deptno in (select deptno from dept where dname=' Human Resources Department'or dname=' Research and Development Department') Classification: MySQL Database

For the above concept of MySQL multi-table query and instance, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.