SQL
system 拥有管理者权限,可以对其他用户进行添加 \ 删除 \ 授权 \ 封锁等操作
drop user scott cascade
cascade 作用 :删除该用户下的所有数据对象(表,视图,存储过程等)
--切换到system用户,删除scott用户
--cascade 删除该用户下的所有数据对象(表,视图,存储过程等)
drop user scott cascade;
--修改用户scott,账户锁定
alter user scott account lock;
--修改用户scott,账户不锁定
alter user scott account unlock;
--修改xiaoming用户的密码为123456
alter user scott identified by 123456;
-- 创建用户
create user scott
identified by "scott"
default tablespace USERS
profile DEFAULT;
-- 授予角色权限
grant connect to SCOTT;
grant resource to SCOTT;
-- 授予系统权限
grant create view to SCOTT;
grant unlimited tablespace to SCOTT;
PL/SQL 快捷键
- ctrl+c 复制
- ctrl+v 粘贴
- ctrl+z 撤销
- ctrl+x 剪切
- ctrl+a 全选
- ctrl+f 查找
- ctrl+r 替换
数据库的表(table)
关系型数据库: 表与表直接存在关联.
数据库表中的横向. 称之为行, 也可以称之为记录
数据库表中的纵向. 称之为列,也可以称之为字段
模式名 = 用户名
select语句
select 需要显示的内容 from 待查询的表名 where 条件
select 字段/* from 表 where 条件;
--从表查询所有字段
select * from emp;
--从scott用户下面的emp查询出所有字段
select * from scott.emp;
--查询表的全部字段
select * from emp;
--这种写法会明确表示这个, 是哪个表里的
select emp.* from emp;
--查询指定字段
select ename,sal from emp;
--查询带条件
select ename,sal from emp where sal>=3000;
select * from emp where sal = 3000;
--查询工资为2000以上 和 工作为经理的员工信息
select * from emp where sal>2000 and job='MANAGER';
--查询工资为2000以上 或 工作为经理的员工信息
select * from emp where sal>2000 or job='MANAGER';
--查询工作不为经理的员工
select * from emp where not job='MANAGER';
比较运算符 <= !=
- 大于 >
- 大于等于 >=
- < 小于
- <= 小于等于
- = 等于
- != <> 不等于
符合条件,结果就为true,就查询出来. 不符合条件结果就为false,丢弃
逻辑运算符 not and or
优先级 not非 and且 or或
--查询emp表中10号部门的经理 和 20号部门的普通员工
--因为and优先级高于or,所以这里不需要添加括号
select * from emp where deptno=10 and job='MANAGER' or deptno=20 and job='CLERK';
--查询emp表中工作不为经理,或者工资大于1000的员工
select * from emp where not job='MANAGER' or sal>1000 ;
查询结果显示
查询结果添加常量列 (数字,字符串)
--常量:值不允许改变
--你好就是一个常量值. 将你好这个字符串放到每一行中
select ename,sal,'你好' from emp;
select * from emp where sal>=2000 or job ='MANAGER';
--双引号不管用只能使用单引号
select * from emp where not job ='MANAGER';
表 列 别名
--表的别名
select e.* from emp e;
--使用表别名
select e.ename,e.sal,e.deptno from emp e;
--一条sql命令的执行顺序--select 字段 from 表 where 条件-- from 表 -> where 条件 -> select 字段
--字段 别名 或者 字段 as 别名
--查询员工的年薪， 公式 年薪 =： 月薪*12 + 提成
select emp.,sal12+nvl(comm,0) 年薪 from emp;select emp.,sal12+nvl(comm,0) as 年薪 from emp;
|| 字符串连接符
--字符串连接符 ||--展示xx的工资是:xxselect ename||'的工资是:'||sal from emp;
--查询工资大于3000的员工姓名,工资,并且新增一列 , 赋值: 高薪员工
select ename,sal,'高薪员工' from emp where sal>3000;
--下面的查询是否有数据返回
select e.* from scott.emp e where e.deptno||e.job ='20MANAGER';
null 值查询处理
null 它不等于任何值. 不能使用等于号进行判断–对于空值的判断,只能使用is is not
--查询没有奖金的员工
select * from emp where comm is null;
--查询有奖金的员工
select * from emp where comm is not null;
--如果条件成立,则可以找出全部的结果
select * from emp where 1=1;
--null不等于任何值,包括自己
select * from emp where null = null;
--处理空值的函数
--nvl(表达式1,表达式2) 对表达式1进行判断,如果值为null,则取表达式2的值,否则取表达式1的值--如果员工没有奖金,则奖励200,有奖金则奖励100
select emp.*,nvl(comm+100,200) from emp;
--空值加上任何值,都得空值. 任何值和空进行运算,结果都为空--给每个员工加200元奖金
select emp.*,nvl(comm,0)+200 from emp;
子查询 select 嵌套
--子查询. 查询语句中还可以存在查询
select e.* from scott.emp e where (select e.ename from emp e where e.empno = 7566) = 'JONES';
--等同于
select e.* from scott.emp e where 1=1;
-- 条件永远成立,则查询出所有记录(所有行)
--课堂练习--1.查询职员表中，在20和30号部门工作的员工姓名，部门号，工资和佣金，如果没有佣金则显示为0.
select ename,deptno,sal,nvl(comm,0) 奖金 from emp where deptno=20 or deptno=30;
--2.查询职员表中，没有管理者的员工姓名及职位
select ename,job from emp where mgr is null;
--针对查询得到的结果进行排序.
order by asc | desc
排序中null ,字符比较
--order by sal asc(升序)/desc(降序)
--查询emp表中所有的员工信息,然后以工资升序--默认就是升序,所以可以省略asc
select * from emp order by sal ;
--以工资降序
select * from emp order by sal desc;
--找出10号部门的所有员工,再按照工资降序--代码执行顺序: from->where->select->order by
select * from emp where deptno=10 order by sal desc;
--oracle中,排序时,null值默认最大
select * from emp order by comm;
--null的三个特点
--1.null和任何数字进行运算都得null
select comm+100 from emp;
--2.null值和任何值比较,都不成立. 它返回的结果都是false
select * from emp where comm<1000;
--3.null默认是最大的select * from emp order by comm;
--nulls first 排序时,如果有null,则放到最前面
--nulls last 排序时,如果有null,则放到最后面. 默认采用
select * from emp order by comm nulls first;
--oracle中对于字符串也能进行排序. 排序依据ascii表
/*'0' -> 48'A' -> 65'a' -> 97
字符串比较规则: 依次比较字符串的每一位,直至比出大小位置helloworld 大
good 大goo*/
select * from emp order by ename;
--为什么这个别名可以拿到外面用. 就是因为子查询优先级执行
select * from(select ename,sal as 工资 from emp)
---通过子查询得到的一个临时表where 工资>2500;
模糊查 not like
通配符: 用于匹配的字符:
% 匹配任意个任意字符 0~无穷大个
_ 匹配1个任意字符 1个
--查询emp表中以A开头的员工信息
select * from emp where ename like 'A%';
--查询emp表中以S结尾的员工信息
select * from emp where ename like '%S';
--查询emp表中包含S的员工信息
select * from emp where ename like '%S%';
--查询emp表中ename第2位为A的员工信息
select * from emp where ename like '_A%';
--查询emp表中ename包含%的员工信息
--escape '/' 前面跟在这个/后面的%,就不再拥有通配符效果. 这里这个/是自己定义的,什么字符都行.但是我们建议使用频率低的字符
select * from emp where ename like '%/%%' escape '/';
--找出包含_的员工
select * from emp where ename like '%/_%' escape '/';
--not取反
--找出emp表中,ename不包含S的员工
select * from emp where ename not like '%S%';
oracle中常用的数据类型有三种
1.数字类型 number
number 数字类型. 最多可以表示38位,包含小数
number(4) 4位数字,且是整数.如果有小数,会四舍五入取整
number(5,2) 5位数字,3位整数,2位小数
varchar2(10) 字符类型,10代表可以容纳10个字母或数字,varchar2一个汉字占用三个字符
drop table student; --删除表
create table student(
sno number(5,2), --列名(字段名)
sname varchar2(10) --列名(字段名)
);
--向表插入数据
insert into student values(555.66,'zhangsan');
2.字符类型 varchar2(大小)
字符串类型
char(10) 固定长度为10,如果只有2个,则补充8个空格. 浪费空间
varchar2(10) 可变长度. 如果只有2个,就只占两个.orcal常用
10 代表字节. oracle中一个字母,数字占用一个字节. 一个汉字占3字节
区别：
char 固定长度为10,如果只有2个,则补充8个空格. 浪费空间
varchar2 可变长度. 如果只有2个,就只占两个. Oracle常用,空字符串会转换为null存储,一个汉字三个字符
varchar msql常用 ,空字符串不作处理,空字符存储，一个汉字两个字符
select * from student where sname is null;
--字符串类型
--char(10) 固定长度为10,如果只有2个,则补充8个空格. 浪费空间
--varchar2(10) 可变长度. 如果只有2个,就只占两个. 使用这个居多
--10 代表字节. oracle中一个字母,数字占用一个字节. 一个汉字占3字节
insert into student values(555.66,'abcdefghijklmn'); --报错,长度超过范围
insert into student values(555.66,'abcdefghij'); --正确
insert into student values(555.66,'诸葛亮'); --正确
insert into student values(555.66,''); --空字符串会转换为null存储
3.日期类型 date
日期类型无法直接使用,需要将字符串转换为日期类型
date’年-月-日’ 不能添加时分秒
to-date() 可以自己指定格式
--日期类型 date. 日期类型无法直接使用,需要将字符串转换为日期类型
create table student(
sno number,
sname varchar2(10),
birthday date --日期类型
);
--date'年-月-日' 只能是这种格式,不能加时分秒
insert into student values(1001,'张三',date'2000-01-01');
insert into student values(1001,'张三',date'2000-01-01 22:33:44'); --错误语法
--使用to-date() 就可以自己指定格式
insert into student values(1001,'张三',to_date('2000-01-01 22:33:44','YYYY-MM-DD HH24:MI:SS'));
--使用自定义格式,可以兼容全球各种人的使用习惯
insert into student values(1001,'张三',to_date('05-20-2022 22:33:44','MM-DD-YYYY HH24:MI:SS'));
select * from student;
数据库表
1.表结构包括 : 表名, 列名, 列的数据类型, 表所属的表空间
--查询当前用户下的所有表
select * from user_tables;
--查看指定用户下的表
select * from all_tables where owner='SCOTT';
--通过pl/sql图形化界面查看
--工具->浏览器->My objects->Tables->当前用户所有表
--1.表结构包括 表名, 列名, 列的数据类型, 表所属的表空间
-- emp ename,sal number,varchar2 USERS
--方式一：右键表名,选择描述. 和查看.
select * from emp;
--方式二：通过查询语句查看表结构
-- 查看scott用户的所有列,然后再指定为emp表
select * from all_tab_columns where owner ='SCOTT' and table_name='EMP';
--方式三：通过命令行查询指定用户下的指定表结构. 使用windows系统
--窗口+R->输入cmd->输入sqlplus->用户system->密码123456->描述表语句desc scott.emp;#已知表名查表结构
2.表描述 comment on table 表名 is ‘描述语言’
列描述 comment on column 列名 is ‘描述语言’
--给表名加上注释
comment on table emp is '员工信息表';
--给列名加上注释
comment on column emp.empno is '员工编号';
comment on column emp.ename is '员工姓名';
3.表关系
-
1:1关系. A表中的一条记录,只能对应B表中的一条记录
-
1:n关系 dept表中的deptno就对应emp表中多条记录的deptno
-
m:n关系 一个学生可能选择多门课程, 同时一门课程可以被多个学生选择
对于多对多关系的表,我们一般设计一张表来映射他们之间的关系,称之为: 桥接表
表数据操作 增insert，删delete，改update，查select
创建create
表名,列名命名规则：只能是字母数字下划线, 且数字不能开头,且不能是关键字,同变量命名规则
方式一：create table 表名(…)
方式二： 根据查询结果直接创建表 ：create table 表名 as select 语句 【where 1=2 仅复制表结构】
--表名,列名命名规则：只能是字母数字下划线, 且数字不能开头,且不能是关键字
--方式一：create table orders(...)
/*练习：参照上面的订单表物理结构,在数据库中创建一张订单表orders，创建一张表,至少需要定义一列，订单号 order_id, 客户号cus_id, 下单日期 order_date, 金额 price，自行插入几条数据*/
create table orders(
order_id number,
cus_id number,
order_date date,
price number
);
insert into orders values(10001,100,date'2022-5-20',800);
insert into orders values(10002,100,date'2022-5-20',1000);
select * from orders;
--方式二：根据查询结果直接创建表，即将查询结果,创建一个表存储起来
create table emp2 as select * from emp where sal>2000;
select * from emp2;
--复制表结构. 只需要列(字段)
create table emp3 as select * from emp where 1=2;
select * from emp3;
--删除表
drop table emp3;
*对表结构更改 alter
alter table 表名 add 列 数据类型|
alter table 表名 modify 列名 数据类型
alter table 表名 rename to 新表名
alter table 表名 rename column 列名 to 新列名
alter table 表名 drop column 列名】
--表已经建好了,再添加列
select * from student2;
--修改表添加字段. 新增字段需要带上类型. 添加字段的值默认为空.
alter table student add sex varchar2(3);
--修改字段的类型. 将sex字段的类型改为数字.
alter table student modify sex number(3);
--修改列名. 将sex字段改为gender字段
alter table student rename column sex to gender;
--删除列
alter table student drop column gender;
--重命名表
alter table student rename to student2;
--创建学生基本信息表
create table stu_bas_inf(
s_no char(6),
s_name varchar2(20),
s_sex char(3),
s_birthday date,
class_no char(5)
);
增insert
方式一：INSERT INTO 表名(列名……) VALUES (值……) 可省略列
方式二：查询结果填充 insert into 表名 select语句；
--insert插入有两种使用方法
--1.不写字段,则代表需要插入所有字段
insert into stu_bas_inf values('1001','张三','男',date'2000-5-3','201');
--2.写字段.则代表有什么字段,就插入什么值. 没有的字段以空代替
insert into stu_bas_inf(s_no,s_name) values('1002','李四');
select * from stu_bas_inf;
select * from emp3;
--复制表结构
create table emp3 as select * from emp where 1=2;
--insert 通过查询结果查询. 查询出的列数,必须等于emp3表里面的列数
insert into emp3 select * from emp where sal>=3000;
--将emp表中有奖金的员工,插入bonus(奖励)表
select * from bonus;
insert into bonus select ename,job,sal,comm from emp where comm is not null;
查select
select 属性、聚类函数、常量...
from 表名
where 条件 [is not null \ not like '%A_' \ rownum between and ]
order by 属性，排序依据 desc / asc
改 update 表名 set 列名1 = 值，列名2 = 值 where 条件；
--修改表内数据 update
--更新bonus表. 将ALLEN的工资改为10000元素
--update 表 set 字段=值 where 条件
update bonus set sal=10000 where ename = 'ALLEN';
select * from emp2;
--给emp2表每人发100元奖金. 如果没有带条件,则代表更新整个表
update emp2 set comm=100;
--给emp2表每人加100元奖金. null加任何值都得null
update emp2 set comm=nvl(comm,0)+100;
删delete
方式一：delete from 表名 where 条件；
方式二：truncate
--删除表内的数据 delete一行(一条记录)
delete from emp2 where ename='JONES';
--清空emp2表
delete from emp2;
--truncate 清空表. 但是不能回滚
truncate table emp2;
--回滚
rollback;
事务
事务概念:
将多条数据库语句放到一个事务中,如果认为该事务操作成功,则提交到数据库. 如果认为该事务操作失败,则执行回滚(撤销).
事务特点:ACID 原子性 一致性 隔离性 持久性
事务状态: 提交和待提交 已提交的操作不能回滚rollback 未提交的数据可发生回滚
提交场景:
1 commit ;
2 自动提交, 通过DDL(create,drop,alter,truncate) 和 DCL(grant,revoke)
DML:database manege language
--事务如何开启: 事务开始于一个DML(insert,delete,update)语句
drop table emp2;
create table emp2 as select * from emp where sal>=3000;
select * from emp2;
--上面的 drop create select 并没有产生事务,所以不能回滚.
update emp2 set sal=10000; --开启一个事务
delete from emp2; --继续存在上面开启的事务中
insert into emp2 select * from emp; --继续存在上面开启的事务中
--回滚. 回到事务开始前的状态. 撤销了上面的三个DML操作. 执行了rollback,上面这个事务就结束了.
rollback;
update emp2 set sal=10000; --又开一个新事务
update emp2 set comm=1000; --继续存在上面开启的事务中
--提交. 认可上面这些操作. 将数据写死到数据库. 就不能再回滚了
commit;
--事务起始于一个DML,事务结束于commit或rollback.
--事务还可以隐式(自动)提交. 通过DDL(create,drop,alter,truncate) 和 DCL(grant,revoke)
update emp2 set sal=20000; --开启事务
create table emp4 as select * from emp; --执行了一个DDL. 就相当于自动commit. 事务就结束了.
rollback; --上面事务已经结束了. 所以无法回滚.
结;
1 事务开始于一个DML(insert,delete,update)语句
2事务 两种状态 提交和未提交,已提交的事务不能回滚,未体检的事务支持回滚,
3 事务启始于 DML(insert,delete,update语句)
4 事务提交方式 commit / 自动提交(DML [create drop alter truncate],DCL[grant revoke])
补:DDL,DML,DCL,DQL
- 数据库定义语言DDL(Data Definition Language)是用于描述数据库中要存储的实体的语言,即描述对象的语言
- 数据操纵语言DML（Data Manipulation Language），用户通过它可以实现对数据库的基本操作。例如，对表中数据的查询、插入、删除和修改。
- 数据控制语言（Data Control Language）是用来设置或者更改数据库用户或角色权限的语句，这些语句包括GRANT、DENY、REVOKE等语句，在默认状态下，只有管理员,所有者等角色的成员才有权利执行数据控制语言。
- 事务控制语言TCL(Transaction Control Language)
- 数据查询语言DQL(Data QueryLanguage) 数据查询语言
数据控制
oracle中存在角色和权限
权限 :
角色:多个权限的集合 connect , resource
CONNECT角色里面的权限是: CREATE SESSION 创建会话 --执行用户为system
RESOURCE角色里面的权限是: CREATE TRIGGER 创建触发器 CREATE TABLE 创建表 – 执行用户为system
创建用户-> 授予权限 [包括授予其他用户下表的权限]-> 回收权限-> 删除用户
grant all on scott.emp to zhangsan; --授予表权限
grant all on 表 to 用户;
revoke all on 表 from 用户;
--创建用户zhangsan,密码为123456 执行用户为system
create user zhangsan account unlock identified by 123456;
--授予zhangsan连接数据库,使用资源的角色. 才能够登录成功 执行用户为system
--connect 允许登录,
--resource 允许建表等操作
grant connect,resource to zhangsan;
--默认A用户不能访问B用户的表 执行用户为zhangsan
select * from scott.emp;
--将使用表的权限,赋予某个用户. 执行这句命令使用system用户
grant all on scott.emp to zhangsan;
--回收表权限 执行这句命令使用system用户
revoke all on scott.emp from zhangsan;
--回收连接数据库,使用资源的角色 执行这句命令使用system用户
revoke connect,resource from zhangsan;
--删除zhangsan用户. 并且删除它名下的所有数据对象(表...) 执行这句命令使用system用户
drop user zhangsan cascade;
数据对象:表 视图 索引 序列 约束 函数 存储过程 包 触发器
查询用户拥有的权限 ,查看角色包含的权限
--查询用户拥有的角色. 执行用户为zhangsan
select * from user_role_privs;
--执行用户为system
--CONNECT角色里面的权限是: CREATE SESSION 创建会话
select * from dba_sys_privs where grantee='CONNECT';
--RESOURCE角色里面的权限是: CREATE TRIGGER 创建触发器 CREATE TABLE 创建表
select * from dba_sys_privs where grantee='RESOURCE';
结:
system 可以查看其他用户下的表 ,其他不同用户不能查看了另一个用户的表,除非用system
约束
约束 constraint :为力保证数据库中数据有效性 而添加的限制条件
常见类型:主键 唯一 检查 默认 非空 外键
方式一 建表时创建约束
drop table student;
--一个表中只能有一个主键
create table student(
sno number primary key, --主键约束 用于唯一区分两条数据,主键的值不允许重复
sname varchar2(20) not null, --非空约束 值不允许为空
seat number unique, --唯一约束 该字段的值不能重复 ,和主键的区别,主键只能在一个表中出现一次,unique 可以出现多次
sex varchar2(3) check(sex='男' or sex='女'), --检查约束 限定该字段只能去那些值
address varchar2(50) default '深圳市'
);
--不符合约束的数据无法插入到表中
insert into student values(1002,'李四',103,'女','北京市');
select * from student;
--使用默认值
insert into student(sno,sname,seat,sex) values (1003,'王五',102,'男');
方式二 表已建立 添加约束
alter table 表名 add constraint 约束名 primary key (列名);
alter table 表名 add constraint 约束名 unique(列名);
alter table 表名 add constraint 约束名 check (列名 = ‘取值1’ or 列名 =‘取值2’)
alter table 表名 modify 列名 not null;
alter table 表名 modify 列名 default ‘默认值’;
--主键约束: 修改student表 添加主键约束,约束名为pk_student_sno,将sno 作为主键
alter table student add constraint pk_student_sno primary key(sno);
--非空约束: 字段的null和 not null 就是二选一的关系,并且字段默认为null,所以对null的约束是修改用modify
alter table student modify sname not null;
alter table student modify address default '深圳市';
--唯一约束
alter table student add constraint uq_student_seat unique(seat);
--检查约束
alter table student add constraint c_student_sex check(sex='男' or sex='女');
外键约束:
建表时添加: sno number not null references student(sno)
方式二:
alter table score add constraint fk_score_sno foreign key(sno) references student(sno);
删除外键约束
alter table score drop constraint 外键约束名;
--一个表无法放下所有的数据内容. 所以就需要分成多张表.
--学生表 学号,姓名,座位号,性别,地址
--成绩表 学号,科目,成绩
create table score(
--外键的数据类型必须和主键的数据类型保持一致
sno number not null references student(sno), --外键约束. 引用自student表里面的sno主键
course varchar2(20),
degree number
);
insert into score values(1001,'语文',88);
insert into score values(1001,'数学',90);
insert into score values(1001,'英语',75);
insert into score values(1002,'语文',82);
insert into score values(1002,'数学',85);
insert into score values(1002,'英语',77);
select * from student;
select * from score;
--1.如果向从表插入一个主键不存在的值,则会失败
insert into score values(1004,'英语',55);
--2.如果外键使用了某个值,则主键不允许被删除
delete from student where sno=1001;
--创建表后再加外键约束
drop table score;
create table score(
sno number,
course varchar2(20),
degree number
);
--修改score表添加约束.约束名为fk_score_sno. 内容是外键sno,引用自student表里的主键sno
alter table score add constraint fk_score_sno foreign key(sno) references student(sno);
--约束的删除. 修改表删除约束
alter table score drop constraint fk_score_sno;
结:
外键: 外键值一定要来源于主键,否则表内的数据就是无效的
结论1:如果向从表插入一个主键不存在的值,则会失败
结论2:如果外键使用了某个值,那么主键不允许被删除
--将高水位线归零
--清空表,且不能回滚
truncate table xxx;
--查询时可以使用算术运算符 + - * /(得到小数) 求余(mod)
select ename,sal,sal+100,sal-100,sal*2,sal/2 from emp;
--求余. A/B 取的是余数,而不是商. 小的数对大的数求余,余数就是小的数.
select mod(8,5),mod(9,4),mod(5,10) from dual;
--如果别名中存在特殊字符,则可以使用双引号
select ename,sal,sal*12+2000+nvl(comm,0) "$年总工资" from emp where sal>2000;
--返回当前日期时间
select sysdate from dual;
--返回明天的日期时间. 数字和日期都可以进行加减
select sysdate+1 from dual;
--自动将字符串转换为数字进行相加
select '10'+1 from dual; --11
select '10'+'20' from dual; --30
--尝试转换为数字失败,则相加失败.
select 'abc'+1 from dual;
--自定义时间相加
select date'2020-5-20'+1 from dual;
--比较运算符只能两两相比
select * from emp where sal>3000 and sal<=5000;
in 操作符 属性=多个值
in 如果一个字段拥有多个值,则可以使用in简化
条件中使用where job in (‘MANAGER’,‘PRESIDENT’,‘CLERK’)
--查询工作为经理,董事,普通员工的
最低0.47元/天 解锁文章
587
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
