MySQL学习笔记4:操作数据表中的记录(增删改查)


插入记录(第1种方法)
INSERT [INTO] tbl_name [(col_name,……)] {VALUES|VALUE}({expr|DEFAULT},……),(),……
其中,列名称是可选的,若省略了列名称,表示要为所有的字段依次赋值
mysql> CREATE TABLE users( #创建数据表users
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL,
    -> password VARCHAR(32) NOT NULL,
    -> age TINYINT UNSIGNED NOT NULL DEFAULT 10,
    -> sex BOOLEAN
    -> );
Query OK, 0 rows affected (0.10 sec)

将NULL或DEFAULT赋给id列,表示id字段采用原来定义好的自动编号
mysql> INSERT INTO users VALUES(NULL,'tom','123',25,1);#将NULL赋给id列,表示id字段采用原来定义好的自动编号。
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO users VALUES(DEFAULT,'jerry','3423',25,0);#将DEFAULT赋给id列,表示id字段采用原来定义好的自动编号。
Query OK, 1 row affected (0.15 sec)

mysql> SELECT * FROM users;#可以看到,id字段采用的是自动编号。
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  1 | tom      | 123      |  25 |    1 |
|  2 | jerry    | 3423     |  25 |    0 |
+----+----------+----------+-----+------+
2 rows in set (0.00 sec)

mysql> INSERT INTO users VALUES(DEFAULT,'jerry','3423',25);#若省略了列名称,必须要为所有的字段依次赋值。
ERROR 1136 (21S01): Column count doesn't match value count at row 1

mysql> INSERT INTO users VALUES(DEFAULT,'jerry','3423',3*7-1,0);#将表达式 3*7-1 的结果赋给age字段。
Query OK, 1 row affected (0.13 sec)

mysql> SELECT * FROM users;
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  1 | tom      | 123      |  25 |    1 |
|  2 | jerry    | 3423     |  25 |    0 |
|  3 | jerry    | 3423     |  20 |    0 |
+----+----------+----------+-----+------+
3 rows in set (0.00 sec)

mysql> SHOW COLUMNS FROM users; #可以看到,age字段定义了默认值10
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)          | NO   |     | NULL    |                |
| password | varchar(32)          | NO   |     | NULL    |                |
| age      | tinyint(3) unsigned  | NO   |     | 10      |                |
| sex      | tinyint(1)           | YES  |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

mysql> INSERT INTO users VALUES(DEFAULT,'jerry','3423',DEFAULT,1);#将DEFAULT赋给age字段,表示age字段采用默认值10。
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM users;
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  1 | tom      | 123      |  25 |    1 |
|  2 | jerry    | 3423     |  25 |    0 |
|  3 | jerry    | 3423     |  20 |    0 |
|  4 | jerry    | 3423     |  10 |    1 |
+----+----------+----------+-----+------+
4 rows in set (0.00 sec)

mysql> INSERT users VALUES(DEFAULT,'marry','mima',23,1),(NULL,'rose','345',8,0);# 一次插入多条记录。
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM  users;
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  1 | tom      | 123      |  25 |    1 |
|  2 | jerry    | 3423     |  25 |    0 |
|  3 | jerry    | 3423     |  20 |    0 |
|  4 | jerry    | 3423     |  10 |    1 |
|  5 | marry    | mima     |  23 |    1 |
|  6 | rose     | 345      |   8 |    0 |
+----+----------+----------+-----+------+
6 rows in set (0.00 sec)

mysql> INSERT users VALUES(DEFAULT,'song',md5('mima'),23,0);#函数md5('mima') 表示计算字符串'mima'的md5值

Query OK, 1 row affected (0.14 sec)

mysql>SELECT * FROM  users;# 其中 3f572fcb0f9af03848738946954b8c43 就是字符串'mima'的md5值。

+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | tom      | 123                              |  25 |    1 |
|  2 | jerry    | 3423                             |  25 |    0 |
|  3 | jerry    | 3423                             |  20 |    0 |
|  4 | jerry    | 3423                             |  10 |    1 |
|  5 | marry    | mima                             |  23 |    1 |
|  6 | rose     | 345                              |   8 |    0 |
|  7 | song     | 3f572fcb0f9af03848738946954b8c43 |  23 |    0 |
+----+----------+----------------------------------+-----+------+
7 rows in set (0.00 sec)

