关系模型和SQL

最新推荐文章于 2024-04-23 12:39:47 发布

都枯槐

最新推荐文章于 2024-04-23 12:39:47 发布

阅读量1k

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/qq_36883141/article/details/93135988

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

SQL语句

SQL是结构化查询语言Structured Query Language,1987年被ISO组织标准化
所有主流的关系型数据库都支持SQL，NoSQL也很大部分支持SQL
SQL语句分为：

DDL数据定义语言，负责数据库定义、数据库对象定义，由CREATE、ALTER与DROP三种语句组成
DML数据操作语言，负责对数据库对象的操作，CRUD增删改查
DCL数据控制语言，负责数据库权限访问控制，由CRANT和REVOKE两个指令组成
TCL事物控制语言，负责处理ACID事务，支持commit、rollback指令

语言规范

SQL语句大小写不敏感
- 一般建议，SQL的关键字，函数等大写
SQL语句末尾应该使用分号结束
注释
- 多行注释/*注释内容*/
- 单行注释–注释内容
- MySQL注释可以使用#
使用空格或缩进来提高可读性
命名规范
- 必须以字母开头
- 可以使用数字、#、$和_
- 不可使用关键字

DCL

GRANT授权、REVOKE撤销

#创建并授权用户
GRANT ALL ON *.* TO '用户名'@‘%’ IDENTIFIED by '密码';
# 授权所有的在某个库 给哪个用户 可以密码识别
#移除所有的授权
REVOKE ALL ON *.* FROM '用户名';

*为通配符，指代任意库或者任意表
*.*表示库的所有表
test.*，表示test库下所有的表
%为通配符，匹配任意长度字符串

DDL

删除用户

# 删除用户
DROP USER ‘用户民’;

库是所有数据的集合，所有数据按照数据模型组织在数据库中

创建数据库

# 创建数据库
CREATE DATABASE IF NOT EXISTS '库名' CHARACTER SET utf8mb4 COLLATE utf8mb4 COLLATE utf8mb4_general_ci;
CREATE DATABASE IF NOT EXISTS '库名' CHARACTER SET utf8;

CHARACTER SET 指定字符集
utf8mb4是utf8的扩展，支持4字节utf8mb4，需要MySQL5.5.3+。
COLLATE指定字符集的校对规则，用来做字符串的比较的。

删除数据库

#删除数据库
DROP DATABASE IF EXISTS '库名'

创建表
表分为行和列，MySQL是行存数据库，数据是一行行存的，列必须固定多少列。
行Row，也称为记录Record，元组
列Column，也称为字段Field、属性
字段的取值范围叫做域Domain。

