干货 | Oracle数据库操作命令大全，满满的案例供你理解，收藏！

干货 | Oracle数据库操作命令大全，满满的案例供你理解，收藏！

文章目录

​​数据库技术​​

​​前言​​

​​T1.什么是数据库?​​​​T2.常见的数据库有哪些?​​​​T3.生活中那些地方使用数据库?​​

​​一、数据库发展史​​

​​1.1 程序管理阶段​​​​1.2 文件系统阶段​​​​1.3 数据库系统阶段​​

​​二、数据库专业术语​​

​​2.1 关系​​​​2.2 元组​​​​2.3 属性​​

​​三、数据库及连接工具介绍​​

​​3.1 Oracle数据库介绍​​​​3.2 连接工具介绍​​​​3.3 Oracle数据库登录​​

​​四、表空间​​​​五、数据类型​​

​​5.1 字符类型​​​​5.3 日期类型​​

​​六、数据库语句​​

​​6.2 SQL语句分类​​​​6.3 创建表​​​​6.4 查看表结构​​​​6.5 插入语句​​

​​6.5.1 向全部列插入数据​​​​6.5.2 向指定列插入数据​​

​​6.6 修改(更新)语句​​​​6.7 删除表中的数据​​​​6.8 删除表​​

​​复习:​​

​​6.9 查询语句(重点)​​

​​6.9.1 没带条件的查询语句​​​​6.9.2 给列起别名​​​​6.9.3 去掉重复的列值(distinct)​​​​6.9.4 排序(order by)​​​​6.9.5 条件查询语句​​

​​1)and 并且 连接2个或者多个条件​​​​2) or 或者​​​​3) not 非​​

​​6.9.5.1 查询列值为空的情况(is null)​​​​6.9.5.2 查询列值不为空的情况(is not null)​​​​6.9.5.3 between....and​​​​6.9.5.4 in 比较一个列中的几个列值​​

​​复习:​​

​​6.9.5.5 模糊查询(-重点)​​

​​6.9.6 处理空值的函数​​​​6.9.7 聚合(分组)函数​​

