SQL备忘录

资料来源

零基础33分钟学会4种数据的SQL语言 | bilibli | SQL优化
SQL内容 | cnblogs | 我没有bug
SQL 教程

基础知识

SQL是用于存储和管理关系数据库中的数据的标准计算机语言
不区分大小写，几乎所有的关系数据库都通用

numeric(a,b)

SQL语句分类

DQL查询总结 | cnblogs | 我没有bug
DDL、DML、DCL总结 | cnblogs | 我没有bug

• DQL:数据查询语言(带有select)
• DML:数据操作语言(对数据增、删、改)
• DDL:数据定义语言(对表的结构改变)
• TCL:事务控制语言
• DCL:数据控制语言

-- 本手册数据库环境
drop table if exists dept;
create table dept(
  deptno int primary key,
  dname varchar(12) not null,
  IOC varchar(10) );
insert into dept values(1,'Development','NEWYORK');
insert into dept(deptno,dname,loc) vaIues(2,'Testing','Chicago');
insert into dept(deptno,dname) values(3,'Marketing');
drop table if exists emp；
create table emp(
  empno int primary key,
  ename char(10) not null,
  deptno int,
  hiredate date,
  sal numeric(8,2)，
  comm numeric(8,2) )；
insert into emp values(1,'Grace',1,'2000-12-02',12000.00,0)；
insert into emp values(2,'Joe',1,'2013-01-03',9100.00,0)；
insert into emp values(3,'JOhn',2,'2021·02·10',8900.00,null)；
insert into emp values(4,'Lisa',2,'2022·12·1',11000.00,null)；
insert into emp values(5,'Ben',3,'2004-09-11',8000.00,9000.00);
commit;

DQL

书写顺序：
select
from
where
group by
having
order by
limit

执行顺序：
from
where
group by
having
select
order by
limit

简单查询

select 字段名 from 表名; --一个字段
select 字段1,字段2 from 表名; --多个字段
select 字段1,字段2...... from 表名; --所有字段
select 字段1,字段2 as abc from 表名; --用as将字段2起名abc
select 字段1,字段2 as "字 符 串" from 表名; --含有空格或中文需要用双引号
select sal*12 from 表名; --可以用数学表达式

条件查询

WHERE子句触发
AND和OR就是&&和||，and是两者都触发，or是前面不触发才触发后面条件
NOT就是不触发

select empno,ename,deptno,hiredate,sal,comm for emp where (deptno<=2 or deptno>3) and sal>15000;
select empno,ename,deptno,hiredate,sal,comm for emp where deptno!=3 and sal>15000;

模糊查询

LIKE,模糊搜索，%(百分号)匹配0个、1个、多个字符，_(下划线)匹配单个字符

select ename from emp where enme like '%a%';
select ename from emp where ename like '__a%';

排序

ORDER BY排序，asc升序，desc降序

select ename,deptno,sal from emp order by deptno desc,sal asc;

UNION和INTERSECT 合并查询和交集查询

INTERSECT查询输出结果的相同数据（交集）
UNION合并查询（合集），输入all不会去掉重复值

SELECT DISTINCT deptno
FROM emp
INTERSECT
SELECT deptno
FROM dept;

SELECT DISTINCT deptno
FROM emp
UNION
SELECT deptno
FROM dept;

SELECT DISTINCT deptno
FROM emp
UNION all
SELECT deptno
FROM dept;

分组查询

GROUP BY用于结合聚合函数，根据一个或多个列对结果集进行分组。

-- GROUP BY演示
select deptno 部门,
	max(sal) as 最高工资，
	min(sal) as 最低工资，
	avg(sal) as 平均工资，
	sum(sal) as 汇总工资，
	count(*) as 员工数，
 from emp
where deptno=1
union
select deptno 部门,
	max(sal) as 最高工资，
	min(sal) as 最低工资，
	avg(sal) as 平均工资，
	sum(sal) as 汇总工资，
	count(*) as 员工数，
 from emp
