常用sql

SELECT语句

-- 测试完整SELECT 语句的形式
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(age) AS sum_age,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age
FROM user1
WHERE id>=2
GROUP BY addr;

SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(age) AS sum_age,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age
FROM user1
WHERE id>=2
GROUP BY addr
HAVING totalUsers>=2;


SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(age) AS sum_age,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age
FROM user1
WHERE id>=2
GROUP BY addr
HAVING totalUsers>=2
ORDER BY totalUsers ASC;

SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(age) AS sum_age,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age
FROM user1
WHERE id>=2
GROUP BY addr
HAVING totalUsers>=2
ORDER BY totalUsers ASC
LIMIT 0,2;

where 条件筛选记录

SELECT id,username,age FROM user1
WHERE id=50;

-- 更新id<=9的用户 userDesc='this is a test'
UPDATE user1 SET userDesc='this is a test'
WHERE id<=9;

-- IS [NOT] NULL检测NULL值
SELECT id,username,age,userDesc FROM user1
WHERE userDesc IS  NULL;
-- IS NOT判断不等于null值
select id ,userDesc from user1
where userDesc is not null

-- 查询薪水在10000~50000之间的用户
SELECT id,username,age,salary FROM user1
WHERE salary BETWEEN 10000 AND 50000;

SELECT id,username,age,salary FROM user1
WHERE salary NOT BETWEEN 10000 AND 50000;

group by 分组

分组后可对组内数据做统计、聚合、求最大值、最小值、平均值等

1. 分组后，再对组做筛选(GROUP BY+HAVING )

根据addr分组，求组内salary的平均值，过滤掉不满足salary的平均值>=4000的组

-- 要求平均薪水>=40000
SELECT addr,
GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers,
SUM(salary) AS sum_salary,
MAX(salary) AS max_salary,
MIN(salary) AS min_salary,
AVG(salary) AS avg_salary
FROM user1
GROUP BY addr
HAVING avg_salary>=40000;

2.先过滤掉不满足条件的数据再分组(WHERE+GROUP BY)

-- 查询age>=30的用户并且按照sex分组
SELECT sex,GROUP_CONCAT(username) AS usersDetail,
COUNT(*) AS totalUsers
FROM user1
WHERE age>=30
GROUP BY sex;

SQL实现group by 分组后组内排序,取组内topN数据

SQL实现group by 分组后组内排序

select a.Classid,a.English from
(select Classid,English,row_number() over(partition by Classid order by English desc) as n
from CJ) a
where n<=2

ROW_NUMBER() OVER()函数用法详解 （分组排序 例子多）

组内比较(平均值、最大值、最小值)

sql分组后组内排序_SQL的开窗函数|解决topN、组内排序、移动平均问题

ORDER BY 排序

ASC升序，DESC降序，不写默认为升序

-- 按照id降序排列
SELECT id,username,age
FROM user1
ORDER BY id DESC;

-- 按照age升序
SELECT id,username,age
FROM user1
ORDER BY age ;

-- 按照多个字段排序
SELECT id,username,age
FROM user1
ORDER BY age ASC,id desc;

SELECT id,username,age
FROM user1
WHERE age>=30
ORDER BY age DESC;

SELECT id,username,age
FROM user1
where age>=30
ORDER BY age ASC, RAND();

LIMIT限制结果集的显示条数

SELECT id,username,age,sex
FROM user1
LIMIT 5;

SELECT id,username,age
FROM user1
LIMIT 2,5

查询前3条记录，将age+5

SELECT id,username,age+5
FROM user1
LIMIT 3

按照id降序排列，查询前三条记录，将age+10

SELECT id,username,age+10
FROM user1
order by id desc
LIMIT 2,5

子查询的使用

函数

ROW_NUMBER()

SQL Server中ROW_NUMBER()函数的使用

mysql中的concat函数,concat_ws函数，concat_group函数之间的区别

mysql中的concat函数,concat_ws函数，concat_group函数之间的区别

MySQL日期时间函数大全

MySQL日期时间函数大全

求和 sum()over()和count()over()分析函数

关于mysql中的count()函数

sum()over()和count()over()分析函数

字符串截取

MySQL|SUBSTR() 函数用法

lag&lead(取上次&下次的值)

mysql里 lag() 和 lead() 用法