概念
oracle数据库体系组成
①oracle数据库
一个相关的操作系统文件(即存储在计算机硬盘上的文件)的集合，这些文件组织在一起，成为一个逻辑整体，即为oracle数据库
oracle用它来存储和管理相关的信息，oracle数据库必须与内存里实例结合，才能对外提供数据管理服务
②oracle实例
位于物理内存里的数据结构，由操作系统的多个后台进程和一个共享的内存池所组成，共享的内存池可以被所有进程访问
1 oracle用它们来管理数据库访问：用户如果要存取数据库(硬盘的文件)里的数据，必须通过oracle实例才能实现，不能直接读取硬盘上的文件
2 oracle实例就是平时所说的数据库服务(service)
oracle数据库和oracle实例的区别：
实例可以操作数据库，任何时候一个实例只能与一个数据库关联，访问一个数据库；而同一个数据库可由多个实例访问(RAC)
数据库操作
首先准备了三个脚本文件，做一些建表操作，准备数据（数据来源于尚硅谷bilibili素材）
链接：https://pan.baidu.com/s/1A3VsXZsF5vi47ibIjsrG3A
提取码：jqjt
打开plsql界面，这里登录的是Scott用户
在命令行输入一下命令
@sql脚本全路径;
例如：
@C:\my_files\尚硅谷\[尚硅谷]_宋红康_oracle_sql_plsql课件_章节练习_资料\2.数据表文件\01_del_data.sql;
@C:\my_files\尚硅谷\[尚硅谷]_宋红康_oracle_sql_plsql课件_章节练习_资料\2.数据表文件\02_hr_cre.sql;
@C:\my_files\尚硅谷\[尚硅谷]_宋红康_oracle_sql_plsql课件_章节练习_资料\2.数据表文件\03_hr_popul.sql;
查看创建表数据
select * from empolyees;
===>脚本执行成功有数据了
基础命令练习
创建用户
-- 默认在users表空间下创建用户,用户名为zhangsan ,密码为zhangsan,该用户在users表空间下创建
create user zhangsan identified by zhangsan default tablespace users;
修改用户密码
-- 将用户zhangsan的密码修改为12345678
alter user zhangsan identified by 12345678；
删除用户
-- 当一个用户创建了表之后，在想删除这个用户必须加上cascade关键字级联删除，否则会报错
drop user username[cascade];
-- 如果该用户还没有创建任何的表，那么可以使用下面的命令删除用户
drop user username;
创建角色与授权
-- 创建角色
create role roleName(比如 designer) identified by 123456;
-- 为角色赋予权限
grant create view,create table,create session to designer;
-- 角色代表一组权限的集合，可以将一组该角色代表的权限赋予用户
grant designer to zhangsan;
--查询一个角色具有的权限
SQL> select * from role_sys_privs where role='DESIGNER';
ROLE PRIVILEGE ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
DESIGNER CREATE SESSION NO
DESIGNER CREATE TABLE NO
DESIGNER CREATE VIEW NO
查看使用的oracle的版本
-- 登录oracle用户 这里使用的是scott用户
-- 在sql plus或者plsql界面执行下面的命令就可以查看当前oracle版本
select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
-- 可以看到这里使用的是oracle11g
plsql中编写sql方式
①在外面的编辑器直接写完然后粘贴到plsql界面执行
②在plsql界面输入ed回车，会弹出编辑sql的界面，然后再里面编写sql，编写好后，点击ok,在界面输入 / 斜线即可执行sql
oracle 11g的企业管理器已经从之前的c/s架构变成b/s架构了
对于企业管理器
1 访问oracle企业管理器需要有SELECT_CATALOG_ROLE角色的权限。
2 如果你新创建了一个用户，需要执行grant SELECT_CATALOG_ROLE to user_name;
才能使该用户访问企业管理器。但是不需要你单独的执行这个语句，通常dba角色也包含了这些权限。
查看表结构
SQL> desc scott.employees;
Name Type Nullable Default Comments
-------------- ------------ -------- ------- --------
EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(20) Y
LAST_NAME VARCHAR2(25)
EMAIL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20) Y
HIRE_DATE DATE
JOB_ID VARCHAR2(10)
SALARY NUMBER(8,2) Y
COMMISSION_PCT NUMBER(2,2) Y
MANAGER_ID NUMBER(6) Y
DEPARTMENT_ID NUMBER(4) Y
oracle中sql语句不区分大小写
oracle中数值和日期(number,date)可以进行算数运算，前者可以±*/等，后者只能进行±
-- 数值的加减乘除
SQL> select first_name,last_name,salary + 100 ,salary * 12 from employees;
FIRST_NAME LAST_NAME SALARY+100 SALARY*12
-------------------- ------------------------- ---------- ----------
Steven King 24100 288000
Neena Kochhar 17100 204000
Lex De Haan 17100 204000
Alexander Hunold 9100 108000
Bruce Ernst 6100 72000
David Austin 4900 57600
Valli Pataballa 4900 57600
-- 日期的加减
SQL> select sysdate,sysdate+1,sysdate-3
2 from dual;
SYSDATE SYSDATE+1 SYSDATE-3
----------- ----------- -----------
2021/2/21 2 2021/2/22 2 2021/2/18 2
凡是空值参与的运算，结果都为空 即 null
oracle字段取别名 一共有三种方式
-- 方式①
字段后空格直接+别名 这种方式在结果表中返回的字段名为全部大写
-- 方式②
字段后 as + 别名 这种方式在结果表中返回的字段名为全部大写
-- 方式③
别名加上双引号，这种方式你的别名是什么样，字段别名就是什么样(适合字段别名有大小写或者全部小写或者字段别名有多个部分组成中间没有_连接的)
SQL> select email em ,email as emm,email as "emi",email as "Emi"
2 from employees;
EM EMM emi Emi
------------------------- ------------------------- ------------------------- -------------------------
ABANDA ABANDA ABANDA ABANDA
ABULL ABULL ABULL ABULL
ACABRIO ACABRIO ACABRIO ACABRIO
AERRAZUR AERRAZUR AERRAZUR AERRAZUR
连接符(字段拼接成一个字段) 拼接符为 ||
SQL> select 'my name is ' || first_name || last_name as detail
2 from employees;
DETAIL
--------------------------------------------------------
my name is StevenKing
my name is NeenaKochhar
my name is LexDe Haan
my name is AlexanderHunold
-- 注意 日期和字符只能在单引号中出现，如果是双引号会报错,双引号只用在别名上
SQL> select "my name is " || first_name || last_name as detail
2 from employees;
select "my name is " || first_name || last_name as detail
from employees
ORA-00904: "my name is ": 标识符无效
sql和sql plus的区别
| sql | sql plus |
|---|---|
| 一种语言 | 一种环境 |
| ANSI标准 | oracle的特性之一 |
| 关键字不能缩写 describe , edit | 关键字可以缩写 desc , ed |
| 使用语句控制数据库中的表的定义信息和表中的数据 | 命令不能改变数据库中的数据的值 |
| 击中运行 |
比较运算符
| 操作符 | 含义 |
|---|---|
| = | 等于 |
| > | 大于 |
| >= | 大于等于 |
| < | 小于 |
| <= | 小于等于 |
| <> | 不等于(也可以使用 != ) |
| between … and … | 在两个值之间(包含边界) |
| in(set) | 等于列表中的一个 |
| like | 模糊查询 匹配单个字符 使用 _ ； 匹配零个，一个或多个 使用 % |
| is null , is not null | 是空值，不是空值 |
like 模糊查询的特殊情况
-- 比如查询姓名中带 _ 的人物信息，因为在sql的模糊查询中 _ 是有特殊含义的
-- 两边的 \ 可以使用其他的字符替代
SQL> select first_name
2 from employees
3 where first_name like '%\_%' escape '\';
FIRST_NAME
--------------------
Su_san
escape的用法
使得放在escape中的字符后的具有特殊含义的字符变成普通字符
例如上面的 first_name like '%\_%' escape '\' ⇒ 使得 _ 不再表示任意的一个字符 而是表示 _ 本身
oracle中的函数
总体分为单行函数和多行函数
单行函数 ==>
字符
①大小写空值函数
lower
upper
initcap
SQL> select lower('HELLO'),upper('hello'),initcap('hello'),initcap('HELLO')
2 from dual;
LOWER('HELLO') UPPER('HELLO') INITCAP('HELLO') INITCAP('HELLO')
-------------- -------------- ---------------- ----------------
hello HELLO Hello Hello
②字符控制函数
concat(a,b) 拼接
substr(a,1,5) 截取
length(a) 字符串长度
instr(a,b) 返回b在a中第一次出现的位置索引 从1开始 如果不存在返回0
lpad(a,n,b) | rpad(a,n,b) 位数不足时使用指定的字符补齐指定至符合指定位数
trim(a from b) 去除首位的指定字符 ，不指定字符时默认为空格
replace(a,b,c) 将a中的所有b换成c
1 SQL> select concat('hello','world'),substr('helloworld',3,3),length('helloworld')
2 from dual;
CONCAT('HELLO','WORLD') SUBSTR('HELLOWORLD',3,3) LENGTH('HELLOWORLD')
----------------------- ------------------------ --------------------
helloworld llo 10
2 SQL> select instr('hellowworld','w')
2 from dual;
INSTR('HELLOWWORLD','W')
------------------------
6
3 SQL> select lpad('abc',10,'#'),rpad('wygd',8,'*')
2 from dual;
LPAD('ABC',10,'#') RPAD('WYGD',8,'*')
------------------ ------------------
#######abc wygd****
4 SQL> select trim('h' from 'helloworldhh'),trim(' dhdjhgj ')
2 from dual;
TRIM('H'FROM'HELLOWORLDHH') TRIM('DHDJHGJ')
--------------------------- ---------------
elloworld dhdjhgj
5 SQL> select replace('helloworld','o','mm')
2 from dual;
REPLACE('HELLOWORLD','O','MM')
------------------------------
hellmmwmmrld
数值函数
round(数值,n) 四舍五入(默认保留至整数，n为正数时表示保留的小数的位数，n为负数时表示四舍五入整数的水平)
trunc(数值，n) 截断 不指定阶段位数默认舍弃小数，当n为正数时，舍弃指定小数位数之后的所有小数，为负数时，舍弃小数部分并舍弃整数指定部分并且用0填充
mod 求余数
1 SQL> select round(123.34,2),round(123.34),round(123.34, -2)
2 from dual;
ROUND(123.34,2) ROUND(123.34) ROUND(123.34,-2)
--------------- ------------- ----------------
123.34 123 100
2 SQL> select trunc(123.35),trunc(123.35,1),trunc(123.35,-2)
2 from dual;
TRUNC(123.35) TRUNC(123.35,1) TRUNC(123.35,-2)
------------- --------------- ----------------
123 123.3 100
3 SQL> select mod(1000,33) from dual;
MOD(1000,33)
------------
10
日期
日期上加上或减去一个数字结果仍为日期
两个日期相减返回日期之间相差的天数。日期不允许做加法运算
可以用数字除24来向日期中加上或减去天数
months_between(x1,x2) x为DATE类型的数据 返回两个日期相差的月份 为浮点数或整数
add_months(x,n) n为整数
next_day(x,des) 加入des为’星期日’ 那么返回该日期x的下一个星期日的日期
last_day(x) 返回x日期所在月的最后一天
round(x,des) des可以为’year’,‘month’,‘day’,‘mm’,‘hh’等 超过一般就进一
trunc(x,des) des可以为’year’,‘month’,‘day’,‘mm’,'hh’等 意为阶段到当前单位
SQL> select months_between(to_date('2021-02-24','yyyy-MM-dd'),to_date('2019-10-30','yyyy-MM-dd')) from dual;
MONTHS_BETWEEN(TO_DATE('2021-02-24','YYYY-MM-DD'),TO_DATE('2019-10-30','YYYY-MM-dd'))
--------------------------------------------------------------------------------
15.8064516129032
-- 在原有基础上加一个月
SQL> select add_months(sysdate,1) from dual;
ADD_MONTHS(SYSDATE,1)
---------------------
2021/3/24 22:52:21
-- 返回指定日期的下一个星期三的日期
SQL> select next_day(to_date('2021-02-24','yyyy-MM-dd'),'星期三')from dual;
NEXT_DAY(TO_DATE('2021-02-24','YYYY-MM-DD'),'星期三')
--------------------------------------------------
2021/3/3
-- 可以看到round中日期超过15，也就是大于等于16才会进一个月，变成下个月的1日
SQL> select round(to_date('2021-02-15','yyyy-MM-dd'),'month') from dual;
ROUND(TO_DATE('2021-02-15','YYYY-MM-DD'),'MONTH')
-------------------------------------------------
2021/2/1
SQL> select round(to_date('2021-02-16','yyyy-MM-dd'),'month') from dual;
ROUND(TO_DATE('2021-02-16','YYYY-MM-DD'),'MONTH')
-------------------------------------------------
2021/3/1
-- 四舍五入到年的时候 月份小于等于6就舍弃，>=7就增加一年
SQL> select round(to_date('2021-07-01','yyyy-MM-dd'),'year') from dual;
ROUND(TO_DATE('2021-07-01','YYYY-MM-DD'),'YEAR')
------------------------------------------------
2022/1/1
SQL> select round(to_date('2021-06-30','yyyy-MM-dd'),'year') from dual;
ROUND(TO_DATE('2021-06-30','YYYY-MM-DD'),'YEAR')
------------------------------------------------
2021/1/1
-- 阶段日期
SQL> select trunc(to_date('2021-02-24','yyyy-MM-dd'),'year') from dual;
TRUNC(TO_DATE('2021-02-24','YYYY-MM-DD'),'YEAR')
------------------------------------------------
2021/1/1
SQL> select trunc(to_date('2021-02-24','yyyy-MM-dd'),'month') from dual;
TRUNC(TO_DATE('2021-02-24','YYYY-MM-DD'),'MONTH')
-------------------------------------------------
2021/2/1
转换函数
隐式转换(oracle自动完成的转换)(有时间拓展一下)
date 两者可相互转换 char,varchar2 两者可相互转换 number
-- 字符串+数字 = 数字
SQL> select '2'+3 from dual;
'2'+3
----------
5
显示转换
to_char
①to_char对数字的转换中经常使用的格式
| 9 | 数字 表示该位数字有就显示，没有就不显示 |
|---|---|
| 0 | 零表示该位数字有就显示数字，没有就显示0 |
| $ | 美元符 |
| L | 本地货币符号 在国内就是rmb |
| . | 小数点 |
| , | 千位符 |
SQL> select to_char(12312312323,'$999,999,999,99')from dual;
TO_CHAR(12312312323,'$999,999,999,99')
--------------------------------------
$123,123,123,23
to_number
-- 美元
SQL> select to_number('$123,123,123,23','$999,999,999,99')from dual;
-- 本地货币
TO_NUMBER('$123,123,123,23','$999,999,999,99')
----------------------------------------------
12312312323
SQL> select to_number('￥123,123,123,23','L999,999,999,99')from dual;
TO_NUMBER('￥123,123,123,23','L999,999,999,99')
----------------------------------------------
12312312323
to_date
-- 当格式中含有中文的时候需要使用双引号引起来
SQL> select to_char(hire_date,'yyyy"年"mm"月"dd"日"') as dates from employees;
DATES
--------------
1987年06月17日
1989年09月21日
1993年01月13日
SQL> select to_date('2021/01/01','yyyy/mm/dd') from dual;
TO_DATE('2021/01/01','YYYY/MM/DD')
----------------------------------
2021/1/1
通用函数 适用于任何数据类型，同时也适用于空值
nvl(expr1,expr2) 当expr1为空时返回expr2,否则返回expr1
nvl2(expr1,expr2,expr3) 当expr1不为空时返回expr2,为空时返回expr3
nullif(expr1,expr2) 若expr1=expr2 那么返回null,否则返回expr1
coalesce(expr1,expr2,…,exprn) nvl的升级版，如果expr1为空返回expr2,如果expr再为空，返回expr3…
-- nvl函数计算年薪(存在奖金为空的情况)，不做判断则有null参与的运算结果均为null
SQL>
SQL> select first_name || last_name as name,salary*12*(1+nvl(COMMISSION_PCT,0)) as annul_salary from employees;
NAME ANNUL_SALARY
--------------------------------------------- ------------
StevenKing 288000
-- nvl函数将没有部门id的显示为没有部门，但是要注意department_id字段为数值，需保证nvl中的内容可以进行隐式转换或者显示转换为同一种数据类型
SQL> select first_name,nvl(to_char(department_id),'没有部门') from employees;
FIRST_NAME NVL(TO_CHAR(DEPARTMENT_ID),'没有部门')
-------------------- ----------------------------------------
Steven 90
Neena 90
Lex 90
-- nvl2 查询员工的奖金率，为空返回0.01,不为空返回原来的奖金率+0.015
SQL> select employee_id,COMMISSION_PCT,nvl2(COMMISSION_PCT,COMMISSION_PCT+0.015,0.01) from employees;
EMPLOYEE_ID COMMISSION_PCT NVL2(COMMISSION_PCT,COMMISSION_PCT+0.015,0.01)
----------- -------------- ----------------------------------------------
144 0.01
145 0.40 0.415
146 0.30 0.315
-- 使用nullif函数
SQL> select first_name,last_name,nullif(length(first_name),length(last_name)) from employees;
FIRST_NAME LAST_NAME NULLIF(LENGTH(FIRST_NAME),LENGTH(LAST_NAME))
-------------------- ------------------------- --------------------------------------------
Steven King 6
Neena Kochhar 5
Lex De Haan 3
Alexander Hunold 9
Bruce Ernst
多行函数
条件表达式
在sql语句中使用IF-THEN_ELSE逻辑
有两种方式：
①case表达式 case 字段 when 值1 then x1 when 值2 then x2 else x3 end
②decode函数 decode(字段,值1,x1,值2,x2,x3) 当字段为值1时返回x1,当为值2时返回x2,否则返回x3
-- case表达式
-- 查询部门id为10，20，30的员工信息，如果部门id为10，输出薪资的1.1倍，如果为20，输出1.2倍，否则输出1.3倍
select employee_id,
department_id,
salary,
case department_id when 10 then salary*1.1
when 20 then salary*1.2
else salary*1.3 end as new_salary
from employees
where department_id in (10,20,30)
EMPLOYEE_ID DEPARTMENT_ID SALARY NEW_SALARY
----------- ------------- ---------- ----------
114 30 11000.00 14300
115 30 3100.00 4030
116 30 2900.00 3770
117 30 2800.00 3640
118 30 2600.00 3380
119 30 2500.00 3250
200 10 4400.00 4840
201 20 13000.00 15600
202 20 6000.00 7200
-- decode函数 再次实现上面的功能
select employee_id,
department_id,
salary,
decode(department_id,10,salary*1.1,20,salary*1.2,salary*1.3) as new_salary,
case department_id when 10 then salary*1.1
when 20 then salary*1.2
else salary*1.3 end as old_salary
from employees
where department_id in (10,20,30)
EMPLOYEE_ID DEPARTMENT_ID SALARY NEW_SALARY OLD_SALARY
----------- ------------- ---------- ---------- ----------
114 30 11000.00 14300 14300
115 30 3100.00 4030 4030
116 30 2900.00 3770 3770
117 30 2800.00 3640 3640
118 30 2600.00 3380 3380
119 30 2500.00 3250 3250
200 10 4400.00 4840 4840
201 20 13000.00 15600 15600
202 20 6000.00 7200 7200
多表(n个)连接查询 至少需要 n-1个连接条件
组函数
avg 返回平均值，数据类型只能是数值
count 返回表中记录总数，适用于任何数据类型
count(expr) 返回expr不为空的记录总数
count(distinct expr) 返回expr非空并且不重复的记录的总数
max 返回最大值，数据类型没有限制
min 返回最小值，数据类型没有限制
stddev 求方差
sum 返回和，数据类型只能是数值型
子查询：
①单行子查询 单行子查询使用单行比较操作符
= , > , >= , < , <= , <>
②多行子查询 多行子查询使用多行比较操作符
| 操作符 | 含义 |
|---|---|
| in | 等于列表中的任意一个 |
| any | 和子查询返回的某一个值比较 |
| all | 和子查询返回的所有值比较 |
any和all举例
-- 返回其他工种的员工中比job_id为‘IT_PROG’的任一工资低的员工的工号，姓名，job_id,salary
select employee_id,last_name,job_id,salary
from employees
where job_id <> 'IT_PROG'
and salary < any(
select salary
from employees
where job_id = 'IT_PROG'
)
-- 返回其他工种的员工中比job_id为‘IT_PROG’的所有员工工资低的员工的工号，姓名，job_id,salary
select employee_id,last_name,job_id,salary
from employees
where job_id <> 'IT_PROG'
and salary < all(
select salary
from employees
where job_id = 'IT_PROG'
)
查询平均工资最低的部门信息
select * -- 平均工资最低的部门信息
from departments
where department_id = (
select department_id -- 查询除部门平均工资最低的部门
from employees
group by department_id
having avg(salary)=(
select min(avg(salary)) -- 查询部门中最低的平均工资
from employees
group by department_id
)
)
查询平均工资最低的部门信息和该部门的平均工资
select d.*,(select avg(salary) from employees where department_id=d.department_id)
from departments d
where department_id = (
select department_id
from employees
group by department_id
having avg(salary)=(
select min(avg(salary))
from employees
group by department_id
)
)
查询job平均工资最高的job信息
select *
from jobs
where job_id in (
select job_id
from employees
group by job_id
having avg(salary)=(
select max(avg(salary))
from employees
group by job_id
)
)
常见的数据库对象
| 数据库对象 | 描述 |
|---|---|
| 表 | 基本的数据存储集合，由行和列组成 |
| 视图 | 从表中抽出的逻辑上相关的数据集合 |
| 序列 | 提供有规律的数值，通常声明为主键的这一列使用序列实现 |
| 索引 | 提高查询的效率 |
| 同义词 | 给对象起别名，与表的别名不同，一旦定义可以永久使用 |
查询当前用户创建了的所有表的信息
select * from user_tables;
-- 包含字段 table_name,TABLESPACE_NAME,CLUSTER_NAME,IOT_NAME,PARTITIONED等
查询当前用户创建的所有表的表名
方式① select table_name from user_tables;
方式② 在plsql中选择myobjects下有tables可以查看
oracle数据库中的表
①用户定义的表
②数据字典 由oracle server自动创建的一组表，包含数据库信息
查看当前用户下有哪些数据库对象类型
select distinct object_type from user_bjects;
==>
OBJECT_TYPE
-------------------
SEQUENCE
INDEX
TABLE
查看用户定义的表，视图，同义词和序列
select * from user_catalog;
表名和列名的命名规则
①必须以字母开头
②必须在1-30个字符之间
③必须只能包含字母大小写，数字，_ , $,#
④不能重名
⑤不能是oracle的保留字
创建表的两种方式
①白手起家型
create table temp(
id number(10),
name varchar2(20)
);
②依托现在表格创建
-- 通过子查询将创建表和插入数据结合起来
create table emp2
as
select * from employees;
--
-- 只创建表结构，不带数据
create table emp2
as
select * from employees
where 1=2;
DDL操作会自动提交事务，不能回滚，回滚也无效
alter table语句
对默认值的修改只影响后续插入的数据
①增加字段 表中有数据的前提下增加字段不受影响，可以执行成功
alter table tableName add (columnName1 columnType1,columnName2 columnType2);
②修改字段 表中待修改的字段有数据的前提下，修改字段类型前必须将数据清空，否则会修改字段数据类型失败
alter table tableName modify(column1 columnType1,column2 columnType2);
③删除字段 表中有数据的前提下，删除字段操作不受影响，可以执行成功
alter table tableName drop column cloumnName;
④重命名字段 表中待重命名字段有数据的前提下，重命名操作不受影响，可以执行成功
alter table tableName rename column columnName1 to columnName2;
删除表
①数据和结构都被删除
②所有正在运行的相关事务被提交
③所有相关索引被删除
④drop table 语句不能回滚
drop table tableName;
清空表
①删除表中所有数据
②释放表的存储空间
③truncate语句不能回滚
④可以使用delete语句删除数据后，可以回滚
truncate table tableName; 不可回滚
改变对象的名称
执行rename语句改变表，视图，序列，或同义词的名称
必须是对象的拥有者
rename tableName1 to tableName2;
设置列不可用
alter table tableName set unused column columnName;
删除不可用的列
alter table tableName drop unused columns;
--不可以直接使用alter table tableName drop column columnName;来删除被设置为不可用的列，会报错
以上这些DDL的命令操作之后皆不可回滚
事务是由完成若干项工作的DML语句组成
DML语句
插入数据
①每次只能插入一行记录 字符和日期型数据应该包含在单引号中 插入的时候有非空约束的字段是一定要添加数值的，否则会报错
insert into tableName [(column1,column2,column3...)] values(value1,value2,value3...);
②从其他表拷贝数据
insert into tableName
select * from tableName2;
③创建脚本的方式输入数值、
在sql中使用 & 变量指定列值
& 变量放在values子句中
insert into tableName(column1,column2,column3)
values(&columnName1,'&columnName2',columnName3);
确定后就会逐个弹出字段的输入框，将输入的值作为该字段的值，number类型的不用加引号，其他的要加单引号
修改值
-- 如果修改的时候不限制条件就会将该字段的所有记录的值都进行修改
-- 没有提交就可以回滚
update tableName set columnName = value1
where condition;
-- 例子 更新114号员工的工作和工资使其与205号员工保持一致
update emp2
set job_id=(
select job_id
from employees
where employee_id='205'
),
salary=(
select salary
from employees
where employee_id='205'
)
where employee_id='114'
delete语句
①
delete from tableName
where ... （加上where删除指定的数据，不加where删除表中的所有数据）
②在delete中使用子查询，使删除基于另一个表的数据
delete from tableName
where columnName = (
子查询
)
例子
delete from departments
where department_id=60;
可能会引发错误:记录中包含被其他表用作外键的主键
delete操作是可以回滚的，删除数据后可以通过rollback恢复
数据类型
| 数据类型 | 描述 |
|---|---|
| varchar2(size) | 可变长字符数据 |
| char(size) | 定长字符数据 |
| number(p,s) | 可变长数值数据 |
| date | 日期型数据 |
| long | 可变长字符数据，最长肯达2G |
| clob | 字符数据最大可达到4G |
| raw(long raw) | 原始的二进制数据 |
| blob | 二进制数据，最大数可达4G |
| bfile | 存储外部文件的二进制数据，最大可达4G |
| rowid | 行地址 |
数据库事务
事务：一组逻辑操作单元，使数据从一种状态变换到另一种状态
数据库事务由以下的部分组成：
①一个或多个DML语句(数据操纵语言-data manipulation language)
②一个DDL语句(数据定义语言- data definition language)
③一个DCL语句(数据控制语言-data control language)
数据库事务的开始
数据库事务的结束
以下面的其中之一作为结束“
①commit或rollback
②DDL语句(自动提交)
③用户会话正常结束(亲测，执行DML语句没有提交，关闭会话窗口，在重新打开plsql,回滚无效，说明关闭窗口前，之前执行的sql会自动提交)
④系统异常终止
rollback回滚到上一次commit后的状态
rollback to savepoint A 回滚到保存点A,此时使用rollback仍然可以回滚到上一次commit后的状态
设置保存点
savepoint pointName;
回滚到指定保存点
rollback to savepoint pointName;
经过测试不管之前建立了多少个保存点，只要rollback或rollback to savepoint pointName一次，之前的保存点就会失效
用户A对当前表进行增删改操作时，没有提交事务，用户B是看不到用户A对该表所作的修改的，并且用户B也不能对用户A修改的指定行进行操作，只有当用户A提交事务后，用户B才能看到用户A对该表做的操作并且才能操作之前用户A修改或者新增的记录。但是用户B可以正常修改用户A没有在操作的行。在用户B操作用户A修改的记录时会进入阻塞状态，直到用户A提交或者回滚事务。
什么是约束
①约束是表级的强制规定
②有以下五种约束
not null
unique
primary key
foreign key
check
约束的注意事项
①如果不指定约束名，oracle server 自动按照sys_cn格式指定约束名
指定约束名的方式：列定义后+constraint 约束名 + 列级约束
②创建和修改约束：
1 建表的同时
2 建表之后
③可以在表级或列级定义约束
④可以通过数据字典视图查看约束
表级约束和列级约束
作用范围：
①列级约束只能作用在一个列上
②表级约束即既可以作用在多个列上，也可以作用在一个列上
定义方式：列级约束必须定义在列的定义后面，表级约束不与列一起，而是单独定义
非空约束 只能定义在列上
非空约束
-- 建表的同时创建约束 constraint表示约束 后面的是约束名称
create table emp4(
id number(10) ,
employee_name varchar2(10) constraint emp3_employee_name_nn not null,
salary number(12,2)
)
唯一约束 不允许重复值插入，但null值不计入重复
create table test(
id number(10) constraint test_id_nn unique,
e_name varchar2(10) constraint test_e_name_nn not null,
email varchar2(10),
constraint test_uk unique(email) -- 表级约束，可以设置多个列，列与列之间使用,分隔
)
主键约束 能够唯一确定一行记录 主键约束要求非空和唯一
-- 以列级约束的方式设置主键约束
create table test(
id number(10) constraint test_id_pk primary key,
e_name varchar2(10),
email varchar2(10)
)
-- 以表级约束的方式设置主键约束
create table test(
id number(10),
e_name varchar2(10),
email varchar2(10),
constraint test_id_pk primary key(id)
)
外键约束 外键引用的列至少需要唯一约束
-- 以列级约束的方式设置外键约束
create table test(
id number(10) constraint test_department_id_fk references departments(department_id),
e_name varchar2(10),
email varchar2(10),
department_id number(10)
)
-- 以表级约束的方式设置外键约束
create table test(
id number(10),
e_name varchar2(10),
email varchar2(10),
department_id number(10),
constraint test_department_id_fk foreign key(department_id) references departments(department_id)
)
-- 外键约束删除时子表记录的处理策略
on delete cascade (级联删除) 当父表中的列被删除时，子表中相对应的列也被删除
on delete set null （级联置空） 当父表中的列被删除时，子表中相对应的列被置空，设置为null
-- 父表列数据删除时，外键子表数据处理举例
create table test(
id number(10) constraint test_department_id_fk references departments(department_id) on delete cascade,
e_name varchar2(10),
email varchar2(10),
department_id number(10)
)
检查约束 数据不符合要求的会插入失败
create table test(
id number(10) ,
name varchar2(10),
salary number(10) constraint test_salary_check check(salary > 3000 and salary < 40000), -- 设置工资的下限> 3000,上限<40000,不在此范围的数据无法插入成功
department_id number(10)
)
建表后增加约束
①增加/删除非空约束 需要使用modify
alter table tableName modify (cloumnName columnType not null);
alter tabke tableName modify (cloumnName columnType);
②增加其他约束 使用add
-- 删除唯一/主键/外键/检查约束
alter table tableName drop constraint constraintName;
-- 添加唯一约束
alter table tableName add constraint constraintName unique(column);
-- 添加主键约束
alter table tableName add constraint constraintName primary key(column);
-- 添加外键约束
alter table test add constraint test_department_id_fk foreign key(department_id) references departments(department_id)
-- 添加检查约束
alter table tableName add constraint constraintName check(conditions);
无效化约束
alter table tableName disable constraint constraintName;
使无效化的约束生效
-- 如果之前使约束失效后并且插入了违反该约束的数据，需要将该数据修改或删除符合约束条件后才能使失效的约束重新生效
alter table tableName enable constraint constraintName;
查询指定表定义约束的列和约束的名称
select constraint_name,column_name
from user_cons_columns
where table_name='大写表名'
视图：
| 特性 | 简单视图 | 复杂视图 |
|---|---|---|
| 表的数量 | 一个 | 一个或多个 |
| 组函数 | 没有 | 有 |
| 分组 | 没有 | 有 |
| DML操作 | 可以 | 有时可以 |
视图的优点和应用
①控制数据访问
②简化查询
③数据独立性
④删除数据时不删除基表数据
应用top-N分析
创建视图
create view viewName
as
select column1,column2..
from tableName
[可以关联多表查询]
where ...
[with read only] -- 不加 with read only 就可以通过该视图进行增删改的DML操作，默认也是不加的，当加上with read only用户就不能通过该视图进行增删除的DML操作
删除视图
drop view viewName;
显示视图的结构
desc viewName;
修改视图
create or replace view viewName --会覆盖之前的同名视图
as
select *
from tableName
where ..
视图的增
insert into viewName [column1,column2...] values(value1,value2...);
视图的删
delete from viewName where ...;
视图的改
update viewName set columnName = value where ...;
视图中使用DML的规定：
当视图定义中包含以下元素之一时不能使用insert,update,delete
①组函数(聚合函数)
②distinct关键字
③rownum伪列
④列的定义为表达式
⑤基表中非空的列在视图定义中未包括
Top-N分析
select last_name,salary
from(
select last_name,salary
from employees
order by salary desc
)
where rownum <= 10;
rownum 伪列只能使用< ，<= 而不能使用 =，>，>= ,如果想使用>,>=,=需要将伪列变成真实查询的一个字段
-- 直接查伪列rownum > 30 and rownum <= 50没有数据返回
select last_name,salary
from(
select last_name,salary
from employees
order by salary desc
)
where rownum > 30 and rownum <= 50;
LAST_NAME SALARY
------------------------- ----------
-- 将排序后的虚拟表的rownum作为一个真实的列查询，就可以使用>,=,>=
select last_name,salary
from (
select rownum as rn,last_name,salary
from(
select last_name,salary
from employees
order by salary desc
)
)
where rn > 30 and rn <= 50;
LAST_NAME SALARY
------------------------- ----------
Gietz 8300.00
Fripp 8200.00
Chen 8200.00
Weiss 8000.00
设置分页
select column1,column2...
from (
select rownum as rn,column1,column2...
from tableName
) e
where e.rn <= pageNo * pageSize and e.rn > (pageNo - 1) * pageSize;
序列
定义:可提供多个用户用来产生唯一数值的数据库对象
①自动提供唯一的数值
②共享对象
③主要用于提供主键值
④将序列值装入内存可以提高访问效率
创建序列
create sequence sequenceName
[increment by n] -- 每次增长的数值
[start with n] -- 从哪个值开始
[[maxvalue n | nomaxvalue]] -- 是否设置最大值
[[minvalue n | nominvalue]] -- 是否设置最小值
[[cycle | nocycle]] -- 是否需要循环
[[cache n | nocache]] -- 是否缓存登录
-- 举例
create sequence empseq
increment by 10
start with 1
maxvalue 100
cycle
nocache;
删除序列
drop sequence sequenceName;
序列的使用
select sequenceName.nextval from dual; -- 查看序列生产的下一个值
select sequenceNamecurrval from dual; -- 查看序列产生的当前值
-- 序列通常用于产生主键值
create table test (
id number(10),
last_name varchar2(10),
salary number(10,2)
);
insert into test values(empseq.nextval,'zhangsan',2000);
insert into test values(empseq.nextval,'lisi',3500);
-- 通过序列自动为每次插入自增后的记录赋予一个id值，并
修改序列
可以修改序列的增量，最大值，最小值，循环选项或是否装入内存
alter sequence sequenceName --后面的只需要写修改的部分就行
[increment by n] -- 每次增长的数值
[start with n] -- 从哪个值开始
[[maxvalue n | nomaxvalue]] -- 是否设置最大值
[[minvalue n | nominvalue]] -- 是否设置最小值
[[cycle | nocycle]] -- 是否需要循环
[[cache n | nocache]] -- 是否缓存登录
修改序列的注意事项
①必须是序列的拥有者或对序列有alter权限
②只有将来的序列值会被改变
③改变序列初始值只能通过删除序列之后重建序列的方式实现
序列在下列情况会出现裂缝
①回滚
②系统异常
③多个表同时使用同一序列(因此建议一个表使用一个序列，不要共用)
查询序列
查询数据字典视图 USER_SEQUENCES获取序列定义信息
select sequence_name,min_value,max_value,increment_by,last_number
from user_sequences;
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER
------------------------------ ---------- ---------- ------------ -----------
DEPARTMENTS_SEQ 1 9990 10 280
EMPLOYEES_SEQ 1 1E28 1 207
EMPSEQ 1 100 10 11
LOCATIONS_SEQ 1 9900 100 3300
如果不将序列值装入内存(nocache),可使用表USER_SEQUENCES 查看序列当前的有效值
索引
①一种独立于表的模式对象，可以存储在与表不同的磁盘或表空间中
②索引被删除或破坏，不会对表产生影响，其影响的只是查询速度
③索引一旦建立，oracle管理系统会对其进行自动维护，而且oracle管理系统巨顶何时使用索引。用户不用在查询语句中指定使用哪个索引
④在删除一个表时，所有基于该表的索引会自动被删除
⑤通过指针加速oracle服务器查询速度
⑥通过快速定位数据的方法，减少磁盘io
创建索引
①自动创建：在定义primary key或unique约束后，系统自动在相应的列上创建唯一性索引
②手动创建：用户可以在其他列上创建非唯一的索引，以加速查询
创建索引
create index indexName
on tableName(column1...);
什么时候创建索引
①列中数据值分布范围很广
②列经常在where子句或连接条件中出现
③表经常被访问而且数据量很大，访问的数据大概占数据总量的2%-4%
什么时候不要创建索引
①表很小
②列不经常作为连接条件或出现在where子句中
③查询的数据大于2%到4%
④表经常更新
创建索引的缺点
创建索引后会导致增删改的速度变慢，因为增删改之后还需要维护一个索引
同义词-synonym
使用同义词访问相同的对象：
①方便访问其他用户的对象
②缩短对象名字的长度
create [public] synonym synonymName
for objectName;
-- 举例
create synonym e for employees;
select * from e;
删除同义词
drop synonym synonymName;
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
