SQL学习

①查询新增

insert into 
curriculum_style_manage(curriculum_style_name, curriculum_style_code) 
select curriculum_style_name, curriculum_style_code from curriculum_style_manage where campus_id ='01001';(不会创建一张表)

select * into destTbl from srcTbl;（创建一张表）

②创建自增序列

create sequence Z_HOUSE_SEQ
increment by 1  
start with 1 
nomaxvalue 
nominvalue 
nocache

③修改字段备注

comment on column S_USER.ALLOWLOGIN is '是否允许登录 1(是)或0(否)';

④索引

1.PRIMARY  KEY（主键索引）
mysql>ALTER  TABLE  `table_name`  ADD  PRIMARY  KEY (  `column`  ) 
2.UNIQUE(唯一索引)
mysql>ALTER  TABLE  `table_name`  ADD  UNIQUE (`column` ) 
3.INDEX(普通索引)
mysql>ALTER  TABLE  `table_name`  ADD  INDEX index_name (  `column`  )
4.FULLTEXT(全文索引)
mysql>ALTER  TABLE  `table_name`  ADD  FULLTEXT ( `column` )
5.多列索引
mysql>ALTER  TABLE  `table_name`  ADD  INDEX index_name (  `column1`,  `column2`,  `column3`  )

⑤添加字段

alter table fp_population_info add FP_POPULATION_SHU  int(10) comment '户人口数';
alter table fp_population_info add FP_TOWN varchar(255) comment '镇(乡)';
alter table fp_population_info add FP_VILLAGE_FP  varchar(255) comment '行政村';
alter table fp_population_info add FP_PEOPLE_SHU bigint(30) comment '人编号';

⑥sql查询最大数

SELECT
IFNULL(MAX(SUBSTR(experiment_task_serial_number,12,14)),0) maxNumber
FROM
experiment_task
WHERE SUBSTR(experiment_task_serial_number, 4, 8) = CONCAT( SUBSTR(NOW(), 1, 4),SUBSTR(NOW(), 6, 2),SUBSTR(NOW(), 9, 2))

⑦sql日期的比较
unix_timestamp 函数可以接受一个参数，也可以不使用参数。
它的返回值是一个无符号的整数。不使用参数，它返回自1970年1月1日0时0分0秒到现在所经过的秒数，

UNIX_TIMESTAMP(DATE_FORMAT(us.out_date, '%Y-%m-%d') ) >  UNIX_TIMESTAMP( DATE_FORMAT(NOW(), '%Y-%m-%d'))

⑧sql日期的加减
DATE_FORMAT 来格式化日期字段
日期、时间相减函数：datediff(date1,date2), timediff(time1,time2)

DATEDIFF(DATE_FORMAT(us.out_date, '%Y-%m-%d'),DATE_FORMAT(NOW(), '%Y-%m-%d'))
TIMEDIFF(DATE_FORMAT(us.out_date, '%Y-%m-%d'),DATE_FORMAT(NOW(), '%Y-%m-%d'))

⑨sql新增返回主键

<insert id="saveEquipmentApplyInfo" parameterType="com.dhrtec.lab.entity.form.EquipmentApplyInfoForm">
	<selectKey resultType="java.lang.String" keyProperty="id" order="AFTER">
		SELECT LAST_INSERT_ID()
	</selectKey>
	insert  into equipment_apply_info
	<trim prefix="(" suffix=")" suffixOverrides=",">
		<if test="equipmentApplySerialNumber != null and equipmentApplySerialNumber != ''">
			equipment_apply_serial_number,
		</if>
		<if test="projectId != null and projectId != ''">
			project_id
		</if>
	</trim>
	<trim prefix="values(" suffix=")" suffixOverrides=",">
		<if test="equipmentApplySerialNumber != null and equipmentApplySerialNumber != ''">
			#{equipmentApplySerialNumber},
		</if>
		<if test="projectId != null and projectId != ''">
			#{projectId}
		</if>
	</trim>
</insert>

⑩.模糊查询

like CONCAT('%',ltrim(rtrim(#{financialNumber})),'%')

截位

 SUBSTRING(dic_name, locate('-', dic_name) + 1, 5 )

10mybatis动态查询
①statementType=“STATEMENT”
② ${versionStyle}
mybatis动态新增

*方法:*
 void  insertTable(@Param("list")List<ProjectDataStructureVO> list
							,@Param("tableName")String tableName);
*sql:*
 <insert id="insertTable" parameterType="java.util.List">
      insert into ${tableName}
        (project_id,model_id,version,line_number,column_name）
      values
     <foreach collection="list" item="item" separator="," index="index">
      (#{item.projectId},#{item.modelId},#{item.version},
	#{item.lineNumber},#{item.columnName})
     </foreach>
 </insert>  

mybatis动态创建表

<update id="createNewTable" parameterType="java.lang.String">
        CREATE TABLE ${tableName} (
          `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
          `project_id` decimal(18,0) DEFAULT NULL COMMENT '项目ID',
          `model_id` int(11) DEFAULT NULL COMMENT '模型id',
          `version` varchar(11) DEFAULT NULL COMMENT '版本表主键',
          `line_number` int(11) DEFAULT NULL COMMENT '行号',
          `column_name` varchar(40) DEFAULT NULL COMMENT '字段名称',
          `column_value` varchar(40) DEFAULT NULL COMMENT '字段值',
          `ranking` int(5) DEFAULT NULL COMMENT '序号',
          `under_user_id` decimal(18,0) DEFAULT NULL COMMENT '被试人员ID',
          `check_flg` varchar(1) DEFAULT NULL COMMENT '核查标志',
          `roll_back_flg` varchar(100) DEFAULT NULL COMMENT '回滚删除标志',
          `is_delete` decimal(1,0) NOT NULL DEFAULT '0' COMMENT '删除标志 0:未删除 1:已删除 默认0',
          `creator_id` decimal(18,0) DEFAULT NULL COMMENT '创建者ID',
          `creator_time` datetime DEFAULT NULL COMMENT '创建时间',
          `update_id` decimal(18,0) DEFAULT NULL COMMENT '更新者ID',
          `update_time` datetime DEFAULT NULL COMMENT '更新时间',
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB AUTO_INCREMENT=1  DEFAULT CHARSET=utf8 COMMENT='项目数据结构表';
    </update>

动态删除表

 <update id="deleteTable">
      DROP TABLE ${tableName}
 </update>