一、 数据库介绍

数据库

广义数据库

• 是物理操作系统和磁盘的集合

狭义数据库

• 数据库+数据库操作系统

Oracle：位于物理内存的数据结构，是有操作系统的多个后台进程一个共享的内存池锁做成，共享的内存可以被所有进程方森

存储结构：

• 物理存储

• 逻辑结构

    数据库 --> 表空间 --> 段 --> 区 --> Oracle 数据块
  

SQL 语言

1、 DDL （data definition languages）

• 数据定义语言
• 定义了不同数据段、数据库、表、列、索引等数据库对象
• CREATE DROP ALTER RENAME TRUNCATE
• 常常有数据库管理员（DBA)使用
• 用在定义或改变表的结构、数据类型、表之间的连接和约束等初始化工作上，大多数在表的建立时使用
• 操作

1. 创建表

-- 创建一张表 
create table name( 字段名 类型（长度），.....);
-- 从其他表拷贝结构
create table name1 as select 字段列表 from 已有表 where 1!=1;

2. 修改表结构

-- 修改表名
rename 原表名 to 新表名
-- 修改列名
alter table 表名 rename column 列名 to 新列名
-- 修改字段类型
alter table 表名 modify(字段 类型）
-- 添加列
alter table 表名 add 字段 类型
-- 删除列
alter table 表名 drop column 字段

3. 删除表

drop table 表名;

2、 DML （data manipulation language）

• 数据操控语句
• 用于添加、删除、更新和查询数据库记录，并检查数据库完整性
• insert,delete,update,select
• 对表内进行操作

3、 DCL（data control language）

• 数据控制用于
• 用于控制不同数据段直接的许可和访问级别的语句
• 定义了数据库、表、字段、用户访问权限和安全级别
• grant、revoke回收权利、commit提交事务、rollback回滚事务

二、 创建用户和表结构

1 sys登录

conn sys/root as sysdba
select instance_name from v$instance;

2 创建表空间

create tablespace 表空间名 datafile ‘ 路径\文件名.dbf‘ size 200m;

创建一个 tds 文件

3 创建用户

create user scott identified by tiger default tablespace scott_tb_space;
create user 用户名 identified by 密码 default tablespace 表空间; -- 创建用户并制定表空间

4 授权权限

grand dba to scott;
grand dba to 用户名;

5 普通用户登录

conn scott/tiger@xe
select * from dual;

三、 表设计

表

数据库中以表为组织单位存储数据，用来存储一些事物的信息；

表名+存储信息

约束

主键约束

primary key
用于定位表中单个行的方式，可唯一确定表的一行
唯一且非空
一个表只能由一个主键

唯一性约束

unique
确保每一行的唯一性，但允许有多空值

非空约束

not null
非空

外键约束

foreign key
主表：被参考的表
从表 参考的表，必须按照主表要求来

检查约束

check

四、 select 语句

1. 表示查询

select  *|colname[,...] from table [alians]
-- * 可以用所有内容的具体信息替代
select deptno,dname from dept;

2. 去重

关键词：instinct

select distinct deptno from emp

3. 别名

方法 1：用 as 关键词

select ename as 姓名， sal as 工资 from emp；

方法 2：省略关键词

select ename 姓名，sal 工资 from emp;

4. 排序

order by 关键词

select ename,sal from emp order by sal desc;
select ename , sal , deptno from emp order by deptno asc, sal desc;

5. 执行顺序

五、 处理空值

方式 1 nvl()函数

--  第一个参数不为空时返回的数，第二个参数，为空时返回的数
select ename, sal, comm, sal+somm as 月收入, from emp; -- 当 comm 为空时，sal 也为空，影响数据结果
select ename, sal, comm, sal+somm + nvl(comm,0)  as 月收入, from emp;
select nvl(1,100) from dual;
select nvl(null,100) from dual;
select * from emp where vvl(comm,0)<=0;

方式 2

nulls first/nulls last 用于排序

select * from emp order by comm desc nulls first/nulls last;

空值判断

1. is null
2. is not null
3. not ... is null

六、 伪列和虚表

伪列

本不应存在的一列，然后根据需求再临时添加一列

-- 求年薪
select ename, sal, 1,from emp;
select ename , sal, sal*12 as 年薪 from emp;

虚表

• dual 虚拟表
• 用于构成 select 的语法规则，Oracle 保证 dual 永远只有一条数据
• 该表只有一行一列，用于选择系统变量或者求一个表达式的值
• 可执行插入更新、删除和 drop 操作，————但如果执行 drop 的话，会造成系统崩溃
• select 完成一些信息，需要借助一个对象时，可以用这个表

如

select 9899*888 from dual;

七、 查询条件

• 条件查询

分类：

• >,>,=,>=,<=
• !=,<>,^=
• between ...and： 闭区间
• not

select * from table where
select * from emp where deptno =10
select ename, deptno  from emp where deptno !=10
select * from emp where ename =‘SMITH‘

• 条件连接运算符
  • and
  • or
  • not

八、 模糊查询

• % :位数不定的模糊字符
• like

select * from emp where like ’%S%‘

select * from emp where ename like ‘%a%%‘ escape (‘a‘);
select * from emp where ename like ‘%aaa%%‘ escape(‘a‘)
select * from emp where ename like ‘%a%%a_%‘ escape(‘a‘）

九、 where 子句

select * from where group by ... having ... order by ...
select * from emp where deptno = (select deptno from dep where dname = ‘SALES‘)
select * from salgrade where sal between (select losal from salgrade where grade  = 2) and (select hisal from salgrad where grade =2)

十、 group by 分组

select ... from .... where ... group by ...
select avg(sal) from emp group by deptno;
select count(*) from emp group by deptno;

十一、 having 过滤

select deptno. coumt(*), from emp where sal >2000 group by deptno having count(*) >=2;

十二、 函数

字符串拼接 ||

select ename from emp;
select ename, ename ||"a"  别名 from emp;
select ename ,comm,ename || comm  as test from emp -- 当有 null 时，会不进行拼接

单行函数

• 在对应表记录时，一条记录返回一个结果

-- 字符串函数

-- concat(x,y)  连接字符串x 和 y
select ename||job as namejob from emp;
select concat(ename,job) from emp;

-- instr（x,str,start,n)；在x中寻找str，可以指定从start开始，可以从指定从第n次开始，返回字符串的位置
select instr(‘helloworld‘ ,‘e‘) from dual
select instr(‘helloworld‘ ,‘a‘) from dual
select ename, instr(ename,‘A‘) from emp;

-- length(x)：返回x的长度

-- lower（x）：x转化为小写
-- upper（x）：x转化为大写

-- ltrim（x, trim_str)：把x左边截去strim_str字符串，缺省截去空格
select ltrim(‘   abc  abc   ‘) from dual

