Python MySQL 数据库技术 part 2

最新推荐文章于 2024-08-07 19:07:34 发布

id-liny

最新推荐文章于 2024-08-07 19:07:34 发布

阅读量316

点赞数

本文为博主原创文章，未经博主允许不得转载。

本文链接：https://blog.youkuaiyun.com/weixin_45872999/article/details/120999841

版权

Python 同时被 2 个专栏收录

22 篇文章

订阅专栏

Mysql

6 篇文章

订阅专栏

本文深入探讨了MySQL的基本数据类型，包括字符串、数值、日期时间及二进制类型，并详细介绍了数据类型约束、运算符、主键和外键。此外，文章还讨论了MySQL的索引、表引擎差异以及字符集的选择。最后，讲解了如何进行数据库和数据表的操作，如创建、查看、修改和删除。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

4.MySQL的基本常用数据类型

数据类型是定义列中可以存储什么类型的数据以及该数据实际怎样存储的基本规则。
数据类型限制存储在数据列表中的数据，如，数值数据类型列只能接受数值类型的数据。
设计表时，应特别重视所用的数据类型，使用错误的数据类型可能会严重的影响应用程序的功能和性能。
更改包含数据的列不是一件小事，且这样做可能导致数据丢失。

数据类型：整型，浮点型，字符串，日期等。

4.1 字符串数据类型

最常用的数据类型是串数据类型。存储串，如名字、地址、电话号码、邮政编码等。
不论何种形式的串数据类型，串值都必须括在引号内。
有两种基本的串类型，定长串和变长串。

定长串 CHAR

接受长度固定的字符串，长度是在创建表时指定的。
定长列不允许存储多于指定长度的数据。
指定长度后，就会分配固定的存储空间，用于存放数据。
char(7) 不论实际插入多少字符，都会占用7个字符位置。

变长串 VARCHAR

存储可变长度的字符串
varchar(7) 若实际插入4个字符串，则只占4个字符的位置，当然插入的数据长度不能超过7个字符串。

MySQL处理定长列远比变长列快得多。

TEXT 最大长度为64k的变长文本

ENUM 最大64k个串组成的一个预定义集合的某个串；
LONGTEXT 与TEXT 相同，但最大长度为4GB；
MEDIUMTEXT 与TEXT 相同，但最大长度为16k；
…

4.2 数值类型

数值数据类型存储数值。MySQL支持多种数值数据类型，
每种存储的数值具有不同的取值范围，支持的取值范围越大，所需存储空间越多。
数值不应在括号内！

INT 整数值
支持 -2147483648~2147483647（若是UNSIGNED，支持 0 ~ 4294967295）的值。
TINYINT 整数值
支持 -128 ~ 127（若是UNSIGNED，支持0~255）的值。
FLOAT 单精度浮点值
DOUBLE 双精度浮点值
DECIMAL (或DEC) 精度可变的浮点值
…
-decimal(5,2) 表示数值总共5位，小数占2位.
-tinyint 1字节(8位) 0 ~ 255, -128 ~ 127.
-int 4字节
-float
MySQL中没有专门存储货币的数据类型，一般使用decimal(8,2)

有符号和无符号
所有数值类型（除BIT和BOOLEAN）有无符号都可以。

4.3 日期和时间类型

MySQL使用专门的数据类型来存储日期和时间值

DATE 表示1000-01-01~9999-12-31的日期，格式为YYYY-MM-DD；
TIME 格式为HH:MM:SS
DATETIME DATE 和 TIME 的组合
YEAR 用2位数字表示，范围是70（1970年）~69（2069年）；
用4位数字表示，范围是1901年~2155年。

4.4 二进制数据类型

二进制数据类型可存储任何数据（甚至包括二进制信息），如图像、多媒体、字处理文档等。

5.MySQL的数据类型约束，运算符和主键

5.1 表的字段约束

unsigned 无符号（数值类型使用，表正数）
字段类型后面加括号限制宽度
–char(5),varchar(7)在字符类型后面加限制表示字符串的长度；
–int(4)没有意义，默认无符号的int为int(11)，有符号的为int(10)
–int(4) unsigned zerofill，只有当给int类型设置前导0（zerofill）时，设置int的宽度才有意义
not null 不能为空，在操作数据库时若输入字段的数据为NULL，就会报错
default 设置默认值
primary key 主键不能为空且唯一，一般和自动递增配合使用。
auto_increment 定义列为自增属性，一般用于主键，数值会自动加1。
unique 唯一索引（数据不能重复：用户名）可以增加查询速度，但会降低插入和更新速度

5.2 MySQL的运算符

算术运算符：+ — * / %
比较运算符：= > < >= <= !=
数据库特有的比较：in，not in， is null，is not null， like，between，and
逻辑运算符：and ， or ， not
–select * from users where age >=22 and age <=30;
–select * from users where age in(22,34);
–select * from users where age=22 or age=33;
like 支持特殊符号 % 和 _ （用于模糊搜索）

