单表查询

创建表student，创建student表的SQL语句如下所示
mysql> CREATE TABLE student
-> (
-> id INT(5) PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(20) NOT NULL,
-> grade FLOAT,
-> gender CHAR(2)
-> );
Query OK, 0 rows affected

执行SQL语句创建student表，然后使用INSERT语句向student表中插入8条记录，INSERT语句如下所示：
mysql> INSERT INTO student(name,grade,gender)
-> VALUES('songjiang',40,'男'),
-> ('wuyong',41,'男'),
-> ('qinming',45,'男'),
-> ('husanniang',38,'女'),
-> ('sunerniang',35,'女'),
-> ('wusong',41,'男')，
-> ('lincong',43,'男'),
-> ('yanqing',50,NULL);
Query OK, 8 rows affected
Records: 8 Duplicates: 0 Warnings: 0

一、简单查询
INSERT语句执行成功后，接下来通过SELECT语句查询student表中的记录，SQL语句如下所示：
mysql> SELECT id,name,gender FROM student;
+----+------------+--------+
| id | name | gender |
+----+------------+--------+
| 1 | songjiang | 男 |
| 2 | wuyong | 男 |
| 3 | qinming | 男 |
| 4 | husanniang | 女 |
| 5 | sunerniang | 女 |
| 6 | wusong | 男 |
| 7 | lincong | 男 |
| 8 | yanqing | NULL |
+----+------------+--------+
8 rows in set

查询所有字段中，在SELECT语句中使用星号(“*”)通配符查询student表中的所有字段，SQL语句如下所示：
mysql> SELECT * FROM student;
+----+------------+-------+--------+
| id | name | grade | gender |
+----+------------+-------+--------+
| 1 | songjiang | 40 | 男 |
| 2 | wuyong | 41 | 男 |
| 3 | qinming | 45 | 男 |
| 4 | husanniang | 38 | 女 |
| 5 | sunerniang | 35 | 女 |
| 6 | wusong | 41 | 男 |
| 7 | lincong | 43 | 男 |
| 8 | yanqing | 50 | NULL |
+----+------------+-------+--------+
8 rows in set

二、按条件查询
查询student表中id为4的学生姓名，SQL语句如下所示：
mysql> SELECT id,name FROM student WHERE id=4;
+----+------------+
| id | name |
+----+------------+
| 4 | husanniang |
+----+------------+
1 row in set

IN关键字用于判断某个字段的值是否在指定集合中，如果字段的值在集合中，则满足条件，该字段所在的记录将被查询出来。
mysql> SELECT id,grade,name,gender FROM student WHERE id IN(1,2,3);

+----+-------+-----------+--------+
| id | grade | name | gender |
+----+-------+-----------+--------+
| 1 | 40 | songjiang | 男 |
| 2 | 41 | wuyong | 男 |
| 3 | 45 | qinming | 男 |
+----+-------+-----------+--------+
3 rows in set

SELECT id,grade,name,gender FROM student WHERE id NOT IN(1,2,3);
//NOT是可选参数，使用NOT表示查询不在IN关键字指定集合范围中的记录。

+----+-------+------------+--------+
| id | grade | name | gender |
+----+-------+------------+--------+
| 4 | 38 | husanniang | 女 |
| 5 | 35 | sunerniang | 女 |
| 6 | 41 | wusong | 男 |
| 7 | 43 | lincong | 男 |
| 8 | 50 | yanqing | NULL |
+----+-------+------------+--------+
5 rows in set

带BETWEEN AND关键字的查询BETWEEN AND用于判断某个字段的值是否在指定的范围之内，如果字段的值在指定范围内，则满足条件，该字段所在的记录将被查询出来，反之则不会被查询出来。
查询student表中id值在2和5之间的学生姓名，SQL语句如下所示：
mysql> SELECT id,name FROM student WHERE id BETWEEN 2 AND 5;

+----+------------+
| id | name |
+----+------------+
| 2 | wuyong |
| 3 | qinming |
| 4 | husanniang |
| 5 | sunerniang |
+----+------------+
4 rows in set
不在2和5之间的：
mysql> SELECT id,name FROM student WHERE id NOT BETWEEN 2 AND 5;

