数据库中常用的增删改查，多表查询

本次分享介绍数据库中最基础，也是最实用的增删改查知识。

新增数据#

insert into 表名(列1,列2...列N) values(值1,值2...值N);

commit | rollback;

commit 是提交此次操作，此次操作生效。

rollback是回滚操作，此次操作无效。

例  insert into t_class values (1, '我爱测试');

commit;

备注 字符串要加引号

删除数据#

delete from 表名 where 过滤条件

例 delete from  emp where sal > 500

删除emp表中sal大于500的所有数据

where语句是数据库中常用的语句，大致可以这样理解 

按照指定的条件进行过滤，如"单值比较运算符"：>、<、=、!=或<>、<=、>=，两边的数据类型要保持一致。  

修改数据#

 update 表名 set 列名1=值1，列名2=值2…列名N=值N where 过滤条件

例：将emp表中职工ALLEN提升为经理，工资增至$8000，奖金增加40%

update emp set job = 'MANAGER'，sal =8000，

comm=1.4*comm where ename = 'ALLEN'; 

查询数据#

个人认为查询数据是增删改查四种基本操作最难也是最有学问的一种。

先看一下最简单的查询语句

select 列1,列2,列3...列N 或 * from 表名

这是无条件查询

select 列1,列2,列3...列N from 表名 where 过滤条件 

这是有条件查询

下面介绍查询的总语句

select 列1,列2,列3...列N 或 * from 表名/(表达式)

where 过滤条件 group by 分组内容 having 组内条件 order by 排序方式 

其中分组内容是字段名 (列名)，组内条件指的是按照该分组取的组内的条件，排序方式也是字段名 (列名)。 

补充介绍#

distinct：对于指定的字段所选择的重复行，只返回一行。

例：查找出20号部门中有多少种不同的工种。

select distinct job from emp where deptno = 20

多条件过滤之逻辑运算符:not、and、or 

例：查找出全部经理 和 第10号部门秘书的有关信息。

select job,ename,sal,deptno from emp where job='MANAGER' or (job='CLERK' and deptno=10)

多值比较运算符：(not)between 值1 and 值2, in, not in 

例:查找出工资在2000到3000之间的职工姓名、工种和工资

select ename,job,sal from emp where sal between   2000 and 3000

模糊查询: like

%表示匹配任意长度的字符串

%test%:表示匹配中间字符串为test的任意长度的字符串

例：查询出名字以"MA"开头的全部职工的姓名、工种、工资和部门号

select ename,job,sal,deptno from emp where ename like 'MA%';

_表示仅能匹配一个字符

o_a:表示匹配长度为3，以字母o开头，且以字母a结尾的字符串，中间一个字符为任意字符。

select ename,job,sal,deptno from emp  where ename like 'SCO__';

is null 或 is not null

例：查找出emp表中经理号(MGR)为空的职工的有关信息

select * from emp where MGR is null;

额外说明  如果某字段为空，则要使用比较运算符"IS NULL"(非空使用"IS NOT NULL")，不能用"=NULL"或"!=NULL"表示。

讲一下刚才提到的排序方式的语句，因为今天刚好看到测试群里面有一个人问到了，自己也就回忆了一下，总结如下

ORDER BY 字段名 ASC/DESC;

字段名，指出查询出来的结果按该字段排序；选项ASC表示按升序排序，DESC表示按降序排序；默认是按升序排序。

例：计算每个销售人员的年度总报酬，并按总报酬由高到低顺序显示

select ename,job,sal,comm,12*(sal+comm) sumprice from emp  where job='SALESMAN'

order by 12*(sal+comm) DESC;

补充说明  sumprice是12*(sal+comm)表达式的一个别名，该别名在标题中使用。别名不影响列的真名，它不能在查询语句的其他任何地方使用。

单表复杂查询#

分组/聚合/统计函数： 

count(字段名)：计算指定字段为非空的行数

count(*)：计算表中的全部行数，包括重复行和空行

avg(字段名)：计算指定字段的平均值

min(字段名)：计算指定字段的最小值

max(字段名)：计算指定字段的最大值

sum(字段名)：计算指定字段的总和

警告 where 后面不能直接加这些分组函数，这是新手常犯的错误！

   select 列1,列2,列3...列N 或 * from 表名/(表达式)

where 过滤条件 group by 分组内容 having 组内条件 order by 排序方式

例：查询各工种组的年平均工资，要求每组至少在2人以上

select job,avg(sal+nvl(comm,0))*12 avgsal,count(*) from emp。group by job having count(*)>2;

这句中的nvl(comm,0)的意思是，如果奖金为空，奖金就取0，不为空，就取奖金的金额。

以上语句就是分组函数的基本语句，这是在本文中第二次提到，可见有多重要！

补充说明 有having一定要有group by，因为having是建立在组内基础的，如果都没有分组，哪来的组内条件之说呢？

多表查询#

在实际工作中，往往多个表通过主键进行关联，两个表有相同的字段，但是也有不同的字段，两个表两两相同，A与B有相同字段a，B与C有相同字段b，如何通过相同的字段查询到不同字段的值呢？通过多表查询可以做到！

个人觉得这个很重要，在工作中也会经常用到，所以分享一下

基本语句

select 表别名1.列名1,表别名1.列名2....  或 表别名1.*,表别名2.* from 表名1 表别名1,表名2 表别名2,...表名n,表别名n  where 表名1或表别名1.列名=表名2或表别名2.列名 and 表名(n-1)或表别名(n-1).列名=表名n或表别名n.列名

看上去是不是头有点晕，哈哈，仔细揣摩，你会发现很简单，只是写的有点复杂而已……

例：查找名字为"ALLEN"的职工所在的部门号、部门名和部门所在地 

select e.ename,e.deptno,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno and     e.ename='ALLEN';

子查询

select 表名.列名 from 表名 表别名1,表名 表别名2

where 表别名1.列名=表别名2.列名;

例：查找出职工号与职工的经理编号相同的职工的名字 

select e.ename employee,m.ename manager

from emp e,emp m where e.empno=m.mgr;

还有外部连接查询，多层嵌套查询等等，我就不一一讲解了，以上是工作中经常用到，也是最为实用的查询语句，记住它们，基本上能解决大多数问题，剩下不会的就百度啦。

时间不早了，最后讲一点数据库拓展知识。

拓展知识#

我提供一些关键词，如有需要，自行了解

伪列 rowid

序号排序 rownum 通常应用在查询某表具体哪几行的数据

分页查询

索引的概念 索引就是一本书的目录，提升查询数据的效率。

视图的概念 视图就是一张临时表，不能往视图中插入、修改、删除数据，只能查询数据

对于以上有疑问的地方 请添加本人微信361891084或者QQ 

如需了解更多软件测试基础知识，微信搜索 公众号 AI测 

一起学习，共同进步！