一、数据库基本操作实验

1.简介

这里将会以书上的选课表为例，来进行试验，熟悉下数据库课设里没怎么用到的数据库sql语言。也是相当于顺带补一下因为金工实习没试的语句。

2.DBMS及辅助软件

DBMS： MySql8.0

辅助软件： Data Grip

3.创建数据库

sql create database 数据库名字;

4.建表

student

sql create table stu ( id int not null primary key, name varchar(255) not null, sdep varchar(255) not null );

course

sql create table cou ( id int not null primary key, name varchar(255) not null, teacher varchar(255) not null );

cs表，将使用双属性作为主键，同时使用外键。

``` sql create table cs ( stu_id int not null, cou_id int not null, score int null, primary key (stu_id, cou_id), constraint cs_ibfk_1 foreign key (stu_id) references stu (id), constraint cs_ibfk_2 foreign key (cou_id) references cou (id) );

create index cou_id on cs (cou_id);# mysql自动对cou_id生成了索引 ```

5.添加数据

向stu插入数据

sql -- insert into 表名(字段1,字段2....) values(值1,值2....); insert into stu(id, name, sdep) values ('1','1','s');

删除插入：在插入一条数据时，如果此条已经存在，那么先删除原来存在的数据再添加插入的数据，如果不存在那么直接插入新的数据。

sql -- replace into 表名(字段1,字段2....) values(值1,值2....); replace into stu(id, name, sdep) values ('1','1','cs');

6.查询

在此基础上新建一张表进行查询（因为查询的语句有些多）

sql CREATE TABLE student( id INT, NAME VARCHAR(20), chinese FLOAT, english FLOAT, math FLOAT ); INSERT INTO student(id,NAME,chinese,english,math) VALUES(1,'张小明',89,78,90); INSERT INTO student(id,NAME,chinese,english,math) VALUES(2,'李进',67,53,95); INSERT INTO student(id,NAME,chinese,english,math) VALUES(3,'王也',87,78,77); INSERT INTO student(id,NAME,chinese,english,math) VALUES(4,'李奎',88,98,92); INSERT INTO student(id,NAME,chinese,english,math) VALUES(5,'李宝',82,84,67); INSERT INTO student(id,NAME,chinese,english,math) VALUES(6,'张楚岚',55,85,45); INSERT INTO student(id,NAME,chinese,english,math) VALUES(7,'黄蓉',75,65,30);

普通查询

sql -- 查询所有数据 SELECT * FROM student;

sql -- 查询指定列数据 SELECT id,NAME FROM student;

``` sql

-- 查询指定列并添加别名(as可以省略) SELECT id AS '编号',NAME AS '姓名' FROM student; -- 或 SELECT id '编号',NAME '姓名' FROM student; ```

sq; -- 查询时添加常量列(添加一个年级列) SELECT *,'三年级'AS '年级' FROM student;

sql -- 查询时合并相关列（chinese，english，math三个的总分） -- 注意：合并列时只能合并数值类型的字段 SELECT id,NAME,(chinese+english+math) AS '总成绩' FROM student;

sql -- 在表中添加新的字段,方便后面使用 ALTER TABLE student ADD gender CHAR; UPDATE student SET gender='男';

sql -- 查询时除去重复记录（distinct）[查询表中学生的性别] SELECT DISTINCT gender FROM student; -- SELECT DISTINCT (gender) FROM student;-- 可以在字段处打上括号

条件查询

sql -- 查询id为2且姓名为李进的学生(and) SELECT * FROM student WHERE id=2 AND NAME='李进';

sql -- 查询id为3，或姓名是王也的学生(or) SELECT * FROM student WHERE id = 4 OR NAME = '王也';

sql -- 查询English分数在80到95之间的学生 SELECT * FROM student WHERE english>=80 AND english<=95; -- 或 -- SELECT * FROM student WHERE english BETWEEN 80 AND 95;

sql -- 查询学生性别为空的记录 UPDATE student SET gender=NULL WHERE id=7 OR id=6;-- 先置空 SELECT * FROM student WHERE gender IS NULL OR gender='';-- （包括null和空字符串）

sql -- 查询性别不为空的 SELECT * FROM student WHERE gender IS NOT NULL ; SELECT * FROM student WHERE gender IS NOT NULL AND gender<>''; -- 此条增加gender不为空的限制

``` sql

-- 模糊的条件查询 -- %：表示任意个字符 -- _:表示单个字符

-- 查询姓“张”的学生 SELECT * FROM student WHERE NAME LIKE '张%'; ```