+----+-----------+
| id | name |
+----+-----------+
| 1 | songjiang |
| 6 | wusong |
| 7 | lincong |
| 8 | yanqing |
+----+-----------+
4 rows in set

空值查询在数据表中，某些列的值可能为空值(NULL)，空值不同于0，也不同于空字符串。在MySQL中，使用IS NULL关键字来判断字段的值是否为空值。
查询student表中gender为空值的记录，SQL语句如下所示：

mysql> SELECT id,name,grade,gender FROM student WHERE gender IS NULL;

+----+---------+-------+--------+
| id | name | grade | gender |
+----+---------+-------+--------+
| 8 | yanqing | 50 | NULL |
+----+---------+-------+--------+
1 row in set

gender不为空值的：
mysql> SELECT id,name,grade,gender FROM student WHERE gender IS NOT NULL;

+----+------------+-------+--------+
| id | name | grade | gender |
+----+------------+-------+--------+
| 1 | songjiang | 40 | 男 |
| 2 | wuyong | 41 | 男 |
| 3 | qinming | 45 | 男 |
| 4 | husanniang | 38 | 女 |
| 5 | sunerniang | 35 | 女 |
| 6 | wusong | 41 | 男 |
| 7 | lincong | 43 | 男 |
+----+------------+-------+--------+
7 rows in set

带DISTINCT关键字的查询在SELECT语句中，可以使用DISTINCT关键字来去掉查询记录中重复的值。查询student表中gender字段的值，查询记录不能重复，SQL语句如下所示：

mysql> SELECT DISTINCT gender FROM student;

+--------+
| gender |
+--------+
| 男 |
| 女 |
| NULL |
+--------+
3 rows in set

DISTINCT关键字作用多个字段
查询student表中的gender和name字段，使用distinct关键字作用于这两个字段，SQL语句如下所示：
mysql> SELECT DISTINCT gender,name FROM student;

+--------+------------+
| gender | name |
+--------+------------+
| 男 | songjiang |
| 男 | wuyong |
| 男 | qinming |
| 女 | husanniang |
| 女 | sunerniang |
| 男 | wusong |
| 男 | lincong |
| NULL | yanqing |
+--------+------------+
8 rows in set
从查询结果可以看到，返回的记录中gender字段仍然出现了重复值，这是因为DISTINCT关键字作用于gender和name两个字段，只有这两个字段的值都相同才被认为是重复记录。
为了能够演示去除多个字段重复的效果，向student表中添加一条新记录，SQL语句如下所示：
mysql> INSERT INTO student(name,grade,gender) VALUES('songjiang',20,'男');
Query OK, 1 row affected

执行完INSERT语句后，使用SELECT语句查询 student表中的所有记录，执行结果如下所示：

mysql> SELECT * FROM student;
+----+------------+-------+--------+
| id | name | grade | gender |
+----+------------+-------+--------+
| 1 | songjiang | 40 | 男 |
| 2 | wuyong | 41 | 男 |
| 3 | qinming | 45 | 男 |
| 4 | husanniang | 38 | 女 |
| 5 | sunerniang | 35 | 女 |
| 6 | wusong | 41 | 男 |
| 7 | lincong | 43 | 男 |
| 8 | yanqing | 50 | NULL |
| 9 | songjiang | 20 | 男 |
+----+------------+-------+--------+
9 rows in set
从查询结果可以看到，student表中一共有9条记录，并且第1条记录、第9条记录的name字段和gender字段值相等，分别为“songjiang”和“男”。

接下来再次查询gender和name字段，并使用distinct作用这两个字段，执行结果如下所示：
mysql> SELECT DISTINCT gender,name FROM student;

带LIKE关键字的查询
MySQL中提供了LIKE关键字，用于对字符串进行模糊查询。（LIKE之前可以使用NOT关键字，用来查询与指定通配字符串不匹配的记录。）

（1）百分号(%)通配符 ——可以匹配任意长度的字符串，包括空字符串。
查找student表中name字段值以字符“s”开头的学生id，SQL语句如下所示：
mysql> SELECT id,name FROM student WHERE name LIKE "s%";

+----+------------+
| id | name |
+----+------------+
| 1 | songjiang |
| 5 | sunerniang |
| 9 | songjiang |
+----+------------+
3 rows in set

