MySQL个人总结，学习笔记和题目：子查询，让列名内容不重复，多表查询【诗书画唱】
（一）
登录Mysql
mysql -uroot -proot
查看数据库
show databases;
数据库:
按照数据结构来组织、存储管理数据的仓库
为什么要使用数据库？
1.查找数据方便
2.节省空间
卸载mysql没有卸载完成
1.控制面板卸载mysql服务
2.文件保存目录删掉
3.隐藏mysql目录也要删
mysql的数据类型:
int:整形
varchar():字符串
boolean:是否
text:大文本
timestamp:时间戳
bolb:二进制
mysql约束:
唯一:unique
自增约束:auto_increments
主外键:foreign key
外键也称为参照完整性，外键表的信息参照着主键表的信息进行设置，主键表没有的信息外键表示不能使用的非空约束:notnull
主键约束:primary key
默认值约束:default
on delete cascade:在设置外键的时候设置级联删除
——————————————————————————————————
mysql增删改查语法:
————————
增：
增加数据:insert into 表名(列名1，列名2) values（值1，值2）
————————
删：
删除语法:delete from 表名 条件表达式
删除表:drop table 表名
清空表:truncate table sss
——————————————————————————————
改：
修改语法:update 表名 set 列名1=值1 where 条件表达式
——————————
查:
基本查询:select * from 表名
查询指定的内容:select 列名 from 表名
列名起别名:select 列名 as 名称（即自己取的列名的别名） from 表名
或省略as简写为：
select 列名 名称（即自己取的列名的别名） from 表名
去除重复(列名下的内容的查询):select distinct name,typename from sss
简单多表查询:select * from 表1,表2 where 表1.id=表2.id
__________________________________________
子查询分为3中子句: where后当条件
将查询结果当做比较条件
select * from 表名 where 列名>（select 列名 from 表名）
from后当数据源
exists后当条件
where子句:后跟一个表达式，表达式返回一个true或者false的数据
表达式可以是
>,<,=,!=,>=,<=
between...and....
in(内容1,内容2)
like:%(匹配0到任意个字符数据) _(代表一个字符)
&&,||,and,or,is not null,is null
order by:
排序....默认是升序(ASC) 倒序(DESC)
select top 3 from sp
limit后可以跟1个或者两个数字
跟1个的时候是从0到第几条数据
跟两个的时候就是从第一个参数开始的位置查询第二个参数的个数
——————————
题目：
16.查询商品编号为为5的商品信息
17.查询商品价格在100-500之间的商品信息
18.查询用户名包含a开头的用户信息
19.查询超出商品保质期的商品
20.查询商品编号为2，5,7的商品信息
21.根据商品的价格进行排序，要求倒序
22.查询商品表前3条数据
23.查询商品表销售价格大于进货价200的商品
24.查询商品表所有商品的总价格
25.查询商品名称2个字的商品，按照价格倒序排序，取前三条
26.查询每个人购买的商品总价格
27.查询编号为3的商品一共卖了多少件
28.查询销售量最高的是哪件商品
29.查询每件商品的平均销售数量
30.查询销售数量比平均销售数量高的商品有哪些
答案：
16.查询商品编号为为5的商品信息
select * from sp where spid=5
17.查询商品价格在100-500之间的商品信息
select * from sp where sp_chushoujia between 100 and 500
select * from sp where sp_chushoujia>100 and sp_chushoujia<500
18.查询用户名包含a开头的用户信息
select * from yonghu where yhuname like 'a%'
19.查询超出商品保质期的商品
select * from sp where spbaozhiqi<current_timestamp
20.查询商品编号为2,5,7的商品信息
select * from sp where spid in(2,5,7)
21.根据商品的价格进行排序，要求倒序
select * from sp order by sp.sp_chushoujia
22.查询商品表前3条数据
select * from sp limit 0,3
23.查询商品表销售价格大于进货价200的商品
select * from sp where sp_chushoujia-spjinhuojia>20
24.查询商品表所有商品的总价格
select sum(sp_chushoujia) from sp
25.查询商品名称2个字的商品，按照价格倒序排序，取前三条
select * from sp where spname like '__' order by sp_chushoujia desc limit 3
————
【关键词出现的先后顺序：
where group by having order by limit
——————
where子句
order by子句
limit子句
group by子句
having子句】
————————
26.查询每个人购买的商品总价格
————
【5个聚合函数：
sum() avg() max() min() count()】
——————
select sum(sp_chushoujia) from sp
select c.yhid '每个人',c.yhname,sum(a.spbuynum)*b.sp_chushoujia as '每个人买的多少钱'
from dingdan a inner join sp b on a.spid=b.spid inner join yonghu c on a.yhid =c.yhid
group by c.yhid
27.查询编号为3的商品一共卖了多少件
select sum(dingdan.spbuynum) from dingdan where spid=3
28.查询销售量最高的是哪件商品
select spid,sum(spbuynum) from dingdan group by spid limit 1
29.查询每件商品的平均销售数量
select spid,avg(spbuynum) from dingdan group by spid
select * from dingdan
30.查询销售数量比平均销售数量高的商品有哪些
select * from dingdan where spbuynum>(select avg(spbuynum) from dingdan)
————————————————————————————
————————————————————————————————————————
（二）
————
下面是常用的语句：
drop table dingdan
drop table sp
drop table yonghu
select * from sp
select * from yonghu
select * from dingdan
————
1.使用sql语句创建以上三张表
create table sp(
spid int primary key auto_increment,
spname varchar(100) not null unique,
spjinhuojia float default 0,
sp_chushoujia float default 0,
spshengchanriqi timestamp default current_timestamp,
spbaozhiqi datetime,
spshuliang int default 0,
spbeizhu varchar(1000)
);
create table yonghu(
yhid int primary key auto_increment,
yhuname varchar(100) not null unique,
yhpwd varchar(100) not null ,
yhname varchar(100) not null unique
);
create table dingdan(
did int primary key auto_increment,
spid int,
yhid int,
spbuynum int,
foreign key(spid) references sp(spid) on delete cascade,
foreign key(yhid) references yonghu(yhid)on delete cascade
)
2.使用insert语句给以上表分别插入10条数据
insert into sp (spname,spjinhuojia,sp_chushoujia,spshengchanriqi,spbaozhiqi,spshuliang,spbeizhu) values('雪碧',3,4,now(),'2020-10-11',10,'hhh'),
('雪花',2,3,now(),'2020-9-8',10,'hhhssss'),('热狗',2,3,now(),'2020-6-11',15,'hhh防守打法'),('乌龙茶',4,5,now(),'2019-10-11',22,'hhh'),
('菠萝啤',1,2,now(),'2020-2-11',110,'hh防守打法h'),('白沙',8,10,now(),'2022-10-11',100,'hhh防守打法'),('牛肉',35,42,now(),'2020-8-21',19,'hh防守打法h'),
('红茶',3,4,now(),'2020-12-11',11,'h大声道hh')
insert into yonghu(yhuname,yhpwd,yhname)values('admin','admin','GM'),('qwe','qwe','gggg'),('ewq','ewq','sssss'),('asd','asd','fsdfsdf'),
('赵云','zhaoyun','常山赵子龙'),('刘备','liubei','大哥'),('关羽','guanyu','二弟'),('张飞','zhangfei','三弟')
insert into dingdan(spid,yhid,spbuynum) values(3,1,10),(3,2,4),(3,3,7),(1,2,6),(3,2,8),(5,6,10),
(7,1,9),(6,2,6),(8,4,3),(2,2,10),(7,2,5),(3,4,10),(8,1,10),(6,6,6),(7,7,7)
3.修改商品表商品编号为3的商品名称为可口可乐
update sp set spname='可口可乐' where spid=3
4.修改用户表用户编号为5的用户姓名为“钟无艳”
update yonghu set yhname='钟无艳' where yhid=5
5.修改购物车表商品订单编号为3的数量为10
update dingdan set spbuynum=10 where did=3
6.添加钟无艳购买可口可的记录，购买数量为5
insert into dingdan (yhid,spid,spbuynum) values((select yhid from yonghu where yhname='钟无艳'),
(select spid from sp where spname='可口可乐'),5)
7.修改钟无艳购买可口可乐的数量为10
update dingdan set spbuynum=10 where spid=(select spid from sp where spname='可口可乐')
and yhid=(select yhid from yonghu where yhname='钟无艳')
8.修改购买数量大于5的商品为可口可乐
update dingdan set spid=(select spid from sp where spname='可口可乐')
where spbuynum>10
9.修改用户编号为3的用户购买商品为可口可乐的数量为5
update dingdan set spbuynum=5 where yhid=3 and
spid=(select spid from sp where spname='可口可乐')
————————————————————————————
重要部分（和我之前做的答案不一样部分）:
10.修改商品剩余数量为0的购买数量为0
update dingdan inner join sp on dingdan.spid=sp.spid
set dingdan.spbuynum=0 where sp.spshuliang=0
11.删除商品可口可乐，要求级联删除
delete from sp where spname='可口可乐'
12.删除商品剩余数量等于为0的商品购买记录
先查商品表，看看商品的剩余数量，如果商品的商品数量为0，就看谁买了这件商品
如果有人买了这件商品就将这件商品的购买记录删除掉
delete dingdan
FROM dingdan inner join sp on dingdan.spid=sp.spid
where sp.spshuliang=0
13.删除用户编号为3的用户信息，购买记录
delete from yonghu where yhid=3
14.联合查询3张表
select * from sp inner join dingdan on sp.spid=dingdan.spid inner join
yonghu on dingdan.yhid=yonghu.yhid
15.查询3张表显示购买记录编号，购买的名称，商品价格，购买的用户姓名，
购买数量和单件商品总价格
select did as '订单编号',sp.spname as '商品名称',
sp.sp_chushoujia as '商品价格', yonghu.yhname as '用户名',dingdan.spbuynum as '购买数量',
(sp.sp_chushoujia*dingdan.spbuynum) as '单件商品总价格'
from sp inner join
dingdan on sp.spid=dingdan.spid inner join
yonghu on dingdan.yhid=yonghu.yhid
————
下面是常用的语句：
drop table cj
drop table stu
drop table zy
select * from cj
select * from stu
select * from zy
————
create table stu(
xuehao int primary key auto_increment,
xingming varchar(100) not null unique,
sex varchar(10) default '男',
chushengriqi timestamp default current_timestamp,
beizhu text
);
create table zy(
zhuanyeid int primary key auto_increment,
zhuanyename varchar(100) not null
);
create table cj(
chengjiid int primary key auto_increment,
xuehao int,
zhuanyeid int,
chengji float,
foreign key (xuehao) references stu(xuehao) on delete cascade,
foreign key(zhuanyeid) references zy(zhuanyeid) on delete cascade
);
insert into stu (chushengriqi,xingming,beizhu,sex) values('1990-08-14','张三17','呵呵','女'),('1990-08-14','张三16','呵呵','女')
insert into stu (chushengriqi,xingming,beizhu) values('1990-08-14','张三15','呵呵'),('2000-08-14','张三14','呵呵')
insert into stu (xingming,beizhu) values('张三10','呵呵'),('张三11','呵呵'),('张三12','呵呵'),('张三13','呵呵'),('张三','呵呵'),
('李四','呵呵1'),('王五','呵呵2'),('马六','呵呵3'),('刘备','呵呵4'),
('关羽','呵呵5'),('张飞','呵呵6'),('黄忠','呵呵7'),('马超','呵呵')
insert into zy(zhuanyename) values('计算机'),('Java'),('C#'),
('mysql'),('sqlserver'),('javascript')
insert into cj (xuehao,zhuanyeid,chengji) values(16,1,40),(17,2,60),(1,1,88),(1,1,66),(1,2,99),(11,2,33),(3,2,33),(1,2,33),
(2,3,44),(4,3,55),(2,4,66),(6,2,77),(7,3,88),(6,4,99)
11.查询编号在3-7之间的所有学生信息
select * from stu where xuehao>=3 and xuehao<7
12.查询名称包含张的学生的成绩
select * from stu where xingming like '%张%'
13.查询性别为男的学生的成绩
--【不好用子查询的，就会用多表查询】
select chengji as'男的学生的成绩' from cj inner join stu on cj.xuehao=stu.xuehao where sex='男'
14.查询编号在2,5,6的学生的信息
select * from stu where xuehao in (2,5,6)
15.查询名称包含王的学生的专业
select zhuanyename as'包含王的学生的专业' from zy inner join cj on zy.zhuanyeid=cj.zhuanyeid
inner join stu on stu.xuehao=cj.xuehao where stu.xingming like '%王%'
16.查询学生id为11的所有课程的成绩,显示学生姓名,专业名称,成绩，使用别名
select xingming as '学生姓名' ,zhuanyename as'专业名称' ,chengji as '成绩' from zy inner join cj on zy.zhuanyeid=cj.zhuanyeid
inner join stu on stu.xuehao=cj.xuehao where stu.xuehao=11
17.查询以王开头的学生并且名称长度为2的学生的信息
select * from stu where xingming like '%王_%'
18.查询年龄小于20岁的学生信息
select * from stu where (year(now())-year(chushengriqi))<20
19.查询每个学生的年龄，按照倒序排序
select xingming as '学生的姓名',(year(now())-year(chushengriqi)) as '年龄' from stu order by (year(now())-year(chushengriqi)) desc
20.查询每个学生的总分
select xingming as '每个学生的姓名', sum(chengji) as '每个学生的总分' from stu inner join cj on stu.xuehao=cj.xuehao group by xingming
21.查询张三的所有科目的总成绩
select sum(chengji) as '张三的所有科目的总成绩' from stu inner join cj on stu.xuehao=cj.xuehao
inner join zy on zy.zhuanyeid=cj.zhuanyeid where xingming='张三'
22.查询张三的所有科目的平均成绩
select zhuanyename as'专业科目名', avg(chengji) as '张三这门科目的平均成绩' from stu inner join cj on stu.xuehao=cj.xuehao
inner join zy on zy.zhuanyeid=cj.zhuanyeid where xingming='张三' group by zhuanyename
23.查询所有学生的最高分，最低分
select zhuanyename as'专业科目名', avg(chengji) as '张三这门科目的平均成绩' from stu inner join cj on stu.xuehao=cj.xuehao
inner join zy on zy.zhuanyeid=cj.zhuanyeid where xingming='张三' group by zhuanyename
24.查询张三有多少个科目
--去除重复:select distinct name,typename from sss【distinct 放在不想重复的列名处】
select count(distinct zhuanyeid) as'张三有的科目数' from cj where xuehao =(select xuehao from stu where xingming='张三' )
--select zhuanyename as'张三考过的科目', count(zhuanyename) as'张三考过的次数' from stu inner join cj on stu.xuehao=cj.xuehao
--inner join zy on zy.zhuanyeid=cj.zhuanyeid where xingming='张三' group by zhuanyename
25.查询每个人的最高分,最低分
select xingming as '姓名', max(chengji) as'最高分', min(chengji) as'最低分' from stu inner join cj on stu.xuehao=cj.xuehao
inner join zy on zy.zhuanyeid=cj.zhuanyeid group by xingming
26.查询每个人的总成绩，平均成绩
【个人总结：就算别的表中没对应的内容,left join也会显示stu 这个left join前（左）表中的所有列名中有的内容（right join也类似，
就算别的表中没对应的内容,right join也会显示stu 这个right join后（右）表中的所有列名中有的内容），
而inner join只会显示都有对应内容的列名中的内容等】
select xingming as '姓名', sum(chengji) as'总成绩', avg(chengji) as'平均成绩' from stu left join cj on stu.xuehao=cj.xuehao
left join zy on zy.zhuanyeid=cj.zhuanyeid group by xingming
27.查询每个人有多少个科目
select xingming as '姓名', count(distinct zhuanyename) as'有的科目数' from stu left join cj on stu.xuehao=cj.xuehao
left join zy on zy.zhuanyeid=cj.zhuanyeid group by xingming
28.查询平均成绩大于60的人的平均成绩
【having avg(chengji )>60是group by xingming后面加的限制条件】
select xingming as '姓名', sum(chengji) as'总成绩', avg(chengji ) as'平均成绩' ,
count(distinct zhuanyename) as'有的科目数' from stu inner join cj on stu.xuehao=cj.xuehao
inner join zy on zy.zhuanyeid=cj.zhuanyeid group by xingming having avg(chengji )>60
--select xingming as '姓名', avg(chengji) as'平均成绩' from stu inner join cj on stu.xuehao=cj.xuehao
--inner join zy on zy.zhuanyeid=cj.zhuanyeid where avg(chengji)>60 group by xingming
29.查询低于班级平均成绩的学生姓名和成绩
select xingming as '姓名', sum(chengji) as'总成绩', avg(chengji ) as'平均成绩' ,
count(distinct zhuanyename) as'有的科目数' , (select sum(chengji)/count(chengjiid)
from stu left join cj on stu.xuehao=cj.xuehao
left join zy on zy.zhuanyeid=cj.zhuanyeid) as '班级平均成绩' from stu left join cj on stu.xuehao=cj.xuehao
left join zy on zy.zhuanyeid=cj.zhuanyeid group by xingming having avg(chengji )<(select sum(chengji)/count(chengjiid)
from stu left join cj on stu.xuehao=cj.xuehao
left join zy on zy.zhuanyeid=cj.zhuanyeid)
【
个人的总结：
select sum(chengji)/count(chengjiid)
from stu left join cj on stu.xuehao=cj.xuehao
left join zy on zy.zhuanyeid=cj.zhuanyeid 求的是总成绩和除以总人数=班级总平均成绩=68.9333333333333，
如果显示8.9333333333333，就是显示不完整，鼠标左键选中后，向左移动，就可以看到"8.9333333333333"前面的"6"，
或者出现箭头，把表格拉长。
】
--select
--sum(chengji)/(count(distinct zhuanyename)*count(distinct xingming)) from stu left join cj on stu.xuehao=cj.xuehao
--left join zy on zy.zhuanyeid=cj.zhuanyeid
30.查询男同学的平均成绩比女同学的平均成绩多多少
select ( (select avg(chengji) from stu left join cj on stu.xuehao=cj.xuehao
left join zy on zy.zhuanyeid=cj.zhuanyeid where sex='男')-(select avg(chengji) from stu left join cj on stu.xuehao=cj.xuehao
left join zy on zy.zhuanyeid=cj.zhuanyeid where sex='女')) as'男同学的平均成绩比女同学的平均成绩多的分数'
【
select distinct( (select avg(chengji) from stu left join cj on stu.xuehao=cj.xuehao
left join zy on zy.zhuanyeid=cj.zhuanyeid where sex='男')-(select avg(chengji) from stu left join cj on stu.xuehao=cj.xuehao
left join zy on zy.zhuanyeid=cj.zhuanyeid where sex='女')) as'男同学的平均成绩比女同学的平均成绩多的分数' from stu left join cj on stu.xuehao=cj.xuehao
left join zy on zy.zhuanyeid=cj.zhuanyeid 】
31.查询至少拥有两门课程及以上的学生
select xingming as '姓名', count(distinct zhuanyename) as'有的科目数' from stu left join cj on stu.xuehao=cj.xuehao
left join zy on zy.zhuanyeid=cj.zhuanyeid group by xingming having count(distinct zhuanyename)>=2
32.查询拥有两门课程以上的学生的平均分
select xingming as '姓名', count(distinct zhuanyename) as'有的科目数' , avg(chengji) as'平均分' from stu left join cj on stu.xuehao=cj.xuehao
left join zy on zy.zhuanyeid=cj.zhuanyeid group by xingming having count(distinct zhuanyename)>=2
