【数据库】关系型数据库MySQL

最新推荐文章于 2024-01-09 14:23:24 发布

potizo

最新推荐文章于 2024-01-09 14:23:24 发布

阅读量1.1k

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/potizo/article/details/103940631

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

1.数据库简介

人类在进化的过程中，创造了数字、文字、符号等来进行数据的记录，但是承受着认知能力和创造能力的提升，数据量越来越大，对于数据的记录和准确查找，成为了一个重大难题。数据库系统解决的问题：持久化存储，优化读写，保证数据的有效性。
计算机诞生后，数据开始在计算机中存储并计算，并设计出了数据库系统。常见的数据库如下：
在这里插入图片描述

1.1数据库分类

关系型数据库：指采用了关系模型来组织数据的数据库。关系模型指的就是二维表格模型，而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。主流的关系型数据库有:
Oracle、Microsoft SQL Server、MySQL、PostgreSQL，SQLite、MariaDB(MySQL的一个分支)、Microsoft Access、SAP。

非关系型数据库：指非关系型的，分布式的，以键值对存储且结构不固定，可以减少一些时间和空间的开销。非关系型数据库都是针对某些特定的应用需求，主要分为以下几类：
1). 面向海量数据访问的面向文档数据库：MongoDB、Amazon DynamoDB、Couchbase等。
2). 面向高性能并发读写的key-value数据库: Redis、 Memcached等。
3). 面向搜索数据内容的搜索引擎：Elasticsearch，Splunk，Solr，MarkLogic和Sphinx等。
4). 面向可扩展性的分布式数据库：Cassandra，HBase等

2.关系型数据库

2.1 E-R模型

在这里插入图片描述
当前物理的数据库都是按照E-R模型进行设计的,
• E表示entry，实体
• R表示relationship，关系
• 一个实体转换为数据库中的一个表
关系描述两个实体之间的对应规则，包括: 一对一 ,一对多, 多对多

2.2 三大范式

经过研究和对使用中问题的总结，对于设计数据库提出了一些规范，这些规范被称为范式
• 第一范式（1NF)：列不可拆分 , 即无重复的域。
符合第一范式的特点就有：有主关键字、主键不能为空、主键不能重复,字段不可以再分。
如下图就不符合第一范式的列不可拆分！！！
在这里插入图片描述
正确的应该为：

• 第二范式（2NF)：唯一标识，即拥有实体的唯一标识(eg: 身份证、id号等)。
第二范式是指每个表必须有且仅有一个数据元素为主关键字(Primary key),其他数据元素与主关键字一一对应。这种关系为函数依赖。
符合第二范式的特点就有：满足第一范式的前提下，消除部分函数依赖。
在这里插入图片描述
上图也不符合第二范式。因为班级地址部分依赖于关键字班级编号，所以要变为两个表：

数据冗余过大，插入异常，删除异常，修改异常的问题，如下表所示：