百分号通配符可以出现在通配字符串的任意位置。查询student表中name字段值以字符“w”开始，以字符“g”结束的学生id，执行结果如下所示：
mysql> SELECT id,name FROM student WHERE name LIKE "w%g";

+----+--------+
| id | name |
+----+--------+
| 2 | wuyong |
| 6 | wusong |
+----+--------+
2 rows in set

在通配字符串中可以出现多个百分号通配符。查询student表中name字段值包含字符“y”的学生id，执行结果如下所示：

mysql> SELECT id,name FROM student WHERE name LIKE "%y%";

+----+---------+
| id | name |
+----+---------+
| 2 | wuyong |
| 8 | yanqing |
+----+---------+
2 rows in set

（2）下划线(_)通配符 ——可以匹配任意长度的字符串，包括空字符串。

查询student表中name字段值以字符串“wu”开始，以字符串“ong”结束，并且两个字符串之间只有一个字符的记录，SQL语句如下所示：
mysql> SELECT * FROM student WHERE name LIKE "wu_ong";

+----+--------+-------+--------+
| id | name | grade | gender |
+----+--------+-------+--------+
| 2 | wuyong | 41 | 男 |
| 6 | wusong | 41 | 男 |
+----+--------+-------+--------+
2 rows in set

查询student表中name字段值包含7个字符，并且以字符串“ing”结束的记录，执行结果如下所示：
mysql> SELECT * FROM student WHERE name LIKE "____ing";

+----+---------+-------+--------+
| id | name | grade | gender |
+----+---------+-------+--------+
| 3 | qinming | 45 | 男 |
| 8 | yanqing | 50 | NULL |
+----+---------+-------+--------+
2 rows in set

使用百分号和下划线通配符进行查询操作
百分号和下划线是通配符，它们在通配字符串中有特殊含义，因此，如果要匹配字符串中的百分号和下划线，就需要在通配字符串中使用右斜线(“\”)对百分号和下划线进行转义，例如，“\%”匹配百分号字面值，“\_”匹配下划线字面值。

查询student表中name字段值包括“%”的记录。在查询之前，首先向student表中添加一条记录，执行结果如下所示：

mysql> INSERT INTO student(name,grade,gender)
-> VALUES('sun%er',60,'男');
Query OK, 1 row affected

从上面的执行语句中可以看到，添加的新记录其name字段值为“sun%er”，包含一个百分号字面值。接下来通过SELECT语句查出这条记录，执行结果如下所示：
mysql> SELECT * FROM student WHERE name LIKE "%\%%";

+----+--------+-------+--------+
| id | name | grade | gender |
+----+--------+-------+--------+
| 10 | sun%er | 60 | 男 |
+----+--------+-------+--------+
1 row in set
从查询结果可以看到，查出了name字段值为“sun%er”的新记录。

带AND关键字的多条件查询
使用AND关键字可以连接两个或者多个查询条件，只有满足所有条件的记录才会被返回。
查询student表中id字段值小于5，并且gender字段值为“女”的学生姓名，SQL语句如下所示：
mysql> SELECT id,name,gender FROM student WHERE id<5 AND gender="女";

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

| id | name | gender |
+----+------------+--------+
| 4 | husanniang | 女 |
+----+------------+--------+
1 row in set

查询student表中id字段值在1、2、3、4之中，name字段值以字符串“ng”结束，并且grade字段值小于80的记录，SQL语句如下所示：

（使用两个AND关键词链接了三个条件表达式）
mysql> SELECT id,name,gender FROM student WHERE id in(1,2,3,4) AND name LIKE "%ng" AND grade<41;

+----+------------+--------+
| id | name | gender |
+----+------------+--------+
| 1 | songjiang | 男 |
| 4 | husanniang | 女 |
+----+------------+--------+
2 rows in set

带OR关键字的多条件查询
使用OR关键字连接多个查询条件，只要记录满足任意一个条件就会被查询出来。
查询student表中id字段值小于3或者gender字段值为“女”的学生姓名，SQL语句及其执行结果如下所示：

mysql> SELECT id,name,gender FROM student WHERE id<3 OR gender="女";
+----+------------+--------+
| id | name | gender |
+----+------------+--------+
| 1 | songjiang | 男 |
| 2 | wuyong | 男 |
| 4 | husanniang | 女 |
| 5 | sunerniang | 女 |
+----+------------+--------+
4 rows in set

