MySQL学习（四）

来源：互联网发布：js === 编辑：程序博客网时间：2024/06/06 13:22

MySQL学习（四）

本MySQL学习系列博客共七篇，此为第四篇。

目录：

1、MySQL索引类型

2、MySQL的select注意事项：

3、MySQL有无check约束？

4、若视图定义中含有group by字句，则此视图不允许更新。

5、实现内容：

6、源代码示例：

7、运行结果：

1、MySQL索引类型包括以下三种：
答：①Normal：表示普通索引
②Unique：表示唯一的，不允许重复的索引
③FullText：表示全文搜索的索引，FullText用于搜索很长一篇文章的时候，效果最好

2、MySQL的select注意事项：

答：在Mysql中不能先select出同一表中的某些值，再进行数据更新这个表在同一语句中，解决办法为先建立一个临时的表然后执行完更新操作后再删除这个临时的表。

3、MySQL有无check约束？

答：Mysql中check约束是无效的。需要用到触发器实现。

4、若视图定义中含有group by字句，则此视图不允许更新。

5、实现内容：

图5.1 实现内容

6、源代码示例：

/*一、创建索引*//*1、为学生成绩表在建立学号和课程编号上的唯一索引唯一索引指的是此处的每一个索引值只对应唯一的数据记录*/create unique index stu_sco_indexon v_student_score(student_id,course_id);/*2、运行结果可通过执行系统的存储过程查看：Sp_helpindex 表名*//*Sql Server运行代码*/Sp_helpindex v_student_score;/*Mysql运行代码*/show indexfrom v_student_score;/*3、为教材选用表在数量上建立聚集索引聚簇索引指索引项的顺序与表中记录的物理顺序一致*//*Sql server运行代码*//*①默认情况下,主键是聚集索引。先删除系统默认创建的索引PK__v_textbook_selec__2A4B4B5E*/alter table v_textbook_selectiondrop constraint PK__v_textbook_selec__2A4B4B5E;/*②添加主键，将表中规定主键修改为非聚集类型*/alter table v_textbook_selectionadd constraint pk_course_textbookprimary key nonclustered(course_id, textbook_id) ;/*③在数量列上创建此聚集索引*/create clustered index textbook_selection_indexon v_textbook_selection(textbook_selection_number);/*④查看索引,查看到一条是sql server默认给主键设置的索引，另一条便是第③步创建的聚集索引*/Sp_helpindex v_textbook_selection;/*Mysql运行代码*//*①为Mysql建立一个普通索引*/create index textbook_selection_index on v_textbook_selection(textbook_selection_number);/*②查看索引*/show index from v_textbook_selection;/*4、删除教材选用表在数量上的索引*//*Sql Server运行代码*/drop index textbook_selection_index on v_textbook_selection;Sp_helpindex v_textbook_selection;/*Mysql运行代码*/alter table v_textbook_selectiondrop index textbook_selection_index;show indexfrom v_textbook_selection;/*二、更新和删除数据*//*1、更新学生成绩表，将选择数据库的同学成绩都加上2分*//*①先查看学生成绩表中的信息*/select * from v_student_score;/*②因为设置的check语句中规定了分数为0到100之间的浮点数，故不能直接加2，否则update会与check约束发生冲突*/update v_student_scoreset grade = grade + 2where grade <= 98;/*③再次查看学生成绩表中的数据，发现已更新*/select * from v_student_score;/*2、更新教材表中《教材十一》的出版时间为今天*//*①先查看教材表中的信息*/select * from v_textbook;/*②修改教材十一的出版时间为今天*//*Sql server运行代码*/update v_textbookset publish_date = getdate()where textbook_name = '教材十一';/*Mysql运行代码*/update v_textbookset publish_date = curdate()where textbook_name = '教材十一';/*③再次查看教材表中的信息*/select * from v_textbook;/*3、删除没有被选中的教材的信息*//*Sql Server代码*/delete from v_textbookwhere textbook_id not in(select v_textbook.textbook_idfrom v_textbook_selection, v_textbookwhere v_textbook_selection.textbook_id = v_textbook.textbook_id);/*Mysql代码*//*①先创建一个临时表*/create table temp as(select v_textbook.textbook_idfrom v_textbook_selection, v_textbookwhere v_textbook_selection.textbook_id = v_textbook.textbook_id );/*②在v_textbook中执行删除操作*/delete from v_textbookwhere textbook_id not in(select *from temp);/*③删除临时表*/drop table temp;/*4、根据学生选课人数修改教材选用的数量，数量为学生人数+2*//*Sql Server执行代码*/update v_textbook_selectionset v_textbook_selection.textbook_selection_number = t.number + 2from v_textbook_selection, (select v_student_score.course_id, count(v_student_score.course_id) as numberfrom v_student_scoregroup by(v_student_score.course_id))as twhere t.course_id = v_textbook_selection.course_id;select * from v_textbook_selection; /*Mysql执行代码*/update v_textbook_selectioninner join (select v_student_score.course_id, count(v_student_score.course_id) as numberfrom v_student_scoregroup by(v_student_score.course_id))as t on t.course_id = v_textbook_selection.course_idset v_textbook_selection.textbook_selection_number = t.number + 2;select * from v_textbook_selection;/*三、修改表结构、约束*//*1、在学生表中增加一个班号列和住址列*/alter table v_studentadd student_class varchar(20);alter table v_studentadd student_address varchar(50);select *from v_student;/*2、为教材表的出版社属性值加非空约束*//*Sql Server运行代码*/alter table v_textbookalter column press varchar(50) not null;/*Mysql运行代码*/alter table v_textbookmodify press varchar(50) not null;/*3、如果课室表中容纳人数为整型，修改为字符类型，如果为字符类型，修改为整型*//*①首先*查询表结构，查询结果为int类型*//*Sql Server运行代码*/Sp_help v_classroom;/*Mysql运行代码*/desc v_classroom;/*②修改类型*//*Sql Server运行代码*/alter table v_classroomalter column classroom_capacity varchar(20);/*Mysql运行代码*/alter table v_classroommodify classroom_capacity varchar(20);/*4、修改成绩取值范围的约束，改为0-150之间*//*Sql Server运行代码*/alter table v_student_scoredrop constraint CK__v_student__grade__08EA5793;  alter table v_student_scoreadd constraint CK_gradecheck(grade <= 150 and grade >=0);/*Mysql运行代码*//*需要用到触发器*//*四、创建视图*//*1、创建一个包含学生信息、课程信息，以及选课信息的视图，包括以下属性：学号，姓名，性别，年龄，系别，课程号，课程名，成绩*/create view STUDENT_INFOasselect v_student.student_id, student_name, student_sex, 2016 - year(student_birthday) as student_age, department_name, v_student_score.course_id, course_name, gradefrom v_student, v_department, v_student_score, v_coursewhere v_student.student_id = v_student_score.student_idand v_student.department_id = v_department.department_idand v_student_score.course_id = v_course.course_idwith check option;/*2、查询视图的全部元组*/select * from STUDENT_INFO;/*3、创建一院系学生平均成绩视图S_AVG，包括院系名称和平均成绩*//*Sql Server运行代码*/create view S_AVGasselect v_department.department_name, department_avg_grade from v_department, (select v_student.department_id, avg(grade) as department_avg_gradefrom v_department, v_student, v_student_scorewhere v_student.student_id = v_student_score.student_idand v_student.department_id = v_department.department_idgroup by (v_student.department_id)) twhere t.department_id = v_department.department_id;/*Mysql运行代码*/create view S_AVGasselect v_department.department_name, avg(grade) as department_avg_gradefrom v_department, v_student, v_student_scorewhere v_student.student_id = v_student_score.student_idand v_student.department_id = v_department.department_idgroup by (v_student.department_id);/*4、查看视图的全部元组*/select * from S_AVG;/*5、修改视图定义，增加一个当前年份属性，并给出修改前后的视图执行结果截图*//*Sql Server运行代码*/alter view S_AVGASselect v_department.department_name, department_avg_grade, datepart(yyyy, getdate()) as current_yearfrom v_department, (select v_student.department_id, avg(grade) as department_avg_gradefrom v_department, v_student, v_student_scorewhere v_student.student_id = v_student_score.student_idand v_student.department_id = v_department.department_idgroup by (v_student.department_id)) twhere t.department_id = v_department.department_id;/*Mysql运行代码*/alter view S_AVGASselect v_department.department_name, avg(grade) as department_avg_grade, date_format(curdate(), '%Y') as current_yearfrom v_department, v_student, v_student_scorewhere v_student.student_id = v_student_score.student_idand v_student.department_id = v_department.department_idgroup by v_student.department_id;select * from S_AVG;

7、运行结果：

答：一、创建索引

（1）为学生成绩表在建立学号和课程编号上的唯一索引

（2）运行结果可通过执行系统的存储过程查看：Sp_helpindex 表名