数据库 实验一 数据定义及更新

目录

实验内容

（一）数据库的创建

1．创建满足以下要求的数据库：

2．利用SQL语言创建满足以下要求的数据库

3．修改数据库的属性

4．数据库的删除

（二）表数据的操作

1．在数据库ST中创建表

2．管理表

3．对表添加、修改、删除数据

4．复制表中的数据创建新表

---

内容仅供参考，如有错误请指出！

实验内容

（一）数据库的创建

1．创建满足以下要求的数据库：

        1）数据库存在于连接 MySQL 中；

        2）数据库名称为 ST；

        3）字符集选择 utf8 -- UTF-8 Unicode；

        4）排序规则选择 utf8_general_ci。

具体创建步骤记录如下：

新建连接“MySQL”—单击选择并右键—“新建数据库”—输入数据库名“mydb”、字符集“utf8 -- UTF-8 Unicode”、排序规则“utf8_general_ci”—“确定”。

2．利用SQL语言创建满足以下要求的数据库

        1）数据库存在于连接 MySQL 中；

        2）数据库名称为 ST；

        3）字符集选择 utf8 -- UTF-8 Unicode；

        4）排序规则选择 utf8_general_ci。

（1）用SQL语句创建数据库，具体步骤如下：

点击“新建查询”，在查询编辑器输入以下代码，点击“运行”，完成后，左侧列表中选中连接“数 MySQL”，点击“刷新”按钮，列表即可见到“ST”。

CREATE DATABASE ST  DEFAULT CHARACTER SET utf8 COLLATE

utf8_general_ci;

（2）利用SQL语句查看、验证创建的数据库

方法1：查看连接中的所有数据库

步骤：点击“新建查询”，输入命令：

SHOW DATABASES;

之后运行。

（3）查看数据库ST 中所有的表

步骤：点击“新建查询”，输入命令：

USE  ST;

SHOW TABLES;

之后运行。（也可以用语句 show tables from ST;）

方法2：在可视化工具（如Navicat）中查看。

具体操作方法：

1、在数据库连接中找到目标数据库ST，左键双击打开数据库
2、在打开的数据库中，可以看到包含所有表的目录
3、双击该目录，即可查看所有的表

（4）查看数据库ST 中“student”表的结构。

步骤：点击“新建查询”，输入

USE mysql;

DESC help_keyword;

之后运行。

3．修改数据库的属性

创建完一个数据库后，可以用SQL语句查看数据库，修改数据库属性等。

（1）用可视化工具（如Navicat）修改数据库ST属性，修改其排序方式为utf8_bin。

步骤：右键单击数据库“mydb”——数据库属性，在排序方式中选择“utf8_bin”——“确定”。

（2）用SQL语句修改数据库ST的字符集为Latin1；

步骤：点击“新建查询”，输入命令：

ALTER DATABASE ST CHARACTER SET Latin1;

之后运行。

4．数据库的删除

（1）使用可视化工具（如Navicat）删除数据库ST

具体操作方法：

1、在数据库连接中找到要删除的数据库 ST
2、右键单击数据库ST，选择 “删除数据库”选项
3、系统将提升确认删除操作
4、确认删除后，数据库ST将被永久删除

（2）使用SQL语句删除数据库ST

步骤：点击“新建查询”，输入命令：

DROP DATABASE ST;

之后运行。

（二）表数据的操作

1．在数据库ST中创建表

（1）使用可视化工具（如Navicat）创建Student表

Student表结构及其约束为：

表1-1 Student表结构和约束

列名	数据类型	宽度	允许空值	缺省值	主键	说明
Sno	char	9	否		是	学号
Sname	char	20	是			姓名
Ssex	char	2	是			性别
Sage	smallint		是			年龄
Sdept	char	20	是			所在系

具体步骤如下：打开新建的数据库“ST”–右键点击“表”--“新建表”。按照上表内容输入个属性的定义，填写“列名”、“数据类型”，并取消或选择“允许 NULL 值”前的选项，并右键点击“Sno”，选择将其设为主键。

（2）使用SQL语句创建表

        1）创建Course表

Course表结构及其约束为：

表1-2 Course表结构和约束

列名	数据类型	宽度	允许空值	缺省值	主键	外键	说明
Cno	char	4	否		是		课程号
Cname	char	40	是				课程名
Cpno	char	4	是			是	先行课
Ccredit	smallint		是				学分

创建Course表SQL语句如下：

（执行正确后请将以Course.sql保存起来以备后用）

CREATE TABLE Course (
    Cno CHAR(4) NOT NULL,
    Cname CHAR(40),
    Cpno CHAR(4),
    Ccredit SMALLINT,
    PRIMARY KEY(Cno),
    FOREIGN KEY(Cpno) REFERENCES Course(Cno)
);

        2）创建表SC表

SC表结构及其约束为：

表1-3 SC表结构和约束

列名	数据类型	宽度	允许空值	缺省值	主键	外键	说明
Sno	char	9	否		是	是	学号
Cno	char	4	否		是	是	课程号
Grade	smallint		是				成绩

其中成绩为百分制。

创建SC表SQL语句如下：

（执行正确后请将以SC.sql保存起来以备后用）

CREATE TABLE SC (
    Sno CHAR(9) NOT NULL,
    Cno CHAR(4) NOT NULL,
    Grade SMALLINT,
    PRIMARY KEY(Sno, Cno),
    FOREIGN KEY (Sno) REFERENCES student(Sno),
    FOREIGN KEY (Cno) REFERENCES course(Cno)
);

2．管理表

（1）添加和删除列

给Course表增加一列，字段名为Ctype(课程类型)，类型为char，长度为10,允许为空值。

        1）使用可视化工具（如Navicat）添加Ctype列。

