循环查mysql 还是 in mysql循环查询数据

文章目录

• 一、查询数据操作

• 1.查询记录
• 2.查询表达式
• 3.WHERE 条件

• 范围查询
• 模糊查询

• 4.GROUP BY 查询分组结果

• 聚合函数

• 5.HAVING语句对分组结果进行二次筛选
• 6.ORDER BY 对查询结果进行排序
• 7.LIMIT限制显示条数
• 8.更新删除应用ORDER BY和LIMIT

一、查询数据操作

1.查询记录

2.查询表达式

mysql> -- 查询
mysql> SELECT * FROM cms_admin;
+----+----------+----------+--------------+------------+
| id | username | password | email        | role       |
+----+----------+----------+--------------+------------+
|  1 | admin    | admin    | [e-mail] | 超级管理员          |
+----+----------+----------+--------------+------------+
1 row in set (0.00 sec)

# 用表名.字段名  告诉来自哪张表
mysql> SELECT cms_admin.* FROM cms_admin;
+----+----------+----------+--------------+------------+
| id | username | password | email        | role       |
+----+----------+----------+--------------+------------+
|  1 | admin    | admin    | [e-mail] | 超级管理员          |
+----+----------+----------+--------------+------------+
1 row in set (0.00 sec)

mysql> -- 查询管理员的编号和名称
mysql> SELECT id,username FROM cms_admin;
+----+----------+
| id | username |
+----+----------+
|  1 | admin    |
+----+----------+
1 row in set (0.00 sec)

mysql> -- 表来自那个数据库的db_naem.tbl_name
mysql> SELECT id,username,role FROM cms.cms_admin;
+----+----------+------------+
| id | username | role       |
+----+----------+------------+
|  1 | admin    | 超级管理员          |
+----+----------+------------+
1 row in set (0.00 sec)

mysql> -- 给表名起别名
mysql> SELECT id,username FROM cms_admin AS a;
+----+----------+
| id | username |
+----+----------+
|  1 | admin    |
+----+----------+
1 row in set (0.00 sec)

mysql> -- 给字段起别名
mysql> SELECT id AS '编号',username AS '用户名',email AS '邮箱',role AS '角色' FROM cms_admin;
+------+--------+--------------+------------+
| 编号     | 用户名      | 邮箱             | 角色          |
+------+--------+--------------+------------+
|    1 | admin  | [e-mail] | 超级管理员          |
+------+--------+--------------+------------+
1 row in set (0.00 sec)

mysql> -- 给用户名和字段都起别名
mysql> SELECT a.id AS '编号1',a.username AS '用户名1',a.email AS '邮箱1',a.role AS '角色1' FROM cms_admin AS a;
+-------+---------+--------------+------------+
| 编号1     | 用户名1      | 邮箱1            | 角色1         |
+-------+---------+--------------+------------+
|     1 | admin   | [e-mail] | 超级管理员          |
+-------+---------+--------------+------------+
1 row in set (0.00 sec)

3.WHERE 条件

<> 不等于
<=> 除了比较NULL外一样，与= 一样

mysql> -- WHERE条件
mysql> -- 查询编号为1的用户
mysql> SELECT id,username,email FROM cms_user WHERE id=1;
+----+----------+-------------+
| id | username | email       |
+----+----------+-------------+
|  1 | test1    | [e-mail] |
+----+----------+-------------+
1 row in set (0.00 sec)

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

-- IN NULL  或 IS NOT NULL
SELECT * FROM com_user WHERE age IS NULL;

范围查询

BETWEEN AND NOT BETWEEN AND

-- 查询编号在3-10之间的用户
SELECT * FROM cms_user WHERE id BETWEEN 3 AND 10;
# 小于3或者大于10 
SELECT * FROM cms_user WHERE id NOT BETWEEN 3 AND 10;

指定集合的

-- 查询编号为1,3,5,7,9,11,13,100
SELECT * FROM cme_user WHERE id IN(1,3,5,7,9,11,13,100);

模糊查询

-- 模糊查询
-- %：代表0个一个或者多个任意字符
-- _:代表1个任意字符

-- 查询出所有姓张的用户
SELECT * FROM cms_user WHERE username LIKE '张%';

-- 查询用户名中包含in的用户
SELECT * FROM cms_user WHERE username LIKE '%in%';

-- 查询用户名为3位的用户
SELECT * FROM cms_user WHERE username LIKE '___';

-- 查询用户名_i%
SELECT * FROM cms_user WHERE username LIKE '_i%';

-- NOT LIKE 取反的作用
SELECT * FROM cms_user WHERE username NOT LIKE '_i%';

4.GROUP BY 查询分组结果

-- 分组查询
-- 按照用户所属身份分组proID
SELECT * FROM cms_user GROUP BY proID;

