pgsql学习

--求所有人的薪水的总和，平均值，最大值，最小值 
select sum(sal) , avg(sal), max(sal) , min(sal) from emp;
--求总的行数 
select count(*) from emp;
--求总的行树，（可以指定具体的字段）但如果字段有null值的时候需要小心使用 
select count(comm) from emp; 
--也可以过滤掉重复的行之后统计行数 
select count(distinct deptno) from emp; 

--可以指明按照哪个字段进行分组.比如；分部门统计最高薪水 
select deptno, max(sal) from emp group by deptno; 


--也可以按照多个字段来分组统计，比如：分部门和岗位，统计最高薪水和行数 
select deptno, job , max(sal), count(*) from emp group by deptno, job; 
//统计每个部门数量大于1的部门
select empno,count(*)  from emp tt group by empno HAVING count(*)>1 ; 


-也可以按照多个字段来分组统计，比如：分部门和岗位，统计最高薪水和行数 
select deptno, job , max(sal), count(*) from emp group by deptno, job; 

 

--求ascii码对应的字符 
select chr(65) from emp; select chr(97) from emp; 
--求字符对应的ascii码 
select ascii('中') from emp; 
--四舍五入 select round(23.652) from emp; 
--四舍五入小数点后面多少位 
select round(23.652, 1) from emp;
 --四舍五入小数点前面多少位 
select round(23.652, -1) from emp;

 

--null的用法 与mysql类似 --求没有年终奖的员工 select ename from emp where comm is null; --求有年终奖的员工 select ename from emp where comm is not null;

 

or not的用法 --求薪水大于1000或者部门在10这个部门的员工信息
 select * from emp where sal > 1000 or deptno = 5;
 --求薪水不是800或者不是1500或者不是3000的员工信息 与mysql <>不等于表达一样 
select * from emp where sal <> 800 and sal <> 1500 and sal <> 3000;
 --也可以这样来写 与mysql not in 
select * from emp where sal not in (800, 1500, 3000);

 

select 语法使用案例
SELECT * from emp; --每个人的部门编号，姓名，薪水 
select empno, ename, sal from emp; 
--每个人的年薪 select ename, sal*12 from emp; 
SELECT 2*3 from emp; --得到当前时间 SELECT sysdate ; 
--可以给列起别名,比如求每个人的年薪 
SELECT ename,sal*12 annn FROM emp;
 --如果别名中有空格，需要用双引号 
select ename, sal*12 "an nn" from emp;
 --如果没有内容，则为空 SELECT ename,sal,empno FROM emp; 
SELECT ename,empno,sal * 12 + comm FROM emp;
 --可以将多个字符串拼在一起。比如：求每个人的薪水，
格式为
smith-sal-123 
select ename || '-' || sal || '-' || comm from emp; 
SELECT ename || '+' || sal || '+' ||comm from emp; 
--如果字符串中有单引号，需要用另外一个单引号转义，比如：这样一个字符串：
 he's friend 
select ename || 'he''s friend' from emp;

 

--DML--Data Manipulation Language--数据操作语言 
query information (SELECT), 
add new rows (INSERT), 
modify existing rows (UPDATE),
 delete existing rows (DELETE), 
融合 perform a conditional update or insert operation (MERGE),
 see an execution plan of SQL (EXPLAIN PLAN), 
and lock a table to restrict access (LOCK TABLE). 

--DDL--Data Definition Language--数据定义语言
 create, modify,drop, or rename objects (CREATE,ALTER,DROP,RENAME),
 删除数据而不改变结构 remove all rows from a database object without dropping the structure (TRUNCATE), 
manage access privileges (GRANT,REVOKE), 
审计 audit database use (AUDIT,NOAUDIT) 
and add a description about an object to the dictionary (COMMENT). 
--Transaction Control事务控制语句 
save the changes(COMMIT) or discard the changes (ROLLBACK) made by DML statements. 
事务回滚点 Also included in the transaction-control statements are statements to set a point or marker in the transaction for possible rollback (SAVEPOINT) 
开启事务 and to define the properties for the transaction (SET TRANSACTION). Used to manage the properties of the database.
 这个类别中只有一条语句(ALTER SYSTEM)。 There isonly one statement in this category (ALTER SYSTEM). 
