python数据库使用一

关系

表

在关系数据库中，关系就是二维表，由行和列组成

MySQL是行存数据库，数据是一行行存的，列必须固定多少列。
行Row，也称为记录Record，元组;
列Column，也称为字段Field、属性;
字段的取值范围叫做域Domain。例如gender字段的聚会就是1或者2两个值。

row、行、记录、元组
列、字段、Field、Column

维数: 关系的维数指关系中的属性的个数;
基数: 元组的个数;

• 注意在关系中，属性的顺序并不重要。理论上，元组顺序也不重要，但是由于元组顺序与存储相关，会影响查询的效率;

候选键

关系中，能唯一标识一条元组的属性或属性集合，称为候选键。
候选键，表中一列或者多列组成唯一的key，通过这一个或者多个列能唯一的标识一条记录。
表中可能有多个候选键。

PRIMARY KEY主键

从候选键中选择出主键。主键的列不能包含空值（null）。主键往往设置为整型、长整型，可以为自增（AUTO_INCREMENT）字段。表中可以没有主键，但是一般表设计中往往都会有主键，以避免记录重复。InnoDB 的表要求使用主键。

Foreign KEY 外键

严格来说，当一个关系中的某个属性或属性集合与另一个关系（也可以是自身）的候选键匹配时，就称作这个属性或属性集合是外键。

约束（Constraint）

为了保证数据的完整正确，数据模型还必须支持完整性约束。

“必须有值”约束 ：
某些列的值必须有值，不允许为空（NULL）。

域约束（Domain Constraint）：
限定了表中字段的取值范围。

实体完整性（Entity Integrity）：
PRIMARY KEY 约束定义了主键，就定义了实体完整性约束。主键不重复且唯一，不能为空。

读多写少
建索引，字典前面建立声母索引，偏旁索引;
空间换时间，对查询大有益处，索引对增删有影响;

• 参考ref student.id

  外键约束

  • 插入规则: 自动具有。主表增加记录，没有任何关系，从表增加记录要看主表脸色，外键的值应该存在于主表的主键;
  • 删除规则:
    • 主表删除记录:
      • cascade 级联，主表及从表参考从表参考该值所在行一起删;
      • set null 设置为null，主表记录删除，从表记录置null 。几乎不用;
      • restrict、nocation 主表删除记录，从表不答应;
    • 从表删除记录: 随便删

  • 更新规则:

    • 主表更新记录:
      • cascade级联,你改我也改
      • set null 设置为null 主表记录改，从表设置为null,几乎不用;
      • restrict 、 no action 主表修改，从表不答应
    • 从表更新记录:
      • 随便改

  • 真正重要的数据，一般都是假删除，不会真正的删除，以避免级联删除后找不回数据;

引用完整性

引用完整性（Referential Integrity）是指数据库中的外键关系确保了数据的一致性和完整性。外键定义中，通常是引用另一张表的主键，但也可以引用其他唯一键。然而，实际上，我们往往只关注引用主键的情况。

具体来说，外键是表B中的一个或多个列，其值引用了表A中的主键或唯一键。表A被称为主表，表B被称为从表。外键的存在确保了从表中的引用值在主表中有对应的值，从而保持了数据的一致性和完整性。

实体-联系（E-R）建模

在数据库设计中，收集用户需求并设计符合企业要求的数据模型是至关重要的。为了构建这样的模型，常常采用实体-联系（E-R）建模方法。此外，还出现了一种建模语言称为统一建模语言（UML）。

实体（Entity）

实体指的是现实世界中具有相同属性的一组对象，这些对象可以是物理存在的实体，也可以是抽象的概念。在数据库设计中，实体通常表示为数据表中的一行或一组行，每个实体都有自己的属性。

联系（Relationship）

联系指的是实体之间的关联集合。它描述了不同实体之间的联系或关系，例如，学生和课程之间的关联、部门和员工之间的关系等。联系可以是一对一、一对多或多对多的关系，反映了现实世界中实体之间的相互作用和依赖关系。

一对一关系用的较少，往往表示表A的一条记录唯一关联表B的一条记录，反之亦然。
它往往是为了将一张表多列分割并产生成了多张表，合起来是完整的信息，或为了方便查询，或为了数据安全隔离一部分字段的数据等等。

视图（View）

