sql语句练习

---

question_1:

---

三张表信息：

1： 用户表 t_user_info:(id,name)

2: 课程表 t_class_info(id, name)

3: 分数表 t_scorere_info(id, u_id, c_id, num)

u_id 关联用户表id，c_id关联课程表id

"""
查询平均分高于60分的学生名称
"""
select * from
((select u_id,avg(num) as avg from t_scorere_info group by u_id)as kk)
where avg > 60;

"""
查询高于“数学”课程平均分的学生名称
"""

# 查询课程的id
c_ids = select id from t_class_info where name = "数学";

# 查询数学的平均成绩
avg_num = select avg(num) from t_scorere_info where c_id='c_ids';


select u_id from
((select  
u_id,avg(num) as avg 
from t_scorere_info 
where c_id='c_ids' group by u_id)
as kk)
where avg>avg_num;

question_2:

---

name	course	grade
张三	语文	81
张三	数学	75
李四	语文	76
李四	数学	90
王五	语文	81
王五	数学	100
王五	英语	90

"""
用一条SQL 语句 查询出每门课都大于80 分的学生姓名
"""
select name from table group by name having min(grade) > 80;

---

sql多条件模糊查询

SELECT id,chapter_url FROM 'chapter' WHERE chapter_url like "%https://pumanovels.com/%" and chapter="";

---

查询某本小说重复出现的次数

SELECT bookname, count(bookname) as number from novel GROUP BY bookname HAVING number >1 ORDER BY number desc; 

---

删除表中重复的数据

DELETE 
from 
askbob_config_engine_05 
where id in (
select 
id 
from 
(SELECT 
count(*) as number,engine_id, id 
FROM `askbob_config_engine_05`
 GROUP BY engine_id HAVING number >1) 
as t)

---

向表中某一个字段后面增加一个字段:
alter table表add column 新增的字段名 字段类型 default null after chinese_name(在哪个字段后面增加);

#案例:
alter table classify add column roots varchar(355) default null after chinese_name;

---