数据库学习

本学期专业课开设数据库系统，之前写程序很少接触此方面，故开学习笔记记录过程，便于知识巩固。

用户创建

DCL(Data Control Language)数据控制语言

创建用户密码

create user c##homework identified by homework;

在Oracle 12c 环境下，使用命令行或是SQL Developer 创建用户失败，错误信息报：
ORA-65096: 公用用户名或角色名无效
65096. 00000 - “invalid common user or role name”
原因及解析：
Oracle 12C 提出了多租户环境（Multitenant Environment）和容器（Container）的概念, 区分了两个层级：
CDB ， Container Database, 数据库容器
PDB, Pluggable Database, 可插拔数据库

在数据库中建立的用户对应以上两个层级：
COMMOM USERS ，普通用户，一般建立在CDB层，用户名需要以 C#或C##开头；
LOCAL USERS , 本地用户，仅建立在PBD层，建立的时候需要指定容器

从上面的错误 “invalid common user or role name” 来看， COMMOM USERS 需要以 C#或C##开头，所有这里的用户名不合规范。

解决方法：
解决方法有两种：
如果确定需要创建在CDB容器中，则在用户名前面加C#或C## （最好是加上C##，部分环境加C#会不行）
如果想保持原用户名，可以将用户创建到PDB容器中。

用户赋予权限

grant create session to c##homework;//授予用户创建session的权限，即登陆权限
grant connect,resource to c##homework；//授予用户connect和resource权限
grant unlimited tablespace to c##homework;//授予用户使用表空间的权限
grant create table to c##homework;//授予创建表的权限
grant drop any table to c##homework;//授予删除表的权限
grant insert any table to c##homework;//插入表的权限
grant update any table to c##homework;//修改表的权限
grant create view to c##homework ; //创建视图权限

CONNECT角色： –是授予最终用户的典型权利，最基本的
ALTER SESSION –修改会话
CREATE CLUSTER –建立聚簇
CREATE DATABASE LINK –建立数据库链接
CREATE SEQUENCE –建立序列
CREATE SESSION –建立会话
CREATE SYNONYM –建立同义词
CREATE VIEW –建立视图
RESOURCE角色： –是授予开发人员的
CREATE CLUSTER –建立聚簇
CREATE PROCEDURE –建立过程
CREATE SEQUENCE –建立序列
CREATE TABLE –建表
CREATE TRIGGER –建立触发器
CREATE TYPE –建立类型

DBA：拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。
RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。
CONNECT：拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。
对于普通用户：授予connect, resource权限。
对于DBA管理用户：授予connect，resource, dba权限。

撤销权限
基本语法同grant,关键字为revoke

查看权限

select * from user_sys_privs;//查看当前用户所有权限
select * from user_tab_privs;//查看所用用户对表的权限

表操作

DDL(Data Definition Language)数据定义语言

操作库

– 创建库

create database db1;

– 创建库是否存在，不存在则创建

create database if not exists db1;

– 查看所有数据库

show databases;

– 查看某个数据库的定义信息

show create database db1;

– 修改数据库字符信息

alter database db1 character set utf8;

– 删除数据库

drop database db1;

操作表

–创建表

create table student(
    id int,
    name varchar(32),
    age int ,
    score double(4,1),
    birthday date,
    insert_time timestamp
);

– 查看表结构

desc 表名;

– 查看创建表的SQL语句

show create table 表名;

– 修改表名

alter table 表名 rename to 新的表名;

– 添加一列

alter table 表名 add 列名 数据类型;

– 删除列

alter table 表名 drop 列名;

– 删除表

drop table 表名;
drop table  if exists 表名 ;

约束条件

添加约束
表格创建成功后，为了确保数据的完整性，我们还需要为表格添加一系列的约束，比如主键、唯一约束、检查约束、默认值、外键等。现在咱们就一一进行讲解。
主键约束：设置主键约束有两种方式，可以在创建表的同时设置主键，也可以在表格创建完成之后在单独添加。
在建表的同时设置主键（以Number字段举例）：

create table StudentInfo(
 ID int not null identity(1,1),--编号
 Number char(10) not null primary key,--学号 主键
 Name varchar(20) not null,--姓名
 Sex char(2) null,--性别
 Age int,--年龄
 Birthday date,--出生日期
 Address varchar(50),--家庭住址
 Class varchar(10)--班级
)

表格创建之后单独添加约束：

alter table StudentInfo add constraint PK_StudentInfo_Number primary key (Number)

这两种方式均可。
如果需要添加多个列作为主键列，则可以使用以下语法：

alter table 表名 add constraint 约束名称 约束 （字段1，字段2，...）

