在上个DDL博客中找到学生表将其中的数据读入到mysql中,使用虚拟机或者Navicat都可以。
将学生表导入Navicat中
查询语句
查询一整张表
查询年龄大于22
select *
from students
where students.age > 22;
年龄大于22的女生
select clazz
,sname
,age
,gender
from students
where students.age > 22 and students.gender = “女”;
查找文科的学生
select *
from students
where clazz like “%文科%”; # %为模糊匹配
查找六班的学生
select *
from students
where clazz like “%六班%”;
select *
from students
where clazz like “%六%”;
计算学生的总分 (group by)
group by 后面的字段必须在select 里面体现出
并且不能体现出不在group by 之后的字段 只有
sum avg count max min 这些函数才可以另外体现
select score.id
,sum(sco)
from score
group by score.id;
这是计算全部学生的总和 可以看成是一个值 在where 判断语句中可以直接用
select sum(sco) from score;
合并两表 (join on xxxx)
select t1.*
,t2.sub_id
,t2.sco
from students as t1
join score as t2
on t1.id = t2.id;
合并两张表 并求总分
先合并在聚合
select tt1.id
,tt1.sname
,tt1.gender
,tt1.clazz
,sum(tt1.sco) as sum_sco
,max(tt1.sco) as max_sco
from (select t1.*
,t2.sco
from students as t1
join score as t2
on t1.id = t2.id ) as tt1
group by tt1.id,tt1.sname,tt1.gender,tt1.clazz;
先聚合在合并
select t2.*
,t1.sum_sco
from (select score.id
,sum(score.sco) as sum_sco
from score
group by score.id) as t1
join students as t2
on t1.id = t2.id;
找到总分分数大于500的学生
having方法(在group by 之后执行)
select tt1.id
,tt1.sname
,tt1.age
,tt1.gender
,tt1.clazz
,sum(sco) as sum_sco
from (select t1.*
,t2.sub_id
,t2.sco
from students as t1
join score as t2
on t1.id = t2.id ) as tt1
group by tt1.id,tt1.sname,tt1.age,tt1.gender,tt1.clazz
having sum_sco > 500;
用where 方法来选择
select t1.*
,t2.sum_sco
from students as t1
join (select score.id
,sum(sco) as sum_sco
from score
group by score.id) t2
on t1.id = t2.id
where t2.sum_sco > 500;
降序排列 (order by xxxx desc )
select tt1.id
,tt1.sname
,tt1.age
,tt1.gender
,tt1.clazz
,sum(sco) as sum_sco
from (select t1.*
,t2.sub_id
,t2.sco
from students as t1
join score as t2
on t1.id = t2.id ) as tt1
group by tt1.id,tt1.sname,tt1.age,tt1.gender,tt1.clazz
having sum_sco > 500
order by sum_sco desc # 不加desc 为升序排列
只展示5个学生 (limit number)
select tt1.id
,tt1.sname
,tt1.age
,tt1.gender
,tt1.clazz
,sum(sco) as sum_sco
from (select t1.*
,t2.sub_id
,t2.sco
from students as t1
join score as t2
on t1.id = t2.id ) as tt1
group by tt1.id,tt1.sname,tt1.age,tt1.gender,tt1.clazz
having sum_sco > 500
order by sum_sco desc # 不加desc 为升序排列
limit 5;
求前三门课程总分(where 在group by 之前执行)
select score.id
,sum(sco) as sum_sco
from score
where score.sub_id = 1000001
or score.sub_id = 1000002
or score.sub_id = 1000003
group by score.id;
合并语句(left join right join 等等)
a表 (id 学号)(name 姓名)
b表 (id 学号) (s_id 学科编号)
inner join 内连接(默认的连接方式)
left join (左连接)
right join(右连接)
union (上下合并)并且去重
union all (上下合并)并且不去重
全连接
sql中实际上不提供全连接 但是如果把左连接根右连接合并则就是全连接
笛卡尔积
where in 使用方法
自增列插入数据 自增列数据使用null 或 0 占位
insert into users() values(null,“zp”);
insert into users() values(0,“yn”);
复习
order 的执行顺序还在select 之后
大致的执行顺序为
== from > where > group by > select > having > order by > limit==
括号里面的优先级最高
-- select 语句
select *
from table
where conditions
group by columns
having conditions
order by columns
limit start,length;
join (left right inner) 三种形式
union (去重) union all 去重和不去重的区别
updata 语句 更新语句 delete 语句 删除语句
-- updata 语句 更新语句
UPDATE students set gender='1' where gender='男';
UPDATE students set gender='0' where gender='女';
UPDATE students set gender='1';
-- delete 语句 删除语句
delete from students where gender='女';
delete from students;
-- 截断表 将表删除并清空 所有的东西重新开始刷新 在进行新的书写
truncate students;
主键 ,FOREIGN KEY 约束建
主键是唯一的 是唯一约束 并且特性是 非空且唯一
FOREIGN KEY 这个是与另外一个表关联 ,当另外一个表变化后 子表也会变化
全部代码
-- 查询一整张表
select * from students;
-- 查询年龄大于22
select *
from students
where students.age > 22;
-- 年龄大于22的女生
select clazz
,sname
,age
,gender
from students
where students.age > 22 and students.gender = "女";
-- 查找文科的学生
select *
from students
where clazz like "%文科%"; # %为模糊匹配
-- 查找六班的学生
select *
from students
where clazz like "%六班%";
select *
from students
where clazz like "%六%";
-- 计算学生的总分
# group by 后面的字段必须在select 里面体现出
# 并且不能体现出不在group by 之后的字段 只有
# sum avg count max min 这些函数才可以另外体现
select score.id
,sum(sco)
from score
group by score.id;
# 这是计算全部学生的总和 可以看成是一个值 在where 判断语句中可以直接用
select sum(sco) from score;
-- 合并两表
select t1.*
,t2.sub_id
,t2.sco
from students as t1
join score as t2
on t1.id = t2.id;
-- 合并两张表 并求总分
# 先合并在聚合
select tt1.id
,tt1.sname
,tt1.gender
,tt1.clazz
,sum(tt1.sco) as sum_sco
,max(tt1.sco) as max_sco
from (select t1.*
,t2.sco
from students as t1
join score as t2
on t1.id = t2.id ) as tt1
group by tt1.id,tt1.sname,tt1.gender,tt1.clazz;
# 先聚合在合并
select t2.*
,t1.sum_sco
from (select score.id
,sum(score.sco) as sum_sco
from score
group by score.id) as t1
join students as t2
on t1.id = t2.id;
-- 找到总分分数大于500的学生
# having 在group by 之后执行
select tt1.id
,tt1.sname
,tt1.age
,tt1.gender
,tt1.clazz
,sum(sco) as sum_sco
from (select t1.*
,t2.sub_id
,t2.sco
from students as t1
join score as t2
on t1.id = t2.id ) as tt1
group by tt1.id,tt1.sname,tt1.age,tt1.gender,tt1.clazz
having sum_sco > 500;
# where 方法 !! where要在having前面执行
select t1.*
,t2.sum_sco
from students as t1
join (select score.id
,sum(sco) as sum_sco
from score
group by score.id) t2
on t1.id = t2.id
where t2.sum_sco > 500;
-- 降序排列
select tt1.id
,tt1.sname
,tt1.age
,tt1.gender
,tt1.clazz
,sum(sco) as sum_sco
from (select t1.*
,t2.sub_id
,t2.sco
from students as t1
join score as t2
on t1.id = t2.id ) as tt1
group by tt1.id,tt1.sname,tt1.age,tt1.gender,tt1.clazz
having sum_sco > 500
## 降序排列
order by sum_sco desc # 不加desc 为升序排列
## 只展示5个学生
limit 5;
-- where 在group by 之前执行
-- 求前三门课程总分
select score.id
,sum(sco) as sum_sco
from score
where score.sub_id = 1000001
or score.sub_id = 1000002
or score.sub_id = 1000003
group by score.id;
原文地址:https://blog.csdn.net/qq_50847752/article/details/134743191
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。
如若转载,请注明出处:http://www.7code.cn/show_40338.html
如若内容造成侵权/违法违规/事实不符,请联系代码007邮箱:suwngjj01@126.com进行投诉反馈,一经查实,立即删除!