mysql作业练习-CSDN博客

本文链接：https://blog.csdn.net/qq_53046794/article/details/130037074

数据库索引
索引概念
是帮助mysql高校获取数据的数据结构，其存储着数据库中所有数据的应用指针
可以简单理解为：排好序的快速查找的数据结构。
在Mysql 中索引也叫做‘’键“
索引的作用
目的在于提高查询效率。
索引的原理
索引的目的在于提高查询效率，本质是通过不断的缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方法来锁定数据。

索引的分类
单值索引：即一个索引只包含单个列，一个表可以有多个单列索引。【推荐不超过5个】
唯一索引：索引列的值必须唯一，但允许有空值
复合索引：即一个索引包含多个列

普通索引和唯一索引普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。唯一索引要求索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值。

单列索引和组合索引单列索引即一个索引只包含单个列，一个表可以有多个单列索引。组合索引是指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合。【组合索引上来讲的】

全文索引全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、 VARCHAR或者TEXT类型的列上创建。MySQL中只有MyISAM存储引擎支持全文索引。

空间索引空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和POLYGON。 MySQL使用SPATIAL关键字进行扩展，

索引的设计原则
（1）索引并非越多越好，一个表中如有大量的索引，不仅占用磁盘空间，还会影响INSERT、DELETE、UPDATE等语句的性能，因为在表中的数据更改时，索引也会进行调整和更新。

（2）避免对经常更新的表进行过多的索引，并且索引中的列要尽可能少。应该经常用于查询的字段创建索引，但要避免添加不必要的字段。

（3）数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

（4）在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值，因此就无须建立索引。

（5）当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度。

（6）在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

索引的优缺点
优点
通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
可以大大加快数据的查询速度，这也是创建索引的主要原因。
在实现数据的参考完整性方面，可以加速表和表之间的连接。
在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间
缺点
创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

-1. 根据编号查询名字【传入编号，获取名字】 
 delimiter$$
	 CREATE PROCEDURE demo01(v_id INT , OUT v_name VARCHAR(50))
	   BEGIN
		SELECT vipname into v_name FROM vip WHERE vipid = v_id; 
       END$$
 delimiter ;
 CALL demo01(1,@vname);
 SELECT @vname;
 
 
 
 --2. 根据编号查询vip的名字(传入编号，获取名字和性别) 
  delimiter$$
	CREATE PROCEDURE demo02(v_id INT , OUT v_name VARCHAR(50),OUT v_sex VARCHAR(50))
		BEGIN
		     SELECT vipname,vipsex into v_name,v_sex FROM vip WHERE vipid = v_id; 
        END$$  
 delimiter ;
 CALL demo02(1,@vname,@vsex);
 SELECT @vname,@vsex; 



 --3. 创建过程，根据输入的成绩编号【pk】，获取该学号，科目号以及成绩 
delimiter$$
	CREATE PROCEDURE demo03(mid INT , OUT sno VARCHAR(50),OUT sid INT,OUT sresult INT)
		BEGIN
			 SELECT StudentNo,SubjectId,StudentResult INTO sno,sid,sresult FROM result WHERE Id = mid;
        END$$
delimiter ;
CALL demo03(1,@sno,@sid,@sresult);
SELECT  @sno,@sid,@sresult;


--4. 创建过程，根据输入的成绩编号【pk】，获取该学生名字，科目名号以及成绩 
delimiter$$
	CREATE PROCEDURE demo04(mid INT , OUT sname VARCHAR(50),OUT subname VARCHAR(50),OUT sresult INT)
		BEGIN
			DECLARE sid int;
			DECLARE sno VARCHAR(50);
			SELECT SubjectId,StudentNo,StudentResult INTO sid,sno,sresult FROM result WHERE Id = mid;
			SELECT SubjectName INTO subname FROM subjects WHERE SubjectId = sid;
			SELECT StudentName INTO sname FROM student WHERE StudentNo = sno;
		END$$
delimiter ;
 CALL demo04(1,@sname,@subname,@sresult);
 SELECT  @sname,@subname,@sresult;
 
 
 --5. 输入员工号，查找员工名字，上级领导名字 以及 工资 
delimiter$$
	CREATE PROCEDURE demo05(eid INT , OUT ename VARCHAR(50),OUT mname VARCHAR(50),OUT esalary INT)
		BEGIN
			DECLARE mid int;
			SELECT CONCAT(first_name,'-',last_name),manager_id,salary INTO ename,mid,esalary FROM employees WHERE employee_id  = eid;
			SELECT CONCAT(first_name,'-',last_name)  INTO mname FROM employees WHERE employee_id  = mid;
 		END$$
delimiter ;

 CALL demo05(105,@ename,@mname,@esalary);
 SELECT  @ename,@mname,@esalary;

1. 使用存储函数|过程 传入学号，获取学生的姓名，总分，平均分
delimiter$$
	CREATE FUNCTION fun1(sno VARCHAR(50))
	    BEGIN
			SELECT StudentName INTO sname FROM student WHERE StudentNo = sno;
			SELECT SUM(StudentResult),AVG(StudentResult) INTO sumscore, avgscore FROM result WHERE StudentNo = sno GROUP BY StudentNo;
			RETURN CONCAT(sname,'-',sumscore,'-',avgscore);
		 END$$
 delimiter ;
 
 SELECT fun1('s1001');
 
-- 2. 输入，科目名字，学时，以及gradeid ，将该数据插入到数据库
delimiter$$
	create function fun2(`_subjectname` varchar(50),`_classhour` int ,`_gradeid` int) returns  VARCHAR(50)
		BEGIN
			declare res varchar(50) ;
			declare gradeidcount INT ;
			declare subjectscount INT ;
			select count(gradeid) into gradeidcount from grade where gradeid = _gradeid ;
			set res ='插入失败' ;
			if gradeidcount = 1 then 
			  set res = '插入成功';
				select count(*) into subjectscount from subjects  ;
				insert into subjects values(subjectscount+1,_subjectname,_classhour,_gradeid);
			end if ;
			return res ;
		END$$
delimiter ;

drop FUNCTION fun2

select fun2('语文',48,1)
-- 3. 根据传入的科目编号删除科目信息，如果该科目以及被考过试，则不能删除
 delimiter$$
	create function fun3(_subjectid int ) returns VARCHAR(50) 
	begin 
		declare subjectcount int ;
		declare res varchar(50) ;
		set res = '删除失败';
		select count(subjectid) into subjectcount from subjects where subjectid = _subjectid ;
		if subjectcount = 1 then 
			delete from subjects where subjectid = _subjectid ;
			set res = '删除成功';
		end if ;
		return res ;
	END$$
delimiter ;

select fun3(100);