MySQL案例2 MySQL实验(一)数据库定义与操作

考核内容及实验目的：

使用SQL语句创建、修改、删除数据库及基本表。掌握表级、列级完整性约束。

一、实验描述

1、创建数据库hdbase，并创建三张表，分别是country、countryLanguage、city。

表信息如下：

  • country表
img_202210141733916
  • countryLanguage表
img_202210141733490
  • city表
img_202210141733282

2、定义表中的主码、外键、约束、数据类型。

（1）country表主码为Code，countryLanguage表中主码为countryCode、Language，city表主码为ID。
（2）countryLanguage表和city表，其表中的CountryCode字段都是country表中的Code字段的外键。
（3）country表和city表的Name字段列级约束均为非空、唯一。
（4）CountryLanguage中isOffice字段采用枚举类型，要求：isOffice enum(‘T’,’F’)。

3、对表中数据进行更新操作。

（1）修改city表中Population字段属性为bigint、not null。
（2）删除表country中的Surfacearea字段。
（3）插入Visit字段到表city中，枚举类型enum，Y代表去过，N代表没去过。默认值为N.
（4）删除countryLanguage表中的外键约束。
（5）给countryLanguage表Language字段加普通索引。

二、上机操作

0、实验环境

  • 硬件平台：一台笔电，操作系统为Win10pro 64位

  • 软件环境：

    数据库Server版本，8.0.30 MySQL Community Server

    图形化管理工具，Navicat Premium 15.0.25

1、用到的SQL语句

  • 创建数据库
1
2
-- 创建数据库hdbase
create database hdbase;
  • 创建三张表，并定义约束
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    -- 创建表country,countryLanguage,city
    create table `hdbase`.`country`(
        Code char(3) not null,
        Name varchar(20) not null unique,
        Continent varchar(20) not null,
        Surfacearea double(9,2) not null,
        primary key(Code)
    );
    create table `hdbase`.`countryLanguage`(
        CountryCode char(3) not null,
        Language varchar(20) not null,
        IsOffical enum('T','F') not null, -- 字段采用枚举类型,enum('T','F')
        Percentage double(3,1) not null, 
        constraint fk_countryLanguage foreign key(CountryCode) references country(Code), -- 定义外键约束,并自定义约束名
        primary key(CountryCode,Language) -- 联合主键
    );
    create table `hdbase`.`city`(
        ID char(4) not null,
        Name varchar(20) not null unique,
        CountryCode char(3) not null,
        District varchar(20) not null,
        Population int unsigned,
        constraint fk_city foreign key(CountryCode) references country(Code), -- 定义外键约束
        primary key(ID)
    );
  • 批量插入数据
    1
    2
    3
    4
    5
    6
    7
    -- 对三张表中插入部分数据
    insert into country (Code,Name,Continent,Surfacearea) values ('AUS','Australia','Oceania','7741220.00'),
    ('CHN','China','Asia','9572900.00'),('DEU','Germany','Europe','357022.00'),('USA','United States','North America','9363520.00');

    insert into countryLanguage (CountryCode,Language, IsOffical,Percentage) values ('CHN','Chinese','T','92.0'),('CHN','Tibetan','F','0.4'),('USA','English','T','98');

    insert into city (ID,Name,CountryCode,District,Population) values  ('1901','Xi`an','CHN','Shaanxi','2761400'),('1990','Xianyang','CHN','Shaanxi','352125'),('1996','Baoji','CHN','Shaanxi','337765');
  • 数据更新操作
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    --（1）修改city表中Population字段属性为bigint、not null，即修改列属性
    -- 给city表修改列属性
    alter table city modify Population bigint;
    -- 添加非空属性
    alter table city modify column Population bigint not null;

    --（2）删除country表中的Surfacearea字段
    alter table country dorp Surfacearea;

    --（3）插入Visit新字段到city表中，枚举类型enum，Y代表去过，N代表没去过，默认值为N
    alter table city add (Visit enum('Y','N') default 'N');
1
2
3
4
5
6
7
8
9
--（4）删除countryLanguage表中的外键约束
-- 先要知道countryLanguage表中的外键约束名,在前面创建表时我定义外键约束名为fk_countryLanguage
alter table countryLanguage drop foreign key fk_countryLanguage;

--（5）给countryLanguage表的Language字段加普通索引
-- 给表中字段添加普通索引的语法：
alter table table_name add index index_name (column);

alter table countryLanguage add index index_Language(Language);

2、完整的SQL代码实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
-- 一、创建数据库
create database hdbase;

-- 二、创建三张表,并定义约束
create table `hdbase`.`country`(
		Code char(3) not null,
		Name varchar(20) not null unique,
		Continent varchar(20) not null,
		Surfacearea double(10,2) not null,
		primary key(Code)
);
create table `hdbase`.`countryLanguage`(
		CountryCode char(3) not null,
		Language varchar(20) not null,
		isOffical enum('T','F') not null,
		Percentage double(3,1) not null,
		constraint fk_countryLanguage foreign key (CountryCode) references country(Code),
		primary key(CountryCode,Language)
);
create table `hdbase`.`city`(
		ID char(4) not null,
		Name varchar(20) not null unique,
		CountryCode char(3) not null,
		District varchar(20) not null,
		Population int unsigned not null,
		constraint fk_city foreign key (CountryCode) references country(Code),
		primary key(ID)
);

-- 三、批量插入数据 
-- 国家表
insert into country (Code,Name,Continent,Surfacearea) values ('AUS','Australia','Oceania','7741220.00'),
('CHN','China','Asia','9572900.00'),('DEU','Germany','Europe','357022.00'),('USA','United States','North America','9363520.00');
select * from country; 

-- 国家语言表
insert into countryLanguage (CountryCode,Language,isOffical,Percentage) values ('CHN','Chinese','T','92.0'),('CHN','Tibetan','F','0.4'),('USA','English','T','98');
select * from countryLanguage; 

-- 城市表
insert into city (ID,Name,CountryCode,District,Population) values ('1901','Xi`an','CHN','Shaanxi','2761400'),('1990','Xianyang','CHN','Shaanxi','352125'),('1996','Baoji','CHN','Shaanxi','337765');
select * from city; 


-- 四、修改表

-- 给city表修改列属性
alter table city modify Population bigint;
-- 添加非空属性
alter table city modify column Population bigint not null;
-- 查看表结构
desc city;

-- 删除列（字段）
alter table country drop Surfacearea;
select * from country; 

-- 插入新字段到city表
alter table city add (Visit enum('Y','N')  default 'Y');
-- 查看表结构
desc city;
select * from city; 

-- 删除表中的外键约束
alter table countryLanguage drop foreign key fk_countryLanguage;

-- 添加普通索引
alter table countryLanguage add index index_name(Language);