sql 去除空格_SQL语言（一）

【023】

一. SQL 语言基础

1.1 什么是 SQL 语言

a) SQL, Structured Query Language, 结构化查询语言

b) SQL 是最重要的关系数据库操作语言，是所有关系数据库管理系统的标准语言.

c) SQL 语言是非过程化的语言, 只需要告诉做什么, 不需要关注怎么做, 简单.

1.2 SQL 语言可以做什么

a) 增删改查(CRUD)

b) 操作数据库对象(用户, 表格, 序列, 索引...)

c) 操作用户权限和角色的授予跟取消

d) 事务(Transaction)管理

1.3 SQL 语言的分类

a) DQL, Data Query Language, 数据查询语言，执行数据库的查询操作, select
b) DML, Data Manipulation Language, 数据操作，执行增删改, insert, delete,update
c) DDL, Data Definition Language, 数据定义语言，用于操作数据库对象, create, alter, drop
d) DCL, Data Control Language, 数据控制语言，操作用户权限, grant, revoke
e) TCL, Transaction Control Language, 事务控制语言, commit, rollback

scott自带表格：

二. select 子句

select子句用于指定查询表格中的列信息

1.1 通配符*

a)查询 emp 表格的所有数据

* 通配符, 表示所有的列    select * from emp;

1.2 指定列

a) 查询所有员工的编号, 姓名和职位
select empno, ename, job from emp;

1.3 支持算数运算

a) 查询所有员工的姓名, 职位和年薪
select 子句中, 支持算数运算  select ename, job, sal*12 from emp;

1.4 列别名

a) select 子句中, 可以通过 as 关键字给列起别名

select ename, job, sal*12 as nianxin from emp;

b)as 关键字可以被省略, 一般都省略

select ename, job, sal*12 nianxin from emp;

c)别名中, 尽量不要使用特殊符号, 例如空格; 如果非要有特殊符号, 可以使用双引号括起来.

d)在 Oracle 中, 双引号表示原样输出.

select ename, job, sal*12 "ni a n xin" from emp;

三、distinct子句和字符串连接符

1. distinct 用于去除重复行信息

a)查询所有的职位信息

select distinct job from emp;

b)查询所有员工的姓名和职位

distinct 只能去除重复行, distinct 的作用范围是它后面的所有列

select distinct ename, job from emp;

2. 字符串连接符 ||

Oracle 中, 用单引号表示字符串

a) 查询所有员工的姓名, 职位和薪资, 以姓名:xxx, 职位:xxx, 薪资:xxx 的形式显示

select '姓名:'||ename||',职位:'||job||',薪资:'||sal info from emp;

3.order by 子句

用于进行排序, 永远写在语句的最后

a)查询所有员工的信息, 按照工资升序排序

 asc, 表示升序(ascend)
 默认情况下, 按照升序排序, 所以, asc 一般被省略
select * from emp order by sal asc;

b)查询所有员工的信息, 按照标号降序排序

 desc, 表示降序排序(descend)
select * from emp order by empno desc;

c)查询所有员工的信息, 按照入职日期降序排序

select * from emp order by hiredate desc;

d)查询所有员工的信息, 按照姓名排序

select * from emp order by ename;

e)查询所有员工信息, 按照薪资降序排序, 如果薪资相同,将新员工排在前面.

select * from emp order by sal desc, hiredate desc;

f)查询所有员工的姓名和年薪, 按照年薪排序

select ename, sal*12 nianxin from emp order by nianxin desc;

四、where子句（等值条件和非等值条件）

1. where 子句：用于进行条件过滤

1.1 等值条件

a) 查询 10 部门所有员工的信息

select * from emp where deptno=10;

b) 查询 SCOTT 的详细信息

select * from emp where ename=scott;✖

字符串必须加单引号, 而且大小写敏感

select * from emp where ename='SCOTT';

c) 查询在 1982-01-23 入职的员工信息

> 日期必须用单引号括起来;

> 日期格式必须是: DD-MM 月-YY

select * from emp where hiredate='23-1 月-82';

1.2 非等值条件

a) 查询工资在 1500 到 3000 之间的员工信息

