数据库语句练习题

最新推荐文章于 2023-10-28 13:16:33 发布

teree_

最新推荐文章于 2023-10-28 13:16:33 发布

阅读量1.2k

点赞数 3

分类专栏： # 数据库原理与应用笔记

teree_

本文链接：https://blog.csdn.net/qq_44152807/article/details/106035752

版权

数据库原理与应用笔记专栏收录该内容

12 篇文章 1 订阅

订阅专栏

文章目录

一、简单查询和连接查询
- 学生选课数据库查询练习
- 职工部门数据库查询练习
二、嵌套查询练习
- 学生选课数据库练习
- 高校图书借阅数据库
三、组合和统计查询
- 图书借阅数据库查询
四、数据完整性和安全性

一、简单查询和连接查询

学生选课数据库查询练习

Student(Sno,Sname,Sage,Ssex,Ssdept) ；
Course(Cno,Cname,Ccredit,Cpno)；
SC(Sno,Cno,Grade)；

在这里插入图片描述

职工部门数据库查询练习

在这里插入图片描述

二、嵌套查询练习

学生选课数据库练习

Student(Sno,Sname,Sage,Ssex,Ssdept)；
Course(Cno,Cname,Ccredit,Cpno)；
SC(Sno,Cno,Grade)；

在这里插入图片描述

针对C1课程，查询C1课程的成绩高于张三的学生学号和成绩”时容易写出的错误语句
即如下是错误写法：

--针对C1课程，查询C1课程的成绩高于张三的学生学号和成绩的错误写法
SELECT Sno,Grade
FROM SC
WHERE Grade>(SELECT Grade
			 FROM SC
			 WHERE Cno=’C1’ AND Sno IN(SELECT Sno
									   FROM Student
							   		   WHERE Sname=’张三’))

如上语句在具体实验过后，发现结果不正确，后分析这样的语句得到的是所有课程中
只要成绩大于张三的C1课程成绩就会输出。即输出了C2等其他课程成绩大于张三的C1课程的情况。实际应该在最外层的WHERE后在加上Cno=’C1’的条件

在这里插入图片描述

高校图书借阅数据库

在这里插入图片描述

三、组合和统计查询

图书借阅数据库查询

在这里插入图片描述

在写第七题：查询既借阅了书号为“j0001”，又借阅了书号为“j0002”的读者编号。
错误写为：

-- 查询既借阅了书号为“j0001”，又借阅了书号为“j0002”的读者编号的错误写法
SELECT 读者编号
FROM 借阅
WHERE 书号='j0001' AND 书号='j0002'

这个语句的意思应该是借阅了j0001同时借阅了j0002,计算机按行扫描，这样写是无法得出正确答案的

四、数据完整性和安全性

1.授权与收权

use 数据库名称
grant 权限名 on 表名(对象名) to 用户名--授权
revoke 权限名 on 表名(对象名) from 用户名--收权

2.触发器的定义与使用语句

creat trigger 触发器名称 on 表名 
for 操作名(insert/Delete/update)
as 具体语句

①.“读者”关系表定义一个“Delete”触发器，要求删除读者记录的同时把读者的借还记录也删除。

触发器的原理
在删除某行读者记录时，并不是直接删除。而是先将它放到deleted表中。
然后进行验证，deleted表中的内容是否和借阅表中对应的读者编号相同
相同则删除

故在本题中，借阅表要和读者表刚要删的这一行作比较，而刚要删的这一行放在deleted中。
如果查询出在借阅表中的读者编号等于deleted表中的读者编号情况下统计结果大于0，则表示读者表中刚删的这一行在借阅表中有关联即(要删的这个读者还有借还记录)，然后就要用deleted语句来删借阅表中的记录

--“读者”关系表定义一个“Delete”触发器，要求删除读者记录的同时把读者的借还记录也删除。
use 图书借阅数据库--指定数据库
go
create trigger tr2 on 读者
for delete
-- 判断要删的读者记录对应的是否还有借阅记录
as if (select count(*)
       from 借阅,deleted
       where 借阅.读者编号=deleted.读者编号)>0--大于0则还有借还记录
-- 删除读者的借阅记录
   begin
     delete 借阅
     from 借阅 inner join deleted on 借阅.读者编号=deleted.读者编号--连接两表
   end

