黑马程序员技术交流社区
标题: 【上海校区】数据库基本操作 [打印本页]
作者: ~Maybe 时间: 2018-11-15 09:09
标题: 【上海校区】数据库基本操作
一、基本查询
1.基本语法格式
语法格式
指定按某字段进行排序，默认增序asc，降序desc
2.常见查询
2.1 select 指定展示结果集
1. #导入了3个sql文件，老师给的！
2. #1.查询emp表中所有的员工信息
3. select * from emp;
4. #2.在emp表中查询所有员工的姓名和职位信息
5. select ename, job from emp;
2.2 where指定检索条件：关系运算符、逻辑运算符、in关键词、是否空值、范围查询between and
1. #3.在emp表中查询所有的20号部门的员工信息
2. select * from emp where deptno=20;
3. #4.在emp表中查询工作大于2000的员工的姓名和工资
4. select ename, sal from emp where sal > 2000;
5. #5.在emp表中查询工作在1000到2000之间的员工信息（范围查询）
6. select * from emp where sal > 1000 and sal < 2000;
7. select * from emp where sal between 1000 and 2000;
8. #6.在emp表中查询员工编号为7788，7369，7521的员工信息
9. select * from emp where empno = 7788 or empno = 7369 or empno = 7521;
10. select * from emp where empno in (7788,7369,7521);
11. #9.在emp表中查询所有没有奖金的员工信息。（comm值为空）
12. select * from emp where comm is null;
13. select * from emp where comm is not null;
2.3 去重distinct 关键字
1. #7.在emp表中查询所有的职位信息（去重）
2. select distinct job from emp;
2.4 取别名
1. #8.在emp中查询工资提升5%后的员工姓名及工资（别名：字段、表达式、表、结果集等）
2. # 别名格式：[as] 别名
3. select ename,sal * 1.05 nsal from emp;
4. select emp.ename,emp.sal from emp;
5. select e.ename,e.sal from emp e;
2.5 排序 order by 和限制结果查询 limit
1. #10.在emp中查询工资最高的员工信息
2. #方法一：使用SQL提供的函数
3. #方法二：倒序排序取第一个
4. #排序：order by 排序的字段（默认升序，asc 升序，desc 降序）
5. #先按照工资降序排序，如果工资一样，则按照编号降序排序
6. select * from emp order by sal desc, empnodesc;
7. #限制结果查询（格式：limit 开始索引, 长度，其中索引从0开始，如果是从0开始，则索引可以不
8. #写，直接写 limit 5，表示去前5条），限制结果查询功能仅适用于mysql。
9. select * from emp order by sal desc limit 1;
2.6 模糊查询
关键字和通配符：
· 模糊查询：like
· %：代表0到多个字符
· _：代表1位字符
示例代码：
1. #11.查询名称中包含s的员工信息（模糊查询：like %：代表0到多个字符 _：代表1位字符）
2. select * from emp where ename like '%s%';
3. select * from emp where ename like 's%';
4. select * from emp where ename like '%s';
5. #查询名称第3个字符是L的员工信息
6. select * from emp where ename like '__L%';
二、高级查询
1.关联查询（也称为连接查询）
1.1 定义：查询多张表或结果集
1.2 分类：内连接和外连接
1.3 内连接
语法格式：
1. #在emp查询所有的员工及其所在部门信息
2. #语法一：
3. #where的条件相当于从笛卡尔积中进行检索
4. select * from emp,dept where emp.deptno = dept.deptno;
5. #语法二：inner join … on
6. select * from emp inner join dept on emp.deptno = dept.deptno;
7. #语法三：存在通用列，所以可以这样写
8. select * from emp inner join dept using(deptno);
特点总结：
· 多张关联表共有的记录才能出现在结果集中。
· 内连接的结果与连接顺序无关。
1.4 外连接
语法格式：
· 左外连接：left [outer] join … on …
· 右外连接：right [outer] join … on …
示例代码：
1. #查询所有的部门及其部门下的员工信息（内连接就解决不了了）
2. select * from dept left join emp on emp.deptno = dept.deptno;
3. select * from dept left join emp on dept.deptno = emp.deptno;
4.
5. #自然连接（自然连接都是等值连接，等值连接不一定是自然连接）
6. select * from emp natural join dept;
7.
8. #查询20号部门所有员工及其所在部门信息
9. select * from emp , dept where emp.deptno = dept.deptno and emp.deptno=20;
10. select * from (select * from emp where deptno = 20) e,dept wheree.deptno=dept.deptno;
11.
12. #查询所有员工及其上级领导（mgr）的名称（自连接）
13. select e1.ename '姓名', e2.ename '领导' from emp e1 left join emp e2 on e1.mgr=e2.empno;
特点总结：
· 外连接结果与连接顺序有关。
· 有主从表之分，依次遍历主表每条记录并与关联表进行匹配，若匹配成功则关联展示到结果集，若匹配失败则以null填充。
2.子查询
1.1 定义
将一个查询结果作为另一个查询的条件或组成继续进行检索。
2.2 分类
单行子查询：子查询返回的结果是一条记录
多行子查询：子查询返回的结果是多条记录
2.3 单行子查询
示例代码：
1. #查询编号为7788的员工所在部门的信息
2. #方法一：关联查询实现
3. select dept.* from (select * from emp where empno = 7788) e, dept where e.deptno=dept.deptno;
4. #方法二：子查询（或嵌套查询）实现
5. #查询7788员工的部门编号，将条件和结果放到一张表中（查找通用列）
6. select * from dept where deptno = (select deptno from emp where empno = 7788);
7.
8. #查询工资>20号部门平均工资的员工信息
9. select * from emp where sal > (select avg(sal) from emp where deptno = 20);
10. #查询工资>20号部门平均工资并且不在20号部门的员工信息
11. select * from emp where sal > (select avg(sal) from emp where deptno = 20) and deptno<>20;
2.4 多行子查询
2.4.1 关键词： any 与all
1. # 关键词：any / all：
2. # =any：相当于in >any：大于最小值 <any：小于最大值
3. # >all：大于最大值 <all：小于最小值
4.
5. #查询工资大于2000的员工所在部门信息
6. select * from dept where deptno in (select distinct deptno from emp where sal > 2000);
7.
8. #查询工资大于所在部门的平均工资的员工信息
9. #关联查询实现
10. select emp.* from emp , (select deptno , avg(sal) avg from emp group by deptno) e where
11. emp.deptno = e.deptno and sal > e.avg;
12. #子查询实现
13. #步骤：
14. #1.主查询将deptno传给子查询
15. #2.子查询根据主查询的deptno查询指定部门的平均工资
16. #3.子查询将该部门平均工资返回给主查询
17. #4.主查询根据返回结果进行最后检索。
18. select * from emp e1 where sal > (
19. select avg(sal) from emp e2 where e2.deptno = e1.deptno
20. );
2.4.2 关键词：exists
示例代码：
1. #查询工资>2000的员工所在的部门信息
2. #exists：
3. #1.将主查询的记录一次次交给子查询
4. #2.在子查询中匹配记录，如果能够找到匹配的记录，返回true，此时将展示主查询该条记录；如
5. #果不匹配则返回false，主查询该条记录不展示
6. select * from dept where exists (
7. select * from emp where sal > 2000 and emp.deptno =dept.deptno
8. );
in和exsits的区别：
· in先执行子查询，exists先执行主查询
· in需要关注子查询返回值的字段信息；exists不需要关心返回字段
· exists将主查询的记录依次在子查询中匹配，如果匹配返回true展示。
2.5 总结
什么时候使用关联查询？什么时候使用子查询？
· 当查询结果存在多张表，使用关联查询；当查询结果存在于一张表，用子查询。
· 关联查询资源消耗高，子查询资源消耗低但可能存在多次查询。
3.联合查询：合并结果集
3.1 条件
多个结果集的字段必须保持一致，才能进行联合查询
3.2 关键词
union：联合结果集并去重
union all：联合结果集但不去重
3.3 为什么使用联合查询？
理论上可以用or关键词代替联合查询，但or关键词会导致索引失效故此使用联合查询。
3.4 示例代码
1. #查询20号部门以及工资>2000的员工信息
2. select * from emp where deptno = 20 or sal > 2000;
3. select * from emp where deptno = 20 union select * from emp where sal > 2000;
4. select * from emp where deptno = 20 union all select * from emp where sal > 2000;
作者: 魔都黑马少年梦 时间: 2018-11-15 16:53
作者: 不二晨 时间: 2018-11-28 16:03
奈斯
| 欢迎光临 黑马程序员技术交流社区 (http://bbs.itheima.com/) |
黑马程序员IT技术论坛 X3.2 |