mysql 查询 select_MySQL查询select常见用法

MySQL数据库使用 SQL select语句来查询数据。我们可以通过mysql>命令提示窗口在数据库查询数据。

语法

SELECT column_name,column_name FROM table_name [WHERE Clause][LIMIT N][OFFSET M]

SELECT * FROM table_name; 投影

SELECT column_name,column_name FROM table_name WHERE qualification; 选择

FROM自己：要查询的关系，表、多个表、其它SELECT语句。

WHERE子句：布尔关系表达式 =、> 、< 、>=、<=

逻辑关系：AND、 OR、 NOT

BETWEEN ... AND...

LIKE ''

%:任意长度任意字符

—：任意单个字符

REGEXP,RLIKE

IN

IS NULL

IS NOT NULL

ORDER BY field_name {ASC| DESC}

字段别名：AS

LIMIT子句：LIMIT [offset,]Count

聚合：SUM(),MIN(),MAX()

GROUP BY:分组

多表查询：

连接：

交叉连接：笛卡尔乘积

自然连接：

左外连接：...LEFT JOIN...ON...

右外连接：...RIGHT JOIN...ON...

自连接：

子查询：

比较操作中使用子查询：子查询只能返回单个值

IN():使用子查询

在FROM中使用子查询

联合查询：

UNION

使用jiaowu数据库

mysql> use jiaowu;

mysql> show tables;

+------------------+

| Tables_in_jiaowu |

+------------------+

| courses       |

| scores        |

| sct          |

| student        |

| tutors         |

+------------------+

5 rows in set (0.00 sec)

查看students表所有内容：

mysql> SELECT * FROM students;

+-----+--------------+------+--------+------+------+------+---------------------+

| SID | Name            | Age  | Gender | CID1 | CID2 | TID  | CreateTime          |

+-----+--------------+------+--------+------+------+------+---------------------+

|   1 | GuoJing      |    19  | M      |    2 |    7 |    3 | 2012-04-06 10:00:00 |

|   2 | YangGuo      |    17  | M      |    2 |    3 |    1 | 0000-00-00 00:00:00 |

|   3 | DingDian      |    25  | M      |    6 |    1 |    7 | 2012-04-06 10:00:00 |

|   4 | HuFei        |    31  | M      |    8 |   10 |    5 | 2012-04-06 10:00:00 |

|   5 | HuangRong      |   16  | F      |    5 |    9 |    9 | 2012-04-06 10:00:00 |

|   6 | YueLingshang    |   18  | F      |    8 |    4 | NULL  | 2012-04-06 10:00:00 |

|   7 | ZhangWuji      |   20  | M      |    1 |    7 | NULL  | 2012-04-06 10:00:00 |

|   8 | Xuzhu        |    26  | M      |    2 |    4 | NULL  | 2012-04-06 10:00:00 |

|   9 | LingHuchong    |    22  | M      |   11 | NULL | NULL   | 0000-00-00 00:00:00 |

|  10 | YiLin        |    19  | F      |   18 | NULL | NULL   | 2012-04-06 10:00:00 |

+-----+--------------+------+--------+------+------+------+---------------------+

查看tutor表所有内容：

mysql> SELECT * FROM tutors;

+-----+--------------+--------+------+

| TID | Tname        | Gender   | Age  |

+-----+--------------+--------+------+

|   1 | HongQigong    | M      |   93 |

|   2 | HuangYaoshi   | M      |   63 |

|   3 | Miejueshitai  | F      |   72 |

|   4 | OuYangfeng    |        |   76 |

|   5 | YiDeng       | M      |   90 |

|   6 | YuCanghai    | M      |   56 |

|   7 | Jinlunfawang  | M      |   67 |

|   8 | HuYidao      | M      |   42 |

|   9 | NingZhongze   | F       |   49 |

+-----+--------------+--------+------+

查看学生表中Name和Age

mysql> SELECT Name,Age FROM students;

+--------------+------+

| Name          | Age  |

+--------------+------+

| GuoJing        |   19 |

| YangGuo        |   17 |

| DingDian       |   25 |

| HuFei         |   31 |

| HuangRong      |   16 |

| YueLingshang    |   18 |

| ZhangWuji      |   20 |

| Xuzhu        |   26 |

| LingHuchong    |   22 |

| YiLin        |   19 |

+--------------+------+

10 rows in set (0.00 sec)

用where条件语句查询

使用WHERE条件查询Age>20的学生；

mysql> SELECT Name,Age FROM students WHERE Age>20;

+-------------+------+

| Name        | Age  |

+-------------+------+

| DingDian     |   25 |

| HuFei       |   31 |

| Xuzhu       |   26 |

| LingHuchong   |   22 |

+-------------+------+