​​1)count(列名/*)​​​​2)sum(列名) 统计列值累加之和​​​​4)min(列名) 求最小值​​​​5)max(列名) 求最大值​​

​​6.9.8 分组查询​​​​6.9.9 having语句​​

​​where和having的区别:​​

​​6.9.10 常用字符处理函数​​

​​1)length(字符串/列名) 统计字符串中字符/列值的个数​​​​2)substr(参数1,参数2,参数3) 截取字符串---重点​​​​3)拼接字符串​​

​​复习:​​

​​条件:​​

​​6.9.11 常用数值处理函数​​

​​1)round(数值,位数) 四舍五入函数​​​​2)trunc(数值,位数) 截取函数​​

​​6.9.12 常用日期处理函数​​

​​1)sysdate 当前系统日期​​​​2)to_char(date,'日期格式的一部分')​​​​3)to_date('特殊日期格式的字符串','日期格式')​​

​​6.9.13 多表连接查询​​

​​2)非等值连接查询​​​​3)自连接​​

​​6.9.14 子(嵌套)查询​​​​复习:​​

​​拼接字符串 || 或者 concat​​

​​6.9.15 事务控制语句​​

​​1)提交事务---commit​​​​2)回滚事务---rollback​​

​​七、约束(constraint)​​

​​约束的分类​​

​​7.1 主键约束(primary key)​​

​​1)创建表时,添加主键约束​​​​2)联合主键(复合主键)---重点​​​​3)修改表时,添加主键约束​​​​4)删除主键约束​​

​​7.2 唯一约束(unique)​​

​​1)创建表时,添加唯一约束​​​​2)修改表时,添加唯一约束​​​​3)删除唯一约束​​

​​复习:​​

​​7.3 检查约束(check)​​

​​1)创建表时,添加检查约束​​​​2)修改表时,添加检查约束​​​​3)删除检查约束​​

​​7.4 默认值约束(default)​​

​​1)创建表时,添加默认值约束​​​​2)修改表时,添加默认值约束​​​​3)删除默认值约束​​

​​7.5 非空约束(not null)​​

​​1)创建表时,添加非空约束​​​​2)修改表时,添加非空约束​​​​3)删除非空约束​​

​​八、索引(index)​​

​​1)创建索引​​​​2)删除索引​​

​​九、序列(sequence)​​

​​1)创建序列​​​​2)nextval​​​​3)序列的属性​​

​​十、视图(view)​​

​​10.1 创建视图​​​​10.2 视图查询​​​​10.3 修改视图​​​​10.4 通过视图实现对表的操作​​

​​1)插入操作​​​​2)修改操作​​​​3)删除操作​​

​​10.5 只读视图(-重点)​​​​10.6 删除视图​​

数据库技术

前言

T1.什么是数据库?

存储数据的仓库

T2.常见的数据库有哪些?

微软-------SQLServer微软-------AccessIBM-------DB2甲骨文-----Oracle甲骨文-----MySQL国产-------人大金仓

T3.生活中那些地方使用数据库?

超市商品管理系统—商品信息和价钱银行管理系统----账户信息医院挂号系统----用户信息网上商城系统-商品信息和价钱和等级12306---------账户信息和车次信息

一、数据库发展史

1.1 程序管理阶段

20世纪50年代中期

特点:不能长期存储数据

1.2 文件系统阶段

20世纪50年代后期—20世纪60年代后期

特点:数据缺乏独立性

1.3 数据库系统阶段

20世纪60年代后期–

特点:数据实现共享,减少冗余

二、数据库专业术语

2.1 关系

一个关系就是一张2维表(Excel)

2.2 元组

在2维表中,一行叫做元组,或者称为

“行”或者 “记录”

2.3 属性

在2为表中,一列叫做属性,或者称为

“列”或者“字段”

三、数据库及连接工具介绍

3.1 Oracle数据库介绍

Oracle是美国ORACLE公司研发的一款关系型数据库,主要特点有:速度快、安全性强、可跨平台,主要版本有:Oracle9i、Oracle10g、Oracle11g、Oracle12c

3.2 连接工具介绍

SQL Developer–Oracle自主研发PL/SQL DeveloperNavicat

3.3 Oracle数据库登录

登录到DOS窗口

开始>搜索程序和文件>cmd>回车 2. 在DOS窗口中输入:sqlplus>回车 3. 请输入用户名:system>回车 4. 输入口令:123456>回车

说明:如果出现连接到:​​Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production SQL>​​表示登录成功

四、表空间

表空间就是数据库中存储对象(2维表、视图、索引、序列)的容器,因为主要存储2维表(表),所以称为表空间

在数据库安装的时候,系统会自动创建表空间(system)可以通过system表空间,创建其它表空间一个数据库中,可以有若干个表空间,也可以只有一个system表空间。

五、数据类型

小说类型=>都市、玄幻、穿越、仙侠

5.1 字符类型

char、varchar、varchar2

char(n) 表示固定长度的字符串,n表示字符串的长度,当实际要保存的数据小于n时,在字符串的右侧,使用空格补齐。

例如: char(10) 存储zhangxs,会占用系统10个存储空间(7+3)

varchar(n) 表示可变长度的字符串,n表示字符串的最大长度,当要保存的数据小于n时,按照实际长度保存。

varchar2,是Oracle自定义的类型,比varchar数据类型兼容性更好,一般在企业开发中,常用varchar2代替varchar

5.2 数值类型

number

number(n) 表示整数类型,n表示能保存整数的最大位数

例如: number(3) 999 88 5

number(n,m) 表示整数或者小数,n表示有效数字的最大位数,m表示小数的最大位数,n-m表示整数的最大位数

例如: number(7,2) 12345.3 1000

5.3 日期类型

date 表示日期类型,包含:年月日时分秒

系统默认日期格式: dd-mon月-yy

dd----表示几号mon—表示几月yy-----表示那年

六、数据库语句

6.1 SQL语句

结构化查询语句,通过SQL语句,可以对数据库进行增删改查。

6.2 SQL语句分类

DDL–数据定义语句,主要是对数据库中的表,创建、删除、修改

创建----create修改----alter删除----drop

DML–数据操纵语句,主要是对数据库表中的数据,插入、修改(更新)、删除

插入—insert更新—update删除—delete

TCL–事务控制语句,主要负责数据库中的事务。

提交事务—commit回滚事务—rollback

DQL–数据查询语句,数据库中的重点

查询—select

6.3 创建表

格式:

create table 表名( 列名1 数据类型 primary key, 列名2 数据类型, ...... 列名n 数据类型);

create table per01(id number(8) primary key,name varchar2(30),age number(3),sex char(3));

create table per02(id number(8) primary key,name varchar(30));

练习:创建一张表,表名为per03,表中包含的字段有:

id number(8) primary keyname varchar2(30)sex char(3)address varchar2(50)

create table per03(id number(8) primary key,name varchar2(30),sex char(3),address varchar2(50));

练习:创建一张表,表名为ter01,表中包含的字段有:

id number(4) primary keyname varchar2(30)age number(3)sex char(3)sal number(7,2)address varchar(50)ttime date

create table ter01(id number(4) primary key,name varchar2(30),age number(3),sex char(3),sal number(7,2),address varchar(50),ttime date);

6.4 查看表结构

格式: desc 表名;

案例:查看per01表的,表结构

desc per01;

练习:查看per03表的,表结构

desc per03;

6.5 插入语句

6.5.1 向全部列插入数据

格式:

insert into 表名 values(列值1,列值2,列值3.....列值n);

说明:values中的列值必须和表结构中的列名是一一对应的(数量、顺序、类型)

验证: ​​select * from 表名;​​

案例:向per01表中,插入2条记录

desc per01;insert into per01 values(1001,'张三',23,'男');insert into per01 values(1002,'李四',30,'女');select * from per01;

练习:向per02表中插入2条记录,并验证

(1)desc per02;(2)insert into per02 values(101,'奥利根');(3)insert into per02 values(102,'奥利奥');(4)select * from per02;

练习:向per03表中,插入2条记录,并验证

desc per03;insert into per03 values(1,'王五','男','北京');insert into per03 values(2,'赵六','男','天津');select * from per03;

6.5.2 向指定列插入数据

格式:

insert into 表名(列名1,列名2,列名3.....列名n) values(列值1,列值2,列值3.....列值n);

说明:表名中的列名必须和values中的列值是一一对应的(数量,顺序,类型)。

验证:​​select * from 表名;​​

案例:向per01表中插入2条记录

ID NAME AGE SEX9001 tom 209002 rose 女9003 25 男

insert into per01(id,name,age) values(9001,'tom',20);insert into per01(id,name,sex) values(9002,'rose','女');select * from per01;insert into per01(id,age,sex) values(9003,25,'男');

练习:向per03表中,插入数据并验证

ID NAME SEX ADDRESS801 jack 男802 女 上海803 lucy 广州

insert into per03(id,name,sex) values(801,'jack','男');insert into per03(id,sex,address) values(802,'女','上海');insert into per03(id,name,address) values(803,'lucy','广州');select * from per03;

6.6 修改(更新)语句

格式:

update 表名 set 列名1=该列新值, 列名2=该列新值,.....列名n=该列新值 where 条件;

说明:如果没有where条件,修改全部数据。

验证:​​select * from 表名;​​

案例:修改per03表中,编号(id)是801的记录,将地址(address)修改为深圳

update per03 set address='深圳' where id=801;select * from per03;

案例:修改per03表中,地址(address)是上海的记录,将姓名(name)修改为rose

update per03 set name='rose' where address='上海';

练习:修改per03表中,姓名(name)是lucy的记录,将性别(sex)修改为女

update per03 set sex='女'where name='lucy';

练习:修改per01表中,编号是9003的记录,将姓名(name)修改为smith,年龄(age)修改为99岁

update per01 set name='smith',age=99 where id=9003;

练习:修改per01表中,将全部年龄(age)修改为18岁

update per01 set age=18;

6.7 删除表中的数据

格式:

delete from 表名 where 条件;

说明:如果没有where条件,删除全部数据。

案例:删除per01表中,将编号(id)是9001的记录删除

delete from per01 where id=9001;

练习:删除per01表中,将姓名(name)是rose的记录删除

delete from per01 where name='rose';

练习:删除per01表中全部数据

delete from per01;

6.8 删除表

格式:​​drop table 表名;​​

案例:删除per01、per02、per03表

drop table per01;select * from per01;

复习:

创建表

create table 表名( 列名1 数据类型 primary key, 列名2 数据类型, 列名3 数据类型, ...... 列名n 数据类型 );

查看表结构

desc 表名;

向全部列插入数据

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

向指定列插入数据

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

修改(更新)语句

update 表名 set 列名1=该列新值,列名2=该列新值,....列名n=该列新值 where 条件;

删除表中的数据

delete from 表名 where 条件;

删除表

drop table 表名;

6.9 查询语句(重点)

6.9.1 没带条件的查询语句

1)查询全部列数据

select * from 表名;

说明: *表示全部列

案例:查询emp表、dept表、salgrade表全部数据

select * from emp; ---员工表select * from dept; ---部门表select * from salgrade; -工资等级表

2)查询指定列数据

格式:

select 列名1,列名2,....列名n from 表名;

案例:查询emp表中,员工的编号(empno)、员工的姓名(ename)、员工的工资(sal)

select empno,ename,salfrom emp;

案例:查询dept表中,部门的编号(deptno),部门名称(dname),部门地址(loc)

select deptno,dname,locfrom dept;

练习:查询emp表中,员工的编号(empno),姓名(ename),职位(job),工资(sal),入职时间(hiredate)

select empno,ename,job,sal,hiredatefrom emp;

练习:查询salgrade表中,工资的等级(grade),最低工资(losal)、最高工资(hisal)

select grade,losal,hisalfrom salgrade;

6.9.2 给列起别名

格式1:

select 列名1 as 别名1,列名2 as 别名2,.....列名n as 别名n from 表名;

案例:查询emp表中,员工的编号(empno),员工姓名(ename),领导编号(mgr),入职时间(hiredate),并给每列起别名

select empno as 员工编号,ename as 员工姓名,mgr as 领导编号,hiredate as 入职时间from emp;

格式2:

select 列名1 别名1,列名2 别名2,.....列名n 别名n from 表名;

练习:查询dept表中,部门编号(deptno),部门名称(dname),部门地址(loc),并给每列起别名

select deptno 部门编号,dname 部门名称,loc 部门地址from dept;

6.9.3 去掉重复的列值(distinct)

格式:

select distinct 列名 from 表名;

案例:查询emp表中,员工的职位(job)信息(去重)

select distinct job from emp;

练习:查询emp表中,部门的编号(deptno)(-去重)

select distinct deptno from emp;

6.9.4 排序(order by)

格式:

select */列名 from 表名order by 列名1 asc/desc,列名2 asc/desc;

说明:

asc----升序排列(默认)desc—降序排列

案例:查询emp表中,员工的编号(empno),姓名(ename),工资(sal),根据员工的编号降序排列

select empno,ename,salfrom emporder by empno desc;

练习:查询emp表中,员工的姓名(ename),职位(job),工资(sal),部门编号(deptno),根据工资升序排列

select ename,job,sal,deptnofrom emporder by sal asc;

练习:查询emp表中,员工的编号(empno),姓名(ename),工资(sal),部门编号(deptno),首先根据工资升序排列,再次根据员工编号降序排列。

select empno,ename,sal,deptnofrom emporder by sal asc,empno desc;

练习:查询emp表中,员工的编号(empno),姓名(ename),职位(job),部门编号(deptno),先根据部门编号降序排列,再根据员工编号升序排列

select empno,ename,job,deptnofrom emporder by deptno desc,empno asc;

说明:

如果根据2列排序,先根据前面的列排序,如果列值相同,那么在根据第2列排序排序永远放在格式的最后面

6.9.5 条件查询语句

格式:

select */列名 from 表名 where 条件;

说明: 条件包含关系运算符、逻辑运算符、特殊情况

关系运算符: ​​> < = >= <= <>/!=(不等于)​​

案例:查询emp表中,工资(sal)大于1500的,员工的编号,姓名,职位,工资

select empno,ename,job,salfrom empwhere sal>1500;

练习:查询emp表中,部门编号是30号部门的,员工的姓名,职位,工资,部门编号(deptno)

select ename,job,sal,deptnofrom empwhere deptno=30;

练习:查询dept表中,部门地址(loc)是BOSTON的,部门的编号(deptno),部门名称(dname),部门地址

select deptno,dname,locfrom deptwhere loc='BOSTON';

练习:查询emp表中,工资不等于1250的,员工的编号,姓名,工资,根据工资降序排列

select empno,ename,salfrom empwhere sal<>1250 order by sal desc;

逻辑运算符:

and(与)or(或)not(非)

1)and 并且 连接2个或者多个条件