1). 每一名学生的学号、姓名、系名、系主任这些数据重复多次。每个系与对应的系主任的数据也重复多次 数据冗余过大
2). 假如学校新建了一个系，但是暂时还没有招收任何学生,那么是无法将系名与系主任的数据单独地添加到数据表中去的 插入异常
3). 假如将某个系中所有学生相关的记录都删除，那么所有系与系主任的数据也就随之消失了。删除异常
4). 假如李小明转系到法律系，那么为了保证数据库中数据的一致性，需要修改三条记录中系与系主任的数据。修改异常
正确的如下：
在这里插入图片描述
• 第三范式（3NF)：引用主键 ,即每列数据都与主键直接相关。
说明：关系型数据库有六种范式。一般说来，数据库只需满足第三范式(3NF）就行了
符合第三范式的特点就有：不存在非主属性对码的传递性依赖以及部分性依赖。
如下：
在这里插入图片描述
不符合第三范式，但完全满足了第二范式。因为奖金等级和奖学金存在传递依赖，更改为：

三大范式只是一般设计数据库的基本理念，可以建立冗余较小、结构合理的数据库。如果有
特殊情况，当然要特殊对待，数据库设计最重要的是看需求跟性能，需求>性能>表结构。
所以不能一味的去追求范式建立数据库。

3. Mysql简介与安装

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一。
在这里插入图片描述

3.1 MySQL常用存储引擎分析

数据库存储引擎是数据库底层软件组织，进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能， MySQL的核心就是存储引擎。
在这里插入图片描述
MySQL查询存储引擎SQL语句：SHOW ENGINES

3.2 mysql的安装

3.2.1.Redhat下安装mariadb

在这里插入图片描述
数据库服务管理

3.2.2.Ubuntu下安装mariadb

sudo apt-get update
sudo apt-get install software-properties-common
sudo apt-get install mariadb-server

记录一下，我在Ubuntu下安装mariadb遇到的问题：
1）.apt-get update 出现E: Could not get lock /var/lib/apt/lists/lock
$ sudo apt-get update Reading package lists... Done E: Could not get lock /var/lib/apt/lists/lock - open (11: Resource temporarily unavailable) E: Unable to lock directory /var/lib/apt/lists/
原因：
资源锁被别的进程占用

解决办法：
方法一：kill掉占用资源的进程（推荐使用）

$ ps -e | grep apt
 2708 ?        00:00:00 apt.systemd.dai
 2713 ?        00:00:00 apt.systemd.dai

查看哪些进程在占用资源
从以上的结果看到占用资源的的PID（进程号）是2708和2713，那么kill掉这两个进程即可

$ sudo kill -9 2708 2713

方法二：重启释放资源

$ sudo reboot

参考：https://blog.csdn.net/zyxlinux888/article/details/6358615

2）.sudo apt-get update 失败
我一开始以为是网速问题，但是真的update一整天都还失败时，我才想起查查别人怎么处理这种问题。
原因：有两种可能，一个是被墙了——换成国内源即可; 另一个是因为architecture不兼容——删掉不兼容的architecture
方法一：换源

cd /etc/apt
sudo cp sources.list sources.list.bak
sudo gedit sources.list

换成清华镜像源，sources.list 的内容更改如下：

deb https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ xenial main restricted universe multiverse
# deb-src https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ xenial main restricted universe multiverse
deb https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ xenial-updates main restricted universe multiverse
# deb-src https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ xenial-updates main restricted universe multiverse
deb https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ xenial-backports main restricted universe multiverse
# deb-src https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ xenial-backports main restricted universe multiverse
deb https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ xenial-security main restricted universe multiverse
# deb-src https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ xenial-security main restricted universe multiverse

再执行sudo apt-get update，即可。
很幸运，我失败的原因就是这一种，所以更换了源之后，update成功。

方法二：移除不兼容的architecture（这个方法我没有尝试，但是留作备用）

sudo dpkg --print-architecture
sudo dpkg --print-foreign-architectures

如果是开发板，就需要移除amd64；如果是PC机，就需要移除arm64；

以 jetson Tx2为例，如果打印出现 amd64,那就移除它

sudo apt-get remove .*:amd64
sudo dpkg --remove-architecture amd64
sudo apt-get update

千万不要删错了，不然可以考虑重装系统了

最后update时出现一些抓取不到的404问题，没有关系，可以正常apt-get install
参考：https://blog.csdn.net/qq_35759574/article/details/82862827

3.2.3.Windows下安装mysql

下面是msi安装文件，自提！
链接: https://pan.baidu.com/s/1LVcMYT9zFXp64E43jDpzFA 提取码: wdbd
安装教程csdn有很多，按照步骤走即可。
此外，我还安装了图形界面操作mysql的软件Navicat 12.安装没有问题，但是在连接mysql时，有遇到问题，也记录一下。
1.出现错误代码2059
解决方法：https://blog.csdn.net/debimeng/article/details/94961432
2.用pycharm连接mysql连接不上，出现报错：Server returns invalid timezone.Go to ‘Advanced’ tab and set ‘serverTimezon’
解决方法：https://blog.csdn.net/qq_39397165/article/details/103433994

3.3 安全性密码设置

在这里插入图片描述

3.4 允许远程连接

关闭mysql服务器的防火墙

systemctl stop firewalld
systemctl disable firewalld

用户授权: 允许root用户通过westos密码在任意主机(%)远程登陆并操作数据库;

grant all privileges on *.* to potizo @'%' identified by '123456';
  #这里面的%可以换成客户端的ip，123456是用户potizo登陆数据库的密码。

客户端主机测试(安装mariadb软件)

在这里插入图片描述

实验如下：客户端: 172.25.254.18 服务端：172.25.254.197 需求：客户端18可以远程连接服务端197数据库

服务端操作：
1).服务端关闭mysql服务器的防火墙：
systemctl stop firewalld
systemctl disable firewalld
2).登陆自己的数据库：mysql -uroot -p
3).查看mysql中数据库表user的三列(查看此时都有谁可以连接服务端数据库)：select Host，User，Password from mysql.user
4).允许客户端以potizo身份密码为123456授权登陆,%表示任意 ip ; * .* 表示所有数据库中的所有数据库表：
grant all on . to potizo@’%’ identified by ‘123456’;
客户端测试：
1).mysql -h 172.25.254.18 -upotizo -p123456 #以root身份密码为westos登陆服务端18的mysql数据库
2).删除远程登陆指定的用户授权：drop user root@’%’; %:任意用户 %换成指定ip表示删除指定用户的登陆授权。

