数据库基础
2017-12-15 本文已影响219人
果芽软件
一、环境搭建
1、安装软件
- 安装oracle_client
- 安装plsql
2、配置数据库连接信息
新建文件：
C:\Oracle\Ora81\network\ADMIN\tnsnames.ora
文件内容：
aliyun_oradb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 120.132.0.117)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME= CCSDB1)
)
)
3、配置plsql
plsql封装了oracle_clent
plsql——>工具——>首选项——>连接：
选择：1. oracle主目录名；2. OCI库
image.png
4、登录数据库
- 数据库：tnsname（本质是变量参数化，包含IP、端口、实例名）
- 用户名
-
密码
image.png
一、对象
- 表
- 数据
二、操作
- 增
- 删
- 改
- 查
三、表的基本元素
-
表
- 表名
- 归属用户（所有的表，都有一个归属用户o用户owner，使用用户c用户customer）
- 注释（comment on table表名 is ‘注释内容’）
- 表空间tablespace
-
列
- 列名
- 类型（varchar2、number、date）
- 是否可为空（not null）
- 默认值（default 值）
- 注释（comment on column 表名.字段名 is ‘注释内容’）（工作中遇到没加的重要字段，尽量补上）
-
字段约束（键）
- 主键（不为空，不重复，给该字段自动生成索引，primary key ）
- 唯一（不重复，空可以出现1次，unique）
- 外键（该字段的值，必须在指定外部表中存在，foreign key）
（选课表的课程号加外键，外键指向课程表的课程号字段，insert到选课表的时候，会拿课程号到课程表查询，查不到，不让加）
- 索引（index）
- 主键索引（默认的，不用写）
- 普通索引
（考虑弊端：增删改，优势：查。如何使用，被动使用，主动使用）
- 权限（o用户：owner所有权用户、c用户：customer使用客户用户；grant）
把表的增删改查权限有选择性的赋权给其它用户（比如程序连接的数据库用户名）
归属用户
- 创建表的用户，也即o用户，owner
- 命名：devxxx1o、devxxx1c；tstxxx1o、tstxxx1c；proxxx1o、proxxx1c
表空间tablespace
- 表都是建立在表空间上的，类似windows的c盘、d盘、e盘
- 建表的时候，一要选用户，二选表空间
- 磁盘监控（linux剩余空间、内存剩余量、数据库表空间）
- 空间满了处理方式1：统计数据库对象大小、删记录（delete无效，truncat降水位线）
- 空间满了处理方式2：加空间
日期
| 变量类型 | 变量名 | 变量值 |
|---|---|---|
| varchar2 | name | guoyasoft |
| number | age | 23 |
| date | 6个变量 | 封装成对象 |
时间内容：
| 内容 | 变量名 |
|---|---|
| 年 | yyyy |
| 月 | mm |
| 日 | dd |
| 时 | hh或者hh24 |
| 分 | mi |
| 秒 | ss |
取值函数：
- to_char(date,'日期格式')
- to_date(日期字符串,'日期格式')
--查询在1987年及以后入职的员工
select * from emp t where to_char(t.hiredate,'yyyy')>='1987';
--查询在2月份入职的员工
select * from emp t where to_char(t.hiredate,'mm')='02';
--查询员工信息，入职日期按照yyyy-mm-dd hh:mi:ss格式显示
select to_char(t.hiredate,'yyyy-mm-dd hh:mi:ss') from emp t ;
--新增一条记录，员工入职日期是2017年12月15日
insert into emp(empno,hiredate)values(1000,to_date('2017-12-15','yyyy-mm-dd'));
系统当前时间：
sysdate
虚拟字段和空表
## student表没有classno，可以在返回结果中虚拟添加一个字段
select t.* ,'1710' classno from tb_student t ;
### 空表dual
select '1710' classno from dual;
select sysdate from dual;
select to_char(sysdate,'yyyy"年"mm"月"') from dual;
默认值
- 固定默认值：字段定义后面加default 默认值
- 变动的值：比如ID（序列sequence生成唯一值，触发器trigger在insert的时候触发取序列的值，并赋值给触发字段上）
-- Create sequence
create sequence S_T_CO_BATCH_DETAIL
minvalue 1
maxvalue 9999999999999999999999999999
start with 10001
increment by 1
cache 10;
CREATE OR REPLACE TRIGGER "TIB_T_CO_BATCH_DETAIL" BEFORE INSERT
ON "T_CO_BATCH_DETAIL" FOR EACH ROW
DECLARE
INTEGRITY_ERROR EXCEPTION;
ERRNO INTEGER;
ERRMSG CHAR(200);
DUMMY INTEGER;
FOUND BOOLEAN;
BEGIN
-- COLUMN ""ID"" USES SEQUENCE S_T_CO_BATCH_DETAIL
SELECT S_T_CO_BATCH_DETAIL.NEXTVAL INTO :NEW."ID" FROM DUAL;
-- ERRORS HANDLING
EXCEPTION
WHEN INTEGRITY_ERROR THEN
RAISE_APPLICATION_ERROR(ERRNO, ERRMSG);
END;
运行顺序：
- 程序员写insert语句
- oracle解析成执行指令
- 执行之前，触发定义的触发器，触发器取序列的值，并替换执行指令id的值
- oracle执行新的指令（所以不管sql写不写id，都没用）
索引
-
不加索引
1000万条数据，查询的时候，先把1000万条数据的全部记录的所有字段都取出来，从第1条查询到最后1条
select * from tb_student t where t.age=28; -
加索引（age字段加索引）
- 先把age字段的全部值和记录对应的地址取出来，生成一个index对象
- 把取出的键值对值，生成一个b-tree树
2的n次方
image.png
image.png
权限、赋权、同义词和dblink
同一个数据库
tstccs1o和tstccs1c两个用户
- o用户建了一张表：tb_student
- o用户给c用户赋权：grant insert,update,select,delete on tstccs1c;
- c用户就可以查询tb_student表了，select * from tstccs1o.tb_student t ;
- c用户建一个同义词synonyms
create or replace sysnonyms tb_student for tstccs1o.tb_student;
select * from tstccs1o.tb_student t ;
简化成：
select * from tb_student t ;
``
### 不同的2个数据库
数据库1的tstccs1o，tstccs1c两个数据库
1. o用户建了一张表：tb_student
2. o用户给c用户赋权：grant insert,update,select,delete on tstccs1c;
3. c用户就可以查询tb_student表了，select * from tstccs1o.tb_student t ;
4. c用户建一个同义词synonyms
create or replace sysnonyms tb_student for tstccs1o.tb_student;
select * from tstccs1o.tb_student t ;
简化成：
select * from tb_student t ;
- 数据库2去访问数据1的tb_student
创建dblink，把数据库1的ip、端口、实例名、用户名、密码打包成一个别名
登录后，找到tstccs1c用户的tb_student表
create /* public */ database link orcl
connect to dbusername identified by dbpassword
using '(DESCRIPTION =(ADDRESS_LIST =
(ADDRESS =(PROTOCOL = TCP)
(HOST = 192.168.0.1)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';
select * from tstccs1c.tb_student@orcl
再取个同义词
create or replace sysnonyms tb_student for tstccs1c.tb_student@orcl;
数据库2，访问数据库1的tb_student，就可以像自己的表一样用
select * from tb_student ;
四、数据的基本元素
image.png
1. 组合因子
- 视图：可以是表、可以是sql处理结果
- 操作关键字（对oracle，1个关键字，就是一个处理方法）
- from
- join on
- where
- group by
- having
- select ：rownum、rowid
- top
- order by
2. 组合规则
- 把1个视图传给操作方法，操作方法处理，再输出一个视图
- 操作方法可以套接成串执行，但是有固定的先后顺序
- sql和sql的结果可以做加减运算（minus、union、union all、intersect）
五、表的增删改查
- 增：create table 表名（属性）
## 正常的建表
create table t1(字段)values(值)
## 第2种，根据SQL建表（不要数据，字段也可以指定）
create table t2
as
select * from t1 where 1=2 ；
## 第3种，根据已有的表建表（要指定数据，一般用于备份数据）
create table t3
as
select * from t1 ；
- 删：drop table 表名；
- 改：alter table 表名
- 查：select * from user_tables t ;
六、数据的增删改查
- 增：insert into 表名（字段名，字段名）values（字段值，字段值）；
- 删：delete 表名 where 条件；（或者全删 truncat table 表名，这个会释放空间降低水位线）；
- 改：update 表名 set 字段名=字段值，字段名=字段值 where 条件
- 查：select * from 表名 where 条件 ；
连接
image.png
- 内连接（B）
- 左连接（A+B）
- 右连接（C+B）
- 全连接（A+B+C）
image.png
内连接
select * from student a, score b where a.sno=b.sno and b.score>=60
select * from student a inner join score b on a.sno=b.sno where and b.score>=60
image.png
左连接
select * from student a left join score b on a.sno=b.sno where and b.score>=60
image.png
右连接
select * from student a right join score b on a.sno=b.sno where and b.score>=60
image.png
全连接
select * from student a full join score b on a.sno=b.sno where and b.score>=60
image.png
SQL组合
image.png
-
minus（A）
sql1
minus
sql2 -
union（A+B+C）
sql1
union
sql2 -
union all（A+2B+C）
sql1
union all
sql2 -
intersect（B）
sq1
intersect
sql2
实践
给定一个学生列表，到数据库统计分析
S001
S002
S004
S005
S006
S007
S008
S0012
S0014
S0015
select * from tb_student t ;
S001
S002
S003
S004
S005
S006
S007
S008
S0011
S009
S0010
## 第1步：创建一个临时表，将列表存入数据库
create table tb_student_wl_1217
as
select * from tb_student t where 1=2;
## 第2步：将列表插入tb_student_wl_1217表
select t.*,t.rowid from tb_student_wl_1217 t ;
## 第3步：统计分析
--数据库有，列表没有
select t.student_id from tb_student t
minus
select t.student_id from tb_student_wl_1217 t;
S0010
S0011
S003
S009
--数据库没有，列表有
select t.student_id from tb_student_wl_1217 t
minus
select t.student_id from tb_student t;
S0012
S0014
S0015
--数据库和列表都有
select t.student_id from tb_student_wl_1217 t
intersect
select t.student_id from tb_student t;
S001
S002
S004
S005
S006
S007
S008
--数据库和列表的合集（去重）
select t.student_id from tb_student_wl_1217 t
union
select t.student_id from tb_student t;
S001
S0010
S0011
S0012
S0014
S0015
S002
S003
S004
S005
S006
S007
S008
S009
八、练习
练习1
1. 查询tb_student表，去掉重复数据
## 不去重
select t.password,t.student_name from tb_student t ;
## 去重distinct
select distinct t.password,t.student_name from tb_student t ;
附带练习：
- F6提示不会的关键字
- 表别名选择字段（一定不要手写字段名）
- 局部注释（手工和功能按钮2种，/**/）
- 按照姓名字段排序
## 不排序
select * from tb_student t ;
## 按姓名排序（正序）
select * from tb_student t order by t.student_name ;
或
select * from tb_student t order by t.student_name asc;
## 按姓名排序（倒序）
select * from tb_student t order by t.student_name desc;
## 按照按照班级倒序、姓名正序排序
select * from tb_student t order by t.sclass desc,t.student_name ;
练习2: 统计学生表中每个班的人数
- 永远都是先把表看懂（有哪些字段，什么含义）
- 写SQL读题
| 序号 | 步骤 | SQL | 读题 |
|---|---|---|---|
| 1 | from | select * from tb_student t | 根据题目中所有用到的字段，查找需要哪几张表 |
| 2 | join on | 判断是否需要左、右、全连接 | |
| 3 | where | 读题，看有没有对字段做限制条件 | |
| 4 | group by | group by t.sclass | 读题，看有没有聚合函数 |
| 5 | having | 读题，看对聚合函数是否有限制条件 | |
| 6 | select | t.sclass,count(*) | 读题，看需要展现的字段 |
| 7 | distinct | 读题，看是否需要去重 | |
| 8 | order by | 读题，看是否需要排序 | |
| 9 | rownum | 读题，看是否需要分页，或限制条数 |
重中之重：
- group by后的视图，只有group by的字段+5个聚合函数，select要展现的字段，只能从group by后面的字段+5个聚合函数中取，*也不能用
- 如果要展现的字段不在group by后面的字段和5个聚合函数内，缺失的字段，再引入新表进行关联
select a.sclass, b.cname, a.scount
from (select t.sclass, count(*) scount
from tb_student t group by t.sclass) a,
tb_class b
where a.sclass = b.cid;
分页查询
- 生成数据视图：查询排序，生成视图1
- 添加行号：最对视图1做查询，添加rownum列，列名rn
- 限制最大行号：对视图2用where限制rownum的最大值，生成视图2
- 限制最小行号：再对视图2做查询，where限制rn最小值
select *
from (select h.*, rownum rn
from (select t.*
from tb_student t
order by t.sclass desc, t.student_name) h
where rownum <= 10)
where rn > 5;
select t.*,rownum rn from tb_student t order by t.sclass desc;
STUDENT_ID PASSWORD STUDENT_NAME RESULT SCLASS RN
1 S009 123456 周武 C004 10
2 S0011 123456 周三1 C004 9
3 S0010 123456 周三1 C004 11
4 S006 123456 孙俪 C003 6
5 S008 123456 周三 C003 8
6 S007 123456 周武 C003 7
7 S005 123456 赵倩 C002 5
8 S004 123456 麻子 C002 4
9 S002 123456 李四 C001 2
10 S001 123456 张三 C001 1
11 S003 123456 王二 C001 3
select的字段会先执行，再执行order by，所有序号是乱的
纠正方式：先排序，将生成的视图传给from，再加一层查询
select h.*,rownum rn from (select t.* from tb_student t order by t.sclass desc) h ;
STUDENT_ID PASSWORD STUDENT_NAME RESULT SCLASS RN
1 S009 123456 周武 C004 1
2 S0011 123456 周三1 C004 2
3 S0010 123456 周三1 C004 3
4 S006 123456 孙俪 C003 4
5 S008 123456 周三 C003 5
6 S007 123456 周武 C003 6
7 S005 123456 赵倩 C002 7
8 S004 123456 麻子 C002 8
9 S002 123456 李四 C001 9
10 S001 123456 张三 C001 10
11 S003 123456 王二 C001 11
深度理解SQL执行顺序
-
行号乱序
select t.*，rownum rn from tb_student t order by t.sclass desc
分析原因：select决定展现字段，此时生成rownum，然后才执行order by，所以乱序 -
where语句rn不能用，order by能用
分析原因：where执行的时候，还没执行select，此时rownum和rn别名都还没处理，所以where用不到
select h.*,rownum rn from (select t.* from tb_student t order by t.sclass desc) h order by rn ;
分析原因：order by再select之后，rownum和别名rn已经处理完成，所以可以直接用
select h.*,rownum rn from (select t.* from tb_student t order by t.sclass desc) h where rownum <=8 ;
select *
from (select h.*, rownum rn
from (select t.* from tb_student t order by t.sclass desc) h
where rownum <= 8)
where rn >= 5
将rn拆分成2个变量，开放给用户选择
--变量1：第几页:2
--变量2：每页多少条：3
--先按照每页条数，统计总记录数，总页数（便于直接查询最后一页）
select count(*),ceil(count(*)/3) from tb_student t ;
--（第几页-1）*每页条数 < rn < 第几页* 每页条数
select *
from (select h.*, rownum rn
from (select t.* from tb_student t order by t.sclass desc) h
where rownum <= 2*3)
where rn > （2-1）*3
image.png
扩展问题
为什么第一个限制行号的地方只能用rownum，用rn却会报错，而外面一层能够用别名rn？
select h., rownum rn
from (select t.
from tb_student t
order by t.sclass desc, t.student_name) h
where rownum <= 10
解答：因为where先执行，select后执行，做where的时候，rn别名还没生效
视图
- sql的每一步，都会生成视图
- 有些复杂的视图，可以把sql存起来，当表用
- 存的类型，就是视图
练习：
创建1个视图
删除1个视图
查询当前用户的所有表
select * from user_tables;
其中user_tables就是系统自带的一个视图，简化用户查询语句，视图的sql
create or replace view sys.user_tables as
select o.name,
decode(bitand(t.property,2151678048), 0, ts.name,
decode(t.ts#, 0, null, ts.name)),
decode(bitand(t.property, 1024), 0, null, co.name),
decode((bitand(t.property, 512)+bitand(t.flags, 536870912)),
0, null, co.name),
decode(bitand(t.trigflag, 1073741824), 1073741824, 'UNUSABLE', 'VALID'),
decode(bitand(t.property, 32+64), 0, mod(t.pctfree$, 100), 64, 0, null),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(t.property, 32+64), 0, t.pctused$, 64, 0, null)),
decode(bitand(t.property, 32), 0, t.initrans, null),
decode(bitand(t.property, 32), 0, t.maxtrans, null),
decode(bitand(t.property, 17179869184), 17179869184,
ds.initial_stg * ts.blocksize,
s.iniexts * ts.blocksize),
decode(bitand(t.property, 17179869184), 17179869184,
ds.next_stg * ts.blocksize,
s.extsize * ts.blocksize),
decode(bitand(t.property, 17179869184), 17179869184,
ds.minext_stg, s.minexts),
decode(bitand(t.property, 17179869184), 17179869184,
ds.maxext_stg, s.maxexts),
decode(bitand(ts.flags, 3), 1, to_number(NULL),
decode(bitand(t.property, 17179869184), 17179869184,
ds.pctinc_stg, s.extpct)),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(o.flags, 2), 2, 1,
decode(bitand(t.property, 17179869184), 17179869184,
decode(ds.frlins_stg, 0, 1, ds.frlins_stg),
decode(s.lists, 0, 1, s.lists)))),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(o.flags, 2), 2, 1,
decode(bitand(t.property, 17179869184), 17179869184,
decode(ds.maxins_stg, 0, 1, ds.maxins_stg),
decode(s.groups, 0, 1, s.groups)))),
decode(bitand(t.property, 32+64), 0,
decode(bitand(t.flags, 32), 0, 'YES', 'NO'), null),
decode(bitand(t.flags,1), 0, 'Y', 1, 'N', '?'),
t.rowcnt,
decode(bitand(t.property, 64), 0, t.blkcnt, null),
decode(bitand(t.property, 64), 0, t.empcnt, null),
decode(bitand(t.property, 64), 0, t.avgspc, null),
t.chncnt, t.avgrln, t.avgspc_flb,
decode(bitand(t.property, 64), 0, t.flbcnt, null),
lpad(decode(t.degree, 32767, 'DEFAULT', nvl(t.degree,1)),10),
lpad(decode(t.instances, 32767, 'DEFAULT', nvl(t.instances,1)),10),
lpad(decode(bitand(t.flags, 8), 8, 'Y', 'N'),5),
decode(bitand(t.flags, 6), 0, 'ENABLED', 'DISABLED'),
t.samplesize, t.analyzetime,
decode(bitand(t.property, 32), 32, 'YES', 'NO'),
decode(bitand(t.property, 64), 64, 'IOT',
decode(bitand(t.property, 512), 512, 'IOT_OVERFLOW',
decode(bitand(t.flags, 536870912), 536870912, 'IOT_MAPPING', null))),
decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N'),
decode(bitand(t.property, 8192), 8192, 'YES',
decode(bitand(t.property, 1), 0, 'NO', 'YES')),
decode(bitand(o.flags, 2), 2, 'DEFAULT',
decode(bitand(decode(bitand(t.property, 17179869184), 17179869184,
ds.bfp_stg, s.cachehint), 3),
1, 'KEEP', 2, 'RECYCLE', 'DEFAULT')),
decode(bitand(o.flags, 2), 2, 'DEFAULT',
decode(bitand(decode(bitand(t.property, 17179869184), 17179869184,
ds.bfp_stg, s.cachehint), 12)/4,
1, 'KEEP', 2, 'NONE', 'DEFAULT')),
decode(bitand(o.flags, 2), 2, 'DEFAULT',
decode(bitand(decode(bitand(t.property, 17179869184), 17179869184,
ds.bfp_stg, s.cachehint), 48)/16,
1, 'KEEP', 2, 'NONE', 'DEFAULT')),
decode(bitand(t.flags, 131072), 131072, 'ENABLED', 'DISABLED'),
decode(bitand(t.flags, 512), 0, 'NO', 'YES'),
decode(bitand(t.flags, 256), 0, 'NO', 'YES'),
decode(bitand(o.flags, 2), 0, NULL,
decode(bitand(t.property, 8388608), 8388608,
'SYS$SESSION', 'SYS$TRANSACTION')),
decode(bitand(t.flags, 1024), 1024, 'ENABLED', 'DISABLED'),
decode(bitand(o.flags, 2), 2, 'NO',
decode(bitand(t.property, 2147483648), 2147483648, 'NO',
decode(ksppcv.ksppstvl, 'TRUE', 'YES', 'NO'))),
decode(bitand(t.property, 1024), 0, null, cu.name),
decode(bitand(t.flags, 8388608), 8388608, 'ENABLED', 'DISABLED'),
case when (bitand(t.property, 32) = 32) then
null
when (bitand(t.property, 17179869184) = 17179869184) then
decode(bitand(ds.flags_stg, 4), 4, 'ENABLED', 'DISABLED')
else
decode(bitand(s.spare1, 2048), 2048, 'ENABLED', 'DISABLED')
end,
case when (bitand(t.property, 32) = 32) then
null
when (bitand(t.property, 17179869184) = 17179869184) then
decode(bitand(ds.flags_stg, 4), 4,
case when bitand(ds.cmpflag_stg, 3) = 1 then 'BASIC'
when bitand(ds.cmpflag_stg, 3) = 2 then 'OLTP'
else decode(ds.cmplvl_stg, 1, 'QUERY LOW',
2, 'QUERY HIGH',
3, 'ARCHIVE LOW',
'ARCHIVE HIGH') end,
null)
else
decode(bitand(s.spare1, 2048), 0, null,
case when bitand(s.spare1, 16777216) = 16777216 -- 0x1000000
then 'OLTP'
when bitand(s.spare1, 100663296) = 33554432 -- 0x2000000
then 'QUERY LOW'
when bitand(s.spare1, 100663296) = 67108864 -- 0x4000000
then 'QUERY HIGH'
when bitand(s.spare1, 100663296) = 100663296 -- 0x2000000+0x4000000
then 'ARCHIVE LOW'
when bitand(s.spare1, 134217728) = 134217728 -- 0x8000000
then 'ARCHIVE HIGH'
else 'BASIC' end)
end,
decode(bitand(o.flags, 128), 128, 'YES', 'NO'),
decode(bitand(t.trigflag, 2097152), 2097152, 'YES', 'NO'),
decode(bitand(t.property, 17179869184), 17179869184, 'NO',
decode(bitand(t.property, 32), 32, 'N/A', 'YES')),
decode(bitand(t.property,16492674416640),2199023255552,'FORCE',
4398046511104,'MANUAL','DEFAULT')
from sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t, sys.obj$ o,
sys.deferred_stg$ ds, sys.obj$ cx, sys.user$ cu, x$ksppcv ksppcv,
x$ksppi ksppi
where o.owner# = userenv('SCHEMAID')
and o.obj# = t.obj#
and bitand(t.property, 1) = 0
and bitand(o.flags, 128) = 0
and t.bobj# = co.obj# (+)
and t.ts# = ts.ts#
and t.file# = s.file# (+)
and t.block# = s.block# (+)
and t.ts# = s.ts# (+)
and t.obj# = ds.obj# (+)
and t.dataobj# = cx.obj# (+)
and cx.owner# = cu.user# (+)
and ksppi.indx = ksppcv.indx
and ksppi.ksppinm = '_dml_monitoring_enabled';
综合练习
1. 题目
学生表(学生id，姓名，性别，分数)student（s_id，name,sex,score）
班级表（班级id，班级名称）class（c_id，c_name）
学生班级表（班级id，学生id）student_class(s_id,c_id)
1.查询一班得分在80分以上的学生
2.查询所有班级的名称，和所有班中女生人数和女生的平均分
2. 核对数据
给定一个学生列表，和数据库的学生表对比差异