插入记录(第2种方法)
INSERT [INTO] tbl_name SET col_name={expr|DEFAULT},……
该方法可以使用子查询(SubQuery),但是一次只能插入一条记录
mysql> INSERT users SET username='Ben',password='2345';#id字段自动编号;age字段设置了默认值10;sex字段允许为空。
Query OK, 1 row affected (0.07 sec)

mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | tom      | 123                              |  25 |    1 |
|  2 | jerry    | 3423                             |  25 |    0 |
|  3 | jerry    | 3423                             |  20 |    0 |
|  4 | jerry    | 3423                             |  10 |    1 |
|  5 | marry    | mima                             |  23 |    1 |
|  6 | rose     | 345                              |   8 |    0 |
|  7 | song     | 3f572fcb0f9af03848738946954b8c43 |  23 |    0 |
|  8 | Ben      | 2345                             |  10 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)

插入记录(第3种方法)
INSERT [INTO] tbl_name [(col_name,……)] SELECT ……
该方法是 将SELECT语句的查询结果 插入到 指定的数据表中!当我们学习了SELECT语句后,再回来练习这种方法。。。

更新记录(单表更新)
UPDATE [LOW_PRIORITY][IGNORE] table_reference SET col_name1={expr|DEFAULT}[,col_name2={expr|DEFAULT}……]
[WHERE where_condition] 如果省略了WHERE条件,则表示更新所有记录
mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | tom      | 123                              |  25 |    1 |
|  2 | jerry    | 3423                             |  25 |    0 |
|  3 | jerry    | 3423                             |  20 |    0 |
|  4 | jerry    | 3423                             |  10 |    1 |
|  5 | marry    | mima                             |  23 |    1 |
|  6 | rose     | 345                              |   8 |    0 |
|  7 | song     | 3f572fcb0f9af03848738946954b8c43 |  23 |    0 |
|  8 | Ben      | 2345                             |  10 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)

mysql> UPDATE users SET age=age+5; #将age字段的值加5;省略了WHERE条件,表示更新所有记录。
Query OK, 8 rows affected (0.11 sec)
Rows matched: 8  Changed: 8  Warnings: 0

mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | tom      | 123                              |  30 |    1 |
|  2 | jerry    | 3423                             |  30 |    0 |
|  3 | jerry    | 3423                             |  25 |    0 |
|  4 | jerry    | 3423                             |  15 |    1 |
|  5 | marry    | mima                             |  28 |    1 |
|  6 | rose     | 345                              |  13 |    0 |
|  7 | song     | 3f572fcb0f9af03848738946954b8c43 |  28 |    0 |
|  8 | Ben      | 2345                             |  15 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)

mysql> UPDATE users SET age=age+id,sex=1; #更新age字段和sex字段;
Query OK, 8 rows affected (0.06 sec)
Rows matched: 8  Changed: 8  Warnings: 0

mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | tom      | 123                              |  31 |    1 |
|  2 | jerry    | 3423                             |  32 |    1 |
|  3 | jerry    | 3423                             |  28 |    1 |
|  4 | jerry    | 3423                             |  19 |    1 |
|  5 | marry    | mima                             |  33 |    1 |
|  6 | rose     | 345                              |  19 |    1 |
|  7 | song     | 3f572fcb0f9af03848738946954b8c43 |  35 |    1 |
|  8 | Ben      | 2345                             |  23 |    1 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)

mysql> UPDATE users SET sex=0 WHERE id%2=0;#更新id为偶数的记录
Query OK, 4 rows affected (0.08 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | tom      | 123                              |  31 |    1 |
|  2 | jerry    | 3423                             |  32 |    0 |
|  3 | jerry    | 3423                             |  28 |    1 |
|  4 | jerry    | 3423                             |  19 |    0 |
|  5 | marry    | mima                             |  33 |    1 |
|  6 | rose     | 345                              |  19 |    0 |
|  7 | song     | 3f572fcb0f9af03848738946954b8c43 |  35 |    1 |
|  8 | Ben      | 2345                             |  23 |    0 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)

删除记录(单表删除)
DELETE FROM tbl_name [WHERE where_condition] 如果省略了WHERE条件,将删除数据表中的全部记录
mysql> DELETE FROM users WHERE id=6; # 删除id=6的记录。
Query OK, 1 row affected (0.07 sec)

