软测学习知识小记——数据库4

2024-08-15软测学习今日总结

---

小记

将多表查询的自连接学习完，DQL查询部分就接近尾声了，接下来简略学习DML数据操纵语言。

---

一、多表查询中的自连接

自连接：在进行多表查询的时候，同一张表与它自己进行的连接查询
在进行自连接的时候需注意：
1.自连接本质上是将一张表当作了多张不同的表来进行使用
2.在进行自连接的时候必须给表起别名（起别名的作用是标识表的身份）

1. 多表查询中的等值连接
   等值连接：在进行多表查询的时候，匹配条件是使用“=” 进行关联的

2. 多表查询中的非等值连接
   非等值连接：在进行多表查询的时候，匹配条件不是使用“=”进行关联的

3. 多表查询中的自然连接（不要使用-查询结果不靠谱）
   自然连接：在进行多表查询的时候，不用指定匹配条件，自动根据表中相同的字段进行等值连接

习题练习：
练习：查询员工信息表中员工编号，姓名以及他领导的编号和领导姓名

二、窗口函数

rank() 与dense_rank（）以及row_number()的区别
1. rank():值相同的时候，排名相同，排名不连续
2. dense_rank():值相同的时候，排名相同，排名连续
3. row_number（）：当分数相同的时候还会依次进行排序，排名不同
用法示例：select empno,ename,sal,rank() over(order by sal desc)as'排名' from emp;
习题练习：
练习：查询员工信息表中员工的编号,姓名,工资,以及工资在公司的排名

三、子查询

子查询就是在一个select语句中嵌套另外一个select语句
在使用子查询的时候需注意
1.子查询的sql语句需要使用括号括起来
2.子查询的结果可以当作判断条件的值来使用，也可以当作表来继续进行查询操作
3.把子查询结果当作判断条件的值来使用时，将其放置在比较运算符的右边
4.把子查询结果当作表来继续进行查询时，需给子查询起个别名，同时子查询返回字段是聚合函数得到的值，也需要给改值起个别名
5.根据子查询返回的结果不同，可以将子查询分为单行子查询、多行子查询和多列子查询
单行子查询：子查询返回的结果是一行一列的
多行子查询：子查询返回的结果是多列多行的
在进行多列子查询的时候需注意：
1.判断条件需要使用括号括起来
2.判断条件的字段数量和子查询返回的字段数量必须相等，并且一一对应之后进行等值判断

替换：（当表来使用）
select * from emp as e inner join (select deptno,max(sal) as max_sal from emp group by deptno) as dept_max_sal on e.deptno=dept_max_sal.deptno and e.sal=dept_max_sal.max_sal;

子查询：当判断条件来使用
select * from emp where (deptno,sal) in(select deptno,max(sal) from emp group by deptno);

习题练习：
示例：查询公司各部门工资最高的员工编号,姓名和部门编号以及工资信息
练习：查询员工信息表中工龄最长的员工编号,姓名,以及入职日期

四、union\union all 合并结果集

使用union或union all可以将多个select查询的内容放到一个结果集中返回
在使用 union\union all 合并结果集时需注意：
1.多个select 语句返回的字段数量要一致，并且数据类型要一一对应
2.返回的字段名称是第一个select语句的子段名称
3.union去重，union all 不去重

使用场景：
分表分库：
电商平台订单， 按月份进行存储 order_YYYMM
2023年11月份的数据 存储在 order_202311
2023年12月份的数据 存储在 order_202312
2024年01月份的数据 存储在 order_202401
2024年02月份的数据 存储在 order_202402

进入电商APP-【我的】-【全部订单】页面,默认查询过去20天的订单数据,那么在2024年2月16日打开APP-进入我的-全部订单页面是,默认查询的数据就过了order 202401和order 202402表中,因此想要查看2月份到3份的数据，就可以将两张表连接起来。

五、DML数据操纵语句

插入数据 inset
语法
inset into 表名(字段1，字段2，…,字段n) values(值1，值2，…,值n)
在插入值的时候需注意：
1.表必须存在
2.字段必须在表中存在
3.创建表时不能为空且没有默认值的字段必须在字段列表中体现
4.一般情况下，再插入数据时不用管自增字段
5.值的数量必须和字段的数量相等，并且值的数据类型也要和字段的数据类型一一对应
6.没有在字段列表中的字段，插入数据后字段的值为空或者创建表时给他设置的默认值
7.除数字类型外，字符串和日期都必须加引号
8.插入数据时，可使用null来可以为空的字段指定为null
9.插入数据时，需确保插入的数据满足创建表时给定的各种约束

insert 的特殊用法1：
语法：insert into 表名 values(值1，值2，…,值n)
表示：如果字段列表中的字段是该表的所有字段，并且字段的顺序也是创建表时的字段顺序，那么字段列表可以忽略不写

insert的特色用法2：
语法：
insert into 表名() values(值1，值2，…,值n);
表示：如果字段列表中的字段是该表的所有字段，并且字段的顺序也是创建表时的字段顺序，那么字段列表可以忽略不记

习题练习：
练习：往员工信息表emp中插入2条数据,其中一条必须指定所有字段并且都需要有值
练习：不指定字段的情况下往部门表dept中插入1条数据
练习：将emp表中工资大于30000的员工数据插入到emp_test_copy表中

总结

多表查询在数据库操作中占据重要地位。自连接允许表与其自身进行连接，常用于处理层级或树状结构数据。窗口函数则提供了强大的数据分析能力，如排名、累计和等，无需分组即可实现复杂计算。子查询作为嵌套查询，可嵌入在主查询中，作为条件或数据源，灵活处理复杂查询需求。INSERT INTO则用于向表中插入新数据，支持从其他表中选择数据插入，实现数据迁移或汇总。这些技术共同构成了数据库查询与操作的核心，提升了数据处理的效率和灵活性