sql -- 查询姓“李”的且姓名只有两个字的 SELECT * FROM student WHERE NAME LIKE '李_';

聚合查询

sql -- 常用的聚合函数：sum()，avg(),max(),min(),count() -- 查询学生Chinese的总分 SELECT SUM(chinese) FROM student;

sql -- 查询学生math的平均分 SELECT AVG(math) FROM student;

sql -- 查询英语的最高分的学生 SELECT NAME,english FROM student WHERE english=(SELECT MAX(english) FROM student);

sql -- 查询数学的最低分 SELECT MIN(math) FROM student;

sql -- 统计有多少学生 SELECT COUNT(*) FROM student;

sql -- 注意：当count（字段）时，是统计该字段不为空的记录，而count(*)是统计每个字段的记录并取最大的值 SELECT COUNT(gender) FROM student;

sql -- 分页查询（limit 起始行（从0开始），查询几行） SELECT * FROM student LIMIT 0,2; SELECT * FROM student LIMIT 2,2;

sql -- 查询排序(order by) -- asc:正序排序，desc：反序排序 SELECT * FROM student ORDER BY math ASC; SELECT * FROM student ORDER BY english DESC;

修改表内数据如下

sql -- 分组查询（group by） -- 查询男女的人数 SELECT gender ,COUNT(*) FROM student GROUP BY gender;

sql -- 查询总人数大于3的性别 SELECT gender FROM student GROUP BY gender HAVING COUNT(*)>3;

7.更新sql

sql update cs set score = 90 where cou_id = '1' AND stu_id = '1'; -- 自动化生成 -- UPDATE test.cs t SET t.score = 91 WHERE t.stu_id = 3 AND t.cou_id = 1

8.删除sql

删除记录

sql DELETE FROM stu WHERE name = '4' LIMIT 6; 一般MySQL并不确定删除的这6条记录是哪6条，为了更保险，我们可以使用ORDER BY对记录进行排序。 DELETE FROM stu WHERE name = '4' ORDER BY id DESC LIMIT 6;

删除表

```sql drop table 名字; drop 是直接删除表信息，速度最快，但是无法找回数据

truncate 名字; truncate 是删除表数据，不删除表的结构，速度排次之，但不能与where一起使用 -- 例 truncate student; drop student; ```

9.视图

建立视图 sql create view stu_grad(stu_name,cou_name,grade) as select stu.name,cou.name,cs.score from stu,cs,cou where stu.id=cs.stu_id and cou.id=cs.cou_id

删除视图

sql drop view stu_grad;

二、使用数据库的应用系统设计报告

1.意向应用

在线音乐网站

2.需求分析

根据自己使用经历，以及对QQ音乐，网易云音乐等考察。现总结出有一下的结论

应将用户分为两种，一种是对网站进行维护的管理员，另一种是对网站进行日常使用的客户（消费者）
网站应有的数据有：歌手数据集，歌曲数据，歌单数据，用户对歌曲的操作数据，用户对歌单的操作数据
管理员能对网站的一切数据进行修改
用户对歌曲的操作数据主要是收藏，点赞，播放，下载，评论。歌单则有评论、

3.开发环境

系统架构：B/S

开发语言：JAVA + JS

IDE：IDEA IJ + VSCODE + DATAGRIP

技术栈

名称	技术
前端	VUE2.0 + Element UI
后端	Spring Boot + MySql + mybatis

4.功能设计

5.前端实现

项目结构

客户端