mysql> SELECT * FROM users; #可以看到,id=6的记录已经被删除。
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | tom      | 123                              |  31 |    1 |
|  2 | jerry    | 3423                             |  32 |    0 |
|  3 | jerry    | 3423                             |  28 |    1 |
|  4 | jerry    | 3423                             |  19 |    0 |
|  5 | marry    | mima                             |  33 |    1 |
|  7 | song     | 3f572fcb0f9af03848738946954b8c43 |  35 |    1 |
|  8 | Ben      | 2345                             |  23 |    0 |
+----+----------+----------------------------------+-----+------+
7 rows in set (0.00 sec)
如果此时,向数据表users中插入一条记录,其id会是多少呢?(是6,还是9呢?)
mysql> INSERT users VALUES(DEFAULT,'ping','123',26,NULL);
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM users; #可以看到,新插入的记录的id为9(在之前 最大id的基础上 加1 )
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | tom      | 123                              |  31 |    1 |
|  2 | jerry    | 3423                             |  32 |    0 |
|  3 | jerry    | 3423                             |  28 |    1 |
|  4 | jerry    | 3423                             |  19 |    0 |
|  5 | marry    | mima                             |  33 |    1 |
|  7 | song     | 3f572fcb0f9af03848738946954b8c43 |  35 |    1 |
|  8 | Ben      | 2345                             |  23 |    0 |
|  9 | ping     | 123                              |  26 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)

针对数据表的操作,SELECT语句能占到80%以上。下面是SELECT语句的介绍:
查找记录,如下:


SELECT语句最简单的形式,如下:
mysql> SELECT VERSION(); #查询函数的返回值
+-----------+
| VERSION() |
+-----------+
| 5.5.37    |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT 3+8; #查询表达式的结果
+-----+
| 3+8 |
+-----+
|  11 |
+-----+
1 row in set (0.00 sec)

查询表达式,如下:


mysql> SHOW COLUMNS FROM users;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| username | varchar(20)          | NO   |     | NULL    |                |
| password | varchar(32)          | NO   |     | NULL    |                |
| age      | tinyint(3) unsigned  | NO   |     | 10      |                |
| sex      | tinyint(1)           | YES  |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

mysql> SELECT id,username FROM users;#从数据表users中,查找id列和username列。
+----+----------+
| id | username |
+----+----------+
|  1 | tom      |
|  2 | jerry    |
|  3 | jerry    |
|  4 | jerry    |
|  5 | marry    |
|  7 | song     |
|  8 | Ben      |
|  9 | ping     |
+----+----------+
8 rows in set (0.00 sec)

mysql> SELECT username,id FROM users; # SELECT语句中字段(查询表达式)的顺序会影响结果中字段的顺序
+----------+----+
| username | id |
+----------+----+
| tom      |  1 |
| jerry    |  2 |
| jerry    |  3 |
| jerry    |  4 |
| marry    |  5 |
| song     |  7 |
| Ben      |  8 |
| ping     |  9 |
+----------+----+
8 rows in set (0.00 sec)

mysql> SELECT * FROM users;#查询数据表users中的所有列。
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | tom      | 123                              |  31 |    1 |
|  2 | jerry    | 3423                             |  32 |    0 |
|  3 | jerry    | 3423                             |  28 |    1 |
|  4 | jerry    | 3423                             |  19 |    0 |
|  5 | marry    | mima                             |  33 |    1 |
|  7 | song     | 3f572fcb0f9af03848738946954b8c43 |  35 |    1 |
|  8 | Ben      | 2345                             |  23 |    0 |
|  9 | ping     | 123                              |  26 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)

mysql> SELECT users.* FROM users; #查询数据表users中的所有列。
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | tom      | 123                              |  31 |    1 |
|  2 | jerry    | 3423                             |  32 |    0 |
|  3 | jerry    | 3423                             |  28 |    1 |
|  4 | jerry    | 3423                             |  19 |    0 |
|  5 | marry    | mima                             |  33 |    1 |
|  7 | song     | 3f572fcb0f9af03848738946954b8c43 |  35 |    1 |
|  8 | Ben      | 2345                             |  23 |    0 |
|  9 | ping     | 123                              |  26 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)

