1
# 1. 创建数据库
2
# 删除已存在的pet_shelter数据库
3
drop database if exists pet_shelter;
4

5
# 新建pet_shelter数据库
6
create database if not exists pet_shelter
7
    character set utf8mb3;
8

9
show create database pet_shelter;
10

11
use pet_shelter;
12

13
# 2. 创建3张表
14
# 创建工作人员表(staff)
15
create table if not exists staff
16
(
17
    # 主键不要添加not null,这会导致无法使用null占位符
18
    staff_id  int primary key auto_increment comment '工作人员编号',
19
    full_name varchar(30) not null comment '姓名',
20
    position  varchar(30) not null comment '职位',
21
    hire_date date        not null default (current_date) comment '入职日期',
22
    phone     varchar(15) not null comment '联系电话'
23
) comment '工作人员表';
24

25
# 创建宠物表(pet)
26
create table if not exists pet
27
(
28
    pet_id        int primary key auto_increment comment '宠物编号',
29
    name          varchar(20) not null comment '宠物名字',
30
    species       varchar(10) not null comment '物种',
31
    breed         varchar(30) not null comment '品种',
32
    intake_date   date        not null default (current_date) comment '入所日期',
33
    health_status varchar(20) not null comment '健康状况'
34
) comment '宠物表';
35

36
# 创建领养记录表(adoption)
37
create table if not exists adoption
38
(
39
    adopt_id     int primary key auto_increment comment '领养记录编号',
40
    pet_id       int         not null comment '宠物编号',
41
    adopter_name varchar(30) not null comment '领养人姓名',
42
    adopt_date   date        not null default (current_date) comment '领养日期',
43
    staff_id     int         not null comment '工作人员编号'
44
) comment '领养记录表';
45

46
# 修改表结构DDL操作
47
# 新增邮箱地址字段
48
alter table staff
49
    add column email varchar(50) comment '邮箱地址';
50
# 修改列名 health_status -> status: 方式一: change column <源列名> <目标列名> <列属性定义>
51
alter table pet
52
    change column health_status status varchar(20) not null comment '健康状况';
53
# 修改列名 health_status -> status: 方式二: rename column <源列名> to <目标列名>
54
alter table pet
55
    rename column health_status to status;
56
# 删除列staff_id
57
alter table adoption
58
    drop column staff_id;
59

60
# 第二部分: DML操作
61

62
insert into staff (staff_id, full_name, position, hire_date, phone, email)
63
values (101, '王建国', '管理员', '2023-05-10', '13811112222', 'wang@shelter.org'),
64
       (102, '李医生', '兽医', '2022-11-01', '13922223333', 'li@shelter.org'),
65
       (103, '张小花', '清洁员', '2024-01-15', '13733334444', 'zhang@shelter.org');
66

67

68
insert into pet (pet_id, name, species, breed, intake_date, status)
69
values (1, '小白', '猫', '中华田园猫', '2025-02-01', '健康'),
70
       (2, '旺财', '狗', '中华田园犬', '2025-02-05', '需治疗'),
71
       (3, '咖啡', '猫', '英国短毛猫', '2025-02-10', '健康'),
72
       (4, '雪球', '狗', '萨摩耶', '2025-02-12', '健康');
73

74

75
insert into adoption (adopt_id, pet_id, adopter_name, adopt_date)
76
values (1001, 1, '刘女士', '2025-03-01'),
77
       (1002, 3, '陈先生', '2025-03-05')
78
;
79

80
# 更新数据
81
update staff
82
set phone = '13999998888'
83
where full_name = '李医生';
84

85
update pet
86
set status = '已康复'
87
where name = '旺财';
88

89
update adoption
90
set adopter_name = '刘芳'
91
where adopter_name = '刘女士';
92

93
# 删除数据
94
delete
95
from pet
96
where species = '狗'
97
  and status = '健康';
98

99
delete
100
from adoption
101
where pet_id = 4;
102

103
delete
104
from staff
105
where full_name = '张小花';
106

107
# 清空操作
108
delete
109
from adoption
110
where adopt_id >= 0; # 条件为抑制IntelliJ安全机制
111
truncate table pet;
112

113

114
# 获取表DDL
115
show create table staff;
116
# 结构化输出表结构
117
desc staff;