SQL-增删改查一锅端

SQL-增删改查一锅端

本文以Oracle数据库做演示，大家也可以使用Mysql，Mysql与Oracle都带有出厂默认表（员工/部门… ），在Sql语句上，都是相通的
本博客前半部分选取了任意表做演示，后半部分选取了Oracle-orcl实例-Scott用户下的雇员表（emp）和部门表（dept）进行演示，这两张表是oracle默认拥有的，不需要独立创建

如果想了解下Oracle的机制与MySql有何不同，可以参考我之前的博客Oracle基本概念，当然，你说你只用Mysql，那也是完全没问题的，完全不影响以下内容的阅读与学习

说明：演示采用PL/SQL软件进行，它是针对oracle可视化sql操作的软件
Mysql推荐使用navicat

sql简介

结构化查询语言(Structured Query Language)简称SQL，用于对数据库表的增删改查

• DML(数据库操作语言)：其语句包括动词insert、update和delete，它们分别用于添加、修改、和删除表中的行 ； DML也称动作查询语言
• DDL（数据库定义语言）：其语句包含动词CREATE和DROP，在数据库中创建新表或删除表、为表加入索引等，它也是DML的一部分
• DCL（数据库控制语言）：它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户 组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。

查询

查询语法

select * |列名 from 表名

select brand_id , brand_name from Eb_Brand

别名用法

在查询的结果列中可以使用别名

select 列名 别名 ，列名 别名，...from 表

select brand_id 品牌ID, brand_name 品牌名称  from Eb_Brand

去除重复数据

Select distinct *|列名, ... from emp

select distinct * from Eb_Brand

字符串连接查询

select '品牌号是:' || brand_id '的手机，品牌名是' || brand_name from Eb_Brand

查询中的四则运算（±*/）

select brand_id*2 from eb_brand

±/就不一一举例了

限定查询(以下就不附带结果图了)

在查询操作中绝大多数操作都是有条件的查询

select *|列名 from 表 where 条件

条件：空与非空

select * from eb_brand where brand_id is not null 

select * from eb_brand where brand_id is null 

连接条件的and/or

多个条件同时满足用and

select * from eb_brand where brand_id is null and brand_name is not null

多个查询条件或满足（满足其一即可）

select * from eb_brand where brand_id is null or brand_name is  null

范例(使用scott/tiger表下的emp表进行演示)

​ 初始条件:一张雇员表

• 查询工资不大于1500和有奖金的人

select * from emp where not(sal>1500) and (comm is not null) 

• 基本工资大于1500但是小于3000的全部雇员

select * from emp where (sal>1500) and (sal<3000)

select * from emp where sal between 1500 and  3000  //等效用法between..and.. 

• 查询1981-1-1到1981-12-31号入职的雇员

select *
  from emp
 where hiredate between to_date('1980-1-1', 'yyyy-mm-dd') and
       to_date('1981-12-31 ', ' yyyy-mm-dd ')

• 查询雇员名字叫WARD的雇员

select *
  from emp
  where ename='WARD'
 #区分大小写

• 查询雇员编号是7369,7499,7521的雇员编号的具体信息

select *
  from emp
  where empno = 7369 or empno = 7499 or empno = 7521

select *
  from emp
  where empno in (7369,7499,7521)
  #or的等效用法

条件的模糊查询

输入一个关键字，把符合的内容全部的查询出来，在sql中使用LIKE语句完成。

在LIKE中使用以下两种通配符

‘%’：匹配任意长度的内容

’_‘：匹配任意一个长度的内容

• 查询出所有雇员名字中第二个字符包含’M‘的雇员

select * from emp where ename like '_M%' 

不等号

oracle中的不等号有两种形式

1. <>
2. !=

• 查询雇员编号不是7369的雇员信息

select * from emp where empno!='7369'

select * from emp where empno<>'7369'

对结果集排序

在sqk中使用order by进行排序

语法

SELECT * |列名 FROM 表名 {WEHRE 查询条件} ORDER BY 列名1 ASC|DESC，列名2 ASC|DESC
#ASC:升序排
#DESC降序排

• 查询员工的工资，从低到高（升序）

select sal  from emp order by sal asc 
#升序是默认排序，不指定也是可以的

注意：ORDER BY语句要放在sql的最后执行

单行函数

字符函数

输入字符，返回字符或数值，dual是伪表

• 小写字符转大写

