Oracle
Win+R sqlplus /nolog 确定 conn system/orcl 已连接 create user scott identified by tiger; 用户已创建 grant connect,resource to scott; 授权成功 conn scott/tiger 已连接 @拽表后回车
再@拽表再回车
...
1、Oracle创始人：拉里·埃里森
2、Oracle数据库，一个可以永久保存数据的仓库database，简称db
是一个关系型数据库（由多张关系表进行存储数据）
3、Oracle中使用的语言：
sql语言:结构化查询语言(Structured Query Language)
又称sql文,sql语句，sql文件的扩展名为.sql
4、Oracle默认的端口号：1521；MySql端口号：3306；SQLServle：1433
分类
DQL： 数据查询语言 查询（select）
DML： 数据操作语言 增 删 改（insert delete update）
TCL ：事务控制语言 commit rollback
DDL：数据定义语言 表的操作：新建表，修改表，删除表，主键，约束
DCL：数据控制语言 DBA某些操作
注意：SQL语言，不区分大小写，建议关键字大写，字段小写，以分号结尾，但不是必须的
数据库对象：数据表、视图、索引、序列...
数据表：
列（字段）
行（记录）：一行是一条记录（记录整条数据的个整信息）
列值（内容）
数据类型
Oracle当中有23种数据类型，常用的数据类型分为几种：
数字型：整数 number(m) m代表位数 number(11)
小数型 number(m,n) m代表总位数，n表示小数点后面几位 number(5,2) 999.99
字符型：可变字符 varchar2(m) m代表字符串的长度 varchar2(5)
固定字符 char(m) m代表字符串的长度 char(10)
日期型：date
面试题：
varchar2和char的区别：
varchar2：是一个可变长度的字符类型，规定了最大字符的空间大小，但实际用多少空间就占用多大空间，不浪 费空间，但是效率低
char：是一个固定长度的字符类型，规定了大小后，存入的字符比规定的长度小，他就会浪费空间，但效率高。
注释：--
设置字体大小步骤：
工具→首选项→用户界面→字体→（编辑器）选择→14→应用→确定
设置关键字大写步骤：
工具→首选项→用户界面→编辑器→找到关键词大小写→设置成大写
查询语句
简单查询
简单查询：没有任何条件的查询，查询所有，或者指定一个字段来查
1、简单查询：
SELECT *
FROM 表名;①select：查询 ②from：从哪个表查询数据 ③*：代表所有列
2、查询指定字段的数据
SELECT 字段1,字段2,字段3...
FROM 表名;3、有算术运算符表达式的查询
算术运算符：+、-、*、/
SELECT 字段1,字段2,字段3+字段4...
FROM 表名;注意：①字段可以用一个算术表达式表示
②任何非空字段与空字段相加操作，结果都为空
空值：null，无效的，未指定的，位置的或者不可预知的...
空值不是空格，不是0
4、别名使用
SELECT 字段1 AS "别名",字段2 AS "别名"
FROM 表名; ①AS可以省略 字段 "别名"
②""可以省略 字段 别名
③双引号的使用：名字中有特殊字符的时候，或 区分大小写的时候
5、|| 拼接
SELECT 字段 || '任意字符' || 字段2 别名
FROM 表名;6、DISTINCT去重 注：去重关键字必须放在开头
SELECT DISTINCT 字段,字段2...
FROM 表名;条件查询/过滤查询
1、条件查询
SELECT *
FROM 表名
WHERE 条件1 AND 条件2 OR 条件3 != 条件4 ①and：并且 ②or：或者 ③!=，<>：取反
④执行顺序：先where后的条件，再select的查询字段
2、is null：是空 is not null：非空
SELECT *
FROM emp
WHERE 字段 IS NULL / 字段 IS NOT NULL;注意：
not：非，不是 and：并且 or：或者
计算的优先级：not > and > or
3、比较运算：1）等值比较：= ①字段 = 数值 ②字段 = '字符串' ③字段 = to_date(日期,'yyyy-MM-dd')
2）不等值比较：> < <= >= != <>
4、between and：
SELECT *
FROM 表名
WHERE 字段 between 值1 AND 值2;①between and：在两者之间 包含边界值
②not between and：不在两者之间
5、in()：在xxx之内【等价于其中的任意一个】
SELECT *
FROM 表名
WHERE 字段 in(值1,值2);模糊查询
SELECT *
FROM 表名
WHERE 字段 like '%m%';开头：where 字段 like ‘字符%’
包含：where 字段 like ‘%字符%’
结尾：where 字段 like ‘%字符’
注意：_代表一个位置
-- 查询名字中带有下划线的员工信息
SELECT *
FROM emp
WHERE ename LIKE '%/_%' ESCAPE '/'; _需要转义，但是/没有转义的功能，必须通过escape关键字将/变为带有转义功能的斜线
排序查询
SELECT *
FROM 表名
ORDER BY 字段 DESC/ASC;执行顺序：先where -----> select -----> order by
order by语句，永远是最后执行
order by 排序（默认正序）
desc：降序
asc：升序
函数查询
一、函数
Oracle中的函数是用来处理和修饰数据的一个方法
只是在临时表中处理数据，不会改变原有的值
dual：虚表，用来显示数据的临时表
二、分类
单行函数，对一行记录进行操作，返回一个结果
多行函数：对多行记录进行操作，返回一个结果
总结：不管单行函数还是多行函数，最终结果就是一个
三、单行行数
数值函数 用来处理数值的
字符函数 用来处理字符的
日期函数 用来处理日期的
转换函数 数据类型相互转换
通用函数
3.1数值函数
round(小数)：四舍五入
round(小数，精确的位数)：精确到小数点第几位，并且四舍五入
round(小数，0)：舍弃小数位置，四舍五入
round(小数，负数)：小数点前几位，四舍五入，不进位取0填补
trunc(m)：截取函数，舍弃小数点不四舍五入
trunc(m,n)：n保留小数点后几位
mod(值1,值2)： 取余数
abs(值1) ：绝对值
ceil()：向上取整
floor()：向下取整
sqrt() ：开方
3.2字符函数
upper()：小写转大写
lower()：大写转小写
initcap()：首字母大写，其他字母变为小写
截取函数：
substr(字符，m)：从第m个位置截取到最后
substr(字符，m，n)：从第m个位置开始，n截取几个字符
substr(字符，-m，n)：-m倒数第几位，n截取几位（向后截取）
length(字符)：字符串长度
拼接函数：concat(值1，值2)
拼接函数嵌套格式：
SELECT concat(concat('我爱','中国'),'HTML')
FROM dual;包含函数：instr(字符1，字符串2)：判断字符2是否在字符1当中，在返回所在位置，不再返回0
lpad(字符，总长度，填充内容)：左填充 总长度 = 字符串长度+填充内容的长度
rpad(字符，总长度，填充内容)：右填充 总长度 = 字符串长度+填充内容的长度
replace(字符，旧字符串，新字符串)：替换函数
trim()：去掉两端空格
trim(字符 from 字符)：去掉两侧的单个字符
ltrim(字符，字符)：去掉左侧的单个字符
rtrim（字符，字符）：去掉右侧的单个字符
3.3日期函数
系统当前时间：sysdate
日期的相关计算：①日期-日期 = 天数 ②日期+数字 = 日期
日期函数：
months_between(日期1，日期2)：两个月份之间相隔了多个月
add_months：返回指定月份后的日期
last_day()：返回指定月份的最后一天
--【日期函数】
--系统当前时间
SELECT SYSDATE
FROM dual;
--日期的相关计算
-- 日期 - 日期 = 天数
--案例：查询员工入职了多少天
SELECT ename,floor(SYSDATE - hiredate)
FROM emp;
-- 案例：查询员工入职了多少年
SELECT ename,floor((SYSDATE - hiredate)/365)
FROM emp;
-- 日期 + 数字 = 日期
-- 查询3天后的日期
SELECT SYSDATE + 4
FROM dual;
SELECT SYSDATE - 4
FROM dual;
---------------------------------------------------------------------
--日期函数
--months_between(日期1，日期2)：两个月份之间相隔了多个个月
SELECT months_between(to_date('2021-12-17','yyyy-MM-dd'),to_date('2021-9-17','yyyy-MM-dd'))
FROM dual;
-- 查询员工入职了多少年
SELECT ename,round(months_between(SYSDATE,hiredate)/12,1)
FROM emp;
-- add_months():返回指定月份后的日期
--查询6个月之后的日期是哪一天
SELECT add_months(SYSDATE,6)
FROM dual;
--6个月之前
SELECT add_months(SYSDATE,-6)
FROM dual;
SELECT add_months(to_date('9','mm'),-6)
FROM dual;
SELECT * FROM emp;
--案例：查询1981-02-23日 四个月后入职的员工
SELECT *
FROM emp
WHERE hiredate> add_months(to_date('1981-02-23','yyyy-mm-dd'),4);
--last_day()：返回指定月份的最后一天
SELECT last_day(SYSDATE)
FROM dual;
SELECT last_day(to_date('1981-02-23','yyyy-MM-dd'))
FROM dual;
SELECT last_day(to_date('2','mm'))
FROM dual;
3.4转换函数
to_number(字符)：字符类型转为数字类型
to_char(数值/日期)：将数值与日期转字符串类型
to_char(123456789,'9999,9999,9999')
to_char(123456789,'0000,0000,0000')
to_char(123456789,'$9999,9999,9999')
to_char(123456789,'L9999,9999,9999')
①9：数字 ②0：零 ③$：美元 ④L：￥
to_char(日期,'yyyy-mm-dd')：把日期类型转为字符串
常用格式：①yyyy-mm-dd
②yyyy/mm/dd
③yyyy”年“mm"月"dd
to_date(字符串,模版)：字符串转日期格式
常用格式：①yyyy-mm-dd
②yyyy/mm/dd
隐式类型转换
SELECT *
FROM emp
WHERE sal > '2000'; --自动 类型转换 为数字to_number3.5通用函数
nvl(字段,值1)：如果字段不为空，返回的是该字段原来的值，如果字段为空，返回值1
nvl2（字段，值1，值2）：类似于三目运算符，如果当前字段不为空，返回值1，如果为空返回值2
decode（字段，值1，返回值1，值2，返回值2，值3，返回值3...）
类似与switch...case...default语句 匹配判断
case ...when ... then ...end :分支判断语句
--【通用函数】
--nvl(字段，值1)：如果字段不为空，返回的是该字段原来的值，如果字段为空，则返回值1
SELECT ename,empno,job,sal+nvl(comm,0)
FROM emp;
--nvl2(字段,值1,值2)：类似于三目运算符，如果当前字段不为空，返回值1，如果为空返回值2
SELECT ename,job,nvl2(comm,sal+comm,sal)
FROM emp;
--decode(字段,值1,返回值1,值2,返回值2,....)
--类似于 switch...case...default语句 匹配判断
SELECT ENAME,
JOB,
DECODE(JOB,
'SALESMAN',
'销售',
'MANAGER',
'经理',
'PRESIDENT',
'董事长',
'普通员工') 职位
FROM EMP;
-- case ... when ... then ... end :分支判断语句
--案例：判断工资的级别：大于3000：高工资， 1000~3000之间：一般工资 <1000：低工资
SELECT ename,sal,
CASE
WHEN sal>3000 THEN '高工资'
WHEN sal BETWEEN 1000 AND 3000 THEN '一般工资'
WHEN sal < 1000 THEN '低工资'
END 工资等级
FROM emp;四、多行函数
max()：获取最大值
min()：获取最小值
sum()：求和函数
avg()：平均值函数
count(*)：返回整张表的记录数
count(字段)：获取该字段有多少条记录，会自动过滤掉null值
注意：单行函数与多行函数不能一起写，除非分组
--【多行函数】
--多行函数：处理多行数据，返回一个结果
--max():获取最大值
--案例：查询工资最高的员工
SELECT MAX(sal)
FROM emp;
--min():获取最小值
SELECT MIN(sal)
FROM emp;
--sum():求和函数
--获取总工资
SELECT SUM(sal)
FROM emp;
--avg()：平均值函数
SELECT AVG(sal)
FROM emp;
--count():获取字段记录个数
SELECT COUNT(ename)
FROM emp;
SELECT COUNT(comm)
FROM emp;
SELECT * FROM emp;
--返回整张表的记录数
SELECT COUNT(*)
FROM emp;
--获取员工表中一共有多少个不同岗位
select COUNT(DISTINCT job)
FROM emp;
--注意：单行函数与多行函数不能一起写
SELECT lower(ename),MIN(sal)
FROM emp;--错误
五、分组函数
语法：
SELECT 多行函数，分组函数
FROM 表名
WHERE 条件
GROUP BY 分组字段
ORDER BY 排序注意：select关键字后只能写 多行函数和group by 后面的字段，不能使用单行函数
执行顺序：from - where - group by - select - order by
-- 案例1：查询每个部门的最高工资
SELECT deptno, MAX(sal) 最高工资
FROM emp
WHERE deptno IS NOT NULL
GROUP BY deptno
ORDER BY 最高工资 DESC;
--案例2：查询不同工作岗位的人数
--分析：先按照工作岗位进行分组，在分组之后的结果里，统计记录数
SELECT COUNT(*),job
FROM emp
GROUP BY job;
SELECT COUNT(ename),job
FROM emp
GROUP BY job;
--案例3：查询不同部门的不同工作岗位的人数
SELECT deptno,job,COUNT(*)
FROM emp
GROUP BY deptno,job
ORDER BY deptno;执行顺序：from - where - group by - having - select - order by
-- 案例4：查询每个部门的工作岗位大于1人的 部门和岗位都是什么，在按部门降序排序
--【错误】 错误：where后不能跟多行函数
SELECT deptno,job,COUNT(*)
FROM emp
WHERE COUNT(*) > 1 --分组的条件都是要在分组之后执行的
GROUP BY deptno,job
ORDER BY deptno DESC;
-- 执行顺序：from - where - group by - having - select - order by
--对
SELECT deptno,job,COUNT(*)
FROM emp
WHERE job IS NOT NULL
GROUP BY deptno,job
HAVING COUNT(*)>1 -- having 关键字主要是用来解决where后面不能放多行函数的
ORDER BY deptno DESC;当where和having条件相同时，where的执行效率会更高
--案例5 查询部门编号大于10的每个部门的每个岗位的人数
SELECT deptno,job,COUNT(*)
FROM emp
WHERE deptno > 10
GROUP BY deptno,job
--HAVING deptno > 10
ORDER BY deptno DESC;多表联查
一、多表联查
将多张表根据某种条件连接起来
二、分类
1.内连接： 笛卡尔积连接
等值连接
不等值连接
2.外连接：左连接
右连接
3.自连接：自己与自己连接
从语法分：SQL92 SQL99
三、内连接
SQL92
1.笛卡尔积连接（满连接）：没有任何条件将两张表直接连接到一起
语法：
select 别名.字段
from 表1 别名1,表2 别名2;
SELECT *
FROM emp,dept;笛卡尔积 = A表记录数 * B表记录数
问题：数据不准确
2.等值连接：在笛卡尔积的基础上提供了一个公共的连接条件
语法：
select 别名.字段
from 表1 别名1，表2 别名2
where 表1.公有字段 = 表2.公有字段;
SELECT *
FROM emp,dept
WHERE emp.deptno = dept.deptno;
--查询几个字段
--A:公有字段需要别名
SELECT ename,job,sal,emp.deptno,dname,loc
FROM emp,dept
WHERE emp.deptno = dept.deptno;
--给多个表起别名
SELECT e.ename,e.job,e.sal,d.deptno,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno = d.deptno AND sal > 2000;--除了写等值连接之外还可以写其他条件
3.不等值连接
原理：用A表里的一个字段，到B表里的一个范围做判断，满足范围内就查询出来
语法：
select 别名.字段
from 表1 别名1，表2 别名2
where 条件 1> < != 条件2
SELECT * FROM emp;--员工信息表
SELECT * FROM salgrade;--工资等级表
SELECT e.ename,e.job,e.sal,s.grade
FROM emp e,salgrade s
WHERE e.sal > s.losal AND e.sal < s.hisal
ORDER BY grade DESC;四、自连接
SQL92语法
原理：自己和自己连接
语法：
select 别名.字段
from 表1 别名1，表1 别名2
where 别名1.公共字段 = 别名2.公共字段;
--案例：查询员工的姓名，工作， 薪金，以及他的领导是谁
SELECT e.ename,e.job,e.sal,e2.ename
FROM emp e,emp e2
WHERE e.mgr = e2.empno;五、外连接
SQL92
左连接：将左表中不满足等值条件的数据显示出来，需要在右表上添加(+)
--案例：查询员工姓名，工作，薪金，部门号，部门名称以及没有部门的员工
SELECT e.ename,e.job,e.sal,e.deptno,d.dname
FROM emp e,dept d
WHERE e.deptno = d.deptno(+); 右连接：将右表中不满足等值条件的数据显示出来，需要在左表上添加(+)
--案例：查询员工姓名，工作，薪金，部门号，部门名称以及没有员工的部门
SELECT * FROM dept;
SELECT e.ename,e.job,e.sal,d.deptno,d.dname
FROM emp e,dept d
WHERE e.deptno(+) = d.deptno;SQL99
1.笛卡尔积连接（满连接）：使用关键字 CROSS JOIN
SELECT * FROM emp,dept;-- sql92
SELECT *
FROM emp CROSS JOIN dept; --sql992.等值连接
自动查找两个表中相同名称字段做连接，使用关键字NATURAL JOIN
SELECT *
FROM emp,dept
WHERE emp.deptno = dept.deptno; --sql92
SELECT *
FROM emp NATURAL JOIN dept;--sql99--等值连接2:使用关键字 inner join using(公共字段)
--自定义的指定公共字段
SELECT *
FROM emp INNER JOIN dept
USING(deptno); --sql993.不等值连接
万能写法：等值连接与不等值连接都可以用 inner join on
--等值连接
SELECT *
FROM emp e INNER JOIN dept d
ON e.deptno = d.deptno;--sql99
SELECT e.ename,d.dname
FROM emp e INNER JOIN dept d
ON e.deptno = d.deptno;--sql99
--不等值连接
SELECT * FROM salgrade;
SELECT *
FROM emp e,salgrade s
WHERE e.sal > s.losal AND e.sal < hisal;--sql92
SELECT *
FROM emp e INNER JOIN salgrade s
ON e.sal > s.losal AND e.sal < s.hisal;--sql994.外连接
左连接：left join using
语法：
select 别名.字段
from 表1 别名1 left join 表2 别名2
using(公共字段)；
--案例：查询员工姓名，职位，薪水，部门名称以及没有部门的员工
SELECT e.ename,e.job,e.sal,d.dname
FROM emp e LEFT JOIN dept d
USING(deptno);--等值连接的条件右连接：right join using
语法：
select 别名.字段
from 表1 别名1 right join 表2 别名2
using(公共字段)；
--案例：查询员工姓名，职位，薪水，部门名称以及没有员工的部门
SELECT e.ename,e.job,e.sal,d.dname
FROM emp e RIGHT JOIN dept d
USING(deptno);全连接（满连接）：把两个表不满足条件的数据全部筛选出来
语法：
select 别名.字段
from 表1 别名1 full join 表2 别名2
using(公共字段)；
SELECT *
FROM emp FULL JOIN dept
USING(deptno);三表连接
--把三张表信息连接到一起
--案例：想知道员工名称，员工的部门，部门所在的城市，城市是哪个州的
SELECT e.ename,e.job,e.sal,d.*,c.*
FROM emp e,dept d,city c
WHERE e.deptno = d.deptno AND d.loc = c.cloc;
SELECT e.ename,e.job,e.sal,d.dname,c.cloc
FROM emp e,dept d,city c
WHERE e.deptno = d.deptno AND d.loc = c.cloc;--sql92
SELECT e.ename,e.job,e.sal,d.*,c.*
FROM emp e,dept d,city c
WHERE e.deptno = d.deptno AND d.loc = c.cloc AND (e.sal >2000 AND e.sal < 3000);
--sql99改写
SELECT e.ename,e.job,e.sal,d.*,c.*
FROM emp e INNER JOIN dept d
ON e.deptno = d.deptno
INNER JOIN city c
ON d.loc = c.cloc 子查询
子查询：一个查询语句的结果作为另一个查询语句的条件或者一个子表来使用，叫子查询
作用：当筛选的条件不确定的时候使用子查询
语法：
SELECT * 或 字段1,字段2,..
FROM 表 或 （子查询）--这是一个结果集
WHERE 字段 > < != (子查询)-- 这是一个值执行顺序：先执行()里的子查询获得结果，在给主查询使用
注意：子查询必须写在小括号()里
多行子查询
< all ：小于小的
> all：大于大的
> any：与子查询的每一个值进行比较，只要大于一个子查询的值，就是满足条件的
< any：与子查询的每一个值进行比较，只要小于一个子查询的值，就是满足条件的
in：在范围内
--单行子查询：子查询的结果是一个单个值
--查询比BLAKE的工资高的员工信息
SELECT *
FROM emp
WHERE sal > (SELECT sal FROM emp WHERE ename = 'BLAKE');
--多行子查询
--查询其他部门比20号部门的任一工资低的员工信息
-- <any：与子查询的每一个值进行比较，只要小于一个子查询的值，就满足条件
-- >any: 与子查询的每一个值进行比较，只要大于一个子查询的值，就满足条件
SELECT *
FROM emp
WHERE sal < ANY(SELECT sal FROM emp WHERE deptno = 20) AND deptno <> 20;
-- 查询其他部门比10号部门的所有人工资都低的员工信息
-- all():比所有的都
-- <all ：小于小的
-- >all ：大于大的
SELECT *
FROM emp
WHERE deptno <> 10 AND sal < ALL(SELECT sal FROM emp WHERE deptno = 10);
--列出某些雇员的姓名和薪水，条件是他们的薪水等于部门10中任何一个雇员的薪水
--in:在范围内
SELECT ename,sal
FROM emp
WHERE sal IN(SELECT sal FROM emp WHERE deptno = 10) AND deptno <> 10;分页查询
1.作用：当一个表中数据量特别大的适合，需要进行分页显示提升用户的体验度
2.rownum(伪列) 关键字，作用：是oracle对外提供的自动给查询结果编号的一个关键字
语法：
select rownum r,e.* from emp e where rownum <= 数值注意：1.使用rownum字段，表需要添加别名
2.r别名不能直接用在where关键字后面
3.rownum字段会跟着排序规则位置发生变化
4.rownum列不能做大于或大于等于的操作
3.分页查询的公式
m：每页显示的数据多少条
n：页码
不考虑排序的分页公式
SELECT *
FROM (SELECT ROWNUM r,e.*
FROM emp e
WHERE ROWNUM <= n*m)
WHERE r > n*m-m;考虑排序的分页公式
SELECT *
FROM (SELECT ROWNUM r ,t.*
FROM (SELECT * FROM emp ORDER BY sal DESC) t
WHERE ROWNUM <= m*n)
WHERE r > m*n-m;--以职位分组，找出平均工资最高的两种职位
SELECT ROWNUM r, e.*
FROM (SELECT job,AVG(sal) s
FROM emp
WHERE job IS NOT NULL
GROUP BY job
ORDER BY s DESC) e
WHERE ROWNUM <= 2;DML：数据操作语言
用于数据的增删改
DML执行完成没有直接提示
DML执行完成时需要配合commit（提交）同时使用，将数据持久化
DML可以使用rollback（回滚）将没有提交的sql语句回滚，提交之后就不可以回滚了
insert插入
语法：
insert into 表名[字段，可以省略]
values(值，必须和字段一一对应);插入数据时，需要按照约束条件插入，一旦违反了约束会报出相应的错误
--复制一个dept表（复制的表没有主键）
CREATE TABLE deptNew
AS
SELECT * FROM dept;
--插入全部数据
--不指定插入哪些字段，就是全部字段，那位置需要匹配，数据类型也得匹配
INSERT INTO deptNew
VALUES(67,'游戏部','高新园区');
INSERT INTO deptNew(deptno,dname)
VALUES(68,'研发部');
--一次插入多个值
--注意：每句结束后必须有分号
INSERT INTO deptNew
VALUES(72,'行政部1','高新园区1');
INSERT INTO deptNew
VALUES(73,'行政部2','高新园区2');
INSERT INTO deptNew
VALUES(74,'行政部3','高新园区3');update 修改数据
语法：
update 表名
set 字段1 = 值1，字段2 = 值2，...
where 条件1 and 条件2...注意：把表中的数据想全部修改，不写where
--修改员工表中的员工工资为3000，King
SELECT * FROM emp;
UPDATE emp
SET sal = 3000
WHERE ename = 'KING';
--将所有人工资都涨1000
UPDATE emp SET sal = sal + 1000;
-- 1.将SMITH的工资设置为与20号部门最高的工资一样
UPDATE emp
SET sal = (SELECT MAX(sal) FROM emp WHERE deptno = 20)
WHERE ename = 'SMITH';
SELECT * FROM emp;
--2.将小王的职位设为与SMITH的一样
UPDATE emp
SET job = (SELECT job FROM emp WHERE ename = 'SMITH')
WHERE ename = '小王';delete删除数据
语法：
delete [from] 表名
[where 条件]注意：若省略where就是删除全部数据
-- 删除表中的数据，不是删除表
--案例1 删除表中的所有数据
DELETE FROM deptNew;
DELETE deptNew;
SELECT * FROM deptNew;
--案例2 ：删除指定条件的数据
delete FROM deptNew
WHERE deptno = 72;
--案例3：删除表
DROP TABLE deptNew;表操作
表：是oracle常见对象之一
oracle对象：表，索引，视图，序列，同义词...
表的命名：
①必须以字母开头，长度在30个字符之内
②可以包含的字符：字母，数字，下划线，$,井字符(#)
③两个单词之间一般使用下划线连接
④表名不可以在同一用户下重复
⑤表名不可以是关键字
⑥对大小写不敏感，不区分大小写
⑦若强制的区分大小写可以用""进行包裹
语法：
create table 表名(
列1 数据类型 [约束] [默认值]，
列2 数据类型 [约束] [默认值]，
列3 数据类型 [约束] [默认值]
);--案例：创建一个学生表(学号【主键】,姓名，年龄，入学时间，学费)
--主键必须要有
CREATE TABLE studentNew(
sno VARCHAR2(10) PRIMARY KEY,--设置为主键表示字段非空唯一的
sname VARCHAR2(20),
sage NUMBER(3),
sdate DATE,
smoney NUMBER(7,2)--22222.22
)
-- 查询新表数据
SELECT * FROM studentNew;
--插入两条数据
INSERT INTO studentNew
VALUES('1001','小王',20,to_date('2009-03-19','yyyy-mm-dd'),2000);--提交事务
--修改数据
UPDATE studentNew
SET smoney = 20000
WHERE sname = '小王';--提交事务
--删除数据
delete FROM studentNew WHERE sno = '1001';--提交事务
--输出表
DROP TABLE studentNew;修改表
添加列
alter table 表名
add（字段名 数据类型(m) [default 默认值]）；修改列
alter table 表名
modify（字段名 数据类型(m)[default 默认值]；修改列名
alter table
rename column 列名 to 新列名；删除列
alter table 表名
drop（字段名）--修改表
--1添加新字段（无论表中是否有数据都可以执行该操作）
ALTER TABLE studentNew
ADD(sgender VARCHAR2(3) DEFAULT '男');--他不能控制位置 ，只能在后面加
--2.修改字段类型 （无论表中是否有数据都可以执行该操作）
--已有数据的情况下长度不能小于当前数据的长度
ALTER TABLE studentNew
MODIFY(sname VARCHAR2(40));
--在已有数据的情况下类型不能乱该
ALTER TABLE studentNew
MODIFY(sname NUMBER(3));--出错
--3.修改字段名
ALTER TABLE studentNew
RENAME COLUMN sgender TO ssex;
--4.删除字段
ALTER TABLE studentNew
DROP(ssex);通过子查询建表
语法：
create table 表名
as
子查询；注意：子查询中若有表达式，必须使用别名，创建的表中的字段，就是别名
--通过子查询建表
CREATE TABLE emp_copy
AS
SELECT * FROM emp;
SELECT * FROM emp_copy2;
--子查询中有表达式，必须使用别名
CREATE TABLE emp_copy2
AS
SELECT empno,ename,job,sal*12 年薪,deptno FROM emp;使用小写字符进行定义表名
--使用小写字符进行定义表名
CREATE TABLE "users"(
u_id NUMBER(5),
"u_name" VARCHAR2(50)
)
--使用""进行严格区分大小写
SELECT * FROM "users";
SELECT u_id,"u_name" FROM "users";表的重命名
RENAME emp_copy2 TO emp_copy02;斩断表
清空表结构
truncate table 表名;
类似与delete from 表名;
不同点在于truncate table 表名； 它不可回滚（rollback）
--斩断表
TRUNCATE TABLE emp_copy02;--不建议使用关系表
表与表之间有一定的关联
主表（是被引用的表）
从表（引用主表的数据）
主键（不可重复，不为空，一张表中一般只有一个，主键所在的表是主表（相对的））
外键（必须为另一表的主键，并加以引用，外键所在的表是从表）
表与表的关联关系
一对一：一个表中的一条记录匹配另一个表的一条记录
一对多：一个表中的一条记录匹配另一个表的多条记录
多对多：一个表中的多条记录匹配另一个表的多条记录，往往是多个一对多组成
表的设计
遵从三大范式
第一范式（1NF）：任何列不可分割，每个列是表中的最小单元
第二范式（2NF）：在第一范式的基础上，任何列都必须依赖于主键的存在，一张表只能描述一个实体
第三范式（3NF）：在第二范式的基础上，要求每一列不得间接的依赖于主键m
TCL：事务控制语言
事务：又称工作单元，一条或多条sql组成
一组sql的特点是要么同时执行（commit），要么同时回滚（rollback）
程序：对数据进行某些操作，需要多条语句完成的，这些语句有先后顺序最后一条语句结束后,整个流程就叫事务
1.事务机制：
必须是一套完整的执行流程，如果一条语句没有成功执行，所有语句就都不会执行，数据不会被修改
2.作用：保证了数据的安全性
3.事务的声明周期：
事务的开启：第一条DML操作被执行的时候
事务的结束：提交commit 或 回滚rollback
4.事务语句
commit：提交
rollback：回滚
savepoint：保存点，回滚点
rollback to savepoint：回到回滚点
--TCL
-- 编写DML，在没有提交事务时，不是持久的，本事务内是默认提交的，但其他事务内部无法查看
--删除9999号员工
DELETE FROM emp WHERE empno = 9999;
--TCL 只能为DML执行一个，要么提交要么回滚
COMMIT;
ROLLBACK;
---------------------------------------------------------------
UPDATE emp SET sal = sal -9000 WHERE ename = '小张';
UPDATE emp SET sal = sal + 9000 WHERE ename = 'ALLEN';
ROLLBACK;
COMMIT;
--------------------------------------------------------------
SELECT * FROM emp;
INSERT INTO emp(empno,ename,job,sal) VALUES(500,'rose111','CLERK1',1700);
-- 保存点 类似于存档
SAVEPOINT a;
INSERT INTO emp(empno,ename,job,sal) VALUES(501,'rose222','CLERK2',1700);
INSERT INTO emp(empno,ename,job,sal) VALUES(502,'rose333','CLERK3',1700);
-- 回滚到保存点，前提是咱们没有提交，可以回归到任何一个点
ROLLBACK TO a;5.事务的例子
-Accout 银行转账 帐号 户名 余额 6662 冯楚涵 10000 9999 徐瑞晨 1000
1.冯楚涵 -9000 UPDATE Accout SET 余额 = 1000 WHERE 帐号 = '6662'; 2.徐瑞晨 +9000 UPDATE Accout SET 余额 = 9000 WHERE 帐号 = '9999';
6.事务的四大特性
原子性：事务是一个独立的工作单元，要么同时完成，要么同时回滚
一致性：一旦事务完成（commit，rollback），数据不可被破坏
隔离性：一个事务的开启不被其他事务所干扰
持久性：事务一旦被提交，则数据永久保存
约束
可以对字段进行限制，无效数据无法输入
| 约束类型 | 解释 | 缩写 |
|---|---|---|
| not null | 非空约束，添加该约束的字段不可以为空 | nn |
| unique | 唯一约束，添加该约束的字段不可以出现重复 | uq |
| primary key | 主键约束，添加该约束一般id/no等的字段，不可为空且不可重复 | pk |
| foreign key | 外键约束，必须依赖于主键存在，没有主键就没有外键（可重复，可为空） | fk |
| check | 检查约束，指定列必须满足指定条件 | ... |
约束同样是数据库对象之一，为表做服务的
约束名：①使用系统定义名称：sys_cn，n代表当前用户下第n个约束，不重复的
②自定义名称：表名_ 字段 _约束类型
约束添加的方式：①建表级约束 ②建表后追加约束
非空约束
语法：
create table 表名(
列名 类型(m) not null --系统命名
);create table 表名(
列名 类型(m) constraint 约束名 not null --自定义名称
);追加非空约束 若原有表中有空字段，则无法追加
alter table 表名
modify 字段 not null; 或 modify 字段 constraint 约束名 not null;
删除非空约束
alter table 表名
modify 字段 null;
--约束
--建表级约束
--1.非空约束
CREATE table a(
a_name VARCHAR2(50) NOT NULL--系统命名
);
--将空值加入到非空约束中不可以
INSERT INTO a(a_name) VALUES(NULL);
INSERT INTO a(a_name) VALUES('tom');
SELECT * FROM aa;
----------------------------------------------------------------------------------
CREATE TABLE aa(
a_name VARCHAR2(20) CONSTRAINT aa_a_name_nn NOT NULL-- 表名_字段名_约束类型
);
INSERT INTO aa(a_name) VALUES(NULL);
INSERT INTO aa(a_name) VALUES('tom');
------------------------------------------------------------------------------------
--建表后追加约束
--追加非空约束
create TABLE aaa(
a_name VARCHAR2(50)
);
INSERT INTO aaa(a_name) VALUES(NULL)
--追加
ALTER TABLE aaa
MODIFY a_name NOT NULL; --不行，若原有表中有空字段，就追加不了
DELETE FROM aaa;
--删除
ALTER TABLE aaa
MODIFY a_name NULL;唯一约束
语法：
在列级添加约束
create table 表名(
列名 数据类型(m) unique --系统命名
);
CREATE TABLE b(
b_name VARCHAR2(50) UNIQUE
);
INSERT INTO b(b_name) VALUES('tom');
INSERT INTO b(b_name) VALUES('tom');--添加重复数据，违反唯一约束条件在表级添加约束
create table 表名(
列名 数据类型(m),
constraint 约束名 unique(列名) --自定义名称
);
CREATE TABLE bb(
b_name VARCHAR2(50),
b_gender VARCHAR2(3),
CONSTRAINT bb_b_name_uq UNIQUE(b_name)
);
INSERT INTO bb(b_name) VALUES('tom');
INSERT INTO bb(b_name) VALUES('tom');--添加重复数据，违反唯一约束条件追加唯一约束
alter table 表名
add constraint 约束名 unique(列名)；
CREATE TABLE bbb(
b_name VARCHAR2(20)
);
--追加
ALTER TABLE bbb
ADD CONSTRAINT bbb_b_name_uq UNIQUE(b_name);
INSERT INTO bbb(b_name) VALUES('tom');
INSERT INTO bbb(b_name) VALUES('tom');--添加重复数据，违反唯一约束条件删除唯一约束
alter table 表名
drop constraint 约束名；
--删除
alter TABLE bbb
DROP CONSTRAINT bbb_b_name_uq;主键约束
语法：
在列级添加约束
create table 表名(
列名 类型(m) primary key --系统命名
);
CREATE TABLE c(
c_id NUMBER(7) PRIMARY KEY
);
INSERT INTO c(c_id) VALUES(1);
INSERT INTO c(c_id) VALUES(1);--主键约束无法加入相同的值
INSERT INTO c(c_id) VALUES(NULL);--主键约束无法加入空值在表级添加约束
create table 表名(
列名 类型(m),
constraint 约束名 primary key(列名) --自定义名称
);
CREATE TABLE cc(
c_id NUMBER(7),
c_name VARCHAR2(50),
CONSTRAINT cc_c_id_pk PRIMARY KEY(c_id)
);
INSERT INTO cc(c_id) VALUES(1);
INSERT INTO cc(c_id) VALUES(1);--主键约束无法加入相同的值
INSERT INTO cc(c_id) VALUES(NULL);--主键约束无法加入空值追加主键约束
alter table 表名
add constraint 约束名 primary key(列名);CREATE TABLE ccc(
c_id NUMBER(7)
)
ALTER TABLE ccc
ADD CONSTRAINT ccc_c_id_pk PRIMARY key(c_id);
INSERT INTO ccc(c_id) VALUES(1);
INSERT INTO ccc(c_id) VALUES(1);--主键约束无法加入相同的值
INSERT INTO ccc(c_id) VALUES(NULL);--主键约束无法加入空值删除主键约束
alter table 表名
drop constraint 约束名;
ALTER TABLE ccc
DROP CONSTRAINT ccc_c_id_pk;外键约束
外键必须依赖主键存在
在表级添加约束
语法：
create table 表名(
列名 类型(m),
constraint 约束名 foreign key(列名) references 主键表(主键字段)
);
主键表（父表）中没有的主键是无法使用的
可以向外键中插入空值
可以向外键中插入重复的值
--主键表
CREATE TABLE test(
t_id NUMBER(7) PRIMARY KEY,
t_name VARCHAR2(50)
);
--外键表
CREATE TABLE d(
d_id NUMBER(7) PRIMARY KEY,
d_name VARCHAR2(50),
t_id NUMBER(7),
CONSTRAINT d_t_id_fk FOREIGN KEY(t_id) REFERENCES test(t_id)
);
--向外键表中添加数据
--①
INSERT INTO d(d_id,d_name,t_id) VALUES(10001,'tom',1);
--主键表（父表）中没有的主键是无法使用的
--向主键表中添加数据
INSERT INTO test VALUES(1,'Java');
SELECT * FROM test;
SELECT * FROM d;
--②
INSERT INTO d(d_id,d_name,t_id) VALUES(10002,'jack',null);--可以向外键中插入空值
--③
INSERT INTO d(d_id,d_name,t_id) VALUES(10003,'rose',1);--可以向外键中插入重复的值追加外键约束
alter table 表名
add constraint 约束名 foreign key(列名) references 主键表(主键字段)
create TABLE dd(
d_id NUMBER(7) PRIMARY KEY,
d_name VARCHAR2(50),
t_id NUMBER(7)
);
--一张表的主键可以被多张表当外键用
ALTER TABLE dd
ADD CONSTRAINT dd_t_id_fk FOREIGN KEY(t_id) REFERENCES test(t_id);
INSERT INTO dd(d_id,d_name,t_id) VALUES(10001,'tom',1);
INSERT INTO dd(d_id,d_name,t_id) VALUES(10002,'jack',null);--可以向外键中插入空值
INSERT INTO dd(d_id,d_name,t_id) VALUES(10003,'rose',1);--可以向外键中插入重复的值删除外键约束
alter table 表名
drop constraint 约束名;
alter TABLE dd
DROP CONSTRAINT dd_t_id_fk;
INSERT INTO dd(d_id,d_name,t_id) VALUES(10004,'rose',2);级联操作
在主键列输出时，外键列的情况
①级联置空
on delete set null 在主键列删除时，外键列对应字段设置为空
②级联删除
on delete cascade 在主键列删除时，外键列对应字段同时被删除
--级联操作
--主键表
CREATE TABLE mydept(
d_id NUMBER(7) PRIMARY KEY,
d_name VARCHAR2(50)
);
INSERT INTO mydept VALUES(1,'技术部');
INSERT INTO mydept VALUES(2,'教务部');
INSERT INTO mydept VALUES(3,'就业部');
select* FROM mydept;
--外键表
CREATE TABLE myemp(
e_id NUMBER(7) PRIMARY KEY,
e_name VARCHAR2(50),
e_sal NUMBER(9,2),
d_id NUMBER(7),
CONSTRAINT myemp_d_id_fk FOREIGN key(d_id) REFERENCES mydept(d_id)
);
INSERT INTO myemp VALUES(1,'tom',10000,1);
INSERT INTO myemp VALUES(2,'jack',7500,2);
INSERT INTO myemp VALUES(3,'rose',8000,3);
SELECT * FROM myemp;
--删除1号部门
DELETE FROM mydept WHERE d_id = 1;--不可以直接删除已经被引用的主键
INSERT INTO mydept VALUES(4,'后勤部');
DELETE FROM mydept WHERE d_id = 4;--没有被应用的则可以删除
--级联置空 删除主键列，外键所在的列设置为空
DROP TABLE myemp;
--外键表
CREATE TABLE myemp(
e_id NUMBER(7) PRIMARY KEY,
e_name VARCHAR2(50),
e_sal NUMBER(9,2),
d_id NUMBER(7),
CONSTRAINT myemp_d_id_fk FOREIGN key(d_id) REFERENCES mydept(d_id) ON DELETE SET null
);
DELETE FROM mydept WHERE d_id = 1;
SELECT * FROM mydept;
SELECT * FROM myemp;
--级联删除 删除主键列，外键所在的列同样被删除
--外键表
CREATE TABLE myemp(
e_id NUMBER(7) PRIMARY KEY,
e_name VARCHAR2(50),
e_sal NUMBER(9,2),
d_id NUMBER(7),
CONSTRAINT myemp_d_id_fk FOREIGN key(d_id) REFERENCES mydept(d_id) ON DELETE cascade
);
--删除1号部门
DELETE FROM mydept WHERE d_id = 1;检查约束
在列级添加约束
语法：
create table 表名(
列名 类型(m) check(条件) --系统命名
)；
create TABLE e(
e_id NUMBER(7) PRIMARY KEY,
e_name VARCHAR2(50) NOT NULL,
a_age NUMBER(3) CHECK(a_age BETWEEN 0 AND 150)
--e_age number(3) check(e_age >=0 and e_age <= 150)
);
INSERT INTO e VALUES(1,'tom',200);--不行
INSERT INTO e VALUES(1,'tom',20);
SELECT * FROM e;在表级添加约束
create table 表名(
列名 类型(m),
constraint 约束名 check(列名 条件) --自定义名称
);
create TABLE ee(
e_id NUMBER(7) PRIMARY KEY,
e_name VARCHAR2(50) NOT NULL,
a_age NUMBER(3),
CONSTRAINT ee_a_age_ch CHECK(a_age BETWEEN 0 AND 150)
);
INSERT INTO ee VALUES(1,'tom',200);--不行
INSERT INTO ee VALUES(1,'tom',20);追加检查约束
alter table 表名
add constraint 约束名 check(列名 条件);
create TABLE eee(
e_id NUMBER(7) PRIMARY KEY,
e_name VARCHAR2(50) NOT NULL,
e_age NUMBER(3)
);
ALTER TABLE eee
ADD CONSTRAINT eee_e_age_ch CHECK(e_age BETWEEN 0 AND 150);
INSERT INTO eee VALUES(1,'tom',200);--不行
INSERT INTO eee VALUES(1,'tom',20);删除检查约束
alter table 表名
drop constraint 约束名；
alter TABLE eee
DROP CONSTRAINT eee_e_age_ch;
INSERT INTO eee VALUES(2,'tom',200);--可以
联合主键：比较少见：两个或两个以上的字段同时做主键
插入数据有何要求：不可为空，多个字段只要有一个字段不重复即可
CREATE TABLE f(
id1 NUMBER(7),
id2 NUMBER(7),
fname VARCHAR2(50),
CONSTRAINT f_id1_id2_pk PRIMARY KEY(id1,id2)
);
INSERT INTO f VALUES(NULL,1,'tom');--一个空值不行
INSERT INTO f VALUES(1,null,'tom');--一个空值不行
INSERT INTO f VALUES(NULL,null,'tom');--两个空值不行
INSERT INTO f VALUES(1,1,'tom');--可加入
INSERT INTO f VALUES(1,1,'tom');--重复的值不可以加入
INSERT INTO f VALUES(1,2,'tom');--有一个值不同可以加入
INSERT INTO f VALUES(2,2,'tom');
INSERT INTO f VALUES(3,3,'tom');物理删除和逻辑删除
逻辑删除：就是通过改变某个字段，从而不显示一些数据
物理删除：就是直接的将数据从数据库中删除
--逻辑删除和物理删除
CREATE TABLE h(
hid NUMBER(7) PRIMARY KEY,
hname VARCHAR2(8) NOT NULL,
hage NUMBER(3) CHECK(hage BETWEEN 0 AND 120),
status NUMBER(3) DEFAULT 0--表示数据的状态，0：正常状态，1：禁用状态
);
SELECT * FROM h;
INSERT INTO h VALUES(1,'tom',18,0);
INSERT INTO h VALUES(2,'jack',18,0);
INSERT INTO h VALUES(3,'rose',18,0);
INSERT INTO h VALUES(4,'lili',18,0);
--查询正常状态的数据
SELECT * FROM h WHERE status = 0;
--查询禁用状态的数据
SELECT * FROM h WHERE status = 1;
--数据tom非法，需要禁用
UPDATE h SET status = 1 WHERE hname = 'tom';-- 逻辑删除：通过字段将某些数据不可以见，就是一种修改
DELETE FROM h WHERE hname = 'tom';--物理删除：从物理地址上将其移除数据库对象：表，约束，序列，视图，索引，同义词
序列
可为表的主键做服务
序列是自增且不重复的字段
序列可以单独使用，不依赖于表
语法：
create sequence 序列名 创建序列，默认从1开始，每次自增量是1
start with n 开始值
increment by n 步长，每次自增的量
maxvalue 最大值
minvalue 最小值
使用：
序列.nextval：获取当前序列的返回值，且向下自增 自增到底是多少呢，取决于你的步长
序列.currval：获取当前序列值，而且必须在nextval使用之后才可用
特点：
为主键做服务
可以自动生成唯一的序列值
可以同时服务多张表，但不建议
建议一个序列只服务一张表
命名：服务的表名_seq
删除序列：drop sequence 序列名;
--序列
--创建序列 SEQUENCE 西困润子
CREATE SEQUENCE stu_seq
START WITH 10000;
--序列的使用
--序列可以单独使用，而不是直接依赖于表
--nextval:获取当前值并向下自增
SELECT stu_seq.nextval FROM dual;
--currval：获取当前值，必须在nextval之后执行
SELECT stu_seq.currval FROM dual;
--为主键做服务
create TABLE s(
sid NUMBER(7) PRIMARY KEY,
sname VARCHAR2(50),
sage NUMBER(3)
);
SELECT * FROM s;
INSERT INTO s VALUES(stu_seq.nextval,'tom',18);
INSERT INTO s VALUES(stu_seq.nextval,'jack',18);
-- 可以同时服务多张表，但建议每个序列只服务一张表
SELECT * FROM departments;
INSERT INTO departments(department_id,department_name) VALUES(stu_seq.nextval,'小卖部')
--删除序列
DROP SEQUENCE stu_seq;
SELECT emp_seq.nextval FROM dual;
视图
是一种虚表，源于表，是一种命名式查询
将查询简单化
优点：
可以给用户看到表的一部分，而不是全部，安全性高
将复杂的查询简单化
提供对数据的不同的显示
语法：
create view 视图名
as 子查询scott用户没有创建视图的权限
使用sysdba为scott授权
win+R → sqlplus /nolog → conn /as sysdba;(conn system/orcl) → grant create view to scott;
表的内部数据发生变化，视图会同步
删除视图：drop view 视图名
创建视图或覆盖视图：or replace
视图的操作和表操作是一样的
创建只读视图：
create or replace view 视图名称 as 子查询 with read only
索引
加快查询速度
减少磁盘的IO
相当于目录功能
可以手动创建
右oracle自行维护
语法：
create index 索引名
on 表名（字段）；
删除索引：drop index 索引名;
--索引：数据越多索引体现的功能越强
SELECT * FROM emp WHERE ename = 'SMITH';--0.031
--创建索引
CREATE INDEX ename_index
ON emp(ename);
SELECT * FROM emp WHERE ename = 'SMITH';--0.015
--删除索引
DROP INDEX ename_index;同义词
相当于给表起一个永久别名 synonym：喜呢逆木
语法：
create synonym 同义词名
for 表名
删除：drop synonym 同义词名
使用sysdba为scott授权
win+R → sqlplus /nolog → conn /as sysdba;(conn system/orcl) → grant create synonym to scott;
标签：--,数据库,INTO,VALUES,emp,Oracle,id,SELECT From： https://www.cnblogs.com/wan-jie/p/16651227.html