本文章的所有SQL語(yǔ)句完全兼容MySQL、Oracle、SQL server和PostgreSQL等四種最流行的數(shù)據(jù)庫(kù)，實(shí)際上也可以兼容幾乎所有的數(shù)據(jù)庫(kù)。

select語(yǔ)句：選擇顯示的內(nèi)容

select name, salary from employees

select name, salary * 12 from employees

select name, salary, salary + 1000?* 12 from employees

select name, salary, (salary+1000) * 12 as annual_salary from employees

select name, salary, (salary+1000) * 12 annual_salary from employees

select name 姓名, salary 月薪, (salary+1000) * 12 年薪 from employees

select name, salary, salary * 12 "annual salary" from employees

distinct去重復(fù)值

注：deptno是數(shù)據(jù)庫(kù)employees中的一個(gè)屬性

select distinct deptno from employees

where條件過(guò)濾

運(yùn)算符：

=等于

<>或!=不等于

>大于

<小于

<=小于等于

>=大于等于

select name, salary, hire_date from employees where hire_date<"2010-01-01";

select name, salary, deptno from employees where deptno=3;

and, or, not運(yùn)算符

and優(yōu)先級(jí)優(yōu)于or

select * from employees where deptno=3 and salary>10000;

select * from employees where deptno=3 or salary>10000;

select * from employees where deptno=3 or (salary>10000 and hire_date>"2015-01-01");?

select * from employees where not (deptno=3 or salary>=10000);

in語(yǔ)句

select 1 where 1 in (1,2);

select name, empno from employees where empno=3 or empno=5 or empno=6;

select name, empno from employees where empno in (3,5,6);

select name, empno from employees where empno not in (3,5,6);

between語(yǔ)句指定范圍

select name, hire_date from employees?

where hire_date>="2013-01-01" and hire_date<="2013-12-31";

select name, hire_date from employees?

where hire_date between "2013-01-01" and "2013-12-31";

select name, hire_date from employees?

where hire_date not between "2013-01-01" and "2013-12-31";

like語(yǔ)句匹配字符串

select * from employees where name like "李%"; %代表匹配包括空的所有字符串

select * from employees where name like "%衛(wèi)%";

select * from employees where email like "__a%"; 下劃線的數(shù)量代表含有多少個(gè)字母

select * from employees where email like "%@qq.com";

by語(yǔ)句排序

select name, salary from employees order by salary; 從小到大排序

select name, salary from employees order by salary desc; 反向排序

select name, salary*12 annual_salary from employees order by annual_salary;

select name, deptno, salary from employees order by deptno, salary; 按屬性順序排序

select name, deptno, salary from employees order by 2 desc, 3 asc; 用序號(hào)代表屬性名稱

SQL注釋

--單行注釋

/* */多行注釋

null空值

select 1 where null is null;

select 1 where null is not null;

insert employees(empno, name, salary) values(18, "德華", null);插入時(shí)沒有指定值都填充null或者默認(rèn)值

update更新一個(gè)字段或者多個(gè)字段

select? * from employees where empno=3;

update employees set deptno=3 where empno=3;

update employees set deptno=3, salary=salary+1000 where empno=3;

update employees set salary=default where empno=3;

update中使用子查詢

update employees set salary=salary+1000?

where deptno=(select deptno from departments where managerno=2);

update employees set salary=salary+1000

where deptno in (select deptno from departments where managerno=2 or managerno=3);

delete語(yǔ)句刪除記錄

delete from employees where empno=9;

truncate table = delete from table

truncate table employees;

delete語(yǔ)句使用子查詢

delete from employees where depto in? (select deptno from departments where? loc="二樓");

傳統(tǒng)的多表連接方法

select name, dname from employees ,departments?

where employees.deptno=departments.deptno;

select name, dname, employees.deptno from employees, departments??

where employees.deptno=departments.deptno;

select name, dname, e.deptno from employees e, departments? e?

where e.deptno=d.deptno;

inner join內(nèi)連接

select name, dname, e.deptno from employees e inner join departments d on e.deptno=d.deptno;? ? ? join 使用on

select j.*, e.name from job_history j join employees e on j.empno=e.empno;

self join自連接

insert into employees(empno, name) values(20, "李四");

select e1.name, e2.empno, e1.empno from employees e1 join employees e2 on e1.name=e2.name and e1.empno<e2.empno;

select j1.empno, j2.empno, j1.deptno, j1.start_date? comm_start

from job_history j1 join job_history j2 on j1.deptno=j2.deptno and j1.empno!=j2.empno and j1.start_date between j2.start_date and j2.end_date;

outer join外連接

select name, dname, e.deptno from employees e left outer join departments d on e.deptno=d.deptno;

select name, dname, e.deptno from employees e right?outer join departments d on e.deptno=d.deptno;

select name, dname, e.deptno from employees e full?outer join departments d on e.deptno=d.deptno;

