数据库实训

目录

实训1 数据库定义 1
实训2 select单表查询 4
实训3 多表查询 6
实训4 记录与视图 11
实训5 用户权限管理 16
实训6 数据库完整性 18
实训7 函数与存储过程 21
实训8 事务与备份 26

实训1 数据库定义

一、 数据库基本操作
1.查看MySQL的数据库
Show databases;
2.创建stu数据库
Create database stu;
3.查看stu数据库
Show create database stu;
4.修改stu数据库的编码方式，将数据库的编码方式改为gbk或者utf8。
alter database 数据库名 character set utf8;
5.删除stu数据库
Drop database stu;
二、表基本操作

1. 创建一个用于存储学生信息的表student1，表结构如下所示。
   student1表结构
   

2． 创建一个用于存储课程信息的表course1，表结构如下所示。
course1表结构

3．创建一个用于成绩的表score1，表结构如下所示。
score1表结构

实训2 select单表查询

一、导入stu数据库：数据库—导入—执行SQL
二、在stu数据库中完成下列操作
1.查询student表中的学生学号，姓名，家庭住址三个字段信息
SELECT sid,sname,addr FROM student;
2.查询输出score表中的所有选修过课程的学生学号（不得重复）
SELECT DISTINCT sid FROM score WHERE result IS NOT NULL;
3.查询输出student表中姓名，年龄（年龄=系统年份-出生年份）
SELECT sname,YEAR(NOW()) - YEAR(birth) AS age FROM student;
4.显示学号、姓名、填表日期，其中日期为当前日期
SELECT sid,sname,NOW() FROM student;
5.查询01号课程成绩在80-90分之间的成绩信息
SELECT result FROM score WHERE (result BETWEEN 80 AND 90) AND cno = ‘01’;
6.日期比较：查询‘2000-9-01’以后出生的学生信息
SELECT * FROM student WHERE birth > ‘2000-9-01’;
7.查询“信息工程系”和“化学工程系”的学生的基本信息
SELECT * FROM student WHERE department IN(‘信息工程系’,‘化学工程系’);
8.查询“信息工程系”和“化学工程系”的女生的基本信息
SELECT * FROM student WHERE department IN(‘信息工程系’,‘化学工程系’) AND sex = ‘女’
9.查询所有学生的总分，平均分，最高分和最低分
SELECT sid,SUM(result) AS 总分,AVG(result) AS 平均分,MAX(result) AS 最高分,MIN(result) AS 最低分 FROM score GROUP BY sid;
10.查询“02”科目的总分，平均分，最高分和最低分
SELECT cno,SUM(result) AS 总分,AVG(result) AS 平均分,MAX(result) AS 最高分,MIN(result) AS 最低分 FROM score WHERE cno = ‘02’;
11.查询学号为“0101”的总分，平均分，最高分和最低分
SELECT sid,SUM(result) AS 总分,AVG(result) AS 平均分,MAX(result) AS 最高分,MIN(result) AS 最低分 FROM score WHERE sid = ‘0101’;
12.查询输出每个学生的学号、姓名、所在系，按所在系升序排列
SELECT sid,sname,department FROM student ORDER BY department ASC;
13.查询所有学生的姓名和年龄，以列名 “姓名、年龄”输出，并按照年龄从小到大输出
SELECT sname AS ‘姓名’,(YEAR(NOW())- YEAR(birth)) AS ‘年龄’ FROM student ORDER BY birth DESC;
14.查询所有学生的学号，姓名和性别，并按性别升序，姓名降序排序。
SELECT sid,sname,sex FROM student ORDER BY sex ASC,sname DESC;
15.查询出score表中选修“03”课程的学号和成绩，并按照成绩的降序排列
SELECT sid,result FROM score WHERE cno = ‘03’ ORDER BY result DESC;
16.查询student表中的学生人数
SELECT COUNT(sid) FROM student;
17.按性别统计男女生人数
SELECT sex,COUNT() FROM student GROUP BY sex;
18查询统计系部人数，显示各系人数在3人以上的系部名称和人数
SELECT department,COUNT() FROM student GROUP BY department HAVING COUNT(*) > 3;
19.统计每门课的总分，平均分，最高分和最低分
SELECT cno,SUM(result) AS 总分,AVG(result) AS 平均分,MAX(result) AS 最高分,MIN(result) AS 最低分 FROM score GROUP BY cno;
20.统计“01”、“02”、“03”三科成绩的平均分、最高分、最低分
SELECT cno,AVG(result) AS 平均分,MAX(result) AS 最高分,MIN(result) AS 最低分 FROM score WHERE cno IN (‘01’,‘02’,‘03’) GROUP BY cno;
21.查询课程平均分在80分以上的课程的总分，平均分
SELECT cno,SUM(result) AS 总分,AVG(result) AS 平均分 FROM score GROUP BY cno HAVING AVG(result) >= 80;
22．统计每个学生的总分，平均分，最高分和最低分
SELECT sid,SUM(result) AS 总分,AVG(result) AS 平均分,MAX(result) AS 最高分,MIN(result) AS 最低分 FROM score GROUP BY sid;
23.查询出“03”成绩前3名的成绩信息
SELECT * FROM score WHERE cno = ‘03’ ORDER BY result DESC LIMIT 0,3;
24.查询出‘02’号课程第2、3名成绩信息
SELECT * FROM score WHERE cno = ‘02’ ORDER BY result DESC LIMIT 1,2;
25.查询出score表中成绩前三名的学生学号
SELECT sid FROM score ORDER BY result DESC LIMIT 0,3;
26.查询出score表中成绩最后三名的学生学号
SELECT sid FROM score ORDER BY result ASC LIMIT 0,3;

实训3 多表查询

一、导入数据库：Student.sql
class—班级号—student—学号----score----课程号—course
二、完成下列查询（where）
1.查找并输出全体学生的姓名、课程号和成绩。（有成绩的）
SELECT 姓名,课程号,成绩
FROM student,score
WHERE student.学号=score.学号
2.查找并输出全体学生的姓名、课程名和成绩。（有成绩的）
student—学号----score----课程号—course
SELECT 姓名,课程名,成绩
FROM student a,score b,course c
WHERE a.学号=b.学号 AND b.课程号=c.课程号
3.查找班级名为“计算机应用001班”的学生姓名、课程名和成绩
class—班级号—student—学号----score----课程号—course
SELECT 姓名,课程名,成绩
FROM student,class,course,score
WHERE class.班级号=student.班级号
AND student.学号=score.学号
AND score.课程号=course.课程号
AND 班级名=‘计算机应用001班’
4.查找班级名为“计算机应用001班”的学生学号、姓名、性别和电话号码。
SELECT 学号,姓名,性别,电话号码
FROM student,class
WHERE class.班级号=student.班级号 AND 班级名=‘计算机应用001班’
5.列出所有选修 ‘大学英语’课程的学生姓名、课程号和成绩
SELECT 姓名,c.课程号,成绩
FROM student a,score b,course c
WHERE a.学号=b.学号 AND 课程名=‘大学英语’
6.查询输出第1学期所有课程的成绩
SELECT 课程名,成绩,开课学期
FROM course,score
WHERE course.课程号=score.课程号 AND 开课学期=‘1’