视图，也称为虚拟表，看起来像表格一样。它是由查询语句生成的，不存储任何数据。虽然视图类似于表格，但其实际上是基于表格或其他视图的查询结果。

视图的作用

• 简化操作： 将复杂的查询SQL语句定义为视图，可以简化查询过程。通过将常用的查询逻辑封装在视图中，用户可以通过简单的查询视图来获取所需的数据，而无需了解复杂的查询语句的细节。

• 数据安全： 视图可以限制用户访问数据的范围，只显示真实表的部分列或计算后的结果，从而隐藏真实表的数据。这有助于确保敏感信息的保密性，并控制用户对数据的访问权限。

数据类型

• MySQL数据类型

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;

分页查询

• where 子句

注意：如果很多表达式需要使用AND、OR计算逻辑表达式的值的时候，由于有结合律的问题，建议使用小括号来避免产生错误

--- select 1 as id
SELECT emp.emp_no as id , concat(first_name, ' ', last_name) as name from employees as emp
whiere id > 10002
-- concat 用于将first_name 和 last_name 连接起来做字符串的拼接
-- as 用于给字段起别名
-- where 用于过滤数据
-- select 用于查询数据
-- from 用于指定表
--- 分页
limit 5
offset 5 
-- limit 用于限制返回的数据条数
-- offset 用于指定从第几条数据开始返回

--- select 1 as id
SELECT emp.emp_no as id , concat(first_name, ' ', last_name) as name from employees as emp
where 
--- emp.emp_no >= 10002 and emp.emp_no <= 10005
--- 以上是限制emp_no的范围
--- emp_no BETWEEN 10002 AND 10005
emp_no in (10002, 10003, 10004, 10005)
and last_name like 'P%'

EXPLAIN select * from employees 
--- 全表扫描
where
emp_no in (10002, 10003, 10004, 10005) and last_name like 'P%'

--- 如果 emp_no in (10002, 10003, 10004, 10005) and last_name like BINARY 'P%',这里BINARY是区分大小写的

EXPLAIN select * from employees where last_name like 'P%'
--- 这种语句永远不要写, 会导致全表扫描并每个进行like匹配

select * from salaries where emp_no = 10003 or emp_no = 10002 ORDER BY emp_no DESC, salary DESC LIMIT 10;
---  以上是SQL语句中 ORDER BY DESC 是为了用于emp_no的降序排序，如相同的emp_no则按照salary的降序排序

select DISTINCT emp_no ,salary from salaries 
--- 以上DISTINCT是为了去重，如果emp_no与salary都相同，则只显示一条记录

聚合函数

SELECT count(*) as c, sum(emp_no) as s ,max(emp_no) ,min(emp_no), avg(emp_no) from employees;
where emp_no > 10006;

--- 以上是统计employees表中emp_no大于10006的记录数，总和，最大值，最小值，平均值

SELECT count(*) as c, sum(emp_no) as s ,max(emp_no) ,min(emp_no), avg(emp_no) from employees;
where emp_no > 10006;

--- 以上是统计employees表中emp_no大于10006的记录数，总和，最大值，最小值，平均值

EXPLAIN SELECT count(*) from employees where last_name like 'P%'
--- 以上是查询employees表中last_name以P开头的记录数，其效率是最底的，因为没有使用索引

分组

--- 分组

SELECT count(*) FROM salaries GROUP BY emp_no;

--- 以上统计是根据emp_no分组的，所以结果是每个emp_no对应的记录数,假如emp_no有5个不同的值，那么结果就是5行

SELECT emp_no, sum(salary) ,avg(salary) FROM salaries GROUP BY emp_no;

--- 以上统计是根据emp_no分组的，所以结果是每个emp_no对应的记录数,假如emp_no有5个不同的值，

SELECT emp_no, sum(salary) ,avg(salary) FROM salaries GROUP BY emp_no HAVING avg(salary) > 50000;

--- 以上统计是根据emp_no分组的，所以结果是每个emp_no对应的记录数,假如emp_no有5个不同的值，并且对应分组中的平均工资大于50000的

SELECT emp_no, sum(salary) ,avg(salary) FROM salaries GROUP BY emp_no HAVING avg(salary) > 50000 ORDER BY avg(salary) DESC;

