MySQL 一些知识-优快云博客

1 插入多行

INSERT INTO student (ID, NAME) VALUES (1, 'ARMAAN'), (2, 'BILLY'), (3, 'CHARLIE');

2 MySQL 用gourp by分组后取某一字段最大值

select
   temp_b.name,temp_b.subject,temp_b.score
from
   (select name,max(score) score from score group by name) temp_a
   inner join score temp_b
on
   temp_a.name = temp_b.name and temp_a.score = temp_b.score;

MySQL 用gourp by分组后取某一字段最大值_没对象的指针的博客-优快云博客

3 mysql使用group by分组后筛选某个值最大的一列

select * from test111 t where (t.name1,t.oder) in (select name1,max(oder) FROM test111 group by name1);
2：
SELECT name1,oder,numm FROM test111 t1 where t1.oder=(select max(oder) from test111 where name1=t1.name1) order by name1;

mysql使用group by分组后筛选某个值最大的一列_group by取最大值那一条_CRUD的W的博客-优快云博客

4 MySQL5.7用group by分组根据组中某个字段的最大值求取那条记录（注意是整条记录）

https://blog.youkuaiyun.com/weixin_38450840/article/details/88836170

SELECT Department.`Name` AS Department,Employee.`Name` AS Employee,Salary
FROM Employee JOIN Department ON Employee.`DepartmentId` = Department.`Id`
WHERE (Salary,DepartmentId) IN
(SELECT MAX(Salary),DepartmentId
FROM Employee
GROUP BY DepartmentId)

5 MySQL分组后取最大一条数据【最优解】

MySQL分组后取最大一条数据【最优解】_mysql分组取每组最大一条_小道仙97的博客-优快云博客

SELECT
   xu.id '用户ID',
   xu.name '用户名',
   xn.message '消息内容'
FROM xdx_user xu
LEFT JOIN (
   SELECT * FROM xdx_notify a
   WHERE id = (SELECT MAX(id) FROM xdx_notify b WHERE a.user_id = b.user_id)
) xn ON xu.id = xn.user_id

6 MySQL之——GROUP BY分组取字段最大值

MySQL之——GROUP BY分组取字段最大值_group by 最大值_冰河的博客-优快云博客

SELECT a.uid, a.ip, a.login_time
FROM (SELECT uid, max(login_time) login_time
FROM tb
GROUP BY uid
) b JOIN tb a ON a.uid = b.uid AND a.login_time = b.login_time;

7. 设置主外键后的表，在执行删除的时候会自动判断事务。