MySQL_03_对数据的增删改查 DML

最新推荐文章于 2021-01-08 23:37:57 发布
最新推荐文章于 2021-01-08 23:37:57 发布
阅读量468 收藏 2
点赞数
分类专栏： 人工智能_1
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zzugsh/article/details/105581924
版权

表中数据的增删改查

更改数据的插入、更新、删除 DML

插入数据

普通插入

  • 不指定具体的字段名
    • INSERT [INTO] tbl_name VALUES|VALUE(值
  • 列出指定字段
    • INSERT [INTO] tbl_name(字段名称1，……）value|values（值1，……） 值必须互相对应
mysql> CREATE TABLE IF NOT EXISTS student1(
    -> 
    -> id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE,
    -> password CHAR(32) NOT NULL, 
    -> email VARCHAR(50) NOT NULL DEFAULT '234234234@qq.com',
    -> age TINYINT UNSIGNED DEFAULT 18
    -> 
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc student1;
+----------+------------------+------+-----+------------------+----------------+
| Field    | Type             | Null | Key | Default          | Extra          |
+----------+------------------+------+-----+------------------+----------------+
| id       | tinyint unsigned | NO   | PRI | NULL             | auto_increment |
| username | varchar(20)      | NO   | UNI | NULL             |                |
| password | char(32)         | NO   |     | NULL             |                |
| email    | varchar(50)      | NO   |     | 234234234@qq.com |                |
| age      | tinyint unsigned | YES  |     | 18               |                |
+----------+------------------+------+-----+------------------+----------------+
5 rows in set (0.00 sec)

mysql> INSERT INTO student1 VALUES(1,'KING','KING','KING@QQ.COM',23);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM student1;
+----+----------+----------+-------------+------+
| id | username | password | email       | age  |
+----+----------+----------+-------------+------+
|  1 | KING     | KING     | KING@QQ.COM |   23 |
+----+----------+----------+-------------+------+
1 row in set (0.00 sec)

mysql> INSERT student1 VALUES(2,'QUEEN','QUEEN','QUEEN@QQ.COM',65);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM student1;
+----+----------+----------+--------------+------+
| id | username | password | email        | age  |
+----+----------+----------+--------------+------+
|  1 | KING     | KING     | KING@QQ.COM  |   23 |
|  2 | QUEEN    | QUEEN    | QUEEN@QQ.COM |   65 |
+----+----------+----------+--------------+------+
2 rows in set (0.00 sec)

mysql> INSERT student1(username,password) VALUES('A','AA');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT student1(username,password) VALUES('B','BB');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM student1;
+----+----------+----------+------------------+------+
| id | username | password | email            | age  |
+----+----------+----------+------------------+------+
|  1 | KING     | KING     | KING@QQ.COM      |   23 |
|  2 | QUEEN    | QUEEN    | QUEEN@QQ.COM     |   65 |
|  3 | A        | AA       | 234234234@qq.com |   18 |
|  4 | B        | BB       | 234234234@qq.com |   18 |
+----+----------+----------+------------------+------+
4 rows in set (0.00 sec)
mysql> INSERT student1 VALUES(10,'GG','GGG','GG@QQ.COM',46),(11,'JJ','JJJ','GG@QQ.COM',46),(12,'HH','HHH','GG@QQ.COM',46);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from student1;                                                                             +----+----------+----------+------------------+------+
| id | username | password | email            | age  |
+----+----------+----------+------------------+------+
|  1 | KING     | KING     | KING@QQ.COM      |   23 |
|  2 | QUEEN    | QUEEN    | QUEEN@QQ.COM     |   65 |
|  3 | A        | AA       | 234234234@qq.com |   18 |
|  4 | B        | BB       | 234234234@qq.com |   18 |
|  6 | D        | DDD      | DD@QQ.COM        |   76 |
|  7 | QUEE     | QUEEN    | QUEEN@QQ.COM     |   65 |
|  8 | F        | FFF      | FF@QQ.COM        |   12 |
|  9 | Fg       | FFF      | FF@QQ.COM        |   46 |
| 10 | GG       | GGG      | GG@QQ.COM        |   46 |
| 11 | JJ       | JJJ      | GG@QQ.COM        |   46 |
| 12 | HH       | HHH      | GG@QQ.COM        |   46 |
| 32 | C        | CCC      | CCC@QQ.COM       |   18 |
+----+----------+----------+------------------+------+
12 rows in set (0.00 sec)

通过SET形式插入记录

mysql> INSERT INTO student1 SET id = 100,username = 'MM',password = 'this is a secret';
Query OK, 1 row affected (0.01 sec)

mysql> select * from student1;                                                                             +-----+----------+------------------+------------------+------+
| id  | username | password         | email            | age  |
+-----+----------+------------------+------------------+------+
|   1 | KING     | KING             | KING@QQ.COM      |   23 |
|   2 | QUEEN    | QUEEN            | QUEEN@QQ.COM     |   65 |
|   3 | A        | AA               | 234234234@qq.com |   18 |
|   4 | B        | BB               | 234234234@qq.com |   18 |
|   6 | D        | DDD              | DD@QQ.COM        |   76 |
|   7 | QUEE     | QUEEN            | QUEEN@QQ.COM     |   65 |
|   8 | F        | FFF              | FF@QQ.COM        |   12 |
|   9 | Fg       | FFF              | FF@QQ.COM        |   46 |
|  10 | GG       | GGG              | GG@QQ.COM        |   46 |
|  11 | JJ       | JJJ              | GG@QQ.COM        |   46 |
|  12 | HH       | HHH              | GG@QQ.COM        |   46 |
|  32 | C        | CCC              | CCC@QQ.COM       |   18 |
|  98 | KK       | this is a secret | BKBJKBKJB@QQ.COM |   88 |
| 100 | MM       | this is a secret | 234234234@qq.com |   18 |
+-----+----------+------------------+------------------+------+
14 rows in set (0.00 sec)

从别的表中选择后插入新表中

mysql> CREATE TABLE testuser(
    -> 
    -> id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    -> username VARCHAR(20) NOT NULL UNIQUE
    -> 
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT testuser SELECT id,username FROM student1;
Query OK, 14 rows affected (0.01 sec)
Records: 14  Duplicates: 0  Warnings: 0

mysql> select * from testuser;
+-----+----------+
| id  | username |
+-----+----------+
|   3 | A        |
|   4 | B        |
|  32 | C        |
|   6 | D        |
|   8 | F        |
|   9 | Fg       |
|  10 | GG       |
|  12 | HH       |
|  11 | JJ       |
|   1 | KING     |
|  98 | KK       |
| 100 | MM       |
|   7 | QUEE     |
|   2 | QUEEN    |
+-----+----------+
14 rows in set (0.00 sec)

更新数据

-UPDATE tbl_name字段名称 = 值…… [WHERE条件][ ORDER BY 字段名称][LIMIT限制条数]

mysql> UPDATE student1 SET age = 8;
Query OK, 14 rows affected (0.00 sec)
Rows matched: 14  Changed: 14  Warnings: 0

mysql> select * from student1;
+-----+----------+------------------+------------------+------+
| id  | username | password         | email            | age  |
+-----+----------+------------------+------------------+------+
|   1 | KING     | KING             | KING@QQ.COM      |    8 |
|   2 | QUEEN    | QUEEN            | QUEEN@QQ.COM     |    8 |
|   3 | A        | AA               | 234234234@qq.com |    8 |
|   4 | B        | BB               | 234234234@qq.com |    8 |
|   6 | D        | DDD              | DD@QQ.COM        |    8 |
|   7 | QUEE     | QUEEN            | QUEEN@QQ.COM     |    8 |
|   8 | F        | FFF              | FF@QQ.COM        |    8 |
|   9 | Fg       | FFF              | FF@QQ.COM        |    8 |
|  10 | GG       | GGG              | GG@QQ.COM        |    8 |
|  11 | JJ       | JJJ              | GG@QQ.COM        |    8 |
|  12 | HH       | HHH              | GG@QQ.COM        |    8 |
|  32 | C        | CCC              | CCC@QQ.COM       |    8 |
|  98 | KK       | this is a secret | BKBJKBKJB@QQ.COM |    8 |
| 100 | MM       | this is a secret | 234234234@qq.com |    8 |
+-----+----------+------------------+------------------+------+
14 rows in set (0.00 sec)
mysql> select * from student1;
+-----+----------+------------------+-----------------+------+
| id  | username | password         | email           | age  |
+-----+----------+------------------+-----------------+------+
|   1 | KING     | king123          | 32423423@QQ.COM |   99 |
|   2 | QUEEN    | QUEEN            | mkgsh@happy.com |   20 |
|   3 | A        | AA               | mkgsh@happy.com |   20 |
|   4 | B        | BB               | mkgsh@happy.com |   20 |
|   6 | D        | DDD              | mkgsh@happy.com |   20 |
|   7 | QUEE     | QUEEN            | mkgsh@happy.com |   20 |
|   8 | F        | FFF              | mkgsh@happy.com |   20 |
|   9 | Fg       | FFF              | mkgsh@happy.com |   20 |
|  10 | GG       | GGG              | mkgsh@happy.com |   20 |
|  11 | JJ       | JJJ              | mkgsh@happy.com |   20 |
|  12 | HH       | HHH              | mkgsh@happy.com |   20 |
|  32 | C        | CCC              | mkgsh@happy.com |   20 |
|  98 | KK       | this is a secret | mkgsh@happy.com |   20 |
| 100 | MM       | this is a secret | mkgsh@happy.com |   20 |
+-----+----------+------------------+-----------------+------+
14 rows in set (0.00 sec)

mysql> UPDATE student2 SET age = age - 5 WHERE id > 3;
ERROR 1146 (42S02): Table 'maizi.student2' doesn't exist
mysql> UPDATE student1 SET age = age - 5 WHERE id > 3;
Query OK, 11 rows affected (0.00 sec)
Rows matched: 11  Changed: 11  Warnings: 0

删除数据

  • 不清空自增长的值
    • DELETE tbl_name 字段名称=值 [WHERE条件] [ORDER BY字段名] [LIMIT限制条数]
  • 彻底清空数据表
    • TRUNCATE [TABLE] tbl_name
-- 删除记录
DELETE FROM testuser; -- 将全部记录删除

-- 删除student1表中id为1的用户
DELETE FROM student1 WHERE id = 1;

-- 让自增长从1开始的方法
ALTER TABLE student1 AUTO_INCREMENT = 1;

-- 彻底请空数据表 不可以带where条件
TRUNCATE table student1;

查询数据的基本操作

查询总结:

SELECT select_expr [, select_expr …]
[
	FROM table_references
	[WHERE 条件]
	[GROUP BY {col_name | position} [ASC| DESC], … 分组]
	[HAVING 条件 对分组结果进行二次筛选]
	[ORDER BY {col_name | position} [ASC| DESC], …排序]
	[LIMIT 限制显示条数]
]

基本查询:

mysql> SELECT * FROM cms_admin;
+----+----------+----------+--------------+-----------------+
| id | username | password | email        | role            |
+----+----------+----------+--------------+-----------------+
|  1 | admin    | admin    | admin@qq.com | 超级管理员      |
|  2 | king     | king     | admin@qq.com | 普通管理员      |
|  3 | 麦子     | maizi    | admin@qq.com | 普通管理员      |
|  4 | queen    | queen    | admin@qq.com | 普通管理员      |
|  5 | test     | test     | admin@qq.com | 普通管理员      |
+----+----------+----------+--------------+-----------------+
5 rows in set (0.00 sec)

mysql> SELECT cms_admin.* FROM cms_admin;
+----+----------+----------+--------------+-----------------+
| id | username | password | email        | role            |
+----+----------+----------+--------------+-----------------+
|  1 | admin    | admin    | admin@qq.com | 超级管理员      |
|  2 | king     | king     | admin@qq.com | 普通管理员      |
|  3 | 麦子     | maizi    | admin@qq.com | 普通管理员      |
|  4 | queen    | queen    | admin@qq.com | 普通管理员      |
|  5 | test     | test     | admin@qq.com | 普通管理员      |
+----+----------+----------+--------------+-----------------+
5 rows in set (0.03 sec)

mysql> SELECT id,username FROM cms_admin;
+----+----------+
| id | username |
+----+----------+
|  1 | admin    |
|  2 | king     |
|  4 | queen    |
|  5 | test     |
|  3 | 麦子     |
+----+----------+
5 rows in set (0.00 sec)

mysql> SELECT username,id,role FROM cms_admin;
+----------+----+-----------------+
| username | id | role            |
+----------+----+-----------------+
| admin    |  1 | 超级管理员      |
| king     |  2 | 普通管理员      |
| 麦子     |  3 | 普通管理员      |
| queen    |  4 | 普通管理员      |
| test     |  5 | 普通管理员      |
+----------+----+-----------------+
5 rows in set (0.00 sec)

mysql> SELECT id,username,role FROM cms.cms_admin;
+----+----------+-----------------+
| id | username | role            |
+----+----------+-----------------+
|  1 | admin    | 超级管理员      |
|  2 | king     | 普通管理员      |
|  3 | 麦子     | 普通管理员      |
|  4 | queen    | 普通管理员      |
|  5 | test     | 普通管理员      |
+----+----------+-----------------+
5 rows in set (0.00 sec)

mysql> SELECT id,username FROM maizi.user;
Empty set (0.17 sec)

mysql> SELECT id,username FROM maizi.user1;
+----+----------+
| id | username |
+----+----------+
|  1 | king     |
| 13 | QUEEN    |
+----+----------+
2 rows in set (0.16 sec)

mysql> SELECT cms_admin.id,cms_amdin.username FROM cms.cms_admin;
ERROR 1054 (42S22): Unknown column 'cms_amdin.username' in 'field list'
mysql> DESC cms_admin;
+----------+-------------------------------------------+------+-----+-----------------+----------------+
| Field    | Type                                      | Null | Key | Default         | Extra          |
+----------+-------------------------------------------+------+-----+-----------------+----------------+
| id       | tinyint(3) unsigned                       | NO   | PRI | NULL            | auto_increment |
| username | varchar(20)                               | NO   | UNI | NULL            |                |
| password | char(32)                                  | NO   |     | NULL            |                |
| email    | varchar(50)                               | NO   |     | admin@qq.com    |                |
| role     | enum('普通管理员','超级管理员')           | YES  |     | 普通管理员      |                |
+----------+-------------------------------------------+------+-----+-----------------+----------------+
5 rows in set (0.05 sec)

mysql> SELECT cms_admin.id,cms_admin.username FROM cms.cms_admin;
+----+----------+
| id | username |
+----+----------+
|  1 | admin    |
|  2 | king     |
|  4 | queen    |
|  5 | test     |
|  3 | 麦子     |
+----+----------+
5 rows in set (0.00 sec)

AS 对字段和表可以起别名

ysql> SELECT id,username FROM cms_admin AS a;
+----+----------+
| id | username |
+----+----------+
|  1 | admin    |
|  2 | king     |
|  4 | queen    |
|  5 | test     |
|  3 | 麦子     |
+----+----------+
5 rows in set (0.00 sec)

mysql> SELECT id,username FROM cms_admin a;
+----+----------+
| id | username |
+----+----------+
|  1 | admin    |
|  2 | king     |
|  4 | queen    |
|  5 | test     |
|  3 | 麦子     |
+----+----------+
5 rows in set (0.00 sec)

mysql> SELECT a.id,a.username,a.email,a.role FROM cms_admin AS a;
+----+----------+--------------+-----------------+
| id | username | email        | role            |
+----+----------+--------------+-----------------+
|  1 | admin    | admin@qq.com | 超级管理员      |
|  2 | king     | admin@qq.com | 普通管理员      |
|  3 | 麦子     | admin@qq.com | 普通管理员      |
|  4 | queen    | admin@qq.com | 普通管理员      |
|  5 | test     | admin@qq.com | 普通管理员      |
+----+----------+--------------+-----------------+
5 rows in set (0.00 sec)

mysql> SELECT id AS '编号',username AS '用户名',email AS '邮箱',role '角色' FROM cms_admin;
+--------+-----------+--------------+-----------------+
| 编号   | 用户名    | 邮箱         | 角色            |
+--------+-----------+--------------+-----------------+
|      1 | admin     | admin@qq.com | 超级管理员      |
|      2 | king      | admin@qq.com | 普通管理员      |
|      3 | 麦子      | admin@qq.com | 普通管理员      |
|      4 | queen     | admin@qq.com | 普通管理员      |
|      5 | test      | admin@qq.com | 普通管理员      |
+--------+-----------+--------------+-----------------+
5 rows in set (0.00 sec)

mysql> SELECT a.id AS i,a.username AS u,a.email as e,a.role AS r FROM cms_admin AS a; 
+---+--------+--------------+-----------------+
| i | u      | e            | r               |
+---+--------+--------------+-----------------+
| 1 | admin  | admin@qq.com | 超级管理员      |
| 2 | king   | admin@qq.com | 普通管理员      |
| 3 | 麦子   | admin@qq.com | 普通管理员      |
| 4 | queen  | admin@qq.com | 普通管理员      |
| 5 | test   | admin@qq.com | 普通管理员      |
+---+--------+--------------+-----------------+
5 rows in set (0.00 sec)

mysql> DESC cms_user;
+----------+---------------------+------+-----+-------------+----------------+
| Field    | Type                | Null | Key | Default     | Extra          |
+----------+---------------------+------+-----+-------------+----------------+
| id       | int(10) unsigned    | NO   | PRI | NULL        | auto_increment |
| username | varchar(20)         | NO   | UNI | NULL        |                |
| password | char(32)            | NO   |     | NULL        |                |
| email    | varchar(50)         | NO   |     | user@qq.com |                |
| regTime  | int(10) unsigned    | NO   |     | NULL        |                |
| face     | varchar(100)        | NO   |     | user.jpg    |                |
| proId    | tinyint(3) unsigned | NO   |     | NULL        |                |
+----------+---------------------+------+-----+-------------+----------------+
7 rows in set (0.02 sec)

mysql> SELECT * FROM cms_user;
+----+-----------+--------------+-------------+------------+----------+-------+
| id | username  | password     | email       | regTime    | face     | proId |
+----+-----------+--------------+-------------+------------+----------+-------+
|  1 | 张三      | zhangsan     | user@qq.com | 1419811708 | user.jpg |     1 |
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |
|  3 | 章子怡    | zhangsan     | user@qq.com | 1419813708 | user.jpg |     3 |
|  4 | long      | long         | user@qq.com | 1419814708 | user.jpg |     4 |
|  5 | ring      | ring         | user@qq.com | 1419815708 | user.jpg |     2 |
|  6 | queen     | queen        | user@qq.com | 1419861708 | user.jpg |     3 |
|  7 | king      | king         | user@qq.com | 1419817708 | user.jpg |     5 |
|  8 | blek      | blek         | user@qq.com | 1419818708 | user.jpg |     1 |
|  9 | rose      | rose         | user@qq.com | 1419821708 | user.jpg |     2 |
| 10 | lily      | lily         | user@qq.com | 1419831708 | user.jpg |     2 |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |
+----+-----------+--------------+-------------+------------+----------+-------+
11 rows in set (0.00 sec)

mysql> SELECT id proId,proId id,username FROM cms_user;
+-------+----+-----------+
| proId | id | username  |
+-------+----+-----------+
|     1 |  1 | 张三      |
|     2 |  2 | 张三丰    |
|     3 |  3 | 章子怡    |
|     4 |  4 | long      |
|     5 |  2 | ring      |
|     6 |  3 | queen     |
|     7 |  5 | king      |
|     8 |  1 | blek      |
|     9 |  2 | rose      |
|    10 |  2 | lily      |
|    11 |  2 | john      |
+-------+----+-----------+
11 rows in set (0.00 sec)

mysql> SELECT 1,2,3,4,5,id,username FROM cms_user;
+---+---+---+---+---+----+-----------+
| 1 | 2 | 3 | 4 | 5 | id | username  |
+---+---+---+---+---+----+-----------+
| 1 | 2 | 3 | 4 | 5 |  8 | blek      |
| 1 | 2 | 3 | 4 | 5 | 11 | john      |
| 1 | 2 | 3 | 4 | 5 |  7 | king      |
| 1 | 2 | 3 | 4 | 5 | 10 | lily      |
| 1 | 2 | 3 | 4 | 5 |  4 | long      |
| 1 | 2 | 3 | 4 | 5 |  6 | queen     |
| 1 | 2 | 3 | 4 | 5 |  5 | ring      |
| 1 | 2 | 3 | 4 | 5 |  9 | rose      |
| 1 | 2 | 3 | 4 | 5 |  1 | 张三      |
| 1 | 2 | 3 | 4 | 5 |  2 | 张三丰    |
| 1 | 2 | 3 | 4 | 5 |  3 | 章子怡    |
+---+---+---+---+---+----+-----------+
11 rows in set (0.01 sec)

WHERE 条件 关键字

基本查询包含where 条件

mysql> SELECT * FROM cms_user;
+----+-----------+--------------+-------------+------------+----------+-------+
| id | username  | password     | email       | regTime    | face     | proId |
+----+-----------+--------------+-------------+------------+----------+-------+
|  1 | 张三      | zhangsan     | user@qq.com | 1419811708 | user.jpg |     1 |
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |
|  3 | 章子怡    | zhangsan     | user@qq.com | 1419813708 | user.jpg |     3 |
|  4 | long      | long         | user@qq.com | 1419814708 | user.jpg |     4 |
|  5 | ring      | ring         | user@qq.com | 1419815708 | user.jpg |     2 |
|  6 | queen     | queen        | user@qq.com | 1419861708 | user.jpg |     3 |
|  7 | king      | king         | user@qq.com | 1419817708 | user.jpg |     5 |
|  8 | blek      | blek         | user@qq.com | 1419818708 | user.jpg |     1 |
|  9 | rose      | rose         | user@qq.com | 1419821708 | user.jpg |     2 |
| 10 | lily      | lily         | user@qq.com | 1419831708 | user.jpg |     2 |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |
+----+-----------+--------------+-------------+------------+----------+-------+
11 rows in set (0.00 sec)

mysql> SELECT id,username,email FROM cms_user WHERE id=1;
+----+----------+-------------+
| id | username | email       |
+----+----------+-------------+
|  1 | 张三     | user@qq.com |
+----+----------+-------------+
1 row in set (0.10 sec)

mysql> SELECT id,username,email FROM cms_user WHERE id=111;
Empty set (0.00 sec)

mysql> SELECT id,username,email FROM cms_user WHERE username='king';
+----+----------+-------------+
| id | username | email       |
+----+----------+-------------+
|  7 | king     | user@qq.com |
+----+----------+-------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM cms_user WHERE id>=5;
+----+----------+----------+-------------+------------+----------+-------+
| id | username | password | email       | regTime    | face     | proId |
+----+----------+----------+-------------+------------+----------+-------+
|  5 | ring     | ring     | user@qq.com | 1419815708 | user.jpg |     2 |
|  6 | queen    | queen    | user@qq.com | 1419861708 | user.jpg |     3 |
|  7 | king     | king     | user@qq.com | 1419817708 | user.jpg |     5 |
|  8 | blek     | blek     | user@qq.com | 1419818708 | user.jpg |     1 |
|  9 | rose     | rose     | user@qq.com | 1419821708 | user.jpg |     2 |
| 10 | lily     | lily     | user@qq.com | 1419831708 | user.jpg |     2 |
| 11 | john     | john     | user@qq.com | 1419841708 | user.jpg |     2 |
+----+----------+----------+-------------+------------+----------+-------+
7 rows in set (0.04 sec)

mysql> SELECT  * FROM cms_user WHERE id!=1;
+----+-----------+--------------+-------------+------------+----------+-------+
| id | username  | password     | email       | regTime    | face     | proId |
+----+-----------+--------------+-------------+------------+----------+-------+
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |
|  3 | 章子怡    | zhangsan     | user@qq.com | 1419813708 | user.jpg |     3 |
|  4 | long      | long         | user@qq.com | 1419814708 | user.jpg |     4 |
|  5 | ring      | ring         | user@qq.com | 1419815708 | user.jpg |     2 |
|  6 | queen     | queen        | user@qq.com | 1419861708 | user.jpg |     3 |
|  7 | king      | king         | user@qq.com | 1419817708 | user.jpg |     5 |
|  8 | blek      | blek         | user@qq.com | 1419818708 | user.jpg |     1 |
|  9 | rose      | rose         | user@qq.com | 1419821708 | user.jpg |     2 |
| 10 | lily      | lily         | user@qq.com | 1419831708 | user.jpg |     2 |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |
+----+-----------+--------------+-------------+------------+----------+-------+
10 rows in set (0.00 sec)

mysql> SELECT  * FROM cms_user WHERE id<>1;
+----+-----------+--------------+-------------+------------+----------+-------+
| id | username  | password     | email       | regTime    | face     | proId |
+----+-----------+--------------+-------------+------------+----------+-------+
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |
|  3 | 章子怡    | zhangsan     | user@qq.com | 1419813708 | user.jpg |     3 |
|  4 | long      | long         | user@qq.com | 1419814708 | user.jpg |     4 |
|  5 | ring      | ring         | user@qq.com | 1419815708 | user.jpg |     2 |
|  6 | queen     | queen        | user@qq.com | 1419861708 | user.jpg |     3 |
|  7 | king      | king         | user@qq.com | 1419817708 | user.jpg |     5 |
|  8 | blek      | blek         | user@qq.com | 1419818708 | user.jpg |     1 |
|  9 | rose      | rose         | user@qq.com | 1419821708 | user.jpg |     2 |
| 10 | lily      | lily         | user@qq.com | 1419831708 | user.jpg |     2 |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |
+----+-----------+--------------+-------------+------------+----------+-------+
10 rows in set (0.00 sec)

mysql> ALTER TABLE cms_user ADD age TINYINT UNSIGNED DEFAULT 18;
Query OK, 0 rows affected (0.89 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT  * FROM cms_user WHERE id<>1;
+----+-----------+--------------+-------------+------------+----------+-------+------+
| id | username  | password     | email       | regTime    | face     | proId | age  |
+----+-----------+--------------+-------------+------------+----------+-------+------+
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   18 |
|  3 | 章子怡    | zhangsan     | user@qq.com | 1419813708 | user.jpg |     3 |   18 |
|  4 | long      | long         | user@qq.com | 1419814708 | user.jpg |     4 |   18 |
|  5 | ring      | ring         | user@qq.com | 1419815708 | user.jpg |     2 |   18 |
|  6 | queen     | queen        | user@qq.com | 1419861708 | user.jpg |     3 |   18 |
|  7 | king      | king         | user@qq.com | 1419817708 | user.jpg |     5 |   18 |
|  8 | blek      | blek         | user@qq.com | 1419818708 | user.jpg |     1 |   18 |
|  9 | rose      | rose         | user@qq.com | 1419821708 | user.jpg |     2 |   18 |
| 10 | lily      | lily         | user@qq.com | 1419831708 | user.jpg |     2 |   18 |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |   18 |
+----+-----------+--------------+-------------+------------+----------+-------+------+
10 rows in set (0.00 sec)

mysql> DESC cms_user;
+----------+---------------------+------+-----+-------------+----------------+
| Field    | Type                | Null | Key | Default     | Extra          |
+----------+---------------------+------+-----+-------------+----------------+
| id       | int(10) unsigned    | NO   | PRI | NULL        | auto_increment |
| username | varchar(20)         | NO   | UNI | NULL        |                |
| password | char(32)            | NO   |     | NULL        |                |
| email    | varchar(50)         | NO   |     | user@qq.com |                |
| regTime  | int(10) unsigned    | NO   |     | NULL        |                |
| face     | varchar(100)        | NO   |     | user.jpg    |                |
| proId    | tinyint(3) unsigned | NO   |     | NULL        |                |
| age      | tinyint(3) unsigned | YES  |     | 18          |                |
+----------+---------------------+------+-----+-------------+----------------+
8 rows in set (0.08 sec)

mysql> INSERT cms_user(username,password,regTime,proId,age)
    -> 
    -> VALUES('test1','test1',1419811708,1,NULL);
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM cms_user;
+----+-----------+--------------+-------------+------------+----------+-------+------+
| id | username  | password     | email       | regTime    | face     | proId | age  |
+----+-----------+--------------+-------------+------------+----------+-------+------+
|  1 | 张三      | zhangsan     | user@qq.com | 1419811708 | user.jpg |     1 |   18 |
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   18 |
|  3 | 章子怡    | zhangsan     | user@qq.com | 1419813708 | user.jpg |     3 |   18 |
|  4 | long      | long         | user@qq.com | 1419814708 | user.jpg |     4 |   18 |
|  5 | ring      | ring         | user@qq.com | 1419815708 | user.jpg |     2 |   18 |
|  6 | queen     | queen        | user@qq.com | 1419861708 | user.jpg |     3 |   18 |
|  7 | king      | king         | user@qq.com | 1419817708 | user.jpg |     5 |   18 |
|  8 | blek      | blek         | user@qq.com | 1419818708 | user.jpg |     1 |   18 |
|  9 | rose      | rose         | user@qq.com | 1419821708 | user.jpg |     2 |   18 |
| 10 | lily      | lily         | user@qq.com | 1419831708 | user.jpg |     2 |   18 |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |   18 |
| 12 | test1     | test1        | user@qq.com | 1419811708 | user.jpg |     1 | NULL |
+----+-----------+--------------+-------------+------------+----------+-------+------+
12 rows in set (0.00 sec)

mysql> SELECT * FROM cms_user WHERE age=NULL;
Empty set (0.03 sec)

mysql> SELECT * FROM cms_user WHERE age<=>NULL;
+----+----------+----------+-------------+------------+----------+-------+------+
| id | username | password | email       | regTime    | face     | proId | age  |
+----+----------+----------+-------------+------------+----------+-------+------+
| 12 | test1    | test1    | user@qq.com | 1419811708 | user.jpg |     1 | NULL |
+----+----------+----------+-------------+------------+----------+-------+------+
1 row in set (0.03 sec)

mysql> SELECT * FROM cms_user WHERE age<=>18;
+----+-----------+--------------+-------------+------------+----------+-------+------+
| id | username  | password     | email       | regTime    | face     | proId | age  |
+----+-----------+--------------+-------------+------------+----------+-------+------+
|  1 | 张三      | zhangsan     | user@qq.com | 1419811708 | user.jpg |     1 |   18 |
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   18 |
|  3 | 章子怡    | zhangsan     | user@qq.com | 1419813708 | user.jpg |     3 |   18 |
|  4 | long      | long         | user@qq.com | 1419814708 | user.jpg |     4 |   18 |
|  5 | ring      | ring         | user@qq.com | 1419815708 | user.jpg |     2 |   18 |
|  6 | queen     | queen        | user@qq.com | 1419861708 | user.jpg |     3 |   18 |
|  7 | king      | king         | user@qq.com | 1419817708 | user.jpg |     5 |   18 |
|  8 | blek      | blek         | user@qq.com | 1419818708 | user.jpg |     1 |   18 |
|  9 | rose      | rose         | user@qq.com | 1419821708 | user.jpg |     2 |   18 |
| 10 | lily      | lily         | user@qq.com | 1419831708 | user.jpg |     2 |   18 |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |   18 |
+----+-----------+--------------+-------------+------------+----------+-------+------+
11 rows in set (0.00 sec)

mysql> SELECT * FROM cms_user WHERE age IS NULL;
+----+----------+----------+-------------+------------+----------+-------+------+
| id | username | password | email       | regTime    | face     | proId | age  |
+----+----------+----------+-------------+------------+----------+-------+------+
| 12 | test1    | test1    | user@qq.com | 1419811708 | user.jpg |     1 | NULL |
+----+----------+----------+-------------+------------+----------+-------+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM cms_user WHERE age IS NOT NULL;
+----+-----------+--------------+-------------+------------+----------+-------+------+
| id | username  | password     | email       | regTime    | face     | proId | age  |
+----+-----------+--------------+-------------+------------+----------+-------+------+
|  1 | 张三      | zhangsan     | user@qq.com | 1419811708 | user.jpg |     1 |   18 |
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   18 |
|  3 | 章子怡    | zhangsan     | user@qq.com | 1419813708 | user.jpg |     3 |   18 |
|  4 | long      | long         | user@qq.com | 1419814708 | user.jpg |     4 |   18 |
|  5 | ring      | ring         | user@qq.com | 1419815708 | user.jpg |     2 |   18 |
|  6 | queen     | queen        | user@qq.com | 1419861708 | user.jpg |     3 |   18 |
|  7 | king      | king         | user@qq.com | 1419817708 | user.jpg |     5 |   18 |
|  8 | blek      | blek         | user@qq.com | 1419818708 | user.jpg |     1 |   18 |
|  9 | rose      | rose         | user@qq.com | 1419821708 | user.jpg |     2 |   18 |
| 10 | lily      | lily         | user@qq.com | 1419831708 | user.jpg |     2 |   18 |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |   18 |
+----+-----------+--------------+-------------+------------+----------+-------+------+
11 rows in set (0.00 sec)

BEYWEEN BY 范围查询

mysql> SELECT * FROM cms_user WHERE id BETWEEN 3 AND 10;
+----+-----------+----------+-------------+------------+----------+-------+------+
| id | username  | password | email       | regTime    | face     | proId | age  |
+----+-----------+----------+-------------+------------+----------+-------+------+
|  3 | 章子怡    | zhangsan | user@qq.com | 1419813708 | user.jpg |     3 |   18 |
|  4 | long      | long     | user@qq.com | 1419814708 | user.jpg |     4 |   18 |
|  5 | ring      | ring     | user@qq.com | 1419815708 | user.jpg |     2 |   18 |
|  6 | queen     | queen    | user@qq.com | 1419861708 | user.jpg |     3 |   18 |
|  7 | king      | king     | user@qq.com | 1419817708 | user.jpg |     5 |   18 |
|  8 | blek      | blek     | user@qq.com | 1419818708 | user.jpg |     1 |   18 |
|  9 | rose      | rose     | user@qq.com | 1419821708 | user.jpg |     2 |   18 |
| 10 | lily      | lily     | user@qq.com | 1419831708 | user.jpg |     2 |   18 |
+----+-----------+----------+-------------+------------+----------+-------+------+
8 rows in set (0.02 sec)

mysql> SELECT * FROM cms_user WHERE id NOT BETWEEN 3 AND 10;
+----+-----------+--------------+-------------+------------+----------+-------+------+
| id | username  | password     | email       | regTime    | face     | proId | age  |
+----+-----------+--------------+-------------+------------+----------+-------+------+
|  1 | 张三      | zhangsan     | user@qq.com | 1419811708 | user.jpg |     1 |   18 |
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   18 |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |   18 |
| 12 | test1     | test1        | user@qq.com | 1419811708 | user.jpg |     1 | NULL |
+----+-----------+--------------+-------------+------------+----------+-------+------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM cms_user WHERE id IN(1,3,5,7,9,11,13,100,1000);
+----+-----------+----------+-------------+------------+----------+-------+------+
| id | username  | password | email       | regTime    | face     | proId | age  |
+----+-----------+----------+-------------+------------+----------+-------+------+
|  1 | 张三      | zhangsan | user@qq.com | 1419811708 | user.jpg |     1 |   18 |
|  3 | 章子怡    | zhangsan | user@qq.com | 1419813708 | user.jpg |     3 |   18 |
|  5 | ring      | ring     | user@qq.com | 1419815708 | user.jpg |     2 |   18 |
|  7 | king      | king     | user@qq.com | 1419817708 | user.jpg |     5 |   18 |
|  9 | rose      | rose     | user@qq.com | 1419821708 | user.jpg |     2 |   18 |
| 11 | john      | john     | user@qq.com | 1419841708 | user.jpg |     2 |   18 |
+----+-----------+----------+-------------+------------+----------+-------+------+
6 rows in set (0.03 sec)

mysql> SELECT * FROM cms_user WHERE proId IN(1,3);
+----+-----------+----------+-------------+------------+----------+-------+------+
| id | username  | password | email       | regTime    | face     | proId | age  |
+----+-----------+----------+-------------+------------+----------+-------+------+
|  1 | 张三      | zhangsan | user@qq.com | 1419811708 | user.jpg |     1 |   18 |
|  3 | 章子怡    | zhangsan | user@qq.com | 1419813708 | user.jpg |     3 |   18 |
|  6 | queen     | queen    | user@qq.com | 1419861708 | user.jpg |     3 |   18 |
|  8 | blek      | blek     | user@qq.com | 1419818708 | user.jpg |     1 |   18 |
| 12 | test1     | test1    | user@qq.com | 1419811708 | user.jpg |     1 | NULL |
+----+-----------+----------+-------------+------------+----------+-------+------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM cms_user WHERE username IN('king','queen','张三','章子怡');
+----+-----------+----------+-------------+------------+----------+-------+------+
| id | username  | password | email       | regTime    | face     | proId | age  |
+----+-----------+----------+-------------+------------+----------+-------+------+
|  1 | 张三      | zhangsan | user@qq.com | 1419811708 | user.jpg |     1 |   18 |
|  3 | 章子怡    | zhangsan | user@qq.com | 1419813708 | user.jpg |     3 |   18 |
|  6 | queen     | queen    | user@qq.com | 1419861708 | user.jpg |     3 |   18 |
|  7 | king      | king     | user@qq.com | 1419817708 | user.jpg |     5 |   18 |
+----+-----------+----------+-------------+------------+----------+-------+------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM cms_user WHERE username IN('KinG','QUEEN','张三','章子怡');
+----+-----------+----------+-------------+------------+----------+-------+------+
| id | username  | password | email       | regTime    | face     | proId | age  |
+----+-----------+----------+-------------+------------+----------+-------+------+
|  1 | 张三      | zhangsan | user@qq.com | 1419811708 | user.jpg |     1 |   18 |
|  3 | 章子怡    | zhangsan | user@qq.com | 1419813708 | user.jpg |     3 |   18 |
|  6 | queen     | queen    | user@qq.com | 1419861708 | user.jpg |     3 |   18 |
|  7 | king      | king     | user@qq.com | 1419817708 | user.jpg |     5 |   18 |
+----+-----------+----------+-------------+------------+----------+-------+------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM cms_user WHERE username NOT IN('KinG','QUEEN','张三','章子怡');
+----+-----------+--------------+-------------+------------+----------+-------+------+
| id | username  | password     | email       | regTime    | face     | proId | age  |
+----+-----------+--------------+-------------+------------+----------+-------+------+
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   18 |
|  4 | long      | long         | user@qq.com | 1419814708 | user.jpg |     4 |   18 |
|  5 | ring      | ring         | user@qq.com | 1419815708 | user.jpg |     2 |   18 |
|  8 | blek      | blek         | user@qq.com | 1419818708 | user.jpg |     1 |   18 |
|  9 | rose      | rose         | user@qq.com | 1419821708 | user.jpg |     2 |   18 |
| 10 | lily      | lily         | user@qq.com | 1419831708 | user.jpg |     2 |   18 |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |   18 |
| 12 | test1     | test1        | user@qq.com | 1419811708 | user.jpg |     1 | NULL |
+----+-----------+--------------+-------------+------------+----------+-------+------+
8 rows in set (0.00 sec)

模糊查询

mysql> SELECT * FROM cms_user WHERE username LIKE '%张%';
+----+-----------+--------------+-------------+------------+----------+-------+------+
| id | username  | password     | email       | regTime    | face     | proId | age  |
+----+-----------+--------------+-------------+------------+----------+-------+------+
|  1 | 张三      | zhangsan     | user@qq.com | 1419811708 | user.jpg |     1 |   18 |
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   18 |
+----+-----------+--------------+-------------+------------+----------+-------+------+
2 rows in set (0.02 sec)

mysql> SELECT * FROM cms_user WHERE username LIKE '张%';
+----+-----------+--------------+-------------+------------+----------+-------+------+
| id | username  | password     | email       | regTime    | face     | proId | age  |
+----+-----------+--------------+-------------+------------+----------+-------+------+
|  1 | 张三      | zhangsan     | user@qq.com | 1419811708 | user.jpg |     1 |   18 |
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   18 |
+----+-----------+--------------+-------------+------------+----------+-------+------+
2 rows in set (0.01 sec)

mysql> SELECT * FROM cms_user WHERE username LIKE '%in%';
+----+----------+----------+-------------+------------+----------+-------+------+
| id | username | password | email       | regTime    | face     | proId | age  |
+----+----------+----------+-------------+------------+----------+-------+------+
|  5 | ring     | ring     | user@qq.com | 1419815708 | user.jpg |     2 |   18 |
|  7 | king     | king     | user@qq.com | 1419817708 | user.jpg |     5 |   18 |
+----+----------+----------+-------------+------------+----------+-------+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM cms_user WHERE username LIKE '%';
+----+-----------+--------------+-------------+------------+----------+-------+------+
| id | username  | password     | email       | regTime    | face     | proId | age  |
+----+-----------+--------------+-------------+------------+----------+-------+------+
|  1 | 张三      | zhangsan     | user@qq.com | 1419811708 | user.jpg |     1 |   18 |
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   18 |
|  3 | 章子怡    | zhangsan     | user@qq.com | 1419813708 | user.jpg |     3 |   18 |
|  4 | long      | long         | user@qq.com | 1419814708 | user.jpg |     4 |   18 |
|  5 | ring      | ring         | user@qq.com | 1419815708 | user.jpg |     2 |   18 |
|  6 | queen     | queen        | user@qq.com | 1419861708 | user.jpg |     3 |   18 |
|  7 | king      | king         | user@qq.com | 1419817708 | user.jpg |     5 |   18 |
|  8 | blek      | blek         | user@qq.com | 1419818708 | user.jpg |     1 |   18 |
|  9 | rose      | rose         | user@qq.com | 1419821708 | user.jpg |     2 |   18 |
| 10 | lily      | lily         | user@qq.com | 1419831708 | user.jpg |     2 |   18 |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |   18 |
| 12 | test1     | test1        | user@qq.com | 1419811708 | user.jpg |     1 | NULL |
+----+-----------+--------------+-------------+------------+----------+-------+------+
12 rows in set (0.00 sec)

mysql> SELECT * FROM cms_user WHERE username LIKE '___';
+----+-----------+--------------+-------------+------------+----------+-------+------+
| id | username  | password     | email       | regTime    | face     | proId | age  |
+----+-----------+--------------+-------------+------------+----------+-------+------+
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   18 |
|  3 | 章子怡    | zhangsan     | user@qq.com | 1419813708 | user.jpg |     3 |   18 |
+----+-----------+--------------+-------------+------------+----------+-------+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM cms_user WHERE username LIKE '____';
+----+----------+----------+-------------+------------+----------+-------+------+
| id | username | password | email       | regTime    | face     | proId | age  |
+----+----------+----------+-------------+------------+----------+-------+------+
|  4 | long     | long     | user@qq.com | 1419814708 | user.jpg |     4 |   18 |
|  5 | ring     | ring     | user@qq.com | 1419815708 | user.jpg |     2 |   18 |
|  7 | king     | king     | user@qq.com | 1419817708 | user.jpg |     5 |   18 |
|  8 | blek     | blek     | user@qq.com | 1419818708 | user.jpg |     1 |   18 |
|  9 | rose     | rose     | user@qq.com | 1419821708 | user.jpg |     2 |   18 |
| 10 | lily     | lily     | user@qq.com | 1419831708 | user.jpg |     2 |   18 |
| 11 | john     | john     | user@qq.com | 1419841708 | user.jpg |     2 |   18 |
+----+----------+----------+-------------+------------+----------+-------+------+
7 rows in set (0.00 sec)

mysql> SELECT * FROM cms_user WHERE username LIKE '_I%';
+----+----------+----------+-------------+------------+----------+-------+------+
| id | username | password | email       | regTime    | face     | proId | age  |
+----+----------+----------+-------------+------------+----------+-------+------+
|  5 | ring     | ring     | user@qq.com | 1419815708 | user.jpg |     2 |   18 |
|  7 | king     | king     | user@qq.com | 1419817708 | user.jpg |     5 |   18 |
| 10 | lily     | lily     | user@qq.com | 1419831708 | user.jpg |     2 |   18 |
+----+----------+----------+-------------+------------+----------+-------+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM cms_user WHERE username LIKE 'king';
+----+----------+----------+-------------+------------+----------+-------+------+
| id | username | password | email       | regTime    | face     | proId | age  |
+----+----------+----------+-------------+------------+----------+-------+------+
|  7 | king     | king     | user@qq.com | 1419817708 | user.jpg |     5 |   18 |
+----+----------+----------+-------------+------------+----------+-------+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM cms_user WHERE username NOT LIKE '_I%';
+----+-----------+--------------+-------------+------------+----------+-------+------+
| id | username  | password     | email       | regTime    | face     | proId | age  |
+----+-----------+--------------+-------------+------------+----------+-------+------+
|  1 | 张三      | zhangsan     | user@qq.com | 1419811708 | user.jpg |     1 |   18 |
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   18 |
|  3 | 章子怡    | zhangsan     | user@qq.com | 1419813708 | user.jpg |     3 |   18 |
|  4 | long      | long         | user@qq.com | 1419814708 | user.jpg |     4 |   18 |
|  6 | queen     | queen        | user@qq.com | 1419861708 | user.jpg |     3 |   18 |
|  8 | blek      | blek         | user@qq.com | 1419818708 | user.jpg |     1 |   18 |
|  9 | rose      | rose         | user@qq.com | 1419821708 | user.jpg |     2 |   18 |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |   18 |
| 12 | test1     | test1        | user@qq.com | 1419811708 | user.jpg |     1 | NULL |
+----+-----------+--------------+-------------+------------+----------+-------+------+
9 rows in set (0.03 sec)

mysql> SELECT * FROM cms_user WHERE username='king' AND password='king';
+----+----------+----------+-------------+------------+----------+-------+------+
| id | username | password | email       | regTime    | face     | proId | age  |
+----+----------+----------+-------------+------------+----------+-------+------+
|  7 | king     | king     | user@qq.com | 1419817708 | user.jpg |     5 |   18 |
+----+----------+----------+-------------+------------+----------+-------+------+
1 row in set (0.02 sec)

mysql> SELECT * FROM cms_user WHERE id>=3 AND age IS NOT NULL;
+----+-----------+----------+-------------+------------+----------+-------+------+
| id | username  | password | email       | regTime    | face     | proId | age  |
+----+-----------+----------+-------------+------------+----------+-------+------+
|  3 | 章子怡    | zhangsan | user@qq.com | 1419813708 | user.jpg |     3 |   18 |
|  4 | long      | long     | user@qq.com | 1419814708 | user.jpg |     4 |   18 |
|  5 | ring      | ring     | user@qq.com | 1419815708 | user.jpg |     2 |   18 |
|  6 | queen     | queen    | user@qq.com | 1419861708 | user.jpg |     3 |   18 |
|  7 | king      | king     | user@qq.com | 1419817708 | user.jpg |     5 |   18 |
|  8 | blek      | blek     | user@qq.com | 1419818708 | user.jpg |     1 |   18 |
|  9 | rose      | rose     | user@qq.com | 1419821708 | user.jpg |     2 |   18 |
| 10 | lily      | lily     | user@qq.com | 1419831708 | user.jpg |     2 |   18 |
| 11 | john      | john     | user@qq.com | 1419841708 | user.jpg |     2 |   18 |
+----+-----------+----------+-------------+------------+----------+-------+------+
9 rows in set (0.02 sec)

mysql> SELECT * FROM cms_user WHERE id>=3 AND age IS NOT NULL AND proId=3;
+----+-----------+----------+-------------+------------+----------+-------+------+
| id | username  | password | email       | regTime    | face     | proId | age  |
+----+-----------+----------+-------------+------------+----------+-------+------+
|  3 | 章子怡    | zhangsan | user@qq.com | 1419813708 | user.jpg |     3 |   18 |
|  6 | queen     | queen    | user@qq.com | 1419861708 | user.jpg |     3 |   18 |
+----+-----------+----------+-------------+------------+----------+-------+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM cms_user WHERE id BETWEEN 5 AND 10 AND username LIKE '____';
+----+----------+----------+-------------+------------+----------+-------+------+
| id | username | password | email       | regTime    | face     | proId | age  |
+----+----------+----------+-------------+------------+----------+-------+------+
|  5 | ring     | ring     | user@qq.com | 1419815708 | user.jpg |     2 |   18 |
|  7 | king     | king     | user@qq.com | 1419817708 | user.jpg |     5 |   18 |
|  8 | blek     | blek     | user@qq.com | 1419818708 | user.jpg |     1 |   18 |
|  9 | rose     | rose     | user@qq.com | 1419821708 | user.jpg |     2 |   18 |
| 10 | lily     | lily     | user@qq.com | 1419831708 | user.jpg |     2 |   18 |
+----+----------+----------+-------------+------------+----------+-------+------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM cms_user WHERE username LIKE '张%' OR proId IN(2,4);
+----+-----------+--------------+-------------+------------+----------+-------+------+
| id | username  | password     | email       | regTime    | face     | proId | age  |
+----+-----------+--------------+-------------+------------+----------+-------+------+
|  1 | 张三      | zhangsan     | user@qq.com | 1419811708 | user.jpg |     1 |   18 |
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   18 |
|  4 | long      | long         | user@qq.com | 1419814708 | user.jpg |     4 |   18 |
|  5 | ring      | ring         | user@qq.com | 1419815708 | user.jpg |     2 |   18 |
|  9 | rose      | rose         | user@qq.com | 1419821708 | user.jpg |     2 |   18 |
| 10 | lily      | lily         | user@qq.com | 1419831708 | user.jpg |     2 |   18 |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |   18 |
+----+-----------+--------------+-------------+------------+----------+-------+------+
7 rows in set (0.00 sec)

分组查询 GROUP BY

  • 配合聚合函数COUNT()、MAX()、MIN()、AVG()、SUM()
  • 配合WITH ROLLUP记录上面所有记录的总和
mysql> SELECT * FROM cms_user;
+----+-----------+--------------+-------------+------------+----------+-------+------+
| id | username  | password     | email       | regTime    | face     | proId | age  |
+----+-----------+--------------+-------------+------------+----------+-------+------+
|  1 | 张三      | zhangsan     | user@qq.com | 1419811708 | user.jpg |     1 |   18 |
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   18 |
|  3 | 章子怡    | zhangsan     | user@qq.com | 1419813708 | user.jpg |     3 |   18 |
|  4 | long      | long         | user@qq.com | 1419814708 | user.jpg |     4 |   18 |
|  5 | ring      | ring         | user@qq.com | 1419815708 | user.jpg |     2 |   18 |
|  6 | queen     | queen        | user@qq.com | 1419861708 | user.jpg |     3 |   18 |
|  7 | king      | king         | user@qq.com | 1419817708 | user.jpg |     5 |   18 |
|  8 | blek      | blek         | user@qq.com | 1419818708 | user.jpg |     1 |   18 |
|  9 | rose      | rose         | user@qq.com | 1419821708 | user.jpg |     2 |   18 |
| 10 | lily      | lily         | user@qq.com | 1419831708 | user.jpg |     2 |   18 |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |   18 |
| 12 | test1     | test1        | user@qq.com | 1419811708 | user.jpg |     1 | NULL |
+----+-----------+--------------+-------------+------------+----------+-------+------+
12 rows in set (0.53 sec)

mysql> SELECT * FROM cms_user GROUP BY proId;
+----+-----------+--------------+-------------+------------+----------+-------+------+
| id | username  | password     | email       | regTime    | face     | proId | age  |
+----+-----------+--------------+-------------+------------+----------+-------+------+
|  1 | 张三      | zhangsan     | user@qq.com | 1419811708 | user.jpg |     1 |   18 |
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   18 |
|  3 | 章子怡    | zhangsan     | user@qq.com | 1419813708 | user.jpg |     3 |   18 |
|  4 | long      | long         | user@qq.com | 1419814708 | user.jpg |     4 |   18 |
|  7 | king      | king         | user@qq.com | 1419817708 | user.jpg |     5 |   18 |
+----+-----------+--------------+-------------+------------+----------+-------+------+
5 rows in set (0.31 sec)

mysql> ALTER TABLE cms_user ADD sex ENUM('男','女','保密');
Query OK, 0 rows affected (1.56 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> UPDATE cms_user SET sex='男' WHERE id IN(1,3,5,7,9);
Query OK, 5 rows affected (0.07 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> 
mysql> UPDATE cms_user SET sex='女' WHERE id IN(2,4,6,8,10);
Query OK, 5 rows affected (0.04 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> 
mysql> UPDATE cms_user SET sex='女' WHERE id IN(2,4,6,8,10);
Query OK, 0 rows affected (0.06 sec)
Rows matched: 5  Changed: 0  Warnings: 0

mysql> 
mysql> UPDATE cms_user SET sex='保密' WHERE id IN(12,11);
Query OK, 2 rows affected (0.05 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> SELECT * FROM cms_user;
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
| id | username  | password     | email       | regTime    | face     | proId | age  | sex    |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
|  1 | 张三      | zhangsan     | user@qq.com | 1419811708 | user.jpg |     1 |   18 | 男     |
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   18 | 女     |
|  3 | 章子怡    | zhangsan     | user@qq.com | 1419813708 | user.jpg |     3 |   18 | 男     |
|  4 | long      | long         | user@qq.com | 1419814708 | user.jpg |     4 |   18 | 女     |
|  5 | ring      | ring         | user@qq.com | 1419815708 | user.jpg |     2 |   18 | 男     |
|  6 | queen     | queen        | user@qq.com | 1419861708 | user.jpg |     3 |   18 | 女     |
|  7 | king      | king         | user@qq.com | 1419817708 | user.jpg |     5 |   18 | 男     |
|  8 | blek      | blek         | user@qq.com | 1419818708 | user.jpg |     1 |   18 | 女     |
|  9 | rose      | rose         | user@qq.com | 1419821708 | user.jpg |     2 |   18 | 男     |
| 10 | lily      | lily         | user@qq.com | 1419831708 | user.jpg |     2 |   18 | 女     |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |   18 | 保密   |
| 12 | test1     | test1        | user@qq.com | 1419811708 | user.jpg |     1 | NULL | 保密   |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
12 rows in set (0.00 sec)

mysql> SELECT * FROM cms_user GROUP BY sex;
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
| id | username  | password     | email       | regTime    | face     | proId | age  | sex    |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
|  1 | 张三      | zhangsan     | user@qq.com | 1419811708 | user.jpg |     1 |   18 | 男     |
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   18 | 女     |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |   18 | 保密   |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM cms_user GROUP BY 7;
+----+-----------+--------------+-------------+------------+----------+-------+------+------+
| id | username  | password     | email       | regTime    | face     | proId | age  | sex  |
+----+-----------+--------------+-------------+------------+----------+-------+------+------+
|  1 | 张三      | zhangsan     | user@qq.com | 1419811708 | user.jpg |     1 |   18 | 男   |
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   18 | 女   |
|  3 | 章子怡    | zhangsan     | user@qq.com | 1419813708 | user.jpg |     3 |   18 | 男   |
|  4 | long      | long         | user@qq.com | 1419814708 | user.jpg |     4 |   18 | 女   |
|  7 | king      | king         | user@qq.com | 1419817708 | user.jpg |     5 |   18 | 男   |
+----+-----------+--------------+-------------+------------+----------+-------+------+------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM cms_user GROUP BY 9;
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
| id | username  | password     | email       | regTime    | face     | proId | age  | sex    |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
|  1 | 张三      | zhangsan     | user@qq.com | 1419811708 | user.jpg |     1 |   18 | 男     |
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   18 | 女     |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |   18 | 保密   |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM cms_user GROUP BY sex,proId;
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
| id | username  | password     | email       | regTime    | face     | proId | age  | sex    |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
|  1 | 张三      | zhangsan     | user@qq.com | 1419811708 | user.jpg |     1 |   18 | 男     |
|  5 | ring      | ring         | user@qq.com | 1419815708 | user.jpg |     2 |   18 | 男     |
|  3 | 章子怡    | zhangsan     | user@qq.com | 1419813708 | user.jpg |     3 |   18 | 男     |
|  7 | king      | king         | user@qq.com | 1419817708 | user.jpg |     5 |   18 | 男     |
|  8 | blek      | blek         | user@qq.com | 1419818708 | user.jpg |     1 |   18 | 女     |
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   18 | 女     |
|  6 | queen     | queen        | user@qq.com | 1419861708 | user.jpg |     3 |   18 | 女     |
|  4 | long      | long         | user@qq.com | 1419814708 | user.jpg |     4 |   18 | 女     |
| 12 | test1     | test1        | user@qq.com | 1419811708 | user.jpg |     1 | NULL | 保密   |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |   18 | 保密   |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM cms_user WHERE id>=5 GROUP BY sex;
+----+----------+----------+-------------+------------+----------+-------+------+--------+
| id | username | password | email       | regTime    | face     | proId | age  | sex    |
+----+----------+----------+-------------+------------+----------+-------+------+--------+
|  5 | ring     | ring     | user@qq.com | 1419815708 | user.jpg |     2 |   18 | 男     |
|  6 | queen    | queen    | user@qq.com | 1419861708 | user.jpg |     3 |   18 | 女     |
| 11 | john     | john     | user@qq.com | 1419841708 | user.jpg |     2 |   18 | 保密   |
+----+----------+----------+-------------+------------+----------+-------+------+--------+
3 rows in set (0.00 sec)

配置GROUP CONCAT() 得到分组详情

mysql> SELECT id,sex,GROUP_CONCAT(username) FROM cms_user GROUP BY sex;
+----+--------+---------------------------------+
| id | sex    | GROUP_CONCAT(username)          |
+----+--------+---------------------------------+
|  1 | 男     | 张三,rose,king,ring,章子怡      |
|  4 | 女     | long,queen,blek,张三丰,lily     |
| 11 | 保密   | john,test1                      |
+----+--------+---------------------------------+
3 rows in set (0.07 sec)

mysql> SELECT proId,GROUP_CONCAT(username),GROUP_CONCAT(sex),GROUP_CONCAT(regTime)
    -> FROM cms_user GROUP BY proId;
+-------+-------------------------------+------------------------+--------------------------------------------------------+
| proId | GROUP_CONCAT(username)        | GROUP_CONCAT(sex)      | GROUP_CONCAT(regTime)                                  |
+-------+-------------------------------+------------------------+--------------------------------------------------------+
|     1 | 张三,blek,test1               | 男,女,保密             | 1419811708,1419818708,1419811708                       |
|     2 | ring,张三丰,rose,lily,john    | 男,女,男,女,保密       | 1419815708,1419812708,1419821708,1419831708,1419841708 |
|     3 | queen,章子怡                  | 女,男                  | 1419861708,1419813708                                  |
|     4 | long                          | 女                     | 1419814708                                             |
|     5 | king                          | 男                     | 1419817708                                             |
+-------+-------------------------------+------------------------+--------------------------------------------------------+
5 rows in set (0.00 sec)

mysql> SELECT proId,GROUP_CONCAT(username),GROUP_CONCAT(sex),GROUP_CONCAT(regTime)
    -> FROM cms_user GROUP BY proId\G;
*************************** 1. row ***************************
                 proId: 1
GROUP_CONCAT(username): 张三,blek,test1
     GROUP_CONCAT(sex): 男,女,保密
 GROUP_CONCAT(regTime): 1419811708,1419818708,1419811708
*************************** 2. row ***************************
                 proId: 2
GROUP_CONCAT(username): ring,张三丰,rose,lily,john
     GROUP_CONCAT(sex): 男,女,男,女,保密
 GROUP_CONCAT(regTime): 1419815708,1419812708,1419821708,1419831708,1419841708
*************************** 3. row ***************************
                 proId: 3
GROUP_CONCAT(username): queen,章子怡
     GROUP_CONCAT(sex): 女,男
 GROUP_CONCAT(regTime): 1419861708,1419813708
*************************** 4. row ***************************
                 proId: 4
GROUP_CONCAT(username): long
     GROUP_CONCAT(sex): 女
 GROUP_CONCAT(regTime): 1419814708
*************************** 5. row ***************************
                 proId: 5
GROUP_CONCAT(username): king
     GROUP_CONCAT(sex): 男
 GROUP_CONCAT(regTime): 1419817708
5 rows in set (0.00 sec)

ERROR: 
No query specified

配合聚合函数

  • COUNT()：统计记录条数，COUNT()；
  • MAX()：查找最大记录；
  • MIN()：查找最小记录；
  • AVG()：得出平均值；
  • SUM()：得出总和。
mysql> SELECT * FROM cms_user;
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
| id | username  | password     | email       | regTime    | face     | proId | age  | sex    |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
|  1 | 张三      | zhangsan     | user@qq.com | 1419811708 | user.jpg |     1 |   18 | 男     |
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   18 | 女     |
|  3 | 章子怡    | zhangsan     | user@qq.com | 1419813708 | user.jpg |     3 |   18 | 男     |
|  4 | long      | long         | user@qq.com | 1419814708 | user.jpg |     4 |   18 | 女     |
|  5 | ring      | ring         | user@qq.com | 1419815708 | user.jpg |     2 |   18 | 男     |
|  6 | queen     | queen        | user@qq.com | 1419861708 | user.jpg |     3 |   18 | 女     |
|  7 | king      | king         | user@qq.com | 1419817708 | user.jpg |     5 |   18 | 男     |
|  8 | blek      | blek         | user@qq.com | 1419818708 | user.jpg |     1 |   18 | 女     |
|  9 | rose      | rose         | user@qq.com | 1419821708 | user.jpg |     2 |   18 | 男     |
| 10 | lily      | lily         | user@qq.com | 1419831708 | user.jpg |     2 |   18 | 女     |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |   18 | 保密   |
| 12 | test1     | test1        | user@qq.com | 1419811708 | user.jpg |     1 | NULL | 保密   |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
12 rows in set (0.00 sec)

mysql> UPDATE cms_user SET age=11 WHERE id=1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE cms_user SET age=21 WHERE id=2;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE cms_user SET age=33 WHERE id=3;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE cms_user SET age=44 WHERE id=4;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE cms_user SET age=25 WHERE id=5;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE cms_user SET age=77 WHERE id=6;
Query OK, 1 row affected (0.14 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE cms_user SET age=56 WHERE id=7;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE cms_user SET age=88 WHERE id=8;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE cms_user SET age=12 WHERE id=9;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE cms_user SET age=32 WHERE id=10;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE cms_user SET age=65 WHERE id=11;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM cms_user;
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
| id | username  | password     | email       | regTime    | face     | proId | age  | sex    |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
|  1 | 张三      | zhangsan     | user@qq.com | 1419811708 | user.jpg |     1 |   11 | 男     |
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   21 | 女     |
|  3 | 章子怡    | zhangsan     | user@qq.com | 1419813708 | user.jpg |     3 |   33 | 男     |
|  4 | long      | long         | user@qq.com | 1419814708 | user.jpg |     4 |   44 | 女     |
|  5 | ring      | ring         | user@qq.com | 1419815708 | user.jpg |     2 |   25 | 男     |
|  6 | queen     | queen        | user@qq.com | 1419861708 | user.jpg |     3 |   77 | 女     |
|  7 | king      | king         | user@qq.com | 1419817708 | user.jpg |     5 |   56 | 男     |
|  8 | blek      | blek         | user@qq.com | 1419818708 | user.jpg |     1 |   88 | 女     |
|  9 | rose      | rose         | user@qq.com | 1419821708 | user.jpg |     2 |   12 | 男     |
| 10 | lily      | lily         | user@qq.com | 1419831708 | user.jpg |     2 |   32 | 女     |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |   65 | 保密   |
| 12 | test1     | test1        | user@qq.com | 1419811708 | user.jpg |     1 | NULL | 保密   |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
12 rows in set (0.00 sec)

mysql> SELECT id,sex,GROUP_CONCAT(username)AS users,COUNT(*) AS totalUsers FROM cms_user GROUP BY sex;
+----+--------+---------------------------------+------------+
| id | sex    | users                           | totalUsers |
+----+--------+---------------------------------+------------+
|  1 | 男     | 张三,rose,king,ring,章子怡      |          5 |
|  4 | 女     | long,queen,blek,张三丰,lily     |          5 |
| 11 | 保密   | john,test1                      |          2 |
+----+--------+---------------------------------+------------+
3 rows in set (0.02 sec)

mysql> SELECT COUNT(*) AS totalUsers FROM cms_user;
+------------+
| totalUsers |
+------------+
|         12 |
+------------+
1 row in set (0.01 sec)

mysql> SELECT COUNT(id) AS totalUsers FROM cms_user;
+------------+
| totalUsers |
+------------+
|         12 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM cms_user;
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
| id | username  | password     | email       | regTime    | face     | proId | age  | sex    |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
|  1 | 张三      | zhangsan     | user@qq.com | 1419811708 | user.jpg |     1 |   11 | 男     |
|  2 | 张三丰    | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |     2 |   21 | 女     |
|  3 | 章子怡    | zhangsan     | user@qq.com | 1419813708 | user.jpg |     3 |   33 | 男     |
|  4 | long      | long         | user@qq.com | 1419814708 | user.jpg |     4 |   44 | 女     |
|  5 | ring      | ring         | user@qq.com | 1419815708 | user.jpg |     2 |   25 | 男     |
|  6 | queen     | queen        | user@qq.com | 1419861708 | user.jpg |     3 |   77 | 女     |
|  7 | king      | king         | user@qq.com | 1419817708 | user.jpg |     5 |   56 | 男     |
|  8 | blek      | blek         | user@qq.com | 1419818708 | user.jpg |     1 |   88 | 女     |
|  9 | rose      | rose         | user@qq.com | 1419821708 | user.jpg |     2 |   12 | 男     |
| 10 | lily      | lily         | user@qq.com | 1419831708 | user.jpg |     2 |   32 | 女     |
| 11 | john      | john         | user@qq.com | 1419841708 | user.jpg |     2 |   65 | 保密   |
| 12 | test1     | test1        | user@qq.com | 1419811708 | user.jpg |     1 | NULL | 保密   |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
12 rows in set (0.00 sec)

mysql> SELECT COUNT(age) AS totalUsers FROM cms_user;
+------------+
| totalUsers |
+------------+
|         11 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT id,sex,GROUP_CONCAT(username),
    -> COUNT(*) AS totalUsers,
    -> MAX(age) AS max_age,
    -> MIN(age) AS min_age,
    -> AVG(age) AS avg_age,
    -> SUM(age) AS sum_age
    -> FROM cms_user
    -> GROUP BY sex;
+----+--------+---------------------------------+------------+---------+---------+---------+---------+
| id | sex    | GROUP_CONCAT(username)          | totalUsers | max_age | min_age | avg_age | sum_age |
+----+--------+---------------------------------+------------+---------+---------+---------+---------+
|  1 | 男     | 张三,rose,king,ring,章子怡      |          5 |      56 |      11 | 27.4000 |     137 |
|  4 | 女     | long,queen,blek,张三丰,lily     |          5 |      88 |      21 | 52.4000 |     262 |
| 11 | 保密   | john,test1                      |          2 |      65 |      65 | 65.0000 |      65 |
+----+--------+---------------------------------+------------+---------+---------+---------+---------+
3 rows in set (0.05 sec)

WITH ROLLUP

  • 记录上面所有记录的总和
mysql> SELECT id,sex,GROUP_CONCAT(username),
    -> COUNT(*) AS totalUsers,
    -> MAX(age) AS max_age,
    -> MIN(age) AS min_age,
    -> AVG(age) AS avg_age,
    -> SUM(age) AS sum_age
    -> FROM cms_user
    -> GROUP BY sex WITH ROLLUP\G;
*************************** 1. row ***************************
                    id: 1
                   sex: 男
GROUP_CONCAT(username): 张三,rose,king,ring,章子怡
            totalUsers: 5
               max_age: 56
               min_age: 11
               avg_age: 27.4000
               sum_age: 137
*************************** 2. row ***************************
                    id: 4
                   sex: 女
GROUP_CONCAT(username): long,queen,blek,张三丰,lily
            totalUsers: 5
               max_age: 88
               min_age: 21
               avg_age: 52.4000
               sum_age: 262
*************************** 3. row ***************************
                    id: 11
                   sex: 保密
GROUP_CONCAT(username): john,test1
            totalUsers: 2
               max_age: 65
               min_age: 65
               avg_age: 65.0000
               sum_age: 65
*************************** 4. row ***************************
                    id: 11
                   sex: NULL
GROUP_CONCAT(username): 张三,rose,king,ring,章子怡,long,queen,blek,张三丰,lily,john,test1
            totalUsers: 12
               max_age: 88
               min_age: 11
               avg_age: 42.1818
               sum_age: 464
4 rows in set (0.00 sec)

ERROR: 
No query specified

mysql> SELECT id,sex,GROUP_CONCAT(username),
    -> COUNT(*) AS totalUsers,
    -> MAX(age) AS max_age,
    -> MIN(age) AS min_age
    -> FROM cms_user
    -> GROUP BY sex WITH ROLLUP;
+----+--------+---------------------------------------------------------------------------+------------+---------+---------+
| id | sex    | GROUP_CONCAT(username)                                                    | totalUsers | max_age | min_age |
+----+--------+---------------------------------------------------------------------------+------------+---------+---------+
|  1 | 男     | 张三,rose,king,ring,章子怡                                                |          5 |      56 |      11 |
|  4 | 女     | long,queen,blek,张三丰,lily                                               |          5 |      88 |      21 |
| 11 | 保密   | john,test1                                                                |          2 |      65 |      65 |
| 11 | NULL   | 张三,rose,king,ring,章子怡,long,queen,blek,张三丰,lily,john,test1         |         12 |      88 |      11 |
+----+--------+---------------------------------------------------------------------------+------------+---------+---------+
4 rows in set (0.00 sec)

HAVING 子句

  • WHERE 是第一次查询
  • 可以通过HAVING子句对分组结果进行二次筛选
mysql> SELECT sex,GROUP_CONCAT(username) AS users,
    -> COUNT(*) AS totalUsers,
    -> MAX(age) AS max_age,
    -> SUM(age) AS sum_age
    -> FROM cms_user 
    -> GROUP BY sex
    -> HAVING COUNT(*)>2;
+------+---------------------------------+------------+---------+---------+
| sex  | users                           | totalUsers | max_age | sum_age |
+------+---------------------------------+------------+---------+---------+
| 男   | 张三,rose,king,ring,章子怡      |          5 |      56 |     137 |
| 女   | long,queen,blek,张三丰,lily     |          5 |      88 |     262 |
+------+---------------------------------+------------+---------+---------+
2 rows in set (0.02 sec)

mysql> SELECT sex,GROUP_CONCAT(username) AS users,
    -> COUNT(*) AS totalUsers,
    -> MAX(age) AS max_age,
    -> SUM(age) AS sum_age
    -> FROM cms_user 
    -> GROUP BY sex
    -> HAVING COUNT(*)>2 AND MAX(age)>60;
+------+--------------------------------+------------+---------+---------+
| sex  | users                          | totalUsers | max_age | sum_age |
+------+--------------------------------+------------+---------+---------+
| 女   | long,queen,blek,张三丰,lily    |          5 |      88 |     262 |
+------+--------------------------------+------------+---------+---------+
1 row in set (0.00 sec)

mysql> SELECT sex,GROUP_CONCAT(username) AS users,
    -> COUNT(*) AS totalUsers,
    -> MAX(age) AS max_age,
    -> SUM(age) AS sum_age
    -> FROM cms_user 
    -> WHERE id>=2
    -> GROUP BY sex
    -> HAVING COUNT(*)>2 AND MAX(age)>60;
+------+--------------------------------+------------+---------+---------+
| sex  | users                          | totalUsers | max_age | sum_age |
+------+--------------------------------+------------+---------+---------+
| 女   | lily,blek,张三丰,queen,long    |          5 |      88 |     262 |
+------+--------------------------------+------------+---------+---------+
1 row in set (0.00 sec)

mysql> SELECT sex,GROUP_CONCAT(username) AS users,
    -> COUNT(*) AS totalUsers,
    -> MAX(age) AS max_age,
    -> SUM(age) AS sum_age
    -> FROM cms_user 
    -> WHERE id>=2
    -> HAVING COUNT(*)>2 AND MAX(age)>60;
+------+--------------------------------------------------------------------+------------+---------+---------+
| sex  | users                                                              | totalUsers | max_age | sum_age |
+------+--------------------------------------------------------------------+------------+---------+---------+
| 女   | 张三丰,章子怡,long,ring,queen,king,blek,rose,lily,john,test1       |         11 |      88 |     453 |
+------+--------------------------------------------------------------------+------------+---------+---------+
1 row in set (0.00 sec)
zzugsh
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL数据管理（DML语言的增删改查）
离心粟
09-24 129
1.DML语言 1.1数据库意义： 数据存储、数据管理 1.2管理数据库数据方法： 通过SQLyog等管理工具管理数据库数据 通过DML语句管理数据库数据 DML语言 ： 数据操作语言 1.3用于操作数据库对象中所包含的数据包括 : INSERT (添加数据语句) UPDATE (更新数据语句) DELETE (删除数据语句) 2.添加数据 INSERT命令 语法： INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值1','值2','值3') 注意： 1.字段或值之间用
MySQL DML(数据操作语言——增删改查)总结
布克思轶的博客
06-20 270
今天，重新整理一下MySQL的增删改查数据操作，希望可以给初学者一些帮助。 一、增加数据INSERT 语法：INSERT INTO [(列名1,列名2...)] VALUES (列值1，列值2...); 示例： SELECT *FROM student; #1.全列插入(列值对应表中的每一个列名) INSERT INTO student VALUES (103,'张三','22','女'...
IDS 日志分析
weixin_30904593的博客
03-01 510
【http://blog.csdn.net/cnbird2008/article/details/5792626】 General Approach通用方法1. Identify which log sources and automated tools you can use during the analysis.确认哪些日志源和自动化工具在分析过程中可以使用。2. Copy log rec...
MySQL查询优化:LIMIT 1避免全表扫描
移动编程
03-27 1125
在某些情况下，如果明知道查询结果只有一个，SQL语句中使用LIMIT 1会提高查询效率。 例如下面的用户表(主键id,邮箱,密码)： create table t_user( id int primary key auto_increment, email varchar(255), password varchar(255) ); 每个用户的email是唯一的，如果用户使用em...
Linux面试题
热门推荐
木村的博客
06-26 19万+
常用命令 艿艿：这一小节会非常非常非常长，当做温故知新吧。 另外，面试官也可能会问，你熟悉 Linux 么？你平时使用哪些 Linux 命令。酱紫的连环炮~ 当然，建议重点看下 「性能相关」 小节。 目录相关 find 命令 《Linux 命令大全 —— find 命令》 查找指定文件名的文件(不区分大小写)：find -iname "MyProgram.c" 。 对找到的文件执行某个命令：...
CTF--web学习
qq_45655136的博客
01-08 7707
这是大二上学期主要学习的一些知识，分享一下 一、linux常见命令 Linux切换到超级管理员 sudo su 切换到普通用户 su username touch创建文件 mkdir创建文件夹 mkdir -p ./aaa/bbb/ccc创建aaa/bbb/ccc目录 mv 既可以重命名，又可以移动文件或文件夹 rm -r就是向下递归，不管有多少级目录，一并删除 ​ -f就是直接删除，不作任何提示的 cp拷贝 pwd查看当前目录 ‘>’ '>>'将命令执行结果重定向
web安全 题目练习
qq_43613772的博客
07-16 2749
1、robots 题目链接：http://111.198.29.45:31667/ 首先看是robots直接在地址栏后方输入robots.txt进行查看，如下图所示： 显示最后一行不要慌张提交flag，还要做进一步操作，获得flag，如下图： 2、backup （题目描述：X老师忘记删除备份文件，他派小宁同学去把备份文件找出来,一起来帮小宁同学吧！） 题目链接：http://111.198.2...
jdbc_day01.rar_JDBC增删改查_MySQL与数据库连接及增删改查操作
09-21
在JDBC中，增删改查（CRUD）操作是核心功能。以下是每种操作的简要说明： - **创建（Create）**：使用INSERT语句向数据库添加新记录。例如，插入一个用户： ```java String sql = "INSERT INTO users (username, ...
黑马旧版JavaWeb品牌数据增删改查
最新发布
05-29
在JavaWeb开发中，"黑马旧版JavaWeb品牌数据增删改查"是一个常见的实践环节，它涵盖了数据库操作的核心概念和技巧。这个主题主要是教授如何使用Java编程语言和相关的Web框架来实现对品牌数据的添加（Add）、删除...
Python操作MySQL数据库实例详解【安装、连接、增删改查等】
09-08
本篇文章将详细阐述如何使用Python与MySQL进行交互，包括安装MySQL库、建立连接、数据库和表的操作以及数据的增删改查。 首先，安装Python连接MySQL的库——MySQL Connector/Python。你可以通过Python的包管理器pip...
JDBC+MVC实现mysql数据增删改查
08-05
本教程将聚焦于使用Java的JDBC（Java Database Connectivity）接口与Model-View-Controller（MVC）架构来实现MySQL数据库中的数据增删改查操作。这是一套适用于初学者的学习资料，通过详细代码示例，帮助你快速掌握...
拉跨记录(3)
njh18790816639的博客
12-11 637
web1 这道题首先要用Firefox这个游览器（因为其它游览器用的话没这个方便）也得装个插件 Max HackBar这个插件可以用于post传参（HackBar也可以），Proxy Switcher这个插件用于代理（不过我不怎么去用，总是在电脑设置中弄代理） 题目如下： 可以看到它用了个post传参，到下面的eval（"$c"）;这个的意思就是c接受的值按php代码运行，那我们按如下进行传参： 然后用Max HackBar这个插件如上图，这样就可以了得出flag了 web2 这道题如下： 这是个
欢迎使用CSDN-markdown编辑器
WANGtesting的专栏
05-11 484
CommandInjection(命令注入) ep: cmd->ping www.baidu.com && net user && //两条命令连真执行 通过命令注入，查询网络IP地址和端口输入“&ipconfig or net userNumeric SQL Injection(SQL数字注入) 通过已知的数据注入 or 1=1
php面试题之一——PHP核心技术（高级部分）
weixin_30488313的博客
06-25 1788
一、PHP核心技术 1.写出一个能创建多级目录的PHP函数（新浪网技术部） <?php /** * 创建多级目录 * @param $path string 要创建的目录 * @param $mode int 创建目录的模式，在windows下可忽略 */ function create_dir($path,$mode = 0777) ...
MySQL_01_数据类型测试
zzugsh的博客
04-14 786
MySQL_01连接服务器数据库相关命令创建数据库基本命令创建不同与默认的编码方式的数据库修改已有数据库的编码方式数据库常用命令查看上一步产生的警告查看已有数据库查看已有数据库的定义打开数据库展示现在所有数据库名称删除指定数据表数据表的相关命令MySQL中的数据结构MySQL储存引擎什么是储存引擎关于引擎的相关代码MySQL常用存储引擎及特点数据表的具体相关命令建立数据表相关代码查看指定表结构表中...
机器学习(一) 数据抽取 归一化 标准化
zzugsh的博客
05-13 602
机器学习 机器学习简介 图灵 人工智能之父 马文 李 闵斯基 对人工智能有突出贡献 机器学习: 比如说过滤一些邮件 深度学习: 识别猫的样子等等 计算能力和数据大小还有算法的发展影响人工智能的发展 机器学习深度学习做什么? 可以识别很多东西 比如说车辆 在街上的电子摄像头 医疗方面 可以看X光识别什么病 图片艺术化 代替PS的工作 自动转化 信贷需求预测 店铺销量预测 包括: 自然语言处理 图像处理 传统预测 机器学习课程: 特征工程 模型策略优化 分类 回归和聚类 Tensorflow 神经网络
数据结构_day05_排序算法
zzugsh的博客
04-11 570
排序算法排序排序算法的稳定性冒泡排序冒泡排序的分析代码体现冒泡排序的时间复杂度选择排序选择排序分析代码体现选择排序的时间复杂度插入排序插入排序的分析代码演示希尔排序希尔排序的分析希尔排序的代码演示希尔排序的时间复杂度快速排序(重点掌握)快速排序分析快速排序代码体现时间复杂度归并排序归并算法的分析归并算法的代码体现归并排序的时间复杂度二分查找二分查找程序分析二分查找代码时间复杂度 排序 排序算法(S...
**ニューラルネットワーク**
zzugsh的博客
05-06 514
ニューラルネットワーク ー研究の歴史と本書の構成 2020/4/23 GUO SIHAN 多層ニューラルネットワークの期待と失望 人工ニューラルネットワークは、生物の神経回路網(しんけいかいろもう)を模倣(もほう)して、高度(こうど)な情報処理の実現(じつげん)を目指します 1940年代に研究が開始(かいし)されました 80年代半ばから90年代にかけて、多層(たそう)ニューラルネットワークの学...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值