Orace基础学习基础
认识数据库
-
认识数据库
数据库：数据的仓库，保存大量数据的地方，有利于对数据的维护。增删改查很方便。数据库分类：层次型数据库：现实世界中很多事物是按层次组织起来的。层次数据模型的提出，首先是为了模拟这种按层次组织起来的事物。
网状型数据库：处理以记录类型为结点的网状数据模型的数据库。处理方法是将网状结构分解成若干棵二级树结构，称为系。 关系型数据库：mysql SQLserver oracle 分布式数据库:是指数据分存在计算机网络中的各台计算机上的数据库oracle 数据库
oracle：甲骨文公司 全球第一大数据库厂商 -
关系型数据库概念
关系型数据库概念
a,由 E.F.Codd博士在1970年提出关系型数据库系统模型
b,它是关系型数据库管理系统的基础
c,关系型数据库模型包含以下3部分内容
i 包含一系列关系和对象
ii 包含一系列对于关系的操作
iii 包含数据完整性约束关系型数据库=关系+对象+操作+约束
实体完整性:主键约束:非空且唯一
参照完整性:外键约束:可以为空,取值一定要是关联表的主键()
A表 通过B_id列 和 B表 进行关联
s_emp 通过 dept_id列 和 s_dept(id) 进行关联
s_dept region_id s_region(id)
自定义完整性：约束插入的数据
二维表
关系型数据库中的关系是指:把实际问题的数据分别归成若干个简单的二元关系，一个二元关系都可以建一个满足一定条件的二维表格
oracle数据库
-
orace主要操作
1.select语句—>查询
2.数据操纵语言（DML语句）data manipulation language
insert, update, delete
有事务概念
3.数据定义语言（DDL语句）data definition language
create(创建), alter(修改), drop(删除), rename(重命名), truncate(删除表中的数据)
4.事务控制语句（TCL语句）transction control
comit(提交), rollback(回滚), savepoint(记录点,用来回滚)
5.数据控制语言（DCL语句）data control language
grant【授权】, revoke【回收】 -
oracle基础操作
1.以管理员方式登录oracle
sqlplus system/密码
show user:显示当前登录的用户名字2.创建新用户briup,密码briup
create user briup identified by briup;
3.给新用户briup授权
grant connect,resource to briup;3.激活用户:
alter user briup account unlock;
4.切换至briup用户
conn briup/briup5.设置当前会话(session)的日期语言格式
alter session set nls_date_language=english;6.执行sql文件
start e:/table.txt
@路径
7.查询当前用户下有哪些表？
select table_name from user_tables;查看表有哪些列？
desc 表名 -
数据类型
1,NUMBER[(p,s)] 数值类型 1,NUMBER[(p,s)] 数值类型
p代表有效位数 精度(总长度)，
s代表小数点后位数 刻度
eg:
NUMBER(6,2):
有效位 6为
精确到小数点后两位
:小数点前只能有四位
小数点后有两个
NUMBER(3):
有效位 3为
123
12.3
0.123
2,字符类型:
2.1:CHAR(8) 定长字符类型:占用空间固定
name: char(8) :name列占8个字符位
tom : 强制占8个字符位
helloworld: 不符合要求
2.2:VARCHAR(8) mysql 变长字符类型:占用空间 跟着输入有关
name: VARCHAR(8) :name列最大占8个字符位
tom : 占用3个字符位
helloworld:不符合要求
2.3:VARCHAR2(8) oracle 变长字符类型:占用空间 跟着输入有关
abc 3个字符长度，存储到计算机底层就是存储了3个字符串长度
3,日期类型:DATE
默认:04-9月-17
3.1:日期类型，世纪年月日时分秒都会保存
3.2:中文语言环境:
系统默认:'DD-MM-YY'
eg:'16-7月-17' ---> 2017/7/16
3.3:修改当前语言环境:
alter session set nls_date_language=english;
3.4:英文语言环境
系统默认:'DD-MM-YY'
eg:'16-JULY-17' ---> 2017/7/16
3.5:修改日期显示格式
alter session set nls_date_format='yyyy-mm-dd';
eg:select sysdate from dual;
4,CLOB:超级长字符,正本小说的所有字符串 可以保存
5,BLOB:字节数组:可以用于保存文件的二进制字节数组.
字节数组: byte[]
oracle练习
以一下三表为例
S_DEPT：部门表
id id
name 部门名称
region_id 所在地区id的外键
S_EMP ：员工表
id id
last_name 后名字 姓
first_name 前名字 名
userid 员工内部编号，没用
start_date 入职日期
comments 注释：记录员工的特点
manager_id 经理的id
title 职称
dept_id 所在部门id外键
salary 工资
commission_pct 提成`
S_REGION：区域表
id id
name 区域名
select 语句
1:distinct：消除重复行,有多条重复数据就只会显示一条
语法：
select [distinct] {*|(列名1,列名2...)}
from table;
* : 所有的列
样例：
(1)查询s_dept表中的所有信息
eg:
select * from s_dept;
(2)查询s_emp表中的last_name,salary
eg:
select last_name,salary
from s_emp;
(3)查看s_emp表id,last_name,salary字段信息(列)
eg:
select id,last_name,salary
from s_emp;
(4)查看s_emp表last_name,salary字段信息,
eg:
select last_name,salary
from s_emp;
eg2:last_name取别名 名字
salary 取别名 工资
select last_name as 名字,salary as 工资
from s_emp;
eg3:省略as关键字 取别名
select last_name 名字,salary 工资
from s_emp;
(5) 查询所有部门名
eg:
select name
from s_dept;
去除重复的行
select distinct name
from s_dept;
select distinct 列,列
from 表
2,起别名：给某一个列 起别名
语法:
select 列1 as 新列名字,列2 as 新列名字...
from 表
a.使用as
b.空格后直接跟别名
select 列1 别名1,列2 别名2...
from 表
c.使用双引号区分大小写
select name as 别名
from 表
select name 别名
from 表
3,nvl(参数一，参数二):空值函数
参数一 ： 可能为空的列，
参数二 ： 如果参数一的列为空 就使用参数二的值代替
单纯查工资和提成：
egs:
select salary,commission_pct
from s_emp;
查询当月工资:
select salary*commission_pct+salary
from s_emp;
使用nvl空值函数 进行操作
select salary*nvl(commission_pct,0)+salary
from s_emp;
select nvl(commission_pct,0)
from s_emp;
4, 算术运算符 + - * /
查询出来的列是可以运算的
select 列+列 from 表;
select 10+列 from 表;
当月工资 加 100;
select salary,salary+100
from s_emp;
eg：查询 s_emp 中last_name 列和年薪
select
last_name,
salary*(1+nvl(commission_pct,0)/100)*12 年薪
from s_emp;
5, 连接符||：可以将多个字符串或者多个字符串列的值进行拼接
作用：可以将多个列的值或者字符串进行拼接
注意：oracle中字符串使用的是单引号
oracle中单引号表示字符串.
语法:
select 列||'你好' from s_emp;
eg:1字符串拼接:
hello
world
select 'heLLo'||'world'
from dual;
dual表:哑表-->同义词
任何用户都可以使用,
一般使用做测试.
eg:2字符串拼接列:
名:last_name
select '名:'||last_name
from s_emp;
eg:3列拼接列:
select '名:'||last_name||'姓'||first_name
from s_emp;
排序 Order by
1,语法:order by 列名 排序,列名 排序;
写在sql语句的最后一行,(最后,最后,最后)
升序：asc 如果不写 默认为asc
降序：desc
语法:
select last_name
from s_emp
where id = 10
order by 列1 asc,列2 desc;
eg:查询员工的姓名,部门id,工资,通过工资升序
1,哪张表中有这些数据
2,查询需要的列
3,排序
select last_name,dept_id,salary
from s_emp
order by salary,dept_id desc;
2,特点：
2.1 : order by放在select语句最后一行，最后，最后，最后
2.2 : 默认升序（asc）,降序（desc）
2.3 : order by后面可以跟列名、别名、select后面字段名的序号
不写排序规则就是默认升序排序
eg:查询姓名,salary,部门id,给salary 取别名为工资 按照工资排序
select last_name,salary 工资,dept_id
from s_emp
order by 工资;
显示升序排序
eg:
显示降序排序
eg:
select id,salary,commission_pct
from s_emp
order by commission_pct desc;
字符串排序是按照ASCII值排序
eg:
按照多个列排序
eg:
注意：如果按照多个列进行排序，多个列之间逗号隔开，先按照第一个列的值进行排序，如果第一个列的值相同，那么会按照第二个列的值进行排序。.......以此类推
按照commission_pct进行排序
select last_name,commission_pct
from s_emp
order by commission_pct asc;
2.4 : null 表示无限大
where子句
作用:过滤（筛选）要查询的数据
eg:只要查询工资大于1000的id,last_name
书写位置：放置在from子句的后面 紧跟着from
select id,last_name
from s_emp
where salary > 1000
书写组成：可以由字段名(列名)、常量、比较符、表达式等组成。
1, 基本操作符
逻辑比较操作符
= > < >= <=
eg:工资大于等于1500员工信息
select id,last_name,salary
from s_emp
where salary >= 1500;
eg:工资大于等于1500并且id小于10员工信息
select id,last_name,salary
from s_emp
where salary >= 1500 and id < 10;
不等于:三个都表示不等于的意思(经常用的是!=)
!= <> ^=
eg：查询 id last_name salary
from s_emp 需要查询的
条件是 工资大于1000
where salary > 1000;
eg:查询 工资 不等于 1100员工
select *
from s_emp
where salary != 1100;
where salary <> 1100;
where salary ^= 1100;
2,where 列 [not] between 一 and 二 ;
在某一个区间 [一,二]
在一到二之间 ===> [1,2]
where salary between 100 and 1000;
where salary not between 100 and 1000;
eg:查询 工资在1000 到2000 之间的员工信息
select id,last_name,salary
from s_emp
where salary not between 1100 and 1450;
3, where 列 (not) in(41,42,43)某一列的值只能是括号里的数据
eg：where 列名 in(41,42,10)
select id,last_name,salary
from s_emp
where id in (1,2,3,4,5);
eg:查询工资为1000或者1500或者1450的员工信息,按照工资排序
select id,last_name,salary
from s_emp
where salary in(1100,1450,1500)
order by salary;
4, and 且
需要连接多个条件
语法:where 条件一 and 条件二 当条件一和条件二都满足的数据才会被查询出来
条件:
eg1: salary > 1000
eg2: salary between 500 and 1200
eg3: id in (4,6,7)
eg:工资大于1500 并且 id<10
select id,last_name
from s_emp
where salary > 1500 and id < 10
eg: 工资在1000,2000之间 并且只能是41号部门
select id,dept_Id,salary
from s_emp
where salary between 1000 and 2000 and dept_id = 41;
where salary between 1000 and 2000 and dept_id in (41,42,44);
5, or 或
需要连接多个条件
eg: where 条件一 or 条件二 or 条件三 当条件一或者条件二满足一个就可以出来
eg:查询id小于20 或者 工资大于2000的员工
select *
from s_emp
where id < 20 or salary > 2000;
6, (not) like：模糊匹配
语法:where 列 like '字符串';
_ ：占位符 占一个符号位
% : 占位符 占0-多个位子
escape : 定义转义符号。将特殊含义的字符串 转义为普通字符串
语法: where last_name like '/_briup' escape '/';
eg:查询last_name中包含g的员工
select id,last_name
from s_emp
where last_name like '%g%';
eg: 查询last_name第二个字母为g的员工
select id,last_name
from s_emp
where last_name like '_g%';
添加数据 _briup
insert into s_emp(id,last_name) values(666,'_briup');
commit;
eg:查询_开头的员工
select id,last_name
from s_emp
where last_name like '_%';
转义:需要自定义转义字符
escape
语法: where 列 like '' escape '#'
解释: 自定义了一个转义字符 字符位#
select id,last_name
from s_emp
where last_name like '#_%' escape '#'
7, is (not) null : 判断那一列是null；
语法: where commission_pct is null;
select id,salary * commission_pct
from s_emp
where commission_pct is not null;
注意：oracle中字符串使用单引号，单引号中的字符区分大小写
eg:41号部门，salary 大于1500 salary降序
select *
from s_emp
where dept_id = 41 and salary > 1500
order by salary desc;
插入一条sql语句
insert into s_emp(id,last_name) values(99,'_briup');
commit;
优先级：
（1）算术运算符
（2）连接符 ||
（3）比较符
（4）列名 [not]in 列名 [not]like
（5）列名 [not]between 1 and 2
（6）not
（7）and
（8）or
（函数）...
*使用括号改变优先级
函数 ：单值函数 + 组函数
1，基础
位置：可以出现在select、where、order by字句中
单值函数分类：
字符函数:
lower('');
upper('');
initcap('');
substr('',0,0);
length('')
concat('','');
nvl('','');
数字函数:
round(3.435,2);
trunc(43.256,2);
mod(30,4)
日期函数
months_between('12-3月-17','12-5月-17');
add_months('12-3月-17',4);
next_day('12-3月-17',星期一);
last_day(system);
转换函数
to_char(32,'$99,99');
to_number('123321');
to_date('2017-06-11','yyyy-MM-dd');
dual：哑表:系统表,只是每个新建的用户有权限操作,里面只有一条数据.
2,字符函数:
2.1 : lower('参数') : 大写转小写，字符串全都转出小写
语法: lower('字符串')
lower(列名)
eg:HELLO worLd
select lower('HELLO worLd')
from dual;
eg:查询Chang用户有关信息
select id,last_name,salary
from s_emp
where last_name = 'Chang';
eg:把last_name列传为小写进行操作
select id,last_name,salary
from s_emp
where lower(last_name) = 'chang';
2.2 : upper('')：小写转大写，字符串全都转出大写
select upper('HeLLo WoRLd') from dual;
select id,last_name
from s_emp
where upper(last_name) = 'CHANG';
2.3 : initcap('') 将每一个单词首字母大写，其他小写
空格区分单词
select initcap('HeLLo WoRLd')
from dual;
select initcap('hello/world-hello?oracle!ojoij')
from dual;
2.4 : concat:作用和||一样 ''||''
嵌套:
concat('字符串1','字符串2');
姓·名
·名 : concat('·',last_name)
select concat(upper(first_name),concat('·',last_name))
from s_emp;
* 2.5 : substr(参数1,参数2,参数3)
substr(参数1,参数2,参数3)
参数1：要截取的字符串 必须
参数2：必须是数字 开始截取的位置(从几号位开始截取(会包含当前位置)) 必须
整数：角标是从1开始的没有零，如果你写从0开始，系统会让你从1开始。
负数：从后面开始截取
参数3：必须是数字 截取的长度，如果没有参数3，会截取到最后 可选
负数：没有意义
hello world
select substr('hello world',3,4) from dual;
select substr('hello world',0,4) from dual;
select substr('hello world',-2,4) from dual;
select substr('hello world',-5) from dual;
select substr('hello world',5) from dual;
eg: 取子串：'Hello WOrlD' 从3开始 取5个长度
2.6 : length()：求字符串或者字符串列的长度
select length('Hello world') from dual; 空格也算
select length(last_name) from s_emp;
select length('hello world') from dual;
select id,salary,length(last_name) 长度,last_name
from s_emp;
2.7 : nvl : 空值函数 nvl(object,num)如果Object不为空返回objec如果为空返回num;
nvl(参数一,参数二)
参数一：可能为空的列
参数二：如果那列的值为空 就用参数二来替代
eg:把last_name = _briup 的first_name
如果的null 就替换成Briup#123456
3,数字函数:
3.1 : round(参数一,参数二) ：四舍五入
参数一:数字
参数二:精确到小数点后几位
select round(20.56,2) from dual; 20.56
select round(20.5656,1) from dual; 20.6
select round(20.5656,-1) from dual; 20
select round(20.5656,-2) from dual; 0
3.2 : trunc(参数一,参数二)：只舍不入
参数一:数字
参数二:精确到小数点后几位
select trunc(43.256,2) from dual;--->43.25
select trunc(43.256,0) from dual;--->43
select trunc(43.256,-1) from dual;-->40
3.3 : mod(参数一,参数二):取余数
参数一:数字
参数二:对几取余
eg:工资是600的整数倍
select id,salary
from s_emp
where mod(salary,600)=0;
3.5: 日期中的round和trunc 保留的年or月
月:5舍6入
日:15舍16入
select round(to_date('27-7月-16'),'month') from dual;
select round(to_date('27-7月-16'),'year') from dual;
month:15舍16入
year:6舍7入
select round(sysdate,'day')
from dual;
select round(sysdate,'month') from dual;
select round(sysdate,'year') from dual;
select trunc(sysdate,'month') from dual;
select trunc(sysdate,'year') from dual;
---day2 end---
4,日期函数：
4.1 : sysdate:当前系统时间 Data
select sysdate from dual;
select id,salary,sysdate
from s_emp;
4.2 : current_date:当前系统时间秒数。有区别...有兴趣的同学自己看看：http://www.cnblogs.com/haitao-fan/archive/2013/01/27/2878841.html
select id,salary,current_date
from s_emp
4.3 : months_between(参数一,参数二)：两个日期相差多少个月
参数一：日期或者当前系统格式的日期字符串
参数二：日期或者当前系统格式的日期字符串
得到的是负数：参数一 < 参数二 :
得到的是小数：不满一个月 --> 1/31
select months_between(sysdate,sysdate+1) from dual;--->1 相差一个月 sysdate+31 算术运行符
select add_months(to_date('20-7月-20'),2) from dual;
4.4 : add_months(参数一,参数二)：返回指定日期+n个月之后的那个日期
参数一:日期或者当前系统格式的日期字符串
参数二:整型数字代表n个月以后
select add_months(sysdate,2) from dual;
4.5 : next_day(参数一,参数二)：即将来临的星期几是哪一天
参数一:日期或者当前系统格式的日期字符串
参数二: 即将到来的星期几 '星期一'
select next_day(sysdate,'星期五') from dual;
4.6 : last_day(参数一)：返回给定日期所在月份的最后那天时间
参数一：
select last_day(sysdate) from dual;
求:在当月倒数第5天入职的员工
入职时间:start_date
本月最后时间:last_day(start_date)
select id,start_date
from s_emp
where (last_day(start_date)-start_date) = 5
5,转换函数：
5.1 : to_char(参数一,参数二):将数字或者日期类型的数据转换为字符串类型
返回值为字符串
参数一:数字或日期
数字：
参数二：'数字输出格式 使用上面学到的数字占位符'
select to_char(1234,'$999,999')
from dual;
1400 --> $1,400.00
select to_char(1400,'$9,999.00') from dual;
返回结果
sqlplus:col salary for $9,999.00 结果显示的控制
日期：
参数二：'日期输出的格式 如下介绍'
格式：
把日期转换成字符串，指定为参数2的格式
yyyy 年
rrrr 四位数的年份
yy 年
rr 两位数的年份
mm 月
d 这周的第几天
dd 这个月的第几天
ddd 这年的第几天
year 英文下的年
month 月
ddsp 当前日期
ddspth 同上
day 星期几
dy 星期几
hh24 小时
mi 分钟
ss 秒
Q 季度
WW 当年第几周
W 当月第几周
yyyy-mm-dd hh24:mi:ss
sysdate
06-9月-17--->
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
eg:
select to_char(123,'$999.99') tochar from dual;
select to_char(sysdate,'yyyy--mm?dd+d ddd year month ddsp ddspth day dy hh24 mi ss ') 日期 from dual;
5.2 : to_number(参数一):将字符串数据转换为数字类型
返回值是一个数字
参数一:全数字的字符串
只能将能够转化成数字类型的字符串转换成数字类型
select to_number('12345') from dual;
select to_number('abc') from dual;//error
5.3 : to_date(参数一,[参数二]):将字符串转化为日期类型
返回值是日期类型Date
参数一：满足当前系统时间格式的字符串
参数二: 某一个日期规格
to_date('12-7月-16')-->2016/7/12
to_date('2016-3-4','yyyy-MM-dd');
//2016/3/4
5.4:
临时改动日期的格式
alter session set nls_date_format='yyyy-mm-dd HH24:MI:SS';
alter session set nls_language='simplified chinese';
5.5 : 千年虫:
在早期的计算机的程序中规定了的年份仅用两位数来表示。也就是说，假如是1971年，在计算机里就会被表示为71，但是到了2000年的时候这个情况就出现了问题，计算机就会将其年份表示为00。这样的话计算机内部对年份的计算就会出现问题。这个事情当时被称为千年虫
数据库中表示日期中年份的有俩种: yy和rr
之前一直使用的时候yy格式,后来才有的rr格式
yy表示使用一个俩位数表示当前年份:
1990 ---yy数据库格式---> 90
1968 ---yy数据库格式---> 68
1979 ---yy数据库格式---> 79
rr格式表示: 另外参照图片:rr日期格式规则.png
如果日期中的年份采用的格式为rr，并且只提供了最后2位年份，那么年份中的前两位数字就由两部分共同确定：提供年份的两位数字(指定年)，数据库服务器上当前日期中年份的后2位数字(当年)。确定指定年所在世纪的规则如下：
规则1 如果指定年在00~49之间，并且当前年份在00~49之间，那么指定年的世纪就与当前年份的世纪相同。因此，指定年的前两位数字就等于当前年份的前两位数字。例如，如果指定年为15，而当前年份为2007，那么指定年就是2015。
规则2 如果指定年在50~99之间，并且当前年份在00~49之间，那么指定年的世纪就等于当前年份的世纪减去1。因此，指定年的前两位数字等于当前年份的前两位数字减去1。例如，如果指定年为75，而当前年份为2007，那么指定年就是1975。
规则3 如果指定年在00~49之间，并且当前年份在50~99之间，那么指定年的世纪就等于当前年份的世纪加上1。因此，指定年的前两位数字等于当前年份的前两位数字加上1。例如，如果指定年为15，而当前年份为2075，那么指定年就是2115。
规则4 如果指定年在50~99之间，并且当前年份在50~99之间，那么指定年的世纪就与当前年份的世纪相同。因此，指定年的前两位数字就等于当前年份的前两位数字。例如，如果指定年为55，而当前年份为2075，那么指定年就是2055。
注意:rr格式并没有完全的解决俩位数年份保存的问题,思考里面还有哪些问题存在。
需求:将公司员工的入职日期按照yyyy-mm-dd格式显示
select to_char(start_date,'yyyy-mm-dd')
from s_emp;
1, 'Velasquez',
to_date('03-MAR-90 8:30','dd-mon-yy hh24:mi')
90-3-3
'Ngao','08-MAR-90',
select to_char(to_date('5-7月-99'),'yyyy-mm-dd') 时间
from s_emp;
多表连接查询:
1,笛卡尔积:有些记录的连接是没有意义的
使用where条件解决这种问题
n个表，n-1个条件
1 a
2 b
3 c
4 d
2,等值连接 : 一般通过外键和主键的值相同进行表关联
2.1: 连接条件使用 =
2.2: 两列的值是一样的
例一：查询所有员工的ID，名字和所在部门的名称？
select y.id,last_name,name
from s_emp y,s_dept b
where y.dept_id = b.id ;
s_emp s_dept
等值连接:使用某两个列的值相等进行连接
连接条件:
s_emp.dept_id = s_dept.id
例二：查询员工的姓名和部门名称所在地区的名称？
select
last_name,s_dept.name,s_region.name
from
s_emp,s_dept,s_region
where
s_emp.dept_Id = s_dept.id and
s_dept.region_id = s_region.id
练习一 ：查询部门名称包含sa的员工姓名薪水
select name,last_name,salary
from s_dept,s_emp
where s_emp.dept_id = s_dept.id
and lower(name) like '%sa%'
练习二：查询部门名称是5位，该部门员工的薪水不等于1500，并按员工的薪水降序排序
select name,last_name,salary
from s_dept,s_emp
where s_dept.id = s_emp.dept_id and
length(name)=5 and salary!=1500
order by salary desc;
3,不等值连接 : 连接条件使用的不是等号的连接, >= <= between...and
解释 : 不等值连接就是表一的某一列 是表二中某几列的一个中间值
前提 : 创建工资等级表
create table s_rank(
id number(5) primary key,
minSal number(7),
maxSal number(7),
name varchar2(10));
insert into s_rank values(1,0,1000,'蓝领');
insert into s_rank values(2,1000,1500,'白领');
insert into s_rank values(3,1500,2500,'金领');
commit;
练习:
例1：查询所有员工的工资等级？
select last_name,salary,name
from s_emp,s_rank
where salary > minsal and salary <= maxsal
4,外连接 : 外键(某一列)为空 并且需要被查询出来
插入数据:
insert into s_emp(id,last_name) values(999,'_briup');
insert into s_dept(id,name) values(60,'教学部');
commit;
eg:查询所有员工的信息，和所在部门名
select Last_name,dept_Id,name
from s_emp,s_dept
where s_emp.dept_Id = s_dept.id;
4.1:左外连接:
左表 右表
_briup(外键为空)
语法: 左表 left [outer] join 右表 on a表和b表连接的连接条件
左表: 某一条数据外键如果为空 也要被显示出来
会把左表中外键(左表的连接条件)为空的列也查询出来
eg:查询员工的id,
姓名,所在部门id,
部门名,所有员工需要被查询出来
外连接:在多表连接以后把外键为空的数据查询出来
select s_emp.id,last_name,dept_id,name
from s_emp left join s_dept on s_emp.dept_id = s_dept.id
4.2:右外连接:
语法: 左表 right [outer] join 右表 on a表和b表连接的连接条件
会把右表中外键(右表的连接条件)为空的列也查询出来
右表: 某一条数据外键(单纯列,两个表连接的条件列)如果为空 也要被显示出来
eg:查询员工的id,姓名,所在部门id,部门名,
select s_emp.id,last_name,dept_id,name
from s_dept right join s_emp on dept_id = s_dept.id
左外连接:左表中所有行数据都要被显示,(即使有的行没有外键)
右外连接:右表中所有行数据都要被显示,(即使有的行没有外键)
4.3:在连接条件后面 加 (+) 进行左外 和右外连接
左外连接:(+) 加在左表和右表连接条件,右表方。
右外连接:(+) 加在左表和右表连接条件,左表方。
eg:
select last_name,dept_id,name
from s_dept,s_emp
where s_emp.dept_id = s_dept.id(+);
s_emp数据全部显示，如果dept_id类值为空，那么该数据也显示
eg:
select last_name,dept_id,name
from s_dept,s_emp
where s_emp.dept_id(+) = s_dept.id;
//(+)写在不需要全部显示出来数据的一方(两个表连接条件)
4.4:全连接:
解释:综合了左外和右外连接
左表外键为空 右表外键为空的数据都会被显示出来
把两个表中的数据都全部显示出来,虽然连接条件不成立
语法:左表 full [outer] join 右表 on 两个表的连接条件
eg:查询所有员工以及对应的部门的名字,
没有任何员工的部门也要显示出来,
没有部门的员工也要显示出来
select last_name,name
from s_emp full join s_dept on dept_id = s_dept.id
5,自连接
解释 ：当前表和当前表连接
eg:查询每个员工的名字以及员工对应的经理的名字
from s_emp(当前员工name,id..) , s_emp(经理的name...)
select a.id,a.last_name 员工名,a.manager_id,b.last_name 经理名
from s_emp a,s_emp b
where a.manager_id = b.id
6,集合操作符:
前提：是对结果集进行操作,俩个被操作的结果集中查询的列要完全一致
语法 ： 结果集一 集合操作符 结果集二;
minus : 差集 : 第一个结果集减去第二个结果集和它相同的部分
select id,last_name,dept_id
from s_emp
where id<900
minus
select id,last_name,dept_id
from s_emp
where id<890
做分页
12
34
56
3页
union : 并集
select id,last_name,dept_id
from s_emp
where id<10
union
select id,last_name,dept_id
from s_emp
where id>6
union all : 并集
select id,last_name,dept_id
from s_emp
where id<10
union all
select id,last_name,dept_id
from s_emp
where id>6
intersect : 交集
select id,last_name,dept_id
from s_emp
where id<10
intersect
select id,last_name,dept_id
from s_emp
where id<5
1,操作两个结果集
2,必须列相同
3,第一个结果集不需要写 ;
oracle检索规则
from s_emp
where id=10;
1，将表s_emp中的每一条数据放到结果集中。
2，如果该条数据满足where条件那么就留在结果集中，并检索下一条数据。
3，如果该条数据不满足where条件 那么就从结果集中剔除该数据，并检索下一条数据。
查询员工的姓名 部门名
7,rownum : 伪列(oracle数据库独有的)
检索规则: 检索表中每条数据，会从上到下检索，每次检索一条数据，
每次检索的数据会先放到结果集中，放到结果集中的数据如果满足where条件 就会留在结果集中，如果不满足就会从结果集中删除。
定义:在结果集中的每条数据oracle会自动分配一个列-->伪列-->伪列的值是1->n
作用：给结果集中的数据编号
测试一:查询id,last_name,rownum但是只要伪列等于2的数据。
select id,last_name,rownum
from s_emp
where rownum = 2;
为什么没有数据？分析？
测试二:查询id,last_name,rownum但是只要伪列小于3的数据。
select id,last_name,rownum
from s_emp
where rownum < 3;
为什么有数据？分析？
测试三:查询id,last_name,rownum但是只要伪列大于3并且伪列小于5的数据。
select id,last_name,rownum
from s_emp
where rownum > 3 or rownum < 5;
为什么没有数据？分析？
思考:利用伪列进行分页操作(介绍方式)
day3--end
分组函数/组函数:
1,基础:分组概念
1.1:效果:分组以后，表变成什么样子了
1.2:关键字: group by
1.3:书写位置:紧跟着where 后面
1.4:语法: group by a列,b列…
1.5:意思: 对当前结果集中的数据进行分组,对a列分组,如果a列中有相同的数据再按照b列进行分组
如果按照多个列分组，会先按照第一列分组，然后按照第二列分组…
或者说：只有在列1和列2和列3的值都相同的才会被分到一个组。
1.6: 对组的限定: having 限定条件
不写group by 并且使用组函数:默认是把整个表分成一个小组
2,组函数:
2.1:基础:只有在分组的结果集中才能使用组函数
或者说有group by的sql语句才能使用组函数.
2.2:解释:使用组函数操作小组,每个小组会产生一个值
具体:
avg(列):求平均值 :在某个小组中列的平均值
count(列):计算记录总数 :在某个小组中列的记录总数
Max(列):求最大值 :在某个小组中列的最大值
Min(列)：求最小值 :在某个小组中列的最小值
sum(列):求和 :在某个小组中列的和
STDDEV(列)：标准差
VARIANCE(列)：方差
2.3:理解组函数：
插入数据:
insert into s_emp(id,last_name,dept_id) values(123,'Patel',42);
commit;
eg:查询 41号部门的平均工资。
平均:avg(列)->组函数->分组
select avg(salary),dept_id
from s_emp
where dept_id = 41
group by dept_id;
select dept_Id
from s_emp
where dept_id = 41
group by dept_id;
2.4:得到结论：
如果使用了组函数,那么在select中的列，必须是group by 列 不然查询不到。
(只能查询列1,列2)
select 列1,列2,组函数(表中任意的列)
from 表
group by 列1,列2
2.5:对表的多个列进行分组:
eg:
select last_name,salary
from s_emp
group by last_name,salary
解释:对last_name和salary进行分组，如果有某几条数据last_name和salary值相同那么这几条数据就会被分到同一个组。
3,代码执行顺序:
3.1:顺序:from--->where-->group by分组-->执行组函数-->having筛选->select-->order by
select avg(salary),last_name
from s_emp
where id<10
group by last_name
having avg(salary) > 10
order by avg(salary);
3.2:组函数出现的位置 : select字句 having字句 order by字句
4,组函数练习:
4.1:查询所有员工的平均工资?
select avg(salary),count(commission_pct)
from s_emp
注意:如果写了组函数 没有group by
那就是把整张表分成一个小组
4.2:查询每个部门的平均工资?
avg(salary)
select dept_id,avg(salary),sum(salary)
from s_emp
group by dept_id
4.3:查询平均工资大于1500的部门?
avg(salary);
select dept_Id,avg(salary),count(id),max(salary)
from s_emp
group by dept_id
having avg(salary)>1500;
4.4:不能在GROUP BY子句中使用select列别名。
select dept_id a
from s_emp
group by a;
4.5:查询每个部门的平均工资并且显示出部门的名字和部门id？
select avg(salary),dept_Id,name
from s_emp,s_dept
where dept_id = s_dept.id
group by dept_id,name;
4.6:查询平均工资大于1400的部门id,并且显示出部门的名字？
select name,dept_id,avg(salary)
from s_emp,s_dept
where dept_id = s_dept.id
group by dept_id,name
having avg(salary) > 1400;
4.7:查询title中不包含vp字符串的每个职位的平均薪水，并对平均薪水进行降序排列，并且每个职位的总薪水大于5000？
select title,avg(salary),sum(salary)
from s_emp
where lower(title) not like '%vp%'
group by title
having sum(salary) > 5000
order by avg(salary) desc;
4.8:查询最大工资数,并且显示出这个人的名字？
第一种：
select last_name,max(e2.salary)
from s_emp e1,s_emp e2
group by e1.last_name,e1.salary
having e1.salary=max(e2.salary);
第二种：
select last_name,salary
from s_emp
where salary=(
select max(salary)
from s_emp
);
子查询
1,定义 : 一条sql语句嵌套一个或者多个sql语句
1,把查询一的结果集(多行多列)当做表 进行查询
2,把查询一的结果集(多行一列)当做当前表的限定条件
3,把查询一的结果集(一行一列)当做当前表的限定条件
2,格式 :
select…
from…
where 字段名 比较符 (
select…
from…
where…(
select…
from …
where…
group by …
having…
order by …
)
group by…
having…
order by…
)
group by…
having…
order by…
3,什么情况下使用：比较符中参数不确定的情况
4,子查询出现的地方：1）from 子句
2）where 子句
3) having
5,子查询练习:将复杂的查询分解为简单的查询然后进行嵌套.
步骤一 ： 将复杂查询分解为多个简单的查询
步骤二 ： 使用子查询将多个简单的查询融入到一个sql中
5.1:查询工资比[41号部门平均工资 1247.5]高的员工信息
分解1：41号部门平均工资
select avg(salary)
from s_emp
group by dept_id
having dept_Id = 41
分解2:查询工资比1247.5高的员工信息
select id,last_name,salary
from s_emp
where salary > (select avg(salary)
from s_emp
group by dept_id
having dept_Id = 41);
5.2:查询工资比{[NewMan所在部门]平均工资}高的员工信息
分解1:NewMan所在部门-->43
select dept_id
from s_emp
where lower(last_name) = 'newman'
分解2:43号部门平均工资-->900
select avg(salary)
from s_emp
where dept_id = (43)
group by dept_Id
分解3:工资比900高的员工信息
select id,last_name,salary
from s_emp
where salary > (900);
最终结果：
select id,last_name,salary
from s_emp
where salary > (select avg(salary)
from s_emp
where dept_id = (select dept_id
from s_emp
where lower(last_name) = 'newman')
group by dept_Id);
5.3:查看部门平均工资大于[32号部门平均工资 1490]的部门id
分解 1:32号部门平均工资--->1490
select avg(salary)
from s_emp
where dept_Id = 32
group by dept_id
分解 2:查看部门平均工资大1490的部门id
select dept_id,avg(salary)
from s_emp
group by dept_id
having avg(salary) > 1490;
最终:
select dept_id,avg(salary)
from s_emp
group by dept_id
having avg(salary) > (select avg(salary)
from s_emp
where dept_Id = 32
group by dept_id);
5.4:查询{平均工资大于[41号部门平均工资]的所有部门}中的员工信息
分解:41号部门平均工资 1247.5
select avg(salary)
from s_emp
where dept_Id = 41
group by dept_id
分解2:平均工资大于1247.5的部门
31 32 33 35 10 50
select dept_Id
from s_emp
group by dept_Id
having avg(salary) > 1247.5
分解3:查询31,32,33,35,10,50部门中的员工信息
select id,last_name,salary
from s_emp
where dept_id in (31,32,33,35,10,50);
最终:
select id,last_name,salary from s_emp where dept_id in (select dept_Id from s_emp group by dept_Id having avg(salary) > (select avg(salary)
from s_emp where dept_Id = 41 group by dept_id));
5.5:查询出工资比[Smith的经理薪水 1200]高的员工信息和Smith的经理id
分解一:Smith的经理id —>
select manager_id
from s_emp
where lower(last_name) = ‘smith’
分解二 : 通过6员工 得到经理的薪水
select salary
from s_emp
where id = 6;
select id,last_name,salary,jid.manager_id
from s_emp,(select manager_id
from s_emp
where lower(last_name) = ‘smith’) jid
where salary > (select salary
from s_emp
where id = (select manager_id
from s_emp
where lower(last_name) = ‘smith’));
test2:一步得到经理工资–>自连接
select j.salary
from s_emp y,s_emp j
where y.manager_id = j.id and lower(y.last_name) = ‘smith’;
测试 子查询写在from 中
select 列1,列2,…(必须要结果集中有的列)
from (结果集) 别名
where 别名.结果集中的列 = 4;
select *
from (select id,last_name,salary from s_emp where id < 6) b
where b.id = 3;
例子:利用子查询做分页:+rownum
1—> 1 2
2—> 3 4
3—> 5 6
2页: 3 4
select id,last_name,salary,rownum r
from s_emp
where rownum < 5;
rownum+子查询 分页的例子:
select id,last_name,salary
from (select id,last_name,salary,rownum r
from s_emp
where rownum < 5) a
where a.r > 2
分页加运行时变量:
模板:
select id,last_name,salary
from (select id,last_name,salary,rownum r
from s_emp
where rownum <= &max) a
where a.r >= &min
运行时变量
1,定义:在sql语句执行时动态添加进去的数据,
2,语法: 使用关键字 &变量名
3,书写位置：
select &变量名
from &变量名
where &变量名
模板:查询列 用户输入
查询表 用户输入
限制id 的条件 用户输入
select &col1,&col2,&col3
from &tablea
where id = &id
4,需求: 查询某一部门的员工信息，部门的id可能根据需要不断变化，
如果直接写dept_id=41，则只能查询41号部门的员工
可以使用 替换变量 即运行时变量，进行占位，以后拿到实际值的时候再进行替换
select id,last_name,salary
from s_emp
where dept_Id = &dept_id
5,操作: &变量名 : 这就是运行时变量，就是一个占位.
当执行包含运行时变量的sql语句时,
会给提示你输入，你输入的值就会替换到 &变量名 那
eg:
select &column1,&column2,&column3
from s_emp
where id=#
eg:用户做模糊查询，有一个值是一直在改变。
select id,last_name,salary
from s_emp
where last_name like '%&name%';
6,规范:
变量名注意命名规则
i 不要使用特殊字符
ii 不要数字开头
iii 不要使用sql中的关键字
7,set verify on/off : 是否打印 运行时变量被替换以后的样子
on : 显示
off: 不显示
8, def: 预定义运行时变量的值:def[ine] 运行时变量名 = 运行时变量的默认值;
eg:
def id = 5;
执行下面sql
select id,last_name
from s_emp
where id = &id;
不需要输入id的值.
8.1: define column_name(变量名)：查看变量命令。
eg: define id
9, undef: 取消已经定义的变量 : undef 运行时变量名
eg: undef id;
select id,last_name
from s_emp
where id = &id;
又需要输入id的值
10,accept: 定义一个变量 值为用户再次输入的数据
语法： accept 变量名
eg : accept id;
sql:
select id,last_name
from s_emp
where id = &id;
11,prompt: 与accept连用 当再次输入数据的时候给提示
语法： accept 变量名 prompt '提示'
eg: accept id prompt '提示信息'
12,hide : 与accept连用 当再次输入的数据时候 不显示用户输入的数据
语法: accept 变量名 hide
eg: accept id hide
*一般在sql脚本中使用预定义值
create
数据库设计:
需求分析-->建模-->建立数据字典-->建表-->根据需求构建测试数据-->测试数据库<------>数据库的重构
建模过程中用的元素：
实体(Entity) : javaBean / 表
属性(Attribute) : 类的属性 / 表中的列
关系(Relationship) : 类和类的关系/表与表的关系
实体：用圆角矩形来表示，实体的名字一般用大写，而且一般会和数据库中的表的名字保持一致
对象实体：现实生活中真实存在的事物
业务实体：由项目中业务逻辑产生出来的实体
属性：一般用小写字母表示，并且一个属性在一张表中只能出现一次
ER图中符号的表示
1) # : 唯一, 以后可能表示为主键
2) *: 非空
3) o: 代表随意，可有可无
4) 虚线: may be 该表的数据不需要另外一张表，外键可以为空，也可以不为空
5) 实线: must be 该表的数据必须依赖另外一张表,外键值不能为空
6) 竖杠(|): 代表要强制在(|)一方建立一个联合主键.
代表联合主键：有两个或者两个以上的字段联合在一起作为主键
当表中的单个字段无法唯一比标志数据
主键的作用用来唯一标识表中的数据
一个表中一个主键
使用多个列 组合 来唯一表示一条数据
实体之间的关系：
one-to-one 1:1
one-to-many 1:n <===>
many-to-one n:1
many-to-many n:n
记下:
1) 一对一关系如果要建立主外键关系，如果都是实线或都是虚线可以随便建。
如果一边实线，一边虚线，实线一边建外键。
2) 一对多关系外键建立在多的一方
3) 多对多可以拆成两个一个多或者建一张桥表
建表：
1,语法:
create table 表名(
列名 列的数据类型 [default ‘默认值’ ] [column_constraint], //列的约束
…
[table_constraint]
);
2,五种约束：
1,not null :不为空 :只能存在列级约束
2,unique :唯一 :定义在表级约束或者列级约束都可以
3,primary key :主键 :定义在表级约束或者列级约束都可以
4,foreign key :外键 :定义在表级约束或者列级约束都可以
5,check :检查约束条件 :定义在表级约束或者列级约束都可以
3,两种约束级别：
3.1:列级约束：在每一列后面加约束
语法规则：constraint 约束名字 约束类型(五种) 约束类型(五种)
语法规则: 约束类型(五种) 约束类型(五种)
eg:
create table s_rank(
id number constraint s_rank_id_pk primary key,
minsal number not null,
maxsal number,
name varchar2(7)
);
3.1:表级约束：表最后
语法规则：用constraint
constraint 约束名字 约束类型(unique/primary key(列)/foreign key/check)
eg:
create table s_rank(
id number ,
minsal number ,
maxsal number,
name varchar2(7),
constraint s_rank_b_id primary key(id)
);
4,五种约束详细解析：
4.1:NOT NULL//修饰列
4.1.1:解释:限制取值不为空
4.1.2:确保该列不允许空值
4.1.3:只能在列约束级别上定义
drop table student;
create table student(
id number(7) not null,
name varchar2(50) constraint student_name_nn not null,--为了起名有意义：表名_列名_约束条件
gender varchar2(10) default 'male'
);
insert into student(id,name) values(1,NULL);
4.2:unique
4.2.1:指定一列或列组中任何两行都不能有相同值
4.2.2:允许空值(多列空值不同)
4.2.3:定义在表级别或者列级别都可以
4.2.4:列级约束:
drop table student;
create table student(
id number(7) constraint student_id_un unique,//添加了一个列级约束
name varchar2(50) unique
)
insert into student(id,name) values(1,NULL);//插入成功
insert into student(id,name) values(2,'tom');//插入成功
insert into student(id,name) values(2,'tom2');//插入失败
insert into student(id,name) values(3,'tom2');//插入失败
4.2.5:表级约束:
drop table a;
create table a(
id number(7),
name varchar2(50),
constraint abcdsss unique(id),
constraint nsdfsdf unique(name)
)
insert into student(id,name) values(1,NULL);//插入成功
insert into student(id,name) values(11,NULL);//插入成功
insert into student(id,name) values(2,'tom');//插入成功
insert into student(id,name) values(2,'tom2');//插入失败
insert into student(id,name) values(3,'tom2');//插入失败
4.3:primary key
4.3.1:一个表中只允许一个主键
4.3.1:非空且唯一
4.3.1:可以定义表级别可以定义列级别
4.3.1:列级约束:
drop table p;
create table p(
id number primary key,
name varchar(10)
);
create table p(
id number,
name varchar(10) constraint myprimary primary key
);
4.3.1:表级约束:
drop table p;
create table p(
id number,
name varchar(10),
constraint myprimarykey primary key(id)
);
4.3.1:联合主键:
4.3.1.1:当前表使用两个列或者更多列当做主键
4.3.1.2:联合主键创建到表级约束上
drop table wp;
create table wp(
id number(7),
name varchar2(50),
constraint wp_idAndName_pkw primary key(id,name)
);
insert into wp(id,name) values(1,'tom');//成功
insert into wp(id,name) values(1,'tom1');//成功
insert into wp(id,name) values(2,'tom');//成功
insert into wp(id,name) values(1,'tom');//失败
4.4:foreign key
4.4.1:声明外键,和另一个表主键进行关联的列
4.4.1:要么为空，要么为另一张表的主键
4.4.1:列级约束
先建立主表（被外键所引用的列所在表）
drop table a;
create table a(
id number primary key,
name varchar2(20) unique not null
);
后建立外键所在的表
drop table b;
create table b(
id number primary key,
name varchar2(10),
a_id number constraint a_fk references a(id)
);
create table b(
id number primary key,
name varchar2(10),
a_id number references a(id)
);
4.4.1:表级约束:
drop table b;
create table b(
id number primary key,
name varchar2(10),
a_id number,
constraint fk_a foreign key(a_id) references a(id)
);
注意：联合外键引用的另一张表的必须是联合主键,并且本表中的两列需要与另表中的数据类型相同
create table wb(
id number primary key,
wp_name varchar2(10),
wp_id number,
constraint leo_id_fk foreign key (wp_id,wp_name) references wp(id,name) ON DELETE CASCADE
);
注:联合外键只能建立到表级别上,引用的wp表必须是以id,name为联合主键，那么才能在wb表中使用联合外键
ON DELETE CASCADE 级联删除行内 内容
删除父表中的数据，把关联的子表数据一同删除
如果没有设置级联删除，需要先删除外键所在的表中的内容，再删除主表中的内容
4.5:check
4.5.1:意义:对某一个列输入的值做限制
4.5.1:语法:constraint 约束名 check(逻辑表达式)
drop table abc;
create table abc(
id number check(id > 4)
);
eg:ch表gender列的值只能是男或女
drop table ch;
create table ch(
gender varchar2(15) constraint abccheck check(gender in('男','女'))
);
eg:ch表的age列需要在20-90之间
drop table ch;
create table ch(
age varchar2(15),
constraint abccheck check(age> 20 and age< 90)
);
eg:ch表name列长度只能在3-6之间
drop table ch;
create table ch(
name varchar2(15),
constraint abccheck check( length(name) between 3 and 6 )
);
eg:ch表name列前两位只能输入 ab
drop table ch;
create table ch(
name varchar2(15),
constraint abccheck check( substr(name,1,2)='ab' )
);
5,关联关系建表语句:
5.1 : 1对1:使用外键关联,外键值唯一
5.2 : 1对多:使用外键关联,外键维护在多的一方，外键的值不唯一
5.3 : 多对多：使用桥表，桥表的两个列，一个是a表的主键，一个是b表的主键，桥表是联合主键。
insert,update，delete
特点：执行DML语句的时候会产生事务(commit后修改的数据才会生效)
insert:插入数据
语法：
insert into 表 values(表中每列的值);----> 这条语句没有真正的对数据库产生影响(改变)
insert into 表(列1,列2,列3) values(列1对应数据1,列2对应数据2,列3对应数据3); 给表中的某几列添加值
给s_rank表插入一条新的数据;
eg:
//values(列1的值,列2的值...)
//列1--> desc 表: 中的第一行数据
//列2--> desc 表: 中的第2行数据
//列3--> desc 表: 中的第3行数据
insert into s_rank values(1,100,200,'name');
insert into s_rank(minsal,maxsal,id,name) values(100,200,29,'你好');
在表(列名1,列名2) values(列名1的值,列名2的值);
insert进阶:插入的数据是从一张表中查询得到
语法:insert into 表(列1,列2,列3...) select 列1的值,列2的值,列3的值 from 表2 where 条件 group by 分组 having 分组约束 order by 列排序;
测试:
基础表:
drop table t1;
create table t1(
id number,
name varchar2(20)
);
insert into t1 values(1,'tom1');
insert into t1 values(2,'tom22');
insert into t1 values(3,'tom333');
insert into t1 values(4,'tom4444');
insert into t1 values(5,'tom55555');
测试表：
drop table t2;
create table t2(
id number,
name varchar2(20),
age number,
email varchar2(20)
);
测试开始:
向t2表中保存数据,数据t1表中的列
update语句 更新数据
语法：
update 表
set 列1=新值1,列2=新值2...
where 限定条件
需求:向s_rank表中添加一条数据
11,0 --- 5000 普通人
insert into s_rank(id,minsal,maxsal,name)
values(14,0,5000,‘普通人’);
更新s_rank表中id为14的一条数据把maxsal修改为2000
update s_rank
set maxsal=2000
where id = 14;
修改minsal -》1000
普通人 -》 一般人
update s_rank
set minsal = 1000 , name = ‘一般人’
where id = 14;
update进阶:更新的数据是从一张表中查询得到
语法:update 表1
set (列1,列2,列3) = (select 列一的值,列二的值,列三的值 from t1 where 表1.id = 表二.id)
where id < 3;
eg:
update test
set (id,name) = (select id,last_name from s_emp where test.id = s_emp.id)
where id < 10;
delete删除表中的某几行数据:删除某一张表中的某一行或者某几行数据
delete 表 where 限制条件
delete s_rank;
eg:
delete s_rank where id = 14;
on delete cascade
建表语句和测试数据上例1相同,只是在声明外键列的时候加入on delete cascade语句
create table abc(
id number,
a_id number,
constraint id_pk primary key(id),
constraint aid_fk foreign key(a_id) references a(id) on delete cascade
);
建表语句和测试数据上例1相同,只是在声明外键列的时候加入on delete set null语句
create table abc(
id number
a_id number,
constraint id_pk primary key(id),
constraint aid_fk foreign key(a_id) references a(id) on delete set null
);
create,alter,rename,truncate
create:
1,创建表：
create table 表名(
列名 列的数据类型 [default] ‘默认值’ [column_constraint], //列的约束
…
[table_constraint]
);
create table s_rank(
id number,
minsal number,
maxsal number,
name varchar2(7)
);
2,创建序列:
create sequence 序列名
[increment by n] 每次增加几 默认：1
[start with n] 从哪开始 默认：0
[{maxvalue n | nomaxvalue}] 最大值 默认：n
[{minvalue n | nominvalue}] 最小值 默认：0
[{cycle | nocycle}] 循环：在取值达到最大值的时候就循环 默认：不循环
[{cache n | nocache}] 缓存，把序列的值放到缓存中，提高效率，缓存这个个数不能大于最大值 默认：没有
create sequence 序列名;//简单创建序列 所有都默认 也可以
eg:
create sequence myseq123
increment by 3
start with 0
maxvalue 12
minvalue 0
cycle
cache 4;
序列名:myseq123
步长：3
从0开始
最大值12
最小值0
循环
缓存4个数据
0 3 6 9 12 0 3 6 9 12-----
序列的属性:
每个序列对象有俩个属性:
nextval 序列中的下一个值
currval 序列中的当前的值 ---> 刚创建出来的序列是没有当前值 会报错
序列属性的调用:
序列名.属性
eg: myseq.nextval
myseq.currval
eg:动态参数+序列:插入多条数据;
insert into test(id,name) values(test_seq.nextVal,'&name');
3,创建视图(view)：
语法: create view 视图名
as
子查询
create view s_view
as
select id,last_name,salary
from s_emp
定义:视图是一条查询的sql语句，用于显示一个或多个表或其他视图中的相关数据。
视图将一个查询的结果作为一个表来使用，因此视图可以被看作是一个虚拟表.
视图基于的表称为基表。
好处:
用户可以通过视图以不同形式来显示基表中的数据。
通过视图可以设定允许用户访问的列和数据行，从而为表提供了额外的安全控制。
视图就对用户隐藏了数据的来源。
创建视图:
前提：当前用户需要有创建序列的权限
授权:给用户授予创建序列的权限(需要管理员用户才可以)
授权语法:grant create view to 用户名;
方式一:
create view s_emp_view
as
select id,last_name,salary from s_emp
方式二:如果视图名已经存在，那就会先删掉再创建
create or replace view s_emp_view
as
select id,last_name,first_name,salary from s_emp
with read only;
查看视图:
select 列,... from 视图名
删除视图:
drop view 视图名;
更新视图：
update 视图名 set 列名=''
where 条件;
视图只读:with read only; 加在创建视图的最后一行。
create or replace view s_view
as
select last_name,salary,dept_id
from s_emp
with read only;
视图限制dml操作:with check option,加在创建视图的最后一行
特点:DML的结果满足该创建的视图中的where子句条件，DML就可以正常进行
//测试用的表及其数据
drop table t_user;
create table t_user(
id number constraint user_id_pk primary key,
name varchar2(100),
salary number
);
insert into t_user values(1,'tom',1000);
insert into t_user values(2,'tom2',2000);
创建视图:
create or replace view v_test
as
select id,name,salary
from t_user
where id <6
with check option;
//插入报错 插入的数据不符合 创建视图时的where条件
insert into v_test values(10,'tom3',3000);
//更新失败 更新以后的结果不符合 创建视图时的where条件
update v_test
set name='lily'
where id=1;
简单视图: 视图所代表的sql中如果没有group by语句,没有组函数,查询的只有一张表,那么这样的视图就是简单视图.
1, 对简单视图进行增删改查会影响基表中的数据.
4,创建索引:
1,概念:
1. 类似书的目录结构
2. Oracle 的"索引"是一种对象，是与表关联的可选对象，能提高SQL查询语句的速度
3. 索引直接指向包含所查询值的行的位置，减少磁盘I/O
4. 索引和表是相互独立的物理结构
5. Oracle 自动使用并维护索引，插入、删除、更新表后，自动更新索引
2,自动创建
当在表中指定了primary Key或者unique约束时会自动创建唯一值索引。
3,用户创建。
用户可以创建非唯一值索引以提高在访问数据时的效率。
格式:
create index 索引名
on 表名 (列名);
eg:
create index emp_index
on s_emp(last_name);
4,查看当前用户的索引
select index_name from user_indexes;
5,给某列创建索引的原则：
1.列经常作为where子句的限定条件或者作为连接条件
2.列包含的数据量很大，并且很多非空的值。
3.两个或者更多列频繁的组合在一起作为where的限定条件或者连接条件
4.列总是作为搜索条件
5.索引查出的数据量占2%~4%
6.索引不是越多越好，不是索引越多越能加速查找。
7.要索引的表不经常进行修改操作
5,创建用户：
create user 用户名 identified by 密码;
没有权限
alter:
只能操作列
1,添加列：
alter table 表名
add(列名 列的数据类型 列的约束条件)
alter table test
add(gender varchar2(20) not null)
2,删除列：
alter table 表名
drop column 列名;
alter table test
drop column last_name;
3,改变数据类型：
alter table 表名
modify(列名 新的数据类型(12) 列的约束条件);
alter table test
modify(age number(20,2) not null)
4,添加列的唯一性约束：
alter table 表名
add constraint 约束名 约束类型(列名);
alter table test
add constraint testa_id_pk primary key(id)
5,删除表中的约束
alter table 表名 drop constraint 约束名;
alter table test drop constraint testa_id_pk;
eg:修改那么的约束为 非空unique 约束
alter table 表名 modify(列名 约束类型);
alter table test
modify(name unique);
drop:
1,删除表：
drop table 表名;
2,删除序列:
drop sequence 序列名;
3,删除视图：
drop view 视图名;
4,删除索引:
drop index 索引名;
5,删除用户:
drop user 用户名 cascde;
rename:
1,修改表的名字
rename 旧表名 to 新表名;
rename test to newtest;
2,修改表中列的名字
alter table 表名
rename column 旧列 to 新列
alter table newtest
rename column dob to newdob;
truncate:
1,删除表中所有数据:truncate table 表;
2,truncate和delete作用一样,也是删除表中数据（只有要删除表中所有数据的时候truncate才和delete等价）
3,delete是DML语句,删除数据后需要自己手动的提交事务或者回滚事务
4,truncate是DDL语句,删除数据后会自动的帮我们提交事务
TCL语句:commit(提交), rollback(回滚), savepoint(记录点,用来回滚)
事务 : 由一组DML语句组成: dml --> insert delete update
1,定义 : 指的是一组操作对数据库的数据真正的影响,这一组操作中有一个操作失败了 这一组事务就全失败了;
2,开启事务:执行第一条dml语句,接下来所有的dml语句都会放到事物中。
3,事务的提交:commit和DDL语句都会提交事务
背:
4,事务的特点:ACID（A原子性、C一致性、I隔离性和D持久性）
1)原子性:事物同时成功或者同时失败
2)一致性:是指事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。
3)隔离性:事务操作应该相互独立
4)持久性:事务提交之后,它对于系统的影响是永久性的:对数据库产生真正的影响。
5,commit;提交事物,对数据库产生永久的影响。没有提交前的数据在当前的session缓存中可以被查询。
commit;ddl ; exit;(退出);
6,exit; 退出sqlplus控制台,会提交事物,点击关闭 也会提交事务(有的版本会)
7,rollback； 回滚全部事务,当前的一组操作失败了，把这一组操作都返回到原始状态.
8,savepoint 保存点; 标记保存点,可以回滚到标记点。
9,rollback to 保存点名; 回滚到某标记点
10,测试事务:
create table c(name varchar2(20));
提交:
insert into c values('tom1') 产生一个事务A
insert into c values('tom2') 把这个操作放进已经存在的事务A里面
updete c set name = 'jary'; 把这个操作放进已经存在的事务A里面
delete c where name ='tom1' 把这个操作放进已经存在的事务A里面
commit; 提交事务:让前面的DML操作都生效
回滚:
insert into c values('tom1') 产生一个事务A
insert into c values('tom2') 把这个操作放进已经存在的事务A里面
updete c set name = 'jary'; 把这个操作放进已经存在的事务A里面
delete c where name ='tom1' 把这个操作放进已经存在的事务A里面
rollback; 回滚事务:让前面的DML操作都撤销
保存点:
insert into c values('tom1'); 产生一个事务A
insert into c values('tom2'); 把这个操作放进已经存在的事务A里面
insert into c values('tom3'); 把这个操作放进已经存在的事务A里面
insert into c values('tom4'); 把这个操作放进已经存在的事务A里面
savepoint a_point;
update c set name='jary'
where name='tom3'; 把这个操作放进已经存在的事务A里面
delete c where name ='tom1'; 把这个操作放进已经存在的事务A里面
savepoint b_point;
insert into c values('tom5'); 把这个操作放进已经存在的事务A里面
insert into c values('tom6'); 把这个操作放进已经存在的事务A里面
回滚到:
rollback to 保存点;
eg: rollback to a_point;
11:事务隔离级别 isolation ,了解
事务并发访问中产生的问题:
1.脏读 主要针对update操作。 一个事务A读到另一个事务B中修改过但是还没有提交的数据
2.不可重复读 主要针对update操作。 一个事务A在第一次读数据和第二次读数据之间,有另一个事务B把这个数据更改并提交了,所以就出现了事务A里面读一个数据俩次,但是读到的结果是不同的。
3.幻读 主要针对的是insert/delete操作。事务A第一次用where条件筛选出了10条数据,事务A第二次用通样的where条件筛选出的却是11条数据,因为事务B在事务A的第一次和第二次查询直接进行了插入操作,并且插入的这个数据满足事务A的where筛选条件.
事务隔离级别有:
read-uncommitted 不提交也能读
read-committed 提交之后才能读 解决了脏读
repeatable-read 解决了脏读和不可重复读
serializable 三个问题都解决了
级别越高解决的问题越多但是效率越低。
注意:并不是所有数据库都支持这四种事务隔离级别,比如oracle就只支持第二种和第四种这俩种,比如mysql就四种全支持.
oracle里面默认的事务隔离级别是第二种:read-committed
oralce里面设置事务隔离级别:
Set Transaction Isolation Level Read Uncommitted;
Set Transaction Isolation Level Read Committed;
Set Transaction Isolation Level Read Repeatable;
Set Transaction Isolation Level Serializable;
事物特点(背)
事物并发导致的问题:
怎么解决事物并发导致的问题:设置事物的隔离级别
用户权限控制
1.创建用户
create user user_name identified by password
例如:
create user zhangsan identified by zhangsan;
2.删除用户
drop user zhangsan cascade;
3.赋予权限
grant 权限 to user;
例如:把建表 建序列 建视图的权限赋给zhangsan
grant create table, create sequence,create view to zhangsan;
//把connect角色和resource角色赋给zhangsan
//角色是一组权限的集合
grant connect,resource to zhangsan;
注意: 只是登陆oracle数据库的话 需要的权限是create session
4.修改密码
alter user user_name identified by password;
例如:
alter user zhangsan identified by zhangsan123;
5.赋予某一个用户某种对象操作的权限
grant 操作[select/delete/update/insert] on 对象名,对象名[表名...] to 用户;
6.回收权限
revoke 操作[select/delete/update/insert] on 对象名,对象名[表名...] from 用户;
例如:
revoke insert,select
on s_view
from tom;
7.创建同义词synonym
作用:可以隐藏表原来的名字
分为:私有同义词 公共同义词
//给表t_user创建一个私有同义词
create synonym my_test for t_user;
create synonym abcds for s_emp;
grant create synonym to briup;
//给用户briup授权可以查询my_test
grant select
on my_test
to briup;
//收回用户briup查询my_test的权限
revoke select
on my_test
from briup;
//利用数据字典查看同义词synonyms
//用户创建的同义词有哪些
select synonym_name
from user_synonyms;
//用户有权利查询的同义词有哪些
select synonym_name
from all_synonyms;
//用户有权利查询的同义词有哪些是以字母D开头的
//注意:表中的数据都是大写存在
select synonym_name
from all_synonyms
where synonym_name like 'D%';
结果可以看到我们常用的dual
8.删除同义词synonym
drop synonym name;
例如:
删除私有同义词
drop synonym my_test;
删除公共同义词
drop public synonym my_test;
9.公共的同义词
因为普通用户没有创建public synonym的权限,所有我们需要用dba的身份登录到数据库中去创建。
sqlplus "/as sysdba"
或者system用户登录
create public synonym my_test2
for test.t_user;
//在管理员权限下创建公有同义词
//同义词名:abc
//同义词来自的表
//所有用户都可以访问同义词
create public synonym abc
for briup.test_abc;
//让所有人都有查询这个同义词的权限
grant select on abc to public;
然后其他用户登录之后就可以通过这个公共的同义词来查询test用户下面的t_user表了
10,数据库的导入导出
系统终端执行
导出：exp 根据提示按回车下一步即可
导入：imp 根据提示按回车下一步即可
导出：数据表和表中数据
数据字典:
*,查询用户下所有的表
select table_name from user_tables;
*,查询用户下所有的视图
select view_name from user_views;
*,查询用户下所有的序列
select sequence_name from user_sequences;
数据字典
dictionary
1,定义:
数据字典是Oracle存放有关数据库信息的地方，其用途是用来描述数据的。
比如一个表的创建者信息，创建时间信息，所属表空间信息，用户访问权限信息等
查询数据字典的所有表名
select table_name from dictionary
where lower(table_name) like 'user_t%';
例如:查询dictionary中有多少条记录
select count(*) from dictionary;
COUNT(*)
----------
626
注意:自己建的表在dictionary这个表是查询不到的.
2,查看当前用户有哪些表
select table_name from user_tables;
3,查看当前用户有哪些表和回收站中的表
select object_name
from user_objects
where object_type='TABLE';
静态数据字典:主要是在用户访问数据字典时不会发生改变的
动态数据字典:是依赖数据库运行的性能的，反映数据库运行的一些内在信息,数据库运行的时候它们会不断进行更新，所以在访问这类数据字典时往往不是一成不变的
Oracle中这些动态性能视图都是以v$开头的视图.
数据字典中的表是不能直接被访问的，但是可以访问数据字典中的视图。
静态数据字典中的视图分为三类，它们分别由三个前缀够成：user_、 all_、 dba_*。
dba—> 数据库管理员
user_*
该视图存储了关于当前用户所拥有的对象的信息（即所有在该用户模式下的对象）
USER_TABLES 包含有关所有表的名称、列数和其他详细信息的信息
SELECT table_name,tablespace_name,dependencies from user_tables where table_name like 'D%'; 此示例显示有关用户创建的表的详细信息。
USER_CATALOG 包含表、视图和同义词
USER_COL_COMMENTS 包含对列的备注
USER_CONSTRAINTS 包含表的约束定义
USER_INDEXES 包含有关为表 (IND) 创建的索引的所有信息
USER_OBJECTS 包含用户拥有的所有数据库对象
USER_TAB_COLUMNS 包含用户拥有的表的列和视图
USER_TAB_COMMENTS 包含对表和视图的备注
USER_TRIGGERS 包含用户定义的所有触发器
USER_USERS 存储有关当前用户的信息
all_*
该视图存储了当前用户能够访问的对象的信息。（与user_*相比，all_* 并不需要拥有该对象，只需要具有访问该对象的权限即可）
ALL_TABLES 包含所有可访问表的所有者和这些表的名称
SELECT owner,table_name, tablespace_name from all_tables where table_name like 'B%'; 此示例显示 ALL_TABLES 视图中表名以“B”开头的表所有者、表名和表空间名。
ALL_CATALOG 包含所有可访问表、视图和同义词的所有者及其相应名称和类型
ALL_OBJECTS 包含可访问数据库对象的所有者及其类型和名称
ALL_TRIGGERS 包含可访问数据库触发器的所有者及其类型和名称
ALL_USERS 存储有关用户的信息
ALL_CONSTRAINTS 描述当前用户可访问的表的约束定义
ALL_PROCEDURES 列出所有函数、过程以及关联的属性
dba_*
该视图存储了数据库中所有对象的信息。（前提是当前用户具有访问这些数据库的权限，一般来说必须具有管理员权限）
DBA_TABLES 包含数据库中所有用户的表。
DBA_CATALOG 包含数据库中定义的表、视图和同义词。
DBA_OBJECTS 包含所有用户的对象。
DBA_DATAFILES 包含有关数据文件的信息。
DBA_TABLESPACES 提供有关数据库中每个表空间的信息。
DBA_TAB_COLUMNS 提供有关表中每个列的详细信息。
DBA_SOURCE 显示存储函数、触发器和 Java 对象的源代码。
Oracle包含了一些潜在的由系统管理员如SYS维护的表和视图
，由于当数据库运行的时候它们会不断进行更新，所以称它们为动态数据字典
（或者是动态性能视图）。这些视图提供了关于内存和磁盘的运行情况，
所以我们只能对其进行只读访问而不能修改它们。
动态数据字典:是依赖数据库运行的性能的，反映数据库运行的一些内在信息,数据库运行的时候它们会不断进行更新，所以在访问这类数据字典时往往不是一成不变的
Oracle中这些动态性能视图都是以v$开头的视图.
例如： v$session:该视图列出当前会话的详细信息。
v$access:该视图显示数据库中锁定的数据库对象以及访问这些对象的会话对象（session对象）。
v$context：该视图列出当前会话的属性信息。比如命名空间、属性值等
v$instance
v$database
GV$和V$视图
从Oracle8开始，GV$视图开始被引入，其含义为Global V$.
除了一些特例以外，每个V$视图都有一个对应的GV$视图存在。
GV$视图的产生是为了满足OPS环境的需要，在OPS环境中，查询GV$视图返回所有实例信息，而每个V$视图基于GV$视图，增加了INST_ID列判断后建立，只包含当前连接实例信息。
注意，每个V$视图都包含类似语句：
where inst_id = USERENV('Instance')
desc user_objects
select *
from dictionary
where table_name='USER_OBJECTS';
select distinct object_type
from user_objects;
查询数据字典中和赋权相关的表或视图
select *
from dictionary
where lower(comments) like '%grant%'
查询当前用户下面所创建的所有约束的名字
SELECT constraint_name
FROM user_constraints;
查询s_emp表中的约束的相关信息
SELECT constraint_name, constraint_type,search_condition
FROM user_constraints
WHERE table_name = 'S_EMP';
查询s_emp表中的约束在哪里列上起作用
SELECT constraint_name, column_name
FROM user_cons_columns
WHERE table_name = 'S_EMP';
实际操作：
select table_name
from dictionary;
3778
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