查询student表中满足条件name字段值以字符“h”开始，或者gender字段值为“女”，或者grade字段值为100的记录，SQL语句及其执行结果如下所示：
mysql> SELECT id,name,grade,gender FROM student WHERE name LIKE "h%" OR gender="女" OR grade=40;
+----+------------+-------+--------+
| id | name | grade | gender |
+----+------------+-------+--------+
| 1 | songjiang | 40 | 男 |
| 4 | husanniang | 38 | 女 |
| 5 | sunerniang | 35 | 女 |
+----+------------+-------+--------+
3 rows in set

注意：OR和AND关键字一起使用时， AND的优先级高于OR，因此当两者在一起使用时，应该先运算AND两边的条件表达式，再运算OR两边的条件表达式。
三、高级查询
（1）聚合函数
1、1、COUNT()函数——用来统计记录的条数
查询student表中一共有多少条记录，SQL语句及其执行结果如下所示：
mysql> SELECT COUNT(*) FROM student;
+----------+
| COUNT(*) |
+----------+
| 10 |
+----------+
1 row in set

2、SUM()函数 ——求和函数，用于求出表中某个字段所有值的总和
求student表中grade字段的总和，SQL语句及其执行结果如下所示：
mysql> SELECT SUM(grade) FROM student;
+------------+
| SUM(grade) |
+------------+
| 413 |
+------------+
1 row in set

3、AVG()函数——用于求出某个字段所有值的平均值
求出student表中grade字段的平均值，SQL语句及其执行结果如下所示：
mysql> SELECT AVG(grade) FROM student;
+------------+
| AVG(grade) |
+------------+
| 41.3 |
+------------+
1 row in set

4、MAX()函数——用于求出某个字段的最大值
求出student表中所有学生grade字段的最大值，SQL语句及其执行结果如下所示：
mysql> SELECT MAX(grade) FROM student;
+------------+
| MAX(grade) |
+------------+
| 60 |
+------------+
1 row in set

5、MIN()函数——用于求出某个字段的最小值。

求出student表中grade字段的最小值，SQL语句如下所示：

mysql> SELECT MIN(grade) FROM student;
+------------+
| MIN(grade) |
+------------+
| 20 |
+------------+
1 row in set

（2）对查询结果排序使用ORDER BY——对查询结果进行排序。

参数ASC表示按照升序进行排序，DESC表示按照降序进行排序。默认情况下，按照ASC方式进行排序。查出student表中的所有记录，并按照grade字段进行排序，SQL语句及其执行结果如下所示：

mysql> SELECT * FROM student ORDER BY grade;//此处默认为ASC排序

+----+------------+-------+--------+
| id | name | grade | gender |
+----+------------+-------+--------+
| 9 | songjiang | 20 | 男 |
| 5 | sunerniang | 35 | 女 |
| 4 | husanniang | 38 | 女 |
| 1 | songjiang | 40 | 男 |
| 6 | wusong | 41 | 男 |
| 2 | wuyong | 41 | 男 |
| 7 | lincong | 43 | 男 |
| 3 | qinming | 45 | 男 |
| 8 | yanqing | 50 | NULL |
| 10 | sun%er | 60 | 男 |
+----+------------+-------+--------+
10 rows in set

运用DESC（降序）排序方法
mysql> SELECT * FROM student ORDER BY grade DESC;

+----+------------+-------+--------+
| id | name | grade | gender |
+----+------------+-------+--------+
| 10 | sun%er | 60 | 男 |
| 8 | yanqing | 50 | NULL |
| 3 | qinming | 45 | 男 |
| 7 | lincong | 43 | 男 |
| 6 | wusong | 41 | 男 |
| 2 | wuyong | 41 | 男 |
| 1 | songjiang | 40 | 男 |
| 4 | husanniang | 38 | 女 |
| 5 | sunerniang | 35 | 女 |
| 9 | songjiang | 20 | 男 |
+----+------------+-------+--------+
10 rows in set

查询student表中的所有记录，按照gender字段的升序和grade字段的降序进行排列，SQL语句及其执行结果如下所示：
mysql> SELECT * FROM student ORDER BY grade DESC,gender ASC;

