Linux mysql-多表查询

Linux mysql-多表查询

多表查询:

建表

mysql> create table emp(emp_id int,emp_name char(10),emp_age tinyint, dpt_id int);

mysql> create table dpt(dpt_id int,dpt_name char(10));





insert into emp values(1,'robin',30,200);

insert into emp values(2,'zorro',25,200);

insert into emp values(3,'tom',22,200);

insert into emp values(4,'jerry',31,100);

insert into emp values(5,'jack',32,100);

insert into emp values(6,'rose',33,100);

insert into emp values(7,'king',40,300);

insert into emp values(8,'jean',41,300);

insert into emp values(9,'natasha',42,300);

insert into emp values(10,'xinghh',43,300);





insert into dpt values(100,'yw');

insert into dpt values(200,'kf');

insert into dpt values(300,'hr');

insert into dpt values(400,'boss');





alter table emp add emp_money int;

update emp set emp_money=10000 where emp_id=1;

update emp set emp_money=20000 where emp_id=2;

update emp set emp_money=30000 where emp_id=3;

update emp set emp_money=40000 where emp_id=4;

update emp set emp_money=50000 where emp_id=5;

update emp set emp_money=60000 where emp_id=6;

update emp set emp_money=70000 where emp_id=7;

update emp set emp_money=80000 where emp_id=8;

update emp set emp_money=90000 where emp_id=9;

update emp set emp_money=100000 where emp_id=10;

mysql> select * from emp;
+--------+----------+---------+--------+-----------+
| emp_id | emp_name | emp_age | dpt_id | emp_money |
+--------+----------+---------+--------+-----------+
|      1 | robin    |      30 |    200 |     10000 |
|      2 | zorro    |      25 |    200 |     20000 |
|      3 | tom      |      22 |    200 |     30000 |
|      4 | jerry    |      31 |    100 |     40000 |
|      5 | jack     |      32 |    100 |     50000 |
|      6 | rose     |      33 |    100 |     60000 |
|      7 | king     |      40 |    300 |     70000 |
|      8 | jean     |      41 |    300 |     80000 |
|      9 | natasha  |      42 |    300 |     90000 |
|     10 | xinghh   |      43 |    300 |    100000 |
+--------+----------+---------+--------+-----------+
10 rows in set (0.00 sec)

mysql> select * from dpt;
+--------+----------+
| dpt_id | dpt_name |
+--------+----------+
|    100 | yw       |
|    200 | kf       |
|    300 | hr       |
|    400 | boss     |
+--------+----------+
4 rows in set (0.00 sec)

各种链接

等值链接(内连接) 只连接匹配的行

不等值连接

外连接－左连接 以左表为准

外连接－右连接 以右表为准

自连接

=================交叉连接======================= 迪卡尔集

mysql> select emp.emp_id,emp.emp_name,dpt.dpt_name from emp,dpt;
+--------+----------+----------+
| emp_id | emp_name | dpt_name |
+--------+----------+----------+
|      1 | robin    | yw       |
|      1 | robin    | kf       |
|      1 | robin    | hr       |
|      1 | robin    | boss     |
|      2 | zorro    | yw       |
|      2 | zorro    | kf       |
|      2 | zorro    | hr       |
|      2 | zorro    | boss     |
|      3 | tom      | yw       |
|      3 | tom      | kf       |
|      3 | tom      | hr       |
|      3 | tom      | boss     |
|      4 | jerry    | yw       |
|      4 | jerry    | kf       |
|      4 | jerry    | hr       |
|      4 | jerry    | boss     |
|      5 | jack     | yw       |
|      5 | jack     | kf       |
|      5 | jack     | hr       |
|      5 | jack     | boss     |
|      6 | rose     | yw       |
|      6 | rose     | kf       |
|      6 | rose     | hr       |
|      6 | rose     | boss     |
|      7 | king     | yw       |
|      7 | king     | kf       |
|      7 | king     | hr       |
|      7 | king     | boss     |
|      8 | jean     | yw       |
|      8 | jean     | kf       |
|      8 | jean     | hr       |
|      8 | jean     | boss     |
|      9 | natasha  | yw       |
|      9 | natasha  | kf       |
|      9 | natasha  | hr       |
|      9 | natasha  | boss     |
|     10 | xinghh   | yw       |
|     10 | xinghh   | kf       |
|     10 | xinghh   | hr       |
|     10 | xinghh   | boss     |
+--------+----------+----------+
40 rows in set (0.00 sec)