案例:查询emp表中,工资在1000~3000之间的,员工的编号,姓名，职位,工资

select empno,ename,job,salfrom empwhere sal>1000 and sal<3000;

练习:查询emp表中,部门编号是30号部门并且工资大于1000的,员工的编号,姓名,职位,工资,部门编号

select empno,ename,job,sal,deptnofrom empwhere deptno=30 and sal>1000;

2) or 或者

案例:查询emp表中,职位是SALESMAN或者工资大于1000的,员工的编号,职位,工资

select empno,job,salfrom empwhere job='SALESMAN' or sal>1000;

练习:查询emp表中,工资大于等于3000或者部门编号是20号部门的,员工的编号,姓名,职位,工资,部门编号,根据工资升序排列

select empno,ename,job,sal,deptnofrom empwhere sal>=3000 or deptno=20order by sal asc;

3) not 非

案例:查询emp表中,工资不等于1250,员工的编号,姓名,职位,工资

select empno,ename,job,salfrom empwhere sal<>1250;

select empno,ename,job,salfrom empwhere not sal=1250;

6.9.5.1 查询列值为空的情况(is null)

案例:查询emp表中,奖金(comm)为空的,员工的编号,姓名,奖金

select empno,ename,commfrom empwhere comm is null;

练习:查询emp表中,领导编号(mgr)为空的,员工的编号、姓名、职位、领导编号