如果只是操作一个数据表,可以只写id(不用写成users.id的形式);
以后,我们会学到多表连接,如果两张表中存在相同的字段id,则只写id就会产生歧义,不知道到底指的是哪张表中的id。
mysql> SELECT users.id,users.username FROM users; #使用users.id的形式,在任何情况下,都不会有歧义
+----+----------+
| id | username |
+----+----------+
|  1 | tom      |
|  2 | jerry    |
|  3 | jerry    |
|  4 | jerry    |
|  5 | marry    |
|  7 | song     |
|  8 | Ben      |
|  9 | ping     |
+----+----------+
8 rows in set (0.04 sec)

有时候,某些字段的名字太长,不容易记忆和书写,可以为该字段起一个别名,别名会出现在结果中(别名会影响到结果集)。
mysql> SELECT id AS userID,username AS uname FROM users;#为id字段起一个别名userID,为username字段起一个别名uname。
+--------+-------+
| userID | uname |
+--------+-------+
|      1 | tom   |
|      2 | jerry |
|      3 | jerry |
|      4 | jerry |
|      5 | marry |
|      7 | song  |
|      8 | Ben   |
|      9 | ping  |
+--------+-------+
8 rows in set (0.03 sec)

起别名时,AS关键词虽然可以省略,但是,推荐最好使用AS关键词
mysql> SELECT id username FROM users;#为id字段起一个别名username(此处,省略了AS关键词)
+----------+
| username |
+----------+
|        1 |
|        2 |
|        3 |
|        4 |
|        5 |
|        7 |
|        8 |
|        9 |
+----------+
8 rows in set (0.00 sec)
注意:上面这个例子中的username指的是id字段的别名username,不是数据表users中的字段名username!!

mysql> SELECT id AS username FROM users; #起别名时,推荐写上AS关键字。(此处的username指的是id字段的别名。)
+----------+
| username |
+----------+
|        1 |
|        2 |
|        3 |
|        4 |
|        5 |
|        7 |
|        8 |
|        9 |
+----------+
8 rows in set (0.00 sec)

WHERE条件表达式
对记录进行过滤,如果没有指定WHERE子句,则显示所有记录
在WHERE表达式中,可以使用MySQL支持的函数或运算符。
mysql> SELECT * FROM users;#如果没有指定WHERE子句,则显示所有记录。
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | tom      | 123                              |  31 |    1 |
|  2 | jerry    | 3423                             |  32 |    0 |
|  3 | jerry    | 3423                             |  28 |    1 |
|  4 | jerry    | 3423                             |  19 |    0 |
|  5 | marry    | mima                             |  33 |    1 |
|  7 | song     | 3f572fcb0f9af03848738946954b8c43 |  35 |    1 |
|  8 | Ben      | 2345                             |  23 |    0 |
|  9 | ping     | 123                              |  26 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM users WHERE id>=8; #显示id>=8的记录
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  8 | Ben      | 2345     |  23 |    0 |
|  9 | ping     | 123      |  26 | NULL |
+----+----------+----------+-----+------+
2 rows in set (0.00 sec)

GROUP BY查询结果分组
[GROUP BY {col_name|position}[ASC|DESC],……]
col_name:按照列名分组;  position:按照位置分组(位置从1开始);
ASC:ascending order 升序;  DESC:descending order 降序;   默认是ASC升序。。。
如果存在多个分组条件,则多个分组条件之间用逗号分隔。
mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | tom      | 123                              |  31 |    1 |
|  2 | jerry    | 3423                             |  32 |    0 |
|  3 | jerry    | 3423                             |  28 |    1 |
|  4 | jerry    | 3423                             |  19 |    0 |
|  5 | marry    | mima                             |  33 |    1 |
|  7 | song     | 3f572fcb0f9af03848738946954b8c43 |  35 |    1 |
|  8 | Ben      | 2345                             |  23 |    0 |
|  9 | ping     | 123                              |  26 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)

mysql> SELECT sex FROM users;
+------+
| sex  |
+------+
|    1 |
|    0 |
|    1 |
|    0 |
|    1 |
|    1 |
|    0 |
| NULL |
+------+
8 rows in set (0.00 sec)

mysql> SELECT sex FROM users GROUP BY sex; #按照列名sex进行分组(对所有的记录做分组)
+------+
| sex  |
+------+
| NULL |
|    0 |
|    1 |
+------+
3 rows in set (0.05 sec)

