MySQL入门教程之常用查询语句

本文章来给各位同学介绍一下关于MySQL常用查询语句介绍,包括有列最大值、拥有某个列的最大值的行、使用用户变量等等语句.

示例数据库:

1. CREATETABLEshop(
2. articleINT(4)UNSIGNEDZEROFILLDEFAULT'0000'NOTNULL,
3. dealerCHAR(20)DEFAULT''NOTNULL,
4. priceDOUBLE(16,2)DEFAULT'0.00'NOTNULL,
5. PRIMARYKEY(article,dealer));
6. INSERTINTOshopVALUES
7. (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
8. (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);
9. mysql>SELECT*FROMshop;--phpfensi.com
10. +---------+--------+-------+
11. |article|dealer|price|
12. +---------+--------+-------+
13. |0001|A|3.45|
14. |0001|B|3.99|
15. |0002|A|10.99|
16. |0003|B|1.45|
17. |0003|C|1.69|
18. |0003|D|1.25|
19. |0004|D|19.95|
20. +---------+--------+-------+

1.列最大值

最大的物品号是什么？

SELECT MAX(article) FROM shop;

2. 拥有某个列的最大值的行

找出最贵物品的编号、销售商和价格？

SELECT article, dealer, price FROM shop WHERE price=(SELECT MAX(price) FROM shop);

另一个解决方案是按价格降序排序所有行并用MySQL特定LIMIT子句只得到第一行:

SELECT article, dealer, price FROM shop ORDER BY price DESC LIMIT 1;3.

列的最大值:按组每项物品的的最高价格是多少？

SELECT article, MAX(price) AS price

1. FROMshop
2. GROUPBYarticle
3. +---------+-------+
4. |article|price|
5. +---------+-------+
6. |0001|3.99|
7. |0002|10.99|
8. |0003|1.69|
9. |0004|19.95|
10. +---------+-------+

4.拥有某个字段的组间最大值的行对每项物品,找出最贵价格的物品的经销商？SELECT article,dealer,price

1. FROMshops1
2. WHEREprice=(SELECTMAX(s2.price)
3. FROMshops2
4. WHEREs1.article=s2.article);

5.使用用户变量找出价格最高或最低的物品的？

1. mysql>SELECT@min_price:=MIN(price),@max_price:=MAX(price)FROMshop;--phpfensi.com
2. mysql>SELECT*FROMshopWHEREprice=@min_priceORprice=@max_price;
3. +---------+--------+-------+
4. |article|dealer|price|
5. +---------+--------+-------+
6. |0003|D|1.25|
7. |0004|D|19.95|
8. +---------+--------+-------+

6.根据两个键搜索寻找两个通过OR组合到一起的关键字:

SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1' OR field2_index = '1'

还可以使用UNION将两个单独的SELECT语句的输出合成到一起:

SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1'UNIONSELECT field1_index, field2_index FROM test_table WHERE field2_index = '1';