首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Mysql——查询

Mysql——查询

作者头像
羊羽shine
发布2019-05-29 18:42:32
26K0
发布2019-05-29 18:42:32
举报
文章被收录于专栏:Golang开发Golang开发

创建表

mysql> CREATE TABLE fruits
    -> (
    -> f_id char(10) NOT NULL,
    -> s_id INT NOT NULL,
    -> f_name char(255) NOT NULL,
    -> f_price decimal(8,2) NOT NULL,
    -> PRIMARY KEY(f_id)
    -> );
Query OK, 0 rows affected (0.02 sec)

插入数据

mysql> INSERT INTO fruits VALUES('a1',101,'apple',5.2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO fruits VALUES('b1',101,'blackerry',10.2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO fruits VALUES('bs1',102,'orange',11.2);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO fruits VALUES('bs2',105,'melon',8.2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO fruits VALUES('t1',102,'banana',10.3);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO fruits VALUES('t2',102,'grape',5.3);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO fruits VALUES('o2',103,'cocounut',9.2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO fruits VALUES('c0',101,'cherry',3.2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO fruits VALUES('l2',104,'lemon',6.4);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO fruits VALUES('b2',104,'berry',7.6);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO fruits VALUES('m1',106,'mango',15.7);
Query OK, 1 row affected (0.00 sec)

where 条件判断符

操作符

说明

=

相等

<>或者!=

不相等

>

大于

>=

大于等于

<

小于

<=

小于等于

BETWEEN

位于两个数值之间

查询价格小于10.2的水果

mysql> SELECT f_name,f_price FROM fruits WHERE f_price < 10.2;
+----------+---------+
| f_name   | f_price |
+----------+---------+
| apple    |    5.20 |
| berry    |    7.60 |
| melon    |    8.20 |
| cherry   |    3.20 |
| lemon    |    6.40 |
| cocounut |    9.20 |
| grape    |    5.30 |
+----------+---------+
7 rows in set (0.00 sec)
IN查询

查询指定范围内的条件记录,将所有的查询条件用括号括起来。每个条件之间用逗号隔开,只需要满足其中一个条件的值就算做匹配项

mysql> SELECT f_name,s_id FROM fruits WHERE s_id IN (101,102) ORDER BY f_name;
+-----------+------+
| f_name    | s_id |
+-----------+------+
| apple     |  101 |
| banana    |  102 |
| blackerry |  101 |
| cherry    |  101 |
| grape     |  102 |
| orange    |  102 |
+-----------+------+
6 rows in set (0.00 sec)
BETWEEN AND 范围查询

BETWEEN 匹配的范围包括开始值和结束值

mysql> SELECT f_name,f_price FROM fruits WHERE f_price BETWEEN 3.20 AND 10.2 ORDER BY f_price DESC;
+-----------+---------+
| f_name    | f_price |
+-----------+---------+
| blackerry |   10.20 |
| cocounut  |    9.20 |
| melon     |    8.20 |
| berry     |    7.60 |
| lemon     |    6.40 |
| grape     |    5.30 |
| apple     |    5.20 |
| cherry    |    3.20 |
+-----------+---------+
8 rows in set (0.00 sec)
带LIKE的字符串匹配查询

LIKE查询一起使用的通配符有‘%'和‘',其中(下划线)只能一次值匹配一个字符 %(百分号)可以匹配任意多字符。

mysql> SELECT f_name FROM fruits WHERE f_name LIKE 'b%';
+-----------+
| f_name    |
+-----------+
| blackerry |
| berry     |
| banana    |
+-----------+
3 rows in set (0.00 sec)
DISTINCT 查询

DISTINCT 关键字查询可以去除重复的记录值

mysql> SELECT DISTINCT s_id FROM fruits;
+------+
| s_id |
+------+
|  101 |
|  104 |
|  102 |
|  105 |
|  106 |
|  103 |
+------+
6 rows in set (0.00 sec)
GROUP BY 分组
mysql> SELECT s_id,COUNT(*) FROM fruits GROUP BY s_id;
+------+----------+
| s_id | COUNT(*) |
+------+----------+
|  101 |        3 |
|  102 |        3 |
|  103 |        1 |
|  104 |        2 |
|  105 |        1 |
|  106 |        1 |
+------+----------+
6 rows in set (0.00 sec)

GROUP_CONCAT()函数可以将分组的中各个字段的值显示出来

mysql> SELECT s_id,GROUP_CONCAT(f_name) AS Names FROM fruits GROUP BY s_id;
+------+------------------------+
| s_id | Names                  |
+------+------------------------+
|  101 | apple,blackerry,cherry |
|  102 | orange,banana,grape    |
|  103 | cocounut               |
|  104 | berry,lemon            |
|  105 | melon                  |
|  106 | mango                  |
+------+------------------------+
6 rows in set (0.00 sec)
分组过滤

GROUP BY 可以好HAVING 一起限定显示记录所需满足的条件,只有满足条件的分组才被显示。

mysql> SELECT s_id,GROUP_CONCAT(f_name) AS Names FROM fruits GROUP BY s_id HAVING COUNT(f_name) > 1;
+------+------------------------+
| s_id | Names                  |
+------+------------------------+
|  101 | apple,blackerry,cherry |
|  102 | orange,banana,grape    |
|  104 | berry,lemon            |
+------+------------------------+
3 rows in set (0.00 sec)
LIMIT 限制查询结果数量

LIMIT [位置偏移量] 行数, 位置偏移量是可选参数,默认是0,从第一条数据开始。

SELECT *FROM fruits LIMIT 4;
+------+------+-----------+---------+
| f_id | s_id | f_name    | f_price |
+------+------+-----------+---------+
| a1   |  101 | apple     |    5.20 |
| b1   |  101 | blackerry |   10.20 |
| b2   |  104 | berry     |    7.60 |
| bs1  |  102 | orange    |   11.20 |
+------+------+-----------+---------+
4 rows in set (0.00 sec)

连接查询

数据准备

mysql> CREATE TABLE supplies(
    -> s_id int NOT NULL AUTO_INCREMENT,
    -> s_name char(50) NOT NULL,
    -> s_city char(50) NULL,
    -> s_zip char(50) NULL,
    -> s_call char(50) NOT NULL,
    -> PRIMARY KEY (s_id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO suppliesr VALUES(101,'FastFruit Inc','Tianjin','30000','48075');
ERROR 1146 (42S02): Table 'test_db.suppliesr' doesn't exist
mysql> INSERT INTO supplies VALUES(101,'FastFruit Inc','Tianjin','30000','48075');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO supplies VALUES(102,'LT Supplies','Chongqing','40000','44333');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO supplies VALUES(103,'ACME','Shanghai','20000','90046');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO supplies VALUES(104,'FNK Inc','Zhongshan','528437','11111');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO supplies VALUES(105,'Good SET','Taiyuan','030000','22222');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO supplies VALUES(106,'Just Eat Ours','Beijing','010','45678');
Query OK, 1 row affected (0.00 sec)

两表联合查询

mysql> SELECT supplies.s_id,s_name,f_name,f_price FROM fruits,supplies WHERE fruits.s_id = supplies.s_id;
+------+---------------+-----------+---------+
| s_id | s_name        | f_name    | f_price |
+------+---------------+-----------+---------+
|  101 | FastFruit Inc | apple     |    5.20 |
|  101 | FastFruit Inc | blackerry |   10.20 |
|  104 | FNK Inc       | berry     |    7.60 |
|  102 | LT Supplies   | orange    |   11.20 |
|  105 | Good SET      | melon     |    8.20 |
|  101 | FastFruit Inc | cherry    |    3.20 |
|  104 | FNK Inc       | lemon     |    6.40 |
|  106 | Just Eat Ours | mango     |   15.70 |
|  103 | ACME          | cocounut  |    9.20 |
|  102 | LT Supplies   | banana    |   10.30 |
|  102 | LT Supplies   | grape     |    5.30 |
+------+---------------+-----------+---------+
11 rows in set (0.00 sec)

内连接(INNER JOIN) 使用比较运算符进行表间某些列数据

mysql> SELECT supplies.s_id,s_name,f_name,f_price FROM fruits INNER JOIN  supplies ON  fruits.s_id = supplies.s_id;
+------+---------------+-----------+---------+
| s_id | s_name        | f_name    | f_price |
+------+---------------+-----------+---------+
|  101 | FastFruit Inc | apple     |    5.20 |
|  101 | FastFruit Inc | blackerry |   10.20 |
|  104 | FNK Inc       | berry     |    7.60 |
|  102 | LT Supplies   | orange    |   11.20 |
|  105 | Good SET      | melon     |    8.20 |
|  101 | FastFruit Inc | cherry    |    3.20 |
|  104 | FNK Inc       | lemon     |    6.40 |
|  106 | Just Eat Ours | mango     |   15.70 |
|  103 | ACME          | cocounut  |    9.20 |
|  102 | LT Supplies   | banana    |   10.30 |
|  102 | LT Supplies   | grape     |    5.30 |
+------+---------------+-----------+---------+
11 rows in set (0.00 sec)

子查询

数据准备

mysql> CREATE TABLE tb11( num1 INT);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE tb12( num2 INT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO tb11 VALUES(1),(5),(13),(27);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO tb12 VALUES(6),(14),(11),(20);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
ANY 、SOME

ANY 、SOME关键字表示满足内层子查询的任何一个比较条件,就返回一个结果作为外层查询的条件。只有大于表tb12字段num2任意的值就符合条件。

mysql> SELECT num1 FROM tb11 WHERE num1 > ANY (SELECT num2 FROM tb12);
+------+
| num1 |
+------+
|   13 |
|   27 |
+------+
2 rows in set (0.00 sec)
mysql> SELECT num1 FROM tb11 WHERE num1 > SOME (SELECT num2 FROM tb12);
+------+
| num1 |
+------+
|   13 |
|   27 |
+------+
2 rows in set (0.00 sec)
ALL

ALL 关键字需要满足所有内层查询的条件

mysql> SELECT num1 FROM tb11 WHERE num1 > ALL (SELECT num2 FROM tb12);
+------+
| num1 |
+------+
|   27 |
+------+
1 row in set (0.00 sec)
EXISTS

EXISTS 关键字后面的参数是一个任意的子查询,系统对子查询进行运算判断是否返回行,主要至少返回一行,那么EXIST的结果为true,如果子查询没有返回任何行,那么EXISTS 返回的结果是false。此时外层语句不做任何查询。

mysql> SELECT *FROM fruits WHERE EXISTS (SELECT s_name FROM supplies WHERE s_id = 106 );
+------+------+-----------+---------+
| f_id | s_id | f_name    | f_price |
+------+------+-----------+---------+
| a1   |  101 | apple     |    5.20 |
| b1   |  101 | blackerry |   10.20 |
| b2   |  104 | berry     |    7.60 |
| bs1  |  102 | orange    |   11.20 |
| bs2  |  105 | melon     |    8.20 |
| c0   |  101 | cherry    |    3.20 |
| l2   |  104 | lemon     |    6.40 |
| m1   |  106 | mango     |   15.70 |
| o2   |  103 | cocounut  |    9.20 |
| t1   |  102 | banana    |   10.30 |
| t2   |  102 | grape     |    5.30 |
+------+------+-----------+---------+
11 rows in set (0.00 sec)
比较运算符的子查询
mysql> SELECT s_id,f_name FROM fruits WHERE s_id = (SELECT s_id FROM supplies WHERE s_city = 'Tianjin');
+------+-----------+
| s_id | f_name    |
+------+-----------+
|  101 | apple     |
|  101 | blackerry |
|  101 | cherry    |
+------+-----------+
3 rows in set (0.00 sec)

合并查询

UINION 不使用关键字ALL,返回的结果会去重,所有的返回结果都是唯一的。

mysql> SELECT s_id,f_name,f_price FROM fruits WHERE f_price < 9.0 UNION ALL SELECT s_id,f_name,f_price FROM fruits WHERE s_id IN (101,103);
+------+-----------+---------+
| s_id | f_name    | f_price |
+------+-----------+---------+
|  101 | apple     |    5.20 |
|  104 | berry     |    7.60 |
|  105 | melon     |    8.20 |
|  101 | cherry    |    3.20 |
|  104 | lemon     |    6.40 |
|  102 | grape     |    5.30 |
|  101 | apple     |    5.20 |
|  101 | blackerry |   10.20 |
|  101 | cherry    |    3.20 |
|  103 | cocounut  |    9.20 |
+------+-----------+---------+
10 rows in set (0.00 sec)

mysql> SELECT s_id,f_name,f_price FROM fruits WHERE f_price < 9.0 UNION  SELECT s_id,f_name,f_price FROM fruits WHERE s_id IN (101,103);
+------+-----------+---------+
| s_id | f_name    | f_price |
+------+-----------+---------+
|  101 | apple     |    5.20 |
|  104 | berry     |    7.60 |
|  105 | melon     |    8.20 |
|  101 | cherry    |    3.20 |
|  104 | lemon     |    6.40 |
|  102 | grape     |    5.30 |
|  101 | blackerry |   10.20 |
|  103 | cocounut  |    9.20 |
+------+-----------+---------+
8 rows in set (0.00 sec)
本文参与 腾讯云自媒体分享计划,分享自作者个人站点/博客。
原始发表:2018.12.23 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • IN查询
  • BETWEEN AND 范围查询
  • 带LIKE的字符串匹配查询
  • DISTINCT 查询
  • GROUP BY 分组
  • 分组过滤
  • LIMIT 限制查询结果数量
  • 连接查询
  • 子查询
    • ANY 、SOME
      • ALL
        • EXISTS
          • 比较运算符的子查询
          • 合并查询
          领券
          问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档