select concat(first_name,last_name) as 全名 from employees

select * from employees where length(first_name)>6

select now()
select SYSDATE()
select now() from dual

-- 組函數(shù)操作salary
select 
SUM(salary) 總和,AVG(salary) 平均值,MAX(salary) 最大值,min(salary) 最小值,COUNT(salary) 非空值的數(shù)量
from employees
-- 計(jì)算表中績(jī)效列不為空的員工數(shù)量
select COUNT(commission_pct) from employees
-- 求表中的行數(shù)
select COUNT(*) from employees
select COUNT(employee_id) from employees

select 列名 from 表名 group by 列名

select department_id,AVG(salary) 平均薪資 from employees group by department_id

-- 查詢部門(mén)id為10、20、30的部門(mén)的平均薪資、
select department_id,AVG(salary) 平均薪資 
from employees
where department_id in(10,20,30) -- 先篩選
GROUP BY department_id -- 后分組查詢

select department_id,AVG(salary) 平均薪資 
from employees
group by department_id -- 先分組
having department_id in(10,20,30)  -- 后篩選

select 列名 from 表名 limit 顯示的起始下標(biāo),顯示條數(shù)

select * from employees limit 0,10
select * from employees limit 10 下標(biāo)為0時(shí)可以省略

語(yǔ)法順序		執(zhí)行順序
select			5
from			1
where			2
group by		3
having			4
order by		6
limit			7

-- 查詢員工id為100的員工的部門(mén)id
select department_id from employees where employee_id=100

-- 子查詢：：查詢員工id為100的員工所在的部門(mén)信息
select * from departments 
where department_id=(select department_id from employees where employee_id=100)

--查詢firstname中包含s的員工所在的部門(mén)信息
select * from departments 
where department_id in(select department_id from employees where first_name like '%s%')

-- 獲取薪資最高的前十個(gè)員工的平均薪資
-- 先獲取薪資最高的前十名員工的薪資
select salary from employees ORDER BY salary desc LIMIT 10
-- 子查詢：再根據(jù)子SQL的查詢結(jié)果臨時(shí)通過(guò)別名構(gòu)建一張數(shù)據(jù)表進(jìn)行查詢
select avg(salary) from (select salary from employees ORDER BY salary desc LIMIT 10) as e

select 表1.列名，表2.列名,... 
from 表1 inner join 表2
on 連接條件

select 表1.列名，表2.列名,... 
from 表1(左表) left outer join 表2（右表）
on 連接條件

-- 查詢所有的員工信息及符合連接條件的部門(mén)信息
select e.*,d.*
from employees e LEFT JOIN departments d
ON e.department_id=d.department_id

-- 內(nèi)連接
select e.*,d.*  
from employees e inner join departments d
on  e.department_id=d.department_id

union -- 對(duì)內(nèi)連接和左連接的查詢結(jié)果進(jìn)行合并

-- 左連接
select e.*,d.*
from employees e LEFT JOIN departments d
ON e.department_id=d.department_id

-- 查詢：?jiǎn)T工id、員工姓名-firstname、直接領(lǐng)導(dǎo)的id、直接領(lǐng)導(dǎo)的姓名
select e1.employee_id 員工id,e1.first_name 員工姓名,e2.employee_id 領(lǐng)導(dǎo)id,e2.first_name 領(lǐng)導(dǎo)姓名
from employees e1 LEFT JOIN employees e2-- e1代表獲取員工信息的表，e2代表獲取領(lǐng)導(dǎo)信息的表
ON e1.manager_id=e2.employee_id -- 把兩個(gè)有關(guān)聯(lián)關(guān)系的字段作為連接條件

-- 查詢工資相同的員工信息
select e1.employee_id 員工1的id,e1.salary 員工1的工資,e2.employee_id 員工2的id,e2.salary 員工2的工資
from employees e1 LEFT JOIN employees e2
on e1.salary=e2.salary -- 連接條件
where e1.employee_id<e2.employee_id -- 防止判斷的雙方是同一個(gè)人，并去重