. ├── build // webpack相关配置文件 ├── config // vue基本配置文件 ├── node_modules // 包 ├── index.html // 入口页面 ├── package.json // 管理包的依赖 ├── src // 项目源码目录 │ ├── assets // 静态资源，图片、js、css 等 │ ├── api // 封装请求的 api │ ├── mixins // 公共方法 │ ├── router // 路由 │ ├── store // 管理数据 │ ├── components │ │ ├── AlbumContent.vue // 展示歌单歌手包含的歌曲 │ │ ├── Comment.vue // 评论 │ │ ├── ContentList.vue // 展示歌单歌手区 │ │ ├── LoginLogo.vue // 登录界面的logo │ │ ├── PlayBar.vue // 页面底部的播放控制区 │ │ ├── ScrollTop.vue // 回到顶部 │ │ ├── SongAudio.vue // 接收音乐并播放的位置 │ │ ├── Swiper.vue // 轮播图 │ │ ├── TheFooter.vue // 页脚 │ │ ├── TheHeader.vue // 页头 │ │ ├── TheAside.vue // 播放列表 │ │ ├── Upload.vue // 修改头像 │ │ ├── Info.vue // 个人信息区 │ │ └── search // 搜索区 │ │ ├── SearchSongLists.vue // 按歌单搜索 │ │ └── SearchSongs.vue // 按歌手搜索 │ ├── pages // 组件 │ │ ├── 404.vue // 404 │ │ ├── Home.vue // 首页 │ │ ├── Setting.vue // 设置 │ │ ├── Login.vue // 登录区 │ │ ├── LoginUp.vue // 注册区 │ │ ├── Lyric.vue // 歌词显示区 │ │ ├── MyMusic.vue // 我的收藏区 │ │ ├── Search.vue // 搜索区 │ │ ├── Singer.vue // 歌手区 │ │ ├── SingerAlbum.vue // 歌手详情 │ │ ├── SongList.vue // 歌单区 │ │ └── SongListAlbum.vue // 歌单详情 │ ├── main.js // 入口js文件 │ └── App.vue // 根组件 ├── static // 存放静态资源 ├── test // 测试文件目录 ├── .babelrc // bable 编译配置 └── .gitignore // 提交忽略的文件配置

管理端

. ├── build ├── config ├── node_modules ├── index.html ├── package.json ├── src │ ├── App.vue │ ├── main.js │ ├── api │ ├── assets │ ├── mixins │ ├── components │ │ ├── Header.vue │ │ ├── Home.vue │ │ ├── Sidebar.vue │ │ └── SongAudio.vue │ ├── pages │ │ ├── CollectPage.vue │ │ ├── CommentPage.vue │ │ ├── ConsumerPage.vue │ │ ├── InfoPage.vue │ │ ├── ListSongPage.vue │ │ ├── Login.vue │ │ ├── SingerPage.vue │ │ ├── SongListPage.vue │ │ └── SongPage.vue │ ├── router │ └── store ├── static └── test

6.后端实现

简要类图

项目结构

. ├── avatorImages // 用户头像 ├── img │ ├── singerPic // 歌手图片 │ ├── songListPic // 歌单图片 │ └── songPic // 歌曲图片 ├── song // 存放歌曲 ├── src │ ├── main │ │ ├── java │ │ │ └── com.example.demo │ │ │ ├── config // 配置（跨域） │ │ │ ├── controller // 控制层，接收请求返回响应 │ │ │ ├── dao // 数据操作层 │ │ │ ├── domain // 实体类 │ │ │ ├── service │ │ │ │ └── impl // Service 层的接口 │ │ │ └── HwMusicApplicationTests.java // 项目入口 │ │ └── resources │ │ ├── mapper // mapper.xml文件，操作数据库 │ │ ├── static // 存放静态资源 │ │ ├── templates │ │ ├── application.properties // 连接数据库 │ │ └── generatorConfig.xml // MyBatis Generator 自动生成代码 │ └── test │ └── java │ └── com.example.demo // 测试用的 ├── pom.xml // 添加相关依赖和插件 └── target

项目层次介绍

实体类（domain 目录下）

定义数据库表所对应的实体类。

Mapper 层 / DAO 层（dao、mapper 目录下）

数据操作层：向数据库发送 SQL 语句，完成数据库操作。

分为 Mapper 接口和 Mapper 接口映射文件。 Mapper 接口在 dao 目录下，定义操作数据库的函数（函数不能直接去进行 CURD）， Mapper 接口映射文件在 mapper 目录下，完成对数据库的访问。

Service 层（service 目录下）

服务层：完成业务逻辑处理。调用 Mapper 层操作数据库。

分为 Service 接口 + Service 实现，用面向接口的编程思想，方便后续功能的解藕及扩展。

Controller 层（controller 目录下）

控制层：对请求和响应进行控制，调用 Service 层进行业务逻辑处理，最后将处理好的数据返回给前端。

7.数据库设计与实现

E-R图

数据字典设计（表设计）

实体类

管理员表

中文名称	管理员表
物理表名	admin
主键	id
字段列表
序号	中文名称	列名	数据类型	主键	非空	外键
1	管理员编号	id	int unsigned	是	是	否
2	管理员账号	name	varchar(45)	否	是	否
3	密码	password	varchar(45)	否	是	否

注：name有用户自定义完整性：字段名唯一

DDL：

