python数据库使用
关系
表
在关系数据库中，关系就是二维表，由行和列组成
MySQL是行存数据库，数据是一行行存的，列必须固定多少列。
行Row，也称为记录Record，元组;
列Column，也称为字段Field、属性;
字段的取值范围叫做域Domain。例如gender字段的聚会就是1或者2两个值。
| emp_no | birth_date | first_name | last_name | gender | hire_date |
|---|---|---|---|---|---|
| 10001 | 1953-09-02 | Georgi | Facello | 1 | 1986-06-26 |
| 10002 | 1964-06-02 | Bezalel | Simmel | 2 | 1985-11-21 |
| 10003 | 1959-12-03 | I Parto | Bamford | 1 | 1986-08-28 |
| 10004 | 1954-05-01 | Chirstian | Koblick | 1 | 1986-12-01 |
| 10005 | 1955-01-21 | Kyoichi | Maliniak | 1 | 1989-09-12 |
| 10006 | 1953-04-20 | Anneke | Preusig | 2 | 1989-06-02 |
| 10007 | 1957-05-23 | Tzvetan | Zielinski | 2 | 1989-02-10 |
| 10008 | 1958-02-19 | I Saniya | Kalloufi | 1 | 1994-09-15 |
| 10009 | 1952-04-19 | Sumant | Peac | 2 | 1985-02-18 |
| 10010 | 1963-06-01 | Duangkaew | Piveteau | 2 | 1989-08-24 |
row、行、记录、元组
列、字段、Field、Column
维数: 关系的维数指关系中的属性的个数;
基数: 元组的个数;
- 注意在关系中，属性的顺序并不重要。理论上，元组顺序也不重要，但是由于元组顺序与存储相关，会影响查询的效率;
候选键
关系中，能唯一标识一条元组的属性或属性集合，称为候选键。
候选键，表中一列或者多列组成唯一的key，通过这一个或者多个列能唯一的标识一条记录。
表中可能有多个候选键。
PRIMARY KEY主键
从候选键中选择出主键。主键的列不能包含空值（null）。主键往往设置为整型、长整型，可以为自增（AUTO_INCREMENT）字段。表中可以没有主键，但是一般表设计中往往都会有主键，以避免记录重复。InnoDB 的表要求使用主键。
Foreign KEY 外键
严格来说，当一个关系中的某个属性或属性集合与另一个关系（也可以是自身）的候选键匹配时，就称作这个属性或属性集合是外键。
约束（Constraint）
为了保证数据的完整正确，数据模型还必须支持完整性约束。
“必须有值”约束 ：
某些列的值必须有值，不允许为空（NULL）。
域约束（Domain Constraint）：
限定了表中字段的取值范围。
实体完整性（Entity Integrity）：PRIMARY KEY 约束定义了主键，就定义了实体完整性约束。主键不重复且唯一，不能为空。
读多写少
建索引，字典前面建立声母索引，偏旁索引;
空间换时间，对查询大有益处，索引对增删有影响;
| stu_id | stu_no | name | age | deleted |
|---|---|---|---|---|
| 1 | tom | 20 | 1 | 1 |
| 2 | 502 | jerry | 18 | 0 |
| 3 | 503 | john | 20 | 0 |
| 4 | s04 | ben | 15 | 0 |
| id | stu_id | chinese |
|---|---|---|
| 1 | 1 | 88 |
| 2 | 1 | 78 |
| 3 | 2 | 90 |
参考ref student.id
外键约束
- 插入规则: 自动具有。主表增加记录，没有任何关系，从表增加记录要看主表脸色，外键的值应该存在于主表的主键;
- 删除规则:
- 主表删除记录:
cascade级联，主表及从表参考从表参考该值所在行一起删;set null设置为null，主表记录删除，从表记录置null。几乎不用;restrict、nocation主表删除记录，从表不答应;
- 从表删除记录: 随便删
- 主表删除记录:
更新规则:
- 主表更新记录:
cascade级联,你改我也改set null设置为null主表记录改，从表设置为null,几乎不用;restrict 、 no action主表修改，从表不答应
- 从表更新记录:
- 随便改
- 主表更新记录:
真正重要的数据，一般都是假删除，不会真正的删除，以避免级联删除后找不回数据;
引用完整性
引用完整性（Referential Integrity）是指数据库中的外键关系确保了数据的一致性和完整性。外键定义中，通常是引用另一张表的主键，但也可以引用其他唯一键。然而，实际上，我们往往只关注引用主键的情况。
具体来说，外键是表B中的一个或多个列，其值引用了表A中的主键或唯一键。表A被称为主表，表B被称为从表。外键的存在确保了从表中的引用值在主表中有对应的值，从而保持了数据的一致性和完整性。
| 设定值 | 说明 |
|---|---|
| CASCADE | 级联，从父表删除或更新会自动删除或更新子表中匹配的行。 |
| SET NULL | 从父表删除或更新行，会设置子表中的外键列为NULL，但必须保证子表列没有指定NOT NULL，也就是说子表的字段可以为NULL才行。 |
| RESTRICT | 如果从父表删除主键，如果子表引用了，则拒绝对父表的删除或更新操作。 |
| NO ACTION | 标准SQL的关键字，在MySQL中与RESTRICT相同。拒绝对父表的删除或更新操作。 |
实体-联系（E-R）建模
在数据库设计中，收集用户需求并设计符合企业要求的数据模型是至关重要的。为了构建这样的模型，常常采用实体-联系（E-R）建模方法。此外，还出现了一种建模语言称为统一建模语言（UML）。
实体（Entity）
实体指的是现实世界中具有相同属性的一组对象，这些对象可以是物理存在的实体，也可以是抽象的概念。在数据库设计中，实体通常表示为数据表中的一行或一组行，每个实体都有自己的属性。
联系（Relationship）
联系指的是实体之间的关联集合。它描述了不同实体之间的联系或关系，例如，学生和课程之间的关联、部门和员工之间的关系等。联系可以是一对一、一对多或多对多的关系，反映了现实世界中实体之间的相互作用和依赖关系。
| 实体间联系类型 | 描述 | 解决方案 |
|---|---|---|
| 一对多联系 | 1:n | 一个员工属于一个部门，一个部门有多个员工。员工外键；部门主键 |
| 多对多联系 | m:n | 一个员工属于多个部门，一个部门有多个员工。建立第三表 |
| 一对一联系 | 1:1 | 假设有实体管理者，一个管理者管理一个部门，一个部门只有一个管理者。字段建在哪张表都行 |
一对一关系用的较少，往往表示表A的一条记录唯一关联表B的一条记录，反之亦然。
它往往是为了将一张表多列分割并产生成了多张表，合起来是完整的信息，或为了方便查询，或为了数据安全隔离一部分字段的数据等等。
视图（View）
视图，也称为虚拟表，看起来像表格一样。它是由查询语句生成的，不存储任何数据。虽然视图类似于表格，但其实际上是基于表格或其他视图的查询结果。
视图的作用
简化操作： 将复杂的查询SQL语句定义为视图，可以简化查询过程。通过将常用的查询逻辑封装在视图中，用户可以通过简单的查询视图来获取所需的数据，而无需了解复杂的查询语句的细节。
数据安全： 视图可以限制用户访问数据的范围，只显示真实表的部分列或计算后的结果，从而隐藏真实表的数据。这有助于确保敏感信息的保密性，并控制用户对数据的访问权限。
数据类型
- MySQL数据类型
| 类型 | 含义 |
|---|---|
| tinyint | 1字节，带符号的范围是-128到127，无符号的范围是0到255。用于表示bool或boolean类型，0表示假，非0表示真。 |
| smallint | 2字节，带符号的范围是-32768到32767，无符号的范围是0到65535。 |
| int | 整型，4字节，带符号的范围是-2147483648到2147483647，无符号的范围是0到4294967295。 |
| bigint | 长整型，8字节，带符号的范围是-9223372036854775808到9223372036854775807，无符号的范围是0到18446744073709551615。 |
| float | 单精度浮点数，精确到大约7位小数位。 |
| double | 双精度浮点数，精确到大约15位小数位。 |
| DATE | 日期，支持的范围为’1000-01-01’到’9999-12-31’。 |
| DATETIME | 日期时间，支持的范围是’1000-01-01 00:00:00’到’9999-12-31 23:59:59’。 |
| TIMESTAMP | 时间戳，范围是’1970-01-01 00:00:00’到’2037-12-31 23:59:59’。 |
| char(M) | 固定长度字符串，右边填充空格以达到长度要求。M为长度，范围为0~255。M指的是字符个数。 |
| varchar(M) | 变长字符串，最大长度为M个字符。M的范围是0到65,535。 |
| text | 大文本，最大长度为65535（2^16-1）个字符。 |
| BLOB | 大字节对象，最大长度为65535（2^16-1）字节。 |
LENGTH函数返回字节数。而char和varchar定义的M是字符数限制。char可以将字符串定义为固定长度，空间换时间，效率略高；varchar为变长，省了空间。
关系操作
在关系数据库中，关系就是二维表格。关系操作就是对这些表格的操作。
选择（Selection）
选择，又称为限制，是从关系中选择出满足给定条件的元组（行）。
投影（Projection）
投影在关系上是指从选择出的关系中选择若干属性列，以组成新的关系。
连接（Join）
连接是将不同的两个关系连接成一个关系。连接操作通常基于两个关系之间的共同属性，将这些属性相匹配的元组组合成新的元组，形成一个新的关系。
表新建
CREATE TABLE `reg` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`loginname` varchar(24) NOT NULL,
`name` varchar(48) DEFAULT NULL,
`password` varchar(128) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `loginname` (`loginname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
表插入
-- 向表中插入一行数据，自增字段、缺省值字段、可空字段可以不写
INSERT INTO table_name (col_name, ...) VALUES (value1, ...);
-- 将select查询的结果插入到表中
INSERT INTO table_name SELECT ...;
-- 如果主键冲突、唯一键冲突就执行update后的设置。这条语句的意思，就是主键不在新增记录，主键在就更新部分字段。
INSERT INTO table_name (col_name1, ...) VALUES (value1, ...) ON DUPLICATE KEY UPDATE col_name1=value1, ...;
-- 如果主键冲突、唯一键冲突就忽略错误，返回一个警告。
INSERT IGNORE INTO table_name (col_name, ...) VALUES (value1, ...);
INSERT INTO reg (loginname, name, password) VALUES ('tom', 'tom', 'tom');
INSERT INTO reg (id, loginname, name, password) VALUES (5, 'tom', 'tom', 'tom');
INSERT INTO reg (id, loginname, name, password) VALUES (1, 'tom', 'tom', 'tom') ON DUPLICATE KEY UPDATE name = 'jerry';
删除表语句
-- 删除符合条件的记录
DELETE FROM tbl_name [WHERE where_definition];
-- 删除一定要有条件
DELETE FROM reg WHERE id = 1;
-- 这条SQL语句是用来更新名为"reg"的表中ID等于1的记录的"deleted"列的值为1。这通常用于标记记录为已删除状态，而不是实际从数据库中删除记录。
UPDATE reg SET deleted=1 WHERE id=l;
