首页 👨‍💻计算机

  1. ishop
select name as 商品名称,outprice-inprice as 商品单间利润 from tb1_commodity;
#查询出进价小于10的商品信息
select name,inprice from tb1_commodity where inprice>=10;
#查询出进价小于10并且商品类型为1的商品信息
select name,inprice,type from tb1_commodity where inprice<10 and type=1;
#查询出新家小于10或者商品类型为1 的商品类型
select name,inprice,type from tb1_commodity where inprice<10 or type=1
#查询出进价在10-20之间
select name inprice,type from tb1_commodity where inprice between 10 and 20
#查询商品销售为空的商品信息
select name from tb1_commodity where outprice is null
insert into tb1_commodity VALUES
(20,'乐高玩具-快乐家庭','中国',1,20,null,100)
(21,'手机模型玩具','中国',1,20,null,100),
(22,'和利波特1-3','中国',1,20,null,100)
#查询商品进价为10或20或30的商品信息
select name,inprice from tb1_commodity where inprice in(10,20,30)
#商品名称包括玩具的所有商品
select name from tb1_commodity where name like '%玩具%'
#商品进价从低到高排序输出
select name,inprice from tb1_commodity order by inprice;
#商品售价从高到低排序输出
select name,outprice from tb1_commodity where outprice is not null order by outprice desc
#商品售价排名前五的
select name,outprice from tb1_commodity where outprice is not null order by outprice desc limit 5
#商品售价排名6-10的
select name,outprice from tb1_commodity where outprice is not null order by outprice desc limit 5,5
#计算每种商品的平均进价
select type,avg(inprice) from tb1_commodity group by type;
#查询客户姓名查询客户信息
select * from tb1_customer where name='刘德华'
#根据客户手机号查询客户信息
select * from tb1_customer where phone='123'
insert into tb1_customer values(2,'朱重洋',123,1,'嘉兴')
#查询出一共有多少男性客户
select count(*) from tb1_customer where gender=1
#查询出一共多少女性客户
select count(*) from tb1_customer where gender=0
#用子查询来获取指定客户买了啥
select name from tb1_commodity where id in(select cid from tb1_order where cuid=(
select id from tb1_customer where name='刘德华'))
#使用连接查询获取指定客户买了啥
select c.name from tb1_commodity as c inner join (select o.cid as id from tb1_order as o inner join tb1_customer as cu on o.cuid=cu.id where cu.name='刘德华')as a on a.id =c.id

scts

#按姓名查询学生信息
select * from tb1_student where sname='宋江'
insert into tb1_student values(001,'宋江',21,1)
#查询年龄小于30岁的学生信息
select * from tb1_student where sage<30
insert into tb1_student values
(002,'卢俊义',21,1),
(003,'吴用',22,1),
(004,'公孙胜',23,1),
(005,'关胜',24,1)
#查询出年龄在25到30岁直接的学生信息
select * from tb1_student where sage between 25 and 30
#按照年龄从大到小顺序输出学生信息
select * from tb1_student order by sage desc
#查询出刘 张 老师的个数
select count(*) from tb1_teacher where tname like '刘%' or tname like '张%';

insert into tb1_teacher values(2,'张三')
insert into tb1_teacher values(3,'刘璋')
insert into tb1_teacher values(4,'张学友')
#查询没学过张学友老师课的同学的学号姓名
select sid,sname from tb1_student where sid not in(
select sid from tb1_sc where cid in(
select cid from tb1_course where tid=(
select tid from tb1_teacher where tname='张学友')))
#查询老师的总数
select count(*) from tb1_teacher
#查询每个老师的授课目录
select t.tname,count(c.cid)
from tb1_teacher as t,tb1_course as c
where t.tid=c.tid
group by t.tname;
#根据科目名称查询科目信息
select * from tb1_course where cname ='python'
select * from tb1_course where cname ='spass'
#查询每个科目对应的老师名字
select c.cid,c.cname,t.tname
from tb1_course as c,tb1_teacher as t
where c.tid =t.tid;
#查询每个科目有多少学生在学
select c.cid,count(sc.sid)
from tb1_course as c,tb1_sc as sc
where c.cid=sc.cid
group by c.cid;
#查询科目名称中带s关键字的科目信息
select * from tb1_course where cname like '%s%'
#查询出不止1个老师教的科目
select cname from tb1_course where cid in(
select cid from tb1_course having count(cname)>1)

#查询出c001比c002课程成绩搞的所有学生的学号
select a.sid from
(select sid,score from tb1_sc where cid=1) as a,
(select sid,score from tb1_sc where cid=2) as b
where a.sid=b.sid and a.score>b.score;

#查询出平均成绩大于60分的同学的学号和平均成绩
select sid,avg(score)
from tb1_sc
group by sid
having avg(score)>60
    #查询所有同学的学号,姓名,选课数,总成绩
    select tb1_sc.sid,tb1_student.sname,count(cid),sum(score)
    from tb1_student,tb1_sc
    where tb1_student.sid=tb1_sc.sid
    group by tb1_sc.sid
#查询所有课程成绩小于60分的同学和姓名
select sid,sname from tb1_student where sid not in(
select sid from tb1_sc where score>60)

#查询不同老师所教不同课程平均分从高到低显示
select tb1_teacher.tid,tb1_course.cid,avg(tb1_sc.score)
from tb1_teacher,tb1_course,tb1_sc
where tb1_teacher.tid=tb1_course.tid and
tb1_course.cid=tb1_sc.cid
group by tb1_teacher.tid,tb1_course.cid
order by avg(tb1_sc.score) desc



文章评论

目录