sql create table admin ( id int unsigned auto_increment primary key, name varchar(45) not null, password varchar(45) not null, constraint name_UNIQUE unique (name) ) charset = utf8;

用户表

中文名称	用户表
物理表名	consumer
主键	id
字段列表
序号	中文名称	列名	数据类型	主键	非空	外键
1	用户编号	id	int unsigned	是	是	否
2	用户账号	name	varchar(45)	否	是	否
3	密码	password	varchar(45)	否	是	否
4	性别	sex	tinyint	否	否	否
5	电话	phone_num	char(15)	否	否	否
6	邮箱	email	char(15)	否	否	否
7	生日	birth	datetime	否	否	否
8	自我介绍	introduction	varchar(255)	否	否	否
9	地点	location	varchar(45)	否	否	否
10	头像	avator	varchar(255)	否	否	否
11	创建时间	create_time	datetime	否	是	否
12	更新时间	update_time	datetime	否	是	否

用户自定义完整性： email，phone_num，username为唯一键

DDL：

sql create table consumer ( id int unsigned auto_increment primary key, username varchar(255) not null, password varchar(100) not null, sex tinyint null, phone_num char(15) null, email char(30) null, birth datetime null, introduction varchar(255) null,b location varchar(45) null, avator varchar(255) null, create_time datetime not null, update_time datetime not null, constraint email_UNIQUE unique (email), constraint phone_num_UNIQUE unique (phone_num), constraint username_UNIQUE unique (username) ) charset = utf8;

歌手表

中文名称	歌手表
物理表名	singer
主键	id
字段列表
序号	中文名称	列名	数据类型	主键	非空	外键
1	歌手编号	id	int unsigned	是	是	否
2	歌手名字	name	varchar(45)	否	是	否
3	性别	sex	tinyint	否	否	否
4	头像	pic	varchar(255)	否	否	否
5	生日	birth	datetime	否	否	否
6	出生地点	location	varchar(45)	否	否	否
7	介绍	introduction	varchar(255)	否	否	否

DDL：

sql create table singer ( id int unsigned auto_increment primary key, name varchar(45) not null, sex tinyint null, pic varchar(255) null, birth datetime null, location varchar(45) null, introduction varchar(255) null ) charset = utf8;\

歌曲表

中文名称	歌曲表
物理表名	song
主键	id
字段列表
序号	中文名称	列名	数据类型	主键	非空	外键
1	歌曲编号	id	int unsigned	是	是	否
2	歌手编号	singer_id	int unsigned	否	是	否
3	歌曲名字	name	varchar(45)	否	是	否
4	介绍	introduction	varchar(255)	否	否	否
5	创建时间	create_time	datetime	否	是	否
6	更新时间	update_time	datetime	否	是	否
7	图片	pic	varchar(255)	否	否	否
8	歌词	lyric	text	否	否	否
9	存放路径	url	varchar(255)	否	是	否

sq create table song ( id int unsigned auto_increment primary key, singer_id int unsigned not null, name varchar(45) not null, introduction varchar(255) null, create_time datetime not null comment '发行时间', update_time datetime not null, pic varchar(255) null, lyric text null, url varchar(255) not null ) charset = utf8;

歌单表

中文名称	用户表
物理表名	song_lis
主键	id
字段列表
序号	中文名称	列名	数据类型	主键	非空	外键
1	编号	id	int unsigned	是	是	否
2	标题	title	varchar(255)	否	是	否
3	封面图片	pic	varchar(255)	否	否	否
4	介绍	introduction	text	否	否	否
5	风格	style	varchqr(10)	否	否	否

DDL：

```sql create table song_list ( id int unsigned auto_increment primary key, title varchar(255) not null, pic varchar(255) null, introduction text null, style varchar(10) default '无' null ) charset = utf8;

```

联系类

收藏表

一开始设计的时候，认为收藏就是一个特殊的歌单，但是在设计歌单的时候，发现并不也一样，所以type和song-list-id并没有什么作用。

中文名称	收藏表
物理表名	collocation
主键	id
字段列表
序号	中文名称	列名	数据类型	主键	非空	外键
1	收藏编号	id	int unsigned	是	是	否
2	用户编号	user_id	int unsigned	否	是	否
3	类型	type	varchar(45)	否	是	否
4	歌曲编号	song_id	int unsigned	否	否	否
5	歌单id	song_list_id	int unsigned	否	否	否
6	创建时间	create_time	datetime	否	是	否

DDL：

