注意以下语法为mysql5.7
一、操作表相关的sql
1、给psi_register表添加一个列 TIME_FLAG 插入数据时并给该列当前系统时间
alter table psi_register add column TIME_FLAG TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
2、删除psi_register表的TIME_FLAG字段
ALTER TABLE psi_register DROP TIME_FLAG;
3、随机删除mysql数据表部分数据
delete from psi_register order by rand() limit 10
4、查询数据库表的某一字段重复的次数
select t.userid,t.username count(*) as count from USER_NAME t group by t.userid having count>1;
5、将含有外键的表结构文件查询出并生成批处理文件
SELECT
concat('ALTER TABLE ',t.TABLE_NAME,' DROP FOREIGN KEY ',t.CONSTRAINT_NAME,' ;')
FROM
information_schema.TABLE_CONSTRAINTS t
where t.TABLE_SCHEMA = '数据库名称' -- and t.TABLE_NAME = '表名'
and t.CONSTRAINT_TYPE = 'FOREIGN KEY';
6、MYSQL数据库,查询一周内,合计每天的数量和出现的行数,没有记录的那一天就显示0
一、表结构及测试数据:
CREATE TABLE tst (
testDate date,
testSum INT
);
INSERT INTO tst VALUES ( '2014-01-01', 1);
INSERT INTO tst VALUES ( '2014-01-02', 3);
INSERT INTO tst VALUES ( '2014-01-02', 4);
INSERT INTO tst VALUES ( '2014-01-03', 5);
INSERT INTO tst VALUES ( '2014-01-05', 9);
INSERT INTO tst VALUES ( '2014-01-06', 11);
INSERT INTO tst VALUES ( '2014-01-07', 13);
二、SQL执行语句:
SELECT
t2.all_day AS `日期`,
IFNULL(SUM(testSum), 0) AS `合计数量`,
COUNT(testSum) AS `出现行数`
FROM
(
SELECT
@rownum :=@rownum + 1 AS NO,
DATE_ADD(
'2014-01-01',
INTERVAL @rownum DAY
) AS all_day
FROM
(SELECT @rownum := -1) r_init,
tst
) t2
LEFT JOIN tst ON (
t2.all_day = DATE(tst.testDate)
)
WHERE
t2.all_day >= '2014-01-01'
AND t2.all_day <= '2014-01-07'
GROUP BY
t2.all_day;
查询结果: