SQL语言详解——基于PLSQL的SQL语言详解


注:本文基于Oracle数据库与数据库管理软件plsql,文中代码里使用的大部分表格为该软件的测试用户SCOTT下的默认表格
文中代码格式使用到<>表示必写项,[]表示可选项

2.1 SQL语言基础

  • 什么是SQL语言
    • 结构化查询语言SQL(Structured Query Language)
    • SQL是最重要的关系数据库操作语言,是所有关系数据库管理系统的标准语言
    • 许多数据库厂商在使用SQL的同时,都对SQL进行了拓展,比如ORACLE的PL/SQL语言,MSSQL-Server的T-SQL语言
    • SQL语言是一种非过程化的语言,只需要提出做什么,不需要提出怎么做
  • SQL可以做什么
    • 数据库的增删改查操作(CRUD)
    • 数据库对象的创建,修改和删除操作(用户,表格,序列,索引…)
    • 用户权限/角色的授予与取消
    • 事务控制
  • SQL语言的分类
    • DQL,Data Query Language,数据查询语言,执行数据库的查询操作:select
    • DML,Data Manipulation Language,数据操作语言,执行增删改的操作:insert,delet,update
    • DDL,Data Definition Language,数据定义语言,用于操作数据库对象:add,modify,drop
    • DCL,Data Control Language,数据控制语言,操作用户权限:grant(授予),revoke(撤销)
    • TCL,Transaction Control Language,事物控制语言,用于管理事物:commit ,rollback
  • 关系数据库的基本名词解释
    • 关系:整个二维表
    • 关系名:表格名称
    • 元组:行数据(记录)
    • 属性:列数据(字段)
    • 属性名:列名称(字段名)
    • 主键:唯一确定元组的属性组(关键字)
    • 域:
  • SQL语言的执行顺序 form–where–group by–select–having–order by

2.2 Select子句

  • 使用“–”来注释
  • 查询某张完整表格,其中表示“*”表示获取所有的列
select * from <表格名>;
  • 按字段名查询某张表格
select <字段名1>,<字段名2> from <表格名>;
  • 查询并临时修改一个字段名
    • 其中as可省略
    • 如新名称中有空格,则新名称需要使用双引号才不会报错
    • SQL中双引号表示原样输出,如双引号中的小写字母不会转换成大写字母
select <字段名> as <新名称> form <表格名>;
  • 查询员工月薪并做简单运算得到年薪,新命名为年薪
select sal*12 年薪 from emp;

2.4 distinct_字符串连接符_order by

  • distinct去除重复行
    • 译作 独特的,明显的,有区别的
    • 作用范围为distinct后面的列,可以是单列、多列和所有列
    • 举例:
    select distinct job from emp; //去除job这一列中重复的两行中的一行并展示之
    select distinct job,mgr from emp; //去除job与mgr两列中完全相同的两行中的一行并展示之
    select distinct * from emp; //去除所有列中完全相同的两行中的一行并展示之
    
  • 字符串连接符 “||”
    • 举例:查询所有员工的姓名、职位和薪资,按格式输出:“”并作为单独的一列,列名称为INFO
    select '姓名:'||ename||' 职位:'||job||' 薪资:'||sal info from emp;
    //单引号必须为英文半角
    
  • order by子句
    • order by子句用于排序
    • 升序举例:查询并按薪资升序(升序asc,asc可以省略不写)
    select * from emp order by sal asc;
    
    • 降序举例:查询并按薪资降序(降序desc)
    select * from emp order by sal desc;
    
    • 多条件排序举例:按薪资降序排序,如果薪资相同,按入职时升序排序
    select * from emp order by sal desc,hiredate asc;
    

2.5 where子句——条件过滤

使用where子句过滤来查询内容

2.5.1 等值条件和非等值条件

-等值判断

  • 查询工资等于800的记录
select * from emp where sal=800;
  • 如值为字符串,则需要用单引号引起来,且区分大小写
select * from emp where ename='SCOTT'
  • 如过滤值为日期,则查询1980年2月17日的格式为 ‘17-2月-1980’ (必须有单引号):
select * form emp where hiredate = '17-2月-1980';
  • 非等值判断(>、<、!=、between、in)
    • 查询工资大于3000的记录
    select * from emp where sal>3000;
    
    • 查询工资在1500到3000的记录,包含边界
    select * from emp where sal between 1500 and 3000;
    或者
    select * from emp where sal>=1500 and sal<=3000;
    
    • 查询员工ALLEN与员工CLARK的信息(区分大小写)
    select * from emp where ename='ALLEN' or ename='CLARK';
    或者
    select * from emp where ename in ('CLARK','ALLEN');
    
    • 查询部门不等于20的所有员工信息
    select * from emp where deptno != 20;
    或者
    select * from emp where deptno <> 20;
    

2.5.2 模糊查询

使用like关键字配合通配符*实现

  • “ _ ”,表示一个字符,“ % ”表示任意个字符,例如查询姓名第二个字母是A的
select *from emp where ename like '_A%'
  • 例如查询名字中任意位置带A的姓名