因为之前建立了三张表的外码约束，故当实际删除读者表中的记录时，会提示不能删除。要先删除读者表和借阅表的外码约束

②.对“借阅”关系表定义一个“insert”触发器，要求插入一条记录到“借阅”关系表中时，更新图书表中对应图书的库存量值。

--对“借阅”关系表定义一个“insert”触发器，要求插入一条记录到“借阅”关系表中时，更新图书表中对应图书的库存量值。
use 图书借阅数据库
go
create trigger tr1 on 借阅
for insert
as 
update 图书
set 库存量=库存量-1
where 书号=(select 书号
            from inserted)
go

mysql when条件触发器

DELIMITER $$

USE `图书租售管理`$$

DROP TRIGGER /*!50032 IF EXISTS */ `tr1`$$

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `tr1` AFTER INSERT ON `purchase` 
    FOR EACH ROW BEGIN
	UPDATE book
		SET stock=
			CASE
			   WHEN new.mode='租' THEN stock-1
			   WHEN new.mode='还' THEN stock+1
		           WHEN new.mode='买' THEN stock-new.number
			END
	        WHERE book_id=new.book_id;
   END;
$$

DELIMITER ;

在这里插入图片描述

触发器多条件多字段更新

DELIMITER $$

USE `图书租售管理`$$

DROP TRIGGER /*!50032 IF EXISTS */ `tr1`$$

CREATE
    TRIGGER `tr1` AFTER INSERT ON `purchase` 
    FOR EACH ROW BEGIN
	UPDATE book 
		SET stock=
			CASE
			   WHEN new.mode='租' THEN stock-1
			   WHEN new.mode='还' THEN stock+1
		           WHEN new.mode='买' THEN stock-new.number
			END,
			book.`rent_number`=
			    CASE
				WHEN new.mode='租' THEN book.`rent_number`+1
				 WHEN new.mode='还' THEN book.`rent_number`+0
		                 WHEN new.mode='买' THEN book.`rent_number`+0
		            END,
		            book.`sale_number`=
			    CASE
				WHEN new.mode='买' THEN book.`sale_number`+new.number
				WHEN new.mode='还' THEN book.`sale_number`+0
		                 WHEN new.mode='租' THEN book.`sale_number`+0
		            END
	        WHERE book_id=new.book_id;
   END;
$$

DELIMITER ;

存储过程

DELIMITER $$
CREATE
    PROCEDURE `图书租售管理`.`profit`(IN a_date DATE,IN b_date DATE)
    BEGIN
      SELECT book.name,book.`book_id`,sale_time,rent_number,sale_number,rent_number*rent_price AS rent_profit ,sale_number*sale_price AS sale_profit
      FROM book INNER JOIN purchase ON book.`book_id`=purchase.`book_id`
      WHERE sale_time BETWEEN a_date AND b_date;
    END$$

DELIMITER ;

/*调用存储过程*/
CALL profit(20210101,20210228);

//存储过程2：计算总价格
DELIMITER $$
CREATE
    PROCEDURE `图书租售管理`.`all_price`(IN cust_id VARCHAR(20))
    BEGIN
	DECLARE all_rent_price INT DEFAULT 0;
        DECLARE all_sale_price INT DEFAULT 0;
	
		#SET all_rent_price=
		SELECT purchase.`number`*book.`rent_price` INTO all_rent_price
				   FROM book INNER JOIN purchase ON book.`book_id`=purchase.`book_id`
				   WHERE purchase.`cus_id`=cust_id AND purchase.`mode`='租';
		#SET all_sale_price=
		SELECT purchase.`number`*book.`sale_price` INTO all_sale_price
				   FROM book INNER JOIN purchase ON book.`book_id`=purchase.`book_id`
				   WHERE purchase.`cus_id`=cust_id AND purchase.`mode`='买';
		SELECT book.`book_id`,book.`rent_price`,book.`sale_price`,purchase.`mode`,purchase.`number`,all_rent_price+all_sale_price AS all_price
		FROM book INNER JOIN purchase ON book.`book_id`=purchase.`book_id`
		WHERE purchase.`cus_id`=cust_id;
    END$$
DELIMITER ;

视图

CREATE
    VIEW `图书租售管理`.`customer_view` 
    AS