> between...and...表示一个范围, 包含边界

select * from emp where sal>=1500 and sal<=3000;
select * from emp where sal between 1500 and 3000;

b) 查询 SCOTT 和 KING 的详细信息

select * from emp where ename='SCOTT' or ename='KING';
select * from emp where ename in ('SCOTT', 'KING');

c) 查询所有员工的信息, 排除 20 部门

select * from emp where deptno != 20;
select * from emp where deptno <> 20;

五、where子句（模糊查询）

1. 模糊查询

使用 like(像)实现, 配合通配符实现

_, 表示任意一个字符

%, 表示任意个任意字符

a)查询姓名首字母为 A 的员工的信息

select * from emp where ename like 'A%';

b)查询姓名第二个字母是 A 的员工的信息

select * from emp where ename like '_A%';

c)查询姓名中带有字母 C 的员工的信息

select * from emp where ename like '%C%';

d)查询姓名中带有下划线的员工的信息

 escape 用于声明转义字符.
select * from emp where ename like '%a_%' escape 'a';

六、where子句（is null和优先级）

1. IS NULL

用于判断空值

a) 查询所有没有提成的员工信息

select * from emp where comm is null;

b) 查询所有有提成的员工信息

select * from emp where comm is not null;
select * from emp where not comm is null;

2. and 和 or 的优先级

and 的优先级高于 or 的优先级

a) 查询所有CLERK的信息和工资大于1250的SALESMAN的信..

select * from emp where job='CLERK' or job='SALESMAN'and sal>1250;

b) 查询所有的 CLERK 和 SALESMAN 的信息同时工资要大于1250.

select * from emp where (job='CLERK' or
job='SALESMAN') and sal>1250;

七、函数

1. 字符函数

1.1 lower, upper, initcap

a) lower, 将所有字母小写

b) upper, 将所有字母大写

c) initcap, 所有单词首字母大写

查询所有员工的姓名, 分别显示小写, 大写和首字母大写

select ename, lower(ename), upper(ename),
initcap(ename) from emp;

在 Oracle 中, 提供了一个虚拟表格, 叫 dual, 专门用于进行测试, 可以从 daul 中查询任何数据

1.2 length 用于计算字符串的长度

a) 查询所有员工的姓名及姓名的长度

select ename, length(ename) from emp;

b) 查询所有姓名长度为 5 的员工的信息

select * from emp where length(ename)=5;

1.3 replace 用于进行字符串的替换

a) 查询所有员工的姓名, 将 A 替换为 a

select ename, replace(ename, 'A', 'a') from emp;

1.4 substr 用于进行字符串的截取

a) 查询所有员工的姓名, 并显示姓名的前 3 个字母

select ename, substr(ename, 1, 3) from emp;

b) 查询所有员工的姓名, 并显示姓名的后 3 个字母

select ename, substr(ename, length(ename)-2, 3) from emp;

第三个参数可以省略, 表示一直截取到末尾, 所以可以简写为:

select ename, substr(ename, length(ename)-2) from emp;

第二个参数可以是负数, 表示倒着截取, 又可以简写为:

select ename, substr(ename, -3) from emp;

2. 数值函数

2.1 ceil 向上取整

select ceil(123.0000001) from dual;

2.2 floor 向下取整

select floor(123.9999999) from dual;

2.3 round 四舍五入

第二个参数可以控制四舍五入的位数, 正数表示小数点后, 负数表示小数点前.

select round(156.9444449, -2) from dual;

2.4 trunc 截断

select trunc(123.456, 1) from dual;

3 日期函数

3.1 sysdate 取系统时间

select sysdate from dual;

3.2 months_between 计算两个日期间的月数

a) 查询所有员工的入职的月数

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

3.3 add_months 给日期加减月数

select sysdate, add_months(sysdate, -5) from dual;

3.4 last_day 计算给定日期所在月份的最后一天是哪个日期

select sysdate, last_day(sysdate) from dual;

3.5 next_day

基于给定日期计算下个给定的星期几是什么日期

select sysdate, next_day(sysdate, '星 期 二 ') from dual;

4. 转换函数

用于在不同数据类型间进行转换. 数值类型, 字符串类型, 日期类型