-- rtrim（x, trim_str)：把x右边截去strim_str字符串，缺省截去空格
select rstrim(‘   abc   abc   ‘)||‘a‘ from dua;
select rstrim(lstrim(‘   abc   abc   ‘)) from dual
select concat(rstrim(lstrim()),‘a‘) from dual;


-- replace(x,old ,new)：从x中查找old ，并替换为new

-- substr( x, start,length)：返回x字符串，从start开始，截取length个字符，缺省length，默认到结尾

-- 数学函数
-- abs（x)： 取绝对值
-- ceil(x) ： 向上取整
-- floor
-- mod(x,y)

-- 日期函数

-- sysdate :当前系统时间，无括号
select sysdate from dual;
select sysdate+10 from dual;

-- current_date 返回当前系统日期，无括号

-- add_months(d1,n1)返回在di基础上再加上n1个月以后的新日期
selct empno, ename, hiredate, add_months(hiredate,3) from emp;
selct empno, ename, hiredate, add_months(hiredate,-3) from emp;

-- last_day(d1) 返回日期 d1 所在鱼粉最后一天的日期
select last_day(hireday) ,hiredauy from emp;

-- months_between(d1,d2) 返回日期d1和日期d2之间的月数
select sysdate, hiredate, months_betweem( sysdate, hiredate)from emp;
select sysdate, hiredate, months_betweem(hiredate, sysdate )from emp;

-- next_day(d1,[,c1]) 返回日期d1在下周，星期几c1，的日期
select next_day(sysdate,‘星期一‘)  as 入职时间 from dual;

-- 转换函数
-- to_char(x,c)将日期或数字x按照c的格式转化成char数据类型
select hiredate ,to_char(hiredate, ‘mm‘-‘dd‘-‘yyyy‘) from emp;
select hiredae, to_char(hiredate, ‘mm"月"dd"日" yyyy "年"‘) from emp;

-- to_date(x,c) 将字符串x按照c的格式转化成日期
select to_date(‘1900/1/1‘, ‘yyyy//mm/dd‘)

-- co_number(x) 将字符串x转化成数字型
select to_number(‘11‘) +1 from dual;

多行函数

• 也叫聚合函数或组合函数
• 可以同时对多条记录进行操作，并返回一个结果
• AVG,SUM,MIN,MAX,COUNT
• 注 null 不能参与运算

select count(nvl(comm,0)) from emp ;

十三、 分页

• 列表内容太多，用分页进行显示
• 方案
  • 一次查询出数据库的所有数据，然后在每页汇总显示指定的记录 —— 假分页
  • 对数据库进行多次查询，每次获得本页的数据并显示

-- 对每一个结果集中的每一条记录的编号，从1开始
select ename , sal, deptno ,rownum from emp;
select ename, sal,deptno, rownum from emp where deptno = 30;


select ename, sal,deptno from emp where rownum <=5;-- 查询第一页数据，每页 5 条数据
select ename , sal, deptno from emp where rownum <= 5; -- 然后查询第二页，rownum 永远从 1 开始

select ename, sal, deptno, rownum from emp; -- 产生衣蛾伪列
select * from (select ename , sal, deptno,rownum as rw from emp) where rw >1;
select * from (select ename , sal, deptno,rownum as rw from emp) where rw >5 and rw <=10;


select ename, sal, deptno, r1 r2 from)
select ename, sal, deptno r1, rownum   r2 from(
select ename, sal, deptno, rownum r1 from emp order by sal desc
)) where r2 <=3;

