MySql_11 表中数据的查询

首先我们准备一点数据

1、创建数据库`cms`

1	CREATE DATABASE IF NOT EXISTS cms DEFAULT CHARACTER SET utf8;

2、创建管理员表`cms_admin`并插入几条数据

CREATE TABLE cms_admin(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
password CHAR(32) NOT NULL,
email VARCHAR(50) NOT NULL DEFAULT 'admin@qq.com',
role ENUM('普通管理员','超级管理员') DEFAULT '普通管理员'
);

INSERT cms_admin(username,password,email,role) VALUES('admin','admin','admin@qq.com',2);
INSERT cms_admin(username,password) VALUES('sing','sing'),
('queen','queen'),
('test','test');

3、创建分类表`cms_cate`并插入数据

CREATE TABLE cms_cate(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
cateName VARCHAR(50) NOT NULL UNIQUE,
cateDesc VARCHAR(200) NOT NULL DEFAULT ''
);

INSERT cms_cate(cateName,cateDesc) VALUES('国内新闻','聚焦当今最热的国内新闻'),
('国际新闻','聚焦当今最热的国际新闻'),
('体育新闻','聚焦当今最热的体育新闻'),
('军事新闻','聚焦当今最热的军事新闻'),
('教育新闻','聚焦当今最热的教育新闻');

4、创建新闻表`cms_news`并插入数据

CREATE TABLE cms_news(
id INT UNSIGNED AUTO_INCREMENT KEY,
title VARCHAR(50) NOT NULL UNIQUE,
content TEXT,
clickNum INT UNSIGNED DEFAULT 0,
pubTime INT UNSIGNED,
cId TINYINT UNSIGNED NOT NULL COMMENT '新闻所属分类，对应分类表中的id',
aId TINYINT UNSIGNED NOT NULL COMMENT '哪个管理员发布的，对应管理员表中的id'
);

INSERT cms_news(title,content,pubTime,cId,aId) VALUES('亚航客机失联搜救尚无线索 未发求救信号','马来西亚亚洲航空公司一架搭载155名乘客的客机28日早晨从印度尼西亚飞往新加坡途中与空中交通控制塔台失去联系，下落不明。',1419818808,1,2),
('北京新开通四条地铁线路 迎接首位客人','12月28日凌晨，随着北京地铁6号线二期、7号线、15号线西段、14号线东段的开通试运营，北京的轨道交通运营里程将再添62公里，共计达到527公里。当日凌晨5时许，北京地铁7号线瓷器口换乘站迎来新线开通的第一位乘客。',1419818108,2,1),
('考研政治题多次出现习近平讲话内容','新京报讯 （记者许路阳 (微博)）APEC反腐宣言、国家公祭日、依法治国……昨日，全国硕士研究生招生考试进行首日初试，其中，思想政治理论考题多次提及时事热点，并且多次出现习近平在不同场合的讲话内容。',1419818208,3,2),
('深度-曾雪麟：佩兰别重蹈卡马乔覆辙','12月25日是前国足主帅曾雪麟的85岁大寿，恰逢圣诞节，患有尿毒症老爷子带着圣诞帽度过了自己的生日。此前，腾讯记者曾专访曾雪麟，尽管已经退休多年，但老爷子仍旧关心着中国足球，为国足揪心，对于国足近几位的教练，他只欣赏高洪波。对即将征战亚洲杯的国足，老爷子希望佩兰不要重蹈卡马乔的覆辙',1419818308,2,4),
('国产JAD-1手枪枪架投入使用 手枪可变"冲锋枪"','日前，JAD-1型多功能手枪枪架通过公安部特种警用装备质量监督检验中心检验，正式投入生产使用。此款多功能枪架由京安盾(北京)警用装备有限公司开发研制，期间经广东省江门市公安特警支队试用，获得好评。',1419818408,4,4),
('测试数据1','测试数据1',1419818508,1,5),
('测试数据2','测试数据2',1419818608,1,5),
('测试数据3','测试数据3',1419818708,1,5);

5、创建省份表`provinces`并插入数据

CREATE TABLE provinces(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
proName VARCHAR(10) NOT NULL UNIQUE
);

INSERT provinces(proName) VALUES('北京'),
('上海'),
('深圳'),
('广州'),
('重庆');

6、创建用户表`cms_user`并插入数据

CREATE TABLE cms_user(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
password CHAR(32) NOT NULL,
email VARCHAR(50) NOT NULL DEFAULT 'user@qq.com',
regTime INT UNSIGNED NOT NULL,
face VARCHAR(100) NOT NULL DEFAULT 'user.jpg',
proId TINYINT UNSIGNED NOT NULL COMMENT '用户所属省份'
);

INSERT cms_user(username,password,regTime,proId)
VALUES('张三','zhangsan',1419811708,1),
('张三丰','zhangsanfeng',1419812708,2),
('章子怡','zhangsan',1419813708,3),
('long','long',1419814708,4),
('ring','ring',1419815708,2),
('queen','queen',1419861708,3),
('sing','sing',1419817708,5),
('blek','blek',1419818708,1),
('rose','rose',1419821708,2),
('lily','lily',1419831708,2),
('john','john',1419841708,2);

