mysql常用sql记录

注意以下语法为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;

查询结果：