前言
T1.什么是数据库?
存储数据的仓库
T2.常见的数据库有哪些?
· 微软-------SQLServer
· 微软-------Access
· IBM-------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 Developer
· Navicat
3.3 Oracle数据库登录
1. 登录到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');
复习:
1. 常见字符处理函数
· length(字符串/列名) 统计字符/列值的个数
· substr(参数1,参数2,参数3) 截取字符串
参数1:被截取的字符串/列名
参数2:从哪里开始截取
参数3:截取个数
拼接字符串 || 或者 concat
· || 可以无限拼接
· concat(字符串/列名,字符串/列名)
1. 常见数值处理函数
· round(数值,位数) 四舍五入函数
· trunc(数值,位数) 截取函数
1. 常见日期处理函数
· sysdate 获取系统当前日期
· to_char(date,'日期格式的一部分')
· to_date('日期格式的字符串','日期格式')
1. 多表连接查询
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;