SELECT customer.`name`,customer.`cus_id`,book.`name` AS book_name,book.`book_id`,purchase.`mode`,purchase.`number`,book.`rent_price`,book.`sale_price`
FROM book INNER JOIN purchase ON book.`book_id`=purchase.`book_id` INNER JOIN customer ON purchase.`cus_id`=customer.`cus_id`;

/*增加了排序*/
DELIMITER $$

ALTER ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `customer_view` AS 
SELECT
  `customer`.`name`   AS `name`,
  `customer`.`cus_id` AS `cus_id`,
  `book`.`name`       AS `book_name`,
  `book`.`book_id`    AS `book_id`,
  `purchase`.`mode`   AS `mode`,
  `purchase`.`number` AS `number`,
  `book`.`rent_price` AS `rent_price`,
  `book`.`sale_price` AS `sale_price`
FROM ((`book`
    JOIN `purchase`
      ON ((`book`.`book_id` = `purchase`.`book_id`)))
   JOIN `customer`
     ON ((`purchase`.`cus_id` = `customer`.`cus_id`)))
ORDER BY `purchase`.`number` ASC;$$
DELIMITER ;

/*视图调用*/
     SELECT *FROM customer_view

综合练习

学生选课数据库中表结构如下：

学生（学号，姓名，年龄，性别，所在系）；

课程（课程号，课程名，学分，先行课）；

选课（学号，课程号，成绩）；

①求选修了高等数学的学生学号和姓名

SELECT 学号,姓名

FROM 学生

WHERE 学号 IN

           (SELECT 学号

             FROM 选课

             WHERE 课程号 IN

                        (SELECT 课程号

                         FROM 课程

                          WHERE 课程名='高等数学'));

②求张三的选修成绩在90~95之间的课程名

SELECT 课程名

FROM 课程

WHERE 课程号 IN
            (SELECT 课程号

             FROM 选课

             WHERE 成绩 between 90 and 95 AND 学号 IN

                                           (SELECT 学号
    
                                            FROM 学生

                                            WHERE 姓名='张三' ))

③ 求其他系中比管理系某学生年龄小的学生信息。

SELECT *

FROM Student

WHERE 所在系<>'管理系' AND 年龄<(SELECT MAX (年龄)

FROM Student

WHERE 所在系='管理系')

④求没有选修C2课程的学生姓名。

SELECT 姓名

FROM 学生

WhERE NOT EXISTS(SELECT *

                 FROM 选课

                 WHERE 学生.学号=选课.学号 AND 课程号='C2')

⑤查询每个系的学生人数

SELECT 所在系,COUNT(学号)

FROM 学生

GROUP BY 所在系

⑥查询选修课程超过3门的学生学号和选课门数，要求只统计成绩及格的情况。

SELECT 学号,COUNT(*) as 选课门数

FROM 选课

WHERE 成绩>=60

GROUP BY 学号

HAVING COUNT(*)>3

⑦查询学生的选修情况，即学号、选课门数，并进行选修情况统计

SELECT 学号,COUNT(*) as 选课门数

FROM 选课

GROUP BY 学号 WITH CUBE

⑧将计算机系学生的选课成绩提高10分

UPDATE 选课

SET 成绩=成绩+10

WHERE 学号 IN

           (SELECT 学号

            FROM 学生

            WHERE 所在系='计算机')

⑨删除成绩不及格的选课信息

DELETE 选课

FROM 选课

WHERE 成绩<60

⑩将学生表的查询、修改权限赋予用户user1

GRANT SELECT,UPDATE ON 学生

TO user1

teree_

关注

3
点赞
踩
18

收藏

觉得还不错? 一键收藏
0
评论
数据库语句练习题

文章目录一、简单查询和连接查询学生选课数据库查询练习职工部门数据库查询练习二、嵌套查询练习学生选课数据库练习高校图书借阅数据库三、组合和统计查询图书借阅数据库查询一、简单查询和连接查询学生选课数据库查询练习Student(Sno,Sname,Sage,Ssex,Ssdept) ；Course(Cno,Cname,Ccredit,Cpno)；SC(Sno,Cno,Grade)；职工部门数据库查询练习二、嵌套查询练习学生选课数据库练习Student(Sno,S
复制链接

扫一扫

专栏目录