SQL命令及MariaDB(二)

综合练习(1)

1. 查询工资在 1000 到 2000 之间有领导的员工信息
2. 查询有领导的员工工资按照降序排序取前三条数据
3. 查询 1 号和 3 号部门出现了哪几种不同的工作
4. 查询每个部门的人数
5. 查询每种工作有领导的员工人数
6. 查询每个部门的最低工资,最高工资和平均工资
1
2
3
4
5
6

SELECT * FROM emp WHERE manager IS NOT NULL AND sal BETWEEN 1000 AND 2000;
SELECT * FROM emp WHERE manager IS NOT NULL ORDER BY sal DESC LIMIT 0,3;
SELECT DISTINCT job FROM emp WHERE dept_id IN(1,3);
SELECT dept_id,COUNT(*) FROM emp GROUP BY dept_id;
SELECT job,COUNT(*) FROM emp WHERE manager IS NOT NULL GROUP BY job ;
SELECT dept_id,MIN(sal),MAX(sal),AVG(sal) FROM emp GROUP BY dept_id;
1
2
3
4
5
6

having

• where后面只能写普通字段条件,不能写聚合函数条件
• having关键字专门用来写聚合函数条件的,并且需要和分组group by结合使用,写在分组的后面

1. 查询每个部门的平均工资,要求平均工资大于2000
2. 查询每种工作的人数,只查询人数大于 1 的
3. 查询每个部门的工资总和,只查询有领导的员工, 并且要求工资总和大于 5400.
4. 查询每个部门的平均工资, 只查询工资在 1000 到 3000 之间的,并且过滤掉平均工资低于 2000 的
1
2
3
4

SELECT dept_id,AVG(sal) s FROM emp GROUP BY dept_id HAVING s>2000;
SELECT job,COUNT(*) c FROM emp GROUP BY job HAVING c>1;
SELECT dept_id,SUM(sal)s FROM emp WHERE manager IS NOT NULL GROUP BY dept_id HAVING s>5400; 
SELECT dept_id,AVG(sal) s FROM emp WHERE sal BETWEEN 1000 AND 3000 GROUP BY dept_id HAVING s>=2000;
1
2
3
4

子查询(嵌套查询)

• 把一条SQL语句嵌套到另一条SQL语句中,称为子查询

1. 查询工资高于2号部门平均工资的员工信息
2. 查询工资高于程序员最高工资的员工信息
3. 查询工资最高的员工信息
4. 查询和孙悟空相同工作的员工信息
5. 查询拿最低工资员工的同事们的信息 (同事指同一部门)
1
2
3
4
5

SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp WHERE dept_id=2); 
SELECT * FROM emp WHERE sal>(SELECT MAX(sal) FROM emp WHERE job='程序员');
SELECT * FROM emp WHERE sal=(SELECT MAX(sal) FROM emp);
SELECT * FROM emp WHERE job=(SELECT job FROM emp WHERE NAME='孙悟空') AND NAME<>'孙悟空';
SELECT * FROM emp WHERE dept_id=(SELECT dept_id FROM emp WHERE sal=(SELECT MIN(sal)FROM emp))AND sal!=(SELECT MIN(sal) FROM emp);
1
2
3
4
5

数值计算

1. 查询每个人的姓名,工资和年终奖(5个月的工资)
2. 给每个2号部门的员工涨薪5块钱
1
2

select name,sal,5*sal 年终奖 from emp;
update emp set sal=sal+5 where dept_id=2;
1
2

关联关系

• 创建表时,表与表存在的业务关系
• 存在哪几种关系?
• • 一对一:有AB两张表,A表中的一条数据对应B表中的一条数据,同时B表中的一条数据也对应A表中的一条数据.
    
• -一对多:有AB两张表,A表中的一条数据对应B表中的多条数据,同时B表中的一条数据对应A表中的一条数据
  
• -多对多:有AB两张表,A表中的一条数据对应B表中的多条数据,同时B表中的一条数据也对应A表中的多条数据.
  
• 如何建立关系
• • 一对一:在AB两张表中任意一张表里添加一个建立关系的字段指向另一外一张表的主键
• • 一对多:在AB两张表中,表示多的表中谈价建立关系的字段指向另外一张表的主键
• -多对多:在单独的关系表中添加两个建立关系的字段指向另外两张表的主键

关联关系

• 同时查询多张表的查询方式称为 关联查询
• 三种关联查询的方式
• • 等值连接
• • 内连接
• • 外连接

内连接

• 内连接和等值连接作用一样,查询到的是两个表之间的交集数据(有关系的数据)

insert into emp(name,sal) values('灭霸',5);
1

• 内连接格式:select 字段信息 from A join B on A.x=B.x(关联关系) where 其它条件

1.查询每个员工的姓名和对应的部门名 
2.查询工资高于2000的员工姓名,工资和部门名称
3.查询孙悟空的部门地址   
1
2
3

SELECT e.`name`,d.`name` FROM emp e JOIN dept d ON e.`dept_id`=d.`id`;
SELECT e.`name`,sal,d.`name` FROM emp e JOIN dept d ON e.`dept_id`=d.`id` WHERE sal>2000;
SELECT loc FROM emp e JOIN dept d ON e.`dept_id`=d.`id` WHERE e.`name`='孙悟空';
1
2
3

外连接

• 内连接和等值连接作用一样,查询到的两个表之间的交集数据
• 外连接查询的是一张表的全部数据和另外一张表的交集
• 格式:select 字段信息 from A left/right B on A.x=B.x where 其它条件;

1.查询所有员工姓名和对应的部门名

SELECT e.`name`,d.`name` FROM emp e LEFT JOIN dept d ON e.`dept_id`=d.`id`;
1

2.查询所有部门的工作地点,和对应的员工姓名,工作
1

SELECT loc,e.`name`,job FROM emp e RIGHT JOIN dept d ON e.`dept_id`=d.`id`;
1

综合练习(2)

1. 查询工资高于程序员平均工资的员工信息
2. 查询工作人数为1的 工作名称
3. 查询1号和2号部门中工资大于2000的员工姓名和部门名
4. 查询所有员工的名称,工资和对应的部门信息
1
2
3
4

select * from emp where sal>(select avg(sal) from emp where job='程序员');
select job from emp where job is not null group by job having count(*)=1;
select e.name,d.name from emp e join dept d on e.dept_id=d.id where dept_id in(1,2) and sal>2000;
select e.name,sal,d.* from emp e left join dept d on e.dept_id=d.id;
1
2
3
4