博客 SQL开发实战系列(二):简单单表查询

SQL开发实战系列(二):简单单表查询

   数栈君   发表于 2023-02-27 18:25  212  0

一、查询表中所有的行与列
进行查询操作之前, 我们先看一下表结构,我这里查询的是oracle官方案例用户scott下的表。

SQL> desc scott.emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4) 编码
ENAME VARCHAR2(10) 名称
JOB VARCHAR2(9) 工作
MGR NUMBER(4) 主管
HIREDATE DATE 聘用日期
SAL NUMBER(7,2) 工资
COMM NUMBER(7,2) 提成
DEPTNO NUMBER(2) 部门编码

如果领导要看员工所有信息。这个操作很简单,大家应该都会。只要用select*就可以返回目标表中所有的列,查询语句及执行结果如下:

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
1001 test 2021-10-9 1

15 rows selected


如果想要修改日期的显示格式,也可以通过to_char*等函数来实现,这些后面会有单独一章讲解,其次,如果你发现你通过sqlplus无法展示的如我这样一行显示完全,可以通过设置set linesize、set pagesize、col ename for a20等等设置页面格式的命令自定义,或则通过plsql的命令行访问,博主就是用的plsql命令行展示的!

二、从表中检索部分行
如果想看公司有多少销售人员,那么在查询数据时只需加一个过滤条件就可以。职位列是job,销售人员条件就是WHERE job='SALESMAN':

SQL> SELECT * FROM emp WHERE job = 'SALESMAN';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30

三、查找空值
如果要查询某一列为空的数据怎么办呢?比如,返回提成(comm)为空的数据。

SQL> SELECT* FROM emp WHERE comm= NULL ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------


从步骤1中明显能看到comm有为null的数据,为什么这里查不到呢?
原因是NULL是不能用"="运算符的,要用IS NULL判断,正确的写法如下。

SQL> select * from emp where comm is null;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
1001 test 2021-10-9 1

11 rows selected


NULL不支持加、减、乘、除、大小比较、相等比较,否则只能为空。

SQL> select * from dept where 1>=null;

DEPTNO DNAME LOC
------ -------------- -------------

SQL> select * from dept where 1<=null;

DEPTNO DNAME LOC
------ -------------- -------------

SQL> select * from dept where 1+null>=0;

DEPTNO DNAME LOC
------ -------------- -------------

SQL> select * from dept where 1+null<=0;

DEPTNO DNAME LOC
------ -------------- -------------

SQL> select * from dept where 1*null>=0;

DEPTNO DNAME LOC
------ -------------- -------------

SQL> select * from dept where 1*null<=0;

DEPTNO DNAME LOC
------ -------------- -------------

SQL>


当然了,在使用其他函数的时候最好测试一下有NULL时会返回什么结果。

SQL> select replace('abcde','b',null) from dual;

REPLACE('ABCDE','B',NULL)
--------------------------------
acde

SQL> select greatest(1,null) from dual;

GREATEST(1,NULL)
----------------

SQL>

可以看到,不同的函数对NULL的支持也不一样,所以大家遇到NULL时最好测试一下,而不是仅凭经验或猜测!

四、拿”返回个值中第一个不为空的值”证明coalesce比nvl好用
对于下面sql:

SELECT coalesce(comm,0) FROM emp;
1
可能有人会问为什么用coalesce而不用nvl?当然是coalesce更好用了!拿”返回个值中第一个不为空的值”这个需求来说:

SQL> with t as (
2 select null as a,null as b,'z' as c,null as d,'f' as e,null as f from dual
3 union all
4 select null as a,null as b,null as c,'y' as d,null as e,'a' as f from dual
5 union all
6 select null as a,null as b,null as c,null as d,'d' as e,'s' as f from dual
7 )
8 select coalesce(a,b,c,d,e,f) from t
9 ;

COALESCE(A,B,C,D,E,F)
---------------------
z
y
d

可以看到,相对nvl来说,coalesce支持多个参数,能很方便地返回第一个不为空的值。如果上面的语句改用nvl,就要嵌套很多层。