4 rows in set (0.00 sec)

多个条件查询

mysql> SELECT Name,Age FROM students WHERE Age>20 AND Gender='m';

+-------------+------+

| Name        | Age  |

+-------------+------+

| DingDian     |   25 |

| HuFei       |   31 |

| Xuzhu       |   26 |

| LingHuchong   |   22 |

+-------------+------+

4 rows in set (0.00 sec)

使用NOT语句查询

mysql> SELECT Name,Age,Gender FROM students WHERE NOT (Age>20 OR Gender='M');

+--------------+------+--------+

| Name         | Age  | Gender |

+--------------+------+--------+

| HuangRong      |   16   | F      |

| YueLingshang    |   18   | F      |

| YiLin         |   19   | F      |

+--------------+------+--------+

3 rows in set (0.00 sec)

使用between...and 条件语句

mysql> SELECT Name,Age,Gender FROM students WHERE Age BETWEEN 20 AND 25;

+-------------+------+--------+

| Name        | Age  | Gender |

+-------------+------+--------+

| DingDian     |   25 | M      |

| ZhangWuji    |   20 | M      |

| LingHuchong   |   22 | M      |

+-------------+------+--------+

使用IN条件语句：

mysql> SELECT Name,Age,Gender FROM students WHERE Age IN (18,20,25);

+--------------+------+--------+

| Name         | Age  | Gender |

+--------------+------+--------+

| DingDian       |   25 | M      |

| YueLingshang    |   18 | F      |

| ZhangWuji      |   20 | M      |

+--------------+------+--------+

查询字符为空

mysql> SELECT Name,Age,CID2 from students WHERE CID2 IS NULL;

+-------------+------+------+

| Name        | Age  | CID2 |

+-------------+------+------+

| LingHuchong   |   22  | NULL |

| YiLin       |   19  | NULL |

+-------------+------+------+

使用ORDER BY 排序；

mysql> SELECT Name,Age,CID2 from students WHERE CID2 IS NOT NULL ORDER BY Name;

+--------------+------+------+

| Name         | Age  | CID2 |

+--------------+------+------+

| DingDian      |   25 |    1 |

| GuoJing       |   19 |    7 |

| HuangRong      |   16 |    9 |

| HuFei         |   31 |   10 |

| Xuzhu         |   26 |    4 |

| YangGuo        |   17 |    3 |

| YueLingshang    |   18 |    4 |

| ZhangWuji      |   20 |    7 |

+--------------+------+------+

使用like语句

mysql> SELECT Name,Age FROM students WHERE Name LIKE 'Y%';

+--------------+------+

| Name         | Age  |

+--------------+------+

| YangGuo      |   17 |

| YueLingshang   |   18 |

| YiLin        |   19 |

+--------------+------+

用AS取别名，用LIMIT取前2行

mysql> SELECT Name AS Student_name,Age FROM students LIMIT 2;

+--------------+------+

| Student_name | Age  |

+--------------+------+

| GuoJing      |   19 |

| YangGuo      |   17 |

+--------------+------+

LIMIT 2,3略过前2行，

mysql> SELECT Name AS Student_name,Age FROM students LIMIT 2,3;

+--------------+------+

| Student_name | Age  |

+--------------+------+

| DingDian      |   25 |

| HuFei        |   31 |

| HuangRong      |   16 |

使用聚合运算 SUN()、AVG()、 MAX()、 MIN() 、COUNT()

mysql> SELECT AVG(Age) FROM students;

+----------+

| AVG(Age) |

+----------+

|  21.3000 |

+----------+

mysql> SELECT MAX(Age) FROM students;

+----------+

| MAX(Age) |

+----------+

|       31 |

+----------+

mysql> SELECT MIN(Age) FROM students;

+----------+

| MIN(Age) |

+----------+

|       16 |

+----------+

mysql> SELECT COUNT(Age) FROM students;

+------------+

| COUNT(Age) |

+------------+

|         10 |

+------------+

mysql> SELECT SUM(Age) FROM students;

+----------+

| SUM(Age) |

+----------+

|      213 |

+----------+

GROUP BY 分组；

mysql> SELECT COUNT(CID1),CID1 FROM students GROUP BY CID1;

+-------------+------+

| COUNT(CID1) | CID1 |

+-------------+------+

|           1 |    1 |

|           3 |    2 |

|           1 |    5 |

|           1 |    6 |

|           2 |    8 |

|           1 |    11 |

|           1 |    18 |

+-------------+------+

GROUP BY 和HAVING 条件语句

mysql> SELECT COUNT(CID1) AS Person,CID1 FROM students GROUP BY CID1 HAVING Person>=2;

+--------+------+

| Person   | CID1 |

+--------+------+