-- 向用户表中添加性别字段
ALTER TABLE cms_user ADD sex ENUM('男','女','保密');

UPDATE cms_user SET sex='男' WHERE id IN(1,3,5,7,9);

UPDATE cms_user SET sex='女' WHERE id IN(2,4,6,8,10);

UPDATE cms_user SET sex='保密' WHERE id IN(11,12);

-- 按照用户性别来分组
SELECT * FROM cms_user GROUP BY sex;

-- 按照位置分组--表头的位置
SELECT * FROM cms_user GROUP BY 7;

-- 按照多个字段来分组
SELECT * FROM cms_user GROUP BY sex,proID;

-- 查询编号大于等于5的用户按照sex分组
SELECT * FROM cms_user WHERE id>=5 GROUP BY sex;

配合GROUP_CONCAT()得到分组详情

-- 分组查询
SELECT id,sex,GROUP_CONCAT(username) FROM cms_user GROUP BY sex;

聚合函数

-- 配合聚合函数
-- 查询编号，SEX，用户名详情以及组中人数按照sex分组
SELECT id,sex,GROUP_CONCAT(username)AS users,COUNT(*) AS totalUsers FROM cms_user GROUP BY sex;

-- 统计表中所有记录
SELECT COUNT(*) AS totalUsers FROM cms_user;
-- 不统计NULL值,除此以外，*与字段就一样了

-- 查询编号，性别，用户详情，组中人数，组中最大年龄、最小年龄、平均年龄、
-- 以及年龄总和按性别分组
SELECT id,sex,GROUP_CONCAT(username),COUNT(*)AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS max_age,
AVG(age) AS avg_age,
SUM(age) AS sum_age
FROM cms_user GROUP BY sex;

-- WITH ROLLUP
SELECT id,sex,COUNT(*)AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS max_age,
FROM cms_user GROUP BY sex WITH ROLLUP;

-- WITH ROLLUP
SELECT id,sex,COUNT(*)AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS max_age,
SUM(age) AS sum_age
FROM cms_user GROUP BY sex WITH ROLLUP;

5.HAVING语句对分组结果进行二次筛选

-- 二次筛选 查询组中人数大于2的
-- 查询性别，用户名详情，组中总人数，最大年龄，年龄总和,根据性别分组
-- 使用HAVING语句对分组结果进行二次筛选
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;

-- 查询组中人数大于2并且最大年龄大于60的
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;
-- HAVING 语句一定要配合GROUP BY 字段  才有意义

6.ORDER BY 对查询结果进行排序

-- 通过ORDER BY 对查询结果进行排序
-- 按照id降序排列DESC 默认的是ASC
SEELECT * FROM cms_user ORDER BY id;

SEELECT * FROM cms_user ORDER BY id ASC;

SEELECT * FROM cms_user ORDER BY id DESC;

-- 按照年龄升序排列
SELECT * FROM cms_user ORDER BY age ASC;

-- 按照年龄升序，id降序来排列
-- 此时，只有当年龄相同时，才会考虑id降序来排列
SELECT * FROM cms_user ORDER BY age ASC,id DESC;

-- 综合使用
SELECT id,age,sex,GROUP_CONCAT(username),COUNT(*) AS totalUsers,SUM(age) AS sum_age
FROM cms_user WHERE id>=2 GROUP BY sex
HAVING COUNT(*)>=2
ORDER BY age DESC,id ASC;

-- 实现记录随机
SELECT * FROM cms_user ORDER BY RAND();

7.LIMIT限制显示条数

-- 查询表中前一条记录
SELECT * FROM cms_user LIMIT 5;

SELECT * FROM cms_user LIMIT 10,5;

SELECT * FROM cms_user LIMIT 15,5;

# 综合使用
SELECT id,age,sex,GROUP_CONCAT(username),COUNT(*) AS totalUsers,SUM(age) AS sum_age
FROM cms_user WHERE id>1 GROUP BY sex
HAVING COUNT(*)>=2
ORDER BY age DESC,id ASC
LIMIT 2,2;

8.更新删除应用ORDER BY和LIMIT

-- 更新用户名为4位的用户，让其已有年龄－3
UPDATE cms_user SET age=age-3 WHERE username LIKE '____';

-- 更新前三条记录  让已有年龄加10
UPDATE cms_user SET age=age+10 LIMIT 3;
-- 更新时 LIMIT 只能有一个参数

-- 按照ID降序排列，更新前三条
UPDATE cms_user SET age=age+10 ORDER BY id DESC LIMIT 3;
-- 对NULL 做加法减法结果还是NULL

-- 删除用户性别为男的用户，按照年龄降序排列，删除前一条记录
DELETE FROM cms_user WHERE sex='男' ORDER BY age DESC LIMIT 1;