数据库系统管理个人练习题

个人练习测试题

一、登陆并创建数据库

        mysql -u root -p

        show databases;

        create database TakeCourse;  //创建练习库

        show databases; 

        select database();  //查看当前库

        use TakeCourse;

二、创建表和数据

  1、创建测试表students

  mysql> CREATE TABLE STUDENTS(
    -> sid char(10) NOT NULL,
    -> sname char(30) NOT NULL,
    -> email char(30) NOT NULL,
    -> grade int NULL,
    -> PRIMARY KEY (SID));
Query OK, 0 rows affected (0.03 sec)  //创建成功

插入测试数据：        

INSERT INTO   STUDENTS ( sid , sname , email, grade ) VALUES ('1201 ', 'tom ', 'tom@qhldj.gov', 2022);

INSERT INTO   STUDENTS ( sid , sname , email , grade ) VALUES ('1205 ', 'jerry ', 'jerry@zqur.org', 2021);

INSERT INTO   STUDENTS ( sid , sname , email, grade ) VALUES ('1207 ', 'terry ', 'terry@qhldj.gov', 2022);

INSERT INTO   STUDENTS ( sid , sname , email , grade ) VALUES ('1215 ', 'anna ', 'anna@zqur.org', 2021);

2、创建测试表courses

mysql> CREATE TABLE COURSES(
    -> cid char(10) NOT NULL,
    -> cname char(30) NOT NULL,
    -> hour int NULL,
    -> PRIMARY KEY (cid));
Query OK, 0 rows affected (0.02 sec) //创建成功

插入测试数据：

INSERT INTO   COURSES ( cid , cname , hour ) VALUES ( '10001', 'database ', 96);

INSERT INTO   COURSES ( cid , cname , hour ) VALUES ( '10002', 'operating system ', 88);

INSERT INTO   COURSES ( cid , cname , hour ) VALUES ( '10003', 'computer graphics ', 48);

INSERT INTO   COURSES ( cid , cname , hour ) VALUES ( '10004', 'java ', 48);

INSERT INTO   COURSES ( cid , cname , hour ) VALUES ( '10005', 'c++ ', 60);

INSERT INTO   COURSES ( cid , cname , hour ) VALUES ( '10006', 'design pattern ', 48);

3、创建测试表choices

mysql> CREATE TABLE CHOICES(
    -> no int NOT NULL,
    -> sid char(10) NOT NULL,
    -> cid char(10) NOT NULL,
    -> Takedate date NOT NULL,
    -> score int NULL,
    -> PRIMARY KEY (no),
    -> FOREIGN KEY(sid) references STUDENTS(sid),
    -> FOREIGN KEY(cid) references COURSES(cid));

//注意该表与另两张表有外键关联

插入测试数据： //可以分批次进行数据插入，避免插入失败

INSERT INTO   CHOICES( no ,sid,cid ,takedate,score ) VALUES (253,'1205' ,'10006','2021-3-1',87);

INSERT INTO   CHOICES( no ,sid,cid ,takedate,score ) VALUES (984,'1205' ,'10001','2022-9-1', 54);

INSERT INTO   CHOICES( no ,sid,cid ,takedate,score ) VALUES (1270,'1201' ,'10005','2021-3-1', 92);

INSERT INTO   CHOICES( no ,sid,cid ,takedate,score ) VALUES (2143,'1205' ,'10005', '2022-9-1',82);

INSERT INTO   CHOICES( no ,sid,cid ,takedate,score ) VALUES (2371,'1201' ,'10002', '2022-9-1',77);

INSERT INTO   CHOICES( no ,sid,cid ,takedate,score ) VALUES (2715,'1207' ,'10001','2022-9-1', 67);

INSERT INTO   CHOICES( no ,sid,cid ,takedate,score ) VALUES (3262,'1207' ,'10005', '2022-9-1',90);

INSERT INTO   CHOICES( no ,sid,cid ,takedate,score ) VALUES (4144,'1207' ,'10004', '2022-9-1',60);

INSERT INTO   CHOICES( no ,sid,cid ,takedate,score ) VALUES (5519,'1205' ,'10002', '2021-3-1',NULL);

INSERT INTO   CHOICES( no ,sid,cid ,takedate,score ) VALUES (5935,'1215' ,'10001', '2022-9-1',70);

