03.MySQL数据库

发布于 2025-07-22  108 次阅读

一、数据库概述

1.数据库的概念

  • 数据库是用于存储数据的仓库，本质上是一个文件系统，按照一定的格式将数据高效地、有条理地存储起来

2.数据库的特点

  • 可以结构化地存储大量的数据
  • 可以有效地保持数据的一致性和完整性
  • 数据库的读写效率极高

3.数据库的分类

  • 关系型数据库
    • 概念：指采用了关系模型来组织数据的数据库
    • 关系模型：简单理解就是二维表格模型，有行有列
    • 关系型数据库大多存储在硬盘，查询速度相对较慢，但安全性相对较高
  • 非关系型数据库
    • 概念：又称为NoSQL(not only SQL)，强调使用key-value方式存储
    • 非关系型数据库大多存储在内存，查询速度相对较快，但安全性相对较低

4.常见数据库

  • 关系型数据库：MySQL,Orecle,DB2,SQLserver,SQLite
  • 非关系型数据库:Redis,HBase,Mong

二、在Linux中安装MySQL

1.卸载系统中的mariadb-lib

  1. 查看系统中是否安装了mariadb：rpm -qa | grep mariadb
  2. 卸载mariadb：rpm -e mariadb的软件包名 --nodeps
  3. 删除mariadb的配置文件：rm -rf /etc/my.cnf（一般是存放在这里的）
  4. 重启系统：reboot

2.安装MySQL

  1. 上传安装包并解压
  2. 安装依赖：yum install numactl openssl-devel.x86_64 openssl.x86_64 autoconf perl.x86_64 perl-devel.x86_64 perl-JSON.noarch -y
  3. 安装MySQL（注意顺序）【默认安装目录/var/lib/mysql）
    • rpm -ivh （mysql-community-common-版本号)开头的文件
    • rpm -ivh （mysql-community-libs-版本号)开头的文件
    • rpm -ivh （mysql-community-client-版本号)开头的文件
    • rpm -ivh （mysql-community-server-版本号)开头的文件
    • 解压后还有其他文件，若中途提示缺少哪一个文件的依赖，则安装对应的文件即可

3.初始化数据库

mysqld --initialize --console

4.授权目录给MySQL

chown -R mysql:mysql /var/lib/mysql/

5.启动MySQL

  • 启动：systemctl start mysqld
  • 设置开机启动：systemctl enable mysqld

6.查看运行状态

systemctl status mysqld

7.查看临时密码

cat /var/log/mysqld.log

8.用临时密码进行登录

mysql -u root -p

9.修改密码

修改密码：alter USER 'root'@'localhost' IDENTIFIED BY '新密码';（注意要加分号）

刷新权限信息：flush privileges

10.授权远程连接

  1. 查看用户：show databases;
  2. 创建新用户：create user 'root'@'%';
  3. 设置密码：alter USER 'root'@'%' IDENTIFIED BY '新密码';
  4. 刷新权限信息：flush privileges

三、MySQL的登录登出

1.登录

  • 本地连接：mysql -u 用户名 -p
  • 远程连接：mysql -h 远程服务器IP地址 -P 端口号 -u 用户名 -p（MySQL端口号默认为3306）

2.退出

  • 方法一：exit;
  • 方法二:quit
  • 方法三：Ctrl + d

四、SQL语句

1.SQL语句的分类

  • DDL(Data Definition Language)：数据定义语言，用于定义数据库对象
  • DML(Data Manipulation Language)：数据操作语言，用于更新数据库对象
  • DQL(Data Query Language)：数据查询语言，用于查询数据库对象
  • DCL(Data Control Language)：数据控制语言，用于设置数据库权限和安全级别以及创建用户

2.SQL基本语法

  • SQL语句可以单行或多行书写，以';'结尾
  • MySQL数据库的SQL语句不区别大小写
  • 允许使用注释：单行注释#或--（结尾有空格），多行注释/* */

五、数据库操作

1.数据库操作概览

  • 创建 
    #创建数据库
create database my_database;
#创建数据库并指定默认字符集
create database my_database default charset utf8;
#检验数据库是否存在，不存在则创建数据库 
create database if not exists my_database

#注意：不能创建同名的数据库

#my_database为任意名称，下方的my_table同理
  • 查询 
    #查询数据库
show database;
  • 删除 
    #删除数据库
drop database my_database;
  • 选择 
    #选择数据库
use my_database;
  • 查看正在使用的数据库 
    #查看正在使用的数据库
select database();

2.数据表的创建

  • 基本语法 
    create table 数据表名称(
  字段1 字段类型 [字段约束]
  字段2 字段类型 [字段约束]
  ...
)engine=使用的引擎 default charset=编码集;

#示例
create table my_table(
  id tinyint,
  username varchar(20),
  password char(32)
);
  • 常用类型
    • tinyint：微整型，有符号型范围-128~127，无符号型范围0~255
    • varchar：变化长度的字符串，理论范围65535，但有部分字符用于保存长度信息，此外编码占用的长度不同，例如GBK占用2个字节长度，UTF-8占用3个字节长度
    • text：文本类型