mysql> SELECT sex FROM users GROUP BY 1;#按照位置分组(对所有的记录做分组)(1指的是SELECT关键词后边,出现的第1个字段名)
+------+
| sex  |
+------+
| NULL |
|    0 |
|    1 |
+------+
3 rows in set (0.00 sec)
上述2种分组方法,我们推荐按照列名来分组!!下面,举一个 有多个分组条件的例子:
mysql> SELECT id,sex FROM users GROUP BY sex,id;#分组条件第一优先级sex,第二优先级id;(对所有的记录做分组)
+----+------+
| id | sex  |
+----+------+
|  9 | NULL |
|  2 |    0 |
|  4 |    0 |
|  8 |    0 |
|  1 |    1 |
|  3 |    1 |
|  5 |    1 |
|  7 |    1 |
+----+------+
8 rows in set (0.00 sec)

having语句设置分组条件
[HAVING where_condition]
我们可以省略having语句,对所有记录做分组;也可以通过having语句设置分组条件,对部分记录做分组
mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | tom      | 123                              |  31 |    1 |
|  2 | jerry    | 3423                             |  32 |    0 |
|  3 | jerry    | 3423                             |  28 |    1 |
|  4 | jerry    | 3423                             |  19 |    0 |
|  5 | marry    | mima                             |  33 |    1 |
|  7 | song     | 3f572fcb0f9af03848738946954b8c43 |  35 |    1 |
|  8 | Ben      | 2345                             |  23 |    0 |
|  9 | ping     | 123                              |  26 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)
mysql> SELECT sex FROM users GROUP BY sex HAVING age >23; 
ERROR 1054 (42S22): Unknown column 'age' in 'having clause'
这个例子报错了,因为使用HAVING语句设置分组条件需要满足下面2个条件中的任意一个
1》HAVING 后的字段名(分组条件)出现在SELECT关键词的后面。
用Having条件分组时候,其条件字段必须出现在select语句中,Having是对select之后的结果进行条件判断。
2》HAVING 后的字段名(分组条件)和聚合函数配合使用。
聚合函数就是把数据聚合起来的函数,如果想对符合某些条件的所有数据进行操作(例如加在一起),那么聚合函数就能派上用途了。
MySQL中比较常用的聚合函数有:
求和函数SUM()
计数函数COUNT()
平均值函数AVG()
最大/最小值函数MAX()/MIN()
mysql> SELECT sex,age FROM users GROUP BY sex HAVING age >23;#HAVING后的字段名age出现在SELECT关键词的后面。
+------+-----+
| sex  | age |
+------+-----+
| NULL |  26 |
|    0 |  32 |
|    1 |  31 |
+------+-----+
3 rows in set (0.03 sec)

mysql> SELECT sex FROM users GROUP BY sex HAVING count(id)>=2; #HAVING后的字段名id和聚合函数配合使用。
+------+
| sex  |
+------+
|    0 |
|    1 |
+------+
2 rows in set (0.04 sec)
上面这条指令,指按照sex分组,分成了三组:sex=0,sex=1和sex=NULL,条件id数大于等于2的留下显示,
显然sex=0个数有3>=2故留下;而sex=NULL个数为1,不满足条件,故不显示;sex=1的个数有4>=2故留下。。。

下面对比介绍一下,mysql中的where和having子句。。。

having子句可以让我们筛选成组后的各种数据,where子句在聚合前先筛选记录,也就是说作用在group by和having子句前。
而having子句在聚合后对组记录进行筛选。

mysql> INSERT users VALUES(DEFAULT,'haha','1234',23,1);
Query OK, 1 row affected (0.08 sec)

mysql> INSERT users VALUES(DEFAULT,'hahhha','1234',23,0);
Query OK, 1 row affected (0.06 sec)


mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | tom      | 123                              |  31 |    1 |
|  2 | jerry    | 3423                             |  32 |    0 |
|  3 | jerry    | 3423                             |  28 |    1 |
|  4 | jerry    | 3423                             |  19 |    0 |
|  5 | marry    | mima                             |  33 |    1 |
|  7 | song     | 3f572fcb0f9af03848738946954b8c43 |  35 |    1 |
|  8 | Ben      | 2345                             |  23 |    0 |
|  9 | ping     | 123                              |  26 | NULL |
| 10 | haha     | 1234                             |  23 |    1 |
| 11 | hahhha   | 1234                             |  23 |    0 |
+----+----------+----------------------------------+-----+------+
10 rows in set (0.00 sec)

