sql数据库基本概念笔记

三大范式

这边会举反例，然后根据概念来改正。

第一范式（1NF）

指的是表中的每一列都是原子的，不可再分的，比如存在表

改正：

第二范式（2NF）

指的是所有非主键字段完全依赖于整个主键，而不能只依赖于主键的一部分。这里班级列出现了数据重复。

改正：

第三范式（3NF）

消除传递依赖，即非主键字段之间不应该有依赖关系，除非这种依赖通过主键间接建立。(上面是错的，改正在下面)

五个约束

primary key 主键

定义了一个表的主键，用于唯一标识表中的每一行记录，逐渐列的值必须唯一且不能为Null。

Foreign Key 外键约束

用来约束表与表之间的关系，他是一个表主键的一个引用。如果一个表的某列是另一个表的外键，那么该列的值必须是另一个表的主键值之一，或者是Null

Unique 唯一约束

确保列值是唯一的，但允许有0个或者多个Null值，和主键不同，唯一约束可以有多个

Check 检查约束

定义一列或者多列上的规则，比如数值必须大于0

Default默认约束

如在插入新行时不指定该列的值，就使用默认值

ACID:

四个关键属性的缩写：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。

原子性：

一个事务被视为不可分割的工作单元，任何操作失败都会把整个事务回滚到事务开始前的状态。

移植性：

一致性保证事务执行的前后，数据库都处于一致的状态。也就是事务开始前，数据满足所有规则和约束，事务结束后依然如此。

隔离性：

隔离性确保多个并发事务之间不会相互干扰，每个事物看起来都像独立运行一样。

持久性：

事务一旦提交，其结果将永久性保存在数据库中，即使在系统崩溃或者故障后。

sql语句：

原来有表 多条插入

Begin
Insert into table (colum1,colum2,...) values (value1,value2,...)
原来没有表 多条插入（创建表   向该表添加多条数据）
create table if not exists table1(
Id int id primary key,
Name varchar(255) not null,
Age int,
Email varchar(255) youxiang
);
Insert into table1 (name,age,email) values (“张三”,19,”1231@qq.xom”),(...,...,...);
End $$

5个聚合函数

COUNT()：
计算给定列或所有行的数量。可以用于计算非NULL值的数量或所有行的数量（COUNT(*)）。
SUM()：
返回给定列所有值的总和。
AVG()：
计算给定列所有非NULL值的平均值。
MAX()：
返回给定列中的最大值。
MIN()：
返回给定列中的最小值。

子查询：

1）Select  列名 from 表名 where 条件  （子查询），
Select * from student where age > 18 (查找所有学生表中大于18岁的学生)
2）in  
Select * from student where name in (select name from chengji where score >90)
3)exists 用于检查子查询是否至少返回一行数据
4)like  (用于模糊搜索，允许使用通配符%和_)
Select * from student where name like ‘%s%’
5)between and
Select * from student where age between 20 and 22
limit（oracle和sqlserver如何做分页的语句）
Sqlsever
Select * from student Order by age desc limit 10;
oracle不支持limit
Select * from (select * from student order by age where age>18) where rnum >10

多表查询：

等值连接，内连接，左连接，右连接，完全连接，交叉连接，复杂子查询

等值连接：select s.id,s.name,s.bjname,s.address from student s,chengji c where s.bjid = c.bjid;
内连接：select s.id,s.name,c.bjname,s.address from student s inner join chengji c on s.bjid = c.bjid;
左连接：select s.id,s.name,s.bjname,s.address from student s left join chengji c on s.bjid = c.bjid;
右连接：select s.id,s.name,s.bjname,s.address from student s right join chengji c on s.bjid = c.bjid;
完全连接：select s.id,s.name,s.bjname,s.address from student s full join chengji c on s.bjid = c.bjid;
交叉连接：select s.id,s.name,s.bjname,s.address from student s cross join chengji c

视图

一种虚拟的表，它并不实际存储数据，而是存储一条 select语句的结果集。视图的数据来源于一个或多个实际存在的表，并且在查询视图时，mysql会执行视图定义中的 select语句，从而动态地从基表中获取数据。
Create view student as select name,age from student;

索引、优化

索引

一、聚集索引：

决定了表中行的物理顺序，一个表只能有一个聚集索引，因为表的行是根据聚集索引的键值排序的。

二、非聚集索引：

并不改变表中行的物理顺序，而是创建一个独立的索引结构，其中包含了索引列的值和指向实际数据行的指针。

三、唯一索引：

保证索引列的值是唯一的，可以是聚集的或者非聚集的。

四、全文索引：

用于全文搜索，是用于文本列的快速全文搜索。

五、空间索引：

用于地理空间数据，如存储地图坐标

查询优化：

一、使用合适的索引：

在经常用于 WHERE, JOIN, ORDER BY 和 GROUP BY 的列上创建索引。
避免在索引列上使用函数或表达式，因为这会阻止数据库使用索引。

二、避免全表扫描：

尽量避免使用 SELECT *，而是指定需要的列，减少数据传输量。
避免在 WHERE 子句中使用 != 或 <>，因为这通常会导致全表扫描。

三、使用覆盖索引：

如果索引包含了查询中需要的所有列，数据库可以直接从索引中获取数据，而不需要回表查询，这称为覆盖索引。

四、并行查询：

在支持并行查询的数据库系统中，可以利用多核处理器并行处理查询，以提高性能。

五、合理使用子查询和连接：

小心使用子查询，有时候内连接或外连接可能更高效。优化连接顺序，优先连接较小的表。

六、限制结果集：

使用 LIMIT 限制返回的行数，特别是在开发和测试环境中。

七、统计信息和分析：

定期更新统计信息，帮助查询优化器做出更好的决策。

事务

事务的执行存寻ACID特性，如上所述

管理事务的三种关键字：

BEGIN TRANSACTION：开启一个新的事务。
COMMIT：提交事务，使事务中的所有更改成为永久性的。
ROLLBACK：回滚事务，取消事务中的所有更改，恢复到事务开始前的状态。

函数与存储过程

函数主要用于执行计算或者返回单一值，它们可以接受输入参数，并且总是返回一个结果，通常是单一的值或结果集。函数在SQL查询中通常用于数据处理或转换，如字符串操作，数学运算，日期时间计算等。
存储过程可以包含复杂的SQL语句和流程控制逻辑，如条件分支和循环，它们可以执行一系列操作，包括数据的读取，插入，更新，删除，或者调用其他存储过程或者函数。

触发器

DML触发器：这些触发器在对表执行DML操作时触发，比如insert，update和delete，可进一步划分为before和after触发器
DDL触发器：在对表执行DDL数据定义语言时触发，比如alter，drop，create由于只能在after模式下工作，所有不能被撤销或者代替。
登录触发器：任何用户在登录到SQLSever实例时触发，用于审计或者其他安全目的。