SELECT nvl(nvl(nvl(nvl(nvl(a,b),c),d),e),f) FROM t;
1
五、查找满足多个条件的行
简单的查询,写起来容易,那复杂一点的呢?
比如,我们要查询部门10中的所有员工、所有得到提成的员工以及部门20中工资不超过2000美元的员工。
这是三个条件的组合,符合上述任一条件即可。
我们把这三个条件整理成逻辑表达式的形式:(部门10中的员工OR所有得到提成的员工OR(工资<=2000and部门号=20))
那我们这么写这个需求:

SELECT *
FROM EMP
WHERE (DEPTNO = 10 OR COMM IS NOT NULL OR (SAL <= 2000 and DEPTNO = 20));

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10

9 rows selected

对于多个条件的组合,要使用括号,这样在更改维护语句时可以不必再考虑优先级问题,而且可以很容易地借助各种工具找到各组合条件的起止位置。

六、从表中检索部分列
前面我们都是取表中所有的列,但实际的场景中,常常只需要返回部分列的数据就可以。如只需员工编码、员工名称、雇佣日期、工资。所以一般要明确指定查询哪些列,而不是用"*"号来代替。另外,明确要返回的列也会使语句的维护更简单,而不必每次看到语句时都需要查看表结构才知道会返回什么数据。

SQL> SELECT empno, ename, hiredate, sal FROM emp WHERE deptno = 10;

EMPNO ENAME HIREDATE SAL
----- ---------- ----------- ---------
7782 CLARK 1981-6-9 2450.00
7839 KING 1981-11-17 5000.00
7934 MILLER 1982-1-23 1300.00

七、为列取别名
不是每个人都能看懂那些简写的字母字段什么意思,所以有时候需要给列取个别名。可以如下面所示在as后面跟别名,也可以不要as,直接在列名后跟别名即可。

SQL> SELECT ename AS 姓名, deptno AS 部门编号,sal AS 工资,comm AS 提成 FROM emp;

姓名 部门编号 工资 提成
---------- ---- --------- ---------
SMITH 20 800.00
ALLEN 30 1600.00 300.00
WARD 30 1250.00 500.00
JONES 20 2975.00
MARTIN 30 1250.00 1400.00
BLAKE 30 2850.00
CLARK 10 2450.00
SCOTT 20 3000.00
KING 10 5000.00
TURNER 30 1500.00 0.00
ADAMS 20 1100.00
JAMES 30 950.00
FORD 20 3000.00
MILLER 10 1300.00
test

15 rows selected


八、在 WHERE 子句中引用取别名的列
写报表时,经常会加上各种条件,而直接在条件中使用别名比列名(如:d001,n002)要清晰得多,引用别名时千万别忘了嵌套一层,因为这个别名是在SELECT之后才有效的.

SQL>
SQL> SELECT *
2 FROM (SELECT sal AS 工资, comm AS 提成 from emp) X
3 WHERE 工资 <1000;

工资 提成
--------- ---------
800.00
950.00

如果你不嵌套一层,会报错的!如下:

SQL> SELECT sal AS 工资, comm AS 提成 from emp
2 WHERE 工资 <1000;
SELECT sal AS 工资, comm AS 提成 from emp
WHERE 工资 <1000

ORA-00904: "工资": 标识符无效

九、拼接列实现批量脚本
若有人不喜欢看表格式的数据,希望返回的数据都像"CLARK的工作是MANAGER"这样的显示。我们可以用字符串连接符"||"来把各列拼在一起。

SQL> SELECT ename || '的工作是'|| job AS msg FROM emp WHERE deptno = 10 ;

MSG
---------------------------------------------------
CLARK的工作是MANAGER
KING的工作是PRESIDENT
MILLER的工作是CLERK

当然,拼接列还有更多意义,比如我平时对某个用户下所有表收集统计信息,我就可以这么写脚本来生成批量脚本:

