数据库基础

关系型数据库：oracle、mysql

create user lao identified by 123456 account unlock //创建用户且设置状态

grant connect to lao   //赋予登录权限

//赋予资源权限
grant resource to lao  //资源权限允许该用户创建自己的数据库对象、表格

grant dba to lao    //赋予管理员权限

revoke resource from lao;     //收回权限
revoke connect from lao;      // dba 权限无法撤回

alter user scott identified by 123456   //修改用户密码

表格的组成

基本组成：字段、字段的值、表名

字段的数据类型：number(数字类型)、char、varchar2、date.字段在被定义数据类型后不能再存其他数据类型的数据。

• number 类型在定义的时候可以同时限制数据的长度
  例1：number(4)：该字段可以最多存放4位整数；
  例2：number(7，2)：共7位，小数位占2位，整数部分5位。最大值：99999.99

• 字符类型

• char:定长类型，长度相等，牺牲了时间，提高了传输效率
  char(20)：数字规定可输入字符的大小，不满的部分自动填充空格。

• varchar2:动态长度类型，长度不等，节省了空间，牺牲了传输效率 varchar2(20):数字规定可输入字符的大小，由字段值的实际大小决定。

• date:文本类型

• 表包含的字段 三大范式 1.表的每个字段保证原子性（不可再分） 2.表的每个字段都和主键直接相关。（主键是以中国字段的约束，被设置为主键的字段有三个特点。1.值不能为空。2.值不可重复。3.一张表中只能有一个主键） 3.表和表之间只存关系，不存数据

例如：图书馆

• 图书表：图书ID、图书名称、作者名称、图书价格、出版社ID（图书ID是主键，每一项要与其有直接联系）
• 出版社表：出版社ID、出版社名称、出版社电话、出版社地址
• 图书-分类关系表：图书ID、类别ID
• 类别表：类别ID、类别名称、类别介绍
• 学生表：学生ID、姓名、性别、班级
• 借阅记录表：ID（本次借阅事件的ID）、图书编号、学号、借阅时间、应还时间、实际归还时间、状态、应缴纳滞纳金额
• 后台管理用户表：ID、姓名、密码

//创建表格t
create table t(
tid number
)

alter user scott account unlock   //设置账户状态

---

表的CRUD

创建表

drop table 名称  //删除表

create table student(
snu number(11) primary key,
sname char(20) not null,           //不能为空
gender char(3),
classnum number(4,0),             //能用number型就不要用char型
addr varchar(300),
age number(2),
enrolldate date
)

---

• 添加约束

alter table student add constraint ok_stu(约束名称) check(gender='男' or gender='女')；

• 删除约束

alter table 表名 drop constraint 约束名 

• 添加唯一约束

alter table student add constraint un_stu unique(sname)；

• 添加非空约束

alter table student add modify (sname not null);

• 添加默认约束

alter table student modify (addr default '地址不详')

• 添加外键约束

alter table 表名 add constraint 约束名 foreign key(字段名) references employee(字段名);

• insert语句 插入语句
• 全字段插入(按照你建表的顺序进行插入)

insert into student values(1,'qinyu','男','101','扬州','22',syadate)

select * from student;  //显示表格内现有数据

• 指定字段插入

insert into student(sno,sname,gender,classnu,ago,enrolldate)
values(2,'guhao','男',102,18,sysdate)

• 指定日期插入

insert into student values(3,'sun','男',103,'南京','22',
to_date('1998-5-20','YYYY-MM-DD'));
//利用to_date函数，将字符串转换成指定日期格式的日期类型数据

• 修改：使用update修改字段值

update 表名 set 想修改的字段 = 修改的值 where 你的条件
//例1：
update student set gender = '女' where sno = 1;

//例2：
update student set gender = '女' where addr='南京' and age=22;

//例3:将不是101班的女生改为25岁的男生
update student set gender = '男',age=25 where gender = '女' and classnum!=101;

