1. DDL管理数据库中的表
如何开始一个新的项目，数据库层面.
创建表空间
创建用户
授权
建表
1.1 创建表空间 (重点)
语法：
create tablespace 名称
datafile 'c:\db24.dbf' -- 存放的文件位置
size 100M -- 初始表空间的大小
autoextend on--表空间容量不足时，自动增长
next 10M -- 自动增长时的大小
--示例
create tablespace fang24
datafile 'c:\fang.dbf'
size 100m
autoextend on
next 10m;
表空间相关查询，练习时可能会用到
/**删除表空间同时删除数据文件**/
drop tablespace ts_name including contents and datafiles;
/**删除用户同时删除由该用户所创建的东东**/
drop user ???? cascade;
/**查看用户，使用哪个表空间**/
select username,default_tablespace from user_users;
/**查看表所在的表空间名**/
SELECT table_name, tablespace_name FROM user_tables;
/**查看表空间对应的文件名所在的表空间**/
select t1.name,t2.name from v$tablespace t1,v$datafile t2 where t1.ts# = t2.ts#;
1.2 创建用户(重点)
语法：
create user 用户名 identified by 密码 default tablespace 表空间名称;
--示例
create user heima24 identified by heima24 default tablespace heima24;
授权:
有三个角色: connect, resource, dba, 可做成小抄
--查看角色拥有哪些权限
select * from dba_sys_privs where GRANTEE='CONNECT';
select * from dba_sys_privs where GRANTEE='RESOURCE';
--查看用户有哪些权限
select username,privilege,'' role from user_sys_privs
union
select up.username,rs.privilege,up.granted_role role from user_role_privs up
left join role_sys_privs rs on rs.role=up.granted_role
给用户授权
grant 角色名/权限名 to 用户
--示例
grant dba to heima24;
1.3 创建表(重点)
语法：
create table 表名(
列名 列的类型 [列的约束] [默认值],
列名 列的类型 [列的约束] [默认值]
);
--示例
CREATE TABLE STUDENT (
SID NUMBER(10) PRIMARY KEY, --number 数值类型
SNAME VARCHAR2(30) NOT NULL,
SAGE INTEGER
);
1.4 修改表 (知道)
1.4.1 添加一列(字段)
alter table 表名 add 新的列名 列的类型
--示例, 给学生表加性别
alter table student add sex integer;
1.4.2 修改列的类型
alter table 表名 modify 列 列的类型
--示例
alter table student modify sex char(1);
1.4.3 修改列的名称
alter table 表名 rename column 原来的列名 to 新的列名
--示例
alter table student rename column sex to gender;
1.4.4 删除列
alter table 表名 drop column 列名
--示例
alter table student drop column gender;
1.4.5 修改表名
rename 原来的表名 to 新的表名
--示例
rename student to stu;
1.4.5 加注释
--给表加注释
comment on table 表名 is 注释内容
--示例
comment on table stu is '学生表';
--给列加注释
comment on column 表名.列名 is 注释内容
--示例
comment on column stu.sid is '学生编号';
1.5 删除表（了解）
drop table 表名
--示例
drop table stu;
--回收站, 是一个视图
select * from user_recyclebin; --返回列中有一个叫object_name
--查看回收站中表的内容
select * from "object_name";
--示例
select * from "BIN$Y2vg2nccTmqgaG0q8PGouw==$0";
--恢复表
flashback table 表名 to before drop;
--示例
flashback table stu to before drop;
--清空加收站
purge recyclebin; --慎用，一旦清除将无法恢复
--暴力删除表
drop table 表名 purge;
--示例--慎用
drop table student purge;
1.5 约束
主键约束(primary key 重点)
第一种:create table student(sid number primary key,--建表时，行里加sname varchar2(10),age number);第二种：create table student(sid number,sname varchar2(10),age number,constraint pk_stu primary key(sid)--最后一行加);语法：constraint 约束名 类型 key(列)第三种 (常用)alter table 表名 add constraint 约束名 primary key(列名);--示例alter table student add constraint pk_student primary key(sid);外键约束(foreign key 重点)
alter table 多方表名 add constraint 约束名 foreign key(多方表中的列名) references 一方的表名(一方表中的列名);--示例alter table student add constraint fk_stu_tea foreign key (tid) references teacher(tid);唯一约束(unique 知道)
alter table 表名 add constraint 约束名 unique(列名,...)--示例alter table student add constraint uk_stu_sname unique(sname);非空约束(not null 重点)
--可为空alter table 表名 modify 列名 null;--不可为空alter table 表名 modify 列名 not null;--示例alter table student modify sage not null;检查约束(check 知道)
alter table 表名 add constraint 约束名 check (列 的表达式);--示例alter table student add constraint age_ck check (sage in (18,20));
1.6 删除约束（知道）
alter table 表名 drop constraint 约束名称;
--示例
alter table student drop constraint age_ck;
2 使用DML管理数据(重要)
2.1 插入数据(insert)
语法:
insert into 表名[列名...] values(值1,值2...);
insert into student values (3,'lisi2',30,null);
insert into student (sid,sname,age) values (3,'lisi2',30);
复制表:
create table 表名 as select * from 另外一张表 [where 条件]
--示例
--复制表 备份数据时使用
create table emp as select * from scott.emp;
--复制表结构
create table emp1 as select * from scott.emp where 1=2;
从查询结果中插入数据
insert into 表1[列...] select [列] from 表2 where 条件; -- 列的类型与顺序必须一致
--示例
insert into emp1 select * from scott.emp where empno=7788;
2.2 更新数据(update)
语法:
update 表名 set 列=值,列2=值2 where 条件
--给所有NEW YORK的员工涨工资100
分析:
1. 找数据所在的表 (部门，员工)
2. 条件所在的表(部门)
3. 数据与条件的关系 操作运算符
实现1
UPDATE EMP SET SAL=SAL+100 WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE LOC='NEW YORK');
实现2
UPDATE EMP E SET E.SAL=E.SAL+100 WHERE EXISTS(
SELECT 1 FROM DEPT D WHERE D.LOC='NEW YORK' AND e.deptno=d.deptno
);
先备份数据:
select * from emp WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE LOC='NEW YORK');
select * from emp e WHERE EXISTS(
SELECT 1 FROM DEPT D WHERE D.LOC='NEW YORK' AND e.deptno=d.deptno
);
2.3 删除数据(delete)
语法:
delete from 表名 where 条件
--删除所有数据
delete from 表名
高效(数据量大时)的删除表中所有数据 truncate
两者的区别:
delete 逐行删除，需要事务(dml)
truncate 先删除表再建表(DDL)，不需要事务 (慎用)
delete from emp1;
truncate table emp;
3 事务控制(重要)
3.1 事务四个特性(ACID):
原子性：最小的执行单元，不可再分割
一致性：要么都成功，要么都失败
隔离性：互不干扰
持久化：数据一旦提交后，写入磁盘，不可更改
3.2 事件的隔离级别：
如果没有隔离级别就会发生：脏读，不可重复读(update)，虚读/幻读(insert)
mysql: 四个(read uncommitted(导致所有),read committed(避免脏读), repeatable read(避免脏读、不可重复读), serializable（所有都可避免）)
oracle的隔离级别：read committed(默认), serializable, read only只读, oracle没有脏读
3.3 事务的提交
commit, rollback
显式提交: commit, rollback
隐式提交：
执行了ddl语句，事务自动提交
正常退出，事务自动提交
异常退出，事务回滚
3.4 保存点
运用常景：处理业务逻辑时，需要一部分数据提交，另外一部分需要看成功与否来决定是否提交事务。
savepoint 保存点的名称 --设置保存点，在这个保存点前的操作都归这个保存点控制
rollback to 保存点的名称 -- 事务回滚到哪个保存点
insert into emp1 select * from scott.emp where empno=7902;
insert into emp1 select * from scott.emp where empno=7369;
savepoint aa;
insert into emp1 select * from scott.emp where empno=7499;
insert into emp1 select * from scott.emp where empno=7521;
rollback to aa;
commit;
4. 数据库的其它对象
4.1 视图(了解)
是一组封装好的查询语句，本身不保存数据，所有的数据都在原来的表中。
作用：封装查询语句, 提高查询的效率。屏蔽表中细节
注：视图可以更新，更新到原来的表中。不推荐
语法： or replace 存在的话就进行替换，否则创建
create or replace view 视图的名称 as
查询语句
select * from ....
示例
--封装复杂查询语句
create or replace view view_hirecount
as
select
sum(t1.cnt) as total,
sum((case t1.y when '1980' then t1.cnt else 0 end)) "1980",
sum((case t1.y when '1981' then t1.cnt else 0 end)) "1981",
sum((case t1.y when '1982' then t1.cnt else 0 end)) "1982",
sum((case t1.y when '1987' then t1.cnt else 0 end)) "1987"
from (
select t.y,count(1) cnt from (
select to_char(hiredate,'yyyy') as y from emp
) t group by t.y
) t1
--屏蔽表中细节
create or replace view view_emp
as
select empno,ename,job from emp1;
--更新视图
update view_emp set job='SALEMAN' WHERE EMPNO=7902;
只读视图: 不能更新
create [or replace] view 视图名
as
查询语句
with read only
--示例
create or replace view view_ro
as
select empno,ename,job from emp1
with read only;
4.2 序列(重点)
是数据库提供一种数值类型，且按一定规则自动增长的序号
场景：需要自动增长主键，或需要自动增长的序号
语法：
create sequence 名称
start with 1 --从什么值开始
minValue 1 --允许的最小值
maxValue 20 --允许的最大值
cycle | nocycle -- 循环, 1-20,又变成1开始
increment by 1 --每次增长的步值，1, 1+1=2
cache 5 --缓存序号的值. 当前4, 取出5,6,7,8,9 放到缓存
cache注：如果异常中断了，缓存里的序号，不再出现了(废弃了)
序列的取值:
序列名.nextval 取下一个值
序列名.currval 取当前值, 必须最少先执行了一次nextval(session级别)
示例
create sequence emp_seq
start with 1 --从什么值开始
minValue 1 --允许的最小值
maxValue 20 --允许的最大值
cycle -- 循环, 1-20,又变成1开始
increment by 1 --每次增长的步值，1, 1+1=2
cache 5 --缓存序号的值. 当前4, 取出5,6,7,8,9 放到缓存
--快速创建，默认的值start with=1, nocycle, nocache, incrment by 1
create sequence dept_seq;
4.3 索引(重要：运用)
索引是一种已经排好序的数据结构，能够帮助数据库快速去查找指定内容。合理的使用索引可以大大降低i/o次数，提高数据访问的性能。类似书本的目录,可以快速的定位到具体的内容
BTree索引: 多叉树
语法
create index 索引名 on 表名(列名,...)
什么样的列名符合创建索引？
答：查询条件的列
索引的失效: in (索引的列), not in (索引的列), 索引的列 is not null
不好的地方：插入，更新，删除慢
测试
--建表
create table stu(
sid number(10),
sname varchar2(30),
sdesc varchar2(30)
)
--加数据
declare
begin
for i in 1..5000000 loop
insert into stu values(i,'学生第'|| i || '学生','学生描述' || i);
end loop;
commit;
end;
--没有索引
select * from stu where sname='学生第4563298学生';--7.669
--创建索引
create index idx_stu_name on stu(sname);
--使用索引查询
select * from stu where sname='学生第4563298学生';--0.211
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