CREATE TABLE `employees` (
  `emp_no` int(11) NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

反引号标注的名称，会被认为是非关键字，使用反引号避免冲突
DESC

查看列信息

# 查看列信息
{DESCRIBE | DESC} tbl_name [col_name | wild]
DESC test;
DESC test '%name';

关系

在关系数据库中，关系就是二维表，由行和列组成。
行Row，也称为记录Record，元组
列Column，也称为字段Field、属性
字段的取值范围叫做域Domain。
维数：关系的维数指关系中属性的个数
基数：元组的个数
注意在关系中，属性的顺序并不重要。理论上，元组顺序也不重要，但是由于元组顺序与存储相关，会影响查询效率。

候选键
关系中，能唯一标识一条元组的属性或属性集合，称为候选键。
PRIMARY KEY主键
表中一列或者多列组成唯一的key，也就是通过这一个或多个列能唯一的标识一条记录。即被选择的候选键，主键的列不能有包含控制null。主键往往设置为整型、长整型，可以为自增AUTO_INCREMENT字段。
表中可以没有主键，但是，一般表设计中，往往都会有主键，以避免记录重复。
Foreign Key外键
严格来讲，当一个关系中的某一个属性或属性集合与另一个关系(可以是自身)的候选键匹配时，就称作这个属性或属性集合为外键。
索引Index
可以看做是一本字典的目录，为了快速检索用的，空间换时间，显著提高查询效率。
可以对一列或者多列字段设定索引。

主键索引，主键会自动建立主键索引，主键本身就是为了快速定位唯一记录的。
唯一索引，表中的索引列组成的索引必须唯一，但是可以为空，非空值必须唯一。
普通索引，没有唯一性的要求，就是建了一个子弟那的目录而已。

在MySQL中，InnoDB和MylSAM的索引数据结构可以使用Hash或BTree，默认是BTree。
Hash时间复杂度是O(1),但是只能进行精确匹配，也就是Hash值得匹配。
BTree，索引可以提高查询所读，但是却影响增删改查的效率，因为需要索引更新或重构。频繁出现在where子句中的列可以考虑使用索引。要避免把性别这种字段设索引。

约束

为了保证数据的完整正确，数据模型还必须支持完整性约束。
‘必须有值’约束
某些列的值必须有值，不许为空NULL。

域约束
限定了表中字段的取值范围
实体完整性
PRIMARY KEY约束定义了主键，就定义了主键约束。主键不重复且唯一，不能为空。
引用完整性
外键定义中，可以不是引用另一张表的主键，但是往往实际只会关注引用主键。
外键：在表B中的列，引用了表A中的主键，表B中的列就是外键。
A表视为主表，B表称为从表。
- 插入规则
  不需要指定。
  如果在表B插入一条记录，B的外键列插入一个值，这个值必须是表A中存在的主键值。
- 更新原则
  定义外键约束时指定该规则。
- 删除规则
  定义外键约束时指定该规则。
  外键约束操作
  
  外键约束，是为了保证数据完整性、一致性，杜绝数冗余、数据错误。

实体-联系E-R

数据库建立，需要收集用户需求，设计符合企业要求的数据模型，而构建这种模型需要方法，这种方法需要成为E-R实体-联系建模，也出现了一种建模语言-----UML统一建模语言
实体Entity:现实世界中具有相同属性的一组对象，可以是物理存在的事物或抽象的事物。
联系：实体之间的关联集合。
在这里插入图片描述
一对一关系用的较少，往往表示表A的一条记录唯一关联表B的一条记录。
它往往是为了将一张表多列分割并产生成了多张表，合起来是完整的信息，或为了方便查询，或为了数据安全隔离
一部分字段的数据等等。

视图

视图，也称为虚表，看起来像表，它是由查询语句生成的，可以通过视图进行CRUD操作。
视图的作用：

简化操作，将复杂查询SQL语句定义为视图，可以简化查询。
数据安全，视图可以只显示真实表的部分列，或计算后的结果，从而隐藏真实表的数据

数据类型

在这里插入图片描述
LENGTH函数返回字节数，而char和varchar定义的M是字符数限制。
char可以将字符串定义为固定长度，空间换时间，效率略高；varchar为变长，省了空间。

关系操作

关系：在关系数据库中，关系就是二维表
关系操作就是对表的操作
选择：又称为限制，是从关系中选择出满足给定条件的元组
投影：在关系上投影就是选择出若干属性列组成新的关系。
连接：将不同的两个关系连接成一个关系

DML --CRUD增删改查

insert语句

INSERT INTO table_name (col_name,……)  VALUES  (value1,……)；
-- 向表中插入一行数据，自增字段，缺省值字段、可为空字段可以不写
INSERT INTO table_name SELECT ……；
-- 将select查询的结果插入表中
INSERT INTO table_name (col_name1,……) VALUES (value1,……) ON DUPLICATE KEY UPDATE col_name=value1,……；
-- 如果主键冲突、唯一键冲突就执行update后的设置，这条语句的意思，就是主键不在新增记录，主键在就更新部分字段
INSERT IGNORE INTO table_name (col_name,……) VALUES (value，……)；
-- 如果主键冲突、唯一键冲突就忽略错误，返回一个警告

update语句

UPDATE [IGNORE] tal_name SET col_name1=expr1 [,col_name2=expr2……] [WHERE where_definition]；
-- IGNORE 意义同INSERT语句
UPDATE reg SET name='ben'；
-- 注意这一句非常危险，会更新所有数据
UPDATE reg name='ben' ,password ='benpwd' WHERE id =1;
-- 更新一定加条件

Delete语句

DELETE FROM tal_name [WHERE where_definition]
-- 删除符合条件的记录
DELETE FROM reg WHERE id=1;
-- 删除一定要有条件

Select语句

SELECT
	 [DISTINCT]
	 select_expr, ...
	 [FROM table_references
	 [WHERE where_definition]
	 [GROUP BY {col_name | expr | position}
	 [ASC | DESC], ... [WITH ROLLUP]]
	 [HAVING where_definition]
	 [ORDER BY {col_name | expr | position}
	 [ASC | DESC] , ...]
	 [LIMIT {[offset,] row_count | row_count OFFSET offset}]
	 [FOR UPDATE | LOCK IN SHARE MODE]]

FOR UPDATE会把行进行写锁定，这是排它锁。

查询
查询的结果成为结果集

Limit 子句

-- 返回5条记录
SELECT * FROM  tal_name LIMIT 5;
-- 返回5条记录，偏移18条
SELECT * FROM tal_name  LIMIT 5 OFFSET 18;
SELECT * FROM tal-name  LIMIT 18, 5;

Where子句

运算符	描述
=	等于
<>	不等于
>、<、>=、<=	大于、小于、大于等于、小于等于
BETWEEN	在某个范围之内，between a and b 等价于[a,b]
LIKE	字符串模式匹配，%表示任意多个字符，_表示一个字符
IN	指定针对某个列的多个可能值
AND	与
OR	或

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

Order by 子句
对查询结果进行排序，可以升序ASC、降序DESC

-- 降序
SELECT * FROM tal_name WHERE selection ORDER BY selection DESC;

DISTINCT
不返回重复记录

-- DISTINCT 使用
select distinct selection from tal_name;

聚合函数

函数	描述
COUNT(expr)	返回记录中记录的数目，如果指定列，则返回非NULL值的行数
COUNT(DISTINCT expr,[expr……])	返回不重复的非NULL值的行数
AVG([DISTINCT] expr)	返回平均值，返回不同值的平均值
MIN(expr),MAX(expr)	最大值，最小值
SUM(DISTINCT) expr)	求和，Distinct返回不同值求和

