SQL语言

发表于 | 分类于 ，

SQL是结构化查询语言（Structured Query Language），具有定义、查询、更新、控制等多种功能，是关系数据库的标准语言。

SQL分类

  • 数据操作语言DML(Manipulation) ：针对表里面的数据
    · SELECT INSERT UPDATE DELETE
  • 数据定义语言DDL（definition）：针对表结构
    · CREATE ALTER DROP RENAME TRUNCATE
  • 数据控制语言DCL（control）：用户访问和权限控制
  • · GRANT REVOKE

命令

在PLSQL的sql window窗口中，--和/**/是注释。

查表和默认表字段含义

▪ select * from tab;//查看用户下的所有表
▪ select * from user_tables;//详细查询当前用户下的所有表
▪ –desc 表名； //查看表结构 表字段的含义，类型等
▪ 查看所有表：select table_name from user_tables;
▪ 查看表结构：describe dept;(戒者desc dept;)
▪ emp表->雇员表(employee)
 – Empno: 雇员工号 Ename: 雇员名字
 – Job:工作（秘书、销售、经理、分析员、保管）
 – Mgr(manager):经理的工号
 – Hiredate:雇用日期
 – Sal: 工资
 – Comm: 津贴
 – Deptno: 所属部门号
▪ dept表->部门表（department）
 – Deptno:部门号
 – Dname:部门名字
 – Loc: 地址
▪ salgrade表->一个公司是有等级制度，用此表表示一个工资的等级
 – Grade:等级
 – losal:最低工资
 – hisal:最高工资
▪ bonus表-> 奖金表：表示一个雇员的工资及奖金
 – Ename:雇员名字， job:工作，
 – sal: 工资 comm:津贴

添加注释

▪ 为表添加注释 comment on table emp is ‘雇员表’;

▪ 为列添加注释comment on column emp.ename is ‘雇员姓名’;

注：一般建表不会用，因为有数据字典。

Select查询

▪ Select-from-where句型
▪ SELECT [DISTINCT] {*,column alias,..}
 FROM table alias
 Where 条件表达式

▪ alias
 – Column alias
 – Column “alias”
 – Column as alias

select
▪ 检索单个列
 – select col from tableName;
▪ 检索多个列
 – select col1, col2,col3 from tableName;
▪ 检索所有列
 – select * from tableName; （最好不要用*）
 – 使用通配符的优点：书写方便、可以检索未知列
 – 使用通配符的缺点：降低检索的性能
▪ 给检索出的列起个别名
 – select job “gong zuo” from emp;
 – select job as “gong zuo” from emp;

表别名
▪ Select e.* from emp e;
▪ Select e.empno,e.deptno from emp e;
最好写上！

distinct去重
▪ distinct必须放在开头
▪ 多字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--查询雇员表中部门编号是10的员工
select empno,ename,job from emp where deptno = 10;
--distinct去重 查询雇员表中拥有的部门编号
select distinct deptno from emp;
--在查询过程中可以给列添加别名，同时也可以给表添加别名
--去重也可以针对多个字段，多个字段值只要有一个不匹配就算是不同的记录
select distinct deptno,Sal from emp;

select e.empno 雇员编号，e.ename 雇员名称, e.job 雇员工作 from emp e where e.deptno = 10;
--给列起别名可以加as也可以不加
select e.empno as 雇员编号，e.ename as 雇员名称, e.job as 雇员工作 from emp e where e.deptno = 10;
--给列起别名，如果别名中包含空格，那么要将别名整体用“”包含起来
select e.empno "雇员 编号"，e.ename "雇员 名称", e.job "雇员 工作" from emp e where e.deptno = 10;
--查询表中所有字段,可以使用*，但是在项目中千万不要随便使用*！
select * from emp;

where

条件比较

– ＝，！＝,<>，<,>,<=,>=,any,some,all
– is null,is not null
– between x and y
– in（list），not in（list）
– exists（sub－query）
– like _ ,%,escape ‘'

＝，！＝,<>，<,>,<=,>=,any,some,all

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- =
select * from emp where deptno = 20;
-- !=
select * from emp where deptno != 20;
-- <> 不等于
select * from emp where deptno <> 20;
-- <,
select sal from emp where sal < 1500;
-- >,
select sal from emp where sal > 1500;
-- <=,
select sal from emp where sal <= 1500;
-- >=,
select sal from emp where sal >= 1500;
--any,取其中任意一个
select sal from emp where sal > any(1000,1500,3000);
--some,跟any效果一样
select sal from emp where sal > some(1000,1500,3000);
--all,所有的值满足才会成立
select sal from emp where sal > all(1000,1500,3000);