select empno,ename,job,mgrfrom empwhere mgr is null;

6.9.5.2 查询列值不为空的情况(is not null)

案例:查询emp表中,奖金不为空的,员工的编号,姓名,职位,工资,奖金,根据工资升序排列

select empno,ename,job,sal,commfrom empwhere comm is not nullorder by sal asc;

练习:查询emp表中,有领导编号的,员工的全部列信息

select * from emp where mgr is not null;

6.9.5.3 between…and

格式:

select */列名 from 表名 where 列名 between 初值 and 终值;

说明:使用between…and查询出来的数据,范围在[初值,终值]之间。

案例:查询emp表中,工资在1000~5000之间的,员工的编号,姓名工资

select empno,ename,salfrom empwhere sal>=1000 and sal<=5000;

select empno,ename,salfrom empwhere sal between 1000 and 5000;

6.9.5.4 in 比较一个列中的几个列值

格式:

select */列名 from 表名 where 列名 in(列值1,列值2,.....列值n);

练习:查询emp表中,员工编号是7521, 7698, 7844, 7902的员工信息

select * from emp where empno=7521 or empno=7698 or empno=7844 or empno=7902;

select * from emp where empno in(7521, 7698, 7844, 7902);

练习:查询emp表中,员工职位是MANAGER, SALESMAN, ANALYST的,员工的编号,姓名,职位,工资

select empno,ename,job,salfrom empwhere job in('MANAGER', ' SALESMAN',' ANALYST');

复习:

查询全部列数据

select * from 表名;

查询指定列数据

select 列名1,列名2,....列名n from 表名;

给列起别名

select 列名1 as 别名1,列名2 as 别名2,....列名n as 别名n from 表名;select 列名1 别名1,列名2 别名2,....列名n 别名n from 表名;

去掉重复的列值

select distinct 列名 from 表名;

排序

select */列名 from 表名 order by 列名1 asc/desc,列名2 asc/desc;

条件查询

select */列名 from 表名 where 条件;

关系运算符:​​> < = >= <= <>/!=​​

sal>2000 job=‘MANAGER’

sal<>1250

逻辑运算符: and or not

sal>1000 and sal<5000

deptno=10 and job=‘MANAGER’

deptno=30 or sal>2000

not sal=1250

特殊情况: - 列值为空 comm is null - 列值不为空 mgr is not null - between…and和and相似

select */列名 from 表名 where 列名 between 初值 and 终值;

in 比较一个列中的几个列值

select */列名 from 表名 where 列名 in(列值1,列值2,....列值n);

6.9.5.5 模糊查询(-重点)

格式:

select */列名 from 表名 where 列名 like 条件;

说明条件组成:

%: 表示0个或者多个任意字符_: 表示任意一个字符

案例:查询emp表中,员工姓名的首字母(第1个字母)是M开头的,员工的编号,姓名,职位

Mfsdfdf M===>M%

select empno,ename,jobfrom empwhere ename like 'M%';

案例:查询emp表中,员工姓名尾字母是(最后一个字母)N的,员工的编号,姓名,职位

dsafdN N ==> %N

select empno,ename,jobfrom empwhere ename like '%N';

案例:查询emp表中,员工姓名中包含(含有)字母N的,员工的编号,姓名,工资

fdsN Newr gfNgfg==>%N%

select empno,ename,salfrom empwhere ename like '%N%';

练习:查询emp表中,员工姓名倒数第2个字母是N的,员工的编号,姓名,工资

dfsdNe Nw ==> %N_

select empno,ename,salfrom empwhere ename like '%N_';

练习:查询emp表中,员工姓名正数第3个字母是N的,员工的编号,姓名,工资

select empno,ename,salfrom empwhere ename like '__N%';

6.9.6 处理空值的函数

案例:查询emp表中,员工的编号,姓名,工资,年薪(工资*12)

select empno,ename,sal,sal*12 from emp;

案例:查询emp表中,员工的编号,姓名,工资,奖金(comm),年薪(工资*12+comm)

select empno,ename,sal,comm,sal*12+nvl(comm,0) from emp;

说明:任何数值类型通过“+”和null连接在一起,结果是null,对于这种情况需要使用nvl()函数处理

nvl(列名,数值) 表示如果列值为null,使用数值代替null。

6.9.7 聚合(分组)函数

count()sum()avg()min()max()