select * from emp where ename like '%A%'
  • 查询内容中带有下划线的时候,需要用到转义字符,而oracle数据库中的转义字符需要用escape来声明
select * from emp where ename like '\_' escape '\'; //将'\'声明为一个转义符号,不一定要是'\'才行 

2.5.3 is null与is not null_运算符优先级

  • 2.7.1 空值判断is null与非空值判断is not null
    • 查询所有没有提成的员工(comm为空)
    select * form emp where comm is null;
    
    • 查询所有有提成的员工
    select * from emp where comm is not null; //对查询条件取反
    或者
    select * from emp where not comm is null;   // 对查询结果取反
    
  • 2.7.2 运算符的优先级
    • and 和 or 的优先级:and > or
    select * from emp where job='CLERK' or job = 'SALESMAN' and sal>1500;
    // 查找职位为CLERK与职位为SALESMAN的员工,其中SALESMAN的工资要大于1500
    
    • 括号与and 的优先级:括号> and
    select * from emp where (job ='CLERK' or job = 'SALESMAN' ) and sal>1500;
    // 查找 职位为 CLERK 与职位为 SALESMAN 的员工,且他们的工资都要大于1500
    

2.6 SQL的函数

SQL的函数有很多,这里只讲解一些最常用的函数

2.6.1 字符函数

  • lower、upper、initcap
    • lower :将所有字母转小写
    • upper :将所有字母转大写
    • initcap:所有单词首字母大写
    select ename , lower(ename) ,upper(ename) ,initcap(ename) from emp;
    
  • 在ORACLE中,提供了一个虚拟表格,叫dual,专门用来测试,可以从dual中查询任何数据
select 12*10 from dual;
select upper('abcf') from dual;
  • length(列名称或’字符串’) :用于计算字符串的长度
select length(ename) from emp;
或者
select length('fkdlsjfld') from dual;
  • replace(列名称,‘原字符’,‘新字符’) :将某列的全部某个字符更改为一个新的字符
select replace(ename,'A','a') from emp;
  • substr(列名称,x,y) :用于字符串的截取,截取从x开始的y个字符,
    • 省略y不写则表示从x开始截取到最后
    • x值为负值时表示从末尾往前数x位的位置
select substr(ename,0,3) form emp;
select substr('fksdjfl') from emp;
select substr(ename,length(ename)-2,3) from emp;//查询并输出ename下的姓名的最后三个字符
select substr(ename,-3) from emp;

2.6.2 数值函数_日期函数

  • ceil() :向上取整
select ceil(130.232421) from dual;
  • floor() :向下取整
select floor(2343.234) from dual;
  • round(x,y) :四舍五入,x为需要四舍五入的数,y表示小数点后保留的位数,当y为负数时,从小数点往左数位数,如下面代码得到的结果是120:
select round(123.123,-1) from dual; 
  • trunc(x,y) :截取。y可以省略不写,则不截取;y为正值时截取小数点后y位;y位负值时截取小数点前y位;不进行四舍五入;
select trunc(123,123,2) from dual;//结果为123.12
  • sysdate :获取当前日期
select sysdate from dual;
  • months_between(date1,date2) :计算两个日期间的月数,比如计算emp表中每个职员入职到现在的月数(date1-date2):
select ename , months_between(sysdate,hiredate) months  from emp;
  • add_months(date,x) : 给日期date加x月,当x为负值时,为减月份:
select sysdate , add_months(sysdate,-3) from dual; //当前日期减3个月
  • last_day(date) : 返回date所在月份的最后一天的日期
select sysdate , last_day(sysdate) from dual;
  • next_day(date,‘星期x’) :基于给定日期date,返回下个星期x的日期
select sysdate,next_day(sysdate,'星期三') from dual; //下个星期三是哪天

2.6.3 转换函数

用于在不同数据类型间进行转换

  • 常见三种类型:数值类型number,字符串类型varchar2,日期型date
  • to_number(varchar2,c1)将字符串转为数子(经常使用在关于钱的时候),转换格式c1查询Oracle函数大全:
//将'¥213,214,421.00'转为数字
select to_number('¥213,214,421.00','L999999999.00') from dual;
  • to_date(varchar2,c1) :字符串转日期,转换格式c1查询Oracle函数大全:
select to_date('2011-12-12 23:23:00','YYYY-MM-DD HH24:MI:SS') from dual;
  • to_char() :将数字或日期转换为字符串
//数字转字符串 to_char(number,'c1')
select to_char(213213123,'L999,999,999.000') from dual;
//日期转字符串 to_char(date,'c1')
select sysdate,to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual;

2.6.4 通用函数

  • nvl(x,y) :如果x为空,则使用y进行替换,例如
//查询所有员工的姓名,工资,提成,总工资。这个在sal+comm中,comm为空会导致总工资运算结果为空
select ename,sal,comm,sal+nvl(comm,0) form emp;
  • nvl2(x,y,z) :如果x为空,则使用y,否则使用z
