创建数据库并插入数据
# 创建表，数据类型请自行查询
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
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