3.5 找回密码

#1)关闭mariadb服务
systemctl stop mariadb
#2）跳过授权表
mysqld_safe --skip-grant-table &
#3）修改root密码
mysql
> update mysql.user set Password=password('123456') where User='root';
#4）关闭跳过授权表的过程，启动mariadb服务，使用新密码即可
ps aux | grep mysql
kill -9 pid
mysql -uroot -p

4.数据库操作

4.1 数据库操作

在这里插入图片描述

4.2 表操作

在这里插入图片描述
表创建: 数据完整性

一个数据库就是一个完整的业务单元，可以包含多张表，数据被存储在表中
在表中为了更加准确的存储数据，保证数据的正确有效，可以在创建表的时候，为表添加一些强制性的验证，包括数据字段的类型、约束

表创建：字段类型
在mysql中包含的数据类型很多，这里主要列出来常用的几种:

数字：int,decimal, float
字符串：varchar,text
日期：datetime
布尔：bool

表的创建: 约束

主键primary key
非空not null
惟一unique
默认default
外键foreign key
自动增长 auto_increment

在这里插入图片描述

4.3 数据操作

在这里插入图片描述

4.4 备份与恢复

在这里插入图片描述

5.查询操作

5.1 查询的基本语句

select * from 表名;

from关键字后面写表名,表示数据来源于是这张表
select后面写表中的列名,如果是*表示在结果中显示表中所有列
在select后面的列名部分,可以使用as为列起别名,这个别名出现在结果集中
如果要查询多个列,之间使用逗号分隔

5.2 消除重复行

在select后面列前面使用distinct可以消除重复的行
select distinct gender from students;
在这里插入图片描述

5.3 条件

在这里插入图片描述
使用where子句对表中的数据筛选,结果为true的行会出现在结果集中
select * from 表名 where 条件;

在这里插入图片描述

优先级
•小括号,not,比较运算符,逻辑运算符
•and比or先运算,如果同时出现并希望先算or,需要结合()使用

5.5 聚合

为了快速得到统计数据,提供了5个聚合函数：
在这里插入图片描述

5.6 排序

为了方便查看数据,可以对数据进行排序：

select * from 表名
order by 列1 asc|desc, 列2 asc|desc, ...

将行数据按照列1进行排序,如果某些行列1的值相同时,则按照列2排序,以此类推
默认按照列值从小到大排列
asc从小到大排列,即升序， desc从大到小排序,即降序

在这里插入图片描述

5.7 分组

按照字段分组,表示此字段相同的数据会被放到一个组中
分组后,只能查询出相同的数据列,对于有差异的数据列无法出现在结果集中
可以对分组后的数据进行统计,做聚合运算

5.8 获取部分行

当数据量过大时,在一页中查看数据是一件非常麻烦的事情：

select * from 表名
limit start，count

从start开始,获取count条数据
start索引从0开始

5.9 多表查询

eg: A表：
aid num
1 10
2 20
3 30

B表:
bid bname
1 粉条
3 粉丝
4 粉带

left join(左联接)： select * from A left join B on A.aid = b.bid;
right join(右联接): select * from A right join B on A.aid = b.bid;
inner join(等值联接): select * from A inner join B on A.aid = b.bid;

两张表的数据如下：
在这里插入图片描述
左联接：

右联接：

等值联接：

三表查询：##查询所有学生的sname、cname、degree列
在这里插入图片描述

5.10 总结

完整的select语句：

select distinct * 
from 表名
where ...
group by... having...
order by...
limit start,count

注意: 实际使用中,只是语句中某些部分的组合,而不是全部
执行顺序为:

from 表名
where …
group by …
select distinct *
having …
order by …
limit star,count

#*********************************关于查询条件************************************

1、 查询students表中的所有记录的sname、ssex和class列。
MariaDB [Blog]> select  sname,ssex,class from students;

2、 查询教师所有的单位即不重复的Depart列。
MariaDB [Blog]> select distinct depart  from  teachers;


3、 查询students表的所有记录。
MariaDB [Blog]> select  *  from students;

4、 查询scores表中成绩在60到80之间的所有记录。
MariaDB [Blog]> select * from scores where degree between 60 and 80;


5、 查询scores表中成绩为85，86或88的记录。
MariaDB [Blog]> select * from scores where degree=85 or degree=86 or degree=88;
MariaDB [Blog]> select * from scores where degree in (85,86,88);

6、 查询students表中“95031”班或性别为“女”的同学记录。（作业）
select * from students where xxxxxx or xxxxx;

