多表联合查询实践
创建表:
MySQL [company]> create table employee6( emp_id int auto_increment primary key not null, emp_name varchar(50), age int, dept_id int); Query OK, 0 rows affected (0.65 sec)
查看表结构:
MySQL [company]> desc employee6; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | emp_id | int(11) | NO | PRI | NULL | auto_increment | | emp_name | varchar(50) | YES | | NULL | | | age | int(11) | YES | | NULL | | | dept_id | int(11) | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
插入模拟数据:
MySQL [company]> insert into employee6(emp_name,age,dept_id) values ('',19,200), ('tom',26,201), ('jack',30,201), ('alice',24,202), ('robin',40,200), ('xingdian',16,200), ('natasha',28,204);
查看数据:
MySQL [company]> select * from employee6; +--------+----------+------+---------+ | emp_id | emp_name | age | dept_id | +--------+----------+------+---------+ | 1 | | 19 | 200 | | 2 | tom | 26 | 201 | | 3 | jack | 30 | 201 | | 4 | alice | 24 | 202 | | 5 | robin | 40 | 200 | | 6 | xingdian | 16 | 200 | | 7 | natasha | 28 | 204 | +--------+----------+------+---------+ 7 rows in set (0.00 sec)
表:company.department6
创建表:
MySQL [company]> create table department6( dept_id int, dept_name varchar(100) ); Query OK, 0 rows affected (0.33 sec)
查看表结构:
MySQL [company]> desc department6; +-----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------------+------+-----+---------+-------+ | dept_id | int(11) | YES | | NULL | | | dept_name | varchar(100) | YES | | NULL | | +-----------+--------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
模拟插入数据:
MySQL [company]> insert into department6 values (200,'hr'), (201,'it'), (202,'sale'), (203,'fd');
查看数据:
MySQL [com