文理学院数据库应用技术实验报告2

@杨星辰

于 2024-10-04 21:04:57 发布

阅读量367

点赞数 11

分类专栏： MySQL 文章标签：数据库 mysql sql 软件工程

本文链接：https://blog.csdn.net/m0_63451989/article/details/142707317

版权

MySQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

文理学院数据库应用技术实验报告2

实验名称	数据增、删、改表操作及数据导入导出	实验日期
课程名称	数据库应用技术	实验项目	数据增、删、改表操作及数据导入导出

1. 实验目的

表记录的添加、修改、删除（重点）

2. 实验原理

2.1 表记录的添加：

（1）单条添加：

insert into 表 [(字段列表)] values (值列表);

（2）多条添加：

insert into 表 [(字段列表)] values (值列表1), (值列表2), ……(值列表n);

（3）添加别的表的查询结果：

create table 新表select字段列表from源表 [where 条件表达式];

（4）使用replace添加新记录：

replace [into] 表set 字段1=值1, 字段2=值2;

2.2 对表修改数据：

update表set字段名1=新值1, 字段名2=新值2...where条件表达式;

2.3 对表删除数据：

（1）删除带条件的值：

delete from表where条件表达式;

（2）清空表数据：

truncate table表;

3. 实验设备、材料

安装了MySQL和navicat的主机

4.实验步骤

完成以下准备工作：

（1）启动机房自带的MySQL服务

（2）使用命令行提示符连接上服务器：

（3）设置该服务器全局字符集为“UTF8”：

（4）从教师机上拷贝“实训五素材”中的文件“bank.sql”，解压后，将文件导入到服务器。

百度网盘：数据库实训5
链接：https://pan.baidu.com/s/1P_KX2zFe-A2_GF0BXlht2Q?pwd=o46u
提取码：o46u

bank.sql的数据库源码如下

-- MySQL dump 10.13  Distrib 5.7.14, for Win64 (x86_64)
--
-- Host: localhost    Database: bank
-- ------------------------------------------------------
-- Server version	5.7.14

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


DROP DATABASE IF EXISTS `Bank`;

CREATE DATABASE `Bank`;

USE `Bank`;

--
-- Table structure for table `交易信息`
--

DROP TABLE IF EXISTS `交易信息`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `交易信息` (
  `交易编号` int(11) NOT NULL AUTO_INCREMENT,
  `交易时间` datetime NOT NULL,
  `卡号` char(19) NOT NULL,
  `交易类型` varchar(4) NOT NULL,
  `交易金额` decimal(20,3) NOT NULL,
  `备注` tinytext,
  PRIMARY KEY (`交易编号`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `交易信息`
--

LOCK TABLES `交易信息` WRITE;
/*!40000 ALTER TABLE `交易信息` DISABLE KEYS */;
INSERT INTO `交易信息` VALUES (1,'2016-06-05 13:01:12','1010 3576 1234 5678','存入',900.000,NULL),(2,'2016-07-18 15:50:46','1010 3576 0954 2351','支取',300.000,NULL),(3,'2016-07-27 09:03:07','1010 3576 0954 2351','支取',1000.000,NULL),(4,'2016-09-05 08:51:32','1010 3576 1234 5678','支取',100.000,NULL),(5,'2016-09-27 18:00:04','1010 3576 1234 5678','存入',200.000,NULL),(6,'2016-10-08 11:32:05','1010 3576 1234 5678','存入',500.000,NULL),(7,'2016-10-27 16:57:31','1010 3576 1212 1134','存入',230.000,NULL),(8,'2016-10-29 11:23:41','1010 3576 1234 5678','支取',288.000,NULL),(9,'2016-11-01 09:46:57','1010 3576 1212 1134','支取',50.000,NULL);
/*!40000 ALTER TABLE `交易信息` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `用户信息`
--

DROP TABLE IF EXISTS `用户信息`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `用户信息` (
  `用户编号` int(11) NOT NULL AUTO_INCREMENT,
  `开户姓名` varchar(20) NOT NULL,
  `身份证号` char(18) NOT NULL,
  `联系电话` varchar(13) DEFAULT NULL,
  `居住地址` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`用户编号`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `用户信息`
--

LOCK TABLES `用户信息` WRITE;
/*!40000 ALTER TABLE `用户信息` DISABLE KEYS */;
INSERT INTO `用户信息` VALUES (1,'朱晓晶','53010319991005124X','15368068582','云南省昆明市云南工商学院'),(2,'张三','123456199012305123','0871-67898978','北京海淀'),(3,'李四','321245198301045678','0478-44443333','XXXX'),(4,'王五','324234198506093679','0987-88888888','昆明市'),(5,'赵六','998754196411111654','13088764321','云南丽江'),(6,'钱七','252148198703249303','13888888888','上海');
/*!40000 ALTER TABLE `用户信息` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `银行卡信息`
--

DROP TABLE IF EXISTS `银行卡信息`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `银行卡信息` (
  `卡号` char(19) NOT NULL,
  `货币类型` varchar(10) NOT NULL,
  `存款类型` varchar(20) NOT NULL,
  `开户时间` datetime NOT NULL,
  `开户金额` decimal(20,3) NOT NULL,
  `余额` decimal(20,3) NOT NULL,
  `密码` char(6) NOT NULL,
  `是否挂失` char(2) NOT NULL,
  `用户编号` int(11) NOT NULL,
  PRIMARY KEY (`卡号`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `银行卡信息`
--

LOCK TABLES `银行卡信息` WRITE;
/*!40000 ALTER TABLE `银行卡信息` DISABLE KEYS */;
INSERT INTO `银行卡信息` VALUES ('1010 3576 0875 9527','RMB','活期','2016-02-20 17:06:38',1.000,1.000,'123456','否',5),('1010 3576 0954 2351','RMB','定期','2015-09-20 12:47:49',349.500,349.500,'123456','否',4),('1010 3576 1212 1134','RMB','定期','2016-05-12 14:44:54',1.000,1.000,'888888','否',2),('1010 3576 1234 5678','RMB','活期','2016-10-26 19:30:15',1000.000,1000.000,'888888','否',1),('1010 3576 1246 8654','RMB','定期','2016-10-27 22:46:07',50.000,50.000,'111111','是',3);
/*!40000 ALTER TABLE `银行卡信息` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2018-02-12 10:55:52

（5）将当前数据库切换到Bank。

该数据库中有三张表，分别是：“用户信息”表、“银行卡信息”表、“交易信息”表，请完成以下添加数据、修改数据、删除数据的操作：（本题请截屏在实验报告中，存为自己的姓名，标注题号）

（1）使用单条添加语句对用户信息表分别添加以下数据：

开户姓名身份证号联系电话居住地址

张大宝 530102198904050012 13599999999 云南省昆明市呈贡区

吴义贵 242422199511021228 16798986543 海南省海口市

李琴琴 764352199408223216 13777777777 湖北省武汉市武昌区

INSERT INTO `用户信息` (`开户姓名`, `身份证号`, `联系电话`, `居住地址`) VALUES ('张大宝', '530102198904050012', '13599999999', '云南省昆明市呈贡区');

INSERT INTO `用户信息` (`开户姓名`, `身份证号`, `联系电话`, `居住地址`) VALUES ('吴义贵', '242422199511021228', '16798986543', '海南省海口市');

INSERT INTO `用户信息` (`开户姓名`, `身份证号`, `联系电话`, `居住地址`) VALUES ('李琴琴', '764352199408223216', '13777777777', '湖北省武汉市武昌区');

（2）使用多条添加语句对银行卡信息表一次性添加以下数据：

卡号货币类型存款类型开户时间开户金额余额密码是否挂失用户编号

1010 3576 0875 9528 RMB 活期 2016-2-6 14:30:22 300 300 123456 否 7

1010 3576 0342 8733 RMB 定期 2016-3-12 15:00:30.334 10000 10000 123321 否 8

INSERT INTO `银行卡信息` (`卡号`, `货币类型`, `存款类型`, `开户时间`, `开户金额`, `余额`, `密码`, `是否挂失`, `用户编号`)  
VALUES ('1010357608759528', 'RMB', '活期', '2016-02-06 14:30:22', 300, 300, '123456', '否', 7),('1010357603428733', 'RMB', '定期', '2016-03-12 15:00:30.334', 10000, 10000, '123321', '否', 8);

（3）使用表的查询结果添加数据到新表：

将银行卡信息表中的卡号、密码、余额添加到另外一张新表aaa中。

CREATE TABLE aaa (卡号 CHAR(19),密码 CHAR(6),余额 DECIMAL(20, 3));
INSERT INTO aaa (卡号, 密码, 余额) SELECT 卡号, 密码, 余额 FROM 银行卡信息;

（4）使用代码对表进行数据修改操作：

①对于交易信息表，将交易类型为“存入”的所有交易，交易金额都增加1元。

UPDATE 交易信息  
SET 交易金额 = 交易金额 + 1  
WHERE 交易类型 = '存入';

②对于aaa表，将所有余额大于10元的余额都乘以1.2倍。

UPDATE aaa  
SET 余额 = 余额 * 1.2  
WHERE 余额 > 10;

③对于银行卡信息表，将挂失了的银行卡密码都改为初始密码（111111）。

UPDATE 银行卡信息
SET 密码 = '111111'  
WHERE 是否挂失 = '是';

（5）使用代码对表进行数据删除（比较两种删除语句的区别）：

①delete删除语句：

删除交易信息表中交易金额小于100元的记录。

DELETE FROM  交易信息 WHERE 交易金额 < 100;

②truncate table删除语句：

删除aaa表中的所有信息。

 TRUNCATE TABLE aaa;

复习数据表约束练习（本题请截屏在实验报告中，标注题号）：

（1）外键约束

① 对于银行卡信息表，创建外键约束，使该表中的用户编号字段成为外键，主键来源于用户信息表中的用户编号字段。

ALTER TABLE  银行卡信息 
ADD CONSTRAINT 用户编号  
FOREIGN KEY (用户编号) REFERENCES 用户信息(用户编号);

② 对于交易信息表，创建外键约束，使该表中的卡号字段成为外键，主键来源于银行卡信息表中的卡号字段。

ALTER TABLE 交易信息
ADD CONSTRAINT 卡号  
FOREIGN KEY (卡号) REFERENCES 银行卡信息(卡号);

（2）唯一约束

对于用户信息表，创建唯一约束，使该表中的身份证号成为唯一字段。

ALTER TABLE  用户信息 
ADD CONSTRAINT unique_id_card_number UNIQUE (身份证号);

（3）检查约束

对于交易信息表，创建检查约束，使该表中的交易类型字段只能取值为“存入”、“支取”、“转账”。

ALTER TABLE  交易信息
ADD CONSTRAINT chk_transaction_type CHECK (交易类型 IN ('存入', '支取', '转账'));

附：全部源码：

INSERT INTO `用户信息` (`开户姓名`, `身份证号`, `联系电话`, `居住地址`) VALUES ('张大宝', '530102198904050012', '13599999999', '云南省昆明市呈贡区');

INSERT INTO `用户信息` (`开户姓名`, `身份证号`, `联系电话`, `居住地址`) VALUES ('吴义贵', '242422199511021228', '16798986543', '海南省海口市');

INSERT INTO `用户信息` (`开户姓名`, `身份证号`, `联系电话`, `居住地址`) VALUES ('李琴琴', '764352199408223216', '13777777777', '湖北省武汉市武昌区');

INSERT INTO `银行卡信息` (`卡号`, `货币类型`, `存款类型`, `开户时间`, `开户金额`, `余额`, `密码`, `是否挂失`, `用户编号`)  
VALUES ('1010357608759528', 'RMB', '活期', '2016-02-06 14:30:22', 300, 300, '123456', '否', 7),('1010357603428733', 'RMB', '定期', '2016-03-12 15:00:30.334', 10000, 10000, '123321', '否', 8);


CREATE TABLE aaa (  
    卡号 CHAR(19),  
    密码 CHAR(6),  
    余额 DECIMAL(20, 3)  
);


INSERT INTO aaa (卡号, 密码, 余额)  
SELECT 卡号, 密码, 余额  
FROM 银行卡信息;

UPDATE 交易信息  
SET 交易金额 = 交易金额 + 1  
WHERE 交易类型 = '存入';


UPDATE aaa  
SET 余额 = 余额 * 1.2  
WHERE 余额 > 10;

UPDATE 银行卡信息
SET 密码 = '111111'  
WHERE 是否挂失 = '是';

DELETE FROM  交易信息
WHERE 交易金额 < 100;

TRUNCATE TABLE aaa;

ALTER TABLE  银行卡信息 
ADD CONSTRAINT 用户编号  
FOREIGN KEY (用户编号) REFERENCES 用户信息(用户编号);

ALTER TABLE 交易信息
ADD CONSTRAINT 卡号  
FOREIGN KEY (卡号) REFERENCES 银行卡信息(卡号);

ALTER TABLE  用户信息 
ADD CONSTRAINT unique_id_card_number UNIQUE (身份证号);

ALTER TABLE  交易信息
ADD CONSTRAINT chk_transaction_type CHECK (交易类型 IN ('存入', '支取', '转账'));