分组查询
使用Group by子句，如果有条件，使用Having子句过滤分组、聚合过的结果。

分组是将数据按照指定的字段分组，最终每组只能出来一条数据，这样就到来了问题，哪一组谁做代表，其实谁做代表都不合适。
如果只投影分组字段、聚合数据，不会有问题，如果投影非分组字段，显示的时候不能确定是组内谁的数据。

子查询
查询语句可以嵌套，内部查询就是子查询
子查询必须在一组小括号中
子查询不能使用Order by
连接join
交叉连接 cross join
笛卡尔乘积，全部交叉
在MySQL中，CROSS JOIN从语法上说与INNER JOIN等同

内连接
inner join,省略为join。
等值连接，只选某些field相等的元组，使用ON限定关联的结果
自然连接，特殊的等值连接，会去掉重复的列

外连接
outer join,可以省略为join
分为左外连接，即左连接；右外连接，即右拦截；全连接

存储过程、触发器

存储过程，数据库系统中，一段完成特定功能的SQL语句，编写成类似函数的方式，可以传参并调用。支持流程控制语句
触发器，由事件触发的特殊的存储过程。
这两种技术，虽然是数据库高级内容，性能不错，但基本很少用了。
它们移植性差，使用时占用的服务器资源，排错、维护不方便。
最大的原因，不太建议把逻辑放在数据库中。

事物

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

