整体介绍

SQL语言是一种数据库语言

 1、DDL数据定义语言

create-创建  drop-删除 alter-修改 rename-重命名 truncate-截断

2、DML数据操作语句

insert-插入 delete-删除 update-更新 select-查询

3、DCL数据控制语句

grant-授权 revoke-回收权力 commit-提交事务 rollback-回滚事务

注Oracle命令不区分大小写但数据和内容区分 

一、select语法

1、基本结构和简单查询

基本结构select 查询的信息 from 数据来源

select * from emp

select deptnosalename from emp

*表示查询该表的所有数据

查询的信息可以是*也可以是一个或多个字段名多个字段之间用逗号分隔字段的顺序即查询结果顺序

2、去重distinct

select distinct deptno from emp

distinct去重是去除的整条记录的重复

3、别名

给列取别名有两种写法

写法1 字段名后跟空格和别名

select ename 姓名sal 工资 form emp

写法2使用as关键字

select ename as 姓名sal as 工资 from emp

4、排序order by

降序desc  升序asc默认升序排序

select enamesal from emp order by sal desc

多字段排序用逗号分隔

select enamesal from emp order by deptno ascsal desc

解释查询emp表中的enamesal字段查询结果集按照先deptno字段值升序排序在deptno升序的基础上按照sal降序排序

5、伪列

生成表中没有的数据列称为伪列

select enamesalsal*2 年薪 from emp

其中年薪属于别名而sal*2是表中没有的数据是通过计算得到所以称为伪列

6、字符串拼接 ||

select enameename || ‘a’ 别名 from emp

注两个变量拼接时如a||b当a为null则结果为b

7、虚表 dual

只有一行一列是用来构成select的语法规则可以执行插入、更新、删除操作还可以执行drop操作

注虽然可以执行drop操作但是还是不建议对dual执行drop操作否则会使系统不能用、起不了数据库。

select 11*22 from dual

8、select 执行顺序

 select distinct 查询字段1 别名查询字段2 as 别名表达式 别名 from 数据来源 order by 排序字段 desc

 以上是之前知识的综合语句

解析顺序from====》select====》order by

先找表再找需要查询的内容最后对结果集排序

9、条件查询*

结构select 查询内容 from 数据来源 where 行记录条件

 解析过程1、查询数据源 2、判断是否符合行记录条件 3、将满足条件的记录在select指定的信息中存放结果集当中

条件运算

=、>、<、>=、<>、!=、between and、in

基础结构

select * form emp where deptno=10

 between...and... 值在一定范围内、闭区间包含两段界限

select * from emp where sal between 2000 and 4000

inab满足a或b都可以

select * from emp where deptno in1020

条件连接运算

and、or、not

and 同时满足

select * from emp where sal>1500 and deptno=20;

 or 满足一个即可

select * from emp where sal>1500 or deptno=20;

 not 取反

select * from emp where not deptno=20;

10、null处理

nvlexplres

null在参加计算式结果为null使用nvlexplres处理空值expi-表达式、res-结果

当expi不为空时结果为expi为空时结果为res

select enamesalcommsal+nvlcomm0月收入 from emp

null排序

nulls first、nulls last 将空值放置在列前或后

select * from emp order by comm desc nulls first

null查询

不为空

select * from emp where comm is not null

 为空

select * from emp where comm is null

 不为空

select * from emp where not comm is null

11、模糊查询 like

select * from emp where like ‘%s%’

%表示任意个字符  _表示一个任意字符

当模糊查询的内容包含”%“、”_“时

select * from emp where like ‘%a%%’ escape‘a’

escape中的a属于标识符用于证明a后的字符不是特殊字符只对其身后的一位字符有效

如想查询‘a%’则...like ‘%aaa%%’ escape‘a’

12、where子句

select * from emp where deptno = select deptno from dept where dname = ‘SALES’

二、函数

1、函数介绍

根据函数的返回结果分为单行函数和多行函数。

单行函数一条记录返回一个结果。

多行函数组函数、聚合函数可同时对多条记录进行操作并返回一个结果。

2、常用的单行函数

字符函数

concatxy连接字符串x和y

instrxstrstartn在x中查找str从start开始也可以从第n次开始。初始位置从1开始。

select instr‘HelloWorld’‘l’13from dual

lengthx返回x的长度

lowerxx转换为小写

upperxx转换为大写

itrimxtrim_str把x左边截去trim_str字符串没有写trim_str就截去空格

replacexoldnew在x中将old替换为new替换的是能找到的所以old

substrxstartlength从start开始截取length个字符缺length默认到结尾

日期函数

sysdate、current_date当前日期精确到秒

add_months(d1,n1)在d1基础上再加n1个月后的日期

last_dayd1放回d1所在月份的最后一天

months_betweend1d2从d1到d2之间的月数

next_day(d1[ ,c1])d1日期的下周星期几c1

转换函数

to_char(x,c)将日期或数据x按照c格式转换

to_date(x,c)将字符串x按照c的格式转换为日期

to_number(x)将字符串x转为数字型

3、组行数

avg平均值、sum求和、min最小值、max最大值、count统计

注null不参与运算

group by 分组

结构select...from...group by....

select deptnoavgsalfrom emp group by deptno

having 过滤组信息

select deptnoavgsalfrom emp group by deptno having avgsal>2000

执行顺序①找到数据源from emp ②对数据源进行分组group by deptno ③对分组内容进行过滤having avgsal>2000 ④从分组中查询数据select deptnoavgsal

过滤行记录和组信息