=================内连接======================= 等值连接

mysql>  select emp.emp_id,emp.emp_name,dpt.dpt_name from emp,dpt where emp.dpt_id =dpt.dpt_id;
+--------+----------+----------+
| emp_id | emp_name | dpt_name |
+--------+----------+----------+
|      1 | robin    | kf       |
|      2 | zorro    | kf       |
|      3 | tom      | kf       |
|      4 | jerry    | yw       |
|      5 | jack     | yw       |
|      6 | rose     | yw       |
|      7 | king     | hr       |
|      8 | jean     | hr       |
|      9 | natasha  | hr       |
|     10 | xinghh   | hr       |
+--------+----------+----------+
10 rows in set (0.00 sec)

不等值连接

=================（左外连接 left join)=======================

mysql> select emp.emp_id,emp.emp_name,dpt.dpt_name from emp left join dpt on emp.dpt_id = dpt.dpt_id ;
+--------+----------+----------+
| emp_id | emp_name | dpt_name |
+--------+----------+----------+
|      4 | jerry    | yw       |
|      5 | jack     | yw       |
|      6 | rose     | yw       |
|      1 | robin    | kf       |
|      2 | zorro    | kf       |
|      3 | tom      | kf       |
|      7 | king     | hr       |
|      8 | jean     | hr       |
|      9 | natasha  | hr       |
|     10 | xinghh   | hr       |
+--------+----------+----------+
10 rows in set (0.00 sec)

=================（右外连接 right join)=======================

mysql> select emp.emp_id,emp.emp_name,dpt.dpt_name from emp left join dpt on emp.dpt_id = dpt.dpt_id order by emp_id;
+--------+----------+----------+
| emp_id | emp_name | dpt_name |
+--------+----------+----------+
|      1 | robin    | kf       |
|      2 | zorro    | kf       |
|      3 | tom      | kf       |
|      4 | jerry    | yw       |
|      5 | jack     | yw       |
|      6 | rose     | yw       |
|      7 | king     | hr       |
|      8 | jean     | hr       |
|      9 | natasha  | hr       |
|     10 | xinghh   | hr       |
+--------+----------+----------+
10 rows in set (0.00 sec)

子查询

mysql>  select emp.emp_id,emp.emp_name,emp.emp_age,dpt.dpt_name from emp,dpt where emp.dpt_id = dpt.dpt_id and emp.emp_age > 25;
+--------+----------+---------+----------+
| emp_id | emp_name | emp_age | dpt_name |
+--------+----------+---------+----------+
|      4 | jerry    |      31 | yw       |
|      5 | jack     |      32 | yw       |
|      6 | rose     |      33 | yw       |
|      1 | robin    |      30 | kf       |
|      7 | king     |      40 | hr       |
|      8 | jean     |      41 | hr       |
|      9 | natasha  |      42 | hr       |
|     10 | xinghh   |      43 | hr       |
+--------+----------+---------+----------+
8 rows in set (0.00 sec)

mysql> select emp.emp_id,emp.emp_name,emp.emp_age,dpt.dpt_name from emp,dpt where emp.dpt_id = dpt.dpt_id order by emp.emp_age;
+--------+----------+---------+----------+
| emp_id | emp_name | emp_age | dpt_name |
+--------+----------+---------+----------+
|      3 | tom      |      22 | kf       |
|      2 | zorro    |      25 | kf       |
|      1 | robin    |      30 | kf       |
|      4 | jerry    |      31 | yw       |
|      5 | jack     |      32 | yw       |
|      6 | rose     |      33 | yw       |
|      7 | king     |      40 | hr       |
|      8 | jean     |      41 | hr       |
|      9 | natasha  |      42 | hr       |
|     10 | xinghh   |      43 | hr       |
+--------+----------+---------+----------+
10 rows in set (0.00 sec)

