什么是数据库？
存储数据的仓库
优点:
1.可存储大量数据
2.方便检索
3.保持数据的一致性、完整性
4.安全，可共享
解决的问题?
数据的持久化(把数据写到磁盘中)
安装看安装文档:
口令管理:
sys/change_on_install 系统用户 为了方便密码可以不用change_on_install,而是用sys
system/manager 系统用户
scott/tiger 普通用户
sh/sh 大数据用
判断是否安装安装成功:
1.查看数据库安装的服务都是否启动了
2.开始菜单栏有很多数据库的图片
3. 通过 sqlplus 登录普通用户
登录的命令:
用户: systen/manager
scott/tiger
普通用户登录:
scott/tiger
登录详细操作:
sqlplus scott/tiger@localhost:1521/orcl
用户切换:
conn system/manager as sysdba;
管理员用户简写:
connn / as sysdba;
显示当前的用户:
show user
清屏的命令:
host cls
用户的锁定:
alter user scott account lock; 用户锁定了
alter user scott account unlock 解锁
查表的操作:
select * from emp;
设置输出的格式大小:
set linesize 120;
set pagesize 30
4张原始的表的介绍:
emp 雇员表
dept 部门表
salgrade 薪资等级表
查看当前用户的所有表"
select * from tab;
二:用户管理: (系统管理员管理用户)
system /sys 系统管理员
1.创建用户:
语法: create user 用户名 identified by 密码;
注意:
1, 管理员才能创建用户
2. 创建用户需要授权才能登陆 管理员要给用户授权
2.用户授权:
用户 (张三)
权限 删除,添加,查询,修改
角色 管理员,普通用户,经理 (角色的目的更好的管理权限)
数据库中的角色: connect resource dba;
授权: 可以给角色 获取权限
语法: grant 角色、权限 to 用户名;
grant connect to root; //没有创建就表的权限 只有连接的权限
grant resource to root; // 可以处理资源的权限(建表 插入数据...)
创建表的语句:
create table t_stu(
sid number(3),
sname varchar2(20)
);
回收权限:
revoke 角色/权限 from 用户;
revoke connect from root;
修改密码:
alter user 用户名 identified by 密码;
用户的删除:
drop user 用户名 cascade
cascade(联机删除 把用户产生的数据也给删除)
– 表的操作
java 中创建一个类
public class Person{
private int age; // 属性 数据类型 属性名称;
private String name;
}
sql表也是类似:
1.表的创建
语法:
create table t_name(
字段1 数据类型1(数据长度),
字段2 数据类型2(数据长度),
字段3 数据类型3(数据长度),
…
);
create table t_student(
sid number(4),
sname varchar2(20)，
sage number(2)
);
CREATE TABLE T_STU(
SID NUMBER(3),
SNAME VARCHAR2(20)
);
注意事项:
1.oracle 是不区分大小,数据库在执行sql语句 会把我们的sql 小写转成大写
2.如果表存在 不能重复创建
3，每个字段使用逗号隔开,最后一个字段不加逗号
4. 表的命名规则:t_表名
- 删除表:
drop table 表名 [cascade consistent];
drop table t_stu;
– select * from t_stu;
3.表的字段添加
alter table 表名 add 字段名 字段类型;
alter table t_student add ssex varchar2(2);
4.修改字段类型
alter table 表名 modify 字段 字段类型
alter table t_student modify ssex number(1);
5.修改字段名:
alert table 表名 rename column 源字段 to 新字段;
alter table t_student rename column ssex to sex;
6.删除字段
alter table 表名 drop column 字段名;
alter table t_student drop column sex;
– 注释
分类:
1. 普通注释 –
2. 表的注释
表的注释:
comment on table 表名 is '解释';
comment on table t_student is '学生表';
字段的注释:
comment on column 表名.字段名 is '解释说明';
comment on column t_student.sid is '学生id';
comment on column t_student.sname is '学生姓名';
comment on column t_student.sage is '学生年龄';
– Oracle中数据类型
– 1.字符型
1. char 字符型[长度固定]
能够存储最大长度是2000 个字节
系统会分配固定长度空间 不会应为数据大小而做出调整 节约计算空间的时间 提高效率
2. varchar2 字符型[长度是可变]
能存存储最大长度是4000字节
相比char 内存利用更高 会计算实际存储长度动态分配内存
3.long 字符型[大型字符]
能存存储最大2G文本
create table testChar(
text1 char(4),
text2 varchar2(4)，
text3 long，
text4 char，
-- text5 varchar2(10)
);
注意: char 可以不给长度 默认是1个长度 varchar2 必须给长度
–2.数据型
number 数值型 可以存储小数 可以存储整数
格式: number[p(，s)]
p 整数 s表示小数
number 默认长度是32位 最高精度38位
number(5) 表示整数 长度是5
number(5,2) 表示真整数是(5-2)位 小数2位 如果小数不够补0 如果小 太多了 四舍五入保留2位小数
create table testNumber(
num1 number,
num2 number(5),
num3 number(5,2)
);
–3.时间型
date 日期 精确到秒 2019-9-7:11:22:33
timestamp 时间戳 精确到 秒后面的6位 2019-9-7:11:22:33.000000
create table testDate(
bir date,
mytime timestamp
);
– 4.大数据类型 LOB
blob 存储大型二进制文件
clob 存储字符文件,类似 long
– 约束
-
非空约束
非空 限定某个字段不能为空
create table 表名(
字段 字段类型 not null
);
create table testNull(
id number(3) not null,
name varchar2(10)
);
2.默认值的约束
如果某个字段没有值 可以设置一个默认值create table teseDefault(
id number(10) not null,
name varchar2(10) default ‘逍遥’,
age number(2) default 18
); -
唯一约束
唯一约束: 表示某个字段值在表中是唯一标识
create table testUnique(
id number(5) unique not null,
name varchar(10) default ‘老王你’
);
注意:
1.如果2个数据不为空 唯一约束不相等 （如果都为空数据是 可以插入）
2. 一个字段可以加入多个约束
4.主键约束 unique + not null
表示是 设定的字段 是唯一约束不能为空
主键是唯一标识符 (加快查询效率 自动创建索引)
create table testPrimary(
id number(4) primary key,
name varchar2(10) default '老宋'
);
联合主键:
create table testKey(
id number(3),
name varchar2(20)
);
-- 创建表后给testKey 添加唯一约束
alter table 表名 add constraint 主键标识符 primary key(字段名);
alter table testKey add constraint pk_key primary key (id);
-- 多个字段联合起来组成主键
alter table testKey add constraint pk_key primary key (id，name);
注意:
1.主键是唯一标识 一张表 只能有一个主键
2. 多个字段联合为一个联合主键
3. 一张表可有多个 unique + not null 但是只能有一个主键
5.外键约束:
外键是从表中重复出现,在主表中作为主键存在 外键关联2张表
主表: 主键的表 一方
从表：外键的表 多方
多方关联一方 多方设置外键
班级 主表 一方 员工 多方
学生 从表 多方 部门 一方
java中的对象模型:
class Student{
private int sid;
private String name;
private int ClassId;
}
class Calss(
private int Classid；
private String className;
);
数据库中的模型:
-- 从表
create table t_stu(
sid number(3),
sname varchar2(20),
classId number(4)，
constraint fk_stu_class foreign key (classId) references t_class(cid)
);
– constraint fk_stu_class foreign key (classId) 把 字段 classId声明为外键 references t_class(cid)关联其他表的字段
– 主表
create table t_class(
cid number(4) primary key，
cname varchar2(10)
);
– 创建表完成后添加外键
alter table t_stu add constraint fk_stu_class foreign key(classId) references t_class(cid);
外键的特点:
1. 外键可以在从表中重复出现 ，他在主表中是主键
2. 一张表可以有多个外键
3. 外键可以为null
4. 外键的作用是关联多种表,可以建立表之间的关系 (一对一 一对多 多对多)
5. 删除主表时候 如果 从表有使用主表的数据 是不能删除的， 从表的数据可以直接删除
6. 2表关联了 先删除 从表 在删除主表
7。 关键是多表查询单 必要条件
表的关系:
一对一 老公--- 老婆
一对多 部门--- 员工
多对多 学生----老师
练习:
myemp 表
ename
eno
dno
mydept表
dname
dno
创建表 设置外键
6.check约束:
某个字段可以在一定的设置范围存储
create table t_stu(
sid number(5) primary key,
sname varchar2(20)，
-- sage number(2) check(sage>20)
-- sage number(2) check(sage = 18 or sage=20)
sage number(2) check(sage in (18,19,20))
);
check 约束可以给字段加运算符 和一些关键字:
> < =
and or
in / not in
is null / is not null
建表以后如何添加check约束
alter table t_stu add constraint ck_stu_age check(sage in (18,19,20));
– DML
数据操纵语言用于插入、修改和删除数据
数据操纵语言是最常用的SQL命令
数据操纵语言命令包括：
INSERT
1,插入操作 表中出入一行数据
方法1:
insert into 表名(字段1，字段2…) values(val1,val1 …);
create table t_stu(
sid number(2),
sname varchar2(20),
sage number(2)
);
insert into t_stu(sid,sname,sage) values (1,'慕容复'，20);
insert into t_stu(sid,sname,sage) values (1,'王语嫣'，20);
insert into t_stu(sid,sname) values (1,'虚竹');
commit;
commit;
注意:
1. 字符串的插入用 '' 包裹
2. 插入数据在内存中 (事务提交)
3. 几个表的就是几个val值
方式2:
插入所有的字段的时候 可以省略字段名
insert into 表名 values (val1,val2...)
insert into t_stu values (3,'段正淳'，50);
多表数据的插入:
create table t_stu_copy(
sid number(2),
sname varchar2(20),
sage number(2)
);
插入多条记录
-- 把 t_stu 表中数据查出来 插入到 t_stu_copy
insert into t_stu_copy(sid,sname) select sid,sname from t_stu;
-- 复制表
复制表结构和数据
create table t_stu_copy1 as select * from t_stu;
仅仅复制表结构
-- where 1 != 1 不把数据查出来
create table t_stu_copy2 as select * from t_stu where 1 != 1;
– 序列: 是oracle 独有的 用来结果我们的插入实现主键自动增长的操作
创建序列的语法:
create sequence SEQ_STU
minvalue 1 -- 最小是 1
maxvalue 9999 -- 最大
start with 2 -- 开始位置
increment by 1 -- 增量
cache 20; -- 缓存大小
序列的使用方式:
nextval: 获取下一值的序列
currval; 获取当前会话的序列
结合 插入语句使用 序列: (实现 表的主键字段自动增长)
-- dual (伪表 专门用来写测试sql 的一些语句)
select seq_stu.nextval from dual; -- 6
select seq_stu.currval from dual;-- 6
create table t_stu(
sid number(2) primary key,
sname varchar2(20),
sage number(2)
)
SEGMENT CREATION IMMEDIATE;
insert into t_stu(sid,sname,sage) values (seq_stu.nextval,'鸠摩智',45);
问题? 插入的序列从 2开始 ?
·Oracle从 11.2.0.1版本开始，提供了一个“延迟段创建”特性：即
当我们创建了新的表(table)和序列(sequence)，
在插入(insert)语句时，序列会跳过第一个值(1)。
所以结果是插入的序列值从 2(序列的第二个值) 开始， 而不是 1开始。
解决办法:
创建表时让seqment立即执行，
SEGMENT CREATION IMMEDIATE;
注意:
1.每次执行序列 会自动增长
2.序列本身是一个变化的数据 可以独立使用 可以交互使用(不建议交互使用)
3 nextval 获取下一序列 currval 获取当前的序列 从新开始了一个会话 不能获取 当前会话序列(currval)
需要先获取下一个序列 才可以 currval
4. 创建序列时候 默认从2开始 解决(创建表的时候 添加 SEGMENT CREATION IMMEDIATE)
UPDATE 更新语句
语法:
update 表名 set 字段1= val1，字段2=val2 where 判断条件;
create table t_person(
pid number(2)，
pname varchar2(20),
page number(2)
);
insert into t_person(pid,pname,page) values (1,'乔峰',45);
insert into t_person(pid,pname,page) values (2,'段誉',35);
insert into t_person(pid,pname,page) values (3,'段正淳',42);
insert into t_person(pid,pname,page) values (4,'丁春秋',60);
insert into t_person(pid,pname,page) values (5,'无崖子',70);
commit;
-- 修改 pid 为1 数据
update t_person set pname = '王语嫣' where page = 45;
-- 修改多条数据 or 或者 in
update t_person set pname = '秦红棉' where pid = 2 or pid = 3;
update t_person set pname = '李秋水',page = 33 where pid in (3,4);
-- 没有给判断条件
update t_person set pname = '王语嫣';
- 注意:
修改的时候一定要把加 where判断条件
DELETE
语法: delete from 表名 where 条件
-- 通过id删除
delete from t_person where pid = 1;
-- 删除需要带条件
delete删除 先将删除的数据放在内存中 执行commit 才是删除数据库
truncate 也 可以删除数据 这种删除是不经过内存 直接删除
SELECT
查询语句:(以系统自带的3张表进行查询)
查询分类:
单表查询:
1. 基础查询
2. 条件查询
3. 模糊查询
4. 排序查询
5. 聚合函数 + 分组
6. 子查询
7 分页查询
多表从查询:
1. 合并结果集
2. 连接查询
3. 子查询
1. 基本查询:
语法:
1. select * from 表名; 查询所有的字段
2. select 字段 as [别名] from 表名; 查询一些字段
-- 查询所有的员工信息
select * from emp;
-- 查询编号,姓名 职位 薪资
select empno,ename,job,sal from emp;
查询结果显示标题 (编号,姓名 职位 薪资)
-- as 添加别名 (员工编号")
-- as 可以省略 "" 可以省略
select empno as "员工编号" ,ename 姓名,job,sal from emp;
-- 查询所有emp 的职位
select job from emp; -- 有重复 去重复
-- 去重复 distinct
select distinct job from emp;
-- 查询中进行四则运算 查员工年薪
select ename,job,(sal * 12 + 200) 年薪 from emp;
-- 查询常量的使用
select '员工',ename,job from emp; -- 可以是字符串 常量
select 9527,ename,job from emp; -- 可以是数组常量 (省略 '')
-- 可以使用 || 拼接符
select empno || ename from emp;
-- 不能""
select '编号:' || empno || '姓名:'|| ename 员工信息 from emp;
-- 查询的时候 表 可以使用别名
select e.ename,e.job from emp e;
2.条件查询:
语法:select 列名
from 表名
where 判断条件;
1. 关系运算符 > = (<> !=) 不等于2中写法
sal > 1500 员工
select * from emp where sal > 1500;
名字叫soctt 的员工信息
select * from emp where ename = 'SCOTT';
职位不是 ANALYST 的员工
select * from emp where job <>'ANALYST';
select * from emp where job !='ANALYST';
2. 逻辑运算符 and or not
sal 在 1500 -2000 之间的
select * from emp where sal > 1500 and sal < 2500;
查询sal > 3000 或者 名字叫 SCOTT
select * from emp where sal > 3000 or ename = 'SCOTT';
SAL 不在这个 1500 -2000
select * from emp where not (sal > 1500 and sal < 2500);
3.区间运算
between ... and ... 可以用在 数值 和 时间上
1. 查找在 1200 到 2500 之间
select * from emp where sal between 1200 and 2500;
2. 在 1981 年入职的员工
select * from emp where hiredate between '01-1月-81' and '30-12月-81';
4. 判断 null 操作
is null is not null
-- comm 是奖金
-- 奖金为null 的员工
select * from emp where comm is null;
-- 不为空
select * from emp where comm is not null;
5. in 和 not in
在in 什么中
判断员工标号 :是否在 (7499,7521,8888)
select * from emp where empno = 7499 or empno = 7521 or empno =8888;
select * from emp where empno in (7499,7521,8888);
三:like (模糊查询)
通配符的解释:
1._ 代表匹配一位
2. % 匹配 0 -多位
1.查以 姓名以 A开头的员工
select * from emp where ename like 'A%';
2.以s结尾
select * from emp where ename like '%S';
3. 查询 第二字符为A的官员
select * from emp where ename like '_A%';
4. 是否包含s的员工
select * from emp where ename like '%S%';
4.排序查询
排序查询的语法:
select * from t
where 条件
order by 字段;
-- sql的执行顺序
1.查表名
2. 查询 where判断条件
3. select * 字段
4. 排序
1.查询所有的员工
select * from emp; -- 通过插入顺序进行 展示的
-- ASC 升序 (默认是升序)
-- desc 降序
-- 通过薪资进行排序 (底到高) 升序
select * from emp
order by sal ASC;
-- 默认是升序 asc 可以省略
select * from emp
order by sal desc;
-- 按照入职时间进行排序(升序) 如果入职时间相同 薪资排序 (降序)
select * from emp
order by hiredate asc,sal desc;
— 常用函数
字符函数
常用函数
Oracle SQL提供了用于执行特定操作的专用函数。这些函数大大增强了SQL语言的功能。
函数可以接受零个或者多个输入参数，并返回一个输出结果。Oracle数据库中主要使用两种类型的函数：
1.单行函数：对每一个函数应用在表的记录中时，只能输入一行结果，返回一个结果，
比如：MOD(x,y)返回x除以y的余数（x和y可以是两个整数，也可以是表中的整数列）。
常用的单行函数有：
字符函数：对字符串操作。
数字函数：对数字进行计算，返回一个数字。
转换函数：可以将一种数据类型转换为另外一种数据类型。
日期函数：对日期和时间进行处理。
2.聚合函数：聚合函数同时可以对多行数据进行操作，并返回一个结果。比如SUM(x)返回结果集中x列的总合。
字符函数
函数 说明
ASCII(x) 返回字符x的ASCII码。
CONCAT(x,y) 连接字符串x和y。
INSTR(x, str [,start] [,n) 在x中查找str，可以指定从start开始，也可以指定从第n次开始。
LENGTH(x) 返回x的长度。
LOWER(x) x转换为小写。
UPPER(x) x转换为大写。
LTRIM(x[,trim_str]) 把x的左边截去trim_str字符串，缺省截去空格。
RTRIM(x[,trim_str]) 把x的右边截去trim_str字符串，缺省截去空格。
TRIM([trim_str FROM] x) 把x的两边截去trim_str字符串，缺省截去空格。
REPLACE(x,old,new) 在x中查找old，并替换为new。
SUBSTR(x,start[,length]) 返回x的字串，从staart处开始，截取length个字符，缺省length，默认到结尾。
案例:
select ascii(‘0’) from dual;
select concat(‘hello’,‘world’) from dual;
select instr(‘HellorWorld’, ‘or’, 7) from dual;
select length(‘HelloWorld’) from dual;
– 查询 名字及长度为 4
select * from emp where length(ename) = 4;
select lower(‘HelloWorld’) from dual;
select upper(‘HelloWorld’) from dual;
select ltrim(’=HelloWorld’,’=’) from dual;
select rtrim('HelloWorld=’,’=’) from dual;
select trim(’=’ from ‘=HelloWorld’) from dual;
select replace(‘ABCDE’,‘AB’, ‘HEHE’) from dual;
select substr(‘ABCDE’, 3, 2) from dual;
数字函数
数字函数
函数 说明 示例
ABS(x) x绝对值 ABS(-3)=3
ACOS(x) x的反余弦 ACOS(1)=0
COS(x) 余弦 COS(1)=1.57079633
CEIL(x) 大于或等于x的最小值 CEIL(5.4)=6
FLOOR(x) 小于或等于x的最大值 FLOOR(5.8)=5
LOG(x,y) x为底y的对数 LOG(2,4)=2
MOD(x,y) x除以y的余数 MOD(8,3)=2
POWER(x,y) x的y次幂 POWER(2,3)=8
ROUND(x[,y]) x在第y位四舍五入 ROUND(3.456,2)=3.46
SQRT(x) x的平方根 SQRT(4)=2
TRUNC(x[,y]) x在第y位截断 TRUNC(3.456,2)=3.45
1.ROUND(X[,Y])，四舍五入。
在缺省y时，默认y=0；比如：ROUND(3.56)=4。
y是正整数，就是四舍五入到小数点后y位。ROUND(5.654,2)=5.65。
y是负整数，四舍五入到小数点左边|y|位。ROUND(351.654,-2)=400。
select round(3.26) from dual; – 保留整数
select ROUND(5.655,2) from dual; – 保留2位小数
– 日薪
select ename,sal,round(sal / 30,1) 日薪 from emp;
select ROUND(341.654,-2) from dual;
- TRUNC(x[,y])，直接截取，不四舍五入。
在缺省y时，默认y=0；比如：TRUNC (3.56)=3。
y是正整数，就是四舍五入到小数点后y位。TRUNC (5.654,2)=5.65。
y是负整数，四舍五入到小数点左边|y|位。TRUNC (351.654,-2)=300。
select TRUNC(5.654) from dual; – 截取去除小数后面的
select TRUNC(5.658,2) from dual;
select TRUNC(351.654,-2) from dual;
日期函数
日期函数
1.ADD_MONTHS(d,n)，在某一个日期d上，加上指定的月数n，返回计算后的新日期。d表示日期，n表示要加的月数。
select add_months(sysdate, 12) from dual;
2.LAST_DAY(d)，返回指定日期当月的最后一天。
select LAST_DAY(sysdate) from dual;
3.ROUND(d[,fmt])，返回一个以fmt为格式的四舍五入日期值，d是日期，fmt是格式模型。默认fmt为DDD，即月中的某一天。
如果fmt为“YEAR”则舍入到某年的1月1日，即前半年舍去，后半年作为下一年。
如果fmt为“MONTH”则舍入到某月的1日，即前月舍去，后半月作为下一月。
默认为“DDD”，即月中的某一天，最靠近的天，前半天舍去，后半天作为第二天。
如果fmt为“DAY”则舍入到最近的周的周日，即上半周舍去，下半周作为下一周周日。
select sysdate, round(sysdate),
round(sysdate, ‘YEAR’),
round(sysdate, ‘MONTH’),
round(sysdate, ‘DDD’),
round(sysdate, ‘DAY’) FROM DUAL;
4.EXTRACT(fmt FROM d)，提取日期中的特定部分。
fmt为：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。
其中YEAR、MONTH、DAY可以为DATE类型匹配，也可以与TIMESTAMP类型匹配；
但是HOUR、MINUTE、SECOND必须与TIMESTAMP类型匹配。
SELECT EXTRACT(YEAR FROM SYSDATE),
EXTRACT(MONTH FROM SYSDATE) ,
EXTRACT(DAY FROM SYSDATE) ,
EXTRACT(HOUR FROM SYSTIMESTAMP) ,
EXTRACT(MINUTE FROM SYSTIMESTAMP) ,
EXTRACT(SECOND FROM SYSTIMESTAMP) FROM DUAL;
– 查看 我们的emp的员工的工龄 (当前的事件 - 入职时间)
select ename,job,hiredate from emp;
– 哪一年入职
select ename,job,hiredate,EXTRACT(YEAR from hiredate) from emp;
– 查看工龄
select ename,job,hiredate,EXTRACT(YEAR from Sysdate) - EXTRACT(YEAR from hiredate) from emp;
-- 查看二月份入职的员工 获取月份
select ename,job,hiredate from emp
where extract(MONTH FROM hiredate) = 2;
5.日期转换函数
to_date : 将字符串转换成日期
to_char : 将日期转换成字符串
注意:
yyyy表示四位年份
mm表示两位月份
dd表示两位天数
hh24表示24小时，hh12表示12小时
mi表示分钟
ss表示秒钟
select sysdate,
to_char(sysdate,‘yyyy/mm/dd hh:mi:ss’),
to_char(sysdate, ‘yyyy-mm-dd’),
to_char(sysdate, ‘yyyy-mm’),
to_char(sysdate, ‘yyyy’)
from dual;
注意 sql 中字符串是数字 可以进行运算
-- 工龄的计算
select ename,job,hiredate from emp;
select ename,job,hiredate,to_char(hiredate,'yyyy') 入职年份 from emp;
-- 工龄
select ename,job,hiredate,(to_char(sysdate,'yyyy') - to_char(hiredate,'yyyy')) 工龄 from emp;
其他函数
6.nvl(column, value) 如果column为空，返回value，否则返回x。
– 查看 奖金
select ename,job,sal,comm from emp;
– 查看总工资
select ename,job,sal + comm 月收入 from emp;
– 有的人没有工资 (sal + comm ) 如果comm为null 结果为null 不合理
使用nvl 函数 nvl(x,y) x如果为null 可以设置值 y
select ename,job,sal,nvl(comm,0) from emp;
– 查看收入
select ename,job,sal + nvl(comm,0) 月收入 from emp;
7.decode: 类似于Java当中 if 、 if else、if else if else
create table stu(
sid number(2)，
sname varchar2(20)，
ssex number(1)
);
select sid,sname,ssex
, decode(ssex, 1, '女')
, decode(ssex, 1, '女', 2, '男')
, decode(ssex, 1, '女', 2, '男','未知')
from stu;
– 多表查询:
当我们使用更多表查询 需要建立表单的模型
当我我们关联对象与对象的关系是 我们需要java模型
java模型: 对象模型可以双向关联 依赖是对象的引用
数据库中: 表与表之间的管理依赖的是外键
java 模型的依赖的关系
1 对 1 关系
一个丈夫对应一个妻子 (引用)
class Wife{
private int wid;
private husband hus;
}
class Husband{
private int hid;
private Wife w;
}
1 对的多 关系
部门和员工(一个部门有多个员工)
class Emp{
private int empno;
private Dept dept;
}
class Dept{
private int deptno;
private List emps;
}
java中多对多的实现:
class Student{
private int sid;
private String sname;
private List teas;
}
class Teacher{
private int tid;
private String tname;
private List stus;
}
数据库中的 表与表之间的关系:
1 对 1
关联关系(让其中的任意一张表添加外键)
create table wife(
wid number(2) primary key,
wname varchar2(20),
constraints fk_wife_hsu_id foreign key (wid) references husband(hid)
);
create table husband(
hid number(2) primary key,
hname varchar2(20)+
);
1 对多
多方(从表) 学生 添加外键
少方(主表) 班级
关联关系 (从表中设置 外键)
create table sxtstu(
sid number(3) primary key ,
sname varchar2(20)，
classid number(2),
constraints fk_stu_cls_id foreign key (classid) references sxtclass(cid)
);
create table sxtclass(
cid number(3) primary key,
cname varchar2(20)
);
多对多的关系
学 和 老师
关联关系(需要媒人介绍(中间表 设置外键 同时关联2张表 ))
create table stu(
sid number(2) primary key,
sname varchar2(20)
);
create table teacher(
tid number(2) primary key,
tname varchar2(20)
);
create table stu_tea_temp(
sid number(2),
tid number(2)，
– constraints fk_stu_id foreign key (sid) references stu(sid)
– constraints fk_tea_id foreign key (tid) references teacher(tid)
);
一般不设置外键 使用逻辑外键 (外键通过 逻辑 管理)
– 多表查询
1.合并结果集 (了解)
2.连接查询
3. 子查询
合并结果集
union 去除重复并且合并
union all 不去重复的记录
技巧: 要求合并的多个结果集中 列的类型和 列的数量必须相同
create table test1(
tid number(2)，
tname varchar2(20)
);
insert into test1 values(1,‘a’);
insert into test1 values(2,‘b’);
insert into test1 values(3,‘c’);
commit;
select * from test1;
create table test2(
did number(2)，
dname varchar2(20)
);
insert into test2 values(3,‘c’);
insert into test2 values(4,‘d’);
insert into test2 values(5,‘f’);
commit;
把查询 结果 合并成一个集
union – 结果集去除重复的
select * from test1
union
select * from test2;
union all-- 结果集不去除重复
select * from test1
union all
select * from test2;
– 连接查询
分类:
1. 交叉连接
交叉连接
查出的员工的 部门信息
查表:
emp 字段 所有
dept 字段 所有
select * from emp; – 15 条数据
select * from dept;-- 4 条数据
select * from emp,dept；
交叉查询的问题：
1.会产生很大的结果集(15 * 4 ) 60 性能低
2. 结果集 有一些数据对我们没意义
2. 等值连接
在交叉查询的基础上 加上条件 (表与表之间的 关联字段 deptno )
添加 条件 加关联字段 deptno
select * from emp,dept where emp.deptno = dept.deptno;
(查询的时候 还是产生了60条数据 通过过滤显示解决没有意义的数据)
3. 内连接 (a inner join b on (关联字段))
和等值连接一样的
select * from emp ,dept where emp.deptno = dept.deptno; (等值连接写法)
内连接
select * from emp inner join dept on (emp.deptno = dept.deptno);
表使用别名:
select * from emp e inner join dept d on (e.deptno = d.deptno);
解决了查询 结果集 数据太多的问题
select * from dept;
问题: 1. 数据补全 (老王，40部门不显示)
4. 外连接
左外连接: 把左表所有数据查询出来
select * from emp e left join dept d
on e.deptno = d.deptno;
解决把 老王查出来
右外连接"把右表所有的数据查出来
select * from emp e right join dept d
on e.deptno = d.deptno;
全外连接 : 把2表不满足的记录 保留a full join b on 关联字段
select * from emp e full join dept d on e.deptno = d.deptno;
oracle 方言查询:
于外连接，也可以使用“(+)”来表示。但须注意：
1. (+)操作符只能出现在where子句中，并且不能与outer join语法同时使用
2. 当使用(+)操作符执行外连接时，如果在where子句中包含有多个条件，则必须所有条件中都包含(+)操作符
3. (+)操作符只适用于列，而不能用在表达式上
4. (+)操作符不能与or和in操作符一起使用
5. (+)操作符只能用于实现左外连接和右外连接，而不能用于实现完全外连接
用(+)来实现，这个+号可以这样来理解：+ 表示补充，即哪个有+，这个表就是匹配表，另外的则是基础表。
例子
select t1.c1,t2.c2 from t1,t2 where t1.c=t2.c(+) --左连接
select t1.c1,t2.c2 from t1,t2 where t1.c(+)=t2.c --右连接
左外连接:
select * from emp e,dept d where e.deptno = d.deptno(+);
右外连接:
select * from emp e,dept d where e.deptno(+) = d.deptno;
99sql写法: [JOIN 表名称 ON（条件）|USING（字段）]
select * from emp e left join dept d using(deptno);
5. 自然连接: 会多表联查 自己给我们关联数据(了解)
select e.ename,d.dname from emp e natural join dept d;
统计函数:(聚合函数)
常用的聚合函数"
sum 求和
count 统计 (非空不会添加)
max 最大值
min 最小值
avg 平均值
– 查所有的薪资和
select sum(sal) 薪资之和 from emp;
– 统计 job的职位 有几个
select * from emp;
select count(distinct job) from emp;
select distinct job from emp;
– 统计emp中有多少员工数
select count(*) from emp; – 统计 每个字段的数量 计算每一列的数组 取最大的
select count(empno) from emp; --统计一个字段的记录条数 select count(comm) from emp;
select count(1) from emp; – 给出一个好常量值 然后在统计 常量列的记录条数 (常量列不可能为null) 、
– 找出薪资最高的
select max(sal) from emp ;
–薪资最低
select min(sal) from emp;
– 找出薪资的平均值
select avg(sal) from emp;
– 分组查询:
– 数据有重复才可以分组
分组语法:
select * from t
where 条件
group by 字段1…
order by 排序;
– 按照部门编号分组 求出每个部门的人数和平均工资
– 查表 emp
– 字段 deptno empno sal
– 查出字段
select deptno,empno,sal from emp;
--分组操作 (分组后有新的语法要求)
1. 分组 字段 必须是有有重复的
2. 分组后,查询字段必须是 分组或者聚合函数 (mysql没有这个要求)
select deptno,count(empno),avg(sal) from emp
group by deptno;
-- 查询部门名称 部门人数 平均薪资
-- 查表 dept emp
-- 字段 dname empno sal
-- 关联字段 detpno
-- 查出来
select d.dname,e.empno,e.sal from emp
e ,dept d where e.deptno = d.deptno;
-- 分组 dname
select d.dname,count(e.empno),round(avg(e.sal),2) from emp
e ,dept d where e.deptno = d.deptno
group by d.dname;
-- 右连接
-- 保留 2位小数 round(avg(e.sal),2)
-- nvl(sal，0) 为空给 0
select d.dname,count(e.empno),nvl(round(avg(e.sal),2),0) from emp
e right join dept d on e.deptno = d.deptno
group by d.dname;
-- 多字段分组查询
-- 显示每个部门编号，名称位置 位置 部门人数 平均薪资
-- 查表 dept emp
-- 字段 dname loc,deptno empno sal
-- 关联字段 detpno
-- 查出来
select d.deptno,d.dname,d.loc,e.empno,e.sal from emp
e right join dept d
on e.deptno = d.deptno;
-- 多字段分组
select d.deptno,d.dname,d.loc,count(e.empno),nvl(avg(e.sal),0) from emp
e right join dept d
on e.deptno = d.deptno
group by d.deptno,d.dname,d.loc;
分组查询 HAVING 的使用
统计出部门的平均工资高于 2000的 部门的详细信息（部门编号、部门名称、部门位置、部门人数、部门平均薪资）。
seledct * from t
where 条件
group by 分组字段
order by 字段
having 条件;
having 的使用 :分组后再次过滤 使用having
where的使用: 在执行group by之前的过滤(总过滤) (不能使用聚合函数)
having 是 在 group by 分组之后进行过滤 (可以使用)
select d.deptno,d.dname,d.loc,e.empno,e.sal
from emp e right join dept d
on d.deptno = e.deptno;
分组:
select d.deptno,d.dname,d.loc,count(e.empno),avg(e.sal)
from emp e right join dept d
on d.deptno = e.deptno
group by d.deptno,d.dname,d.loc;
-- 过滤
select d.deptno,d.dname,d.loc,count(e.empno),avg(e.sal)
from emp e right join dept d
on d.deptno = e.deptno
group by d.deptno,d.dname,d.loc
having avg(e.sal) > 2000;
分组查询总和运用
思考一：显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，
并且要满足从事同一工作的雇员的月工资合计大于$5000，
输出结果按月工资的合计升序排列：
– 1. 显示非销售人员工作名称
select * from emp where job = ‘SALESMAN’;
– 把非销售 查出来
select * from emp where job != ‘SALESMAN’;
– 2 从事同一工作雇员的月工资的总和 (分组 + 聚合)
select job,sum(sal) from emp
where job != ‘SALESMAN’
group by job;
– 大于 5000
select job,sum(sal) from emp
where job != ‘SALESMAN’
group by job
having sum(sal) > 5000;
– 排序 升序
select job,sum(sal) from emp
where job != ‘SALESMAN’
group by job
having sum(sal) > 5000
order by sum(sal) asc;
思考二：显示部门编号不是30的，的部门详细信息（部门编号、部门名称、部门人数、部门月薪资总和），
并要求部门月工资总和大于$8000，输出结果按部门月薪资的总和降序排列。
– 显示部门编号不是30的，的部门详细信息
– 查表 emp , dept
– 字段 deptno dname empno sal
– 查出信息
select * from emp e right join dept d on e.deptno = d.deptno;
– 部门不是 30
select d.deptno,d.dname,e.empno,e.sal from emp e right join dept d
on e.deptno = d.deptno
where e.deptno != 30;
–分组
select d.deptno,d.dname,count(e.empno),sum(e.sal) from emp e right join dept d
on e.deptno = d.deptno
where e.deptno != 30
group by d.deptno,d.dname;
-- 综合大于 80000
select d.deptno,d.dname,count(e.empno),sum(e.sal) from emp e right join dept d
on e.deptno = d.deptno
where e.deptno != 30
group by d.deptno,d.dname
having sum(e.sal) > 8000;
-- 降序
select d.deptno,d.dname,count(e.empno),sum(e.sal) from emp e right join dept d
on e.deptno = d.deptno
where e.deptno != 30
group by d.deptno,d.dname
having sum(e.sal) > 8000
order by sum(e.sal) desc;
子查询:
查询语句中嵌套查询:
嵌套查询 中的结果集:
单行单列 where/having 中
多列单行 少见 了解
单列多行: where中
多行多列 用在from后面
1. 查询薪资比 SCOTT 高的员工
-- 查询 scott 的薪资
select sal from emp where ename = 'SCOTT';
2. SAL > SCOTT
select * from emp where sal > (
select sal from emp where ename = 'SCOTT'
);
– 查询 高于公司 平均薪资的雇员的信息
– 查出平均薪资
select avg(sal) from emp;
select * from emp where sal > (
select avg(sal) from emp
);
– 单行多列
查询 和soctt 薪资一样的 job一样的雇员信息
– scott 薪资 和job 查出来
select SAL,JOB from emp where ename = ‘SCOTT’;
– 判断 使用in 或 = 操作
select * from emp
where (sal,job) = (select SAL,JOB from emp where ename = ‘SCOTT’);
select * from emp
where (sal,job) in (select SAL,JOB from emp where ename = ‘SCOTT’);
– 单列多行
判断使用 in ,any ,all
-- 查询和 进来薪资一样的员工信息
-- 经理薪资
select sal from emp where job = 'MANAGER';
-- 薪资和 manager一样的
select * from emp where sal in (select sal from emp where job = 'MANAGER');
= any 和in 是一样的
-- 2975 ， 2850 ,2450
> any 比子查询返回最小记录还要大的数据 > 2450
select * from emp where sal >any (select sal from emp where job = 'MANAGER')
< any 比子查询 返回最大记录还要小 < 2975
select * from emp where sal < any (select sal from emp where job = 'MANAGER')
> all 大于子查询最大的
select * from emp where sal > all (select sal from emp where job = 'MANAGER')；
或者
select * from emp where sal > (select max(sal) from emp where job = 'MANAGER') -- 多行单列转成 当行单列
< all 小于最小的
select * from emp where sal < all (select sal from emp where job = 'MANAGER')
-- 多行多列
查询每个部门编号 名称 位置 部门人数 平均薪资
select d.deptno,d.dname,d.loc,count(e.empno),avg(e.sal)
from emp e，dept d
where e.deptno = d.deptno
group by d.deptno,d.dname,d.loc;
-- 等值查询 emp 15 和 dept 4 表 === > 15 * 4 = 60 次计算
-- 使用子查询 优化sql
先子查询 emp 分组 统计 再 和 dept 管理
select e.deptno,e.empno,e.sal from emp e;
-- 分组 + 统计 (结果为 多行多列 当做数据源使用 在from 后面可以使用)
select e.deptno,count(e.empno),avg(e.sal) from emp e
group by e.deptno;
select d.dname,d.deptno,d.loc,temp.nums,temp.avgsal from dept d,(
select e.deptno dno,count(e.empno) nums,avg(e.sal) avgsal
from emp e
group by e.deptno -- 12条数据 分组 4条数
) temp
where d.deptno = temp.dno;
查询 表dept 4条数 temp 表 4条数据 == > 16 次计算 28次计算
伪列:
rowid
rownum
伪列:是一张表不存在的列 附加的列
不会存储表中的真实数据 只能够用于查询
rowid 用于表示我们的一行记录存储的物理地址 唯一
select * from emp;
select ename,rowid from emp;
rownum: 在查询结果集 会为每一行结果生成一个行号 第一行为 1 一次递增 有1 会有2
select * from emp;
select ename,rownum from emp;
-- 查询emp表中前 1-4 条记录 pageSize = 4 currentPage = 1
select ename，empno,rownum from emp
where rownum <= 4;
-- 查询 5- 8 条数据 pageSize = 4 currentPage = 2
select ename，empno,rownum from emp
where rownum <= 8 and rownum >=5 ;
-- oracle 的分页需要用到子查询
你先要查询 小于的 8 的结果集 有伪列 通过伪列进行判断
-- mysql 的分页 limit
-- 小于 8 的伪列结果集
select ename,empno,rownum from emp where rownum <=8;
-- 通过子查询 过滤 rownum >= 5
写法1: 查询的范围小 效率高一些
select t.ename,t.empno,t.num from (
select ename,empno,rownum num from emp where rownum <=8 ) t
where t.num >= 5
写法2:
select t.ename,t.empno,t.num from (
select ename,empno,rownum num from emp) t
where t.num >= 5 and t.num <= 8;
-- 查询 9 - 12 的数据 pageSize = 4 currentPage = 3
select ename,rownum from emp where rownum <= 12;
select t.ename,t.empno,t.num from (
select ename,empno,rownum num from emp where rownum <= 12 ) t
where t.num >=9 ;
-- pageSize = 4 currentPage = 1 1- 4记录
-- pageSize = 4 currentPage = 2 5- 8记录
-- pageSize = 4 currentPage = 3 9- 12记录
-- oracle 分页的 sql公式
select t.ename,t.empno,t.num from (
select ename,empno,rownum num from emp where rownum <= pageSize * currentPage ) t
where t.num >= (currentPage - 1) * pageSize +1 ;
-- 查出薪资最高的员工信息
-- 查询薪资最高的
select max(sal) from emp;
select * from emp where sal = ( select max(sal) from emp);
-- 分页实现 最高的?
-- 排序后
select * from emp where order by sal desc;
-- 分页
select t.empno,t.ename,t.sal,rownum from (select empno,ename,sal from emp order by sal desc ) t;
select t.empno,t.ename,t.sal,rownum from (
select empno,ename,sal from emp order by sal desc ) t
where rownum = 1;
-- 查询 薪资 第三高的
-- 排序后 查询结果 有 rownum
select t.empno,t.ename,t.sal,rownum from (
select empno,ename,sal from emp order by sal desc ) t;
-- 判断
select t.empno,t.ename,t.sal,rownum from (
select empno,ename,sal from emp order by sal desc ) t
where rownum <=3;
select * from (
select t.empno,t.ename,t.sal,rownum rn from (
select empno,ename,sal from emp order by sal desc ) t
where rownum <=3
) temp
where temp.rn = 3;
--- 视图
view 视图是一张虚表 物理不存在的表 主要目的用于查询 可以操作 MDL(不建议使用)
作用: 1. 开放部分字段给用户使用 隐藏 信息
select * from emp;
创建一个视图
要授权: 创建视图
切换 conn system/manager as sysdba;
授权: grant create view to 用户;
create or replace view 视图名字 as [新的数据] [with read only]
-- 创建一个视图 给你三个 字段
create or replace view v_emp as select ename,empno,deptno from emp with read only;
select * from v_emp;
– 索引 一般对sql语句优化有很大的作用
主键会自动创建索引
创建索引语句:
为员工表中姓名 这一列创建索引
create index iindex_emp_name on emp (ename);
索引好处:
创建索引能够提高数据库的查询效率 但是会同时影响到 MDL操作的性能
系统介绍索引 大家可以看下 扫盲下 (一共7 篇文章)
https://blog.csdn.net/why15732625998/article/details/80388236
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