十四、 去重

• rowid

用于定位数据库中一条记录的一个相对位移的地址值
通常情况下，该值在该行数据插入到数据库表是即被确定却唯一
是一个伪列，并不存在于表中
在oracle在读取表中数据行是，根据每一行数据的物理地址信息编码而成的一个伪列
根据一行数据的rowid 能找到一行数据的物理地址信息，从而快速定位到数据行
rowid是进行单记录定位速度最快的

• 重复记录的查找

create table copy as select * from dept;
select * from copy;
select deptno, dname, loc, rowid from copy order by deptno;
insert into copy select * from dept;
commit;

select min(rowid) from copy group bu deptno,dname,loc;
select * from copy where row not in (select min(rowid) from copy group by deptno,dname,loc);
delete from copy where rowid not in (
select min(rowid) from copy group bu deptno,dname,loc
);
commit;

十五、 表链接

• 一个表的行根据指定的条件与另个表的行连接起来形成新的过程

• 92 语法

select .. from t1,t2,t3,... where ...
简化表名
可能存在自连接的情况
原理： 按照from 后面表的出现顺序，前面的表作为内存的for循环，后出现的表作为外城的for循环
笛卡尔积

等值连接与非等值连接（!= 、>、 <、 <>、 between and）
select ename, sal, hiredate, grade from emp e, salgrade s 
where e.sal between losal and hisal;

自连接：使用比价运算符根据每个表共有的列匹配两个表中的行
select * from emp as e,emp as m where e.mgr = m.empno;

外连接：可以左向外连接、右向外连接或完整外部链接

select * from dept as d, (select count(*), deptno from emp group by deptno) as c where d.deptno = c.deptno(+);  -- ”+“ 代表非主表
select d.deptno, dname, loc, nul(cc,0) from dept d, 
(select count(*) cc, deptno from emp group by deptno) c where d.deptno = c.deptno
select * from emp e, emp m where e.mgr = m.empno(+);

• 99 语法

-- cross join 交叉连接，实现笛卡尔积
select * from dept cross join emp;

-- natural join ：自然连接，做等值连接，要求同名列或者主外键
select ename, empno, deptno, dname from emp natural join dept;
select ename,deptno, dname from emp natural join dept where deptno = 10;

-- join using ：等值连接，必须有同名列进行连接
select enmae,empno,deptno,dname from emp join dept using (deptnu);
select ename,deptno, dname from emp join using (dept no) where deptno = 10;

-- join on ：可做等值连接、非等值连接、自连接、解决一切连接，关系列必须要区分
select ename, empno, e.deptno, dname from emp e join dept d on e.deptno = d.deptno

select ename, sal, e.deptno, grade, dname from
emp e 
join dept d on e.deptno = d.deptno
 join salgrade on e.sal between losal and hisal
where e,deptno = 30;

-- outer join ：外连接，有主表和从表
left [outer] join on
left [outer] join using
right [outer] join on 
right [outer] join using

-- full join on | using

• 92语法与 99 语法的区别

十六、 集合操作

集合分类

union

并集、去重、对两个结果集合进行并集操作，不包括重复行，默认排序按照规则

union all

全集、不去重，对两个结果集合进行并集操作，包括重复行，不排序

interset

交集，找到重复，对两个结果集合进行交集操作，不包括重复行，默认规则排序

minus

差集，减去重复，对两个结果集合进行差集操作，不包括重复行，默认规则排序

要求

两个结果集合，要求字段个数和字段类型以一对应

select ‘a‘,‘b‘ from dual;
select ‘c‘,‘d‘ from dual;


select ‘a‘,‘b‘ from dual
union
select ‘c‘,‘d‘ from dual
select ‘a‘,‘b‘ from dual; -- 求并集，ab 、有两个，去重

select ‘a‘,‘b‘ from dual
union
select ‘c‘,‘d‘ from dual
union all
select ‘a‘ , ‘b‘ from dual; -- 全集不去重