select upper('smith') from dual

• 大写转小写

select lower('SMITH') from dual

• 首字符大写

select initcap('smith') from dual 

• 字符串的连接，可以使用concat 或 || （建议）

select concat('hello','world') from dual 

• 字符串的截取，使用substr ，第一个参数是源字符串，第二个参数是开始索引，第三个参数是结束索引，开始的索引使用0和1是相同的效果

select substr('helloword',1,3) from dual 

• 获取字符串的长度

select length('hello') from dual 

• 字符串替换，第一个参数是源字符串，第二个参数是被替换字符串，第三个参数是替换字符串

select replace('helloworld','world','') from dual

数值函数

• 四舍五入

select round(12.53) from dual  #结果为13

• 取整

select trunc(12.53) from dual  #结果为12

• 保留小数

select round(12.531，2) from dual  #结果为12.53
select trunc(12.531，2) from dual  #结果为12.53

• 取余（模运算）

select mod(10,3) from dual  #结果为1

日期函数

日期 – 数字 = 日期

日期 + 数字 = 日期

日期 – 日期 = 数字

• 范例：查询雇员进入公司的周数

select ename, round((sysdate-hiredate)/7) from emp 
#sysdate -->当前时间

Months_between()获取两个时间段内的月数

• 范例：查询所有雇员进入公司的月数

select ename , round(months_between(sysdate,hiredate)) from emp 

Add_Months()求几个月后的日期

select add_months(sysdate,3) from dual 
#结果为2020/2/13 18:33:07

Last_Day（）求出一个日期最后的一天

select last_day(sysdate) from dual 
#结果为2019/11/30 21:47:52

转换函数

TO_CHAR:字符串转换函数

• 查询所有雇员将年月日分开，此时可以用TO_CHAR函数来拆分，拆分时需要使用通配符

年：y 使用yyyy

月：m 使用mm

日：d 使用dd

select empno,
       ename,
       to_char(hiredate, 'yyyy') 年,
       to_char(hiredate, 'mm') 月, to_char(hiredate, 'dd') 日
  from emp

  select empno , ename,to_char(hiredate ,'yyyy-mm-dd') from emp

TO_CHAR给数字格式化

• 把雇员工资按三位用’，‘分割，在oracle中’9‘代表一位数字

select ename, to_char(sal,'99,999') from emp

• 如果在钱的前面加上国家的符号可以使用“$”代表是美元，如果要使用本地的钱的单位使用“L”

select ename , to_char(sal,'L99,999') from emp

TO_NUMBER数值转换函数

可以把字符串变成数值

select to_number('10') + to_number('10') from dual
#结果为20

TO_DATE日期转换函数

可以把字符串转换成日期类型

select to_date('1999-11-11','yyyy-mm-dd') from dual
#结果为1999/11/11

通用函数

nvl控制处理

• 查询所有雇员的底薪

select ename , sal*12+comm from emp 

我们发现有员工的底薪是空的，原因是有员工是没有奖金的，也就是comm是null，null和任何数值计算都是null，这时我们用nvi来处理

select ename , nvl(comm,0) , sal*12+nvl(comm,0) from emp 

DECODE函数

该函数类似if…else…

语法：

语法：DECODE(col/expression, [search1,result1],[search2, result2]....[default])

Col/expression:列名或表达式
Search1，search2...:用于比较的条件
Result1, result2...:返回值
如果col/expression和Searchi匹配就返回resulti,否则返回default的默认值

• 查询出所有雇员的职位的中文名

select ename,
       decode(job, 
       'CLERK','业务员',
       'SALESMAN','销售',
       '其他'
       ) from emp ;

多表查询

多表连接查询

使用一张表以上的查询称多表查询

语法

	SELECT {DISTINCT} *|列名.. FROM 表名 别名，表名1 别名
			{WHERE 限制条件	ORDER BY 排序字段 ASC|DESC...}

• 查询员工和部门表

select * from emp , dept
#dept是部门表

笛卡尔积

将两张表直接放在一起查询会产生笛卡尔积，对性能造成影响，故此我们应该进行在多表查询的基础上进行关联查询

关联查询

我们发现两张表都有一个deptno字段，它就是两张表的关联字段，两张表的关联字段一般是其中一张表i的主键，另一张表的外键

select * from emp , dept where emp.deptno = dept.deptno

这样就能避免产生笛卡尔积造成元组过多

别名