• 修改/增加：用alter修改字段属性（字段名称慎重修改）

//增加一个字段
alter table student add gpoint number(2,1);

//删除一个字段
alter table student drop column gpoint;

//重命名字段
alter table student rename column gpoint to soorepoint;

//修改字段数据类型
alter table student modify soorepoint number(4,1);

• 删除

例如:删除所有qinyu的记录
delete from student where sname = 'qinyu'

delete from student   //删除全表内容

truncate table student//删除全表内容，不建议使用

---

数据库语句：DDL/DML

• DDL:会及时提交到数据库。 例：create drop alter truncate
• DML:不会及时提交到数据库,存在日志文件中，属于一次绘画，在程序员运行commit之前不会被提交到数据库。 例：update

DML语句的存在避免大量数据同时提交数据库，降低效率，日志文件在commit每次提交到数据库后都会被清空。

---

ACID

• 原子性:事务中的SQL语句要么全部提交，要么全都不提交

savepoint        //两者都存在日志文件中，commit后会被清楚
rollback         //回退点

例如：

savepoint A;
delect from student where sno=1;
delect from student where sno=2;
savepoint B;
delect from student where sno=2;
rollback to A；
commit;                        //commit一定要位于回退指令后，否则数据将被清空

• 一致性：从不同的客户端查询的数据是一致的
• 持久性：所有的数据操作对数据库的影响是永久的
• 隔离性：在某一个事务操作一个数据的时候，如果另一个事务也想操作这个数据，要么在前一个事务操作之前，要么在之后。

---

外键约束

• 父表

create table student(
Sno varchar2(3) primary key,
Sname varchar2(8) not null,
Ssex varchar2(3) not null,
Sbirthday Date,
SClass varchar2(5)
)

• 子表

create table scores(
scid number primary key,
term varchar2(2) check(term = 's1' or term = 's2'),
sno number(11) not null,
examno varchar2(7) not null,
writtenscore number(4,1) default 'o' not null,
lascore number(4,1) default 'o' not null,
 //提供外界约束,将此表的sno和另一张名为student的sno关联，student表是父表，scores是子表
constraint fk_scores_student_stuno foenugn key(sno)
references student(sno)        
)

• 向子表插入数据：建立外键约束之后，子表想要插入数据，关联字段必须在父表中可以找到对应的值

insert into scores values(1,'s1',2,'高数',59,5,59,5)  //此时如果学生表中没有2号学生，则插入数据失败

• 删除记录:在父表中如果在子表中有对应子记录，则父表中不可以直接被删除。即要先将所有子表中相关的记录删除后才可以删除记录，也不可以直接删除父表。

delete from student where sno = 4;

---

查询（research）

select * from emp                        //*表示任意匹配，即全部内容
select ename,sal from cmp;               //ename为要查找的内容，查看对应表时在此句的 from后添加表名.cmp

实际开发中查询语句可能会增加服务器压力，慎重使用。

select ename 姓名 from emp    //

select sal * 12 年薪 from emp  //查询时将sal字段名称显示为年薪，但实际不改变表格内容

select empno 员工编号,ename 姓名,sal/24 日薪 from emp    //查询员工日薪

select distinct(deptno) from emp;//查询公司中所有部门编号,去重

• nvl 语句：空值处理

//查询所有人的奖金情况
select ename,com from emp;  //查询奖金状况，有些人的为空值
select ename,nvl(comm,0) from emp;    //空值处理
select ename,nvl2(comm,comm+10000,0) from emp;   //非空值加10000，空值即显示为0

• || 字符串连接符

select ebame||'的薪水情况为'||sal from emp;    //字符连接

练习：查询出所有员工的工资情况（工资=sal+comm） 字段显示效果：每个人的工资情况

select ename||'的薪水情况为'|| (sal+nvl(comm,0)) 每个人的工资情况 from emp;

• 条件语句：where 例：查询除了SMITH以外所有人

select * ename emp where ename != 'SMITH'