数据准备结束

1、查询记录

单标查询的完整语句：

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 限制显示条数]
  ]

2、查询表达式

每一个表达式表示想要的一列，必须至少有一列，多个列之间以逗号分隔
*表示所有列,tbl_name.*可以表示命名表的所有列
查询表达式可以使用[AS]alias_name为其赋予别名

比如我们只查询cms_admin表中的id和username:

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

测试给表名起别名，一般为表名缩写，建议不超过2个字母：

mysql> SELECT id,username FROM cms_admin AS a;
+----+----------+
| id | username |
+----+----------+
|  1 | admin    |
|  3 | queen    |
|  2 | sing     |
|  4 | test     |
+----+----------+
4 rows in set (0.03 sec)

此时我们的表名+字段名的写法就可以用别名来写：

1
2
3

SELECT cms_admin.id, cms_admin.username FROM cms_admin;
< == >
SELECT a.id,a.username FROM cms_admin AS a;

测试给字段起别名：

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

3.WHERE 条件

查询条件	符号
比较	+、<、<=、>、>=、!=、<>、!>、!<、<=>
指定范围	BETWEEN AND、NOT BETWEEN AND
指定集合	IN、NOT IN
匹配字符	LIKE、NOT LIKE
是否为控制	IS NULL、IS NOT NULL
多个查询条件	AND、OR

然后我们单个测试一下：

mysql> # 查询编号为1的用户
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> # 查询编号大于等于5的用户
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 | sing     | sing     | 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.01 sec)

mysql> # 查询编号不等于1
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 | sing      | sing         | 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> # 同样的查询编号不等于1
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 | sing      | sing         | 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)

<=>与=是同样的意思，但是<=>可以检测值为NULL的字段，我们先加入一个可以为空的age字段，然后插入一条数据，分别用两种方式查询一下：

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

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

mysql> SELECT * FROM cms_user WHERE age=NULL;
Empty set (0.00 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.00 sec)

我们还有另一种方式来检测是否为空：

1	SELECT * FROM cms_user WHERE age IS NULL;

如果我们查询编号在3~10之间的学员可以这样使用：

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 | sing      | sing     | 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.00 sec)

相反的如果想查出不在3~10之间的数据可以这样写：

1	SELECT * FROM cms_user WHERE id NOT BETWEEN 3 AND 10;

查询指定数据(集合)的方法：

mysql> SELECT * FROM cms_user WHERE id IN(1,3,5,7,9,11,13,100);
+----+-----------+----------+-------------+------------+----------+-------+------+
| 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 | sing      | sing     | 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.00 sec)

模糊查询：

%:代表0个1个或多个任意字符
_:代表1个任意字符

查询名字在包含张的用户

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.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)

多条件查询：
查询用户名为sing且密码为sing的用户：

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

4、GROUP BY查询结果分组

按照用户所属省份分组：

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 | sing      | sing         | user@qq.com | 1419817708 | user.jpg |     5 |   18 |
+----+-----------+--------------+-------------+------------+----------+-------+------+
5 rows in set (0.00 sec)

# 如果出现以下错误：
mysql> SELECT * FROM cms_user GROUP BY proId;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'cms.cms_user.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
# 此时只需输入以下命令即可：
set @@GLOBAL.sql_mode='';
set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
# 参考文章：http://www.cnblogs.com/jim2016/p/6322703.html

由上面的查询结果可以看出，把我们查询的结果分为了5组，但是并不知道每组有多少数据，现在我们增加一个字段性别sex，并给各条数据赋值后按性别查询分组：

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

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

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

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

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 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 | sing      | sing         | 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)

此时首先会以sex分为三组，然后在每个组内按proId分组，但是我们怎么显示每个分组的数量？

GROUP_CONCAT

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

我们通过GROUP_CONCAT+字段名可以看到详细的用户名组；

聚合函数

COUNT() 不统计NULL值
MAX()
MIN()
AVG()
SUM()

更新一下我们的年龄字段，然后使用这些函数测试

UPDATE cms_user SET age=11 WHERE id=1;
UPDATE cms_user SET age=21 WHERE id=2;
UPDATE cms_user SET age=33 WHERE id=3;
UPDATE cms_user SET age=44 WHERE id=4;
UPDATE cms_user SET age=25 WHERE id=5;
UPDATE cms_user SET age=77 WHERE id=6;
UPDATE cms_user SET age=56 WHERE id=7;
UPDATE cms_user SET age=88 WHERE id=8;
UPDATE cms_user SET age=12 WHERE id=9;
UPDATE cms_user SET age=32 WHERE id=10;
UPDATE cms_user SET age=65 WHERE id=11;

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 | 男     | 张三,章子怡,ring,sing,rose      |          5 |      56 |      11 | 27.4000 |     137 |
|  2 | 女     | 张三丰,long,queen,blek,lily     |          5 |      88 |      21 | 52.4000 |     262 |
| 11 | 保密   | john,test1                      |          2 |      65 |      65 | 65.0000 |      65 |
+----+--------+---------------------------------+------------+---------+---------+---------+---------+
3 rows in set (0.00 sec)