我们可以为每个表起一个别名，方便我们的关联操作

• 查询出雇员的编号，姓名，部门编号和名称，地址

select e.empno, e.ename, d.deptno, d.dname
  from emp e, dept d
 where e.deptno = d.deptno

• 查询出每个员工的上级领导

  分析：emp表的mgr字段为当前雇员的上级领导的编号，所以该字段对emp表产生了自身关联，可以使用mgr字段和empno来关联

  select e.empno, e.ename, e1.empno, e1.ename
    from emp e, emp e1
   where e.mgr = e1.empno
   #e中查出的eno和ename为领导
   #e1中查出的..为员工
  
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-axMPxml7-1573707041816)(

• 在上一张表的基础上查询该员工的部门名称

  分析：只要在上一个例子的基础上再加一张表的关联，使用deptno来做关联字段即可

select e.empno, e.ename, e1.empno, e1.ename, d.dname
  from emp e, emp e1, dept d
 where e.mgr = e1.empno
   and e.deptno = d.deptno

• 查询出每个员工编号，姓名，部门名称，工资等级和他的上级领导的姓名，工资等级

select e.empno,
       e.ename,
       decode(s.grade,
              '1',
              '一级',
              '2',
              '二级',
              '3',
              '三级',
              '4',
              '四级',
              '5',
              '五级') grade,
       d.dname,
       e1.empno,
       e1.ename,
       decode(s.grade,
              '1',
              '一级',
              '2',
              '二级',
              '3',
              '三级',
              '4',
              '四级',
              '5',
              '五级') grade,
  from emp e, emp e1, dept d, salgrade s, salgrade s1
 where e.mgr = e1.empno
   and e.deptno = d.deptno
   #雇员表与工资表的关联采用between...and来关联
   and e.sal betweeen s.losal and s.hisal
   and e1.sal between s.losal and s.hisal
   

外连接（左右查询）

右连接

当我们在做基本连接查询的时候，查询出所有部门下的员工，我们发现编号为40的部门下无员工，但是要求把该部门也展示出来，我们发现上面的基本查询是办不到的

这时我们使用(+)表示左连接或者右连接，当(+)在左边表的关联条件字段上时是左连接，如果是在右边表的关联条件字段上就是右连接。

select e.empno , e.ename , d.deptno, d.dname
from emp e, dept d 
where e.deptno(+) = d.deptno

• 范例：查询出所有员工的上级领导

  分析：我们发现使用我们以前的做法发现KING和KING的上级领导没有被展示，因为他是最高领导了，我们需要使用左右连接把他查询出来

select e.empno , e.ename , e1.empno , e1.ename 
from emp e , emp e1 
where e.mgr = e1.empno（+）

sql1999对sql的支持

Join - 交叉连接

cross join - 产生笛卡尔积

natural join - 自然连接，去除笛卡尔积，无需指定关联列

using join - 自然连接，去除笛卡尔积，需指定关联列

ON子句-自己编写连接条件

On相当于where

Join - 交叉连接

select * from emp e join dept d on e.deptno =  d.deptno

左连接和右连接LEFT JOIN 和RIGHT JOIN

select * from emp e left join dept d on e.deptno = d.deptno

select * from emp e right join dept d on e.deptno = d.deptno

分组函数

统计记录数count()

• 范例：查询出所有员工的记录数

select count(*) from emp 
#结果14

但是我不建议大家使用count(*)，会影响性能，可以使用一个具体的列

select count(ename) from emp 
#结果14

最小值查询

• 查询出员工最低工资

select min(sal) from emp
#结果800

最大值查询

• 查询出员工最高工资

select max(sal) from emp 

平均值查询

• 查询出员工平均工资

select avg(sal) from emp 

求和函数

• 查询出20部门员工的工资总和

select sum(sal) from emp e where e.deptno = 20

分组统计GROUP BY

语法：

SELECT * |列名 FROM 表名 {WEHRE 查询条件}   {GROUP BY 分组字段} ORDER BY 列名1 ASC|DESC，列名2...ASC|DESC

• 范例：查询每个部门的人数

select deptno, count(deptno) from emp group by deptno

• 范例：查询每个部门的平均工资

select deptno, avg(sal) from emp group by deptno

注意：

1. 如果使用分组函数，SQL只可以把GOURP BY分组条件字段和分组函数查询出来，不能有其他字段。

2. 如果使用分组函数，不使用GROUP BY 只可以查询出来分组函数的值

典型错误1：使用分组函数而不使用group by

分析：deptno和count(*)产生冲突，无法同时在表中呈现

典型错误2：

分析：只可以把GOURP BY分组条件字段和分组函数查询出来，不能有其他字段。*

• 范例：按部门分组、查询出部门名称和部门的员工数量

select d.deptno, d.dname, count(ename)
  from emp e, dept d
 where e.deptno = d.deptno
 group by d.deptno, dname

• 查询出部门人数大于5的部门

由于此时需要给count加条件（>5），此时不能使用where，应该使用having

select d.deptno, d.dname, count(ename)
  from emp e, dept d
 where e.deptno = d.deptno
 group by d.deptno, dname
 having count(ename)>5
 

• 范例：显示非销售人员工作名称以及从事同一工作的员工的月工资的总和，并且要满足从事同一工作月工资总和大于5000，结果按月工资总和的升序排列。

  分析：

  1. 查询非销售人员

  select * from emp e where e.job!='SALESMAN'
  

  2.以步骤1为基础按工作分组求工资的总和

  select e.job, sum(sal) from emp e where e.job != 'SALESMAN' group by e.job
  

  3. 以步骤2为基础查询出月工资总和大于5000的工作

  select e.job, sum(sal) from emp e where e.job != 'SALESMAN' group by e.job 
  having sum(sal)>5000
  
  

  4. 升序asc

select e.job, sum(sal) from emp e where e.job != 'SALESMAN' group by e.job 
having sum(sal)>5000
order by sum(sal) asc

子查询

所谓子查询，就是在一个查询的内部还包括另一个查询，在sql语句的任何一个位置都可以加入子查询

• 范例：查询比7654工资高的雇员

分析：查询出7654员工的工资是多少、把它作为条件

select t.sal from emp t where t.empno = 7654

select *
  from emp e
 where e.sal > (select t.sal from emp t where t.empno = 7654)

所有的子查询必须在“（）”中编写

子查询在操作中有三类：

单列子查询：返回的结果是一列的一个内容

单行子查询：返回多个列，有可能是一个完整的记录

多行子查询：返回多条记录

• 范例：查询出比雇员7654的工资高，同时从事和7788的工作一样的员工

select *
  from emp e
 where e.sal > (select t.sal from emp t where t.empno = 7654)
 and e.job = (select job from emp where empno = 7788)

• 范例：要求查询每个部门的最低工资和最低工资的雇员和部门名称

select d.dname , a.minsal ,e.ename
from emp e ,dept d,(select deptno, min(sal) minsal from emp group by deptno) a
where d.deptno = a.deptno
and e.sal = a.minsal

注意：在返回多条记录的子查询可以对它的结果集起别名，如图-a

• 范例：查询出每个部门最低工资的员工

  select * from emp e
  where e.sal in (select min(sal) from emp group by deptno)
  

• 范例：查询出有员工的部门有哪些

select * from dept d where d.deptno in (select distinct deptno from emp)

select * from dept d where d.deptno in (select distinct deptno from emp)
#无员工的部门

Union和Union All的用法

Union:对两个结果集进行并集操作，不包括重复行

如

select * from emp where sal > 1000
union
select * from emp where sal > 1500 

例：查询出比雇员7654的工资高，同时从事和7788的工作一样的员工

select *
  from emp e
 where e.sal > (select t.sal from emp t where t.empno = 7654)
 and e.job = (select job from emp where empno = 7788)

• 范例：要求查询每个部门的最低工资和最低工资的雇员和部门名称

select d.dname , a.minsal ,e.ename
from emp e ,dept d,(select deptno, min(sal) minsal from emp group by deptno) a
where d.deptno = a.deptno
and e.sal = a.minsal

注意：在返回多条记录的子查询可以对它的结果集起别名，如图-a

• 范例：查询出每个部门最低工资的员工

  select * from emp e
  where e.sal in (select min(sal) from emp group by deptno)
  

• 范例：查询出有员工的部门有哪些

select * from dept d where d.deptno in (select distinct deptno from emp)

select * from dept d where d.deptno in (select distinct deptno from emp)
#无员工的部门

Union和Union All的用法

Union:对两个结果集进行并集操作，不包括重复行

如

select * from emp where sal > 1000
union
select * from emp where sal > 1500 

Union All对两个结果集进行并集操作，包括重复行