[toc] # 一、基本查詢 ## 1.查詢表中多個字段 ~~~ select salary,email from employees; //字段之間逗號隔開 ~~~ ## 2.查詢表中的所有字段 ~~~ select * from employees; ~~~ ## 3.起別名 ~~~ //1.使用as select last_name as 姓，first_name as 名 from employees; //2.使用空格 select last_name 姓，first_name 名 from employees; ~~~ ## 4.去重 ~~~ //查詢員工表中涉及到的所有部門的編號（關鍵字：distinct） select distinct department_id from employees; ~~~ ## 5.+號的作用 MySQL中+號僅僅只有一個功能：運算符 ~~~ select ‘132’+90； //字符型會試圖轉成數字型，若轉換成功則繼續做加法 select ‘Jack’+90; //若轉換失敗，字符串部分轉換為0 select null+10; //只要其中一方為null，結果必為null。（此處需要ifnull（null,0）方法避免結果為null） ~~~ # 二、條件查詢 語法 ~~~ select 查詢列表 from 表名 where 篩選條件； ~~~ ## 1.按條件表達式查詢 ~~~ //查詢工資>12000的員工信息 select * from employees where salary>12000; ~~~ ## 2.按邏輯表達式篩選 ~~~ //查詢工資在10000到20000之間的員工信息 select * from employees where salary>=10000 and salary<=20000; ~~~ ## 3.模糊查詢 ~~~ /*like 一般和通配符使用：% 任意多個字符，包含0；_任意單個字符 between ... and ... 在...區間內 in(x,y,z) 在x,y,z這三個值之中 is null 為空 */ //查詢員工名中第二個字符為_的員工名 select last_name from employees where last_name like '_$_%' escape '$'; ~~~ # 三、分組函數 功能：用作統計使用 分類：sum 求和 ；avg 平均值；max 最大值；min 最小值；count 計算個數 # 四、排序查詢 語法 ~~~ select 查詢列表 from 表名 where 條件 order by 排序的字段 +（asc/desc） //order by子句在查詢語句的最后面，默認為升序 ~~~ # 五、常見函數 ## limit方法 limit子句用于限制查詢結果返回的數量 ~~~ limit i,n i：為查詢結果的索引值（默認從0開始）； n：為查詢結果返回的數量 注意：limit一般用在SQL語句的最后！！！ ~~~ ## case函數方法 ~~~ //流程控制函數case 1.case函數方法一：類似java里switch case的效果 SELECT salary 原始工資,department_id, CASE department_id WHEN 30 THEN salary*1.1 WHEN 40 THEN salary*1.2 WHEN 50 THEN salary*1.3 ELSE salary END AS 新工資 FROM employees; ~~~ 結果如下，注意case是在<b>select和from中間</b>,處在一個比較靠前的位置，注意select上倒數第二個字段要逗號，<b>end as 后面接的才是最后一個字段</b>。還有就是如果when后面接的是字符串一定要加<b>引號</b>。  ~~~ 2.case函數方法二：類似if else //此方法when后面是條件表達式，所以記住case后面什么都不寫 SELECT salary, CASE WHEN salary>20000 THEN 'A' WHEN salary>15000 THEN 'B' WHEN salary>10000 THEN 'C' ELSE 'D' END AS 工資級別 FROM employees; ~~~ # 六、分組查詢 語法 ~~~ select 字段 from 表 【where 篩選條件】 group by 分組字段 【having 篩選條件】 【order by 排序字段】 ~~~ ## where和having區別 <b>where主要對原始表中的字段進行篩選 having主要對原始表中不存在的字段進行篩選</b> ## 分組查詢小技巧 看到<b>“每個”</b>來形容的字段就是要分組的字段 分組查詢中，select后面接的字段基本是由<b>兩部分</b>組成： - 1.分組函數如：sum,avg,min,max,count。 - 2.后面要分組的字段，即group by后面接的字段。 # 七、連接查詢 ## 等值連接查詢 ~~~ //案例：查詢有獎金的員工名、部門名 SELECT last_name,department_name,commission_pct FROM employees e,departments d WHERE e.`department_id`=d.`department_id` AND e.`commission_pct` IS NOT NULL; ~~~ 等值查詢要給表起別名，這樣有利于區分重名的字段，而且提高語句的簡潔度。 ## 自連接查詢 ~~~ //查詢員工號、名和上級的工號和名。 select e.employee_id,e.first_name,m.employee_id,m.last_name from employees e,employees m where e.manager_id = m.employee_id; ~~~ 這里的就是同一個表起了兩個別名，我們假設第一個表里是員工，第二個表里是上司，然后將兩張表按上司和員工的關系連接即可查詢到相應的結果。 ## sql99查詢語法 1.等值查詢 ~~~ SELECT e.department_id,job_id,location_id FROM employees e JOIN departments d ON e.department_id=d.department_id WHERE e.department_id=90; //兩個表的等值連接join里寫要連接的表，on后面寫兩張表連接的條件，where后接篩選條件 ~~~ 2.多表連接 ~~~ SELECT j.job_id,j.job_title,department_name,MIN(salary) FROM employees e JOIN departments d ON e.department_id=d.department_id JOIN jobs j ON j.job_id=e.job_id GROUP BY job_id,department_name; //多表連接的話用多個join和on組合即可實現 ~~~ # 八、子查詢 ## 1.where或having后面 特點： ①子查詢放在小括號內 () ②子查詢一般放在條件的右側 工資>(子查詢) ③標量子查詢，一般搭配著單行操作符使用 ~~~ //1.標量子查詢 案例：查詢 最低工資大于50號部門最低工資的部門 部門id和其最低工資 ①查詢50號部門的最低工資 SELECT MIN(salary) FROM employees WHERE department_id = 50 ②查詢每個部門的最低工資 SELECT MIN(salary),department_id FROM employees GROUP BY department_id ③ 在②基礎上篩選，滿足min(salary)>① SELECT MIN(salary),department_id FROM employees GROUP BY department_id HAVING MIN(salary)>( SELECT MIN(salary) FROM employees WHERE department_id = 50 ); //2.列子查詢：只有一列，可以有多行 案例：返回其它部門中比job_id為‘IT_PROG’部門所有工資都低的員工 的員工號、姓名、job_id 以及salary SELECT last_name,employee_id,job_id,salary FROM employees WHERE salary<( SELECT MIN( salary) FROM employees WHERE job_id = 'IT_PROG' ) AND job_id<>'IT_PROG'; //3.行子查詢：只有一行，可以多列 案例：查詢員工編號最小并且工資最高的員工信息 SELECT * FROM employees WHERE (employee_id,salary)=( SELECT MIN(employee_id),MAX(salary) FROM employees ); //注意這里有重點，子查詢還可以這樣寫where（字段1，字段2）=（select 目標1，目標2 from 表名），保證字段和目標一一對應即可。 ~~~ ## 2.from或者join后面 <b>子查詢的結果可以充當一張表，要求必須起別名</b> ~~~ 案例：查詢各部門中工資比本部門平均工資高的員工的員工號、姓名和工資 SELECT e.employee_id,e.first_name,e.salary FROM employees e JOIN (SELECT avg(salary) a, department_id FROM employees GROUP BY department_id) d on e.department_id=d.department_id WHERE salary>d.a; //注意這里，就是用子查詢建了一個包含平均工資和部門的表，然后按照部門名相同連接起來，再做工資的篩選。 ~~~ ## 3.where exists后面（相關子查詢） where exists的返回值是0或1，如果子查詢存在返回1，否則返回0。 ~~~ 案例：查詢有員工的部門名 SELECT department_name FROM departments d WHERE EXISTS( SELECT * FROM employees e WHERE d.`department_id`=e.`department_id` ); ~~~ # 九、聯合查詢 語法 ~~~ 查詢語句1 union 查詢語句2 ~~~ 應用場景：要查詢的結果來自多個表，且多個表沒有直接的連接關系，但查詢的信息必須一致。 ~~~ //引入的案例：查詢部門編號>90或郵箱包含a的員工信息 SELECT * FROM employees WHERE email LIKE '%a%' UNION SELECT * FROM employees WHERE department_id>90; ~~~ 注意點： 1.要求多條查詢語句的列數和查詢的<b>字段要一致</b> 2.union關鍵字默認去重，如果使用union all可以包含重復項