% 表示任意数量的任意字符；
_ 表示任意一位字符
select * from users where name like ‘_san’;

5.3 MySQL的主键

关系模型：主键、外键、索引

表中每一行都应该有可以唯一标识自己的一列，用于记录两条信息不能重复，任意两行都不具有相同的主键值。
应该总是定义主键，虽并不总是需要主键，但大多数据库设计人员都应保证他们创建的每个表具有一个主键，以便于以后数据的操作和管理。

要求

记录一旦插入到表中，主键最好不要修改
不允许null
不在主键列中使用可能会更改的值
–如，若使用一个名字作为主键以标示某个供应商，当该供应商合并更改名字时，必须更改这个主键
自增整数类型：数据库会在插入数据时，自动为每一条记录分配一个自增整数，如此可不用担心主键重复，不用自己预先生成主键。
可以使用多个列作为联合主键，但联合主键并不常用，使用多列作为主键时，所有列值的组合必须是唯一的

5.4 外键

一对多

用主键唯一标识记录时，可以在students表中确定任意一个学生的记录：
id name other columns
1 zhang …
2 su …

还可在classes表中确定任意一个班级记录：
id name other columns
1 class 1 …
2 class 2 …

由于一个班级可以有多个学生，在关系模型中，这两个表的关系可以称为“一对多”，即一个classes的记录可以对应多个students表的记录。
为了表达这种一对多的关系，需要在students表中加入一列class_id，让它的值与classes表的某条记录相对应：

id  class_id    name    other columns
1      1        zhang   ..
2      1         su     ..

在students表中，通过 class_id 的字段，可以把数据与另一张表关联起来，这种列称为外键。
外键并不是通过列名实现的，而是通过定义外键约束实现的：

ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);

其中，外键约束的名称 fk_class_id 可以任意，FOREIGN KEY (class_id)指定了class_id作为外键，REFERENCES classes (id)指定了这个外键将关联到classes表的id列（即classes表的主键）。
通过定义外键约束，关系数据库可以保证无法插入无效的数据。即如果 classes表不存在 id=99 的记录，students表就无法插入class_id=99的记录。
由于外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅靠应用程序自身来保证逻辑的正确性。
这种情况下，class_id仅仅是一个普通的列，只是它起到了外键的作用而已。
要删除一个外键约束，也是通过 ALTER TABLE 实现的：

ALTER TABLE students
DROP FOREIGN KEY fk_class_id;

注意：删除外键约束并没有删除外键这一列。删除列是通过 DROP COLUMN …实现的。

多对多

通过一个表的外键关联到另一个表，我们可以定义出一对多关系。有些时候，还需要定义“多对多”关系。
例如，一个老师可以对应多个班级，一个班级也可以对应多个老师，因此，班级表和老师表存在多对多关系。
多对多关系实际上是通过两个一对多关系实现的，即通过一个中间表，关联两个一对多关系，就形成了多对多关系：
teachers表：
id name
1 张老师
2 王老师
3 李老师

classes表：
id name
1 一班
2 二班

中间表teacher_class关联两个一对多关系：
id teacher_id class_id
1 1 1
2 1 2
3 2 1
4 2 2
5 3 1
通过中间表teacher_class可知teachers到classes的关系：

id=1的张老师对应id=1,2的一班和二班；
id=2的王老师对应id=1,2的一班和二班；
id=3的李老师对应id=1的一班；
id=4的赵老师对应id=2的二班。
同理可知classes到teachers的关系：

id=1的一班对应id=1,2,3的张老师、王老师和李老师；
id=2的二班对应id=1,2,4的张老师、王老师和赵老师；
因此，通过中间表，就定义了一个“多对多”关系。

一对一

一对一关系是指，一个表的记录对应到另一个表的唯一一个记录。
如果业务允许，完全可以把两个表合为一个表。但是，有些时候，如果某个学生没有手机号，那么，contacts表就不存在对应的记录。
实际上，一对一关系准确地说，是contacts表一对一对应students表。
还有一些应用会把一个大表拆成两个一对一的表，目的是把经常读取和不经常读取的字段分开，以获得更高的性能。
例如，把一个大的用户表分拆为用户基本信息表user_info和用户详细信息表user_profiles，大部分时候，只需要查询user_info表，
并不需要查询user_profiles表，这样就提高了查询速度。

小结
关系数据库通过外键可以实现一对多、多对多和一对一的关系。外键既可以通过数据库来约束，也可以不设置约束，仅依靠应用程序的逻辑来保证。

5.5 索引

索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。
通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。
例如，对于students表：
id class_id name gender score
1 1 小明 M 90
2 1 小红 F 95
如果要经常根据score列进行查询，就可以对score列创建索引：

