Oracle数据库基本操作
–常用的关系型数据库**
oralce mysql db2 sqlServer
–什么是关系型数据库
–基于关系模型提出的数据库
–什么是关系模型:
–由行和列组成的二维数据表
–行:记录
–列:字段
–单元格:值
–数据库的版本
–11g
–数据库的实例:
–orcl
1.连接数据库
sqlplus/nolog
2.连接超级管理员
conn/as sysdba;
3.创建用户
create user hhh identified by ok;
----identified by:指定密码
----注意:拥有dba权限的用户才能创建其他用户
4.登录用户
conn hhh/123
---没有登陆权限
5.返回登录管理员并授予hhh的登录权限
conn/as sysdba
grant create session to hhh;
6.登录用户
conn hhh/123;
7.创建表
create table student(id number(4),name varchar2(10));
---没有建表权限
8.返回登录管理员给hhh建表权限
conn/as sysdba
grant create table to hhh;
9.登录hhh创建表
conn hhh/123
create table student(id number(4),name varchar2(10));
10.登录管理员给用户分配内存
conn/as sysdba
alter user hhh quota unlimited on users
11.登录hhh用户并创建表
conn hhh/123;
create table student(id number(4),name varchar2(10));
12.插入数据
insert into student(id,name) values (1,'hhh');
13.解锁scott账户
alter user scott account unlock;
14.给用户scott dba权限
grant create session to scott;
15.创建表空间
create tablespace test1
datafile 'd:\test1.dbf'
size 10m
autoextend on next 200m
maxsize unlimited;
16.创建临时表空间
create temporary tablespace test2
tempfile 'd:\test2.dbf'
size 20m
autoextend on next 200m
maxsize unlimited;
17.创建用户并指定空间
create user abc identified by 123
default tablespace test1
temporary tablespace test2 quota 5m on test1;
18.修改密码
alter user scott identified by tiger;
19.密码过期
alter user scott password expire;
20.设置账号被锁
alter user scott account lock;
21.解锁账号
alter user scott account unlock;
22.删除用户
drop user cascade;
23.权限
最高权限:dba
创建用户
create user hh identified by ok;
授予登录权限
grant create session to hh;
收回权限
revoke create session from hh;
24.角色
创建角色
create role tr;
给角色授权
grant create session,create table to tr;
通过角色给用户授权
grant tr to hh;
从角色中收回权限
revoke create session,create table from tr;
删除角色
drop role tr;
25.约束
非空约束
create table student1(
sid number(4),
sname varchar2(10) not null,
sage number(4) constraint student1_sage_uq unique,
birth date
)
insert into student1 values(1,'张三',12,to_date('2020-8-31','yyyy-mm-dd'));
--不指定字段所有的值都要添加
--指定具体的字段那么按照指定的字段添加
insert into student1(sid,sname,sage,birth) values(2,'张三',12,to_date('2020-8-28','yyyy-mm-dd'));
select * from student1;
drop table student1;
主键约束
特点:
1.非空性
2.唯一性
create table student1(
sid number(4) constraint student1_sid_pk primary key,
sname varchar2(10) not null,
sage number(4) constraint student1_sage_uq unique,
birth date
)
1.非空性
insert into student1(sid,sname,sage,birth)
values(1,'张三',21,to_date('2020-1-1','yyyy-mm-dd'));
2.唯一性
insert into student1(sid,sname,sage,birth)
values(1,'张三',21,to_date('2020-1-1','yyyy-mm-dd'));
检查性约束
create table student1(
sid number(4) constraint student1_sid_pk primary key,
sname varchar2(10) not null,
sage number(4) constraint student1_sage_uq unique,
weight number(4) constraint student1_weight_ck check(weight between 30 and 50),
birth date
)
insert into student1(sid,sname,sage,weight,birth)
values(1,'张三',21,30,to_date('2020-1-1','yyyy-mm-dd'));
外建约束(foreign key)
create table student1(
sid number(4) constraint student1_sid_pk primary key,
sname varchar2(10) not null,
sage number(4) constraint student1_sage_uq unique,
weight number(4) constraint student1_weight_ck check(weight between 30 and 50),
birth date,
cid constraint student1_cid_fk REFERENCES class(cid)
)
--班级表
create table class(
cid number(4) constraint class_cid_pk primary key,
cname varchar2(10)
)
--删除数据:先删除从表中的数据在删除主表中的数据
--删除表:要先删除从表在删主表
select * from student1 for update;
drop table student1