king hr:

1. ==========================

mysql> select emp_name from emp  where emp_name='king';
+----------+
| emp_name |
+----------+
| king     |
+----------+
1 row in set (0.00 sec)

自连接

求各部门最高的薪水

mysql> select max(emp_money) from emp group by dpt_id;
+----------------+
| max(emp_money) |
+----------------+
|          60000 |
|          30000 |
|         100000 |
+----------------+
3 rows in set (0.00 sec)

mysql> select * from emp;
+--------+----------+---------+--------+-----------+
| emp_id | emp_name | emp_age | dpt_id | emp_money |
+--------+----------+---------+--------+-----------+
|      1 | robin    |      30 |    200 |     10000 |
|      2 | zorro    |      25 |    200 |     20000 |
|      3 | tom      |      22 |    200 |     30000 |
|      4 | jerry    |      31 |    100 |     40000 |
|      5 | jack     |      32 |    100 |     50000 |
|      6 | rose     |      33 |    100 |     60000 |
|      7 | king     |      40 |    300 |     70000 |
|      8 | jean     |      41 |    300 |     80000 |
|      9 | natasha  |      42 |    300 |     90000 |
|     10 | xinghh   |      43 |    300 |    100000 |
+--------+----------+---------+--------+-----------+
10 rows in set (0.00 sec)

mysql> select emp_name,emp_money from emp,(select max(emp_money) as money from emp group by dpt_id) as new where emp_money=new.money;
+----------+-----------+
| emp_name | emp_money |
+----------+-----------+
| tom      |     30000 |
| rose     |     60000 |
| xinghh   |    100000 |
+----------+-----------+
3 rows in set (0.01 sec)

mysql> create table new as(select emp_id as id,emp_name as name from emp);
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| dpt            |
| emp            |
| new            |
| test           |
| test1          |
| test2          |
| test3          |
| test4          |
| test5          |
| test6          |
+----------------+
10 rows in set (0.00 sec)



mysql> desc new;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc emp;
+-----------+------------+------+-----+---------+-------+
| Field     | Type       | Null | Key | Default | Extra |
+-----------+------------+------+-----+---------+-------+
| emp_id    | int(11)    | YES  |     | NULL    |       |
| emp_name  | char(10)   | YES  |     | NULL    |       |
| emp_age   | tinyint(4) | YES  |     | NULL    |       |
| dpt_id    | int(11)    | YES  |     | NULL    |       |
| emp_money | int(11)    | YES  |     | NULL    |       |
+-----------+------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> select * from new;
+------+---------+
| id   | name    |
+------+---------+
|    1 | robin   |
|    2 | zorro   |
|    3 | tom     |
|    4 | jerry   |
|    5 | jack    |
|    6 | rose    |
|    7 | king    |
|    8 | jean    |
|    9 | natasha |
|   10 | xinghh  |
+------+---------+
10 rows in set (0.00 sec)

船舰一个结构一样但是没有数据的表

mysql> create table new2 as (select * from emp where 1=2);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| dpt            |
| emp            |
| new            |
| new2           |
| test           |
| test1          |
| test2          |
| test3          |
| test4          |
| test5          |
| test6          |
+----------------+
11 rows in set (0.00 sec)


mysql> desc new2
    -> ;
+-----------+------------+------+-----+---------+-------+
| Field     | Type       | Null | Key | Default | Extra |
+-----------+------------+------+-----+---------+-------+
| emp_id    | int(11)    | YES  |     | NULL    |       |
| emp_name  | char(10)   | YES  |     | NULL    |       |
| emp_age   | tinyint(4) | YES  |     | NULL    |       |
| dpt_id    | int(11)    | YES  |     | NULL    |       |
| emp_money | int(11)    | YES  |     | NULL    |       |
+-----------+------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> select * from new2
    -> ;
Empty set (0.00 sec)