关闭

MYSQL分组查询

43人阅读 评论(0) 收藏 举报

更新用户名为4位的用户,让其已有年龄-3

UPDATE csm_user SET age=age-3 WHERE username LIKE ‘__‘;

更新前3条几率,让已有年龄+10

UPDATE smc_user SET age=age+10 LIMIT 3;

按照id降序排列,更新前3调

UPDATE smd_user SET age=age+10 ORDER BY id DESC LIMIT 3;

删除用户性别为男的用户,按照年龄降序排列, 删除1条前记录

DELETE FROM user WHERE sex=’男’ ORDER BY age DESC LIMIT 1;

查询cms_user id,username

provinces,proname

SELECT cms_user.id,username,proName FROM cms_proName,provinces;

cms_user的proid对应省份表中的id

SELECT cms_user.id,username,proName FROM cms_proName,provinces
WHERE cms_user.proid=provinces.id;

查询cms_user表中id,username,email,sex

查询provinces表中的proName

SELECT u.id,u.username,u.email,u.sex,p.proName
FROM cms_user AS u
INNER JOIN provinces AS p
ON u.proid=p.id;

查询cms_user id,lusername,sex

查询provinces proName

条件是cms_user 的性别为男的用户

根据proname分组

SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalusers,GROUP_CONCAT(username)
FROM cms_user AS u
INNER JOIN provinces AS p
ON u.proid=p.id
WHERE u.sex=’男’
GROUP BY p.proName;

对分组结果进行筛选,选出组中人数大于1的用户

SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalusers,GROUP_CONCAT(username)
FROM cms_user AS u
INNER JOIN provinces AS p
ON u.proid=p.id
WHERE u.sex=’男’
GROUP BY p.proName
HAVING COUNT(*)>=1;

按照id升序排列

SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalusers,GROUP_CONCAT(username)
FROM cms_user AS u
INNER JOIN provinces AS p
ON u.proid=p.id
WHERE u.sex=’男’
GROUP BY p.proName
HAVING COUNT(*)>=1
ORDER BY u.id ASC;

限制显示条数 ,前2条

SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalusers,GROUP_CONCAT(username)
FROM cms_user AS u
INNER JOIN provinces AS p
ON u.proid=p.id
WHERE u.sex=’男’
GROUP BY p.proName
HAVING COUNT(*)>=1
ORDER BY u.id ASC
LIMIT 0,2;

3个表的链接

SELECT u.id,p.id,a.id
FROM cms_user AS u
JOIN
cms_new AS p
ON u.id=p.id
JOIN
cms_sd AS a
ON u.id=a.id;

外连接 (左)

SELECT u.id,p.id,a.id
FROM cms_user AS u
LEFT JOIN
cms_new AS p
ON u.id=p.id
JOIN
cms_sd AS a
ON u.id=a.id;

外连接 (右)

SELECT u.id,p.id,a.id
FROM cms_user AS u
RIGHT JOIN
cms_new AS p
ON u.id=p.id
JOIN
cms_sd AS a
ON u.id=a.id;

外键的操作,在创建表的时候添加(创建父子关系) FOREIGN KEY(cms_admin) REFERENCES cms_admi(id)

删除外键

ALTER TABLE employee DROP FOREIGN KEY em_fk_dep;

添加外键

ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depid) REFERENCES departemen(id);

删除主表,对应的子表也删除 (在创建子表中加入ON DELETE CASCADE)

更新主表,对应的子表也删除 (在创建子表中加入NO UPDATE CASCADE)

联合查询(UNION合并在一起,去掉重复)(UNION ALL合并在一起)

SELECT username FROM employee UNION SELECT username FROM cms_user;

0
0

猜你在找
【直播】机器学习&数据挖掘7周实训--韦玮
【套餐】系统集成项目管理工程师顺利通关--徐朋
【直播】3小时掌握Docker最佳实战-徐西宁
【套餐】机器学习系列套餐(算法+实战)--唐宇迪
【直播】计算机视觉原理及实战--屈教授
【套餐】微信订阅号+服务号Java版 v2.0--翟东平
【直播】机器学习之矩阵--黄博士
【套餐】微信订阅号+服务号Java版 v2.0--翟东平
【直播】机器学习之凸优化--马博士
【套餐】Javascript 设计模式实战--曾亮
查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:4013次
    • 积分:401
    • 等级:
    • 排名:千里之外
    • 原创:38篇
    • 转载:1篇
    • 译文:0篇
    • 评论:0条
    文章分类
    文章存档