--- 以上统计是根据emp_no分组的，所以结果是每个emp_no对应的记录数,假如emp_no有5个不同的值，并且对应分组中的平均工资大于50000的,并且按照平均工资降序排列

SELECT emp_no, sum(salary) ,avg(salary) FROM salaries GROUP BY emp_no HAVING avg(salary) > 50000 ORDER BY avg(salary) DESC LIMIT 1;

--- 以上统计是根据emp_no分组的，所以结果是每个emp_no对应的记录数,假如emp_no有5个不同的值，并且对应分组中的平均工资大于50000的,并且按照平均工资降序排列,只取第一行，即最大的平均工资

SELECT emp_no, sum(salary) ,avg(salary) FROM salaries GROUP BY emp_no HAVING avg(salary) > 50000 ORDER BY avg(salary) DESC LIMIT 1 OFFSET 1;

--- 以上统计是根据emp_no分组的，所以结果是每个emp_no对应的记录数,假如emp_no有5个不同的值，并且对应分组中的平均工资大于50000的,并且按照平均工资降序排列,只取第一行，即最大的平均工资,并且跳过第一行，取第二行

--- 通过以上可以理解SQL的执行过程，先过滤，再分组，再聚合，最后排序，最后取值

SELECT emp_no, min(salary) as ss FROM salaries GROUP BY emp_no ORDER BY ss DESC LIMIT 1;

--- 以上统计是根据emp_no分组的，所以结果是每个emp_no对应的记录数,假如emp_no有5个不同的值，并且对应分组中的最小工资，然后按照最小工资降序排列,只取第一行，即最大的最小工资

--- 单表较为复杂的语句

SELECT
    emp_no,
    avg(salary) AS avg_salary,
FROM
    salaries
WHERE
    salary > 70000
GROUP BY
    emp_no
HAVING
    avg(salary) > 5000
ORDER BY
    avg_salary DESC
LIMIT 1

--- 以上统计是根据emp_no分组的，所以结果是每个emp_no对应的记录数,假如emp_no有5个不同的值，并且对应分组中的平均工资大于50000的,并且按照平均工资降序排列,只取第一行，即最大的平均工资

子查询

--- 子查询

SELECT * FROM employess 
WHERE emp_no in (SELECT DISTINCT emp_no FROM salaries)

--- 以上是子查询,将子查询的结果作为条件

SELECT emp.emp_no, emp.last_name from (SELECT * FROM employess WHERE emp_no > 10015) emp GROUP BY

--- 以上是生成一张临时表，然后再查询

join(关联查询,m*n的问题)

交叉连接cross join
笛卡尔乘积，全部交叉
在MySQL中，CROSS JOIN 从语法上说与INNER JOIN等同
Join会构建一张临时表

--- join 关联查询

SELECT e.* , s.salary from employee e JOIN salary s on e.emp_no = s.emp_no;

--- 以上是实现了两个表的关联查询，查询出了员工的信息和工资信息

SELECT * FROM employee e JOIN salary s
ON e.emp_no = s.emp_no;
WHERE e.emp_no = 10002;

--- 以上是实现了两个表的关联查询，查询出了员工的信息和工资信息，同时还加上了条件查询，查询出了员工编号为10002的员工信息和工资信息

SELECT * FROM employee e JOIN salary s
WHERE e.emp_no = s.emp_no AND e.emp_no = 10002;

--- 等值连接查询，查询出了员工编号为10002的员工信息和工资信息

SELECT * FROM employee e LEFT JOIN salaries s 
ON e.emp_no = s.emp_no;

--- 左连接查询，查询出了员工的信息和工资信息，左外链接查询，查询出了员工的信息和工资信息，如果工资信息为空，则显示NULL,左外连接以左表为主

SELECT * FROM employee e RIGHT JOIN salaries s 
ON e.emp_no = s.emp_no;

--- 右连接查询，查询出了员工的信息和工资信息，右外链接查询，查询出了员工的信息和工资信息，如果员工信息为空，则显示NULL,右外连接以右表为主

SELECT * FROM employee e RIGHT JOIN salaries s 
ON e.emp_no = s.emp_no;
WHERE s.emp_no IS NOT NULL;

--- 右连接查询，查询出了员工的信息和工资信息，右外链接查询，查询出了员工的信息和工资信息，如果员工信息为空，则显示NULL,右外连接以右表为主，同时还加上了条件查询，查询出了工资信息不为空的员工信息和工资信息

