mysql--基本语句4-多表查询2

create table  tableA (
    id int primary key ,
    name varchar(20)

) ;

create table tableB(
    id int primary key ,
    name varchar(20) ,
    tableA_id int
) ;

insert into tableA values (1,'Alex'),
                          (2,'Jack'),
                          (3,'Amy') ;

insert into tableB values (1,'小雨',1),
                          (2,'彬彬',2),
                          (3,'洲洲',4) ;

# tableA
select * from tableA ;
# tableB
select * from tableB ;

# all
select * from tableA, tableB ;

select * from tableA, tableB where tableB.tableA_id=tableA.id ;

--
--

create table employee(
    id int primary key auto_increment comment '主键id',
    emp_name varchar(20) comment '名字',
    age int comment '年龄',
    dept_id int comment '部门ID'
) ;

insert into employee (emp_name, age, dept_id) values ('a', 19, 200),
                                                     ('b', 26, 201),
                                                     ('c', 30, 201),
                                                     ('d', 24, 200),
                                                     ('e', 20, 200),
                                                     ('f', 28, 204) ;

create table department(
    dept_id int comment '部门ID',
    dept_name varchar(20) comment '部门名字'
) ;

insert into department values (200, '人事部'),
                              (201, '技术部'),
                              (202, '销售部'),
                              (203, '财政部') ;

select * from employee ;
select * from department ;

select * from employee ,department ;

select employee.id, employee.emp_name, department.dept_name from department,employee where employee.dept_id=department.dept_id and employee.emp_name='a';

# 内连接查询 select * from 表1 inner join 表2 on [条件]
select employee.id ,employee.emp_name, department.dept_name from department inner join employee on employee.dept_id=department.dept_id and emp_name='a' ;

# 外连接查询
select employee.id, employee.emp_name, department.dept_name from employee left join department on employee.dept_id=department.dept_id ;
select employee.id, employee.emp_name, department.dept_name from department right join employee on employee.dept_id=department.dept_id ;
select employee.id, employee.emp_name, department.dept_name from department left join employee on employee.dept_id=department.dept_id ;
select employee.id ,employee.emp_name, department.dept_name from employee right join department on employee.dept_id=department.dept_id ;

select employee.emp_name from employee ,department where employee.dept_id = department.dept_id and department.dept_name = '技术部' ;

# 子查询 select * from + 表名 in (子语句)