数据库SQL编程

本文章的所有SQL语句完全兼容MySQL、Oracle、SQL server和PostgreSQL等四种最流行的数据库，实际上也可以兼容几乎所有的数据库。

select语句：选择显示的内容

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去重复值

注：deptno是数据库employees中的一个属性

select distinct deptno from employees

where条件过滤

运算符：

=等于

<>或!=不等于

>大于

<小于

<=小于等于

>=大于等于

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运算符

and优先级优于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语句

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语句指定范围

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语句匹配字符串

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

select * from employees where name like "%卫%";

select * from employees where email like "__a%"; 下划线的数量代表含有多少个字母

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

by语句排序

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; 用序号代表属性名称

SQL注释

--单行注释

/* */多行注释

null空值

select 1 where null is null;

select 1 where null is not null;

insert employees(empno, name, salary) values(18, "德华", null);插入时没有指定值都填充null或者默认值

update更新一个字段或者多个字段

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语句删除记录

delete from employees where empno=9;

truncate table = delete from table

truncate table employees;

delete语句使用子查询

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

传统的多表连接方法

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内连接

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将两个表或者多个表集合成一个集合，需要所有属性和类型相同

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取出两个集合共同的部分

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

常用的聚合分组函数

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过滤分组，是针对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运算符中的子查询

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关键字，修饰集合

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关键字，符合条件任一元素则成立

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);

相关子查询

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运算符

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中记录数大于job_history时，用in效率高

如果employees中记录数小于job_history时，用exists效率高

select语句中的子查询

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窗口函数

select name, depto, salary, sum(salary) over (partition by deptno) 部门工资合计 from employees;

注：over是采取分区

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 部门内序号 from employees;

select name, salary, deptno, row_number() over (partition by deptno order by salary 部门内序号 from employees;

select name, salary, deptno, dense_rank() over (partition by deptno order by salary 部门内序号 from employees;

case when

select empno, deptno, 

case deptno 

when 1 then "开发部"

when 2 then "测试部"

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