下面的例子,首先按照sex分组,分成了3组;然后用聚合函数SUM,对每组中的id字段和age字段分别求和。
mysql> SELECT sex,SUM(id),SUM(age) FROM users GROUP BY sex;
+------+---------+----------+
| sex  | SUM(id) | SUM(age) |
+------+---------+----------+
| NULL |       9 |       26 |
|    0 |      25 |       97 |
|    1 |      26 |      150 |
+------+---------+----------+
3 rows in set (0.04 sec)

mysql> SELECT sex,SUM(id),SUM(age) FROM users GROUP BY sex HAVING SUM(age)>50;#having子句会筛选成组后的数据
+------+---------+----------+
| sex  | SUM(id) | SUM(age) |
+------+---------+----------+
|    0 |      25 |       97 |
|    1 |      26 |      150 |
+------+---------+----------+
2 rows in set (0.04 sec)

mysql判断某个字段的长度:
mysql> SELECT * FROM users WHERE char_length(trim(username))=4;#查找username字段长度为4的记录
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  7 | song     | 3f572fcb0f9af03848738946954b8c43 |  35 |    1 |
|  9 | ping     | 123                              |  26 | NULL |
| 10 | haha     | 1234                             |  23 |    1 |
+----+----------+----------------------------------+-----+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM users WHERE char_length(trim(username))<5 and char_length(username)>2;#查找username字段长度为3或4的记录
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | tom      | 123                              |  31 |    1 |
|  7 | song     | 3f572fcb0f9af03848738946954b8c43 |  35 |    1 |
|  8 | Ben      | 2345                             |  23 |    0 |
|  9 | ping     | 123                              |  26 | NULL |
| 10 | haha     | 1234                             |  23 |    1 |
+----+----------+----------------------------------+-----+------+
5 rows in set (0.00 sec)

mysql中的where和having子句都可以实现过滤记录的功能,但他们的用法还是有一些区别的,看一例子:
mysql> SELECT username,count(*) AS ct FROM users GROUP BY username;
+----------+----+
| username | ct |
+----------+----+
| Ben      |  1 |
| haha     |  1 |
| hahhha   |  1 |
| jerry    |  3 |
| marry    |  1 |
| ping     |  1 |
| song     |  1 |
| tom      |  1 |
+----------+----+
8 rows in set (0.00 sec)

mysql> SELECT username,count(*) AS ct FROM users GROUP BY username HAVING ct>1;#使用HAVING子句进行过滤。
+----------+----+
| username | ct |
+----------+----+
| jerry    |  3 |
+----------+----+
1 row in set (0.00 sec)
先用group by对username进行分组,再用having来过滤大于1的,这样查找出来的就是username字段重复的记录了。。。

mysql> SELECT max(age) FROM users;
+----------+
| max(age) |
+----------+
|       35 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM users WHERE age=(SELECT max(age) FROM users); #使用WHERE子句进行过滤。
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  7 | song     | 3f572fcb0f9af03848738946954b8c43 |  35 |    1 |
+----+----------+----------------------------------+-----+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM users WHERE age=35;#使用WHERE子句进行过滤。
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  7 | song     | 3f572fcb0f9af03848738946954b8c43 |  35 |    1 |
+----+----------+----------------------------------+-----+------+
1 row in set (0.00 sec)

having和where的区别:
作用的对象不同;WHERE 子句作用于表和视图,WHERE子句不能包含聚集函数;HAVING子句作用于,HAVING可以使用聚合函数


order by语句对查询结果排序
[ ORDER BY {col_name|expr|position}[ASC|DESC]] 默认是ASC升序
ASC:ascending order 升序;  DESC:descending order 降序;   默认是ASC升序。。。

mysql> DELETE FROM users WHERE id=10;
Query OK, 1 row affected (0.07 sec)

mysql> DELETE FROM users WHERE id=11;
Query OK, 1 row affected (0.06 sec)


mysql> SELECT *  FROM users; #按照 记录的插入顺序 显示。
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | tom      | 123                              |  31 |    1 |
|  2 | jerry    | 3423                             |  32 |    0 |
|  3 | jerry    | 3423                             |  28 |    1 |
|  4 | jerry    | 3423                             |  19 |    0 |
|  5 | marry    | mima                             |  33 |    1 |
|  7 | song     | 3f572fcb0f9af03848738946954b8c43 |  35 |    1 |
|  8 | Ben      | 2345                             |  23 |    0 |
|  9 | ping     | 123                              |  26 | NULL |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)