ALTER TABLE students
ADD INDEX idx_score (score);

使用ADD INDEX idx_score (score)就创建了一个名称为idx_score，使用列score的索引。
索引名称是任意的，索引如果有多列，可以在括号里依次写上，例如：

ALTER TABLE students
ADD INDEX idx_name_score (name, score);

索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。反过来，如果记录的列存在大量相同的值，
例如gender列，大约一半的记录值是M，另一半是F，因此，对该列创建索引就没有意义。

可以对一张表创建多个索引。索引的优点是提高了查询效率，缺点是在插入、更新和删除记录时，需要同时修改索引，
因此，索引越多，插入、更新和删除记录的速度就越慢。

对于主键，关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的，因为主键会保证绝对唯一。

唯一索引

在设计关系数据表的时候，看上去唯一的列，例如身份证号、邮箱地址等，因为具有业务含义，因此不宜作为主键。
但是，这些列根据业务要求，又具有唯一性约束：即不能出现两条记录存储了同一个身份证号。这个时候，就可以给该列添加一个唯一索引。
例如，假设students表的name不能重复：

ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);

通过UNIQUE关键字就添加了一个唯一索引。

也可以只对某一列添加一个唯一约束而不创建唯一索引：

ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);

这种情况下，name列没有索引，但仍然具有唯一性保证。

无论是否创建索引，对于用户和应用程序来说，使用关系数据库不会有任何区别。
这里的意思是说，当我们在数据库中查询时，如果有相应的索引可用，数据库系统就会自动使用索引来提高查询效率，
如果没有索引，查询也能正常执行，只是速度会变慢。因此，索引可以在使用数据库的过程中逐步优化。

小结
通过对数据库表创建索引，可以提高查询速度。
通过创建唯一索引，可以保证某一列的值具有唯一性。
数据库索引对于用户和应用程序来说都是透明的。

6.MySQL数据库与数据表操作

库就像文件夹，库中可以有很多个表；
表就像Excel表格文件，每个表可以存储很多数据

MySQL中可以有不同的库，库中可以有不同的表。
表中可以定义不同的列（字段），
表中可以根据结构去存储很多数据。

6.1 数据库操作

6.1.1 创建库：

create database 库名 default charset=utf8;
create database if not exists 库名 default charset=utf8;
-1）库名若不存在则创建，存在则不创建；
-2）创建库，并设置字符集为utf8；
-3）无特殊情况都要求为utf8或utf8mb4的字符编码

6.1.2 查看当前MySQL所有的库

show databases;

6.1.3 打开/进入/选择库

use MySQL;

6.1.4 删除数据库

drop database 库名;

6.2 数据表操作

6.2.1 创建表：

1）表存在会报错
create table 表名(
字段名类型字段约束,
字段名类型字段约束,
字段名类型字段约束,
)engine=innodb default charset=utf8;

create table user(
    id int unsigned not null primary key auto_increment,
    name varchar(10),
    age tinyint not null default 20,
    sex char(1) not null default 'F',
    password char(32) not null
)engine=innodb default charset=utf8;

-数据库中存储密码通常为32位， >md5(‘33456’); >加密后输出为32位

2）表不存在，则创建；存在则不执行
create table if not exists 表名(字段1 类型，字段2 类型);

6.2.2 查看当前库中的所有数据表

show tables;

6.2.3 查看表中数据

1）查看user表中的所有数据的所有字段：
select * from user;
–* 代表表中所有字段
–user 代表user这张表
2）查看user表中的所有数据的 host和user 字段列：
select host,user from user;

6.2.4 删除表

drop table 表名;

6.2.5 查看表结构

desc 表名;

6.2.6 查看创建表的语句：

show create table users;

6.2.7 修改表结构 *

alter table 表名 action [更改的选项]

添加字段
alter table 表名 add 添加的字段信息;
alter table 表名 add 新字段信息 after 旧字段名;
alter table 表名 add 新字段信息 first;
删除字段
alter table 表名 drop 字段名;
修改字段
alter table 表名 change|modify 修改的字段信息
-change ：可以修改字段名
-modify ：不能修改字段名

--# 修改字段的类型
alter table users modify age tinyint not null default 18;
--# 修改字段和类型
alter table users change age ages int;

一般情况，无特殊要求，不要轻易修改表结构

修改表名
alter table 表名 rename as 新表名
更改表中自增的值
常规情况，默认从1开始自增
alter table users auto_increment = 20;
表示从20开始自增
修改表引擎
通过查看建表语句获取当前的表引擎
show create table users\G; \G 竖立显示
直接查看表引擎
show table status from 库名 where name=‘表名’\G;
修改表引擎
alter table users engine = ‘myisam’;

7.MySQL数据库表引擎与字符集

7.1 服务器处理客户端请求