例如：alter table StudentInfo add constraint PK_StudentInfo_Number primary key (ID,Number,Name)

唯一约束：同主键约束一样，唯一约束也可以有两种添加方式。

在建表的时候设置（以ID字段示例）：

create table StudentInfo(
 ID int not null identity(1,1) unique,--编号 唯一键
 Number char(10) not null primary key,--学号 主键
 Name varchar(20) not null,--姓名
 Sex char(2) null,--性别
 Age int,--年龄
 Birthday date,--出生日期
 Address varchar(50),--家庭住址
 Class varchar(10)--班级
)

表格创建之后单独添加约束：

alter table StudentInfo add constraint UQ_StudentInfo_ID unique (ID)

默认值约束：

在建表的时候设置（以Sex字段示例）：

create table StudentInfo(
 ID int not null identity(1,1) unique,--编号 唯一键
 Number char(10) not null primary key,--学号 主键
 Name varchar(20) not null,--姓名
 Sex char(2) null default '男',--性别 默认为男
 Age int,--年龄
 Birthday date,--出生日期
 Address varchar(50),--家庭住址
 Class varchar(10)--班级
)

表格创建之后单独添加约束：

alter table StudentInfo add constraint DF_StudentInfo_Sex default '男' for S

检查约束（check）

某列取值范围限制、格式限制等

格式：check(字段名 like ‘约束条件’) 或者 check（regexp_like（字段名，’约束条件’））

检查性别只能是男或女

–第一种
create table test(
s_id number primary key,
sex varchar2(2) check(sex =’男’ or sex=’女’)
);

–第二种
create table test1(
t_id number primary key,
sex varchar2(2)
);

alter table test1 add constraint chkk check (sex =’男’ or sex=’女’);
alter table test1 add constraint chkk check (sex in(‘男’,’女’));

在一个范围中间

–第一种
create table test2(
t_id number primary key,
age number check(age between 12 and 30)
);

–第二种
create table test3(
t_id number primary key ,
age number
);

alter table test3 add constraint ch_test3 check(age>12 and age<30);
alter table test3 add constraint ch_test3 check(age between 12 and 30);

长度大于等于某个值

–第一种
create table test4(
id number primary key ,
password varchar2(20) check(length(password)>=6)
);
–第二种
create table test5(
id number primary key ,
password varchar2(20)
);
alter table test5 add constraint check_test5 check(length(password)>=6);
以下内容仅写第一种方法（表中check约束）

数大于某个值

create table test6(
id number(10) primary key,
no number(10) check(no>1)
);

只能是8位字符,前两位是0,34位为数字,第 5 位为””下划线,68位为字母

create table test7(
id number(10) primary key ,
password varchar2(10)check((regexp_like(password,’^00[0-9]{2}[_][a-z,A-Z]{3}$’) )and(length(password)=8))
);

insert into test7 values(1,’0012_jaa’);

电子邮箱要含有@符号check

–第一种 like方法
create table test8(
id number(10) primary key,
email varchar2(10) check (email like ‘%@%’)
);
insert into test8 values(1,‘12@126.com‘);

–第二种 regexp_like方法
create table test9(
id number(10) primary key,
email varchar2(10) check ( regexp_like(email,‘@’))
);
insert into test9 values(1,‘12@126.com‘);

用check约束一列的首字母为’s’

–第一种方法 like
create table test10(
id number(10) primary key ,
name varchar2(10) check(name like ‘s%’)
);
insert into test10 values(1,’same’);

第二种方法 regexp_like
create table test11(
id number(10) primary key ,
name varchar2(10) check(regexp_like(name,’s’,’i’) )
);
insert into test11 values(1,’same’);
以下内容仅采用like方法，regexp_like方法由读者自行修改调试

检查约束前3位和后5位均为数字字符: （代码存在问题）–认为思路正确（求help）

create table test12(
id number(10) primary key,
wno varchar2(10)check(wno like ‘[0-9]{3}%[0-9]{5}’)
);
insert into test12 values(4,’12324578’);

增加

-- 写全所有列名
insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
-- 不写列名（所有列全部添加）
insert into 表名 values(值1,值2,...值n);
-- 插入部分数据
insert into 表名(列名1,列名2) values(值1,值2);

删除

-- 删除表中数据
delete from 表名 where 列名  = 值;
-- 删除表中所有数据
delete from 表名;
-- 删除表中所有数据（高效 先删除表，然后再创建一张一样的表。）
truncate table 表名;

修改

-- 不带条件的修改(会修改所有行)
update 表名 set 列名 = 值;
-- 带条件的修改
update 表名 set 列名 = 值 where 列名=值;

未完待续。。。