cross join 交叉連接

select * from employees cross join departments;

select * from employees cross join departments order by empno;

union將兩個(gè)表或者多個(gè)表集合成一個(gè)集合，需要所有屬性和類型相同

select empno, deptno from employees;

select managerno, deptno from departments;

select empno, deptno from employees union select empno, deptno from departments;

select empno, deptno from employees union all select empno, deptno from departments;

intersect取出兩個(gè)集合共同的部分

select empno, deptno from employees intersect select managernp, deptno from departments;

常用的聚合分組函數(shù)

max()

min()

avg()

sum()

count()

select max(salary) as "max_salary", min(salary) as "min_salary", avg(salary) as "avg_salary", sum(salary) as "sum_salary", count(*) as "num_stuff" from employees;

group by分組

select deptno as "department",?max(salary) as "max_salary", min(salary) as "min_salary", avg(salary) as "avg_salary", sum(salary) as "sum_salary" from employees group by deptno;

select deptno,? avg(salary) from employees group by deptno order by avg(salary);

select deptno, max(salary), min(salary), avg(salary), sum(salary), count(*) from employees? where hire_date="2010-01-01" group by deptno order by avg(salary);????

select deptno, name, avg(salary) from employees group by deptno, name;

having過(guò)濾分組，是針對(duì)group by操作的

select deptno, avg(salary) from employees group by deptno having avg(salary)>3000;

select deptno, avg(salary), count(*)?from employees group by deptno having avg(salary)>3000 and count(*)>1;?

子查詢

select name from employees where hire_date<(select hire_date from employees where name="lisi");

in運(yùn)算符中的子查詢

select deptno from employees where loc="second_floor";

select empno, name from employees where deptno in (2,4);

select empno, name from employees where deptno in (select deptno from employees where loc="second_floor");

子查詢和連接

select empno, name from employees where deptno in (select deptno from employees where loc="second_floor");

select name from employees join departments on employees.deptno=departments.deptno where loc="second_floor";

select a.* from employees a join (select deptno, max(salary) max_sal from employees group by deptno) b on a.deptno=b.deptno and a.salary=b.max_sal;

all關(guān)鍵字，修飾集合

select name from employees where salary>(select max(salary) from employees where deptno=1);

select name from employees where salary> all (select max(salary) from employees where deptno=1);

select * from employees where empno not in (select managerno from departments where managerno is not null);

select * from employees where empno?<> all(select managerno from departments where managerno is not null);

any關(guān)鍵字，符合條件任一元素則成立

select name from employees where salary<(select max(salary) from employees where deptno=2);

select name from employees where salary<any(select salary from employees? where deptno=2);

select name from employees where empno in(select managerno from departments);

select name from employees where empno=any(select managerno from departments);

相關(guān)子查詢

select name, deptno, salary from employees e where salary>(select avg(salary) from empoyees);

select name, deptno, salary from employees e where salary >(select avg(salary) from employees where deptno=e.deptno);

exists運(yùn)算符

select name from employees where empno in (select distinct empno from job_history);

select name from employees where exists(select distinct empno from job_history where employees.empno=job_history.empno);

如果employees中記錄數(shù)大于job_history時(shí)，用in效率高

如果employees中記錄數(shù)小于job_history時(shí)，用exists效率高

select語(yǔ)句中的子查詢

select name, salary, (select avg(salary) from employees)?"avg_salary" from?employees;

from子句中的子查詢

select * from (select name, salary, deptno, (select avg(salary) from employees where deptno=e.deptno) 部門平均工資 from employees e) e2 order by salary;?

partition by窗口函數(shù)

select name, depto, salary, sum(salary) over (partition by deptno) 部門工資合計(jì) from employees;

注：over是采取分區(qū)

select name, deptno, hiredate

first_value(hiredate) over (partition by deptno order by hiredate) first,

last_value(hiredate) over (partition by deptno oder by hiredate) last

from employees;

select name, deptno, deptno, hiredate,?

lead(hiredate) over (partition by deptno order by hiredate) "lead",

lag(hiredate) over (partition by deptno oder by hiredate) "lag"

from employees;

select name, salary, deptno, rank() over (partition by deptno order by salary 部門內(nèi)序號(hào) from employees;

select name, salary, deptno, row_number() over (partition by deptno order by salary 部門內(nèi)序號(hào) from employees;

select name, salary, deptno, dense_rank() over (partition by deptno order by salary 部門內(nèi)序號(hào) from employees;

case when

select empno, deptno,?

case deptno?

when 1 then "開發(fā)部"

when 2 then "測(cè)試部"

when 3 then "銷售部"

else "其他部門" end deptname

from employees;

update employees set salary=

case

when salary>=20000 then salary*0.95

when salary between 10000 and 20000 then salary *1.1

else salary * 1.2

end;

2023-02-06-11：08