肖sir__mysql之综合题练习__013

数据库考试
姓名________
解答题 （15*2）
1 Drop, delete，truncate 三者的区别？

A.执行速度：drop > truncate > detele
B.delete 和 truncate 只删除表数据，而 drop 会删除表数据和表结构以及表的索引、约束和触发器
C.delete 可以加 where 条件实现部分数据删除，而 truncate 和 drop 不能加 where 条件是整体删除
D.truncate 和 drop 是立即执行，且不能恢复；而 delete 会走事务，可以撤回和恢复
E.truncate 会重置自增列为 1，而 delete 不会重置自增列
F.truncate 和 drop 是 DDL 语句，而 delete 是 DML 语句
Drop>truncates>delete 删除数据速度

2 linux 中如何备份一个数据库和还原一个数据库，写出具体的命令？
备份：mysqldump -uroot -p 存在库>新建数据库文件.sql
还原：新建一个库，mysql -u root -p 新建库名<存在库.sql

3 数据库中库的类型，表的类型，数据的类型分别是指什么？

a.库：关系数据库与非关系数据库
b. myisam、innodb
c、int char、varchar 、bigint、float 、data、year

4 group by 和 order by 的区别？
group by:分组 一般和函数使用，条件时接having
order by:排序 asc desc

5 聚合函数有哪些？
count()统计
sum()求和
avg()求平均数
max()最大值
min()最小值
distinct()去重

6 写出增删改查的基本格式？
1、insert into 增
2、delete from 删
3、update set 改
4、select 查

7 描述下你对索引的理解
索引是一种数据结构设计
一个索引是存储的表中数据结构；
索引是建立在表字段上，
索引包含了一列值，这个值保存在一个数据结构中
索引的作用：
保持数据的唯一性,
实现表与表之间参照完整性
减少排序和分组的时间，可以利用索引快速访问数据库中的特定信息
索引：普通索引、主键索引，唯一索引

8 描述下你对视图的理解
视图为虚拟表，不在数据库中以存储的形式保存（本身包含数据），是在使用视图时动态生成。可以提高查询效率，提高安全性，不需要关系表结构

9 数据库中常用的约束有哪些?
not null >非空约束
parmary key 主键约束，里面的值必须是唯一的，不能重复
不放主键约束主键会重复
Default>默认值约束
auto_increment 自增长约束 比如现在的id是00234–》00235
在当前的基础上+1，自增长约束一般配合主键约束使用
foreign key 外键约束

