数据库系统概论--约束、视图、游标、缓冲池

柚稚姐姐

已于 2022-03-17 22:48:03 修改

阅读量1k

点赞数

分类专栏： # 数据结构文章标签：数据库 sql sqlserver

于 2022-03-14 23:08:26 首次发布

本文链接：https://blog.csdn.net/qq_41977843/article/details/123491190

版权

数据结构专栏收录该内容

8 篇文章 3 订阅

订阅专栏

约束

1.什么是数据库的完整性约束

为什么需要约束：为了保证数据的完整性

什么叫约束：对表中字段的限制

2.约束的分类：

（1）列级约束

（2）表级约束

3.约束的作用：

（1）not null（非空值约束）：没有表级约束，只有列级约束

#3 not null (非空约束)
#3.1-在CREATE TABLE时添加约束
CREATE TABLE testl(
id INT NOT NULL,l
last _name VARCHAR (15)NOT NULL,
email VARCHAR(25),
salary DECIMAL (10,2)
);

（2）unique:（唯一约束）:可以向声明为unique的字段上添加null值。而且可以多次添加null

#4.1 在CREATE TABLE时添加约束
CREATE TABLE test2(
id INT UNIQUE,
last_name VARCHAR(15) ,
emaii VARCHAR(25)UNIQUE,
salary DECIMAL ( 10,2)
) ;

#4.1 在CREATE TABLE时添加约束
CREATE TABLE test2(
id INT UNIQUE，#列级约束
last_name VARCHAR(15) ,
emaii VARCHAR (25),
salary DECIMAL ( 10,2),
#表级约束
CONSTRAINT uk_test2_email UNIQUE ( email)) ;

#4.2在ALTER TABLE时添加约束DEsc test2 ;
UPDATE test2
SET salary = 5000
WHERE id = 3 ;

#方式1:
ALTER TABLE test2
ADD CONSTRAINT uk_test2_sal UNIQUE(salary);

#方式2:
ALTER TABLE test2
MODIFY last_name VARCHAR(15) UNIQUE;

（3）primary key（主键约束）:可以再列级别约束、也可以在表级别约束；

如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。

（4）foreign key (外键约束)

（6）check (检查约束)

（7）default (默认约束)

4.如何添加/删除约束

（1）creat table时添加约束

（2）alter table 时增加约束、删除约束

完整性约束条件是指数据库中的数据应该满足的语义约束条件

4.DBMS的完整性控制机制应具有哪三个方面的功能？

（1）定义功能：提供定义完整性约束条件的机制，完整性约束条件也称为完整性规则，是数据库中必须满足的予以约束条件

（2）检查功能：检查用户发出的操作（Insert、Update 、delete）请求是否违背了完整性约束条件

（3）违约处理功能：用户操作请求使数据违背了完整性约束条件，则采取一定的动作来保证数据的完整性（NO ACTION），拒绝或者级联执行其他操作（CASCADE）

DBMS进行检查的好处

用DBMS管理的好处：不必由应用程序来完成，从而减轻了应用程序员的负担。

视图

（1）视图：一个或者多个数据表里的数据的逻辑显示，视图并不存储数据，视图是一种虚拟的表，本身不具有数据，占用很少的空间。

（2）视图的本质：可以看做是存储起来的select语句

视图中select语句涉及到的表，称为基表

视图本身的删除不会导致数据的删除

视图的应用场景：针对于大型项目

（3）.视图的优点：简化查询,减少数据的冗余，数据安全（设置访问权限）

CREATE DATABASE dbtest;
USE dbtest;

CREATE TABLE emps
AS
SELECT *  
FROM atguigudb.employees;

CREATE TABLE depts
AS
SELECT *
FROM atguigudb.departments;

SELECT * FROM emps;

SELECT * FROM depts;

DESC emps;

DESC atguigudb.employees;


#1.针对于单标
CREATE VIEW vu_emp1
AS 
SELECT employee_id,last_name,salary
FROM emps;

SELECT * FROM vu_emp1;


#查询语句中的字段的别名会坐作为视图的中的字段出现呢
CREATE VIEW vu_emmp2
AS 
SELECT employee_id,last_name,salary
FROM emps
WHERE salary>8000;

SELECT * FROM vu_emmp2;

#小括号内字段个数与select中字段的个数相同
CREATE VIEW vu_emp3(emp_id,NAME,monthly_sal)
AS
SELECT employ_id,last_name,salary
FROM emps
WHERE salary>8000

#情况二：视图中的字段在基表中可能没有对应的字段
CREATE VIEW vu_emp_sal
AS
SELECT department_id,AVG(salary) as  avg_sal
FROM emps
WHERE department_id IS NOT NULL
GROUP BY department_id;

SELECT * FROM vu_emp_sal

#针对多表
CREATE VIEW vu_emp_dep
AS
SELECT e.employee_id,d.department_id,d.department_name
FROM emps e JOIN depts  d
ON e.department_id=d.department_id