1)count(列名/*)

*: 表示统计表中数据的总数量

列名: 表示统计该列中,列值不为空的总数量

案例:统计emp表中,员工的总数量

select count(*) from emp;

案例:统计emp表中,奖金不为空的,员工的总数量

select count(comm) from emp;

练习:统计emp表中,员工职位的总数

select count(distinct job) from emp;

2)sum(列名) 统计列值累加之和

案例:统计emp表中,员工的工资总和

select sum(sal) from emp;

练习:统计emp表中,员工的奖金总和

select sum(comm) from emp;

3)avg(列名) 求平均值

案例:求emp表中,奖金的平均值

select avg(comm) from emp;

练习:求emp表中,工资的平均值

select avg(sal) from emp;

4)min(列名) 求最小值

案例:查询emp表中,员工的最低工资

select min(sal) from emp;

练习:查询emp表中,员工编号的最小值

select min(empno) from emp;

5)max(列名) 求最大值

案例:查询emp表中,员工的最高工资

select max(sal) from emp;

练习:查询emp表中,员工总数,工资总和,平均工资,最低工资,最高工资,并给每列起别名

select count(*) 员工总数,sum(sal) 工资总和,avg(sal) 平均工资,min(sal)

最低工资,max(sal) 最高工资 from emp;

6.9.8 分组查询

解释分组查询:在数据库中的表中,对表中某一列的数据,根据列值分成几组(相同的分成一组),然后对每一组的数据,使用聚合函数,聚合函数经常和分组查询一起使用。

格式:

select 列名/聚合函数from 表名where 条件group by 列名order by 列名/聚合函数 asc/desc;

案例:查询emp表中,每个部门的编号,部门的人数,部门的最高工资

select deptno,count(*),max(sal)from empgroup by deptno;

案例:查询emp表中,工资大于1000的,每个部门的编号,部门的工资总和,部门的平均工资

select deptno,sum(sal),avg(sal)from empwhere sal>1000group by deptno;

练习:查询emp表中,领导编号不为空的,每个部门的编号,部门的最高工资,部门的人数,根据部门编号升序排列

select deptno,max(sal),count(*)from empwhere mgr is not nullgroup by deptnoorder by deptno asc;

练习:查询emp表中,员工姓名中不包含C的,每个职位的名称,职位的最高工资,每个职位的人数,根据人数降序排列

select job,max(sal),count(*)from empwhere not ename like '%C%'group by joborder by count(*) desc;

6.9.9 having语句

解释:分组查询后,得到的结果,再次进行过滤,这个时候使用having语句

格式:

select 列名/聚合函数from 表名where 条件group by 列名having条件order by 列名/聚合函数 asc/desc;

where和having的区别:

where是对整张表的数据进行过滤,可以单独使用having是对分组后的数据进行过滤,

having必须和group by一起使用。

案例:查询emp表中,部门的平均工资大于2000的,每个部门的编号,部门人数,部门的平均工资

select deptno,count(*),avg(sal)from empgroup by deptnohaving avg(sal)>2000;

练习:查询emp表中,工资在1000~5000之间的,每个职位的名称,职位的人数,职位的平均工资,职位的最高工资,要求职位的最高工资小于3000,根据平均工资降序排列

select job,count(*),avg(sal),max(sal)from empwhere sal>1000 and sal<5000group by jobhaving max(sal)<3000order by avg(sal) desc;

6.9.10 常用字符处理函数

1)length(字符串/列名) 统计字符串中字符/列值的个数

案例:查询emp表中,员工的姓名以及姓名的长度

select ename,length(ename) from emp;

案例:查询helloworld字符串的长度

说明:如果试题中没有表,可以使用系统自带表—dual可以验证各种函数的使用

select length('helloworld') from dual;

2)substr(参数1,参数2,参数3) 截取字符串—重点

参数1: 要截取的字符串/列名参数2: 从哪里开始截取

如果是正数表示从正数第几位开始截取,如果是负数,表示从倒数第几位开始截取

参数3:截取的数量

select substr('helloworld',-3,2) from dual;

select substr('helloworld',5,3) from dual;

练习:查询emp表中,员工的姓名以及姓名最后2个字母

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

3)拼接字符串

使用||拼接字符串

select '窗前明月光' || '疑是地上霜' || '举头望明月' || '低头思故乡' from dual;select ename || job from emp;```sql- 使用concat拼接字符串```sqlconcat(字符串/列名,字符串/列名)

select ename,job,concat(ename,job) from emp;

复习:

模糊查询

select 列名 from 表名where 列名 like 条件;

条件:

%: 0个或者多个任意字符

_: 1个任意字符

处理空值的函数

nvl(列名,数值) 如果列值为null,使用数值代替null

聚合函数

count(列名/*)

*:表示统计表中数据总数量

列名:列值不为空的,数据总数量

sum(列名) 求和

avg(列名) 求平均值

min(列名) 求最小值

max(列名) 求最大值

分组查询

聚合函数经常和分组查询一起使用

select 列名/聚合函数from 表名 ---1where 条件 ---2group by 列名 ----3order by 列名/聚合函数 asc/desc;

having语句

select 列名/聚合函数from 表名 ---1where 条件 ---2group by 列名----3having 条件order by 列名/聚合函数 asc/desc;

6.9.11 常用数值处理函数

1)round(数值,位数) 四舍五入函数

如果位数>0,表示小数点后保留几位小数,如果位数=0,不保留小数,位数<0,小数点之前第几位进行四舍五入

select round(35.376,2) from dual;select round(35.376,1) from dual;select round(35.576,0) from dual;select round(35.576,-1) from dual;----40select round(35.576,-2) from dual;----0

2)trunc(数值,位数) 截取函数

如果位数>0,表示小数点后保留几位小数,如果位数=0,不保留小数,位数<0,舍掉小数点之前第几位

select trunc(35.376,2) from dual;--35.37select trunc(35.376,1) from dual;--35.3select trunc(35.576,0) from dual;--35select trunc(35.576,-1) from dual;--30select trunc(35.576,-2) from dual;--0

6.9.12 常用日期处理函数

常见的日期格式:

系统默认格式: ​​dd-mon月-yy​​

年月日格式: ​​yyyy-mm-dd​​

年月日时分秒格式: ​​yyyy-mm-dd hh24:mi:ss​​

1)sysdate 当前系统日期

案例:查询当前系统时间

select sysdate from dual;

练习:查询昨天、今天、明天的日期

select sysdate-1,sysdate,sysdate+1 from dual;

2)to_char(date,‘日期格式的一部分’)

案例:查询当前的月份

select to_char(sysdate,'mm') from dual;

练习:查询当前时间的分钟数

select to_char(sysdate,'mi') from dual;

练习:查询emp表中,12月份入职的,员工的编号,姓名,职位,入职时间(hiredate)–重点

select empno,ename,job,hiredatefrom empwhere to_char(hiredate,'mm')='12';

3)to_date(‘特殊日期格式的字符串’,‘日期格式’)

将字符类型按照某种日期格式,转换为日期类型,经常用于插入语句操作

案例:向emp表中插入2条记录

empno ename hiredate2345 ROSE 2019-5-9 2346 JACK 2000-10-112347 TOMS 2008-8-8

insert into emp(empno,ename,hiredate) values(2345,'ROSE',to_date('2019-5-9','yyyy-mm-dd'));insert into emp(empno,ename,hiredate) values(2346,'JACK',to_date('2000-10-11','yyyy-mm-dd'));insert into emp(empno,ename,hiredate) values(2347,'TOMS',to_date('2008-8-8','yyyy-mm-dd'));

6.9.13 多表连接查询

说明:多表连接查询就是,通过一条SQL语句,可以将分布在不同表中的数据,一次查询出来------多表连接查询

格式:

select 别名1.*/列名,别名2.*/列名,...from 表1 别名1,表2 别名2,.....where 关联条件;

案例:查询emp表中,员工的编号,姓名,职位以及dept表中,部门的编号,部门名称

select e.empno,e.ename,e.job,d.deptno,d.dnamefrom emp e,dept dwhere e.deptno=d.deptno;

说明:如果2张表中的,关联条件是通过“=”相连接的,称为等值连接查询,特点是把2张表中有关联的数据全部查询出来。

案例:查询emp表中,工资在1000~5000之间的,员工的编号,姓名，职位,工资以及dept表中全部列的数据

select e.empno,e.ename,e.job,e.sal,d.*from emp e,dept dwhere e.deptno=d.deptno and e.sal>1000 and e.sal<5000;

练习:查询emp表中,工资大于1000的,员工的编号,姓名,职位,工资以及dept表中,部门的名称(dname),部门地址(loc),根据工资进行降序排列

select e.empno,e.ename,e.job,e.sal,d.dname,d.locfrom emp e,dept dwhere e.deptno=d.deptno and e.sal>1000order by e.sal desc;

练习:查询emp表中,员工姓名中不包含字母K的并且在10和20号部门下的,员工的编号,姓名,职位,以及dept表中部门名称,部门地址

select e.empno,e.ename,e.job,d.dname,d.locfrom emp e,dept dwhere e.deptno=d.deptno and not e.ename like '%K%' and (e.deptno=10 or e.deptno=20);

select e.empno,e.ename,e.job,d.dname,d.locfrom emp e,dept dwhere e.deptno=d.deptno and not e.ename like '%K%' and e.deptno in(10,20);

2)非等值连接查询

