Create department table and employee table, one to many
#Create database create database company charset utf8mb4; # Create dept department table create table dept( deptno tinyint unsigned primary key, deptname varchar(20), location varchar(20) ); #Insert Department data insert into dept values (10, 'Accounting', 'New York'), (20, 'Research','Dallas'), (30, 'Sales', 'Chicago'), (40, 'Operations','Boston'); #Create employee table create table emp( empno int unsigned not null primary key auto_increment, empname varchar(20), empjob varchar(20), empleaderno int unsigned, hiredate date, salary decimal(10,3), allowance decimal(10,3), deptno tinyint unsigned constraint fk_deptno_emp foreign key(deptno) references dept(deptno) ); #Insert employee data insert into emp values (7369, 'Smith', 'clerk ', 7902,'1980-12-17',800, null, 20), (7499, 'Allen', 'salesman', 7698,'1981-02-20', 1600,300,30), (7521, 'Ward' , 'salesman', 7698, '1981-02-22',1250,500,30), (7566, 'Jones', 'manager', 7839,'1981-04-02',2975, null, 20), (7654, 'Maritn', 'salesman', 7698,'1981-09-28',1250, 1400,30), (7698,'Blake', 'manager', 7839,'1981-05-01', 2850,null, 30), (7782,'Clark' , 'manager', 7839,'1981-06-09', 2450, null, 10), (7788, 'Scott', 'analyst', 7566,'1987-04-19',3000, null, 20), (7839, 'King', 'president', null, '1981-11-17', 5000, null, 10), (7844, 'Turner' , 'salesman', 7698,'1981-09-08',1500,0,30), (7876, 'Adams', 'clerk', 7788,'1987-05-23',1100, null, 20), (7900, 'James', 'clerk', 7698,'1981-12-03', 950, null, 30), (7902,'Ford', 'analyst', 7566,'1981-12-03', 3000, null, 20), (7934, 'Miller' , 'clerk', 7782,'1982-01-23', 1300,null, 10);
1, cross join
Cross join refers to the Cartesian product of two or more tables and the result of any combination of data in each row of two or more tables.
Syntax: select query content from table1 cross join table2
For example, select * from emp cross join dept;
characteristic:mysql> select * from dept cross join emp; +--------+------------+----------+-------+---------+-----------+-----------+------------+----------+-----------+--------+ | deptno | deptname | location | empno | empname | empjob | empleader | hiredate | salary | allowance | deptno | +--------+------------+----------+-------+---------+-----------+-----------+------------+----------+-----------+--------+ | 10 | Accounting | New York | 7369 | Smith | clerk | 7902 | 1980-12-17 | 800.000 | NULL | 20 | | 20 | Research | Dallas | 7369 | Smith | clerk | 7902 | 1980-12-17 | 800.000 | NULL | 20 | | 30 | Sales | Chicago | 7369 | Smith | clerk | 7902 | 1980-12-17 | 800.000 | NULL | 20 | | 40 | Operations | Boston | 7369 | Smith | clerk | 7902 | 1980-12-17 | 800.000 | NULL | 20 | | 10 | Accounting | New York | 7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.000 | 300.000 | 30 | | 20 | Research | Dallas | 7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.000 | 300.000 | 30 | | 30 | Sales | Chicago | 7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.000 | 300.000 | 30 | | 40 | Operations | Boston | 7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.000 | 300.000 | 30 | | 10 | Accounting | New York | 7521 | Ward | salesman | 7698 | 1981-02-22 | 1250.000 | 500.000 | 30 | | 20 | Research | Dallas | 7521 | Ward | salesman | 7698 | 1981-02-22 | 1250.000 | 500.000 | 30 | | 30 | Sales | Chicago | 7521 | Ward | salesman | 7698 | 1981-02-22 | 1250.000 | 500.000 | 30 | | 40 | Operations | Boston | 7521 | Ward | salesman | 7698 | 1981-02-22 | 1250.000 | 500.000 | 30 | | 10 | Accounting | New York | 7566 | Jones | manager | 7839 | 1981-04-02 | 2975.000 | NULL | 20 | | 20 | Research | Dallas | 7566 | Jones | manager | 7839 | 1981-04-02 | 2975.000 | NULL | 20 | | 30 | Sales | Chicago | 7566 | Jones | manager | 7839 | 1981-04-02 | 2975.000 | NULL | 20 | | 40 | Operations | Boston | 7566 | Jones | manager | 7839 | 1981-04-02 | 2975.000 | NULL | 20 | | 10 | Accounting | New York | 7654 | Maritn | salesman | 7698 | 1981-09-28 | 1250.000 | 1400.000 | 30 | | 20 | Research | Dallas | 7654 | Maritn | salesman | 7698 | 1981-09-28 | 1250.000 | 1400.000 | 30 | | 30 | Sales | Chicago | 7654 | Maritn | salesman | 7698 | 1981-09-28 | 1250.000 | 1400.000 | 30 | | 40 | Operations | Boston | 7654 | Maritn | salesman | 7698 | 1981-09-28 | 1250.000 | 1400.000 | 30 | | 10 | Accounting | New York | 7698 | Blake | manager | 7839 | 1981-05-01 | 2850.000 | NULL | 30 | | 20 | Research | Dallas | 7698 | Blake | manager | 7839 | 1981-05-01 | 2850.000 | NULL | 30 | | 30 | Sales | Chicago | 7698 | Blake | manager | 7839 | 1981-05-01 | 2850.000 | NULL | 30 | | 40 | Operations | Boston | 7698 | Blake | manager | 7839 | 1981-05-01 | 2850.000 | NULL | 30 | | 10 | Accounting | New York | 7782 | Clark | manager | 7839 | 1981-06-09 | 2450.000 | NULL | 10 | | 20 | Research | Dallas | 7782 | Clark | manager | 7839 | 1981-06-09 | 2450.000 | NULL | 10 | | 30 | Sales | Chicago | 7782 | Clark | manager | 7839 | 1981-06-09 | 2450.000 | NULL | 10 | | 40 | Operations | Boston | 7782 | Clark | manager | 7839 | 1981-06-09 | 2450.000 | NULL | 10 | | 10 | Accounting | New York | 7788 | Scott | analyst | 7566 | 1987-04-19 | 3000.000 | NULL | 20 | | 20 | Research | Dallas | 7788 | Scott | analyst | 7566 | 1987-04-19 | 3000.000 | NULL | 20 | | 30 | Sales | Chicago | 7788 | Scott | analyst | 7566 | 1987-04-19 | 3000.000 | NULL | 20 | | 40 | Operations | Boston | 7788 | Scott | analyst | 7566 | 1987-04-19 | 3000.000 | NULL | 20 | | 10 | Accounting | New York | 7839 | King | president | NULL | 1981-11-17 | 5000.000 | NULL | 10 | | 20 | Research | Dallas | 7839 | King | president | NULL | 1981-11-17 | 5000.000 | NULL | 10 | | 30 | Sales | Chicago | 7839 | King | president | NULL | 1981-11-17 | 5000.000 | NULL | 10 | | 40 | Operations | Boston | 7839 | King | president | NULL | 1981-11-17 | 5000.000 | NULL | 10 | | 10 | Accounting | New York | 7844 | Turner | salesman | 7698 | 1981-09-08 | 1500.000 | 0.000 | 30 | | 20 | Research | Dallas | 7844 | Turner | salesman | 7698 | 1981-09-08 | 1500.000 | 0.000 | 30 | | 30 | Sales | Chicago | 7844 | Turner | salesman | 7698 | 1981-09-08 | 1500.000 | 0.000 | 30 | | 40 | Operations | Boston | 7844 | Turner | salesman | 7698 | 1981-09-08 | 1500.000 | 0.000 | 30 | | 10 | Accounting | New York | 7876 | Adams | clerk | 7788 | 1987-05-23 | 1100.000 | NULL | 20 | | 20 | Research | Dallas | 7876 | Adams | clerk | 7788 | 1987-05-23 | 1100.000 | NULL | 20 | | 30 | Sales | Chicago | 7876 | Adams | clerk | 7788 | 1987-05-23 | 1100.000 | NULL | 20 | | 40 | Operations | Boston | 7876 | Adams | clerk | 7788 | 1987-05-23 | 1100.000 | NULL | 20 | | 10 | Accounting | New York | 7900 | James | clerk | 7698 | 1981-12-03 | 950.000 | NULL | 30 | | 20 | Research | Dallas | 7900 | James | clerk | 7698 | 1981-12-03 | 950.000 | NULL | 30 | | 30 | Sales | Chicago | 7900 | James | clerk | 7698 | 1981-12-03 | 950.000 | NULL | 30 | | 40 | Operations | Boston | 7900 | James | clerk | 7698 | 1981-12-03 | 950.000 | NULL | 30 | | 10 | Accounting | New York | 7902 | Ford | analyst | 7566 | 1981-12-03 | 3000.000 | NULL | 20 | | 20 | Research | Dallas | 7902 | Ford | analyst | 7566 | 1981-12-03 | 3000.000 | NULL | 20 | | 30 | Sales | Chicago | 7902 | Ford | analyst | 7566 | 1981-12-03 | 3000.000 | NULL | 20 | | 40 | Operations | Boston | 7902 | Ford | analyst | 7566 | 1981-12-03 | 3000.000 | NULL | 20 | | 10 | Accounting | New York | 7934 | Miller | clerk | 7782 | 1982-01-23 | 1300.000 | NULL | 10 | | 20 | Research | Dallas | 7934 | Miller | clerk | 7782 | 1982-01-23 | 1300.000 | NULL | 10 | | 30 | Sales | Chicago | 7934 | Miller | clerk | 7782 | 1982-01-23 | 1300.000 | NULL | 10 | | 40 | Operations | Boston | 7934 | Miller | clerk | 7782 | 1982-01-23 | 1300.000 | NULL | 10 | +--------+------------+----------+-------+---------+-----------+-----------+------------+----------+-----------+--------+ 56 rows in set (0.00 sec)
Total records: table1 records * table2 records
Total columns: table1 columns + table2 columns
Disadvantages: duplicate values exist
2, Natural join
Natural join is the result of automatic matching of fields with the same name in the relational table, which will remove duplicate columns
Syntax: select query content from table1 natural join table2
mysql> select * from dept natural join emp; +--------+------------+----------+-------+---------+-----------+-----------+------------+----------+-----------+ | deptno | deptname | location | empno | empname | empjob | empleader | hiredate | salary | allowance | +--------+------------+----------+-------+---------+-----------+-----------+------------+----------+-----------+ | 10 | Accounting | New York | 7782 | Clark | manager | 7839 | 1981-06-09 | 2450.000 | NULL | | 10 | Accounting | New York | 7839 | King | president | NULL | 1981-11-17 | 5000.000 | NULL | | 10 | Accounting | New York | 7934 | Miller | clerk | 7782 | 1982-01-23 | 1300.000 | NULL | | 20 | Research | Dallas | 7369 | Smith | clerk | 7902 | 1980-12-17 | 800.000 | NULL | | 20 | Research | Dallas | 7566 | Jones | manager | 7839 | 1981-04-02 | 2975.000 | NULL | | 20 | Research | Dallas | 7788 | Scott | analyst | 7566 | 1987-04-19 | 3000.000 | NULL | | 20 | Research | Dallas | 7876 | Adams | clerk | 7788 | 1987-05-23 | 1100.000 | NULL | | 20 | Research | Dallas | 7902 | Ford | analyst | 7566 | 1981-12-03 | 3000.000 | NULL | | 30 | Sales | Chicago | 7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.000 | 300.000 | | 30 | Sales | Chicago | 7521 | Ward | salesman | 7698 | 1981-02-22 | 1250.000 | 500.000 | | 30 | Sales | Chicago | 7654 | Maritn | salesman | 7698 | 1981-09-28 | 1250.000 | 1400.000 | | 30 | Sales | Chicago | 7698 | Blake | manager | 7839 | 1981-05-01 | 2850.000 | NULL | | 30 | Sales | Chicago | 7844 | Turner | salesman | 7698 | 1981-09-08 | 1500.000 | 0.000 | | 30 | Sales | Chicago | 7900 | James | clerk | 7698 | 1981-12-03 | 950.000 | NULL | +--------+------------+----------+-------+---------+-----------+-----------+------------+----------+-----------+ 14 rows in set (0.01 sec)
Query the employee name and department name with bonus
mysql> select empname,deptname from dept natural join emp where allowance is not null; +---------+----------+ | empname | deptname | +---------+----------+ | Allen | Sales | | Ward | Sales | | Maritn | Sales | | Turner | Sales | +---------+----------+
be careful:
1. The associated table must have fields with the same name (the same field name and the same field data type)
2. The fields with the same name will be de duplicated
3. Natural join can be used with where clause
3, Inner join
It refers to finding the qualified results according to the given connection conditions in the Cartesian product records generated in two or more tables
3.1 sql92 syntax
Syntax: select query content from table1, table2 where Association condition [and] condition
For example, query employee information and employee department information
mysql> select * from emp,dept where emp.deptno=dept.deptno; +-------+---------+-----------+-----------+------------+----------+-----------+--------+--------+------------+----------+ | empno | empname | empjob | empleader | hiredate | salary | allowance | deptno | deptno | deptname | location | +-------+---------+-----------+-----------+------------+----------+-----------+--------+--------+------------+----------+ | 7782 | Clark | manager | 7839 | 1981-06-09 | 2450.000 | NULL | 10 | 10 | Accounting | New York | | 7839 | King | president | NULL | 1981-11-17 | 5000.000 | NULL | 10 | 10 | Accounting | New York | | 7934 | Miller | clerk | 7782 | 1982-01-23 | 1300.000 | NULL | 10 | 10 | Accounting | New York | | 7369 | Smith | clerk | 7902 | 1980-12-17 | 800.000 | NULL | 20 | 20 | Research | Dallas | | 7566 | Jones | manager | 7839 | 1981-04-02 | 2975.000 | NULL | 20 | 20 | Research | Dallas | | 7788 | Scott | analyst | 7566 | 1987-04-19 | 3000.000 | NULL | 20 | 20 | Research | Dallas | | 7876 | Adams | clerk | 7788 | 1987-05-23 | 1100.000 | NULL | 20 | 20 | Research | Dallas | | 7902 | Ford | analyst | 7566 | 1981-12-03 | 3000.000 | NULL | 20 | 20 | Research | Dallas | | 7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.000 | 300.000 | 30 | 30 | Sales | Chicago | | 7521 | Ward | salesman | 7698 | 1981-02-22 | 1250.000 | 500.000 | 30 | 30 | Sales | Chicago | | 7654 | Maritn | salesman | 7698 | 1981-09-28 | 1250.000 | 1400.000 | 30 | 30 | Sales | Chicago | | 7698 | Blake | manager | 7839 | 1981-05-01 | 2850.000 | NULL | 30 | 30 | Sales | Chicago | | 7844 | Turner | salesman | 7698 | 1981-09-08 | 1500.000 | 0.000 | 30 | 30 | Sales | Chicago | | 7900 | James | clerk | 7698 | 1981-12-03 | 950.000 | NULL | 30 | 30 | Sales | Chicago | +-------+---------+-----------+-----------+------------+----------+-----------+--------+--------+------------+----------+
Or use an alias: select * from emp as e,dept as d where e.deptno=d.deptno
3.2 sql99 syntax
Syntax: select query content from table1 inner join table2 on Association condition [and] Association condition can be selected
mysql> select * from emp as e inner join dept as d on e.deptno=d.deptno; +-------+---------+-----------+-----------+------------+----------+-----------+--------+--------+------------+----------+ | empno | empname | empjob | empleader | hiredate | salary | allowance | deptno | deptno | deptname | location | +-------+---------+-----------+-----------+------------+----------+-----------+--------+--------+------------+----------+ | 7782 | Clark | manager | 7839 | 1981-06-09 | 2450.000 | NULL | 10 | 10 | Accounting | New York | | 7839 | King | president | NULL | 1981-11-17 | 5000.000 | NULL | 10 | 10 | Accounting | New York | | 7934 | Miller | clerk | 7782 | 1982-01-23 | 1300.000 | NULL | 10 | 10 | Accounting | New York | | 7369 | Smith | clerk | 7902 | 1980-12-17 | 800.000 | NULL | 20 | 20 | Research | Dallas | | 7566 | Jones | manager | 7839 | 1981-04-02 | 2975.000 | NULL | 20 | 20 | Research | Dallas | | 7788 | Scott | analyst | 7566 | 1987-04-19 | 3000.000 | NULL | 20 | 20 | Research | Dallas | | 7876 | Adams | clerk | 7788 | 1987-05-23 | 1100.000 | NULL | 20 | 20 | Research | Dallas | | 7902 | Ford | analyst | 7566 | 1981-12-03 | 3000.000 | NULL | 20 | 20 | Research | Dallas | | 7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.000 | 300.000 | 30 | 30 | Sales | Chicago | | 7521 | Ward | salesman | 7698 | 1981-02-22 | 1250.000 | 500.000 | 30 | 30 | Sales | Chicago | | 7654 | Maritn | salesman | 7698 | 1981-09-28 | 1250.000 | 1400.000 | 30 | 30 | Sales | Chicago | | 7698 | Blake | manager | 7839 | 1981-05-01 | 2850.000 | NULL | 30 | 30 | Sales | Chicago | | 7844 | Turner | salesman | 7698 | 1981-09-08 | 1500.000 | 0.000 | 30 | 30 | Sales | Chicago | | 7900 | James | clerk | 7698 | 1981-12-03 | 950.000 | NULL | 30 | 30 | Sales | Chicago | +-------+---------+-----------+-----------+------------+----------+-----------+--------+--------+------------+----------
Inquire about the employee letter and department name with department No. 30
mysql> select e.*,d.deptname from emp as e inner join dept as d on e.deptno=d.deptno where d.deptno=30; +-------+---------+----------+-----------+------------+----------+-----------+--------+----------+ | empno | empname | empjob | empleader | hiredate | salary | allowance | deptno | deptname | +-------+---------+----------+-----------+------------+----------+-----------+--------+----------+ | 7499 | Allen | salesman | 7698 | 1981-02-20 | 1600.000 | 300.000 | 30 | Sales | | 7521 | Ward | salesman | 7698 | 1981-02-22 | 1250.000 | 500.000 | 30 | Sales | | 7654 | Maritn | salesman | 7698 | 1981-09-28 | 1250.000 | 1400.000 | 30 | Sales | | 7698 | Blake | manager | 7839 | 1981-05-01 | 2850.000 | NULL | 30 | Sales | | 7844 | Turner | salesman | 7698 | 1981-09-08 | 1500.000 | 0.000 | 30 | Sales | | 7900 | James | clerk | 7698 | 1981-12-03 | 950.000 | NULL | 30 | Sales | +-------+---------+----------+-----------+------------+----------+-----------+--------+----------+ 6 rows in set (0.00 sec)
Query employee name, salary and department name
mysql> select empname,salary,deptname from emp as e join dept as d where e.deptno=d.deptno; +---------+----------+------------+ | empname | salary | deptname | +---------+----------+------------+ | Clark | 2450.000 | Accounting | | King | 5000.000 | Accounting | | Miller | 1300.000 | Accounting | | Smith | 800.000 | Research | | Jones | 2975.000 | Research | | Scott | 3000.000 | Research | | Adams | 1100.000 | Research | | Ford | 3000.000 | Research | | Allen | 1600.000 | Sales | | Ward | 1250.000 | Sales | | Maritn | 1250.000 | Sales | | Blake | 2850.000 | Sales | | Turner | 1500.000 | Sales | | James | 950.000 | Sales | +---------+----------+------------+ 14 rows in set (0.00 sec)
Query the name and number of employees with bonus
mysql> select empname, deptname from emp as e join dept as d on e.deptno=d.deptno and e.allowance is not null; +---------+----------+ | empname | deptname | +---------+----------+ | Allen | Sales | | Ward | Sales | | Maritn | Sales | | Turner | Sales | +---------+----------+
Query the employee name and department name with the second character e of the Department name
# where can also be changed to and mysql> select empname,deptname from emp as e inner join dept as d on e.deptno=d.deptno where deptname like '_e%'; +---------+----------+ | empname | deptname | +---------+----------+ | Smith | Research | | Jones | Research | | Scott | Research | | Adams | Research | | Ford | Research | +---------+----------+
Query the Department name with more than 5 employees in the Department
mysql> select d.deptname from emp as e join dept as d on e.deptno=d.deptno group by e.deptno having count(*)>5; +----------+ | deptname | +----------+ | Sales | +----------+
be careful:
1.inner can be omitted
2. Two or more tables have the same field. You need to add a table name before the field. You can use the alias of the table name.
4, Self join: two associated tables are the same table
For example: query employee name, employee salary, employee leader name, employee leader salary (equivalent connection query)
1. Realized with sql92:
mysql> select e.empname as 'employee',e.salary,m.empname as 'leader',m.salary from emp as e , emp as m where e.empleaderno=m.empno; +--------+----------+--------+----------+ | employee | salary | leader | salary | +--------+----------+--------+----------+ | Smith | 800.000 | Ford | 3000.000 | | Allen | 1600.000 | Blake | 2850.000 | | Ward | 1250.000 | Blake | 2850.000 | | Jones | 2975.000 | King | 5000.000 | | Maritn | 1250.000 | Blake | 2850.000 | | Blake | 2850.000 | King | 5000.000 | | Clark | 2450.000 | King | 5000.000 | | Scott | 3000.000 | Jones | 2975.000 | | Turner | 1500.000 | Blake | 2850.000 | | Adams | 1100.000 | Scott | 3000.000 | | James | 950.000 | Blake | 2850.000 | | Ford | 3000.000 | Jones | 2975.000 | | Miller | 1300.000 | Clark | 2450.000 | +--------+----------+--------+----------+
2. Implemented with sql99
mysql> select e.empname as 'employee',e.salary,m.empname as 'leader',m.salary from emp as e inner join emp as m on e.empleaderno=m.empno; +--------+----------+--------+----------+ | employee | salary | leader | salary | +--------+----------+--------+----------+ | Smith | 800.000 | Ford | 3000.000 | | Allen | 1600.000 | Blake | 2850.000 | | Ward | 1250.000 | Blake | 2850.000 | | Jones | 2975.000 | King | 5000.000 | | Maritn | 1250.000 | Blake | 2850.000 | | Blake | 2850.000 | King | 5000.000 | | Clark | 2450.000 | King | 5000.000 | | Scott | 3000.000 | Jones | 2975.000 | | Turner | 1500.000 | Blake | 2850.000 | | Adams | 1100.000 | Scott | 3000.000 | | James | 950.000 | Blake | 2850.000 | | Ford | 3000.000 | Jones | 2975.000 | | Miller | 1300.000 | Clark | 2450.000 | +--------+----------+--------+----------+
Non equivalent connection query
Query employee name, employee salary, employee leader name and employee leader salary, and the employee number is greater than the leader number.
mysql> select e.empname as 'employee', e.empno as 'Employee number',e.salary,m.empname as 'leader',m.empno as 'Leader number',m.salary from emp as e , empas m where e.empleaderno=m.empno and e.empno>e.empleaderno; +--------+--------------+----------+--------+--------------+----------+ | employee | Employee number | salary | leader | Leader number | salary | +--------+--------------+----------+--------+--------------+----------+ | Scott | 7788 | 3000.000 | Jones | 7566 | 2975.000 | | Turner | 7844 | 1500.000 | Blake | 7698 | 2850.000 | | Adams | 7876 | 1100.000 | Scott | 7788 | 3000.000 | | James | 7900 | 950.000 | Blake | 7698 | 2850.000 | | Ford | 7902 | 3000.000 | Jones | 7566 | 2975.000 | | Miller | 7934 | 1300.000 | Clark | 7782 | 2450.000 | +--------+--------------+----------+--------+--------------+----------+
5, left/right out join:
External join can not only filter out the data records matching the connection conditions from the Cartesian product records generated by two or more tables, but also retain some unmatched records according to the user's specification. According to different sources of mismatched records, external connections can be divided into left external connections and right external connections.
You should not only query the records that meet the conditions in two or more tables, but also query the records that do not meet the connection conditions in one of the tables
Query employee table and employee department information
select * from emp e, depts d where e.deptno=d.deptno
Departments without employees should also be displayed
grammar select * from table1 left|right outer join table2 on Connection conditions left join: The left outer connection will be placed left join The records in the left table that do not meet the conditions are also displayed right join: The right outer connection will be placed right join Records that do not meet the conditions in the right parity are also displayed
For example:
Query each employee's name, position, leader's name and leader's position, and display employees without leaders
Compare the difference between the inner connection and the left outer connection as follows:
# Inner connection mysql> select e.empname,e.empjob,m.empname,m.empjob from emp as e,emp as m where e.empleaderno=m.empno; +---------+----------+---------+-----------+ | empname | empjob | empname | empjob | +---------+----------+---------+-----------+ | Smith | clerk | Ford | analyst | | Allen | salesman | Blake | manager | | Ward | salesman | Blake | manager | | Jones | manager | King | president | | Maritn | salesman | Blake | manager | | Blake | manager | King | president | | Clark | manager | King | president | | Scott | analyst | Jones | manager | | Turner | salesman | Blake | manager | | Adams | clerk | Scott | analyst | | James | clerk | Blake | manager | | Ford | analyst | Jones | manager | | Miller | clerk | Clark | manager | +---------+----------+---------+-----------+ 13 rows in set (0.00 sec) # Left outer connection mysql> select e.empname,e.empjob,m.empname,m.empjob from emp as e left outer join emp as m on e.empleaderno=m.empno; +---------+-----------+---------+-----------+ | empname | empjob | empname | empjob | +---------+-----------+---------+-----------+ | Smith | clerk | Ford | analyst | | Allen | salesman | Blake | manager | | Ward | salesman | Blake | manager | | Jones | manager | King | president | | Maritn | salesman | Blake | manager | | Blake | manager | King | president | | Clark | manager | King | president | | Scott | analyst | Jones | manager | | King | president | NULL | NULL | | Turner | salesman | Blake | manager | | Adams | clerk | Scott | analyst | | James | clerk | Blake | manager | | Ford | analyst | Jones | manager | | Miller | clerk | Clark | manager | +---------+-----------+---------+-----------+ 14 rows in set (0.00 sec) You can see one more piece of data King,because King of empleaderno by Null，Internal connection query will not be displayed
Query the details of all departments and the average monthly salary of each department, including departments without employees, and sort by the average monthly salary from high to low.
Compare the difference between inner connection and right outer connection
#Inner connection mysql> select d.*,avg(salary) from emp as e inner join dept as d on e.deptno=d.deptno group by d.deptno order by avg(salary) desc; +--------+------------+----------+--------------+ | deptno | deptname | location | avg(salary) | +--------+------------+----------+--------------+ | 10 | Accounting | New York | 2916.6666667 | | 20 | Research | Dallas | 2175.0000000 | | 30 | Sales | Chicago | 1566.6666667 | +--------+------------+----------+--------------+ 3 rows in set (0.00 sec) #Right outer connection mysql> select d.*,avg(salary) from emp as e right outer join dept as d on e.deptno=d.deptno group by d.deptno order by avg(salary) desc; +--------+------------+----------+--------------+ | deptno | deptname | location | avg(salary) | +--------+------------+----------+--------------+ | 10 | Accounting | New York | 2916.6666667 | | 20 | Research | Dallas | 2175.0000000 | | 30 | Sales | Chicago | 1566.6666667 | | 40 | Operations | Boston | NULL | +--------+------------+----------+--------------+ 4 rows in set (0.00 sec) # The left outer connection has the same effect as the right outer connection select d.*,avg(salary) from dept as d left outer join emp as e on e.deptno=d.deptno group by d.deptno order by avg(salary) desc; You can see that there is one more piece of data in the external connection on the right, because there are no employees in department 40. If the conditions are not met during the internal connection, it will not be displayed
6, Multi table query exercise
1. Query employee name, department number and department name (SQL92 and sQL99 respectively)
#sql92 select empname,d.deptno,deptname from emp as e,dept as d where e.deptno=d.deptno; #sql99 select empname,d.deptno,deptname from emp as e inner join dept as d on e.deptno=d.deptno;
2. Query the unique list of all positions with department number 30, and display the Department location and department number at the same time
mysql> select distinct empjob,d.location,d.deptno from emp as e inner join dept as d on e.deptno=d.deptno and d.deptno=30; +----------+----------+--------+ | empjob | location | deptno | +----------+----------+--------+ | salesman | Chicago | 30 | | manager | Chicago | 30 | | clerk | Chicago | 30 | +----------+----------+--------+
3. Display all employee names and department names with a in the employee name
mysql> select empname,deptname from emp as e inner join dept as d on e.deptno=d.deptno and empname like '%a%'; +---------+------------+ | empname | deptname | +---------+------------+ | Allen | Sales | | Ward | Sales | | Maritn | Sales | | Blake | Sales | | Clark | Accounting | | Adams | Research | | James | Sales | +---------+------------+ 7 rows in set (0.00 sec)
4. Use the inner link to write a query to display the names, positions and department names of all employees working in New York
mysql> select empname,empjob,deptname from emp as e inner join dept as d on e.deptno=d.deptno and location='New York'; +---------+-----------+------------+ | empname | empjob | deptname | +---------+-----------+------------+ | Clark | manager | Accounting | | King | president | Accounting | | Miller | clerk | Accounting | +---------+-----------+------------+ 3 rows in set (0.00 sec)
5. Display the employee's name, employee number and the name and leader number of their leader
mysql> select e.empname as 'Employee name',e.empno as 'Employee number',m.empname as 'Leader name',m.empno as 'Leader name' from emp as e,emp as m where e.empleaderno=m.empno; +--------------+--------------+--------------+--------------+ | Employee name | Employee number | Leader name | Leader name | +--------------+--------------+--------------+--------------+ | Smith | 7369 | Ford | 7902 | | Allen | 7499 | Blake | 7698 | | Ward | 7521 | Blake | 7698 | | Jones | 7566 | King | 7839 | | Maritn | 7654 | Blake | 7698 | | Blake | 7698 | King | 7839 | | Clark | 7782 | King | 7839 | | Scott | 7788 | Jones | 7566 | | Turner | 7844 | Blake | 7698 | | Adams | 7876 | Scott | 7788 | | James | 7900 | Blake | 7698 | | Ford | 7902 | Jones | 7566 | | Miller | 7934 | Clark | 7782 | +--------------+--------------+--------------+--------------+ 13 rows in set (0.00 sec)
6. Query the leaders of all employees, including employee king, who has no leader. Display the employee number, employee name, leader number and leader name, and sort them in ascending order by employee number
mysql> select e.empno, e.empname,e.empjob,m.empname,m.empjob from emp as e left outer join emp as m on e.empleaderno=m.empno order by e.empno desc; +-------+---------+-----------+---------+-----------+ | empno | empname | empjob | empname | empjob | +-------+---------+-----------+---------+-----------+ | 7934 | Miller | clerk | Clark | manager | | 7902 | Ford | analyst | Jones | manager | | 7900 | James | clerk | Blake | manager | | 7876 | Adams | clerk | Scott | analyst | | 7844 | Turner | salesman | Blake | manager | | 7839 | King | president | NULL | NULL | | 7788 | Scott | analyst | Jones | manager | | 7782 | Clark | manager | King | president | | 7698 | Blake | manager | King | president | | 7654 | Maritn | salesman | Blake | manager | | 7566 | Jones | manager | King | president | | 7521 | Ward | salesman | Blake | manager | | 7499 | Allen | salesman | Blake | manager | | 7369 | Smith | clerk | Ford | analyst | +-------+---------+-----------+---------+-----------+ 14 rows in set (0.00 sec)
7. Display the names and entry dates of all employees who entered the company before their leaders, together with the names and entry dates of their leaders.
mysql> select e.empname as 'Employee name',e.hiredate,m.empname as 'Leader name',m.hiredate from emp as e join emp as m on e.empleaderno=m.empno and e.hiredate<m.hiredate; +--------------+------------+--------------+------------+ | Employee name | hiredate | Leader name | hiredate | +--------------+------------+--------------+------------+ | Smith | 1980-12-17 | Ford | 1981-12-03 | | Allen | 1981-02-20 | Blake | 1981-05-01 | | Ward | 1981-02-22 | Blake | 1981-05-01 | | Jones | 1981-04-02 | King | 1981-11-17 | | Blake | 1981-05-01 | King | 1981-11-17 | | Clark | 1981-06-09 | King | 1981-11-17 | +--------------+------------+--------------+------------+ 6 rows in set (0.00 sec)