select * from emp where sal<2000    //查询所有薪水小于2000的员工信息

//查询所有薪水大于等于2000且小于等于3000的员工信息，取边界值
select * from emp where sal>=2000 and sal<=3000    //方法1
select * from emp where sal between 2000 and 3000  //方法2

//查询所有工资时3000或5000的员工信息
select * from emp where sal = 3000 or sal = 5000   //方法1
select * from emp where sal in(3000,5000)     //方法2，实际应用尽可能避免

//查询所有job为salesman，且工资大于2000，并且部门编号不为10的所有员工信息
select * from emp where job = 'SALESMAN' and sal >2000 and deptno != 10

//查询所有入职时间超过39年的员工
select * from emp where to_char(sysdate,'YYYY') - to_char(hiredate,'YYYY')>39 //用系统时间减入职时间

to_char//时间转换,把日期格式转换成字符串

select to_char(sysdate,'YYYY') from emp;  //取出emp中所有入职年份

• 模糊查询（like）

% :匹配0个或1个或多个字符 _ ：匹配一个字符

select * from emp where ename like 'E%'//查询所有姓名开头为E的员工信息

select * from emp where ename like '_E%' //第二个字母为E，后面还有其他字母

select * from emp where ename like '_A%M__'   //姓名第二个为A，倒数第三个为M的人

• 如果在使用模糊查询时出现特殊字符，则需要加入转义字符 / 并且用escape声明
• escape 原则上可以赋予任意字符特殊含义

例如含有%的名字
select * from emp where ename like '%/_/%/_%' escape '/'

//练习1：查询所有姓名第一个字符为_，第二个字符为%的所有员工信息
select * from emp where ename like '/_/%%' escape '/'
//联系2：查询所有姓名倒数第二个字符为/的员工信息
select * from emp where ename like '%//%' escape '/'

• 单行函数：nvl、nvl2、to_date、to_char

• 多行函数：avg、sum、max、min、count

 //查询所有员工的平均薪水，薪水综合，最高薪水
select avg(sal),sum(sal),max(sal),min(sal) from emp;

• count 统计的是指定字段下所有非空值的数量

//统计emp这个表的行数，不考虑是否非空
select count(*) from emp; 
select count(1) from emp;  //此行执行效率更高   

select 70*70 from emp;  //结果会将70x70的结果输入每一行
select （70*70） from emp;

• avg

//部门编号为10的员工的平均工资
select avg(sal) from emp where deptmo = 10;

• union

//查询各部门平均工资
select avg(sal) from emp where deptno = 10
union
select avg(sal) from emp where deptno = 20
uinon
select avg(sal) from emp where deptno = 30;

• minus

// 显示除了第10部门以外的员工信息
select * from emp
minus
select * from emp where deptno = 10;  

//将部门30里是CLERK职位的人去掉
select * from emp where job = 'CLERK'
minus
select * from emp where deptno = 30;

//对于两个完全无交集的字段，minus后哪个放在前面就显示哪个
select * from emp where deptno = 10;
minus
select * from emp where deptno = 20;

• 分组查询 groupby: 将后面跟的字段相同的分成一组，分组后select后的检索字段只可以显示avg、sum、max、min、count。因为数据在经历分组后从行数据变成了组数据，只能查询与组数据相关的字段，即以组为单位。

//计算各部门平均工资
select deptno,avg(sal) from emp group by deptno

//用来查询字符时，比较的是ASCII码的大小
select max(ename),avg(sal),deptno from emp group by deptno;

• where:对分组前的数据进行筛选
• having：对分组后的数据进行筛选

//查询各个部门中，平均薪水大于2000的部门，以及其平均薪水
select deptno,avg(sal) 平均薪水 from emp group by deptno having avg(sal)>2000;

• order by:查询并排序(默认升序：asc)

select * from emp order by sal asc；   //升序
select * from emp order by sal dasc；  //降序

• 并列排序