is null,is not null
在sql语句中，null表示一个特殊含义，null != null，必须使用is null或者is not null 判断。

1
2
3
4
5
6
7
--is null
select * from emp where comm = null; --运行结果为空
select * from emp where comm is null;
--is not null
select * from emp where comm is not null;
select * from emp where null = null; --运行结果为空
select * from emp where null is null; --可以显示emp所有值

between x and y

1
2
3
--between x and y, [x,y]
select * from emp where sal between 1500 and 3000;
select * from emp where sal >= 1500 and sal <=3000 --等价

in（list），not in（list）
需要进行某些值的等值判断的时候，可以使用in和not in。如果里面是字符串，用单引号括起来
也可以使用and 和 or这样的关键字，and相当于与，or相当于或操作。一个语句中，要注意优先级。and的优先级高于or，所以一定要将or的相关操作用括号括起来

1
2
3
4
5
6
--in(list)
select * from emp where deptno in(10,20);
select * from emp where deptno = 10 or deptno = 20; --等价
--not in(list)
select * from emp where deptno not in(10,20);
select * from emp where deptno != 10 and deptno != 20;--等价

exists（sub－query）
exists(sub-query),当exists中的子查询语句能查到对应结果的时候，
意味着条件满足，相当于双重for循环。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
/*
现在要查询部门编号为10和20的员工，要求使用exists实现
*/
--正确
select * from emp where deptno = 10 or deptno = 20;
--错误,会查到所有的结果.因为子查询不管怎么查都会出现10和20这两个值
--既然能查到结果，意味着条件满足，所以每一条记录都会被查出结果。
select *
  from emp
 where exists (select deptno
          from dept
         where deptno = 10
            or deptno = 20);
--通过外层循环来规范内层循环
--错误,依旧查出全部结果。因为and优先级高
select *
  from emp e
 where exists (select deptno d
          from dept d
         where d.deptno = 10
            or d.deptno = 20
           and e.deptno = d.deptno);
--正确
select *
  from emp e
 where exists (select deptno d
          from dept d
         where (d.deptno = 10 or d.deptno = 20)
           and e.deptno = d.deptno);

like,_ ,%,escape
模糊查询
在like语句中，需要使用占位符或者通配符:
 _，某个字符或者数字只出现一次
 %，任意字符出现任意次数
 escape，使用转义字符，可以自己规定转义字符
注意：
使用like的时候要慎重，因为like的效率比较低
使用like可以参考使用索引，但是要求不能以%开头
涉及到大文本的检索的时候，可以使用某些框架 luence，solr，elastic search

1
2
3
4
5
6
--查询名字以S开头的用户
select * from emp where ename like('S%');
--查询名字以S开头且倒数第二个字符为T的用户
select * from emp where ename like ('S%T_');
--查询名字中带%的人的用户
select * from emp where ename like ('%a%%') escape('a');

逻辑复合条件

– not，（and，or） and优先级高

例子：
列出deptno为10或者30，幵且工资>2000的所有人。
select * from Emp where deptno=30 or deptno=10 and sal>2000;
这个命令列出的人中薪水有<2000的，为什么？
 and优先级高，or要加括号。
 计算次序问题的解决，最好用括号进行分组处理

SQL优化问题

– AND: 把检索结果较少的条件放到后面
– OR: 把检索结果较多的条件放到后面

order by

▪ 按照单个列排序
 – order by col
▪ 降序和升序：
 – order by col desc (asc)
▪ 按多个列排序（优先级）
 – order by col1 desc(asc), col2 desc(asc)

order by进行排序默认升序操作
asc：默认的排序方式，升序
desc：降序
按照自然顺序进行排序的。数值从大到小；字符串按照字典序（ASCII码）
A~Z在a~z前

在进行排序的时候，可以指定多个字段，而且多个字段可以采用不同的排序方式
每次在执行order by的时候，相当于是做了全排序，思考全排序的效率。
所以这些耗费内存的操作最好放到业务不太繁忙的时候进行，使用的时候一定要慎重。