select 'begin'||chr(13)||
'dbms_stats.lock_table_stats(ownname =>'''||a.OWNER||''',tabname =>'''||a.TABLE_NAME||''');'||chr(13)||'END;'||chr(13)||'/'||chr(13)||'prompt '||rownum
from dba_tables a where a.OWNER in('ZYD');
SQL> select 'begin'||chr(13)||
2 'dbms_stats.lock_table_stats(ownname =>'''||a.OWNER||''',tabname =>'''||a.TABLE_NAME||''');'||chr(13)||'END;'||chr(13)||'/'||chr(13)||'prompt '||rownum
3 from dba_tables a where a.OWNER in('ZYD');

'BEGIN'||CHR(13)||'DBMS_STATS.LOCK_TABLE_STATS(OWNNAME=>'''||A.OWNER||''',TABNAM
--------------------------------------------------------------------------------
begin
dbms_stats.lock_table_stats(ownname =>'ZYD',tabname =>'TEST_ORACLE_HIVE');
END;
/
prompt 1
begin
dbms_stats.lock_table_stats(ownname =>'ZYD',tabname =>'TEST_HIVE_ORACLE');
END;
/
prompt 2
begin


十、在 SELECT 语句中使用case when条件逻辑
有时为了更清楚地区分返回的信息,需要做如下处理。
如:当职员工资小于或等于2000美元时,就返回消息“过低“,大于或等于4000美元时,就返回消息“过高”,如果在这两者之间,就返回"OK"。
类似这种需求也许会经常遇见,处理这样的需求可以用CASE WHEN来判断转化

select ename,
sal,
CASE
WHEN sal <= 2000 THEN
'过 低'
WHEN sal > = 4000 THEN
'过高 '
ELSE
' OK '
END AS status
FROM emp
WHERE deptno = 10;
ENAME SAL STATUS
---------- --------- ------
CLARK 2450.00 OK
KING 5000.00 过高
MILLER 1300.00 过 低


这种方式还常用在报表中,比如要按工资分档次统计人数:

SELECT 档次, COUNT(*) AS 人数
from (SELECT (CASE
WHEN sal <= 1000 THEN
'0000-1000'
WHEN sal <= 2000 THEN
'1000-2000'
WHEN sal <= 3000 THEN
'2000-3000'
WHEN sal <= 4000 THEN
'3000-4000'
WHEN sal <= 5000 THEN
'4000-5000'
ELSE
'好高'
END) AS 档次,
ename,
sal
FROM emp)
GROUP BY 档次
ORDER BY 1;

档次 人数
--------- ----------
0000-1000 2
1000-2000 6
2000-3000 5
4000-5000 1
好高 1

十一、限制返回的行数
在查询时,并不要求每次都要返回所有的数据,比如,进行抽查的时候会要求只返回两条数据。
我们可以用伪列rownum来过滤,rownum依次对返回的每一条数据做一个标识。

SQL>
SQL> SELECT * FROM emp WHERE rownum<=2;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30

如果直接用rownum=2来查询会出现会什么情况?

SQL> SELECT * FROM emp WHERE rownum=2;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------

SQL>

因为rownum是依次对数据做标识的,就像上学时依据考分排名一样,需要有第一名,后面才会有第二名。所以,要先把所有的数据取出来,才能确认第二名。
正确地取第二行数据的查询应该像下面这样,先生成序号:

SQL> SELECT *
2 FROM (SELECT rownum AS sn, emp.* FROM emp WHERE rownum <= 2)
3 WHERE sn = 2;

SN EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ----- ---------- --------- ----- ----------- --------- --------- ------
2 7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30

SQL>

十二、你真的会从表中随机返回 n 条记录吗
我们这里的目标是随机返回N条记录,大多开发者在这里都会误认为自己平时随机取数据的方式对了!接下来我们一步步分析。
我们可以先用dbms_random来对数据进行随机排序,然后取其中三行。

SELECT empno,ename
FROM (select empno,ename FROM emp ORDER BY dbms_random.value()) WHERE rownum <= 3 ;

有人会问:为什么要嵌套一层呢?直接这样用多好。

SELECT empno , ename FROM emp WHERE rownum <= 3 ORDER BY dbms_random.value();

你可以运行一下看, 为了方便观察, 我们对得到的结果进行排序 , 运行下面的语句就可以。

SELECT *
FROM (SELECT empno, ename
FROM emp
WHERE rownum <= 3
ORDER BY dbms_random.value())
ORDER BY 1;

多运行几次,会发现是不是每次得到的数据都一样,而不是随机?为了便于解释,我们先对上面的语句进行等价改写:

SELECT empno, ename, dbms_random.value() ran
FROM emp
WHERE rownum <= 3
ORDER BY ran;

查询语句中这几处的执行顺序为:

SELECT
ROWNUM
ORDER BY
也就是说,要先取出数据,然后生成序号,最后才是排序。我们可以通过子查询把排序前后的序号分别取出来对比。

SELECT rownum AS 排序后, 排序前, empno AS 编码, ename 姓名, ran AS 随机数
FROM (SELECT rownum AS 排序前, empno, ename, dbms_random.value() ran
FROM emp
WHERE rownum <= 3
ORDER BY ran);

同样,你可以运行几次,看是不是与刚才描述的一致。因此,正确的写法是:先随机排序,再取数据。

SELECT empno, ename
FROM (SELECT empno, ename FROM emp ORDER BY dbms_random.value())
WHERE rownum <= 3;

错误的写法是: 先取数据, 再随机排序 。

SELECT empno , ename FROM emp WHERE rownum <= 3 ORDER BY dbms_random.value();
1
十三、模糊查询,使用escape转译字符
有如下临时表:

with t as (
SELECT 'ABCEDF' AS vname FROM dual
UNION ALL
SELECT '_BCEFG' AS vname FROM dual
UNION ALL
SELECT '_BCBPF' AS vname FROM dual
UNION ALL
SELECT '_\BCEDF' AS vname FROM dual
UNION ALL
SELECT 'XYCEG' AS vname FROM dual )
select * from t 

要求一: 查出 vname 中包含字符串 " CED " 的。

with t as (
SELECT 'ABCEDF' AS vname FROM dual
UNION ALL
SELECT '_BCEFG' AS vname FROM dual
UNION ALL
SELECT '_BCBPF' AS vname FROM dual
UNION ALL
SELECT '_\BCEDF' AS vname FROM dual
UNION ALL
SELECT 'XYCEG' AS vname FROM dual )
select * from t where vname like '%CED%';

VNAME
-------
ABCEDF
_\BCEDF

要求二:查出vname中包含字符串"BCE"的。这里我们需要注意,””在模糊查询是会被当做通配符的,“%”代表替代一个或多个字符“”替代一个字符。所以这个需求我们需要用转译字符来转译“”字符,我们可以使用escape关键字把’\’标识为转译字符,那么查询如下:

with t as (
SELECT 'ABCEDF' AS vname FROM dual
UNION ALL
SELECT '_BCEFG' AS vname FROM dual
UNION ALL
SELECT '_BCBPF' AS vname FROM dual
UNION ALL
SELECT '_\BCEDF' AS vname FROM dual
UNION ALL
SELECT 'XYCEG' AS vname FROM dual )
select * from t where vname like '\_BCE%' escape '\';
VNAME
-------
_BCEFG

要求三:查出vname中包含字符串"_\BCE"的。根据上面解释,我们可以这么写!

with t as (
SELECT 'ABCEDF' AS vname FROM dual
UNION ALL
SELECT '_BCEFG' AS vname FROM dual
UNION ALL
SELECT '_BCBPF' AS vname FROM dual
UNION ALL
SELECT '_\BCEDF' AS vname FROM dual
UNION ALL
SELECT 'XYCEG' AS vname FROM dual )
select * from t where vname like '\_\\BCE%' escape '\';
VNAME
-------
_\BCEDF

对于类似“%”的转译我们也可以参照这么写。


————————————————

免责申明:

本文系转载,版权归原作者所有,如若侵权请联系我们进行删除!

想了解或咨询更多有关袋鼠云大数据产品、行业解决方案、客户案例的朋友,浏览袋鼠云官网:https://www.dtstack.com/?src=bbs
同时,欢迎对大数据开源项目有兴趣的同学加入「袋鼠云开源框架钉钉技术群」,交流最新开源技术信息,群号码:30537511,项目地址:https://github.com/DTStack

0条评论
社区公告
  • 大数据领域最专业的产品&技术交流社区,专注于探讨与分享大数据领域有趣又火热的信息,专业又专注的数据人园地

最新活动更多
微信扫码获取数字化转型资料
钉钉扫码加入技术交流群