TwoDay_MySQL 多表连接查询

本文主要是介绍TwoDay_MySQL 多表连接查询，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

一、去重

　　eg：查找有哪些工作岗位？

　　select distinct job from emp;//去掉重复数据

　　select ename, distinct job from emp;//这个是错误的，distinct 必须出现在所有字段前面

　　select distinct deptno,job from emp;//表示deptno与job联合去重

　　eg：查找岗位的数量？

select count(distinct job) from emp;

二、连接查询

在实际开发中，都是多张表查询。

2.1 链接查询的分类

• 根据语法出现的年代来划分的：

SQL92 （数据库管理员，DBA有些还在使用）

SQL99 （新的语法）

• 根据表的链接方式来划分：

内连接：

等值连接

非等值连接

自链接

外连接：

左外链接

右外连接

全连接（很少用）

2.2、在表的链接查询有一种现象被称为：笛卡尔积现象

dept表中有四条数据，emp表中有14条数据，笛卡尔积后（emp中的每一条数据都与dept表中的四条数据进行连接）14乘以4 共56条数据

select ename,dname from emp,dept;

上面的语句效率太低，为解决效率问题，建议给表起别名：

select e.ename,d.dname from emp e,dname d;

注意：避免了笛卡尔积现象，不会减少匹配次数，还是匹配56次，只不过会显示有效数据

eg：找出每一个员工的部门名称，要求显示员工名和部门名

select e.ename,d.dname

from emp e,dept d

where e.deptno=d.deptno; (SQL92，以后不用)

2.3 内连接之等值连接

特点：条件是等量关系

eg：找出每一个员工的部门名称，要求显示员工名和部门名（SQL99）

select e.ename,d.dname

from emp e (inter) join dept d

on e.deptno=d.deptno;

inter可以省略

2.4内连接之非等值连接

特点：连接条件的关系是非等量关系

eg：查询员工的姓名，工资及工资等级？

select e.ename,e.sal,s.grade

from emp e join salgrade s

on e.sal between s.losal and s.hisal;

2.5自连接

一张表看作两张表，进行连接

eg：查询每个员工的领导姓名，显示员工姓名与之对应的领导名

select e.empno,e.ename,e1.ename from emp e join emp e1 on e.mgr=e1.empno;

（这个语句的执行结果 会少了没有上级领导的员工）

2.6外连接

内连接与外连接的区别？

内连接：A表与B表是平等关系，没有主副之分

外连接：一张表是主表，一张表是副表，查询主表里的数据，副表负责匹配主表所需的信息，如果在副表中找不到，则匹配null。

eg：查询每个员工的领导姓名，显示员工姓名与之对应的领导名（每个员工都需要查出来）

使用左外连接：

select e.empno,e.ename,e1.ename from emp e left (outer) join emp e1 on e.mgr=e1.empno;

使用右外连接：

select e.empno,e.ename,e1.ename from emp e1 right (outer) join emp e on e.mgr=e1.empno;

（这个语句的执行结果，所有员工都会显示，没有上级领导的员工，领导的字段为null）

outer 可以省略

三、三表查询

eg: 找出每个员工的部门名称和工资等级

select e.ename,d.dname,s.grade

from emp e join dept d on e.deptno=d.deptno

join salgrade s on e.sal between s.losal and s.hisal;

语法格式：

select xxx

from A

join B

on xxx

join C

on xxx

where xxxx;

eg:找出每个员工的部门名称和工资等级及领导名

select e.ename,d.dname,s.grade,e1.ename as '领导'

from emp e join dept d on e.deptno=d.deptno

join salgrade s on e.sal between s.losal and s.hisal

left join emp e1 on e.mgr=e1.empno;

四、子查询

select语句中嵌套select语句

4.1 在where 子句中嵌套子查询

eg: 查找高于平均工资的员工信息？

select * from emp where sal>(select avg(sal) from emp);

4.2在from子句中嵌套查询

eg:找出每个部门平均工资的工资等级？

第一步：select avg(sal) as avg ,deptno from emp group by deptno；//这是每个部门的平均薪水，这是一张新表。用这个给新表和salgrade表进行连接。

select s.grade,e.deptno ,e.avg

from (select avg(sal) as avg ,deptno from emp group by deptno) e

join salgrade s on e.avg between s.losal and s.hisal;

eg:找出每个部门平均的薪水等级（先找出每个员工的薪水等级，再按照部门求平均等级）

第一步： select s.grade ,e.deptno as dno from emp e join salgrade s on e.sal between s.losal and s.hisal //这句是每名员工的薪资等级