1
2
3
4
select * from emp order by sal;
select * from emp order by sal desc; 
select * from emp order by ename;
select * from emp order by sal desc, ename asc;

创建计算字段

▪ 为什么需要计算字段？
 我们经常需要直接从数据库中检索出转换、计算或格式化过的数据；
 而不是检索出数据，然后再在客户机应用程序中重新格式化。
▪ 计算字段并不实际存在于数据库表
▪ Sql允许select子句中出现+,-,*,/以及列名和常数的表达式
▪ 拼接字段 || 也可以使用函数concat(arg1,arg2)，拼接arg1和arg2

1
2
select 'my name is '||ename||'!' from emp;
select concat('my name is ',ename) from emp;

通用函数 nvl

Sql中允许列值为空，空值用保留字NULL表示。NULL丌同不0戒者
空格，它就是代表了一个不确定的内容。
注意：

  1. NULL != NULL。
  2. 任何含有null值的数学表达式最后的结果都为空值。可以使用nvl函数来处理。
  3. 函数nvl(arg1,arg2)，如果arg1是空，那么返回arg2；如果不为空，则返回原来的值
    注：两个参数的类型要相同
1
2
3
4
5
6
7
8
9
-- 计算所有员工的年薪
select ename,(e.sal+e.comm)*12 from emp e;
--null是比较特殊的存在，null做任何运算都还是为空，因此要将空做转换
--引入函数nvl
select ename,(e.sal+nvl(e.comm,0))*12 from emp e;
--dual是oracle数据库中的一张虚拟表，没有实际的数据，可以拿来做测试
select 100+null from dual; -- 结果为空
--显示没有上级管理（mgr为null）的公司首脑  
select ename,nvl(to_char(mgr),'boss') from emp where mgr is null;

并集，全集，交集，差集

1、union all 全集
将2个集合的数据全显示，不去重
2、union 并集（去重）
将2个集合中所有数据都进行显示，但是不包含重复的数据
3.intersect 交集
两个集合中重复的数据集，只显示一次
4.minus 差集
包含在A集合而不包含在B集合中的数据，跟A,B顺序相关

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
--A  6条
select * from emp where deptno = 30;
--B  12条
select * from emp where sal > 1000;
--并集
select * from emp where deptno = 30 union
select * from emp where sal > 1000; -- 13条
--全集
select * from emp where deptno = 30 union all
select * from emp where sal > 1000; -- 18条
--交集
select * from emp where deptno = 30 intersect
select * from emp where sal > 1000; -- 5条
--差集
select * from emp where deptno = 30 minus
select * from emp where sal > 1000;  -- 1条

易错例子

具体看GitHub代码。下面只选出部分的例子。

1、查询部门名称为 SALES 和 RESEARCH 的雇员姓名、工资、部门编号

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
--使用exists 和 in
select e.ename, e.sal, e.deptno
  from emp e
 where exists (select d.deptno
          from dept d
         where (d.dname in ('SALES', 'RESEARCH'))
           and e.deptno = d.deptno);
--使用exists           
select e.ename, e.sal, e.deptno
  from emp e
 where exists (select d.deptno
          from dept d
         where (d.dname = 'SALES' or d.dname = 'RESEARCH')
           and e.deptno = d.deptno);
--使用in
select e.ename, e.sal, e.deptno
  from emp e
 where e.deptno in
       (select d.deptno from dept d where d.dname in ('SALES', 'RESEARCH'));

注意：
■ in中如果是字符串，用单引号括起来
■ in(list1,list2…) 不是in[]

2、查询EMP表显示所有雇员名及其全年收入(月收入=工资+补助),处理NULL行,并指定列别名为”年收入”

1
select ename,(sal+nvl(0,comm))*12 年收入 from emp;

注意：
■ 别称可以选加双引号，但不能使用单引号
■ 别称前可以选加as

3、查询EMP表显示第2个字符为”A”的所有雇员名其工资

1
select ename,sal from emp where ename like ('_A%');

注意：
■ like里面有单引号

4、查询EMP表显示在1981年2月1日到1981年5月1日之间雇佣的雇员名、岗位及雇佣日期，并以雇佣日期进行排序

1
2
3
4
5
select ename, job, hiredate
  from emp
 where hiredate between to_date('1981/2/1', 'yyyy/mm/dd') and
       to_date('1981/5/1', 'yyyy/mm/dd')
 order by hiredate;

注意：
■ 日期转换函数to_date的使用