sql应用总结

1、查询内容等于当月内容：

SELECT * FROM t_qa_scholarships WHERE SUBSTR(add_time,1,7) = SUBSTR(CURDATE(),1,7);

2、数据里的时间字段要通过JAVA转换：

java.util.Date   yourDate   =   new   java.util.Date(rs.getTimestamp(num|str).getTime());

3、数据库的几种查看功能

SHOW DATABASES;

USE database_name;

SHOW TABLES;

DESC USER;

SHOW INDEX FROM wp_links;

CREATE INDEX idx_employee ON employee(NAME); 

DROP INDEX idx_employee ON employee; 

4、MYSQL删除重复记录

a、DELETE t_qa_snatch_week AS a FROM t_qa_snatch_week AS a,

(

SELECT *,MIN(id) FROM t_qa_snatch_week GROUP BY user_id HAVING COUNT(1) > 1

) AS b

 WHERE a.user_id = b.user_id AND a.id > b.id;

b、将重复记录导入临时表中在对两表做关联删除操作

4、给mysql数据表字段添加唯一性约束

 ALTER TABLE astro

ADD CONSTRAINT constraint_name UNIQUE KEY(cn_name);