1.1 to_number 将字符串转换为数字. 涉及到钱的时候,

$123,123,123.00到123123123.00
select to_number('￥123,123,123.00','L999,999,999.00')+1 from dual;

4.2 to_date 将字符串转换为日期

select to_date('2022-12-12 23:20:20', 'YYYY-MM-DD
HH24:MI:SS') from dual;

4.3 to_char 将数字或日期转换为字符串

select to_char(1231231231, 'L999,999,999,999.99') from dual;
select sysdate, to_char(sysdate, 'yyyy"年"mm"月"dd"
日" hh24:mi:ss') from dual;

5. 通用函数

5.1 nvl 用来处理空值, 如果某个字段为空, 则使用对应的数据进行替换

注意: 两者的数据类型必须一致

查询所有员工的姓名, 工资, 提成和总工资(工资+提成)

select ename, sal, comm, sal+nvl(comm, 0) total from emp;

5.2 nvl2 有三个参数, 如果第一个参数不为空, 则使用第二个参数, 如果为空,则使用第三个参数

select ename, sal, comm, nvl2(comm, sal+comm, sal) total from emp;

5.3 decode 类似于 switch...case...,

查询所有的职位, 并显示对应的中文描述

select distinct job, decode(job, 'CLERK', '职员',
'SALESMAN', '销 售 ', 'PRESIDENT', '董 事 长 ',
'MANAGER', '经理', 'ANALYST', '分析师') job_zh from emp;

6. 分组函数(聚组函数)

6.1 sum 求和

查询所有员工的工资总和  ：select sum(sal) from emp;

6.2 avg 求平均值

查询平均工资 ：      select avg(sal) from emp;

6.3 max 求最大值

统计公司的最高工资：select max(sal) from emp;

6.4 min 求最小值

统计公司的最低工资：select min(sal) from emp;

6.5 count 计数

统计公司的员工总数

select count(empno) from emp;
select count(*) from emp;

八、分组查询

1. group by 进行分组查询, group by 子句可以将数据分为若干个组

1.1 分组查询

注意: 出现在 SELECT 子句中的字段，如果不是包含在多行函数中，那么该字段必须同时在 GROUP BY 子句中出现。

a) 统计每个部门的编号, 最高工资和最低工资

select deptno, max(sal), min(sal) from emp group by
deptno order by deptno;

1.2 带 where 的分组查询

注意: group by 子句要写到 where 子句的后面

a) 查询每个部门的人数和平均工资, 排除 10 部门

select deptno, count(*), avg(sal) from emp where
deptno<>10 group by deptno order by deptno;

1.3 带 having 的分组查询

注意:

> where 子句中不允许使用分组函数, 用于分组前过滤

> having 用于过滤分组后的条件

a) 查询每个部门的总工资和平均工资, 排除平均工资低于1600 的部门

select deptno, sum(sal), avg(sal) from emp group by
deptno having avg(sal)>=1600 order by deptno;

1.4 select 语句的执行顺序

from-where-group by-select-having-order by

a)在 emp 表中，列出工资最小值小于 2000 的职位

select job, min(sal)
from emp
group by job
having min(sal)<2000;

b)列出平均工资大于 1200 元的部门和工作搭配组合

select deptno, job, avg(sal)
from emp
group by deptno,job
having avg(sal)>1200
order by deptno;

九. 数据操纵语言（DML）

1.1 复制一个测试表格

a) 复制 emp 表格, 命名为 tmp

create table temp as (select * from emp);

b) 赋值 emp 表格的表结构, 不复制数据

create table temp2 as (select * from emp where 1=2);

1.2 新增(insert)

a) 语法

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

b) 向 temp2 表格插入一条数据

insert into temp2 (empno, ename, job, mgr, hiredate,
sal, comm, deptno) values (1234, '小明', '学生', 1111,
to_date('2008-8-8', 'yyyy-mm-dd'), 3000, 200, 10);

a)当表格的每一列都要插入数据时, 可以省略列名不写. 需要保证值的顺序和列的顺序一致.

insert into temp2 values (1235, '小红', '学生', 1111,
to_date('2008-8-8', 'yyyy-mm-dd'), 3000, 200, 10);