INSERT INTO   CHOICES( no ,sid,cid ,takedate,score ) VALUES (5987,'1201' ,'10004', '2021-3-1',82);

INSERT INTO   CHOICES( no ,sid,cid ,takedate,score ) VALUES (6353,'1205' ,'10006', '2022-9-1',76);

INSERT INTO   CHOICES( no ,sid,cid ,takedate,score ) VALUES (6805,'1201' ,'10003', '2021-9-1',88);

INSERT INTO   CHOICES( no ,sid,cid ,takedate,score ) VALUES (6824,'1205' ,'10004', '2022-9-1',72);

INSERT INTO   CHOICES( no ,sid,cid ,takedate,score ) VALUES (7004,'1207' ,'10002', '2022-9-1',64);

INSERT INTO   CHOICES( no ,sid,cid ,takedate,score ) VALUES (7901,'1215' ,'10002', '2021-9-1',75);

INSERT INTO   CHOICES( no ,sid,cid ,takedate,score ) VALUES (8311,'1215' ,'10006', '2022-9-1',63);

show tables;

三、实验题用select命令实现简单查询

3.1、查看三个新建表的内容：

mysql> select * from students; //学生表

mysql> select * from courses; //课程表

mysql> select * from choices;  //选课表

3.2、统计课程表中课程的门数

mysql> select count(*) from courses;

3.3、查找课程名中包含data的课程

mysql> select * from courses where cname like '%data%';

3.4、显示课程表的信息，按照学时数升序排列

mysql> select * from courses order by hour asc;

3.5、按照每门课的考试分数由低到高来显示所有选修的信息

mysql> select * from choices order by cid,score asc ;

3.6、按照所有课程的考试分数由低到高来显示选修的信息

mysql> select * from choices order by score asc;

3.7、显示选课表中分数为空的记录

mysql> select * from choices where score is NULL;

3.8、统计选了‘10006’课程的学生人数

mysql> select * from choices where score is NULL;

3.9、统计每一门课程选修的人数和最低成绩

mysql> select count(cid),min(score) from choices group by cid;

3.10、列出每门课程的课程名，以及选修的人数和最低成绩

mysql> select choices.cid,courses.cname,count(*),min(score) from choices natural join courses group by choices.cid,courses.cname;

3.11、统计每个人选修的课程数量

mysql> select sid,count(*) from choices group by sid;

3.12、所有课程都及格的学生号

mysql> select sid from choices group by sid having min(score) >= '60';

四、实验用select命令实现连接查询

目的：

        掌握基于多表的连接查询，包括自然连接：natural join，内连接：inner join，外连接：左left outer join，右right outer join，全连接full outer join

4.1、请显示出tom同学选修的课程的名字和学分

mysql> select courses.cname,courses.hour from courses natural join students where students.sname = 'tom';

4.2、插入一个学生的信息（'1219','leke','leke@qq.com',2024）

mysql> insert into students values('1219','leke','leke@qq.com',2024);

mysql> select * from students;

4.3、请显示出没有选课的学生的姓名

mysql> select * from students left outer join choices on choices.sid = students.sid where choices.cid is NULL;

4.4、请列出2022级选了‘java’课程的学生姓名

mysql> select students.sname from courses natural join choices natural join students where students.grade = 2022 and courses.cname = 'java';

4.5、请列出各年级学生的修课总数

mysql> select students.grade,count(*) from students natural join choices group by students.grade;

4.6、请列出各年级学生‘c++’ 的最高分和最低分

mysql> select students.grade,max(score),min(score) from students natural join courses natural join choices where courses.cname = 'c++' group by students.grade;

4.7、列出各门课程各个年级选修的人数

mysql> select courses.cid,courses.cname,students.grade,count(*) from courses natural join choices natural join students group by courses.cid,courses.cname,students.grade;

4.8、对各门课程按照年级的升序列出选修的情况

mysql> select * from students natural join choices order by cid,grade asc;

五、实验用select命令实现嵌套查询

目的：使用嵌套查询实现多表查询，使用嵌套查询实现减法运算

5.1、请用嵌套查询选修了c++的学生姓名

mysql> select sname from students where sid in (select sid from choices where cid in (select cid from courses where cname = 'c++'));

