数据库学习笔记三

1. 数据库表结构的设计

在Oracle数据库中，常用的数据类型包含以下几类：

• 数字类型：涵盖整数和小数，使用number表示。
• 字符串类型：像中文、英文字母、手机号码等，可选用varchar2和char。其中，varchar2是可变长度的字符串，比如设定长度为10，但实际存储3个字符，那么其内容长度就是3；而char是固定长度的字符串，若设定长度为10，即便只存储3个字符，内容长度依然是10。
• 日期类型：例如出生日期、新增时间、修改时间、下单时间等，可使用date（表示年月日）和timestamp（范围有限且与时区有关）。
• 二进制类型：用于存储音频、视频、二进制文件等。
• 大文本类型：诸如.html、.docx、.xml这类文件，使用clob类型存储。

设计表结构时，需要遵循以下步骤：

• 明确表头字段，确定表中所需的各个字段。
• 选择字段的数据类型，根据字段存储内容的特性选择合适的数据类型并设定合理长度。
• 定义字段约束关系，约束主要是对表中插入数据的限制，以此保障数据的准确性和完整性。
• 规划表与表之间的关联关系，根据业务逻辑确定各表之间的联系。
• 遵循SQL相关规范以及范式，确保数据库设计的合理性和高效性。

2. 创建表

创建表的语法为：create table 表名称(字段一 数据类型(长度),字段二 数据类型(长度),...字段n 数据类型(长度))。

例如，设计一张商品表product，包含以下字段：

• 商品编号：id
• 商品名称：name
• 商品的价格：price
• 商品的图片：pic
• 商品的详情信息：info
• 商品上架时间：adddate

根据上述字段，设计出的product表如下：

-- 创建product表
create table product(
    id number,
    name varchar2(50),  -- 商品的名称最长只能写50个字节
    price number(8,2),  -- 价格最长为8位，且小数点后面2位
    pic varchar2(100),
    info varchar2(200),
    adddate date  -- 注意：最后一个字段的后面不需要写逗号
);

再创建一个用户信息表userinfo，字段如下：

• 用户编号：id
• 用户姓名：name
• 用户性别：gender
• 用户年龄：age
• 用户生日：birthday
• 用户居住地：address

创建用户信息表userinfo的SQL语句如下：

create table userinfo(
    id number,
    name varchar2(10),
    gender varchar2(6),
    age number,
    birthday date,
    address varchar2(20)
);

3. 表结构修改

修改表结构时，使用alter table关键字，常见的修改情况如下：

• 新增字段：在已有表的基础上添加新字段。
• 例如，在userinfo表中新增一个手机号码的字段phone，语法为alter table 表名称 add 字段 类型，对应操作语句为alter table userinfo add phone varchar2(11)。
• 修改字段名称：更改已有字段的名称。
• 比如，在userinfo表中将原来的gender名称改成sex，语法是alter table 表名称 rename column 原名称 to 新名称，执行语句为alter table userinfo rename column gender to sex。
• 修改字段长度：调整已有字段的存储长度。
• 在userinfo表中，将address的长度由20改为50，语法为alter table 表名称 modify 字段 数据类型，操作语句为alter table userinfo modify address varchar2(50)。
• 删除字段：移除表中的某个字段。
• 在userinfo表中删除phone字段，语法为alter table 表名称 drop column 字段，即alter table userinfo drop column phone。

删除一张表的语法是：drop table 表名称。

4. 插入数据、修改数据、删除数据

• 插入数据：语法为insert into 表名称(字段一,字段二,.....,字段n) values(值一,值二,....,值n)。例如，向userinfo表中插入一条数据，需要注意出生日期是日期格式，需使用to_date函数进行转换：

-- 插入数据到userinfo中
insert into userinfo(id,name,sex,age,birthday,address) values(1,'张三','男',20,to_date('2002-10-01','yyyy-mm-dd'),'南京');
-- 检测一下数据是否插入成功
select * from userinfo;

执行上述插入数据操作后，重新打开新窗口查询可能无法查到数据。这是因为在Oracle数据库中，对表中的数据进行插入、修改、删除操作时，涉及事务的概念，且Oracle数据库需要手动提交事务。事务操作包括：
- 提交事务：使用commit命令，一旦提交事务，数据将会永久持久化到数据库表结构中。
- 回滚事务：通过roll back命令，回滚后之前的操作将不生效，数据回到原来状态。但如果已经提交了事务，再次回滚将不会成功。
插入数据还有一种简化写法：insert into 表名称 values(值一,值二,......,值n)，不过使用前提是插入所有字段，且插入的值必须与字段一一对应，不能错位。