第二步：每个部门的平均等级

select e.deptno as dno ,avg(s.grade) from emp e join salgrade s on e.sal between s.losal and s.hisal group by e.deptno;

4.3在select后面嵌套子查询

eg：查询每个员工所在的部门名称（使用嵌套子查询）

select e.ename,(select dname from dept d where d.deptno=e.deptno) as dname from emp e;

五、union（可以将查询结果集相加）

eg：找出工作岗位是manager或是salesman的员工？

第一种：select ename,job from emp where job='manager' or job='salesman';

第二种：select ename ,job from emp where job in('manager','salesman');

第三种：

select ename,job from emp where job='manager'

union

select ename,job from emp where job='salesman';

六、limit

limit是mysql特有的,Oracle中有一个相同的机制叫rownum

6.1 语法机制：

limit startIndex,length

startIndex 表示起始位置 从0开始

length表示取几个

eg: 取出工资前5名的员工sele

select ename,sal from emp order by sal desc limit 0,5;

select ename,sal from emp order by sal desc limit 5;//默认startIndex=0

注意：limit 是sql语句中最后执行的

6.2 通用的标准分页sql

假如每页显示3条记录

第一页：limit 0，3

第二页：limit 3，3

第三页：limit 6，3

......

总结规律：每页显示n条数据，则第m页 limit（m-1）*n ,n

七、创建表

7.1 语法格式：

create table 表名(

字段名1 数据类型，

字段名2 数据类型，

.........

)

7.2常见的数据类型

int 整数型

bigint 长整型

float 浮点型

char 定长字符串

varchar 可变长字符串

date 日期类型

blob 二进制大对象 (存储图片，视频等流媒体信息)Binary Large Object

clob 字符大对象（存储较大文本，比如4G的字符串）

char与varchar怎么选择？

当某个数据长度不发生改变的时候，是定长的如：性别，生日等都可用char

当一个字段的数据长度不确定，如简介、姓名等可以用varchar

表名最好以t，tal开头，见名知意

7.3建表

学生信息：学号 长整型，姓名 可变长字符串 ，性别 定长字符串，班级 可变长字符串，生日 定长字符串

create table t_student(

sno bigint,

sname varchar(6),

sex char(1),

classno varchar(255),

brith char(10)

);

八、插入数据

inster into 表名(字段名1，字段名2......) values (值1，值2......);

8.1 对t_student插入数据

注意：字段名全部省略，默认values添加所有

insert into t_student values(1801,"jack","1","1.1","2000-12-12");

删表

drop table if exists t_student;

重建

create table t_student(

sno bigint default 1,//给学号默认值为1，那么在显示表结构的时候可以看到default是1，否则为null

sname varchar(6),

sex char(1),

classno varchar(255),

brith char(10)

);

8.2 一次插入多个值

insert into t_student values(1801,"jack","1","1.1","2000-12-12"),

(1802,"mark","1","1.1","2001-12-12");

九、表的复制

语法：create table 表名 as select 语句；

将查询结果当作表创建出来

9.1将查询结果插入到一张表中

语法：insert into 表名 select 语句；//对于这样的语句有插入要求。

十、修改数据

update

语法格式：update 表名 set 字段名1=值1，字段名2=值2......where 条件;

注意没有条件，整张表全部更新

eg：将部门10的Loc修改为shnaghai,将部门名修改为renshibu

update dept set loc="shanghai",dname="reshibu" where deptno=10;

十一、删除数据

语法：delete from 表名 where 条件；

注意：没有条件全部删除

删除部门10的员工信息？

delete from emp1 where deptno=10;

删除所有数据

delete from emp1;

怎么删除大表？

truncate table empl1;//表被截断，不可回滚，永久丢失

注意：增删改查有一个术语：CRUD

Create Retrieve(检索) Update Delete

十二、创建表约束

（Constraint）约束

12.1什么是约束？

在创建表的时候，可以给某些字段添加约束，添加约束的作用是为了保证表中的数据合法，有效，完整。

12.2常见的约束

非空约束（not null）

唯一约束 (unique)

主键约束(primary key)

外键约束(foreign key)

检查约束(check)

12.3 not null

drop table if exists t_user;

create table t_user(

id bigint,

username varchar(255) not null,

password varchar(255)

);

Insert into t_user(id,password) values (1,'123');

报错：ERROR 1364 (HY000): Field 'username' doesn't have a default value

这篇关于TwoDay_MySQL 多表连接查询的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！