10 如何创建一个索引和删除一个索引？
创建：alter table 表名 add index(字段名）
alter table 表名 add primary KEY(字段名)
alter table 表名 add unique 外键名(索引名) ;
删除：alter table 表名 drop index 索引名;
alter tbale 表名 drop primary key

12 如何创建一个表？
create table test（属性）

13 如何修改一个表字段？
alter table test change 旧字段 新字段 字段类型（字段长度）

14 如何删除一个表字段？
alter table test drop 字段名

15 where 和having的区别？
where后面可以接group by分组函数，但是group by
后面不能接where,否则会报错
group by后面不能接where,可以使用having代替where使用
先分组再过滤

基础题(1*20)
画出多表查询的基本模型（6个），以及写出这6个模型的基本格式+一个基本连接。表名以table1 ,table2 表示，字段以id表示

基本连接
select * from 表1 INNER JOIN 表2 on 表1.关联字段1=表2.关联字段2 ;

内连接
select * from 表1, 表2 where 表1.关联字段1=表2.关联字段2 ;
左连接
select * from 表1 left JOIN 表2 on 表1.关联字段1=p.d表2.关联字段2 ;
右连接
select * from 表1 right JOIN 表2 on 表1.关联字段1=p.d表2.关联字段2 ;

左表独有的数据
select * from 表1 left JOIN 表2 on 表1.关联字段1=p.d表2.关联字段2 where 左表字段名 is null ;

右表独有数据
select * from 表1 right JOIN 表2 on 表1.关联字段1=p.d表2.关联字段2 where 左表字段名 is null ;
左右表独有的数据
select * from dept INNER JOIN emp on dept.dept1=emp.dept2
UNION
select * FROM dept left join emp on dept.dept1=emp.dept2 where name is null
UNION
select * FROM dept right join emp on dept.dept1=emp.dept2 where dept_name is null ;

数据库题（10*5）
下面是一个学生与课程的数据库，三个关系表为：
学生表S（Sid，SNAME,AGE,SEX）
成绩表SC（Sid，Cid，GRADE）
课程表C（Cid，CNAME，TEACHER）
其中Sid为学号，SNAME为学生名字，AGE为年龄，SEX为性别，
Cid为课程号，GRADE为成绩，CNAME为课程名字，TEACHER为教师姓名。

SQL脚本如下：
#学生表#create table S(Sid int(1),SNAME char(20),AGE int(1),SEX int(1));
#成绩表#create table SC(Sid int(1),Cid char(5),GRADE int(20)) ;
#课程表#create table C(Cid char(5),CNAME char(20),TEACHER char(20));
/*
insert into S(Sid,SNAME,AGE,SEX)values(1111,‘David’,28,1);
insert into S(Sid,SNAME,AGE,SEX)values(1112,‘Dany’,23,0);
insert into S(Sid,SNAME,AGE,SEX)values(1113,‘TOM’,38,1);
insert into S(Sid,SNAME,AGE,SEX)values(1114,‘Lily’,20,0); */

insert into SC(Sid,Cid,GRADE)values(1113,‘C1’,‘38’);
insert into SC(Sid,Cid,GRADE)values(1112,‘C2’,‘80’);
insert into SC(Sid, Cid,GRADE)values(1112,‘C3’,‘67’);
insert into SC(Sid,Cid,GRADE)values(1111,‘C4’,‘88’);
insert into SC(Sid,Cid,GRADE)values(1114,‘C4’,‘66’);
*/

insert into C(Cid,CNAME,TEACHER)values(‘C4’,‘Maths’,‘zhangsan’);
insert into C(Cid,CNAME,TEACHER)values(‘C3’,‘English’,‘lisi’);
insert into C(Cid,CNAME,TEACHER)values(‘C2’,‘Chinese’,‘wangwu’);
insert into C(Cid,CNAME,TEACHER)values(‘C1’,‘Computer’,‘zhaoliu’);/
/

– 查询出学习成绩及格以上的学生姓名与成绩
方法1：select sname,grade from s inner join sc on s.sid=sc.sid where grade>60
方法2：select a.sname,a.grade from (select s.,sc.grade,c. from s inner join sc on s.sid=sc.sid inner join c on sc.cid=c.cid)a where a.grade>=60;
方法3：select sname,grade from s,sc,c where s.sid=sc.sid and sc.cid=c.cid and sc.grade>59;
– 查询姓名以ny结尾的学生姓名及其任课老师姓名
方法1：SELECT SNAME,TEACHER FROM S,SCORE,C WHERE S.Sid=SCORE.Sid AND SCORE.Cid=C.Cid AND SNAME LIKE ‘%ny’
方法2：select a.sname,a.teacher from (select s.,sc.grade,c. from s inner join sc on s.sid=sc.sid inner join c on sc.cid=c.cid)a where a.sname like ‘%ny’;
方法3：select sname,teacher from s,sc,c where s.sid=sc.sid and sc.cid =c.cid and s.sname like ‘%ny’ ;

– 选修课名为Maths的学生学号与姓名
方法1：SELECT s.sid,sname from s INNER JOIN sc on s.sid=sc.sid INNER JOIN c on c.cid=sc.cid where cname=‘maths’
方法2：select a.sid,a.sname from (select s.,sc.grade,c. from s inner join sc on s.sid=sc.sid inner join c on sc.cid=c.cid)a where a.CNAME='Maths
方法3：
– 选修课号为C2和C4的学生学号
方法1：select s.sid from s,sc,c where s.sid=sc.sid and sc.cid=c.cid and (c.cid=‘C2’ or c.cid=‘C4’);
方法2：select s.sid from s,sc1,c where s.sid=sc1.sid and sc1.cid=c.cid and c.cid in（‘c2’,‘c4’）;
方法3：
select a.sid from (select s.,sc.grade,c. from s inner join sc on s.sid=sc.sid inner join c on sc.cid=c.cid)a where a.cid=‘c2’ or a.cid='c4

– 请问没有参加考试的学生的姓名及其学号
方法1：select s.sid,sname from s inner join sc on s.sid=sc.sid inner join c on sc.cid=c.cid where grade is null;
方法2：select a.sid,a.sname from (select s.,sc.grade,c. from s inner join sc on s.sid=sc.sid inner join c on sc.cid=c.cid)a where a.grade is null;
方法3：
select sname, s.sid from s,sc1,c where s.sid=sc1.sid and sc1.cid=c.cid and grade is null

– 请问总分在80分以上的学生姓名
方法1：select sname from s,(select sid,sum(grade)sum from sc group by sid)a where s.sid=a.sid and a.sum >80
方法2：select s.sname from s,(select sname,sum(grade)sum from s,sc,c where s.sid=sc.sid and sc.cid=c.cid GROUP BY s.sid)z where s.sname=z.sname and sum>80;
方法3：select s.sid,sname from s inner join sc on s.sid=sc.sid inner join c on sc.cid=c.cid group by sc.sid having sum(grade)>80
方法4：
select sname from s inner join (select sid,sum(grade)a from sc group by sid)b on s.sid=b.sid where a>80;

– 请问考试不及格的考生姓名以及科目名称
方法1：select sname, cname from s,sc1,c where s.sid=sc1.sid and sc1.cid=c.cid and grade<60;
方法2：select SNAME,CNAME from S,SCORE,C where S.Sid=SCORE.Sid AND SCORE.Cid=C.Cid and grade<60
方法3：
select a.sname,a.cname from (select s.,sc.grade,c. from s inner join sc on s.sid=sc.sid inner join c on sc.cid=c.cid)a where a.grade<60;

– 显示出参加考试的学生的学号和姓名
方法1：select s.sid,sname from s,sc1,c where s.sid=sc1.sid and sc1.cid=c.cid and grade is not null;
方法2：select s.sid,sname from s inner join sc on s.sid=sc.sid inner join c on sc.cid=c.cid where grade is not null;
方法3：select a.sname,a.sid from (select s.,sc.grade,c. from s inner join sc on s.sid=sc.sid inner join c on sc.cid=c.cid)a where a.grade is not null;
– 请问TOM参加了哪一门科目的考试
方法1：select cname from s,sc,c where s.sid=sc.sid and sc.cid=c.cid and sname=‘TOM’;

– 找出Dany的所有考试后成绩中最高的科目的任课老师姓名
方法1：select teacher from s,sc,c where s.sid=sc.sid and sc.cid=c.cid and sname='Dany’and grade=(select max(grade) from s,sc,c where s.sid=sc.sid and sc.cid=c.cid and sname=‘Dany’);
方法2：
select teacher from s inner join sc on s.sid=sc.sid inner join c on sc.cid=c.cid where sname=‘dany’ and grade=(select max(grade) from sc where sc.sid=(select s.sid from s where sname=‘dany’));
方法3：
select teacher from s inner join sc on s.sid=sc.sid inner join c on sc.cid=c.cid where sname=‘dany’ and grade=(select max(grade) from sc where sc.sid=(select s.sid from s where sname=‘dany’));

S

SC

C