mysql求利润_利用MySQL进行数据复杂查询

你的名字 2022-10-25 04:59 358阅读 0赞

利用MySQL进行数据复杂查询

一，某电商网商品库存管理系统数据的查询操作

1,查询每件商品的单件利润

select name as 商品名称,(outprice-inprice) as 商品单件利润 from tb1\_commodity

![0fb61047609e1c5cd4a4bff55edeccff.png][]

2,查询进价大于等于10的商品信息

SELECT NAME

inprice

FROM

tb1\_commodity

WHERE

inprice >= 10

![764b4499ce877a07c8167236215cfbe3.png][]

3,查询进价小于10的商品信息

SELECT NAME

inprice

FROM

tb1\_commodity

WHERE

inprice < 10\`\`

![2cdb71441d9328a13fe1f53cde413983.png][]

4)查询进价小于10并且商品类型为1的商品信息

SELECT NAME

inprice,

type

FROM

tb1\_commodity

WHERE

inprice < 10

AND type = 1

![8a27627a9660eebd17eaf74dfd60375e.png][]

5)查询进价小于10或商品类型为1的商品信息

SELECT NAME

inprice,

type

FROM

tb1\_commodity

WHERE

inprice < 10

OR type = 1

![bab9ee0a0270ed8913d3f46410f6f516.png][]

6)查询进价在10-20之间

SELECT NAME AS

inprice,

type

FROM

tb1\_commodity

WHERE

inprice <= 20 AND inprice >= 10

![4cfba835999309eab7db8729c3d0c4e6.png][]

7)查询商品售价为空的商品信息

SELECT NAME

FROM

tb1\_commodity

WHERE

outprice IS NULL

![bb6e3ed69b1ca57c5dd97d87fe49c6af.png][]

8)查询商品进价为10或20或30的商品信息

SELECT

FROM

tb1\_commodity

WHERE

inprice = 10

OR inprice = 20

OR inprice = 30

![cc4011a57baf2e3af187fa488a72862c.png][]

9)商品名称包括'玩具'的所有商品

SELECT NAME

FROM

tb1\_commodity

WHERE

NAME LIKE '%玩具%'

![3aa7bcc440e2b862d52bbb59b8cd1af5.png][]

10)根据商品进价从低到高排序输出

SELECT NAME

inprice

FROM

tb1\_commodity

ORDER BY

inprice ASC

![c4dc8622372870e072bd75e917d36c1d.png][]

11)根据商品售价从高到低排序输出

SELECT NAME

outprice

FROM

tb1\_commodity

ORDER BY

outprice DESC

![f792d3634f7beee1a4f3e8640ec85523.png][]

12)商品售价排行榜前5名

SELECT NAME

outprice

FROM

tb1\_commodity

ORDER BY

outprice DESC

LIMIT 5

![cb6e45b4f59c73fae232fa721535728a.png][]

13)商品售价排行榜第6-10名

SELECT NAME

outprice

FROM

tb1\_commodity

ORDER BY

outprice DESC

LIMIT 6,

![92924248cf1a5a4766f98ce09c737ed8.png][]

14)计算每种商品的平均进价

SELECT

type,

avg( inprice )

FROM

tb1\_commodity

GROUP BY

type

![edb0ecd01154da2fb7aba6448fccb42b.png][]

15)根据客户姓名查询客户信息

SELECT

FROM

tb1\_customer

WHERE

NAME = '刘德华'

![71250455beb41e450f73508275a14feb.png][]

16)根据客户手机号查询客户信息

SELECT

FROM

tb1\_customer

WHERE

phone = 123

![c69303a7509e46039cc1318196f892ba.png][]

17)查询出一共有多少男性客户

SELECT

count( NAME )

FROM

tb1\_customer

WHERE

gender = 1

![0ebaabf44f2fbe825b3aa20987f6186e.png][]

18)查询出一共有多少女性客户

SELECT

count( NAME )

FROM

tb1\_customer

WHERE

gender = 0

![7135019bb903422b2b15e07c98cf1edf.png][]

19)使用子查询来获取指定客户买了什么

SELECT NAME

FROM

tb1\_commodity

WHERE

id IN (

SELECT

cid

FROM

tb1\_order

WHERE

cuid = ( SELECT id FROM tb1\_customer WHERE NAME = '刘德华' ))

![79e957bf9ce70aeeab93b9abfa7ce789.png][]

20)使用连接查来获取指定客户买了什么

SELECT

c.NAME

FROM

tb1\_order a,

tb1\_customer b,

tb1\_commodity c

WHERE

b.id = c.id

AND b.id = a.cid;

![4fd2e28c81821fc4e5df65ee4d929946.png][]

二，某中学在线考试系统数据的查询操作

1)根据姓名查询学生信息

select \* from tb1\_student where sname='关胜'

![5150b0a0323404e07cae553cd225b7ea.png][]

2)查询年龄小于30岁的学生信息

select \* from tb1\_student where sage<30

![603defa8604397e1e2a2aad089285685.png][]

3)查询年龄在25至30之间的学生信息

select \* from tb1\_student where sage BETWEEN 25 and 30

![aa0fdade5fc24a01f25492ee48b3287b.png][]

4)按照年龄从大到小顺序输出学生信息

select \* from tb1\_student order by sage desc

![7c463efdf8ff67dc3380ca8e6fc6379b.png][]

5)查询年龄最小的学生信息

select \* from tb1\_student order by sage asc limit 1

![84814a590674e338eda37a802208733c.png][]

6)查询姓“刘”“张”的老师的个数

select count(\*) from tb1\_teacher where tname like '刘%' or tname like '张%'

![3e4fabf01e9326b2a46b30786ae2fa59.png][]

7)查询没学过“张学友”老师课的同学的学号、姓名

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 = '张学友'

)))

![e3c82cb2937e1ba453bffdb544cf6099.png][]

8)查询老师的总数

SELECT

count( tid )

FROM

tb1\_teacher

ORDER BY

tid

![8d5e25396bbde9983e83437ba5980713.png][]

9)查询每个老师的授课数目

SELECT

a.tid,

count( b.tname )

FROM

tb1\_teacher b,

tb1\_course a

WHERE

a.tid = b.tid

GROUP BY

b.tname

![e3a2e674a4987f77bf6ee174931a59e2.png][]

10)查询每个老师有多少学生

SELECT

tname,

COUNT( c.sid )

FROM

tb1\_teacher a,

tb1\_course b,

tb1\_sc c

WHERE

a.tid = b.tid

AND b.cid = c.cid

GROUP BY

c.sid

![7dc60851dd70af00c4359f42e27fbf16.png][]

11)根据科目名称查询科目信息

SELECT

FROM

tb1\_course

WHERE

cname = '体育'

![aa1baa525c6316b4072508e564fca925.png][]

12)查询每个科目对应的老师名字

SELECT

a.cid,

a.cname,

b.tname

FROM

tb1\_course a, tb1\_teacher b

WHERE

a.tid = b.tid

![d474a92c8502e098e3b712e9d49423c1.png][]

13)查询每个科目有多少学生在学

SELECT

cname,

COUNT( cname )

FROM

tb1\_course a,

tb1\_sc b

WHERE

a.cid = b.cid

GROUP BY

cname

![3bd2045d7fab24f11d39d9df400a5365.png][]

14)查询科目名称中带有's'关键字的科目信息

SELECT

FROM

tb1\_course

WHERE

cname LIKE '%s%'

![f397fc64229e14eaffbe519e57c86a77.png][]

15)查询不止1个老师教的科目

SELECT

a.cid,

a.cname,

b.tname

FROM

tb1\_course a,

tb1\_teacher b

WHERE

a.tid = b.tid

GROUP BY

cname

![7e9732cfe82f553de16814a8e54d3a87.png][]

16)查询“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

![3cfe650aea932362e857c15277238675.png][]

17)查询平均成绩大于60分的同学的学号和平均成绩

SELECT

sid,

avg( score )

FROM

tb1\_sc

GROUP BY

sid

HAVING

avg( score )> 60

![cdb79ff9f0deffc9070a6d4f475ba6c1.png][]

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

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

![b6bc1f40513803017911ad1255f8f76c.png][]

19)查询所有课程成绩小于60分的同学的学号、姓名

SELECT

a.sid,

a.sname,

b.score

FROM

tb1\_student a,

tb1\_sc b

WHERE

a.sid = b.sid

AND score < 60

![86b5b5fc35a3e60a0d51106f75612147.png][]

20)查询不同老师所教不同课程平均分从高到低显示

SELECT

a.tid,

b.cid,

avg( c.score )

FROM

tb1\_teacher a,

tb1\_course b,

tb1\_sc c

WHERE

a.tid = b.tid

AND b.cid = c.cid

GROUP BY

a.tid,

b.cid

ORDER BY

avg( c.score ) DESC

![56d080d5a18bb992be67973ade50dd5e.png][]

[0fb61047609e1c5cd4a4bff55edeccff.png]: /images/20221024/73b0d8906a9a4521b8f0d3e1370f8459.png
[764b4499ce877a07c8167236215cfbe3.png]: /images/20221024/c59511e7349a4623b077c0a26daf6c04.png
[2cdb71441d9328a13fe1f53cde413983.png]: /images/20221024/1ab7a298b847467d817f9480cc4a64a9.png
[8a27627a9660eebd17eaf74dfd60375e.png]: /images/20221024/c8652b83cd6e4fe98c45333049566336.png
[bab9ee0a0270ed8913d3f46410f6f516.png]: /images/20221024/7b9e733e864c464594af711cbae1aa47.png
[4cfba835999309eab7db8729c3d0c4e6.png]: /images/20221024/ce4353c32e7149228cd43d9ac977408b.png
[bb6e3ed69b1ca57c5dd97d87fe49c6af.png]: /images/20221024/8050b91752af46c99effa88f9fc021e7.png
[cc4011a57baf2e3af187fa488a72862c.png]: /images/20221024/ac31b6a9012a40cc8b83c82a6213e0b9.png
[3aa7bcc440e2b862d52bbb59b8cd1af5.png]: /images/20221024/57705eac16474f0f9be5ff687d07e7c3.png
[c4dc8622372870e072bd75e917d36c1d.png]: /images/20221024/2e5babffd2ec4808bb0ee0eb62ad936b.png
[f792d3634f7beee1a4f3e8640ec85523.png]: /images/20221024/36d481f16ded41e7a93643a0fbb797ee.png
[cb6e45b4f59c73fae232fa721535728a.png]: /images/20221024/33c55309965f4b508b2932578b07b26c.png
[92924248cf1a5a4766f98ce09c737ed8.png]: /images/20221024/7830ef0222794a80a67289f57b104c0d.png
[edb0ecd01154da2fb7aba6448fccb42b.png]: /images/20221024/9ec990cb9c824618beca240febe8c883.png
[71250455beb41e450f73508275a14feb.png]: /images/20221024/4a9191d3a821495b83e85ef61573b815.png
[c69303a7509e46039cc1318196f892ba.png]: /images/20221024/cc612c20a8514f8daeb9c27c879d4ec4.png
[0ebaabf44f2fbe825b3aa20987f6186e.png]: /images/20221024/55c53563af2d49fba5546b8d191b428a.png
[7135019bb903422b2b15e07c98cf1edf.png]: /images/20221024/5e96b1a6634f456192df71fe78ceabbd.png
[79e957bf9ce70aeeab93b9abfa7ce789.png]: /images/20221024/d7b6c50e3516426bb1bcdd69e41b154b.png
[4fd2e28c81821fc4e5df65ee4d929946.png]: /images/20221024/85d62915035044cd8161e047d151793e.png
[5150b0a0323404e07cae553cd225b7ea.png]: /images/20221024/09cc716b660340e4881298036c3718cc.png
[603defa8604397e1e2a2aad089285685.png]: /images/20221024/2119ee632f7d4c178e0ffad1092eaf30.png
[aa0fdade5fc24a01f25492ee48b3287b.png]: /images/20221024/016316909b29486396e14500e454a798.png
[7c463efdf8ff67dc3380ca8e6fc6379b.png]: /images/20221024/9a1f2aaec6b6421eb61aa49090653ea9.png
[84814a590674e338eda37a802208733c.png]: /images/20221024/91038118f6f94b2a907e5d0ce1637cb1.png
[3e4fabf01e9326b2a46b30786ae2fa59.png]: /images/20221024/29c9134dc12749498bda89b4882122a7.png
[e3c82cb2937e1ba453bffdb544cf6099.png]: /images/20221024/0ca9591a0bf9485d9170636f781f0773.png
[8d5e25396bbde9983e83437ba5980713.png]: /images/20221024/cad10675a2dd4672876ead9f605437c7.png
[e3a2e674a4987f77bf6ee174931a59e2.png]: /images/20221024/3c1d60566bc64919abd57967c1592118.png
[7dc60851dd70af00c4359f42e27fbf16.png]: /images/20221024/57effd9c083941ef9930665818847b37.png
[aa1baa525c6316b4072508e564fca925.png]: /images/20221024/e688d39edd574de3bd3d2057dbf69573.png
[d474a92c8502e098e3b712e9d49423c1.png]: /images/20221024/0fad4db43216404daa2499480adf42c9.png
[3bd2045d7fab24f11d39d9df400a5365.png]: /images/20221024/88dc38f319ce42abafea02ea9dd46db1.png
[f397fc64229e14eaffbe519e57c86a77.png]: /images/20221024/b16272d1e86844d494fbfc080af907d0.png
[7e9732cfe82f553de16814a8e54d3a87.png]: /images/20221024/dc17fe358d0f4e3a892c2b244ea7084d.png
[3cfe650aea932362e857c15277238675.png]: /images/20221024/a9ddcba87171402b8b4e34ef9e9704b3.png
[cdb79ff9f0deffc9070a6d4f475ba6c1.png]: /images/20221024/2434c0f6362b432da9659127daa1ef3a.png
[b6bc1f40513803017911ad1255f8f76c.png]: /images/20221024/f8fe3727428e4f158fff1ceec5405a15.png
[86b5b5fc35a3e60a0d51106f75612147.png]: /images/20221024/86cde2bea1574c65a77b94e8a4db55e3.png
[56d080d5a18bb992be67973ade50dd5e.png]: /images/20221024/31f8699819a048dbb1492ecc68763746.png