操作步骤如下：

1、在st数据库中找到Course表，右键单击选择 “设计表”选项
2、选择 添加字段，在新加的空行中填写对应信息

插入该列后，在可视化工具（如Navicat）中删除该列的操作步骤：

1、在st数据库中找到Course表，右键单击选择 “设计表”选项
2、选中 Ctype 行，删除字段，保存即可

        2）使用SQL语句进行。

添加Ctype字段的SQL语句：

ALTER TABLE Course
ADD Ctype CHAR(10);

删除Ctype字段的SQL语句：

ALTER TABLE Course
DROP COLUMN Ctype;

（2）表的重命名及删除

        1）使用可视化工具（如Navicat）重命名及删除表。

重命名操作步骤：

(如将Course表重命名为Course1表)

1、右键Course, 选择“重命名”
2、输入 Course1, 点击任意空白区域即可

删除Course1表的操作步骤：

1、右键 Course1, 选择 “删除表”
2、在弹出系统提示框中点击确定

        2）使用SQL语句实现

在查询命令窗口中，将已保存的Course.sql打开，运行重建Course表。

重命名Course表为Course1表的SQL语句。（提示：使用rename）

ALTER TABLE Course RENAME TO Course1;

删除Course1表的SQL语句。

DROP TABLE Course1;

3．对表添加、修改、删除数据

（1）插入数据简单操作

        1）使用可视化工具（如Navicat）向Student表中插入数据。

表1-4 Student表

Sno	Sname	Ssex	Sage	Sdept
200215121	李勇	男	20	CS
200215122	刘晨	女	19	CS
200215123	王敏	女	18	MA
200215125	张立	男	19	IS
200215126	张红	女	19	CS

操作步骤：

在Student表中依次输入每一行的数据，输入完毕点击左下角的对号保存，点击加号获取新的一行，重复进行直至数据输入完毕

更多其它数据可自行添加。

        2）使用SQL语句向Course表中插入数据。

表1-5 Course表

Cno	Cname	Cpno	Ccredit
1	数据库	5	4
2	数学		2
3	信息系统	1	4
4	操作系统	6	3
5	数据结构	7	4
6	数据处理		2
7	PASCAL语言	6	4

在查询命令窗口中，将已保存的Course.sql打开，运行重建Course表。

向Course表中插入以上数据的SQL命令为：

INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES
    ('6','数据处理',NULL,2),
    ('7','PASCAL语言','6',4),
    ('5','数据结构','7',4),
    ('1','数据库','5',4),
    ('4','操作系统','6',3),
    ('2','数学',NULL,2),
    ('3','信息系统','1',4);

更多其它数据可自行添加。

        3）使用SQL语句向SC表中插入数据。

表1-6 SC表

Sno	Cno	Grade
200215121	1	92
200215121	2	85
200215121	3	88
200215122	2	90
200215122	3	80

向SC表中插入以上数据的SQL命令为：（保存为insert_SC.sql已备后用）。其它数据可自行添加。

INSERT INTO SC (Sno, Cno, Grade)
VALUES ('200215121', '1', 92),
       ('200215121', '2', 85),
       ('200215121', '3', 88),
       ('200215122', '2', 90),
       ('200215122', '3', 80);

（完成以上操作后，请将Stu_Course数据库备份，得到Stu_Course.bak备份文件，保存起来以备后面实验用）

（2）修改数据简单操作。

        1）在可视化工具（如Navicat）中，将Student表中的学号为“200215125”的同学的姓名改为“李双”。

操作步骤：

双击student表 - > 选择200212125的同学姓名 - > 删除并改为李双 - > 点击左下角对号保存

        2）使用SQL语句，将Course表中的课程号为“2”的学分改为4。

SQL语句为：

UPDATE Course
SET Ccredit = 4
WHERE Cno = '2';

        3）使用SQL语句，将SC表中的选修了“2”课程的同学的成绩*80%。

SQL语句为：

UPDATE SC
SET Grade = Grade * 0.8
WHERE Cno = ‘2’;

（3）删除数据简单操作（请注意约束的限制）

        1）假设学号为“200215123”的同学已离开学校，则需要删除该记录，请在可视化工具（如Navicat）中将Student表中将该记录删除。

操作步骤：

选中该列 - > 右键删除记录 - > 点击确定 - > 点击左下角对号保存

        2）删除选修了“信息系统”的学生的记录。

SQL语句：

DELETE FROM SC
WHERE Cno IN (
    SELECT Cno
    FROM Course
    WHERE Cname = ‘信息系统’
);

        3）使用SQL语句，删除所有的学生选课记录。

SQL语句：

DELETE FROM SC;

4．复制表中的数据创建新表

（1）将Student表中的数据复制到一个新表Student1中。

（语句格式：create table 新表名  like 源表）

SQL语句：

CREATE TABLE Student1 LIKE Student;
INSERT INTO Student1 SELECT * FROM Student;

（2）新建库XUE，将Stu_Course库中的Student表中的数据复制到XUE库中，名称仍为Student。

SQL语句：

CREATE DATABASE XUE;
USE XUE;
CREATE TABLE Student LIKE Stu_Course.Student;
INSERT INTO Student SELECT * FROM Stu_Course.Student;

（3）复制Stu_Course库中SC表与XUE库中的Student表的部分内容，以创建一新表Student2，其内容包括学生的学号、姓名、课程名、成绩。

SQL语句：

USE XUE;
CREATE TABLE Student2 AS
SELECT S.Sno, S.Sname, C.Cname, SC.Grade
FROM Stu_Course.SC SC
JOIN XUE.Student S ON SC.Sno = S.Sno
JOIN Stu_Course.Course C ON SC.Cno = C.Cno;