mysql 【常用sql】

修改过mysql数据库字段内容默认值为当前时间
--添加CreateTime 设置默认时间 CURRENT_TIMESTAMP 

ALTER TABLE `table_name`
ADD COLUMN  `CreateTime` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' ;

 
https://www.cnblogs.com/testway/p/5531969.html



--修改CreateTime 设置默认时间 CURRENT_TIMESTAMP 
ALTER TABLE `table_name`
MODIFY COLUMN  `CreateTime` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' ;

 

--添加UpdateTime 设置 默认时间 CURRENT_TIMESTAMP   设置更新时间为 ON UPDATE CURRENT_TIMESTAMP 
ALTER TABLE `table_name`
ADD COLUMN `UpdateTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间' ;

 

--修改 UpdateTime 设置 默认时间 CURRENT_TIMESTAMP   设置更新时间为 ON UPDATE CURRENT_TIMESTAMP 

ALTER TABLE `table_name`
MODIFY COLUMN `UpdateTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMPCOMMENT '创建时间' ;

更新一个表中的某个字段值等于另一个表的某个字段值：

update a inner join b on a.bid=b.id set a.x=b.x,a.y=b.y ;

 insert

    INSERT INTO  table1 (id, table1_field1，table1_field2，...) select table2_filed1, table2_field2,... from table2 where condition1 and condition2 ...;

INSERT INTO T1
( UserId, DepId, SubDepId, PostionType, AuthorityId, ChangeDateS,                   　　InsertDate,  UpdateDate, SakuseiSyaId ) SELECT 
  UserId, DepId, SubDepId, PostionType, AuthorityId, DATE_FORMAT(EmployDate, '%Y%m%d'), NOW(),　　　　NOW(),      uuid() 
 FROM T2 WHERE `Status` = 0 AND QuitFlg = 0 AND UserId < 2

INSERT INTO cpa_ticket_catalog_template 
( id,                       book_id, catalog_id,   summary, summary_type, catalog_name, templateSign, is_rest_by_tax, remarks, deleted, created, updated, spare) SELECT 
REPLACE (UUID(), '-', ''),  "",         id,          "",         2,           NAME,       "",          1,              "",         0,    now(),   NOW(),   ""
FROM cpa_ticket_catalog WHERE parent_id = "1804b3409118465e9438e83c1d97287b"

update当前表 字段 = 另一关联表的 字段 

 UPDATE T1 AS a1, T2 AS a2
  SET a2._name=a1.name
  WHERE a1.id='xxx' AND a1.id=a2.customerId 

MySQL添加数据库的唯一索引的几种方式~

创建表时直接设置:

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `stu_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`stu_id`),
  UNIQUE KEY `UK_student_name` (`name`) 
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

创建唯一索引:
create unique index UK_student_name on student (name);

建表后添加约束:
alter table student add constraint uk_student_name unique (name);

 

Mysql添加和删除唯一索引、主键

1、PRIMARY KEY（主键索引）
添加 ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` );
删除 ALTER TABLE `table_name` DROP PRIMARY KEY;

2、UNIQUE(唯一索引)
添加 ALTER TABLE `table_name` ADD UNIQUE ( `column` );
删除 ALTER TABLE `table_name` DROP INDEX `column`;

————mysql分页——start————————————————————

int pageSize = pageVo.getPageSize();
int startPage = pageSize * (pageVo.getPageNum() - 1);
List<ProjectInfoVO> getProjectList(@Param("startPage") int startPage, @Param("pageSize") int pageSize, @Param("gender") int gender);

 <select id="getUerList" resultType="xx.xx.">
    select
        <include refid="baseColumns"/>
    from user
        where gender = #{gender}
        limit #{startPage}, #{pageSize}
 </select>

————mysql分页——end————————————————————