select ‘a‘,‘b‘ from dual
union
select ‘c‘,‘d‘ from dual
union all
select ‘a‘ , ‘b‘ from dual; -- ab


（select ‘a‘,‘b‘ from dual
union
select ‘c‘,‘d‘ from dual）
minus
（ select ‘a‘,‘b‘ from dual 
union 
select ‘e‘,‘f‘ from dual） -- cd

十七、 数据类型

• VARCHAR2(size）

  可变长度字符串，1：4000

• NVARCHAR2(size）

  可变长度字符串，根据所需国家字符集来定义最大长度 必须指定长度

• NUMBER

• LONG

• DATA

• RAW(size）

• LONG RAW

• CHAR(size）

• NCHAR(size）

• CLOB

• NCLOB

• BLOB

• BFILE

十八、 创建带有约束的表

create table t1(
userid number(5) primary key,
username varchar2(30) check(length(username between 4 and 20) not null,
userpwd varchar2(20) not null check(length(userpwd) between 4 and 18),
age number(3) default(18) check(age> =18),
gender char(3) default(‘男‘) check (gender in (‘男‘,‘女‘)),
email varchar2(30) unique,
regtime date default(sysdate)
);

create table t2 (
txtid number(5) primary key, -- 主键约束
title varchar2(32) not null check(length(title)>=4 and length(title) <= 30,
txt varchar2(1024),
pubtime date default(sysdate),
userid number(5) reference t1(userid) on delete set null
)

创建带有名字的约束

create table t1(
userid number(5) ,
username varchar2(30) contraint user_name not null ,
userpwd varchar2(20) constraint not null ,
age number(3) default(18) ,
gender char(3) default(‘男‘) ,
email varchar2(30) ,
regtime date default(sysdate)
constraint ke_uyser_id primary key(userid),
constraint ck_user_name check(length(username) between 4 and 20)
constraint ck_user_pwd check(length(userpwd) between 4 and 18),
constraint ck_user_age check(age> =18),
constraint ck_user_gender check (gender in (‘男‘,‘女‘)),
constraint ck_user_email unique(email)
);  


create table t2 (
txtid number(5) ,
title varchar2(32) nn_txt_title not null,
txt varchar2(1024),
pubtime date default(sysdate),
userid number(5),
constraint pt_txt_id primary key(txid),
 constraint ck_txt_title check (length(title)>=4 and length(title) <= 30
constraint fk_txt_user_id foreign key(userid) references tb_user(userid ) on delete set null
)

创建并追加约束

create table t1(
userid  number(5)，
username varchar2(30),
userpassword varhcar2(20),
age number(3),
gender char(2),
email varchar2(30),
regtime time default(sysdate)
);

alter table t1 add constraint pk_us_id primary key(userid);
alter table t1 add constraint ck_user_name check(length(username) between 4 and 28);
alter table t1 add constraint ck_user_pwd check(length(userpwd) between 4 and 18);
alter table t1 add constraint ck_user_age check(age>=18);
alter table t1 add constraint ck_user_gender check(gender in(‘男‘,‘女‘));
alter table t1 add constraint uq_user_email unique(email);
alter table t1 modify(username constraint nn_user_name not null);
alter table t1 modify(userpawd constraint nn_user_pwd not null);
alter table t1 modify (age default(18)
alter table t1 modify

create table t2 (
txtid number(10),
title varchar2(32),
txt varchar2(1024),
pubtime date,
userid number(5)
);

alter table t2 add constraint pk_txt_id primary key(txtid);
later table t2 add constraint ck_txt_id check(length(title) >=4 and length(title)<=30);
alter table t2 add constaint fk_txt_ref_user_id foreign key(userid) references t1(userid);-- 强制不删除
alter table t2 add constaint fk_txt_ref_user_id foreign key(userid) references t1(userid) on delete set null; -- 自动设为空
alter table t2 add constaint fk_txt_ref_user_id foreign key(userid) references t1(userid) on delete cascade; -- 联级删除

禁用和启用约束

1. 启动禁用：enable，disable，是否对新变更的数据启动验证约束
2. 验证，非验证：validate、novalidate，是否对表中以客观存在的数据进行约束验证

• enable validate：默认的约束组合状态，无法添加违反约束的数据行，数据表中也不能存在违反约束的数据行
• enable novalidate：无法添加违反约束的数据行，但对已存在的违反约束的数据行不做验证
• disable validate ：可以添加违反约束的数据行，但对已存在的违反约束的数据行不做验证
• disable novalidate：可以添加违反约束的数据行，对已经存在的违反约束的数据行也不做验证

删除约束

alter table t2 drop constraint uq_user_email cascade;

十九、DML

数据控制语句 操作数据库对象中包含的数据

• insert：向数据表中插入一条记录
• update：修改已经存在和表中的记录的内容
• delete：删除数据表中的一条或多条记录