查詢的定義與用法)
一. SELECT語句基礎(chǔ)定義SELECT語句用于從數(shù)據(jù)庫表中檢索數(shù)據(jù)。它是SQL中最基本和最常用的命令。語法解析SELECT column1, column2, ... -- 選擇要檢索的列FROM table_name -- 指定數(shù)據(jù)來源的表[WHERE condition] -- 可選的篩選條件[ORDER BY column_name [ASC|DESC]] -- 可選的排序[LIMIT number]; -- 可選的返回行數(shù)限制詳細(xì)解釋- SELECT指定要返回的列可以使用*表示所有列- FROM指定數(shù)據(jù)來源的一個(gè)或多個(gè)表- WHERE過濾條件只有滿足條件的行才會(huì)被返回- ORDER BY對(duì)結(jié)果集進(jìn)行排序- LIMIT限制返回的行數(shù)二. WHERE條件篩選定義WHERE子句用于過濾記錄只返回滿足指定條件的行。比較運(yùn)算符-- 定義精確匹配查詢SELECT * FROM employees WHERE salary 5000; -- 等于-- 定義范圍比較查詢SELECT * FROM employees WHERE salary 5000; -- 大于SELECT * FROM employees WHERE salary 5000; -- 小于SELECT * FROM employees WHERE salary 5000; -- 大于等于SELECT * FROM employees WHERE salary 5000; -- 小于等于SELECT * FROM employees WHERE salary 5000; -- 不等于-- 定義范圍區(qū)間查詢SELECT * FROM employees WHERE salary BETWEEN 3000 AND 6000; -- 在3000-6000之間邏輯運(yùn)算符定義-- 定義AND要求所有條件都滿足SELECT * FROM employeesWHERE salary 5000 AND department_id 1; -- 工資5000且部門1-- 定義OR要求至少一個(gè)條件滿足SELECT * FROM employeesWHERE salary 5000 OR department_id 1; -- 工資5000或部門1-- 定義NOT否定條件SELECT * FROM employeesWHERE NOT department_id 1; -- 部門不等于1-- 定義復(fù)雜邏輯組合SELECT * FROM employeesWHERE (salary 5000 OR commission_pct IS NOT NULL) -- 括號(hào)優(yōu)先執(zhí)行AND hire_date 2020-01-01;模糊查詢定義-- 定義LIKE用于模式匹配%匹配任意字符序列SELECT * FROM employees WHERE first_name LIKE J%; -- 以J開頭SELECT * FROM employees WHERE first_name LIKE %n; -- 以n結(jié)尾SELECT * FROM employees WHERE first_name LIKE %oh%; -- 包含oh-- 定義_匹配單個(gè)字符SELECT * FROM employees WHERE first_name LIKE J_; -- J開頭兩個(gè)字符SELECT * FROM employees WHERE first_name LIKE J__; -- J開頭三個(gè)字符NULL值處理定義-- 定義IS NULL檢查空值SELECT * FROM employees WHERE commission_pct IS NULL;-- 定義IS NOT NULL檢查非空值SELECT * FROM employees WHERE commission_pct IS NOT NULL;-- 定義COALESCE函數(shù)處理空值返回第一個(gè)非空值SELECTfirst_name,COALESCE(commission_pct, 0) AS commission -- 如果commission_pct為NULL則返回0FROM employees;三. JOIN連接查詢定義JOIN用于根據(jù)兩個(gè)或多個(gè)表之間的相關(guān)列合并行。用于從多個(gè)表中檢索數(shù)據(jù)。INNER JOIN定義-- 定義內(nèi)連接返回兩個(gè)表中匹配的行SELECTe.first_name,e.last_name,d.department_nameFROM employees e -- e是employees表的別名INNER JOIN departments d ON e.department_id d.department_id; -- 連接條件解釋只返回employees和departments表中department_id匹配的記錄。LEFT JOIN定義-- 定義左連接返回左表所有記錄和右表匹配的記錄SELECTe.first_name,d.department_nameFROM employees eLEFT JOIN departments d ON e.department_id d.department_id;解釋返回所有員工記錄即使某些員工沒有分配部門department_name為NULL。RIGHT JOIN定義- 定義右連接返回右表所有記錄和左表匹配的記錄SELECTe.first_name,d.department_nameFROM employees eRIGHT JOIN departments d ON e.department_id d.department_id;解釋返回所有部門記錄即使某些部門沒有員工first_name為NULL。自連接定義-- 定義表與自身連接用于查詢層次結(jié)構(gòu)數(shù)據(jù)SELECTe1.first_name AS 員工姓名,e2.first_name AS 經(jīng)理姓名FROM employees e1LEFT JOIN employees e2 ON e1.manager_id e2.employee_id;解釋通過manager_id字段將員工表與自身連接查找每個(gè)員工的經(jīng)理。四. 聚合函數(shù)與分組聚合函數(shù)定義聚合函數(shù)對(duì)一組值執(zhí)行計(jì)算并返回單個(gè)值。-- 定義COUNT計(jì)算行數(shù)SELECT COUNT(*) AS total_employees FROM employees; -- 總員工數(shù)SELECT COUNT(DISTINCT department_id) AS unique_departments FROM employees; -- 不重復(fù)部門數(shù)-- 定義SUM計(jì)算總和SELECT SUM(salary) AS total_salary FROM employees; -- 工資總額-- 定義AVG計(jì)算平均值SELECT AVG(salary) AS average_salary FROM employees; -- 平均工資-- 定義MAX/MIN找最大最小值SELECT MAX(salary) AS max_salary FROM employees; -- 最高工資SELECT MIN(salary) AS min_salary FROM employees; -- 最低工資GROUP BY定義-- 定義按部門分組統(tǒng)計(jì)SELECTdepartment_id,COUNT(*) AS employee_count, -- 每個(gè)部門的員工數(shù)AVG(salary) AS avg_salary -- 每個(gè)部門的平均工資FROM employeesGROUP BY department_id; -- 按department_id分組解釋將員工按部門分組然后對(duì)每個(gè)組計(jì)算統(tǒng)計(jì)值。HAVING定義-- 定義HAVING對(duì)分組后的結(jié)果進(jìn)行篩選SELECTdepartment_id,AVG(salary) AS avg_salaryFROM employeesGROUP BY department_idHAVING AVG(salary) 5000; -- 只顯示平均工資5000的部門解釋W(xué)HERE在分組前過濾行HAVING在分組后過濾組。五. 子查詢定義子查詢是嵌套在其他SQL查詢中的查詢。標(biāo)量子查詢定義-- 定義返回單個(gè)值的子查詢SELECT * FROM employeesWHERE salary (SELECT AVG(salary) FROM employees); -- 子查詢返回平均工資解釋子查詢(SELECT AVG(salary) FROM employees)返回一個(gè)值平均工資主查詢使用這個(gè)值進(jìn)行比較。列子查詢定義-- 定義返回一列值的子查詢通常與IN一起使用SELECT * FROM employeesWHERE department_id IN (SELECT department_id FROM departmentsWHERE location_id 1700 -- 子查詢返回特定位置的部門ID列表);解釋子查詢返回一個(gè)部門ID列表主查詢檢查員工的部門ID是否在這個(gè)列表中。EXISTS子查詢定義-- 定義檢查子查詢是否返回任何行SELECT * FROM departments dWHERE EXISTS (SELECT 1 FROM employees eWHERE e.department_id d.department_id -- 關(guān)聯(lián)子查詢);解釋對(duì)于每個(gè)部門檢查是否存在屬于該部門的員工。EXISTS返回布爾值。關(guān)鍵概念總結(jié)1. **SELECT**數(shù)據(jù)檢索的核心命令2. **WHERE**行級(jí)過濾條件3. **JOIN**多表數(shù)據(jù)關(guān)聯(lián)4. **GROUP BY**數(shù)據(jù)分組聚合5. **HAVING**分組結(jié)果過濾6. **子查詢**查詢嵌套解決復(fù)雜問題7. **索引**提高查詢性能的數(shù)據(jù)結(jié)構(gòu) MySQL:每個(gè)查詢的定義與用法
一. SELECT語句基礎(chǔ)定義SELECT語句用于從數(shù)據(jù)庫表中檢索數(shù)據(jù)。它是SQL中最基本和最常用的命令。語法解析SELECT column1, column2, ... -- 選擇要檢索的列FROM table_name -- 指定數(shù)據(jù)來源的表[WHERE condition] -- 可選的篩選條件[ORDER BY column_name [ASC|DESC]] -- 可選的排序[LIMIT number]; -- 可選的返回行數(shù)限制詳細(xì)解釋- SELECT指定要返回的列可以使用*表示所有列- FROM指定數(shù)據(jù)來源的一個(gè)或多個(gè)表- WHERE過濾條件只有滿足條件的行才會(huì)被返回- ORDER BY對(duì)結(jié)果集進(jìn)行排序- LIMIT限制返回的行數(shù)二. WHERE條件篩選定義WHERE子句用于過濾記錄只返回滿足指定條件的行。比較運(yùn)算符-- 定義精確匹配查詢SELECT * FROM employees WHERE salary 5000; -- 等于-- 定義范圍比較查詢SELECT * FROM employees WHERE salary 5000; -- 大于SELECT * FROM employees WHERE salary 5000; -- 小于SELECT * FROM employees WHERE salary 5000; -- 大于等于SELECT * FROM employees WHERE salary 5000; -- 小于等于SELECT * FROM employees WHERE salary 5000; -- 不等于-- 定義范圍區(qū)間查詢SELECT * FROM employees WHERE salary BETWEEN 3000 AND 6000; -- 在3000-6000之間邏輯運(yùn)算符定義-- 定義AND要求所有條件都滿足SELECT * FROM employeesWHERE salary 5000 AND department_id 1; -- 工資5000且部門1-- 定義OR要求至少一個(gè)條件滿足SELECT * FROM employeesWHERE salary 5000 OR department_id 1; -- 工資5000或部門1-- 定義NOT否定條件SELECT * FROM employeesWHERE NOT department_id 1; -- 部門不等于1-- 定義復(fù)雜邏輯組合SELECT * FROM employeesWHERE (salary 5000 OR commission_pct IS NOT NULL) -- 括號(hào)優(yōu)先執(zhí)行AND hire_date 2020-01-01;模糊查詢定義-- 定義LIKE用于模式匹配%匹配任意字符序列SELECT * FROM employees WHERE first_name LIKE J%; -- 以J開頭SELECT * FROM employees WHERE first_name LIKE %n; -- 以n結(jié)尾SELECT * FROM employees WHERE first_name LIKE %oh%; -- 包含oh-- 定義_匹配單個(gè)字符SELECT * FROM employees WHERE first_name LIKE J_; -- J開頭兩個(gè)字符SELECT * FROM employees WHERE first_name LIKE J__; -- J開頭三個(gè)字符NULL值處理定義-- 定義IS NULL檢查空值SELECT * FROM employees WHERE commission_pct IS NULL;-- 定義IS NOT NULL檢查非空值SELECT * FROM employees WHERE commission_pct IS NOT NULL;-- 定義COALESCE函數(shù)處理空值返回第一個(gè)非空值SELECTfirst_name,COALESCE(commission_pct, 0) AS commission -- 如果commission_pct為NULL則返回0FROM employees;三. JOIN連接查詢定義JOIN用于根據(jù)兩個(gè)或多個(gè)表之間的相關(guān)列合并行。用于從多個(gè)表中檢索數(shù)據(jù)。INNER JOIN定義-- 定義內(nèi)連接返回兩個(gè)表中匹配的行SELECTe.first_name,e.last_name,d.department_nameFROM employees e -- e是employees表的別名INNER JOIN departments d ON e.department_id d.department_id; -- 連接條件解釋只返回employees和departments表中department_id匹配的記錄。LEFT JOIN定義-- 定義左連接返回左表所有記錄和右表匹配的記錄SELECTe.first_name,d.department_nameFROM employees eLEFT JOIN departments d ON e.department_id d.department_id;解釋返回所有員工記錄即使某些員工沒有分配部門department_name為NULL。RIGHT JOIN定義- 定義右連接返回右表所有記錄和左表匹配的記錄SELECTe.first_name,d.department_nameFROM employees eRIGHT JOIN departments d ON e.department_id d.department_id;解釋返回所有部門記錄即使某些部門沒有員工first_name為NULL。自連接定義-- 定義表與自身連接用于查詢層次結(jié)構(gòu)數(shù)據(jù)SELECTe1.first_name AS 員工姓名,e2.first_name AS 經(jīng)理姓名FROM employees e1LEFT JOIN employees e2 ON e1.manager_id e2.employee_id;解釋通過manager_id字段將員工表與自身連接查找每個(gè)員工的經(jīng)理。四. 聚合函數(shù)與分組聚合函數(shù)定義聚合函數(shù)對(duì)一組值執(zhí)行計(jì)算并返回單個(gè)值。-- 定義COUNT計(jì)算行數(shù)SELECT COUNT(*) AS total_employees FROM employees; -- 總員工數(shù)SELECT COUNT(DISTINCT department_id) AS unique_departments FROM employees; -- 不重復(fù)部門數(shù)-- 定義SUM計(jì)算總和SELECT SUM(salary) AS total_salary FROM employees; -- 工資總額-- 定義AVG計(jì)算平均值SELECT AVG(salary) AS average_salary FROM employees; -- 平均工資-- 定義MAX/MIN找最大最小值SELECT MAX(salary) AS max_salary FROM employees; -- 最高工資SELECT MIN(salary) AS min_salary FROM employees; -- 最低工資GROUP BY定義-- 定義按部門分組統(tǒng)計(jì)SELECTdepartment_id,COUNT(*) AS employee_count, -- 每個(gè)部門的員工數(shù)AVG(salary) AS avg_salary -- 每個(gè)部門的平均工資FROM employeesGROUP BY department_id; -- 按department_id分組解釋將員工按部門分組然后對(duì)每個(gè)組計(jì)算統(tǒng)計(jì)值。HAVING定義-- 定義HAVING對(duì)分組后的結(jié)果進(jìn)行篩選SELECTdepartment_id,AVG(salary) AS avg_salaryFROM employeesGROUP BY department_idHAVING AVG(salary) 5000; -- 只顯示平均工資5000的部門解釋W(xué)HERE在分組前過濾行HAVING在分組后過濾組。五. 子查詢定義子查詢是嵌套在其他SQL查詢中的查詢。標(biāo)量子查詢定義-- 定義返回單個(gè)值的子查詢SELECT * FROM employeesWHERE salary (SELECT AVG(salary) FROM employees); -- 子查詢返回平均工資解釋子查詢(SELECT AVG(salary) FROM employees)返回一個(gè)值平均工資主查詢使用這個(gè)值進(jìn)行比較。列子查詢定義-- 定義返回一列值的子查詢通常與IN一起使用SELECT * FROM employeesWHERE department_id IN (SELECT department_id FROM departmentsWHERE location_id 1700 -- 子查詢返回特定位置的部門ID列表);解釋子查詢返回一個(gè)部門ID列表主查詢檢查員工的部門ID是否在這個(gè)列表中。EXISTS子查詢定義-- 定義檢查子查詢是否返回任何行SELECT * FROM departments dWHERE EXISTS (SELECT 1 FROM employees eWHERE e.department_id d.department_id -- 關(guān)聯(lián)子查詢);解釋對(duì)于每個(gè)部門檢查是否存在屬于該部門的員工。EXISTS返回布爾值。關(guān)鍵概念總結(jié)1. **SELECT**數(shù)據(jù)檢索的核心命令2. **WHERE**行級(jí)過濾條件3. **JOIN**多表數(shù)據(jù)關(guān)聯(lián)4. **GROUP BY**數(shù)據(jù)分組聚合5. **HAVING**分組結(jié)果過濾6. **子查詢**查詢嵌套解決復(fù)雜問題7. **索引**提高查詢性能的數(shù)據(jù)結(jié)構(gòu)