--DCL--Data Contro Language--与开发关系不是很密切,用于权限的分配与回收 
grant,revoke,data control --Session Control control the session properties (ALTER SESSION) and to enable/disable roles (SET ROLE).
 --System Control

 

select语法使用案例：

SELECT * from emp;

--每个人的部门编号，姓名，薪水 
select empno, ename, sal from emp;
--每个人的年薪 
select ename, sal*12 from emp; 

SELECT 2*3 from emp;
--得到当前时间 
SELECT sysdate ;

--可以给列起别名,比如求每个人的年薪 
SELECT ename,sal*12 annn FROM emp;
--如果别名中有空格，需要用双引号 
select ename, sal*12 "an nn" from emp; 
--如果没有内容，则为空 
SELECT ename,sal,empno FROM emp;
SELECT ename,empno,sal * 12 + comm  FROM emp;
--可以将多个字符串拼在一起。比如：求每个人的薪水，格式为smith-sal-123 

select ename || '-' || sal || '-' || comm from emp; 
SELECT ename || '+' || sal || '+' ||comm  from emp;

--如果字符串中有单引号，需要用另外一个单引号转义，比如：这样一个字符串： he's friend 
select ename || 'he''s friend' from emp; 

 

 

--distinct  去重 关键词的用法 

--求有哪些个部门 
select deptno from emp; 
--可以用来修饰多个字段 
--求有哪些个部门和job的组
SELECT deptno,job from emp;
select distinct deptno , job from emp; 

 

where关键字的用法
与mysql不同的是字符串也可参与比较大小
--字符串也可以做不等值判断，比如：求所有ename大于'CBA'的员工信息。 

select ename from emp where ename > 'baio5'; 

--求部门不是10的部门 
select * from emp where deptno <> 5; 

--where...in..的用法。比如：求薪水是800或者1500或正2000的员工信息   与mysql语法类似
select ename, empno, sal from emp where sal in (800, 1500, 2000);

--相当于写成这样 
select ename, empno , sal from emp where sal = 800 or sal = 1500 or sal = 2000; 
日期和mysql一样可以类似字符串表达
select ename, hiredate from emp where hiredate = '2021-04-27 15:59:48'; 

 

 

--like的用法 
--求名字中包含ALL这三个字符的员工信息 
select ename from emp where ename like '%biao%';
--求名字中的第二个字母是A的员工 
select * from emp where ename like '_a%'; 

--特殊字符需要转义。比如：求员工中包含特殊字符%的员工信息 
select ename from emp where ename like '%\%%' escape '\'; 

 

--order by的用法 
--员工信息按照姓名正序排列 
select ename, sal from emp order by ename asc; --ascent 
--员工信息按照倒叙排列 
select ename, sal from emp order by ename desc; --descent 
--也可以是多个字段组合排列。例如：员工信息按照部门正序排列，并且按照姓名倒叙排列 
select ename, sal, deptno from emp order by deptno asc, ename desc; 

 

--function的用法 

--把所有姓名变成小写 
select lower(ename) from emp; 
--把所有姓名变成大写 
select upper(ename) from emp; 

--把所有姓名变成大写 
select upper(ename) from emp; 

--截取子字符串，比如求Hello的一部分    从第二个位置截取，不包括2，开始索引是1，和java字符串的区别于此
select substr('Hello', 2) from emp;

--求Hello的一部分，并指明长度 
select substr('Hello', 2, 2) from emp; 

 

--important!日期转换函数   这里是mysql不同的地方

--important!日期转换函数 
-------------------------------------------------------- 
--将当前日期转换成1981-03-12 12:00:00这种形式的字符串 
select to_char(hiredate, 'YYYY-MM-DD HH24:MI:SS') from emp; 
--将1981-03-12 12:00:00字符串转换成日期 
select to_date('1981-03-12 12:00:00', 'YYYY-MM-DD HH24:MI:SS') from emp; 
--将每个人的薪水转换成固定格式的字符串 
select to_char(sal, 'L00,000.9999') from emp; 
--将固定格式的字符串转换成数值 
select to_number('$1,250.00', '$9,999.99') from emp; 

--null当null参与计算时候，需要要coalesce这个函数 
select ename, sal*12+comm from emp; 
select ename,coalesce(comm,0) from emp

 

--having从句的用法 
--求平均薪水是2000以上的部门 
select avg(sal), deptno from emp group by deptno having avg(sal) > 2000; 
--求最高薪水是2000以上的部门 
select MAX(sal), deptno from emp group by deptno having MAX(sal) > 2000; 

 