说明:如果2张表中,关联条件不是通过“=”相连接的,称为非等值连接查询

案例:查询emp表中,员工的编号,姓名,职位,工资以及salgrade表中,工资等级(grade),最低工资(losal),最高工资(hisal)

emp表中的sal,一定在salgrade表中的最低工资(losal)和最高工资(hisal)之间

e.sal between s.losal and s.hisal

select e.empno,e.ename,e.job,e.sal,s.grade,s.losal,s.hisalfrom emp e,salgrade swhere e.sal between s.losal and s.hisal;

3)自连接

自连接查询就是,列与列之间存在关联关系,我们将1张表看成2张表,使用等值连接方式进行查询。

练习:查询emp表中,员工的编号,姓名,职位,领导编号(mgr),领导姓名

select e.empno,e.ename,e.job,e.mgr,m.enamefrom emp e,emp mwhere m.empno=e.mgr;

6.9.14 子(嵌套)查询

子查询就是在一个查询语句的内部,又包含一个查询语句。

案例:查询emp表中,工资大于平均工资的,员工的编号,姓名,职位,工资

1)求出平均工资

select avg(sal) from emp;

2)合成

select empno,ename,job,salfrom empwhere sal>(select avg(sal) from emp);

练习:查询emp表中,工资大于30号部门最高工资的,员工的编号,姓名,工资

select empno,ename,salfrom empwhere sal>(select max(sal) from emp where deptno=30);

练习:查询dept表中,部门地址(loc)是DALLAS下的,员工的编号,姓名,职位，工资

select e.empno,e.ename,e.job,e.salfrom emp e,dept dwhere e.deptno=d.deptno and d.loc='DALLAS';

select empno,ename,job,sal from emp where deptno=(select deptno from dept where loc='DALLAS');

练习:查询emp表中和JONES是同一个部门的,员工的编号,姓名,职位,部门编号

select empno,ename,job,deptno from emp where deptno=(select deptno from emp where ename='JONES');

复习:

常见字符处理函数

length(字符串/列名)统计字符/列值的个数substr(参数1,参数2,参数3)截取字符串

参数1:被截取的字符串/列名

参数2:从哪里开始截取

参数3:截取个数

拼接字符串 || 或者 concat

||可以无限拼接concat(字符串/列名,字符串/列名)

常见数值处理函数

round(数值,位数)四舍五入函数trunc(数值,位数)截取函数

常见日期处理函数

sysdate获取系统当前日期to_char(date,‘日期格式的一部分’)to_date(‘日期格式的字符串’,‘日期格式’)

多表连接查询

select 别名1.*/列名,别名2.*/列名...from 表1 别名1,表2 别名2....where 关联条件;

6.9.15 事务控制语句

1)提交事务—commit

create table test01(id number(4) primary key,name varchar2(30));insert into test01 values(101,'乔峰');insert into test01 values(102,'虚竹');select * from test01;commit;

2)回滚事务—rollback

说明:回滚事务只可以撤销未保存(没有提交事务的)的事务

insert into test01 values(103,'段誉');insert into test01 values(104,'许仙');select * from test01;rollback;

七、约束(constraint)

说明:在创建表的时候,对表中的列设置一些规则,在插入数据的时候,只有满足这些规则才可以插入数据,我们把这些规则叫做约束。

约束的分类

主键约束(primary key)唯一约束(unique)检查约束(check)默认值约束(default)非空约束(not null)

7.1 主键约束(primary key)

说明:主键约束就是用来标识表中唯一的一条记录,被主键约束所修饰的列,其列值是唯一且非空的,一张表中,只可以有一个主键约束。

1)创建表时,添加主键约束

create table test02(id number(4) primary key,name varchar2(30),age number(3));insert into test02 values(101,'rose',22);insert into test02 values(101,'jack',25);insert into test02(name,age) values('toms',30);

2)联合主键(复合主键)—重点

使用主键约束修饰2个列或者多列叫做复合主键

create table test03(id number(4),name varchar2(30),age number(3),address varchar2(50),constraint pk_id_name_test03 primary key(id,name));insert into test03 values(101,'tom',20,'北京');insert into test03 values(101,'toms',22,'上海');insert into test03 values(101,'toms',25,'重庆');

3)修改表时,添加主键约束

格式:

alter table 表名 add constraint约束名 primary key(列名1,列名2....列名n);

案例:创建一张表,表名为test04,表中的字段有: id number(4),name varchar2(30), sex char(3),email varchar2(30),修改表时,对id列添加主键约束,约束名为pk_id_test04

create table test04(id number(4),name varchar2(30),sex char(3),email varchar2(30));alter table test04 add constraint pk_id_test04 primary key(id);insert into test04(id,name) values(101,'金庸');insert into test04(id,name) values(101,'古龙');

