MySQL第六篇:索引與子查詢
我把MySQL的內容整理成9篇部落格,學完這9篇部落格雖不能說能成為大神,但是應付一般中小企業的開發已經足夠了,有疑問或建議的歡迎留言討論。
子查詢
子查詢,從原有的查詢語句中 嵌入新的查詢 來得到我們想要的結果,也可稱為巢狀查詢。
一、where 型
1、查詢課程名為“Java”的學生資訊
-- 使用關聯查詢實現
SELECT
s.*
FROM
students s,
class c
WHERE
s.class_id = c.class_id
AND c.class_name = `JAVA`
-- 使用子查詢實現
SELECT
*
FROM
STUDENTS
WHERE
CLASS_ID = (
SELECT
CLASS_ID
FROM
CLASS
WHERE
CLASS_NAME = `JAVA`
)
2、用子查詢實現查詢出PHP 和 Java的學生
select * from students
where class_id IN(
SELECT
class_id
FROM
class
WHERE
class_name = `JAVA`
OR class_name = `PHP`
)
注意:
1、IN 的元素不能超過1000個。
2、in的效率會較低。有時候會用exists代替 in。
3、所有的關聯查詢都可以轉換為子查詢。但是並不是所有的子查詢都能轉化成關聯查詢。
二、from型
把內層的查詢結果集作為臨時表 供外層sql再次查詢。
SELECT
s.*, c.class_name
FROM
(
SELECT
*
FROM
students
WHERE
height > 170
) s,
class c
WHERE
c.class_id = s.class_id
SELECT
s.*,(select class_name from class c where c.class_id = s.class_id) 課程名稱
FROM
(
SELECT
*
FROM
students
WHERE
height > 170
) s
三、子查詢與inert update delete的結合使用
例:
INSERT INTO students (
s_id,
s_name,
age,
height,
sex,
class_id
)
VALUES
(
`J1609004`,
`餘志堅`,
26,
175,
1,
(
SELECT
class_id
FROM
class
WHERE
class_name = `JAVA`
)
)
將學號為J1609004 的class_id更新成PHP的class_id
UPDATE students
SET class_id = (
SELECT
class_id
FROM
class
WHERE
class_name = `PHP`
)
WHERE
s_id = `J1609004`
索引
資料庫索引:資料庫管理系統中的一個排序的資料結構,以協助快速查詢,更新資料庫表中的資料。簡單來說,建立了索引後會在資料庫裡面單獨一塊區域建立一個小空間,把資料排好序,這樣查詢起來就快得多。
一、為表新增索引的代價:
- a.資料庫的儲存空間的佔用
- b.插入或修改資料的時候要花費較多的時間 – 重新建立索引
二、使用索引的三大原則
- a.單表資料太少,索引反而會影響速度,資料較少的表不需要建立索引
- b.資料較多的情況下, where 後的條件、order by 、group by等過濾時,後面的欄位最好加上索引,根據實際情況,選擇primary key,unique index,索引不是越多越好。
- c.聯合查詢,子查詢等多表操作時對關聯欄位加索引。
三、索引型別
1、單欄位索引:最常用的索引,只有一個欄位,並且不保證欄位值的唯一性。
語法:create index 索引名 on 表名(列名)
create index index_name on students(s_name);
2、唯一索引:可以根據唯一索引確定唯一的一條資料,用於改善效能和保證資料的完整性。
語法:create unique index 索引名 on 表名(列名)
create unique index index_id on students(s_id);
3、組合索引
語法:create index 索引名 on 表名(列名1,列名2.);
create index index_idname on students(s_name,s_id);
SELECT * FROM STUDENTS WHERE S_ID=`3` and s_name=`小紅`
注意:
1、組合索引頁可以是唯一索引。
2、當查詢條件有組合查詢的第一個欄位(s_name)的時候就一定會用該索引去查詢,無論where後面的查詢條件的順是怎樣的,無論有沒有其他查詢條件。
4、隱含索引:資料庫建立物件的時候自動穿件。主鍵約束 和唯一約束會自動建立索引。
補充:
- 1、在上例中,如果不存在索引,查詢時將會掃描全表的s_name欄位。
- 2、在一張表中可以建立多個所以,但是所以不是越多越好。
- 3、唯一索引的意思是這個欄位的每個值都不一樣,可以根據索引值唯一確定一條資料,而其他索引不能根據索引值確定唯一的資料。
四、修改與刪除索引
修改語法:alter index 索引名 (生產系統不建議使用索引的修改,資料庫執行時無法進行查詢操作)
刪除語法:drop index 索引名 on 表名
DROP INDEX index_name ON talbe_name;
ALTER TABLE table_name DROP INDEX index_name;
五、建立索引指導原則
1.越小的資料型別越好。
2.簡單的資料型別越好,給日期做索引的 使用datetime 。
3.儘量避免使用NULL,指定列為 not null 有空值的列很難進行查詢優化。可以使用0或者一個特殊的值或者空字串來代替。
六、索引的優缺點
- 優點:加快查詢速度
- 缺點:降低增 改 刪的速度,增大了表的檔案大小
相關文章
- MySQL索引與查詢優化MySql索引優化
- MySQL子查詢MySql
- MYsql 子查詢MySql
- 優化-mysql子查詢索引失效問題解決優化MySql索引
- mysql的子查詢MySql
- mysql查詢索引結構MySql索引
- MySQL聯結查詢和子查詢MySql
- MySQL之連線查詢和子查詢MySql
- 【MySQL】檢視&子查詢MySql
- MySQL 相關子查詢MySql
- [MYSQL -14]使用子查詢MySql
- 【MySQL】子查詢之一MySql
- MongoDB慢查詢與索引MongoDB索引
- 反向索引與模糊查詢索引
- mysql-分組查詢-子查詢-連線查詢-組合查詢MySql
- MySQL 覆蓋索引、回表查詢MySql索引
- 子查詢-表子查詢
- 理解索引(中):MySQL查詢過程和高階查詢索引MySql
- sql語法相關子查詢與非相關子查詢SQL
- MySQL全面瓦解11:子查詢和組合查詢MySql
- MySQL資料庫基礎——多表查詢:子查詢MySql資料庫
- MYSQL學習筆記26: 多表查詢|子查詢MySql筆記
- 還傻傻分不清MySQL回表查詢與索引覆蓋?MySql索引
- MySQL-10.索引最佳化與查詢最佳化MySql索引
- MYSQL學習筆記25: 多表查詢(子查詢)[標量子查詢,列子查詢]MySql筆記
- 【索引】Oracle查詢指定索引提高查詢效率索引Oracle
- MySQL子查詢的優化薦MySql優化
- MySQL 索引及查詢優化總結MySql索引優化
- MySQL 學習之索引篇和查詢MySql索引
- MySQL索引原理及慢查詢優化MySql索引優化
- MySQL索引的最左字首原理與查詢的相關優化MySql索引優化
- exists與in子查詢優化優化
- MySQL索引原理及慢查詢最佳化MySql索引
- MySQL 庫大小、表大小、索引大小查詢命令MySql索引
- 關於mysql 子查詢中 使用 limitMySqlMIT
- Elasticsearch 第六篇:聚合統計查詢Elasticsearch
- 子查詢與join效能差異
- [not] in/exists 與 帶TOP的子查詢