pgsql 两个表join还有where 应该怎么建索引

Mysql查询语句练习题

salgrade表

** dept表 **

** emp表 **

一、单表查询

• 查询员工号，姓名，月薪

SELECT empno,ename,sal FROM emp

** select中的null值处理**
查询员工号，姓名，月薪，年薪，奖金，总收入
【空值的处理函数】
ifnull(表达式1,表达式2) //如果表达式1的值不为null，返回表达式1，否则返回表达式2
isnull(表达式) //如果表达式的值为null，则返回1，否则返回0

错误写法：SELECT empno,ename,sal,sal*12 as 年薪,comm,sal*12+comm as 总收入 from emp
正确写法：SELECT empno,ename,sal,sal*12 as 年薪,comm,sal*12+IFNULL(comm,0) as 总收入 from emp

【字符串处理函数】
substr(s, begin, len) //截取字符串s从begin开始的len个字符，begin从1开始
concat(s1, s2，s3, …) //字符串的连接
distinct关键字—去除重复的行
– 查询员工的不同职务

SELECT DISTINCT(job) from emp

模糊查询—like
查询名字是4个字母的员工
SELECT * from emp where ename like ‘____’
_表示单个任意字符
#表示多个任意字符

SELECT * from emp where ename like '____'

日期时间查询
查询1981年11月1日到1981年12月31日之间入职的员工

select ename from emp where hiredate BETWEEN '1981-11-1' and  '1981-12-31'

查询1981年11月以后入职的员工

SELECT ename from emp where year(hiredate)=1981 and month(hiredate)>=11

集合查询
查询在10号部门和20号部门工作的员工（两种写法）

//方法一：
SELECT * from emp where deptno=10 or deptno=20
方法二：
SELECT * from emp WHERE deptno in (10,20)

null值判断—is null 和 is not null
查询没有奖金的员工

SELECT * from emp where ISNULL(comm)

查询有奖金的员工

SELECT * from emp where !ISNULL(comm)

分组函数 count sum avg max min

查询10号部门最高工资

//方法一：
SELECT max(sal) from emp where deptno=10 
//方法二：
SELECT sal from emp where deptno=10 ORDER BY sal desc LIMIT 1

查询平均工资大于2000的部门号和平均工资

//错误方法
SELECT deptno, avg(sal) from emp where avg(sal) > 2000  
//正确方法
SELECT avg(sal),deptno from emp GROUP BY deptno  having avg(sal) > 2000

【注意】select子句和having子句中可以使用分组函数，where子句中不能使用分组函数
查询每个部门的部门号，部门人数，平均工资

SELECT deptno,count(empno),avg(sal) from emp GROUP BY deptno

查询每个部门的部门号，部门人数，平均工资

SELECT dept.dname,count(emp.deptno),avg(sal)  from emp inner join dept on emp.deptno = dept.deptno  GROUP BY emp.deptno

查询每个职务的员工人数和最高工资

SELECT count(1),max(sal) from emp GROUP BY job

查询平均工资大于2000的部门名称和平均工资（需要加连接）

SELECT dname,avg(sal) from emp,dept where emp.deptno=dept.deptno GROUP BY emp.deptno having avg(sal)>2000

SQL优化策略3—尽量先使用where过滤掉不符合条件的行以后再分组
查询20号部门中工资总和超过5000的职务

SELECT job from emp WHERE deptno=20 GROUP BY job having SUM(sal)>1000

查询SALES部门中工资总和超过5000的职务（需要加连接）

SELECT job from emp,dept where emp.deptno=dept.deptno AND dept.dname='SALES' GROUP BY job having SUM(sal)>1000

在MySQL中，分组函数不能嵌套使用，只能使用子查询实现
查询所有部门中最高平均工资的部门号

SELECT deptno FROM emp having SUM(sal) LIMIT 0,1

order by子句
按工资升序（两种写法）

SELECT * from emp ORDER BY sal

按年薪降序

SELECT * from emp ORDER BY (sal*12) desc

按部门号升序，再按工资降序

SELECT * from emp ORDER BY deptno,sal desc

二、多表查询

笛卡尔积和交叉连接—行数等于两表行数之积，列数等于两表列数之和

SELECT * from emp, dept   --SQL92

SELECT * from emp cross join dept  --SQL99

自然连接—两表相同字段的等值连接

select empno, ename,sal, dname from emp, dept where emp.deptno = dept.deptno  --SQL92

select empno, ename,sal, dname from emp NATURAL join dept  --SQL99

不等值连接
查询所有员工号，姓名，月薪和所属工资等级

SELECT empno,ename,sal,grade from emp,salgrade WHERE sal BETWEEN losal and hisal

内连接
查询所有员工的姓名，月薪，所在部门编号和部门名称

select ename, sal, emp.deptno，dname from emp, dept where emp.deptno = dept.deptno    --SQL92
select ename, sal, emp.deptno, dname from emp inner join dept on emp.deptno = dept.deptno  --SQL99

【注意1】两表连接时，要明确写出两表相同字段等值连接的条件
【注意2】使用连接字段时，必须加表名的前缀，否则报错Column ‘deptno’ in field list is ambiguous
查询SALES部门中工资总和超过5000的职务

select job from emp join dept on emp.deptno=dept.deptno where dname='sales' GROUP BY job having sum(sal) > 5000

自连接—通过表的别名，将同一张表视为多张表
查询员工的姓名及其经理的姓名

select e1.empno, e1.ename, e1.mgr as bossno, e2.ename as bossname from emp as e1, emp as e2 where e1.mgr = e2.empno

SELECT e1.empno,e1.ename,e1.mgr,e2.ename FROM emp as e1,emp as e2 WHERE e1.mgr=e2.empno

三、子查询

查询SALES部门的员工

//先查出SALES部门的部门编号
  select deptno from dept where dname='sales'

//再在where子句中使用子查询
  select * from emp where deptno = (select deptno from dept where dname='sales')