526互联

数据库总结复习（sql应用题 一）

发布时间 2023-12-02 16:23:17作者: lmj625

目录

前言

本文针对考纲上的30分sql应用题所涉及到的知识进行归纳总结。
会分为两篇文章，此篇为mysql语句。



mysql基础语句

ddl

Data Definition Language，数据定义语言。create，drop，alter等定义表结构的语句。

示例1 创建表

  • 主键外键定义
  • 非空约束
  • 唯一约束
CREATE TABLE emp (
    id INT NOT NULL UNIQUE,
    name VARCHAR(20),
    password VARCHAR(20),
    address VARCHAR(30),
    salary DOUBLE,
	dep_id INT,
	primary key (id),
	constraint fk_emp_dept foreign key(dep_id)references dept(id)
);
  • 增添检查约束
alter table emp add constraint CK_emp_salary check(salary>0 and salary<200000);

dcl

Data Control Language，数据控制语句。grant,revoke之类的。

授权

示例：授予用户学生成绩数据库中对所有表的select,delete权限。并且允许其把权限授予他人。

直接授权：

grant select,delete on xscj.* to user1 with grant option;

用角色授权：

create role role1;
grant select,delete on xscj.* to role1 with grant option;
grant role1 to user1;

收回权限

revoke select,delete on xscj.* from user1;

dml

Data Manipulation Language，数据操作语句。必考。按pta题集复习。

结合事务

  • 事务
    将员工'E001'转部门,从'行政部'门转至'人事部',并成为人事部门的经理(注意事务成功与失败的两种情况处理
-- 开启自动提交事务
START TRANSACTION;

-- 将员工'E001'转至人事部
UPDATE emp
SET dno = (SELECT dno FROM dept WHERE dname = '人事部')
WHERE eno = 'E001';

-- 检查第一个UPDATE语句的影响行数
IF ROW_COUNT() = 0 THEN
    -- 第一个UPDATE语句失败，回滚事务
    ROLLBACK;
ELSE
    -- 将员工'E001'设为人事部门经理
    UPDATE dept
    SET mgr_eno = 'E001'
    WHERE dname = '人事部';

    -- 检查第二个UPDATE语句的影响行数
    IF ROW_COUNT() = 0 THEN
        -- 第二个UPDATE语句失败，回滚事务
        ROLLBACK;
    ELSE
        -- 两个UPDATE语句都成功，提交事务
        COMMIT;
    END IF;
END IF;


索引

不应该在这里考察，应该是和关系代数语法树一起考。

分类

索引分为普通索引、唯一性索引、全文索引、单列索引、多列索引。
实现方法有B+树（分聚集索引和非聚集），哈希。
聚集索引是逻辑和物理上都顺序存放的，树叶子结点是数据。
非聚集索引只是逻辑上顺序存放，叶子结点是主键和物理地址。


格式

CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])
示例：create index idx_name on t_dept(name);


推荐阅读：
mysql索引分类
mysql索引介绍
创建索引三种方式



视图

示例：为表 sc 创建一个视图 s_grade(sno,sname,cou_cnt,avg_grade,fail_cnt)，包括有各个学生学号、姓名、选修课程的门数、平均分及不及格门数。

create view s_grade as
select stu.sno,
	sname,
	count(cno) as cou_cnt,
	ifnull(AVG(sc.grade), 0) as avg_grade,
	sum( if( grade<60 ,1,0) ) as fail_cnt
from sc left join stu on stu.sno=sc.sno
group by stu.sno;

行列子集视图

从单个基本表导出的,并且只是去掉了基本表的某些行和某些列,但保留了主码,则称这类视图为行列子集视图。


可更新性

行列子集视图可以更新。

想更新视图，那么必须确保视图的定义满足以下条件：
视图必须只引用一个基本表。
视图的SELECT语句必须是可删除的，即它不能包含聚合函数、计算列、GROUP BY子句等。
视图必须包含唯一标识行的列，通常是主键。

不可更新的视图情形如下：

  • 包含聚合函数，如 SUM(), MIN(), MAX(), COUNT()等。
  • 包含 DISTINCT、GROUP BY、HAVING 和 UNION、UNION ALL 等关键字。
  • 包含子查询。
  • 引用不可更新视图。
  • 创建视图时使用临时表，即设置 ALGORITHM = TEMPTABLE。
  • 仅引用常量值，即常量视图。


存储过程

  • 会结合事务一起考

示例1 带返回值

创建存储过程 P4，输入某课程名，查询该课程的平均分，选课人数并输出之。

create procedure p4(acname char(32),out avg_grd decimal(4,1),out cnt_stu int)
begin
	select IFNULL(avg(grade),0),IFNULL(count(sno),0)
	into avg_grd,cnt_stu
	from cou left join sc on cou.cno = sc.cno
	where cou.cname = acname;
end;
call p4('C语言',@avg,@cnt);
select @avg,@cnt;

示例2 游标

创建存储过程 P8，查询每个学生在每个学期所学课程门数、平均分以及不及格的课程门数。

要求：使用游标，取出 stu 表中每个学生学号，逐个学生调用存储过程 P7，将每个学生的每个学期的课程门数、平均分以及不及格的课程门数插入到临时表中。最终查询该临时表显示结果集。

注意：临时表在当前用户会话期间都存在，第一次调用 P7 之前，应先删除该临时表，运行存储过程 P8 后，将创建的临时表删除。

create procedure p8()
begin
	declare asno varchar(8);
	declare done int default 0;

	declare cur_stu cursor for
		select sno from stu;
	declare continue handler for not found set done = 1;

	open cur_stu;
	fetch cur_stu into asno;
	repeat
		call p7(asno);
		fetch cur_stu into asno;
	until done = 1
	end repeat;
	close cur_stu;
end;

示例3 结合简单事务

创建存储过程 P1 删除某部门及其相关信息：

DELIMITER //
CREATE PROCEDURE P1(IN department_id VARCHAR(10))
BEGIN
    -- 定义异常处理方式
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Error occurred during the transaction';
    END;

    START TRANSACTION;
    -- 删除部门职工
    DELETE FROM employees WHERE department_id = department_id;
    -- 删除部门
    DELETE FROM departments WHERE department_id = department_id;
    COMMIT;
END //
DELIMITER ;


触发器

当 major 表删除一条专业记录时，检查该专业在 stu 表中是否已经存在学生记录，如果已经存在，将原该专业学生的 mno 全部置为空。如果修改某专业的 mno，也随之修改在 stu 表中该专业学生的 mno 值。

delimiter //
create trigger tri_bfdlt_mj before delete on major for each row
begin
	if (select count(*) from stu where stu.mno=old.mno) > 0 then
		update stu
		set stu.mno = null
		where stu.mno = old.mno;
	end if;
end//
delimiter ;