postgresql 基础语句
文章目录
前言
在开始我们今天的学习前，首先创建2张表，在这里就用大家最熟悉的dept和emp表，建表语句如下：
创建表及模式
–创建模式
CREATE SCHEMA "lianxi";
–建表
DROP TABLE IF EXISTS "lianxi"."emp";--删除原有的emp表
-- 创建emp表
CREATE TABLE "lianxi"."emp" (
"empno" int4 NOT NULL,
"ename" varchar(30) COLLATE "pg_catalog"."default",
"job" varchar(30) COLLATE "pg_catalog"."default",
"mgr" int4,
"hiredate" date,
"sal" numeric(10,2),
"comm" numeric(10,2),
"deptno" int4,
CONSTRAINT "emp_pkey" PRIMARY KEY ("empno")
)
;
-- 创建dept表
DROP TABLE IF EXISTS "lianxi"."dept";--删除原有的dept表
CREATE TABLE "lianxi"."dept" (
"deptno" int4 NOT NULL,
"dname" varchar(30) COLLATE "pg_catalog"."default",
"loc" varchar(30) COLLATE "pg_catalog"."default",
CONSTRAINT "dept_pkey" PRIMARY KEY ("deptno")
)
;
插入数据
–往dept表插入数据
INSERT INTO "lianxi"."dept"("deptno", "dname", "loc") VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO "lianxi"."dept"("deptno", "dname", "loc") VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO "lianxi"."dept"("deptno", "dname", "loc") VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO "lianxi"."dept"("deptno", "dname", "loc") VALUES (40, 'OPERATIONS', 'BOSTON');
–往emp表插入数据
INSERT INTO "lianxi"."emp"("empno", "ename", "job", "mgr", "hiredate", "sal", "comm", "deptno") VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', '800.00', NULL, 20);
INSERT INTO "lianxi"."emp"("empno", "ename", "job", "mgr", "hiredate", "sal", "comm", "deptno") VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', '1600.00', '300.00', 30);
INSERT INTO "lianxi"."emp"("empno", "ename", "job", "mgr", "hiredate", "sal", "comm", "deptno") VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', '1250.00', '500.00', 30);
INSERT INTO "lianxi"."emp"("empno", "ename", "job", "mgr", "hiredate", "sal", "comm", "deptno") VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', '2975.00', NULL, 20);
INSERT INTO "lianxi"."emp"("empno", "ename", "job", "mgr", "hiredate", "sal", "comm", "deptno") VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', '1250.00', '1400.00', 30);
INSERT INTO "lianxi"."emp"("empno", "ename", "job", "mgr", "hiredate", "sal", "comm", "deptno") VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', '2850.00', NULL, 30);
INSERT INTO "lianxi"."emp"("empno", "ename", "job", "mgr", "hiredate", "sal", "comm", "deptno") VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', '2450.00', NULL, 10);
INSERT INTO "lianxi"."emp"("empno", "ename", "job", "mgr", "hiredate", "sal", "comm", "deptno") VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', '3000.00', NULL, 20);
INSERT INTO "lianxi"."emp"("empno", "ename", "job", "mgr", "hiredate", "sal", "comm", "deptno") VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', '5000.00', NULL, 10);
INSERT INTO "lianxi"."emp"("empno", "ename", "job", "mgr", "hiredate", "sal", "comm", "deptno") VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', '1500.00', '0.00', 30);
INSERT INTO "lianxi"."emp"("empno", "ename", "job", "mgr", "hiredate", "sal", "comm", "deptno") VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', '1100.00', NULL, 20);
INSERT INTO "lianxi"."emp"("empno", "ename", "job", "mgr", "hiredate", "sal", "comm", "deptno") VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', '950.00', NULL, 30);
INSERT INTO "lianxi"."emp"("empno", "ename", "job", "mgr", "hiredate", "sal", "comm", "deptno") VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', '3000.00', NULL, 20);
INSERT INTO "lianxi"."emp"("empno", "ename", "job", "mgr", "hiredate", "sal", "comm", "deptno") VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', '1300.00', NULL, 10);
INSERT INTO "lianxi"."emp"("empno", "ename", "job", "mgr", "hiredate", "sal", "comm", "deptno") VALUES (9999, 'SHUNPING', 'CLERK', 7782, '1988-05-05', '2456.34', '55.66', 10);
一 SQL（Data QueryLanguage）数据查询
1.简单查询
1.1全表查询
SELECT * from "模式名"."表名";
实例：
SELECT * from lianxi.dept;
1.2 查询某列
查询单列
SELECT 列名 from “模式名”.“表名”；
查询多列
SELECT 列名,列名 from “模式名”.“表名”；
实例：
SELECT deptno from lianxi.dept ;
SELECT deptno,dname,loc from lianxi.dept;
1.3 别名
1.3.1 给表起别名
SELECT * from "模式名"."表名" as 别名;
as 可以忽略不写
实例：
SELECT * from lianxi.dept as abc ; --给dept表起别名abc
SELECT abc.* from lianxi.dept abc ;
1.3.2 给列起别名
--单列
SELECT 列名 as 别名 from "模式名"."表名"；
--多列
SELECT 列名1 as 别名1，列名2 as 别名2 from "模式名"."表名"；
SELECT 列名1 as 别名1，列名2 as 别名2,列名3 as 别名3 from "模式名"."表名"；
as 可以忽略不写
实例：
SELECT deptno as 序号 from lianxi.dept ;
SELECT deptno 序号 from lianxi.dept ;
SELECT deptno as 序号,loc as 城市 from lianxi.dept as abc ;
2.筛选查询
2.1 = 等于
select * from 表名 where 列名=值;
SELECT * from lianxi.dept WHERE deptno='20' ;
2.2 <,>,<=,>=,<>或!=
--< 小于
SELECT * from lianxi.dept WHERE deptno<'20' ;
-- > 大于
SELECT * from lianxi.dept WHERE deptno>'20' ;
-- <= 小于等于
SELECT * from lianxi.dept WHERE deptno<='20' ;
-- >=大于等于
SELECT * from lianxi.dept WHERE deptno>='20' ;
-- <>或!= 不等于
SELECT * from lianxi.dept WHERE deptno<>'20' ;
SELECT * from lianxi.dept WHERE deptno!='20' ;
2.3 between… and
其中注意between … and是包含边界的,所有在使用时要注意,如果是统计数据,则慎用.
SELECT * from lianxi.dept WHERE deptno between '20' and '40';
2.4 in ,not in
in () 在这个（范围里面）
SELECT * from lianxi.dept WHERE deptno in ('10','20');
SELECT * from lianxi.dept WHERE deptno in ('20');
not in () 不在这个（范围里面）
SELECT * from lianxi.dept WHERE deptno not in ('10','20');
SELECT * from lianxi.dept WHERE deptno not in ('20');
2.5 and 和 or
2.5.1 定义
and:意为同时满足and前后两个条件
or:满足or前后任意一个条件即可,也可以理解为查询结果是满足前面条件的结果和满足后面条件结果的合集.
SELECT * from lianxi.dept WHERE deptno in ('10','20') and deptno<'20';
SELECT * from lianxi.dept WHERE deptno in ('10','20') or deptno<'20';
2.5.2 优先级
而且and和or有优先级关系: ()>and>or
即当有and和or同时存在时,先执行and,再执行or. 如果有括号,则先执行括号.
2.6 空值和空字符
is null 空值
is not null 非空值
= ‘’ 是 空字符
SELECT * from lianxi.dept where dname ='';
SELECT * from lianxi.dept where dname is null;
SELECT * from lianxi.dept where dname is not null;
3.模糊查询
3.1 like 与 not like
select * from 表名 where 列名 like ‘%内容%’;
select * from 表名 where 列名 not like ‘%内容%’;
3.2 占位符
%和_的含义:
%在它和它之前有0个或者多个字符
_在当前位置有一个字符
select * from emp where ename like '%TH%';--ename中包含TH字样的
select * from emp where ename like '___TH';--查询ename以TH结尾,且长度是5位的
select * from 表名 where 列名 like '内容%';--以固定内容开头
selct * from 表名 where 列名 like '%内容';--以固定内容结尾的数据
搜索固定长度的数据
select * from 表名 where 列名 like '_____';--搜索长度是5位的数据 考虑函数方式
搜索带%或者_的数据
select * from 表名 where 列名 like '%/%%' escape '/';
select * from 表名 where 列名 like '%/_%' escape '/';
搜索带%和_且不挨着的数据
'%/%%_/_%' escape '/'
'%/_%_/%%' escape '/'
4.分组与排序
4.1 排序
order by:排序,也是关键字,按照之后的列和排序方式进行排序
排序的两种方式 asc,desc:升序 asc (默认), 降序 desc
- 排序语法:
select * from 表名 order by 列名 ;
SELECT * from lianxi.dept order by deptno ;
SELECT * from lianxi.dept order by deptno asc ;
SELECT * from lianxi.dept order by deptno desc ;
- 多列排序的语法和样式：
order by 列名1 asc, 列名2 desc;–先按照第一列排序,当第一列中有重复数据时,按照第二列排序.如果第一列中没有重复数据,则根据第一列的排序就是最终结果
select * from 表名 order by 列名1 desc, 列名2 asc;
4.2 分组
基本语法 多列分组(语法和含义)
select 内容 from 表名 group by 列名;
select 内容 from 表名 group by 列名1，列名2，;
select之后的内容(聚合函数, 如：coount(1), count(2))
分组列,聚合函数处理过的列
内容:分组列或者聚合函数处理过的列
-
聚合函数
MAX–最大值
MIN–最小值
SUM–求和
AVG–平均数
COUNT–数量 count(*), count(1), count(2),
count(列名) 如果列中有空值,那么得出的数量是不包括空值的. -
having和where:
where:针对全表数据做过滤
having:针对分组内的数据过滤 -
group by 列名 having 内容;
having之后的内容:
1.分组列可用;
2.聚合函数可以用;
select * from 表名 where 条件 group by 列 having 内容 order by 列 asc/desc;
4.当出现上面的语时，执行顺序是：
(1):where条件;
(2):group by;
(3):having 过滤;
(4):select;
(5):order by;
5. 关联查询
语法
select * from 表1,表2,表3 where 表1.列名=表2.列名 and 表2.列名=表3.列名;
实例：
SELECT * from dept ,emp WHERE dept.deptno=emp.deptno;
左连接:
–
显示两张表全部的列,左表在前,右表在后.显示左表全部数据,右表能关联上到部分,显示右表数据,否则右表部分显示为空;
select * from 表1 left join 表2 on 表1.列名=表2.列名;
实例：
select * from dept left join emp on dept.deptno=emp.deptno;
右连接:
– 显示两张表全部的列,左表在前,右表在后,显示右表全部的数据,左表能关联上的部分,显示左表数据,否则左表部分显示为空;
select * from 表1 right join 表2 on 表1.列名=表2.列名;
实例：
select * from dept right join emp on dept.deptno=emp.deptno;
内连接:
select * from 表1 inner join 表2 on 表1.列名=表2.列名;
实例：
select * from dept inner join emp on dept.deptno=emp.deptno;
外连接
分为左外连接和右外连接,功能上等同于左连接和右连接
select * from 表1 left outer join 表2 on 表1.列名=表2.列名;
select * from 表1 right outer join 表2 on 表1.列名=表2.列名;
实例：
select * from dept left outer join emp on dept.deptno=emp.deptno;
select * from dept right outer join emp on dept.deptno=emp.deptno;
全连接:
是左连接和右连接的集合,展示左表全部数据和右表全部数据,关联不上的部分各自补空;
select * from 表1 full join 表2 on 表1.列名=表2.列名;
实例：
select * from dept full join emp on dept.deptno=emp.deptno;
6.集合
集合这部分需要在建一个表
-- 创建dept1表
DROP TABLE IF EXISTS "lianxi"."dept1";--删除原有的dept表
CREATE TABLE "lianxi"."dept1" (
"deptno" int4 NOT NULL,
"dname" varchar(30) COLLATE "pg_catalog"."default",
"loc" varchar(30) COLLATE "pg_catalog"."default",
CONSTRAINT "dept_pkey" PRIMARY KEY ("deptno")
)
;
–往dept表插入数据
INSERT INTO "lianxi"."dept1"("deptno", "dname", "loc") VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO "lianxi"."dept1"("deptno", "dname", "loc") VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO "lianxi"."dept1"("deptno", "dname", "loc") VALUES (31, 'SALES', 'CHICAGO');
INSERT INTO "lianxi"."dept1"("deptno", "dname", "loc") VALUES (41, 'OPERATIONS', 'beijing');
合集:
union和union all, union有去重和排序的作用(升序排列),SQL优化时,如果允许,可以把unoin换成union all
select * from dept UNION all select * from dept1;
select * from dept UNION select * from dept1;
交集:
intersect 取两个结果集交叉的部分
select * from dept intersect select * from dept1;
差集:
A EXCEPT B 显示A中去掉AB交集的部分
B EXCEPT A 显示B中去掉AB交集的部分
select * from dept EXCEPT select * from dept1;
select * from dept1 EXCEPT select * from dept;
7. 子查询
子查询的概念:筛选查询时,我们的过滤条件是列等于某个值,这个值是直接给出的.如果这个值不是直接给出,而是给出计算逻辑和参数,那就需要用一个sql来获取,获取的的这个sql就是子查询
可以用在什么地方:
1. 作为select的内容;
2. 作为where条件的一部分;
3. 作为having的一部分
4. 作为结果集(子查询作为一张表);
5. pg的子查询要起别名；
9254
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
