Oracle001- 数据库基础

Oracle001- 数据库基础

---

运行 cmd.exe ==>sqlplus
用户名：sys 密码：manager as sysdba
用户名:scott 密码：tiger（默认） --用此用户操作数据库
注意：可在 sys 下修改 scott 密码：alter user scott identified by 新密码;
注意：在 SQL>提示下登录（conn）： conn sys/manager as sysdba;

---

A、伪列:

select ename,rowid from scott.emp;
rowid :数据对象编号 6 位+文件编号 3 位+块编号 6 位+行编号 3 位（共 18 位，
唯一标识符）
select ename,rownum from scott.emp;
rownum:返回序号
1、查询前 3 行数据：
select ename,rownum from scott.emp where rownum<4;
查询第 3 行数据：
2、select ename,rownum from scott.emp where rownum=3;（错误）
select ename,rn from （select ename,rownum rn from scott.emp）e where
e.rn=3;(正确)

---

B、数据定义语言（DDL）(create/alter/drop) --用于改变数据库结构，包括 创建、更改和删除数据库对象

create table stuInfo /-创建学员信息表-/
(
stuName varchar2(20) not null, --学员姓名,非空（必填）
stuNo char(6) not null, --学号,非空（必填）
stuAge number(3,0) not null, --年龄,非空（必填）
stuID numeric(18,0), --身份证号，代表 18 位数字,小数位数为 0
stuSeat numeric(2,0) --座位号
);
1、更改 stuName 列的定义，使此列能容纳 25 个字符(modify)
alter table stuinfo modify(stuname varchar2(25));
2、向 stuInfo 表添加 stuTel_no 和 stuAddress 两个列
alter table stuinfo add(stuTel_no number(11,0),stuAddress
varchar2(50));
3、从 stuInfo 表中删除列
alter table stuinfo drop (stuTel_no,stuAddress);
alter table stuinfo drop column stuTel_no;
4、删除 stuInfo 表
drop table stuinfo;
5、更改 stuName 列的列名为 name
alter table stuinfo rename column stuName to name;

---

C、数据操纵语言（DML）(insert/update/delete/select) --用于检索、插入 和修改数据

插入数据：
insert into stuInfo values (‘张三’，1，18，null，1);
insert into stuInfo values (‘李四’，2，20，null，2);
insert into stuInfo values (‘王五’，3，15，null，3);
insert into stuInfo values (‘张三’，4，18，null，4);
insert into stuInfo values (‘张三’，5，20，null，5);
操纵表中数据：
1、选择无重复的列（distinct）
select distinct stuname,stuage from stuinfo;(去除重复的列)
2、查询带条件和排序的记录
年龄大于17岁，按姓名升序（asc,默认，可不写），姓名相同，就按年龄降序（desc）
select stuname,stuage from stuinfo where stuage>17 order by stuname
asc,stuage desc;
3、使用列别名(as ,可省略不写)
select distinct stuname as “姓名”,stuage as “年龄” from stuinfo;
4、使用现有的表创建新表（as）
create table stuinfo1 as select * from stuinfo; 整张表复制到新表
create table stuinfo2 as select stuname,stuage from stuinfo;选择 2 列复
制新表
create table stuinfo3 as select stuname,stuage from stuinfo where 1=2;
选择 2 列，只复制表结构到新表
5、查询表中的行数（count）(最好用 1 代替*，效率提升)(最大 max，最小 min，
平均 avg，求和 sum)
select count(*) from stuinfo;
6、取出不重复数据的记录(以 stuname,stuage 分组，group by .（分组依
据）.having .(条件).)
select stuname,stuage from stuinfo group by stuname,stuage having
(count(stuname||stuage)<2);
7、删除指定列的重复行（stuname,stuage 列）
查询重复的记录最大 ROWID：select max(rowid) from stuinfo group by
stuname,stuage having (count(stuname||stuage)>1);
查询不重复的记录 ROWID：select max(rowid) from stuinfo group by
stuname,stuage having (count(stuname||stuage)=1);
联合查询 unoin：select max(rowid) from stuinfo group by stuname,stuage
having (count(stuname||stuage)>1)
union
select max(rowid) from stuinfo group by stuname,stuage
having (count(stuname||stuage)=1);
删除不需要的数据：delete from stuinfo where rowid not in(联合查询结果);
8、查询 SCOTT 所有数据量>10 的表信息
select u.owner,u.num_rows,u.table_name from all_tables u where
u.owner=‘SCOTT’ and u.num_rows>10;
9、修改王五的年龄为 16，身份证账号为 111
update stuinfo set stuAge=16,stuId=111 where stuName=‘王五’;

---

D.事务控制语言（DCL）(commit\savepoint\rollback) --事务是最小的工作单 元，作为一个整体进行工作，称为事务控制