客户端进程向服务器进程发出一段文本（MySQL语句），服务器进程处理后再向客户端进程发送一段文本（处理结果）。
客户端可以向服务器发送增删查改各类请求。

7.2 存储引擎

MySQL服务器把数据的存储和提取操作都封装到了一个叫存储引擎的模块里。
不同的存储引擎管理的表具体的存储结构可能不同，采取的存取算法也可能不同。
存储引擎以前叫做表处理器，功能就是接收上层传下来的指令，然后对表中的数据进行提取或写入操作。

连接管理、查询缓冲、语法解析、查询优化这些不涉及真实数据存储的功能划分为 MySQL server的功能。
不同的存储引擎向上边的 MySQL server层提供统一的调用接口（即存储引擎API），包含了几十个底层函数，如“读取索引第一条内容”、“插入记录”等。
在MySQL server 完成了查询优化后，只需按照生成的执行计划调用底层存储引擎提供的API，获取到数据后返回给客户端即可。

InnoDB 具备外键支持功能的事务存储引擎
MyISAM 主要的非事务处理存储引擎
MEMORY 置于内存的表

7.3 MyISAM和InnoDB表引擎的区别

7.3.1. 事务支持 *

MyISAM：不支持事务，InnoDB：支持。

-事务：访问并更新数据库中数据的执行单元。事务操作中，要么都执行，要么都不执行。

7.3.2. 存储结构 *

MyISAM：每个MyISAM在磁盘上存储成三个文件。

.frm 文件存储表结构
.MYD 文件存储数据
.MYI 文件存储索引

InnoDB：主要分为两种文件进行存储。

.frm 存储表结构
.ibd 存储数据和索引（也可能是多个.ibd文件，或是独立的表空间文件

7.3.3. 表锁差异 *

MyISAM：只支持表级锁。
用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，
若加锁以后的表满足insert并发的情况下，可在表的尾部插入新的数据。

InnoDB：支持事务和行级锁，是InnoDB的最大特色。
行锁大幅度提高了多用户并发操作的性能。但是InnoDB的行锁，只是在WHERE的主键是有效的非主键的WHERE都会锁全表。

7.3.4. 表主键 *

MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。

InnoDB：若没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键（用户不可见）。
数据是主索引的一部分，附加索引保存的是主索引的值。
InnoDB的主键范围更大，最大是MyISAM的2倍。

7.3.5. 表的具体行数

MyISAM：保存有表的总行数
若 select count() from table; 会直接取出该值。

InnoDB：没有保存表的总行数（只能遍历）。
若 select count() from table; 会遍历整个表。消耗相当大。
但在加了where条件后，MyISAM和InnoDB处理的方式都一样。

7.3.6. CURD操作

create update read delete ？
MyISAM：若执行大量的 select，MyISAM是更好的选择。

InnoDB：若执行大量的 insert 或 update，出于性能方面的考虑，应使用InnoDB。
delete 从性能上InnoDB更优，但 delete from table 时，InnoDB不会重新建立表，而是一行一行的删除，
在InnoDB上若要清空保存有大量数据的表，最好使用 truncate table 命令。

7.3.7. 外键

MyISAM：不支持。
InnoDB：支持。

7.3.8. 查询效率

MyISAM：相对简单，效率上优于InnoDB，小型应用可以考虑使用MyISAM。

InnoDB：其有很多良好的特点，如事务支持，存储过程，视图，行级锁定等等，
在并发很多的情况下，推荐考虑使用InnoDB更好。

7.3.9. MyISAM和InnovateDB的应用场景

MyISAM：管理非事务表，提供高速存储和检索，以及全文搜索能力。若应用中需要执行大量的 select 查询，使用MyISAM。

InnoDB：用于事务处理应用程序，具有众多特性，包括ACID事务支持，若应用中需要执行大量的 insert 或 update，应使用InnoDB。

7.4 了解字符集和乱码

通过建立字符与二进制数据的映射关系，来存储字符串。
界定字符范围。
编码：将一个字符映射成一个二进制数据的过程。
解码：将一个二进制数据映射成一个字符的过程。

抽象出一个字符集的概念来描述某个字符范围的编码规则。

ASCII 字符集
共收录128个字符。包括空格、标点符号、数字、大小写字母和一些不可见字符。可用1个字节进行编码。如：
L ： 01001100（十六进制：0x4C，十进制：76）
M ： 01001101（十六进制：0x4D，十进制：77）
ISO 8859-1 字符集
共收录256个字符。在ASCII字符集的基础上扩充了128个西欧常用字符（包括德法两国的字母）。可以使用1个字节进行编码。
该字符集有一个别名 latinl。
GB2312 字符集
GBK 字符集
utf8 字符集
收录地球上能想到的所有字符。且还在不断扩充。
字符集不能混用，否则会乱码。