MySQL 面试题
一, 建表语句
1, 使用数据库 --USE 数据库名;
2, 建表语句 -- 以一个学生成绩表为例CREATE TABLE test(
idintnotnull,
namechar(20),
scoreint,
primary key(id),
check(score between0and100)
)engine=Innodb;
约束语句:
设置默认值 --DEFAULT '默认值'
设置非空 --NOT NULL
设置唯一 --UNIQUE(字段名)
设置主键 --PRIMARY KEY(字段名)
设置外键 --FOREIGN KEY(字段名) REFERENCES 表名 (字段名)
设置 CHECK 约束 --CHECK(字段名 BETWEEN xx AND xx) 或 CHECK(字段名 = xx or ......) 或 CHECK(字段名 > xx AND ......)
约束类型.
具体语法分为: 列级___约束, 表级__约束
主键约束 : 是用来唯一标识表中一个列的, 一个表中主键约束只能有一个, 但是一个主键约束中可以包含多个列, 也称联合主键.
外键约束: 用来建立两个表中列之间关系的, 它可以由 1 列或多列组成. 一个表可以有一个或多个外键.
唯一约束: 也是用来唯一标识表中列的, 与主键约束不同的是在一张数据表中可以有多个唯一约束.
检查约束:(该约束在表中是不起作用的, 即仍然可以插入不符合条件的数据) 用来限定表中列里输入值的范围.
默认值约束: 用来当不给表中的列输入值时, 自动为该列添加一个值.
非空约束:(这个是给表中新建字段时, 都会添加 null 或者 not null , 默认为 null) 用来限定数据表中的列必须输入值.
注意提醒:
a. 以上 6 种约束中, 只有主键约束在一个表中只能有一个, 其他的约束都可以有多个.
b. 联合主键约束只能用表级主键约束的语法格式.
c.
二, 插入数据 --insert into 表名 (字段 1, 字段 2,......) values(值 1, 值 2,......);
示例: insert into test(id,name,score) values(1,'test1',60);
insert ... set 形式
insert tableName().... select 形式 (可以将其他表中的数据插入该表中)
三, 删除数据 --delete form 表名 where 条件;
示例: delete from test where id = 1;
四, 修改数据 --update 表名 set 字段名 = 新值 where 条件;
示例: update test set score=666 where id = 1;
五, 聚合函数
注意: where 子句中不能使用聚合函数, 因为聚合函数对结果集进行操作, 而 where 子句运行时还没有筛选出结果集, 所以此时使用聚合函数会报错; 与 where 相比, having 虽然也是用来筛选的, 但 having 是用来筛选分组的, 跟在 group by 之后, 所以 having 子句运行时结果集已经被筛选出, 此时可以使用聚合函数进行二次筛选.
1, 返回某列平均值 --AVG(字段名) 示例: select max(score) from test;
2, 返回某列行数 --COUNT(字段名) 示例: select count(id) from test;
3, 返回某列最大值 --MAX(字段名) 示例: select max(score) from test;
4, 返回某列最小值 --MIN(字段名) 示例: select min(score) from test;
5, 返回某列值之和 --SUM(字段名) 示例: select sum(score) from test;
综合示例: select count(id),max(score),min(score) from test;
Group By 使用:
示例:
首先在表中加上一个字段 sex 性别 (这样的修改尽量避免, 在此只为测试)--alter table test sex char(2) default '男'
并插入一条实例数据 --insert into test(id,name,score,sex) values(8, 'test8', 88, '女');
然后对 sex 分组 --select sex from test group by sex;
发现这样的结果只是会根据 sex 类型分为两组, 每组只显示一条数据, 这样的操作是没有意义的, 所以 Group By 一般与聚合函数一起使用.
聚集函数与 Group By 使用:
示例: select sex,count(sex) from test group by sex;
这样就会把各个性别人数统计出来.
聚集函数与 Group By 与 Having 一起使用:
having 子句可以再次筛选符合条件的分组
示例: select sex,count(sex) from test group by sex having count(sex)> 1;
六, 面试常见查询语句 (能掌握以下几个例子就基本可以了)
表结构:
student(s#,sname,sage,ssex) 学生表
course(c#,cname,T#) 课程表
sc(s#,c#,score) 成绩表
Teacher(T#,tname) 教师表
1. 查询 001 课程比 002 课程成绩高的所有学生的学号:selectsc1.s#
fromsc sc1joinsc sc2 on sc1.s# = sc2.s#
wheresc1.c# = '001' and sc2.c# = '002' and sc1.score> sc2.score
2. 查询平均成绩大于 60 分的同学的学号和平均成绩: 13708374810
select s#, avg(score) from sc group by s# having avg(score)>60;
3. 查询所有同学的学号, 姓名, 选课数, 总成绩:selectstudent.s#,student.name ,count(sc.c#) ,sum(sc.score)
fromstudent leftjoinsc on student.s# = sc.s# 注: 内连接要计算笛卡尔积, 这里使用左外连接效率更高
4. 查询姓李的老师个数:
select count(T#) from Teacher where tname like "李 %"
注: 模糊查询, % 代表任意字符,_代表任一单个字符,[......] 代表是括号中字符中的单个字符, [^......] 代表不是括号中字符中的单个字符
5. 查询没有学过叶萍老师课的同学学号, 姓名:selectstudet.s# student.sname
fromstudent
wheres# not in (select distinct(sc.s#) from sc, course, teacher where sc.c# = course.c# and teacher.t# = course.t# and teacher.tname = "叶萍");
三表联合 + 子查询
6. 查询学过 001 和 002 课程的同学的姓名学号:selectstudent.s3 student.sname
fromstudentjoincourse on student.s# = course.s#
where(course.c# = '001' union course.c# = '002');
7. 查询学过叶萍老师课的同学的学号, 姓名:selectstudent.s# student.sname
fromstudent
wheres# in(select distinct(sc.s#) from sc course teacher where sc.c# = course.c# and teacher.t# = course.t# and teacher.tnmae = "叶萍") 注: distinct 为去重函数
8. 查询 002 成绩比 001 低的同学的学号, 姓名:selectstudent.s# student .name
fromstudentwherestudent.s# in(select sc1.s# from sc sc1 join sc sc2 on sc1.s# = sc2.s# where s1.c# = 001 and s2.c# = 002 and sc1.score> sc2.score);
9. 查询所有课程成绩小于 60 分的同学的学号, 姓名:selectstudent.s# student.sname from student join sc on student.s# = sc.s#
wheresc.score>60;
10. 查询没有学全 所有课的同学的学号和姓名:selectstudent.s# student.sname from student,sc
wherestudent.s# = sc.s# group by student.s#,student.sname having count(c#) < (select count(c#)from course);
11. 查询至少有一门课程与学号 1001 的同学所学相同的同学的学号和姓名:selectstudent.s# ,student.sname from student join sc
wherestudent.s# = sc.s# and c# in select c# from sc where s# = '1001'
12. 查询至少学过学号为 001 同学所有一门课的其他同学的学号和姓名:selectdistinct student.s#,student,sname from
SCjoinstudent on SC.s# = student.s#
wherec# in(select c# from sc where s#='001');