特性	描述
原子性	一个事务是不可分割的工作单位，事务中包括的所有操作要么全部做完，要么什么都不做
一致性	事务必须是使数据库一个一致性状态变到另一个一致性状态，一致性和原子性是密切相关的
隔离性	一个事务的执行不能被其他事务干扰，即同一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰
持久性	持久性也称永久性，指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的，接下来的其他操作或故障不应该对其有任何影响

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

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

更新丢失Lost Update
事务A和B，更新同一个数据，它们都读取了初始值100，A要减10，B要加100，A减去10后更新为90，B加
100更新为200，A的更新丢失了，就像从来没有减过10一样。
脏读
事务A和B，事务B读取到了事务A未提交的数据（这个数据可能是一个中间值，也可能事务A后来回滚事
务）。事务A是否最后提交并不关心。只要读取到了这个被修改的数据就是脏读。
不可重复读Unrepeatable read
事务A在事务执行中相同查询语句，得到了不同的结果，不能保证同一条查询语句重复读相同的结果就是不可
以重复读。
例如，事务A查询了一次后，事务B修改了数据，事务A又查询了一次，发现数据不一致了。
注意，脏读讲的是可以读到相同的数据的，但是读取的是一个未提交的数据，而不是提交的最终结果。
幻读Phantom read
事务A中同一个查询要进行多次，事务B插入数据，导致A返回不同的结果集，如同幻觉，就是幻读。
数据集有记录增加了，可以看做是增加了记录的不可重复读。

隔离级别越高，串行化越高，数据库执行效率低；隔离级别越低，并行度越高，性能越高。
隔离级别越高，当前事务处理的中间结果对其它事务不可见程度越高。

SERIALIZABLE，串行了，解决所有问题
REPEATABLE READ，事务A中同一条查询语句返回同样的结果，就是可以重复读数据了。例如语句为(select * from user)。解决的办法有：
1、对select的数据加锁，不允许其它事务删除、修改的操作
2、第一次select的时候，对最后一次确切提交的事务的结果做快照
解决了不可以重复读，但是有可能出现幻读。因为另一个事务可以增删数据。
READ COMMITTED，在事务中，每次select可以读取到别的事务刚提交成功的新的数据。因为读到的是提交后的数据，解决了脏读，但是不能解决不可重复读和幻读的问题。因为其他事务前后修改了数据或增删了数据。
READ UNCOMMITTED，能读取到别的事务还没有提交的数据，完全没有隔离性可言，出现了脏读，当前其他问题都可能出现。

事务语法
START TRANSACTION或BEGIN开始一个事务，START TRANSACTION是标准SQL的语法。
使用COMMIT提交事务后，变更成为永久变更。
ROLLBACK可以在提交事务之前，回滚变更，事务中的操作就如同没有发生过一样（原子性）。
SET AUTOCOMMIT语句可以禁用或启用默认的autocommit模式，用于当前连接。SET
AUTOCOMMIT = 0禁用自动提交事务。如果开启自动提交，如果有一个修改表的语句执行后，会立即把更新存储到磁盘。

数据仓库和数据库的区别

本质上来说没有区别，都是存放数据的地方。
但是数据库关注数据的持久化、数据的关系，为业务系统提供支持，事务支持；
数据仓库存储数据的是为了分析或者发掘而设计的表结构，可以存储海量数据。
数据库存储在线交易数据OLTP（联机事务处理OLTP，On-line Transaction Processing）；数据仓库存储历史数据
用于分析OLAP（联机分析处理OLAP，On-Line Analytical Processing）。
数据库支持在线业务，需要频繁增删改查；数据仓库一般囤积历史数据支持用于分析的SQL，一般不建议删改。

都枯槐

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
关系模型和SQL

SQL语句SQL是结构化查询语言Structured Query Language,1987年被ISO组织标准化所有主流的关系型数据库都支持SQL，NoSQL也很大部分支持SQLSQL语句分为：DDL数据定义语言，负责数据库定义、数据库对象定义，由CREATE、ALTER与DROP三种语句组成DML数据操作语言，负责对数据库对象的操作，CRUD增删改查DCL数据控制语言，负责数据库权...
复制链接

扫一扫