• 自链接

往往用在层级上面

--- 假设有有manager 表，里面有经理的信息，经理的信息是员工的信息的一部分，经理的信息是员工的信息的一部分
--- empno name mgr 
--- 1 tom null
--- 2 jerry 1
--- 3 jack 2

SELECT * FROM manager WHERE mgr IS NOT NULL

--- 查询出了经理信息不为空的员工信息

SELECT emp.* , mgr.name as mgr_name FROM manager as emp JOIN user as mgr
ON emp.mgr_id = mgr.mgr_id

--- 自连接查询，查询出了员工的信息和经理的信息，自连接查询，查询出了员工的信息和经理的信息，经理的信息是员工的信息，只是经理的信息是员工的信息的一部分

事务Transaction

InnoDB引擎，支持事务。
事务：由若干条语句组成的，指的是要做的一系列操作。
关系型数据库中支持事务，必须支持其四个属性（ACID）：

原子性，要求事务中的所有操作，不可分割，不能做了一部分操作，还剩一部分操作；
一致性，多个事务并行执行的结果，应该和事务排队执行的结果一致。如果事务的并行执行和多线
程读写共享资源一样不可预期，就不能保证一致性。
隔离性，就是指多个事务访问共同的数据了，应该互不干扰。隔离性，指的是究竟在一个事务处理
期间，其他事务能不能访问的问题
持久性，比较好理解，就是事务提交后，数据不能丢失。

MySQL隔离级别

隔离性不好，事务的操作就会互相影响，带来不同严重程度的后果。

1. 更新丢失（Lost Update）

事务A和B更新同一个数据。它们都读取了初始值为100。A要减10，B要加100。A减去10后更新为90，B加100更新为200，A的更新丢失了，就像从来没有减过10一样。

2. 脏读（Dirty Read）

事务B读取了事务A未提交的数据。事务A是否最终提交并不关心，只要读取到了这个被修改的还未提交的数据就是脏读。

3. 不可重复读（Unrepeatable Read）

事务A在执行过程中使用相同的查询语句，得到了不同的结果。无法保证同一条查询语句重复读取到相同的结果，即不能保证重复读。

例如，事务A查询了一次后，事务B修改了数据并提交了事务，事务A又查询了一次，发现数据不一致了。

4. 幻读（Phantom Read）

事务A中同一个查询要进行多次，事务B插入数据，导致A返回不同的结果集，如同幻觉，就是幻读。

注意，脏读指的是读取到未提交的数据，而不是最终结果。

有了上述问题，数据库就必须要解决，提出了隔离级别。
隔离级别由低到高，如下表

隔离级别越高，串行化越高，数据库并行执行效率低；隔离级别越低，并行度越高，性能越高。
隔离级别越高，当前事务处理的中间结果对其它事务不可见程度越高。
• SERIALIZABLE，串行了，解决所有问题
• REPEATABLE READ，事务A中同一条查询语句返回同样的结果，就是可以重复读数据了。例如语句为（select * from user）。解决的办法有：
1、对select的数据加锁，不允许其它事务删除、修改的操作
2、第一次select的时候，对最后一次确切提交的事务的结果做快照
解决了不可以重复读，但是仍有可能出现幻读。例如，事务A、事务B开启，事务A中增加了一条数据并提交，事务B反复查询看不到新的数据，但是可以使用update语句更新成功，再查询就看到了。这也是幻读，如同出现了幻觉。
• READ COMMHTED，在事务中，每次select可以读取到别的事务刚提交成功的新的数据。因为读到的是提交后的数据，解决了脏读，但是不能解决 不可重复读和幻读的问题。因为其他事务前后修改了数据或增删了数据。
• READ UNCOMMITTED，能读取到别的事务还没有提交的数据，完全没有隔离性可言，出现了脏读，当前其他问题都可能出现。

事务语法

开始一个事务使用 START TRANSACTION 或 BEGIN，START TRANSACTION 是标准 SQL 语法。

提交事务使用 COMMIT，提交后的变更成为永久变更。

使用 ROLLBACK 可以在提交事务之前回滚变更，使得事务中的操作就好像没有发生过一样（原子性）。