练习:创建一张表,表名为test05,表中包含的字段有:tid number(4)、tname varchar2(30)、passwd varchar2(30)

修改表时,对tid和tdname添主键约束,约束名为pk_tid_tname_test05

create table test05(tid number(4),tname varchar2(30),passwd varchar2(30));alter table test05 add constraint pk_tid_tname_test05 primary key(tid,tname);

4)删除主键约束

格式1:

alter table 表名 drop primary key;

格式2:

alter table 表名 drop constraint 约束名;

说明:可以使用此格式,删除主键约束、唯一约束、检查约束。

案例:删除test02表中的主键约束

alter table test02 drop primary key;

案例:删除test05表中的主键约束,约束名为pk_tid_tname_test05

alter table test05 drop constraint pk_tid_tname_test05;

练习:删除test04表中的主键约束,约束名为pk_id_test04

alter table test04 drop constraint pk_id_test04;

7.2 唯一约束(unique)

说明:使用唯一约束可以修饰1列或者多列的组合值,使其具有唯一性,防止用户输入重复数据

被唯一约束修饰的列,列值可以为null一张表中,可以有多个唯一约束

1)创建表时,添加唯一约束

create table test06(id number(4) primary key,name varchar2(30) unique,age number(3),email varchar2(30));insert into test06(id,name) values(101,'小花');insert into test06(id,name) values(102,'小花');

2)修改表时,添加唯一约束

格式:

alter table 表名 add constraint 约束名 unique(列名1,列名2,.....列名n);

案例:对test06表中的,email列,添加唯一约束,约束名为uq_email_test06

alter table test06 add constraint uq_email_test06 unique(email);

练习:创建一张表,表名为test07,表中包含的字段有: id number(4) pk,

name varchar2(30), phone number(7),address varchar2(50)

修改表时,对name和address列添加唯一约束,约束名为uq_name_address_test07

create table test07(id number(4) primary key,name varchar2(30),phone number(7),address varchar2(50));alter table test07 add constraint uq_name_address_test07 unique(name,address);

3)删除唯一约束

格式:

alter table 表名 drop constraint 约束名;

案例:删除test07表中,唯一约束,约束名为uq_name_address_test07

alter table test07 drop constraint uq_name_address_test07;

练习:删除test06表中,唯一约束,约束名为uq_email_test06

alter table test06 drop constraint uq_email_test06;

复习:

主键约束(primary key)

1)联合主键

create table test(id number(4),name varchar2(30),age number(3),constraint pk_id_name_test priamry key(id,name));

唯一约束(unique)

主键约束一张表中只可以有一个,而唯一约束一张表中可以有多个被主键约束所修饰的列,其列值是唯一且非空而唯一约束所修饰的列,列值是唯一的,可以为空

检查约束(check)

check(age between 1 and 150)check(sex in('男','女'))check(email like '%@%')

7.3 检查约束(check)

说明:检查约束就是用于限定某列,必须满足某种特定的条件,防止用户输入非法数据

1)创建表时,添加检查约束

create table test08(id number(4) primary key,name varchar2(30) unique,sex char(3) check(sex in('男','女')),age number(3), email varchar2(30));insert into test08(id,name,sex) values(101,'东方不败','中');

2)修改表时,添加检查约束

格式:

alter table 表名 add conctraint 约束名 check(条件);

练习:对test08表中,age列,添加检查约束,要求年龄在1~150之间,约束名chk_age_test08

alter table test08 add constraint chk_age_test08 check(age between 1 and 150);insert into test08(id,name,sex,age) values(102,'陆小凤','男',200);

练习:对test08表中,email列添加检查约束,要求email中必须包含@符号,约束名为chk_email_test08,并验证

alter table test08 add constraint chk_email_test08 check(email like '%@%');

3)删除检查约束

格式:

alter table 表名 drop constraint 约束名;

案例:删除test08表中,email列上的检查约束,约束名为chk_email_test08

alter table test08 drop constraint chk_email_test08;

7.4 默认值约束(default)

当执行插入操作的时候,被默认约束所修饰的列,如果没有插入数据,系统会自动将默认值作为该列列值。

1)创建表时,添加默认值约束

create table test09(id number(4) primary key,name varchar2(30) unique,sex char(3) check(sex in('男','女')),hiredate date default sysdate,age number(3) default 18,address varchar2(50));insert into test09(id,name,sex) values(101,'tom','男');select * from test09;

2)修改表时,添加默认值约束

格式:

alter table 表名 modify 列名 数据类型 default 默认值;

案例:修改test09表中,age列上的默认值约束,将默认值修改为20

alter table test09 modify age number(3) default 20;insert into test09(id,name,sex) values(111,'toms','男');select * from test09;

练习:修改test09表,对address添加默认值约束,默认值设置为北京,并验证

alter table test09 modify address varchar2(50) default '北京';insert into test09(id,name,sex) values(112,'rose','女');select * from test09;

3)删除默认值约束

格式:

alter table 表名 modify 列名 数据类型 default null;

案例:删除test09表中,age列上的默认值约束

alter table test09 modify age number(3) default null;insert into test09(id,name,sex) values(119,'jack','男');select * from test09;

练习:删除test09表中,hiredate列上的默认值约束

alter table test09 modify hiredate date default null;

7.5 非空约束(not null)

当执行插入操作的时候,被非空约束所修饰的列,列值不能为空

1)创建表时,添加非空约束

create table test10(id number(4) primary key,name varchar2(30) unique,sex char(3) check(sex in('男','女')),hiredate date default sysdate,age number(3) not null,address varchar2(50));insert into test10(id,name,sex) values(101,'西门吹雪','男');

2)修改表时,添加非空约束

格式:

alter table 表名modify(列名1 not null)modify(列名2 not null)....modify(列名n not null);

案例:修改test10表,对address列添加非空约束

alter table test10 modify(address not null);insert into test10(id,name,sex,age) values(102,'叶孤城','男',20);

练习:创建一张表,表名test11,表中包含的字段有:

id number(4) pkname varchar2(30)sex char(3)age number(3)

修改表时,对name和sex列添加非空约束

