提示：只是自己做的练习，并不代表全部是正确的，新手不要当成教程哦，期待您的指教

1 面试题1 学生成绩表 score 、课程表 course 结构

1.1 请写出课程表中建表SQL语句

create table course(
co_id char(5) primary key,
co_name char(3) not null,
co_desc varchar(60));

1.2 如果学号的前两位表示年级，要查找98级女生的姓名，请写出相应的SQL语句

select sc_name from score
where substr(sc_number,1,2) = '98' and sc_sex = '0';

1.3 要查找所需要补考（小于60分）的学生姓名和这门课程的名称和成绩，请写出相应的SQL语句

select sc_name,co_name,sc_score
from score sc left join course co on sc.sc_courseid = co.co_id
where sc_ismakeup = '1';

1.4 查询每个学生需要补考（小于60分）的课程的平均分，并以平均分排序

#升序asc，没有明确按什么排序时默认为升序
select sc_name,avg(sc_score) as avg_score from score
where sc_ismakeup = '1'
group by sc_name
order by avg_score;

#降序desc
select sc_name,avg(sc_score) as avg_score from score
where sc_ismakeup = '1'
group by sc_name
order by avg_score desc;

2 面试题2 某班级学生和考试成绩信息如下图 (ID 为学生的编号 ,Name 为姓名 ,Mark为成绩）

2.1 查询成绩>85分的学生姓名

select Name,Mark from Student S left join Achievement A on S.ID = A.ID
where Mark >85;

2.2 查询成绩>=90分的学生人数

select count(*) from Achievement where Mark >=90 group by Mark;

2.3 Robert此次考虑80分,请将其成绩添加进去

insert into Achievement(ID,Mark)(7,85);

2.4 将Rose的成绩修改为87

update Achievement set Mark = 87 where ID = 3;

2.5 删除Betty的记录

delete * from student where ID=6;
delete * from Achievement where ID=6;

3 面试题3

下表为一张商品表product示例,Id为自增列,完成语句

3.1 请从表中找出名称中包含"企鹅"的所有商品

selcet * from product where name like "%企鹅%"；

3.2 查询Cash第二贵的物品的Name

#方法1
select name from product order by cash desc limit 1,1;

#方法2
select name from product order by cash desc limit 1 offset 1;

3.3 请插入一条名称为"角色卡",Cash为"100"的新数据

insert into product (name,cash) values("角色卡","100");

3.4 更新"皇帝企鹅2代"的Cash,增加20

update product set cash = cash + 20 
where name = "皇帝企鹅2代"

5 面试题5

5.1 查询所有同学的学号、姓名、选课数、总成绩

select s#,sname,count(cname),sum(score) group by s# from student stu 
left join course co on stu.s# = co.c#
left join sc on stu.s# = sc.s#;

5.2 查询平均成绩不小于60分的同学的学号和平时成绩

select s#,avg(score) as avg_score from sc
where avg_score>=60;

6 面试题6

6.1 创建student表，包括学号，姓名，性别，生日，年级，学号为主键自增

create table student(
snum int(4) primary key auto_increment;
sname;ssex;sbirthday;grade);

6.2 删除student表中姓名是王五，学号为5217的学生

delete *from student where snum = "5217" and sname = "王五";

6.3 在student表中，增加学生信息：学号1001，姓名张三，性别男，生日20050202，年级2

insert into student (sname,snum,ssex,sbirthday,grade) 
values("张三","1001","男","20050202","2");

6.4 修改student表中学号为6359的学生姓名为李四

update student set sname = "李四" where snum = "6359";

6.5 查询student表中性别女年级3的所有学生信息

select * from student where ssex = "女" and grade = "3";

7 面试题7

select * from 购物信息
where 购物人 in (select 购物人 from 购物信息 group by 购物人 having count(*) >=2);

 8 面试题8

select name from (
select name,min(score) from student group by name having min(score)>80);

---

select class from (select distinct * from courses) as c 
group by c.class having count(class)>=5;

总结

1.聚变函数

        count(): 统计数量

        sum(): 求和

        max(): 最大值

        min(): 最小值

        avg(): 平均值

2.limit显示

select * from table limit 起始位置,记录数；

(起始位置从0开始）

select * from table limit 起始位置 OFFSET 记录数；

(同上一条一样的效果)

select * from table limit 5

(只有一个参数，他表示返回的最大行记录数，limit n = limit 0,n)

3.substr函数截取字符串

substr(字段名,起始位数,截取位数)

4.操作数据

统计人数：count()...group by 字段名

排序：order by 字段名 asc/desc

添加数据：insert into 表名 (字段1,值1),(字段2,值2)...;

insert into 表名 (字段1,..,字段n) values(值1,...,值n);

删除数据：delect * from table;

更新(修改)数据：update 表名 set 字段名 = 值;

5.范围查询

like   "%3%"   "3%"     "%3"

in 需要知道具体的范围

去重去空

select distinct 字段名 from 表名 where is not null;