|      3 |    2 |

|      2 |    8 |

+--------+------+

多表查询

mysql> SELECT * FROM students,courses WHERE students.CID1=courses.CID;

+-----+--------------+------+--------+------+------+------+---------------------+-----+-------------------+-----+

| SID | Name         | Age  | Gender | CID1 | CID2 | TID  | CreateTime          | CID | Cname             | TID |

+-----+--------------+------+--------+------+------+------+---------------------+-----+-------------------+-----+

|   1 | GuoJing       |   19  | M      |     2 |    7 |    3 | 2012-04-06 10:00:00 |   2 | TaiJiquan         |   3 |

|   2 | YangGuo       |   17  | M      |     2 |    3 |    1 | 0000-00-00 00:00:00 |   2 | TaiJiquan         |   3 |

|   3 | DingDian      |   25  | M      |     6 |    1 |    7 | 2012-04-06 10:00:00 |   6 | Qishangquan       |   5 |

|   4 | HuFei        |   31  | M      |     8 |   10 |    5 | 2012-04-06 10:00:00 |   8 | Wanliduxing       |   8 |

|   5 | HuangRong      |   16  | F      |    5 |    9 |    9 | 2012-04-06 10:00:00 |   5 | Qianzhuwandushou   |   4 |

|   6 | YueLingshang     |   18  | F      |    8 |    4 | NULL | 2012-04-06 10:00:00 |   8 | Wanliduxing       |   8 |

|   7 | ZhangWuji      |   20  | M      |    1 |    7 | NULL | 2012-04-06 10:00:00 |   1 | Hamagong          |   2 |

|   8 | Xuzhu         |   26  | M      |    2 |    4 | NULL | 2012-04-06 10:00:00 |   2 | TaiJiquan          |   3 |

+-----+--------------+------+--------+------+------+------+---------------------+-----+-------------------+-----+

mysql> SELECT students.Name,courses.Cname FROM students,courses WHERE students.CID1=courses.CID;

+--------------+-------------------+

| Name         | Cname             |

+--------------+-------------------+

| GuoJing       | TaiJiquan         |

| YangGuo       | TaiJiquan         |

| DingDian       | Qishangquan       |

| HuFei         | Wanliduxing       |

| HuangRong      | Qianzhuwandushou     |

| YueLingshang    | Wanliduxing       |

| ZhangWuji      | Hamagong          |

| Xuzhu         | TaiJiquan         |

+--------------+-------------------+

JOIN LEFT 左连接

mysql> SELECT s.SID,s.Name,c.CID,c.Cname FROM students AS s LEFT JOIN courses AS c ON s.CID1=c.CID;

+-----+--------------+------+-------------------+

| SID | Name         | CID  | Cname             |

+-----+--------------+------+-------------------+

|   1 | GuoJing      |    2 | TaiJiquan         |

|   2 | YangGuo      |    2 | TaiJiquan         |

|   3 | DingDian      |    6 | Qishangquan       |

|   4 | HuFei        |    8 | Wanliduxing       |

|   5 | HuangRong      |    5 | Qianzhuwandushou   |

|   6 | YueLingshang    |    8 | Wanliduxing       |

|   7 | ZhangWuji      |    1 | Hamagong          |

|   8 | Xuzhu         |    2 | TaiJiquan         |

|   9 | LingHuchong     |   NULL | NULL              |

|  10 | YiLin         |   NULL | NULL              |

+-----+--------------+------+-------------------+

子查询

mysql> SELECT Name,Age FROM students WHERE Age >(SELECT AVG(Age) FROM students);

+-------------+------+

| Name        | Age  |

+-------------+------+

| DingDian    |   25 |

| HuFei       |   31 |

| Xuzhu       |   26 |

| LingHuchong |   22 |

显示老师和学生年龄一样，用IN查询

mysql> SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM tutors);

Empty set (0.00 sec)

UNION 联合

mysql> (SELECT Name,Age FROM students) UNION (SELECT Tname,Age FROM tutors);

+--------------+------+

| Name         | Age  |

+--------------+------+

| GuoJing      |   19 |

| YangGuo      |   17 |

| DingDian     |   25 |

| HuFei        |   31 |

| HuangRong    |   16 |

| YueLingshang |   18 |

| ZhangWuji    |   20 |

| Xuzhu        |   26 |

| LingHuchong  |   22 |

| YiLin        |   19 |

| HongQigong   |   93 |

| HuangYaoshi  |   63 |

| Miejueshitai |   72 |

| OuYangfeng   |   76 |

| YiDeng       |   90 |

| YuCanghai    |   56 |

| Jinlunfawang |   67 |

| HuYidao      |   42 |

| NingZhongze  |   49 |

+--------------+------+