3.数据表的查询

  • 基本语法 
    #显示所有数据表
use my_database;#注意要先选定数据库
show tables;

#显示数据表的详细信息
desc my_table;

4.数据表的修改

  • 数据表字段的修改 
    #添加数据表字段
alter table my_table add 新字段名称 新字段类型 first|after 其他字段名称;
#first：将新字段放在第一位
#after：将新字段放在指定字段后面

#修改字段名称或类型
alter table my_table change 旧字段名称 新字段名称 新字段类型;

#删除字段
alter table my_table drop 字段名称;
  • 数据表名称的修改 
    rename table 旧名称 to 新名称;

5.数据表的删除

  • 基本语法 
    drop table my_table;

六、字段类型详解

1.整数类型

  • tinyint：微整型，-128~127
  • samllint：小整型，-32768~32767
  • mediumint：中整型，-8388608~8388607
  • int：整型，-2147483648~2147483647

2.浮点类型

  • float：单精度浮点型
  • double：双精度浮点型
  • decimal：定点数，例如decimal(10,2)表示最大长度为10，保留2位小数

3.日期类型

  • year：YYYY
  • time：HH:MM:SS
  • date：YYYY-MM-DD
  • datetime：YYYY-MM-DD HH:MM:SS
  • timesstamp：YYYY-MM-DD HH:MM:SS
  • 注：Y-年，M-月，D-日，H-时，M-分，S-秒

4.文本类型

  • char(m)：0~255固定长度，m表示字符数
  • varchar(m)：0~65535变化长度
  • text：0~65535字节
  • mediumtext：0~167772150字节
  • longtext：0~4294967295字节

七、DML数据操作语言

1.数据的增加操作

#增加：insert
insert into my_table([字段1,字段2,...]) values ([字段1的值,字段2的值,...]);

2.数据的修改操作

#修改：update
update my_table set 字段1=新值,字段2=新值,...where 更新条件;

3.数据的删除操作

#删除：delete
delete from my_table where 更新条件;

4.数据表的清空操作

#1.方式一，这种方式可以在原生环境中使用，在DataGrip中被限制
delete from my_table;
#2.方式二
truncate my_table;

八、SQL约束

1.主键约束(primary key)

  • 特点
    • 主键约束唯一标识数据表中的每条记录
    • 主键必须包含唯一的值
    • 主键不能包含NULL值
    • 每个表有且仅有一个主键
  • 遵循原则
    • 主键应对用户来说是无意义的
    • 永远不要更新主键
    • 主键不应包含动态数据
    • 主键应由计算机自动生成
  • 创建主键约束 
    create table my_table(
  id int primary key #这句代码就生成了主键
);
  • 删除主键约束 
    alter table my_table drop primary key;
  • 自动增长关键字（auto_increment） 
    create table my_table(
id int auto_increment primary key #生成了自动增加的主键
);

#下面这种创建方式也是可以的
create table my_table(
  id int auto_increment,
  primary key(id)
);

2.非空约束(NOT NULL)

  • 非空约束强制列不接受NULL值，这意味着如果不向这种列添加值，则无法插入或更新记录
  • 创建非空约束 
    create table my_table(
name varchar not null #此处添加了非空约束，意味着每条记录必须有name值
);

3.唯一约束(UNIQUE)

  • 特点
    • 唯一约束唯一标识数据表中的记录，大多为手动设置
    • 唯一约束不要求该列不为空，可以出现NULL值
    • 数据表中可以有多个唯一约束
  • 创建唯一约束 
    create table my_table(
username varchar unique #唯一约束，记录间的username不能重复
);

4.默认值约束(default)

  • 指定某列的默认值，若插入记录时未向该列赋值，则使用默认值
  • 创建默认值约束 
    create table my_table(
age tinyint default 18 #默认值约束，age默认为18
);

5.外键约束(foreign key)

  • 外键约束用于多表之间的关联使用
  • 多表之间通过某个字段产生联系，这个字段在本表中称为主键，另一个表中称为外键

九、DQL数据查询语言

1.简单查询

select [distinct] 字段 from 表 查询子句;#字段出填写'*'表示查询所有字段

2.SQL五子句

  • where子句-条件查询
    • 比较查询
    符号 功能
    > < >= <= = <> != 大于 小于 大于等于 小于等于 等于 不等于 不等于
    • 范围查询
    符号 功能
    BETWEEN ... AND ... 在闭区间内
    IN(set) 在set列表内
    • 模糊查询
    符号 功能
    LIKE '_a%' %表示任意个字符，_表示一个字符
    • 非空查询
    符号 功能
    IS NULL IS NOT NULL 判断是否为空
    • 逻辑查询
    符号 功能
    and or not 同时成立 任一成立 不成立
  • order by子句-排序查询
    • ASC：升序排列
    • DESC：降序排列
  • group by子句和having子句
    • 聚合函数
    select 聚合函数 from my_table;
    聚合函数 功能
    count() 统计不为NULL的行数
    sum() 计算指定列的数值和，若不为数值类型则返回0
    max() 计算指定列的最大值，若为字符串类型则使用字符串排序运算
    min() 计算指定列的最小值，若为字符串类型则使用字符串排序运算
    avg() 计算指定列的平均值，若不为数值类型则返回0
    • 分组查询
    group by 列名 [having 条件表达式] [with rollup];