MySQL 默认采用自动提交模式，即每一条查询语句都会作为一个事务提交。使用 SET AUTOCOMMIT 语句可以禁用或启用默认的自动提交模式，针对当前连接。例如，SET AUTOCOMMIT = 0 禁用自动提交事务。如果开启自动提交，当执行一条修改表的语句后，会立即将更新存储到磁盘。

查询隔离级别可以通过以下语句进行：

• SELECT @@global.tx_isolation; 或 SHOW GLOBAL VARIABLES LIKE '%iso%'; 查询全局隔离级别。
• SELECT @@session.autocommit; 或 SHOW SESSION VARIABLES LIKE '%iso%'; 查询会话隔离级别。
• SELECT @@tx_isolation; 查询当前事务隔离级别。
• SHOW VARIABLES LIKE '%isolation%'; 查询隔离级别设置。

设置会话级或者全局隔离级别使用相应的 SQL 语句。
这样

FOR UPDATE和锁

在数据库中不可避免会发生并发操作，同时有人读取数据，有人写入数据，如果读写操作发生在同一张表的同一条记录上，就会产生冲突。

数据库提供了锁机制来处理并发操作：

• 读锁（共享锁）：允许多个用户同时读取同一个资源，互不干扰。
• 写锁（排他锁）：具有更高的优先级，阻塞其他用户对该资源的读写操作，包括读锁和写锁。

在SQL中，使用 SELECT ... FOR UPDATE 会对选中的行进行写锁定，这是一种排他锁。如果能明确记录行的主键，就会使用行级锁。例如，id=100 使用了主键，则会使用行级锁。如果无法确定记录行的主键，就会使用表级锁。例如，id <> 3，条件中没有使用主键，将会使用表级锁。另外，例如条件为 name='tom' 也会使用表级锁。

FOR UPDATE 是一种悲观锁，它认为会发生冲突，所以先锁定资源，独占使用。然而，悲观锁会影响并发性能，因此在使用 FOR UPDATE 时需要保证操作时间短，并尽量利用行级锁。

相对应的，还有乐观锁，它认为冲突很少发生，只有在写入时才会加锁。但是，需要在数据冲突时进行检测。

数据仓库和数据库的区别

本质上来说，数据仓库和数据库都是存放数据的地方。然而，它们在设计目的、数据结构、使用方式等方面有所不同：

数据库

• 关注数据的持久化、数据的关系，为业务系统提供支持。
• 提供事务支持，用于处理在线交易数据（OLTP，联机事务处理）。
• 用于存储在线业务数据，需要频繁进行增删改查操作。

数据仓库

• 设计用于存储、分析和发掘数据的表结构。
• 可以存储海量数据，并提供高效的数据分析能力。
• 主要用于存储历史数据，支持用于分析的SQL查询（OLAP，联机分析处理）。
• 一般不建议频繁进行数据的修改或删除操作。

总的来说，数据库用于支持业务系统的在线交易数据，而数据仓库则主要用于存储历史数据以供分析和决策使用。

存储过程和触发器

存储过程（Stored Procedure）是数据库系统中的一段完成特定功能的SQL语句。它类似于函数，可以接收参数并进行调用，支持流程控制语句，提供了一种封装和重用 SQL 逻辑的方式。

触发器（Trigger）是一种特殊的存储过程，由特定事件触发执行，例如在数据库表中插入、更新、删除数据时触发。触发器可以用来在数据库的特定操作发生时自动执行一些操作。

这两种技术在数据库中可以提供一定的性能优势，但是由于以下原因，它们在实际应用中已经不太常见：

• 移植性差：存储过程和触发器的语法和实现方式在不同的数据库系统中可能有较大差异，不利于跨数据库平台的移植。
• 占用服务器资源：存储过程和触发器的执行会占用数据库服务器的资源，可能影响数据库的性能。
• 维护困难：一旦存储过程或触发器出现问题，排错和维护都比较困难，尤其是对于复杂的存储过程和触发器。
• 不建议放置业务逻辑：将业务逻辑放置在数据库层面会导致应用程序和数据库之间的耦合度增加，不利于系统的维护和扩展。

综上所述，尽管存储过程和触发器具有一定的优点，但在现代应用开发中，更多地倾向于将业务逻辑放置在应用程序层面进行处理，而不是依赖数据库的存储过程和触发器。