先得我们得到分组的详情，然后我们配合使用WITH ROLLUP来进行筛选输出：

mysql> SELECT id,sex,
    -> COUNT(*) AS totalUsers,
    -> MAX(age) AS max_age,
    -> MIN(age) AS min_age,
    -> SUM(age) AS sum_age
    -> FROM cms_user
    -> GROUP BY sex WITH ROLLUP;
+----+--------+------------+---------+---------+---------+
| id | sex    | totalUsers | max_age | min_age | sum_age |
+----+--------+------------+---------+---------+---------+
|  1 | 男     |          5 |      56 |      11 |     137 |
|  2 | 女     |          5 |      88 |      21 |     262 |
| 11 | 保密   |          2 |      65 |      65 |      65 |
| 11 | NULL   |         12 |      88 |      11 |     464 |
+----+--------+------------+---------+---------+---------+
4 rows in set (0.00 sec)

我们可以看到最后一行，id为以上列表中最大的一条，totalUsers为以上列表中的总和，max_age为以上列表的最大值，min_age为以上列表的最小值，sum_age为以上列表的总和。

5、HAVING子句

通过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;
+--------+---------------------------------+------------+---------+---------+
| sex    | users                           | totalUsers | max_age | sum_age |
+--------+---------------------------------+------------+---------+---------+
| 男     | 张三,章子怡,ring,sing,rose      |          5 |      56 |     137 |
| 女     | 张三丰,long,queen,blek,lily     |          5 |      88 |     262 |
| 保密   | john,test1                      |          2 |      65 |      65 |
+--------+---------------------------------+------------+---------+---------+
3 rows in set (0.00 sec)

此时查询出三组，然后保密一组人数为2，我们用HAVING条件过滤掉组中人数大于2的：

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 |
+------+---------------------------------+------------+---------+---------+
| 男   | 张三,章子怡,ring,sing,rose      |          5 |      56 |     137 |
| 女   | 张三丰,long,queen,blek,lily     |          5 |      88 |     262 |
+------+---------------------------------+------------+---------+---------+
2 rows in set (0.01 sec)

再查询一下，组中人数大于2，且最大年龄最大年龄小于60：

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)

6、ORDER BY 排序

降序排列DESC，默认的是升序ASC

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

又年龄升序，id降序：

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

实现随机提取记录：

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

6、LIMIT限制查询结果显示条数

1、LIMIT 显示条数(对于查询结果进行限制)

查询表中前3条记录

mysql> SELECT * FROM cms_user LIMIT 3;
+----+-----------+--------------+-------------+------------+----------+-------+------+------+
| 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 | 男   |
+----+-----------+--------------+-------------+------------+----------+-------+------+------+
3 rows in set (0.01 sec)

2、LIMIT 偏移量，显示条数

通过该命令可以实现分页，偏移量从0开始，例如，去第五条开始的五条数据：

mysql> SELECT * FROM cms_user LIMIT 5,5;
+----+----------+----------+-------------+------------+----------+-------+------+------+
| id | username | password | email       | regTime    | face     | proId | age  | sex  |
+----+----------+----------+-------------+------------+----------+-------+------+------+
|  6 | queen    | queen    | user@qq.com | 1419861708 | user.jpg |     3 |   77 | 女   |
|  7 | sing     | sing     | 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 | 女   |
+----+----------+----------+-------------+------------+----------+-------+------+------+
5 rows in set (0.00 sec)

到此，查询语句基本完成。

最后演示一下查询的全部条件语句：

SELECT id,sex,age,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
WHERE id>=1
GROUP BY sex
HAVING COUNT(*)>=2
ORDER BY age DESC
LIMIT 0,2;

MySql_11 表中数据的查询

MySql_11 表中数据的查询

首先我们准备一点数据

1、创建数据库cms

2、创建管理员表cms_admin并插入几条数据

3、创建分类表cms_cate并插入数据

4、创建新闻表cms_news并插入数据

5、创建省份表provinces并插入数据

6、创建用户表cms_user并插入数据

数据准备结束

1、查询记录

2、查询表达式

3.WHERE 条件

4、GROUP BY查询结果分组

GROUP_CONCAT

聚合函数

5、HAVING子句

6、ORDER BY 排序

6、LIMIT限制查询结果显示条数

1、LIMIT 显示条数(对于查询结果进行限制)

2、LIMIT 偏移量，显示条数

1、创建数据库`cms`

2、创建管理员表`cms_admin`并插入几条数据

3、创建分类表`cms_cate`并插入数据

4、创建新闻表`cms_news`并插入数据

5、创建省份表`provinces`并插入数据

6、创建用户表`cms_user`并插入数据