SELECT * FROM vu_emp_dep;

#基于视图创建视图
CREATE VIEW vu_emp4
AS
SELECT employee_id,last_name
FROM vu_emp1;

SELECT * FROM vu_emp4;

#查看视图
SHOW TABLEs;

DESCRIBE vu_emp4;

SHOW TABLE STATUS LIKE 'vu_emp1';

SELECT employee_id,last_name,salary
FROM emps;

#更新视图的数据，会导致基表中的数据的修改
UPDATE vu_emp1
SET salary = 20000
WHERE employee_id=101;

#同理，更新表中的数据，也会导致途中的数据的修改
UPDATE emps
SET salary = 10000
WHERE employee_id = 101;

SELECT * FROM emps;

#删除视图中的数据，也会导致表中的数据的删除


#修改视图

DESC vu_emp1;

CREATE OR REPLACE VIEW vu_emp1
AS
SELECT employee_id,last_name,salary,email
FROM emps
WHERE salary>7000

SELECT * FROM vu_emp1;

#删除视图
DROP VIEW vu_emp4;

SHOW TABLES;

DROP VIEW IF EXISTS vu_emp1;

游标

游标，提供了一种灵活的操作方式，让我们能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作的数据结构。游标让sQL这种面向集合的语言有了面向过程开发的能力。

在SQL中，游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。

这里游标充当了指针的作用，我们可以通过操作游标来对数据行进行操作。

MysQL中游标可以在存储过程和函数中使用。


/*游标的使用


#声明游标

#打开游标

#使用游标（从游标中获取资源）

#关闭游标，游标会占用系统资源，如果不及时关闭，则会一直在存储过程中

*/

#举例:创建存储过程get_count_by_limit_total_salary()"，声明IN参数limit_total_salary，DOUBLE类型;
#声明OUT参数total_count，INT类型。函数的功能可以实现累加薪资最高的几个员工的薪资值，
#直到薪资总和达到limit_total_salary参数的值，返回累加的人数给total_count。


DELIMITER //
CREATE PROCEDURE ts(IN limt_total_salary DOUBLE, OUT total_count INT)

BEGIN
			#先声明变量
			DECLARE sum_sal DOUBLE DEFAULT 0.0;
			DECLARE esal DOUBLE;
			DECLARE ecount INT DEFAULT 0;
			#声明游标
			DECLARE emp_cursor CURSOR FOR SELECT  salary FROM employees ORDER BY salary desc;
			#使用游标
			OPEN emp_cursor;
			#使用游标			
			REPEAT
			
						FETCH emp_cursor INTO esal;
						SET sum_sal = sum_sal + esal;
						SET ecount = ecount + 1;
						UNTIL sum_sal >= limit_total_salary
			END REPEAT;
			SET total_count = ecount;		
			#关闭游标	
			CLOSE emp_cursor;
			
END //
DELIMITER;


#调用
CALL ts(100000,@total_count);

SELECT @total_count;

游标是MysQL的一个重要的功能，为逐条读取结果集中的数据，提供了完美的解决方案。跟在应用层面实现相同的功能相比，游标可以在存储程序中使用，效率高，程序也更加简洁。但同时也会带来一些性能问题，比如在使用游标的过程中，会对数据行进行加锁，这样在业务并发量大的时候不仅会影响业务之间的效率, 还会消耗系统资源，造成内存不足，这是因为游标是在内存中进行的处理。
建议:养成用完之后就关闭的习惯，这样才能提高系统的整体效率。

缓冲池

1.数据库缓冲池(buffer pool)
InnoDB存储引擎是以页为单位来管理存储空间的，我们进行的增删改查操作其实本质上都是在访问页面〈包括读页面、写页面、创建新页面等操作)。而磁盘I/O需要消耗的时间很多，而在内存中进行操作，效率则会高很多，为了能让数据表或者索引中的数据随时被我们所用，DBMS会申请占用内存来作为数据缓冲池，在真正访问页面之前，需要把在磁盘上的页缓存到内存中的Buffer Pool之后才可以访问。.
这样做的好处是可以让磁盘活动最小化，从而减少与磁盘直接进行工/O 的时间。要知道，这种策略对提升sQL语句的查询性能来说至关重要。如果索引的数据在缓冲池里，那么访问的成本就会降低很多。

2.缓冲池(Buffer Pool)
InnoDB缓冲池包括了数据页、索引页、插入缓冲、锁信息、自适应Hash和数据字典信息等。

3.缓存原则:
“位置*频次”这个原则，可以帮我们对IO访问效率进行优化。
首先，位置决定效率，提供缓冲池就是为了在内存中可以直接访问数据。
其次，频次决定优先级顺序。因为缓冲池的大小是有限的，比如磁盘有200G，但是内存只有16G，缓冲池大小只有1G，就无法将所有数据都加载到缓冲池里，这时就涉及到优先级顺序，会`优先对使用频次高的热数据进行加载