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 查询

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 表名; --可以用数学表达式

DISTINCT 查询去重

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

条件过滤

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;

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

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

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

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;

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确保每个员工只出现一次，避免重复。
*/

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

AND和OR就是&&和||，and是两者都触发，or是前面不触发才触发后面条件
NOT就是不触发
IN（字段1,字段2），匹配括号里的值
BETWEEN，指定范围匹配
LIKE,模糊搜索，%(百分号)匹配0个、1个、多个字符，_(下划线)匹配单个字符
ORDER BY排序，asc正序，desc倒序
INNER JOIN内连接，内连接为默认方式，可以省略INNER
SELF JOIN自连接，在同一个表里，输出多个不同的结果
OUTER JOIN外连接，有LRFT JOIN、RIGHT JOIN、FULL OUTER JOIN，加上WHERE a.key IS NULL、WHERE a.key IS NULL OR b.key IS NULL可以将相同数据排除
INTERSECT查询输出结果的相同数据（交集）
UNION合并查询（合集），输入all不会去掉重复值

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() - 返回总和
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;

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

约束

• 非空约束：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年之后的列为新员工

本栏目推荐文章
• SQL Server 检测是不是数字型数据的两种方法
• SQL Join的一些总结
• SQL Server 高性能写入的一些总结
• 网络攻击技术开篇——SQL Injection
• SQL SERVER日期时间转字符串
• sql server 保留2位小数
• 摄像头 配置备忘 sip erwa.cn
• DBEAVER 23.0.2 调整SQL编辑器字体大小 ver:20240112
• 用友 U9 【料品表】全字段Sql输出
• sql server 2012配置备份

备忘录 SQL备忘录sql 备忘录 语句 常用sql 备忘录 备忘录 模式 备忘录123 备忘录git 备忘录cuda 备忘录linux 备忘录memento模式 备忘录suricata