创建 studentInfo 表,如有表，请先删除，删除表指令：drop table studentInfo
create table studentInfo
( id number(2) primary key, --学生 ID
stuName varchar2(12), --姓名
age number(3) --年龄
);
添加数据：
insert into studentInfo values(1,‘张三’,18);
insert into studentInfo values(2,‘李四’,20);
insert into studentInfo values(3,‘王五’,19);
或
insert all
into studentInfo values(1,‘张三’,18)
into studentInfo values(2,‘李四’,20)
into studentInfo values(3,‘王五’,19)
select * from dual;
comimit； --提交数据，执行
select * from studentInfo; --查看数据（3 条数据）
insert into studentInfo values(4,‘小六’,17);
insert into studentInfo values(5,‘大块头’,19); --插入 2 条测试数据
savepoint sp; --设置保存点 sp
insert into studentInfo values(6,‘小周’,15); --再插入 1 条数据
rollback to savepoint sp; --查看数据（已没有学生 ID=6 的数据,返回到
savepoint sp 保存的节点处）
rollback; --查看数据（已没有学生 ID=4,5 的数据，返回到上次 commit 保存
的节点处）

---

E.数据控制语言（DCL）(grant/revoke) --用户提供权限控制命令

赋予用户权限 grant 权限 1，权限 2 … to 用户名;
撤销用户权限 revoke 权限 1，权限 2 … from 用户名;
注意：代表权限的角色：resource/connect/sysdba/…
其他权限：select on 表名/insert on 表名/…
grant read,write on directory dpdata1 to scott; --给 scott 用户赋予在
指定目录的操作权限
grant unlimited tablespace to 用户名; --给用户无限表空间权限

F.SQL 操作符：算数、比较、逻辑、集合、连接操作符等 集合操作符：

1,2,3 UNION(联合) 2,5,8 ====>1,2,3,5,8
1,2,3 UNION ALL(联合所有) 2,5,8 ====>1,2,3,2,5,8
1,2,3 INTERSECT(交集) 2,5,8 ====>2
1,2,3 MINUS(减集) 2,5,8 ====>1,3
select ename,sal from emp where ename like ‘A%’
minus
select ename,sal from emp where ename like ‘%S’
order by sal desc;
连接操作符(||) --连接 2 个字符串，字符串和数字等
select ename||’_’||sal from emp;

---

G.SQL 函数（单行函数、分组函数、分析函数）

1、单行函数：

a、转换函数：

select to_char(sysdate,‘yyyy"年"fmMM"月"fmdd"日" HH24:mi:ss’) 当前时间
from dual; --字符串转为时间
select to_char(1234.5,’$9,999.00’) 数字转字符串 from dual; --数字
转字符串
select to_date(‘2016-11-10’,‘yyyy-mm-dd’) 转换时间 from dual; --时
间转字符串
select to_number(‘100’) from dual; --字符串转数字

b.字符串操作函数

select replace (‘jack and jue’, ‘j’, ‘bl’) from dual; --替换
select instr (‘worldwide’, ‘d’) from dual; --查询
select substr (‘abcdefg’,3,2) from dual; --截取字符串

c、截取时间函数

select extract(day from sysdate) 当前日 from dual; --year 年
select extract(month from date’2011-05-17’) 月份 from dual;

d、数学函数

select mod(10,3) from dual; --取余数
select round(100.256,2) from dual; --四舍五入
select trunc(100.256,2) from dual; --截取

e、聚合函数（C5）

f、其他函数

select ename 姓名,sal+nvl(comm,0) 薪水 from emp; --comm 为空值，
comm=0
select ename 姓名,nvl2(comm,sal+comm,sal) 薪水 from emp; --comm 为
空值，执行 sal；comm 不为空，执行 sal+comm
select decode(to_char(hiredate,‘mm’),‘01’,‘一月’,‘02’,‘二月’,‘03’,‘三
月’,‘04’,‘四月’,‘05’,‘五月’,‘06’,‘六月’,‘下半年’) 入职月份 from emp;
–to_chr(hiredate,‘mm’)是条件，(when…then…)else…

2、分组函数：

select deptno,avg(sal),max(sal),min(sal) from emp group by deptno order
by avg(sal) desc; --按 deptno 分组，查询每组的平均工资，最高工资，最低
工资，而且按平均工资降序排序
select deptno,avg(sal),max(sal),min(sal) from emp group by deptno
having(avg(sal)>2000); --按 deptno 分组，查询每组的平均工资，最高工资，
最低工资，而且平均工资大于 2000

3、分析函数：

select ename,deptno,sal,
rank() over(partition by deptno order by sal desc) rank, --具有相等
值的行排位相同，序数随后跳跃
dense_rank() over(partition by deptno order by sal desc) dense_rank, -
具有相等值的行排位相同，序号是连续的
row_number() over(partition by deptno order by sal desc) row_number -
返回连续的排位，不论值是否相等
from emp;

---

H.分页查询

emp 表，每页 3 条记录，查询第二页（第 4-6 条）
select ename,rn from （select ename,rownum rn from scott.emp）e where rn<=6
and rn>=4;
优化后：select ename,rn from （select ename,rownum rn from scott.emp where
rownum<=6）e where rn>=4;
项目中：–每页条数：pageSize --当前页 currPageNo
select ename,rn from （ select ename,rownum rn from scott.emp where
rownum<=【pageSize*currPageNo】）e where rn>=【(currPageNo-1)*pageSize+1】 ;