// 查询所有员工信息，sal进行升序排序，如果sal相同，按照comm进行降序排序
select *from emp order by sal asc,comm desc;

---

语句执行顺序

• 1、from ：对分组之前的数据进行筛选
• 2、where ：对分组后的数据进行筛选
• 3、group by ：对数据进行分组
• 4、having ：对分组后的数据进行筛选
• 5、select :决定了从哪张表中获取数据，从筛选之后的查询结果中，选择指定的字段进行显示
• 6、order by： 排序

---

• 子查询：将一个语句的查询结果作为另一个语句的查询条件

// 查询所有工资比SMITH高的员工信息
select * from emp where sal > (select sal from emp where ename = 'SMITH');

//查询所有工资比部门10平均工资高的员工信息
select * from emp where sal > (select avg(sal) from emp where deptno = 10);

//查询所有工资和任意一个职位平均工资相等的员工
select * from emp where sal in (select avg(sal) from emp group by job);

• rowid:表内的每个信息都有一个唯一的id

//创建表格
create table sgy(
sid number,
name char(20)
);

//插入重复的数据
insert into sgy values(1,'11');
insert into sgy values(1,'11');
insert into sgy values(1,'11');
insert into sgy values(2,'222');
insert into sgy values(2,'222');
insert into sgy values(2,'222');

//方法1：查询表内信息，并且在查询信息的时候不显示重复信息
select sid,max(name) name from sgy group by sid;

//方法2：联合字段分组
select sid,name from sgy group by sid,name;

//方法3：使用rowid，去除查询时重复的数据。从每一组中抽取一个rowid,max 和 min 都行
select * from sgy where rowid in (select max(rowid) from sgy group by sid);

---

• rownum：伪列。

//查看emp表的rownum
select rownum,e.* from emp e;

//查询emp表的前十行记录
select * from emp where rownum< 11;

//在使用比对时，只要出现一次不满足就会停止比对，无法正常使用大于号>。
select * from emp where rownum >= 1;       //便利所有数据
select * from emp where rownum > 1;        //没有数据

//查询emp表的第六行到第十行记录,将rownum的值赋予一张新的表作为普通数值
select * from (select rownum r,e.*from emp e)a where a.r>5 and a.r<10;

//查询emp表中工资前十高的员工信息
select * from (select * from emp order by sal desc) where rownum < 11; //截取前十行作为一张表

//查询emp表中工资第6到第10高的员工信息，嵌套表数据
select * from
(select rownum r,a.* from 
(select * from emp order by sal desc)a)b
where b.r>5 and b.r<11;

---

多表

笛卡儿积

在表和表的笛卡尔积关系中，表作为集合，表中的每一行数据作为笛卡尔积的元素，但数据只是单纯遍历显示，无任何有效整合和匹配。

• 将表emp，和表dept中的所有数据都列出来

select * from emp,dept;   

• 将表的内容按照部门对应整合（笛卡尔积方式）

select e.ename,e.sal,d.dname
from emp e,dept d where e.deptno=d.deptno     

此时只能用笛卡尔积方式查询，而不能用子查询，子查询只能用于单表查询

• 创建新表与表emp、dept关联

create table footteam(
fid varchar2(1)
);
insert into footteam values('a');
insert into footteam values('b');
insert into footteam values('c');
insert into footteam values('d');

• 使用笛卡尔积列出所有球队主客场踢球的所有情况

select * from footteam f1,footteam f2 where f1.fid != f2.fid

• 使用笛卡尔积列出所有球队单场制踢球的所有情况（要考虑本队不会和本队踢）

//大于或者小于都可以，比较的是ASCII码
select * from footteam f1,footteam f2 where f1.fid > f2.fid  

92版本SQL笛卡尔积

select * from emp e,dept d where
e.deptno=d.deptno

97版本SQL笛卡尔积

//inner join：忽略存在null的行，只显示一一对应的行数据
select * from emp e inner join dept d on
e.deptno=d.deptno