sql create table collect ( id int unsigned auto_increment primary key, user_id int unsigned not null, type tinyint not null, song_id int unsigned null, song_list_id int unsigned null, create_time datetime not null ) charset = utf8;

评价表

因为对歌曲的评价和对歌单的评价是差不多的，所以就将两个关系合二为一。

中文名称	评价表
物理表名	comment
主键	id
字段列表
序号	中文名称	列名	数据类型	主键	非空	外键
1	评价编号	id	int unsigned	是	是	否
2	用户编号	user_id	int unsigned	否	是	否
3	歌曲编号	song_id	int unsigned	否	否	否
4	歌单编号	song_list_id	int unsigned	否	否	否
5	评论	content	varchar(255)	否	否	否
6	创建时间	create_time	datetime	否	否	否
7	类型	type	tinyint	否	否	否
8	点赞	up	int unsigned	否	否	否

DDL：

sql create table comment ( id int unsigned auto_increment primary key, user_id int unsigned not null, song_id int unsigned null, song_list_id int unsigned null, content varchar(255) null, create_time datetime null, type tinyint not null, up int unsigned default '0' not null ) charset = utf8;

歌单歌曲表

中文名称	歌单歌曲表
物理表名	list_song
主键	id
字段列表
序号	中文名称	列名	数据类型	主键	非空	外键
1	记录编号	id	int unsigned	是	是	否
2	歌曲编号	song_id	int unsigned	否	是	否
3	歌单编号	song_list_id	int unsigned	否	是	否

DDL：

sql create table list_song ( id int unsigned auto_increment primary key, song_id int unsigned not null, song_list_id int unsigned not null ) charset = utf8;

评分表 | 中文名称 | 评分表 | | | | | | | :------: | :-------: | :--------: | :-------------: | :--: | :--: | :--: | | 物理表名 | list_song | | | | | | | 主键 | id | | | | | | | 字段列表 | | | | | | | | 序号 | 中文名称 | 列名 | 数据类型 | 主键 | 非空 | 外键 | | 1 | 记录编号 | id | int unsigned | 是 | 是 | 否 | | 2 | 歌单编号 | songListId | bigint unsigned | 否 | 是 | 否 | | 3 | 用户编号 | consumerId | bigint unsigned | 否 | 是 | 否 | | 4 | 评分 | score | int unsigned | 否 | 是 | 否 |

DDL：

sql create table `rank`// 此处rank为关键字，所以必须加引号 ( id bigint unsigned auto_increment primary key, songListId bigint unsigned not null, consumerId bigint unsigned not null, score int unsigned default '0' not null, constraint consumerId unique (consumerId, songListId) ) charset = utf8;

总览

蓝色是虚拟外键

数据库交互设计

因为后端使用的是spring boot 框架，所以数据库的交互使用的常用的Mybatis + JDBC。MyBatis 是一款优秀的持久层框架，它支持自定义 SQL、存储过程以及高级映射。MyBatis 免除了几乎所有的 JDBC 代码以及设置参数和获取结果集的工作。MyBatis 可以通过简单的 XML 或注解来配置和映射原始类型、接口和 Java POJO（Plain Old Java Objects，普通老式 Java 对象）为数据库中的记录。

传统的JDBC操作数据库：

``` java ResultSet rs = null; //4.向数据库发sql,并获取代表结果集的resultset String sql = "select id,name,password,email,birthday from users"; rs = st.executeQuery(sql);

//5.取出结果集的数据 rs.afterLast(); rs.previous(); System.out.println("id=" + rs.getObject("id")); System.out.println("name=" + rs.getObject("name")); System.out.println("password=" + rs.getObject("password")); System.out.println("email=" + rs.getObject("email")); System.out.println("birthday=" + rs.getObject("birthday")); ```

使用mybatis后的数据库操作

xml <select id="verifyPassword" resultType="java.lang.Integer"> SELECT count(*) FROM admin where name = #{param1} and password = #{param2} ; </select>

id 为对应接口方法的名字，resultType为查询数据库后将数据自动封装的类型。以本语句为例，SQL语句对应的是一个名叫verifyPassword的方法，返回类型是Java.lang.Interger类型。MyBatis主要减少的是对sql语句写封装语句的时间，可以在这上面极大的减少相应的开发量。

mybatis配置如下

``` properties

数据库访问的相关配置

spring.datasource.url = jdbc:mysql://127.0.0.1:3306/keshe?serverTimezone=Asia/Shanghai spring.datasource.username=root spring.datasource.password=root spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver