单表查询

1：简单查询

SELECT语句

 

 1.1：查询所有字段

 

 eg1：查询student表中的所有的记录

创建一个student数据表

mysql> USE itcast;
Database changed

mysql> CREATE TABLE student(id INT(3) PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20) NOT NULL,grade FLOAT,gender CHAR(2));
Query OK, 0 rows affected

用INSERT语句插入数据

mysql> INSERT INTO student(name,grade,gender) VALUES('wangwu',40,'na'),('zhangsan',100,'nv'),('qingming',90,'na'),('yuying',89,'na'),('zhansa',60,'nv'),('youqian',77,'na');
Query OK, 6 rows affected
Records: 6  Duplicates: 0  Warnings: 4

查询数据

mysql> SELECT id,name,grade,gender FROM student;

+----+----------+-------+--------+
| id | name     | grade | gender |
+----+----------+-------+--------+
|  1 | wangwu   |    40 | na     |
|  2 | zhangsan |   100 | nv     |
|  3 | qingming |    90 | na     |
|  4 | yuying   |    89 | na     |
|  5 | zhansa   |    60 | nv     |
|  6 | youqian  |    77 | na     |
+----+----------+-------+--------+
6 rows in set

 

 例如：把name换到最后

mysql> SELECT id,grade,gender,name FROM student;

+----+-------+--------+----------+
| id | grade | gender | name     |
+----+-------+--------+----------+
|  1 |    40 | na     | wangwu   |
|  2 |   100 | nv     | zhangsan |
|  3 |    90 | na     | qingming |
|  4 |    89 | na     | yuying   |
|  5 |    60 | nv     | zhansa   |
|  6 |    77 | na     | youqian  |
+----+-------+--------+----------+
6 rows in set

 

 

 

 例如：用SELECT *FROM 查询student表中的数据

mysql> SELECT * FROM student;

+----+----------+-------+--------+
| id | name     | grade | gender |
+----+----------+-------+--------+
|  1 | wangwu   |    40 | na     |
|  2 | zhangsan |   100 | nv     |
|  3 | qingming |    90 | na     |
|  4 | yuying   |    89 | na     |
|  5 | zhansa   |    60 | nv     |
|  6 | youqian  |    77 | na     |
+----+----------+-------+--------+
6 rows in set

 1.2：查询指定字段

 

 eg2：

 

 查询结果

mysql> SELECT name,gender FROM student;
+----------+--------+
| name     | gender |
+----------+--------+
| wangwu   | na     |
| zhangsan | nv     |
| qingming | na     |
| yuying   | na     |
| zhansa   | nv     |
| youqian  | na     |
+----------+--------+
6 rows in set

注意：改变字段位置，所显示的结果位置也会改变，

例如：改变name和gender的位置

mysql> SELECT gender,name FROM student;

+--------+----------+
| gender | name     |
+--------+----------+
| na     | wangwu   |
| nv     | zhangsan |
| na     | qingming |
| na     | yuying   |
| nv     | zhansa   |
| na     | youqian  |
+--------+----------+
6 rows in set

2：按条件查询

2.1：关系运算符的查询

 

 常见关系运算符有：

 

 例如：查询student表中id=4的学生的姓名

mysql> SELECT id,name FROM student WHERE id=4;

+----+--------+
| id | name   |
+----+--------+
|  4 | yuying |
+----+--------+
1 row in set

例如：查询name为“wangwu”的学生的性别

mysql> SELECT name,gender FROM student WHERE name='wangwu';

+--------+--------+
| name   | gender |
+--------+--------+
| wangwu | na     |
+--------+--------+
1 row in set

例如：查询表中grade大于80的学生姓名

mysql> SELECT name FROM student WHERE grade>80;

+----------+
| name     |
+----------+
| zhangsan |
| qingming |
| yuying   |
+----------+
3 rows in set

2.2：带IN关键字的查询

 

 

例如：查询表中id为1 2 3的记录

mysql> SELECT id,grade,name,gender FROM student WHERE id IN(1,2,3);

+----+-------+----------+--------+
| id | grade | name     | gender |
+----+-------+----------+--------+
|  1 |    40 | wangwu   | na     |
|  2 |   100 | zhangsan | nv     |
|  3 |    90 | qingming | na     |
+----+-------+----------+--------+
3 rows in set

例如：查询表中id不为1 2 3的记录

mysql> SELECT id,grade,name,gender FROM student WHERE id NOT IN(1,2,3);

+----+-------+---------+--------+
| id | grade | name    | gender |
+----+-------+---------+--------+
|  4 |    89 | yuying  | na     |
|  5 |    60 | zhansa  | nv     |
|  6 |    77 | youqian | na     |
+----+-------+---------+--------+
3 rows in set

2.3：带BETWEEN　AND关键字的查询

 

 

 例如：查询表中ｉｄ在２－５之间的学生姓名

mysql> SELECT id,name FROM student WHERE id BETWEEN 2 AND 5;

+----+----------+
| id | name     |
+----+----------+
|  2 | zhangsan |
|  3 | qingming |
|  4 | yuying   |
|  5 | zhansa   |
+----+----------+
4 rows in set

 

 例如：查询表中ｉｄ不在２－５之间的学生姓名

mysql> SELECT id,name FROM student WHERE id NOT BETWEEN 2 AND 5;

+----+---------+
| id | name    |
+----+---------+
|  1 | wangwu  |
|  6 | youqian |
+----+---------+
2 rows in set

 

 

2.4：空值查询 

语法格式如下：

 例如：

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

Empty set

例如：

SELECT id,name,grade,gender FROM student WHERE gender is NOT NULL;
+----+----------+-------+--------+
| id | name     | grade | gender |
+----+----------+-------+--------+
|  1 | wangwu   |    40 | na     |
|  2 | zhangsan |   100 | nv     |
|  3 | qingming |    90 | na     |
|  4 | yuying   |    89 | na     |
|  5 | zhansa   |    60 | nv     |
|  6 | youqian  |    77 | na     |
+----+----------+-------+--------+
6 rows in set

2.5：带DISTINCT关键字的查询

 语法格式如下：

 例如：

 mysql> SELECT DISTINCT gender FROM student;
+--------+
| gender |
+--------+
| na     |
| nv     |
+--------+
2 rows in set

从查询结果上来看，返回了两条记录的gender值，分别为“na”、“nv”，不再有重复值

 

 

 mysql> SELECT DISTINCT gender ,grade FROM student;
+--------+-------+
| gender | grade |
+--------+-------+
| na     |    40 |
| nv     |   100 |
| na     |    90 |
| na     |    89 |
| nv     |    60 |
| na     |    77 |
+--------+-------+
6 rows in set

 为了可以演示，添加一条记录

mysql> INSERT INTO student(name,grade,gender) VALUES('changjiang',100,'nv');
Query OK, 1 row affected

检查是是否添加成功 

mysql> SELECT * FROM student;
+----+------------+-------+--------+
| id | name       | grade | gender |
+----+------------+-------+--------+
|  1 | wangwu     |    40 | na     |
|  2 | zhangsan   |   100 | nv     |
|  3 | qingming   |    90 | na     |
|  4 | yuying     |    89 | na     |
|  5 | zhansa     |    60 | nv     |
|  6 | youqian    |    77 | na     |
|  7 | changjiang |   100 | nv     |
+----+------------+-------+--------+
7 rows in set

演示删除多个字段重复效果 

 mysql> SELECT DISTINCT grade,gender FROM student;
+-------+--------+
| grade | gender |
+-------+--------+
|    40 | na     |
|   100 | nv     |
|    90 | na     |
|    89 | na     |
|    60 | nv     |
|    77 | na     |
+-------+--------+
6 rows in set

2.6：带 LIKE关键字的查询  

 格式如下：

 注意：

 2.6.1：通配符

2.6.1.1：百分号（%）通配符

 mysql> SELECT id, name FROM student WHERE name LIKE "Z%";
+----+----------+
| id | name     |
+----+----------+
|  2 | zhangsan |
|  5 | zhansa   |
+----+----------+
2 rows in set

 mysql> SELECT id, name FROM student WHERE name LIKE "w%g";
Empty set

查询student表中name字段含“a”的学生id

mysql> SELECT id, name FROM student WHERE name LIKE "%a%";
+----+------------+
| id | name       |
+----+------------+
|  1 | wangwu     |
|  2 | zhangsan   |
|  5 | zhansa     |
|  6 | youqian    |
|  7 | changjiang |
+----+------------+
5 rows in set

例如：

 

mysql> SELECT id, name FROM student WHERE name NOT LIKE "%y%";
+----+------------+
| id | name       |
+----+------------+
|  1 | wangwu     |
|  2 | zhangsan   |
|  3 | qingming   |
|  5 | zhansa     |
|  7 | changjiang |
+----+------------+
5 rows in set

2.6.1.2：下划线 （_）通配符

mysql> SELECT * FROM student WHERE name LIKE 'wu_ong';

Empty set

mysql> SELECT * FROM student WHERE name LIKE '____ing';

Empty set

 2.6.1.3：使用百分号和下划线通配符

 添加数据记录

mysql> INSERT INTO student(name,grade,gender) VALUES("sun%er",95,"na");
Query OK, 1 row affected

 mysql> SELECT *FROM student  WHERE name LIKE '%\%%';
+----+--------+-------+--------+
| id | name   | grade | gender |
+----+--------+-------+--------+
|  8 | sun%er |    95 | na     |
+----+--------+-------+--------+
1 row in set

2.7:带AND关键字的多条件查询

 语法格式：

例如：

 

 mysql> SELECT id,name,gender FROM student WHERE id<5 AND gender='nv';
+----+----------+--------+
| id | name     | gender |
+----+----------+--------+
|  2 | zhangsan | nv     |
+----+----------+--------+
1 row in set

例如：

 mysql> SELECT id,name,grade,gender FROM student WHERE id in(1,2,3,4)AND name LIKE '%ng' AND grade<80;
Empty set

2.8：带OR关键字的多条件查询

 语法格式如下：

 例如：

 mysql> SELECT id,name,gender FROM student WHERE  id<3 OR gender='nv';
+----+------------+--------+
| id | name       | gender |
+----+------------+--------+
|  1 | wangwu     | na     |
|  2 | zhangsan   | nv     |
|  5 | zhansa     | nv     |
|  7 | changjiang | nv     |
+----+------------+--------+
4 rows in set

 例如：

 mysql> SELECT id,name,gender FROM student WHERE  name LIKE 'h%'<3 OR gender='nv' OR grade=100;
+----+------------+--------+
| id | name       | gender |
+----+------------+--------+
|  1 | wangwu     | na     |
|  2 | zhangsan   | nv     |
|  3 | qingming   | na     |
|  4 | yuying     | na     |
|  5 | zhansa     | nv     |
|  6 | youqian    | na     |
|  7 | changjiang | nv     |
|  8 | sun%er     | na     |
+----+------------+--------+
8 rows in set

OR和AND关键字一起使用的情况

 例如：

 mysql> SELECT name,gender,grade FROM student WHERE gender='nv' OR gender='na' AND grade=100;
+------------+--------+-------+
| name       | gender | grade |
+------------+--------+-------+
| zhangsan   | nv     |   100 |
| zhansa     | nv     |    60 |
| changjiang | nv     |   100 |
+------------+--------+-------+
3 rows in set

３：高级查询

３.１：聚合函数

 

 

 

３.１.１：count（）函数

用来统计记录的个数

语法如下：

 例如：

mysql> SELECT COUNT(*) FROM student;
 
+----------+
| COUNT(*) |
+----------+
|        8 |
+----------+
1 row in set

３．１.２：ｓｕｍ（）函数

 

 

 语法格式如下：

 

 例如：

 

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

３.１.３：AUG（）函数

 语法格式如下：

 例如：

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

３.１.４：MAX（）函数

 语法格式如下：

 例如：

 

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

３.１.５：ＭＩＮ（）函数

 语法格式如下：

 例如：

mysql> SELECT MIN(grade)FROM student;
+------------+
| MIN(grade) |
+------------+
|         40 |
+------------+

1 row in set

３.２：对查询结果排序

 语法格式如下：

 例如；

 mysql> SELECT *FROM student ORDER BY grade;
+----+------------+-------+--------+
| id | name       | grade | gender |
+----+------------+-------+--------+
|  1 | wangwu     |    40 | na     |
|  5 | zhansa     |    60 | nv     |
|  6 | youqian    |    77 | na     |
|  4 | yuying     |    89 | na     |
|  3 | qingming   |    90 | na     |
|  8 | sun%er     |    95 | na     |
|  2 | zhangsan   |   100 | nv     |
|  7 | changjiang |   100 | nv     |
+----+------------+-------+--------+
8 rows in set

注意：默认排序方式是升序排序

 例如：

 

 mysql> SELECT *FROM student ORDER BY grade DESC;
+----+------------+-------+--------+
| id | name       | grade | gender |
+----+------------+-------+--------+
|  2 | zhangsan   |   100 | nv     |
|  7 | changjiang |   100 | nv     |
|  8 | sun%er     |    95 | na     |
|  3 | qingming   |    90 | na     |
|  4 | yuying     |    89 | na     |
|  6 | youqian    |    77 | na     |
|  5 | zhansa     |    60 | nv     |
|  1 | wangwu     |    40 | na     |
+----+------------+-------+--------+
8 rows in set

例如：

 

 mysql> SELECT *FROM student ORDER BY gender ASC,grade DESC;
+----+------------+-------+--------+
| id | name       | grade | gender |
+----+------------+-------+--------+
|  8 | sun%er     |    95 | na     |
|  3 | qingming   |    90 | na     |
|  4 | yuying     |    89 | na     |
|  6 | youqian    |    77 | na     |
|  1 | wangwu     |    40 | na     |
|  2 | zhangsan   |   100 | nv     |
|  7 | changjiang |   100 | nv     |
|  5 | zhansa     |    60 | nv     |
+----+------------+-------+--------+
8 rows in set

３.３：分组查询

 语法格式如下：

 3.3.1：单独使用　GROUP　BY　分组

 

 例如：

 mysql> SELECT *FROM student GROUP BY gender;
+----+----------+-------+--------+
| id | name     | grade | gender |
+----+----------+-------+--------+
|  1 | wangwu   |    40 | na     |
|  2 | zhangsan |   100 | nv     |
+----+----------+-------+--------+
2 rows in set

 3.3.２：GROUP　BY　和聚合函数一起使用

 

 例如：

 mysql> SELECT COUNT(*),gender FROM student GROUP BY gender;
+----------+--------+
| COUNT(*) | gender |
+----------+--------+
|        5 | na     |
|        3 | nv     |
+----------+--------+
2 rows in set

３.３.２：GROUP BY和HAVING关键字一起使用

 

 例如：

 

 mysql> SELECT SUM(grade),gender FROM student GROUP BY gender HAVING SUM(grade)<300;
+------------+--------+
| SUM(grade) | gender |
+------------+--------+
|        260 | nv     |
+------------+--------+
1 row in set

３.４：使用LIMIT限制查询结果的数量

 语法格式如下：

 例如：

 

mysql> SELECT *FROM student LIMIT 4;
+----+----------+-------+--------+
| id | name     | grade | gender |
+----+----------+-------+--------+
|  1 | wangwu   |    40 | na     |
|  2 | zhangsan |   100 | nv     |
|  3 | qingming |    90 | na     |
|  4 | yuying   |    89 | na     |
+----+----------+-------+--------+
4 rows in set：

例如：

mysql> SELECT *FROM student ORDER BY grade DESC LIMIT 4,4;
+----+---------+-------+--------+
| id | name    | grade | gender |
+----+---------+-------+--------+
|  4 | yuying  |    89 | na     |
|  6 | youqian |    77 | na     |
|  5 | zhansa  |    60 | nv     |
|  1 | wangwu  |    40 | na     |
+----+---------+-------+--------+
4 rows in set

３.５：函数（列表）

 

 例如：

以ＣＯＮＣＡＴ（ｓ１，ｓ２，．．．）和IF（ｅｘｐｒ，ｖ１，ｖ２）为例

 

 mysql> SELECT  CONCAT(id,'_',name,'_',grade,'_',gender) FROM student ;
+------------------------------------------+
| CONCAT(id,'_',name,'_',grade,'_',gender) |
+------------------------------------------+
| 1_wangwu_40_na                           |
| 2_zhangsan_100_nv                        |
| 3_qingming_90_na                         |
| 4_yuying_89_na                           |
| 5_zhansa_60_nv                           |
| 6_youqian_77_na                          |
| 7_changjiang_100_nv                      |
| 8_sun%er_95_na                           |
+------------------------------------------+
8 rows in set

例如：

mysql> SELECT  id,IF(gender='na',1,0) FROM student ;
+----+---------------------+
| id | IF(gender='na',1,0) |
+----+---------------------+
|  1 |                   1 |
|  2 |                   0 |
|  3 |                   1 |
|  4 |                   1 |
|  5 |                   0 |
|  6 |                   1 |
|  7 |                   0 |
|  8 |                   1 |
+----+---------------------+
8 rows in set

 ４．１：为表取别名

 格式如下：

 例如：

 mysql> SELECT  *FROM student AS s WHERE s.gender='nv';
+----+------------+-------+--------+
| id | name       | grade | gender |
+----+------------+-------+--------+
|  2 | zhangsan   |   100 | nv     |
|  5 | zhansa     |    60 | nv     |
|  7 | changjiang |   100 | nv     |
+----+------------+-------+--------+
3 rows in set

４．２：为字段取别名

格式如下：

 例如：

 mysql> SELECT  name AS stu_gender FROM student;
+------------+
| stu_gender |
+------------+
| wangwu     |
| zhangsan   |
| qingming   |
| yuying     |
| zhansa     |
| youqian    |
| changjiang |
| sun%er     |
+------------+
8 rows in set