SQL之连表查询

1、内连接

　　语法：

SELECT 查询字段1,查询字段2, ... FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段=表2.关系字段

　　准备数据

-- 若存在数据库mydb则删除DROP DATABASE IF EXISTS mydb;-- 创建数据库mydbCREATE DATABASE mydb;-- 选择数据库mydbUSE mydb;-- 创建部门表CREATE TABLE department(  did int (4) NOT NULL PRIMARY KEY,   dname varchar(20));-- 创建员工表CREATE TABLE employee (  eid int (4) NOT NULL PRIMARY KEY,   ename varchar (20),   eage int (2),   departmentid int (4) NOT NULL);-- 向部门表插入数据INSERT INTO department VALUES(1001,'财务部');INSERT INTO department VALUES(1002,'技术部');INSERT INTO department VALUES(1003,'行政部');INSERT INTO department VALUES(1004,'生活部');-- 向员工表插入数据INSERT INTO employee VALUES(1,'张三',19,1003);INSERT INTO employee VALUES(2,'李四',18,1002);INSERT INTO employee VALUES(3,'王五',20,1001);INSERT INTO employee VALUES(4,'赵六',20,1004);

查询：

select employee.ename,department.dname from department inner join employee on department.did=employee.departmentid;

output:

2、外连接查询-左连接

　　语法：

SELECT 查询字段1,查询字段2, ... FROM 表1 LEFT | RIGHT [OUTER] JOIN 表2 ON 表1.关系字段=表2.关系字段 WHERE 条件

2.1、LEFT [OUTER] JOIN 左(外)连接：返回包括左表中的所有记录和右表中符合连接条件的记录。
2.2、RIGHT [OUTER] JOIN 右(外)连接：返回包括右表中的所有记录和左表中符合连接条件的记录。

准备数据：

-- 若存在数据库mydb则删除DROP DATABASE IF EXISTS mydb;-- 创建数据库mydbCREATE DATABASE mydb;-- 选择数据库mydbUSE mydb;-- 创建班级表CREATE TABLE class(  cid int (4) NOT NULL PRIMARY KEY,   cname varchar(20));-- 创建学生表CREATE TABLE student (  sid int (4) NOT NULL PRIMARY KEY,   sname varchar (20),   sage int (2),   classid int (4) NOT NULL);-- 向班级表插入数据INSERT INTO class VALUES(1001,'Java');INSERT INTO class VALUES(1002,'C++');INSERT INTO class VALUES(1003,'Python');INSERT INTO class VALUES(1004,'PHP');-- 向学生表插入数据INSERT INTO student VALUES(1,'张三',20,1001);INSERT INTO student VALUES(2,'李四',21,1002);INSERT INTO student VALUES(3,'王五',24,1002);INSERT INTO student VALUES(4,'赵六',23,1003);INSERT INTO student VALUES(5,'Jack',22,1009);

查询：

select class.cid,class.cname,student.sname from class left outer join student on class.cid=student.classid;

output:

3、外连接查询-右连接

查询：

select class.cid,class.cname,student.sname from class right outer join student on class.cid=student.classid;

output:

只有永不遏止的奋斗，才能使青春之花，即便是凋谢，也是壮丽地凋谢