行记录的过滤是针对每条记录的筛选组信息的过滤是针对组的筛选是可以同时出现的先筛选行再过滤组

结构⑤select...①from...②where...③group by...④having...序号代表执行顺序

三、分页和去重

1、分页

假分页一次查询出数据库中的所有记录然后在每页中显示指定的记录

缺服务器负载大 优与数据库只对接一次

真分页对数据库多次查询每次只获得本页的数据并显示

缺频繁操作数据库 优服务器负载小

2、rownum

是对每一个结果集中的每一条记录的编号从1开始

查询伪列rownum

select enamesaldeptnorownum from emp

将查询出的结果集作为临时的数据来源此时结果集中的rownum rw为普通字段

select * fromselect enamesaldeptnorownum rw from empwhere rw>5 and rw <=10

3、排序分页每页只展示3条数据

select enamesaldeptnor1r2
  fromselect enamesaldeptnor1rownum r2
    fromselect enamesaldeptnorownum r1
      from emp order by sal desc
where r2>(3-1)*3 and r2<=3*3

4、rowid去重

rowid相当于伪列由Oracle生成是唯一的

delete from copy where rowid not inselect minrowidfrom copy group by deptno

四、表连接

概念多表查询-多表拼接

1、92语法

结构select...from table1,table2...where

很多时候需要为表取别名①简化表名②可能存在自连接的情况

原理from后面出现的顺序前面的表作为内存的for循环后出现的表作为外层的for循环

笛卡儿积

两个集合中的每一个成员都与对方集合中的任意一个成员有关联如果没有给筛选条件即为笛卡儿积

等值连接

在笛卡儿积的基础上取条件列相同的值

select * from emp edeptno d where e.deptno=d.deptno

非等值连接

!=、>、<、<>、between...and...

select enamesalhiredategrade s from emp esalgrade s where e.sal between losl and hisal

自连接

特殊的等值连接来自于同一张表

外连接

内连接使用比较运算符根据每个表共有的列的值匹配两个表中的行

外连接可以是左向外连接、右向外连接或完整外部连接

注没有+的是主表

select * 
  from detp dselect count*deptno 
    from emp group by deptnoc
where d.deptno=c.deptno+

2、99语法

笛卡儿积 cross join

select * from dept cross join emp

自然连接 natural join

做等值连接需要有同名列主外键关系

select enameempnodeptnodname from emp natural join dept

using连接、等值连接 join using

select enameempnodeptno,daname from emp join dept using(deptno)

join on

等值

select enameempnoe.deptno,dname from emp e join dept d on e.deptno=d.deptno

非等

select enamesal,deptno,grade from emp e join salgrade s on sal between losal and hisal

outer join

left [outer] join on | using

right [outer] join on | using

 全连接 full join on

select ... from table full join table2 on 连接条件 where...

 3、集合操作

union 并集 union All 全集 instersect 交集 Minus 差集

五、DDL语法

1、表操作

创建表

create table 表名字段名 类型长度...

 数据类型

详细解释可以查看官方文档

修改表结构

修改表名

rname 原表名 to 新表名

修改列名

alter table 表名 rename column 列名 to 

修改字段类型

alter table 表名 modify字段类型

alter table tb_student modify(studentId char(10))

添加列

alter table 表名 add 字段 类型

删除列

alter table 表名 drop column 字段

2、删除表

drop table 表名

 3、约束

主键约束primary key该字段在表中唯一且非空

唯一性约束unique只能出现一次

非空约束not null不能为空

外键约束foreign key外键从表中的数据必须在主表中已经存在才能使用

检查约束check

创建表的同时将约束进行创建约束不设定名称

create table tb_user(
  userid number(5) primary key,
  username varchar2(30) check(length(username) berween 4 and 20) not null,
  age number(3) default(18) check(age>=18),
  gender char(3) default('男') check(gender in('男','女')),
  regtime date default(sysdate)
)

创建表的同时给约束设定名称

create table tb_user(
  userid number(5),
  username varchar2(30) not null,
  age number(3) default(18),
  gender char(3) default('男'),
  regtime date default(sysdate)
  constraint pk_user_id primary key(userid),
  constraint ck_user_name check(length(username) berween 4 and 20),
  constraint ck_user_age check(age>=18),
  constraint ck_user_gender check(gender in('男','女'))
)

创建完表后追加约束

alter table tb_user add constraint pk_user_id primary key(userid)

 修改约束

alter table tb_user modify(username constrant nn_user_name no null)

 外键处理方式

强制不让删除

alter table tb_txt add constraint fk_txt_user_id foreign key(userid) references tb_user(userid)

删除后自动设置为null

alter table tb_txt add constraint fk_txt_user_id foreign key(userid) references tb_user(userid) on delete set null

级联删除

alter table tb_txt add constraint fk_txt_user_id foreign key(userid) references tb_user(userid) on delete cascade

禁用和启用约束

启用/禁用enable/disable对后来的数据进行校验

验证/非验证validate/novalidate对已存在的数据进行校验

删除约束

alter table tb_user drop cpnstraint ag_user_email cascade

六、DML

1、插入

insert into 表名 [字段列表] values值列表

 插入记录时要满足长度兼容、约束

默认添加

insert into 表名 values值列表

 使用默认添加时数据必须和表结构字段顺序和个数一致

添加时指定列和顺序

insert into 表名指定列values值列表

 如果是外键被参考的主表字段一定要有数据

2、更新

update 表名 set 字段1=值1[字段2=值2...] where 过滤行记录

 注在更新时一定记得加上where条件

3、删除

delete from 表名 where 过滤行记录

 注先删从表后删主表