create table test11(id number(4) primary key,name varchar2(30),sex char(3),age number(3));alter table test11modify(name not null)modify(sex not null);

3)删除非空约束

格式:

alter table 表名modify(列名1 null)modify(列名2 null)....modify(列名n null);

案例:删除test10表中,address列上的非空约束

alter table test10 modify(address null);

八、索引(index)

索引是建立在表中列上的,数据库对象,索引可以提高查询速度。

1)创建索引

格式:

create index 索引名称 on表名(列名);

create table test12(id number(4) primary key,name varchar2(30),address varchar2(50));create index index_name_test12 on test12(name);select id,name from test12 where name='rose'; 3s

说明:

被主键约束所修饰的列,自带索引被唯一约束所修饰的列,自带索引

2)删除索引

格式:

drop index 索引名称;

案例:删除test12表中的,索引,索引名称为index_name_test12

drop index index_name_test12;

九、序列(sequence)

序列是数据库中的一个对象,通过序列可以生成自动增长的数字,经常使用序列生成的数字,作为主键所修饰列的列值。

1)创建序列

create sequence 序列名称;

2)nextval

生成一个新的数字,第1次执行的时候,默认值为1

案例:创建一个序列,序列名称为seq_id01

create sequence seq_id01;select seq_id01.nextval from dual;

案例:创建一张表,表名为test13,表中包含的字段有: id number(4) pk

name varchar2(30),使用序列生成自动增长的数字,作为主键所在的列值,序列名称为seq_n01

create table test13(id number(4) primary key,name varchar2(30));create sequence seq_n01;insert into test13 values(seq_n01.nextval,'天蚕土豆丝');insert into test13 values(seq_n01.nextval,'我吃西红柿');insert into test13 values(seq_n01.nextval,'辰东');insert into test13 values(seq_n01.nextval,'鱼人二代');

3)序列的属性

序列的初始值

start with 初始值

序列的增长步长

increment by 步长

序列的最小值minvalue 最小值序列的最大值maxvalue 最大值

案例:创建一个序列,序列名为seq_n02,初始值为:100,增长步长为:5,最小值为10,最大值为200

create sequence seq_n02start with 100increment by 5minvalue 10maxvalue 200;select seq_n02.nextval from dual;

十、视图(view)

视图就是1张虚拟表,可以通过视图查询一张或者多张表的数据

10.1 创建视图

create view 视图名称as查询语句;

10.2 视图查询

案例:查询emp表中的全部数据,将结果给视图view_emp01

create view view_emp01asselect * from emp;select * from view_emp01;

练习:通过视图view_emp01,查询员工的编号,姓名,职位,工资,根据工资降序排列

select empno,ename,job,salfrom view_emp01order by sal desc;

练习:通过视图view_emp01,查询工资大于1000的并且姓名中含有字母N的,员工的编号,姓名,职位,工资,根据编号升序排列

select empno,ename,job,salfrom view_emp01where sal>1000 and ename like '%N%'order by empno asc;

练习:创建一个视图,视图名称为:view_emp_dept,查询emp表中的

全部数据以及dept表中的,部门名称(dname)和部门地址(loc),作为视图view_emp_dept的结果

create view view_emp_deptasselect e.*,d.dname,d.locfrom emp e,dept dwhere e.deptno=d.deptno;select * from view_emp_dept;

练习:查询工资在1000~5000之间的并且上级领导编号不为空的,员工的编号,姓名,职位,工资,上级领导编号(mgr),部门地址,首先根据工资降序排列,再次根据编号升序排列

select e.empno,e.ename,e.job,e.sal,e.mgr,d.locfrom emp e,dept dwhere e.deptno=d.deptno and e.sal>1000 and e.sal<5000 and e.mgr is not nullorder by e.sal desc,e.empno asc;select empno,ename,job,sal,mgr,locfrom view_emp_deptwhere sal>1000 and sal<5000 and mgr is not nullorder by sal desc,empno asc;

练习:创建一个视图,视图名称为view_emp_sal,查询工资在1000~5000之间的,员工的编号,姓名,职位,工资,工资等级(grade),最低工资(losal),最高工资(hisal),作为视图view_emp_sal结果

emp<------->salgrade

create view view_emp_salasselect e.empno,e.ename,e.job,e.sal,s.*from emp e,salgrade swhere e.sal between s.losal and s.hisal and e.sal>1000 and e.sal<5000;

10.3 修改视图

格式:

create or replace view 视图名称as查询语句;

案例:修改view_emp01视图,查询10,30号部门的,员工的编号,姓名,职位,工资,部门编号,作为视图结果

create or replace view view_emp01asselect empno,ename,job,sal,deptnofrom empwhere deptno in(10,30);select * from view_emp01;

练习:修改视图view_emp_dept,查询dept表中全部数据,作为视图结果

create or replace view view_emp_deptasselect * from dept;select * from view_emp_dept;

10.4 通过视图实现对表的操作

create view view_dept01asselect * from dept;select * from view_dept01;

1)插入操作

insert into view_dept01 values(50,'TESTING','BEIJING');insert into view_dept01 values(60,'DEVELOPER','SAHNHA');insert into view_dept01 values(70,'UI','HANGZHOU');select * from view_dept01;select * from dept;

2)修改操作

案例:通过视图view_dept01,修改部门编号是60的,将地址(loc)修改为SHENZHENG

update view_dept01 set loc='SHENZHENG' where deptno=60;select * from view_dept01;select * from dept;

练习:通过视图view_dept01,将部门地址(loc)全部修改为BEIJING

update view_dept01 set loc='BEIJING';

3)删除操作

练习:删除view_dept01视图中,50,60,70号部门的记录

delete from view_dept01 where deptno in(50,60,70);

10.5 只读视图(-重点)

说明:只能查询,不能修改

格式:

create view 视图名称as查询语句with read only;

案例:创建一个只读视图,视图名称为view_dept02,并验证

create view view_dept02asselect * from deptwith read only;insert into view_dept02 values(50,'TEST','BEIJING');

10.6 删除视图

格式:

drop view 视图名称;

案例:删除view_dept02视图

drop view view_dept02;select * from view_dept02;

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。