5.2、请列出2021级选了‘java’课程的学生姓名

mysql> select sname from students where grade = 2022 and sid in (select sid from choices where cid in ( select cid from courses where cname = 'java' ));

5.3、请用嵌套查询找出没有选修courses的学生学号和姓名（差运算）

差运算：指从第一个集合中减去与第二个集合的交集部分，得到的结果集，可使用 NOT EXISTS或 left outer join 

mysql> select students.sid,students.sname from students left outer join choices on students.sid = choices.sid where choices.cid is NULL;

mysql> select sid,sname from students where sid not in(select sid from choices);

5.4、请用嵌套查询找出选修了c++和java 课程的学生学号和姓名（交运算）

交运算：是指取两个集合的共有部分，可使用INNER JOIN 或  EXISTS

mysql> select students.sid,students.sname from students natural join choices natural join courses where courses.cname = 'c++' and students.sid in (select students.sid from students natural join choices natural join courses where courses.cname = 'java');

mysql>  select students.sid,students.sname from students inner join choices on students.sid = choices.sid inner join courses on choices.cid = courses.cid where courses.cname = 'java' and students.sid in (select students.sid from students inner join choices on students.sid = choices.sid inner join courses on choices.cid = courses.cid where courses.cname = 'c++');  //inner join

5.5、请用嵌套查询找出选修了operating system没有选修c++课程的学生学号和姓名（交运算）

mysql> select students.sid,students.sname from students natural join choices natural join courses where courses.cname = 'operating system' and students.sid not  in (select sid from choices natural join courses where courses.cname = 'c++');

mysql> select students.sid,students.sname from students natural join choices natural join courses where courses.cname = 'operating system' and students.sid not  in (select students.sid from students natural join choices natural join courses where courses.cname = 'c++');

mysql> select students.sid,students.sname from students inner join choices on students.sid = choices.sid inner join courses on choices.cid = courses.cid where courses.cname = 'operating system' and students.sid not in (select students.sid from students inner join choices on students.sid = choices.sid inner join courses on choices.cid = courses.cid where courses.cname = 'c++');

六、实验用update命令修改数据

目的：掌握update命令

6.1、将所有学生表中年级+1

mysql> update students set grade = grade+1 ;

6.2、把学号‘1201’的学生的年级改为2022

mysql> update students set grade = '2022' where sid = '1201';

6.3、把姓名为Tom的学生的operating system课的成绩改为90分

mysql> update choices set score = '90' where cid in (select cid from courses where cname = 'operating system' and sid in (select sid from students where sname = 'tom'));

七、实验用delete命令语句删除数据

目的：掌握delete删除命令

7.1、删除courses中database的课程信息

测试时，先做7.2的删除操作后在做本题

mysql> delete from courses where cname = 'database';

7.2、删除choices中所有database课程的选修信息

测试时，先做这个删除在做7.1的删除操作

mysql> delete from choices where cid in (select cid from courses where cname = 'database'); 

八、实验用户及安全

目的：掌握新建用户的命令和使用方法，掌握如何使用grant命令给用户授权

8.1、创建用户TeacherB，密码 abc123，然后授权查询权限

//创建用户TeacherB，'TeacherB'@'%'表示可以被远程访问
//如果是‘TeacherB’@'localhost'，表示只能本地访问
//mysql> create user 'TeacherB'@'%' identified by '111111';

//为用户test授予对数据库db_one 的操作权限
//mysql> grant all privileges on db_one to 'TeacherB'@'%';

//刷新权限
//mysql> flush privileges;

mysql> create user TeacherB identified by 'abc123';

将表students上select权限赋给用户TeacherB

mysql> grant select on takecourse.students to TeacherB;

8.2、以用户TeacherB的身份进行登陆数据库tabkecourse，查询表students 和表choices、表courses，看看是否有相应权限

C:\Windows\System32>mysql -uTeacherB -p
Enter password: ******

mysql> select * from takecourse.students;

mysql> select * from takecourse.choinces;

mysql> select * from takecourse.courses;

上述操作结果可以看到：

可以登录数据库和查询takecourse.students表，不可以查询choices表和courses表

--实验过程中出现创建用户正常，但无法登陆的情况，可查看user表是否有空用户，如有delete删除后在进行连接测试。