查询表中重复的数据:
SELECT username,count(*) from user GROUP BY username HAVING count(*) > 1;
查询前一天的数据:
select * from user where to_days(now()) - to_days(sp_logindate) = 1;
根据汉字排序:
select * from order where status='待派工' ORDER BY CONVERT (name USING gbk) COLLATE gbk_chinese_ci;
MySQL中group by 与 order by 同时使用(根据汉字排序):
SELECT
name AS username,
COUNT(*) AS COUNT
FROM
order
WHERE
1=1
AND (
status='待派工'
)
GROUP BY
username
ORDER BY
CONVERT (name USING gbk) COLLATE gbk_chinese_ci;
如果表中的指定的字段长度不够的话,前面补0:
UPDATE user SET password=CONCAT("0",password) WHERE LENGTH(password)=31;
把同一个表的字段更新到另一个字段(user_name1更新到user_name2):
UPDATE user SET user_name2= user_name1;
把一个表中为null的字段更新为固定的值:
UPDATE user SET user_age = '18' WHERE user_age IS NULL;
给数据库固定表的固定字段加索引:
ALTER table user ADD INDEX index_name (`name`);(注意:name两边的不是单引号,而是数字键最左边的符号)
查询7天前的数据
SELECT * FROM user WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) > DATE(user_time) ORDER BY user_time DESC;