where deptno=2
union
select deptno 部门,
	max(sal) as 最高工资，
	min(sal) as 最低工资，
	avg(sal) as 平均工资，
	sum(sal) as 汇总工资，
	count(*) as 员工数，
 from emp
where deptno=3;

select deptno 部门,
	max(sal) as 最高工资，
	min(sal) as 最低工资，
	avg(sal) as 平均工资，
	sum(sal) as 汇总工资，
	count(*) as 员工数，
 from emp
group by deptno;

HAVING过滤分组，用于GROUP BY分组之后过滤输出结果，GROUP BY是在WHERE之后执行的。

select deptno,avg(sal) from emp
group by deptno
having avg(sal)>10000;

DISTINCT 查询去重

select distinct job from 表名;
select distinct dname,job from 表名; --name和job联合去重
select count(distinct job) from 表名; --统计工作岗位数量

连接查询

内连接

INNER JOIN内连接，内连接为默认方式，可以省略INNER

select ename,loc from emp,dept where emp.deptno=dept.deptno; -- 按照deptno使用WHERE子句查询
select ename,loc from emp INNER JOIN dept on emp.deptno=dept.deptno; -- 使用inner join子句
select ename,loc,e.deptno from emp e join dept d on e.deptno=d.deptno;

自连接

SELF JOIN自连接，在同一个表里，输出多个不同的结果

select e1.ename,e1.deptno from emp e1 join emp 32
  on e1.deptno=e2.deptno and e1.empno<>e2.empno; -- 同一个部门里共事的员工

select e1.ename, e2.ename, e3.ename
from emp e1, emp e2, emp e3
where e1.deptno = e2.deptno and e2.deptno = e3.deptno
and e1.empno < e2.empno and e2.empno < e3.empno; 
/*
同一个部门共事的三个员工
e1.empno < e2.empno < e3.empno确保每个员工只出现一次，避免重复。
*/

外连接

OUTER JOIN外连接，有LRFT JOIN、RIGHT JOIN、FULL OUTER JOIN，加上WHERE a.key IS NULL、WHERE a.key IS NULL OR b.key IS NULL可以将相同数据排除

insert into emp(empno,ename,deptno,sal) values(10,'Frank',4,12000.00);
insert into dept(deptno,dname) values(5,'Operation');
select e.name,d.name,e.deptno,d.deptno from emp e join dept d on e.deptno=d.deptno;
select e.name,d.name,e.deptno,d.deptno from emp e left join dept d on e.deptno=d.deptno;
select e.name,d.name,e.deptno,d.deptno from emp e right join dept d on e.deptno=d.deptno;
/*
分别在两个表插入新的数据，用内连接无法输出新插入的数据。
用左连接(left join)和右连接(right join)分别输出新数据
*/

子查询

select empno,ename from emp where empno in (3,4,5);
select empno,ename from emp where empno not in (3,4,5);

select ename,hiredate from emp where hiredate between '2013-01-01' and '2013-12-31';

IN（字段1,字段2），匹配括号里的值
BETWEEN，指定范围匹配

NOT IN注意事项

NOT IN表示不在集合中

-- 加入一个新员工后会无法输出结果
insert into dept(deptno,dname) values(5,'Operation');
select deptno,dname from dept d
  where d.deptno not in (select e.deptno from emp e);
insert into emp(empno,ename,deptno) values(10,'Frank',NULL);
select deptno,dname from dept d
  where d.deptno not in (select e.deptno from emp e);
-- 用or运算符能将结果输出
select deptno, dname from dept d
  where d.deptno not in (select e.deptno from emp) or dname = 'Operation';
-- 用LEFT JOIN子句
select d.deptno, d.dname
from dept d
left join emp e on d.deptno = e.deptno
where e.deptno is null or d.dname = 'Operation';

DML

INSERT 插入

insert into 表名(字段名1,字段名2,字段名3,字段名4) values(值1，值2，值3，值4);
insert into 表名(字段名1,字段名2,字段名3) values(值1，值2，值3),(值1，值2，值3),(值1，值2，值3);

每次insert都会增加一条记录，没有指定的字段为null
如果前面字段名都省略，相当于所有字段名都写了，那么后面values里的值要写全(不建议采用)