mysql> SELECT *  FROM users ORDER BY id DESC; #按照id降序排列
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  9 | ping     | 123                              |  26 | NULL |
|  8 | Ben      | 2345                             |  23 |    0 |
|  7 | song     | 3f572fcb0f9af03848738946954b8c43 |  35 |    1 |
|  5 | marry    | mima                             |  33 |    1 |
|  4 | jerry    | 3423                             |  19 |    0 |
|  3 | jerry    | 3423                             |  28 |    1 |
|  2 | jerry    | 3423                             |  32 |    0 |
|  1 | tom      | 123                              |  31 |    1 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)

mysql> SELECT *  FROM users ORDER BY age; #按照age排序(默认按照升序排列)
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  4 | jerry    | 3423                             |  19 |    0 |
|  8 | Ben      | 2345                             |  23 |    0 |
|  9 | ping     | 123                              |  26 | NULL |
|  3 | jerry    | 3423                             |  28 |    1 |
|  1 | tom      | 123                              |  31 |    1 |
|  2 | jerry    | 3423                             |  32 |    0 |
|  5 | marry    | mima                             |  33 |    1 |
|  7 | song     | 3f572fcb0f9af03848738946954b8c43 |  35 |    1 |
+----+----------+----------------------------------+-----+------+
8 rows in set (0.00 sec)

mysql> INSERT INTO users VALUES(NULL,'haha','1234',23,1);#插入记录
Query OK, 1 row affected (0.18 sec)

mysql> INSERT INTO users VALUES(NULL,'hahhha','1234',23,0);#插入记录
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM users ORDER BY age; #按照age排序(默认升序排列)
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  4 | jerry    | 3423                             |  19 |    0 |
| 13 | hahhha   | 1234                             |  23 |    0 |
| 12 | haha     | 1234                             |  23 |    1 |
|  8 | Ben      | 2345                             |  23 |    0 |
|  9 | ping     | 123                              |  26 | NULL |
|  3 | jerry    | 3423                             |  28 |    1 |
|  1 | tom      | 123                              |  31 |    1 |
|  2 | jerry    | 3423                             |  32 |    0 |
|  5 | marry    | mima                             |  33 |    1 |
|  7 | song     | 3f572fcb0f9af03848738946954b8c43 |  35 |    1 |
+----+----------+----------------------------------+-----+------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM users ORDER BY age,id DESC; #首先按照age字段(默认)升序排列;若age字段的值相同,再按照id降序排列。
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  4 | jerry    | 3423                             |  19 |    0 |
| 13 | hahhha   | 1234                             |  23 |    0 |
| 12 | haha     | 1234                             |  23 |    1 |
|  8 | Ben      | 2345                             |  23 |    0 |
|  9 | ping     | 123                              |  26 | NULL |
|  3 | jerry    | 3423                             |  28 |    1 |
|  1 | tom      | 123                              |  31 |    1 |
|  2 | jerry    | 3423                             |  32 |    0 |
|  5 | marry    | mima                             |  33 |    1 |
|  7 | song     | 3f572fcb0f9af03848738946954b8c43 |  35 |    1 |
+----+----------+----------------------------------+-----+------+
10 rows in set (0.00 sec)

限制 查询结果返回的数量
[LIMIT {[offset,] row_count|row_count OFFSET offset}]
即:
第1种方式:LIMIT [offset,] row_count    offset是可选的,若省略,offset默认取0
第2种方式:LIMIT row_count OFFSET offset
其中,offset的取值从0开始!
mysql> SELECT * FROM users; #没有使用LIMIT子句;返回全部的查询结果。
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | tom      | 123                              |  31 |    1 |
|  2 | jerry    | 3423                             |  32 |    0 |
|  3 | jerry    | 3423                             |  28 |    1 |
|  4 | jerry    | 3423                             |  19 |    0 |
|  5 | marry    | mima                             |  33 |    1 |
|  7 | song     | 3f572fcb0f9af03848738946954b8c43 |  35 |    1 |
|  8 | Ben      | 2345                             |  23 |    0 |
|  9 | ping     | 123                              |  26 | NULL |
| 12 | haha     | 1234                             |  23 |    1 |
| 13 | hahhha   | 1234                             |  23 |    0 |
+----+----------+----------------------------------+-----+------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM users LIMIT 0,2; #指定offset为0 (offset的取值从0开始,若省略offset,则offset取默认值0)
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  1 | tom      | 123      |  31 |    1 |
|  2 | jerry    | 3423     |  32 |    0 |
+----+----------+----------+-----+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM users LIMIT 2; #省略了offset;offset取默认值0;
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  1 | tom      | 123      |  31 |    1 |
|  2 | jerry    | 3423     |  32 |    0 |
+----+----------+----------+-----+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM users LIMIT 2 OFFSET 0; #从offset=0开始,连续的返回2条记录(采用第2种方式)
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  1 | tom      | 123      |  31 |    1 |
|  2 | jerry    | 3423     |  32 |    0 |
+----+----------+----------+-----+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM users LIMIT 2,2; #从offset=2开始,连续的返回2条记录。
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  3 | jerry    | 3423     |  28 |    1 |
|  4 | jerry    | 3423     |  19 |    0 |
+----+----------+----------+-----+------+
2 rows in set (0.00 sec)
注意:offset的取值从0开始;offset的取值与id字段的取值没有任何的联系!!