#HAVING：用于过滤分组后的数据，类似于where的使用
#WITH ROLLUP：在所有记录的最后添加一条记录，显示聚合函数的统计和计算结果
  • limit子句
    • 限制查询
    select * from my_table limit offset,count;#当只填写一个参数时，默认赋值给COUNT

#OFFSET：表示查找开始的索引，默认为0
#COUNT：表示查询数量限制

十、多表查询

1.交叉连接(cross join)

#方式一
select * from my_table_1 cross join my_tabel_2;
#方式二
select * from my_table_1,my_table_2;

2.内连接(inner join)

#用于查询多表中满足关联条件的共同部分
select * from my_table_1 inner join my_table_2 on 数据表1.字段 = 数据表2.字段;

#可以使用 数据表名 [as] 别名 来进行简写

3.外连接

  • 左外连接查询
    • 以左表作为主表查询右表中的数据，会打印左表所有记录，若左表的数据在右边不存在，则右表对应项不存在用NULL填充
    select 字段 from 表1 left join 表2 on 表1.字段1 = 表2.字段2;
  • 右外连接查询
    • 以右表作为主表查询左表中的数据，会打印右表所有记录，若右表的数据在左边不存在，则左表对应项不存在用NULL填充
    select 字段 from 表1 right join 表2 on 表1.字段1 = 表2.字段2;

4.子查询

  • 子查询：又称为嵌套查询，指在一个select语句中嵌套了一个select语句

十一、数据库设计三范式

1.相关概念

  • 范式：设计关系型数据库时，会遵循一定的规范要求，设计出合理的关系型数据库。这些不同的规范要求就被称为不同的范式，各种范式呈递增规范，越高的范式数据冗余越少。
  • 数据冗余：同一数据存储在不同的数据库中，称为数据冗余

2.范式的划分

  • 第一范式（1NF）
  • 第二范式（2NF）
  • 第三范式（3NF）
  • 巴斯·科德范式（BCNF）
  • 第四范式（4NF）
  • 第五范式（5NF）

3.范式要求

  • 第一范式
    • 强调字符的原子性，一个字段不能再分为多个字段。
  • 第二范式
    • 数据表必须有一个主键
    • 非主键字段必须完全依赖于主键，而不能部分依赖
  • 第三范式
    • 非主键字段必须直接依赖于主键，而不能传递依赖

十二、E-R模型和数据表关系

1.E-R模型

  • E-R模型：即实体-关系模型，用于描述数据库存储数据的结构模型

2.表现方式

  • 实体：使用矩形标识，标注实体名称
  • 属性：使用椭圆标识，标注属性名称
  • 关系：使用菱形标识，标注关系名称

3.数据表关系

  • 一对一关系
  • 一对多关系
  • 多对多关系

十三、PyMySQL

1.安装与卸载PyMySQL模块

  • 方式一：命令行安装
    • 安装pip install pymysql
    • 卸载pip uninstall pymysql
  • 方式二：PyCharm安装
    • File-Settings-Project-Python Interpreter-'+'-搜索安装

2.PyMySQL的使用

  • 导入PyMySQL包 
    import pymysql
  • 创建连接对象 
    对象名 = pymysql.connect(参数列表)

"""
参数列表
host：IP地址，本机则为localhost
port：端口号
user：用户名
password：密码
datebase：连接的数据库名称
charset：编码格式
"""

#可能会要求安装和导入cryptography包，注意报错信息
#如果使用的是Python3.7版本，这个库会弹出warning，但不影响执行
  • 基本操作 
    #关闭连接
对象名.close()

#提交数据
对象名.commit()

#撤销数据
对象名.rollback()

3.游标

  • 获取游标对象 
    #获取游标对象
游标名 = 对象名.cursor()
  • 使用游标执行SQL语句 
    execute(operation [parameters])

#返回受影响的行数

#示例
sql = "select * from student"
row_count = cursor.execute(sql)
#row_count返回受影响的行数
#cursor为游标对象
#sql存储的是SQL命令

4.使用PyMySQL实现数据库的查询

  • 获取查询结果 
    #获取一条数据
游标名.fetchone()

#获取全部数据
游标名.fetchall()

#返回元组
  • 关闭游标 
    游标名.close()

5.使用PyMySQL实现数据库的增删

  • 增删操作之后，需要进行commit来提交数据实现持久化
  • 通过execute提交对应的SQL语句就可以实现增删改
  • 一般使用try语句来包含增删改操作，出现异常时执行rollback

学习是一段漫长的旅途