b)向表格插入一行数据, 只有编号和姓名

insert into temp2 (empno, ename) values (1236, '小李');

1.3 修改(update)

a) 语法

update 表名 set 列 1=值 1[, 列 2=值 2...][where 条件]

b) 将 10 部门员工的工资调高 10%.

update temp2 set sal=sal*1.1 where deptno=10;

1.4 删除(delete)

a) 语法

delete [from] 表名 [where 条件];

b) 删除编号为 1234 的员工信息

delete from temp2 where empno=1234;

十、SQL99内连接、外连接和自连接

1. cross join(交叉连接)

交叉连接会产生一个笛卡尔积

select * from emp cross join dept;

在笛卡尔积中, 有很多数据是无意义的, 所以需要消除, 可以通过 where 子句来消除

select * from emp cross join dept where
emp.deptno=dept.deptno;

可以在查询时, 给表格起别名

select e.*, dname, loc from emp e cross join dept d
where e.deptno=d.deptno;

2. natural join(自然连接)

natural join 用于针对多张表的同名字段进行等值连接

select * from emp e natural join dept d;

特点:

a)在自然连接时, 自动进行所有同名列的等值连接, 不需要写连接的条件

b)同名列只显示一列, 而且在使用时, 不能加表前缀

查询所有员工的姓名, 部门编号和部门名称

select e.ename, deptno, d.dname from emp e natural join dept d

1.using 子句

用来指定进行等值连接的同名字段, 针对自然连接提供的.同名字段依然不能使用表前缀.

a)查询 20 部门员工的姓名, 工资, 部门编号和部门名称

select e.ename, e.sal, deptno, d.dname from emp e
natural join dept d where deptno=20;
select e.ename, e.sal, deptno, d.dname from emp e join
dept d using (deptno) where deptno=20;

2. on 子句

on 子句可以被用来指定连接的条件. 用于将过滤条件和关联条件分开.

a)查询所有员工的姓名, 工资和工资等级

select e.ename, e.sal, s.grade from emp e join
salgrade s on e.sal>=s.losal and e.sal<=s.hisal;
select e.ename, e.sal, s.grade from emp e join
salgrade s on e.sal between s.losal and s.hisal;

b)查询 30 部门员工的编号, 姓名, 部门名称和所在地

select e.empno, e.ename, d.dname, d.loc
from emp e
join dept d
on e.deptno=d.deptno
where d.deptno=30;

c)查询所有员工的姓名, 部门名称, 工资及工资等级

N 张表连接, 至少需要 N-1 个连接条件

select e.ename, d.dname, e.sal, s.grade
from emp e
join dept d
on e.deptno=d.deptno
join salgrade s
on e.sal between s.losal and s.hisal;

3. 外连接

外连接除了能显示满足连接条件的数据以外, 还用于显示不满足连接条件的数据

3.1 左外连接

left [outer] join, 表示左外连接, 可以显示左表中不满足连接条件的数据
select e.ename, e.job, d.deptno, d.dname, d.loc
from dept d
left join emp e
on e.deptno=d.deptno;

3.2 右外连接

right [outer] join, 表示右外连接, 可以显示右表中不满足连接条件的数据
select e.ename, e.job, d.deptno, d.dname, d.loc
from emp e
right join dept d
on e.deptno=d.deptno;

3.3 全外连接

full [outer] join, 表示全外连接, 可以显示左右两表中不
满足连接条件的数据
select e1.empno, e1.ename, e2.empno, e2.ename
from emp e1
full join emp e2
on e1.mgr=e2.empno
order by e1.empno;

4. 自连接

自连接是发生在同一个表格中的连接

a)查询所有员工的编号, 姓名和领导的编号及姓名

select e1.empno, e1.ename, e2.empno, e2.ename
from emp e1
join emp e2
on e1.mgr=e2.empno
order by e1.empno;

b)查询所有员工的编号, 姓名和领导的编号及姓名, 并显示没有领导的员工信息

select e1.empno, e1.ename, e2.empno, e2.ename
from emp e1
left join emp e2
on e1.mgr=e2.empno
order by e1.empno;