(3)分组查询
使用GROUP BY按某个字段或者多个字段中的值进行分组，字段中值相同的为一组。

1、单独使用GROUP BY分组——查询的是每个分组中的一条记录。
查询student表中的记录，按照gender字段值进行分组，SQL语句及其执行结果如下所示：
mysql> SELECT * FROM student GROUP BY gender;

+----+------------+-------+--------+
| id | name | grade | gender |
+----+------------+-------+--------+
| 8 | yanqing | 50 | NULL |
| 1 | songjiang | 40 | 男 |
| 4 | husanniang | 38 | 女 |
+----+------------+-------+--------+
3 rows in set

2、GROUP BY和聚合函数一起使用
可以统计出某个或者某些字段在一个分组中的最大值、最小值、平均值等等。将student表按照gender进行分组查询，计算出每个分组中各有多少名学生，SQL语句及其执行结果如下所示：
mysql> SELECT COUNT(*),gender FROM student GROUP BY gender;

+----------+--------+
| COUNT(*) | gender |
+----------+--------+
| 1 | NULL |
| 7 | 男 |
| 2 | 女 |
+----------+--------+
3 rows in set

3、GROUP BY和HAVING关键字一起使用
HAVING关键字和WHERE关键字的作用相同，都用于设置条件表达式对查询结果进行过滤。

HAVING关键字和WHERE关键字的区别在于，HAVING关键字后可以跟聚合函数，而WHERE关键字不能。通常情况下HAVING关键字都和GROUP BY一起使用，用于对分组后的结果进行过滤。
将student表按照gender字段进行分组查询，查询出grade字段值之和小于200的分组，SQL语句及其执行结果如下所示：

mysql> SELECT sum(grade),gender FROM student GROUP BY gender HAVING SUM(grade)<200;
+------------+--------+
| sum(grade) | gender |
+------------+--------+
| 50 | NULL |
| 73 | 女 |
+------------+--------+
2 rows in set

（4）使用LIMIT限制查询结果的数量可以指定查询结果从哪一条记录开始以及一共查询多少条信息
查询student表中的前4条记录，SQL语句及其执行结果如下所示：
mysql> SELECT * FROM student LIMIT 4;+----+------------+-------+--------+
| id | name | grade | gender |
+----+------------+-------+--------+
| 1 | songjiang | 40 | 男 |
| 2 | wuyong | 41 | 男 |
| 3 | qinming | 45 | 男 |
| 4 | husanniang | 38 | 女 |
+----+------------+-------+--------+
4 rows in set

查询student表中grade字段值从第5位到第8位的学生(从高到低)
mysql> SELECT * FROM student LIMIT 4,4;
+----+------------+-------+--------+
| id | name | grade | gender |
+----+------------+-------+--------+
| 5 | sunerniang | 35 | 女 |
| 6 | wusong | 41 | 男 |
| 7 | lincong | 43 | 男 |
| 8 | yanqing | 50 | NULL |
+----+------------+-------+--------+
4 rows in set

（5）函数（如图）

四、为表取别名
为student表起一个别名s，并查询student表中gender字段值为“女”的记录，SQL语句及其执行结果如下所示：
mysql> SELECT * FROM student AS s WHERE s.gender="女";

+----+------------+-------+--------+
| id | name | grade | gender |
+----+------------+-------+--------+
| 4 | husanniang | 38 | 女 |
| 5 | sunerniang | 35 | 女 |
+----+------------+-------+--------+
2 rows in set

为字段取别名
查询student表中的所有记录的name和gender字段值，并为这两个字段其别名stu_name和stu_gender，SQL语句及其执行结果如下所示：
mysql> SELECT name AS stu_name,gender stu_gender FROM student;

+------------+------------+
| stu_name | stu_gender |
+------------+------------+
| songjiang | 男 |
| wuyong | 男 |
| qinming | 男 |
| husanniang | 女 |
| sunerniang | 女 |
| wusong | 男 |
| lincong | 男 |
| yanqing | NULL |
| songjiang | 男 |
| sun%er | 男 |
+------------+------------+
10 rows in set

posted @ 2019-11-20 16:49 余笙1035 阅读(789) 评论(0) 收藏举报

刷新页面返回顶部

余笙1035

单表查询

公告