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

This article mainly gives you examples to introduce the mysql multi-table query method, the contents of the article are carefully selected and edited by the author, with a certain pertinence, the reference significance for everyone is still relatively great, the following with the author to understand the mysql multi-table query method.

Create a sample data table:

# Student form create table stu (sid int primary key,sname varchar (10) not null); # transcript create table score (sid int, score int, cid int); # subject table create table subjects (cid int primary key,cname varchar (10)); alter table score add constraint fk_score_sid foreign key (sid) references stu (sid); alter table score add constraint fk_score_cid foreign key (cid) references subjects (cid)

Inquire about student scores and subject names

Select * from stu,score where stu.sid = score.sid

Those who inquire about students' score requirements of more than 70%

Select s.sid, s.sname, c.score from stu sfocus score c where s.sid = c.sid and c.score > 70

Inquire about the grades of all students

Select * from stu s inner join score c on s.sid = c.sid Inner join is equivalent to linking two tables into one table query using inner join keyword condition using on and Inner can omit outer link query left outer join query the data in the left table as the main left side table will be queried, which may produce useless data select s.sid, s.sname, c.score from score c left join stu s on s.sid = c.sid The right outer link queries select s.sid, s.sname, c.score from score c right join stu s on s.sid = c. Sid. natural link: it automatically matches the same fields in the table and naturally queries select * from stu natural join score without foreign keys.

Inquire about the scores and examination subjects of all students

Select s.sid, s.sname, c. Score. u.cname from stu s left join score c on s.sid = c.sid join subjects u on c.cid = u.cid. when querying three tables, there are 2 conditions, n tables and 1 conditions.

Create a sample data table:

# employee form CREATE TABLE emp (empno INT, ename VARCHAR (50), job VARCHAR (50), mgr INT, hiredate DATE, sal DECIMAL (7magic2), comm decimal (7page2), deptno INT); INSERT INTO emp values (7369) SMITHQ, CLERK (1980-12-17), 800 (20); INSERT INTO emp values (7499, 799, 798, 7698, 1600, 300, 30) INSERT INTO emp values (7521); INSERT INTO emp values (7566); INSERT INTO emp values (7654); INSERT INTO emp values (7654); INSERT INTO emp values (7698); INSERT INTO emp values (7698); (7566) 1250; 500 (30); (7566). INSERT INTO emp values (7782); INSERT INTO emp values (778-8); INSERT INTO emp values (1987-04-19); INSERT INTO emp values (7839); INSERT INTO emp values (1981-17); INSERT INTO emp values (7844). INSERT INTO emp values (787-05-23); INSERT INTO emp values (7900); INSERT INTO emp values (7902); INSERT INTO emp values (7902); and INSERT INTO emp values (793). -Department tables: deptCREATE TABLE dept (deptno INT, dname varchar (14), loc varchar (13)); INSERT INTO dept values (10, 'ACCOUNTING',' NEW YORK'); INSERT INTO dept values (20, 'RESEARCH',' DALLAS'); INSERT INTO dept values (30, 'SALES',' CHICAGO'); INSERT INTO dept values (40, 'OPERATIONS',' BOSTON')

Query employees in the same department as SCOTT

Select ename from emp where deptno = (select deptno from emp where ename='SCOTT')

Employee information whose salary is higher than that of everyone in department 30

Select * from emp where sal > (select max (sal) from emp where deptno = 30)

Query employee information whose job and salary are exactly the same as MARTIN

Select * from emp where (job,sal) in (select job,sal from emp where ename='MARTIN')

There are more than two employees with direct subordinates.

Select * from emp where empno in (select mgr from emp group by mgr having count (mgr) > = 2)

Query the employee number 7788 employee name, employee payroll department name, department address

Select e.enameree.salred.dnameplayd.loc from emp e, dept d where e.deptno = d.deptno and empno = 7788

Ask for 7369 employee number, name, manager number and manager name

Select ename,empno from emp where empno in (7369, (select mgr from emp where empno = 7369)); select e1.ename where e1.mgr e1.empnoree2.enameree2.empno from emp e1 where e1.mgr = e2.empno and e1.empno = 7369

Ask for all the information of the employees with the highest salary in each department.

Select * from emp where (deptno, sal) in (select deptno,max (sal) from emp group by deptno); when there are problems in the query process, the conditions of the department are not brought into the query, but the maximum wage can be used to use the result set returned by the query as a new table. Select * from emp E1, (select deptno,max (sal) msal from emp group by deptno) e2 where e1.sal = e2.msal and e1.deptno = e2.deptno

After reading the above about mysql multi-table query methods, many readers must have some understanding, if you need to get more industry knowledge and information, you can continue to pay attention to our industry information column.

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.