mysql join on 索引_连接查询,表关联查询join on，索引,触发器,视图

一、连接查询

1、统计每一个部门的人数  “部门名，部门的人数”

select department,count(eid) from employee group by

department;

2、统计每个中姓“李”的人数  “部门名，部门中姓李的人数”

select department,count(eid) from employee where name like

'李%' group by

department;

PS：where 和group by同时出现在一条sql语句中，要先写where 子句 在写group by子句

3、编写一条sql，同时将 “部门名，每个部门的人数、每个部门中姓李的人数”：

Select

department,

(select count(eid) from employee ep

where ep. department  = emp .department) count_all

,

(select count(ep1.eid) from employee ep1 where

ep1.name like ‘李%’ and ep1.department =emp.department

)

count_li  from employee

emp

group by emp.department

分解：

select count(eid) from employee ep where

ep.name like ‘李%’ and ep.department = ‘MMM’;

select count(eid) from employee ep where

ep. department = ‘MMM’;

分析后发现，如果查询的结果有n条结果，相当于执行了2*n+1次查询，查询效率较低。

表关联查询：

Select name,coure from

employee,training

但是这样写会出现笛卡尔积，两张张表中的数据分别为n、m

,安装该sql查询会出现n*m条，

其中产生了大量的错误数据，接下来我们思考，如何过滤掉没有用的数据？

4、每个人学过的课程

Select name,course from employee

emp,training tra where emp.eid = tra.eid

5、使用表关联查询，来优化案例3

Select

emp.department,

Emp.c_all

'部门总人数',

Ep.c_li '部门中姓李的人数'

From (select department,count(eid)

c_all from employee group by department)

emp,

(select department,count(eid)

c_li from employee where name like '李%' group by

department)  ep

Where emp.department =

ep.department

但是运行发现只有两条数据，部门没有姓李的就没有显示出来(本来共四个部门)，

这是因为有两个部门下没有姓李的，ep对应的计算数量的地方 null,所以就不进行显示。

join  on

修改案例4：查询每个学过的课程

select name,course from employee emp

join training tra on emp.eid =

tra.eid

left join  on

和 right join  on

tb1(表名) left join  tb2

on:

tb1(表名) right join  tb2

on:

6、优化案例，将四个部门都显示出来：

Select

emp.department,

Emp.c_all

'部门总人数',

Ep.c_li '部门中姓李的人数'

From (select department,count(eid)

c_all from employee group by department) emp left

join

(select department,count(eid)

c_li from employee where name like '李%' group by

department)  ep on emp.department =

ep.department

查询结果为：

+------------+------------+------------------+

| department | 部门总人数 | 部门中姓李的人数 |

+------------+------------+------------------+

| LUKE  |  2 |

2 |

| MMM  |  1 |

NULL

|

| MTD  |  2 |

NULL

|

| SBB  |  4 |

3 |

+------------+------------+------------------+

人数显示了两个NULL  ,为了使用方便 ，希望该地方显示“0”

，MySQL给大家提供了一个替换NULL的函数 ：IFNULL(c_li,0),修改上句sql：

Select

emp.department,

Emp.c_all

'部门总人数',

IFNULL(Ep.c_li,0)

'部门中姓李的人数'

From (select department,count(eid)

c_all from employee group by department) emp left

join

(select department,count(eid)

c_li from employee where name like '李%' group by

department)  ep on emp.department =

ep.department;

排序 order by 字段 (默认升序 asc )  desc 降序

select * from employee order by eid desc;

MySql分页关键字：limit m,n

:表示从m+1开始，显示<=n行

Select * from employee limit m,n

Oracle分页关键字：rownum  n>=m

思路:先查询出rownum<=n行以前的所有数据，

再得到的数据基础上查询rownum>=m

SQLServer分页关键字：top

10001 | 李明  | SBB  | EG

| 0  |

NULL