UPDATE 更新

select empno from emp where empno=3;
update emp set deptno=1 where empno=3;
update emp set sal=sal+1000 where empno=3;
update emp set deptno=1,sal=sal+1000 where empno=3;
update emp set sal=sal+1000 where deptno=(select deptno from deptno where dname='Development';)

DELETE 删除

delete from emp where deptno=5; --删除一行数据
truncate table emp; --删除整个表

DDL

CREATE 创建

create table 表名(
    字段名1 数据类型,
    字段名2 数据类型,
    字段名3 数据类型
);

DROP 删除

drop table 表名;
drop table 表名 if exists;

如果表不存在上面语句会报错，下面不报错

TCL

DCL

函数

AVG() - 返回平均值
COUNT() - 返回行数
FIRST() - 返回第一个记录的值
LAST() - 返回最后一个记录的值
MAX() - 返回最大值
MIN() - 返回最小值
SUM() - 返回总和
ALL一个字段对比全部

-- 比2号员工高工资的所有员工
select ename from emp where sal > (select max(sal) from emp where deptno=2)
select ename from emp where sal > all (select sal from emp where deptno=2)

ANY集合中的任意值

-- 比比2号员工高工资的任一员工
select ename from emp where sal > (select mix(sal) from emp where deptno=2)
select ename from emp where sal > any (select sal from emp where deptno=2)

NULL空值

NULL 值代表遗漏的未知数据。
null+null是null,null-null还是null
null=null和null!=null都不会显示结果，因为null不能比较
NULL 用作未知的或不适用的值的占位符。（比如不知道这个员工的佣金，则使用null）

select 1 where null is null;
select 1 where 0 is not null;
select 1 where '' is not null;

-- 如果不用ifnull()| ISNULL() | COALESCE() | NVL() 函数，收入总额会提示NULL。
-- MySQL版本：
SELECT ename, sal, IFNULL(comm, 0) as comm, sal+ IFNULL(comm, 0) as 收入总和 FROM emp;
SELECT ename, sal, COALESCE(comm, 0) as comm, sal+ COALESCE(comm, 0) as 收入总和 FROM emp;

-- Oracle版本：
SELECT ename, sal, NVL(comm, 0) as comm, sal + NVL(comm, 0) as 收入总和 FROM emp;

-- SQL Server版本：
SELECT ename, sal, ISNULL(comm, 0) as comm, sal+ ISNULL(comm, 0) as 收入总和 FROM emp;

-- PostgreSQL版本：
SELECT ename, sal, COALESCE(comm, 0) as comm, sal+ COALESCE(comm, 0) as 收入总和 FROM emp;

约束

• 非空约束：not null
• 唯一性约束：unique
• 主键约束：primary key(简称PK)
• 外键约束：foreign key(简称FK)
• 检查约束：check(MySQL不支持，Oracle支持)

主键由表中的一个字段或者多字符组成，主键唯一代表表中的一条记录

例子

1. 查询出比自己部门平均工资高的员工
2. 使用between查询工资在一万和一万五千之间的员工。
3. 因为当地生活成本上涨，公司决定给在CHICAGO工作的员工增加10％的工资
4. 找出姓名最后一个字面是n或者第二字面是i的员工。
5. 按照部门升序和入职日期降序员工名。
6. 查询2010年之前入职的姓名、工资和增加10％后的工资(updated salary)和入职时间。
7. 因为公司经营困难，解雇2010年前入职的老员工
8. 解雇所有在NEW YORK工作的员工，把他们从员工表中删除。
9. 列出所有佣金不为空的员工的姓名、工资、佣金和工资和佣金的总和。
10. 找出不在部门1和部门3的员工的姓名。
11. 查询部门名和部门的工资总和，提示：需要用到emp表、deptno表和相关子查询。
12. 查询所有员工的姓名和入职时间，根据入职时间将员工进行分类成资深程度

• 早于2001之前入职员工列为创始人
• 在2001年和2019年之间的列为老员工
• 在2020年之后的列为新员工