实习准备——数据库基础复习

大二的时候学的数据库，基础语法学的是sql server，后来蹭了隔壁东软来的老师的课，就学了Oracle，大作业数据库也是用的PL/SQL，前端用的java。先从基础入门开始。
参考https://www.cnblogs.com/kscnchina/p/4570865.html

Oracle数据库基础

分类

• 小型：access, foxbase
• 中型：sql server ,mysql
• 大型：sybase,db2,oracle

用户

• sys是超级用户，具有最高权限，具有sysdba角色，有create database权限，
• system用户是管理操作员，权限也很大，具有sysoper角色，没有create database权限

用户管理一

• 创建用户
  一般是dba才可以创建
  select user 用户名 identified by 密码（密码必须以字母开头）

• 用户修改密码
  dba身份删除某个用户，其他用户去删除就需要有drop user权限
  drop user 用户名 【cascade】
  如果用户已经创建了表就需要加cascade将该用户创建的表一起删除

• 权限
  系统权限：用户对数据库的相关权限，connect，resource,dba等系统权限，例如建库，建表，建索引，建存储过程，登录数据库等。
  用户权限：用户对其他用户的数据对象操作的权限，insert,delete,update,select,all等。

grant select on scott.emp to xiaoming(小明可以查scott的emp表
grant all on scott.emp to xiaoming(小明可以去修改，查询，删除，添加scott的emp表
revoke select on scott.emp from xiaoming(收回权限
grant select on scott.emp to xiaoming with grant option(小明也可以把这个权限传递给别人

with admin option，当收回上级时，下级依然可以拥有权限，但可以显示收回下级权限
with grant option，当收回上级时，下级权限也被收回，不可以显示收回下级权限

Oracle表的管理

• 表名和列名的命名规则
  必须以字母开头，长度不能超过30个字符，不能使用Oracle的保留字

• 数据类型
  char：长度固定，最多2000个字符（char(10) ‘小明’，前四个字符存放小明，后六个自动补齐）
  varchar：长度可变，最多容纳4000（varchar(10）‘小明’只分配4个字符
  clob（character large object）：字符型大对象，最多4g
  number：数字型，-10的38次方到10的38次方，可以表示整数，也可以表示小数，number（5，2）表示数字有五位数，两位小数
  data：日期类型，默认为1-1月-1999
  timestamp：可以精确到毫秒
  blob：二进制数据，可以保存图片或声音，但是真实项目中不会把图片或音频放在数据库中

• 创建表

-- 创建表
create table student(
	xh number(4),
	xm varchar2(20),
	sex char(2),
	birthday date,
	sal number(7,2) --奖学金
）；

--修改表
alter table student add(classid number(2)); --添加一个字段
alter table student modify(xm varchar2(30));--修改一个字段的长度
alter table student drop column sal;--删除字段（不建议做）
rename student to stu;

--删除表
drop table student

--添加数据
insert into student values('a001','张三','男','01-5月-05',10);--每个字段都插入数据
alter session set nls_data_format = 'yyyy-mm-dd' ;   --修改日期默认格式为1996-09-01
insert into student(xh,xm,sex) values('a003','xluna','女');  --插入部分字段
insert into student(xh,xm,sex) values('a004','john',null);--插入空值
select * from student where sex is null; --查询的时候要注意不是=是is
select * from student where sex is  not null;

--修改数据
update student set sex = '女' where xh = 'a001';--修改一个字段
update student set sex = '男',birthday = '1996-09-01' where xh = 'a001'

--删除数据
delete from student where xh = 'a001';    --删除一条记录
delete from student;    --删除所有的记录，但是表的结构孩子，写日志，可以恢复，速度慢
--恢复过程
savepoint a;
delete from student;
rollback to a;  --恢复到保存点a
drop table student;    --删除了表的结构和数据
truncate table student;    --删除表中的所有记录，表结构还在，不写日志，无法恢复，速度快

表查询

--查看表的结构
desc emp;

--查询所有的列
select * from dept;

--表复制
insert into table2(field1,field2,...) select value1, value2,... from table1; --如果名字重复了要添加table.value1

--添加主键约束
alter table tb_dept add constraint tb_dept primary key(deptno);

--统计
select count(*) from emp;

--查询指定的列
select ename,sal,job,deptno from emp;

--取消重复行
select distinct deptno, job from emp;

--nvl函数
nvl(string1,replace_with);--如果string1为null，则函数返回replace_with的值，否则返回string1的值（二者要为同一类型的）

--使用列的别名
select  ename "姓名",sal*12 as "年收入" from emp;

--连接字符串
select ename || 'is a' || job from emp;

--使用where子句
select ename,sal from emp where sal>=2000 and sal<=3000;
select * from emp where empno in (7844,5874,2541,6543);    -使用in

--使用like操作符
--%表示0到多个字符，_表示任意单个字符
select ename,sal from emp where ename like 's%';    --首字母为s的员工的姓名和工资
select ename,sal from emp where ename like '__o%';   --名字的第三个字母为大写的O的所有员工的姓名和工资

Oracle表查询二

--使用逻辑操作符号
select * from emp where (sal>500 or job = 'MANAGE') and ename like 'J%';   --同时满足多个条件

--使用order by（默认升序）
select * from emp order by sal;
select * from emp order by deptno, sal desc; --部门号升序，雇员工资降序排列

--使用列的别名排序
select ename,(sal+nvl(comm,0))*12 "年薪" from emp order by "年薪" asc;

--聚合函数（max,min,avg,sum,count）
select max(sal),min(sal) from emp e;  --两个都必须是分组函数，ename，max(sal)这样不行
select ename,sal from emp where sal = (select max(sal) from emp); --最高工资的人是谁


--group by 和 having 子句
--group by 用于对查询的结果分组统计，having子句用于限制分组显示结果
select avg(sal), max(sal),deptno from emp group by deptno having avg(sal)<2000;  --平均工资低于两千的部门的平均工资和最高工资，注意deptno一定要出现在查询的列表中，不然会报错。
select min(sal),avg(sal),deptno,job from emp group buy deptno, job; --显示美分部门每个岗位的最低工资和平均工资

--分组函数总结

 1. 分组函数只能出现在选择列表、having、order by的子句中（不能出现在where中）
 2. 如果在select语句中同时包含group by, having, order by，那么顺序如上
 3. 在选择列中如果有列、表达式和分组函数，那么这些列和表达式必须要有一个出现在group by子句中。

--多表查询
SELECT  e.ename,e.sal,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno and e,deptno = 10
--自连接（给同一个表起两个名字，然后自己和自己连接）

--子查询
--嵌入在其他sql语句中的select语句
select * from emp where job in (select distinct job from emp where deptno = 10);--和10号部门员工同样工作的职员信息
--使用all操作符
select ename,sal,deptno from emp where sal >all(select sal from emp where deptno = 30);--比所有的30部门的员工工资都高
--使用any操作符
select ename,sal,deptno from emp where sal >any(select sal from emp where deptno = 30);--比任意一个30部门的员工工资高


--在from中使用子查询
当在from中使用子查询时，该查询会被作为一个视图来对待，叫做内嵌视图，当在from中使用子查询的时候必须要给他起名字，但是不要加as，在给列起别名的时候可以加as

--可以使用查询结果创建新表
create table mytable(id, name, sal, job) as select empno,ename, sal,job from emp

--合并查询

 1. union 取得两个查询结果的并集，并且会自动去掉结果中的重复行
 2. union all 不会取消重复行，而且不会排序
 3. intersect 取得两个查询结果的交集
 4. minus 两个查询结果的差集

Oracle分页

1. 根据rowid分页
2. 根据分析函数来分
3. 根据rownum来分

Oracle事务

1. 事务用于保证数据的一致性，它由一组相关的dml语句组成，改组的dml（数据操作语言，增删改，没有查询）语句要么全部成功，要么全部失败。
2. 为了保证事务的并发进行，需要对事务处理的对象加锁，防止其他用户的修改。
3. 当我们commit时可以提交事务，当执行了commit语句后，会确认事务的变化、结束事务，删除保存点，释放锁。当使用了commit语句结束事务之后，其他会话将可以查看到事务变化的新数据。
4. 当执行rollback时，通过指定保存点可以回退到指定的点。
   注意回滚事务必须是没有commit前使用的，如果事务提交了，那么无论刚才做了多少个保存点，都全部被删除了。

savepoint a;--创建保存点
rollback to a;  --回到a的位置

事务四大特性：
原子性：事务被视为不可分割的最小单元，所有操作要么全部提交成功，要么全部失败
一致性：数据库在事务执行前后都保持一致性的状态，在一致性状态下，所有事物对一个数据的读取结果都是相同的
隔离性：一个事物所做的修改在最终提交之前不会被其他事务所见
持久性：一旦事务提交，所做的修改会永远保存到数据库中，即使系统崩溃，事务执行的结果也不会丢失。使用重做日志来保持持久性。