//left jion：以左表数据为准，强制显示所有左表内容，如图1
select * from emp e inner jion dept d on
e.deptno=d.deptno

//right jion：以右表数据位准，强制显示所有右表内容，右表没有与其相匹配的内容则显示为null，如图2
select * from emp e right join dept d on
e.deptno=d.deptno

//full outer join:显示所有内容，包括null
select * from emp e full outer join dept d on
e.deptno=d.deptno

//cross join：等价于笛卡尔积但不匹配数据
select * from emp e cross join dept d;
//加where后自动匹配数据
select * from emp e cross join dept d where e.deptno=d.deptno

• 图1 
• 图2 

建立表的副本

只复制数据和数据类型，不会获取相应约束

create table emp1 as select * from emp

三表关联

create table food(
loc varchar2(13),
food varchar2(20)
);
insert into food values('NEW YORK','小汉堡');
insert into food values('DALLAS','肋排');
insert into food values('BOSTON','龙虾');
insert into food values('CHICAGO','羊肉');

• 查询员工SMITH的food

//方法1
select e.ename,e.sal,d.deptno,d.loc,f.food 
from emp e join dept d on e.deptno = d.deptno 
join food f on d.loc=f.loc;

//方法2
select e.ename,e.sal,d.deptno,d.loc,f.food
from emp e,dept d,food f where  e.deptno = d.deptno 
and d.loc=f.loc;

• 方法1更高效，因为方法2的from后要遍历emp、dept、food三个表的所有内容再进行筛选，实际使用尽量避免。
• 在实际应用时，为提高效率，笛卡尔积的使用关联一般不会超过5张表，会以增加冗余的方式在相应的表中增加无关数据。

子查询 （相关子查询和不 相关子查询）

create table a(
aid number,
aname char
);
insert into a values(1,'A');
insert into a values(2,'B');
create table b(
bid number,
bname char
);
insert into a values(1,'C');
insert into a values(2,'D');
insert into a values(3,'E');

• 不相关子查询（括号内的子查询语句不需要依赖别的表数据，可独立运行）

select * from b where bid in(select aid from a)

• 相关子查询

select * from b where exists(select aid from a where a.aid=b.bid)

用相关子查询时，子查询语句不能单独运行，所以在涉及另一张表的数据时，语句会自动向外面的表找对应数据，暗中读入了外面的表的相关数据。实际应用中不建议使用。

• 使用join必须要配on，除了cross join，可以用1=1进行结构拼凑

select * from a join b on 1=1

---

虚表

临时显示系统时间：

select sysdate from dual;

显示字符ASII码：

select ascii('a') from dual;

小写字母转大写字母：

select upper('a') from dual;

大写字母转小写字母：

select lower('A') from dual;

去除字符串左边的空格：

select ltrim('        aaaa') from dual;

去除字符串右边的空格：

select rtrim('aaaaa       ') from dual;

select upper('abc') from dual;

显示该月份最后一天

select last_day(sysdate) from dual;
//计算
select last_day(sysdate) - sysdate from dual;

select length('abcd') from dual;

---

常用数据库对象

同义词

• 创建用户a，在a用户下创建一个scoot用户的emp的同义词.除了管理员用户外，所有用户创建数据库对象，必须由管理员进行权限赋予

// 在管理员用户下创建新用户a，并赋予权限
create user a identified by a;
grant resource,connect to a;
grant create SYNONYM to a;

• 创建同义词

//创建该表后，a用户不能正常查询到该表，只有scott用户赋予权限才可以访问，因为原表属于scott
create SYNONYM MyEmp FOR SCOTT.EMP

//用户scott赋予权限给用户a，赋予全部权限
grant all on emp to a  

//用户a在MyEmp修改表数据时，会同时影响到原表emp
update MyEmp set ename='smith' where ename='SMITH'

• 删除同义词(关键字一般用大写)

DROP SYNONYM MYEMP;

---

视图

