MySql教程

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 多表连接查询的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!