mysql> SELECT * FROM users ORDER BY id DESC; #按照id降序排列
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
| 13 | hahhha   | 1234                             |  23 |    0 |
| 12 | haha     | 1234                             |  23 |    1 |
|  9 | ping     | 123                              |  26 | NULL |
|  8 | Ben      | 2345                             |  23 |    0 |
|  7 | song     | 3f572fcb0f9af03848738946954b8c43 |  35 |    1 |
|  5 | marry    | mima                             |  33 |    1 |
|  4 | jerry    | 3423                             |  19 |    0 |
|  3 | jerry    | 3423                             |  28 |    1 |
|  2 | jerry    | 3423                             |  32 |    0 |
|  1 | tom      | 123                              |  31 |    1 |
+----+----------+----------------------------------+-----+------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM users ORDER BY id DESC LIMIT 2,2;#先按照id降序排列;然后,从offset=2开始,连续的返回2条记录;
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  9 | ping     | 123      |  26 | NULL |
|  8 | Ben      | 2345     |  23 |    0 |
+----+----------+----------+-----+------+
2 rows in set (0.00 sec)

前面,我们学习过 插入记录有3种方法;现在,我们学习了SELECT子句,可以练习第3种插入记录的方法,如下:
mysql> CREATE TABLE test(    #创建数据表test
    -> id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.25 sec)

mysql> SELECT * FROM users WHERE age>30; #查找数据表users中age>30的记录。
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | tom      | 123                              |  31 |    1 |
|  2 | jerry    | 3423                             |  32 |    0 |
|  5 | marry    | mima                             |  33 |    1 |
|  7 | song     | 3f572fcb0f9af03848738946954b8c43 |  35 |    1 |
+----+----------+----------------------------------+-----+------+
4 rows in set (0.00 sec)

mysql> INSERT test SELECT username FROM users WHERE age>30;
ERROR 1136 (21S01): Column count doesn't match value count at row 1
上述命令报错了,原因是:SELECT子句的返回结果只有1列;而test数据表包含2列;二者列数不匹配!
mysql> SELECT username FROM users WHERE age>30; #SELECT子句的返回结果只有1列。
+----------+
| username |
+----------+
| tom      |
| jerry    |
| marry    |
| song     |
+----------+
4 rows in set (0.00 sec)
正确的做法是:
mysql> INSERT test(username) SELECT username FROM users WHERE age>30;#将SELECT子句的查询结果 写入 指定的数据表test
Query OK, 4 rows affected (0.11 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM test; #可以看到,SELECT子句的查询结果,已经写入到了test数据表。
+----+----------+
| id | username |
+----+----------+
|  1 | tom      |
|  2 | jerry    |
|  3 | marry    |
|  4 | song     |
+----+----------+
4 rows in set (0.00 sec)
小结:
本文主要介绍了针对记录的 增删改查 4个操作。
插入记录的3种方法,如下:


更新记录的方法,如下:


删除记录的方法,如下:


查找记录,如下:


(完)



成功没有尽头,生活没有尽头,生活中的艰难困苦对我们的考验没有尽头,在艰苦奋斗后我们所得到的收获和喜悦也没有尽头。当你完全懂得了”成功永远没有尽头”这句话的含义时,生活之美也就向你展开了她迷人的笑容。