作用 ：1、存放一段SQL语句，简化复杂的查询语句。 2、屏蔽部分字段，保证数据安全性。 3.保护基表信息，即字段的来源表。 在创建视图时

• 在管理员用户赋予创建视图权限

grant create view to scott

• 在scoot创建视图

create or replace view emp_scott
as
select ename,sal,deptno from emp;

• 查看视图内容

select * from emp_scott;

• 为了保证基表数据的安全，在创建视图时添加只读语句

create or replace view emp_scott
as
select ename,sal,deptno from emp
with read only;

• 系统自带视图

all_tables
all_users

---

序列

• 创建序列（1）

CREATE SEQUENCE a_seq
start with 1
increment by 1
maxvalue 20
minvalue 1
nocycle          //没有循环，读到最大值后无法再继续读取nextval
nocache

//利用虚表查看当前值和下一个值，必须先运行nextval再运行currval。运行nextval时不能超过最大值
select a_seq.nextval from dual;
select a_seq.currval from dual;

• 创建序列（2）

CREATE SEQUENCE a_seq
start with 5
increment by 1
maxvalue 20
minvalue 1
cycle           // 当读取nextval，读到最大值20的下一个会从1再重新开始
nocache

• 创建一个序列，初始值为1，增长间隔为1，不循环，无最大值，无最小值，创建一个表,向表中插入五条数据，使用序列生成的值代替主键插入。

CREATE SEQUENCE a_seq
start with 1
increment by 1
nomaxvalue 
nominvalue 
nocycle
nocache


create table abc(
sid number primary key,
sname varchar
)；

insert into abc values(a_seq.nextval,'aa');
insert into abc values(a_seq.nextval,'aa');
insert into abc values(a_seq.nextval,'aa');
insert into abc values(a_seq.nextval,'aa');
insert into abc values(a_seq.nextval,'aa');

• 删除序列

drop SEQUENCE a_seq

---

索引

索引基于字段 以emp表为例，对ename字段建立索引，即提前按照一定的规则对该字段的数据进行预排序，当用户使用建立了索引的字段进行查询时，通过读取数据的rowid直接定位到指定行从而提高查询效率。 建立索引的前提 1.ORACLE在建表的时候会自动为主键和唯一键建立索引 2.在建立索引时，字段中的重复值越多，索引的效率越低 3.考虑表数据的CRUD对索引的影响。即每次对表数据进行CRUD时，都要重新建立索引。 4.数据量越大，索引的效率越高。

创建索引

• 1、普通索引

create index index1 on emp1(ename)

• 2、唯一索引

create unique index index2 on emp1(edeptno)

---

表空间

1、系统表空间

select distinct(tablespace_name) from all_tables

2、TEMP表空间

临时表空间，安装数据库时创建（相当于高速缓存），存放临时计算数据

3、用户表自定义表空间

用户可以通过 create tablespace 命令

CREATE TABLESPACE lssspace
DATAFILE '/root/lss1.ora' SIZE 10M,
'/root/lss2.ora'SIZE 5M
EXTENT MANAGEMENT LOCAL      --本地存储分配
--UNIFORM SIZE 1M(AutoALLocate)

• 扩充表空间

ALTER TABLESPAcE lssspace
ADD DATAFILE '/root/lss3.ora' SIZE 10M

在虚拟中查看 cd /root 下的目录

• 创建新表并且指定表空间

create table lss(
lid number,
lname varchar2(20)
)
tablespace lssspace;

//查看表空间
select * from all_tables where table_name = 'LSS'

• 在管理员用户下创建新用户lsss，并且给该用户指定表空间，即该用户创建的所有表都存在该表空间下。便于数据库的迁移和备份，但在数据库层未对表空间做任何操作的情况下，不能在物理层（数据库所位于的操作系统中）做任何修改，此举会对数据库造成损坏。

create user lsss identified by 123456
default tablespace lssspace

4、用户默认表空间（users）,创建用户时不指定表空间则存入默认表空间