Mysql7

31、ORDER BY 对查询结果排序
–查询按照id降序排列DESC ，默认的是ASC升序
SELECT * FROM cms_user ORDER BY id; 升序
SELECT * FROM cms_user ORDER BY id ASC; 升序
SELECT * FROM cms_user ORDER BY id DESC； 降序

–按照年龄升序排列
SELECT * FROM cms_user ORDER BY age ASC;
SELECT * FROM cms_user ORDER BY 1 DESC; --使用字段位置
UPDATE cms_user SET age=12 WHERE id=5;

–按照年龄升序，id降序排列
SELECT * FROM cms_user ORDER BY age ASC,id DESC; —年龄相同时，id降序

SELECT id,age,sex,GROUP_CONCAT(username),COUNT() AS totalUser,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(); —实现随机提取记录

32、通过limit限制显示条数
LIMIT 显示条数
LIMIT 偏移量，显示条数
–查询表中前三条记录
SELECT * FROM cms_user LIMIT 3;
SELECT * FROM cms_user ORDER BY id DESC LIMIT 5;

–查询表中前一条记录
SELECT * FROM cms_user LIMIT 1；
SELECT * FROM cms_user LIMIT 0,1; —查询第一条记录
SELECT * FROM cms_user LIMIT 1,1; —第二条
SELECT * FROM cms_user LIMIT 0,5; —前五条
SELECT * FROM cms_user LIMIT 5,5; —下五条
SELECT * FROM cms_user LIMIT 10,5; —再往后五条

SELECT id,sex age,GROUP_CONCAT(username),
COUNT() AS totalUser,
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;
33、更新删除应用order by和limit
更新数据
UPDATE tbl_name SET 字段名称=值,… [WHERE 条件][ORDER BY 字段名称][LIMIT 限制条数]
删除数据
DELECT FROM tbl_name [WHERE 条件][ORDER BY 字段名称][LIMIT 限制条件]
彻底清空数据表：TRUNCATE [TABLE] tbl_name
–更新用户名为4位的用户，让其已有年龄-3
UPDATE cms_user SET age=age-3 WHERE username LIKE ‘___’;

–更新前3条记录，让已有年龄+10
UPDATE cms_user SET age=age+10 LIMIT 3;
UPDATE cms_user SET age=age+10 LIMIT 0,3; —报错 更新或者删除时limit只能写一个参数

–按照id降序排列，更新前3条
UPDATE cms_user SET age=age+10 ORDER BY id DESC LIMIT 3;

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

34、内连接查询
连接查询：是将两个或两个以上的表按某个条件连接起来，从中选取需要的数据。是同时查询两个或两个以上的表时使用的。当不同的表存在相同意义的字段时，可以通过该字段连接这几个表。
内连接查询：JOIN|CROSS JOIN INNER JOIN;通过ON 连接条件；显示两个表中符合连接条件的记录。
–查询cms_user id,username
–province,proName
SELECT cms_user.id,username,proName FROM cms_user,provinces;

–cms_user的proId对应省份表中的id
SELECT cms_user.id,username,proName FROM cms_user,provinces
WHERE cms_user.proId=provinces.id;

–查询cms_user表中is,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;

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

SELECT u.id,u.username,u.email,u.sex,p.proName
FROM provinces AS p
CROSS cms_user AS u
ON u.proId=p.id;

–查询cms_user id，username，sex
–查询provinces proName
–条件是cms_user的性别为男的用户
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM cms_user AS u
JOIN
provinces AS p
ON u.proId=p.id
WHERE u.sex=‘男’;

–根据proName分组
SELECT u.id,u.username,u.email,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)
FROM cms_user AS u
JOIN
provinces AS p
ON u.proId=p.id
WHERE u.sex=‘男’
GROUP BY p.proName;

–对分组结果进行筛选，选出组中人数大于等于1的
SELECT u.id,u.username,u.email,u.sex,p.proName,COUNT() AS totalUsers,GROUP_CONCAT(username)
FROM cms_user AS u
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.email,u.sex,p.proName,COUNT() AS totalUsers,GROUP_CONCAT(username)
FROM cms_user AS u
JOIN
provinces AS p
ON u.proId=p.id
WHERE u.sex=‘男’
GROUP BY p.proName
HAVING COUNT()>=1
ORDER BY u.id ASC; —要确定是哪个表的id

–限制显示条数，前两条
SELECT u.id,u.username,u.email,u.sex,p.proName,COUNT() AS totalUsers,GROUP_CONCAT(username)
FROM cms_user AS u
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;

–查询cms_news中的id，title
–查询cms_cate中的cateName
SELECT n.id,n.title,c.cateName FROM
cms_news AS n
JOIN
cms_cate AS c
ON n.cId=c.id;

–cms_news id,title
–cms_admin username,role
SELECT n.id,n.title,a.username,a.role
FROM
cms_news AS n
JOIN
cms_admin AS a
ON n.aId=a.id;

–cms_news id,title
–cms_cate cateName
–cms_admin username,role
SELECT n.id,n.title,c.careName,a.username,a.role
FROM cms_cate AS c
JOIN
cms_news AS n
ON n.cId=c.id
JOIN
cms_admin AS a
ON n.aId=a.id;