【MySQL基础】mysql介绍，库、表的CRUD，常用数据类型、运算符、函数，数据的CRUD操作，集合操作

原创已于 2022-06-29 15:35:03 修改 · 377 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #python

于 2022-06-29 15:34:29 首次发布

python知识专栏收录该内容

8 篇文章

订阅专栏

本文介绍了关系型数据库和非关系型数据库的概念、特点及应用场景。关系型数据库如MySQL支持ACID事务，适用于数据完整性要求高的场景；非关系型数据库如MongoDB、Cassandra则适合大数据量、高性能和灵活数据模型的需求。接着，详细阐述了MySQL的基本操作，包括创建、查看、更新和删除数据库与数据表，以及各种数据类型和常用运算符。此外，还涵盖了CRUD操作和集合操作的示例。

数据库基本介绍

关系型数据库

创建在关系模型基础上的数据库，用来存储和管理结构化的数据。

关系模型

# 类似python中的类

class Student:
    def __init__(self, name, classes ...):
        self.name = name
        self.classes = classes
    
    def borrow(self, book):
        print(f"student {self.name} borrow {book.name}")

class Book:
    pass

在关系型数据库当中, 可以用三张数据表来表示

学生表

图书表

借阅表(记录行为)

关系型数据库的特点(也就是事务的特点)

ACID四大特点

Atomic(原子性)

指事务的操作是不可分割的, 要么完成, 要么不完成. 不存在其他的中间态
```
A -> B 转账, 如果中途中断, 那么整个银行系统就会崩溃.
```

Consistence(一致性)

事务A和事务B同时运行, 无论谁先结束, 数据库都会到达一致.

集合点在东方明珠, 同学A从静安出发, 同学B从黄埔出发.
一致性不关心A和B谁先到达, 只关心最终两者都在东方明珠汇合.

Isolation(隔离性)

解决多个事务同时对数据进行读写和修改的能力.
Duration(持久性)

当某个事务一旦提交, 无论数据库崩溃还是其他原因, 该事务的结果都能够被持久化地保存下来.
```
事务的所有操作都是有记录的, 即使数据库中途崩溃, 仍然可以通过记录恢复
```

适用场景

考虑到事务和日志

对数据完整性有要求.
存储的数据结构化完整.
单个数据库服务实例可以满足需求.

建立集群方案, 大多需要适用企业版, 企业版收费高昂.
读表操作远远大于写表操作.

非关系型数据库(`Nosql, not noly sql`)

创建在Nosql系统存储(键对值)基础上的数据库, 既可以存储结构化的数据, 也可以存储非结构化的数据。

非关系型数据库的特点

易扩展

NoSQL数据库种类繁多，但是一个共同的特点都是去掉关系数据库的关系型特性。数据之间无关系，这样就非常容易扩展。无形之间，在架构的层面上带来了可扩展的能力。

大数据量，高性能

NoSQL数据库都具有非常高的读写性能，尤其在大数据量下，同样表现优秀。这得益于它的无关系性，数据库的结构简单。一般MySQL使用Query Cache。NoSQL的Cache是记录级的，是一种细粒度的Cache，所以NoSQL在这个层面上来说性能就要高很多。

灵活的数据模型

NoSQL无须事先为要存储的数据建立字段，随时可以存储自定义的数据格式。而在关系数据库里，增删字段是一件非常麻烦的事情。如果是非常大数据量的表，增加字段简直就是——个噩梦。这点在大数据量的Web 2.0时代尤其明显。

高可用

NoSQL在不太影响性能的情况，就可以方便地实现高可用的架构。比如Cassandra、HBase模型，通过复制模型也能实现高可用。

常见的非关系型数据库

MongoDB：是一个面向文档的开源NoSQL数据库。MongoDB使用JSON之类的文档来存储任何数据。它是用c++写的。

Cassandra：是Facebook为收件箱搜索开发的。Cassandra是一个用于处理大量结构化数据的分布式数据存储系统。

Redis：是最著名的键值存储。Redis是用C语言编写的。它是根据BSD授权的。

HBase：是谷歌为BigTable数据库设计的分布式非关系型数据库。

Mysql数据库的基本使用

连接

localhost

填写主机的时候就是填写ip地址

localhost对应的地址约等于127.0.0.1

数据库(database)

Create 创建
```
create database tunan_class_2;
```
- 设置字符集
  - utf8和utf8mb4
    
    mysql中utf8字符是不全的, utf8mb4才是和我们python中的utf-8字符集一致.
  - 排序规则
    
    选择默认的utf8mb4_general_ci
Retrieve 查看
```
show databases;
```

Update 更新

ALTER database tunan_class_2 DEFAULT CHARACTER SET 'utf8mb4'

Delete 删除

drop database 库名

drop database 库名

数据表(table)

Create 创建

use tunan_class;

CREATE TABLE class_2(
id int PRIMARY KEY auto_increment,
student_name varchar(255)
)DEFAULT charset=utf8mb4;

Retrieve 查看
```
show tables;
```
Update 更新
```
ALTER TABLE class_2 RENAME class_3;
```
Delete 删除
```
drop table 表名
```
- 只清空数据, 不删除表
```
truncate 表名
```

常用的数据类型

int

数字范围 -2 * 32 ~ 2 * 32

bigint

数字范围 -263 ~ 2 ** 63-1

float

float(m,d), 其中m表示的是有效位, d表示小数位
有效位就是把当前的小数: 12345.12 转变成科学计数法: 1.234512 * 10**5
m最大值为7

double

