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

最新推荐文章于 2024-01-03 20:38:45 发布

快递小可

最新推荐文章于 2024-01-03 20:38:45 发布

阅读量1.7k

点赞数 1

分类专栏： MySQL 文章标签： MySQL 操作数据表增删改查

本文链接：https://blog.csdn.net/sxingming/article/details/52704617

版权

MySQL 专栏收录该内容

26 篇文章 1 订阅

订阅专栏

插入记录（第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种方法，如下：