文章目录
5.DQL 查询数据操作(1)
5.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限制显示条数]
]
5.2查询表达式
①每一个表达式表示想要的一列,必须至少有一列,多个列之间以逗号隔开
②查询长达式可以使用[AS]alias_name为其赋予別名
③’*’ 表示所有列,tbI_name. * 可以表示命名表的所有列
1.测试查询
两种方法
- 查询
SELECT * FROM cms_admin;
SELECT cms_admin.* FROM cms_admin;
--查询表中特定内容
--查询管理员编号和名称
SELECT id,username FROM cms_admin;
SELECT username,id,role FROM cms_admin;
--表来自于哪个数据库下db_name.tbl_name
SELECT id,username,role FROM cms.cms_admin;
--字段来自于哪张表
SELECT cms_admin.id,cms_admin.username FROM cms.cms_admin;
- 为表、字段️起别名
--给表名起别名
SELECT id,username FROM cms_admin AS a;
SELECT id,username FROM cms_admin a;
SELECT a.id,a.username,a.email,a.role FROM cms_admin AS a;
--给字段起别名
SELECT id AS '编号',username AS '用户名',email AS '邮箱',role '角色' FROM cms_admin;
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 | 普通管理员 |
+------+--------+--------------+------------+
3.3WHERE 条件
3.3.1简介
3.3.2带WHERE条件的查询
注:cms_user为创建好的用户表,包含以下信息:
| id | username | password | email | regTime | face | proId |
--(1)WHERE条件
--查询编号为1的用户
SELECT id,username,email FROM cms_user WHERE id=1;
SELECT id,username,email FROM cms_user WHERE username='king';
--查询编号不为1的用户
SELECT * FROM cms_user WHERE id!=1;
SELECT * FROM cms_user WHERE id<>1;
--添加age字段
ALTER TABLE cms_user ADD age TINYINT UNSIGNED DEFAULT 18;
INSERT cms_user(username,password,regTime,proId,age)
VALUES('test1','test1',1419811708,1,NULL);
--查询表中记录age值为NULL
SELECT * FROM cms_user WHERE age=NULL;
mysql> SELECT * FROM cms_user WHERE age=NULL;
Empty set (0.00 sec)
--检测null需要用<=>
SELECT * FROM cms_user WHERE age<=>NULL;
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)
SELECT * FROM cms_user WHERE age<=>18;
IS NULL 或者IS NOT NULL
SELECT * FROM cms_user WHERE age IS NULL;
--(2)BETWEEN、IN 的用法
--查询编号在3~10之间的用户
SELECT * FROM cms_user WHERE id BETWEEN 3 AND 10;
--查询编号为1,3,5,7,9,11,13,100
SELECT * FROM cms_user WHERE id IN(1,3,5,7,9,11,13,100,1000);
--查询proId为1 和3的用户
SELECT * FROM cms_user WHERE proId IN(1,3);
--查询用户名为king,queen,张三,章子怡的记录
SELECT * FROM cms_user WHERE username IN('king','queen','张三','章子怡');
3.3.3模糊查询
%:代表0个一个或者多个任意字符
_:代表1个任意字符
举例:
--查询姓张的用户
SELECT * FROM cms_user WHERE username LIKE '张%';
--查询用户名中包含in的用户
SELECT * FROM cms_user WHERE username LIKE '%in%';
SELECT * FROM cms_user WHERE username LIKE '%';
--查询用户名为3位的用户
SELECT * FROM cms_user WHERE username LIKE '___';
--用户名_i%
SELECT * FROM cms_user WHERE username LIKE '_I%';
SELECT * FROM cms_user WHERE username LIKE 'king';
SELECT * FROM cms_user WHERE username NOT LIKE '_I%';
--查询用户名为king并且密码为king的用户
SELECT * FROM cms_user WHERE username='king' AND password='king';
--查询编号大于等于3的变量年龄不为NULL的用户
SELECT * FROM cms_user WHERE id>=3 AND age IS NOT NULL;
--查询编号大于等于3的变量年龄不为NULL的用户 并且proId为的3
SELECT * FROM cms_user WHERE id>=3 AND age IS NOT NULL AND proId=3;
--查询编号在5~10的用户并且用户名为4位的用户
SELECT * FROM cms_user WHERE id BETWEEN 5 AND 10 AND username LIKE '____';
--查询用户名以张开始或者用户所在身份为2,4的记录
SELECT * FROM cms_user WHERE username LIKE '张%' OR proId IN(2,4);SELECT
3.4分组查询
①配合GROUP_ CONCAT()得到分组详情
②配合聚函数
COUNT()、MAX()、AVG()、MIN()、SUM()
③配合WITH ROLLUP记录上面所有记录的总和
举例:
--按照用户所属身份分组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(12,11);
--按照用户性别分组
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;
3.4.1GROUP_ CONCAT()得到分组详情
--查询id,sex,用户名详情按照性别分组
SELECT id,sex,GROUP_CONCAT(username) FROM cms_user GROUP BY sex;
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.4.2分组查询配合聚合函数
--查询proId,性别详情,注册时间详情,用户名详情 按照proId
SELECT proId,GROUP_CONCAT(username),GROUP_CONCAT(sex),GROUP_CONCAT(regTime) FROM cms_user GROUP BY proId;
--更新年龄
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;
--查询编号,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;
SELECT COUNT(id) AS totalUsers FROM cms_user;
mysql> SELECT COUNT(id) AS totalUsers FROM cms_user;
+------------+
| totalUsers |
+------------+
| 12 |
+------------+
--COUNT(字段)不统计NULL值
SELECT COUNT(age) AS totalUsers FROM cms_user;
--聚合函数(查询编号,性别,用户名详情,组中总人数,组中最大年龄,最小年龄,平均年龄,以及年龄总和按照性别分组)
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 在group分组字段的基础上再进行统计数据
SELECT id,sex,
COUNT(*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age
FROM cms_user
GROUP BY sex WITH ROLLUP;
+----+------+------------+---------+---------+
| id | sex | totalUsers | max_age | min_age |
+----+------+------------+---------+---------+
| 1 | 男 | 5 | 18 | 18 |
| 4 | 女 | 5 | 18 | 18 |
| 11 | 保密 | 2 | 18 | 18 |
| 11 | NULL | 12 | 18 | 18 |
+----+------+------------+---------+---------+
3.5 Having语句对分组结果进行二次筛选
--二次筛选(查询性别sex,用户名详情,组中总人数,最大年龄,年龄总和,根据性别分组)
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;
--查询组中人数大于2的
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;
--查询编号大于等于2的用户
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;
SELECT id,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;