double(m, d), 其中m表示的是有效位, d表示小数位
m的最大值为15

decimal [ˈdesɪml]

涉及金额的时候使用decimal.

decimal(m, d), 其中m表示的是有效位, d表示小数位
m的最大值为65

不会产生精度问题, 因为decimal没有精度损失的本质是因为它存的是字符串

char

表示固定长度的字符串, 长度为255个字节

中文字符占据3~4个字节

varchar

表示不定长的字符串, 长度为 0 ~ 65535 个字节

text

长文本类型, 最大长度占据64kb

datetime

如果当前时区发生更改, datetime类型不会发生更改, 与存入的日期保持一致

timestamp

如果当前时区发生更改, timestamp类型会跟着时区更改

常用的运算符

算术运算符

+
-
*
/
div (取商)

SELECT 5 div 3

mod (取余)

SELECT 5 mod 3

比较运算符

类似python中的 ==

SELECT 1 =1

!=
> 与>=
<与<=
between 与not between

SELECT 2 not between 1 and 3

is null 与 is not null

用来判断当前是否有记录

逻辑运算符

NOT
AND
OR
XOR 异或, 两个值一个True, 一个为False, 当前表达式才为True

常用的函数

算术运算

SUM 和
AVG 平均数
MAX和MIN 最大值和最小值

对字符串进行操作时, 类似python的排序, 是根据ascii码来排序的.

COUNT

计算当前记录数
SELECT count(*) from test

字符处理

CHAR_LENGTH

SELECT CHAR_LENGTH("test")

FORMAT 格式化

SELECT FORMAT(0.333333,2)

LEFT和RIGHT 取左或取右

SELECT right("abcdefg", 2)

TRIM 去除空白字符

SELECT trim("abcdefg        ")

CRUD操作

Create(增)

单条插入

INSERT INTO 表名(字段1, 字段2..) VALUES(值1, 值2)

INSERT INTO class_1(name) VALUES('name_1')

多条插入

INSERT INTO 表名(字段1, 字段2..) VALUES(值1, 值2), (值1, 值2)

Retrieve(查)

获取所有记录

SELECT 字段1, 字段2 FROM 表名

SELECT name FROM class_1
SELECT * FROM class_1

条件查询(WHERE, AND , OR)

SELECT 字段1, 字段2 FROM 表名 WHERE 表达式

SELECT * FROM class_1 WHERE name="name_1" AND id=8

模糊匹配(LIKE, %)

模糊匹配有性能问题, 表记录如果比较多, 查询速度很慢

SELECT 字段1, 字段2 FROM 表名 WHERE 表达式

SELECT * FROM class_1 WHERE name LIKE "%m%"

限制返回条数(LIMIT)

SELECT * FROM class_1 WHERE name LIKE "%m%" LIMIT 3

过滤重复值(DISTINCT)

对查询的结果进行过滤.

如果指定多个字段, 会对多个字段联合进行过滤

SELECT DISTINCT 字段1, 字段2 FROM 表名 [条件语句]

SELECT distinct name, create_time  FROM class_1 WHERE name like '%m%'

排序问题

对查询的结果进行排序

升序(ASC)

数据库默认是升序的

SELECT * FROM class_1 WHERE name LIKE "%m%"  ORDER BY id ASC

降序(DESC)

SELECT * FROM class_1 WHERE name LIKE "%m%"  ORDER BY id DESC

获取查询结果的条数(COUNT)

SELECT COUNT(*) FROM 表名 [条件语句]

Update(更新)

UPDATE 表名 SET 字段1=新值, 字段2=新值 WHERE 表达式

UPDATE class_1 SET name='name_0' WHERE id=8

Delete(删除)

DELETE FROM 表名 WHERE 表达式

DELETE FROM class_1 WHERE name='name_0'

集合操作

并集 UNINO

子语句SELECT 必须拥有相同数量的列(字段)，且列的数据类型也相同

SELECT name from class_1 WHERE name is not NULL
UNION
SELECT name from class_2 WHERE name is not NULL

交集 JION / INNER JION

SELECT s1.name FROM
(SELECT name from class_1 WHERE name is not NULL) as s1
JOIN
(SELECT name from class_2 WHERE name is not NULL) as s2
ON s1.name=s2.name

差集 LEFT JION / RIGHT JION

A对B的差集(LEFT JOIN)

SELECT * FROM // s1对s2的差集, select就可以使用s1.name
(SELECT name from class_1 WHERE name is not NULL) as s1
LEFT JOIN
(SELECT name from class_2 WHERE name is not NULL) as s2
ON s1.name=s2.name
WHERE s2.name is NULL   // 限定s1有, s2没有的记录

B对A的差集(RIGHT JOIN)

SELECT * FROM
(SELECT name from class_1 WHERE name is not NULL) as s1
RIGHT JOIN
(SELECT name from class_2 WHERE name is not NULL) as s2
ON s1.name=s2.name
WHERE s1.name is NULL

补集

A与B的补集 = A与B的并集 - A与B的交集

A与B的补集 = A对B的差集 + B对A的差集

SELECT s1.name FROM
(SELECT name from class_1 WHERE name is not NULL) as s1
LEFT JOIN
(SELECT name from class_2 WHERE name is not NULL) as s2
ON s1.name=s2.name
WHERE s2.name is NULL

UNION

SELECT s2.name FROM
(SELECT name from class_1 WHERE name is not NULL) as s1
RIGHT JOIN
(SELECT name from class_2 WHERE name is not NULL) as s2
ON s1.name=s2.name
WHERE s1.name is NULL