查询课程名称包含数学的课程信息。(模糊查询)
MariaDB [Blog]> select * from courses where cname like '%数学%';
MariaDB [Blog]>  select * from courses where cname like '__数学';

======
1). 查询95033班和95031班全体学生的记录。
2). 查询存在有85分以上成绩的课程Cno.
3). 查询Student表中不姓“王”的同学记录。
4). 查询所有任课教师的Tname和Depart.
5). 查询所有未讲课的教师的Tname和Depart.

***************************************关于排序*********************************************
7、 以class降序查询students表的所有记录。
MariaDB [Blog]> select * from students order by class desc;
MariaDB [Blog]> select * from students order by class;（默认升序）

8、 以cno升序、degree降序查询scores表的所有记录。
    以cno升序、degree降序: 当cno相同时， 按照degree降序排列。
    cno degree
    1   3
    1   2
    2   3
MariaDB [Blog]> select * from scores order by cno,degree desc;


********************************************关于聚合函数*************************************
9、 查询“95031”班的学生人数。
MariaDB [Blog]> select * from students where class='95031';
MariaDB [Blog]> select count(*) from students where class='95031';
MariaDB [Blog]> select count(*) as studentCount from students where class='95031'; (最终版)

10、查询‘3-105’号课程的平均分。
MariaDB [Blog]> select avg(degree) as avgScore  from scores where cno='3-105';

====
查询Student表中最大和最小的Sbirthday日期值。


*******************************************关于group by 和having*****************************
11、查询scores表中至少有5名学生选修的并以3开头的课程的平均分数。
1). 至少有5名学生选修(聚合函数): count(*) >= 5
2). 以3开头的课程(模糊查询): cno like '3%'
3). 平均分数(聚合函数): avg(degree)
select cno,avg(degree) from scores where cno like '3%'  group by cno having count(cno)>=5;



12、查询最低分大于70，最高分小于90的Sno列。
select * from scores where max(degree) < 90 and min(degree) > 70;(错误的SQL语句)
MariaDB [Blog]> select sno  from scores group by sno having max(degree)<90 and min(degree)>70;（正确的）

======
查询scores中选学一门以上课程的同学中分数为非最高分成绩的记录。
- 非最高分成绩： degree != max(degree)
- 选学一门以上课程: count(cno) > 1
- 分组: sno
MariaDB [Blog]> select * from scores group  by sno having count(cno)>1 and degree!=max(degree);


****************************************关于limit**************************
13、查询scores表中的最高分的学生学号和课程号。
select sno,cno from scores order by degree desc limit 1;(不是最优的方式， 如果最高分有多名学生会出现问题)
MariaDB [Blog]> select sno,cno from scores where degree=(select max(degree) from scores);(较优的方式)



查询成绩最高的前5名学生信息。
MariaDB [Blog]> select * from scores order by degree desc limit 5;(默认从第0个索引开始， 拿前5条数据)
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |   92.0 |
| 107 | 3-105 |   91.0 |
| 105 | 3-105 |   88.0 |
| 103 | 3-245 |   86.0 |
| 101 | 6-166 |   85.0 |
+-----+-------+--------+
5 rows in set (0.00 sec)

MariaDB [Blog]> select * from scores order by degree desc limit 2,3;(从第2个索引开始获取3条数据)

+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 105 | 3-105 |   88.0 |
| 103 | 3-245 |   86.0 |
| 101 | 6-166 |   85.0 |
+-----+-------+--------+
3 rows in set (0.01 sec)


************************************多表查询********************************
14、查询所有学生的sname、cno和degree列。
MariaDB [Blog]> select sname,cno,degree from students inner join scores on students.sno=scores.sno;
15、查询所有学生的sname、cname和degree列。（三表拼接）
MariaDB [Blog]> select distinct  sname,cname,degree from students inner join scores
on(students.sno=scores.sno) inner join courses  on(scores.cno=courses.cno) order by sname;

16、查询所有学生的Sname、Cname和Degree列。

17、查询“95033”班所选课程的平均分。

potizo

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
【数据库】关系型数据库MySQL

关系型数据库MySQL数据库简介数据库分类关系型数据库E-R模型三大范式Mysql简介与安装MySQL常用存储引擎分析数据库操作数据库简介人类在进化的过程中，创造了数字、文字、符号等来进行数据的记录，但是承受着认知能力和创造能力的提升，数据量越来越大，对于数据的记录和准确查找，成为了一个重大难题。数据库系统解决的问题：持久化存储，优化读写，保证数据的有效性。计算机诞生后，数据开始在计算机中...
复制链接

扫一扫