10003 | 李平  | LUKE  | ITM  | 0

| NULL

10009 | 徐仲刚 | SBB  | EG  | 0

| NULL

10023 | 李燕  | SBB  | ETN

| 0  | NULL

10044 | 胡斐  | MTD  | ETN

| 0  | NULL

11045 | 李洁  | SBB  | EG

| 0  | NULL

20001 | 李立  | LUKE  | ETN  | 0

| NULL

20078 | 张青  | MMM  | EG

| 0  | NULL

20460 | 陆明生 | MTD  | ETN  | 0

| NULL

练习题：

1、 列出所有员工参加培训的信息，要求显示

EID，Name、Department、Course，用一条SQL语句。

Select emp.eid,emp.name,emp.department ,tra.course

from employee emp

join training tra on emp.eid = tra.eid;

2、 筛选出未参加培训的人员名单，按照表employee的格式显示，用一条sql语句显示

Select ep.*

From employee ep

Where ep.eid not in (

Select distinct emp.eid

From employee emp join training tra

on

Emp.eid = tra.eid

);

Distinct  可以过滤掉重复的内容

3、 列出所有各课程的最高成绩的员工信息，要求显示 EID,Name,Department,Course,Grade

，用一条SQL语句。

Select

tmp.eid,emp.name,emp.department,tmp.course,tmp.grade

From  (Select

course,max(grade) grade,eid  from training group

by course) tmp join employee emp on tmp.eid = emp.eid

二、索引

(1) 索引(index)

索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

优点：

大大加快数据的检索速度;

创建唯一性索引，保证数据库表中每一行数据的唯一性;

加速表和表之间的连接;

在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

缺点：

索引需要占物理空间

当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

更多理解：

使用索引：加快对表执行SELECT语句的速度。

为一个表添加和删除索引之后，对该表的sql语句没有任何的影响。

索引的分类：

普通索引：这是一个最基本的索引没有任何的限制。

唯一索引：它是在普通索引的基础上，要求索引的列对应的值唯一(允许为空，但不允许重复)。

主键索引：通过主键约束间接创建。它还是一个特殊的唯一索引。

组合索引：在表中的多个列创建索引，多个列之间可以相邻，也可以不相邻。

索引的创建：

普通(唯一)索引

Create  [UNIQUE]  index

index_name(索引名称)  ON

表名(列名…)

Create unique index admin_info_index1 on

admin_info(admin_code);

组合索引的创建：

Create  [UNIQUE]  index

index_name(索引名称)  ON

表名(列名1，列名2…)

Create unique index admin_info_index2 on

admin_info(name,password);

查看索引：

Show index from 表名；

Show index from admin_info;

删除索引：

Drop index 索引名 on  表名；

三、触发器：

它是数据库的一个程序，用来监听数据库中某一张表的某一个行为(insert,update,delete)时，马上去执行一条SQL语句。

Create  trigger  触发器的名字

after|before

事件(insert、update 、delete)

On  表名(被监听的表)

For each row

SQL语句

当往admin_info表中添加一条数据时，往tb_log中添加一条信息

Create trigger

insert_admin_info_trigger1

After insert  on

admin_info

For each  row

Insert into tb_log(log,create_date)

values('向admin_info表中添加了一条数据',current_date);

查看触发器：

show triggers;  查看当前数据库下有什么触发器

show triggers from|in  dbName;

查看指定数据库下的触发器。

删除触发器：

Drop trigger 触发器的名字;

四、视图：

模板：

Create view

view_name

As (SQL)

Create view

find_no_teview

As (Select ep.*

From employee ep

Where ep.eid not in (

Select distinct emp.eid

From employee emp join training tra

on

Emp.eid = tra.eid

))

使用：

select * from

find_no_teview;

修改视图：

ALTER

VIEW view_name

AS (select_statement)

查看视图：

Show create view 视图的名称；

删除视图：

Drop view 视图名称；