-------------------------------------------------------- 
--总结一下select语法 
select 
from 
where 
group by 
having 
order by 
-------------------------------------------------------- 
-- 执行顺序very important! 
-- 首先执行where语句将原有记录过滤； 
-- 第二执行group by 进行分组； 
-- 第三执行having过滤分组； 
-- 然后将select 中的字段值选出来； 
-- 最后执行order by 进行排序； 

匹配模式符“%”可以匹配任意0到多个字符，“_”表示匹配任意1个字符  使用like用法时与mysql相似

 select  综合练习
--要求每位雇员的薪水等级 -
select ename, sal, grade from emp, salgrade where emp.sal >= salgrade.losal and emp.sal <= salgrade.hisal; 
select ename, sal, grade from emp e, salgrade s where e.sal between s.losal and s.hisal; 

--求工作职位是’PRESIDENT’的雇员姓名，部门名称和薪水等级时 
select ename, dname, grade 
from emp e, dept d, salgrade s 
where e.deptno = d.deptno and e.sal between s.losal and s.hisal 
and job = 'PRESIDENT'; 

--求每位员工的姓名，及其上级经理的姓名
select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno;

--新语法 
--在SQL1992的语法规则中，语句过滤的条件和表连接的条件都被放在了where子句中，当条件过多时，容易造成混淆， 
--SQL1999修正了这个缺点，将连接条件和数据过滤条件区分开来， 

--交叉连接   
--结果会产生这两张表的笛卡尔乘积 
select ename , dname from emp cross join dept; --inner join 

--要用deptno作为等值连接条件，我们可以这样写   字段相同  直接使用use
select ename, dname from emp join dept using(deptno); 
--相当于 
select ename, dname from emp join dept on emp.deptno = dept.deptno; 
--也可以写成这样 
select ename, dname from emp join dept on (emp.deptno = dept..deptno); 

--也可以用于非等值连接 
--求每位雇员的薪水等级 
select ename, sal, grade from emp 
join salgrade on (emp.sal >= salgrade.losal and emp.sal <= salgrade.hisal); 

--多个join,where组合使用 
--(求工作职位是’PRESIDENT’的雇员姓名，部门名称和薪水等级时) 
select ename, dname , grade from emp e 
join dept d on (e.deptno = d.deptno) 
join salgrade s on (e.sal between s.losal and s.hisal) 
where job = 'PRESIDENT'; 

--外连接--取出表中连接不到一起的多余的数据 
--左有内外连接，没有全内连接，没有右内连接 

--其中outer也可以省略，简写为left join , right join , full join 
--left inner join可以缩写成inner join 也可以缩写成join，意思是左内。 
--update emp set deptno=null where ename='SMITH'; 
--commit; 
--左内 
select dname,ename from emp left inner join dept using(deptno) 
--左外连接 
select ename,dname from emp left outer join dept using(deptno) 
--右外连接 
select ename,dname from emp right outer join dept using(deptno) 
--全外连接 
select ename,dname from emp full outer join dept using (deptno); 
--左外,右外的区别 
左内连接
select e1.ename,e2.ename from emp e1 join emp e2 on(e1.mgr = e2.empno); 
左外连接
select e1.ename,e2.ename from emp e1 left outer join emp e2 on(e1.mgr = e2.empno); 
右外连接
select e1.ename,e2.ename from emp e1 right outer join emp e2 on(e1.mgr = e2.empno); 
全外连接
select e1.ename,e2.ename from emp e1 full outer join emp e2 on(e1.mgr = e2.empno); 

左查询和右查询和mysql类似

 

--在Where语句中使用子查询 
----------------------------------------------------------------- 
--雇员中最高薪水的人员名称 
--1,先求出最高薪水 
--2,再求雇员中最高薪水的人员名称 
select ename from emp where sal = ( 
select max(sal) from emp 
); 

--有哪些人的薪水是在整个雇员的平均薪水之上的 
select empno, ename from emp where sal > (select avg(sal) from emp); 

--雇员中哪些人是经理人 
--1,首先查询mgr中有哪些号码 
--2,再看有哪些人员的号码在此出现 
select ename from emp where empno in ( 
select distinct mgr from emp where mgr is not null 
);