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 視圖名稱;
總結
以上是生活随笔為你收集整理的mysql join on 索引_连接查询,表关联查询join on,索引,触发器,视图的全部內容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: mysql error 1442_MyS
- 下一篇: mysql slave 能写吗_MySQ