select ename , sal , comm ,sal+nvl2(comm,comm,0) as tal from emp;
  • decode(A,x,x1,y,y1,…) : 相当于java中的switch语句,将A列下的x,y…转换为x1,y1…
select distinct job , decode(job,'CLERK','职员','MANAGER','经理') from emp;

2.6.5 分组函数

  • sum() : 求和
//求所有员工工资的总和
select sum(sal) from emp;
  • avg() : 求平均值
select avg(sal) from emp;
  • man() : 求最大值
select max(sal) from emp;
  • min() : 求最小值
select min(sal) from emp;
  • count () : 统计数量,统计记录数量
//统计员工总数
select count(empno) from emp;
//或者
select count(*) from emp;
//函数不对空值做处理,例如查看多少人有提成
select count(comm) from emp;

2.7 分组查询group by_分组过滤having

2.7.1 group by

用来进行分组查询,即将查询分为若干个组

  • 分组时可以按一个或多个列分组,如果按做个列分组,那么在select和group by中都要出现这一列
select job,deptno from emp group by job,deptno;
  • 带order by的分组查询:group by 要写在order by前面
//例子:分组统计每个部门的最高工资和最低工资
select deptno,min(sal),max(sal) from emp group by deptno;
  • 带where的分组查询:where 写在 group by前面
//例子:分组统计除了第10部门外的其它部门的总工资
select deptno ,sum(sal) from emp where deptno<>10 group by deptno;

2.7.2 having()

having和where一样用来过滤的,由于group by的优先级比where的低,所以使用having来对分组后的数据进行过滤(保留下having条件后的组)

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

2.8 DML:insert_update_delete

2.8.1 新增 insert

DML(Data Manipulation Language),数据操作语言

  • 复制表格emp,重命名为tmp
create table tmp as (select * from emp);
  • 复制表格emp的表结构,不复制emp的内容,重命名为tmp2
create table tmp2 as (select * from emp where 1=2); //where后跟一个不成立的等式就行了
  • 新增数据(insert into),新增后F10提交
insert into <表名> (列1,列2,...)
values();//严格按照数据类型添加数据
  • 当按顺序每一列都新增数据时,可以省略列名
insert into tmp2 
values (13,'kkkkk','eat',24,to_date('2019-12-3','yyyy,mm,dd'),214,342,10);

2.8.2 修改 update

  • update的语法格式
update <表名> set <列名1>=值1 [<列2>=值2 <列3>=值3 ...][where xxx筛选条件];
  • 修改tmp2表下的所有工资sal为2000
update temp2 set sal=2000;

2.8.3 删除 delete

  • 语法
delete [from] <表名> [where 条件];
  • 例子:删除tmp2表中的所有内容
delete tmp2 ;//只删除数据不删除表结构
  • 例子:删除名字为‘myself’的一条记录
select from tmp2 where ename='myself';

2.9 多表连接查询SQL99

2.9.1 cross join、natural join

  • cross join 交叉连接(
    交叉连接会产生一个笛卡尔积:表1记录数*表2记录数
    • 例子:从emp与dept表查询所有列
    //将查询出14*4=56条记录
    select *from emp cross join dept;
    
    • 例子:从emp与dept表查询所有列,且部门相同的列
    select *form emp cross join dept where emp.deptno=dept.deptno;
    //由于有两个重复的deptno,只保留一个(如果列名称不是重复的,可以不在前面加表名)
    select emp.*,dept.dname,dept.loc from emp cross join dept where emp.deptno=dept.deptno;
    
    • 给表起别名方便实用
    //例如:給emp取名为e
    select * form emp e cross join dept where e.deptno=dept.deptno;
    
  • natural join 自然连接
    • Natural join基于两个表中的全部同名列建立连接
    • 从两个表中选出同名列的值军对应相等的所有行
    • 如果两个表中同名列的数据类型不同,则出错
    • 同名列不能加表前缀
    • 自然连接的结果不保留重复的属性
    select *from emp natural join dept [where 条件];
    

2.9.2 using、on

  • using()
    用来指定进行等值连接的字段,针对自然连接
    • 例子:查询20部门员工的姓名,工资,部门编号,部门名称
    select ename,sal,deptno,dname from emp natural join dept where deptno =20;
    或
    //using()中的列不可以表前缀
    select ename,sal,deptno,dname from emp  join dept  using(deptno) where deptno=20;
    
  • on
    用来指定连接的条件
    • 非等值连接例子:列出sal及sal对应的工资等级
    select e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
    
    • 等值连接例子:查询30部门员工的姓名,标号,部门名称和所在地
    select ename,empno,dname,loc from emp join dept on emp.deptno=dept.deptno where emp.deptno=30;
    
    • 三表连接例子:查询公司所有员工姓名,工资,工资等级,所在部门名称
    select ename,sal,s.grade,dname from emp e join salgrade s on e.sal between s.losal and s.hisal join dept d on e.deptno=d.deptno;
    //可以优化成
    select ename,sal,s.grade,dname from emp e join salgrade s on e.sal between s.losal and s.hisal natural join dept;
    

2.10 多表连接查询SQL92

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页