• 修改数据：语法为update 表名称 set 字段一 = 值一, 字段二 = 值二, ...... , 字段n = 值n where 条件。在进行修改数据操作时，务必加上条件，否则全部数据都会被修改。例如：

-- 修改张三用户的age为22（错误示范，会将所有用户的age都修改为22）
update userinfo set age = 22;
commit;
select * from userinfo;

-- 正确做法，一定要加上条件（尽量要做到唯一）
update userinfo set age = 20 where id =1;
commit;

-- 一次性也可以修改多个字段
update userinfo set age = 20, address = '武汉' where id = 2;
commit;

• 删除表中数据：语法为delete from 表名称 where 条件。删除数据和修改数据一样，尽量加上条件，否则所有数据都会被删除。例如，删除王五的数据：

delete from userinfo where id = 3;
commit;

5. 约束

约束主要用于限制表中插入的数据，保证数据的准确性和完整性。例如，id一般不能重复，姓名不能为空，性别只能是男或女等，这些对数据的限制语法就是约束。约束主要分为以下几类：

• 主键约束：使用primary key定义，通常应用在表中的主键字段上。数据库中的主键一般没有实际业务意义，主要用于后续数据库表结构关联。一般一张表中只能有一个主键，习惯将id设为主键。主键内容可以是数字，也可以是字符串，但必须保证唯一性，主键约束包含唯一约束。
• 非空约束：通过not null表示，意味着该字段在插入数据时不允许为空。
• 唯一约束：用unique定义，表明该字段的内容一般是唯一的，不允许重复。
• 检查约束：使用check关键字，用于检查插入的内容是否符合设定的规则。比如性别字段sex，可设置为check (sex in('男','女')) 。
• 默认值约束：使用default关键字，如果不给该字段插入值，那么会插入默认值。

根据上述约束，创建一张用户信息表userinfo：

create table userinfo(
    id number primary key,  -- 设置id为主键约束
    name varchar2(20) not null,  -- 设置name非空
    sex varchar2(6) check(sex in('男','女')),  -- 设置sex为检查约束
    age number,
    birthday date,
    phone varchar2(11) unique,  -- 设置phone为唯一约束
    address varchar2(50) default '南京'  -- 设置address给与默认值
);

插入数据测试时：

insert into userinfo(id,name,sex,phone) values(1,'张三','1','13333333333');

由于插入的性别是1，不符合sex字段的检查约束（只能是男或女），所以会报错。

insert into userinfo(id,sex,phone) values(3,'男','18888888888');

这条插入语句报错是因为name字段设置了非空约束，插入数据时必须给其赋值。

约束的存在意义在于保护表中数据的完整性，防止无效数据影响项目的正常运行。

6. 外键约束

外键约束用于在设计表关联关系时，通过主键实现表与表之间的关联。比如，A表和B表都有主键，要实现两表关联，就需要使用外键约束。常见的表关联关系有以下几种：

• 一对一的关联设计：在一张表中直接体现关联关系，这种情况相对少见。
• 一对多的关联设计：通常在多的一方额外添加一个字段，用于与另一张表进行关联。例如用户表和银行账户表，一个用户可以拥有多个银行账户信息记录，而一条银行账户信息只能属于某一个用户。设计表结构如下：

-- 重新设计userinfo表
create table userinfo(
    u_id number primary key,
    uname varchar2(10) not null,
    idCard varchar2(18) unique
);

-- 设计银行账户表account
create table account(
    aid number primary key,
    aname varchar2(20) not null,
    acard varchar2(15) unique,
    money number(10,2),
    u_id number,  -- 要将u_id字段和userinfo表中的主键u_id进行关联
    foreign key (u_id) references userinfo(u_id)
);

• 多对多的关联设计：一般会建立一张中间表来表示表与表之间的关联关系。例如学生表t_student和课程表t_course，一个学生可以选择多门课程学习，一门课程也可以被多名学生选择。设计表结构如下：

-- 创建学生信息表t_student
create table t_student (
    sid number primary key,
    sname varchar2(20) not null,
    sage number
);

-- 创建课程信息表t_course
create table t_course (
    cid number primary key,
    cname varchar2(20) not null,
    cval number,
    ctime number
);

-- 建立一张中间表用于表示课程表和学生表的关联关系
create table t_student_t_course(
    sid number,
    cid number,
    cmark number,
    -- 使用外键关联来将sid以及cid分别和t_student、t_course表关联
    foreign key (sid) references t_student(sid),
    foreign key (cid) references t_course(cid)
);