创建数据库并插入数据

# 创建表，数据类型请自行查询
CREATE TABLE fruits (
  id INT NOT NULL,
  sid INT NOT NULL,
  NAME CHAR(255) NOT NULL,
  price DECIMAL (8, 2) NOT NULL,
  PRIMARY KEY (id)
);
# 表中插入数据
INSERT INTO fruits
VALUES
  ('1', 101, 'apple', 5.2),
  ('2', 101, 'blackberry', 10.2),
  ('3', 102, 'orange', 11.2),
  ('4', 105, 'melon', 8.2),
  ('5', 102, 'banana', 10.3),
  ('6', 102, 'grape', 5.3),
  ('7', 103, 'coconut', 9.2),
  ('8', 101, 'cherry', 3.2),
  ('9', 103, 'apricot', 2.2),
  ('10', 104, 'lemon', 6.4),
  ('11', 104, 'berry', 7.6),
  ('12', 106, 'mango', 15.6);

单表查询

查询所有字段

SELECT * FROM fruits;
id    sid    name    price
1    101    apple    5.20
2    101    blackberry    10.20
3    102    orange    11.20
4    105    melon    8.20
5    102    banana    10.30
6    102    grape    5.30
7    103    coconut    9.20
8    101    cherry    3.20
9    103    apricot    2.20
10    104    lemon    6.40
11    104    berry    7.60
12    106    mango    15.60

查询指定字段

SELECT ID,NAME FROM fruits;
ID    NAME
1    apple
2    blackberry
3    orange
4    melon
5    banana
6    grape
7    coconut
8    cherry
9    apricot
10    lemon
11    berry
12    mango

查询指定条件的字段

SELECT * FROM fruits WHERE NAME = 'BANANA';
id    sid    name    price
5    102    banana    10.30

带IN关键字的查询

IN关键字：IN(xx，yy，…) 满足条件范围内的一个值即为匹配项，括号内的值，或的关系

SELECT * FROM fruits WHERE NAME IN ('BANANA','ORANGE');
id    sid    name    price
3    102    orange    11.20
5    102    banana    10.30
SELECT * FROM fruits WHERE ID NOT IN (3,8);
id    sid    name    price
1    101    apple    5.20
2    101    blackberry    10.20
4    105    melon    8.20
5    102    banana    10.30
6    102    grape    5.30
7    103    coconut    9.20
9    103    apricot    2.20
10    104    lemon    6.40
11    104    berry    7.60
12    106    mango    15.60

带BETWEEN AND 的范围查询

BETWEEN … AND … : 在…到…范围内的值即为匹配项

SELECT * FROM fruits WHERE ID BETWEEN 3 AND 9;
id    sid    name    price
3    102    orange    11.20
4    105    melon    8.20
5    102    banana    10.30
6    102    grape    5.30
7    103    coconut    9.20
8    101    cherry    3.20
9    103    apricot    2.20
SELECT * FROM fruits WHERE ID NOT BETWEEN 5 AND 11;
id    sid    name    price
1    101    apple    5.20
2    101    blackberry    10.20
3    102    orange    11.20
4    105    melon    8.20
12    106    mango    15.60

带LIKE的字符匹配查询

LIKE: 模糊查询，和LIKE一起使用的通配符有 “%”、”_“

通配符	功能
“%”	作用是能匹配任意长度的字符。
“_”	只能匹配任意一个字符

SELECT * FROM fruits WHERE NAME LIKE 'black%';
id    sid    name    price
2    101    blackberry    10.20
SELECT * FROM fruits WHERE NAME LIKE 'b%y';
id    sid    name    price
2    101    blackberry    10.20
11    104    berry    7.60
SELECT * FROM fruits WHERE NAME LIKE '_ER_Y';
id    sid    name    price
11    104    berry    7.60

逻辑与之带AND的多条件查询

and：同时满足条件

SELECT
  *
FROM
  fruits
WHERE id IN (2, 4, 6, 8, 10)
  AND sid > 102;
id    sid    name    price
4    105    melon    8.20
10    104    lemon    6.40

逻辑或之OR的多条件查询

OR:有一个满足即可，类似in

SELECT
  *
FROM
  fruits
WHERE id IN (2, 4, 6, 8, 10)
  OR sid > 102;
id    sid    name    price
2    101    blackberry    10.20
4    105    melon    8.20
6    102    grape    5.30
7    103    coconut    9.20
8    101    cherry    3.20
9    103    apricot    2.20
10    104    lemon    6.40
11    104    berry    7.60
12    106    mango    15.60

关键字DISTINCT查询不重复的数据

SELECT
  DISTINCT SID
FROM
  fruits
WHERE id IN (2, 4, 6, 8, 10)
  OR sid > 102;
SID
101
105
102
103
104
106

ORDER BY对查询的结果排序

ORDER BY 字段 DESC 逆序排列

SELECT DISTINCT
  SID
FROM
  fruits
WHERE id IN (2, 4, 6, 8, 10)
  OR sid > 102
ORDER BY sid DESC;
SID
106
105
104
103
102
101

ORDER BY 字段 ASC 正序排列，默认为正

SELECT DISTINCT
  SID
FROM
  fruits
WHERE id IN (2, 4, 6, 8, 10)
  OR sid > 102
ORDER BY sid ASC;
SID
101
102
103
104
105
106

GROUP BY 对查询结果进行分组

不分组

SELECT SID FROM fruits;
SID
101
101
102
105
102
102
103
101
103
104
104
106

将相同的内容分到同一个组里面

分组之后，重复的都被分到一组

SELECT SID FROM fruits GROUP BY SID ;
SID
101
102
105
103
104
106

GROUP_CONCAT查看分组后的数目和内容

查看分组中的各个字段内容 GROUP_CONCAT( )

SELECT SID,COUNT(NAME),GROUP_CONCAT(NAME) FROM fruits GROUP BY sid;
SID    count(name)    group_concat(name)
101    3    apple,blackberry,cherry
102    3    orange,banana,grape
103    2    coconut,apricot
104    2    lemon,berry
105    1    melon
106    1    mango

HAVING条件过滤，相当于WHERE，只能分组用

SELECT SID,COUNT(NAME),GROUP_CONCAT(NAME) FROM fruits GROUP BY sid HAVING SID > 103;
SID    count(name)    group_concat(name)
104    2    lemon,berry
105    1    melon
106    1    mango

LIMIT 限制查询结果的数量

LIMIT 位置偏移量,行数
默认位置偏移量为0，即第1行

通过LIMIT可以选择数据库表中的任意行数，也就是不用从第一条记录开始遍历，可以直接拿到第5条到第10条的记录，也可以直接拿到第12到第15条的记录。

SELECT * FROM fruits WHERE ID LIMIT 0,5;
id    sid    name    price
1    101    apple    5.20
2    101    blackberry    10.20
3    102    orange    11.20
4    105    melon    8.20
5    102    banana    10.30
SELECT * FROM fruits WHERE ID LIMIT 5,9;
id    sid    name    price
6    102    grape    5.30
7    103    coconut    9.20
8    101    cherry    3.20
9    103    apricot    2.20
10    104    lemon    6.40
11    104    berry    7.60
12    106    mango    15.60